Skip to Content

B1 Open Sales order query with batch numbers/selected batches

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]

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

4 Answers

  • Best Answer
    Jan 21 at 02:23 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 10 at 03:10 PM

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

    Regards, Zal

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 11 at 07:12 AM

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

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 12 at 10:41 AM

    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

    Add comment
    10|10000 characters needed characters exceeded