iVend Database Database : CancelStockTransfer Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@pStockTransferKey

In

VarWChar

50

@RETURN_VALUE

Return Value

Integer

4

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

1

GetCompanyDateTime

User Defined Function

1

InvBatch

Table

Batch details gets affected when doing any transaction with item which has been batch tracked.

1

InvBatchLocation

Table

Describes the batch information with Location, Warehouse, Product and Expiry date

1

InvBatchLog

Table

Stores the Delta updates made to any batch in the system. This table gets affected when doing any inventory related transaction for a batch item.

1

InvGoodReceipt

Table

Stores the Goods receipt done in the system.

1

InvGoodReceiptDetail

Table

Defines the details associated with each goods receipt

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

InvInventoryLocation

Table

Stores the location information for the Inventory available at different locations

1

InvSerial

Table

Serial details gets affected when doing any transaction with the item which has been serial tracked.

1

InvSerialLog

Table

Stores the Delta updates made to any serial in the system. This table gets affected when doing any inventory related transaction for a serial item.

1

InvStockTransfer

Table

Stores stock transfer details

1

InvStockTransferBatch

Table

This gets affected in case the stock transfer is done for a batch product

1

InvStockTransferDetail

Table

Detail table which stores the stock transfer detail information.

1

InvStockTransferRequest

Table

Defines the information for the generation of the Stock Transfer request

1

InvStockTransferRequestDetail

Table

Stores the detailed information for a generated Stock Transfer request

1

InvStockTransferSerial

Table

This gets affected in case the stock transfer is done for a serially tracked product

1

InvWarehouse

Table

Defines a list of all warehouses defined in the system.

1

RepReplicationTransaction

Table

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

1

CREATE PROCEDURE [dbo].[CancelStockTransfer]

(

@pStockTransferKey     nvarchar(50)

)

AS

BEGIN

DECLARE @pSiteId INT,

  @ErrorDesc varchar(255),

  @BatchKey nvarchar(50),

  @pPOSSiteId INT

SELECT @pSiteId = SiteId, @pPOSSiteId = ISNULL(POSSiteId, 0) FROM CfgSiteInformation

select @BatchKey =NEWID()

IF EXISTS (SELECT 1 FROM InvGoodReceipt A, InvGoodReceiptDetail B

WHERE A.SourceType = 2

AND A.GoodsReceiptKey = B.GoodsReceiptKey

AND B.SourceKey = @pStockTransferKey)

BEGIN

  UPDATE InvStockTransfer SET Status = 0 Where StockTransferKey = @pStockTransferKey AND Status <> 1

  EXEC DataNotification @pSourceType =53,

        @pSourceKey =@pStockTransferKey,

        @pBatchKey =@BatchKey,

        @pOperationType = 1,

        @pUserKey = '-1',

        @pDebug = 'FALSE'

  RETURN

END

UPDATE InvStockTransfer SET Status = 3 Where StockTransferKey = @pStockTransferKey

EXEC DataNotification @pSourceType =53,

        @pSourceKey =@pStockTransferKey,

        @pBatchKey =@BatchKey,

        @pOperationType = 1,

        @pUserKey = '-1',

        @pDebug = 'FALSE'

--EXEC GetNextSiteNumber @pSiteId,'Table.InventoryItemLog', @InventoryItemLogKey out, 0, @pPOSSiteId

--SET @beforeInventoryItemLogKey = @InventoryItemLogKey

--REVERSE THE QUANTITY OF THE WAREHOUSE FROM WHERE THE STOCK TRANSFER WAS CREATED

INSERT INTO InvInventoryItemLog

  (

   WarehouseKey, ProductKey, InQty, OutQty, OnFulfillmentQuanity,

   SourceType, SourceKey, SourceDetailKey,

   InventoryItemLogKey, UpdateType, Created, ItemCost, IsProcessed, LocationKey

  )

SELECT A.FromWarehouseKey, B.ProductKey, B.Quantity, 0, 0,

  14, A.StockTransferKey, b.StockTransferDetailKey,

  NEWID(),

  0, dbo.GetCompanyDateTime(), B.Price, 1 As IsProcessed

   , ISNULL(B.FromLocationKey, '0')

FROM InvStockTransfer A, InvStockTransferDetail B

Where A.StockTransferKey = B.StockTransferKey

And A.StockTransferKey = @pStockTransferKey

--SELECT @InventoryItemLogKey = @InventoryItemLogKey + @@RowCount

--UPDATE THE QUANTITIES IN THE CURRENT STORE

UPDATE D

SET D.AvailableQuantity = D.AvailableQuantity + B.Quantity,

  D.InStockQuantity = D.InStockQuantity + B.Quantity

FROM InvStockTransfer A, InvStockTransferDetail B, InvInventoryItem D

Where A.StockTransferKey = B.StockTransferKey

And A.StockTransferKey = @pStockTransferKey

AND D.ProductKey = B.ProductKey

And A.FromWarehouseKey = D.WarehouseKey

--updates the quantity of the locations in case the locations are enabled in the system

UPDATE D

SET D.AvailableQuantity = D.AvailableQuantity + B.Quantity,

  D.InStockQuantity = D.InStockQuantity + B.Quantity

FROM InvStockTransfer A, InvStockTransferDetail B, InvInventoryLocation D

Where A.StockTransferKey = B.StockTransferKey

And  A.StockTransferKey = @pStockTransferKey

AND B.ProductKey = D.ProductKey

And  A.FromWarehouseKey = D.WarehouseKey

And  B.FromLocationKey = D.LocationKey

--REVERSE THE QUANTITY OF THE INTRANSIT WAREHOUSE

INSERT INTO InvInventoryItemLog

  ( WarehouseKey, ProductKey, InQty, OutQty, OnFulfillmentQuanity,

   SourceType, SourceKey, SourceDetailKey,

   InventoryItemLogKey, UpdateType, Created, ItemCost, IsProcessed, LocationKey

  )

SELECT C.InTransitWarehouseKey, B.ProductKey, 0, B.Quantity, 0,

  14, A.StockTransferKey, b.StockTransferDetailKey,

  NEWID(),

  0, dbo.GetCompanyDateTime(), B.Price, 1 As IsProcessed

   , '0' As LocationKey

FROM InvStockTransfer A, InvStockTransferDetail B, InvWarehouse C

Where A.StockTransferKey = B.StockTransferKey

And A.StockTransferKey = @pStockTransferKey

And C.WarehouseKey = A.ToWarehouseKey

--SELECT @InventoryItemLogKey = @InventoryItemLogKey + @@RowCount

--UPDATE THE QUANTITIES OF THE INTRANSIT IN THE CURRENT STORE

UPDATE D

SET D.AvailableQuantity = D.AvailableQuantity - B.Quantity,

  D.InStockQuantity = D.InStockQuantity - B.Quantity

FROM InvStockTransfer A, InvStockTransferDetail B,InvWarehouse C, InvInventoryItem D

Where A.StockTransferKey = B.StockTransferKey

And A.StockTransferKey = @pStockTransferKey

And C.WarehouseKey = A.ToWarehouseKey

AND D.WarehouseKey = C.InTransitWarehouseKey

And B.ProductKey = D.ProductKey

--UPDATE THE INTRANSIT QUANTITIES OF THE CURRENT STORE

UPDATE D

SET D.InTransitQuantity = D.InTransitQuantity - B.Quantity

FROM InvStockTransfer A, InvStockTransferDetail B,InvWarehouse C, InvInventoryItem D

Where A.StockTransferKey = B.StockTransferKey

And A.StockTransferKey = @pStockTransferKey

And C.WarehouseKey = A.ToWarehouseKey

AND D.WarehouseKey = C.WarehouseKey

And B.ProductKey = D.ProductKey

INSERT INTO RepReplicationTransaction

  (

   ReplicationTransactionKey, SourceType, SourceKey

   , BatchKey,OperationType,Flag

  )

SELECT NEWID(),

    172, InventoryItemLogKey,

    @BatchKey,

    0, 'FALSE'

  FROM InvInventoryItemLog

  WHERE SourceType = 14

  AND SourceKey = @pStockTransferKey

IF EXISTS(SELECT 1 FROM InvStockTransferDetail A, InvStockTransferSerial B Where A.StockTransferDetailKey = B.StockTransferDetailKey and A.StockTransferKey = @pStockTransferKey)

