ONT Order Discount Summary

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,
       	h.ordered_date         		Order_Date,
	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
			  --added
,ONT_OEXPRPRS_XMLP_PKG.C_DATA_NOT_FOUNDFORMULA(h.transactional_curr_code)  C_DATA_NOT_FOUND,
ONT_OEXPRPRS_XMLP_PKG.CF_1FORMULA(l.charge_periodicity_code)   C_CHARGE_PERIODICITY,
ONT_OEXPRPRS_XMLP_PKG.C_DISCOUNT_PRICEFormula(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))), 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))) )   C_DISCOUNT_PRICE,
ONT_OEXPRPRS_XMLP_PKG.C_ORDER_AMOUNTFormula(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))),curr.precision ) C_ORDER_AMOUNT,
ONT_OEXPRPRS_XMLP_PKG.C_ORDER_LISTFORMULA(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))),curr.precision)    C_ORDER_LIST
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) 
  and ot.LANGUAGE = SYS_CONTEXT('USERENV','LANG') 
  and  h.transactional_curr_code = curr.currency_code
  &lp_customer_name
  &lp_customer_number
  &lp_salesrep
  &lp_agreement
  &lp_order_type
  &lp_open_orders_only
  &lp_order_num
  &lp_order_date
  &lp_order_category 
&lp_line_category
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
&lp_having
ORDER BY h.transactional_curr_code,
	 ot.name
                    &lp_sort_by