SELECT h.transactional_curr_code Currency2,
ot.name Order_Type,
DECODE(SUBSTR(UPPER(:P_ORDER_BY),1,1),'C',CUST_ACCT.CUST_ACCOUNT_ID,NULL) CUSTOMER_ID,
DECODE(SUBSTR(UPPER(:P_ORDER_BY),1,1),'C',PARTY.PARTY_NAME,NULL) CUSTOMER_NAME_2,
h.order_number Order_Number,
PARTY.PARTY_NAME CUSTOMER_NAME_1,
CUST_ACCT.ACCOUNT_NUMBER CUSTOMER_NUMBER,
ag.name Agreement,
sr.name Sales_Person,
sum(nvl(l.ordered_quantity,0)* DECODE(L.LINE_CATEGORY_CODE,'RETURN', -1*nvl(l.unit_list_price,0),nvl(l.unit_list_price,0))) Order_List,
sum(nvl(l.ordered_quantity,0)* DECODE(L.LINE_CATEGORY_CODE,'RETURN',-1*nvl(l.unit_selling_price,0),nvl(l.unit_selling_price,0))) Order_Amount ,
curr.precision c_pre,
l.charge_periodicity_code
FROM oe_order_headers_all h,
oe_order_lines_all l,
OE_TRANSACTION_TYPES_TL OT,
ra_salesreps sr,
HZ_CUST_ACCOUNTS CUST_ACCT,
HZ_PARTIES PARTY,
oe_agreements ag ,
fnd_currencies curr
WHERE
OT.TRANSACTION_TYPE_ID = h.order_type_id
AND h.sold_to_org_id = CUST_ACCT.CUST_ACCOUNT_ID
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND h.salesrep_id=sr.salesrep_id (+)
AND h.agreement_id = ag.agreement_id(+)
AND l.header_id=h.header_id
AND l.service_reference_line_id is null
AND NVL(h.cancelled_flag, 'N') = 'N'
and nvl(h.org_id,0) = nvl(:p_organization_id,0)
and nvl(l.org_id,0) = nvl(:p_organization_id,0)
and nvl(sr.org_id,0) = nvl(:p_organization_id,0) -- BUG#2202575
and ot.LANGUAGE = SYS_CONTEXT('USERENV','LANG') -- change for bug3526405
and h.transactional_curr_code = curr.currency_code
GROUP BY h.transactional_curr_code,
ot.name,
DECODE(SUBSTR(UPPER(:P_ORDER_BY),1,1),'C',CUST_ACCT.CUST_ACCOUNT_ID,NULL),
DECODE(SUBSTR(UPPER(:P_ORDER_BY),1,1),'C',PARTY.PARTY_NAME,NULL),
h.order_number,
PARTY.PARTY_NAME,
CUST_ACCT.ACCOUNT_NUMBER,
h.ordered_date,
ag.name,
sr.name,
curr.precision,
l.charge_periodicity_code
ORDER BY h.transactional_curr_code,
ot.name
