APPS.IEX_PAYMENT_CAMP_RPT_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 137

       select name
	  into l_org_id
	  from hr_operating_units
	  where organization_id=p_org_id;

Line: 146

    select nvl(fnd_profile.value('IEX_COLLECTIONS_RATE_TYPE'),'N')
    into l_coll_rate
    from dual;

Line: 151

  select default_exchange_rate_type
  into l_coll_rate
  from ar_cmgt_setup_options;

Line: 162

       select source_code
       into l_campaign
       from ams_source_codes
       where source_code_id=p_campaign;

Line: 169

	   l_where:=l_where||' and (p.campaign_sched_id in (Select source_code_id '||
 	                     'from  ams_source_codes '||
			     'where arc_source_code_for =''CAMP'') '||
 	                     'OR p.campaign_sched_id is NULL)';

Line: 177

     select source_name
     into l_collector
     from jtf_rs_resource_extns
     where resource_id=p_collector;

Line: 182

      SELECT DISTINCT COUNT(resource_id)
         INTO l_res_cnt
         FROM JTF_RS_RESOURCE_EXTNS
        WHERE source_id IN
        ( SELECT DISTINCT b.person_id
           FROM jtf_rs_rep_managers b,
          JTF_RS_RESOURCE_EXTNS a
          WHERE b.manager_person_id = a.source_id
        AND a.resource_id           = p_collector
        );--100000937);

Line: 193

        l_res_qry  := '(select distinct resource_id '||
	'from '|| 'JTF_RS_RESOURCE_EXTNS '||
	'where source_id in (select distinct b.person_id '||
	'from jtf_rs_rep_managers b, JTF_RS_RESOURCE_EXTNS a '||
	'where b.manager_person_id = a.source_id '|| 'and a.resource_id = '||p_collector;

Line: 219

    select meaning
    into l_pay_typ_meaning
    from iex_lookups_v
    where lookup_type='IEX_PAYMENT_TYPES'
    and lookup_code=p_payment_type;

