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

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

InvBatchLog

Table

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

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

TrxTransactionLayaway

Table

Store layway type of transactions.

1

TrxTransactionOrder

Table

Stores information about various order booked in the system.

1

TrxTransactionSaleItem

Table

Stores sale/refund/delivery items attached to a transaction

1

CREATE PROCEDURE [dbo].[TrxInventoryUpdateBatchNumber]

(

@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,

  @modifiedBy NVARCHAR(50),

  @modified DateTime ,

  @pPOSSiteId INT

  Set @modified = dbo.GetCompanyDateTime()

  Select @modifiedBy = ModifiedBy, @modified = Modified

  FROM TrxTransaction Where TransactionKey = @pTransactionKey

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

---Updating batches table for layaway items in case the batch is provided

If @pDebug = 'Y'

  Select 'Updating Batches table for layaway items'

  BEGIN

  INSERT INTO InvBatchLog

    (

     InventoryBatchLogKey, BatchKey, WarehouseKey

     , ProductKey, InQty, OutQty

     , SourceType, SourceKey, SourceDetailKey

     , LocationKey, ReservedQuantity, UpdateType, IsProcessed

    )

  Select NEWID(),

     i.ProductDetailKey, i.WarehouseKey

     , i.ProductKey, 0 As InQty, CASE WHEN i.InventoryAllocationMethod = 0 THEN i.Quantity ELSE 0 END As OutQty

     , 8 As SourceType, i.TransactionKey As SourceKey, i.TransactionLayawayKey As SourceDetailKey

     , i.DeliveryLocationKey As LocationKey, CASE WHEN i.InventoryAllocationMethod = 1 THEN i.Quantity ELSE 0 END As ReservedQuantity, 0, 1 As IsProcessed

  FROM TrxTransaction h, TrxTransactionLayaway i

  WHERE h.TransactionKey   = i.TransactionKey

  AND  h.TransactionKey   = @pTransactionKey

  AND  i.HasBatchNumber   = 'TRUE' -- Denotes that the batch number is provided while creation of the layway item

  AND  i.ProductDetailKey   != '0' -- Denotes that the batch number is provided while creation of the layway item

  AND  i.Type     = 0 -- [0] Layaway is created

  IF(@Error <>0)

    BEGIN

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

    GOTO ERRORHANDLER

  END

  END

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

  ----AT THE TIME OF DELIVERY AGAINST THE SALE WITH DELIVERY FULFILLMENT REDUCE THE RESERVED QUANTITY

  INSERT INTO InvBatchLog( InventoryBatchLogKey, BatchKey, WarehouseKey, ProductKey, InQty, OutQty,

  SourceType, SourceKey, SourceDetailKey, LocationKey, ReservedQuantity, UpdateType, IsProcessed)

  Select NEWID(),

    i.ProductDetailKey, i.WarehouseKey, i.ProductKey, 0 As InQty, CASE WHEN  f.InventoryAllocationMethod = 1 THEN i.Quantity ELSE 0 END As OutQty

    , 7, i.TransactionKey, i.TransactionItemKey, i.DeliveryLocationKey,

    CASE WHEN  original.ProductDetailKey = '0' THEN '0' ELSE -1 * i.Quantity END As ReservedQuantity, 0, 1 As IsProcessed

  FROM TrxTransaction h, TrxTransactionSaleItem i, TrxTransactionSaleItem original, TrxTransactionFulfillment f, TrxTransactionFulfillmentDetail fd

  WHERE h.TransactionKey   = i.TransactionKey

  AND  h.TransactionKey   = @pTransactionKey

  AND  i.OriginalDetailKey   = original.TransactionItemKey

  AND  i.OriginalDocumentKey = original.TransactionKey

  AND  original.TransactionItemKey =   fd.SourceDetailKey

  AND  original.TransactionKey = fd.TransactionKey

  AND  f.FulfillmentKey   = fd.FulfillmentKey

  AND  i.ProductDetailKey   != '0'

  AND  fd.SourceType   = 0 --[0] FULFILLMENT ON SALE

  AND  original.HasFulfillment = 'TRUE'

  AND  i.HasBatchNumber   = 'TRUE'

  AND  f.InventoryAllocationMethod = 1 --[1] DENOTES THIS IS Fulfillment is of DeliveryType

  AND  i.Type     = 4 --[1] DENOTES THIS IS DELIVERY AGAINST SALE ITEM

  IF(@Error <>0)

  BEGIN

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

  GOTO ERRORHANDLER

  END

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

----------------------------------------SALE ITEM WITH OUT FULLFILLMENT-----------------------------------------

If @pDebug = 'Y'

Select 'Updating Batches table for Sale Items WITHOUT Fulfillment attached to it'

  INSERT INTO InvBatchLog( InventoryBatchLogKey, BatchKey, WarehouseKey, ProductKey, InQty,OutQty,

  SourceType, SourceKey, SourceDetailKey, LocationKey, UpdateType, IsProcessed)

  Select NEWID(),

    s.ProductDetailKey, s.WarehouseKey, s.ProductKey,0,S.Quantity ,7, S.TransactionKey, s.TransactionItemKey, S.DeliveryLocationKey, 0, 1 AS IsProcessed

  --SELECT i.Quantity 'Quantity', i.ProductDetailKey into #tempBatch1

  FROM TrxTransaction h, TrxTransactionSaleItem S

  WHERE h.TransactionKey = S.TransactionKey

  AND  h.TransactionKey = @pTransactionKey

  AND  S.HasFulfillment = 'FALSE' --[FALSE] NO FULFILLMENT IS ATTACHED ON THIS

  AND  S.HasBatchNumber = 'TRUE'

  AND  S.Type   = 0   --[0] DENOTES SALES ITEMS

  IF(@Error <>0)

  BEGIN

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

  GOTO ERRORHANDLER

  END

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

----------------------------------------SALE ITEM WITH FULLFILLMENT-----------------------------------------

If @pDebug = 'Y'

Select 'Updating Batches table for Sale Items with fulfillment attached to it'

  INSERT INTO InvBatchLog( InventoryBatchLogKey, BatchKey, WarehouseKey, ProductKey, InQty,OutQty,

  SourceType, SourceKey, SourceDetailKey, LocationKey, ReservedQuantity, UpdateType, IsProcessed)

  Select NEWID(),

    i.ProductDetailKey, i.WarehouseKey, i.ProductKey, 0 As InQty, CASE WHEN F.InventoryAllocationMethod = 0 THEN i.Quantity ELSE 0 END As OutQty,

    7 As SourceType, i.TransactionKey, i.TransactionItemKey, i.DeliveryLocationKey, CASE WHEN F.InventoryAllocationMethod = 1 THEN i.Quantity ELSE 0 END As ReservedQuantity, 0, 1 AS IsProcessed

  FROM TrxTransaction h, TrxTransactionSaleItem i, TrxTransactionFulfillment f, TrxTransactionFulfillmentDetail fd

  WHERE h.TransactionKey = i.TransactionKey

  AND  h.TransactionKey = @pTransactionKey

  AND  i.HasFulfillment = 'TRUE' --[FALSE] NO FULFILLMENT IS ATTACHED ON THIS

  AND  i.HasBatchNumber = 'TRUE'

  AND  i.ProductDetailKey != '0'

  AND  i.Type     = 0   --[0] DENOTES SALES ITEMS

  AND  h.TransactionKey = fd.TransactionKey

  AND  fd.FulfillmentKey = f.FulFillmentKey

  AND  i.TransactionItemKey = fd.SourceDetailKey

  AND  fd.SourceType   = 0   --[0] DENOTES FULFILLMENT AGAINST SALE

  IF(@Error <>0)

  BEGIN

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

  GOTO ERRORHANDLER

  END

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

--Sale order item delivery with-----------------------------------------

If @pDebug = 'Y'

Select 'Updating Batches table for Sale Items with fulfillment attached to it'

BEGIN

INSERT INTO InvBatchLog (InventoryBatchLogKey, BatchKey, WarehouseKey, ProductKey, InQty, OutQty,

 SourceType, SourceKey, SourceDetailKey, AllocatedQuantity, LocationKey, ReservedQuantity, UpdateType, IsProcessed)

Select NEWID(),

 i.ProductDetailKey, i.WarehouseKey, i.ProductKey, 0,

case when i.IsDeliveryPackage = 1 then 0 else i.Quantity end,

7, i.TransactionKey, i.TransactionItemKey,

case when i.IsDeliveryPackage = 1 then i.Quantity else 0 end,

 i.DeliveryLocationKey, 0 As ReservedQuantity,

CASE WHEN i.IsDeliveryPackage = 0 THEN 0 ELSE 3 END, 1 AS IsProcessed

FROM TrxTransaction h, TrxTransactionSaleItem i, TrxTransactionOrder original, TrxTransactionFulfillment f, TrxTransactionFulfillmentDetail fd

WHERE h.TransactionKey   = i.TransactionKey

AND  h.TransactionKey   = @pTransactionKey

AND  i.OriginalDetailKey   = original.TransactionOrderKey

AND  i.OriginalDocumentKey   = original.TransactionKey

AND  i.HasBatchNumber   = 'TRUE'

AND  original.HasFulfillment   = 'TRUE' --[FALSE] NO FULFILLMENT IS ATTACHED ON THIS

AND  original.TransactionKey   =   fd.TransactionKey

AND  fd.FulfillmentKey   = f.FulFillmentKey

AND  original.TransactionOrderKey = fd.SourceDetailKey

AND  fd.SourceType     = 1   --[1] DENOTES FULFILLMENT AGAINST SALE ORDER

AND  i.Type       = 3   --[3] DENOTES SALES ORDER DELIVERY ITEMS

IF(@Error <>0)

BEGIN

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

  GOTO ERRORHANDLER

END

END

--Update the InStore, Avaiable and Reserved Quantity in case of the Layaway with Delivery-----------------------------------------

  BEGIN

  INSERT INTO InvBatchLog

   (

     InventoryBatchLogKey, BatchKey, WarehouseKey

     , ProductKey, InQty, OutQty

     , SourceType, SourceKey, SourceDetailKey

     , LocationKey, ReservedQuantity, UpdateType, IsProcessed

   )

  Select NEWID(),  i.ProductDetailKey, i.WarehouseKey

     , i.ProductKey, 0 As InQty, CASE WHEN original.InventoryAllocationMethod = 1 THEN i.Quantity ELSE 0 END As OutQty

     , 7 As SourceType, i.TransactionKey As SourceKey, i.TransactionItemKey As SourceDetailKey

     , i.DeliveryLocationKey As LocationKey, CASE WHEN original.InventoryAllocationMethod = 1 AND original.ProductDetailKey !='0' THEN -1 * i.Quantity ELSE 0 END As OutQty, 0,

  1 AS IsProcessed

  FROM TrxTransaction h, TrxTransactionSaleItem i, TrxTransactionLayaway original, TrxTransactionFulfillment f, TrxTransactionFulfillmentDetail fd

  WHERE h.TransactionKey   = i.TransactionKey

  AND  h.TransactionKey   = @pTransactionKey

  AND  i.OriginalDetailKey   = original.TransactionLayawayKey

  AND  i.OriginalDocumentKey   = original.TransactionKey

  AND  i.HasBatchNumber   = 'TRUE'

  AND  original.HasFulfillment   = 'TRUE' --[FALSE] NO FULFILLMENT IS ATTACHED ON THIS

  AND  original.TransactionKey   =   fd.TransactionKey

  AND  fd.FulfillmentKey   = f.FulFillmentKey

  AND  original.TransactionLayawayKey = fd.SourceDetailKey

  AND  fd.SourceType     = 2   --[1] DENOTES FULFILLMENT AGAINST LAYAWAY

  AND  i.Type       = 5   --[5] DENOTES LAYAWAY DELIVERY ITEMS

  AND  original.InventoryAllocationMethod = 1 --[1] INVENTORY IS ALLOCATED AT THE TIME OF DELIVERY

  IF(@Error <>0)

    BEGIN

    SET @ErrorDesc = 'Error while posting the batch updates for the Layaway with Delivery.'

    GOTO ERRORHANDLER

  END

  END

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

--Sale refund for which does not have fulfillment-----------------------------

If @pDebug = 'Y'

  Select 'Updating Batches for sales returns'

  BEGIN

  -------------------------GENERATE THE ENTRY FOR THE batch INVENTORY UPDATE----------------------------------

  INSERT INTO InvBatchLog( InventoryBatchLogKey, BatchKey, WarehouseKey, ProductKey, InQty,OutQty,

   SourceType, SourceKey, SourceDetailKey, LocationKey, UpdateType, IsProcessed)

  Select NEWID(),

     i.ProductDetailKey, i.WarehouseKey, i.ProductKey,i.Quantity, 0, 7, i.TransactionKey, i.TransactionItemKey, i.DeliveryLocationKey, 0, 1 AS IsProcessed

  FROM TrxTransaction h, TrxTransactionSaleItem i

  WHERE h.TransactionKey = i.TransactionKey

  AND  h.TransactionKey = @pTransactionKey

  AND  i.OriginalDetailKey = '0'

  AND  i.OriginalDocumentKey = '0'

  AND  i.HasFulfillment = 'FALSE'

  AND  i.HasBatchNumber = 'TRUE'

  AND  i.Type     = 1

  IF(@Error <>0)

    BEGIN

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

    GOTO ERRORHANDLER

  END

  END

-------------------------GENERATE THE ENTRY FOR THE batch INVENTORY UPDATE----------------------------------

  INSERT INTO InvBatchLog( InventoryBatchLogKey, BatchKey, WarehouseKey, ProductKey, InQty,OutQty,

  SourceType, SourceKey, SourceDetailKey, LocationKey, UpdateType, IsProcessed)

  Select NEWID(),

    i.ProductDetailKey, i.WarehouseKey, i.ProductKey,i.Quantity, 0, 7, i.TransactionKey, i.TransactionItemKey, i.DeliveryLocationKey, 0 , 1 AS IsProcessed

  FROM TrxTransaction h, TrxTransactionSaleItem i, TrxTransactionSaleItem original

  WHERE h.TransactionKey = i.TransactionKey

  AND  h.TransactionKey = @pTransactionKey

  AND  i.OriginalDetailKey <> '0'

  AND  i.OriginalDocumentKey <> '0'

  AND  i.OriginalDetailKey = original.TransactionItemKey

  AND  i.OriginalDocumentKey = original.TransactionKey

  AND  original.HasFulfillment = 'FALSE'

  AND  i.HasBatchNumber = 'TRUE'

  AND  i.Type     = 1

  IF(@Error <>0)

  BEGIN

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

  GOTO ERRORHANDLER

  END

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

------------------------------------------SALE REFUND WHICH HAVE FULFILLMENT-----------------------------

If @pDebug = 'Y'

  Select 'Updating Batches for sales returns'

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

  INSERT INTO InvBatchLog( InventoryBatchLogKey, BatchKey, WarehouseKey, ProductKey, InQty, OutQty,

  SourceType, SourceKey, SourceDetailKey, LocationKey, ReservedQuantity, UpdateType, IsProcessed)

  Select NEWID(),

    i.ProductDetailKey, i.WarehouseKey, i.ProductKey, CASE WHEN f.InventoryAllocationMethod = 0 then i.Quantity else 0 end As InQty, 0 As OutQty

    , 7, i.TransactionKey, i.TransactionItemKey, i.DeliveryLocationKey,

    CASE WHEN f.InventoryAllocationMethod = 1 then -1 * i.Quantity ELSE 0 END As ReservedQuantity, 0, 1 AS IsProcessed

  FROM TrxTransaction h, TrxTransactionSaleItem i, TrxTransactionSaleItem original, TrxTransactionFulfillment f, TrxTransactionFulfillmentDetail fd

  WHERE h.TransactionKey   = i.TransactionKey

  AND  h.TransactionKey   = @pTransactionKey

  AND  i.OriginalDetailKey   = original.TransactionItemKey

  AND  i.OriginalDocumentKey = original.TransactionKey

  AND  original.TransactionItemKey =   fd.SourceDetailKey

  AND  original.TransactionKey = fd.TransactionKey

  AND  f.FulfillmentKey   = fd.FulfillmentKey

  AND  i.ProductDetailKey   != '0'

  AND  fd.SourceType   = 0 --[0] FULFILLMENT ON SALE

  AND  original.HasFulfillment = 'TRUE'

  AND  i.HasBatchNumber   = 'TRUE'

  AND  i.Type     = 1 --[1] DENOTES THIS IS REFUND ITEM

  IF(@Error <>0)

  BEGIN

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

  GOTO ERRORHANDLER

  END

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

--------------------------UPDATING BATCHES TABLE FOR LAYAWAY CANCELLATION ITEMS------------------------------------

If @pDebug = 'Y'

  Select 'Updating Batches table for layaway items'

  BEGIN

  INSERT INTO InvBatchLog

   (

    InventoryBatchLogKey, BatchKey, WarehouseKey

    , ProductKey, InQty, OutQty

    , SourceType, SourceKey, SourceDetailKey

    , LocationKey, ReservedQuantity, UpdateType, IsProcessed

   )

  Select NEWID(), i.ProductDetailKey, i.WarehouseKey

     , i.ProductKey, CASE WHEN original.InventoryAllocationMethod = 0 THEN i.Quantity ELSE 0 END AS InQty, 0 As OutQty

     , 7, i.TransactionKey, i.TransactionLayawayKey

     , original.DeliveryLocationKey, CASE WHEN original.InventoryAllocationMethod = 1 THEN -1 * i.Quantity ELSE 0 END AS ReservedQuantity, 0, 1 As IsProcessed

  FROM TrxTransaction h, TrxTransactionLayaway i, TrxTransactionLayaway original

  WHERE h.TransactionKey     = i.TransactionKey

  AND  h.TransactionKey     = @pTransactionKey

  AND  i.Type       = 1   --[1] DENOTES THIS IS FOR THE CANCELLED ENTRY

  AND  i.OriginalDetailKey     = original.TransactionLayawayKey

  AND  i.OriginalDocumentKey   = original.TransactionKey

  AND  original.Status     = 2   --[2] DENOTES THAT THE ORIGINAL LAYWAY ENTRY IS CANCELLED

  AND  original.InventoryAllocationMethod = 0   --[0] INVENTORY IS ALLOCATED AT THE TIME OF CREATION OF LAYAWAY

    AND  i.HasBatchNumber     = 'TRUE'   --DENOTES THAT THE BATCH NUMBER IS PROVIDED WHILE CREATION OF THE LAYWAY ITEM

  AND  original.ProductDetailKey   != '0'

  IF(@Error <>0)

    BEGIN

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

    GOTO ERRORHANDLER

  END

  END

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

RETURN

ERRORHANDLER:

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

RETURN

END