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

2

GetCompanyDateTime

User Defined Function

1

InvAssemblyComponent

Table

Defines the Assembly set up at the console together with the parent as well as the associated products.

1

InvInventoryItem

Table

Stores inventory details for each Product for each Warehouse

1

InvInventoryItemLog

Table

Stores any Delta changes to the Inventory. Stores the quantity changes along with the reference of the document due to which the inventory got updated.

1

InvProduct

Table

Stores the product related details.

1

RepReplicationTransaction

Table

Stores the Transaction details for them the data needs to be replicated

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

TrxTransactionDynamicAssemblyItem

Table

Stores sale/refund/delivery if Dynamic assmebly items attached to a 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].[TrxInventoryUpdateSaleDelivery]

(

@pDebug     CHAR(1) ,

@pTransactionKey NVARCHAR(50) ,

@pSiteId   NVARCHAR(50) ,

@pBatchKey   NVARCHAR(50) ,

@pTransactionStoreKey NVARCHAR(50)

)

AS

BEGIN

SET NOCOUNT ON

Declare @ErrorDesc VARCHAR(255),

  @modifiedBy NVARCHAR(50) ,

  @modified DATETIME ,

  @Error INT

  Set @modified = dbo.GetCompanyDateTime()

  Select @modifiedBy = ModifiedBy, @modified = Modified

  FROM TrxTransaction

  Where TransactionKey = @pTransactionKey

-----------------------FOR GENERATING THE DELTA ENTRY AND REPLICATION ENTRY-----------------------

INSERT INTO InvInventoryItemLog

  ( WarehouseKey , ProductKey , InQty , OutQty , OnFulfillmentQuanity ,

   SourceType , SourceKey , SourceDetailKey ,

   InventoryItemLogKey , UpdateType , Created , ItemCost , AllocatedQuantity , LocationKey ,

   ReservedQuantity , IsProcessed

  )

SELECT Store.WarehouseKey , SalesItem.ProductKey , 0 ,

  CASE WHEN f.InventoryAllocationMethod = 1 THEN SalesItem.Quantity ELSE 0 END ,

   (-1) * SalesItem.Quantity ,

  7 , SalesItem.TransactionKey , SalesItem.TransactionItemKey ,

  NEWID() , CASE WHEN SalesItem.IsDeliveryPackage =0 THEN 0 ELSE 3 END , --BOTH NEEDS TO BE UPDATED

   dbo.GetCompanyDateTime() , SalesItem.AverageCost , CASE WHEN SalesItem.IsDeliveryPackage = 0 THEN 0 ELSE SalesItem.Quantity END , SalesItem.DeliveryLocationKey ,

  -- Changed by Santosh to fix - bug 30906

  --CASE WHEN f.ReserveQuantity = 1 THEN (-1) * SalesItem.Quantity ELSE 0 END ,

  CASE WHEN SalesItem.IsDeliveryPackage = 1 THEN 0 ELSE (CASE WHEN f.ReserveQuantity = 1 THEN (-1) * SalesItem.Quantity ELSE 0 END) END,

  1 AS IsProcessed

FROM  TrxTransaction h With(nolock),

   TrxTransactionSaleItem SalesItem With(nolock),

   TrxTransactionOrder SalesOrder With(nolock),

   InvProduct p With(nolock),

   TrxTransactionFulfillment f With(nolock),

   TrxTransactionFulfillmentDetail FD With(nolock),

   RtlStore Store With(nolock)

WHERE h.TransactionKey   = SalesItem.TransactionKey

AND  SalesItem.ProductKey   = p.ProductKey

AND  SalesItem.OriginalDocumentKey = SalesOrder.TransactionKey

AND  SalesItem.OriginalDetailKey = SalesOrder.TransactionOrderKey

AND  f.FulfillmentKey   = fd.FulfillmentKey

AND  fd.TransactionKey   = SalesOrder.TransactionKey

AND  fd.SourceDetailKey   = SalesOrder.TransactionOrderKey

AND  SalesOrder.DeliveryWarehouseKey = Store.WarehouseKey

AND  fd.SourceType     = 1 --INDICATES THAT THE FULFILLMENT IS ON SPEACIAL ORDER

AND  SalesOrder.HasFulfillment = 'TRUE' --INDICATES THAT THE ORDER ITEM HAS FULFILLMENT

AND  p.IsNonStock     = 'FALSE' --INDICATES THAT THE ORDER ITEM IS STOCKABLE ITEM

AND  p.IsAssembly     = 'FALSE' --INDICATES THAT THE ITEM IS NOT AN ASSEMBLY ITEM

AND  SalesItem.Type     = 3 --INDICATES THAT THE DELIVERY IS MADE AGAINST THE ORDER

AND  h.TransactionKey   = @pTransactionKey

And  Store.IsDeleted     = 0

--####

--####

  If @pDebug = 'Y'

  SELECT 'UPDATING THE FULLFILLMENT QUANTITY OF THE SPECIAL ORDER AGAINST DELIVERY'

  UPDATE OrderItem

  SET  OrderItem.FullfilledQuantity = OrderItem.FullfilledQuantity + b.Quantity ,

    OrderItem.OpenQuantity   = OrderItem.OpenQuantity - b.Quantity

  FROM TrxTransactionOrder OrderItem ,

  (

  SELECT SUM(SalesItem.Quantity) 'Quantity' ,

     SalesItem.OriginalDocumentKey ,

     SalesItem.OriginalDetailKey

  FROM TrxTransaction h With(nolock), TrxTransactionSaleItem SalesItem With(nolock), TrxTransactionOrder OrderItem With(nolock)

  WHERE h.TransactionKey   = SalesItem.TransactionKey

  AND  SalesItem.OriginalDocumentKey = OrderItem.TransactionKey

  AND  SalesItem.OriginalDetailKey = OrderItem.TransactionOrderKey

  AND  h.TransactionKey   = @pTransactionKey

  AND  SalesItem.Type     = 3 --INDICATES THAT THE DELIVERY IS MADE AGAINST THE ORDER

  GROUP BY SalesItem.OriginalDocumentKey , SalesItem.OriginalDetailKey

  ) b

  WHERE b.OriginalDocumentKey = OrderItem.TransactionKey

  AND  b.OriginalDetailKey = OrderItem.TransactionOrderKey

  IF(@@ERROR <>0)

  BEGIN

  SET @ErrorDesc = 'ERROR WHILE UPDATING FULFILLED QUANTITY FOR ORDER ITEM'

  GOTO ERRORHANDLER

  END

  ---FOR GENERATING THE REPLICATION ENTRY FOR SO LINES AGAINST WHICH THE DELIVERY IS DONE-------------

  INSERT INTO RepReplicationTransaction(ReplicationTransactionKey , SourceType , SourceKey , BatchKey , OperationType , Flag)

  SELECT NEWID() , 160 , B.OriginalDetailKey , @pBatchKey , 1 , 'FALSE'

  FROM TrxTransaction A With(nolock), TrxTransactionSaleItem B With(nolock)

  WHERE A.TransactionKey = B.TransactionKey

  AND  A.TransactionKey = @pTransactionKey

  AND  B.Type   = 3 --INDICATES THAT THE DELIVERY IS MADE AGAINST THE ORDER

--####

--####

