ONT Agreement Activity

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Agreement Activity Report
Application: Order Management
Source: Agreement Activity Report (XML)
Short Name: OEXOEASR_XML
DB package: ONT_OEXOEASR_XMLP_PKG
Run ONT Agreement Activity and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT  h.transactional_curr_code Currency2,
       h.conversion_rate conversion_rate,
       h.conversion_type_code conversion_type_code,
       a.name agreement_name,
       a.start_date_active start_date ,
       a.end_date_active end_date,
       sr.name salesperson,
       c.party_name customer_name,
       acct.account_number customer_number,
       h.order_number order_number,
       h.ordered_date order_date,
       a.purchase_order_num purchase_order_num,
       sum((nvl(l.ordered_quantity,0) -nvl(l.cancelled_quantity,0)) *
		nvl(l.unit_selling_price,0)) qty_ordered,
	sum(nvl(l.shipped_quantity,0)*nvl(l.unit_selling_price,0)) qty_shipped,
       SUM(nvl(trxl.quantity_invoiced,0)*nvl(trxl.unit_selling_price,0)) qty_invoiced,
	   ONT_OEXOEASR_XMLP_PKG.C_CONVERT_AMOUNTFormula(:P_USE_FUNCTIONAL_CURRENCY,sum((nvl(l.ordered_quantity,0) -nvl(l.cancelled_quantity,0)) *
		nvl(l.unit_selling_price,0)) , SUM(nvl(trxl.quantity_invoiced,0)*nvl(trxl.unit_selling_price,0)),sum(nvl(l.shipped_quantity,0)*nvl(l.unit_selling_price,0)) ,
		 h.transactional_curr_code,:rp_functional_currency, h.conversion_rate,:P_SOB_ID,h.ordered_date,h.conversion_type_code)  C_CONVERT_AMOUNT,
	ONT_OEXOEASR_XMLP_PKG.c_use_currencyformula(h.transactional_curr_code) C_USE_CURRENCY, 
	ONT_OEXOEASR_XMLP_PKG.c_data_not_foundformula(h.transactional_curr_code) C_DATA_NOT_FOUND,
	ONT_OEXOEASR_XMLP_PKG.c_qty_ordered_p c_qty_ordered,
	ONT_OEXOEASR_XMLP_PKG.c_qty_shipped_p c_qty_shipped,
	ONT_OEXOEASR_XMLP_PKG.c_qty_shipped_dsp_p c_qty_shipped_dsp,
	ONT_OEXOEASR_XMLP_PKG.c_qty_ordered_dsp_p c_qty_ordered_dsp,
	ONT_OEXOEASR_XMLP_PKG.c_qty_invoiced_dsp_p c_qty_invoiced_dsp,
	ONT_OEXOEASR_XMLP_PKG.c_qty_invoiced_p c_qty_invoiced
	--added
FROM  OE_AGREEMENTS a,
      OE_ORDER_HEADERS h,
      OE_ORDER_LINES_ALL l,
      RA_CUSTOMER_TRX_LINES_ALL trxl,
      QP_LOOKUPS lu,
      HZ_PARTIES  c,
      HZ_CUST_ACCOUNTS acct,
      JTF_RS_SALESREPS sr
WHERE 
 a.agreement_type_code = lu.lookup_code(+)
AND lu.lookup_type(+)='QP_AGREEMENT_TYPE' 
AND a.agreement_id = l.AGREEMENT_ID
AND  h.header_id = l.header_id
AND  l.line_category_code = 'ORDER'
AND  to_char(l.line_id) = trxl.interface_line_attribute6(+)
AND  trxl.interface_line_context(+) = :p_interface_line_context
AND   c.party_id = acct.party_id
AND acct.cust_account_id = h.sold_to_org_id 
AND   a.salesrep_id =sr.salesrep_id(+)
&lp_agreement_range
&lp_customer_range
&lp_agreement_type
&lp_purchase_num
&lp_order_number_range
&lp_salesperson
GROUP BY h.transactional_curr_code,
       h.conversion_rate,
       h.conversion_type_code,
       a.name,
       a.start_date_active ,
       a.end_date_active,
       sr.name,
       c.party_name,
       acct.account_number,
       h.order_number,
       h.ordered_date,
       a.purchase_order_num
ORDER BY h.transactional_curr_code,a.name,c.party_name,h.order_number
Parameter Name SQL text Validation
Operating Unit 
 
 LOV
Agreement Name (Low) 
 
 LOV Oracle
Agreement Name (High) 
 
 LOV Oracle
Customer (Low) 
 
 LOV Oracle
Customer (High) 
 
 LOV Oracle
Agreement Type 
 
 LOV Oracle
Purchase Order Number 
 
 LOV Oracle
Sales Person 
 
 LOV Oracle
Use Ledger Currency 
 
 LOV Oracle
Order Number (Low) 
 
 Char
Order Number (High) 
 
 Char
Download
 
Related Blitz Reports
AR Customers and Sites GL Account Analysis (Distributions) GL Account Analysis ONT Orders ONT Orders and Lines ONT Credit Order Detail ONT DIFOT ONT Comprehensive Order Detail AR Transactions and Lines
Blitz Report™

Blitz Report™ provides multiple benefits:

Blitz Report™