iVend Database Database : BarCodeResolution Stored Procedure

Creation Date

6/11/2019 7:24 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@pCode

In

Code which needs to be resolved

VarWChar

100

@pCustomerKey

In

Reference key of the CustomerReference key of the Customer

VarWChar

50

@pWarehouseKey

In

VarWChar

50

@pContext

Out

Returns the Context in which the code was parsed

VarWChar

50

@pReturnKey

Out

If bar code resolved successfully, Reference key of the object parsed

VarWChar

50

@pMultipleFound

Out

Whether multiple objects were found for the code passed in

Boolean

1

@pUPCQuantity

Out

Specifies the quantity of the Alternate codeSpecifies the quantity of the Alternate codeSpecifies the quantity of the Alternate codeSpecifies the quantity of the Alternate code

Numeric

9

@pPrice

Out

Numeric

9

@pUOMKey

Out

VarWChar

50

@pSerialKey

Out

VarWChar

50

@pBatchKey

Out

VarWChar

50

@pRetailProfileKey

In

VarWChar

50

@pPriceCheck

In

Specifies is PriceCheck

Boolean

1

@pProcessType

In

VarWChar

10

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

ArrAccountsReceivable

Table

Defines the on Credit details of all Customers

1

CfgBarCodeMask

Table

Store the barcode mask.

1

CfgBarCodeMaskDetail

Table

Detail tables for storing the barcode mask information.

1

CfgEnterprise

Table

Stores the basic configuration entries for the system . These settings are mandatory for the system to work properly.

1

CfgSiteInformation

Table

Stores the site information. Enterprise always has the site id as 1. System Table Warning : Should not be modified directly

1

CusCustomer

Table

Defines the customer details that interact with the enterprise.

1

CusCustomerBranch

Table

Stores the customer branch information

1

CusCustomerCatalog

Table

Header table to store Customer catalog details defined for a customer

1

CusCustomerCatalogDetail

Table

Detail table to store Customer catalog details defined for a customer. Stores the customer catalog number against the products

1

InvBatch

Table

Batch details gets affected when doing any transaction with item which has been batch tracked.

1

InvInventoryItem

Table

Stores inventory details for each Product for each Warehouse

1

InvProduct

Table

Stores the product related details.

1

InvSerial

Table

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

1

InvUPCTranslation

Table

Specified multiple UPC Codes or Bar codes for a product.

1

InvWarehouse

Table

Defines a list of all warehouses defined in the system.

1

LbrUser

Table

Defines a list of all employees/ labor users created in the system

1

PmtGiftCertificate

Table

Used to store the values for the Gift Certificate issued

1

ProCoupon

Table

Stores the issued coupon details for iVend

1

ProCouponCondition

Table

Stores the conditions for a Coupon to apply

1

RtlRetailProfile

Table

Contains different profiles defined in the system. Profiles contains information which is defines the behaviour of the POS and Console.

1

RtlStore

Table

Contains the details of Stores defined in the system.

1

TrxScannedDataDetail

Table

1

TrxScannedDataHeader

Table

1

TrxTransaction

Table

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

1

CREATE PROCEDURE [dbo].[BarCodeResolution]

(

    @pCode   NVARCHAR(100),

    @pCustomerKey   nvarchar(50),

  @pWarehouseKey nvarchar(50),

    @pContext   NVARCHAR(50) OUTPUT,

    @pReturnKey   nvarchar(50)   OUTPUT,

    @pMultipleFound BIT    OUTPUT,

    @pUPCQuantity   NUMERIC(20, 5) OUTPUT,

    @pPrice    NUMERIC(20, 5) OUTPUT,

    @pUOMKey   nvarchar(50)   OUTPUT,

    @pSerialKey   nvarchar(50)   OUTPUT,

    @pBatchKey   nvarchar(50)   OUTPUT,

  @pRetailProfileKey nvarchar(50) = '0',

    @pPriceCheck   BIT ,

  @pProcessType   NVARCHAR(10)

)

AS

BEGIN

SET NOCOUNT ON;

set @pSerialKey = '0';

set @pBatchKey = '0';

/***** BAR CODE RESOLUTION ORDER USED

 1) Primary UPC Code

 2) Alternate UPC Code

 3) Barcode Masking Resolution

 3) Product Id

 4) Serial Number

 5) Batch Number

 6) Customer Catalog

 7) Customer Id

 8) Sales Person Id

 9) Product Description

 10) Customer First Name

 11) Customer Last Name

 12) Transaction Id

 13) Transaction Key

 14) Suspended Transaction Key

 *****/

SELECT @pContext = '',

  @pMultipleFound = 0,

  @pReturnKey = '0',

  @pUPCQuantity = 0 ,

  @pUOMKey = '0'

DECLARE @lBarcodeString   NVARCHAR(100),

  @Index     INT,

  @lRowCount   INT,

  @lCashCustomerKey nvarchar(50),

  @lWareHouseKey   nvarchar(50),

  @lQuantity    NUMERIC(20, 5),

  @lProfileData   XML,

  @lAllowCouponScan BIT,

  @lAllowProductScan BIT,

  @lAllowCustomerScan BIT,

  @lAllowTransactionScan BIT,

  @lAllowSalesPersonScan BIT,

  @lBranchCode   NVARCHAR(50),

  @lBranchKey   nvarchar(50),

  @lIsSubsidiaryEnabled BIT,

  @lSubsidaryKey   nvarchar(50),

  @lBarCodeMaskExists INT

SET @lAllowCouponScan = 'TRUE'

SET @lAllowProductScan = 'TRUE'

SET @lAllowCustomerScan = 'TRUE'

SET @lAllowTransactionScan = 'TRUE'

SET @lAllowSalesPersonScan = 'TRUE'

SET @lIsSubsidiaryEnabled = 'FALSE'

SELECT @lWareHouseKey = A.WareHouseKey, @lCashCustomerKey = CashCustomerKey, @lBranchCode = B.BranchCode

FROM RtlStore A With(nolock), InvWarehouse B With(nolock)

WHERE A.SiteId = (SELECT SiteId FROM CfgSiteInformation With(nolock))

And A.WarehouseKey = B.WarehouseKey

SET @lBranchKey = (CASE WHEN @lBranchCode = '' OR @lBranchCode='SBO' THEN '0' ELSE CAST(@lBranchCode AS nvarchar(50)) END)

IF (@lBranchKey IS NULL )

SET @lBranchKey = '0'

Select @lProfileData = ProfileData From RtlRetailProfile With(nolock) Where RetailProfileKey = @pRetailProfileKey

Select @lIsSubsidiaryEnabled = IsMultipleSubsidiaryEnabled From CfgEnterprise With(nolock) Where RetailProfileKey = @pRetailProfileKey

Select @lSubsidaryKey = ISNULL(SubsidiaryKey, '0') From InvWarehouse With(nolock) Where WarehouseKey= @pWarehouseKey

IF @lProfileData IS NOT NULL

BEGIN

SELECT @lAllowCouponScan = ISNULL(a.b.value('AllowCouponScan[1]','BIT'), 'TRUE') FROM @lProfileData.nodes('RetailProfile') a(b)

SELECT @lAllowProductScan = ISNULL(a.b.value('AllowProductScan[1]','BIT'), 'TRUE') FROM @lProfileData.nodes('RetailProfile') a(b)

SELECT @lAllowCustomerScan = ISNULL(a.b.value('AllowCustomerScan[1]','BIT'), 'TRUE') FROM @lProfileData.nodes('RetailProfile') a(b)

SELECT @lAllowTransactionScan = ISNULL(a.b.value('AllowTransactionScan[1]','BIT'), 'TRUE') FROM @lProfileData.nodes('RetailProfile') a(b)

SELECT @lAllowSalesPersonScan = ISNULL(a.b.value('AllowSalesPersonScan[1]','BIT'), 'TRUE') FROM @lProfileData.nodes('RetailProfile') a(b)

END

SET @lBarcodeString = @pCode

Select @Index = CharIndex('%', @lBarcodeString)

Select @lBarCodeMaskExists = Count(1) FROM CfgBarCodeMask With(nolock) Where StartSentinel in ('20','02') And IsDeleted = 0

if @lBarCodeMaskExists = 0

BEGIN

IF (@Index >1)

BEGIN

  SELECT @pCode = SUBSTRING(@lBarcodeString,1,@Index - 1)

  IF ISNUMERIC(SUBSTRING(@lBarcodeString,@Index + 1,10)) = 1

  BEGIN

  SELECT @pUPCQuantity = SUBSTRING(@lBarcodeString,@Index + 1,10)

  SELECT @pUPCQuantity = @pUPCQuantity/1000

  END

END

ELSE IF LEN(@lBarcodeString) = 12 AND LEFT(@lBarcodeString, 1)= '2' AND ISNUMERIC(@lBarcodeString) = 1

BEGIN

  select @pCode = SUBSTRING(@lBarcodeString,1, 6)

  SELECT @pUPCQuantity = SUBSTRING(@lBarcodeString,7,5)

  SELECT @pUPCQuantity = @pUPCQuantity/1000

END

ELSE IF LEN(@lBarcodeString) = 13 AND LEFT(@lBarcodeString, 2)= '02' AND ISNUMERIC(@lBarcodeString) = 1

BEGIN

  SELECT @pCode = SUBSTRING(@lBarcodeString,2, 6)

  SELECT @pUPCQuantity = SUBSTRING(@lBarcodeString,8,5)

  SELECT @pUPCQuantity = @pUPCQuantity/1000

END

-- UPCCode Lookup

IF @lAllowProductScan = 'TRUE'

BEGIN

SELECT @pContext = 'BARCODE'

SELECT @pReturnKey = A.ProductKey

FROM InvProduct A With(nolock), InvInventoryItem B With(nolock)

WHERE UPC = @pCode

AND IsDeleted = 'False'

AND A.ProductKey = B.ProductKey

And B.WarehouseKey = @pWarehouseKey

AND ((@pPriceCheck = 0) OR (@pPriceCheck = 1 AND A.IsGiftCertificate = 0))

SELECT @lRowCount = @@RowCount

IF @lRowCount = 1

BEGIN

  RETURN

END

ELSE

  BEGIN

  IF @lRowCount > 1

    BEGIN

    SELECT @pMultipleFound = 1

  RETURN

  END

END

----------------------------------------------------------------------------------------------------------------------

-- UPC Translation

IF object_id('tempdb..#temUPCTransaction') IS NOT NULL

DROP TABLE #temUPCTransaction

SELECT @pContext = 'UPCTRANSLATION'

select  A.ProductKey, A.UPCTranslationQuantity, A.UOMKey, B.PurchaseUOMKey, B.SaleUOMKey, B.InventoryUOMKey, A.UPC INTO #temUPCTransaction

FROM    InvUPCTranslation A With(nolock)

INNER JOIN InvProduct B With(nolock) ON A.ProductKey = B.ProductKey

WHERE   A.UPC = @pCode AND ISNULL(A.IsDeleted, 0) = 0

AND ((@pPriceCheck = 0) OR (@pPriceCheck = 1 AND B.IsGiftCertificate = 0))

  SELECT @lRowCount= COUNT(*) FROM #temUPCTransaction ;

  IF (@lRowCount = 1)

  BEGIN

  SELECT @pReturnKey = ProductKey, @pUPCQuantity = UPCTranslationQuantity, @pUOMKey = UOMKey FROM    #temUPCTransaction

  RETURN

  END

ELSE

BEGIN

  IF @lRowCount > 1

  BEGIN

  SELECT @pMultipleFound = 1

  DROP TABLE #temUPCTransaction

  RETURN

  --DECLARE @DistinctId INT

  --SELECT @DistinctId= COUNT (DISTINCT ProductKey) FROM #temUPCTransaction

  --IF (@DistinctId > 1)

  --BEGIN

  -- SELECT  @pMultipleFound = 1

  -- RETURN

  --END

  --ELSE

  --BEGIN

  -- SELECT  @pMultipleFound = 1

  -- RETURN

  -- ----logic to get the uom key and quantity in case of same upc code with diffrent process

  -- --SELECT  @pReturnKey = ProductKey, @pUPCQuantity = UPCTranslationQuantity, @pUOMKey = UOMKey

  -- --FROM  #temUPCTransaction where UOMKey = CASE

  -- -- WHEN @pProcessType = 'SALE' THEN SaleUOMKey WHEN @pProcessType='PURCHASE' THEN  PurchaseUOMKey ELSE InventoryUOMKey END

  -- --RETURN

  -- END

  END

  END

  DROP TABLE #temUPCTransaction

