Need to identify genuine stock quantity discrepancies between ERP & WMS?
-
Hi Everyone,
I have been asked to report on missing Stock in our Warehouse. My work uses SAP Business One for ERP, and Accellos for Warehouse Management. Both SAP / Accellos maintain stock levels, and whilst they do talk to each other (in real time), nothing is perfect and stock counts (within each system) sometimes develop discrepancies.
Here is the code that I developed to show stock discrepancies -
SELECT
Tx.[Item Code]
, ISNULL(Ty.Qty, 0) AS 'A1 Qty'
, Tx.Qty AS 'B1 Qty'
, Ty.Qty - Tx.Qty AS 'A1 to B1 Diff'
, Tx.AvgPrice AS 'Price /ea'
, (Ty.Qty - Tx.Qty) * Tx.AvgPrice AS 'Tot Price Diff'
, Tx.Whs AS Warehouse
FROM
(
/*** Stock in SAP Business One ***/
SELECT
T0.ItemCode AS 'Item Code'
, T0.WhsCode AS 'Whs'
, SUM(T0.OnHand) AS 'Qty'
, T1.AvgPrice AS AvgPrice
FROM OITW T0
INNER JOIN OITM T1 ON T1.ItemCode = T0.ItemCode
GROUP BY T0.ItemCode, T0.WhsCode, T1.AvgPrice
) AS Tx
FULL JOIN
(
/*** Stock in Accellos ***/
SELECT
T0.PRODUCT AS 'Item Code'
, T0.WAREHOUSE collate DATABASE_DEFAULT AS 'Whs'
, SUM(T0.Quantity) AS 'Qty'
, '' AS AvgPrice
FROM A1Warehouse.dbo.BINLOCAT T0
GROUP BY T0.PRODUCT, T0.WAREHOUSE--, T1.AvgPrice
) AS Ty
ON Ty.[Item Code] = Tx.[Item Code] collate SQL_Latin1_General_CP850_CI_AS AND Ty.Whs = Tx.Whs collate SQL_Latin1_General_CP850_CI_AS
WHERE Tx.Qty <> Ty.Qty
ORDER BY [Item Code]
The above code works as desired, with a sample output shown below -
Notice above that I have highlighted two product codes; ACR10904 & ACR401142030. Whilst both of these products are not in sync between SAP and Accellos this is not a big concern because each represents a genuine transfer of goods between two geographically separated warehouses.
I can tell that these products are being transferred because; 1). the 'A1 to B1 Diff' values cancel each other out, and 2). One warehouse is TRANSIT whilst the other is a physical warehouse (e.g.: 03).
Knowing all of the above I can further refine my initial query to give me only those products which are in difference and belonging to the TRANSIT warehouse (in Accellos).
SELECT
Tx.[Item Code]
, ISNULL(Ty.Qty, 0) AS 'A1 Qty'
, Tx.Qty AS 'B1 Qty'
, Ty.Qty - Tx.Qty AS 'A1 to B1 Diff'
, Tx.AvgPrice AS 'Price /ea'
, (Ty.Qty - Tx.Qty) * Tx.AvgPrice AS 'Tot Price Diff'
, Tx.Whs AS Warehouse
FROM
(
/*** Stock in B1 ***/
SELECT
T0.ItemCode AS 'Item Code'
, T0.WhsCode AS 'Whs'
, SUM(T0.OnHand) AS 'Qty'
, T1.AvgPrice AS AvgPrice
FROM OITW T0
INNER JOIN OITM T1 ON T1.ItemCode = T0.ItemCode
GROUP BY T0.ItemCode, T0.WhsCode, T1.AvgPrice
) AS Tx
FULL JOIN
(
/*** Stock in Accellos ***/
SELECT
T0.PRODUCT AS 'Item Code'
, T0.WAREHOUSE collate DATABASE_DEFAULT AS 'Whs'
, SUM(T0.Quantity) AS 'Qty'
, '' AS AvgPrice
FROM A1Warehouse.dbo.BINLOCAT T0
WHERE T0.WAREHOUSE = 'TRANSIT'
GROUP BY T0.PRODUCT, T0.WAREHOUSE--, T1.AvgPrice
) AS Ty
ON Ty.[Item Code] = Tx.[Item Code] collate SQL_Latin1_General_CP850_CI_AS AND Ty.Whs = Tx.Whs collate SQL_Latin1_General_CP850_CI_AS
WHERE Tx.Qty <> Ty.Qty
ORDER BY [Item Code]
Notice above in the second derived table, called Ty that I have added the WHERE clause and am now only returning results where the Accellos warehouse is 'TRANSIT', as shown below -
I can then alter the WHERE clause to return only SAP warehouses, e.g.: none that are 'TRANSIT', by modifying the line below -
WHERE T0.WAREHOUSE <> 'TRANSIT'
It has occurred to me that I may be able to perform some kind of a subtraction operation by creating two CTEs, the first containing only products that are in the (Accellos) 'TRANSIT' warehouse, and the other only products that are in a physical (SAP) warehouse (e.g.: 03, 04, etc).
However aside from creating the CTEs I am stuck for ideas...
;
WITH CTETransitWhs AS
(
SELECT
Tx.[Item Code]
, ISNULL(Ty.Qty, 0) AS 'A1 Qty'
, Tx.Qty AS 'B1 Qty'
, Ty.Qty - Tx.Qty AS 'A1 to B1 Diff'
, Tx.AvgPrice AS 'Price /ea'
, (Ty.Qty - Tx.Qty) * Tx.AvgPrice AS 'Tot Price Diff'
, Tx.Whs AS Warehouse
FROM
(
/*** Stock in B1 ***/
SELECT
T0.ItemCode AS 'Item Code'
, T0.WhsCode AS 'Whs'
, SUM(T0.OnHand) AS 'Qty'
, T1.AvgPrice AS AvgPrice
FROM OITW T0
INNER JOIN OITM T1 ON T1.ItemCode = T0.ItemCode
GROUP BY T0.ItemCode, T0.WhsCode, T1.AvgPrice
) AS Tx
FULL JOIN
(
/*** Stock in Accellos ***/
SELECT
T0.PRODUCT AS 'Item Code'
, T0.WAREHOUSE collate DATABASE_DEFAULT AS 'Whs'
, SUM(T0.Quantity) AS 'Qty'
, '' AS AvgPrice
FROM A1Warehouse.dbo.BINLOCAT T0
WHERE T0.WAREHOUSE = 'TRANSIT'
GROUP BY T0.PRODUCT, T0.WAREHOUSE--, T1.AvgPrice
) AS Ty
ON Ty.[Item Code] = Tx.[Item Code] collate SQL_Latin1_General_CP850_CI_AS AND Ty.Whs = Tx.Whs collate SQL_Latin1_General_CP850_CI_AS
WHERE Tx.Qty <> Ty.Qty
)
,
CTEPhysicalWhs AS
(
SELECT
Tx.[Item Code]
, ISNULL(Ty.Qty, 0) AS 'A1 Qty'
, Tx.Qty AS 'B1 Qty'
, +(Ty.Qty - Tx.Qty) AS 'A1 to B1 Diff'
, Tx.AvgPrice AS 'Price /ea'
, (Ty.Qty - Tx.Qty) * Tx.AvgPrice AS 'Tot Price Diff'
, Tx.Whs AS Warehouse
FROM
(
/*** Stock in B1 ***/
SELECT
T0.ItemCode AS 'Item Code'
, T0.WhsCode AS 'Whs'
, SUM(T0.OnHand) AS 'Qty'
, T1.AvgPrice AS AvgPrice
FROM OITW T0
INNER JOIN OITM T1 ON T1.ItemCode = T0.ItemCode
GROUP BY T0.ItemCode, T0.WhsCode, T1.AvgPrice
) AS Tx
FULL JOIN
(
/*** Stock in Accellos ***/
SELECT
T0.PRODUCT AS 'Item Code'
, T0.WAREHOUSE collate DATABASE_DEFAULT AS 'Whs'
, SUM(T0.Quantity) AS 'Qty'
, '' AS AvgPrice
FROM A1Warehouse.dbo.BINLOCAT T0
WHERE T0.WAREHOUSE <> 'TRANSIT'
GROUP BY T0.PRODUCT, T0.WAREHOUSE--, T1.AvgPrice
) AS Ty
ON Ty.[Item Code] = Tx.[Item Code] collate SQL_Latin1_General_CP850_CI_AS AND Ty.Whs = Tx.Whs collate SQL_Latin1_General_CP850_CI_AS
WHERE Tx.Qty <> Ty.Qty
)
SELECT
???
If anybody can suggest how I go about 'subtracting' one result set from the other, to ultimately show only products that are genuinely out of sync it will be greatly appreciated. All ideas and suggestions are welcome.
Finally I would like to apologise for the length of this post, and reiterate that I want the results below -
Thanks for having a read over this post.
Kind Regards,
David
-
First question, why you have Wharehouses ('Whs') in SELECT ... GROUP BY in CTEs (SAP, Accellos) and do not join grouped data by warehouses?
Suppose you just omit Wharehouses from SELECT ... GROUP BY in CTEs of the first query, thus getting totals for the item at all warehouses.
Isn' it what you need?
-
In the absence of the OP I will hypothesis that it is required to identify discrepancies within a warehouse.
You have 'real' warehouses and 'Transit' warehouses. Where the discrepancy between Warehouse A in System X and Warehouse A in System Y is reconciled by the 'Transit' warehouse, the discrepancy can be ignored. If the values were
WhsA/SystX = 200, WhsA/SystY = 300 and
WhsB/SystX = 500, WhsB/SystY = 400
Then you would still need to report the error as the stock locations are out of sync even though the total stock is correct.
-
David,
How about the following?
WITH DIFFERENCES AS (
SELECT Tx.[Item Code]
, ISNULL(Ty.Qty, 0) AS 'A1 Qty'
, Tx.Qty AS 'B1 Qty'
, Ty.Qty - Tx.Qty AS 'A1 to B1 Diff'
, Tx.AvgPrice AS 'Price /ea'
, (Ty.Qty - Tx.Qty) * Tx.AvgPrice AS 'Tot Price Diff'
, Tx.Whs AS Warehouse
FROM
(/*** Stock in SAP Business One ***/
SELECT T0.ItemCode AS 'Item Code'
, T0.WhsCode AS 'Whs'
, SUM(T0.OnHand) AS 'Qty'
, T1.AvgPrice AS AvgPrice
FROM OITW AS T0
INNER JOIN OITM AS T1
ON T1.ItemCode = T0.ItemCode
GROUP BY T0.ItemCode, T0.WhsCode, T1.AvgPrice
) AS Tx
FULL OUTER JOIN
(/*** Stock in Accellos ***/
SELECT T0.PRODUCT AS 'Item Code'
, T0.WAREHOUSE collate DATABASE_DEFAULT AS 'Whs'
, SUM(T0.Quantity) AS 'Qty'
, '' AS AvgPrice
FROM A1Warehouse.dbo.BINLOCAT AS T0
GROUP BY T0.PRODUCT, T0.WAREHOUSE--, T1.AvgPrice
) AS Ty
ON Ty.[Item Code] = Tx.[Item Code] COLLATE SQL_Latin1_General_CP850_CI_AS
AND Ty.Whs = Tx.Whs COLLATE SQL_Latin1_General_CP850_CI_AS
WHERE Tx.Qty <> Ty.Qty
)
SELECT D.*
FROM DIFFERENCES AS D
WHERE D.[Item Code] NOT IN (
SELECT D1.[Item Code]
FROM DIFFERENCES AS D1
WHERE D1.[Item Code] = D.[Item Code]
GROUP BY D1.[Item Code]
HAVING MIN(D1.Warehouse) <> 'TRANSIT'
AND MAX(D1.Warehouse) = 'TRANSIT'
AND SUM([A1 to B1 Diff]) = 0
)
ORDER BY D.[Item Code]
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply