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

@pUserKey

In

Reference key of the User

VarWChar

50

@pCountType

In

Integer

4

@pSiteId

In

to identify the store

Integer

4

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

InvSerial

Table

Serial details gets affected when doing any transaction with the item which has been serial tracked.

1

PmtCurrency

Table

Define a list of all currecies defined in the system.

1

PmtCurrencyDenomination

Table

Defines a list of denominations for each of the currencies.

1

PmtExchangeRate

Table

Stores the Exchange Rate values for the currencies defined in application

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

PmtPaymentTypeStore

Table

Stores the payment types associated with a store

1

RtlStore

Table

Contains the details of Stores defined in the system.

1

RtlSubsidiary

Table

Defines all the Subsidiaries defined in the application

1

TilInventory

Table

Specifies the inventory count based on warehouse

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

TrxTransaction

Table

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

1

TrxTransactionPayment

Table

Stores all the payment details for each transaction.

1

CREATE PROCEDURE [dbo].[CreateTillCount]

(

@pTillKey   nvarchar(50),

@pUserKey   nvarchar(50),

@pCountType   INT,

      --Valid values are:

      --Create = 0

      --Assign = 1

      --Remove = 2

      --Close = 3

      --Finalize = 4

      --Audit = 5

@pSiteId INT

)

AS

BEGIN

SET NOCOUNT ON

DECLARE @lErrorDesc   VARCHAR(255),

  @lTillStatus INT ,

  @lLastCountKey nvarchar(50),

  @lLastSessionKey nvarchar(50),

  @lSubSidiaryKey nvarchar(50),

  @lSubSidiaryCurrencyKey nvarchar(50),

  @StoreKey   nvarchar(50)

SELECT @lTillStatus = 0

SELECT @lSubSidiaryKey = '0'

SELECT @lSubSidiaryCurrencyKey = '0'

Select @StoreKey = StoreKey

From RtlStore

Where SiteId= @pSiteId AND IsDeleted=0

Select @lSubSidiaryKey = ISNULL(C.SubsidiaryKey,'0') , @lSubSidiaryCurrencyKey = iSNULL(D.LocalCurrencyKey,'0')

From TilTill A, RtlStore C, RtlSubsidiary D

Where A.StoreKey = C.StoreKey

AND TillKey = @pTillKey

And C.SubsidiaryKey = D.SubsidiaryKey

/*

 ******** COUNT TYPE ************

 Create = 0,

       Assign = 1,

       Remove  = 2,

       Close = 3,

       Finalize = 4,

       Audit = 5

*/

If @pCountType = 0 -- CREATE NEW TILL COUNT

BEGIN

-- Get the Count Header Record

SELECT 'TillKey' = @pTillKey, 'UserKey' = @pUserKey, 'OpeningAmount' = 0.00000, 'SystemAmount' = 0.00000,

  'CountedAmount' = 0.00000, 'VarianceAmount' = 0.00000

-- Get the Count Detail record, get all valid payment types where counting is allowed

SELECT p.PaymentTypeKey, 'OpeningAmount' = 0.00000, 'CountedAmount' = 0.00000, 'SystemAmount' = 0.00000,

  'VarianceAmount' = 0.00000, 'DocumentCount' = 0

FROM PmtPaymentType p Inner Join PmtPaymentTypeStore s

On  p.PaymentTypeKey= s.PaymentTypeKey

WHERE p.IsCounted = 'TRUE'

AND  p.IsDeleted = 'FALSE'

AND  p.IsActive = 'TRUE'

AND ISNULL(p.SubsidiaryKey, '0') = @lSubSidiaryKey

AND  s.StoreKey= @StoreKey

ORDER BY p.DisplayOrder

END

ELSE If @pCountType = 4 -- FINALIZE TILL COUNT

BEGIN

-- Get the Count Header Record

SELECT 'TillKey' = TillKey, 'UserKey' = @pUserKey, 'OpeningAmount' = Sum(d.StartingAmount),

  'SystemAmount' = Sum(d.SystemClosingAmount),

  'CountedAmount' = 0.00000, 'VarianceAmount' = Sum(d.SystemClosingAmount) * -1

FROM TilTillDetail d, PmtPaymentType p, PmtPaymentTypeStore s

WHERE TillKey = @pTillKey

