20
Why is HQ quantity not machine with store quantity, i.e in HQ an item in inventory the quantity will show like 20pcs but went you check store quantity tab you will see the quantity of each store but if you total evey thing it does not match with that of HQ.
*This post is locked for comments
It is bcoz Item Quantity looks after the sum of itemdynamic.Quantity while the Store Quantity in ItemDynamic.SnapshotQuantity.
To get rid if this, execute this trigger in your hq db. In this way, everytime the sync updates the current onhan, it will be automatically update the total item quantity.
/****** Object: Trigger [dbo].[tr_ItemStoreQty] Script Date: 02/04/2016 09:42:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[tr_ItemStoreQty]
ON [dbo].[ItemDynamic] FOR UPDATE, INSERT, DELETE AS
/* Updates Quantity and QuantityCommitted in Item table when changes are made to the ItemDynamic table */
DECLARE @Rows INT
SELECT @Rows = @@ROWCOUNT
IF @Rows = 0
/* No rows inserted or deleted, exit trigger */
RETURN
/* Update the Quantity and QuantityCommitted fields in ItemDynamic table */
SELECT ItemDynamic.ItemID,
SUM(ItemDynamic.Quantity) AS Quantity,
SUM(ItemDynamic.QuantityCommitted) AS QuantityCommitted
INTO #tr_Temp
FROM ItemDynamic
WHERE ItemDynamic.ItemID IN
(
SELECT ItemID
FROM INSERTED
UNION
SELECT ItemID
FROM DELETED
)
GROUP BY ItemDynamic.ItemID
UPDATE Item
SET Quantity = #tr_Temp.Quantity,
QuantityCommitted = #Tr_temp.QuantityCommitted
FROM Item, #tr_Temp
WHERE Item.ID = #tr_Temp.ItemID
GO
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,235
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (