iVend Database Database : AssignTill 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

@pPOSKey

In

Reference key of the POS

VarWChar

50

@pUserKey

In

Reference key of the User

VarWChar

50

@pModifiedBy

In

Reference key of the Security User

VarWChar

50

@pCashDrawerId

In

Cash drawer to which the till has been assigned

Integer

4

@pCountKey

In

Reference key of the Count

VarWChar

50

@pDebug

In

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

VarChar

1

@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

RtlPOS

Table

Contains details about all POS defined in the system.

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

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

EXEC AssignTill 20000000000124, 10000000000001, 10000000000001, 1,  1, 20000000000125, 'Y'

rollback

select * from TilSession

select * from TilSessionDetail

Rollback

*/

CREATE PROCEDURE [dbo].[AssignTill]

(

@pTillKey   nvarchar(50),

@pPOSKey   nvarchar(50),

@pUserKey   nvarchar(50),

@pModifiedBy nvarchar(50),

@pCashDrawerId Int,

@pCountKey   nvarchar(50),

@pDebug   CHAR(1) = 'N'

)

AS

BEGIN

SET NOCOUNT ON

DECLARE @lErrorDesc   VARCHAR(255),

  @lSiteId   INT,

  @lRowCount   INT,

  @lTillSessionKey NVARCHAR(50),

  @lBatchKey   NVARCHAR(50),

  @lStartingAmount DECIMAL(20, 5),

  @lStoreKey   nvarchar(50),

  @lPOSSiteId   INT,

  @lNextNumber INT,

  @lSubsidiaryKey     nvarchar(50)

BEGIN TRY

select @lBatchKey = newid()

-- Check if drawer selected already has a till or not

IF EXISTS(SELECT 1 FROM TilSession A, RtlPOS B WHERE A.Status = 0 AND A.POSKey = @pPOSKey AND A.CashDrawerId = @pCashDrawerId AND B.POSType = 0 AND B.POSKey = A.POSKey)

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

-- Check if the Till is in OPEN mode

IF NOT EXISTS ( SELECT 1 FROM TilTill WHERE TillKey = @pTillKey

    AND (Status = 0 --// OPEN

    OR Status = 2)) --// DEASSIGNED

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

Select @lSubsidiaryKey = B.SubsidiaryKey

From TilTill A, RtlStore B

Where TillKey = @pTillKey

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 @pDebug = 'Y'

SELECT '@lSiteId' = @lSiteId

SELECT @lStoreKey = StoreKey From RtlPOS Where POSKey = @pPOSKey

-- Update Till Status to Assigned

Update TilTill

Set  Status = 1,   --// ASSIGNED

StartDate = Case When StartDate Is Null Then dbo.GetCompanyDateTime() Else StartDate End,

ModifiedBy = @pModifiedBy,

Modified = dbo.GetCompanyDateTime()

Where TillKey = @pTillKey

INSERT INTO RepReplicationTransaction(ReplicationTransactionKey, SourceType,

   SourceKey, BatchKey,OperationType,Flag)

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

SELECT @lTillSessionKey = NEWID()

-- Insert into TilSession table

Insert Into TilSession(

  [TillSessionKey], [TillKey], [StoreKey] ,[POSKey] ,[UserKey] ,[BusinessDate] ,[StartDate] ,[EndDate],

  [Status] ,[StartingAmount] ,[TransactionAmount] ,[SystemClosingAmount] ,[ClosingAmount] ,[CashDrawerId],

  [Created], [Modified], [CreatedBy] ,[ModifiedBy])

SELECT @lTillSessionKey, TillKey, t.StoreKey, @pPOSKey, @pUserKey, s.BusinessDate, dbo.GetCompanyDateTime(), null,

  0, 0.00, 0.00, 0.00, 0.00, @pCashDrawerId,

  dbo.GetCompanyDateTime(), dbo.GetCompanyDateTime(), @pModifiedBy, @pModifiedBy

FROM TilTill t, RtlStore s

WHERE s.StoreKey = t.StoreKey

AND  s.StoreKey = @lStoreKey

AND  t.TillKey = @pTillKey

