[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select name
into l_org_id
from hr_operating_units
where organization_id=p_org_id;
select nvl(fnd_profile.value('IEX_COLLECTIONS_RATE_TYPE'),'N')
into l_coll_rate
from dual;
select default_exchange_rate_type
into l_coll_rate
from ar_cmgt_setup_options;
select source_name
into l_collector
from jtf_rs_resource_extns
where resource_id=p_collector;
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
);
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;
select meaning
into l_pay_typ_meaning
from iex_lookups_v
where lookup_type='IEX_PAYMENT_TYPES'
and lookup_code=p_payment_type;
l_query := 'SELECT l.meaning payment_method,p.ipayment_status payment_status,p.campaign_sched_id campaign_schedule_id ,'||
'IEX_UTILITIES.REPLACE_SPECIAL_CHARS(c.source_code) source_code,to_char(p.creation_date,''YYYY-MM-DD'') payment_date,'||
'gl_currency_api.convert_amount(r.currency_code,'''||p_currency||''',sysdate,''Corporate'',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,s.payment_schedule_id,s.class,'||
'gl_currency_api.convert_amount(s.invoice_currency_code,'''||p_currency||''',sysdate,''Corporate'',s.amount_due_remaining) amount_due_remaining,'||
'gl_currency_api.convert_amount(s.invoice_currency_code,'''||p_currency||''',sysdate,''Corporate'',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,'||
'hz_cust_accounts hca,'||
'jtf_rs_resource_extns j,'||
'iex_pay_receipt_xref xpr,'||
'ar_cash_receipts_all r,'||
'ar_system_parameters asp,'||
'iex_lookups_v l '||
'WHERE xdp.payment_id = p.payment_id '||
'AND xdp.DELINQUENCY_ID= d.DELINQUENCY_ID '||
'AND d.payment_schedule_id = s.payment_schedule_id '||
'AND d.cust_account_id=hca.cust_account_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 r.cash_receipt_id = xpr.cash_receipt_id '||
'AND l.lookup_type = ''IEX_PAYMENT_TYPES'' '||
'AND d.org_id = asp.org_id '||
'AND p.payment_method = l.lookup_code '||
'and s.class=''INV'' ';
l_query:='SELECT 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,ar_system_parameters asp1,'||
'ams_source_codes c,jtf_rs_resource_extns j,iex_pay_receipt_xref xpr,ar_cash_receipts_all r,'|| 'iex_lookups_v l '||
'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 d.org_id = asp1.org_id '||
'AND p.resource_id = j.resource_id(+) '|| 'AND p.payment_id = xpr.payment_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'' ';
select TRUNC(sysdate)
into l_sysdate
from dual;