Skip to Content
0

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

Oct 10, 2016 at 11:17 AM

1.3k

avatar image

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)
10 |10000 characters needed characters left characters exceeded

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

0
* Please Login or Register to Answer, Follow or Comment.

3 Answers

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

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

Share
10 |10000 characters needed characters left characters exceeded
Nagarajan K Oct 24, 2016 at 08:55 PM
0

Hi,

Yes possible to create such query.

Thanks

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Oct 14, 2016 at 08:05 AM
0

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]


untitled.png (8.1 kB)
Share
10 |10000 characters needed characters left characters exceeded