[Home] [Help]
175: IF p_collector IS NOT NULL THEN
176: -- FND_FILE.put_line( FND_FILE.LOG,'4.5');
177: select source_name
178: into l_collector
179: from jtf_rs_resource_extns
180: where resource_id=p_collector;
181: IF p_report_level='GROUP' THEN
182: SELECT DISTINCT COUNT(resource_id)
183: INTO l_res_cnt
180: where resource_id=p_collector;
181: IF p_report_level='GROUP' THEN
182: SELECT DISTINCT COUNT(resource_id)
183: INTO l_res_cnt
184: FROM JTF_RS_RESOURCE_EXTNS
185: WHERE source_id IN
186: ( SELECT DISTINCT b.person_id
187: FROM jtf_rs_rep_managers b,
188: JTF_RS_RESOURCE_EXTNS a
184: FROM JTF_RS_RESOURCE_EXTNS
185: WHERE source_id IN
186: ( SELECT DISTINCT b.person_id
187: FROM jtf_rs_rep_managers b,
188: JTF_RS_RESOURCE_EXTNS a
189: WHERE b.manager_person_id = a.source_id
190: AND a.resource_id = p_collector
191: );--100000937);
192: IF l_res_cnt <> 0 THEN
190: AND a.resource_id = p_collector
191: );--100000937);
192: IF l_res_cnt <> 0 THEN
193: l_res_qry := '(select distinct resource_id '||
194: 'from '|| 'JTF_RS_RESOURCE_EXTNS '||
195: 'where source_id in (select distinct b.person_id '||
196: 'from jtf_rs_rep_managers b, JTF_RS_RESOURCE_EXTNS a '||
197: 'where b.manager_person_id = a.source_id '|| 'and a.resource_id = '||p_collector;
198: l_where :=l_where||' and p.resource_id in '|| l_res_qry ;
192: IF l_res_cnt <> 0 THEN
193: l_res_qry := '(select distinct resource_id '||
194: 'from '|| 'JTF_RS_RESOURCE_EXTNS '||
195: 'where source_id in (select distinct b.person_id '||
196: 'from jtf_rs_rep_managers b, JTF_RS_RESOURCE_EXTNS a '||
197: 'where b.manager_person_id = a.source_id '|| 'and a.resource_id = '||p_collector;
198: l_where :=l_where||' and p.resource_id in '|| l_res_qry ;
199: l_where :=l_where||'))';
200: ELSE
237: '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
238: 'gl_currency_api.convert_amount(s.invoice_currency_code,'''||p_currency||''',sysdate,'''||l_coll_rate||''',s.amount_due_original) amount_due_original,'||
239: 'r.currency_code currency_code,l.lookup_code payment_method_lookup_code '||
240: 'FROM iex_delinquencies d,'|| 'iex_payments p,'||
241: '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,'||
242: 'iex_lookups_v l,hz_cust_accounts hca,ar_system_parameters asp '||
243: 'WHERE xdp.payment_id = p.payment_id '|| 'AND xdp.DELINQUENCY_ID= d.DELINQUENCY_ID '|| 'AND d.payment_schedule_id = s.payment_schedule_id '||
244: '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 '||
245: 'AND p.payment_id = xpr.payment_id '|| 'AND r.cash_receipt_id = xpr.cash_receipt_id and d.org_id=asp.org_id '||
289: '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,'||
290: 'COUNT(DISTINCT(d.cust_account_id)) Accounts,'||
291: '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 '||
292: 'FROM '|| 'iex_delinquencies d,iex_payments p, ar_payment_schedules_all s , iex_del_pay_xref xdp,'||
293: '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 '||
294: 'WHERE xdp.payment_id = p.payment_id '|| 'AND xdp.DELINQUENCY_ID = d.DELINQUENCY_ID '|| 'AND d.payment_schedule_id = s.payment_schedule_id '||
295: '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 '||
296: 'AND r.cash_receipt_id = xpr.cash_receipt_id '|| 'AND l.lookup_type = ''IEX_PAYMENT_TYPES'' '|| 'AND p.payment_method = l.lookup_code '||
297: 'AND s.class =''INV'' ';