Sales Order Query in Oracle Fusion

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