ONT Order Discount Summary

Description
Categories: BI Publisher
Imported from BI Publisher
Description: Order Discount Summary Report
Application: Order Management
Source: Order Discount Summary Report (XML)
Short Name: OEXPRPRS_XML
DB package: ONT_OEXPRPRS_XMLP_PKG
Run ONT Order Discount Summary and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT  
        	h.transactional_curr_code        	Currency2,
	ot.name                		Order_Type,
	DECODE(SUBSTR(UPPER(:P_ORDER_BY),1,1),'C',CUST_ACCT.CUST_ACCOUNT_ID,NULL) CUSTOMER_ID,
	DECODE(SUBSTR(UPPER(:P_ORDER_BY),1,1),'C',PARTY.PARTY_NAME,NULL) CUSTOMER_NAME_2,
	h.order_number         		Order_Number,
	PARTY.PARTY_NAME	CUSTOMER_NAME_1,	
	CUST_ACCT.ACCOUNT_NUMBER CUSTOMER_NUMBER,
       	h.ordered_date         		Order_Date,
	ag.name                		Agreement,
	sr.name                		Sales_Person,
                  sum(nvl(l.ordered_quantity,0)*  DECODE(L.LINE_CATEGORY_CODE,'RETURN', -1*nvl(l.unit_list_price,0),nvl(l.unit_list_price,0))) Order_List,
        	sum(nvl(l.ordered_quantity,0)*  DECODE(L.LINE_CATEGORY_CODE,'RETURN',-1*nvl(l.unit_selling_price,0),nvl(l.unit_selling_price,0))) Order_Amount ,
              curr.precision     c_pre,
              l.charge_periodicity_code
			  --added
,ONT_OEXPRPRS_XMLP_PKG.C_DATA_NOT_FOUNDFORMULA(h.transactional_curr_code)  C_DATA_NOT_FOUND,
ONT_OEXPRPRS_XMLP_PKG.CF_1FORMULA(l.charge_periodicity_code)   C_CHARGE_PERIODICITY,
ONT_OEXPRPRS_XMLP_PKG.C_DISCOUNT_PRICEFormula(sum(nvl(l.ordered_quantity,0)*  DECODE(L.LINE_CATEGORY_CODE,'RETURN',-1*nvl(l.unit_selling_price,0),nvl(l.unit_selling_price,0))), sum(nvl(l.ordered_quantity,0)*  DECODE(L.LINE_CATEGORY_CODE,'RETURN', -1*nvl(l.unit_list_price,0),nvl(l.unit_list_price,0))) )   C_DISCOUNT_PRICE,
ONT_OEXPRPRS_XMLP_PKG.C_ORDER_AMOUNTFormula(sum(nvl(l.ordered_quantity,0)*  DECODE(L.LINE_CATEGORY_CODE,'RETURN',-1*nvl(l.unit_selling_price,0),nvl(l.unit_selling_price,0))),curr.precision ) C_ORDER_AMOUNT,
ONT_OEXPRPRS_XMLP_PKG.C_ORDER_LISTFORMULA(sum(nvl(l.ordered_quantity,0)*  DECODE(L.LINE_CATEGORY_CODE,'RETURN', -1*nvl(l.unit_list_price,0),nvl(l.unit_list_price,0))),curr.precision)    C_ORDER_LIST
FROM	oe_order_headers_all h,
	oe_order_lines_all l,
	OE_TRANSACTION_TYPES_TL OT,  
       	ra_salesreps sr, 
	HZ_CUST_ACCOUNTS CUST_ACCT,
	HZ_PARTIES PARTY,
	oe_agreements ag ,
                fnd_currencies    curr
WHERE  
	OT.TRANSACTION_TYPE_ID = h.order_type_id
  AND	h.sold_to_org_id = CUST_ACCT.CUST_ACCOUNT_ID
  AND 	CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
  AND  h.salesrep_id=sr.salesrep_id (+)
  AND  h.agreement_id = ag.agreement_id(+)
  AND  l.header_id=h.header_id
  AND  l.service_reference_line_id  is null
  AND NVL(h.cancelled_flag, 'N') = 'N'
  and nvl(h.org_id,0) = nvl(:p_organization_id,0)
  and nvl(l.org_id,0) = nvl(:p_organization_id,0)
  and nvl(sr.org_id,0) = nvl(:p_organization_id,0) 
  and ot.LANGUAGE = SYS_CONTEXT('USERENV','LANG') 
  and  h.transactional_curr_code = curr.currency_code
  &lp_customer_name
  &lp_customer_number
  &lp_salesrep
  &lp_agreement
  &lp_order_type
  &lp_open_orders_only
  &lp_order_num
  &lp_order_date
  &lp_order_category 
&lp_line_category
GROUP BY h.transactional_curr_code,
	 ot.name,
DECODE(SUBSTR(UPPER(:P_ORDER_BY),1,1),'C',CUST_ACCT.CUST_ACCOUNT_ID,NULL),
	DECODE(SUBSTR(UPPER(:P_ORDER_BY),1,1),'C',PARTY.PARTY_NAME,NULL),
	 h.order_number,
	PARTY.PARTY_NAME,
	CUST_ACCT.ACCOUNT_NUMBER,
       	 h.ordered_date,
	 ag.name,
	 sr.name,
                 curr.precision,
               l.charge_periodicity_code
&lp_having
ORDER BY h.transactional_curr_code,
	 ot.name
                    &lp_sort_by
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
Customer Number (From)
 
LOV Oracle
Customer Number (To)
 
LOV Oracle
Sales Person (From)
 
LOV Oracle
Salesperson (To)
 
LOV Oracle
Agreement
 
LOV Oracle
Order Type (From)
 
LOV Oracle
Order Type (To)
 
LOV Oracle
Order Number (From)
 
Number
Order Number (To)
 
Number
Order Date (From)
 
Date
Order Date (To)
 
Date
Order Amount (From)
 
Number
Order Amount (To)
 
Number
Order List (From)
 
Number
Order List (To)
 
Number
Order Category
 
LOV Oracle
Line Category
 
LOV Oracle