--####

  If @pDebug = 'Y'

  SELECT 'UPDATING THE FULLFILLMENT QUANTITY OF THE SALE IF THE DELIVERY IS MADE AGAINST THE SALE'

  UPDATE  OriginalSalesItem

  SET  OriginalSalesItem.FullfilledQuantity = OriginalSalesItem.FullfilledQuantity + SalesItem.Quantity ,

    OriginalSalesItem.OpenQuantity = OriginalSalesItem.OpenQuantity - SalesItem.Quantity

  FROM TrxTransaction h With(nolock), TrxTransactionSaleItem SalesItem With(nolock), TrxTransactionSaleItem OriginalSalesItem With(nolock)

  WHERE h.TransactionKey   = SalesItem.TransactionKey

  AND  h.TransactionKey   = @pTransactionKey

  AND  SalesItem.OriginalDocumentKey = OriginalSalesItem.TransactionKey

  AND  SalesItem.OriginalDetailKey = OriginalSalesItem.TransactionItemKey

  AND  SalesItem.Type     = 4 --INDICATES THAT THE DELIVERY IS MADE AGAINST THE SALES ITEM

  ---FOR GENERATING THE REPLICATION ENTRY FOR SALE LINES AGAINST WHICH THE DELIVERY IS DONE-------------

  INSERT INTO RepReplicationTransaction(ReplicationTransactionKey , SourceType , SourceKey , BatchKey , OperationType , Flag)

  SELECT NEWID(), 151 , B.OriginalDetailKey , @pBatchKey , 1 , 'FALSE'

  FROM TrxTransaction A With(nolock), TrxTransactionSaleItem B With(nolock)

  WHERE A.TransactionKey = B.TransactionKey

  AND  A.TransactionKey = @pTransactionKey

  AND  B.Type   = 4

  --####

  --####

  If @pDebug = 'Y'

  SELECT 'UPDATE THE ONFULFILLED QUANTITY FOR THE DELIVERY AGAINST SALES IF THE PLAN IS SETUP FOR REDUCING THE INVENTORY ON DELIVERY'

  INSERT INTO InvInventoryItemLog

   ( WarehouseKey

       , ProductKey

       , InQty

       , OutQty

       , OnFulfillmentQuanity

       , SourceType

       , SourceKey

       , SourceDetailKey

       , InventoryItemLogKey

       , UpdateType

       , Created

       , ItemCost

       , LocationKey

       , ReservedQuantity

       , IsProcessed

   )

  SELECT Store.WarehouseKey

       , SalesItem.ProductKey

       , 0

       , CASE WHEN f.InventoryAllocationMethod = 1 THEN SalesItem.Quantity ELSE 0 END

       , (-1) * SalesItem.Quantity

       , 7

       , SalesItem.TransactionKey

       , SalesItem.TransactionItemKey

       , NEWID()

       , 0

       , dbo.GetCompanyDateTime()

       , SalesItem.AverageCost

       , SalesItem.DeliveryLocationKey

       , CASE WHEN f.InventoryAllocationMethod = 1 THEN -1 * SalesItem.Quantity ELSE 0 END As ReservedQuantity

       , 1 As IsProcessed

  From TrxTransaction h With(nolock)

   , TrxTransactionSaleItem SalesItem With(nolock)

   , TrxTransactionSaleItem OriginalSalesItem With(nolock)

   , InvProduct Product With(nolock)

   , TrxTransactionFulfillment f With(nolock)

   , TrxTransactionFulfillmentDetail FD With(nolock)

   , RtlStore Store With(nolock)

  WHERE h.TransactionKey     = SalesItem.TransactionKey

  AND  SalesItem.ProductKey   = Product.ProductKey

  AND  SalesItem.OriginalDocumentKey = OriginalSalesItem.TransactionKey

  AND  SalesItem.OriginalDetailKey   = OriginalSalesItem.TransactionItemKey

  AND  f.FulfillmentKey     =  fd.FulfillmentKey

  AND  fd.TransactionKey     = OriginalSalesItem.TransactionKey

  AND  fd.SourceDetailKey     = OriginalSalesItem.TransactionItemKey

  AND  Store.WarehouseKey     = OriginalSalesItem.DeliveryWarehouseKey

  AND  OriginalSalesItem.HasFulfillment = 'TRUE' --INDICATES THE ORIGINAL SALES HAD THE FULFILLMENT ATTACHED

  AND  Product.IsNonStock     = 'FALSE' --INDICATES THAT THE SALES ITEMS IS A STOCKABLE ITEM

  AND  Product.IsAssembly     = 'FALSE' --INDICATES THAT THE ITEMS IS NOT A ASSEMBLY ITEM

  AND  fd.SourceType     = 0   --INDICATES THAT THE FULFILLMENT IS ON SALES ITEM

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

  AND  h.TransactionKey     = @pTransactionKey

  And  Store.IsDeleted     = 0

  ---Delivery of sales dynamic assembly item

  INSERT INTO InvInventoryItemLog

   ( WarehouseKey , ProductKey , InQty  , OutQty , OnFulfillmentQuanity

       , SourceType , SourceKey , SourceDetailKey , InventoryItemLogKey

       , UpdateType , Created , ItemCost , LocationKey

       , ReservedQuantity , IsProcessed

   )

  SELECT Store.WarehouseKey , dynamicItem.ProductKey  , 0 , CASE WHEN f.InventoryAllocationMethod = 1 THEN dynamicItem.Quantity ELSE 0 END , (-1) * dynamicItem.Quantity As OnFulfillmentQuanity

       , 7 , SalesItem.TransactionKey , SalesItem.TransactionItemKey

       ,NEWID()

       , 0

       , dbo.GetCompanyDateTime()

       , SalesItem.AverageCost

       , SalesItem.DeliveryLocationKey

       , CASE WHEN f.InventoryAllocationMethod = 1 THEN -1 * dynamicItem.Quantity ELSE 0 END As ReservedQuantity

       , 1 As IsProcessed

  From TrxTransaction h With(nolock)

   , TrxTransactionSaleItem SalesItem With(nolock)

   , TrxTransactionSaleItem OriginalSalesItem With(nolock)

   , TrxTransactionDynamicAssemblyItem dynamicItem WITH(NOLOCK)

   , TrxTransactionFulfillment f With(nolock)

   , TrxTransactionFulfillmentDetail FD With(nolock)

   , RtlStore Store With(nolock)

  WHERE h.TransactionKey = SalesItem.TransactionKey

  AND  SalesItem.OriginalDocumentKey = OriginalSalesItem.TransactionKey

  AND  SalesItem.OriginalDetailKey = OriginalSalesItem.TransactionItemKey

  And      dynamicItem.SourceKey = OriginalSalesItem.TransactionItemKey

  And      dynamicItem.TransactionKey = OriginalSalesItem.TransactionKey

  AND  f.FulfillmentKey = fd.FulfillmentKey

  AND  fd.TransactionKey = OriginalSalesItem.TransactionKey

  AND  fd.SourceDetailKey = OriginalSalesItem.TransactionItemKey

  AND  Store.WarehouseKey = OriginalSalesItem.DeliveryWarehouseKey

  AND  OriginalSalesItem.HasFulfillment = 'TRUE' --INDICATES THE ORIGINAL SALES HAD THE FULFILLMENT ATTACHED

  AND  fd.SourceType = 0   --INDICATES THAT THE FULFILLMENT IS ON SALES ITEM

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

  AND  h.TransactionKey = @pTransactionKey

  And  Store.IsDeleted = 0

