iVend Database Database : TrxInventoryUpdateSerialNumber Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@pDebug

In

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

VarChar

1

@pTransactionKey

In

Reference key of the Transaction

VarWChar

50

@pSiteId

In

Reference key of the Site

VarWChar

50

@pBatchKey

In

Reference key of the batch in which the transaction is getting saved, to maintain concurrency

VarWChar

50

@pTransactionStoreKey

In

Reference key of Store

VarWChar

50

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

TrxCompleteTransactionUpdates

Stored Procedure

1

Database Object

Object Type

Description

Dep Level

CfgSiteInformation

Table

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

1

InvSerial

Table

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

1

InvSerialLog

Table

Stores the Delta updates made to any serial in the system. This table gets affected when doing any inventory related transaction for a serial item.

1

RtlStore

Table

Contains the details of Stores defined in the system.

1

TrxTransaction

Table

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

1

TrxTransactionFulfillment

Table

Defines a list of all those transactions which have a fulfillment plan attached to it.

1

TrxTransactionFulfillmentDetail

Table

Defines the details of all the transactions which have a fulfillment plan attached to them.

1

TrxTransactionGiftCertificate

Table

Stores the values for the Gift certificate for the transaction

1

TrxTransactionLayaway

Table

Store layway type of transactions.

1

TrxTransactionSaleItem

Table

Stores sale/refund/delivery items attached to a transaction

1

CREATE PROCEDURE [dbo].[TrxInventoryUpdateSerialNumber]

(

@pDebug     CHAR(1),

@pTransactionKey NVARCHAR(50),

@pSiteId   NVARCHAR(50),

@pBatchKey   NVARCHAR(50),

@pTransactionStoreKey NVARCHAR(50)

)

AS

BEGIN

SET NOCOUNT ON

Declare @ErrorDesc     VARCHAR(255),

  @Error     INT,

  @pTransactionWarehouseKey NVARCHAR(50),

  @pPOSSiteId     INT

Select @pTransactionWarehouseKey = WarehouseKey From RtlStore Where StoreKey = @pTransactionStoreKey

SELECT @pPOSSiteId = ISNULL(POSSiteId, 0) FROM CfgSiteInformation

-------------------------------------------------UPDATE THE STATUS OF THE SERIAL NO FOR SALE AND FOR DELIVERY-------------------------------------

If @pDebug = 'Y'

  Select 'UPDATING SERIAL NUMBER TABLE FOR SALE ITEMS DONE AGAINST SALES OR DELIVERY OF SO'

--CHECK IF THE SERIAL NUMBER IS AVAILABLE TO BE SOLD OR NOT

IF EXISTS(

    SELECT A.SerialNumber

    FROM   InvSerial A With (nolock)

       ,TrxTransaction h With (nolock)

       , TrxTransactionSaleItem s With (nolock)

    WHERE h.TransactionKey = s.TransactionKey

    AND  A.SerialKey   = s.ProductDetailKey

    AND  h.TransactionKey = @pTransactionKey

    AND  s.HasSerialNumber = 'TRUE'

    AND  s.ProductDetailKey != '0'

    AND  s.Type   IN (0, 3, 5) --[0] Sale, [3] Delivery Against Sales Order

    AND  A.Status   NOT IN (1, 4, 6, 7) --instore, on layaway, reserved, commited

   )

    BEGIN

  SET @ErrorDesc = 'SERIAL SELECTED IN TRANSACTION IS NOT AVAILABLE.'

        GOTO ERRORHANDLER

END

---1. Update the serial status as Reserved in case the sale has delivery fulfillment

UPDATE InvSerial

  SET Status = CASE WHEN T1.Type IN (3, 5) THEN 2 WHEN T2.InventoryAllocationMethod = 1 then 7 ELSE 2 END, -- SOLD

   ModifiedBy = T0.ModifiedBy,

   Modified = T0.Modified

FROM TrxTransaction T0 With (nolock)

  INNER JOIN TrxTransactionSaleItem T1 With (nolock)

  ON T0.TransactionKey = T1.TransactionKey

  INNER JOIN InvSerial T4 With (nolock)

  ON T1.ProductDetailKey = T4.SerialKey

  AND T1.HasSerialNumber = 1

  AND T1.ProductDetailKey != '0'

  LEFT OUTER JOIN TrxTransactionFulfillment T2 With (nolock)

  ON T0.TransactionKey = T2.TransactionKey

  LEFT OUTER JOIN TrxTransactionFulfillmentDetail T3 With (nolock)

  ON T2.FulfillmentKey = T3.FulfillmentKey

WHERE T0.TransactionKey = @pTransactionKey

AND  T1.Type IN (0, 3, 5) --[0] Sale, [3] Delivery Against Sales Order,  [5] Delivery Against Layby

IF(@@ERROR <>0)

    BEGIN

  SET @ErrorDesc = 'ERROR WHILE UPDATING SERIAL TABLE FOR SALE ITEMS.'

        GOTO ERRORHANDLER

END

-------------------------GENERATE THE ENTRY FOR THE SERIAL INVENTORY UPDATE----------------------------------

  INSERT INTO InvSerialLog(InventorySerialLogKey, SerialKey, WarehouseKey, ProductKey, Status,

  SourceType, SourceKey, SourceDetailKey, IsProcessed)

  Select NEWID(),

    T1.ProductDetailKey, T1.WarehouseKey, T1.ProductKey, CASE WHEN T1.Type IN (3,5) THEN 2 WHEN T2.InventoryAllocationMethod = 1 then 7 ELSE 2 END,

    7, T0.TransactionKey, T1.TransactionItemKey, 1 As IsProcessed

  FROM TrxTransaction T0 With (nolock)INNER JOIN TrxTransactionSaleItem T1 With (nolock)

  ON T0.TransactionKey = T1.TransactionKey

  INNER JOIN InvSerial T4 With (nolock)

  ON T1.ProductDetailKey = T4.SerialKey

  AND T1.HasSerialNumber = 1

  AND T1.ProductDetailKey != '0'

  LEFT OUTER JOIN TrxTransactionFulfillment T2 With (nolock)

  ON T0.TransactionKey = T2.TransactionKey

  LEFT OUTER JOIN TrxTransactionFulfillmentDetail T3 With (nolock)

  ON T2.FulfillmentKey = T3.FulfillmentKey

  WHERE T0.TransactionKey = @pTransactionKey

  AND  T1.Type IN (0, 3, 5) --[0] Sale, [3] Delivery Against Sales Order,  [5] Delivery Against Layby

  IF(@Error <>0)

  BEGIN

  SET @ErrorDesc = 'Error inserting in InvSerialLog Table.'

  GOTO ERRORHANDLER

  END

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

-------------------------------------------------UPDATE THE STATUS OF THE SERIAL FOR THE DELIVERY BASED ON SALES-------------------------------------

If @pDebug = 'Y'

  SELECT 'UPDATING SERIAL NUMBER TABLE FOR SALE ITEMS DONE AGAINST SALES OR DELIVERY OF SO'

--CHECK IF THE SERIAL NUMBER IS AVAILABLE TO BE SOLD OR NOT

IF EXISTS(

    SELECT Serial.SerialNumber

    FROM InvSerial Serial With (nolock)

       , TrxTransaction h With (nolock)

       , TrxTransactionSaleItem SalesItem With (nolock)

       , TrxTransactionSaleItem OriginalSalesItem With (nolock)

       , RtlStore Store With (nolock)

    WHERE h.TransactionKey     = SalesItem.TransactionKey

    AND  Serial.SerialKey     = SalesItem.ProductDetailKey

    AND  h.TransactionKey     = @pTransactionKey

    AND  SalesItem.HasSerialNumber   = 'TRUE'

    AND  SalesItem.ProductDetailKey   != '0'

    AND  SalesItem.OriginalDocumentKey = OriginalSalesItem.TransactionKey

    AND  SalesItem.OriginalDetailKey   = OriginalSalesItem.TransactionItemKey

    AND  OriginalSalesItem.DeliveryWarehouseKey != @pTransactionWarehouseKey

    AND  SalesItem.Type     = 4 --INDICATES DELIVERY AGAINST THE SALES

    AND  Serial.Status     NOT IN (1, 4, 6, 7)

    AND  Store.IsDeleted     = 0

   )

    BEGIN

  SET @ErrorDesc = 'Serial selected in transaction is not available.'

        GOTO ERRORHANDLER

END

UPDATE InvSerial

SET  Status = 2, -- SOLD

   ModifiedBy = h.ModifiedBy,

   Modified = h.Modified

FROM TrxTransaction h With (nolock)

   , TrxTransactionSaleItem SalesItem With (nolock)

WHERE h.TransactionKey     = SalesItem.TransactionKey

AND  InvSerial.SerialKey     = SalesItem.ProductDetailKey

AND  h.TransactionKey     = @pTransactionKey

AND  SalesItem.HasSerialNumber   = 'TRUE' --INDICATES THAT THE ITEM SERIAL NUMBER IS DEFINED

AND  SalesItem.ProductDetailKey   != '0' --INDICATES THAT THE ITEM SERIAL NUMBER IS DEFINED

AND  SalesItem.Type     = 4 --INDICATES DELIVERY IS AGINST THE SALES

IF(@@ERROR <>0)

    BEGIN

  SET @ErrorDesc = 'Error updating the serial status - Delivery For Sale'

        GOTO ERRORHANDLER

END

-------------------------GENERATE THE ENTRY FOR THE SERIAL INVENTORY UPDATE----------------------------------

  INSERT INTO InvSerialLog(InventorySerialLogKey, SerialKey, WarehouseKey,

        ProductKey, Status, SourceType,

        SourceKey, SourceDetailKey, IsProcessed)

  Select NEWID(),

    SalesItem.ProductDetailKey, SalesItem.WarehouseKey, SalesItem.ProductKey, 2, 7, SalesItem.TransactionKey, SalesItem.TransactionItemKey, 1 As IsProcessed

  FROM TrxTransaction h With (nolock)

   , TrxTransactionSaleItem SalesItem With (nolock)

   , InvSerial With (nolock)

  WHERE h.TransactionKey     = SalesItem.TransactionKey

  AND  InvSerial.SerialKey     = SalesItem.ProductDetailKey

  AND  InvSerial.ProductKey   = SalesItem.ProductKey

  AND  h.TransactionKey     = @pTransactionKey

  AND  SalesItem.HasSerialNumber   = 'TRUE' --INDICATES THAT THE ITEM SERIAL NUMBER IS DEFINED

  AND  SalesItem.ProductDetailKey   != '0' --INDICATES THAT THE ITEM SERIAL NUMBER IS DEFINED

  AND  SalesItem.Type     = 4 --INDICATES DELIVERY IS AGINST THE SALES

  IF(@Error <>0)

  BEGIN

  SET @ErrorDesc = 'Error inserting in InvSerialLog Table  - Delivery For Sale'

  GOTO ERRORHANDLER

  END

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

-------------------UPDATE THE STATUS OF THE SERIAL NO FOR LAYAWAY CREATION---------------------------------------------------------------------------

If @pDebug = 'Y'

  Select 'UPDATING SERIAL NUMBER TABLE FOR LAYAWAY CREATION'

UPDATE InvSerial

SET  Status = CASE WHEN S.InventoryAllocationMethod = 0 THEN 4 else 7 END,   -- ON LAYAWAY

   ModifiedBy = h.ModifiedBy,

   Modified = h.Modified

FROM  TrxTransaction h With (nolock)

   , TrxTransactionLayaway s With (nolock)

WHERE h.TransactionKey = s.TransactionKey

AND  h.TransactionKey = @pTransactionKey

AND  s.Type   = 0 ---[INDICATES CREATION OF LAYAWAY]

AND  s.HasSerialNumber = 'TRUE'

AND  s.ProductDetailKey != '0'

AND  InvSerial.SerialKey = s.ProductDetailKey

IF(@@ERROR <>0)

    BEGIN

  SET @ErrorDesc = 'ERROR WHILE UPDATING SERIAL TABLE FOR SALE ITEMS.'

        GOTO ERRORHANDLER

