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

[former_member201155]

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!

[Johan_H]

Hi,

All data can be obtained from the SO data, except whether a delivered quantity was created with a delivery note or an invoice.

Is this relevant to your query?

regards,

Johan

[kvbalakumar]

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

[azaleamaemae]

I'm using this basic query in one of our integrations with SAPB1 and today found that I need to filter out cancelled invoices.

This add'l JOIN will do the trick (add it as the last JOIN):

JOIN OINV SalesInvoice ON SalesInvoice.DocEntry = T4.DocEntry and SalesInvoice.CANCELED = 'N'

[azaleamaemae]

This add'l JOIN will filter out invoice cancellations: `JOIN OINV SalesInvoice ON SalesInvoice.DocEntry = T4.DocEntry and SalesInvoice.CANCELED = 'N'`