iVend Database Database : InventoryItemSerialStatusUpdates Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@pInventorySerialLogKey

In

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

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

InvSerial

Table

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

1

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

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

CREATE PROCEDURE [dbo].[InventoryItemSerialStatusUpdates]

(

@pInventorySerialLogKey nvarchar(50),

@pDebug   CHAR(1) = 'N'

)

AS

BEGIN

SET NOCOUNT ON

Declare @ErrorDesc varchar(255)

If @pDebug = 'Y'

  SELECT 'UPDATE THE INVENTORY STATUS OF THE SERIAL ITEM'

  UPDATE B

  SET B.Status = A.Status,

  B.Modified = dbo.GetCompanyDateTime()

  FROM  InvSerialLog A,  InvSerial B

  WHERE A.SerialKey = B.SerialKey

  AND  A.InventorySerialLogKey = @pInventorySerialLogKey

IF(@@ERROR <>0)

    BEGIN

  SET @ErrorDesc = 'Error while updating the status of serial item.'

        GOTO ERRORHANDLER

END

--THIS UPDATED THE IsProcessed FLAG OG LOG TABLE

Update InvSerialLog SET IsProcessed = 'true' Where InventorySerialLogKey = @pInventorySerialLogKey

RETURN

ERRORHANDLER:

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

RETURN

END