INSERT INTO RepReplicationTransaction(ReplicationTransactionKey, SourceType,

   SourceKey, BatchKey,OperationType,Flag)

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

IF @pDebug = 'Y'

SELECT 'After Inserting Entry Into Till Session Header...'

-- Insert into Till Session Detail table

INSERT INTO TilSessionDetail

   (

    TillSessionDetailKey, TillSessionKey, PaymentTypeKey, StartingAmount, TransactionAmount,

    TotalAmount, DocumentCount

   )

SELECT NEWID(), @lTillSessionKey, PaymentTypeKey, 0, 0, 0, 0

FROM PmtPaymentType

WHERE IsActive = 'TRUE'

AND  IsDeleted = 'FALSE'

And  SubsidiaryKey = @lSubsidiaryKey

  INSERT INTO RepReplicationTransaction(ReplicationTransactionKey, SourceType,

   SourceKey, BatchKey,OperationType,Flag)

SELECT NEWID(), 137, TillSessionDetailKey, @lBatchKey, 0, 0

FROM TilSessionDetail

WHERE TillSessionKey = @lTillSessionKey

declare @tillRecordCount INT

SELECT @tillRecordCount = COUNT(1) FROM TilSession Where TillKey = @pTillKey and UserKey = @pUserKey

---in case the till is etup for eachlogin then do not update the startingamount for each session

DECLARE @lastSessionKey nvarchar(50)

IF EXISTS(SELECT 1 FROM TilTill WHERE  TillKey = @pTillKey And (RemoveTillOnLogout = 1 or IsVariableFloatAmount = 1) And Mode = 0 And @tillRecordCount > 1)

BEGIN

  SELECT TOP 1 @lastSessionKey = TillSessionKey FROM TilSession Where TillKey = @pTillKey And TillSessionKey <> @lTillSessionKey ORDER BY TillSessionKey DESC

  UPDATE A

  SET  A.StartingAmount = B.StartingAmount,

    A.TransactionAmount = B.TransactionAmount,

    A.TotalAmount = B.TotalAmount

  FROM TilSessionDetail A, TilSessionDetail B

  WHERE A.PaymentTypeKey = B.PaymentTypeKey

  AND  A.TillSessionKey = @lTillSessionKey

  AND  B.TillSessionKey = @lastSessionKey

  UPDATE A

  SET  A.StartingAmount = B.StartingAmount,

    A.TransactionAmount = B.TransactionAmount,

    A.SystemClosingAmount = B.SystemClosingAmount

  FROM TilSession A, TilSession B

  WHERE A.TillSessionKey = @lTillSessionKey

  AND  B.TillSessionKey = @lastSessionKey

  AND  A.TillKey = B.TillKey

  And  A.TillKey = @pTillKey

END

ELSE

BEGIN

  -- Update the Starting Amounts for the Session depending on the Current System Amount for the Till

  UPDATE s

  SET  StartingAmount = SystemClosingAmount,

    TotalAmount = SystemClosingAmount

  FROM TilSessionDetail s, TilTillDetail c, PmtPaymentType p

  WHERE s.PaymentTypeKey = c.PaymentTypeKey

  AND  c.PaymentTypeKey = p.PaymentTypeKey

  AND  c.TillKey = @pTillKey

  AND  s.TillSessionKey = @lTillSessionKey

  And  p.SubsidiaryKey = @lSubsidiaryKey

  SELECT @lStartingAmount = Sum(StartingAmount)

  FROM TilSessionDetail

  WHERE TillSessionKey = @lTillSessionKey

  UPDATE TilSession

  SET  StartingAmount = IsNull(@lStartingAmount, 0),

    SystemClosingAmount = IsNull(@lStartingAmount, 0)

  WHERE TillSessionKey = @lTillSessionKey

END

IF @pDebug = 'Y'

BEGIN

Select * from TilSession Where TillSessionKey = @lTillSessionKey

Select * from TilSessionDetail Where TillSessionKey = @lTillSessionKey

END

RETURN (0)

END TRY

BEGIN CATCH

-- Raise the Error that caused the Error

Exec usp_RethrowError

RETURN (1)

END CATCH

END