[Home] [Help]
1440: AND DECODE(UPPER(:pg_in_currency),NULL, ps.invoice_currency_code,
1441: UPPER(:pg_in_currency)) = ps.invoice_currency_code
1442: and not exists
1443: ( SELECT ''x''
1444: FROM ar_receivable_applications ra
1445: WHERE ra.cash_receipt_id = crh.cash_receipt_id
1446: AND ra.status = ''ACTIVITY''
1447: AND applied_payment_schedule_id = -2
1448: )'||pg_org_where_crh||l_where_clause;
1447: AND applied_payment_schedule_id = -2
1448: )'||pg_org_where_crh||l_where_clause;
1449:
1450: l_ra_sub_query := '
1451: select /*+ leading(ps) index(ps AR_PAYMENT_SCHEDULES_N9) index(app AR_RECEIVABLE_APPLICATIONS_N1)*/
1452: distinct ps.payment_schedule_id,
1453: ''RA'' source_type
1454: from ar_receivable_applications app,
1455: ar_payment_schedules ps '||l_from_clause||'
1450: l_ra_sub_query := '
1451: select /*+ leading(ps) index(ps AR_PAYMENT_SCHEDULES_N9) index(app AR_RECEIVABLE_APPLICATIONS_N1)*/
1452: distinct ps.payment_schedule_id,
1453: ''RA'' source_type
1454: from ar_receivable_applications app,
1455: ar_payment_schedules ps '||l_from_clause||'
1456: where app.gl_date <= :as_of_date
1457: AND app.status IN ( ''ACC'' ,
1458: ''UNAPP'',
2206: nvl(ps.exchange_rate, 1) exchange_rate,
2207: 0 cons_inv_id
2208: FROM ar_aging_payment_schedules aging,
2209: ar_payment_schedules ps,
2210: ar_receivable_applications app
2211: WHERE aging.parent_request_id = :parent_request_id
2212: AND aging.worker_id = :worker_id
2213: AND aging.source_type = ''INV''
2214: AND aging.payment_schedule_id = ps.payment_schedule_id
2443: hz_cust_site_uses site,
2444: hz_cust_acct_sites acct_site,
2445: hz_party_sites party_site,
2446: hz_locations loc,
2447: ar_receivable_applications app,
2448: gl_code_combinations c '||l_rep_spec_from_list ||'
2449: where aging.parent_request_id = :parent_request_id
2450: AND aging.worker_id = :worker_id
2451: AND aging.source_type = ''RA''
2579: ''Y'',''RISK_ELIMINATED'',
2580: ''N'',''CLEARED''),
2581: ''REVERSED'')
2582: and not exists (select ''x''
2583: from ar_receivable_applications ra
2584: where ra.cash_receipt_id = cr.cash_receipt_id
2585: and ra.status = ''ACTIVITY''
2586: and applied_payment_schedule_id = -2)
2587: '||l_rep_spec_where_cls;
2757: ae.currency_code
2758: from xla_ae_headers hdr,
2759: xla_ae_lines ae,
2760: xla_distribution_links xdl,
2761: ( select /*+ leading(ext) index(ra AR_RECEIVABLE_APPLICATIONS_N3)*/
2762: ra.event_id,
2763: ext.payment_schedule_id,
2764: ext.amt_due_remaining,
2765: 'RA_APPLIED_FROM' source_identifier
2763: ext.payment_schedule_id,
2764: ext.amt_due_remaining,
2765: 'RA_APPLIED_FROM' source_identifier
2766: from ar_aging_extract ext,
2767: ar_receivable_applications ra
2768: where ext.parent_request_id = pg_parent_request_id
2769: and ext.worker_id = pg_worker_id
2770: and ext.payment_schedule_id = ra.payment_schedule_id
2771: and ra.gl_date <= pg_in_as_of_date_low
2775: ext.amt_due_remaining
2776:
2777: UNION ALL
2778:
2779: select /*+ leading(ext) index(ra AR_RECEIVABLE_APPLICATIONS_N8)*/
2780: ra.event_id,
2781: ext.payment_schedule_id,
2782: ext.amt_due_remaining,
2783: 'RA_APPLIED_TO' source_identifier
2781: ext.payment_schedule_id,
2782: ext.amt_due_remaining,
2783: 'RA_APPLIED_TO' source_identifier
2784: from ar_aging_extract ext,
2785: ar_receivable_applications ra
2786: where ext.parent_request_id = pg_parent_request_id
2787: and ext.worker_id = pg_worker_id
2788: and ext.payment_schedule_id = ra.applied_payment_schedule_id
2789: and ra.gl_date <= pg_in_as_of_date_low