Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2014
    Posts
    35

    Unanswered: Need to identify genuine stock quantity discrepancies between ERP & WMS?

    Hi Everyone,

    I have been asked to report on missing Stock in my works Warehouses. 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 -

    Code:
    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 -

    Click image for larger version. 

Name:	awb5ds.jpg 
Views:	2 
Size:	138.2 KB 
ID:	16015

    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).

    Code:
    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 -

    Click image for larger version. 

Name:	riuffc.jpg 
Views:	2 
Size:	95.2 KB 
ID:	16016

    I can then alter the WHERE clause to return only SAP warehouses, e.g.: none that are 'TRANSIT', by modifying the line below -

    Code:
    WHERE T0.WAREHOUSE <> 'TRANSIT'
    Click image for larger version. 

Name:	fa26tf.jpg 
Views:	1 
Size:	88.8 KB 
ID:	16017

    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) warehouses (e.g.: 03, 04, etc).

    However aside from creating the CTEs I am stuck for ideas...

    Code:
    /*
    ;
    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 -

    Click image for larger version. 

Name:	13yeyph.jpg 
Views:	2 
Size:	145.2 KB 
ID:	16018

    Thanks for having a read over this post.

    Kind Regards,

    David

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Would grouping by "Item Code" and adding a SUM(A1 to B1 Diff) As NetDiff and excluding NetDiff = 0 do it for you?
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •