[Home] [Help]
217: -- FND_FILE.put_line( FND_FILE.LOG,'4.7');
218: l_where:=l_where||' and l.lookup_code='''||p_payment_type||'''';
219: select meaning
220: into l_pay_typ_meaning
221: from iex_lookups_v
222: where lookup_type='IEX_PAYMENT_TYPES'
223: and lookup_code=p_payment_type;
224: else
225: l_pay_typ_meaning:=iex_utilities.get_lookup_meaning('IEX_ALL_TYPE','ALL');
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 '||
246: 'AND l.lookup_type = ''IEX_PAYMENT_TYPES'' '|| 'AND p.payment_method = l.lookup_code '||'and s.class=''INV'' ';
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'' ';