Implemented O2C cycle? In this post I’ll mention the Sales Order Table Linkage with various tables to find important information of the Sales Order. Alias I have given to the table is dha i.e. if I have to connect any column of doo_headers_all table, join statement will look like dha.<column_name>
| Entity Name | Table Name | Join Condition(s) | Select column |
| Customer Name | hz_parties hp | dha.SOLD_TO_PARTY_ID = hp.party_id(+) | hp.party_name |
| sales Person | doo_sales_credits sales, hz_parties party1,msc_sales_credit_types typ | sales.SALESPERSON_ID = party1.party_id AND sales.header_id = dha.header_id AND typ.SALES_CREDIT_TYPE_ID = sales.SALES_CREDIT_TYPE_ID AND typ.name = ‘Quota Sales Credit’ | party1.party_name |
| Customer Account Name | doo_order_addresses address , hz_cust_accounts acct | address.header_id(+) = dha.header_id AND address.ADDRESS_USE_TYPE(+) = ‘BILL_TO’ AND address.cust_acct_id = acct.cust_account_id(+) | acct.account_name |
| Customer Account Number | doo_order_addresses address , hz_cust_accounts acct | address.header_id(+) = dha.header_id AND address.ADDRESS_USE_TYPE(+) = ‘BILL_TO’ AND address.cust_acct_id = acct.cust_account_id(+) | acct.account_number |
| ship to Customer | doo_order_addresses ship_address , hz_parties ship_party | AND ship_address.header_id(+) = dha.header_id AND ship_address.ADDRESS_USE_TYPE(+) = ‘SHIP_TO’ AND ship_party.party_id(+) = ship_address.party_id | ship_party.party_name |
| ship to address | doo_order_addresses ship_address , hz_parties ship_party , hz_party_sites party_site , hz_locations ship_to_loc | AND ship_address.header_id(+) = dha.header_id AND ship_address.ADDRESS_USE_TYPE(+) = ‘SHIP_TO’ AND ship_party.party_id(+) = ship_address.party_id AND party_site.party_site_id(+) = ship_address.party_site_id AND ship_to_loc.location_id(+) = party_site.location_id | ship_to_loc.ADDRESS1 ||’ ‘||ship_to_loc.ADDRESS2 ||’ ‘ ||ship_to_loc.city ||’ ‘||ship_to_loc.state |
| Order Type | fnd_lookup_values val | AND val.lookup_type = ‘ORA_DOO_ORDER_TYPES’ AND val.lookup_code = dha.order_type_code | val.meaning |
| AR Invoice Line Detail | ra_customer_trx_lines_all rctla | rctla.sales_order = dha.order_number | rctla.* |
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...
Related Posts:
Oracle Fusion Sales Order Table – DOO_HEADERS_ALL
Sales Order Query in Oracle Fusion
Work Order – Sales Order Connecting Query
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