ONT Order/Invoice Summary

SELECT	  
		h.transactional_curr_code          Currency,
                                    h.conversion_rate        Conversion_rate ,
                                    h.conversion_type_code Conversion_Type_Code ,
                                    decode (:p_use_functional_currency,'N',     h.transactional_curr_code, :rp_functional_currency) 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',ot.name,NULL) Order_Type,
	                 decode (substr(upper(:p_order_by),1,1), 'C',cust_acct.cust_account_id,NULL) Trx_id1,
		decode (substr(upper(:p_order_by),1,1), 'C',party.party_name,NULL) Customer_Name1,
		decode (substr(upper(:p_order_by),1,1), 'C',NULL,cust_acct.cust_account_id) Trx_id2,
		decode (substr(upper(:p_order_by),1,1), 'C',NULL,party.party_name) Customer_Name2,
		h.order_number           Order_Number, 
		h.ordered_date           Order_Date,
		party.party_name       Customer_Name3,
		h.header_id              Header_id,
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,
ONT_OEXOEIOS_XMLP_PKG.C_PRECISIONFORMULA(h.transactional_curr_code) C_PRECISION,
ONT_OEXOEIOS_XMLP_PKG.C_ORDER_AMOUNTFORMULA(h.transactional_curr_code ,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) , h.conversion_type_code ,h.ordered_date ,ONT_OEXOEIOS_XMLP_PKG.C_PRECISIONFORMULA(h.transactional_curr_code) ,h.conversion_rate) C_ORDER_AMOUNT,
ONT_OEXOEIOS_XMLP_PKG.C_LINE_COUNTFORMULA(h.header_id) C_LINE_COUNT,
ONT_OEXOEIOS_XMLP_PKG.C_ORDER_COUNTFORMULA C_ORDER_COUNT
	FROM	oe_order_headers_all h,
		hz_cust_accounts cust_acct,
		hz_parties party,
		oe_order_lines_all l,
		oe_order_types_v ot, 
	                hz_cust_site_uses_all su,
		hz_locations loc,
          hz_party_sites party_site,
		hz_cust_acct_sites_all acct_site,
		ra_salesreps_all sr,
		fnd_territories_vl terr
	WHERE l.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 	 nvl(l.org_id,0) = nvl(:p_org_id,0)
  	AND   h.order_type_id = ot.order_type_id
	AND   l.ship_to_org_id = su.site_use_id
     and   terr.territory_code = loc.country
     and   loc.location_id = party_site.location_id
     and   party_site.party_site_id = acct_site.party_site_id
     and   su.cust_acct_site_id = acct_site.cust_acct_site_id
	&lp_order_num
	&lp_salesrep
	&lp_order_type
	&lp_country
	&lp_customer_name
	&lp_open_orders_only
	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.ordered_date  ,
		party.party_name, 
		ot.name,
		h.header_id
                HAVING sum(decode(l.invoice_interface_status_code,'YES',1,0)) > 0 
	ORDER BY  1 ,decode(substr(upper(:p_order_by),1,1), 
	'S', sr.name ,'O', ot.name,party.party_name),
	decode(substr(upper(:p_order_by),1,1), 
	'S', party.party_name ,'O', party.party_name),
	 h.order_number