Work Order – Sales Order Connecting Query

If you have implemented O2C Cycle Order Management and Supply Chain Execution Module in Oracle Fusion, there are chances that your Sales Order (SO) will need some work to be done on item before it is shipped to the customer. In that case you might be using Supply Chain Orchestration to create the Work Order (WO) against an SO and reserve the item against the newly created Work Order. And In this scenario, it is inevitable that you’ll end up querying the WO – SO relationship. In this post I’ll share the small but important query you can use to connect the two entities. But pls pay attention to two statuses being queried in the SQL. Use them wisely as applicable in your case.

Main Tables Used are:

• DOS_SUPPLY_TRACKING_LINE_DOCS –> To hold the relationship between Supply Line and Work Order Number.
• DOS_SUPPLY_LINES –> Child table of DOS_SUPPLY_HEADERS. Table stores the relationship between supply lines and Supply Headers
• DOS_SUPPLY_HEADERS –> This table stores the Sales Order Number. By connecting it to DOS_SUPPLY_LINES table and then DOS_SUPPLY_TRACKING_LINE_DOCS table, one must be able to find the relationship between Work Order and SO.

By the way, Here ( India) or Here ( Other Geographies) are few UNCONVENTIONAL tricks which I have learnt to improve the performance of BI reports or SQL queries. Try them only when nothing else works.. kind of SOS...

Work Order- Sales Order Connection Query

SELECT dstld.document_number wo_number,
dsl.status_code supply_line_status,
dsh.status_code supply_header_status_code,
dsh.supply_order_number ,
dsh.supply_order_reference_number sales_order_number
FROM dos_supply_tracking_line_docs dstld ,
dos_supply_lines dsl,
dos_supply_headers dsh
WHERE 1=1
AND dstld.document_type=’ORA_WO’ AND dsl.line_id=dstld.line_id
AND dsh.header_id = dsl.header_id AND dsh.supply_order_source =’DOO’

Related Posts:

WorkOrder Detail Query
Sales Order Query in Oracle Fusion
Oracle Fusion Sales Order Table – DOO_HEADERS_ALL

Feedback:
Hope the article helped you. If it did, please rate the post. In case it didn’t, do leave a comment to let us know what did we miss.

Reference:
support.oracle.com

• https://support.oracle.com

Check out the Amazon Deals of the day based on your interests..

Check out the Amazon Deals of the day based on your interests..