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