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

TrxTransactionSaleItem

Table

Stores sale/refund/delivery items attached to a transaction

1

CREATE PROCEDURE [dbo].[TrxInventoryUpdatesSaleRefund]

(

@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

  ---############ 1.0 SALE REFUND ####################################################################################

  --#######

  If @pDebug = 'Y'

  Select 'Updating the fulFillment Quantities if the original sale item was marked for fulFillment'

  Update fd

    Set fd.Quantity = fd.Quantity - SalesItem.Quantity

  FROM     TrxTransaction h With(nolock)

       , TrxTransactionSaleItem SalesItem With(nolock)

       , TrxTransactionSaleItem OriginalSalesItem With(nolock)

       , TrxTransactionFulfillment f With(nolock)

       , TrxTransactionFulfillmentDetail fd With(nolock)

  WHERE h.TransactionKey     = SalesItem.TransactionKey

  AND  SalesItem.OriginalDocumentKey   = OriginalSalesItem.TransactionKey

  AND  SalesItem.OriginalDetailKey   = OriginalSalesItem.TransactionItemKey

  AND  OriginalSalesItem.TransactionItemKey = fd.SourceDetailKey

  AND  f.FulfillmentKey     = fd.FulfillmentKey

  AND  f.TransactionKey     = OriginalSalesItem.TransactionKey

  AND  OriginalSalesItem.HasFulfillment = 'TRUE' --INDICATES THAT THE ITEM HAS FULFILLMENT ATTACHED TO IT

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

  AND  SalesItem.Type     = 1 --INDICATES THAT THE SALE ITEM IS REFUNDED

  AND  h.TransactionKey     = @pTransactionKey

  IF(@@ERROR <>0)

  BEGIN

  SET @ErrorDesc = 'Error while updating the fulFillment quantities for refund item.'

  GOTO ERRORHANDLER

  END

  ---##

  --#######

  If @pDebug = 'Y'

  Select 'UPDATING INVENTORY FOR REFUND ITEMS'

  Update OriginalSalesItem

  Set  OriginalSalesItem.RefundedQuantity = OriginalSalesItem.RefundedQuantity + 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  SalesItem.OriginalDocumentKey = OriginalSalesItem.TransactionKey

  AND  SalesItem.OriginalDetailKey = OriginalSalesItem.TransactionItemKey

  AND  SalesItem.Type     = 1 --INDICATES THAT THE SALE ITEM IS REFUNDED

  AND  h.TransactionKey   = @pTransactionKey

  IF(@@ERROR <>0)

  BEGIN

  SET @ErrorDesc = 'ERROR WHILE UPDATING REFUNDED QUANTITIES.'

  GOTO ERRORHANDLER

  END

  ---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, SalesItem.OriginalDetailKey, @pBatchKey,1, 'FALSE'

  FROM TrxTransaction h With(nolock)

   , TrxTransactionSaleItem SalesItem With(nolock)

   , TrxTransactionSaleItem OriginalSalesItem With(nolock)

  WHERE h.TransactionKey   = SalesItem.TransactionKey

  AND  SalesItem.OriginalDocumentKey = OriginalSalesItem.TransactionKey

  AND  SalesItem.OriginalDetailKey = OriginalSalesItem.TransactionItemKey

  AND  SalesItem.Type     = 1 --INDICATES THAT THE SALE ITEM IS REFUNDED

  AND  h.TransactionKey   = @pTransactionKey

  IF(@Error <>0)

  BEGIN

  SET @ErrorDesc = 'ERROR WHILE GENERATING THE REPLICATION ENTRY - FULFILLED QUANTITY FOR SALE ITEM.'

  GOTO ERRORHANDLER

  END

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

  --#######

  INSERT INTO InvInventoryItemLog

  (

   WarehouseKey, ProductKey, InQty, OutQty,

   SourceType, SourceKey, SourceDetailKey,

   InventoryItemLogKey, UpdateType, Created

   , ItemCost, LocationKey, IsProcessed

  )

  SELECT SalesItem.WarehouseKey, SalesItem.ProductKey, SalesItem.Quantity, 0,

    7, SalesItem.TransactionKey, SalesItem.TransactionItemKey,

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

    SalesItem.DeliveryLocationKey, 1 As IsProcessed --UPDATE THE BOTH AVAILABLE AND INSTORE

  FROM TrxTransaction h With (NOLOCK)

   , TrxTransactionSaleItem SalesItem With (NOLOCK)

   , TrxTransactionSaleItem OriginalSalesItem With (NOLOCK)

   , InvProduct Product With(nolock)

  WHERE h.TransactionKey     = SalesItem.TransactionKey

  AND  SalesItem.OriginalDocumentKey = OriginalSalesItem.TransactionKey

  AND  SalesItem.OriginalDetailKey   = OriginalSalesItem.TransactionItemKey

  AND  SalesItem.ProductKey   = Product.ProductKey

  AND  OriginalSalesItem.HasFulfillment = 'FALSE' --INDICATES THAT THE ITEM HAS NO FULFILLMENT ATTACHED TO IT

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

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

  AND  SalesItem.Type     = 1 --INDICATES THAT THE ITEM IS REFUNDED

  AND  h.TransactionKey     = @pTransactionKey

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

  --#######

  INSERT INTO InvInventoryItemLog

  (

   WarehouseKey, ProductKey, InQty, OutQty,

   SourceType, SourceKey, SourceDetailKey,

   InventoryItemLogKey, UpdateType, Created

   , ItemCost, OnFulfillmentQuanity, LocationKey, ReservedQuantity, IsProcessed

  )

  SELECT SalesItem.WarehouseKey, SalesItem.ProductKey, CASE WHEN F.InventoryAllocationMethod = 0 THEN SalesItem.Quantity ELSE 0 END, 0 As OutQty,

    7, SalesItem.TransactionKey, SalesItem.TransactionItemKey

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

    , OriginalSaleItem.AverageCost, -1 * SalesItem.Quantity As OnFulfillmentQuanity, 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 OriginalSaleItem With(nolock)

   , TrxTransactionFulfillment f With(nolock)

   , TrxTransactionFulfillmentDetail fd With(nolock)

   , InvProduct Product With(nolock)

   , RtlStore Store With(nolock)

  WHERE h.TransactionKey     = SalesItem.TransactionKey

  AND  SalesItem.OriginalDocumentKey       = OriginalSaleItem.TransactionKey

  AND  SalesItem.OriginalDetailKey   = OriginalSaleItem.TransactionItemKey

  AND  Product.ProductKey     = SalesItem.ProductKey

  AND  OriginalSaleItem.TransactionItemKey = fd.SourceDetailKey

  AND  f.FulfillmentKey     = fd.FulfillmentKey

  AND  f.TransactionKey     = OriginalSaleItem.TransactionKey

  AND  h.StoreKey     = Store.StoreKey

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

  AND  OriginalSaleItem.HasFulfillment = 'TRUE'       --INDICATES THAT THE ITEM HAS FULFILLMENT ATTACHED TO IT

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

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

  AND  SalesItem.Type     = 1         --INDICATES THAT THE ITEM IS REFUNDED

  AND  OriginalSaleItem.DeliveryWarehouseKey = Store.WarehouseKey --INDICATES THAT THE DELIVERY WAS EXPECTED FROM THE SAME STORE ONLY

  AND  h.TransactionKey     = @pTransactionKey

  And  Store.IsDeleted = 0

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

  ---#####

  INSERT INTO InvInventoryItemLog

  (

   WarehouseKey, ProductKey, InQty, OutQty,

   SourceType, SourceKey, SourceDetailKey,

   InventoryItemLogKey, UpdateType, Created

   , ItemCost, OnFulfillmentQuanity, LocationKey, ReservedQuantity, IsProcessed

  )

  SELECT OriginalSaleItem.WarehouseKey, OriginalSaleItem.ProductKey, CASE WHEN f.InventoryAllocationMethod = 0 then SalesItem.Quantity else 0 end, 0 As OutQty,

    7, SalesItem.TransactionKey, SalesItem.TransactionItemKey,

    NEWID(), 0, dbo.GetCompanyDateTime()

    , OriginalSaleItem.AverageCost, -1 * SalesItem.Quantity, 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 OriginalSaleItem With(nolock)

   , TrxTransactionFulfillment f With(nolock)

   , TrxTransactionFulfillmentDetail fd With(nolock)

   , InvProduct Product With(nolock)

   , RtlStore Store With(nolock)

  WHERE h.TransactionKey     = SalesItem.TransactionKey

  AND  SalesItem.OriginalDocumentKey = OriginalSaleItem.TransactionKey

  AND  SalesItem.OriginalDetailKey   = OriginalSaleItem.TransactionItemKey

  AND  Product.ProductKey     = SalesItem.ProductKey

  AND  OriginalSaleItem.TransactionItemKey = fd.SourceDetailKey

  AND  f.FulfillmentKey     = fd.FulfillmentKey

  AND  f.TransactionKey     = OriginalSaleItem.TransactionKey

  AND  h.StoreKey       = Store.StoreKey

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

  AND  OriginalSaleItem.HasFulfillment = 'TRUE'   --INDICATES THAT THE ITEM HAS FULFILLMENT ATTACHED TO IT

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

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

  AND  SalesItem.Type     = 1   --INDICATES THAT THE ITEM IS REFUNDED

  AND  OriginalSaleItem.DeliveryWarehouseKey != Store.WarehouseKey --INDICATES THAT THE DELIVERY WAS EXPECTED FROM THE SAME STORE ONLY

  AND  h.TransactionKey     = @pTransactionKey

  And  Store.IsDeleted = 0

  --#######

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

  INSERT INTO InvInventoryItemLog

   ( WarehouseKey, ProductKey, InQty, OutQty,

    SourceType, SourceKey, SourceDetailKey,

    InventoryItemLogKey, UpdateType, Created, ItemCost, LocationKey, IsProcessed

   )

  SELECT SalesItem.WarehouseKey, SalesItem.ProductKey, SalesItem.Quantity, 0,

    7, SalesItem.TransactionKey, SalesItem.TransactionItemKey,

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

    SalesItem.DeliveryLocationKey, 1 As IsProcessed --UPDATE BOTH THE QUANITITES

  FROM TrxTransaction t With (NOLOCK)

   , TrxTransactionSaleItem SalesItem With (NOLOCK)

   , InvProduct Product With (NOLOCK)

  WHERE t.TransactionKey   = SalesItem.TransactionKey

  AND  Product.ProductKey   = SalesItem.ProductKey

  AND  Product.IsNonStock   = 'FALSE'

  AND  Product.IsAssembly   = 'FALSE'

  AND  SalesItem.OriginalDocumentKey = '0'

  AND  SalesItem.OriginalDetailKey = '0'

  AND  SalesItem.Type     = 1

  AND  t.TransactionKey   = @pTransactionKey

  IF(@@ERROR <>0)

  BEGIN

  SET @ErrorDesc = 'ERROR WHILE UPDATING INVENTORY FOR LINE REFUNDS.'

  GOTO ERRORHANDLER

  END

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

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

INSERT INTO InvInventoryItemLog

 (

  WarehouseKey, ProductKey, InQty, OutQty, SourceType, SourceKey, SourceDetailKey,

  InventoryItemLogKey, UpdateType, Created, ItemCost, LocationKey, IsProcessed

 )

SELECT SalesItem.WarehouseKey, DynamicItem.ProductKey, DynamicItem.Quantity, 0, 7, DynamicItem.TransactionKey, DynamicItem.SourceKey,

NEWID(), 0, dbo.GetCompanyDateTime(), ISNULL(item.AverageCost,0) ,

 SalesItem.DeliveryLocationKey, 1 As IsProcessed --UPDATE BOTH THE QUANITITES

FROM TrxTransaction Trx WITH (NOLOCK),

   TrxTransactionSaleItem SalesItem WITH (NOLOCK),

   TrxTransactionDynamicAssemblyItem DynamicItem WITH (NOLOCK),

   InvProduct Product WITH (NOLOCK),

   InvInventoryItem Item With (nolock)

WHERE Trx.TransactionKey   = SalesItem.TransactionKey

AND  SalesItem.TransactionItemKey = DynamicItem.SourceKey

AND  SalesItem.Type     = DynamicItem.SourceType

AND  Product.ProductKey   = DynamicItem.ProductKey

AND  Product.IsNonStock   = 'FALSE'

AND  Product.IsAssembly   = 'FALSE'

AND  SalesItem.Type     = 1

AND  DynamicItem.ProductKey   = Item.ProductKey

And  SalesItem.WarehouseKey   =    Item.WarehouseKey

AND  Trx.TransactionKey   = @pTransactionKey

IF(@@ERROR <>0)

BEGIN

  SET @ErrorDesc = 'ERROR WHILE UPDATING INVENTORY FOR LINE REFUNDS.'

  GOTO ERRORHANDLER

END

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

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

IF EXISTS(SELECT 1 FROM TrxTransactionSaleItem A With(nolock), InvProduct B With(nolock) WHERE A.ProductKey = B.ProductKey And A.Type =1 And B.IsAssembly = 1 And A.TransactionKey = @pTransactionKey And IsExchange = 0)

BEGIN   ---Begining of  Assembly Block

  -- 1.########

      ;WITH RecursionCTE (AssemblyComponentKey,ParentProductKey,ProductKey,Quantity, InventoryItemKey, WareHouseKey, TransactionKey, TransactionItemKey, 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, ISNULL(Item.AverageCost,0) , Detail.DeliveryLocationKey

    FROM     InvAssemblyComponent Assembly With(nolock)

    , TrxTransaction Sale With(nolock)

    , TrxTransactionSaleItem Detail With(nolock)

    , InvProduct Product With(nolock)

    , InvInventoryItem Item 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  Detail.OriginalDocumentKey = '0'   --INDICATES THAT THIS IS LINE REFUND

  AND  Detail.OriginalDetailKey = '0'   --INDICATES THAT THIS IS LINE REFUND

  AND  Detail.HasFulfillment   = 'FALSE' --INDICATES THAT THE PARENT PRODUCT ITEM HAS NO FULFILLMENT ATTACHED

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

  AND  Detail.Type     = 1   --INDICATES THAT THIS IS REFUND ITEM

  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,

     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 #AssemblyComponents

  FROM RecursionCTE A, InvProduct B With(nolock)

  WHERE A.ProductKey = B.ProductKey

  AND  B.IsAssembly = 'FALSE'

  AND  B.IsNonStock = 'FALSE'

  INSERT INTO InvInventoryItemLog

   ( WarehouseKey, ProductKey, InQty, OutQty,

    SourceType, SourceKey, SourceDetailKey,

    InventoryItemLogKey, UpdateType, Created, ItemCost, LocationKey, IsProcessed

   )

  SELECT WarehouseKey, ProductKey, Quantity, 0,

    7, TransactionKey, TransactionItemKey,

    NEWID(), 0, dbo.GetCompanyDateTime(), AverageCost, LocationKey, 1 As IsProcessed --UPDATE BOTH THE QUANITITES

  FROM #AssemblyComponents

    --########

    --2. #######################

     ;WITH RecursionCTE (AssemblyComponentKey,ParentProductKey,ProductKey,Quantity, InventoryItemKey, WareHouseKey, TransactionKey, TransactionItemKey, 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, ISNULL(Item.AverageCost, 0), Detail.DeliveryLocationKey

    FROM     InvAssemblyComponent Assembly With(nolock)

   , TrxTransaction Sale With(nolock)

   , TrxTransactionSaleItem Detail With(nolock)

   , TrxTransactionSaleItem OriginalSaleDetails With(nolock)

   , InvProduct Product With(nolock)

   , InvInventoryItem Item 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  Detail.OriginalDocumentKey = OriginalSaleDetails.TransactionKey --INDICATES THAT THIS IS LINE REFUND

  AND  Detail.OriginalDetailKey   = OriginalSaleDetails.TransactionItemKey --INDICATES THAT THIS IS LINE REFUND

  AND  OriginalSaleDetails.HasFulfillment = 'FALSE'         --INDICATES THAT THE FULFILLMENT WAS NOT APPLIED ON ORIGINAL ITEM

  AND  Detail.HasFulfillment   = 'FALSE'         --INDICATES THAT THE PARENT PRODUCT ITEM HAS NO FULFILLMENT ATTACHED

  AND  Product.IsAssembly     = 'TRUE'         --INDICATES THAT THE PARENT PRODUCT IS ASSEMBLY ITEM

  AND  Detail.Type       = 1         --INDICATES THAT THIS IS REFUND ITEM

  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,

     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'

    INSERT INTO InvInventoryItemLog

   ( WarehouseKey, ProductKey, InQty, OutQty,

    SourceType, SourceKey, SourceDetailKey,

    InventoryItemLogKey,UpdateType, Created, ItemCost, LocationKey, IsProcessed

   )

  SELECT WarehouseKey, ProductKey, Quantity, 0,

    7, TransactionKey, TransactionItemKey,

    NEWID(), 0, dbo.GetCompanyDateTime(), AverageCost, LocationKey, 1 As IsProcessed --UPDATE BOTH INSTORE AND AVAILABLE

  FROM #AssemblyComponents1

    --######################### END ITEMS WITHOUT FULFILLMENT ON THE ORIGINAL ITEMS#############################

    --3. #######

  ---3.1#####

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

    ISNULL(Item.AverageCost, 0), Detail.DeliveryLocationKey

    FROM     InvAssemblyComponent Assembly With(nolock)

     , TrxTransaction Sale With(nolock)

     , TrxTransactionSaleItem Detail With(nolock)

     , TrxTransactionSaleItem OriginalSaleDetails With(nolock)

     , InvProduct Product With(nolock)

     , InvInventoryItem Item With(nolock)

     , TrxTransactionFulfillment Fulfillment With(nolock)

     , TrxTransactionFulfillmentDetail FulfillmentDetail With(nolock)

     , RtlStore Store 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  Fulfillment.FulfillmentKey   = FulfillmentDetail.FulfillmentKey

  AND  Fulfillment.TransactionKey   = OriginalSaleDetails.TransactionKey

  AND  FulfillmentDetail.SourceDetailKey =   OriginalSaleDetails.TransactionItemKey

  AND  Sale.StoreKey     = Store.StoreKey

  AND  Detail.OriginalDocumentKey   = OriginalSaleDetails.TransactionKey --INDICATES THAT THIS IS LINE REFUND

  AND  Detail.OriginalDetailKey   = OriginalSaleDetails.TransactionItemKey --INDICATES THAT THIS IS LINE REFUND

  AND  OriginalSaleDetails.HasFulfillment = 'TRUE'         --INDICATES THAT THE FULFILLMENT WAS NOT APPLIED ON ORIGINAL ITEM

  AND  Detail.HasFulfillment   = 'FALSE'         --INDICATES THAT THE PARENT PRODUCT ITEM HAS NO FULFILLMENT ATTACHED

  AND  Product.IsAssembly     = 'TRUE'         --INDICATES THAT THE PARENT PRODUCT IS ASSEMBLY ITEM

  AND  Detail.Type       = 1         --INDICATES THAT THIS IS REFUND ITEM

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

  AND  OriginalSaleDetails.DeliveryWarehouseKey = Store.WarehouseKey     --WILL ENSURE THAT THE ITEM WHICH ARE MARKED TO BE DELIVERD FROM THE REFUND WAREHOUSE

  AND  Sale.TransactionKey     = @pTransactionKey

  And  Store.IsDeleted = 0

    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'

    INSERT INTO InvInventoryItemLog

   ( WarehouseKey, ProductKey, InQty, OutQty, OnFulfillmentQuanity,

    SourceType, SourceKey, SourceDetailKey,

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

   )

  SELECT WarehouseKey, ProductKey, Case When InventoryAllocationMethod = 0 then Quantity Else 0 End, 0, -1 * Quantity,

    7, TransactionKey, TransactionItemKey,

    NEWID(), 0, dbo.GetCompanyDateTime(), AverageCost, LocationKey, 1 As IsProcessed

    , Case When InventoryAllocationMethod = 1 then -1 * Quantity Else 0 End

  FROM #AssemblyComponents2

    --##

    ---3.2#####

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

    ISNULL(Item.AverageCost, 0),

    Detail.DeliveryLocationKey

    FROM     InvAssemblyComponent Assembly With(nolock)

   , TrxTransaction Sale With(nolock)

   , TrxTransactionSaleItem Detail With(nolock)

   , TrxTransactionSaleItem OriginalSaleDetails With(nolock)

   , InvProduct Product With(nolock)

   , InvInventoryItem Item With(nolock)

   , TrxTransactionFulfillment Fulfillment With(nolock)

   , TrxTransactionFulfillmentDetail FulfillmentDetail With(nolock)

   , RtlStore Store 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  Fulfillment.FulfillmentKey   = FulfillmentDetail.FulfillmentKey

  AND  Fulfillment.TransactionKey   = OriginalSaleDetails.TransactionKey

  AND  FulfillmentDetail.SourceDetailKey =   OriginalSaleDetails.TransactionItemKey

  AND  Sale.StoreKey     = Store.StoreKey

  AND  Detail.OriginalDocumentKey   = OriginalSaleDetails.TransactionKey --INDICATES THAT THIS IS LINE REFUND

  AND  Detail.OriginalDetailKey   = OriginalSaleDetails.TransactionItemKey --INDICATES THAT THIS IS LINE REFUND

  AND  OriginalSaleDetails.HasFulfillment = 'TRUE'         --INDICATES THAT THE FULFILLMENT WAS NOT APPLIED ON ORIGINAL ITEM

  AND  Detail.HasFulfillment   = 'FALSE'         --INDICATES THAT THE PARENT PRODUCT ITEM HAS NO FULFILLMENT ATTACHED

  AND  Product.IsAssembly     = 'TRUE'         --INDICATES THAT THE PARENT PRODUCT IS ASSEMBLY ITEM

  AND  Detail.Type       = 1         --INDICATES THAT THIS IS REFUND ITEM

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

  AND  OriginalSaleDetails.DeliveryWarehouseKey != Store.WarehouseKey   --WILL ENSURE THAT THE ITEM WHICH ARE MARKED TO BE DELIVERD FROM THE REFUND WAREHOUSE

  AND  Sale.TransactionKey     = @pTransactionKey

  And  Store.IsDeleted = 0

    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 #AssemblyComponents3

  FROM RecursionCTE A, InvProduct B With(nolock)

  WHERE A.ProductKey = B.ProductKey

  AND  B.IsAssembly = 'FALSE'

  AND  B.IsNonStock = 'FALSE'

    INSERT INTO InvInventoryItemLog

   ( WarehouseKey, ProductKey, InQty, OutQty, OnFulfillmentQuanity,

    SourceType, SourceKey, SourceDetailKey,

    InventoryItemLogKey, UpdateType, Created

    , ItemCost, LocationKey , ReservedQuantity, IsProcessed

   )

  SELECT WarehouseKey, ProductKey, CASE WHEN InventoryAllocationMethod = 0 THEN Quantity ELSE 0 END, 0 AS OutQty, -1 * Quantity As OnFulfillmentQuanity,

    7, TransactionKey, TransactionItemKey,

    NEWID(), 1, dbo.GetCompanyDateTime()

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

  FROM #AssemblyComponents3

    ---#####

END

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

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

RETURN

ERRORHANDLER:

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

RETURN

END