AND  d.PaymentTypeKey = p.PaymentTypeKey

AND  p.IsCounted = 'TRUE'

And ISNULL(p.SubsidiaryKey, '0') = @lSubSidiaryKey

And  P.IsDeleted = 'FALSE'

AND     s.PaymentTypeKey = p.PaymentTypeKey

AND  s.StoreKey = @StoreKey

GROUP BY TillKey

-- Get the Count Detail record, get all valid payment types where counting is allowed

SELECT p.PaymentTypeKey, 'OpeningAmount' = d.StartingAmount, 'CountedAmount' = 0.00000,

  'SystemAmount' = d.SystemClosingAmount,

  'VarianceAmount' = d.SystemClosingAmount * -1, 'DocumentCount' = 0

FROM TilTillDetail d, PmtPaymentType p, PmtPaymentTypeStore s

WHERE TillKey = @pTillKey

AND  d.PaymentTypeKey = p.PaymentTypeKey

AND  p.IsCounted = 'TRUE'

And ISNULL(SubsidiaryKey, '0') = @lSubSidiaryKey

And  P.IsDeleted = 'FALSE'

AND     p.PaymentTypeKey = s.PaymentTypeKey

AND  s.StoreKey = @StoreKey

ORDER BY p.DisplayOrder

END

ELSE

BEGIN

/***** TILL STATUS *******

  New = 0,

  Assigned = 1,

  DeAssigned = 2,

  Closed = 3,

  Finalized = 4

 *******/

SELECT @lTillStatus = Status

FROM TilTill

WHERE TillKey = @pTillKey

IF @lTillStatus = 0 --// NEW

BEGIN

  --// If Till is New, then get the amounts from the TilCountTables where CountType = START

  SELECT @pTillKey, 'UserKey' = @pUserKey, 'OpeningAmount' = Sum(StartingAmount),

    'CountedAmount' = 0.0000,

    'SystemAmount' = Sum(StartingAmount),

    'VarianceAmount' = 0.00000 - Sum(StartingAmount),

    'DocumentCount' = 0

  FROM TilTillDetail d, PmtPaymentType p, PmtPaymentTypeStore s

  WHERE d.PaymentTypeKey = p.PaymentTypeKey

  AND  p.IsCounted = 'TRUE'

  AND  p.IsDeleted = 'FALSE'

  AND  p.IsActive = 'TRUE'

  And ISNULL(SubsidiaryKey, '0') = @lSubSidiaryKey

  AND  TillKey = @pTillKey

  AND     p.PaymentTypeKey = s.PaymentTypeKey

  AND  s.StoreKey = @StoreKey

  -- Get the Count Detail record, get all valid payment types where counting is allowed

  SELECT d.PaymentTypeKey, 'OpeningAmount' = StartingAmount,

    'CountedAmount' = 0.0000,

    'SystemAmount' = StartingAmount,

    'VarianceAmount' = 0.00000 - StartingAmount,

    'DocumentCount' = 0

  FROM TilTillDetail d, PmtPaymentType p, PmtPaymentTypeStore s

  WHERE d.PaymentTypeKey = p.PaymentTypeKey

  AND  p.IsCounted = 'TRUE'

  AND  p.IsDeleted = 'FALSE'

  AND  p.IsActive = 'TRUE'

  And ISNULL(SubsidiaryKey, '0') = @lSubSidiaryKey

  AND  TillKey = @pTillKey

  AND     s.PaymentTypeKey= p.PaymentTypeKey

  AND  s.StoreKey= @StoreKey

END

ELSE IF @lTillStatus = 1 -- ASSIGNED

