Find Quantity Reserved value for all relevant SQL database tables

If you are trying to find where the QuantityReserved is coming from for a particular stock item, you can run a script in the SQL database to pull through any affected tables to then correct if necessary.

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)

/* THIS SCRIPT WILL RETURN ANY ENTRIES IN ALL RELEVANT TABLES WHERE THERE IS A QUANTITYRESERVED
   VALUE FOR THE SPECIFIED STOCK CODE.

   IF THE SCRIPT SHOWS NO RESULTS FOR A TABLE - THERE ARE NO ENTRIES THERE WITH A
   QUANTITYRESERVED.
   THIS SCRIPT IS NOT DESIGNED TO FIX QUANTITYRESERVED DISCREPANCIES. THESE MUST BE CORRECTED
   WHERE POSSIBLE BY BALANCE LEDGERS ROUTINE OR CORRECTED MANUALLY ON A NON-LIVE DATABASE.  
   IF YOU GET ERROR "There is already an object named '#TableName' in the database." WHEN
   RUNNING THE SCRIPT, RUN:
   DROP TABLE #SOPLines
   DROP TABLE #TraceItems
   DROP TABLE #TraceBinItems
   */

   /************ ENTER YOUR STOCK CODE HERE *************/
                ----------------------------

   /********/ SET @StockCode='ABBuiltIn/15/0/2' /********/

   /*****************************************************/

/*temporary table #SOPLines used later to hold all soplines relating to this stock item*/

CREATE TABLE #SOPLines
(
	SOPOrderReturnLineID bigint,
	QuantityReserved decimal(15,5)
)

/*temporary table #TraceItems used later to hold all traceable items relating to this stock item*/

CREATE TABLE #TraceItems
(
	TraceableItemID bigint,
	QuantityReserved decimal(15,5)
)

/*temporary table #TraceBinItems used later to hold all traceable bin items relating to this stock item*/

CREATE TABLE #TraceBinItems
(
	TraceableBinItemID bigint,
	QuantityReserved decimal (15,5)
)

/*will show if the StockItem entry for this item has a QuantityReserved*/

SELECT
a.ItemID AS ItemID,
a.code As Code,
a.QuantityReserved AS QuantityReserved

FROM
StockItem a

WHERE
a.Code=@StockCode AND a.QuantityReserved<>0

/*print any entries in WarehouseItem for this item where there is a QuantityReserved*/

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

/*print any entries in BinItem for this item where there is a QuantityReserved*/

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

/*print any entries in AllocationBalance for this item where there is a QuantityReserved*/

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

/*print any entries in MovementBalance for this item where there is a QuantityReserved*/

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

/*populates temp table #SOPLines with all SOPOrderReturnLine entries relating to this item*/

INSERT INTO #SOPLines

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

/*print all relevant #SOPLines (where QuantityReserved is not 0)*/

SELECT * FROM #SOPLines
WHERE QuantityReserved<>0

/*print all relevant sop despatches (based on #SOPLines)*/

SELECT
g.SOPDespatchReceiptLineID AS SOPDespatchReceiptLineID,
g.QuantityReserved AS QuantityReserved

FROM
#SOPLines x
INNER JOIN SOPDespatchReceiptLine g ON x.SOPOrderReturnLineID=g.SOPOrderReturnLineID

WHERE g.QuantityReserved<>0

/*populate #TraceItems with all TraceableItem table entries relating to this item*/

INSERT INTO #TraceItems

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

/*print all #TraceItems entries with a QuantityReserved value*/

SELECT * FROM #TraceItems
WHERE QuantityReserved<>0

/*populates #TraceBinItems with TraceableBinItem entries relating to #TraceItems*/

INSERT INTO #TraceBinItems
SELECT
i.TraceableBinItemID AS TraceableBinItemID,
i.QuantityReserved AS QuantityReserved

FROM
#TraceItems y
INNER JOIN TraceableBinItem i ON i.TraceableItemID=y.TraceableItemID

/*prints all #TraceBinItems with a QuantityReserved value*/

SELECT * FROM #TraceBinItems
WHERE QuantityReserved<>0

/*prints all TraceableAllocationBal entries relating to #TraceBinItems with a QuantityReserved*/

SELECT
j.TraceableAllocationBalID AS TraceableAllocationBalID,
j.QuantityReserved AS QuantityReserved

FROM
#TraceBinItems w
INNER JOIN TraceableAllocationBal j ON w.TraceableBinItemID=j.TraceableBinItemID

WHERE j.QuantityReserved<>0

DROP TABLE #SOPLines
DROP TABLE #TraceItems
DROP TABLE #TraceBinItems