cancel
Showing results for 
Search instead for 
Did you mean: 

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

0 Kudos

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
Active Contributor
0 Kudos

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

Accepted Solutions (0)

Answers (3)

Answers (3)

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Yes possible to create such query.

Thanks

kvbalakumar
Active Contributor
0 Kudos

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
Explorer
0 Kudos

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
Explorer
0 Kudos
This add'l JOIN will filter out invoice cancellations: `JOIN OINV SalesInvoice ON SalesInvoice.DocEntry = T4.DocEntry and SalesInvoice.CANCELED = 'N'`
Former Member
0 Kudos

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]