iVend Database Database : BatchLocationQuantityUpdates Stored Procedure

Creation Date

9/18/2015 10:23 AM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@pInventoryBatchLocationLogKey

In

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

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

InvBatchLocation

Table

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

1

InvBatchLocationLog

Table

Stores each record processed for the batch with location deifned.

1

CREATE PROCEDURE [dbo].[BatchLocationQuantityUpdates]

(

@pInventoryBatchLocationLogKey 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 = B.AvailableQuantity + A.InQty - A.OutQty - isnull(A.AllocatedQuantity,0),

   B.InStockQuantity = B.InStockQuantity + A.InQty - A.OutQty - isnull(A.AllocatedQuantity,0),

   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  InvBatchLocationLog 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.BatchLocationLogKey = @pInventoryBatchLocationLogKey

IF(@@ERROR <>0)

    BEGIN

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

        GOTO ERRORHANDLER

END

RETURN

ERRORHANDLER:

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

RETURN

END