ONT Order/Invoice Detail

SELECT    distinct
  h.transactional_curr_code          Currency,
                                     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',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,  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,
                                    ot.name Order_Type1,
                                    h.conversion_rate  Conversion_Rate,
  h.salesrep_id header_salesrep_id,
  sr.name header_salesrep_name,
  h.ship_to_org_id header_ship_country_id,
  terr.territory_short_name header_ship_country_name,
  ONT_OEXOEIOD_XMLP_PKG.C_LINE_COUNTFORMULA(h.header_id ) C_LINE_COUNT,
  ONT_OEXOEIOD_XMLP_PKG.C_CURRENCYFormula(:Currency1) c_Currency,
  ONT_OEXOEIOD_XMLP_PKG.C_DATA_NOT_FOUNDFORMULA(:Currency1)
 FROM oe_order_headers h,
  oe_order_lines_all l,
  oe_transaction_types_tl  ot,
                                hz_cust_site_uses_all site,
  hz_cust_acct_sites_all acct_site,
                                hz_party_sites party_site,
                                hz_locations loc,
  ra_salesreps_all sr,
  fnd_territories_vl terr,
                                hz_parties party,
                 hz_cust_accounts acct
                 WHERE h.sold_to_org_id = acct.cust_account_id
                 AND     party.party_id   = acct.party_id
 AND  h.header_id=l.header_id
    AND l.invoice_interface_status_code = 'YES'
  AND  nvl(h.org_id,0) = nvl(:lp_org_id,0)
                AND        ot.transaction_type_id = h.order_type_id
                AND       ot.language = userenv('LANG')
   AND   h.salesrep_id = sr.salesrep_id (+)
               AND            nvl(sr.org_id(+),0) = nvl(:lp_org_id,0)
                AND    h.ship_to_org_id = site.site_use_id
 AND  site.cust_acct_site_id = acct_site.cust_acct_site_id
                AND     acct_site.party_site_id = party_site.party_site_id
                AND     party_site.location_id  = loc.location_id
 AND loc.country = terr.territory_code
 &lp_order_num
 &lp_salesrep
 &lp_order_type
 &lp_country
 &lp_customer_name
                  &lp_open_orders_only
 &lp_order_category
 &lp_line_category
 ORDER BY  1 ASC,3 ASC,
  4 ASC,6 ASC,5 ASC,8 ASC,7 ASC ,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