SELECT h.transactional_curr_code Currency,
h.conversion_rate Conversion_rate,
h.conversion_type_code Conversion_Type_Code,
h.transactional_curr_code use_currency,
DECODE (SUBSTR (UPPER (:p_order_by), 1, 1), 'S', sr.name, NULL)
Sales_Person,
DECODE (SUBSTR (UPPER (:p_order_by), 1, 1), 'O', h.ordered_date, NULL)
dummy_order_date,
DECODE (SUBSTR (UPPER (:p_order_by), 1, 1), 'A', agree.name, NULL)
dummy_agreement,
DECODE (SUBSTR (UPPER (:p_order_by), 1, 1),
'P', h.cust_po_number,
NULL)
dummy_po_num,
ot.name Order_Type,
DECODE (SUBSTR (UPPER (:p_order_by), 1, 1),
'C', party.party_name,
NULL)
Customer_Name1,
h.order_number Order_Number,
h.cust_po_number PO_Number,
h.ordered_date Order_Date,
party.party_name Customer_Name3,
h.header_id Header_id,
u.user_name created_by,
h.flow_status_code,
fl.meaning Order_Status,
agree.name Agreement,
sr.name Salesrep,
NVL (
SUM (
DECODE (
l.line_category_code,
'RETURN', ( NVL (l.unit_list_price, 0)
* (NVL (l.ordered_quantity, 0))
* (-1)),
(NVL (l.unit_list_price, 0) * (NVL (l.ordered_quantity, 0))))),
0)
List_Value,
NVL (
SUM (
DECODE (
l.line_category_code,
'RETURN', ( NVL (l.unit_selling_price, 0)
* (NVL (l.ordered_quantity, 0))
* (-1)),
(NVL (l.unit_selling_price, 0)
* (NVL (l.ordered_quantity, 0))))),
0)
Order_Amount,
NVL (
SUM (
DECODE (
l.line_category_code,
'RETURN', ( NVL (l.unit_selling_price, 0)
* (NVL (l.shipped_quantity, 0))
* (-1)),
(NVL (l.unit_selling_price, 0)
* (NVL (l.shipped_quantity, 0))))),
0)
Ship_Value,
l.charge_periodicity_code
FROM oe_order_headers_all h,
oe_order_lines_all l,
oe_transaction_types_tl ot,
fnd_user u,
fnd_lookup_values fl,
oe_agreements_tl agree,
HZ_CUST_SITE_USES_ALL su,
hz_party_sites party_site,
hz_locations loc,
hz_cust_acct_sites_all acct_site,
ra_salesreps sr,
fnd_territories_vl terr,
hz_parties party,
hz_cust_accounts cust_acct
WHERE h.sold_to_org_id = cust_acct.cust_account_id
AND cust_acct.party_id = party.party_id
AND h.header_id = l.header_id(+)
AND NVL (h.org_id, 0) = NVL (:p_org_id, 0)
AND h.order_type_id = ot.transaction_type_id
AND ot.language = USERENV ('LANG')
AND sr.salesrep_id(+) = h.salesrep_id
AND NVL (sr.org_id(+), NVL (:p_org_id, 0)) = NVL (:p_org_id, 0)
AND h.agreement_id = agree.agreement_id(+)
AND agree.language(+) = USERENV ('LANG')
AND h.created_by = u.user_id
AND h.ship_to_org_id = su.site_use_id(+)
AND acct_site.party_site_id = party_site.party_site_id(+)
AND loc.location_id(+) = party_site.location_id
AND su.CUST_ACCT_SITE_ID = acct_site.cust_acct_site_id(+)
AND loc.country = terr.territory_code(+)
AND fl.lookup_type = 'FLOW_STATUS'
AND fl.lookup_code = h.flow_status_code
AND fl.language = USERENV ('LANG')
GROUP BY h.transactional_curr_code,
h.conversion_rate,
h.conversion_type_code,
DECODE (:p_use_functional_currency,
'N', h.transactional_curr_code,
:rp_functional_currency),
sr.name,
cust_acct.cust_account_id,
h.order_number,
h.cust_po_number,
h.ordered_date,
party.party_name,
ot.name,
h.header_id,
h.flow_status_code,
fl.meaning,
agree.name,
u.user_name,
l.charge_periodicity_code
ORDER BY 1,
DECODE (SUBSTR (UPPER (:p_order_by), 1, 1),
'S', sr.name,
'O', h.ordered_date,
h.order_number),
DECODE (SUBSTR (UPPER (:p_order_by), 1, 1),
'S', party.party_name,
'O', party.party_name),
h.order_number
