Recapitulation - Sales Order and Delivery Note Report

[supjkt]

Hi there,

need help please.

we try to looking built-in report on SAP Business One 9.2, but can't find for our needs.

we like to lookup all Sales Order which have been created for certain period (date range), and see its status (delivery or still outstanding, how many, when..etc).

basically, what it's told in Relationship map in sales order, we like to view as report.

is that possible? trying to figure out name table, but still struggling.

we consider to build report using crystal report.

kindly please help.

thank you.

[supjkt]

hi there..

thanks for help. those all good reference. thank you so much.

now it depends on the user, whether the report is informative enough or not.

thanks all.

[kothandaraman_nagarajan]

Hi,

Check this thread for complete details,

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

Regards,

Nagarajan

[jimmyl]

Hi Pengguna,

Just checking if you've tried those built-in "Open Items List" and Sales Analysis Reports, but those didn't help.

It's possible to get the relationship between Sales Order and Delivery in SQL, so you can check the delivery status - if I understand your requirements correctly. You may refer to the query in my reply to a similar scenario. https://answers.sap.com/questions/438295/find-target-oldn-given-ordr.html. You should be able to get the table relationship and fields (line status, open quantity, doc date, delivery date and etc.) for your requirements.

If the result you would like to return is just a list of documents, you may try to run the query using the "Query Manager" in Business One. (You may refer to this short video in case you are not familiar with this feature.) The benefit will be that the system can detect some key fields and create the "Golden Arrow" link to allow users to open related windows directly. In this case, you don't have to touch Crystal Reports.

However, if you need your report with some advanced formatting, say grouping, subtotal calculations, or conditional formatting on the result returned, then you can still use the query but in Crystal Reports.

Hope it helps,

Jimmy

[nalamo]

The Order header table is ORDR, lines table RDR1. The Delivery Header is ODLN and lines is DLN1. In the delivery line table, there is BaseEntry and BaseLine which correspond to the DocEntry and LineNum of the Sales Order from which the Delivery was created.

Link these tables together and you have the foundation for your report.