iVend Database Database : UseDrawer Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@pPOSKey

In

Reference key of the POS

VarWChar

50

@pUserKey

In

Reference key of the Employee

VarWChar

50

@pModifiedBy

In

Reference key of the User

VarWChar

50

@pCashDrawerId

In

Drawer selected by the user

Integer

4

@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

LbrUser

Table

Defines a list of all employees/ labor users created 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

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

CREATE PROCEDURE [dbo].[UseDrawer]

(

@pPOSKey   NVARCHAR(50),

@pUserKey   NVARCHAR(50),

@pModifiedBy NVARCHAR(50),

@pCashDrawerId INT

)

AS

BEGIN

SET NOCOUNT ON

DECLARE @ErrorDesc VARCHAR(255),

  @lSiteId INT,

  @pTillKey NVARCHAR(50),

  @lPOSSiteId INT

Select @pTillKey = TillKey

From TilSession

Where POSKey = @pPOSKey

AND  Status = 0 -- OPEN

AND  CashDrawerId = @pCashDrawerId

IF @@ROWCOUNT = 0

BEGIN

SET @ErrorDesc = 'TillFactory.NoTillinDrawer'

      GOTO ERRORHANDLER

END

-- Check if user is already using that Drawer

If EXISTS ( SELECT 1

  FROM TilSessionUser u, TilSession s

  WHERE u.TillSessionKey = s.TillSessionKey

  --AND  u.TillKey = @pTillKey

  AND  u.UserKey = @pUserKey

  AND  u.POSKey = @pPOSKey

  AND  s.CashDrawerId = @pCashDrawerId

  AND  u.Status = 0 -- OPEN

    )

BEGIN

Return

END

-- Check if drawer has a till or not

IF NOT EXISTS ( SELECT 1

    FROM TilSession

    WHERE Status = 0   --// OPEN

    AND  TillKey = @pTillKey

    AND  POSKey = @pPOSKey

    AND  CashDrawerId = @pCashDrawerId )

BEGIN

SET @ErrorDesc = 'TillFactory.NoTillinDrawer'

      GOTO ERRORHANDLER

END

IF NOT EXISTS ( SELECT 1

    FROM TilTill

    WHERE TillKey = @pTillKey

    AND  Mode = 0   -- USER TILL

    AND  SourceKey = @pUserKey

    UNION

    SELECT 1

    FROM TilTill t, LbrUser u

    WHERE u.IsTeamBanking = 'TRUE'

    AND  Mode = 1   -- TEAM TILL

    AND  TillKey = @pTillKey

    AND  u.UserKey = @pUserKey

    AND  t.SourceKey = u.TeamKey

      )

BEGIN

SET @ErrorDesc = 'TillFactory.NotAllowedtoUseTill'

      GOTO ERRORHANDLER

END

-- Get Site Id

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

FROM CfgSiteInformation

IF @@ROWCOUNT = 0

BEGIN

SET @ErrorDesc = 'TillFactory.SiteNotFound'

      GOTO ERRORHANDLER

END

-- If the User is already using another Drawer on this POS, close other session

UPDATE  TilSessionUser

SET  Status = 1, -- CLOSED

  EndDate = dbo.GetCompanyDateTime(),

  Modified = dbo.GetCompanyDateTime(),

  ModifiedBy = @pModifiedBy

WHERE POSKey = @pPOSKey

AND  UserKey = @pUserKey

--AND  TillKey = @pTillKey

IF(@@ERROR <>0)

  BEGIN

SET @ErrorDesc = 'TillFactory.UpdateTilSessionUserError'

      GOTO ERRORHANDLER

END

-- Insert into TilSessionUser table

Insert Into TilSessionUser(

  [TillUserSessionKey] ,[TillSessionKey] ,[TillKey] ,[POSKey] ,[UserKey] ,[StartDate] ,[EndDate],

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

SELECT NEWID(), TillSessionKey, @pTillKey, @pPOSKey, @pUserKey, dbo.GetCompanyDateTime(), NULL,

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

FROM TilSession

WHERE Status = 0   --// OPEN

AND  TillKey = @pTillKey

AND  POSKey = @pPOSKey

AND  CashDrawerId = @pCashDrawerId

IF(@@ERROR <>0)

  BEGIN

SET @ErrorDesc = 'TillFactory.TilSessionUserInsertError'

      GOTO ERRORHANDLER

END

RETURN

ERRORHANDLER:

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

RETURN

END