iVend Database Database : InventoryCount_Save Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@pInventoryCountKey

In

VarWChar

50

@pUpdatePrice

In

Boolean

1

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

InvInventoryCount

Table

Stores the master data for the Inventory counting

1

InvInventoryCountDetail

Table

Stores the details counting data for the inventory

1

InvInventoryItem

Table

Stores inventory details for each Product for each Warehouse

1

InvInventoryLocation

Table

Stores the location information for the Inventory available at different locations

1

InvProduct

Table

Stores the product related details.

1

InvWarehouse

Table

Defines a list of all warehouses defined in the system.

1

PrcPriceMatrix

Table

Defines the list of products that are associated with each price list. It is also integrated through ERP.

1

RtlStore

Table

Contains the details of Stores defined in the system.

1

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

-- Author:  <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

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

CREATE PROCEDURE [dbo].[InventoryCount_Save]

@pInventoryCountKey As nvarchar(50),

@pUpdatePrice AS BIT

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

DECLARE @lWarehouseKey nvarchar(50)

DECLARE @IsLocationEnabled BIT

SELECT @lWarehouseKey = ISNULL(WarehouseKey,'0') FROM InvInventoryCount WHERE InventoryCountKey = @pInventoryCountKey

SELECT @IsLocationEnabled = ISNULL(IsLocationEnabled,0) FROM InvWarehouse WHERE WarehouseKey = @lWarehouseKey

IF(@IsLocationEnabled = 1)

BEGIN

UPDATE T0

SET T0.StartingCount = T1.InStockQuantity,

 T0.InStoreQuantity = T1.InStockQuantity,

 T0.DifferenceQuantity = T0.CountedQuantity - T1.InStockQuantity

FROM InvInventoryCountDetail T0, InvInventoryLocation T1

WHERE T0.ProductKey = T1.ProductKey

AND T0.WarehouseKey = T1.WarehouseKey

AND T0.LocationKey = T1.LocationKey

AND T0.InventoryCountKey = @pInventoryCountKey

END

ELSE

BEGIN

UPDATE T0

SET T0.StartingCount = T1.InStockQuantity,

 T0.InStoreQuantity = T1.InStockQuantity,

 T0.DifferenceQuantity = T0.CountedQuantity - T1.InStockQuantity

FROM InvInventoryCountDetail T0, InvInventoryItem T1

WHERE T0.InventoryItemKey = T1.InventoryItemKey

AND T0.InventoryCountKey = @pInventoryCountKey

END

IF @pUpdatePrice = 1

BEGIN

  --update the items with the price list -- Plugged/UnPlugged Versions

  UPDATE T1

  SET T1.Price = T2.Price

  FROM InvInventoryCount T0, InvInventoryCountDetail T1, PrcPriceMatrix T2

  WHERE T0.InventoryCountKey = T1.InventoryCountKey

  AND T0.InventoryCountKey = @pInventoryCountKey

  AND T1.ProductKey = T2.ProductKey

  AND T0.PriceListKey = T2.PriceListKey

  AND T2.IsDeleted = 0

  --update the items with the average cost --UnPlugged Version

  UPDATE T1

  SET T1.Price = T3.ProductCost

  FROM InvInventoryCountDetail T1,  InvInventoryItem T2, vw_ProductCost T3

  WHERE T1.InventoryItemKey = T2.InventoryItemKey

  AND T2.ProductKey = T3.ProductKey

  and T2.WarehouseKey = T3.WarehouseKey

  AND T1.InventoryCountKey = @pInventoryCountKey

  AND T1.Price = 0

  AND T3.ProductCost > 0

  --update the items with the cost price list attached to warehouse in case the warehouse is a retail store--Plugged Version

  UPDATE T1

  SET T1.Price = T2.Price

  FROM InvInventoryCount T0, InvInventoryCountDetail T1,  PrcPriceMatrix T2, RtlStore T3

  WHERE T0.InventoryCountKey = T1.InventoryCountKey

  AND T0.InventoryCountKey = @pInventoryCountKey

  AND T1.ProductKey = T2.ProductKey

  AND T2.PriceListKey = T3.CostPriceListKey

  AND T2.IsDeleted = 0

  AND T1.Price = 0

  and T2.Price > 0

  --update the items with the base price -- Plugged/UnPlugged Versions

  UPDATE T1

  SET T1.Price = T2.BasePrice

  FROM InvInventoryCount T0, InvInventoryCountDetail T1,  InvProduct T2

  WHERE T0.InventoryCountKey = T1.InventoryCountKey

  AND T0.InventoryCountKey = @pInventoryCountKey

  AND T1.ProductKey = T2.ProductKey

  AND T1.Price = 0

  and T2.BasePrice > 0

END

END