iVend Database Database : InventoryLocationQuantityUpdates Stored Procedure

Creation Date

9/18/2015 10:23 AM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@pInventoryLocationLogKey

In

Reference key of the Inventory Item Log.

VarWChar

50

@pDebug

In

Whether this procedure has to be executed in Debug mode or not

VarChar

1

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

InvInventoryLocation

Table

Stores the location information for the Inventory available at different locations

1

InvInventoryLocationLog

Table

Stores the logs or Audit information of location for the Inventory available at different locations

1

CREATE PROCEDURE [dbo].[InventoryLocationQuantityUpdates]

(

@pInventoryLocationLogKey NVARCHAR(50),

@pDebug     CHAR(1) = 'N'

)

AS

BEGIN

SET NOCOUNT ON

Declare @ErrorDesc varchar(255)

If @pDebug = 'Y'

  SELECT 'UPDATE THE INVENTORY'

  UPDATE B

  Set B.AvailableQuantity = CASE WHEN UpdateType IN(0, 1) THEN B.AvailableQuantity + A.InQty - A.OutQty - ISNULL(A.AllocatedQuantity, 0) ELSE B.AvailableQuantity END,

   B.InStockQuantity = CASE WHEN UpdateType IN(0, 2, 3) THEN B.InStockQuantity + A.InQty - A.OutQty ELSE B.InStockQuantity END,

   B.OnFulFillmentQuantity = B.OnFulFillmentQuantity + ISNULL(A.OnFulfillmentQuanity,0),

   B.AllocatedQuantity = ISNULL(B.AllocatedQuantity, 0) + ISNULL(A.AllocatedQuantity, 0),

   B.Modified = A.Created

  FROM  InvInventoryLocationLog A,  InvInventoryLocation B

  WHERE A.ProductKey = B.ProductKey

  AND  A.WarehouseKey = B.WarehouseKey

  AND  A.LocationKey   = B.LocationKey

  AND  A.InventoryLocationLogKey = @pInventoryLocationLogKey

IF(@@ERROR <>0)

    BEGIN

  SET @ErrorDesc = 'Error while updating inventory'

        GOTO ERRORHANDLER

END

RETURN

ERRORHANDLER:

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

RETURN

END