iVend Database Database : BatchQuantityUpdates Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@pInventoryBatchLogKey

In

Reference key of the Inventory Batch 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

ExecuteInventoryItemLogUpdate

Stored Procedure

1

TrgSBOBatchUpdate

Trigger

Handles updates made to Batch table from ERP system (Business One in this case)

1

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

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

InvWarehouse

Table

Defines a list of all warehouses defined in the system.

1

CREATE PROCEDURE [dbo].[BatchQuantityUpdates]

(

@pInventoryBatchLogKey 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 ISNULL(A.UpdateType, 0) IN(0, 1) THEN B.AvailableQuantity + A.InQty - A.OutQty - ISNULL(A.AllocatedQuantity, 0) ELSE B.AvailableQuantity END,

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

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

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

  B.Modified = dbo.GetCompanyDateTime() ,

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

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

FROM  InvBatchLog A,  InvBatch B

WHERE A.ProductKey = B.ProductKey

AND  A.WarehouseKey = B.WarehouseKey

AND  A.BatchKey = B.BatchKey

AND  A.InventoryBatchLogKey = @pInventoryBatchLogKey

IF EXISTS (SELECT 1 FROM InvWarehouse A, InvBatchLog B WHERE A.IsLocationEnabled=1 AND A.WarehouseKey = B.WarehouseKey AND B.InventoryBatchLogKey = @pInventoryBatchLogKey)

BEGIN

  Update B

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

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

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

   B.Modified = dbo.GetCompanyDateTime() ,

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

  FROM  InvBatchLog A,  InvBatchLocation B

  WHERE A.ProductKey = B.ProductKey

  AND  A.WarehouseKey = B.WarehouseKey

  AND  A.BatchKey = B.BatchKey

  And  a.LocationKey = b.LocationKey

  AND  A.InventoryBatchLogKey = @pInventoryBatchLogKey

END

  --THIS UPDATED THE IsProcessed FLAG OG LOG TABLE

Update InvBatchLog SET IsProcessed = 'true' Where InventoryBatchLogKey = @pInventoryBatchLogKey

IF(@@ERROR <>0)

    BEGIN

  SET @ErrorDesc = 'Error while updating the batch quantities.'

        GOTO ERRORHANDLER

END

RETURN

ERRORHANDLER:

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

RETURN

END