--####

--####

  --####

  If @pDebug = 'Y'

  SELECT 'UPDATING THE FULLFILLMENT QUANTITY OF THE LAYAWAY - DELIVERY'

  UPDATE o

  SET  FullfilledQuantity = FullfilledQuantity + b.Quantity ,

    OpenQuantity = OpenQuantity - b.Quantity

  FROM TrxTransactionLayaway o ,

  (

  Select sum(s.Quantity) 'Quantity', s.OriginalDocumentKey, s.OriginalDetailKey

  FROM TrxTransaction h With(nolock)

      , TrxTransactionSaleItem s With(nolock)

      , TrxTransactionLayaway o With(nolock)

  WHERE h.TransactionKey = s.TransactionKey

  AND  s.OriginalDocumentKey = o.TransactionKey

  AND  s.OriginalDetailKey = o.TransactionLayawayKey

  AND  h.TransactionKey = @pTransactionKey

  AND  s.Type     = 5 --INDICATES THAT THE DELIVERY IS MADE AGAINST THE LAYAWAY

  GROUP BY s.OriginalDocumentKey , s.OriginalDetailKey

  ) b

  WHERE b.OriginalDocumentKey = o.TransactionKey

  AND  b.OriginalDetailKey = o.TransactionLayawayKey

  IF(@@ERROR <>0)

  BEGIN

  SET @ErrorDesc = 'ERROR WHILE UPDATING FULFILLED QUANTITY FOR LAYAWAY ITEM'

  GOTO ERRORHANDLER

  END

  ---FOR GENERATING THE REPLICATION ENTRY FOR LAYAWAY LINES AGAINST WHICH THE DELIVERY IS DONE-------------

  INSERT INTO RepReplicationTransaction(ReplicationTransactionKey , SourceType , SourceKey , BatchKey , OperationType , Flag)

  SELECT NEWID() , 148 , B.OriginalDetailKey , @pBatchKey , 1 , 'FALSE'

  FROM TrxTransaction A , TrxTransactionSaleItem B

  WHERE A.TransactionKey = B.TransactionKey

  AND  A.TransactionKey = @pTransactionKey

  AND  B.Type   = 5

  --####

  --####

  If @pDebug = 'Y'

  SELECT 'UPDATE THE INVENTORY FOR THE NORMAL LAYBY ITEM.'

  INSERT INTO InvInventoryItemLog

   ( WarehouseKey , ProductKey , InQty , OutQty , OnFulfillmentQuanity ,

    SourceType , SourceKey , SourceDetailKey ,

    InventoryItemLogKey , UpdateType , Created , ItemCost , LocationKey , ReservedQuantity , IsProcessed

   )

  SELECT Store.WarehouseKey, SalesItem.ProductKey, 0 As InQty,

    CASE WHEN f.InventoryAllocationMethod = 1 THEN SalesItem.Quantity ELSE 0 END As OutQty, (-1) * SalesItem.Quantity As OnFulfillmentQuanity,

    7 , SalesItem.TransactionKey , SalesItem.TransactionItemKey ,

    NEWID() , 0 , dbo.GetCompanyDateTime()

    , SalesItem.AverageCost, SalesItem.DeliveryLocationKey , CASE WHEN f.InventoryAllocationMethod = 1 then (-1) * SalesItem.Quantity ELSE 0 END AS ReservedQuantity,

    1 As IsProcessed

  From TrxTransaction h With(nolock)

   , TrxTransactionSaleItem SalesItem With(nolock)

   , TrxTransactionLayaway LayawayItem With(nolock)

   , InvProduct Product With(nolock)

   , TrxTransactionFulfillment f With(nolock)

   , TrxTransactionFulfillmentDetail FD With(nolock)

   , RtlStore Store With(nolock)

  WHERE h.TransactionKey   = SalesItem.TransactionKey

  AND  SalesItem.ProductKey   = Product.ProductKey

  AND  h.TransactionKey   = @pTransactionKey

  AND  Product.IsNonStock   = 'FALSE' --SALES ITEMS IS NOT A NONSTOCK ITEM

  AND  Product.IsAssembly   = 'FALSE' --ITEMS IS NOT A ASSEMBLY ITEM

  AND  SalesItem.OriginalDocumentKey = LayawayItem.TransactionKey

  AND  SalesItem.OriginalDetailKey = LayawayItem.TransactionLayawayKey

  AND  f.FulfillmentKey   =  fd.FulfillmentKey

  AND  fd.TransactionKey   = LayawayItem.TransactionKey

  AND  fd.SourceDetailKey   = LayawayItem.TransactionLayawayKey

  AND  LayawayItem.DeliveryWarehouseKey = Store.WarehouseKey

  AND  fd.SourceType     = 2 --INDICATES THE FULFILLMENT IS FOR LAYAWAY

  AND  SalesItem.Type     = 5 --INDICATES THE DELIVERY IS AGAINST LAYAWAY

  And  Store.IsDeleted     = 0

--####

--####

--################################

  --################################

  If @pDebug = 'Y'

  SELECT 'HANDLING OF THE ORDER ITEM - ASSEMBLY'

  ;WITH RecursionCTE (AssemblyComponentKey, ParentProductKey, ProductKey, Quantity, InventoryItemKey, WareHouseKey, TransactionKey, TransactionItemKey, AverageCost, LocationKey, ReservedQuantity)

  as

   (

    SELECT   Assembly.AssemblyComponentKey , Assembly.ParentProductKey , Assembly.ProductKey ,

    CONVERT(DECIMAL(20 , 5) , Assembly.Quantity * Detail.Quantity) , Item.InventoryItemKey , Detail.WareHouseKey ,

     Detail.TransactionKey , Detail.TransactionItemKey ,

     Detail.AverageCost , Detail.DeliveryLocationKey,

    case when fullfillment.ReserveQuantity = 1 then CONVERT(DECIMAL(20 , 5) , Assembly.Quantity * Detail.Quantity) else 0 End

    FROM    InvAssemblyComponent Assembly With(nolock),

       TrxTransaction Sale With(nolock) ,

       TrxTransactionSaleItem Detail With(nolock),

       InvProduct Product With(nolock),

       InvInventoryItem Item With(nolock),

       TrxTransactionFulfillment fullfillment With(nolock),

       TrxTransactionFulfillmentDetail fulfillmentDetail With(nolock)

  Where Assembly.ParentProductKey   = Detail.ProductKey

  And  Detail.ProductKey     = Product.ProductKey

  And  Sale.TransactionKey     = Detail.TransactionKey

  AND  Item.WarehouseKey     = Detail.WarehouseKey

  AND  Assembly.ProductKey     = Item.ProductKey

  And  Product.IsAssembly     = 'TRUE'   --INDICATES THAT THE PARENTPRODUCT IS ASSEMBLY ITEM

  AND  Detail.Type       = 3   --INDICATES THAT THE DELIVERY IS MADE AGAINST THE SALES ORDER

  And  Sale.TransactionKey     = @pTransactionKey

  AND    fullfillment.FulfillmentKey = fulfillmentDetail.FulfillmentKey

  AND    fullfillment.TransactionKey = Detail.OriginalDocumentKey

  And    fulfillmentDetail.SourceDetailKey = Detail.OriginalDetailKey

  And    fulfillmentDetail.SourceType = 1

    UNION ALL

    SELECT R1.AssemblyComponentKey

    , R1.ParentProductKey

    ,  R1.ProductKey

    , convert(decimal(20 , 5) , R1.Quantity * R2.Quantity)

    , R3.InventoryItemKey

    , R2.WareHouseKey

    , R2.TransactionKey

    , R2.TransactionItemKey

    , R3.AverageCost

    , R2.LocationKey

    , convert(decimal(20 , 5) , R1.Quantity * R2.ReservedQuantity)

  FROM InvAssemblyComponent AS R1 With(nolock)

  JOIN RecursionCTE AS R2 ON R1.ParentProductKey = R2.ProductKey

  JOIN InvInventoryItem AS R3 With(nolock) ON R1.ProductKey = R3.ProductKey

  AND  R3.WareHouseKey = R2.WarehouseKey

    )

  SELECT A.* INTO #AssemblyComponents

  FROM RecursionCTE A , InvProduct B With(nolock)

  WHERE A.ProductKey = B.ProductKey

  And  B.IsAssembly = 'FALSE'

  And  B.IsNonStock = 'FALSE'