Line: 231

      l_query := 'SELECT l.meaning payment_method,p.ipayment_status payment_status,IEX_UTILITIES.REPLACE_SPECIAL_CHARS(c.source_code) campaign,p.campaign_sched_id campaign_schedule_id ,'||
      'to_char(p.creation_date,''YYYY-MM-DD'') payment_date,gl_currency_api.convert_amount(r.currency_code,'''||p_currency||''',sysdate,'''||l_coll_rate||''',r.amount) amount,'||
      'p.payment_id,d.delinquency_id,p.resource_id,IEX_UTILITIES.REPLACE_SPECIAL_CHARS(j.source_name) collector,IEX_UTILITIES.REPLACE_SPECIAL_CHARS(hca.account_number) account_number,'||
      'IEX_UTILITIES.REPLACE_SPECIAL_CHARS(s.trx_number) invoice_id,d.payment_schedule_id,s.class,'||
      /*'gl_currency_api.convert_amount(r.currency_code,'''||p_currency||''',sysdate,'''||l_coll_rate||''',s.amount_due_remaining) amount_due_remaining,'||  commented for bug 14753025
      'gl_currency_api.convert_amount(r.currency_code,'''||p_currency||''',sysdate,'''||l_coll_rate||''',s.amount_due_original) amount_due_original,'||*/
      'gl_currency_api.convert_amount(s.invoice_currency_code,'''||p_currency||''',sysdate,'''||l_coll_rate||''',s.amount_due_remaining) amount_due_remaining,'|| --added for bug 14753025
      'gl_currency_api.convert_amount(s.invoice_currency_code,'''||p_currency||''',sysdate,'''||l_coll_rate||''',s.amount_due_original) amount_due_original,'||
      'r.currency_code currency_code,l.lookup_code payment_method_lookup_code '||
      'FROM iex_delinquencies d,'|| 'iex_payments p,'||
      'ar_payment_schedules_all s,'|| 'iex_del_pay_xref xdp,'|| 'ams_source_codes c,'|| 'jtf_rs_resource_extns j,'|| 'iex_pay_receipt_xref xpr,'||'ar_cash_receipts_all r,'||
      'iex_lookups_v l,hz_cust_accounts hca,ar_system_parameters asp '||
      'WHERE xdp.payment_id = p.payment_id '|| 'AND xdp.DELINQUENCY_ID= d.DELINQUENCY_ID '|| 'AND d.payment_schedule_id = s.payment_schedule_id '||
      'AND c.source_code_id(+)   = p.campaign_sched_id '|| 'AND p.resource_id         = j.resource_id(+) '|| 'AND hca.cust_account_id=d.cust_account_id '||
      'AND p.payment_id          = xpr.payment_id '||      'AND r.cash_receipt_id     = xpr.cash_receipt_id and d.org_id=asp.org_id '||
      'AND l.lookup_type         = ''IEX_PAYMENT_TYPES'' '|| 'AND p.payment_method      = l.lookup_code '||'and s.class=''INV'' ';

Line: 276

      l_query:='SELECT IEX_UTILITIES.REPLACE_SPECIAL_CHARS(c.source_code) Campaign,IEX_UTILITIES.REPLACE_SPECIAL_CHARS(j.source_name) Collector,'||
      'SUM(gl_currency_api.convert_amount(r.currency_code,'''||p_currency||''',sysdate,'''||l_coll_rate||''',r.amount)) Total_Amount_Collected,'||
      'ROUND((SUM(gl_currency_api.convert_amount(r.currency_code,'''||p_currency||''',sysdate,'''||l_coll_rate||''',r.amount))/((to_date('''||l_to_date||
      ''',''DD-MON-RRRR'')-to_date('''||l_from_date||''',''DD-MON-RRRR''))+1))/8,2) Hourly_Average,'||l_hourly_goal||' Hourly_Goal,'||
      'ROUND(((((SUM(gl_currency_api.convert_amount(r.currency_code,'''||p_currency||''',sysdate,'''||l_coll_rate||''',r.amount))/((to_date('''||l_to_date||
      ''',''DD-MON-RRRR'')-to_date('''||l_from_date||''',''DD-MON-RRRR''))+1))/8)/'||l_hourly_goal||') *100),2) percent_hourly_avr,'||
      'ROUND((SUM(gl_currency_api.convert_amount(r.currency_code,'''||p_currency||''',sysdate,'''||l_coll_rate||''',r.amount))/((to_date('''||l_to_date||
      ''',''DD-MON-RRRR'')-to_date('''||l_from_date||''',''DD-MON-RRRR''))+1)),2) Daily_Average,'|| l_daily_goal||' Daily_Goal,'||
      'ROUND((((SUM(gl_currency_api.convert_amount(r.currency_code,'''||p_currency||''',sysdate,'''||l_coll_rate||''',r.amount))/((to_date('''||l_to_date||
      ''',''DD-MON-RRRR'')-to_date('''||l_from_date||''',''DD-MON-RRRR''))+1))/'||l_daily_goal||') *100),2) percent_daily_avr,'||
      'COUNT(DISTINCT(p.payment_id)) Payments,'||
      'ROUND(SUM(gl_currency_api.convert_amount(r.currency_code,'''||p_currency||''',sysdate,'''||l_coll_rate||''',r.amount))/COUNT(DISTINCT(p.payment_id)),2) Average_Payment,'||
      'COUNT(DISTINCT(d.transaction_id)) Invoices,'||
      'ROUND(SUM(gl_currency_api.convert_amount(r.currency_code,'''||p_currency||''',sysdate,'''||l_coll_rate||''',r.amount))/COUNT(DISTINCT(d.transaction_id)),2) Average_Invoices,'||
      'COUNT(DISTINCT(d.cust_account_id)) Accounts,'||
      'ROUND(SUM(gl_currency_api.convert_amount(r.currency_code,'''||p_currency||''',sysdate,'''||l_coll_rate||''',r.amount))/COUNT(DISTINCT(d.cust_account_id)),2) Average_Account '||
      'FROM '|| 'iex_delinquencies d,iex_payments p, ar_payment_schedules_all s , iex_del_pay_xref xdp,'||
      'ams_source_codes c,jtf_rs_resource_extns j,iex_pay_receipt_xref xpr,ar_cash_receipts_all r,'|| 'iex_lookups_v l,ar_system_parameters asp '||
      'WHERE xdp.payment_id      = p.payment_id '|| 'AND xdp.DELINQUENCY_ID      = d.DELINQUENCY_ID '|| 'AND d.payment_schedule_id   = s.payment_schedule_id '||
      'AND c.source_code_id(+)     = p.campaign_sched_id '|| 'AND p.resource_id = j.resource_id(+) '|| 'AND p.payment_id = xpr.payment_id and d.org_id = asp.org_id '||
      'AND r.cash_receipt_id       = xpr.cash_receipt_id '|| 'AND l.lookup_type           = ''IEX_PAYMENT_TYPES'' '|| 'AND p.payment_method        = l.lookup_code '||
      'AND s.class                 =''INV'' ';

Line: 340

  select trunc(sysdate)
  into l_sysdate
  from dual;