Stocks Carry forward balance within SAP B1 9.1 Query

[shahmed]

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.

[former_member185682 · Accepted solution]

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

[shahmed · Accepted solution]

order-detail.jpg

Hi Diego Thanks for your response. but i am little bit off with what i require. Please see attached data.what i need is

Sr #1 has 270 is stock and 5 to release but when this style repeats at row # 15 (where SO # changed) it should show in stock 265 but what i am getting is, same numbers instead of remaining balance when style gets repeated in new sales order.

Thanks

[former_member185682 · Accepted solution]

Hi Shazhad,

Try the query below:

SELECT
	T0.[DocDueDate],
	T0.[CardName],
	T0.[NumAtCard],
	T0.[DocNum],
	T1.[ItemCode],
	T1.[Dscription],
	T3.[WhsCode],
	T3.[OnHand] - SUM(OpenQty) OVER (Partition By T1.ItemCode ORDER BY DocDueDate, LineNum, NumAtCard, DocNum) + SUM(T1.OpenQty) AS 'Available',
	SUM(T1.[Quantity]) AS 'PO QTY',
	SUM(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] AND T1.[WhsCode] = T3.[WhsCode]
WHERE
	T0.[CardCode] = 'c00192' and
	T0.[DocStatus] = 'O' AND
	T1.[LineStatus] = 'O' AND
	T0.[CANCELED] = 'N' AND
	T1.[WhsCode] = '01' AND
	T0.[NumAtCard] Like '%818237%'
GROUP BY
	T0.[DocDueDate], 
	T0.[CardName], 
	T0.[NumAtCard], 
	T0.[DocNum], 
	T1.[ItemCode], 
	T1.[Dscription],
	T3.[OnHand],
	T3.[WhsCode],
	T1.[LineNum],
	T1.OpenQty
ORDER BY 
	T0.[DocDueDate], 
	T1.[LineNum],
	T0.[NumAtCard], 
	T0.[DocNum]

I think will fit your purpose.

Hope it helps.

Kind Regards,

Diego Lother

[shahmed · Accepted solution]

Awesome. absolute genious.