ONT Orders Summary

Description
Categories: BI Publisher, Sales
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
Ledger Id
 
Number
Use Ledger Currency
 
LOV Oracle
Open Orders Only
 
LOV Oracle
Order Source
 
LOV Oracle
Agreement
 
LOV Oracle
Created By (To)
 
LOV Oracle
Created By (From)
 
LOV Oracle
Customer PO Number (To)
 
Customer PO Number (From)
 
Order Type (To)
 
LOV Oracle
Order Type (From)
 
LOV Oracle
Ship to Country (To)
 
LOV Oracle
Ship to Country (From)
 
LOV Oracle
Order Number (To)
 
Number
Order Number (From)
 
Number
Order Date (To)
 
Date
Order Date (From)
 
Date
Salesperson (To)
 
LOV Oracle
Salesperson (From)
 
LOV Oracle
Customer Name (To)
 
LOV Oracle
Customer Name (From)
 
LOV Oracle
Sort By
 
LOV Oracle