iVend Database Database : Integration_InventoryItemPrice Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@WhsCode

In

the warehouse code with which the price list is attached.

VarChar

20

@PriceListKey

In

the corresponding pricelistkey in iVend, that is attached with above warehouse

VarWChar

50

@Sender

In

For future use

VarChar

50

@Receiver

In

For future use

VarChar

50

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

CfgSiteInformation

Table

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

1

InvInventoryItem

Table

Stores inventory details for each Product for each Warehouse

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

RepReplicationTransaction

Table

Stores the Transaction details for them the data needs to be replicated

1

--USE [CXSRetail_624]

--GO

--/****** Object:  StoredProcedure [dbo].[Integration_InventoryItemPrice]    Script Date: 3/3/2016 2:20:11 PM ******/

--SET ANSI_NULLS ON

--GO

--SET QUOTED_IDENTIFIER ON

--GO

--1. Updates the corresponding price list in InvWarehouse in iVend.

--2. Update the corresponding price in InvInventoryItem for the new price list attached..

--##SUMMARY_END

CREATE PROC [dbo].[Integration_InventoryItemPrice](

          @WhsCode VARCHAR(20),

          @PriceListKey NVARCHAR(50),

          @Sender   VARCHAR(50),

          @Receiver VARCHAR(50)

         )

AS

BEGIN

SET NOCOUNT ON

DECLARE @WhsKey   nvarchar(50),

  @lSiteID   INT,

  @ErrMsg   VARCHAR(255)

SELECT @ErrMsg = ''

SELECT @lSiteID = SiteId FROM CfgSiteInformation

SELECT @WhsKey = WarehouseKey FROM InvWarehouse WHERE AccountingID = @WhsCode

IF ISNULL(@WhsKey, '-1') = '-1'

BEGIN

SELECT @ErrMsg = 'Warehouse <' + @WhsCode + '> not found in iVend'

SELECT @ErrMsg

RETURN

END

--If we detach a price list from warehouse the  @PriceListKey variable passed to this procedure contains -1

--so convert this to 0 before updating the same in InvWarehouse

IF @PriceListKey = '-1'

BEGIN

SET @PriceListKey = '0'

END

--Update the price list for the corresponding warehouse

UPDATE InvWarehouse

SET  PriceListKey = @PriceListKey

WHERE WarehouseKey = @WhsKey

--Insert record in RepReplicationTransaction table for replication of above update

INSERT INTO RepReplicationTransaction

 (ReplicationTransactionKey, SourceType, SourceKey

 , BatchKey, OperationType, Flag

 , StoreId, FromSBO, Sender, Receiver)

VALUES(

  NEWID(), 61, @WhsKey

  , NEWID(), 1, 0

  , CAST(@lSiteID AS VARCHAR), 1,@Sender,@Receiver

 )

DECLARE @lWarehousePriceList nvarchar(50)

SELECT @lWarehousePriceList = PriceListKey FROM InvWarehouse WHERE WarehouseKey = @WhsKey

Select  T0.ProductKey Into #TempProductList

FROM InvInventoryItem T0 INNER JOIN PrcPriceMatrix T1 ON T0.ProductKey = T1.ProductKey

  AND T1.PriceListKey = @lWarehousePriceList

WHERE T0.WarehouseKey = @WhsKey

AND  T0.Price <> ISNULL(T1.Price, 0)

And  T1.IsDeleted = 0

WHILE (EXISTS(SELECT 1 FROM #TempProductList))

BEGIN

UPDATE TOP (500) T0

SET  T0.Price = ISNULL(T1.Price, 0)

FROM InvInventoryItem T0 INNER JOIN PrcPriceMatrix T1 ON T0.ProductKey = T1.ProductKey AND T1.PriceListKey = @lWarehousePriceList

Inner Join #TempProductList T2 On T0.ProductKey = T2.ProductKey

WHERE T0.WarehouseKey = @WhsKey

AND  T0.Price <> ISNULL(T1.Price, 0)

And  T1.IsDeleted = 0

DELETE TOP (500) FROM #TempProductList

END

SELECT @ErrMsg

RETURN

SET NOCOUNT OFF

END