-----------------------FOR GENERATING THE DELTA ENTRY AND REPLICATION ENTRY-----------------------

INSERT INTO InvInventoryItemLog

  ( WarehouseKey , ProductKey ,

   InQty , OutQty , OnFulfillmentQuanity ,

   SourceType , SourceKey , SourceDetailKey ,

   InventoryItemLogKey , UpdateType , Created , ItemCost , LocationKey , IsProcessed, ReservedQuantity

  )

SELECT WarehouseKey , ProductKey ,

  0 , Quantity , -1* Quantity ,

  7 , TransactionKey , TransactionItemKey ,

  NEWID() , 0 , dbo.GetCompanyDateTime() , AverageCost , LocationKey ,

  1 As IsProcessed --REDUCE THE INVENTORY OF BOTH IN STORE AND AVAILABLE

   , -1 * ISNULL(ReservedQuantity, 0)

From #AssemblyComponents

  --################################

--################################

If @pDebug = 'Y'

SELECT 'HANDLING DELIVERY OF THE SALE ITEM - ASSEMBLY'

  ;WITH RecursionCTE (AssemblyComponentKey, ParentProductKey, ProductKey, Quantity, InventoryItemKey, WareHouseKey, TransactionKey, TransactionItemKey, InventoryAllocationMethod, AverageCost, LocationKey)

  as

   (

    SELECT   Assembly.AssemblyComponentKey , Assembly.ParentProductKey , Assembly.ProductKey ,

    CONVERT(DECIMAL(20 , 5) , Assembly.Quantity * Detail.Quantity) , Item.InventoryItemKey , Detail.WareHouseKey ,

     Detail.TransactionKey , Detail.TransactionItemKey ,

     Fulfillment.InventoryAllocationMethod ,

     Detail.AverageCost , Detail.DeliveryLocationKey

    FROM     InvAssemblyComponent Assembly With(nolock)

       , TrxTransaction Sale With(nolock)

       , TrxTransactionSaleItem Detail With(nolock)

       , TrxTransactionSaleItem OriginalSaleDetail With(nolock)

       , InvProduct Product With(nolock)

       , TrxTransactionFulfillment Fulfillment With(nolock)

       , TrxTransactionFulfillmentDetail FulfillmentDetail With(nolock)

       , InvInventoryItem Item With(nolock)

  Where Assembly.ParentProductKey   = Detail.ProductKey

  And  Detail.ProductKey     = Product.ProductKey

  And  Sale.TransactionKey     = Detail.TransactionKey

  AND  Detail.OriginalDocumentKey   = OriginalSaleDetail.TransactionKey

  AND  Detail.OriginalDetailKey   = OriginalSaleDetail.TransactionItemKey

  AND  Fulfillment.FulfillmentKey   =  FulfillmentDetail.FulfillmentKey

  AND  FulfillmentDetail.TransactionKey = OriginalSaleDetail.TransactionKey

  AND  FulfillmentDetail.SourceDetailKey = OriginalSaleDetail.TransactionItemKey

  AND  Item.WarehouseKey     = OriginalSaleDetail.WarehouseKey

  AND  Assembly.ProductKey     = Item.ProductKey

  AND  FulfillmentDetail.SourceType   = 0   --INDICATES THAT THE FULFILLMENT IS ON SALE ITEM

  AND  OriginalSaleDetail.HasFulfillment = 'TRUE'   --INDICATES THAT THE ORIGINAL SALES HAD THE FULFILLMENT ATTACHED

  And  Product.IsAssembly     = 'TRUE'   --INDICATES THAT THE PARENTPRODUCT IS ASSEMBLY ITEM

  AND  Detail.Type       = 4   --INDICATES THAT THE DELIVERY IS MADE AGAINST SALE

  And  Sale.TransactionKey     = @pTransactionKey

    UNION ALL

    SELECT R1.AssemblyComponentKey ,

      R1.ParentProductKey ,

      R1.ProductKey ,

      convert(decimal(20 , 5) , R1.Quantity * R2.Quantity) ,

      R3.InventoryItemKey ,

      R2.WareHouseKey ,

      R2.TransactionKey ,

      R2.TransactionItemKey ,

      R2.InventoryAllocationMethod ,

      R3.AverageCost ,

      R2.LocationKey

  FROM InvAssemblyComponent AS R1 With(nolock)

  JOIN RecursionCTE AS R2 ON R1.ParentProductKey = R2.ProductKey

  JOIN InvInventoryItem AS R3 With(nolock) ON R1.ProductKey = R3.ProductKey

  AND R3.WareHouseKey = R2.WarehouseKey

    )

  SELECT A.* INTO #AssemblyComponents1

  FROM RecursionCTE A , InvProduct B With(nolock)

  WHERE A.ProductKey = B.ProductKey

  And  B.IsAssembly = 'FALSE'

  And  B.IsNonStock = 'FALSE'

  -----------------------FOR GENERATING THE DELTA ENTRY AND REPLICATION ENTRY-----------------------

  INSERT INTO InvInventoryItemLog

   (

    WarehouseKey , ProductKey ,

    InQty , OutQty , OnFulfillmentQuanity ,

    SourceType , SourceKey , SourceDetailKey ,

    InventoryItemLogKey , UpdateType , Created

       , ItemCost , LocationKey , ReservedQuantity , IsProcessed

   )

  SELECT WarehouseKey , ProductKey ,

    0, CASE WHEN InventoryAllocationMethod = 1 THEN Quantity ELSE 0 END, -1 * Quantity As OnFulfillmentQuanity,

    7 , TransactionKey , TransactionItemKey ,

    NEWID() , 0 , dbo.GetCompanyDateTime()

    , AverageCost, LocationKey, CASE WHEN InventoryAllocationMethod = 1 THEN -1 * Quantity ELSE 0 END As ReservedQuantity,

    1 As IsProcessed

  FROM #AssemblyComponents1

--################################

--################################

If @pDebug = 'Y'

SELECT 'HANDLING DELIVERY OF THE LAYAWAY ITEM - ASSEMBLY'

  ;WITH RecursionCTE (AssemblyComponentKey, ParentProductKey, ProductKey, Quantity, InventoryItemKey, WareHouseKey, TransactionKey, TransactionItemKey, InventoryAllocationMethod, AverageCost, LocationKey)

  as

   (

    SELECT   Assembly.AssemblyComponentKey , Assembly.ParentProductKey , Assembly.ProductKey ,

    CONVERT(DECIMAL(20 , 5) , Assembly.Quantity * Detail.Quantity) , Item.InventoryItemKey , Detail.WareHouseKey ,

     Detail.TransactionKey , Detail.TransactionItemKey ,

     Fulfillment.InventoryAllocationMethod ,

     Detail.AverageCost

       , Detail.DeliveryLocationKey

    FROM     InvAssemblyComponent Assembly With(nolock)

       , TrxTransaction Sale With(nolock)

       , TrxTransactionSaleItem Detail With(nolock)

       , TrxTransactionLayaway OriginalSaleDetail With(nolock)

       , InvProduct Product With(nolock)

       , TrxTransactionFulfillment Fulfillment With(nolock)

       , TrxTransactionFulfillmentDetail FulfillmentDetail With(nolock)

       , InvInventoryItem Item With(nolock)

  Where Assembly.ParentProductKey   = Detail.ProductKey

  And  Detail.ProductKey     = Product.ProductKey

  And  Sale.TransactionKey     = Detail.TransactionKey

  AND  Detail.OriginalDocumentKey   = OriginalSaleDetail.TransactionKey

  AND  Detail.OriginalDetailKey   = OriginalSaleDetail.TransactionLayawayKey

  AND  Fulfillment.FulfillmentKey   =  FulfillmentDetail.FulfillmentKey

  AND  FulfillmentDetail.TransactionKey = OriginalSaleDetail.TransactionKey

  AND  FulfillmentDetail.SourceDetailKey = OriginalSaleDetail.TransactionLayawayKey

  AND  Item.WarehouseKey     = OriginalSaleDetail.WarehouseKey

  AND  Assembly.ProductKey     = Item.ProductKey

  AND  FulfillmentDetail.SourceType   = 2   --INDICATES THAT THE FULFILLMENT IS ON LAYAWAY ITEM

  AND  OriginalSaleDetail.HasFulfillment = 'TRUE'   --INDICATES THAT THE ORIGINAL SALES HAD THE FULFILLMENT ATTACHED

  And  Product.IsAssembly     = 'TRUE'   --INDICATES THAT THE PARENTPRODUCT IS ASSEMBLY ITEM

  AND  Detail.Type       = 5   --INDICATES THAT THE DELIVERY IS MADE AGAINST LAYAWAY

  And  Sale.TransactionKey     = @pTransactionKey

    UNION ALL

    SELECT R1.AssemblyComponentKey

      , R1.ParentProductKey

      , R1.ProductKey

      , convert(decimal(20 , 5) , R1.Quantity * R2.Quantity)

      , R3.InventoryItemKey

      , R2.WareHouseKey

      , R2.TransactionKey

      , R2.TransactionItemKey

      , R2.InventoryAllocationMethod

      , R3.AverageCost

      , R2.LocationKey

  FROM InvAssemblyComponent AS R1 With(nolock)

  JOIN RecursionCTE AS R2 ON R1.ParentProductKey = R2.ProductKey

  JOIN InvInventoryItem AS R3 With(nolock) ON R1.ProductKey = R3.ProductKey

  AND R3.WareHouseKey = R2.WarehouseKey

    )

  SELECT A.* INTO #AssemblyComponents2

  FROM RecursionCTE A , InvProduct B With(nolock)

  WHERE A.ProductKey = B.ProductKey

  And  B.IsAssembly = 'FALSE'

  And  B.IsNonStock = 'FALSE'

  -----------------------FOR GENERATING THE DELTA ENTRY AND REPLICATION ENTRY-----------------------

  INSERT INTO InvInventoryItemLog

   (

    WarehouseKey , ProductKey ,

    InQty , OutQty , OnFulfillmentQuanity ,

    SourceType , SourceKey , SourceDetailKey ,

    InventoryItemLogKey , UpdateType , Created

       , ItemCost , LocationKey , ReservedQuantity , IsProcessed

   )

  SELECT WarehouseKey , ProductKey ,

    0 , CASE WHEN InventoryAllocationMethod = 1 THEN Quantity ELSE 0 END , -1 * Quantity ,

    7 , TransactionKey , TransactionItemKey ,

    NEWID(), 0 , dbo.GetCompanyDateTime()

    ,AverageCost, LocationKey , CASE WHEN InventoryAllocationMethod = 1 THEN -1 * Quantity ELSE 0 END As ReservedQuantity,

    1 As IsProcessed --update both instore and available

  FROM #AssemblyComponents2

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

  RETURN

ERRORHANDLER:

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

RETURN

END