iVend Database Database : Integration_InventoryItem Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@WarehouseKey

In

The new warehouse that has been added in InvWarehouse

VarWChar

50

@Sender

In

For future use

VarChar

50

@Receiver

In

For future use

VarChar

50

@IsAllProductLock

In

Lock the Inventory Item for the warehouse

Boolean

1

@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

CREATE PROCEDURE [dbo].[Integration_InventoryItem]

(

@WarehouseKey NVARCHAR(50),

@Sender   VARCHAR(50),

@Receiver VARCHAR(50),

@IsAllProductLock BIT = 0

)

AS

BEGIN

Declare @SiteID       VARCHAR(3),

  @NextInventoryItemKey   NVARCHAR(50),

  @NextInventoryItemKeyNew NVARCHAR(50),

  @NextBatchKey     NVARCHAR(50),

  @NextReplicationTransactionKey NVARCHAR(50),

  @SubsidiaryKey     NVARCHAR(50),

  @RecordsAffected   INT,

  @Guid     NVARCHAR(50)

SELECT @SiteID = SiteId FROM CfgSiteInformation

SELECT @SubsidiaryKey = ISNULL(SubsidiaryKey, '') From InvWarehouse Where WarehouseKey = @WarehouseKey

SELECT DISTINCT ProductKey, @WarehouseKey AS WarehouseKey, IsInclusiveTaxed, Price, InventoryItemKey AS [GUID]

INTO #temp

FROM InvInventoryItem

WHERE WarehouseKey = @WarehouseKey

IF (SELECT COUNT(*) FROM #temp) = 0

BEGIN

IF (@SubsidiaryKey = '0' OR @SubsidiaryKey = '')

  INSERT INTO #temp

  SELECT DISTINCT ProductKey, @WarehouseKey As WarehouseKey, 0 As IsInclusiveTaxed, CONVERT(DECIMAL(20,5), BasePrice) AS Price, NEWID() AS [Guid]

  FROM InvProduct

  WHERE IsDeleted = 0

  ORDER BY ProductKey

ELSE

  INSERT INTO #temp

  SELECT DISTINCT ProductKey, @WarehouseKey As WarehouseKey , C.IsTaxInclusive As IsInclusiveTaxed, CONVERT(DECIMAL(20,5), 0) AS Price, NEWID() AS [Guid]

  FROM InvProduct A INNER JOIN SubSubsidiaryItem B ON A.ProductKey = B.SourceKey And b.SubsidiaryKey = @SubsidiaryKey And b.SourceType = 46

  INNER JOIN RtlSubsidiary C ON B.SubsidiaryKey = C.SubsidiaryKey

  WHERE A.IsDeleted = 0

  ORDER BY A.ProductKey

END

--select * from #temp

--WHILE (EXISTS(SELECT 1 FROM #TEMP))

--BEGIN

INSERT INTO InvInventoryItem ( InventoryItemKey, ProductKey, WarehouseKey, PurchaseTaxKey, SalesTaxKey, InStockQuantity,

         InReturnQuantity, OnRentQuantity, LostQuantity, OnLayawayQuantity, OnOrderQuantity,

         OnFulFillmentQuantity, AvailableQuantity, KitsAvailableToBuild, AssembliesAvailableToBuild,

         Price, Created, Modified, CreatedBy, ModifiedBy, LeadTime, IsLocked, IsInclusiveTaxed, FulfillmentWarehouseKey, PreferedVendorKey, PriceListKey)

SELECT [Guid],

   A.ProductKey, CAST(@WarehouseKey As NVARCHAR(50)), '0', '0', 0,

  0, 0, 0, 0, 0,

  0, 0, 0, 0,

  0, dbo.GetCompanyDateTime(), dbo.GetCompanyDateTime(), -1, -1, 0, CASE WHEN @IsAllProductLock = 1 THEN 1 ELSE 0 END, A.IsInclusiveTaxed,

  '0','0','0'-----FulfillmentWarehouseKey, PreferedVendorKey, PriceListKey

FROM #temp A left outer join InvInventoryItem B WITH (NOLOCK) on A.[GUID] = InventoryItemKey

Where b.InventoryItemKey IS NULL

Order by A.ProductKey

INSERT INTO RepReplicationTransaction( ReplicationTransactionKey, SourceType, SourceKey, BatchKey, OperationType,

           Flag, StoreId, FromSBO, Sender, Receiver)

SELECT NEWID() AS ReplicationTransactionKey, 38,

  [Guid] AS SourceKey,

  NEWID() AS BatchKey,

  0, 0, @SiteID, 1, @Sender, @Receiver

FROM #temp Order by ProductKey

--SET @NextInventoryItemKey =  @NextInventoryItemKey + @@ROWCOUNT

--DELETE TOP (500) FROM #temp WHERE ProductKey in (SELECT TOP 500 ProductKey FROM #temp ORDER BY ProductKey)

--END

End