ONT Orders Summary
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Orders Summary Report
Application: Order Management
Source: Orders Summary Report (XML)
Short Name: OEXOEOSR_XML
DB package: ONT_OEXOEOSR_XMLP_PKG
Description: Orders Summary Report
Application: Order Management
Source: Orders Summary Report (XML)
Short Name: OEXOEOSR_XML
DB package: ONT_OEXOEOSR_XMLP_PKG
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 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Operating Unit |
|
LOV | |
Sort By |
|
LOV Oracle | |
Customer Name (From) |
|
LOV Oracle | |
Customer Name (To) |
|
LOV Oracle | |
Salesperson (From) |
|
LOV Oracle | |
Salesperson (To) |
|
LOV Oracle | |
Order Date (From) |
|
Date | |
Order Date (To) |
|
Date | |
Order Number (From) |
|
Number | |
Order Number (To) |
|
Number | |
Ship to Country (From) |
|
LOV Oracle | |
Ship to Country (To) |
|
LOV Oracle | |
Order Type (From) |
|
LOV Oracle | |
Order Type (To) |
|
LOV Oracle | |
Customer PO Number (From) |
|
Char | |
Customer PO Number (To) |
|
Char | |
Created By (From) |
|
LOV Oracle | |
Created By (To) |
|
LOV Oracle | |
Agreement |
|
LOV Oracle | |
Order Source |
|
LOV Oracle | |
Open Orders Only |
|
LOV Oracle | |
Use Ledger Currency |
|
LOV Oracle |