BEGIN

  SELECT TillKey, 'UserKey' = @pUserKey, 'OpeningAmount' = Sum(d.StartingAmount),

    'CountedAmount' = 0.0000,

    'SystemAmount' = Sum(d.TotalAmount),

    'VarianceAmount' = 0.00000 - Sum(d.TotalAmount),

    'DocumentCount' = 0

  FROM TilSession h, TilSessionDetail d, PmtPaymentType p, PmtPaymentTypeStore s

  WHERE h.TillKey = @pTillKey

  AND  h.TillSessionKey = d.TillSessionKey

  AND  h.Status = 0 -- OPEN TILL TilSessionDetail

  AND  d.PaymentTypeKey = p.PaymentTypeKey

  AND  p.IsCounted = 'TRUE'

  AND  p.IsDeleted = 'FALSE'

  And ISNULL(SubsidiaryKey, '0') = @lSubSidiaryKey

  AND  p.IsActive = 'TRUE'

  AND     s.PaymentTypeKey= p.PaymentTypeKey

  AND  s.StoreKey= @StoreKey

  GROUP BY TillKey

  SELECT d.PaymentTypeKey, 'OpeningAmount' = d.StartingAmount,

    'CountedAmount' = 0.0000,

    'SystemAmount' = d.TotalAmount,

    'VarianceAmount' = 0.00000 - d.TotalAmount,

    'DocumentCount' = 0

  FROM TilSession h, TilSessionDetail d, PmtPaymentType p, PmtPaymentTypeStore s

  WHERE h.TillKey = @pTillKey

  AND  h.TillSessionKey = d.TillSessionKey

  AND  h.Status = 0 -- OPEN TILL TilSessionDetail

  AND  d.PaymentTypeKey = p.PaymentTypeKey

  AND  p.IsCounted = 'TRUE'

  AND  p.IsDeleted = 'FALSE'

  And ISNULL(SubsidiaryKey, '0') = @lSubSidiaryKey

  AND  p.IsActive = 'TRUE'

  AND     s.PaymentTypeKey= p.PaymentTypeKey

  AND  s.StoreKey= @StoreKey

END

ELSE IF @lTillStatus = 2 -- DEASSIGNED

BEGIN

  SELECT @lLastSessionKey = '0'

  SELECT TOP 1 @lLastSessionKey = TillSessionKey

  FROM TilSession

  WHERE TillKey = @pTillKey

  AND  Status = 1 --// CLOSED

  ORDER BY TillSessionKey DESC

  IF @lLastSessionKey = '0'

  BEGIN

  SELECT @lErrorDesc = 'CountSubSystem.NotAbletoGetLastSession'

  GOTO ERRORHANDLER

  END

  SELECT TillKey, 'UserKey' = @pUserKey, 'OpeningAmount' = Sum(d.StartingAmount),

    'CountedAmount' = 0.0000,

    'SystemAmount' = Sum(d.TotalAmount),

    'VarianceAmount' = 0.00000 - Sum(d.TotalAmount),

    'DocumentCount' = 0

  FROM TilSession h, TilSessionDetail d, PmtPaymentType p, PmtPaymentTypeStore s

  WHERE h.TillSessionKey = @lLastSessionKey

  AND  h.TillSessionKey = d.TillSessionKey

  AND  d.PaymentTypeKey = p.PaymentTypeKey

  AND  p.IsCounted = 'TRUE'

  AND  p.IsDeleted = 'FALSE'

  AND  p.IsActive = 'TRUE'

  And ISNULL(SubsidiaryKey, '0') = @lSubSidiaryKey

  AND     s.PaymentTypeKey= p.PaymentTypeKey

  AND  s.StoreKey= @StoreKey

  GROUP BY TillKey

  SELECT d.PaymentTypeKey, 'OpeningAmount' = d.StartingAmount,

    'CountedAmount' = 0.0000,

    'SystemAmount' = d.TotalAmount,

    'VarianceAmount' = 0.00000 - d.TotalAmount,

    'DocumentCount' = 0

  FROM TilSession h, TilSessionDetail d, PmtPaymentType p, PmtPaymentTypeStore s

  WHERE h.TillSessionKey = @lLastSessionKey

  AND  h.TillSessionKey = d.TillSessionKey

  AND  d.PaymentTypeKey = p.PaymentTypeKey

  AND  p.IsCounted = 'TRUE'

  AND  p.IsDeleted = 'FALSE'

  AND  p.IsActive = 'TRUE'

  And ISNULL(SubsidiaryKey, '0') = @lSubSidiaryKey

  AND     s.PaymentTypeKey= p.PaymentTypeKey

  AND  s.StoreKey= @StoreKey

END

ELSE IF @lTillStatus = 3 -- CLOSED