BEGIN

  --INSERT serials REcords For the FromWarehouse as status ''Avaialable''

  INSERT INTO InvSerialLog (InventorySerialLogKey, SerialKey, WarehouseKey, ProductKey, Status, SourceType, SourceKey, SourceDetailKey, IsProcessed)

  SELECT NEWID(), E.SerialKey, A.FromWarehouseKey, B.ProductKey, 1, 14, A.StockTransferKey, B.StockTransferDetailKey, 1 As IsProcessed

  FROM  InvStockTransfer A

  INNER JOIN InvStockTransferDetail B ON A.StockTransferKey = B.StockTransferKey

  INNER JOIN InvStockTransferSerial C ON B.StockTransferDetailKey = C.StockTransferDetailKey

  INNER JOIN InvSerial D ON C.SerialKey = D.SerialKey

  INNER JOIN InvSerial E ON D.SerialNumber = E.SerialNumber AND D.ProductKey = E.ProductKey AND A.FromWarehouseKey = E.WarehouseKey

  WHERE A.StockTransferKey = @pStockTransferKey

  --INSERT serials REcords For the IntransitWarehouse as status ''Not Avaialable''

  INSERT INTO InvSerialLog (InventorySerialLogKey, SerialKey, WarehouseKey, ProductKey, Status, SourceType, SourceKey, SourceDetailKey, IsProcessed)

  SELECT NEWID(), SerialKey, D.InTransitWarehouseKey, B.ProductKey, 0, 14, A.StockTransferKey, B.StockTransferDetailKey, 1 As IsProcessed

  FROM  InvStockTransfer A

  INNER JOIN InvStockTransferDetail B ON A.StockTransferKey = B.StockTransferKey

  INNER JOIN InvStockTransferSerial C ON B.StockTransferDetailKey = C.StockTransferDetailKey

  INNER JOIN InvWarehouse D ON A.ToWarehouseKey = D.WarehouseKey

  WHERE A.StockTransferKey = @pStockTransferKey

  UPDATE B

  SET B.Status = A.Status

  FROM  InvSerialLog A,  InvSerial B

  WHERE A.SerialKey = B.SerialKey

  AND  A.SourceType = 14

  AND A.SourceKey = @pStockTransferKey

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

  SELECT NEWID(), 173, InventorySerialLogKey, @BatchKey, 0, 'FALSE'

  FROM InvSerialLog

  WHERE SourceType = 14

  And SourceKey = @pStockTransferKey

END

IF EXISTS(SELECT 1 FROM InvStockTransferDetail A, InvStockTransferBatch B Where A.StockTransferDetailKey = B.StockTransferDetailKey and A.StockTransferKey = @pStockTransferKey)

BEGIN

  INSERT INTO InvBatchLog (InventoryBatchLogKey, BatchKey,WarehouseKey, ProductKey, InQty, OutQty, SourceType, SourceKey, SourceDetailKey, IsProcessed)

  SELECT NEWID(), E.BatchKey,A.FromWarehouseKey, B.ProductKey, C.Quantity InQty, 0 OutQty, 14, A.StockTransferKey, B.StockTransferDetailKey, 1 As IsProcessed

  FROM  InvStockTransfer A

  INNER JOIN InvStockTransferDetail B ON A.StockTransferKey = B.StockTransferKey

  INNER JOIN InvStockTransferBatch C ON B.StockTransferDetailKey = C.StockTransferDetailKey

  INNER JOIN InvBatch D ON C.BatchKey = D.BatchKey

  INNER JOIN InvBatch E ON D.BatchNumber = E.BatchNumber AND D.ProductKey = E.ProductKey AND A.FromWarehouseKey = E.WarehouseKey

  WHERE A.StockTransferKey = @pStockTransferKey

  INSERT INTO InvBatchLog (InventoryBatchLogKey, BatchKey,WarehouseKey, ProductKey, InQty, OutQty, SourceType, SourceKey, SourceDetailKey, IsProcessed)

  SELECT NEWID(), BatchKey,D.InTransitWarehouseKey, ProductKey, 0, C.Quantity, 14, A.StockTransferKey, B.StockTransferDetailKey, 1 As IsProcessed

  FROM  InvStockTransfer A

  INNER JOIN InvStockTransferDetail B ON A.StockTransferKey = B.StockTransferKey

  INNER JOIN InvStockTransferBatch C ON B.StockTransferDetailKey = C.StockTransferDetailKey

  INNER JOIN InvWarehouse D ON A.ToWarehouseKey = D.WarehouseKey

  WHERE A.StockTransferKey = @pStockTransferKey

  Update B

  Set B.AvailableQuantity = B.AvailableQuantity + A.InQty - A.OutQty,

     B.InStockQuantity = B.InStockQuantity + A.InQty - A.OutQty,

     B.Status = case when B.AvailableQuantity + A.InQty - A.OutQty > 0 then 1 else 0 end

  FROM InvBatchLog A,  InvBatch B

  WHERE A.ProductKey = B.ProductKey

  AND  A.WarehouseKey = B.WarehouseKey

  AND  A.BatchKey = B.BatchKey

  AND  A.SourceKey= @pStockTransferKey

  AND  A.SourceType = 14

  Update C

  Set C.AvailableQuantity = C.AvailableQuantity + A.InQty - A.OutQty,

     C.InStockQuantity = C.InStockQuantity + A.InQty - A.OutQty,

     C.Status = case when C.AvailableQuantity + A.InQty - A.OutQty > 0 then 1 else 0 end

  FROM InvBatchLog A,  InvBatchLocation C

  WHERE A.SourceKey= @pStockTransferKey

  And   A.LocationKey = C.LocationKey

  And   A.BatchKey = C.BatchKey

  And   A.ProductKey = C.ProductKey

  And   A.WarehouseKey = C.WarehouseKey

  AND  A.SourceType = 14

  INSERT INTO RepReplicationTransaction(ReplicationTransactionKey, SourceType,

    SourceKey, BatchKey,OperationType,Flag)

  SELECT NEWID(), 174, InventoryBatchLogKey,

    @BatchKey, 0, 'FALSE'

  FROM InvBatchLog

  WHERE SourceType = 14

  And SourceKey = @pStockTransferKey

