[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT decode(p_currency_code,
NULL , NULL ,
nvl(sum(decode(ps.class, 'INV' , ps.amount_due_original, 0)),0)
),
decode(p_currency_code,
NULL , NULL ,
nvl(sum(decode(ps.class, 'DM' , ps.amount_due_original, 0)),0)
),
decode(p_currency_code,
NULL , NULL ,
nvl(sum(decode(ps.class, 'CB' , ps.amount_due_original, 0)),0)
),
decode(p_currency_code,
NULL , NULL ,
nvl(sum(decode(ps.class, 'DEP' , ps.amount_due_original, 0)),0)
),
decode(p_currency_code,
NULL , NULL ,
nvl(sum(decode(ps.class, 'GUAR' , ps.amount_due_original, 0)),0)
),
decode(p_currency_code,
NULL , NULL ,
nvl(sum(decode(ps.class, 'CM' , ps.amount_due_original, 0)),0)
),
/* 18-MAY-2000 J Rautiainen BR Implementation */
decode(p_currency_code,
NULL , NULL ,
nvl(sum(decode(ps.class, 'BR' , ps.amount_due_original, 0)),0)
),
decode(p_currency_code,
NULL , NULL ,
nvl(sum(decode(ps.class, 'INV' , ps.amount_due_remaining, 0)),0)
),
decode(p_currency_code,
NULL , NULL ,
nvl(sum(decode(ps.class, 'DM' , ps.amount_due_remaining, 0)),0)
),
decode(p_currency_code,
NULL , NULL ,
nvl(sum(decode(ps.class, 'CB' , ps.amount_due_remaining, 0)),0)
),
decode(p_currency_code,
NULL , NULL ,
nvl(sum(decode(ps.class, 'DEP' , ps.amount_due_remaining, 0)),0)
),
decode(p_currency_code,
NULL , NULL ,
nvl(sum(decode(ps.class, 'GUAR' , ps.amount_due_remaining, 0)),0)
),
decode(p_currency_code,
NULL , NULL ,
nvl(sum(decode(ps.class, 'CM' , ps.amount_due_remaining, 0)),0)
),
/* 18-MAY-2000 J Rautiainen BR Implementation */
decode(p_currency_code,
NULL , NULL ,
nvl(sum(decode(ps.class, 'BR' , ps.amount_due_remaining, 0)),0)
),
nvl(sum(decode(ps.class,
'INV' , arpcurr.functional_amount( ps.amount_due_original,
p_func_curr,
nvl(p_exc_rate,ps.exchange_rate),
p_precision,
p_min_acc_unit
),
0)
), 0),
nvl(sum(decode(ps.class,
'DM' , arpcurr.functional_amount( ps.amount_due_original,
p_func_curr,
nvl(p_exc_rate,ps.exchange_rate),
p_precision,
p_min_acc_unit
),
0)
), 0),
nvl(sum(decode(ps.class,
'CB' , arpcurr.functional_amount( ps.amount_due_original,
p_func_curr,
nvl(p_exc_rate, ps.exchange_rate),
p_precision,
p_min_acc_unit ), 0)), 0),
nvl(sum(decode(ps.class,
'DEP' , arpcurr.functional_amount( ps.amount_due_original,
p_func_curr,
nvl(p_exc_rate, ps.exchange_rate),
p_precision,
p_min_acc_unit ), 0)), 0),
nvl(sum(decode(ps.class,
'GUAR' , arpcurr.functional_amount( ps.amount_due_original,
p_func_curr,
nvl(p_exc_rate, ps.exchange_rate),
p_precision,
p_min_acc_unit ), 0)), 0),
nvl(sum(decode(ps.class,
'CM' , arpcurr.functional_amount( ps.amount_due_original,
p_func_curr,
nvl(p_exc_rate, ps.exchange_rate),
p_precision,
p_min_acc_unit ), 0)), 0),
/* 18-MAY-2000 J Rautiainen BR Implementation */
nvl(sum(decode(ps.class,
'BR' , arpcurr.functional_amount( ps.amount_due_original,
p_func_curr,
nvl(p_exc_rate, ps.exchange_rate),
p_precision,
p_min_acc_unit ), 0)), 0),
nvl(sum(decode(ps.class,
'INV' , decode(p_exc_rate,
NULL, ps.acctd_amount_due_remaining,
arpcurr.functional_amount( ps.amount_due_remaining,
p_func_curr,
p_exc_rate,
p_precision,
p_min_acc_unit )) ,0)), 0),
nvl(sum(decode(ps.class,
'DM' , decode(p_exc_rate,
NULL, ps.acctd_amount_due_remaining,
arpcurr.functional_amount( ps.amount_due_remaining,
p_func_curr,
p_exc_rate,
p_precision,
p_min_acc_unit ) ), 0)), 0),
nvl(sum(decode(ps.class,
'CB' , decode(p_exc_rate,
NULL, ps.acctd_amount_due_remaining,
arpcurr.functional_amount( ps.amount_due_remaining,
p_func_curr,
p_exc_rate,
p_precision,
p_min_acc_unit )), 0)), 0),
nvl(sum(decode(ps.class,
'DEP' , decode(p_exc_rate,
NULL, ps.acctd_amount_due_remaining,
arpcurr.functional_amount( ps.amount_due_remaining,
p_func_curr,
p_exc_rate,
p_precision,
p_min_acc_unit )), 0)), 0),
nvl(sum(decode(ps.class,
'GUAR' , decode(p_exc_rate,
NULL, ps.acctd_amount_due_remaining,
arpcurr.functional_amount( ps.amount_due_remaining,
p_func_curr,
p_exc_rate,
p_precision,
p_min_acc_unit )), 0)), 0),
nvl(sum(decode(ps.class,
'CM' , decode(p_exc_rate,
NULL, ps.acctd_amount_due_remaining,
arpcurr.functional_amount( ps.amount_due_remaining,
p_func_curr,
p_exc_rate,
p_precision,
p_min_acc_unit )), 0)), 0),
/* 18-MAY-2000 J Rautiainen BR Implementation */
nvl(sum(decode(ps.class,
'BR' , decode(p_exc_rate,
NULL, ps.acctd_amount_due_remaining,
arpcurr.functional_amount( ps.amount_due_remaining,
p_func_curr,
p_exc_rate,
p_precision,
p_min_acc_unit )), 0)), 0),
nvl(sum(decode(ps.class,
'INV' , 1, 0)),0),
nvl(sum(decode(ps.class,
'DM' , 1, 0)),0),
nvl(sum(decode(ps.class,
'CB' , 1, 0)),0),
nvl(sum(decode(ps.class,
'DEP' , 1, 0)),0),
nvl(sum(decode(ps.class,
'GUAR' , 1, 0)),0),
nvl(sum(decode(ps.class,
'CM' , 1, 0)),0),
/* 18-MAY-2000 J Rautiainen BR Implementation */
nvl(sum(decode(ps.class,
'BR' , 1, 0)),0)
into p_sum_inv_ori_amt,
p_sum_dm_ori_amt,
p_sum_cb_ori_amt,
p_sum_dep_ori_amt,
p_sum_guar_ori_amt,
p_sum_cm_ori_amt,
/* 18-MAY-2000 J Rautiainen BR Implementation */
p_sum_br_ori_amt,
p_sum_inv_rem_amt,
p_sum_dm_rem_amt,
p_sum_cb_rem_amt,
p_sum_dep_rem_amt,
p_sum_guar_rem_amt,
p_sum_cm_rem_amt,
/* 18-MAY-2000 J Rautiainen BR Implementation */
p_sum_br_rem_amt,
p_sum_inv_func_ori_amt,
p_sum_dm_func_ori_amt,
p_sum_cb_func_ori_amt,
p_sum_dep_func_ori_amt,
p_sum_guar_func_ori_amt,
p_sum_cm_func_ori_amt,
/* 18-MAY-2000 J Rautiainen BR Implementation */
p_sum_br_func_ori_amt,
p_sum_inv_func_rem_amt,
p_sum_dm_func_rem_amt,
p_sum_cb_func_rem_amt,
p_sum_dep_func_rem_amt,
p_sum_guar_func_rem_amt,
p_sum_cm_func_rem_amt,
/* 18-MAY-2000 J Rautiainen BR Implementation */
p_sum_br_func_rem_amt,
p_inv_count,
p_dm_count,
p_cb_count,
p_dep_count,
p_guar_count,
p_cm_count,
/* 18-MAY-2000 J Rautiainen BR Implementation */
p_br_count
from ar_payment_schedules ps
where ps.customer_id = p_customer_id /* bug1963032 */
and nvl(ps.customer_site_use_id, -10) = nvl(p_site_use_id, nvl(ps.customer_site_use_id, -10) )
and ps.gl_date between p_start_date and p_end_date
and ps.invoice_currency_code = nvl(p_currency_code,ps.invoice_currency_code)
and nvl(ps.receipt_confirmed_flag,'Y') = 'Y'
and ps.cash_receipt_id is NULL
and ps.status = nvl(p_status, ps.status);
select decode(p_currency_code,
NULL , NULL ,
nvl(sum(decode(ps.class, 'PMT' , ps.amount_due_original, 0)),0)
), /* Sum of Original Amount */
decode(p_currency_code,
NULL , NULL ,
nvl(sum(decode(ps.class, 'PMT' , ps.amount_due_remaining, 0)),0)
), /* Sum of Amount Due Remaining */
nvl(sum(decode(ps.class,
'PMT', arpcurr.functional_amount( ps.amount_due_original,
p_func_curr,
nvl(p_exc_rate, ps.exchange_rate),
p_precision,
p_min_acc_unit
),
0)), 0), /* Sum of Functional Original Amount */
nvl(sum(decode(ps.class,
'PMT', decode(p_exc_rate,
NULL, ps.acctd_amount_due_remaining,
arpcurr.functional_amount( ps.amount_due_remaining,
p_func_curr,
p_exc_rate,
p_precision,
p_min_acc_unit
)
), 0)
), 0), /* Sum of Functional Amount Due Remaining */
nvl(sum(decode(ps.class,
'PMT' , 1, 0
)
),0), /* Count of Receipts */
NULL,
NULL,
0,
0,
0
into p_sum_pmt_ori_amt,
p_sum_pmt_rem_amt,
p_sum_pmt_func_ori_amt,
p_sum_pmt_func_rem_amt,
p_pmt_count,
p_sum_risk_ori_amt,
p_sum_risk_rem_amt,
p_sum_risk_func_ori_amt,
p_sum_risk_func_rem_amt,
p_risk_count
from ar_cash_receipts cr,
ar_payment_schedules ps
where ps.customer_id = p_customer_id /* Bug 1963032 */
and ps.cash_receipt_id = cr.cash_receipt_id
and nvl(ps.customer_site_use_id, -10) = nvl(p_site_use_id, nvl(ps.customer_site_use_id, -10) )
and ps.gl_date between p_start_date and p_end_date
and ps.invoice_currency_code = nvl(p_currency_code,ps.invoice_currency_code)
and (nvl(cr.reversal_category, cr.status||'X') <> cr.status OR
(nvl(cr.reversal_category, cr.status||'X') = cr.status AND
'Y' = (SELECT 'Y'
FROM ar_payment_schedules PS_DM,
ra_cust_trx_types CTT_DM,
ra_customer_trx CT_DM,
ra_cust_trx_line_gl_dist DM_GLD
WHERE PS_DM.reversed_cash_receipt_id = cr.cash_receipt_id
AND PS_DM.class = 'DM'
AND PS_DM.cust_trx_type_id = CTT_DM.cust_trx_type_id
AND PS_DM.customer_trx_id = CT_DM.customer_trx_id
AND DM_GLD.customer_trx_id = PS_DM.customer_trx_id
AND DM_GLD.account_class = 'REC'
AND DM_GLD.latest_rec_flag = 'Y')))
and nvl(ps.receipt_confirmed_flag,'Y') = 'Y'
and ps.status = nvl(p_status, ps.status);
select decode(p_currency_code,
NULL , NULL ,
nvl(sum(decode(ps.class, 'PMT' , ps.amount_due_original, 0)),0)
), /* Sum of Original Amount */
decode(p_currency_code,
NULL , NULL ,
nvl(sum(decode(ps.class, 'PMT' , ps.amount_due_remaining, 0)),0)
), /* Sum of Amount Due Remaining */
nvl(sum(decode(ps.class,
'PMT', arpcurr.functional_amount( ps.amount_due_original,
p_func_curr,
nvl(p_exc_rate, ps.exchange_rate),
p_precision,
p_min_acc_unit
),
0)), 0), /* Sum of Functional Original Amount */
nvl(sum(decode(ps.class,
'PMT', decode(p_exc_rate,
NULL, ps.acctd_amount_due_remaining,
arpcurr.functional_amount( ps.amount_due_remaining,
p_func_curr,
p_exc_rate,
p_precision,
p_min_acc_unit
)
), 0)
), 0), /* Sum of Functional Amount Due Remaining */
nvl(sum(decode(ps.class,
'PMT' , 1, 0
)
),0) /* Count of Receipts */
into p_sum_risk_ori_amt,
p_sum_risk_rem_amt,
p_sum_risk_func_ori_amt,
p_sum_risk_func_rem_amt,
p_risk_count
from ar_cash_receipts cr,
ar_payment_schedules ps,
ar_cash_receipt_history crh
where ps.customer_id = p_customer_id /* Bug 1963032 */
and ps.cash_receipt_id = cr.cash_receipt_id
and nvl(ps.customer_site_use_id, -10) = nvl(p_site_use_id, nvl(ps.customer_site_use_id, -10) )
and ps.gl_date between p_start_date and p_end_date
and ps.invoice_currency_code = nvl(p_currency_code,ps.invoice_currency_code)
and nvl(cr.reversal_category, cr.status||'X') <> cr.status
and nvl(ps.receipt_confirmed_flag,'Y') = 'Y'
and ps.status = nvl(p_status, ps.status)
and cr.cash_receipt_id = crh.cash_receipt_id
and crh.current_record_flag||'' = 'Y'
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(sign(ra.apply_date - ps.due_date),1, 1,0)),0),
nvl(sum(decode(sign(ra.apply_date - ps.due_date),1, 0,1)),0),
decode(p_currency_code,
NULL , NULL ,
nvl(sum(decode(sign(ra.apply_date - ps.due_date),1, ra.amount_applied,0)),0)),
decode(p_currency_code,
NULL , NULL ,
nvl(sum(decode(sign(ra.apply_date - ps.due_date),1, 0,ra.amount_applied)),0)),
nvl(sum(decode(sign(ra.apply_date - ps.due_date),1, decode(p_exc_rate,
NULL, ra.acctd_amount_applied_from,
arpcurr.functional_amount(ra.amount_applied,
p_func_curr,
p_exc_rate,
p_precision,
p_min_acc_unit
)),0
)),0),
nvl(sum(decode(sign(ra.apply_date - ps.due_date),1, 0, decode(p_exc_rate,
NULL, ra.acctd_amount_applied_from,
arpcurr.functional_amount(ra.amount_applied,
p_func_curr,
p_exc_rate,
p_precision,
p_min_acc_unit
))
)),0)
into p_payments_late_count,
p_payments_ontime_count,
p_payments_late_amount,
p_payments_ontime_amount,
p_payments_late_func_amt,
p_payments_ontime_func_amt
from ar_receivable_applications ra,
ar_payment_schedules ps
where ra.applied_payment_schedule_id = ps.payment_schedule_id
and ps.customer_id = p_customer_id /* bug1963032 */
and nvl(ps.customer_site_use_id, -10) = nvl(p_site_use_id, nvl(ps.customer_site_use_id, -10) )
AND ra.apply_date between p_start_date and p_end_date
and ra.status = 'APP'
and ra.display = 'Y'
AND ps.invoice_currency_code = nvl(p_currency_code, ps.invoice_currency_code)
AND nvl(ps.receipt_confirmed_flag,'Y') = 'Y'
AND ps.status = nvl(p_status, ps.status);
SELECT decode(p_currency_code,
NULL , NULL ,
nvl(sum(cr.amount),0)
),
nvl(sum(arpcurr.functional_amount( cr.amount,
p_func_curr,
nvl(p_exc_rate,ps.exchange_rate),
p_precision,
p_min_acc_unit
)
),0),
count(cr.amount)
INTO p_nsf_stop_amount,
p_nsf_stop_func_amt,
p_nsf_stop_count
FROM ar_cash_receipts cr,
ar_payment_schedules ps
WHERE ps.gl_date between p_start_date and p_end_date
AND ps.cash_receipt_id = cr.cash_receipt_id
AND cr.reversal_category in ('NSF','STOP')
AND cr.pay_from_customer = p_customer_id /* bug1963032 */
and nvl(cr.customer_site_use_id, -10) = nvl(p_site_use_id, nvl(cr.customer_site_use_id, -10) )
AND ps.invoice_currency_code = nvl(p_currency_code, ps.invoice_currency_code)
AND nvl(ps.receipt_confirmed_flag,'Y') = 'Y'
AND ps.status = nvl(p_status, ps.status);
select decode(p_currency_code,
NULL , NULL , nvl(sum(a.amount),0)
),
nvl(sum( decode(p_exc_rate,
NULL, a.acctd_amount,
arpcurr.functional_amount(a.amount,
p_func_curr,
p_exc_rate,
p_precision,
p_min_acc_unit
)
)),0),
count(a.amount)
into p_adjustment_amount,
p_adjustment_func_amt,
p_adjustment_count
from ar_adjustments a,
ar_receivables_trx rt,
ar_payment_schedules ps
where a.gl_date between p_start_date and p_end_date
and nvl(a.postable,'Y') = 'Y'
and a.payment_schedule_id = ps.payment_schedule_id
and ps.customer_id = p_customer_id /* bug1963032 */
and nvl(ps.customer_site_use_id, -10) = nvl(p_site_use_id, nvl(ps.customer_site_use_id, -10) )
and a.receivables_trx_id = rt.receivables_trx_id
and nvl(rt.type,'X') <> 'FINCHRG'
AND ps.invoice_currency_code = nvl(p_currency_code, ps.invoice_currency_code)
AND nvl(ps.receipt_confirmed_flag,'Y') = 'Y'
AND ps.status = nvl(p_status, ps.status);
select decode(p_currency_code,
NULL , NULL , nvl(sum(a.amount),0)
),
nvl(sum( decode(p_exc_rate,
NULL, a.acctd_amount,
arpcurr.functional_amount(a.amount,
p_func_curr,
p_exc_rate,
p_precision,
p_min_acc_unit
)
)),0),
count(a.amount)
into p_financecharg_amount,
p_financecharg_func_amt,
p_financecharg_count
from ar_adjustments a,
ar_receivables_trx rt,
ar_payment_schedules ps
where a.gl_date between p_start_date and p_end_date
and nvl(a.postable,'Y') = 'Y'
and a.payment_schedule_id = ps.payment_schedule_id
and ps.customer_id = p_customer_id /* bug1963032 */
and nvl(ps.customer_site_use_id, -10) = nvl(p_site_use_id, nvl(ps.customer_site_use_id, -10) )
and a.receivables_trx_id = rt.receivables_trx_id
and nvl(rt.type,'X') = 'FINCHRG'
AND ps.invoice_currency_code = nvl(p_currency_code, ps.invoice_currency_code)
AND nvl(ps.receipt_confirmed_flag,'Y') = 'Y'
AND ps.status = nvl(p_status, ps.status);
SELECT decode(p_currency_code,
NULL , NULL ,
nvl(sum(ra.earned_discount_taken),0)
),
decode(p_currency_code,
NULL , NULL ,
nvl(sum(ra.unearned_discount_taken),0)
),
nvl(sum( decode(p_exc_rate,
NULL, ra.acctd_earned_discount_taken,
arpcurr.functional_amount( nvl(ra.earned_discount_taken,0),
p_func_curr,
p_exc_rate, p_precision,
p_min_acc_unit
)
)
),0),
nvl(sum( decode(p_exc_rate,
NULL, ra.acctd_unearned_discount_taken,
arpcurr.functional_amount( nvl(ra.unearned_discount_taken,0),
p_func_curr,
p_exc_rate,
p_precision,
p_min_acc_unit
)
)),0),
count(decode(ra.earned_discount_taken,
0, NULL,
ra.earned_discount_taken
)
),
count(decode(ra.unearned_discount_taken,
0, NULL,
ra.unearned_discount_taken
)
)
INTO p_earned_discounts,
p_unearned_discounts,
p_earned_func_disc,
p_unearned_func_disc,
p_earned_disc_count,
p_unearned_disc_count
FROM ar_receivable_applications ra,
ar_payment_schedules ps
where ra.gl_date between p_start_date and p_end_date
and ps.payment_schedule_id = ra.applied_payment_schedule_id
and ps.customer_id = p_customer_id /* bug1963032 */
and nvl(ps.customer_site_use_id, -10) = nvl(p_site_use_id, nvl(ps.customer_site_use_id, -10) )
and ps.invoice_currency_code = nvl(p_currency_code, ps.invoice_currency_code)
and nvl(ps.receipt_confirmed_flag,'Y') = 'Y'
and ps.status = nvl(p_status, ps.status);
select decode(p_currency_code,
NULL , NULL ,
nvl(sum(cr.amount),0)
),
nvl(sum(arpcurr.functional_amount( cr.amount,
p_func_curr,
nvl(p_exc_rate,ps.exchange_rate),
p_precision,
p_min_acc_unit
)
),0),
count(cr.amount)
into p_pend_confirm_amt,
p_pend_confirm_func_amt,
p_pend_confirm_count
from ar_payment_schedules ps,
ar_cash_receipts cr,
ar_cash_receipt_history crh
where ps.customer_id = p_customer_id /* bug1963032 */
and ps.cash_receipt_id = cr.cash_receipt_id
and nvl(ps.customer_site_use_id, -10) = nvl(p_site_use_id, nvl(ps.customer_site_use_id,
-10) )
and ps.gl_date between p_start_date and p_end_date
and ps.invoice_currency_code = nvl(p_currency_code,ps.invoice_currency_code)
and ps.status = nvl(p_status, ps.status)
and cr.cash_receipt_id = crh.cash_receipt_id
and crh.current_record_flag||'' = 'Y'
and crh.status = 'APPROVED';
select decode(p_currency_code,
NULL , NULL ,
nvl(sum(decode(ps.class, 'PMT' , ps.amount_due_original, 0)),0)
), /* Sum of Original Amount */
decode(p_currency_code,
NULL , NULL ,
nvl(sum(decode(ps.class, 'PMT' , ps.amount_due_remaining, 0)),0)
), /* Sum of Amount Due Remaining */
nvl(sum(decode(ps.class,
'PMT', arpcurr.functional_amount( ps.amount_due_original,
p_func_curr,
nvl(p_exc_rate, ps.exchange_rate),
p_precision,
p_min_acc_unit
),
0)), 0), /* Sum of Functional Original Amount */
nvl(sum(decode(ps.class,
'PMT', decode(p_exc_rate,
NULL, ps.acctd_amount_due_remaining,
arpcurr.functional_amount( ps.amount_due_remaining,
p_func_curr,
p_exc_rate,
p_precision,
p_min_acc_unit
)
), 0)
), 0), /* Sum of Functional Amount Due Remaining */
nvl(sum(decode(ps.class,
'PMT' , 1, 0
)
),0) /* Count of Receipts */
into l_ori_amount,
l_rem_amount,
l_func_ori_amt,
l_func_rem_amt,
l_count
from ar_cash_receipts cr,
ar_payment_schedules ps,
ar_cash_receipt_history crh,
ar_receipt_methods rm,
ar_receipt_classes rc
where ps.customer_id = p_customer_id /* bug1963032 */
and ps.cash_receipt_id = cr.cash_receipt_id
and nvl(ps.customer_site_use_id, -10) = nvl(p_site_use_id, nvl(ps.customer_site_use_id, -10) )
and ps.gl_date between p_start_date and p_end_date
and ps.invoice_currency_code = nvl(p_currency_code,ps.invoice_currency_code)
and nvl(cr.reversal_category, cr.status||'X') <> cr.status
and nvl(ps.receipt_confirmed_flag,'Y') = 'Y'
and ps.status = nvl(p_status, ps.status)
and cr.cash_receipt_id = crh.cash_receipt_id
and crh.current_record_flag||'' = 'Y'
and crh.status = 'CONFIRMED'
and cr.receipt_method_id = rm.receipt_method_id
and rm.receipt_class_id = rc.receipt_class_id
and nvl(rc.bill_of_exchange_flag, 'N') = decode(l_type, 'BOE', 'Y', 'N')
and nvl(rc.notes_receivable, 'N') = decode(l_type, 'NOTES', 'Y', 'N');
select decode(p_currency_code,
NULL , NULL ,
nvl(sum(decode(ps.class, 'PMT' , ps.amount_due_original, 0)),0)
), /* Sum of Original Amount */
decode(p_currency_code,
NULL , NULL ,
nvl(sum(decode(ps.class, 'PMT' , ps.amount_due_remaining, 0)),0)
), /* Sum of Amount Due Remaining */
nvl(sum(decode(ps.class,
'PMT', arpcurr.functional_amount( ps.amount_due_original,
p_func_curr,
nvl(p_exc_rate, ps.exchange_rate),
p_precision,
p_min_acc_unit
),
0)), 0), /* Sum of Functional Original Amount */
nvl(sum(decode(ps.class,
'PMT', decode(p_exc_rate,
NULL, ps.acctd_amount_due_remaining,
arpcurr.functional_amount( ps.amount_due_remaining,
p_func_curr,
p_exc_rate,
p_precision,
p_min_acc_unit
)
), 0)
), 0), /* Sum of Functional Amount Due Remaining */
nvl(sum(decode(ps.class,
'PMT' , 1, 0
)
),0) /* Count of Receipts */
into l_ori_amount,
l_rem_amount,
l_func_ori_amt,
l_func_rem_amt,
l_count
from ar_cash_receipts cr,
ar_payment_schedules ps,
ar_cash_receipt_history crh
where ps.customer_id = p_customer_id /* bug1963032 */
and ps.cash_receipt_id = cr.cash_receipt_id
and nvl(ps.customer_site_use_id, -10) = nvl(p_site_use_id, nvl(ps.customer_site_use_id, -10) )
and ps.gl_date between p_start_date and p_end_date
and ps.invoice_currency_code = nvl(p_currency_code,ps.invoice_currency_code)
and nvl(cr.reversal_category, cr.status||'X') <> cr.status
and nvl(ps.receipt_confirmed_flag,'Y') = 'Y'
and ps.status = nvl(p_status, ps.status)
and cr.cash_receipt_id = crh.cash_receipt_id
and crh.current_record_flag||'' = 'Y'
and crh.status = 'REMITTED'
and crh.factor_flag = decode(l_type, 'STANDARD',
'N', 'Y');
select decode(p_currency_code,
NULL , NULL , nvl(sum(ps.amount_due_remaining),0)
),
nvl(sum( decode(p_exc_rate,
NULL, ps.acctd_amount_due_remaining,
arpcurr.functional_amount(ps.amount_due_remaining,
p_func_curr,
p_exc_rate,
p_precision,
p_min_acc_unit
)
)),0),
count(ps.amount_due_remaining)
into p_BR_protested_amt,
p_BR_protested_func_amt,
p_BR_protested_count
from ar_transaction_history trh,
ar_payment_schedules ps
where trh.gl_date between p_start_date and p_end_date
and trh.status = 'PROTESTED'
and nvl(trh.current_record_flag,'Y') = 'Y'
and ps.customer_trx_id = trh.customer_trx_id
and ps.customer_id = p_customer_id /* bug1963032 */
and nvl(ps.customer_site_use_id, -10) = nvl(p_site_use_id, nvl(ps.customer_site_use_id, -10) )
AND ps.invoice_currency_code = nvl(p_currency_code, ps.invoice_currency_code)
AND ps.status = nvl(p_status, ps.status);
select decode(p_currency_code,
NULL , NULL , nvl(sum(ps.amount_due_remaining),0)
),
nvl(sum( decode(p_exc_rate,
NULL, ps.acctd_amount_due_remaining,
arpcurr.functional_amount(ps.amount_due_remaining,
p_func_curr,
p_exc_rate,
p_precision,
p_min_acc_unit
)
)),0),
count(ps.amount_due_remaining)
into p_BR_unpaid_amt,
p_BR_unpaid_func_amt,
p_BR_unpaid_count
from ar_transaction_history trh,
ar_payment_schedules ps
where trh.gl_date between p_start_date and p_end_date
and trh.status = 'UNPAID'
and nvl(trh.current_record_flag,'Y') = 'Y'
and ps.customer_trx_id = trh.customer_trx_id
and ps.customer_id = p_customer_id /* bug1963032 */
and nvl(ps.customer_site_use_id, -10) = nvl(p_site_use_id, nvl(ps.customer_site_use_id, -10) )
AND ps.invoice_currency_code = nvl(p_currency_code, ps.invoice_currency_code)
AND ps.status = nvl(p_status, ps.status);
select decode(p_currency_code,
NULL , NULL , nvl(sum(ctl.extended_amount),0)
),
nvl(sum( decode(p_exc_rate,
NULL, ctl.extended_acctd_amount,
arpcurr.functional_amount(ctl.extended_amount,
p_func_curr,
p_exc_rate,
p_precision,
p_min_acc_unit
)
)),0),
count(distinct ctl.customer_trx_id)
into p_BR_pend_acceptance_amt,
p_BR_pend_acceptance_func_amt,
p_BR_pend_acceptance_count
from ar_transaction_history trh,
ra_customer_trx ct,
ra_customer_trx_lines ctl
where trh.gl_date between p_start_date and p_end_date
and trh.status = 'PENDING_ACCEPTANCE'
and nvl(trh.current_record_flag,'Y') = 'Y'
and ct.customer_trx_id = trh.customer_trx_id
and ct.drawee_id = p_customer_id /* bug1963032 */
AND ct.invoice_currency_code = nvl(p_currency_code, ct.invoice_currency_code)
and nvl(ct.drawee_site_use_id, -10) = nvl(p_site_use_id, nvl(ct.drawee_site_use_id, -10) )
and ctl.customer_trx_id = ct.customer_trx_id;