ONT Order Discount Summary

Description
Categories: BI Publisher, Sales
Application: Order Management
Source: Order Discount Summary Report (XML)
Short Name: OEXPRPRS_XML
DB package: ONT_OEXPRPRS_XMLP_PKG
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
Ledger Id
 
Number
Line Category
 
LOV Oracle
Order Category
 
LOV Oracle
Order List (To)
 
Number
Order List (From)
 
Number
Order Amount (To)
 
Number
Order Amount (From)
 
Number
Order Date (To)
 
Date
Order Date (From)
 
Date
Order Number (To)
 
Number
Order Number (From)
 
Number
Order Type (To)
 
LOV Oracle
Order Type (From)
 
LOV Oracle
Agreement
 
LOV Oracle
Salesperson (To)
 
LOV Oracle
Sales Person (From)
 
LOV Oracle
Customer Number (To)
 
LOV Oracle
Customer Number (From)
 
LOV Oracle
Customer Name (To)
 
LOV Oracle
Customer Name (From)
 
LOV Oracle
Open Orders Only
 
LOV Oracle
Sort By
 
LOV Oracle