CAUTION: This script has been prepared as a goodwill gesture only and Sage accepts no liability or responsibility for its use. For further support please refer to your Business Partner. DECLARE @StockCode varchar(30)
SET @StockCode='ABBuiltIn/15/0/2'
CREATE TABLE
(
SOPOrderReturnLineID bigint,
QuantityReserved decimal(15,5)
)
CREATE TABLE
(
TraceableItemID bigint,
QuantityReserved decimal(15,5)
)
CREATE TABLE
(
TraceableBinItemID bigint,
QuantityReserved decimal (15,5)
)
SELECT
a.ItemID AS ItemID,
a.code As Code,
a.QuantityReserved AS QuantityReserved
FROM
StockItem a
WHERE
a.Code=@StockCode AND a.QuantityReserved<>0
SELECT
b.WarehouseItemID AS WarehouseItemID,
b.QuantityReserved AS QuantityReserved
FROM
StockItem a
INNER JOIN WarehouseItem b ON a.ItemID=b.ItemID
WHERE
a.Code=@StockCode AND b.QuantityReserved<>0
SELECT
c.BinItemID AS BinItemID,
c.QuantityReserved AS QuantityReserved
FROM
StockItem a
INNER JOIN BinItem c ON a.ItemID=c.ItemID
WHERE
a.Code=@StockCode AND c.QuantityReserved<>0
SELECT
d.AllocationID AS AllocationBalanceID,
d.QuantityReserved AS QuantityReserved
FROM
StockItem a
INNER JOIN AllocationBalance d ON a.ItemID=d.ItemID
WHERE a.Code=@StockCode AND d.QuantityReserved<>0
SELECT
e.MovementID AS MovementBalanceID,
e.QuantityReserved AS QuantityReserved
FROM
StockItem a
INNER JOIN MovementBalance e ON a.ItemID=e.ItemID
WHERE
a.Code=@StockCode AND e.QuantityReserved<>0
INSERT INTO
SELECT
f.SOPOrderReturnLineID AS SOPOrderReturnLineID,
f.QuantityReserved AS QuantityReserved
FROM
StockItem a
INNER JOIN SOPStandardItemLink z ON a.ItemID=z.ItemID
INNER JOIN SOPOrderReturnLine f ON f.SOPOrderReturnLineID=z.SOPOrderReturnLineID
WHERE
a.Code=@StockCode
SELECT * FROM
WHERE QuantityReserved<>0
SELECT
g.SOPDespatchReceiptLineID AS SOPDespatchReceiptLineID,
g.QuantityReserved AS QuantityReserved
FROM
INNER JOIN SOPDespatchReceiptLine g ON x.SOPOrderReturnLineID=g.SOPOrderReturnLineID
WHERE g.QuantityReserved<>0
INSERT INTO
SELECT
h.TraceableItemID AS TraceableItemID,
h.QuantityReserved AS QuantityReserved
FROM
StockItem a
INNER JOIN TraceableItem h ON a.ItemID=h.StockItemID
WHERE a.Code=@StockCode
SELECT * FROM
WHERE QuantityReserved<>0
INSERT INTO
SELECT
i.TraceableBinItemID AS TraceableBinItemID,
i.QuantityReserved AS QuantityReserved
FROM
INNER JOIN TraceableBinItem i ON i.TraceableItemID=y.TraceableItemID
SELECT * FROM
WHERE QuantityReserved<>0
SELECT
j.TraceableAllocationBalID AS TraceableAllocationBalID,
j.QuantityReserved AS QuantityReserved
FROM
INNER JOIN TraceableAllocationBal j ON w.TraceableBinItemID=j.TraceableBinItemID
WHERE j.QuantityReserved<>0
DROP TABLE
DROP TABLE
DROP TABLE
|