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.* |
Want to give back to the society? (Do you have something which you think can be used by folks working on Oracle world wide?) Then Click here, share your email id and a brief description of the topic. And keep your photo and the content ready.
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