iVend Database Database : RemoveTill Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@pTillKey

In

Reference key of the Till

VarWChar

50

@pTillSessionKey

In

Reference key of the Till Session

VarWChar

50

@pModifiedBy

In

Reference key of the Security User

VarWChar

50

@pCountKey

In

Reference key of the Count

VarWChar

50

@RETURN_VALUE

Return Value

Integer

4

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

TilCount

Table

Defines the amount details like the opening amount, system amount etc. associated with the till

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

TilSessionUser

Table

Stores the various till user session 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

usp_RethrowError

Stored Procedure

Rethrow an error to the calling function/application

1

CREATE PROCEDURE [dbo].[RemoveTill]

(

@pTillKey   nvarchar(50),

@pTillSessionKey nvarchar(50),

@pModifiedBy nvarchar(50),

@pCountKey   nvarchar(50)

)

AS

BEGIN

SET NOCOUNT ON

DECLARE @lErrorDesc   VARCHAR(255),

  @lClosingAmount Decimal(20, 5),

  @lTillSessionKey nvarchar(50),

  --@BatchKey   nvarchar(50),

  --@ReplicationKey  nvarchar(50),

  @lSiteId       INT,

  @lPOSSiteId   INT,

  @lBatchKey   NVARCHAR(50),

  @lSubsidiaryKey     nvarchar(50)

select @lBatchKey = newid()

SELECT @lClosingAmount = 0.00000

Select @lSubsidiaryKey = B.SubsidiaryKey

From TilTill A, RtlStore B

Where TillKey = @pTillKey

And A.StoreKey = B.StoreKey

BEGIN TRY

-- Close all Active User Sessions....

UPDATE TilSessionUser

SET  Status = 1, --//CLOSED

  EndDate = dbo.GetCompanyDateTime(),

  Modified = dbo.GetCompanyDateTime(),

  ModifiedBy = @pModifiedBy

WHERE TillKey = @pTillKey

-- GET ALL ACTIVE TILL SESSION'S

DECLARE CurTillSessions CURSOR FOR

SELECT TillSessionKey

FROM TilSession

WHERE TillKey = @pTillKey

AND  Status = 0 --// OPEN

OPEN CurTillSessions

FETCH NEXT FROM  CurTillSessions INTO @lTillSessionKey

WHILE @@FETCH_STATUS = 0

BEGIN

  IF @pCountKey != '0' and @lTillSessionKey = @pTillSessionKey

  BEGIN

  SELECT @lClosingAmount = CountedAmount

  FROM TilCount

  WHERE TillCountKey = @pCountKey

  Select @lClosingAmount = ISNULL(@lClosingAmount,0) + ISNULL(Sum(TotalAmount),0)

  from TilSessionDetail d, PmtPaymentType p

  Where d.PaymentTypeKey = p.PaymentTypeKey

  AND  p.IsCounted = 'FALSE'

  AND  TillSessionKey = @pTillSessionKey

And p.PaymentTypeKey = @lSubsidiaryKey

  END

  ELSE -- If NO counting was done at REMOVE,

  BEGIN

  SELECT @lClosingAmount = ISNULL(SystemClosingAmount,0)

  FROM TilSession

  WHERE TillSessionKey = @lTillSessionKey

  END

  UPDATE TilSession

  SET  Status = 1,   --// CLOSED

    EndDate = dbo.GetCompanyDateTime(),

    ClosingAmount = isnull(@lClosingAmount,0),

    Modified = dbo.GetCompanyDateTime(),

    ModifiedBy = @pModifiedBy

  WHERE TillSessionKey = @lTillSessionKey

  INSERT INTO RepReplicationTransaction(ReplicationTransactionKey, SourceType,

   SourceKey, BatchKey,OperationType,Flag)

SELECT NEWID(), 136, @lTillSessionKey, @lBatchKey, 1, 'FALSE'

FETCH NEXT FROM CurTillSessions INTO @lTillSessionKey

END

CLOSE CurTillSessions

DEALLOCATE CurTillSessions

-- Change the Status of the Till being removed

UPDATE TilTill

SET  Status = 2 --// DEASSIGNED

WHERE TillKey = @pTillKey

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

FROM CfgSiteInformation

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

SELECT NEWID(), 141, @pTillKey, @lBatchKey, 1, 'FALSE'

RETURN (0)

END TRY

BEGIN CATCH

IF CURSOR_STATUS('global','CurTillSessions') > 0

BEGIN

CLOSE CurTillSessions

DEALLOCATE CurTillSessions

END

-- Raise the Error that caused the Error

Exec usp_RethrowError

RETURN (1)

END CATCH

END