SAP Business One query for Sales order,delivery and invoice all joined

[millicentdark]

Dear Experts,

I need an SAP Business one query that will show in the below format;

The query should have a join where a sales order is made and joined to it's delivery and from delivery to Sales invoice. The query should also take into consideration where sales order is done and no delivery is done but an invoice is done on the sales order.

Please treat this as urgent. I do not want duplication but a one row output as shown above.

Regards

Justice

[zal_parchem2]

Hello Justice. I always like these types of requests, because it is a learning experience for both the person making the request and those working hard to provide you a solution. So, to that end, there are several questions you first need to answer so we learn your business "world" to find a solution.

1. Just from what I see above, in order to get the report with one line output for a Sales Order, your End Users must do a complete delivery of ONE Sales Order to ONE Delivery to ONE AR Invoice. Is that what your End Users do??? In my experience, End Users will sometimes combine several Sales Orders into one delivery (you can see in the attached SQL results) and they sometimes will split a Sales Order to several deliveries (as you can see in the second screen print of the SQL results).
2. What do you mean by "Net"??? Net generally means Sales minus Taxes and minus Freight. How do you define it???
3. By "Description" do you mean the Customer PO Number (BP Reference Number)???
4. You mention you want only one row output, so the answer to these questions will let us know how you want to see the results. What happens if three Deliveries are connected to one Sales Order or if two Sales Orders are combined into one Delivery??? Also remember that two or more Deliveries can be combined into one AR Invoice (and vice-versa). It can get quite complicated.
5. You say this is urgent, so please let us know the answers and that will give us a better idea of what you are looking for...

Best Regards, Zal

[kothandaraman_nagarajan]

Hi,

Try and change query from below thread,

https://archive.sap.com/discussions/thread/3583628

Regards,

Nagarajan

[former_member419594]

Hello Justice,

You need to check the lines (RDR1) where you can find the TargetType

15 = Delivery / 13 = Sales-Invoice / ...

you can try for the first steps:

select TOP 100 T0.DocNum, T0.cardCode, T1.ItemCode, T1.TargetType
,Case 
when T1.TargetType = '15' THEN (Select MIN(DocEntry) from DLN1 Where BaseRef = T0.Docnum)
when T1.TargetType = '13' THEN (Select MIN(DocEntry) from INV1 Where BaseRef = T0.Docnum)
ELSE '' END
FROM ORDR T0
INNER JOIN RDR1 T1 on T0.DocEntry = T1.DocEntry
Where T1.TargetType <> '-1'
Order by T0.Docnum desc

Targettype '-1' = closed or not delivered

The subquery in CASE ... only collect the first Entry of the Target Document.

Maybe you can try the way back from Invoice --> Delivery --> sales order...

you need these fields: INV1.Basetype, DLN1.BaseType

BaseType '17' = SalesOrder

BaseRef = DocEntry

hope that helps

Best regards

Markus