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

TrxARPayment

Table

Defines the details of the on account payments or any other settlements being done by the customer and the amount.

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

TrxTransactionOrder

Table

Stores information about various order booked in the system.

1

TrxTransactionSaleItem

Table

Stores sale/refund/delivery items attached to a transaction

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

(

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

  @hasCancelledSalesOrder BIT

  Set @modified = dbo.GetCompanyDateTime()

  Select @modifiedBy = ModifiedBy, @modified = Modified, @hasCancelledSalesOrder = HasCancelledSalesOrder

  FROM TrxTransaction Where TransactionKey = @pTransactionKey

--####1. Creation on Sales Order and handling of normal items

BEGIN

  INSERT INTO InvInventoryItemLog

   ( WarehouseKey, ProductKey, InQty, OutQty, OnFulfillmentQuanity,

    SourceType, SourceKey, SourceDetailKey,

    InventoryItemLogKey, UpdateType, Created, LocationKey,ReservedQuantity, IsProcessed

   )

  SELECT SalesOrder.DeliveryWarehouseKey, SalesOrder.ProductKey, 0, 0, SalesOrder.Quantity,

    9, SalesOrder.TransactionKey, SalesOrder.TransactionOrderKey,

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

    CASE WHEN f.ReserveQuantity = 1 THEN 1 * SalesOrder.Quantity ELSE 0 END As ReservedQuantity,

    1 AS IsProcessed --UPDATE ONLY THE FULFILLMENT QUANTITIES

  From  TrxTransaction h with(nolock)

   , TrxTransactionOrder SalesOrder with(nolock)

   , InvProduct Product with(nolock)

   , TrxTransactionFulfillment f with(nolock)

   , TrxTransactionFulfillmentDetail FD with(nolock)

  WHERE h.TransactionKey   = SalesOrder.TransactionKey

  AND  SalesOrder.ProductKey = Product.ProductKey

  AND  f.FulfillmentKey   = fd.FulfillmentKey

  AND  fd.TransactionKey   = SalesOrder.TransactionKey

  AND  fd.SourceDetailKey   = SalesOrder.TransactionOrderKey

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

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

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

  AND  SalesOrder.Status   = 0   --INDICATES THAT THE ORDER IS ACTIVE AND NOT CANCELLED

  AND  h.TransactionKey   = @pTransactionKey

END

--####2. Updation of sales order with normal items

BEGIN

  --transaction key is null indicates that the sales order has been cancelled

  INSERT INTO InvInventoryItemLog

   ( WarehouseKey, ProductKey, InQty, OutQty, OnFulfillmentQuanity,

    SourceType, SourceKey, SourceDetailKey,

    InventoryItemLogKey, UpdateType, Created, LocationKey,ReservedQuantity, IsProcessed

   )

  SELECT T4.WarehouseKey,

    T1.ProductKey, 0, 0,

    CASE WHEN T5.TransactionKey IS NULL THEN -1 * (T2.Quantity - T2.FullfilledQuantity) ELSE -1 * T2.Quantity END,

    9, T1.TransactionKey, T1.TransactionOrderKey,

    NEWID(),

    0, dbo.GetCompanyDateTime(), 0,

    CASE WHEN T5.ReserveQuantity = 1 THEN -1 * (T2.Quantity - T2.FullfilledQuantity) ELSE 0 END,

    1 As IsProcessed --UPDATE ONLY THE FULFILLMENT QUANTITIES

  From TrxTransaction T0 with(nolock) INNER JOIN

          TrxTransactionOrder T1 with(nolock) ON T0.TransactionKey = T1.TransactionKey

    INNER JOIN  TrxTransactionOrder T2 with(nolock) ON T1.OriginalDetailKey = T2.TransactionOrderKey AND T1.OriginalDocumentKey = t2.TransactionKey

    INNER JOIN  InvProduct T3 with(nolock) ON T1.ProductKey = T3.ProductKey

    INNER JOIN  RtlStore T4 with(nolock) ON T2.DeliveryWarehouseKey = T4.WarehouseKey And T4.IsDeleted = 0

    INNER JOIN  TrxTransactionFulfillment T5 with(nolock) ON T1.OriginalDocumentKey = T5.TransactionKey

    INNER JOIN TrxTransactionFulfillmentDetail T6 with(nolock) ON T5.FulfillmentKey = T6.FulfillmentKey AND T6.SourceDetailKey = T1.OriginalDetailKey And T6.SourceType = 1

  WHERE T1.Status = 0

  AND  T0.TransactionKey   = @pTransactionKey

  AND  T3.IsNonStock     = 'FALSE' --INDICATES THAT ITEMS IS A STOCKABLE ITEM

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

END

--####3. Creation of Sales Order and handling of Assembly items

BEGIN

  ;WITH RecursionCTE (AssemblyComponentKey, ParentProductKey, ProductKey, Quantity, InventoryItemKey, WareHouseKey, TransactionKey, TransactionOrderKey, ReservedQuantity)

  as

   (

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

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

     Detail.TransactionKey, Detail.TransactionOrderKey ,

    CASE WHEN Fulfillment.ReserveQuantity = 1 THEN CONVERT(DECIMAL(20,5), Assembly.Quantity * Detail.OpenQuantity) else 0 END As ReservedQuantity

    FROM     InvAssemblyComponent Assembly with(nolock),

       TrxTransaction Sale with(nolock),

       TrxTransactionOrder 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  Product.IsAssembly   = 'TRUE' --INDICATES THAT THE PARENTPRODUCT IS ASSEMBLY ITEM

  AND  Detail.Status   = 0   --INDICATES THAT THE ORDER IS ACTIVE AND NOT CANCELLED

  AND  Item.WarehouseKey   = Detail.WarehouseKey

  AND  Assembly.ProductKey   = Item.ProductKey

  AND  Sale.TransactionKey   = @pTransactionKey

  AND  Detail.OpenQuantity   != 0

  AND  Detail.TransactionKey =   Fulfillment.TransactionKey

  AND  Detail.TransactionOrderKey =   FulfillmentDetail.SourceDetailKey

  And    Fulfillment.FulfillmentKey =   FulfillmentDetail.FulfillmentKey

  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.TransactionOrderKey,

      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'

  INSERT INTO InvInventoryItemLog

    (

     WarehouseKey, ProductKey, InQty, OutQty, OnFulfillmentQuanity,

     SourceType, SourceKey, SourceDetailKey,

     InventoryItemLogKey, UpdateType, Created, LocationKey,

     ReservedQuantity, IsProcessed

    )

  SELECT WarehouseKey, ProductKey, 0, 0, Quantity,

    9, TransactionKey, TransactionOrderKey,

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

     ReservedQuantity,

    1 As IsProcessed --UPDATE ONLY THE FULFILLMENT QUANTITIES

  FROM #AssemblyComponents

END

--####4. Updation of Sales Order and handling of Assembly items

BEGIN

  ;WITH RecursionCTE (AssemblyComponentKey, ParentProductKey, ProductKey, Quantity, InventoryItemKey, WareHouseKey, TransactionKey, TransactionOrderKey, ReservedQuantity)

  as

  (

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

    CONVERT(DECIMAL(20,5), Assembly.Quantity * OriginalOrderDetail.OpenQuantity), Item.InventoryItemKey, Store.WareHouseKey,

       Detail.TransactionKey, Detail.TransactionOrderKey,

      CASE WHEN Fulfillment.ReserveQuantity = 1 THEN CONVERT(DECIMAL(20,5), Assembly.Quantity * OriginalOrderDetail.OpenQuantity) else 0 END As ReservedQuantity

    FROM    InvAssemblyComponent Assembly with(nolock),

       TrxTransaction Sale with(nolock),

       TrxTransactionOrder Detail with(nolock),

       InvProduct Product with(nolock),

       InvInventoryItem Item with(nolock),

       TrxTransactionOrder OriginalOrderDetail with(nolock),

       RtlStore Store 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  Detail.OriginalDetailKey   = OriginalOrderDetail.TransactionOrderKey

  AND  Detail.OriginalDocumentKey   = OriginalOrderDetail.TransactionKey

  AND  OriginalOrderDetail.DeliveryWarehouseKey = Store.WarehouseKey

  AND  Store.WarehouseKey     = Item.WarehouseKey

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

  --AND  Detail.Status       = 0   --INDICATES THAT THE ORDER IS ACTIVE AND NOT CANCELLED

  AND  Assembly.ProductKey     = Item.ProductKey

  And  Sale.TransactionKey     = @pTransactionKey

  And  Store.IsDeleted = 0

  AND  Detail.OriginalDocumentKey         =   Fulfillment.TransactionKey

  And  Detail.OriginalDetailKey     = FulfillmentDetail.SourceDetailKey

  And      FulfillmentDetail.FulfillmentKey                 = Fulfillment.FulfillmentKey

  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.TransactionOrderKey,

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

  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 #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, LocationKey, ReservedQuantity, IsProcessed

   )

  SELECT WarehouseKey, ProductKey, 0, 0, (-1) * Quantity,

    9, TransactionKey, TransactionOrderKey,

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

    (-1) * ReservedQuantity,

    1 As IsProcessed --UPDATE ONLY THE FULFILLMENT QUANTITIES

  FROM #AssemblyComponents1

END

--##

--###

If @pDebug = 'Y'

Select 'UPDATING THE PAYMENTS COLLECTED TILL DATE FOR THIS SALES ORDER'

Declare @AmountPaidTillDate decimal(20,5),

  @AmountPaidInCurrentTransaction decimal(20,5)

Select @AmountPaidTillDate = SpecialOrderPaidAmount

  FROM TrxTransactionOrder SalesOrder with(nolock)

   , TrxTransactionStatus Status with(nolock)

WHERE SalesOrder.TransactionKey = @pTransactionKey

AND  SalesOrder.OriginalDocumentKey = Status.TransactionKey

AND  SalesOrder.OriginalDocumentKey != '0'

---check if the AR Advance payment is paid against this order if not then only assume that all the surcharges are paid else

IF NOT EXISTS

 (

  SELECT * FROM TrxARPayment

  Where TransactionKey = @pTransactionKey

  And  PaymentType = 1

  And  DetailKey = @pTransactionKey

 )

BEGIN

  SELECT @AmountPaidInCurrentTransaction = (IsNull(Sum(Surcharge.Total), 0))

  FROM  TrxTransactionSurcharge Surcharge with(nolock)

  WHERE Surcharge.PayInAdvance =   'TRUE'

  And  Surcharge.IsNewSurcharge = 'TRUE'

  And  Surcharge.SourceType = 7

  AND  Surcharge.SourceKey IN

  (

  SELECT DISTINCT FL.FulfillmentKey

  FROM TrxTransaction TR with(nolock)

       , TrxTransactionOrder SalesOrder with(nolock)

       , TrxTransactionFulfillment FL with(nolock)

       , TrxTransactionFulfillmentDetail FD with(nolock)

  Where TR.TransactionKey = SalesOrder.TransactionKey

  And  FL.TransactionKey = TR.TransactionKey

  And  FL.FulfillmentKey = FD.FulFillmentKey

  And  FD.SourceDetailKey = SalesOrder.TransactionOrderKey

  And  FD.SourceType = 1

  And  TR.TransactionKey = @pTransactionKey

  )

  AND Surcharge.TransactionKey = @pTransactionKey

END

-- Start - Modified by Santosh

-- Get total of products delivered against the original Sales Order, to deduct from balance balance amount,

-- so that SpecialOrderBalanceAmount becomes ZERO, otherwise this amount is shwowing as balance, and allowing to refundable

Declare @DeliveredAmountAgainstSpecialOrder decimal(20,5)

IF @hasCancelledSalesOrder = 1

  SELECT @DeliveredAmountAgainstSpecialOrder = SUM(TotalPostSaleDiscount + SurchargeTotal) FROM  TrxTransactionSaleItem

  WHERE Type = 3 -- 3 - DeliveryAgainstSpecialOrder

  AND OriginalDocumentKey IN (

    SELECT OriginalDocumentKey FROM TrxTransactionOrder

    WHERE TransactionKey = @pTransactionKey

   )

Update TrxTransactionStatus

Set  SpecialOrderPaidAmount = IsNull(SpecialOrderPaidAmount, 0) +

          IsNull(@AmountPaidInCurrentTransaction, 0) +

          IsNull(@AmountPaidTillDate, 0) - ISNULL(@DeliveredAmountAgainstSpecialOrder, 0)

Where   TransactionKey   = @pTransactionKey

-- End - Modified by Santosh

Update TrxTransactionStatus

Set  SpecialOrderBalanceAmount =  SpecialOrderAmount - IsNull(SpecialOrderPaidAmount, 0)

Where   TransactionKey   = @pTransactionKey

---------------UPDATING THE EARLIER PAYMENT RECORDS SO THAT THEY MAP TO MODIFIED SO-----------

Update TrxARPayment

Set  DetailKey = @pTransactionKey

WHERE PaymentType = 1

AND  DetailKey =

 (

  Select Distinct t.TransactionKey

  FROM TrxTransactionOrder o with(nolock)

       , TrxTransaction t with(nolock)

  WHERE o.TransactionKey = @pTransactionKey

  AND  o.OriginalDocumentKey = t.TransactionKey

 )

IF(@@ERROR <>0)

    BEGIN

  SET @ErrorDesc = 'ERROR WHILE UPDATING PAID AMOUNT FOR ORDER.'

        GOTO ERRORHANDLER

END

---for generating the replication entry for the AR PAYMENT of the original sales order-------------

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

SELECT NEWID(), 142, ARPaymentKey, @pBatchKey,1, 'FALSE'

FROM  TrxARPayment A with(nolock)

   , TrxTransactionOrder B with(nolock)

WHERE B.OriginalDocumentKey = A.DetailKey

AND  A.PaymentType = 1

AND  B.TransactionKey = @pTransactionKey

IF(@Error <>0)

    BEGIN

  SET @ErrorDesc = 'ERROR WHILE GENERATING THE REPLICATION ENTRY FOR THE AR PAYMENT ON SO UPDATION.'

        GOTO ERRORHANDLER

END

--###

--###

If @pDebug = 'Y'

  Select 'UPDATING THE ITEM SURCHAGES FOR THE EARLY TRANSACTION'

Update Surcharge

Set  Surcharge.Status = 1     -- DENOTES THE SURCHARGE IS CANCELLED

FROM  TrxTransaction h with(nolock)

   , TrxTransactionOrder SalesOrder with(nolock)

   , TrxTransactionOrder OriginalSalesOrder with(nolock)

   , TrxTransactionSurcharge Surcharge with(nolock)

WHERE h.TransactionKey   = SalesOrder.TransactionKey

AND  SalesOrder.OriginalDocumentKey = OriginalSalesOrder.TransactionKey

AND  SalesOrder.OriginalDetailKey = OriginalSalesOrder.TransactionOrderKey

AND  Surcharge.SourceKey   = OriginalSalesOrder.TransactionOrderKey

AND  Surcharge.SourceType   = 1   -- DENOTES SURCHAGRES AGAINST SPECIAL ORDER

AND  h.TransactionKey   = @pTransactionKey

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 SURCHARGES OF THE ORIGINAL SALES ORDER-------------

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

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

FROM  TrxTransactionOrder A with(nolock)

   ,  TrxTransactionSurcharge B with(nolock)

WHERE A.OriginalDocumentKey = B.TransactionKey

AND  A.OriginalDetailKey = B.SourceKey

AND  B.SourceType   = 1 -- DENOTES SURCHAGRES AGAINST SPECIAL ORDER

AND  A.OriginalDocumentKey != '0' -- DENOTES THIS ENTRY IS FOR EDITING OF THE PREVIOUS ENTRY

AND  A.OriginalDetailKey != '0' -- DENOTES THIS ENTRY IS FOR EDITING OF THE PREVIOUS ENTRY

AND  A.TransactionKey = @pTransactionKey

--#####5.3 UPDATING THE STATUS FOR THE ORIGINAL TRANSACTION ITEMS ######################################

--Mark the original sales order lines as cancelled in case the sales order cancellation is done

BEGIN

  Update OriginalSalesOrder

  Set  OriginalSalesOrder.Status = 1   -- DENOTES THE ORDER IS CANCELLED

  FROM  TrxTransaction h with(nolock)

       , TrxTransactionOrder SalesOrder with(nolock)

       , TrxTransactionOrder OriginalSalesOrder with(nolock)

  WHERE h.TransactionKey   = SalesOrder.TransactionKey

  AND  h.TransactionKey   = @pTransactionKey

  AND  SalesOrder.OriginalDocumentKey = OriginalSalesOrder.TransactionKey

  AND  SalesOrder.OriginalDetailKey = OriginalSalesOrder.TransactionOrderKey

  ---FOR GENERATING THE REPLICATION ENTRY FOR THE ORIGINAL SO-------------

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

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

  FROM TrxTransactionOrder A with(nolock)

   ,  TrxTransactionOrder B with(nolock)

  WHERE A.OriginalDocumentKey = B.TransactionKey

  AND  A.OriginalDetailKey = B.TransactionOrderKey

  AND  A.OriginalDocumentKey !='0' -- DENOTES THIS ENTRY IS FOR EDITING OF THE PREVIOUS ENTRY

  AND  A.OriginalDetailKey !='0' -- DENOTES THIS ENTRY IS FOR EDITING OF THE PREVIOUS ENTRY

  AND  A.TransactionKey = @pTransactionKey

END

---Reverse the Fulfillment Quantities In case sales order is cancelled

BEGIN

  INSERT INTO InvInventoryItemLog

   ( WarehouseKey, ProductKey, InQty, OutQty, OnFulfillmentQuanity,

    SourceType, SourceKey, SourceDetailKey,

    InventoryItemLogKey, UpdateType, Created, LocationKey, ReservedQuantity, IsProcessed

   )

  SELECT SalesOrder.DeliveryWarehouseKey, SalesOrder.ProductKey, 0, 0, -1 * (fd.Quantity - fd.QuantityFulfilled) As OnFulfillmentQuanity,

    9, SalesOrder.TransactionKey, SalesOrder.TransactionOrderKey,

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

      case when f.ReserveQuantity = 1 then -1 * (fd.Quantity - fd.QuantityFulfilled) else 0 end,

                      1 As IsProcessed --UPDATE ONLY THE FULFILLMENT QUANTITIES

  FROM     TrxTransaction h with(nolock)

       , TrxTransactionOrder SalesOrder with(nolock)

       , TrxTransactionOrder OriginalSalesOrder with(nolock)

       , TrxTransactionFulfillment f with(nolock)

       , TrxTransactionFulfillmentDetail fd with(nolock)

  WHERE h.TransactionKey     = SalesOrder.TransactionKey

  AND  SalesOrder.OriginalDocumentKey = OriginalSalesOrder.TransactionKey

  AND  SalesOrder.OriginalDetailKey = OriginalSalesOrder.TransactionOrderKey

  AND  OriginalSalesOrder.TransactionKey = f.TransactionKey

  AND  f.FulfillmentKey     = fd.FulfillmentKey

  AND  fd.SourceDetailKey     = OriginalSalesOrder.TransactionOrderKey

  AND  fd.SourceType     = 1   --DENOTES THAT THIS IS FOR FULFILLMENT

  AND  h.TransactionKey     = @pTransactionKey

  AND  OriginalSalesOrder.Status   = 1

  And  SalesOrder.Status     = 2

END

--Update the quantity with the QuantityFulfilled and mark this row as deleted in the original sales order

BEGIN

  UPDATE fd

  SET  fd.Quantity = fd.QuantityFulfilled,

    fd.IsDeleted = 1   -- DENOTES THAT THIS FULFILLMENT LINE IS DELETED

  FROM TrxTransaction h with(nolock)

   , TrxTransactionOrder SalesOrder with(nolock)

   , TrxTransactionOrder OriginalSalesOrder with(nolock)

   , TrxTransactionFulfillment f with(nolock)

   , TrxTransactionFulfillmentDetail fd with(nolock)

  WHERE h.TransactionKey     = SalesOrder.TransactionKey

  AND  SalesOrder.OriginalDocumentKey = OriginalSalesOrder.TransactionKey

  AND  SalesOrder.OriginalDetailKey = OriginalSalesOrder.TransactionOrderKey

  AND  OriginalSalesOrder.TransactionKey = f.TransactionKey

  AND  f.FulfillmentKey     = fd.FulfillmentKey

  AND  fd.SourceDetailKey     = OriginalSalesOrder.TransactionOrderKey

  AND  fd.SourceType     = 1   --DENOTES THAT THIS IS FOR FULFILLMENT

  AND  h.TransactionKey     = @pTransactionKey

  IF(@@ERROR <>0)

  BEGIN

  SET @ErrorDesc = 'ERROR WHILE UPDATING FULFILLMENT QUANTITY.'

  GOTO ERRORHANDLER

  END

END

---Generate replication entry for the original so-------------

BEGIN

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

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

  FROM TrxTransactionOrder A with(nolock)

   ,  TrxTransactionFulfillmentDetail B with(nolock)

   , TrxTransactionFulfillment C with(nolock)

  WHERE A.OriginalDocumentKey = B.TransactionKey

  AND  A.OriginalDetailKey = B.SourceDetailKey

  AND  B.FulfillmentKey = C.FulfillmentKey

  AND  B.SourceType   = 1 -- DENOTES THAT THIS IS FOR FULFILLMENT

  AND  A.OriginalDocumentKey !='0' -- DENOTES THIS ENTRY IS FOR EDITING OF THE PREVIOUS ENTRY

  AND  A.OriginalDetailKey !='0' -- DENOTES THIS ENTRY IS FOR EDITING OF THE PREVIOUS ENTRY

  AND  A.TransactionKey = @pTransactionKey

END

---Updating the surcharges for the fulfillment on the original transaction items ##################################

BEGIN

  Update Surcharge

  Set  Surcharge.Status = 1   -- DENOTES THAT THE SURCHARGES ARE DELETED

  FROM TrxTransaction h with(nolock)

   , TrxTransactionOrder SalesOrder with(nolock)

   , TrxTransactionOrder OriginalSalesOrder with(nolock)

   , TrxTransactionFulfillment f with(nolock)

   , TrxTransactionFulfillmentDetail fd with(nolock)

   , TrxTransactionSurcharge Surcharge with(nolock)

  WHERE h.TransactionKey     = SalesOrder.TransactionKey

  AND  SalesOrder.OriginalDocumentKey = OriginalSalesOrder.TransactionKey

  AND  SalesOrder.OriginalDetailKey = OriginalSalesOrder.TransactionOrderKey

  AND  OriginalSalesOrder.TransactionKey = f.TransactionKey

  AND  f.FulfillmentKey     = fd.FulfillmentKey

  AND  fd.SourceDetailKey     = OriginalSalesOrder.TransactionOrderKey

  AND  Surcharge.SourceKey     = f.FulfillmentKey

  AND  fd.SourceType     = 1   --DENOTES THAT THE FULFILLMENT IS FOR ORDER ITEM

  AND  Surcharge.SourceType   = 7 --DENOTES THAT THE SURCHARGE IS ON THE FULFILLMENT

  AND  h.TransactionKey     = @pTransactionKey

  IF(@@ERROR <>0)

  BEGIN

  SET @ErrorDesc = 'ERROR WHILE UPDATING THE SURCHAGE STATUS FOR THE ORDER ITEM.'

  GOTO ERRORHANDLER

  END

END

---Generate the replication entry for cancelling the surcharges against original SO

BEGIN

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

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

  from (

  SELECT DISTINCT D.TransactionSurchargeKey

  FROM TrxTransactionOrder A with(nolock)

       ,  TrxTransactionFulfillmentDetail B with(nolock)

       , TrxTransactionFulfillment C with(nolock)

       , TrxTransactionSurcharge D with(nolock)

  WHERE A.OriginalDocumentKey = B.TransactionKey

  AND  A.OriginalDetailKey = B.SourceDetailKey

  AND  B.FulfillmentKey = C.FulfillmentKey

  AND  B.SourceType   = 1 -- DENOTES THAT THIS IS FOR FULFILLMENT

  AND  A.OriginalDocumentKey !='0' -- DENOTES THIS ENTRY IS FOR EDITING OF THE PREVIOUS ENTRY

  AND  A.OriginalDetailKey !='0' -- DENOTES THIS ENTRY IS FOR EDITING OF THE PREVIOUS ENTRY

  AND  C.FulfillmentKey = D.SourceKey

  AND  D.SourceType   = 7 -- DENOTES THIS IS SURCHARGE ON FULFILLMENT

  AND  A.TransactionKey = @pTransactionKey

  ) Final

  IF(@Error <>0)

  BEGIN

  SET @ErrorDesc = 'ERROR WHILE GENERATING THE REPLICATION ENTRY FOR THE FULFILLMENT SURCHARGES ON SO UPDATION.'

  GOTO ERRORHANDLER

  END

END

RETURN

ERRORHANDLER:

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

RETURN

END