oh no. Do not change item quantities in HQ Manager. Since you only create fake store for this purpose and you dont have any store server connected on it, you can query directly in itemdynamic table since this table holds the current store item quantity as snapshotquantity. I guess, if you look item quantity for a specific store (your fake store) , you will not find anything because you dont have store server connected to it and no items uploaded in itemdynamic table. You need to add the items first in itemdynamic table specifying the new quantity.
The above query works fine but i will add some extra on it.
You can use this stored procedure:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Description: This will create item and update quantity in itemdynamic table.
-- =============================================
ALTER PROCEDURE RebuildItemDynamic
@storecode AS NVARCHAR(30)
AS
BEGIN
SET NOCOUNT ON
DECLARE @rowcount AS INT
DECLARE @storeid AS INT
SELECT @storeid = ID FROM store WHERE StoreCode = @storecode
IF (@storeid > 0)
BEGIN
PRINT 'Store ''' + @storecode + ''' exists!'
SELECT * FROM Item WHERE ItemLookupCode LIKE 'AAA%'
SET @rowcount = (SELECT @@ROWCOUNT)
IF (@rowcount > 0)
BEGIN
PRINT 'There are ' + CAST(@rowcount AS VARCHAR) + ' item(s) found.'
IF OBJECT_ID(N'tempdb..#TempToUpload') IS NOT NULL BEGIN DROP TABLE #TempToUpload END
SELECT Item.ID As ItemID,
@storeid as StoreID,
Item.LastReceived, Item.LastSold,
Item.Quantity AS SnapShotQuantity,
Item.QuantityCommitted As SnapShotQuantityCommitted,
GETDATE() AS SnapShotTime,
Item.Price AS SnapShotPrice,
Item.PriceA AS SnapShotPriceA,
Item.PriceB AS SnapShotPriceB,
Item.PriceC AS SnapShotPriceC,
Item.SalePrice AS SnapShotSalePrice,
Item.SaleStartDate AS SnapShotSaleStartDate,
Item.SaleEndDate AS SnapShotSaleEndDate,
Item.Cost AS SnapShotCost,
Item.LastCost AS SnapShotLastCost,
Item.ReplacementCost AS SnapShotReplacementCost,
Item.PriceLowerBound AS SnapShotPriceLowerBound,
Item.PriceUpperBound AS SnapShotPriceUpperBound,
Item.ReorderPoint AS SnapShotReorderPoint,
Item.RestockLevel AS SnapShotRestockLevel,
Item.TaxID AS SnapShotTaxID
INTO #TempToUpload
FROM Item WHERE ItemLookupCode LIKE 'AAA%'
UPDATE ItemDynamic SET
dbo.ItemDynamic.Quantity = #TempToUpload.SnapShotQuantityCommitted,
dbo.ItemDynamic.SnapShotQuantity = 100,
dbo.ItemDynamic.SnapShotQuantityCommitted = #TempToUpload.SnapShotQuantityCommitted,
ItemDynamic.SnapShotTime = GETDATE(),
ItemDynamic.SnapShotPrice = #TempToUpload.SnapShotPrice,
ItemDynamic.SnapShotPriceA = #TempToUpload.SnapShotPriceA,
ItemDynamic.SnapShotPriceB = #TempToUpload.SnapShotPriceB,
ItemDynamic.SnapShotPriceC = #TempToUpload.SnapShotPriceC,
ItemDynamic.SnapShotSalePrice = #TempToUpload.SnapShotSalePrice,
ItemDynamic.SnapShotSaleStartDate = #TempToUpload.SnapShotSaleStartDate,
ItemDynamic.SnapShotSaleEndDate = #TempToUpload.SnapShotSaleEndDate,
ItemDynamic.SnapShotCost = #TempToUpload.SnapShotCost,
ItemDynamic.SnapShotLastCost = #TempToUpload.SnapShotLastCost,
ItemDynamic.SnapShotReplacementCost = #TempToUpload.SnapShotReplacementCost,
ItemDynamic.SnapShotPriceLowerBound = #TempToUpload.SnapShotPriceLowerBound,
ItemDynamic.SnapShotPriceUpperBound = #TempToUpload.SnapShotPriceUpperBound,
ItemDynamic.SnapShotReorderPoint = #TempToUpload.SnapShotReorderPoint,
ItemDynamic.SnapShotRestockLevel = #TempToUpload.SnapShotRestockLevel,
ItemDynamic.SnapShotTaxID = #TempToUpload.SnapShotTaxID
FROM ItemDynamic INNER JOIN #TempToUpload ON #TempToUpload.ItemID = dbo.ItemDynamic.ItemID
AND #TempToUpload.StoreID = dbo.ItemDynamic.StoreID
seT @rowcount = (SELECT @@ROWCOUNT)
PRINT 'Updated ' + CAST(@rowcount as VARCHAR) + ' record(s) in itemdynamic table.'
DELETE #TempToUpload
FROM #TempToUpload,dbo.ItemDynamic
WHERE #TempToUpload.ItemID = dbo.ItemDynamic.ItemID
AND #TempToUpload.StoreID = dbo.ItemDynamic.StoreID
INSERT INTO dbo.ItemDynamic
( ItemID ,
StoreID ,
TaxID ,
Quantity ,
QuantityCommitted ,
ReorderPoint ,
RestockLevel ,
LastReceived ,
LastSold ,
SnapShotQuantity ,
SnapShotQuantityCommitted ,
DeltaQuantity ,
DeltaQuantityCommitted ,
SnapShotTime ,
DBTimeStamp ,
SnapShotPrice ,
SnapShotPriceA ,
SnapShotPriceB ,
SnapShotPriceC ,
SnapShotSalePrice ,
SnapShotSaleStartDate ,
SnapShotSaleEndDate ,
SnapShotCost ,
SnapShotLastCost ,
SnapShotReplacementCost ,
SnapShotPriceLowerBound ,
SnapShotPriceUpperBound ,
SnapShotReorderPoint ,
SnapShotRestockLevel ,
SnapShotTaxID
)
SELECT ItemID ,
StoreID ,
TaxID ,
Quantity ,
QuantityCommitted ,
ReorderPoint ,
RestockLevel ,
LastReceived ,
LastSold ,
SnapShotQuantity ,
SnapShotQuantityCommitted ,
DeltaQuantity ,
DeltaQuantityCommitted ,
SnapShotTime ,
DBTimeStamp ,
SnapShotPrice ,
SnapShotPriceA ,
SnapShotPriceB ,
SnapShotPriceC ,
SnapShotSalePrice ,
SnapShotSaleStartDate ,
SnapShotSaleEndDate ,
SnapShotCost ,
SnapShotLastCost ,
SnapShotReplacementCost ,
SnapShotPriceLowerBound ,
SnapShotPriceUpperBound ,
SnapShotReorderPoint ,
SnapShotRestockLevel ,
SnapShotTaxID
from #TempToUpload
PRINT 'Inserted ' + CAST(@rowcount as VARCHAR) + ' record(s) in itemdynamic table.'
END
ELSE
BEGIN
PRINT 'There are no items found.'
END
END
ELSE
BEGIN
PRINT 'Store ''' + @storecode + ''' does not exists!'
END
END
GO
-- Copy the procedure above and run on your hq server. Dont forget to specify storecode.
Please verify if this answers your question.
Share
Report
908
All responses (
Answers (
