Skip to Content
0
Nov 03, 2017 at 07:55 PM

Stocks Carry forward balance within SAP B1 9.1 Query

75 Views

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.