END

  -------------------------GENERATE THE ENTRY FOR THE SERIAL INVENTORY UPDATE----------------------------------

  INSERT INTO InvSerialLog(InventorySerialLogKey, SerialKey, WarehouseKey, ProductKey, Status,

  SourceType, SourceKey, SourceDetailKey, IsProcessed)

  Select NEWID(),

    s.ProductDetailKey, s.WarehouseKey, s.ProductKey, CASE WHEN S.InventoryAllocationMethod = 0 THEN 4 else 7 END, 8, S.TransactionKey, s.TransactionLayawayKey,

    1 As IsProcessed

  FROM TrxTransaction h With (nolock)

   , TrxTransactionLayaway s With (nolock)

  WHERE h.TransactionKey = s.TransactionKey

  AND  h.TransactionKey = @pTransactionKey

  AND  s.HasSerialNumber = 'TRUE'

  AND  s.ProductDetailKey != '0'

  AND  s.Type   = 0 --[0] Layaway Creation

  IF(@Error <>0)

  BEGIN

  SET @ErrorDesc = 'ERROR WHILE GENERATING THE RPLICATION KEY FOR SERIAL UPDATE.'

  GOTO ERRORHANDLER

  END

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

-------------------UPDATE THE STATUS OF THE SERIAL NO FOR LAYAWAY CANCELLATION---------------------------------------------------------------------------

If @pDebug = 'Y'

  SELECT 'UPDATING SERIAL NUMBER TABLE FOR LAYAWAY CANCELLATION'

UPDATE InvSerial

SET  Status = 1,   -- IN STORE

   ModifiedBy = h.ModifiedBy,

   Modified = h.Modified

FROM TrxTransaction h With (nolock)

   , TrxTransactionLayaway s With (nolock)

WHERE h.TransactionKey = s.TransactionKey

AND  h.TransactionKey = @pTransactionKey

AND  s.Type   = 1 ---[INDICATES CREATION OF LAYAWAY CANCELLATION]

AND  s.HasSerialNumber = 'TRUE'

AND  s.ProductDetailKey != '0'

AND  InvSerial.SerialKey = s.ProductDetailKey

IF(@@ERROR <>0)

    BEGIN

  SET @ErrorDesc = 'ERROR WHILE UPDATING SERIAL TABLE FOR SALE ITEMS.'

        GOTO ERRORHANDLER

END

  -------------------------GENERATE THE ENTRY FOR THE SERIAL INVENTORY UPDATE----------------------------------

  INSERT INTO InvSerialLog(InventorySerialLogKey, SerialKey, WarehouseKey, ProductKey, Status,

  SourceType, SourceKey, SourceDetailKey, IsProcessed)

  Select NEWID(),

    s.ProductDetailKey, s.WarehouseKey, s.ProductKey, 1, 8, S.TransactionKey, s.TransactionLayawayKey, 1 As IsProcessed

  FROM TrxTransaction h With (nolock)

  , TrxTransactionLayaway s With (nolock)

  WHERE h.TransactionKey = s.TransactionKey

  AND  h.TransactionKey = @pTransactionKey

  AND  s.ProductDetailKey != '0'

  AND  s.HasSerialNumber = 'TRUE'

  AND  s.Type   = 1 --[0] LAYAWAY CANCELLATION

  IF(@Error <>0)

  BEGIN

  SET @ErrorDesc = 'ERROR WHILE GENERATING THE REPLICATION KEY FOR SERIAL UPDATE.'

  GOTO ERRORHANDLER

  END

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

-------------------------------------------------UPDATE THE STATUS OF THE SERIAL NO FOR REFUND-------------------------------------------------------------

If @pDebug = 'Y'

  Select 'UPDATING SERIAL NUMBER TABLE FOR SALE ITEMS DONE AGAINST SALES OR DELIVERY OF SO'

UPDATE InvSerial

SET  Status = 1, -- IN STORE

   ModifiedBy = h.ModifiedBy,

   Modified = h.Modified

FROM TrxTransaction h With (nolock)

 , TrxTransactionSaleItem s With (nolock)

WHERE h.TransactionKey = s.TransactionKey

AND  h.TransactionKey = @pTransactionKey

AND  s.HasSerialNumber = 'TRUE'

AND  s.ProductDetailKey != '0'

AND  s.Type   = 1 --[0] REFUND

AND  InvSerial.SerialKey = s.ProductDetailKey

IF(@@ERROR <>0)

    BEGIN

  SET @ErrorDesc = 'ERROR WHILE UPDATING SERIAL TABLE FOR SALE ITEMS.'

        GOTO ERRORHANDLER

END

  -------------------------GENERATE THE ENTRY FOR THE SERIAL INVENTORY UPDATE----------------------------------

  INSERT INTO InvSerialLog(InventorySerialLogKey, SerialKey, WarehouseKey, ProductKey, Status,

  SourceType, SourceKey, SourceDetailKey, IsProcessed)

  Select NEWID(),

    s.ProductDetailKey, s.WarehouseKey, s.ProductKey, 1, 7, S.TransactionKey, s.TransactionItemKey, 1 As IsProcessed

  FROM TrxTransaction h With (nolock)

  , TrxTransactionSaleItem s With (nolock)

  WHERE h.TransactionKey = s.TransactionKey

  AND  h.TransactionKey = @pTransactionKey

  AND  s.HasSerialNumber = 'TRUE'

  AND  s.Type   = 1 --[0] REFUND

  AND  s.ProductDetailKey != '0'

  IF(@Error <>0)

  BEGIN

  SET @ErrorDesc = 'ERROR WHILE GENERATING THE REPLICATION KEY FOR SERIAL UPDATE.'

  GOTO ERRORHANDLER

  END

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

-------------------------------------------------UPDATE THE STATUS OF THE SERIAL NO FOR GIFT CERTIFICATE SALE-------------------------------------

If @pDebug = 'Y'

  Select 'UPDATING SERIAL NUMBER TABLE FOR GIFT CERTIFICATE '

--check if the serial number is available to be sold or not

IF EXISTS(

    SELECT  A.SerialNumber

    FROM InvSerial A With (nolock)

    , TrxTransaction h With (nolock)

    , TrxTransactionGiftCertificate s With (nolock)

    WHERE h.TransactionKey = s.TransactionKey

    AND  h.TransactionKey = @pTransactionKey

    AND  A.SerialKey = s.SerialNumberKey

    AND  A.Status NOT IN (1, 4,6)

    AND  s.Type = 0 ---[indicates the gc is sold]

   )

    BEGIN

  SET @ErrorDesc = 'SERIAL SELECTED IN TRANSACTION IS NOT AVAILABLE.'

        GOTO ERRORHANDLER

END

UPDATE InvSerial

SET  Status = 2, -- SOLD

   ModifiedBy = h.ModifiedBy,

   Modified = h.Modified

FROM TrxTransaction h With (nolock)

 , TrxTransactionGiftCertificate s With (nolock)

WHERE h.TransactionKey = s.TransactionKey

AND  h.TransactionKey = @pTransactionKey

AND  InvSerial.SerialKey = s.SerialNumberKey

AND  s.Type = 0 ---[indicates the gc is sold]

IF(@@ERROR <>0)

    BEGIN

  SET @ErrorDesc = 'ERROR WHILE UPDATING SERIAL TABLE FOR GIFT CERTIFICATE ITEMS.'

        GOTO ERRORHANDLER

END

  -------------------------GENERATE THE ENTRY FOR THE SERIAL INVENTORY UPDATE----------------------------------

  INSERT INTO InvSerialLog(InventorySerialLogKey, SerialKey, WarehouseKey, ProductKey, Status,

   SourceType, SourceKey, SourceDetailKey, IsProcessed)

  Select NEWID(),

       s.SerialNumberKey, s.WarehouseKey, s.ProductKey, 2, 10, S.TransactionKey, s.TransactionGiftCertificateKey, 1 As IsProcessed

  FROM TrxTransaction h With (nolock)

   , TrxTransactionGiftCertificate s With (nolock)

  WHERE h.TransactionKey = s.TransactionKey

  AND  h.TransactionKey = @pTransactionKey

  AND  s.Type = 0 ---[indicates the gc is sold]

  IF(@Error <>0)

    BEGIN

  SET @ErrorDesc = 'ERROR WHILE GENERATING THE REPLICATION KEY FOR SERIAL UPDATE.'

  GOTO ERRORHANDLER

  END

RETURN

ERRORHANDLER:

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

RETURN

END