iVend Database Database : InventoryItemQuantityUpdates Stored Procedure

Creation Date

7/5/2019 6:02 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@pInventoryItemLogKey

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

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

1

GetCompanyDateTime

User Defined Function

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

InvProduct

Table

Stores the product related details.

1

InvProductCostUpdateLog

Table

Stores the log information for the Product cost

1

InvWarehouse

Table

Defines a list of all warehouses defined in the system.

1

RtlStore

Table

Contains the details of Stores defined in the system.

1

--==========================================================

CREATE PROCEDURE [dbo].[InventoryItemQuantityUpdates]

(

@pInventoryItemLogKey nvarchar(50),

@pDebug     CHAR(1) = 'N'

)

AS

BEGIN

SET NOCOUNT ON

Declare @ErrorDesc varchar(255)

If @pDebug = 'Y'

  SELECT 'UPDATE THE INVENTORY'

  DECLARE @lProductKey   nvarchar(50),

    @lWarehouseKey   nvarchar(50),

    @lInventoryItemKey nvarchar(50),

    @lProductCost   DECIMAL(20,5),

    @lInStockQuantity DECIMAL(20,5),

    @lSourceDocumentType INT,

    @lQuantityReceived DECIMAL(20,5),

    @lPrice     DECIMAL(20,5),

    @lTotalCost   DECIMAL(20,5),

    @lAverageCost   DECIMAL(20,5),

    @lInQuantity   DECIMAL(20,5),

    @lItemCost   DECIMAL(20,5),

    @lUpdateType   INT,

    @lSiteId   INT,

    @lERPSystemType   INT,

    @lPOSSiteId   INT,

    @lSubsidiaryKey   nvarchar(50),

    @lCurrentWarehouseKey nvarchar(50)

  SELECT @lSiteId = SiteId, @lPOSSiteId = ISNULL(POSSiteId, 0) FROM CfgSiteInformation

  SELECT @lCurrentWarehouseKey = WarehouseKey From RtlStore With(nolock) Where SiteId = @lSiteId

  IF EXISTS(SELECT 1 FROM InvInventoryItemLog WITH (NOLOCK) Where InventoryItemLogKey = @pInventoryItemLogKey And SourceType = 16)

  BEGIN

  Update B

  Set B.AverageCost = A.ItemCost

  FROM InvInventoryItemLog A, InvInventoryItem B

  Where A.ProductKey = B.ProductKey

  And A.WarehouseKey = B.WarehouseKey

  And A.InventoryItemLogKey = @pInventoryItemLogKey

  And A.SourceType = 16

  Update InvInventoryItemLog

  Set UpdatedAverageCost = ItemCost

  Where InventoryItemLogKey = @pInventoryItemLogKey

  return

  END

  SELECT @lProductKey = A.ProductKey

  From InvInventoryItem A, InvInventoryItemLog B WITH (NOLOCK)

  Where A.ProductKey = B.ProductKey and A.WarehouseKey = b.WarehouseKey

  And B.InventoryItemLogKey = @pInventoryItemLogKey

  SELECT @lERPSystemType = ERPSystemType From CfgEnterprise WITH (NOLOCK)

  SELECT

  @lWarehouseKey = A.WarehouseKey,

  @lSubsidiaryKey = ISNULL(B.SubsidiaryKey, '0')

  From InvInventoryItemLog A, InvWarehouse B

  Where InventoryItemLogKey = @pInventoryItemLogKey

  And A.WarehouseKey = b.WarehouseKey

  --Recalculate the average pricing only if you are HO or the inventory update is generated from the current warehouse

  IF @lERPSystemType = 0 AND (ISNULL(@lCurrentWarehouseKey, '0') = @lWarehouseKey or @lSiteId = 1)

    And EXISTS (SELECT 1 FROM InvProduct With(nolock) Where ProductKey = @lProductKey AND CostingMethod = 1 AND CostingSubMethod = 1)

  BEGIN

    SELECT @lProductKey = A.ProductKey,

      @lProductCost = MIN(B.AverageCost),

      @lInStockQuantity = SUM(B.InStockQuantity),

      @lSourceDocumentType = a.SourceType,

      @lInQuantity = A.InQty,

      @lItemCost = A.ItemCost,

      @lUpdateType = a.UpdateType

    FROM InvInventoryItemLog A WITH (NOLOCK), InvInventoryItem B

    WHERE InventoryItemLogKey = @pInventoryItemLogKey

    AND  A.ProductKey = B.ProductKey

    AND  B.WarehouseKey IN (SELECT WarehouseKey From InvWarehouse Where SubsidiaryKey IN(@lSubsidiaryKey , '0'))

    GROUP BY A.ProductKey, A.SourceType, A.InQty, A.ItemCost, A.UpdateType

    IF @lInQuantity > 0 AND @lSourceDocumentType IN (1,2,3,7) AND @lUpdateType != 1

    BEGIN

      --if the net quantity is less than 1 then retain the earlier cost

      IF @lInQuantity + @lInStockQuantity > 0

      BEGIN

      Set @lTotalCost = @lProductCost * CASE WHEN @lInStockQuantity < 0 THEN 0 ELSE @lInStockQuantity END + @lInQuantity * @lItemCost

      Set @lAverageCost = @lTotalCost/CASE WHEN (CASE WHEN @lInStockQuantity < 0 THEN 0 ELSE @lInStockQuantity END + @lInQuantity) = 0 THEN 1 ELSE (CASE WHEN @lInStockQuantity < 0 THEN 0 ELSE @lInStockQuantity END + @lInQuantity) END

      Update InvInventoryItem

      SET AverageCost = @lAverageCost

      WHERE ProductKey = @lProductKey

      And WarehouseKey IN (SELECT WarehouseKey From InvWarehouse Where SubsidiaryKey IN(@lSubsidiaryKey , '0'))

      IF @lSiteId = 1

      BEGIN

        DECLARE @ProductCostUpdateLogKey nvarchar(50)

        SELECT @ProductCostUpdateLogKey = NEWID()

        INSERT INTO InvProductCostUpdateLog(ProductCostUpdateLogKey, InventoryItemLogKey, ProductKey, UpdatedCost, [TimeStamp], SubsidiaryKey)

        Select @ProductCostUpdateLogKey, @pInventoryItemLogKey , @lProductKey, @lAverageCost, dbo.GetCompanyDateTime(), @lSubsidiaryKey

        exec DataNotification @pSourceType = 325, @pSourceKey = @ProductCostUpdateLogKey, @pBatchKey = 0, @pOperationType = 0, @pUserKey = -1, @pDebug= 'false'

      END

      Update InvInventoryItemLog Set UpdatedAverageCost = @lAverageCost Where InventoryItemLogKey = @pInventoryItemLogKey

      END

    END

  END

  IF @lERPSystemType = 0 AND EXISTS (SELECT 1 FROM InvProduct WITH (NOLOCK) Where ProductKey = @lProductKey AND CostingMethod = 1 AND CostingSubMethod = 2)

  BEGIN

    SELECT @lProductKey = A.ProductKey,

      @lWarehouseKey = B.WarehouseKey ,

      @lInventoryItemKey = B.InventoryItemKey,

      @lProductCost = ISNULL(B.AverageCost, 0),

      @lInStockQuantity = B.InStockQuantity,

      @lSourceDocumentType = a.SourceType,

      @lInQuantity = A.InQty,

      @lItemCost = A.ItemCost,

      @lUpdateType = a.UpdateType

    FROM InvInventoryItemLog A WITH (NOLOCK), InvInventoryItem B

    WHERE InventoryItemLogKey = @pInventoryItemLogKey

    AND  A.ProductKey = B.ProductKey

    AND  A.WarehouseKey = B.WarehouseKey

    IF @lInQuantity > 0 AND @lSourceDocumentType IN (1,2,3,7) AND @lUpdateType != 1

    BEGIN

      IF @lInQuantity + @lInStockQuantity > 0

      BEGIN

        Set @lTotalCost = @lProductCost * CASE WHEN @lInStockQuantity < 0 THEN 0 ELSE @lInStockQuantity END + @lInQuantity * @lItemCost

        Set @lAverageCost = @lTotalCost/CASE WHEN (CASE WHEN @lInStockQuantity < 0 THEN 0 ELSE @lInStockQuantity END + @lInQuantity) = 0 THEN 1 ELSE (CASE WHEN @lInStockQuantity < 0 THEN 0 ELSE @lInStockQuantity END + @lInQuantity) END

        Update InvInventoryItem

        SET AverageCost = @lAverageCost

        WHERE InventoryItemKey = @lInventoryItemKey

        Update InvInventoryItemLog Set UpdatedAverageCost = @lAverageCost Where InventoryItemLogKey = @pInventoryItemLogKey

      END

    END

  END

  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.ReservedQuantity = ISNULL(B.ReservedQuantity, 0) + ISNULL(A.ReservedQuantity, 0),

   B.Modified = A.Created

  FROM  InvInventoryItemLog A WITH (NOLOCK),  InvInventoryItem B

  WHERE A.ProductKey = B.ProductKey

  AND  A.WarehouseKey = B.WarehouseKey

  AND  A.InventoryItemLogKey = @pInventoryItemLogKey

  ---THIS UPDATES THE LOCATION 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  InvInventoryItemLog A WITH (NOLOCK),  InvInventoryLocation B

  WHERE A.InventoryItemLogKey = @pInventoryItemLogKey

  And  A.LocationKey = B.LocationKey

  And  A.ProductKey = B.ProductKey

  And  A.WarehouseKey = B.WarehouseKey

  --THIS UPDATED THE IsProcessed FLAG OG LOG TABLE

  Update InvInventoryItemLog SET IsProcessed = 'true' Where InventoryItemLogKey = @pInventoryItemLogKey

IF(@@ERROR <>0)

    BEGIN

  SET @ErrorDesc = 'Error while updating inventory'

        GOTO ERRORHANDLER

END

RETURN

ERRORHANDLER:

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

RETURN

END