Sales Order Header Level information
Main query:
SELECT Nvl(BU.bu_name, 'No BU')
|| ' - '
|| Nvl(HP.party_name, 'No Party')
|| ' ('
|| Nvl(HCA.account_number, 'No Acct')
|| ') - '
|| HA.order_number
|| ' - '
|| HA.status_code
|| ' - '
|| To_char(SUM(Nvl(LA.extended_amount, 0)), '999,999,990.00')
|| ' '
|| HA.transactional_currency_code HeaderLevel_ORDER_INFO
FROM doo_headers_all HA,
doo_lines_all LA,
hz_cust_accounts HCA,
hz_parties HP,
fun_all_business_units_v BU
WHERE HA.header_id = LA.header_id(+)
AND HA.sold_to_customer_id = HCA.cust_account_id(+)
AND HCA.party_id = HP.party_id(+)
AND HA.org_id = BU.bu_id(+)
-- Only Sales Order Parameter
AND ( HA.order_number = :P_ORDER_NUM
OR :P_ORDER_NUM IS NULL )
GROUP BY BU.bu_name,
HP.party_name,
HCA.account_number,
HA.order_number,
HA.status_code,
HA.transactional_currency_code
-----------------
LOV query
SELECT order_number,
order_number AS DISPLAY_ORDER_NUMBER
FROM doo_headers_all HA
WHERE EXISTS (SELECT 1
FROM hz_cust_accounts HCA
WHERE HA.sold_to_customer_id = HCA.cust_account_id)
ORDER BY creation_date DESC
Result:
