iVend Database Database : UpdateInventoryCost Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@pProductCostUpdateLogKey

In

VarWChar

50

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

InvInventoryItem

Table

Stores inventory details for each Product for each Warehouse

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

CREATE PROCEDURE [dbo].[UpdateInventoryCost]

(

@pProductCostUpdateLogKey nvarchar(50)

)

AS

BEGIN

SET NOCOUNT ON

Declare @ErrorDesc varchar(255),

@lProductKey nvarchar(50),

@lAverageCost DECIMAL(20,5),

@lSubsidiaryKey nvarchar(50)

  Select @lProductKey = ProductKey , @lAverageCost = UpdatedCost, @lSubsidiaryKey = SubsidiaryKey

  From InvProductCostUpdateLog

  Where ProductCostUpdateLogKey = @pProductCostUpdateLogKey

  Update InvInventoryItem

  Set AverageCost = @lAverageCost

  Where ProductKey = @lProductKey

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

IF(@@ERROR <>0)

    BEGIN

  SET @ErrorDesc = 'Error while updating the item cost.'

        GOTO ERRORHANDLER

END

RETURN

ERRORHANDLER:

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

RETURN

END