BEGIN

  SELECT @pTillKey, 'UserKey' = @pUserKey, 'OpeningAmount' = Sum(StartingAmount),

    'CountedAmount' = 0.0000,

    'SystemAmount' = Sum(SystemClosingAmount),

    'VarianceAmount' = 0.00000 - Sum(SystemClosingAmount),

    'DocumentCount' = 0

  FROM TilTillDetail d, PmtPaymentType p, PmtPaymentTypeStore s

  WHERE d.PaymentTypeKey = p.PaymentTypeKey

  AND  p.IsCounted = 'TRUE'

  AND  p.IsDeleted = 'FALSE'

  AND  p.IsActive = 'TRUE'

  AND  TillKey = @pTillKey

  And ISNULL(SubsidiaryKey, '0') = @lSubSidiaryKey

  AND     s.PaymentTypeKey= p.PaymentTypeKey

  AND  s.StoreKey= @StoreKey

  -- Get the Count Detail record, get all valid payment types where counting is allowed

  SELECT d.PaymentTypeKey, 'OpeningAmount' = StartingAmount,

    'CountedAmount' = 0.0000,

    'SystemAmount' = SystemClosingAmount,

    'VarianceAmount' = 0.00000 - SystemClosingAmount,

    'DocumentCount' = 0

  FROM TilTillDetail d, PmtPaymentType p, PmtPaymentTypeStore s

  WHERE d.PaymentTypeKey = p.PaymentTypeKey

  AND  p.IsCounted = 'TRUE'

  AND  p.IsDeleted = 'FALSE'

  AND  p.IsActive = 'TRUE'

  AND  TillKey = @pTillKey

  And ISNULL(SubsidiaryKey, '0') = @lSubSidiaryKey

  AND     s.PaymentTypeKey= p.PaymentTypeKey

  AND  s.StoreKey= @StoreKey

END

END

if(@lSubSidiaryKey = '0')

BEGIN

-- GET DENOMINATIONS FOR CASH (BASE CURRENCY)

SELECT CurrencyDenominationKey, c.CurrencyKey, p.PaymentTypeKey, 'Quantity' = 0,

  'DenominationValue' = d.Value, 'Amount' = 0.00000,

  'ExchangeRateKey' = '0', 'BuyRate' = 1.00000

FROM PmtCurrency c, PmtCurrencyDenomination d, PmtPaymentType p, PmtPaymentTypeStore s

WHERE c.IsBaseCurrency = 'TRUE'

AND  c.IsDeleted = 'FALSE'

AND  c.CurrencyKey = d.CurrencyKey

AND  d.IsDeleted = 'FALSE'

AND  p.Type = 0 -- CASH

AND  CountType = 2 -- DETAIL COUNTING REQUIRED

AND  IsActive = 'TRUE'

And ISNULL(SubsidiaryKey, '0') = @lSubSidiaryKey

And  p.IsDeleted = 'FALSE'

AND     s.PaymentTypeKey= p.PaymentTypeKey

AND  s.StoreKey= @StoreKey

END

ELSE

BEGIN

-- GET DENOMINATIONS FOR CASH (SUBSIDIARY BASE CURRENCY)

SELECT CurrencyDenominationKey, c.CurrencyKey, p.PaymentTypeKey, 'Quantity' = 0,

  'DenominationValue' = d.Value, 'Amount' = 0.00000,

  'ExchangeRateKey' = 0, 'BuyRate' = 1.00000

FROM PmtCurrency c, PmtCurrencyDenomination d, PmtPaymentType p, RtlSubsidiary r, PmtPaymentTypeStore s

WHERE --c.IsBaseCurrency = 'TRUE'

 r.LocalCurrencyKey = d.CurrencyKey -- Subsidiary Local (Base) Currency

AND  c.IsDeleted = 'FALSE'

AND  c.CurrencyKey = d.CurrencyKey

AND  d.IsDeleted = 'FALSE'

AND  p.Type = 0 -- CASH

AND  CountType = 2 -- DETAIL COUNTING REQUIRED

AND  IsActive = 'TRUE'

AND ISNULL(p.SubsidiaryKey, '0') = r.SubsidiaryKey

AND ISNULL(p.SubsidiaryKey, '0') = @lSubSidiaryKey

AND  p.IsDeleted = 'FALSE'

AND     s.PaymentTypeKey= p.PaymentTypeKey

AND  s.StoreKey= @StoreKey

END

SELECT i.ProductKey, i.WarehouseKey, i.InventoryItemKey, i.SerialKey, s.SerialNumber

