iVend Database Database : UpdateTillAmounts Stored Procedure

Creation Date

6/11/2019 7:24 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@pTillSessionKey

In

Reference key of the valid Till session

VarWChar

50

@pUserKey

In

Reference key of the Employee

VarWChar

50

@pComments

In

Comments provided, if any

VarWChar

200

@pReasonCodeKey

In

Reference key of the Reason Code

VarWChar

50

@pTransactionType

In

Type of transaction done

Integer

4

@pSourceDocumentKey

In

Reference key of the source document

VarWChar

50

@pAmount

In

Amount of the transaction

Numeric

9

@pModifiedBy

In

Reference key of the user

VarWChar

50

@pBatchKey

In

Reference key of the Replication Batch

VarWChar

50

@pStoreKey

In

VarWChar

50

@pSessionTransaction

Out

VarWChar

50

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

TrxCompleteTransactionUpdates

Stored Procedure

1

Database Object

Object Type

Description

Dep Level

CfgEnterprise

Table

Stores the basic configuration entries for the system . These settings are mandatory for the system to work properly.

2

CfgSiteInformation

Table

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

1

GetCompanyDateTime

User Defined Function

1

PmtPaymentType

Table

Defines a list of payment types specified in the system. These payment types are used to take payment against a transaction at POS.

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

TilSession

Table

Defines all the till sessions from the time a till is assigned to a POS till the time it is finalized.

1

TilSessionDetail

Table

Defines all the till sessions from the time a till is assigned to a POS till the time it is finalized against each tender type.

1

TilSessionTransaction

Table

Defines the list of all the transactions that occur through the particular till along with the store , POS n user details.

1

TilTill

Table

Till is defined at the MC and attached with the cash drawer at POS. At a time only one till can be associated with the POS. It is the virtual entity in which we defined some settings (like setting the mode in which the till works) that will be applicable at the POS (for which the till is attached).

1

TilTillDetail

Table

Defines the details for the each till set up.

1

TrxTransactionPayment

Table

Stores all the payment details for each transaction.

1

usp_RethrowError

Stored Procedure

Rethrow an error to the calling function/application

1

/**

Begin tran

Select * from TilSessionDetail

EXEC UpdateTillAmounts 10000000000034, 10000000000001, 'sdfa', 0,  3, 0, 1000, 10000000000001

Select * from TilSessionDetail

Rollback

*/

CREATE PROCEDURE [dbo].[UpdateTillAmounts]

(

@pTillSessionKey NVARCHAR(50),

@pUserKey   NVARCHAR(50),

@pComments   NVARCHAR(200),

@pReasonCodeKey NVARCHAR(50),

@pTransactionType INT,

      --

      --Valid values are:

      --CashIn = 0

      --CashOut = 1

      --Transaction = 2

      --Expense = 3

@pSourceDocumentKey NVARCHAR(50),

@pAmount   DECIMAL(20, 5),

@pModifiedBy NVARCHAR(50),

@pBatchKey   NVARCHAR(50) = 0,

@pStoreKey   NVARCHAR(50),

@pSessionTransaction NVARCHAR(50) OUTPUT

)

AS

BEGIN

SET NOCOUNT ON

-- If by chance amount comes as ZERO, just get out of this procedure...

IF @pAmount = 0

RETURN

DECLARE @lErrorDesc   VARCHAR(255),

  @lTillKey   NVARCHAR(50),

  @lNewKey   NVARCHAR(50),

  @lPOSKey   NVARCHAR(50),

  @lSiteId   INT,

  @lPOSSiteId   INT,

  @lSubsidiaryKey     NVARCHAR(50),

  @lTillDetailKey NVARCHAR(50)

BEGIN TRY

-- Get Till information

Select @lTillKey = TillKey, @lPOSKey = POSKey

FROM   TilSession

WHERE  TillSessionKey = @pTillSessionKey

IF @@ROWCOUNT < 1

RAISERROR('%s',16,-1,'TillLogic.TillNotFound')

Select @lSubsidiaryKey = B.SubsidiaryKey

From TilTill A, RtlStore B

Where TillKey = @lTillKey

And A.StoreKey = B.StoreKey

---- Get Site Id

