[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT cust_account_id
FROM HZ_CUST_ACCOUNTS
WHERE party_id = p_party ;
SELECT cust_account_id
FROM HZ_CUST_ACCOUNTS
WHERE party_id IN
(SELECT p_party FROM dual
UNION
SELECT ar.related_party_id
FROM ar_paying_relationships_v ar
WHERE ar.party_id = p_party
AND TRUNC(sysdate) BETWEEN
TRUNC(NVL(ar.effective_start_date,sysdate)) AND
TRUNC(NVL(ar.effective_end_date,sysdate)));
select lines.days_start,
lines.days_to,
lines.report_heading1,
lines.report_heading2,
lines.type,
lines.aging_bucket_line_id,
lines.bucket_sequence_num
from ar_aging_bucket_lines lines,
ar_aging_buckets buckets
where lines.aging_bucket_id = buckets.aging_bucket_id
and buckets.aging_bucket_id = p_bucket_id
and buckets.status = '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',
'CM') <> ps.class
and decode(upper(p_credit_option),
'AGE', '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(upper(p_credit_option),
'AGE', 'dummy',
'CM') <> ps.class
and decode(upper(p_credit_option),
'AGE', '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');
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_filter_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.status = 'OP' -- fixed a bug 5569664
AND ps.gl_date <= p_as_of_date;
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_site_use_id = p_filter_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.status = 'OP' -- fixed a bug 5569664
AND ps.gl_date <= p_as_of_date;
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,
hz_cust_accounts hzca
WHERE ps.customer_id = hzca.cust_account_id
AND hzca.party_id = p_filter_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.status = 'OP' -- fixed a bug 5569664
AND ps.gl_date <= p_as_of_date;
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,
hz_cust_accounts hzca
WHERE ps.customer_id = hzca.cust_account_id
AND hzca.party_id IN
(SELECT p_filter_id FROM dual
UNION
SELECT ar.related_party_id
FROM ar_paying_relationships_v ar
WHERE ar.party_id = p_filter_id
AND TRUNC(sysdate) BETWEEN
TRUNC(NVL(ar.effective_start_date,sysdate)) AND
TRUNC(NVL(ar.effective_end_date,sysdate)) )
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.status = 'OP' -- fixed a bug 5569664
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,
hz_cust_accounts hzca
WHERE ps.customer_id = hzca.cust_account_id
AND hzca.party_id = p_filter_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 ps.cash_receipt_id = app.cash_receipt_id
AND nvl( app.confirmed_flag, 'Y' ) = 'Y'
AND app.status in ( 'UNAPP', 'ACC' ,'OTHER ACC')
AND decode(upper(p_currency_code),
NULL, ps.invoice_currency_code,
upper(p_currency_code)) = ps.invoice_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,
hz_cust_accounts hzca
WHERE ps.customer_id = hzca.cust_account_id
AND hzca.party_id IN
(SELECT p_filter_id FROM dual
UNION
SELECT ar.related_party_id
FROM ar_paying_relationships_v ar
WHERE ar.party_id = p_filter_id
AND TRUNC(sysdate) BETWEEN
TRUNC(NVL(ar.effective_start_date,sysdate)) AND
TRUNC(NVL(ar.effective_end_date,sysdate)) )
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 ps.cash_receipt_id = app.cash_receipt_id
AND nvl( app.confirmed_flag, 'Y' ) = 'Y'
AND app.status in ( 'UNAPP', 'ACC' ,'OTHER ACC')
AND decode(upper(p_currency_code),
NULL, ps.invoice_currency_code,
upper(p_currency_code)) = ps.invoice_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_filter_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 ps.cash_receipt_id = app.cash_receipt_id
AND nvl( app.confirmed_flag, 'Y' ) = 'Y'
AND app.status in ( 'UNAPP', 'ACC' ,'OTHER ACC')
AND decode(upper(p_currency_code),
NULL, ps.invoice_currency_code,
upper(p_currency_code)) = ps.invoice_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_site_use_id = p_filter_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 decode(upper(p_currency_code),
NULL, ps.invoice_currency_code,
upper(p_currency_code)) = ps.invoice_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,
hz_cust_site_uses hzsu,
hz_cust_acct_sites hzas
WHERE ps.customer_id = hzas.cust_account_id
and hzsu.cust_acct_site_id = hzas.cust_acct_site_id
AND hzsu.site_use_id = ps.customer_site_use_id
AND ps.cash_receipt_id = app.cash_receipt_id
AND hzsu.site_use_id = p_filter_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 ps.cash_receipt_id = app.cash_receipt_id
AND nvl( app.confirmed_flag, 'Y' ) = 'Y'
AND app.status in ( 'UNAPP', 'ACC' ,'OTHER ACC')
AND decode(upper(p_currency_code),
NULL, ps.invoice_currency_code,
upper(p_currency_code)) = ps.invoice_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_filter_id
/* bug no : 1274152. Aging form did not consider the receipts done
without customer location for the calculation of receipt at risk.
NVL is added for ps.customer_site_use_id in the procedure
calc_risk_receipts to avoid null = null comparison 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, crh.acctd_amount,
crh.amount)), 0)
FROM ar_cash_receipts cr,
ar_cash_receipt_history crh,
hz_cust_accounts hzca
WHERE cr.pay_from_customer = hzca.cust_account_id
AND hzca.party_id = p_filter_id
/* bug no : 1274152. Aging form did not consider the receipts done
without customer location for the calculation of receipt at risk.
NVL is added for ps.customer_site_use_id in the procedure
calc_risk_receipts to avoid null = null comparison 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, crh.acctd_amount,
crh.amount)), 0)
FROM ar_cash_receipts cr,
ar_cash_receipt_history crh,
hz_cust_accounts hzca
WHERE cr.pay_from_customer = hzca.cust_account_id
AND hzca.party_id IN
(SELECT p_filter_id FROM dual
UNION
SELECT ar.related_party_id
FROM ar_paying_relationships_v ar
WHERE ar.party_id = p_filter_id
AND TRUNC(sysdate) BETWEEN
TRUNC(NVL(ar.effective_start_date,sysdate)) AND
TRUNC(NVL(ar.effective_end_date,sysdate)) )
/* bug no : 1274152. Aging form did not consider the receipts done
without customer location for the calculation of receipt at risk.
NVL is added for ps.customer_site_use_id in the procedure
calc_risk_receipts to avoid null = null comparison 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, crh.acctd_amount,
crh.amount)), 0)
FROM ar_cash_receipts cr,
ar_cash_receipt_history crh,
/* begin add for bug 4930373 to use AR_CASH_RECEIPTS_N2 */
hz_cust_acct_sites_all acct_site,
hz_cust_site_uses_all site_uses,
hz_cust_accounts_all cust_acct
/* end add for bug 4930373 to use AR_CASH_RECEIPTS_N2 */
WHERE cr.customer_site_use_id = p_filter_id
/* bug no : 1274152. Aging form did not consider the receipts done
without customer location for the calculation of receipt at risk.
NVL is added for ps.customer_site_use_id in the procedure
calc_risk_receipts to avoid null = null comparison fixed by rajsrini */
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')
/* begin add for bug 4930373 to use AR_CASH_RECEIPTS_N2 */
and site_uses.site_use_id = p_filter_id
AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
and cust_acct.cust_account_id = acct_site.cust_account_id
and cust_acct.party_id = cr.pay_from_customer;
SELECT NVL(SUM(decode(p_currency_code, NULL,
ROUND(ps.amount_in_dispute * nvl(ps.exchange_rate,1),l_round_decimal),
ps.amount_in_dispute)),0)
FROM ar_payment_schedules ps
WHERE ps.customer_id = p_filter_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 --Commented for bug#7044352 on 23-May-2008 by SCHEKURI
AND nvl( ps.amount_in_dispute, 0 ) <> 0
and ps.amount_due_remaining > 0 -- fixed a bug 5473635
and ps.status = 'OP' -- fixed a bug 5569664
AND nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y' ;
SELECT NVL(SUM(decode(p_currency_code, NULL,
ROUND(ps.amount_in_dispute * nvl(ps.exchange_rate,1),l_round_decimal),
ps.amount_in_dispute)),0)
FROM ar_payment_schedules ps
WHERE ps.customer_site_use_id = p_filter_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 --Commented for bug#7044352 on 23-May-2008 by SCHEKURI
AND nvl( ps.amount_in_dispute, 0 ) <> 0
--- Begin - Andre Araujo - 11/09/2004 - Performance fix we will select the aging separately
AND ps.status = 'OP'
--- End - Andre Araujo - 11/09/2004 - Performance fix we will select the aging separately
and ps.amount_due_remaining > 0 -- fixed a bug 5473635
AND nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y' ;
SELECT NVL(SUM(decode(p_currency_code, NULL,
ROUND(ps.amount_in_dispute * nvl(ps.exchange_rate,1),l_round_decimal),
ps.amount_in_dispute)),0)
FROM ar_payment_schedules ps,
hz_cust_accounts hzca
WHERE ps.customer_id = hzca.cust_Account_id
AND hzca.party_id = p_filter_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 --Commented for bug#7044352 on 23-May-2008 by SCHEKURI
AND nvl( ps.amount_in_dispute, 0 ) <> 0
and ps.amount_due_remaining > 0 -- fixed a bug 5473635
and ps.status = 'OP' -- fixed a bug 5569664
AND nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y' ;
SELECT NVL(SUM(decode(p_currency_code, NULL,
ROUND(ps.amount_in_dispute * nvl(ps.exchange_rate,1),l_round_decimal),
ps.amount_in_dispute)),0)
FROM ar_payment_schedules ps,
hz_cust_accounts hzca
WHERE ps.customer_id = hzca.cust_Account_id
AND hzca.party_id IN
(SELECT p_filter_id FROM dual
UNION
SELECT ar.related_party_id
FROM ar_paying_relationships_v ar
WHERE ar.party_id = p_filter_id
AND TRUNC(sysdate) BETWEEN
TRUNC(NVL(ar.effective_start_date,sysdate)) AND
TRUNC(NVL(ar.effective_end_date,sysdate)) )
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 --Commented for bug#7044352 on 23-May-2008 by SCHEKURI
AND nvl( ps.amount_in_dispute, 0 ) <> 0
and ps.amount_due_remaining > 0 -- fixed a bug 5473635
and ps.status = 'OP' -- fixed a bug 5569664
AND nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y' ;
SELECT
ROUND(NVL(SUM(ps.amount_adjusted *
NVL(ps.exchange_rate, 1)),0), l_round_decimal),
ROUND(NVL(SUM(ps.amount_adjusted_pending *
NVL(ps.exchange_rate, 1)),0), l_round_decimal)
FROM ar_payment_schedules ps
WHERE ps.customer_id = p_filter_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 commented for bug#7418862 by PNAVEENK on 21-OCT-2008
AND (nvl( ps.amount_adjusted_pending, 0 ) <> 0
OR
nvl( ps.amount_adjusted, 0 ) <> 0)
and ps.status = 'OP' -- fixed a bug 5569664
AND nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y';
SELECT
ROUND(NVL(SUM(ps.amount_adjusted *
NVL(ps.exchange_rate, 1)),0), l_round_decimal),
ROUND(NVL(SUM(ps.amount_adjusted_pending *
NVL(ps.exchange_rate, 1)),0), l_round_decimal)
FROM ar_payment_schedules ps,
hz_cust_accounts hzca
WHERE ps.customer_id = hzca.cust_account_id
AND hzca.party_id = p_filter_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 commented for bug#7418862 by PNAVEENK on 21-OCT-2008
AND (nvl( ps.amount_adjusted_pending, 0 ) <> 0
OR
nvl( ps.amount_adjusted, 0 ) <> 0)
and ps.status = 'OP' -- fixed a bug 5569664
AND nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y';
SELECT
ROUND(NVL(SUM(ps.amount_adjusted *
NVL(ps.exchange_rate, 1)),0), l_round_decimal),
ROUND(NVL(SUM(ps.amount_adjusted_pending *
NVL(ps.exchange_rate, 1)),0), l_round_decimal)
FROM ar_payment_schedules ps,
hz_cust_accounts hzca
WHERE ps.customer_id = hzca.cust_account_id
AND hzca.party_id IN
(SELECT p_filter_id FROM dual
UNION
SELECT ar.related_party_id
FROM ar_paying_relationships_v ar
WHERE ar.party_id = p_filter_id
AND TRUNC(sysdate) BETWEEN
TRUNC(NVL(ar.effective_start_date,sysdate)) AND
TRUNC(NVL(ar.effective_end_date,sysdate)) )
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 commented for bug#7418862 by PNAVEENK on 21-OCT-2008
AND (nvl( ps.amount_adjusted_pending, 0 ) <> 0
OR
nvl( ps.amount_adjusted, 0 ) <> 0)
and ps.status = 'OP' -- fixed a bug 5569664
AND nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y';
SELECT
ROUND(NVL(SUM(ps.amount_adjusted *
NVL(ps.exchange_rate, 1)),0), l_round_decimal),
ROUND(NVL(SUM(ps.amount_adjusted_pending *
NVL(ps.exchange_rate, 1)),0), l_round_decimal)
FROM ar_payment_schedules ps
WHERE ps.customer_site_use_id = p_filter_id
--- Begin - Andre Araujo - 11/09/2004 - Performance fix
AND ps.status = 'OP'
--- End - Andre Araujo - 11/09/2004 - Performance fix
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 commented for bug#7418862 by PNAVEENK on 21-OCT-2008
AND (nvl( ps.amount_adjusted_pending, 0 ) <> 0
OR
nvl( ps.amount_adjusted, 0 ) <> 0)
and ps.status = 'OP' -- fixed a bug 5569664
AND nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y';
SELECT
ROUND(NVL(SUM( ps.receivables_charges_charged
* NVL(ps.exchange_rate,1)),0),l_round_decimal)
FROM ar_payment_schedules ps
WHERE ps.customer_id = p_filter_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 commented for bug#7418916 by PNAVEENK on 20-OCT-2008
--- Begin - Andre Araujo - 11/09/2004 - Performance fix
AND ps.status = 'OP'
--- End - Andre Araujo - 11/09/2004 - Performance fix
AND nvl(ps.receivables_charges_charged, 0 ) <> 0 ;
SELECT
ROUND(NVL(SUM( ps.receivables_charges_charged
* NVL(ps.exchange_rate,1)),0),l_round_decimal)
FROM ar_payment_schedules ps,
hz_cust_accounts hzca
WHERE ps.customer_id = hzca.cust_account_id
--- Begin - Andre Araujo - 11/09/2004 - Performance fix
AND ps.status = 'OP'
--- End - Andre Araujo - 11/09/2004 - Performance fix
AND hzca.party_id = p_filter_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 commented for bug#7418916 by PNAVEENK on 20-OCT-2008
AND nvl(ps.receivables_charges_charged, 0 ) <> 0 ;
SELECT
ROUND(NVL(SUM( ps.receivables_charges_charged
* NVL(ps.exchange_rate,1)),0),l_round_decimal)
FROM ar_payment_schedules ps,
hz_cust_accounts hzca
WHERE ps.customer_id = hzca.cust_account_id
AND hzca.party_id IN
(SELECT p_filter_id FROM dual
UNION
SELECT ar.related_party_id
FROM ar_paying_relationships_v ar
WHERE ar.party_id = p_filter_id
AND TRUNC(sysdate) BETWEEN
TRUNC(NVL(ar.effective_start_date,sysdate)) AND
TRUNC(NVL(ar.effective_end_date,sysdate)) )
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
--- Begin - Andre Araujo - 11/09/2004 - Performance fix
AND ps.status = 'OP'
--- End - Andre Araujo - 11/09/2004 - Performance fix
-- AND ps.due_date <= p_as_of_date commented for bug#7418916 by PNAVEENK on 20-OCT-2008
AND nvl(ps.receivables_charges_charged, 0 ) <> 0 ;
SELECT
ROUND(NVL(SUM( ps.receivables_charges_charged
* NVL(ps.exchange_rate,1)),0),l_round_decimal)
FROM ar_payment_schedules ps
WHERE ps.customer_site_use_id = p_filter_id
AND decode(upper(p_currency_code),
NULL, ps.invoice_currency_code,
upper(p_currency_code)) = ps.invoice_currency_code
--- Begin - Andre Araujo - 11/09/2004 - Performance fix
AND ps.status = 'OP'
--- End - Andre Araujo - 11/09/2004 - Performance fix
-- AND ps.due_date <= p_as_of_date commented for bug#7418916 by PNAVEENK on 20-OCT-2008
AND nvl(ps.receivables_charges_charged, 0 ) <> 0 ;
v_aging_summary_select_rec Aging_Summary_Select_Rec ;
Select TRUNC(sysdate)
into l_date
from dual ;
select DISTINCT aps.customer_id
into v_billto_cust_account_id
from ar_payment_schedules aps
where aps.customer_site_use_id = p_filter_id ;
select aabl.aging_bucket_line_id, sum(acctd_amount_due_remaining)
BULK COLLECT INTO
l_collectible_bkt_id_tbl,
l_collectible_bkt_amt_tbl
from ar_aging_bucket_lines aabl,
ar_payment_schedules aps,
iex_delinquencies id
where id.payment_schedule_id = aps.payment_schedule_id
--BEGIN-FIX BUG#4356388-05/24/2005-JYPARK-amount overdue should inculde Pre-Delinquent transactions which are not past due
-- and id.status IN ('DELINQUENT', 'PRE-DELINQUENT')
and id.status IN ('DELINQUENT', 'PREDELINQUENT')
--END-FIX BUG#4356388-05/24/2005-JYPARK-amount overdue should inculde Pre-Delinquent transactions which are not past due
and trunc(sysdate)-aps.due_date >= aabl.days_start
and trunc(sysdate)-aps.due_date <= aabl.days_to
and id.cust_account_id = p_filter_id
and aabl.aging_bucket_id = p_bucket_id
--- Begin - Andre Araujo - 11/09/2004 - Performance fix
AND aps.status = 'OP'
--- End - Andre Araujo - 11/09/2004 - Performance fix
group by aabl.aging_bucket_line_id ;
SELECT nvl(SUM(total_amount), 0)
INTO l_collect_dispute_amt
FROM ra_cm_requests
WHERE customer_trx_id IN
(SELECT DISTINCT id.transaction_id
FROM ar_aging_bucket_lines aabl,
ar_payment_schedules aps,
iex_delinquencies id
WHERE id.payment_schedule_id = aps.payment_schedule_id
AND id.status IN('DELINQUENT', 'PREDELINQUENT')
AND TRUNC(sysdate) -aps.due_date >= aabl.days_start
AND TRUNC(sysdate) -aps.due_date <= aabl.days_to
AND id.cust_account_id = p_filter_id
AND aabl.aging_bucket_id = p_bucket_id
AND aps.status = 'OP'
AND aabl.aging_bucket_line_id = l_collectible_bkt_id_tbl(coll_bkt_cnt))
AND status = 'PENDING_APPROVAL';
select aabl.aging_bucket_line_id, sum(acctd_amount_due_remaining)
BULK COLLECT INTO
l_collectible_bkt_id_tbl,
l_collectible_bkt_amt_tbl
from ar_aging_bucket_lines aabl,
ar_payment_schedules aps,
iex_delinquencies id,
hz_cust_accounts hzca
where id.payment_schedule_id = aps.payment_schedule_id
--BEGIN-FIX BUG#4356388-05/24/2005-JYPARK-amount overdue should inculde Pre-Delinquent transactions which are not past due
-- and id.status IN ('DELINQUENT', 'PRE-DELINQUENT')
and id.status IN ('DELINQUENT', 'PREDELINQUENT')
--END-FIX BUG#4356388-05/24/2005-JYPARK-amount overdue should inculde Pre-Delinquent transactions which are not past due
and trunc(sysdate)-aps.due_date >= aabl.days_start
and trunc(sysdate)-aps.due_date <= aabl.days_to
and id.cust_account_id = hzca.cust_account_id
and hzca.party_id IN
(SELECT p_filter_id FROM dual
UNION
SELECT ar.related_party_id
FROM ar_paying_relationships_v ar
WHERE ar.party_id = p_filter_id
AND TRUNC(sysdate) BETWEEN
TRUNC(NVL(ar.effective_start_date,sysdate)) AND
TRUNC(NVL(ar.effective_end_date,sysdate)) )
and aabl.aging_bucket_id = p_bucket_id
--- Begin - Andre Araujo - 11/09/2004 - Performance fix
AND aps.status = 'OP'
--- End - Andre Araujo - 11/09/2004 - Performance fix
group by aabl.aging_bucket_line_id ;
SELECT nvl(SUM(total_amount), 0)
INTO l_collect_dispute_amt
FROM ra_cm_requests
WHERE customer_trx_id IN
(SELECT DISTINCT id.transaction_id
FROM ar_aging_bucket_lines aabl,
ar_payment_schedules aps,
iex_delinquencies id,
hz_cust_accounts hzca
WHERE id.payment_schedule_id = aps.payment_schedule_id
AND id.status IN('DELINQUENT', 'PREDELINQUENT')
AND TRUNC(sysdate) -aps.due_date >= aabl.days_start
AND TRUNC(sysdate) -aps.due_date <= aabl.days_to
AND id.cust_account_id = hzca.cust_account_id
AND hzca.party_id IN
(SELECT p_filter_id
FROM dual
UNION
SELECT ar.related_party_id
FROM ar_paying_relationships_v ar
WHERE ar.party_id = p_filter_id
AND TRUNC(sysdate) BETWEEN TRUNC(nvl(ar.effective_start_date, sysdate))
AND TRUNC(nvl(ar.effective_end_date, sysdate)))
AND aabl.aging_bucket_id = p_bucket_id
AND aps.status = 'OP'
AND aabl.aging_bucket_line_id = l_collectible_bkt_id_tbl(coll_bkt_cnt))
AND status = 'PENDING_APPROVAL';
select aabl.aging_bucket_line_id, sum(acctd_amount_due_remaining)
BULK COLLECT INTO
l_collectible_bkt_id_tbl,
l_collectible_bkt_amt_tbl
from ar_aging_bucket_lines aabl,
ar_payment_schedules aps,
iex_delinquencies id,
hz_cust_accounts hzca
where id.payment_schedule_id = aps.payment_schedule_id
--BEGIN-FIX BUG#4356388-05/24/2005-JYPARK-amount overdue should inculde Pre-Delinquent transactions which are not past due
-- and id.status IN ('DELINQUENT', 'PRE-DELINQUENT')
and id.status IN ('DELINQUENT', 'PREDELINQUENT')
--END-FIX BUG#4356388-05/24/2005-JYPARK-amount overdue should inculde Pre-Delinquent transactions which are not past due
and trunc(sysdate)-aps.due_date >= aabl.days_start
and trunc(sysdate)-aps.due_date <= aabl.days_to
and id.cust_account_id = hzca.cust_account_id
and hzca.party_id = p_filter_id
and aabl.aging_bucket_id = p_bucket_id
--- Begin - Andre Araujo - 11/09/2004 - Performance fix
AND aps.status = 'OP'
--- End - Andre Araujo - 11/09/2004 - Performance fix
group by aabl.aging_bucket_line_id ;
SELECT nvl(SUM(total_amount), 0)
INTO l_collect_dispute_amt
FROM ra_cm_requests
WHERE customer_trx_id IN
(SELECT DISTINCT id.transaction_id
FROM ar_aging_bucket_lines aabl,
ar_payment_schedules aps,
iex_delinquencies id,
hz_cust_accounts hzca
WHERE id.payment_schedule_id = aps.payment_schedule_id
AND id.status IN('DELINQUENT', 'PREDELINQUENT')
AND TRUNC(sysdate) -aps.due_date >= aabl.days_start
AND TRUNC(sysdate) -aps.due_date <= aabl.days_to
AND id.cust_account_id = hzca.cust_account_id
AND hzca.party_id = p_filter_id
AND aabl.aging_bucket_id = p_bucket_id
AND aps.status = 'OP'
AND aabl.aging_bucket_line_id = l_collectible_bkt_id_tbl(coll_bkt_cnt))
AND status = 'PENDING_APPROVAL';
select aabl.aging_bucket_line_id, sum(acctd_amount_due_remaining) -- added by ehuh for bill-to
BULK COLLECT INTO -- added by ehuh for bill-to
l_collectible_bkt_id_tbl, -- added by ehuh for bill-to
l_collectible_bkt_amt_tbl -- added by ehuh for bill-to
from ar_aging_bucket_lines aabl, -- added by ehuh for bill-to
ar_payment_schedules aps, -- added by ehuh for bill-to
iex_delinquencies id
where id.payment_schedule_id = aps.payment_schedule_id -- added by ehuh for bill-to
--BEGIN-FIX BUG#4356388-05/24/2005-JYPARK-amount overdue should inculde Pre-Delinquent transactions which are not past due
-- and id.status IN ('DELINQUENT', 'PRE-DELINQUENT')
and id.status IN ('DELINQUENT', 'PREDELINQUENT')
--END-FIX BUG#4356388-05/24/2005-JYPARK-amount overdue should inculde Pre-Delinquent transactions which are not past due
and trunc(sysdate)-aps.due_date >= aabl.days_start -- added by ehuh for bill-to
and trunc(sysdate)-aps.due_date <= aabl.days_to -- added by ehuh for bill-to
and aabl.aging_bucket_id = p_bucket_id -- added by ehuh for bill-to
and aps.customer_site_use_id = p_filter_id -- added by ehuh for bill-to
--- Begin - Andre Araujo - 11/09/2004 - Performance fix
AND aps.status = 'OP'
--- End - Andre Araujo - 11/09/2004 - Performance fix
group by aabl.aging_bucket_line_id ; -- added by ehuh for bill-to
SELECT nvl(SUM(total_amount), 0)
INTO l_collect_dispute_amt
FROM ra_cm_requests
WHERE customer_trx_id IN
(SELECT DISTINCT id.transaction_id
FROM ar_aging_bucket_lines aabl,
ar_payment_schedules aps,
iex_delinquencies id
WHERE id.payment_schedule_id = aps.payment_schedule_id
AND id.status IN('DELINQUENT', 'PREDELINQUENT')
AND TRUNC(sysdate) -aps.due_date >= aabl.days_start
AND TRUNC(sysdate) -aps.due_date <= aabl.days_to
AND aabl.aging_bucket_id = p_bucket_id
AND aps.customer_site_use_id = p_filter_id
AND aps.status = 'OP'
AND aabl.aging_bucket_line_id = l_collectible_bkt_id_tbl(coll_bkt_cnt))
AND status = 'PENDING_APPROVAL';
--- Begin - Andre Araujo - 11/09/2004 - Performance fix we will select the aging separately
v_days_start NUMBER;
--- End - Andre Araujo - 11/09/2004 - Performance fix we will select the aging separately
BEGIN
x_class_count := 0; -- added by ehuh for bill-to
--- Begin - Andre Araujo - 11/09/2004 - Performance fix we will select the aging separately
select days_start, days_to
into v_days_start, v_days_to
from ar_aging_bucket_lines
where aging_bucket_line_id = p_bucket_line_id;
'SELECT NVL(count(1), 0), NVL(SUM(ACCTD_AMOUNT_DUE_REMAINING), 0)
FROM ar_payment_schedules arp
WHERE (arp.customer_id = :cust_account_id
and arp.status = ''OP'')
and
(
trunc(sysdate)-trunc(arp.due_date) >= :days_start
and trunc(sysdate)-trunc(arp.due_date) <= :days_to
and arp.class = :class
)' ;
'SELECT NVL(count(1), 0), NVL(SUM(ACCTD_AMOUNT_DUE_REMAINING), 0)
FROM ar_payment_schedules arp,
ar_aging_bucket_lines aabl,
hz_cust_accounts hzca
WHERE trunc(sysdate)-trunc(arp.due_date) >= aabl.days_start
and trunc(sysdate)-trunc(arp.due_date) <= aabl.days_to
and arp.class = :class
and arp.status = ''OP''
and arp.customer_id = hzca.cust_account_id
and aabl.aging_bucket_line_id = :bucket_line_id
and hzca.party_id IN
(SELECT :party_id FROM dual
UNION
SELECT ar.related_party_id
FROM ar_paying_relationships_v ar
WHERE ar.party_id = :party_id
AND TRUNC(sysdate) BETWEEN
TRUNC(NVL(ar.effective_start_date,sysdate)) AND
TRUNC(NVL(ar.effective_end_date,sysdate)) ) ' ;
'SELECT NVL(count(1), 0), NVL(SUM(ACCTD_AMOUNT_DUE_REMAINING), 0)
FROM ar_payment_schedules arp
WHERE (arp.customer_id in (select cust_account_id from hz_cust_accounts where party_id = :party_id)
and arp.status = ''OP'')
and
(
trunc(sysdate)-trunc(arp.due_date) >= :days_start
and trunc(sysdate)-trunc(arp.due_date) <= :days_to
and arp.class = :class
)' ;
'SELECT NVL(count(1), 0), NVL(SUM(ACCTD_AMOUNT_DUE_REMAINING), 0)
FROM ar_payment_schedules arp
WHERE (arp.customer_site_use_id = :customer_site_use_id
and arp.status = ''OP'')
and
(
trunc(sysdate)-trunc(arp.due_date) >= :days_start
and trunc(sysdate)-trunc(arp.due_date) <= :days_to
and arp.class = :class
)' ;
'SELECT count(distinct ocahb.id)
FROM ar_payment_schedules arp,
ar_aging_bucket_lines aabl,
ra_customer_trx rct,
okl_cnsld_ar_strms_b ocasb,
OKL_CNSLD_AR_LINES_B ocalb,
OKL_CNSLD_AR_HDRS_B ocahb
WHERE (:l_date - arp.due_date) >= aabl.days_start
and (:l_date - arp.due_date) <= aabl.days_to
and arp.class = :l_class
and rct.customer_trx_id = arp.customer_trx_id
and ocasb.receivables_invoice_id = rct.customer_trx_id
and ocalb.id = ocasb.lln_id
and ocahb.id = ocalb.cnr_id
and rct.interface_header_attribute9 <> :l_interface_attr
and rct.interface_header_context = :l_interface_context
and arp.status = :l_status
and aabl.aging_bucket_line_id = :bucket_line_id
AND arp.customer_id = :cust_account_id' ;
'SELECT count(distinct ocahb.id)
FROM ar_payment_schedules arp,
ar_aging_bucket_lines aabl,
ra_customer_trx rct,
okl_cnsld_ar_strms_b ocasb,
OKL_CNSLD_AR_LINES_B ocalb,
OKL_CNSLD_AR_HDRS_B ocahb,
HZ_CUST_ACCOUNTS hzca
WHERE (:l_date - arp.due_date) >= aabl.days_start
and (:l_date - arp.due_date) <= aabl.days_to
and arp.class = :l_class
and rct.customer_trx_id = arp.customer_trx_id
and ocasb.receivables_invoice_id = rct.customer_trx_id
and ocalb.id = ocasb.lln_id
and ocahb.id = ocalb.cnr_id
and rct.interface_header_attribute9 <> :l_interface_attr
and rct.interface_header_context = :l_interface_context
and arp.status = :l_status
and aabl.aging_bucket_line_id = :bucket_line_id
AND arp.customer_id = hzca.cust_account_id
AND hzca.party_id IN
(SELECT :party_id FROM dual
UNION
SELECT ar.related_party_id
FROM ar_paying_relationships_v ar
WHERE ar.party_id = :party_id
AND TRUNC(sysdate) BETWEEN
TRUNC(NVL(ar.effective_start_date,sysdate)) AND
TRUNC(NVL(ar.effective_end_date,sysdate)) ) ' ;
'SELECT count(distinct ocahb.id)
FROM ar_payment_schedules arp,
ar_aging_bucket_lines aabl,
ra_customer_trx rct,
okl_cnsld_ar_strms_b ocasb,
OKL_CNSLD_AR_LINES_B ocalb,
OKL_CNSLD_AR_HDRS_B ocahb,
HZ_CUST_ACCOUNTS hzca
WHERE (:l_date - arp.due_date) >= aabl.days_start
and (:l_date - arp.due_date) <= aabl.days_to
and arp.class = :l_class
and rct.customer_trx_id = arp.customer_trx_id
and ocasb.receivables_invoice_id = rct.customer_trx_id
and ocalb.id = ocasb.lln_id
and ocahb.id = ocalb.cnr_id
and rct.interface_header_attribute9 <> :l_interface_attr
and rct.interface_header_context = :l_interface_context
and arp.status = :l_status
and aabl.aging_bucket_line_id = :bucket_line_id
AND arp.customer_id = hzca.cust_account_id
AND hzca.party_id = :party_id ' ;
'SELECT count(distinct ocahb.id)
FROM ar_payment_schedules arp,
ar_aging_bucket_lines aabl,
ra_customer_trx rct,
okl_cnsld_ar_strms_b ocasb,
OKL_CNSLD_AR_LINES_B ocalb,
OKL_CNSLD_AR_HDRS_B ocahb,
HZ_CUST_ACCOUNTS hzca
WHERE (:l_date - arp.due_date) >= aabl.days_start
and (:l_date - arp.due_date) <= aabl.days_to
and arp.class = :l_class
and rct.customer_trx_id = arp.customer_trx_id
and ocasb.receivables_invoice_id = rct.customer_trx_id
and ocalb.id = ocasb.lln_id
and ocahb.id = ocalb.cnr_id
and rct.interface_header_attribute9 <> :l_interface_attr
and rct.interface_header_context = :l_interface_context
and arp.status = :l_status
and aabl.aging_bucket_line_id = :bucket_line_id
AND arp.customer_id = hzca.cust_account_id
AND arp.customer_site_use_id = :customer_site_use_id' ;