[Home] [Help]
199: FND_FILE.put_line( FND_FILE.LOG,'4.7');
200: l_where:=l_where||' and l.lookup_code='''||p_payment_type||'''';
201: select meaning
202: into l_pay_typ_meaning
203: from iex_lookups_v
204: where lookup_type='IEX_PAYMENT_TYPES'
205: and lookup_code=p_payment_type;
206: else
207: l_pay_typ_meaning:=iex_utilities.get_lookup_meaning('IEX_ALL_TYPE','ALL');
226: 'jtf_rs_resource_extns j,'||
227: 'iex_pay_receipt_xref xpr,'||
228: 'ar_cash_receipts_all r,'||
229: 'ar_system_parameters asp,'||
230: 'iex_lookups_v l '||
231: 'WHERE xdp.payment_id = p.payment_id '||
232: 'AND xdp.DELINQUENCY_ID= d.DELINQUENCY_ID '||
233: 'AND d.payment_schedule_id = s.payment_schedule_id '||
234: 'AND d.cust_account_id=hca.cust_account_id '||
284: '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,'||
285: 'COUNT(DISTINCT(d.cust_account_id)) Accounts,'||
286: '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 '||
287: 'FROM '|| 'iex_delinquencies d,iex_payments p, ar_payment_schedules_all s , iex_del_pay_xref xdp,ar_system_parameters asp1,'||
288: 'ams_source_codes c,jtf_rs_resource_extns j,iex_pay_receipt_xref xpr,ar_cash_receipts_all r,'|| 'iex_lookups_v l '||
289: 'WHERE xdp.payment_id = p.payment_id '|| 'AND xdp.DELINQUENCY_ID = d.DELINQUENCY_ID '||
290: '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 '||
291: 'AND p.resource_id = j.resource_id(+) '|| 'AND p.payment_id = xpr.payment_id '|| 'AND r.cash_receipt_id = xpr.cash_receipt_id '||
292: 'AND l.lookup_type = ''IEX_PAYMENT_TYPES'' '|| 'AND p.payment_method= l.lookup_code '|| 'AND s.class =''INV'' ';