SELECT @lSiteId = SiteId, @lPOSSiteId = ISNULL(POSSiteId, 0) FROM CfgSiteInformation

IF @@ROWCOUNT = 0

RAISERROR('%s',16,-1,'TillLogic.SiteNotFound')

if @lPOSSiteId > 0

set @lSiteId = @lPOSSiteId

IF @pTransactionType = 2 -- TRANSACTION

BEGIN

SELECT @pAmount = ISNULL(Sum(Amount - ChangeAmount),0)

From TrxTransactionPayment With(nolock)

Where TransactionKey = @pSourceDocumentKey

And  IsVoided = 'FALSE'

END

-- Insert into Session Transaction

-- Generate Next Number

SELECT @lNewKey = NEWID()

INSERT INTO TilSessionTransaction (

  [TillSessionTransactionKey], [TillSessionKey], [TillKey], [StoreKey] ,[POSKey] ,[UserKey] ,

  [TransactionType] ,[SourceDocumentKey] ,[ReasonCodeKey] ,[Amount] ,[BusinessDate] ,[EntryDate], Comments)

  SELECT @lNewKey, @pTillSessionKey

   , @lTillKey, StoreKey

   , @lPOSKey, @pUserKey

   , @pTransactionType

   , @pSourceDocumentKey, @pReasonCodeKey

   , @pAmount, BusinessDate

   , dbo.GetCompanyDateTime()

   , LTRIM(RTRIM(ISNULL(@pComments, '')))

FROM RtlStore

WHERE StoreKey = @pStoreKey

SELECT @pSessionTransaction = @lNewKey

-- GENERATE REPLICATION ENTRY FOR Till Session Transaction

Exec DataNotification 139, @lNewKey, @pBatchKey , 0, @pModifiedBy

-- Update the Till Session for Amounts

UPDATE TilSession

SET  TransactionAmount = TransactionAmount + @pAmount,

  SystemClosingAmount = SystemClosingAmount + @pAmount

WHERE TillSessionKey = @pTillSessionKey

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

SELECT NEWID(), 136, @pTillSessionKey, @pBatchKey, 1, 'FALSE', @lSiteId

-- Update Session Detail amounts for Transactions

IF (@pTransactionType = 0 OR @pTransactionType = 1 OR @pTransactionType = 3) --// CASH IN AND OUT

BEGIN

-- Update Session Detail table  for CASH

UPDATE TilSessionDetail

SET  TransactionAmount = TransactionAmount + @pAmount, -- ChangeAmount,

   TotalAmount = TotalAmount + @pAmount -- ChangeAmount

FROM  PmtPaymentType p

WHERE TilSessionDetail.PaymentTypeKey = p.PaymentTypeKey

AND  p.Type = 0 -- CASH

AND  TillSessionKey = @pTillSessionKey

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

SELECT NEWID(), 137, TillSessionDetailKey, @pBatchKey, 1, 0, @lSiteId

FROM TilSessionDetail A, PmtPaymentType p

WHERE TillSessionKey = @pTillSessionKey

And A.PaymentTypeKey = p.PaymentTypeKey

And p.Type = 0

And p.SubsidiaryKey = @lSubsidiaryKey

SELECT @lTillDetailKey =  A.TillDetailKey

FROM TilTillDetail A, PmtPaymentType B

WHERE A.PaymentTypeKey = B.PaymentTypeKey

AND  B.Type = 0 -- CASH

AND  A.TillKey = @lTillKey

And  B.SubsidiaryKey = @lSubsidiaryKey

-- Update Till Detail table  for CASH

UPDATE TilTillDetail

SET  TransactionAmount = TransactionAmount + @pAmount,

   SystemClosingAmount = SystemClosingAmount + @pAmount

WHERE TillDetailKey= @lTillDetailKey

--Generate the event for the tiltilldetail

Exec DataNotification 175, @lTillDetailKey, @pBatchKey, 0, @pModifiedBy

-- Update Tipple Amount

-- CASH always affects Tipple Point

Update TilTill

SET  TippleAmount = TippleAmount + @pAmount

WHERE TillKey = @lTillKey

END

ELSE IF @pTransactionType = 2 -- TRANSACTION

BEGIN

-- Update Session Detail table

