ONT Agreement Activity

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