END

--- Open Stock Transfer request Start

Select STD.SourceKey, STD.OriginalDocumentSourceKey into #StockTransferRequest from InvStockTransferRequestDetail STRD JOIN  InvStockTransferDetail STD

ON STRD.StockTransferRequestDetailKey = STD.SourceKey

and STRD.StockTransferRequestKey = STD.OriginalDocumentSourceKey

and STD.StockTransferKey in (@pStockTransferKey)

and STD.UOMQuantityOpen > 0

Update STRD set Status = 0 from InvStockTransferRequestDetail STRD JOIN  InvStockTransferDetail STD

ON STRD.StockTransferRequestDetailKey = STD.SourceKey

and STRD.StockTransferRequestKey = STD.OriginalDocumentSourceKey

and STD.StockTransferKey = @pStockTransferKey

and STD.UOMQuantityOpen > 0

Update InvStockTransferRequest Set Status = 0 Where StockTransferRequestKey in

 (Select OriginalDocumentSourceKey From InvStockTransferDetail Where StockTransferKey = @pStockTransferKey)

-- DECLARE the cursor

DECLARE CUR CURSOR FAST_FORWARD READ_ONLY FOR SELECT SourceKey,OriginalDocumentSourceKey FROM #StockTransferRequest

-- DECLARE some variables to store the values in

DECLARE @Added bit

SET @Added = 0

DECLARE @StockTransferRequestKey nvarchar(50)

DECLARE @StockTransferRequestDetailKey nvarchar(50)

-- Use the cursor

OPEN CUR

FETCH NEXT FROM CUR INTO @StockTransferRequestKey, @StockTransferRequestDetailKey

WHILE @@FETCH_STATUS = 0

BEGIN

if(@Added = 0)

BEGIN

  EXEC DataNotification @pSourceType = 215,

        @pSourceKey = @StockTransferRequestKey,

        @pBatchKey =@BatchKey,

        @pOperationType = 1,

        @pUserKey = '-1',

        @pDebug = 'FALSE'

  SET @Added = 1

END

EXEC DataNotification @pSourceType = 216,

        @pSourceKey = @StockTransferRequestDetailKey,

        @pBatchKey =@BatchKey,

        @pOperationType = 1,

        @pUserKey = '-1',

        @pDebug = 'FALSE'

  FETCH NEXT FROM CUR INTO @StockTransferRequestKey, @StockTransferRequestDetailKey

END

CLOSE CUR

DEALLOCATE CUR

--- Open Stock Transfer request END

RETURN

ERRORHANDLER:

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

RETURN

END