Skip to Content

SAP Business One Sales Order Query, to show Delivery and Invoice (Quantity and Open Quantity)

SAP Business One Sales Order Query, to show Delivery and Invoice (Quantity and Open Quantity).

We have a unique query / report where our finance is looking at presenting senior management with a unified report, using the Sales Order (SO) as the 'start' of the process, and then moving on to the Delivery Order (DO) and AR Invoice.

Data entry:

  1. Create an SO # 100, with 2 row items (Apple = 10 pcs, Orange = 15 pcs).
  2. Create a DO # 201, with 2 row items (Apple = 3 pcs, Orange = 10 pcs).
  3. Create an AR Invoice # 220, with 2 row items (Apple = 1 pcs, Orange = 7 pcs).

What is needed is to show (per row of each SO):

(the unit price / value is required but I believe we can work on the query to multiply the quantity and price to derive that).

The key challenge is that the Finance department sometimes skips the DO creation and copies the SO directly to AR Invoice.

In the event that they skip the DO creation, the report is to show the SO # and Line Num, but the DO Qty & DO Open Qty will be 0.

Would such a query be possible?

Thank you in advance!

so-query.png (5.6 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • avatar image
    Former Member
    Oct 21, 2016 at 05:56 PM

    Hello,

    Try this

    SELECT  T0.DocNum, T1.LineNum+1 AS 'SO Line Num', T1.ItemCode, T1.Dscription, T1.Quantity AS 'SO Qty',
            T1.OpenQty AS 'SO Open Qty', SUM(ISNULL(T2.Quantity, 0)) AS 'DO Qty',
            SUM(ISNULL(T2.OpenQty, 0)) AS 'DO Open Qty',
            SUM(ISNULL(T3.Quantity, 0) + ISNULL(T4.Quantity, 0)) AS 'Invoice Qty'
    FROM    dbo.ORDR T0
            INNER JOIN dbo.RDR1 T1 ON T1.DocEntry = T0.DocEntry
            LEFT JOIN dbo.DLN1 T2 ON T2.BaseEntry = T1.DocEntry
                                     AND T2.BaseLine = T1.LineNum
                                     AND T2.BaseType = T0.ObjType
            LEFT JOIN dbo.INV1 T3 ON T3.BaseEntry = T2.DocEntry
                                     AND T3.BaseLine = T2.LineNum
                                     AND T3.BaseType = 15
            LEFT JOIN dbo.INV1 T4 ON T4.BaseEntry = T1.DocEntry
                                     AND T4.BaseLine = T1.LineNum
                                     AND T4.BaseType = T0.ObjType
    GROUP BY T0.DocNum, T1.LineNum, T1.ItemCode, T1.Dscription, T1.Quantity, T1.OpenQty, T3.Quantity, T4.Quantity

    Regards,

    Bala

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 24, 2016 at 08:55 PM

    Hi,

    Yes possible to create such query.

    Thanks

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 14, 2016 at 08:05 AM

    Dear Singh

    The following query may solve your problem. I do the same steps like you.

    1. Create an SO # 100, with 2 row items (Apple = 10 pcs, Orange = 15 pcs).
    2. Create a DO # 201, with 2 row items (Apple = 3 pcs, Orange = 10 pcs).
    3. Create an AR Invoice # 220, with 2 row items (Apple = 1 pcs, Orange = 7 pcs).
    4. Create an AR Invoice #221, with 2 row items (Apple = 7 pcs, Orange = 5 pcs).
    5. If Create an AR Invoice #222, with 2 row items (Apple = 2 pcs, Orange = 3 pcs), then will complete SO #100.

    SELECT T0.[DocNum], T1.[LineNum], T1.[ItemCode], T1.[Quantity] as 'SO Qty', T1.[OpenQty] as 'SO Open Qty', T2.[Quantity] as 'DO Qty', T2.[OpenQty] as 'DO Oepn Qty', (T3.[Quantity]+T4.quantity) as 'AR Inv Qty' FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry] left join DLN1 T2 on T2.baseentry=T1.docentry and T2.itemcode=T1.itemcode left join inv1 T3 on T3.baseentry=T2.docentry and T3.itemcode=T2.itemcode left join inv1 T4 on T4.baseentry=T1.docentry and T4.itemcode=T1.itemcode WHERE T0.[DocNum] =[%0]

    Add comment
    10|10000 characters needed characters exceeded