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

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

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.

1

TrxTransactionLayaway

Table

Store layway type of transactions.

1

TrxTransactionStatus

Table

Defines the transaction status with respect to various amounts that are affected by it.

1

TrxTransactionSurcharge

Table

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

1

CREATE PROCEDURE [dbo].[TrxInventoryUpdateLaybyCancellationItem]

(

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

  @transactionLayawayKey NVARCHAR(50)

  Set @modified = dbo.GetCompanyDateTime()

  Select @modifiedBy = ModifiedBy, @modified = Modified

  FROM TrxTransaction Where TransactionKey = @pTransactionKey

  --THIS WILL UPDATE THE TRANSACTION STATUS IF THE SALE LEVEL LAWAWAY IS APPLIED

  IF EXISTS(SELECT 1 FROM CfgEnterprise Where ApplyLayawayOnSale = 'TRUE')

  BEGIN

  UPDATE TrxTransactionStatus

  SET LayawayBalanceAmount = 0

  WHERE TransactionKey

  IN (

    SELECT DISTINCT OriginalDocumentKey

    FROM TrxTransactionLayaway

    WHERE TransactionKey = @pTransactionKey

    AND  Type = 1

   )

  ---for generating the replication entry for the transaction status of the earlier Sales Order-------------

  INSERT INTO RepReplicationTransaction(ReplicationTransactionKey, SourceType,

      SourceKey, BatchKey,OperationType,Flag)

  SELECT NEWID(), 178, TransactionStatusKey,

    @pBatchKey, 1, 'FALSE'

  FROM TrxTransactionStatus o , TrxTransaction h, TrxTransactionLayaway p

  WHERE O.TransactionKey = h.TransactionKey

  AND  h.TransactionKey = p.OriginalDocumentKey

  AND  P.TransactionKey = @pTransactionKey

  AND  p.Type = 1

  IF(@@ERROR <>0)

    BEGIN

    SET @ErrorDesc = 'ERROR WHILE UPDATING THE CANCELLED STATUS OF THE LAYAWAY INSTALLMENT.'

    GOTO ERRORHANDLER

  END

  END

  ---------------------------------UPDATION OF INSTALLMENTS------------------------------------------

  If @pDebug = 'Y'

  Select 'Updating the status of the installment'

  UPDATE TrxTransactionInstallmentDetail

  SET  Status = 4   ---[4] CANCELLED

  WHERE SourceType = 0   ---[0] LAYAWAY INSTALLMENTS

  AND  SourceKey In

  (

  SELECT DISTINCT OriginalDetailKey

  FROM TrxTransactionLayaway

  WHERE TransactionKey = @pTransactionKey

  AND    Type = 1 -- [1] CANCELLED LAYAWAY

  )

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

  IF(@@ERROR <>0)

    BEGIN

    SET @ErrorDesc = 'ERROR WHILE UPDATING THE CANCELLED STATUS OF THE LAYAWAY INSTALLMENT.'

    GOTO ERRORHANDLER

  END

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

  ---FOR GENERATING THE REPLICATION ENTRY FOR THE TRANSACTION STATUS OF THE EARLIER SALES ORDER-------------

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

    SELECT NEWID(), 171, InstallmentDetailKey, @pBatchKey, 1, 'FALSE'

  FROM TrxTransactionLayaway A, TrxTransactionInstallmentDetail B

  WHERE A.OriginalDetailKey = B.SourceKey

  AND  B.SourceType = 0

  AND  A.Type = 1

  AND  A.TransactionKey = @pTransactionKey

  IF(@Error <>0)

    BEGIN

    SET @ErrorDesc = 'Error while generating the replication entry for updating the cancelled status of the layaway installment.'

    GOTO ERRORHANDLER

  END

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

  ---------------------------------UPDATION OF SURCHARGES OF ORIGINAL LAYAWAY ITEM-------------------

  If @pDebug = 'Y'

  Select 'Updating the item surchages for the early transaction'

  Update su

  Set  su.Status = 1         --[1] SURCHARE IS CANCELLED

  FROM TrxTransaction h, TrxTransactionLayaway s,

    TrxTransactionLayaway original, TrxTransactionSurcharge su

  WHERE h.TransactionKey = s.TransactionKey

  AND  h.TransactionKey = @pTransactionKey

  AND  s.OriginalDocumentKey = original.TransactionKey

  AND  s.OriginalDetailKey = original.TransactionLayawayKey

  AND  su.SourceKey   = original.TransactionLayawayKey

  AND  su.SourceType   = 2       -- [2] SURCHARE AGAINST LAYAWAY

  AND  s.Type     = 1       -- [1] LAYAWAY IS CANCELLED

  IF(@@ERROR <>0)

  BEGIN

  SET @ErrorDesc = 'Error while updating the surchage status for the order item.'

  GOTO ERRORHANDLER

  END

  ---for generating the replication entry for the transaction -------------

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

    SELECT NEWID(), 155, TransactionSurchargeKey, @pBatchKey, 1, 'FALSE'

  FROM TrxTransactionLayaway A, TrxTransactionSurcharge B

  WHERE A.OriginalDocumentKey = B.TransactionKey

  AND  A.OriginalDetailKey = B.SourceKey

  AND  A.TransactionKey = @pTransactionKey

  AND  B.SourceType   = 2       -- [2] SURCHARE AGAINST LAYAWAY

  AND  A.Type     = 1       -- [1] LAYAWAY IS CANCELLED

  IF(@Error <>0)

    BEGIN

    SET @ErrorDesc = 'Error while generating the replication entry for updating the cancelled status of the layaway surcharges.'

    GOTO ERRORHANDLER

  END

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

  ---------------------------------UPDATION OF ORIGINAL LAYAWAY ITEM--------------------------------

  If @pDebug = 'Y'

  Select 'Updating the status of the installment'

  UPDATE original

  SET  original.Status = 2           --[2] THIS LINE IS NOW CANCELLED

  From TrxTransactionLayaway original,  TrxTransactionLayaway layaway

  Where original.TransactionLayawayKey = layaway.OriginalDetailKey

  And  original.TransactionKey   = layaway.OriginalDocumentKey

  And  layaway.Type     = 1       --[1] LAYAWAY IS CANCELLED

  And  layaway.TransactionKey   = @pTransactionKey

  IF(@@ERROR <>0)

    BEGIN

    SET @ErrorDesc = 'Error while updating the cancelled status of the layaway item.'

    GOTO ERRORHANDLER

  END

  ---for generating the replication entry for the transaction -------------

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

    SELECT NEWID(), 148, original.TransactionLayawayKey, @pBatchKey, 1, 'FALSE'

  From TrxTransactionLayaway original,  TrxTransactionLayaway layaway

  Where original.TransactionLayawayKey = layaway.OriginalDetailKey

  And  original.TransactionKey   = layaway.OriginalDocumentKey

  And  layaway.Type     = 1       --[1] LAYAWAY IS CANCELLED

  And  layaway.TransactionKey   = @pTransactionKey

  IF(@Error <>0)

    BEGIN

    SET @ErrorDesc = 'Error while generating the replication entry for updating the cancelled status of the layaway item.'

    GOTO ERRORHANDLER

  END

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

  ---------------------------------UPDATION OF ORIGINAL FULFILLMENT SURCHARGES--------------------

  If @pDebug = 'Y'

  Select 'Marking the cancelled for the fulfillment surcharges'

  Update sur

  Set  sur.Status = 1 -- denotes that the surcharges are deleted

  FROM TrxTransaction h, TrxTransactionLayaway s, TrxTransactionLayaway original,

    TrxTransactionFulfillment f, TrxTransactionFulfillmentDetail fd, TrxTransactionSurcharge sur

  WHERE h.TransactionKey = s.TransactionKey

  AND  h.TransactionKey = @pTransactionKey

  AND  s.OriginalDocumentKey = original.TransactionKey

  AND  s.OriginalDetailKey = original.TransactionLayawayKey

  AND  original.TransactionKey = f.TransactionKey

  AND  f.FulfillmentKey = fd.FulfillmentKey

  AND  fd.SourceDetailKey = original.TransactionLayawayKey

  AND  s.Type     = 1       --[1] LAYAWAY IS CANCELLED

  AND  fd.SourceType   = 2       --[2] FULFILLMENT IS FOR LAYAWAY

  AND  sur.SourceKey   = f.FulfillmentKey

  AND  sur.SourceType   = 7       --[7] SURCHARGE IS AGAINST FULFILLMENT

  ---for generating the replication entry for the transaction -------------

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

    SELECT NEWID(), 155, D.TransactionSurchargeKey, @pBatchKey, 1, 'FALSE'

  From TrxTransactionLayaway A,  TrxTransactionFulfillmentDetail B, TrxTransactionFulfillment C, TrxTransactionSurcharge D

  Where A.OriginalDocumentKey = B.TransactionKey

  AND  A.OriginalDetailKey = B.SourceDetailKey

  AND  B.SourceType   = 2   --[2] FULFILLMENT IS FOR LAYAWAY

  AND  B.FulfillmentKey = C.FulfillmentKey

  AND  B.FulfillmentKey = D.SourceKey

  AND  D.SourceType   = 7   --[7] SURCHARGE IS AGAINST FULFILLMENT

  AND  A.TransactionKey = @pTransactionKey

  AND  A.TYPE     = 1   --[1] LAYAWAY IS CANCELLED

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

  ---------------------------------UPDATION OF FULFILLMENT----------------------------------------

  Update fd

  Set  fd.Quantity = fd.QuantityFulfilled,

    fd.IsDeleted = 1         -- [1] THIS FULFILLMENT LINE IS DELETED

  FROM TrxTransaction h, TrxTransactionLayaway s, TrxTransactionLayaway original,

    TrxTransactionFulfillment f, TrxTransactionFulfillmentDetail fd

  WHERE h.TransactionKey = s.TransactionKey

  AND  h.TransactionKey = @pTransactionKey

  AND  s.OriginalDocumentKey = original.TransactionKey

  AND  s.OriginalDetailKey = original.TransactionLayawayKey

  AND  original.TransactionKey = f.TransactionKey

  AND  f.FulfillmentKey = fd.FulfillmentKey

  AND  fd.SourceDetailKey = original.TransactionLayawayKey

  AND  fd.SourceType   = 2       --[2] FULFILLMENT WAS FOR LAYAWAY

  AND  s.Type     = 1       --[1] LAYAWAY IS CANCELLED

  ---for generating the replication entry for the transaction -------------

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

    SELECT NEWID(), 27, B.FulfillmentDetailKey, @pBatchKey, 1, 'FALSE'

  From TrxTransactionLayaway A,  TrxTransactionFulfillmentDetail B, TrxTransactionFulfillment C

  Where A.OriginalDocumentKey = B.TransactionKey

  AND  A.OriginalDetailKey = B.SourceDetailKey

  AND  B.SourceType   = 2   --[2] FULFILLMENT IS FOR LAYAWAY

  AND  B.FulfillmentKey = C.FulfillmentKey

  AND  A.TransactionKey = @pTransactionKey

  AND  A.TYPE     = 1   --[1] LAYAWAY IS CANCELLED

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

-----------------------------------------UPDATING INVENTORY FOR LAYAWAY CANCELLATION ITEMS------------------------------------------------------

  If @pDebug = 'Y'

  Select 'If the fulFillment is on layaway item then based on the plan update the inventory'

INSERT INTO InvInventoryItemLog

  ( WarehouseKey, ProductKey, InQty, OutQty,

   SourceType, SourceKey, SourceDetailKey,

   InventoryItemLogKey, UpdateType, Created

   , ItemCost, LocationKey, OnFulfillmentQuanity

   , ReservedQuantity, IsProcessed

  )

SELECT Store.WarehouseKey, layaway.ProductKey, case when f.InventoryAllocationMethod = 0 then layaway.Quantity else 0 end As InQty, 0 As OutQty,

  8 As SourceType, layaway.TransactionKey, layaway.TransactionLayawayKey,

  NEWID(), 0

   , dbo.GetCompanyDateTime(), original.AverageCost, original.DeliveryLocationKey As LocationKey

   , -1 * layaway.Quantity As OnFulfillmentQuanity, case when f.InventoryAllocationMethod = 1 then -1 * layaway.Quantity else 0 end As ReservedQuantity

   , 1 As IsProcessed

FROM TrxTransactionLayaway original, InvProduct Product,

   TrxTransactionFulfillment f, TrxTransactionFulfillmentDetail FD, TrxTransactionLayaway layaway,

   RtlStore Store

WHERE layaway.OriginalDocumentKey = original.TransactionKey

AND  layaway.OriginalDetailKey = original.TransactionLayawayKey

AND  original.ProductKey   = Product.ProductKey

AND  layaway.TransactionKey = @pTransactionKey

AND  Product.IsNonStock   = 'FALSE'

AND  Product.IsAssembly   = 'FALSE'

AND  f.FulfillmentKey   =  fd.FulfillmentKey

AND  fd.TransactionKey   = original.TransactionKey

AND  fd.SourceDetailKey   = original.TransactionLayawayKey

AND  original.DeliveryWarehouseKey = Store.WarehouseKey

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

AND  layaway.Type   = 1     -- [0] CANCELLATION OF LAYAWAY

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

AND  Store.IsDeleted   = 0

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

-----------------------------------------UPDATING INVENTORY FOR ASSEMBLY ITEMS------------------------------------------------------

If @pDebug = 'Y'

Select 'Updating Inventory for Normal Sale Items'

  If @pDebug = 'Y'

  Select 'If the fulFillment is on layaway item then based on the plan update the inventory'

  ;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 * OriginalLayawayDetail.Quantity),

     Item.InventoryItemKey, Detail.WareHouseKey, Sale.TransactionKey,

     Detail.TransactionLayawayKey, Fulfillment.InventoryAllocationMethod, OriginalLayawayDetail.AverageCost,

     OriginalLayawayDetail.DeliveryLocationKey

    FROM     InvAssemblyComponent Assembly, TrxTransaction Sale,

     TrxTransactionLayaway Detail, TrxTransactionLayaway OriginalLayawayDetail,

     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  Detail.OriginalDocumentKey   = OriginalLayawayDetail.TransactionKey

  AND  Detail.OriginalDetailKey   = OriginalLayawayDetail.TransactionLayawayKey

  And  Fulfillment.TransactionKey   = OriginalLayawayDetail.TransactionKey

  And  Fulfillment.FulfillmentKey   = FulfillmentDetail.FulfillmentKey

  And  FulfillmentDetail.SourceDetailKey = OriginalLayawayDetail.TransactionLayawayKey

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

  AND  OriginalLayawayDetail.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       = 1   -- [0] CANCELLATION OF 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

  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'

  --INCREASE INSTORE AND SALE IN CASE OF SALE FULFILLMENT

  --REDUCE ON FULFILLMENT IN ALL CASES

  --REDUCE RESERVED IN CASE OF DELIVERY FULFILLMENT

  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 As InQty , 0, - 1 * Quantity AS OnFulfillmentQuanity,

    8, TransactionKey, TransactionItemKey,

    NEWID(), 0, dbo.GetCompanyDateTime()

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

  FROM #AssemblyComponents

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

RETURN

ERRORHANDLER:

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

RETURN

END