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

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