[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select lines.days_start,
lines.days_to,
lines.report_heading1,
lines.report_heading2,
lines.type
from ar_aging_bucket_lines lines,
ar_aging_buckets buckets
where lines.aging_bucket_id = buckets.aging_bucket_id
and upper(buckets.bucket_name) = upper(p_bucket_name)
and nvl(buckets.status,'A') = 'A'
order by lines.bucket_sequence_num
;
select decode(p_currency_code, NULL, ps.acctd_amount_due_remaining,
ps.amount_due_remaining),
decode(v_bucket_line_type_0,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_0,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_0,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b0,
decode(v_bucket_line_type_1,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_1,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_1,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b1,
decode(v_bucket_line_type_2,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_2,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_2,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b2,
decode(v_bucket_line_type_3,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_3,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_3,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b3,
decode(v_bucket_line_type_4,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_4,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_4,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b4,
decode(v_bucket_line_type_5,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_5,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_5,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b5,
decode(v_bucket_line_type_6,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_6,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_6,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b6
from ra_cust_trx_types,
ar_payment_schedules ps
where ps.gl_date <= p_as_of_date
and ps.cust_trx_type_id = ra_cust_trx_types.cust_trx_type_id
and ps.gl_date_closed > p_as_of_date
and ps.customer_id = p_customer_id
and decode(p_customer_site_use_id,
NULL, ps.customer_site_use_id,
p_customer_site_use_id) = ps.customer_site_use_id
and decode(upper(p_currency_code),
NULL, ps.invoice_currency_code,
upper(p_currency_code)) = ps.invoice_currency_code
and decode(upper(p_credit_option),
'AGE', 'dummy','SUMMARY','dummy',
'CM') <> ps.class
and decode(upper(p_credit_option),
'AGE', 'dummy','SUMMARY','dummy',
'PMT') <> ps.class
and decode(p_invoice_type_low,
NULL, ra_cust_trx_types.name,
p_invoice_type_low) <= ra_cust_trx_types.name
and decode(p_invoice_type_high,
NULL, ra_cust_trx_types.name,
p_invoice_type_high) >= ra_cust_trx_types.name
UNION ALL
select -sum(decode(p_currency_code, NULL, app.acctd_amount_applied_from,
app.amount_applied)),
decode(v_bucket_line_type_0,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_0,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_0,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b0,
decode(v_bucket_line_type_1,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_1,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_1,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1)))b1,
decode(v_bucket_line_type_2,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_2,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_2,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b2,
decode(v_bucket_line_type_3,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_3,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_3,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b3,
decode(v_bucket_line_type_4,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_4,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_4,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b4,
decode(v_bucket_line_type_5,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_5,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_5,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b5,
decode(v_bucket_line_type_6,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_6,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_6,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b6
from ar_payment_schedules ps,
ar_receivable_applications app
where app.gl_date+0 <= p_as_of_date
and ps.cash_receipt_id+0 = app.cash_receipt_id
and app.status in ( 'ACC', 'UNAPP', 'UNID','OTHER ACC')
and nvl(app.confirmed_flag, 'Y') = 'Y'
and ps.gl_date_closed > p_as_of_date
and (app.reversal_gl_date > p_as_of_date OR
app.reversal_gl_date is null )
and nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y'
and ps.customer_id = p_customer_id
and decode(p_customer_site_use_id,
NULL, nvl(ps.customer_site_use_id,-10),
p_customer_site_use_id) = nvl(ps.customer_site_use_id,-10)
and decode(upper(p_currency_code),
NULL, ps.invoice_currency_code,
upper(p_currency_code)) = ps.invoice_currency_code
and decode(p_credit_option,'AGE','AGE','dummy') = 'AGE' /*4436914*/
and decode(upper(p_credit_option),
'AGE', 'dummy','SUMMARY','dummy',
'CM') <> ps.class
and decode(upper(p_credit_option),
'AGE', 'dummy','SUMMARY','dummy',
'PMT') <> ps.class
group by ps.due_date,
ps.amount_due_original,
ps.amount_adjusted,
ps.amount_applied,
ps.amount_credited,
ps.gl_date,
ps.amount_in_dispute,
ps.amount_adjusted_pending,
ps.invoice_currency_code,
ps.exchange_rate,
ps.class,
decode( app.status, 'UNID', 'UNID', 'UNAPP')
UNION ALL /*Bug 4436914 excluded APP and adjustments after as of date*/
select nvl(sum(decode(p_currency_code, NULL, app.amount_applied,
app.acctd_amount_applied_from)),0) amount_due_remaining,
decode(v_bucket_line_type_0,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_0,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_0,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b0,
decode(v_bucket_line_type_1,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_1,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_1,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1)))b1,
decode(v_bucket_line_type_2,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_2,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_2,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b2,
decode(v_bucket_line_type_3,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_3,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_3,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b3,
decode(v_bucket_line_type_4,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_4,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_4,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b4,
decode(v_bucket_line_type_5,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_5,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_5,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b5,
decode(v_bucket_line_type_6,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_6,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_6,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b6
from ar_payment_schedules ps,
ar_receivable_applications app
where app.gl_date+0 > p_as_of_date
and ps.cash_receipt_id = app.cash_receipt_id /*4436914*/
and (ps.payment_schedule_id = app.applied_payment_schedule_id
OR
ps.payment_schedule_id = app.payment_schedule_id)
and app.status in ( 'APP')
and nvl(app.confirmed_flag, 'Y') = 'Y'
and ps.gl_date_closed > p_as_of_date
and (app.reversal_gl_date > p_as_of_date OR
app.reversal_gl_date is null )
and nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y'
and ps.customer_id = p_customer_id
and decode(p_customer_site_use_id,
NULL, nvl(ps.customer_site_use_id,-10),
p_customer_site_use_id) = nvl(ps.customer_site_use_id,-10)
and decode(upper(p_currency_code),
NULL, ps.invoice_currency_code,
upper(p_currency_code)) = ps.invoice_currency_code
and decode(upper(p_credit_option),
'AGE', 'dummy','SUMMARY','dummy',
'CM') <> ps.class
and decode(upper(p_credit_option),
'AGE', 'dummy','SUMMARY','dummy',
'PMT') <> ps.class
group by ps.due_date,
ps.amount_due_original,
ps.amount_adjusted,
ps.amount_applied,
ps.amount_credited,
ps.gl_date,
ps.amount_in_dispute,
ps.amount_adjusted_pending,
ps.invoice_currency_code,
ps.exchange_rate,
ps.class,
ps.payment_schedule_id
UNION ALL
SELECT -sum(nvl(adj.amount,0)) amount_due_remaining,1,0,0,0,0,0,0
FROM ar_adjustments adj,
ar_payment_schedules_all ps
WHERE adj.GL_date > p_as_of_date
AND ps.payment_schedule_id = adj.payment_schedule_id
AND adj.status = 'A'
AND ps.gl_date_closed > p_as_of_date
AND nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y'
AND ps.customer_id = p_customer_id
AND decode(p_customer_site_use_id,
NULL, nvl(ps.customer_site_use_id,-10),
p_customer_site_use_id) = nvl(ps.customer_site_use_id,-10)
AND decode(upper(p_currency_code),NULL, ps.invoice_currency_code,upper(p_currency_code))= ps.invoice_currency_code
AND decode(upper(p_credit_option),'AGE', 'dummy','SUMMARY','dummy','CM') <> ps.class
AND decode(upper(p_credit_option),'AGE', 'dummy','SUMMARY','dummy','PMT') <> ps.class
group by ps.due_date,
ps.amount_due_original,
ps.amount_adjusted,
ps.amount_applied,
ps.amount_credited,
ps.gl_date,
ps.amount_in_dispute,
ps.amount_adjusted_pending,
ps.invoice_currency_code,
ps.exchange_rate,
ps.class;
select decode(p_currency_code, NULL, ps.acctd_amount_due_remaining,
ps.amount_due_remaining),
decode(v_bucket_line_type_0,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_0,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_0,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b0,
decode(v_bucket_line_type_1,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_1,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_1,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b1,
decode(v_bucket_line_type_2,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_2,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_2,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b2,
decode(v_bucket_line_type_3,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_3,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_3,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b3,
decode(v_bucket_line_type_4,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_4,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_4,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b4,
decode(v_bucket_line_type_5,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_5,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_5,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b5,
decode(v_bucket_line_type_6,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_6,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_6,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b6
from ar_payment_schedules ps,
ra_cust_trx_types rctt
where ps.gl_date <= p_as_of_date
and ps.gl_date_closed > p_as_of_date
and ps.cust_trx_type_id = rctt.cust_trx_type_id
and ps.customer_id = p_customer_id
and ps.customer_site_use_id = p_customer_site_use_id
and decode(upper(p_currency_code),
NULL, ps.invoice_currency_code,
upper(p_currency_code)) = ps.invoice_currency_code
and decode(upper(p_credit_option),
'AGE', 'dummy','SUMMARY','dummy',
'CM') <> ps.class
and decode(upper(p_credit_option),
'AGE', 'dummy', 'SUMMARY','dummy',
'PMT') <> ps.class
UNION ALL
select -sum(decode(p_currency_code, NULL, app.acctd_amount_applied_from,
app.amount_applied)),
decode(v_bucket_line_type_0,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_0,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_0,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b0,
decode(v_bucket_line_type_1,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_1,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_1,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1)))b1,
decode(v_bucket_line_type_2,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_2,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_2,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b2,
decode(v_bucket_line_type_3,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_3,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_3,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b3,
decode(v_bucket_line_type_4,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_4,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_4,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b4,
decode(v_bucket_line_type_5,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_5,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_5,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b5,
decode(v_bucket_line_type_6,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_6,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_6,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b6
from ar_payment_schedules ps,
ar_receivable_applications app
where app.gl_date+0 <= p_as_of_date
and ps.cash_receipt_id+0 = app.cash_receipt_id
and app.status in ( 'ACC', 'UNAPP', 'UNID','OTHER ACC')
and nvl(app.confirmed_flag, 'Y') = 'Y'
and ps.gl_date_closed > p_as_of_date
and (app.reversal_gl_date > p_as_of_date OR
app.reversal_gl_date is null )
and nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y'
and ps.customer_id = p_customer_id
and ps.customer_site_use_id = p_customer_site_use_id
and decode(upper(p_currency_code),
NULL, ps.invoice_currency_code,
upper(p_currency_code)) = ps.invoice_currency_code
and decode(p_credit_option,'AGE','AGE','dummy') = 'AGE' /*4436914*/
and decode(upper(p_credit_option),
'AGE', 'dummy','SUMMARY','dummy',
'CM') <> ps.class
and decode(upper(p_credit_option),
'AGE', 'dummy','SUMMARY','dummy',
'PMT') <> ps.class
group by ps.due_date,
ps.amount_due_original,
ps.amount_adjusted,
ps.amount_applied,
ps.amount_credited,
ps.gl_date,
ps.amount_in_dispute,
ps.amount_adjusted_pending,
ps.invoice_currency_code,
ps.exchange_rate,
ps.class,
decode( app.status, 'UNID', 'UNID', 'UNAPP')
UNION ALL /*Bug 4436914 excluded APP and adjustments after as of date*/
select nvl(sum(decode(p_currency_code, NULL, app.amount_applied,
app.acctd_amount_applied_from)),0) amount_due_remaining,
decode(v_bucket_line_type_0,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_0,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_0,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b0,
decode(v_bucket_line_type_1,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_1,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_1,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1)))b1,
decode(v_bucket_line_type_2,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_2,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_2,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b2,
decode(v_bucket_line_type_3,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_3,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_3,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b3,
decode(v_bucket_line_type_4,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_4,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_4,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b4,
decode(v_bucket_line_type_5,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_5,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_5,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b5,
decode(v_bucket_line_type_6,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_6,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_6,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b6
from ar_payment_schedules ps,
ar_receivable_applications app
where app.gl_date+0 > p_as_of_date
and ps.cash_receipt_id = app.cash_receipt_id /*4436914*/
and (ps.payment_schedule_id = app.applied_payment_schedule_id
OR
ps.payment_schedule_id = app.payment_schedule_id)
and app.status in ( 'APP')
and nvl(app.confirmed_flag, 'Y') = 'Y'
and ps.gl_date_closed > p_as_of_date
and (app.reversal_gl_date > p_as_of_date OR
app.reversal_gl_date is null )
and nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y'
and ps.customer_id = p_customer_id
and ps.customer_site_use_id = p_customer_site_use_id
and decode(upper(p_currency_code),
NULL, ps.invoice_currency_code,
upper(p_currency_code)) = ps.invoice_currency_code
and decode(upper(p_credit_option),
'AGE', 'dummy','SUMMARY','dummy',
'CM') <> ps.class
and decode(upper(p_credit_option),
'AGE', 'dummy','SUMMARY','dummy',
'PMT') <> ps.class
group by ps.due_date,
ps.amount_due_original,
ps.amount_adjusted,
ps.amount_applied,
ps.amount_credited,
ps.gl_date,
ps.amount_in_dispute,
ps.amount_adjusted_pending,
ps.invoice_currency_code,
ps.exchange_rate,
ps.class,
ps.payment_schedule_id
UNION ALL
SELECT -sum(nvl(adj.amount,0)) amount_due_remaining,1,0,0,0,0,0,0
FROM ar_adjustments adj,
ar_payment_schedules_all ps
WHERE adj.GL_date > p_as_of_date
AND ps.payment_schedule_id = adj.payment_schedule_id
AND adj.status = 'A'
AND ps.gl_date_closed > p_as_of_date
AND nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y'
AND ps.customer_id = p_customer_id
AND ps.customer_site_use_id = p_customer_site_use_id
AND decode(upper(p_currency_code),NULL, ps.invoice_currency_code,upper(p_currency_code))= ps.invoice_currency_code
AND decode(upper(p_credit_option),'AGE', 'dummy','SUMMARY','dummy','CM') <> ps.class
AND decode(upper(p_credit_option),'AGE', 'dummy','SUMMARY','dummy','PMT') <> ps.class
group by ps.due_date,
ps.amount_due_original,
ps.amount_adjusted,
ps.amount_applied,
ps.amount_credited,
ps.gl_date,
ps.amount_in_dispute,
ps.amount_adjusted_pending,
ps.invoice_currency_code,
ps.exchange_rate,
ps.class;
select decode(p_currency_code, NULL, ps.acctd_amount_due_remaining,
ps.amount_due_remaining),
decode(v_bucket_line_type_0,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_0,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_0,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b0,
decode(v_bucket_line_type_1,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_1,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_1,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b1,
decode(v_bucket_line_type_2,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_2,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_2,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b2,
decode(v_bucket_line_type_3,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_3,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_3,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b3,
decode(v_bucket_line_type_4,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_4,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_4,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b4,
decode(v_bucket_line_type_5,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_5,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_5,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b5,
decode(v_bucket_line_type_6,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_6,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_6,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b6
from ra_cust_trx_types,
ar_payment_schedules ps
where ps.gl_date <= p_as_of_date
and ps.cust_trx_type_id = ra_cust_trx_types.cust_trx_type_id
and ps.gl_date_closed > p_as_of_date
and ps.customer_id = p_customer_id
and ps.customer_site_use_id = p_customer_site_use_id
and decode(upper(p_currency_code),
NULL, ps.invoice_currency_code,
upper(p_currency_code)) = ps.invoice_currency_code
and decode(upper(p_credit_option),
'AGE', 'dummy', 'SUMMARY','dummy',
'CM') <> ps.class
and decode(upper(p_credit_option),
'AGE', 'dummy', 'SUMMARY','dummy',
'PMT') <> ps.class
and decode(p_invoice_type_low,
NULL, ra_cust_trx_types.name,
p_invoice_type_low) <= ra_cust_trx_types.name
and decode(p_invoice_type_high,
NULL, ra_cust_trx_types.name,
p_invoice_type_high) >= ra_cust_trx_types.name
UNION ALL
select -sum(decode(p_currency_code, NULL, app.acctd_amount_applied_from,
app.amount_applied)),
decode(v_bucket_line_type_0,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_0,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_0,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b0,
decode(v_bucket_line_type_1,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_1,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_1,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1)))b1,
decode(v_bucket_line_type_2,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_2,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_2,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b2,
decode(v_bucket_line_type_3,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_3,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_3,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b3,
decode(v_bucket_line_type_4,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_4,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_4,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b4,
decode(v_bucket_line_type_5,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_5,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_5,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b5,
decode(v_bucket_line_type_6,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_6,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_6,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b6
from ar_payment_schedules ps,
ar_receivable_applications app
where app.gl_date+0 <= p_as_of_date
and ps.cash_receipt_id+0 = app.cash_receipt_id
and app.status in ( 'ACC', 'UNAPP', 'UNID','OTHER ACC')
and nvl(app.confirmed_flag, 'Y') = 'Y'
and ps.gl_date_closed > p_as_of_date
and (app.reversal_gl_date > p_as_of_date OR
app.reversal_gl_date is null )
and nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y'
and ps.customer_id = p_customer_id
and ps.customer_site_use_id = p_customer_site_use_id
and decode(upper(p_currency_code),
NULL, ps.invoice_currency_code,
upper(p_currency_code)) = ps.invoice_currency_code
and decode(p_credit_option,'AGE','AGE','dummy') = 'AGE' /*4436914*/
and decode(upper(p_credit_option),
'AGE', 'dummy', 'SUMMARY','dummy',
'CM') <> ps.class
and decode(upper(p_credit_option),
'AGE', 'dummy', 'SUMMARY','dummy',
'PMT') <> ps.class
group by ps.due_date,
ps.amount_due_original,
ps.amount_adjusted,
ps.amount_applied,
ps.amount_credited,
ps.gl_date,
ps.amount_in_dispute,
ps.amount_adjusted_pending,
ps.invoice_currency_code,
ps.exchange_rate,
ps.class,
decode( app.status, 'UNID', 'UNID', 'UNAPP')
UNION ALL /*Bug 4436914 excluded APP and adjustments after as of date*/
select nvl(sum(decode(p_currency_code, NULL, app.amount_applied,
app.acctd_amount_applied_from)),0) amount_due_remaining,
decode(v_bucket_line_type_0,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_0,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_0,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b0,
decode(v_bucket_line_type_1,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_1,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_1,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1)))b1,
decode(v_bucket_line_type_2,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_2,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_2,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b2,
decode(v_bucket_line_type_3,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_3,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_3,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b3,
decode(v_bucket_line_type_4,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_4,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_4,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b4,
decode(v_bucket_line_type_5,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_5,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_5,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b5,
decode(v_bucket_line_type_6,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_6,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_6,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b6
from ar_payment_schedules ps,
ar_receivable_applications app
where app.gl_date+0 > p_as_of_date
and ps.cash_receipt_id = app.cash_receipt_id /*4436914*/
and (ps.payment_schedule_id = app.applied_payment_schedule_id
OR
ps.payment_schedule_id = app.payment_schedule_id)
and app.status in ( 'APP')
and nvl(app.confirmed_flag, 'Y') = 'Y'
and ps.gl_date_closed > p_as_of_date
and (app.reversal_gl_date > p_as_of_date OR
app.reversal_gl_date is null )
and nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y'
and ps.customer_id = p_customer_id
and ps.customer_site_use_id = p_customer_site_use_id
and decode(upper(p_currency_code),
NULL, ps.invoice_currency_code,
upper(p_currency_code)) = ps.invoice_currency_code
and decode(upper(p_credit_option),
'AGE', 'dummy','SUMMARY','dummy',
'CM') <> ps.class
and decode(upper(p_credit_option),
'AGE', 'dummy','SUMMARY','dummy',
'PMT') <> ps.class
group by ps.due_date,
ps.amount_due_original,
ps.amount_adjusted,
ps.amount_applied,
ps.amount_credited,
ps.gl_date,
ps.amount_in_dispute,
ps.amount_adjusted_pending,
ps.invoice_currency_code,
ps.exchange_rate,
ps.class,
ps.payment_schedule_id
UNION ALL
SELECT -sum(nvl(adj.amount,0)) amount_due_remaining,1,0,0,0,0,0,0
FROM ar_adjustments adj,
ar_payment_schedules_all ps
WHERE adj.GL_date > p_as_of_date
AND ps.payment_schedule_id = adj.payment_schedule_id
AND adj.status = 'A'
AND ps.gl_date_closed > p_as_of_date
AND nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y'
AND ps.customer_id = p_customer_id
AND ps.customer_site_use_id = p_customer_site_use_id
AND decode(upper(p_currency_code),NULL, ps.invoice_currency_code,upper(p_currency_code))= ps.invoice_currency_code
AND decode(upper(p_credit_option),'AGE', 'dummy','SUMMARY','dummy','CM') <> ps.class
AND decode(upper(p_credit_option),'AGE', 'dummy','SUMMARY','dummy','PMT') <> ps.class
group by ps.due_date,
ps.amount_due_original,
ps.amount_adjusted,
ps.amount_applied,
ps.amount_credited,
ps.gl_date,
ps.amount_in_dispute,
ps.amount_adjusted_pending,
ps.invoice_currency_code,
ps.exchange_rate,
ps.class;
select decode(p_currency_code, NULL, ps.acctd_amount_due_remaining,
ps.amount_due_remaining),
decode(v_bucket_line_type_0,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_0,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_0,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b0,
decode(v_bucket_line_type_1,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_1,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_1,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b1,
decode(v_bucket_line_type_2,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_2,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_2,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b2,
decode(v_bucket_line_type_3,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_3,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_3,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b3,
decode(v_bucket_line_type_4,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_4,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_4,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b4,
decode(v_bucket_line_type_5,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_5,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_5,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b5,
decode(v_bucket_line_type_6,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_6,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_6,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b6
from ar_payment_schedules ps,
ra_cust_trx_types rctt
where ps.gl_date <= p_as_of_date
and ps.gl_date_closed > p_as_of_date
and ps.cust_trx_type_id = rctt.cust_trx_type_id
and ps.customer_id = p_customer_id
and decode(upper(p_currency_code),
NULL, ps.invoice_currency_code,
upper(p_currency_code)) = ps.invoice_currency_code
and decode(upper(p_credit_option),
'AGE', 'dummy', 'SUMMARY','dummy',
'CM') <> ps.class
and decode(upper(p_credit_option),
'AGE', 'dummy', 'SUMMARY','dummy',
'PMT') <> ps.class
UNION ALL
select -sum(decode(p_currency_code, NULL, app.acctd_amount_applied_from,
app.amount_applied)),
decode(v_bucket_line_type_0,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_0,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_0,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b0,
decode(v_bucket_line_type_1,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_1,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_1,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1)))b1,
decode(v_bucket_line_type_2,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_2,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_2,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b2,
decode(v_bucket_line_type_3,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_3,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_3,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b3,
decode(v_bucket_line_type_4,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_4,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_4,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b4,
decode(v_bucket_line_type_5,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_5,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_5,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b5,
decode(v_bucket_line_type_6,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_6,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_6,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b6
from ar_payment_schedules ps,
ar_receivable_applications app
where app.gl_date+0 <= p_as_of_date
and ps.cash_receipt_id+0 = app.cash_receipt_id
and app.status in ( 'ACC', 'UNAPP', 'UNID','OTHER ACC')
and nvl(app.confirmed_flag, 'Y') = 'Y'
and ps.gl_date_closed > p_as_of_date
and (app.reversal_gl_date > p_as_of_date OR
app.reversal_gl_date is null )
and nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y'
and ps.customer_id = p_customer_id
and decode(upper(p_currency_code),
NULL, ps.invoice_currency_code,
upper(p_currency_code)) = ps.invoice_currency_code
and decode(p_credit_option,'AGE','AGE','dummy') = 'AGE' /*4436914*/
and decode(upper(p_credit_option),
'AGE', 'dummy', 'SUMMARY','dummy',
'CM') <> ps.class
and decode(upper(p_credit_option),
'AGE', 'dummy', 'SUMMARY','dummy',
'PMT') <> ps.class
group by ps.due_date,
ps.amount_due_original,
ps.amount_adjusted,
ps.amount_applied,
ps.amount_credited,
ps.gl_date,
ps.amount_in_dispute,
ps.amount_adjusted_pending,
ps.invoice_currency_code,
ps.exchange_rate,
ps.class,
decode( app.status, 'UNID', 'UNID', 'UNAPP')
UNION ALL /*Bug 4436914 excluded APP and adjustments after as of date*/
select nvl(sum(decode(p_currency_code, NULL, app.amount_applied,
app.acctd_amount_applied_from)),0) amount_due_remaining,
decode(v_bucket_line_type_0,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_0,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_0,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b0,
decode(v_bucket_line_type_1,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_1,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_1,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1)))b1,
decode(v_bucket_line_type_2,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_2,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_2,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b2,
decode(v_bucket_line_type_3,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_3,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_3,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b3,
decode(v_bucket_line_type_4,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_4,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_4,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b4,
decode(v_bucket_line_type_5,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_5,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_5,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b5,
decode(v_bucket_line_type_6,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_6,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_6,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b6
from ar_payment_schedules ps,
ar_receivable_applications app
where app.gl_date+0 > p_as_of_date
and ps.cash_receipt_id = app.cash_receipt_id /*4436914*/
and (ps.payment_schedule_id = app.applied_payment_schedule_id
OR
ps.payment_schedule_id = app.payment_schedule_id)
and app.status in ( 'APP')
and nvl(app.confirmed_flag, 'Y') = 'Y'
and ps.gl_date_closed > p_as_of_date
and (app.reversal_gl_date > p_as_of_date OR
app.reversal_gl_date is null )
and nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y'
and ps.customer_id = p_customer_id
and decode(upper(p_currency_code),
NULL, ps.invoice_currency_code,
upper(p_currency_code)) = ps.invoice_currency_code
and decode(upper(p_credit_option),
'AGE', 'dummy','SUMMARY','dummy',
'CM') <> ps.class
and decode(upper(p_credit_option),
'AGE', 'dummy','SUMMARY','dummy',
'PMT') <> ps.class
group by ps.due_date,
ps.amount_due_original,
ps.amount_adjusted,
ps.amount_applied,
ps.amount_credited,
ps.gl_date,
ps.amount_in_dispute,
ps.amount_adjusted_pending,
ps.invoice_currency_code,
ps.exchange_rate,
ps.class,
ps.payment_schedule_id
UNION ALL
SELECT -sum(nvl(adj.amount,0)) amount_due_remaining,1,0,0,0,0,0,0
FROM ar_adjustments adj,
ar_payment_schedules_all ps
WHERE adj.GL_date > p_as_of_date
AND ps.payment_schedule_id = adj.payment_schedule_id
AND adj.status = 'A'
AND ps.gl_date_closed > p_as_of_date
AND nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y'
AND ps.customer_id = p_customer_id
AND decode(upper(p_currency_code),NULL, ps.invoice_currency_code,upper(p_currency_code))= ps.invoice_currency_code
AND decode(upper(p_credit_option),'AGE', 'dummy','SUMMARY','dummy','CM') <> ps.class
AND decode(upper(p_credit_option),'AGE', 'dummy','SUMMARY','dummy','PMT') <> ps.class
group by ps.due_date,
ps.amount_due_original,
ps.amount_adjusted,
ps.amount_applied,
ps.amount_credited,
ps.gl_date,
ps.amount_in_dispute,
ps.amount_adjusted_pending,
ps.invoice_currency_code,
ps.exchange_rate,
ps.class;
select decode(p_currency_code, NULL, ps.acctd_amount_due_remaining,
ps.amount_due_remaining) amount_due_remaining,
decode(v_bucket_line_type_0,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_0,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_0,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b0,
decode(v_bucket_line_type_1,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_1,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_1,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b1,
decode(v_bucket_line_type_2,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_2,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_2,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b2,
decode(v_bucket_line_type_3,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_3,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_3,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b3,
decode(v_bucket_line_type_4,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_4,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_4,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b4,
decode(v_bucket_line_type_5,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_5,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_5,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b5,
decode(v_bucket_line_type_6,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_6,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_6,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b6
from ra_cust_trx_types,
ar_payment_schedules ps
where ps.gl_date <= p_as_of_date
and ps.cust_trx_type_id = ra_cust_trx_types.cust_trx_type_id
and ps.gl_date_closed > p_as_of_date
and ps.customer_id = p_customer_id
and decode(upper(p_currency_code),
NULL, ps.invoice_currency_code,
upper(p_currency_code)) = ps.invoice_currency_code
and decode(upper(p_credit_option),
'AGE', 'dummy', 'SUMMARY','dummy',
'CM') <> ps.class
and decode(upper(p_credit_option),
'AGE', 'dummy', 'SUMMARY','dummy',
'PMT') <> ps.class
and decode(p_invoice_type_low,
NULL, ra_cust_trx_types.name,
p_invoice_type_low) <= ra_cust_trx_types.name
and decode(p_invoice_type_high,
NULL, ra_cust_trx_types.name,
p_invoice_type_high) >= ra_cust_trx_types.name
UNION ALL
select nvl(-sum(decode(p_currency_code, NULL, app.amount_applied,
app.acctd_amount_applied_from)),0) amount_due_remaining,
decode(v_bucket_line_type_0,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_0,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_0,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b0,
decode(v_bucket_line_type_1,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_1,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_1,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1)))b1,
decode(v_bucket_line_type_2,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_2,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_2,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b2,
decode(v_bucket_line_type_3,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_3,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_3,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b3,
decode(v_bucket_line_type_4,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_4,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_4,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b4,
decode(v_bucket_line_type_5,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_5,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_5,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b5,
decode(v_bucket_line_type_6,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_6,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_6,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b6
from ar_payment_schedules ps,
ar_receivable_applications app
where app.gl_date+0 <= p_as_of_date
and ps.cash_receipt_id+0 = app.cash_receipt_id
and app.status in ( 'ACC', 'UNAPP', 'UNID','OTHER ACC')
and nvl(app.confirmed_flag, 'Y') = 'Y'
and ps.gl_date_closed > p_as_of_date
and (app.reversal_gl_date > p_as_of_date OR
app.reversal_gl_date is null )
and nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y'
and ps.customer_id = p_customer_id
and decode(upper(p_currency_code),
NULL, ps.invoice_currency_code,
upper(p_currency_code)) = ps.invoice_currency_code
and decode(p_credit_option,'AGE','AGE','dummy') = 'AGE' /*4436914*/
and decode(upper(p_credit_option),
'AGE', 'dummy', 'SUMMARY','dummy',
'CM') <> ps.class
and decode(upper(p_credit_option),
'AGE', 'dummy', 'SUMMARY','dummy',
'PMT') <> ps.class
group by ps.due_date,
ps.amount_due_original,
ps.amount_adjusted,
ps.amount_applied,
ps.amount_credited,
ps.gl_date,
ps.amount_in_dispute,
ps.amount_adjusted_pending,
ps.invoice_currency_code,
ps.exchange_rate,
ps.class,
decode( app.status, 'UNID', 'UNID', 'UNAPP')
UNION ALL /*Bug 4436914 included APP*/
select nvl(sum(decode(p_currency_code, NULL, app.amount_applied,
app.acctd_amount_applied_from)),0) amount_due_remaining,
decode(v_bucket_line_type_0,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_0,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_0,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b0,
decode(v_bucket_line_type_1,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_1,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_1,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1)))b1,
decode(v_bucket_line_type_2,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_2,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_2,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b2,
decode(v_bucket_line_type_3,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_3,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_3,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b3,
decode(v_bucket_line_type_4,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_4,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_4,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b4,
decode(v_bucket_line_type_5,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_5,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_5,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b5,
decode(v_bucket_line_type_6,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_6,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_6,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b6
from ar_payment_schedules ps,
ar_receivable_applications app
where app.gl_date+0 > p_as_of_date
and ps.cash_receipt_id = app.cash_receipt_id /*4436914*/
and (ps.payment_schedule_id = app.applied_payment_schedule_id
OR
ps.payment_schedule_id = app.payment_schedule_id)
and app.status in ( 'APP')
and nvl(app.confirmed_flag, 'Y') = 'Y'
and ps.gl_date_closed > p_as_of_date
and (app.reversal_gl_date > p_as_of_date OR
app.reversal_gl_date is null )
and nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y'
and ps.customer_id = p_customer_id
and decode(upper(p_currency_code),
NULL, ps.invoice_currency_code,
upper(p_currency_code)) = ps.invoice_currency_code
and decode(upper(p_credit_option),
'AGE', 'dummy','SUMMARY','dummy',
'CM') <> ps.class
and decode(upper(p_credit_option),
'AGE', 'dummy','SUMMARY','dummy',
'PMT') <> ps.class
group by ps.due_date,
ps.amount_due_original,
ps.amount_adjusted,
ps.amount_applied,
ps.amount_credited,
ps.gl_date,
ps.amount_in_dispute,
ps.amount_adjusted_pending,
ps.invoice_currency_code,
ps.exchange_rate,
ps.class,
ps.payment_schedule_id
UNION ALL
SELECT -sum(nvl(adj.amount,0)) amount_due_remaining,1,0,0,0,0,0,0
FROM ar_adjustments adj,
ar_payment_schedules_all ps
WHERE adj.GL_date > p_as_of_date
AND ps.payment_schedule_id = adj.payment_schedule_id
AND adj.status = 'A'
AND ps.gl_date_closed > p_as_of_date
AND nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y'
AND ps.customer_id = p_customer_id
AND decode(upper(p_currency_code),NULL, ps.invoice_currency_code,upper(p_currency_code))= ps.invoice_currency_code
AND decode(upper(p_credit_option),'AGE', 'dummy','SUMMARY','dummy','CM') <> ps.class
AND decode(upper(p_credit_option),'AGE', 'dummy','SUMMARY','dummy','PMT') <> ps.class
group by ps.due_date,
ps.amount_due_original,
ps.amount_adjusted,
ps.amount_applied,
ps.amount_credited,
ps.gl_date,
ps.amount_in_dispute,
ps.amount_adjusted_pending,
ps.invoice_currency_code,
ps.exchange_rate,
ps.class;
SELECT NVL( SUM( DECODE(p_currency_code, NULL,
ps.acctd_amount_due_remaining,
ps.amount_due_remaining)), 0)
FROM ar_payment_schedules ps,
ra_cust_trx_line_gl_dist gld
WHERE ps.customer_id = p_customer_id
AND decode(p_customer_site_use_id,
NULL, ps.customer_site_use_id,
p_customer_site_use_id) = ps.customer_site_use_id
AND decode(upper(p_currency_code),
NULL, ps.invoice_currency_code,
upper(p_currency_code)) = ps.invoice_currency_code
AND ps.customer_trx_id = gld.customer_trx_id
AND gld.account_class = 'REC'
AND gld.latest_rec_flag = 'Y'
AND ps.class||'' = 'CM'
AND ps.gl_date <= p_as_of_date;
SELECT NVL(SUM( DECODE(app.status,'UNAPP',
DECODE(p_currency_code, NULL,
-app.acctd_amount_applied_from,
-app.amount_applied), 0) ), 0),
NVL(SUM( DECODE(app.status,'ACC',
DECODE(p_currency_code, NULL,
-app.acctd_amount_applied_from,
-app.amount_applied), 0) ), 0),
NVL(SUM( DECODE(app.status,'OTHER ACC',
DECODE(app.applied_payment_schedule_id, -4,
DECODE(p_currency_code, NULL,
-app.acctd_amount_applied_from,
-app.amount_applied),0), 0) ), 0),
NVL(SUM( DECODE(app.status,'OTHER ACC',
DECODE(app.applied_payment_schedule_id, -7,
DECODE(p_currency_code, NULL,
-app.acctd_amount_applied_from,
-app.amount_applied),0), 0) ), 0)
FROM ar_receivable_applications app,
ar_payment_schedules ps
WHERE ps.customer_id = p_customer_id
AND ps.cash_receipt_id = app.cash_receipt_id
AND nvl( app.confirmed_flag, 'Y' ) = 'Y'
AND app.status in ( 'UNAPP', 'ACC' ,'OTHER ACC')
AND app.gl_date <= p_as_of_date;
SELECT NVL(SUM( DECODE(app.status,'UNAPP',
DECODE(p_currency_code, NULL,
-app.acctd_amount_applied_from,
-app.amount_applied), 0) ), 0),
NVL(SUM( DECODE(app.status,'ACC',
DECODE(p_currency_code, NULL,
-app.acctd_amount_applied_from,
-app.amount_applied), 0) ), 0),
NVL(SUM( DECODE(app.status,'OTHER ACC',
DECODE(app.applied_payment_schedule_id, -4,
DECODE(p_currency_code, NULL,
-app.acctd_amount_applied_from,
-app.amount_applied),0), 0) ), 0),
NVL(SUM( DECODE(app.status,'OTHER ACC',
DECODE(app.applied_payment_schedule_id, -7,
DECODE(p_currency_code, NULL,
-app.acctd_amount_applied_from,
-app.amount_applied),0), 0) ), 0)
FROM ar_receivable_applications app,
ar_payment_schedules ps
WHERE ps.customer_id = p_customer_id
AND ps.customer_site_use_id = p_customer_site_use_id
AND ps.cash_receipt_id = app.cash_receipt_id
AND nvl( app.confirmed_flag, 'Y' ) = 'Y'
AND app.status in ( 'UNAPP', 'ACC' ,'OTHER ACC')
AND app.gl_date <= p_as_of_date;
SELECT NVL(SUM( DECODE(app.status,'UNAPP',
DECODE(p_currency_code, NULL,
-app.acctd_amount_applied_from,
-app.amount_applied), 0) ), 0),
NVL(SUM( DECODE(app.status,'ACC',
DECODE(p_currency_code, NULL,
-app.acctd_amount_applied_from,
-app.amount_applied), 0) ), 0),
NVL(SUM( DECODE(app.status,'OTHER ACC',
DECODE(app.applied_payment_schedule_id, -4,
DECODE(p_currency_code, NULL,
-app.acctd_amount_applied_from,
-app.amount_applied),0), 0) ), 0),
NVL(SUM( DECODE(app.status,'OTHER ACC',
DECODE(app.applied_payment_schedule_id, -7,
DECODE(p_currency_code, NULL,
-app.acctd_amount_applied_from,
-app.amount_applied),0), 0) ), 0)
FROM ar_receivable_applications app,
ar_payment_schedules ps
WHERE ps.customer_id = p_customer_id
AND ps.cash_receipt_id = app.cash_receipt_id
AND nvl( app.confirmed_flag, 'Y' ) = 'Y'
AND app.status in ( 'UNAPP', 'ACC' ,'OTHER ACC')
AND ps.invoice_currency_code = p_currency_code
AND app.gl_date <= p_as_of_date;
SELECT NVL(SUM( DECODE(app.status,'UNAPP',
DECODE(p_currency_code, NULL,
-app.acctd_amount_applied_from,
-app.amount_applied), 0) ), 0),
NVL(SUM( DECODE(app.status,'ACC',
DECODE(p_currency_code, NULL,
-app.acctd_amount_applied_from,
-app.amount_applied), 0) ), 0),
NVL(SUM( DECODE(app.status,'OTHER ACC',
DECODE(app.applied_payment_schedule_id, -4,
DECODE(p_currency_code, NULL,
-app.acctd_amount_applied_from,
-app.amount_applied),0), 0) ), 0),
NVL(SUM( DECODE(app.status,'OTHER ACC',
DECODE(app.applied_payment_schedule_id, -7,
DECODE(p_currency_code, NULL,
-app.acctd_amount_applied_from,
-app.amount_applied),0), 0) ), 0)
FROM ar_receivable_applications app,
ar_payment_schedules ps
WHERE ps.customer_id = p_customer_id
AND ps.customer_site_use_id = p_customer_site_use_id
AND ps.cash_receipt_id = app.cash_receipt_id
AND nvl( app.confirmed_flag, 'Y' ) = 'Y'
AND app.status in ( 'UNAPP', 'ACC' ,'OTHER ACC')
AND ps.invoice_currency_code = p_currency_code
AND app.gl_date <= p_as_of_date;
SELECT NVL( SUM( DECODE(p_currency_code, NULL, crh.acctd_amount,
crh.amount)), 0)
FROM ar_cash_receipts cr,
ar_cash_receipt_history crh
WHERE cr.pay_from_customer = p_customer_id
/* bug no : 1274152. Aging form did not consider the receipts done without custo
mer location for the calculation of receipt at risk. NVL is added for ps.custome
r_site_use_id in the procedure calc_risk_receipts to avoid null = null compariso
n
fixed by rajsrini */
AND decode(p_customer_site_use_id,
NULL, nvl(cr.customer_site_use_id,0),
p_customer_site_use_id) = nvl(cr.customer_site_use_id,0)
AND cr.currency_code= nvl(p_currency_code,cr.currency_code)
AND cr.reversal_date is null
AND nvl(cr.confirmed_flag,'Y') = 'Y'
AND cr.cash_receipt_id = crh.cash_receipt_id
AND crh.current_record_flag||'' = 'Y'
AND crh.gl_date <= p_as_of_date
AND crh.status NOT IN ( DECODE ( crh.factor_flag,'Y',
'RISK_ELIMINATED','N', 'CLEARED'), 'REVERSED' )
/* 06-AUG-2000 J Rautiainen BR Implementation
* Short term debt applications are not considered as receipts at risk */
and not exists (select 'X'
from ar_receivable_applications rap
where rap.cash_receipt_id = cr.cash_receipt_id
and rap.applied_payment_schedule_id = -2
and rap.display = 'Y');
SELECT NVL(SUM(decode(p_currency_code,NULL,ps.amount_in_dispute * nvl(ps.exchange_rate,1), ps.amount_in_dispute)),0)
FROM ar_payment_schedules ps
WHERE ps.customer_id = p_customer_id
AND decode(p_customer_site_use_id,
NULL, ps.customer_site_use_id,
p_customer_site_use_id) = ps.customer_site_use_id
AND decode(upper(p_currency_code),
NULL, ps.invoice_currency_code,
upper(p_currency_code)) = ps.invoice_currency_code
--Bug-1304510:Changed ps.due_date to ps.dispute_date.
AND ps.dispute_date <= p_as_of_date
AND nvl( ps.amount_in_dispute, 0 ) <> 0
AND nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y';
SELECT NVL( SUM( ps.amount_adjusted_pending ), 0)
FROM ar_payment_schedules ps
WHERE ps.customer_id = p_customer_id
AND decode(p_customer_site_use_id,
NULL, ps.customer_site_use_id,
p_customer_site_use_id) = ps.customer_site_use_id
AND decode(upper(p_currency_code),
NULL, ps.invoice_currency_code,
upper(p_currency_code)) = ps.invoice_currency_code
AND ps.due_date <= p_as_of_date
AND nvl( ps.amount_adjusted_pending, 0 ) <> 0
AND nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y';