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.
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.
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