BI Report, Business Intelligence Report,, Oracle Fusion, Oracle Fusion Finance, sales Order

Sales Order Query in Oracle Fusion

June 9, 2020November 7, 2022 Mohit Saxena

Below is the Sales Order query to fetch you the following fields:

SO Number
Business Unit
SO Status
SO Creation Date
Salesperson
Customer Name
Customer Account
Account Number
SO Order Type
Customer PO Number
Creation Date
Order Date
Ship To Customer
Ship To Address

As you will be able to see, that this query is primarily based on DOO_HEADERS_ALL table which is the base table of Sales Order Information in Oracle Fusion.

SELECT hdr.ORDER_NUMBER SO_Number, 
hou.name Business_Unit, 
hdr.STATUS_CODE , 
TO_CHAR(hdr.CREATION_DATE, 'MM/DD/YYYY') SO_CREATION_DATE, 
(SELECT LISTAGG(party1.party_name, ', ') WITHIN GROUP ( ORDER BY sales.SALES_CREDIT_ID ) 
FROM DOO_SALES_CREDITS sales, 
hz_parties party1, 
MSC_SALES_CREDIT_TYPES typ 
WHERE 1=1 
AND sales.SALESPERSON_ID = party1.party_id 
AND sales.header_id = hdr.header_id 
AND typ.SALES_CREDIT_TYPE_ID = sales.SALES_CREDIT_TYPE_ID 
AND typ.name = 'Quota Sales Credit' )sales_Person, 
party2.party_name Customer_Name, 
acct.account_name Customer_Account , 
acct.account_number Acct_Number , 
val.meaning SO_Order_Type , 
hdr.customer_po_number , 
hdr.creation_date , 
hdr.ordered_date , 
ship_party.party_name ship_to_Customer, 
ship_to_loc.ADDRESS1 ||' '||ship_to_loc.ADDRESS2 ||' ' ||ship_to_loc.city ||' '||ship_to_loc.state ship_to_address 
FROM doo_headers_all hdr , 
hz_parties party2 , 
fnd_lookup_values val , 
doo_order_addresses address , 
doo_order_addresses ship_address , 
hz_parties ship_party , 
hz_party_sites party_site , 
hz_locations ship_to_loc , 
hz_cust_accounts acct , 
hr_operating_units hou 
WHERE 1=1 
AND hdr.SOLD_TO_PARTY_ID = party2.party_id(+) 
AND val.lookup_type = 'ORA_DOO_ORDER_TYPES' 
AND val.lookup_code = hdr.order_type_code 
AND address.header_id(+) = hdr.header_id 
AND address.ADDRESS_USE_TYPE(+) = 'BILL_TO' 
AND address.cust_acct_id = acct.cust_account_id(+) 
AND ship_address.header_id(+) = hdr.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 
AND hou.organization_id = hdr.org_id 
AND hdr.object_version_number = 
                     (SELECT MAX(object_version_number) 
                        FROM doo_headers_all dha_latest 
                       WHERE dha_latest.order_number = hdr.order_number 
                         AND dha_latest.status_code = hdr.status_code )

Related Posts
Sales Order Table Linkage in Oracle Fusion – DOO_HEADERS_ALL
DOO_LINES_ALL: Sales Order Lines Table
DOO_FULFILL_LINES_ALL: Columns, Indexes & Salient Points
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

Related