FROM TilInventory i, InvSerial s

WHERE i.SerialKey = s.SerialKey

AND  TillKey = @pTillKey

-- GET DETAILS FOR ALL TENDERS where counting is DETAIL level and Tender is not CASH

SELECT t.PaymentTypeKey, t.TransactionPaymentKey, t.CardNumber, t.Amount, h.TransactionId, t.AccountType

FROM TrxTransactionPayment t, TilSessionTransaction s, PmtPaymentType p, TrxTransaction h

WHERE t.TransactionKey = s.SourceDocumentKey

AND  s.TransactionType = 2 -- TRANSACTION TYPE ONLY

AND  s.TillKey = @pTillKey

AND  t.TenderType <> 0 -- DO NOT INCLUDE CASH

AND  t.PaymentTypeKey = p.PaymentTypeKey

AND  p.IsCounted = 'TRUE'

-- AND  p.CountType = 2  -- DETAIL LEVEL COUNTING

AND  t.TransactionKey = h.TransactionKey

AND  t.IsVoided = 'FALSE'

And ISNULL(p.SubsidiaryKey, '0') = @lSubSidiaryKey

And  P.IsDeleted = 'FALSE'

ORDER By p.PaymentTypeKey, TransactionId

IF @lSubSidiaryCurrencyKey = '0'

BEGIN

  -- GET DENOMINATIONS FOR FC

  SELECT CurrencyDenominationKey, c.CurrencyKey, PaymentTypeKey, 'Quantity' = 0,

    'DenominationValue' = d.Value, 'Amount' = 0.00000,

    ExchangeRateKey, BuyRate

  FROM PmtCurrency c, PmtCurrencyDenomination d, PmtPaymentType p, RtlStore s, PmtExchangeRate e

  WHERE c.IsBaseCurrency = 'FALSE'

  AND  c.IsDeleted = 'FALSE'

  AND  c.CurrencyKey = d.CurrencyKey

  AND  d.IsDeleted = 'FALSE'

  AND  p.Type = 7 -- FOREIGNCURRENCY

  AND  CountType = 2 -- DETAIL COUNTING REQUIRED

  AND  IsActive = 'TRUE'

  AND  s.SiteID = @pSiteId

  And  c.CurrencyKey = e.CurrencyKey

  And ISNULL(p.SubsidiaryKey,'0') = @lSubSidiaryKey

  AND Convert(char(8), e.EffectiveDate, 112) = Convert(char(8), s.BusinessDate, 112)

  And  P.IsDeleted = 'FALSE'

  ORDER BY c.CurrencyKey, d.CurrencyDenominationKey

END

if (@lSubSidiaryCurrencyKey != '0')

BEGIN

-- GET DENOMINATIONS FOR FC

SELECT CurrencyDenominationKey, c.CurrencyKey, PaymentTypeKey, 'Quantity' = 0, c.id,

  'DenominationValue' = d.Value, 'Amount' = 0.00000,

  1 ExchangeRateKey --, --BuyRate

   , dbo.GetExchangeRate( C.CurrencyKey, @lSubSidiaryCurrencyKey ,s.BusinessDate) As BuyRate

FROM PmtCurrency c, PmtCurrencyDenomination d, PmtPaymentType p, RtlStore s --, PmtExchangeRate e

WHERE c.IsBaseCurrency = CASE WHEN @lSubSidiaryKey = '0' THEN 'FALSE' ELSE c.IsBaseCurrency END

AND  C.CurrencyKey IN (SELECT CurrencyKey FROM PmtCurrency Where CurrencyKey != @lSubSidiaryCurrencyKey)

AND  c.IsDeleted = 'FALSE'

AND  c.CurrencyKey = d.CurrencyKey

AND  d.IsDeleted = 'FALSE'

AND  p.Type = 7 -- FOREIGNCURRENCY

AND  CountType = 2 -- DETAIL COUNTING REQUIRED

AND  IsActive = 'TRUE'

AND  s.SiteID = @pSiteId

And ISNULL(p.SubsidiaryKey, '0') = @lSubSidiaryKey

And  P.IsDeleted = 'FALSE'

ORDER BY c.CurrencyKey, d.CurrencyDenominationKey

END

RETURN

ERRORHANDLER:

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

RETURN

END