on 10-10-2016 12:17 PM
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:
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!
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
97 | |
9 | |
6 | |
6 | |
3 | |
3 | |
3 | |
3 | |
2 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.