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

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].[TrxInventoryUpdateSaleItem]

(

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

  @beforeInventoryItemLogKey nvarchar(50)

  Set @modified = dbo.GetCompanyDateTime()

  Select @modifiedBy = ModifiedBy, @modified = Modified

  FROM TrxTransaction Where TransactionKey = @pTransactionKey

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

BEGIN

  INSERT INTO InvInventoryItemLog

    (

    WarehouseKey, ProductKey, InQty, OutQty, OnFulfillmentQuanity,

    SourceType, SourceKey, SourceDetailKey,

    InventoryItemLogKey, UpdateType, Created, ItemCost, LocationKey, IsProcessed

    )

  SELECT Sale.WarehouseKey, Sale.ProductKey, 0, Sale.Quantity, 0,

    7, Sale.TransactionKey, Sale.TransactionItemKey,

    NEWID(), 0, dbo.GetCompanyDateTime(), Sale.AverageCost, Sale.DeliveryLocationKey,

    1 As IsProcessed --UPDATE THE BOTH AVL. AND IN STOCK

  FROM TrxTransaction h with(nolock), TrxTransactionSaleItem Sale with(nolock), InvProduct Product with(nolock)

  WHERE h.TransactionKey = Sale.TransactionKey

  AND  h.TransactionKey = @pTransactionKey

  AND  Sale.ProductKey = Product.ProductKey

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

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

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

  AND  Sale.Type   = 0 --INDICATES THAT THE SALE ITEM IS OF TYPE SALE

END

--##

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

BEGIN

  INSERT INTO InvInventoryItemLog

   ( WarehouseKey, ProductKey, InQty, OutQty, OnFulfillmentQuanity,

    SourceType, SourceKey, SourceDetailKey,

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

   )

  SELECT Sale.DeliveryWarehouseKey, Sale.ProductKey, 0, CASE WHEN F.InventoryAllocationMethod = 0 THEN Sale.Quantity ELSE 0 END

           , Sale.Quantity,

    7, Sale.TransactionKey, Sale.TransactionItemKey,

    NEWID(), CASE WHEN F.InventoryAllocationMethod = 0 THEN 0 ELSE 2 END

    , dbo.GetCompanyDateTime() , Sale.AverageCost, Sale.DeliveryLocationKey, CASE WHEN F.InventoryAllocationMethod = 0 THEN 0 ELSE Sale.Quantity End As ReservedQuantity,

    1 As IsProcessed --AVAILABLE QUANTITY

  FROM TrxTransaction h with(nolock)

   , TrxTransactionSaleItem Sale with(nolock)

   , InvProduct Product with(nolock)

   , TrxTransactionFulfillment F with(nolock)

   , TrxTransactionFulfillmentDetail FD with(nolock)

    --,RtlStore Store

  WHERE h.TransactionKey = Sale.TransactionKey

  AND  Sale.ProductKey   = Product.ProductKey

  AND  h.TransactionKey = @pTransactionKey

  AND  Product.IsNonStock = 'FALSE'

  AND  Product.IsAssembly = 'FALSE'

  AND  f.FulfillmentKey =  fd.FulfillmentKey

  AND  fd.TransactionKey = Sale.TransactionKey

  AND  fd.SourceDetailKey = Sale.TransactionItemKey

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

  AND  Sale.Type   = 0 --[0] INDICATES THAT THE ITEM IS SALE ITEM

END

--#

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

BEGIN

  INSERT INTO InvInventoryItemLog

    (

    WarehouseKey, ProductKey, InQty, OutQty, OnFulfillmentQuanity,

    SourceType, SourceKey, SourceDetailKey,

    InventoryItemLogKey, UpdateType, Created, ItemCost, LocationKey, IsProcessed

    )

  SELECT Sale.WarehouseKey, dynamicItem.ProductKey, 0, dynamicItem.Quantity, 0,

    7, @pTransactionKey, dynamicItem.SourceKey,

    NEWID(), 0, dbo.GetCompanyDateTime(), item.AverageCost, sale.DeliveryLocationKey,

    1 As IsProcessed --UPDATE THE BOTH AVL. AND IN STOCK

  FROM  TrxTransactionSaleItem Sale WITH(NOLOCK),

    TrxTransactionDynamicAssemblyItem dynamicItem WITH(NOLOCK),

    InvProduct Product WITH(NOLOCK),

    InvInventoryItem item with(nolock)

  WHERE Sale.TransactionKey = @pTransactionKey

  AND  Sale.TransactionItemKey = dynamicItem.SourceKey

  And  dynamicItem.TransactionKey = @pTransactionKey

  AND  dynamicItem.ProductKey = Product.ProductKey

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

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

  AND  Sale.Type   = 0   --INDICATES THAT THE SALE ITEM IS OF TYPE SALE

  And      dynamicItem.ProductKey   = item.ProductKey

  And     item.WarehouseKey = sale.WarehouseKey

END

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

BEGIN

  INSERT INTO InvInventoryItemLog

   ( WarehouseKey, ProductKey, InQty, OutQty, OnFulfillmentQuanity,

    SourceType, SourceKey, SourceDetailKey,

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

   )

  SELECT Sale.DeliveryWarehouseKey, dynamicItem.ProductKey, 0, CASE WHEN F.InventoryAllocationMethod = 0 THEN dynamicItem.Quantity ELSE 0 END

           , dynamicItem.Quantity,

    7, Sale.TransactionKey, dynamicItem.SourceKey,

    NEWID(), CASE WHEN F.InventoryAllocationMethod = 0 THEN 0 ELSE 2 END

    , dbo.GetCompanyDateTime() , Sale.AverageCost, Sale.DeliveryLocationKey, CASE WHEN F.InventoryAllocationMethod = 0 THEN 0 ELSE dynamicItem.Quantity End As ReservedQuantity,

    1 As IsProcessed --AVAILABLE QUANTITY

  FROM TrxTransaction h with(nolock)

   , TrxTransactionSaleItem Sale with(nolock)

   , TrxTransactionDynamicAssemblyItem dynamicItem WITH(NOLOCK)

   , TrxTransactionFulfillment F with(nolock)

   , TrxTransactionFulfillmentDetail FD with(nolock)

  WHERE h.TransactionKey = Sale.TransactionKey

  AND  h.TransactionKey = @pTransactionKey

  And  dynamicItem.TransactionKey = @pTransactionKey

  AND  Sale.TransactionItemKey =  dynamicItem.SourceKey

  AND  f.FulfillmentKey =  fd.FulfillmentKey

  AND  fd.TransactionKey = Sale.TransactionKey

  AND  fd.SourceDetailKey = Sale.TransactionItemKey

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

  AND  Sale.Type   = 0 --[0] INDICATES THAT THE ITEM IS SALE ITEM

END

--#

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

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

---##1. NORMAL SALE OF ASSEMBLY ITEM W/O FULFILLMENT

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

  as

  (

    SELECT   A.AssemblyComponentKey,A.ParentProductKey, A.ProductKey,

    CONVERT(DECIMAL(20,5), A.Quantity * Sale.Quantity),E.InventoryItemKey, Sale.WareHouseKey,

    Sale.TransactionKey, Sale.TransactionItemKey

    , Sale.AverageCost, Sale.DeliveryLocationKey

    FROM     INVASSEMBLYCOMPONENT A with(nolock)

    , TrxTransaction B with(nolock)

    , TrxTransactionSaleItem Sale with(nolock)

    , InvProduct D with(nolock)

    , InvInventoryItem E with(nolock)

    Where A.ParentProductKey = Sale.ProductKey

  And  Sale.ProductKey   = D.ProductKey

  And  B.TransactionKey = Sale.TransactionKey

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

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

  AND  Sale.Type   = 0   --INDICATES THAT THE SALE ITEM IS OF TYPE SALE

  AND  E.WarehouseKey   = Sale.WarehouseKey

  AND  A.ProductKey   = E.ProductKey

  And  B.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, 0, Quantity,

    7, TransactionKey, TransactionItemKey,

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

    1 As IsProcessed --both available and instore gets affected

  FROM #AssemblyComponents

---#

--###

 ;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, Sale.TransactionKey,

    Detail.TransactionItemKey, Fulfillment.InventoryAllocationMethod,

    Detail.AverageCost, Detail.DeliveryLocationKey

    FROM     InvAssemblyComponent Assembly with(nolock)

   , TrxTransaction Sale with(nolock)

   , TrxTransactionSaleItem Detail with(nolock)

   , InvProduct Product with(nolock)

   , InvInventoryItem Item with(nolock)

   , TrxTransactionFulfillment Fulfillment 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  Fulfillment.TransactionKey   = Sale.TransactionKey

  AND  Fulfillment.FulfillmentKey   = FulfillmentDetail.FulfillmentKey

  AND  FulfillmentDetail.SourceDetailKey = Detail.TransactionItemKey

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

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

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

  AND  Detail.Type       = 0   --INDICATES THAT THE SALE ITEM IS OF TYPE 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

  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, 0, CASE WHEN InventoryAllocationMethod = 0 THEN Quantity ELSE 0 END, Quantity,

    7, TransactionKey,

    TransactionItemKey,

    NEWID()

    ,Case When InventoryAllocationMethod = 0 then 0 else 2 END --if sale type then reduce both available and in stock

    ,dbo.GetCompanyDateTime(),AverageCost, LocationKey

    ,1 As IsProcessed -- 1 INDICATES AVAILABLE QUANTITY

      , Case When InventoryAllocationMethod = 1 Then Quantity Else 0 End -- if delivery type then update the reserved quantity

  FROM  #AssemblyComponents1

RETURN

ERRORHANDLER:

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

RETURN

END