iVend Database Database : UpdateTillAfterPaymentInsert Stored Procedure

Creation Date

7/5/2019 6:02 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@pPaymentTypeKey

In

Reference key of the Payment Type

VarWChar

50

@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

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

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

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

usp_RethrowError

Stored Procedure

Rethrow an error to the calling function/application

1

/**

Begin tran

select * from Tiltilldetail

Update TilTill set Status = 0 where tillkey =  10000000000019

Select * from SysNextNumber where NextNumberID in ('Table.TillDetail', 'Table.SessionDetail')

EXEC UpdateTillAfterPaymentInsert 10000000000011

Select * from SysNextNumber where NextNumberID in ('Table.TillDetail', 'Table.SessionDetail')

select * from Tiltilldetail

Rollback

--10000000000015 / 15

Select * from SysNextNumber

Select * from TilTill

select * from Tiltilldetail --14

-- 10000000000143 / 143

select * from TilSessionDetail -- 21

Rollback

Begin Tran

Delete from CfgSiteInformation

Exec UpdateTillAfterPaymentInsert 1

Rollback

*/

CREATE PROCEDURE [dbo].[UpdateTillAfterPaymentInsert]

(

  @pPaymentTypeKey NVARCHAR(50)

)

AS

BEGIN

  SET NOCOUNT ON

/******

 This procedure would be called after a new payment Type is created in the system

 This proc would insert records in the detail table for Till

 for all tills which are not finalized

 Also would insert records for Open Till Sessions in the session detail table

******/

  DECLARE @lErrorDesc VARCHAR(255),

  @lSiteId INT,

  @lRowCount INT,

  @lErrror INT,

  @lPOSSiteId INT

BEGIN TRY

-- Get Site Id

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

  FROM    CfgSiteInformation

  IF @@ROWCOUNT = 0

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

---SECTION : - Inserting into Till Detail table -----

-- Check if any till is not FINALIZED mode

  IF NOT EXISTS ( SELECT 1

                  FROM    TilTill

                  WHERE   Status <> 4 --// Finalized

   )

  BEGIN

      RETURN

  END

    DECLARE @PmtPaymentType INT

  SELECT @PmtPaymentType =     Type

                    FROM      PmtPaymentType

                    WHERE     PaymentTypeKey = @pPaymentTypeKey

-- Insert record into Till Session Detail for all open sessions for the new

-- payment type created

    IF NOT EXISTS ( SELECT TOP 1 1

                                    FROM   TilTillDetail

                                    WHERE  PaymentTypeKey = @pPaymentTypeKey )

BEGIN

  INSERT INTO TilTillDetail

           (

             TillDetailKey,

             TillKey,

             PaymentTypeKey,

             Type,

             StartingAmount,

             TransactionAmount,

             SystemClosingAmount,

             ClosingAmount

           )

          SELECT NEWID(),

                   TillKey,

                  @pPaymentTypeKey,

    @PmtPaymentType,

                  --( SELECT    Type

                  --  FROM      PmtPaymentType

                  --  WHERE     PaymentTypeKey = @pPaymentTypeKey

                  --),

                  0,

                  0,

                  0,

                  0

          FROM    TilTill

          WHERE   Status < 3

                  --AND NOT EXISTS ( SELECT 1

                  --                 FROM   TilTillDetail

                  --                 WHERE  PaymentTypeKey = @pPaymentTypeKey )

END

  ---SECTION : - Inserting into Till Session Detail table -----

-- Check if any till session is open

  IF NOT EXISTS ( SELECT 1

                  FROM    TilSession

                  WHERE   Status = 0 --// OPEN

   )

  BEGIN

      RETURN

  END

-- Insert record into Till Session Detail for all open sessions for the new

-- payment type created

  INSERT INTO TilSessionDetail

           (

             TillSessionDetailKey, TillSessionKey, PaymentTypeKey,

             StartingAmount, TransactionAmount, TotalAmount,

             DocumentCount

           )

          SELECT NEWID(), TillSessionKey, @pPaymentTypeKey,

                  0 StartingAmount, 0 TransactionAmount, 0 TotalAmount,

    0 DocumentCount

          FROM    TilSession

          WHERE   Status = 0

                  AND NOT EXISTS ( SELECT 1

                                    FROM   TilSessionDetail

                                    WHERE  PaymentTypeKey = @pPaymentTypeKey )

---ENDSECTION : - Inserting into Till Session Detail table -----

RETURN (0)

END TRY

BEGIN CATCH

-- Raise the Error that caused the Error

Exec usp_RethrowError

RETURN (1)

END CATCH

END