iVend Database Database : DeleteSuspendedTransactionDetails Stored Procedure

Creation Date

9/12/2019 6:40 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@pTransactionKey

In

Reference key of the Suspended Transaction

VarWChar

50

@pNewTransactionKey

In

Reference key of the Current Transaction

VarWChar

50

@pBatchKey

In

Reference key of the current Batch

VarWChar

50

@pDebug

In

Whether this procedure has to be executed in Debug mode or not

VarChar

1

@pSiteId

In

to identify the store

Integer

4

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

CfgSiteInformation

Table

Stores the site information. Enterprise always has the site id as 1. System Table Warning : Should not be modified directly

1

RepReplicationTransaction

Table

Stores the Transaction details for them the data needs to be replicated

1

RtlStore

Table

Contains the details of Stores defined in the system.

1

TrxTransaction

Table

The main table which defined the primary details concerned with every type of transaction.

1

CREATE PROCEDURE [dbo].[DeleteSuspendedTransactionDetails]

(

@pTransactionKey nvarchar(50),

@pNewTransactionKey nvarchar(50),

@pBatchKey   nvarchar(50),

@pDebug   CHAR(1),

@pSiteId   INT

)

AS

BEGIN

SET NOCOUNT ON

Declare @ErrorDesc   NVARCHAR(255),

  @lCurrentTransactionKey nvarchar(50),

  @pStoreKey   nvarchar(50),

  @pPOSSiteId   INT

  Select @pStoreKey = StoreKey From RtlStore With(nolock) Where SiteId = @pSiteId

  Select @pPOSSiteId = ISNULL(POSSiteId, 0) From CfgSiteInformation

  Create table #SuspendTransactions (TransactionKey nvarchar(50))

  if (len(@pTransactionKey) > 1)

      BEGIN

      INSERT INTO #SuspendTransactions

      SELECT DISTINCT TransactionKey

      FROM TrxTransaction With (nolock)

      WHERE IsSuspended = 'TRUE'

      And IsVoided = 0

      AND TransactionKey = @pTransactionKey

      And StoreKey = @pStoreKey

      END

  ELSE

      BEGIN

      Insert into #SuspendTransactions

      SELECT DISTINCT TransactionKey

      FROM TrxTransaction With (nolock)

      WHERE IsSuspended = 'TRUE'

      AND ApprovalStatus != 1

      And IsVoided = 0

      AND TransactionKey = CASE WHEN @pTransactionKey = 0 THEN TransactionKey ELSE @pTransactionKey END

      And StoreKey = @pStoreKey

  END

  -- Commenting this code to fix Support Ticket 130881

  --Delete A

  --FROM #SuspendTransactions A , TrxTransactionPayment B with(nolock)

  --WHERE A.TransactionKey = B.TransactionKey

  --And B.IsVoided = 0

  if not exists (select 1 from #SuspendTransactions)

  begin

  return

  end

    DECLARE TransactionListCursor CURSOR FOR

SELECT TransactionKey From #SuspendTransactions

OPEN TransactionListCursor

FETCH NEXT FROM  TransactionListCursor INTO @lCurrentTransactionKey

WHILE @@FETCH_STATUS=0

BEGIN

INSERT INTO RepReplicationTransaction(ReplicationTransactionKey, SourceType, SourceKey, BatchKey,OperationType,Flag)

SELECT NEWID(), 143, @lCurrentTransactionKey, @pBatchKey, 1, 'FALSE'

Update TrxTransaction

Set IsDeleted = 1

Where TransactionKey = @lCurrentTransactionKey

FETCH NEXT FROM TransactionListCursor INTO @lCurrentTransactionKey

  END

  CLOSE TransactionListCursor

  DEALLOCATE TransactionListCursor

RETURN

ERRORHANDLER:

RAISERROR('%s',16,-1,@ErrorDesc)

RETURN

END