iVend Database Database : ExecuteInventoryItemLogUpdate Stored Procedure

Creation Date

10/12/2015 6:29 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

BatchQuantityUpdates

Stored Procedure

1

CfgEnterprise

Table

Stores the basic configuration entries for the system . These settings are mandatory for the system to work properly.

3

CfgSiteInformation

Table

Stores the site information. Enterprise always has the site id as 1. System Table Warning : Should not be modified directly

3

GetCompanyDateTime

User Defined Function

2

GiftCertificateUpdate

Stored Procedure

1

InvBatch

Table

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

2

InvBatchLocation

Table

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

2

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

InventoryItemSerialStatusUpdates

Stored Procedure

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

InvSerial

Table

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

2

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

InvWarehouse

Table

Defines a list of all warehouses defined in the system.

2

PmtGiftCertificate

Table

Used to store the values for the Gift Certificate issued

2

PmtGiftCertificateLog

Table

Stores the log for every Gift certificate used

1

CREATE PROCEDURE [dbo].[ExecuteInventoryItemLogUpdate]

AS

BEGIN

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

-- Process Inventory Item Log

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

SELECT InventoryItemLogKey INTO #TempInventoryItemLog FROM InvInventoryItemLog WHERE IsProcessed = 0 OR IsProcessed Is NULL

DECLARE @ItemLogKey nvarchar(50)

DECLARE InventoryItemLogCursor CURSOR FOR SELECT * FROM #TempInventoryItemLog

OPEN InventoryItemLogCursor

FETCH NEXT FROM InventoryItemLogCursor INTO @ItemLogKey

WHILE (@@FETCH_STATUS=0)

BEGIN

EXEC InventoryItemQuantityUpdates @ItemLogKey

FETCH NEXT FROM InventoryItemLogCursor INTO @ItemLogKey

END

CLOSE InventoryItemLogCursor

DEALLOCATE InventoryItemLogCursor

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

-- Process Serial Log

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

SELECT InventorySerialLogKey INTO #TempSerialLog FROM InvSerialLog WHERE IsProcessed = 0 OR IsProcessed Is NULL

DECLARE @SerialLogKey nvarchar(50)

DECLARE SerialLogCursor CURSOR FOR SELECT * FROM #TempSerialLog

OPEN SerialLogCursor

FETCH NEXT FROM SerialLogCursor INTO @SerialLogKey

WHILE (@@FETCH_STATUS=0)

BEGIN

EXEC InventoryItemSerialStatusUpdates @SerialLogKey

FETCH NEXT FROM SerialLogCursor INTO @SerialLogKey

END

CLOSE SerialLogCursor

DEALLOCATE SerialLogCursor

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

-- Process Batch Log

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

SELECT InventoryBatchLogKey INTO #TempBatchlLog FROM InvBatchLog WHERE IsProcessed = 0 OR IsProcessed Is NULL

DECLARE @BatchLogKey nvarchar(50)

DECLARE BatchLogCursor CURSOR FOR SELECT * FROM #TempBatchlLog

OPEN BatchLogCursor

FETCH NEXT FROM BatchLogCursor INTO @BatchLogKey

WHILE (@@FETCH_STATUS=0)

BEGIN

EXEC BatchQuantityUpdates @BatchLogKey

FETCH NEXT FROM BatchLogCursor INTO @BatchLogKey

END

CLOSE BatchLogCursor

DEALLOCATE BatchLogCursor

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

-- Process Gift Certificate Log

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

SELECT GiftCertificateLogKey INTO #TempGiftCertificatelLog FROM PmtGiftCertificateLog WHERE IsProcessed = 0 OR IsProcessed Is NULL

DECLARE @GiftCertificateLogKey nvarchar(50)

DECLARE GiftCertificateLogCursor CURSOR FOR SELECT * FROM #TempGiftCertificatelLog

OPEN GiftCertificateLogCursor

FETCH NEXT FROM GiftCertificateLogCursor INTO @GiftCertificateLogKey

WHILE (@@FETCH_STATUS=0)

BEGIN

EXEC GiftCertificateUpdate @GiftCertificateLogKey

FETCH NEXT FROM GiftCertificateLogCursor INTO @GiftCertificateLogKey

END

CLOSE GiftCertificateLogCursor

DEALLOCATE GiftCertificateLogCursor

END

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