B1 Open Sales order query with batch numbers/selected batches

[ebie]

We have and use reports and queries to show open Sales Orders.

What i need now is to see what batches is allocated to those open sales orders, so looking for a query that shows the Sales order and then also populates the batches and quantities linked to the open sales order

would like to have it added to this query

SELECT T0.[DocNum], T0.[DocDate], T0.[DocDueDate], t2.slpname, t3.u_name, T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[OpenQty], T1.[Quantity], T1.[Price] FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode INNER JOIN OUSR T3 ON T0.[UserSign] = T3.[USERID]

WHERE T0.[DocDueDate] < (getdate() +6000) AND T1.[LineStatus] = 'O'

order by T0.[DocDueDate]

[kothandaraman_nagarajan · Accepted solution]

Hi,

Try this query,

SELECT T1.[DocNum], T1.[CardName], T2.[ItemCode], T2.[Dscription], T2.[Quantity], case when T0.[Direction] = '2' then 'Allocatedqty' end, T0.[Quantity], T0.[BatchNum] FROM IBT1 T0 left join ORDR T1 on T0.[BaseType] = T1.[ObjType] left JOIN RDR1 T2 ON T1.DocEntry = T2.DocEntry WHERE T0.[Direction] = '2' and T0.[BaseNum] = T1.[DocNum] and T1.Docdate between [%0] and [%1]

GROUP BY T1.[DocNum], T1.[CardName], T2.[ItemCode], T2.[Dscription], T2.[Quantity], T0.[Direction], T0.[Quantity], T0.[BatchNum]

order by T1.[DocNum]

Regards,

Nagarajan

[kothandaraman_nagarajan]

Hi,

For allocated sales orders, try this query;

SELECT T1.[DocNum], T1.[CardName], T2.[ItemCode], T2.[Dscription], T2.[Quantity], case when T0.[Direction] = '2' then 'Allocatedqty' end, T0.[Quantity], T0.[BatchNum] FROM IBT1 T0 left join ORDR T1 on T0.[BaseType] = T1.[ObjType] left JOIN RDR1 T2 ON T1.DocEntry = T2.DocEntry WHERE T0.[Direction] = '2' and T0.[BaseNum] = T1.[DocNum] GROUP BY T1.[DocNum], T1.[CardName], T2.[ItemCode], T2.[Dscription], T2.[Quantity], T0.[Direction], T0.[Quantity], T0.[BatchNum] order by T1.[DocNum]

Regards,

Nagarajan

[ebie]

HI

thanks for the solution but i get this error when running the query

[ebie]

SAP business one 9.1 (9.10.170) PL:07 (32 bit)

[zal_parchem2]

Hello Ebie - oops wait - what Version of SAP B1 are you using?

Regards, Zal