Very useful SQL Scripts related queries, topics and discussions
User avatar
By admin
#335 SQL Scripts for Stock Balances

Verification of the balances in SC01, SC03, SC33 and SC07 can be achieved by running the series of SQL scripts shown below. Any discrepancy will show as a difference between Balance and Quantity.

1. To check the balances between SC07 and SC33:

Code: Select allSELECT SC33001 AS StoCode, SC33002 AS Whouse, SC33003 AS BatchID, SC33005 AS Balance, SUM(SC07004) AS Quantity
FROM SC07cc00 JOIN SC33cc00 ON SC33001 = SC07003 AND SC33002 = SC07009 AND SC33003 = SC07021
GROUP BY SC33001, SC33002, SC33003, SC33005, SC07003, SC07009, SC07021
HAVING SC33005 <> SUM(SC07004)

2. To check the balances between SC33 and SC03:

Code: Select allSELECT SC03001 AS StoCode, SC03002 AS Whouse, SC03003 AS Balance, SUM(SC33005) AS Quantity
FROM SC33cc00 JOIN SC03cc00 ON SC03001 = SC33001 AND SC03002 = SC33002
GROUP BY SC03001, SC03002, SC03003, SC33001, SC33002
HAVING SC03003 <> SUM(SC33005)

This script could be extended to account for other balances, i.e.
Reserved Qty: SC03004 versus SUM(SC33006)
Ordered Qty: SC03006 versus SUM(SC33007)

3. To check the balances between SC03 and SC01:

Code: Select allSELECT SC01001 AS StoCode, SC01042 AS Balance, SUM(SC03003) AS Quantity
FROM SC03cc00 JOIN SC01cc00 ON SC01001 = SC03001
GROUP BY SC01001, SC01042, SC03001
HAVING SC01042 <> SUM(SC03003)

This script could be extended to account for other balances, i.e.
Reserved Qty: SC01043 versus SUM(SC03004)
Back-Ord Qty: SC01044 versus SUM(SC03005)
Ordered Qty: SC01045 versus SUM(SC03006)
ST Difference: SC01046 versus SUM(SC03007)

These will show all stock items with a problem, rather than individual ones.