ONT Order/Invoice Detail

Description
Categories: BI Publisher, Sales
Application: Order Management
Source: Order/Invoice Detail Report (XML)
Short Name: OEXOEIOD_XML
DB package: ONT_OEXOEIOD_XMLP_PKG
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
Parameter Name SQL text Validation
Invoice Line Context
 
Item Flex Code
 
Ledger Id
 
Number
Use Ledger Currency
 
LOV Oracle
Line Category
 
LOV Oracle
Order Category
 
LOV Oracle
Ship to Country
 
LOV Oracle
Order Number (To)
 
Number
Order Number (From)
 
Number
Order Type (To)
 
LOV Oracle
Order Type (From)
 
LOV Oracle
Salesperson (To)
 
LOV Oracle
Salesperson (From)
 
LOV Oracle
Customer Name (To)
 
LOV Oracle
Customer Name (From)
 
LOV Oracle
Item Display
 
LOV Oracle
Open Orders Only
 
LOV Oracle
Sort By
 
LOV Oracle