ONT Agreement Activity

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
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 NameSQL textValidation
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