-----------------------------------------------------------------------------------------------------------------------

END

END

SET @pCode = @lBarcodeString

SELECT @pContext = '',

  @pMultipleFound = 0,

  @pReturnKey = '0',

  @pUPCQuantity = 0 ,

  @pUOMKey = '0'

IF @lAllowProductScan = 'TRUE'

BEGIN

IF LEN(@pCode) > 2

BEGIN

  DECLARE @lStartSentinel   NVARCHAR(2),

    @lBarCodeMaskKey nvarchar(50),

    @lBarcodePartLength INT,

    @lItemCodePartLength INT,

    @lCurrentPosition INT,

    @lValueType   INT,

    @lLength   INT,

    @lPrimaryValueType INT,

    @lFactor    NUMERIC(20,5),

    @lSerialKey   nvarchar(50),

    @lBatchKey   nvarchar(50),

    @lMaskType   INT,

    @lMaskOrder   INT,

    @lIsBatchTracked BIT,

    @lIsSerialTracked BIT,

    @ldelimeter   NVARCHAR(1)

  SELECT @lStartSentinel = LEFT(@pCode, 2)

  SELECT @lBarCodeMaskKey = BarCodeMaskKey, @lMaskType = MaskType ,

    @ldelimeter = Delimiter --0 as delimiter and 1 as fixed length

  FROM CfgBarCodeMask With(nolock)

  Where StartSentinel = @lStartSentinel

  AND LEN(@pCode) >= Length + 2

  AND IsDeleted = 0

  IF @lMaskType = 1

  BEGIN

  SET @lCurrentPosition = 2

  set @pcode = @lBarcodeString

  SELECT @lMaskOrder = MIN(MaskOrder)

  FROM CfgBarCodeMaskDetail   WITH(NOLOCK)

  Where BarCodeMaskKey = @lBarCodeMaskKey

  And  ValueType IN (0,6,8)

  AND IsDeleted = 0

  if exists (SELECT 1 FROM CfgBarCodeMaskDetail With(nolock) Where BarCodeMaskKey = @lBarCodeMaskKey And MaskOrder < @lMaskOrder AND IsDeleted = 0)

  Begin

    SELECT @lCurrentPosition = @lCurrentPosition + SUM(Length)

    FROM CfgBarCodeMaskDetail WITH(NOLOCK)

    Where BarCodeMaskKey = @lBarCodeMaskKey

    And MaskOrder < @lMaskOrder

    AND IsDeleted = 0

  End

  SELECT @lPrimaryValueType = ValueType,   @lBarcodePartLength = Length

  FROM CfgBarCodeMaskDetail   WITH(NOLOCK)

  Where BarCodeMaskKey = @lBarCodeMaskKey

  And MaskOrder = @lMaskOrder

  AND IsDeleted = 0

  SET @lCurrentPosition = @lCurrentPosition + 1

  IF @lPrimaryValueType = 0 --ProductId = 0

  BEGIN

    SELECT DISTINCT TOP 1

    @pReturnKey = InvProduct.ProductKey,

    @lIsBatchTracked = InvProduct.IsBatchTracked,

    @lIsSerialTracked = InvProduct.IsSerialTracked

    FROM InvProduct With(nolock)

    WHERE InvProduct.Id = SUBSTRING(@pCode, @lCurrentPosition, @lBarcodePartLength)

    AND BarCodeMaskKey = @lBarCodeMaskKey

    AND ((@pPriceCheck = 0) OR (@pPriceCheck = 1 AND IsGiftCertificate = 0))

    SELECT @lRowCount = @@RowCount

  END

  ELSE IF @lPrimaryValueType = 6 --ProductBarcode = 6

  BEGIN

    SELECT DISTINCT TOP 1 @pReturnKey = InvProduct.ProductKey,

      @lIsBatchTracked = InvProduct.IsBatchTracked,

      @lIsSerialTracked = InvProduct.IsSerialTracked

    FROM InvProduct With(nolock)

    WHERE InvProduct.UPC = SUBSTRING(@pCode, @lCurrentPosition, @lBarcodePartLength)

    AND BarCodeMaskKey = @lBarCodeMaskKey

    AND ((@pPriceCheck = 0) OR (@pPriceCheck = 1 AND IsGiftCertificate = 0))

    SELECT @lRowCount = @@RowCount

  END

  ELSE IF @lPrimaryValueType = 8 --UOMBarCode = 8

  BEGIN

    SELECT DISTINCT TOP 1 @pReturnKey = InvProduct.ProductKey,

    @pUOMKey = ISNULL(InvUPCTranslation.UOMKey, 0),

    @lIsBatchTracked = InvProduct.IsBatchTracked,

    @lIsSerialTracked = InvProduct.IsSerialTracked

    FROM InvProduct With(nolock) INNER JOIN InvUPCTranslation With(nolock) ON InvProduct.ProductKey = InvUPCTranslation.ProductKey

    AND  InvUPCTranslation.UPC = SUBSTRING(@pCode, @lCurrentPosition, @lBarcodePartLength) AND ISNULL(InvUPCTranslation.IsDeleted, 0) = 0

    AND BarCodeMaskKey = @lBarCodeMaskKey

    AND ((@pPriceCheck = 0) OR (@pPriceCheck = 1 AND InvProduct.IsGiftCertificate = 0))

    SELECT @lRowCount = @@RowCount

  END

  IF @lRowCount > 0

  BEGIN

    SELECT @pContext = 'BARCODE'

    SET @lCurrentPosition = @lCurrentPosition + @lBarcodePartLength

    --GET ALL THE COMPONENTS OF THIS BARCODE MASK AND THEN EXTRAXT THE INFORMATION BASED ON THE NUMBER OF CHARS

    DECLARE ProductListCursor CURSOR FOR

    SELECT ValueType, Length, Factor FROM CfgBarCodeMaskDetail With(nolock)

    WHERE  BarCodeMaskKey = @lBarCodeMaskKey

    AND   MaskOrder > @lMaskOrder

    AND IsDeleted = 0 order by MaskOrder

    OPEN ProductListCursor

    FETCH NEXT FROM  ProductListCursor INTO @lValueType, @lLength, @lFactor

    WHILE @@FETCH_STATUS=0

    BEGIN

    IF @lValueType = 7 and @lIsSerialTracked = 'TRUE' --INDICATES THAT IT IS BATCH OR SERIAL

    BEGIN

      SELECT @pSerialKey = SerialKey

      FROM InvSerial With(nolock)

      WHERE ProductKey = @pReturnKey

      AND  SerialNumber = SUBSTRING(@pCode, @lCurrentPosition, @lLength)

      AND IsDeleted = 0

      AND WarehouseKey = @lWareHouseKey

      IF ISNULL(@pSerialKey, '0')= '0'

      BEGIN

      CLOSE ProductListCursor

      DEALLOCATE ProductListCursor

      RETURN

      END

      SET @lCurrentPosition = @lCurrentPosition + @lLength

    END

    ELSE IF @lValueType = 7 and @lIsBatchTracked = 'TRUE' --INDICATES THAT IT IS BATCH OR SERIAL

    BEGIN

      SELECT @pBatchKey = BatchKey

      FROM InvBatch WITH(NOLOCK) where ProductKey = @pReturnKey

      AND  BatchNumber = SUBSTRING(@pCode, @lCurrentPosition , @lLength)

      AND IsDeleted = 0

      AND WarehouseKey = @lWareHouseKey

      IF ISNULL(@pBatchKey, '0')= '0'

      BEGIN

      CLOSE ProductListCursor

      DEALLOCATE ProductListCursor

      RETURN

      END

      SET @lCurrentPosition = @lCurrentPosition + @lLength

    END

    ELSE IF @lValueType=3

    BEGIN

      SET @lCurrentPosition = @lCurrentPosition + @lLength

    END

    ELSE IF @lValueType!=7

    BEGIN

      Declare @ftempValue nvarchar(50)

      set @ftempValue = SUBSTRING(@pCode, @lCurrentPosition, @lLength)

      SELECT @pUPCQuantity = CASE WHEN @lValueType = 2 and ISNUMERIC(@ftempValue)> 0 THEN CONVERT(NUMERIC(20,5),@ftempValue)/@lFactor ELSE @pUPCQuantity END,

      @pPrice = CASE WHEN @lValueType = 1 and ISNUMERIC(@ftempValue)> 0 THEN CONVERT(NUMERIC(20,5), @ftempValue)/@lFactor ELSE @pPrice END

      SET @lCurrentPosition = @lCurrentPosition + @lLength

    End

    FETCH NEXT FROM ProductListCursor INTO @lValueType, @lLength, @lFactor

    END

    CLOSE ProductListCursor

    DEALLOCATE ProductListCursor

    IF @pReturnKey != '0'

    BEGIN

    RETURN

    END

    ELSE

    BEGIN

    IF @lRowCount > 1

    BEGIN

      SELECT @pMultipleFound = 1

      RETURN

    END

    END

  END

  END

  Else -- IF Mask Type = 0 , ---- CASE DELIMETER  ---------

  BEGIN

  set @pcode = @lBarcodeString

  CREATE TABLE  #tmpValuetypes(RowNum int identity(1,1),Items nvarchar(200))

  INSERT into #tmpValuetypes(Items) SELECT * from dbo.FunSplit( SUBSTRING(@pcode,3,Len(@pcode)-2),@ldelimeter)

  SELECT @lMaskOrder = MIN(MaskOrder)

  FROM CfgBarCodeMaskDetail WITH(NOLOCK)

  Where BarCodeMaskKey = @lBarCodeMaskKey

  And  ValueType IN (0,6,8)

  AND IsDeleted = 0

  SELECT @lPrimaryValueType = ValueType

  FROM CfgBarCodeMaskDetail WITH(NOLOCK)

  Where BarCodeMaskKey = @lBarCodeMaskKey

  AND MaskOrder = @lMaskOrder

  AND IsDeleted = 0

  DECLARE @count int

  SET @count = @lMaskOrder --+ 1

  IF @lPrimaryValueType = 0 --ProductId = 0

  BEGIN

    SELECT DISTINCT TOP 1

    @pReturnKey = InvProduct.ProductKey,

    @lIsBatchTracked = InvProduct.IsBatchTracked,

    @lIsSerialTracked = InvProduct.IsSerialTracked

    FROM InvProduct WITH(NOLOCK)

    WHERE InvProduct.Id = (select items from  #tmpValuetypes t where t.rownum = @count) -- @lmaskorder + 1

    AND BarCodeMaskKey = @lBarCodeMaskKey

    AND ((@pPriceCheck = 0) OR (@pPriceCheck = 1 AND IsGiftCertificate = 0))

    SELECT @lRowCount = @@RowCount

  END

  ELSE IF @lPrimaryValueType = 6 --ProductBarcode = 6

  BEGIN

    SELECT DISTINCT TOP 1 @pReturnKey = InvProduct.ProductKey,

    @lIsBatchTracked = InvProduct.IsBatchTracked,

    @lIsSerialTracked = InvProduct.IsSerialTracked

    FROM InvProduct WITH(NOLOCK)

    WHERE InvProduct.UPC = (select items from  #tmpValuetypes t where t.rownum = @count)

    AND BarCodeMaskKey = @lBarCodeMaskKey

    AND ((@pPriceCheck = 0) OR (@pPriceCheck = 1 AND IsGiftCertificate = 0))

    SELECT @lRowCount = @@RowCount

  END

  ELSE IF @lPrimaryValueType = 8 --UOMBarCode = 8

  BEGIN

    SELECT DISTINCT TOP 1 @pReturnKey = InvProduct.ProductKey,

    @pUOMKey = ISNULL(InvUPCTranslation.UOMKey, 0),

    @lIsBatchTracked = InvProduct.IsBatchTracked,

    @lIsSerialTracked = InvProduct.IsSerialTracked

    FROM InvProduct WITH(NOLOCK) INNER JOIN InvUPCTranslation WITH(NOLOCK) ON InvProduct.ProductKey = InvUPCTranslation.ProductKey

    AND  InvUPCTranslation.UPC = (select items from  #tmpValuetypes t where t.rownum = @count) AND ISNULL(InvUPCTranslation.IsDeleted, 0) = 0

    AND BarCodeMaskKey = @lBarCodeMaskKey

    AND ((@pPriceCheck = 0) OR (@pPriceCheck = 1 AND InvProduct.IsGiftCertificate = 0))

    SELECT @lRowCount = @@RowCount

  END

  IF @lRowCount > 0

  BEGIN

    SELECT @pContext = 'BARCODE'

    --GET ALL THE COMPONENTS OF THIS BARCODE MASK AND THEN EXTRAXT THE INFORMATION BASED ON THE NUMBER OF CHARS

    DECLARE ProductListCursor CURSOR FOR

    SELECT ValueType,Factor FROM CfgBarCodeMaskDetail WITH(NOLOCK)

    WHERE  BarCodeMaskKey = @lBarCodeMaskKey

    AND   MaskOrder > @lMaskOrder

    AND IsDeleted = 0 order by MaskOrder

    OPEN ProductListCursor

    FETCH NEXT FROM  ProductListCursor INTO @lValueType,@lFactor

    WHILE @@FETCH_STATUS=0

    BEGIN

    set @count =@count + 1

    IF @lValueType = 7 and @lIsSerialTracked = 'TRUE' --INDICATES THAT IT IS BATCH OR SERIAL

    BEGIN

      SELECT @pSerialKey = SerialKey

      FROM InvSerial where ProductKey = @pReturnKey

      AND  SerialNumber = (select items from  #tmpValuetypes t where t.rownum = @count)

      AND IsDeleted = 0

      AND WarehouseKey = @lWareHouseKey

      IF ISNULL(@pSerialKey, '0')= '0'

      BEGIN

      CLOSE ProductListCursor

      DEALLOCATE ProductListCursor

      RETURN

      END

    END

    ELSE IF @lValueType = 7 and @lIsBatchTracked = 'TRUE' --INDICATES THAT IT IS BATCH OR SERIAL

    BEGIN

      SELECT @pBatchKey = BatchKey

      FROM InvBatch WITH(NOLOCK) where ProductKey = @pReturnKey

      AND  BatchNumber = (select items from  #tmpValuetypes t where t.rownum = @count)--SUBSTRING(@pCode, @lCurrentPosition , @lLength)

      AND IsDeleted = 0

      AND WarehouseKey = @lWareHouseKey

      IF ISNULL(@pBatchKey, '0')= '0'

      BEGIN

      CLOSE ProductListCursor

      DEALLOCATE ProductListCursor

      RETURN

      END

    END

    ELSE IF @lValueType!=7

    BEGIN

      Declare @dtempValue nvarchar(50)

      select @dtempValue = items from  #tmpValuetypes t where t.rownum = @count

      SELECT @pUPCQuantity = CASE WHEN @lValueType = 2 and ISNUMERIC(@dtempValue)> 0 THEN CONVERT(NUMERIC(20,5),@dtempValue)/@lFactor ELSE @pUPCQuantity END, -- SUBSTRING(@pCode, @lCurrentPosition, @lLength))/@lFactor ELSE @pUPCQuantity

      @pPrice = CASE WHEN @lValueType = 1 and ISNUMERIC(@dtempValue)> 0 THEN CONVERT(NUMERIC(20,5), @dtempValue)/@lFactor ELSE @pPrice END

    END

    FETCH NEXT FROM ProductListCursor INTO @lValueType,@lFactor

    END

    CLOSE ProductListCursor

    DEALLOCATE ProductListCursor

    IF @pReturnKey != '0'

    BEGIN

    RETURN

    END

    ELSE

    BEGIN

    IF @lRowCount > 1

    BEGIN

      SELECT @pMultipleFound = 1

      RETURN

    END

    END

  END

  DROP TABLE #tmpValuetypes

  END--OF BARCODE MASKING BLOCK

END

END

IF @lAllowProductScan = 'TRUE'

BEGIN

--------------------------------------------------------------------------------------------------

-- UPC Translation

SELECT @pContext = 'UPCTRANSLATION'

IF object_id('tempdb..#tmpUPCTransaction') IS NOT NULL

DROP TABLE #tmpUPCTransaction;

select  InvUPCTranslation.ProductKey, UPCTranslationQuantity,UOMKey,PurchaseUOMKey,SaleUOMKey,InventoryUOMKey,InvUPCTranslation.UPC into #tmpUPCTransaction

FROM    InvUPCTranslation WITH(NOLOCK)

INNER JOIN InvProduct ON InvUPCTranslation.ProductKey = InvProduct.ProductKey

WHERE   InvUPCTranslation.UPC = @pCode AND ISNULL(InvUPCTranslation.IsDeleted, 0) = 0

AND ((@pPriceCheck = 0) OR (@pPriceCheck = 1 AND InvProduct.IsGiftCertificate = 0))

  SELECT @lRowCount= COUNT(*) FROM #tmpUPCTransaction ;

  IF (@lRowCount = 1)

  BEGIN

  SELECT @pReturnKey = ProductKey, @pUPCQuantity = UPCTranslationQuantity, @pUOMKey = UOMKey FROM    #tmpUPCTransaction

  RETURN

  END

ELSE

BEGIN

  IF @lRowCount > 1

  BEGIN

  DECLARE @DistId INT

  SELECT @DistId= COUNT (DISTINCT ProductKey) FROM #tmpUPCTransaction

  IF (@DistId > 1)

  BEGIN

    SELECT @pMultipleFound = 1

    RETURN

  END

  ELSE

  BEGIN

    --logic to get the uom key and quantity

    SELECT @pReturnKey = ProductKey, @pUPCQuantity = UPCTranslationQuantity, @pUOMKey = UOMKey FROM    #tmpUPCTransaction where UOMKey = CASE

    WHEN @pProcessType = 'SALE' THEN SaleUOMKey WHEN @pProcessType='PURCHASE' THEN  PurchaseUOMKey ELSE InventoryUOMKey END

    RETURN

    END

  END

  DROP TABLE #tmpUPCTransaction

  END

---------------------------------------------------------------------------------------------------------------------------------

-- Barcode

IF @pReturnKey = '0'

BEGIN

  SELECT @pContext = 'BARCODE'

  SELECT @pReturnKey = ProductKey

  FROM    InvProduct WITH(NOLOCK)

  WHERE   UPC = @pCode

  AND  IsDeleted = 'False'

  AND ((@pPriceCheck = 0) OR (@pPriceCheck = 1 AND IsGiftCertificate = 0))

  SELECT @lRowCount = @@RowCount

  IF @lRowCount = 1

  BEGIN

  RETURN

  END

  ELSE

  BEGIN

  IF @lRowCount > 1

  BEGIN

    SELECT @pMultipleFound = 1

    RETURN

  END

  END

END

-- ProductId

SELECT @pContext = 'PRODUCT'

SELECT @pReturnKey = ProductKey

FROM    InvProduct     WITH(NOLOCK)

WHERE   Id = @pCode

AND  IsDeleted = 'False'

AND ((@pPriceCheck = 0) OR (@pPriceCheck = 1 AND IsGiftCertificate = 0))

SELECT @lRowCount = @@RowCount

IF @lRowCount = 1

BEGIN

  RETURN

END

ELSE

BEGIN

  IF @lRowCount > 1

  BEGIN

  SELECT @pMultipleFound = 1

  RETURN

  END

END

-- Serial

SELECT @pContext = 'BARCODE'

SELECT @pReturnKey = A.ProductKey,

@pSerialKey = A.SerialKey

FROM    InvSerial A WITH(NOLOCK), InvProduct B WITH(NOLOCK)

WHERE   A.ProductKey = B.ProductKey

AND A.SerialNumber = @pCode

AND A.IsDeleted = 'False'

And A.Status = 1

AND A.WarehouseKey = @lWareHouseKey

AND ((@pPriceCheck = 0) OR (@pPriceCheck = 1 AND B.IsGiftCertificate = 0))

SELECT @lRowCount = @@RowCount

IF @lRowCount = 1

BEGIN

  RETURN

END

ELSE

BEGIN

  IF @lRowCount > 1

  BEGIN

  SELECT @pContext = 'SERIAL'

  SELECT @pMultipleFound = 1

  RETURN

  END

END

-- Serial   (For refund)

SELECT @pContext = 'BARCODE'

SELECT @pReturnKey = A.ProductKey,

@pSerialKey = A.SerialKey

FROM    InvSerial A WITH(NOLOCK), InvProduct B     WITH(NOLOCK)

WHERE   A.ProductKey = B.ProductKey

AND A.SerialNumber = @pCode

AND A.IsDeleted = 'False'

And A.Status = 2

AND A.WarehouseKey = @lWareHouseKey

AND ((@pPriceCheck = 0) OR (@pPriceCheck = 1 AND B.IsGiftCertificate = 0))

SELECT @lRowCount = @@RowCount

IF @lRowCount = 1

BEGIN

  RETURN

END

ELSE

BEGIN

  IF @lRowCount > 1

  BEGIN

  SELECT @pContext = 'SERIAL'

  SELECT @pMultipleFound = 1

  RETURN

  END

END

--Batch

SELECT @pContext = 'BARCODE'

SELECT @pReturnKey = A.ProductKey,

@pBatchKey = A.BatchKey

FROM    InvBatch A WITH(NOLOCK),InvProduct B WITH(NOLOCK)

WHERE   A.ProductKey = B.ProductKey

AND A.BatchNumber = @pCode

AND A.IsDeleted = 'False'

AND A.WarehouseKey = @lWareHouseKey

AND ((@pPriceCheck = 0) OR (@pPriceCheck = 1 AND B.IsGiftCertificate = 0))

SELECT @lRowCount = @@RowCount

IF @lRowCount = 1

BEGIN

  RETURN

END

ELSE

BEGIN

  IF @lRowCount > 1

  BEGIN

  SELECT @pContext = 'BATCH'

  SELECT @pMultipleFound = 1

  RETURN

  END

END

-- Customer Catalog

IF @pCustomerKey > '0'

BEGIN

  SELECT @pContext = 'CUSTOMERCATALOG'

  SELECT @pReturnKey = ProductKey

  FROM    CusCustomerCatalog h WITH(NOLOCK),CusCustomerCatalogDetail d WITH(NOLOCK)

  WHERE   h.CustomerCatalogKey = d.CustomerCatalogKey

  AND  h.CustomerKey = @pCustomerKey

  AND  PartNumber = @pCode

  AND  d.IsDeleted = 0

  AND (@lBranchKey = '0' OR h.CustomerKey IN (SELECT CustomerKey FROM CusCustomerBranch WHERE BranchKey = @lBranchKey))

  SELECT @lRowCount = @@RowCount

  IF @lRowCount = 1

  BEGIN

  RETURN

  END

  ELSE

  BEGIN

  IF @lRowCount > 1

  BEGIN

    SELECT @pMultipleFound = 1

    RETURN

  END

  END

END

END

-- Customer

IF @lAllowCustomerScan = 'TRUE' AND @pPriceCheck = 0

BEGIN

SELECT @pContext = 'CUSTOMER'

SELECT @pReturnKey = A.CustomerKey

FROM    CusCustomer A WITH(NOLOCK), ArrAccountsReceivable B WITH(NOLOCK)

WHERE   A.CustomerKey = B.CustomerKey

AND  B.SubsidiaryKey = @lSubsidaryKey

AND  Id = @pCode

AND  A.IsDeleted = 'False'

AND  MultiCurrency = 'TRUE'

AND (@lBranchKey = '0' OR A.CustomerKey IN (SELECT CustomerKey FROM CusCustomerBranch WHERE BranchKey = @lBranchKey))

SELECT @lRowCount = @@RowCount

IF @lRowCount = 1

BEGIN

  RETURN

END

ELSE

BEGIN

  IF @lRowCount > 1

  BEGIN

  SELECT @pMultipleFound = 1

  RETURN

  END

END

-- Customer  electronic id

SELECT @pContext = 'CUSTOMER'

SELECT @pReturnKey = A.CustomerKey

FROM    CusCustomer A WITH(NOLOCK), ArrAccountsReceivable B   WITH(NOLOCK)

WHERE   A.CustomerKey = B.CustomerKey

AND  B.SubsidiaryKey = @lSubsidaryKey

AND  ((@lSubsidaryKey != '0' AND B.ElectronicId = @pCode) OR /*Subsidiary Enabled*/

    (@lSubsidaryKey = '0' AND A.ElectronicId = @pCode) /*Subsidiary disabled*/

   )

AND  A.IsDeleted = 'False'

AND  A.MultiCurrency = 'TRUE'

AND (@lBranchKey = '0' OR A.CustomerKey IN (SELECT CustomerKey FROM CusCustomerBranch WHERE BranchKey = @lBranchKey))

SELECT @lRowCount = @@RowCount

IF @lRowCount = 1

BEGIN

  RETURN

END

ELSE

BEGIN

  IF @lRowCount > 1

  BEGIN

  SELECT @pMultipleFound = 1

  RETURN

  END

END

-- Customer Tax Number

SELECT @pContext = 'CUSTOMERTAXNUMBER'

SELECT @pReturnKey = A.CustomerKey

FROM    CusCustomer A WITH(NOLOCK), ArrAccountsReceivable B WITH(NOLOCK)

WHERE   A.CustomerKey = B.CustomerKey

AND  B.SubsidiaryKey = @lSubsidaryKey

AND  ((@lSubsidaryKey != '0' AND B.TaxNumber = @pCode) OR /*Subsidiary Enabled*/

    (@lSubsidaryKey = '0' AND A.TaxNumber = @pCode)   /*Subsidiary disabled*/

   )

AND  A.IsDeleted = 0

AND  MultiCurrency = 1

AND (@lBranchKey = '0' OR A.CustomerKey IN (SELECT CustomerKey FROM CusCustomerBranch WHERE BranchKey = @lBranchKey))

SELECT @lRowCount = @@RowCount

IF @lRowCount = 1

BEGIN

  RETURN

END

ELSE

BEGIN

  IF @lRowCount > 1

  BEGIN

  SELECT @pMultipleFound = 1

  RETURN

  END

END

END

-- SALES PERSON

IF @lAllowSalesPersonScan = 'TRUE' AND @pPriceCheck = 0

BEGIN

SELECT @pContext = 'SALESPERSON'

SELECT @pReturnKey = UserKey

FROM    LbrUser     WITH(NOLOCK)

WHERE   Id = @pCode

AND  IsDeleted = 'False'

AND  IsActive = 'True'

AND  IsSalesPerson = 'True'

SELECT @lRowCount = @@RowCount

IF @lRowCount = 1

BEGIN

  RETURN

END

ELSE

BEGIN

  IF @lRowCount > 1

  BEGIN

  SELECT @pMultipleFound = 1

  RETURN

  END

END

END

IF @lAllowProductScan = 'TRUE'

BEGIN

SELECT @pContext = 'PRODUCTDESCRIPTION'

SELECT @pReturnKey = A.ProductKey

FROM    InvProduct  A WITH(NOLOCK), InvInventoryItem B   WITH(NOLOCK)

WHERE   A.ProductKey = B.ProductKey

And  B.WarehouseKey = @pWarehouseKey

AND  Description LIKE '%' + CASE @pCode WHEN '_' THEN '[_]' ELSE @pCode END + '%'

AND  IsDeleted = 0

AND ((@pPriceCheck = 0) OR (@pPriceCheck = 1 AND A.IsGiftCertificate = 0))

SELECT @lRowCount = @@RowCount

IF @lRowCount = 1

BEGIN

  RETURN

END

ELSE

BEGIN

  IF @lRowCount > 1

  BEGIN

  SELECT @pMultipleFound = 1

  RETURN

  END

END

END

IF @lAllowCustomerScan = 'TRUE' AND @pPriceCheck = 0

BEGIN

SELECT @pContext = 'CUSTOMERFIRSTNAME'

SELECT @pReturnKey = A.CustomerKey

FROM    CusCustomer A WITH(NOLOCK), ArrAccountsReceivable B WITH(NOLOCK)

WHERE   A.CustomerKey = B.CustomerKey

AND  B.SubsidiaryKey = @lSubsidaryKey

AND  FirstName LIKE '%' + CASE @pCode WHEN '_' THEN '[_]' ELSE @pCode END + '%'

AND  A.IsDeleted = 'False'

AND  MultiCurrency = 'TRUE'

AND (@lBranchKey = '0' OR A.CustomerKey IN (SELECT CustomerKey FROM CusCustomerBranch WHERE BranchKey = @lBranchKey))

SELECT @lRowCount = @@RowCount

IF @lRowCount = 1

BEGIN

  RETURN

END

ELSE

BEGIN

  IF @lRowCount > 1

  BEGIN

  SELECT @pMultipleFound = 1

  RETURN

  END

END

SELECT @pContext = 'CUSTOMERLASTNAME'

SELECT @pReturnKey = A.CustomerKey

FROM    CusCustomer A WITH(NOLOCK), ArrAccountsReceivable B WITH(NOLOCK)

WHERE   A.CustomerKey = B.CustomerKey

AND  B.SubsidiaryKey = @lSubsidaryKey

AND  LastName LIKE '%' + CASE @pCode WHEN '_' THEN '[_]' ELSE @pCode END + '%'

AND  A.IsDeleted = 'False'

AND  MultiCurrency = 'TRUE'

AND (@lBranchKey = '0' OR A.CustomerKey IN (SELECT CustomerKey FROM CusCustomerBranch WHERE BranchKey = @lBranchKey))

SELECT @lRowCount = @@RowCount

IF @lRowCount = 1

BEGIN

  RETURN

END

ELSE

BEGIN

  IF @lRowCount > 1

  BEGIN

  SELECT @pMultipleFound = 1

  RETURN

  END

END

SELECT @pContext = 'CUSTOMERMOBILE'

SELECT @pReturnKey = A.CustomerKey

FROM    CusCustomer A WITH(NOLOCK), ArrAccountsReceivable B WITH(NOLOCK)

WHERE   A.CustomerKey = B.CustomerKey

AND  B.SubsidiaryKey = @lSubsidaryKey

AND  MobilePhone = @pCode

AND  A.IsDeleted = 0

AND  MultiCurrency = 1

AND (@lBranchKey = '0' OR A.CustomerKey IN (SELECT CustomerKey FROM CusCustomerBranch WHERE BranchKey = @lBranchKey))

SELECT @lRowCount = @@RowCount

IF @lRowCount = 1

BEGIN

  RETURN

END

ELSE

BEGIN

  IF @lRowCount > 1

  BEGIN

  SELECT @pMultipleFound = 1

  RETURN

  END

END

SELECT @pContext = 'CUSTOMEREMAIL'

SELECT @pReturnKey = A.CustomerKey

FROM    CusCustomer A WITH(NOLOCK), ArrAccountsReceivable B   WITH(NOLOCK)

WHERE   A.CustomerKey = B.CustomerKey

AND  B.SubsidiaryKey = @lSubsidaryKey

AND  Email = @pCode

AND  A.IsDeleted = 'False'

AND  MultiCurrency = 'TRUE'

AND (@lBranchKey = '0' OR A.CustomerKey IN (SELECT CustomerKey FROM CusCustomerBranch WHERE BranchKey = @lBranchKey))

SELECT @lRowCount = @@RowCount

IF @lRowCount = 1

BEGIN

  RETURN

END

ELSE

BEGIN

  IF @lRowCount > 1

  BEGIN

  SELECT @pMultipleFound = 1

  RETURN

  END

END

END

-- Transaction

IF @lAllowTransactionScan = 'TRUE' AND @pPriceCheck = 0

BEGIN

SELECT @pContext = 'TRANSACTION'

SELECT @pReturnKey = TransactionKey

FROM TrxTransaction A WITH(NOLOCK)

INNER JOIN RtlStore B WITH(NOLOCK) ON A.StoreKey = B.StoreKey

INNER JOIN InvWarehouse C WITH(NOLOCK) ON B.WarehouseKey = C.WarehouseKey

WHERE TransactionId = @pCode

AND IsSuspended = 0

AND CustomerKey = CASE WHEN ISNULL(@pCustomerKey, '0') = ISNULL(@lCashCustomerKey, '0') THEN CustomerKey ELSE @pCustomerKey END

And C.BranchCode = @lBranchCode

SELECT @lRowCount = @@RowCount

IF @lRowCount = 1

BEGIN

  RETURN

END

ELSE

BEGIN

  IF @lRowCount > 1

  BEGIN

  SELECT @pMultipleFound = 1

  RETURN

  END

END

  --Transaction with GC

SELECT @pContext = 'TRANSACTION'

SELECT @pReturnKey = A.TransactionKey

FROM TrxTransaction A

INNER JOIN RtlStore B ON A.StoreKey = B.StoreKey

INNER JOIN InvWarehouse C ON B.WarehouseKey = C.WarehouseKey

INNER JOIN PmtGiftCertificate D ON A.TransactionKey = D.TransactionKey

INNER JOIN InvSerial E ON D.SerialNumber = E.SerialNumber and D.ProductKey = E.ProductKey

WHERE D.SerialNumber = @pCode

AND IsSuspended = 0

AND E.Status = 2

AND A.CustomerKey = CASE WHEN ISNULL(@pCustomerKey, '0') = ISNULL(@lCashCustomerKey, '0') THEN A.CustomerKey ELSE @pCustomerKey END

And C.BranchCode = @lBranchCode

SELECT @lRowCount = @@RowCount

IF @lRowCount = 1

BEGIN

  RETURN

END

ELSE

BEGIN

  IF @lRowCount > 1

  BEGIN

  SELECT @pMultipleFound = 1

  RETURN

  END

END

-- Transaction

SELECT @pContext = 'TRANSACTIONKEY'

SELECT @pReturnKey = TransactionKey

FROM    TrxTransaction A WITH(NOLOCK)

  INNER JOIN RtlStore B WITH(NOLOCK) ON A.StoreKey = B.StoreKey

  INNER JOIN InvWarehouse C WITH(NOLOCK) ON B.WarehouseKey = C.WarehouseKey

WHERE  TransactionKey = case when CHARINDEX('0', @pCode) = 1 then SUBSTRING(@pCode,2, Len(@pCode) -1) else @pCode end   --@pCode

AND  IsSuspended = 'FALSE'

AND  CustomerKey = CASE WHEN ISNULL(@pCustomerKey, '0') = ISNULL(@lCashCustomerKey, '0') THEN CustomerKey ELSE @pCustomerKey END

And C.BranchCode = @lBranchCode

SELECT @lRowCount = @@RowCount

IF @lRowCount = 1

BEGIN

  RETURN

END

ELSE

BEGIN

  IF @lRowCount > 1

  BEGIN

  SELECT @pMultipleFound = 1

  RETURN

  END

END

-- Transaction

SELECT @pContext = 'SUSPENDEDTRANSACTIONKEY'

SELECT @pReturnKey = TransactionKey

FROM    TrxTransaction A WITH(NOLOCK)

  INNER JOIN RtlStore B WITH(NOLOCK) ON A.StoreKey = B.StoreKey

  INNER JOIN InvWarehouse C   WITH(NOLOCK) ON B.WarehouseKey = C.WarehouseKey

WHERE   TransactionKey = case when CHARINDEX('0', @pCode) = 1 then SUBSTRING(@pCode,2, Len(@pCode) -1) else @pCode end   --@pCode