IF EXISTS

   (

  Select 1 From PmtPaymentType

  Where PaymentTypeKey Not in (Select PaymentTypeKey From TilTillDetail Where TillKey = @lTillKey)

      And IsCounted =1 And IsActive = 1

      And IsDeleted = 0 And SubsidiaryKey = @lSubsidiaryKey

  )

BEGIN

  SELECT @lTillDetailKey = NEWID()

  INSERT INTO TilTillDetail (TillDetailKey, TillKey, PaymentTypeKey, Type, StartingAmount, TransactionAmount, SystemClosingAmount, ClosingAmount)

  Select TOP 1 @lTillDetailKey, @lTillKey, PaymentTypeKey, Type, 0, 0, 0 ,0

  From PmtPaymentType

  Where PaymentTypeKey Not in (Select PaymentTypeKey From TilTillDetail Where TillKey = @lTillKey)

  And IsCounted = 1

  And IsActive = 1

  And IsDeleted = 0

  And SubsidiaryKey = @lSubsidiaryKey

  --Generate the event for the tiltilldetail

  Exec DataNotification 175, @lTillDetailKey, @pBatchKey, 0, @pModifiedBy

END

UPDATE s

SET  s.TransactionAmount = s.TransactionAmount + ISNULL(b.Amount, 0),

   s.TotalAmount = s.TotalAmount + ISNULL(b.Amount, 0)

FROM TilSessionDetail s ,

 (

    Select PaymentTypeKey, Sum(Amount - ChangeAmount) 'Amount'

    FROM TrxTransactionPayment

    WHERE TransactionKey = @pSourceDocumentKey

    And  IsVoided = 'FALSE'

    GROUP BY PaymentTypeKey

 ) b

WHERE TillSessionKey = @pTillSessionKey

AND  s.PaymentTypeKey = b.PaymentTypeKey

--Generate the replication entry of the tilsession

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

SELECT NEWID(), 137, TillSessionDetailKey, @pBatchKey, 1, 0, @lSiteId

FROM TilSessionDetail s ,

 (

    Select PaymentTypeKey, Sum(Amount - ChangeAmount) 'Amount'

    FROM TrxTransactionPayment

    WHERE TransactionKey = @pSourceDocumentKey

    And  IsVoided = 'FALSE'

    GROUP BY PaymentTypeKey

 ) b

WHERE TillSessionKey = @pTillSessionKey

AND  s.PaymentTypeKey = b.PaymentTypeKey

-- Update Till Detail table

UPDATE s

SET  s.TransactionAmount = s.TransactionAmount + ISNULL(b.Amount, 0),

   s.SystemClosingAmount = s.SystemClosingAmount + ISNULL(b.Amount, 0)

FROM TilTillDetail s ,

 (

  Select PaymentTypeKey, Sum(Amount - ChangeAmount) 'Amount'

  FROM TrxTransactionPayment

  WHERE TransactionKey = @pSourceDocumentKey

  And  IsVoided = 'FALSE'

  GROUP BY PaymentTypeKey

 ) b

WHERE TillKey = @lTillKey

AND  s.PaymentTypeKey = b.PaymentTypeKey

-- Update Tipple Amount

Update TilTill

SET  TippleAmount = TippleAmount + ISNULL(b.Amount, 0)

FROM (Select 'Amount' = Sum(p.Amount - p.ChangeAmount)

  FROM TrxTransactionPayment p, PmtPaymentType t

  WHERE p.TransactionKey = @pSourceDocumentKey

  AND  p.PaymentTypeKey = t.PaymentTypeKey

  AND  t.AffectsTippleAmount = 'TRUE'

  And  p.IsVoided = 'FALSE'

  And  t.SubsidiaryKey = @lSubsidiaryKey

   ) b

WHERE TillKey = @lTillKey

END

-- Update the Till for Amounts

UPDATE TilTill

SET  TransactionAmount = TransactionAmount + @pAmount,

  SystemClosingAmount = SystemClosingAmount + @pAmount

WHERE TillKey = @lTillKey

RETURN (0)

END TRY

BEGIN CATCH

-- Raise the Error that caused the Error

Exec usp_RethrowError

RETURN (1)

END CATCH

END