ONT Orders Summary

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,  -- 8925328
                      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
	--added as fix
	,ONT_OEXOEOSR_XMLP_PKG.C_CURRENCYFORMULA(h.transactional_curr_code) C_CURRENCY
	,ONT_OEXOEOSR_XMLP_PKG.C_LIST_AMOUNTFORMULA(h.transactional_curr_code,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),h.conversion_type_code,h.ordered_date,h.conversion_rate) C_LIST_AMOUNT
    ,ONT_OEXOEOSR_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,h.conversion_rate) C_ORDER_AMOUNT
    ,ONT_OEXOEOSR_XMLP_PKG.C_SHIPPED_AMOUNTFORMULA(h.transactional_curr_code,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),h.conversion_type_code,h.ordered_date,h.conversion_rate) C_SHIPPED_AMOUNT
    ,ONT_OEXOEOSR_XMLP_PKG.C_ORDER_COUNTFORMULA() C_ORDER_COUNT
    ,ONT_OEXOEOSR_XMLP_PKG.C_LINE_COUNTFORMULA(h.header_id) C_LINE_COUNT
    ,ONT_OEXOEOSR_XMLP_PKG.C_DATA_NOT_FOUNDFORMULA(h.transactional_curr_code) C_DATA_NOT_FOUND
	FROM	oe_order_headers_all h,
		oe_order_lines_all l,
                                      oe_transaction_types_tl ot,
                                    fnd_user u,
                fnd_lookup_values fl,   -- 8925328
		oe_agreements_tl agree,
		HZ_CUST_SITE_USES_ALL  su,
                                      hz_party_sites party_site,
                                      -- hz_loc_assignments loc_assign, --bug 9796318
                                      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_v,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_v,0)) =nvl(:p_org_id_v,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           loc.location_id = loc_assign.location_id(+) --bug 9796318
                   -- AND           NVL(acct_site.org_id,-99) = NVL(loc_assign.org_id,-99) --bug 9796318
	   AND	su.CUST_ACCT_SITE_ID = acct_site.cust_acct_site_id(+)
                   AND           loc.country = terr.territory_code(+)
            -- Begin 8925328
                AND       fl.lookup_type = 'FLOW_STATUS'
                AND       fl.lookup_code = h.flow_status_code
                AND       fl.language = Userenv('LANG')
            -- End 8925328
	&lp_order_num
	&lp_salesrep
	&lp_order_type
	&lp_country
	&lp_customer_name
	 &lp_order_date
                  &lp_po_num
                  &lp_agreement
                 &lp_order_source
                 &lp_created_by
                &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.cust_po_number,
                                               h.ordered_date  ,
                                      party.party_name,
		ot.name,
		h.header_id,
                                     h.flow_status_code,
                                    fl.meaning,  -- 8925328
                                    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*/
	 -- bug 9796318 - removed the space between ":" and "p_order_by" in below clause
	 ORDER BY 1 ASC,9 ASC,5 ASC,23 ASC,10 ASC,8 ASC,7 ASC,6 ASC , 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