iVend Database Database : TrxInventoryUpdateLaybyItem 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.

1

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

PmtLayawayPlan

Table

Defines the plan through which the items can be put on layaways to the customers.

2

RepReplicationTransaction

Table

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

2

RtlStore

Table

Contains the details of Stores defined in the system.

1

TrxGenerateLayawayInstallments

Stored Procedure

Generates Layaway installments for the Layaway transaction

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

TrxTransactionInstallmentDetail

Table

Defines the installments details of all those transactions which have a layaway plan attached to it.

2

TrxTransactionLayaway

Table

Store layway type of transactions.

1

TrxTransactionSurcharge

Table

Provides a reference of all the surcharges & other details attached with any transaction.

2

CREATE PROCEDURE [dbo].[TrxInventoryUpdateLaybyItem]

(

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

  @transactionLayawayKey NVARCHAR(50)

  Set @modified = dbo.GetCompanyDateTime()

  Select @modifiedBy = ModifiedBy, @modified = Modified

  FROM TrxTransaction Where TransactionKey = @pTransactionKey

IF EXISTS (SELECT 1 FROM CfgEnterprise WHERE ApplyLayawayOnSale = 'FALSE')

BEGIN

  DECLARE transactionDetailCursor CURSOR FOR

  SELECT TransactionLayawayKey

  FROM TrxTransactionLayaway

  WHERE TransactionKey = @pTransactionKey

  AND  Type   = 0     -- [0] CREATION OF LAYBY

  OPEN transactionDetailCursor

  FETCH NEXT FROM  transactionDetailCursor INTO @transactionLayawayKey

  WHILE @@FETCH_STATUS = 0

  BEGIN

  Exec TrxGenerateLayawayInstallments @pTransactionKey, @transactionLayawayKey, @pDebug, @pSiteId, @pBatchKey

  FETCH NEXT FROM  transactionDetailCursor INTO @transactionLayawayKey

  END

  CLOSE transactionDetailCursor

  DEALLOCATE transactionDetailCursor

END

-----------------------------------------UPDATING INVENTORY FOR NORMAL SALE ITEMS-------------------------------------------------------

If @pDebug = 'Y'

Select 'UPDATING INVENTORY FOR LAYAWAY'

INSERT INTO InvInventoryItemLog

  (

   WarehouseKey, ProductKey, InQty

   , OutQty,OnFulfillmentQuanity , SourceType, SourceKey, SourceDetailKey

   , InventoryItemLogKey, UpdateType, Created

   , ItemCost, LocationKey, ReservedQuantity, IsProcessed

  )

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

  CASE WHEN f.InventoryAllocationMethod = 0 THEN LayawayItem.Quantity ELSE 0 END As OutQty,

   LayawayItem.Quantity,

  8, LayawayItem.TransactionKey, LayawayItem.TransactionLayawayKey,

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

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

  1 AS IsProcessed -- UPDATE BOTH AVAILABLE AND INSTOCK

FROM TrxTransaction h, TrxTransactionLayaway LayawayItem, InvProduct Product,

   TrxTransactionFulfillment f, TrxTransactionFulfillmentDetail FD, RtlStore Store

WHERE h.TransactionKey   = LayawayItem.TransactionKey

AND  LayawayItem.ProductKey = Product.ProductKey

AND  Product.IsNonStock   = 'FALSE'

AND  Product.IsAssembly   = 'FALSE'

AND  f.FulfillmentKey   =  fd.FulfillmentKey

AND  fd.TransactionKey   = LayawayItem.TransactionKey

AND  fd.SourceDetailKey   = LayawayItem.TransactionLayawayKey

AND  Store.WarehouseKey   = LayawayItem.DeliveryWarehouseKey

AND  fd.SourceType   = 2     -- [2] FULFILLMENT OF LAYAWAY

AND  LayawayItem.Type   = 0     -- [0] CREATION OF LAYAWAY

--AND  f.InventoryAllocationMethod = 0 --[0] INVENTORY IS ALLOCATED AT THE TIME OF SALE

AND  h.TransactionKey   = @pTransactionKey

AND  Store.IsDeleted   = 0

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

;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.TransactionLayawayKey, Fulfillment.InventoryAllocationMethod, Detail.AverageCost, Detail.DeliveryLocationKey

FROM     InvAssemblyComponent Assembly, TrxTransaction Sale,

     TrxTransactionLayaway Detail, InvProduct Product, InvInventoryItem Item,

     TrxTransactionFulfillment Fulfillment, TrxTransactionFulfillmentDetail FulfillmentDetail

    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.TransactionLayawayKey

  AND  FulfillmentDetail.SourceType = 2   --INDICATED THAT THE FULFILLMENT IS ON LAYAWAY 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

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

  JOIN InvInventoryItem AS R3 ON R1.ProductKey = R3.ProductKey

  AND R3.WareHouseKey = R2.WarehouseKey

    )

  SELECT A.* INTO #AssemblyComponents

  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

   )

  SELECT WarehouseKey, ProductKey,

    0, Quantity, Quantity,

    8, TransactionKey, TransactionItemKey,

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

  FROM #AssemblyComponents

  WHERE InventoryAllocationMethod = 0 --[0] INVENTORY IS ALLOCATED AT THE TIME OF SALE

INSERT INTO InvInventoryItemLog

   ( WarehouseKey, ProductKey,

    InQty, OutQty, OnFulfillmentQuanity,

    SourceType, SourceKey, SourceDetailKey,

    InventoryItemLogKey, UpdateType, Created,

    ItemCost, LocationKey, IsProcessed

   )

  SELECT WarehouseKey, ProductKey,

    0, 0, Quantity As OnFulfillmentQuanity,

    8, TransactionKey, TransactionItemKey,

    NEWID(), 0 ,

    dbo.GetCompanyDateTime(), AverageCost, LocationKey, 1 AS IsProcessed-- JUST INCREASE THE FULFILLMENT QUANTITY

  FROM #AssemblyComponents

  WHERE InventoryAllocationMethod = 1 --[0] INVENTORY IS ALLOCATED AT THE TIME OF DELIVERY

RETURN

ERRORHANDLER:

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

RETURN

END