Skip to Content

Stocks Carry forward balance within SAP B1 9.1 Query

Hi Gurus,

I am trying to get a query where a particular style has stocks in SAP. i we have released few piece out of it then i need to see remaining balance of stock left like a carry forward balance. below mentioned query takes full stocks every time when we have new sales order under same customer. Purpose of this query is to see order is filled completely or not and if not then what style is not filled under what sales order of the same customer.

Query is as below:

SELECT

T0.[DocDueDate],

T0.[CardName],

T0.[NumAtCard],

T0.[DocNum],

T1.[ItemCode],

T1.[Dscription],

T3.[OnHand],

T3.[WhsCode],

T1.[Quantity] as 'PO QTY',

T1.OpenQty AS 'To Release'

FROM

ORDR T0 INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode] INNER JOIN OITW T3 ON T2.[ItemCode] = T3.[ItemCode]

WHERE

T0.[CardCode] = 'c00192' and

T0.[DocStatus] = 'O' and

T3.[WhsCode] = '161' and

T0.[NumAtCard] Like '%%818237%%'

GROUP BY

T0.[DocDueDate], T0.[CardName], T0.[NumAtCard], T0.[DocNum], T1.[ItemCode], T1.[Dscription], T3.[OnHand], T3.[WhsCode], T1.[Quantity], T1.OpenQty

ORDER BY T0.[DocDueDate], T0.[NumAtCard], T0.[DocNum]

Thanks

Shahzad.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Nov 04, 2017 at 11:47 PM

    Hi Shahzad,

    If I understood correct, this is what you need:

    SELECT
    	T0.[DocDueDate],
    	T0.[CardName],
    	T0.[NumAtCard],
    	T0.[DocNum],
    	T1.[ItemCode],
    	T1.[Dscription],
    	((T3.[OnHand] - T3.[IsCommited]) + T1.OpenQty) AS 'Available',
    	T3.[WhsCode],
    	T1.[Quantity] as 'PO QTY',
    	T1.OpenQty AS 'To Release'
    FROM
    	ORDR T0 
    	INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry] 
    	INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode] 
    	INNER JOIN OITW T3 ON T2.[ItemCode] = T3.[ItemCode]
    WHERE
    	T0.[CardCode] = 'c00192' and
    	T0.[DocStatus] = 'O' and
    	T3.[WhsCode] = '161' and
    	T0.[NumAtCard] Like '%818237%'
    GROUP BY
    	T0.[DocDueDate], 
    	T0.[CardName], 
    	T0.[NumAtCard], 
    	T0.[DocNum], 
    	T1.[ItemCode], 
    	T1.[Dscription], 
    	T3.[OnHand],
    	T3.[IsCommited],
    	T3.[WhsCode], 
    	T1.[Quantity], 
    	T1.OpenQty
    ORDER BY 
    	T0.[DocDueDate], 
    	T0.[NumAtCard], 
    	T0.[DocNum]
    

    Hope it helps.

    Kind Regards,

    Diego Lother

    Add comment
    10|10000 characters needed characters exceeded