ONT Order/Invoice Summary

Description
Categories: BI Publisher
Imported from BI Publisher
Description: Order/Invoice Summary Report
Application: Order Management
Source: Order/Invoice Summary Report (XML)
Short Name: OEXOEIOS_XML
DB package: ONT_OEXOEIOS_XMLP_PKG
Run ONT Order/Invoice Summary and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Parameter Name SQL text Validation
Operating Unit
 
LOV
Sort By
 
LOV Oracle
Open Orders Only
 
LOV Oracle
Customer Name (From)
 
LOV Oracle
Customer Name (To)
 
LOV Oracle
Salesperson (From)
 
LOV Oracle
Salesperson (To)
 
LOV Oracle
Order Type (From)
 
LOV Oracle
Order Type (To)
 
LOV Oracle
Order Number (From)
 
Number
Order Number (To)
 
Number
Ship to Country
 
LOV Oracle
Use Ledger Currency
 
LOV Oracle
Blitz Report™