[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
pg_last_updated_by number;
pg_last_update_login number;
SELECT precision,
minimum_accountable_unit
FROM fnd_currencies
WHERE currency_code = cp_currency_code;
select ar_interest_headers_s.nextval
into l_next_hdr_id
from dual;
Procedure Insert_int_rev_rect_overdue( p_fin_charge_date IN DATE,
p_worker_number IN NUMBER,
p_total_workers IN NUMBER) IS
l_fin_charge_date DATE;
Insert into ar_late_charge_trx_t
(late_charge_trx_id,
customer_id,
customer_site_use_id,
currency_code,
customer_trx_id,
legal_entity_id,
payment_schedule_id,
class,
amount_due_original,
amount_due_remaining,
fin_charge_charged,
trx_date,
cust_trx_type_id,
last_charge_date,
exchange_rate_type,
min_interest_charge,
max_interest_charge,
overdue_late_pay_amount,
original_balance,
due_date,
receipt_date,
finance_charge_date,
charge_type,
actual_date_closed,
interest_rate,
interest_days,
rate_start_date,
rate_end_date,
schedule_days_start,
schedule_days_to,
late_charge_amount,
late_charge_type,
late_charge_term_id,
interest_period_days,
interest_calculation_period,
charge_on_finance_charge_flag,
message_text_id,
interest_type,
min_fc_invoice_overdue_type,
min_fc_invoice_amount,
min_fc_invoice_percent,
charge_line_type,
org_id,
request_id,
display_flag )
SELECT ar_late_charge_trx_s.nextval,
b.customer_id,
b.customer_site_use_id ,
b.invoice_currency_code,
b.customer_trx_id,
b.legal_entity_id,
b.payment_schedule_id,
b.class ,
b.amount_due_original,
b.amount_due_remaining ,
b.finance_charge_charged,
b.trx_date,
b.cust_trx_type_id,
NVL(b.last_charge_date, decode(b.finance_charge_charged,
0, NULL,
b.last_accrue_charge_date)) last_charge_date,
b.exchange_rate_type,
b.min_interest_charge,
b.max_interest_charge,
b.overdue_amt,
b.original_balance,
b.due_date,
NULL,
b.fin_charge_date,
b.charge_type,
b.actual_date_closed,
decode(b.interest_type,
'CHARGES_SCHEDULE',sched_lines.rate,
'FIXED_RATE',b.interest_rate, NULL) interest_rate,
least(decode(b.multiple_interest_rates_flag,
'Y',decode(sched_hdrs.schedule_header_type,
'RATE',
nvl(sched_hdrs.end_date,b.eff_fin_charge_date),
b.eff_fin_charge_date),
b.eff_fin_charge_date)) -
greatest(decode(b.multiple_interest_rates_flag,
'Y',decode(sched_hdrs.schedule_header_type,
'RATE',sched_hdrs.start_date-1,b.eff_due_date),
b.eff_due_date), b.eff_due_date,b.eff_last_charge_date) interest_days,
sched_hdrs.start_date rate_start_date,
sched_hdrs.end_date rate_end_date,
bucket_lines.days_start schedule_days_start,
bucket_lines.days_to schedule_days_to,
decode(b.interest_type,
'FIXED_AMOUNT',0,
'CHARGE_PER_TIER',0 ,
decode(sched_hdrs.schedule_header_type,
'AMOUNT',0,
ar_calc_late_charge.calculate_interest(
decode(b.charge_on_finance_charge_flag,
'F',0,b.overdue_amt),
b.charge_on_finance_charge_flag,
least(decode(b.multiple_interest_rates_flag,
'Y',decode(sched_hdrs.schedule_header_type,
'RATE',
nvl(sched_hdrs.end_date,
b.eff_fin_charge_date),
b.eff_fin_charge_date),
b.eff_fin_charge_date)) -
greatest(decode(b.multiple_interest_rates_flag,
'Y',decode(sched_hdrs.schedule_header_type,
'RATE',sched_hdrs.start_date-1,
b.eff_due_date),
b.eff_due_date),b.eff_due_date,
b.eff_last_charge_date),
decode(b.interest_type,
'CHARGES_SCHEDULE',sched_lines.rate,
'FIXED_RATE',b.interest_rate, NULL),
b.interest_period_days,
b.invoice_currency_code,
b.payment_schedule_id))) late_charge_amount,
b.late_charge_type,
b.late_charge_term_id,
b.interest_period_days,
b.interest_calculation_period,
b.charge_on_finance_charge_flag,
b.message_text_id,
b.interest_type,
b.min_fc_invoice_overdue_type,
b.min_fc_invoice_amount,
b.min_fc_invoice_percent,
'INTEREST',
b.org_id,
-1,
'Y'
from (
select
ps.customer_id,
decode(ps.class,
'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
'PMT',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
ps.customer_site_use_id) customer_site_use_id,
ps.invoice_currency_code,
ps.customer_trx_id,
int_headers.legal_entity_id,
ps.payment_schedule_id,
ps.class ,
ps.amount_due_original,
ps.amount_due_remaining,
int_lines.interest_charged finance_charge_charged,
ps.trx_date,
ps.cust_trx_type_id,
cr.receipt_date last_charge_date,
cust_site.last_accrue_charge_date ,
cust_site.exchange_rate,
cust_site.exchange_rate_type,
cust_site.min_interest_charge,
cust_site.max_interest_charge,
nvl(int_lines.outstanding_amount,0) overdue_amt,
nvl(int_lines.outstanding_amount,0) original_balance,
decode(ps.class,'PMT',ps.trx_date,ps.due_date) due_date,
l_fin_charge_date fin_charge_date,
ps.actual_date_closed,
cust_site.late_charge_type,
cust_site.late_charge_term_id ,
cust_site.interest_period_days,
cust_site.interest_calculation_period,
cust_site.charge_on_finance_charge_flag,
cust_site.message_text_id,
cust_site.interest_type,
cust_site.interest_rate,
cust_site.interest_schedule_id,
cust_site.min_fc_invoice_overdue_type,
cust_site.min_fc_invoice_amount,
cust_site.min_fc_invoice_percent,
cust_site.multiple_interest_rates_flag,
cust_site.hold_charged_invoices_flag,
ps.org_id,
cust_site.interest_fixed_amount,
ps.cash_receipt_id,
'OVERDUE' charge_type,
decode(cust_site.interest_calculation_period,
'DAILY',ps.last_charge_date,
'MONTHLY',last_day(ps.last_charge_date)) eff_fin_charge_date,
decode(cust_site.interest_calculation_period,
'DAILY',nvl(int_lines.payment_date,
decode(int_lines.finance_charge_charged,
0,int_lines.due_date,
int_lines.last_charge_date)),
'MONTHLY',first_day(nvl(int_lines.last_charge_date,
decode(int_lines.finance_charge_charged,
0,int_lines.due_date,
int_lines.last_charge_date)))) eff_last_charge_date,
decode(cust_site.interest_calculation_period,
'DAILY',int_lines.due_date,
'MONTHLY',first_day(int_lines.due_date)) eff_due_date
from ar_interest_lines int_lines,
ar_interest_headers int_headers,
ar_cash_receipts cr,
ar_payment_schedules ps,
ar_lc_cust_sites_t cust_site,
ar_late_charge_cust_balance_gt bal
where ps.customer_id = cust_site.customer_id
and cust_site.customer_site_use_id = decode(ps.class,
'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
'PMT',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
ps.customer_site_use_id)
and ps.invoice_currency_code = cust_site.currency_code
and mod(nvl(cust_site.customer_site_use_id,0),l_total_workers) =
decode(l_total_workers,l_worker_number,0,l_worker_number)
and int_headers.customer_id=cust_site.customer_id
and cr.reversal_date is not null
and cr.cash_receipt_id=int_lines.cash_receipt_id
and ps.payment_schedule_id=int_lines.payment_schedule_id
and ps.org_id = cust_site.org_id
and cust_site.late_charge_calculation_trx in ('OVERDUE_LATE')
and int_lines.type='LATE'
and int_lines.interest_header_id=int_headers.interest_header_id
-- and cust_site.late_charge_type = 'INV'
/* Apply Customer Level tolerances */
and cust_site.lc_cust_sites_id = bal.late_charge_cust_sites_id
and cust_site.org_id = bal.org_id
and decode(cust_site.min_fc_balance_overdue_type,
'PERCENT',(nvl(cust_site.min_fc_balance_percent,0)
* nvl(bal.customer_open_balance,0)/100),
'AMOUNT',nvl(cust_site.min_fc_balance_amount,0),
0) <= nvl(bal.customer_overdue_balance,0)
/* Added NVL() for cust_site.credit_items_flag as part of Bug 10280458, manishri */
and decode(nvl(cust_site.disputed_transactions_flag,'N'),
'N',decode(nvl(ps.amount_in_dispute,0),
0, 'Y','N'),
'Y' ) = 'Y'
and decode(nvl(cust_site.credit_items_flag, 'N'),'N',
decode (ps.class, 'PMT','N','CM','N','INV',decode(sign(ps.amount_due_original),-1,'N','Y'),'Y'),'Y') = 'Y'
and nvl(ps.receipt_confirmed_flag, 'Y') = 'Y'
and ps.last_charge_date > cr.receipt_date
and cr.reversal_date < l_fin_charge_date
and cr.reversal_date > ps.last_charge_date
group by ps.customer_id,
decode(ps.class,
'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
'PMT',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
ps.customer_site_use_id),
ps.invoice_currency_code,
ps.customer_trx_id,
int_headers.legal_entity_id,
ps.payment_schedule_id,
ps.class ,
ps.amount_due_original,
ps.amount_due_remaining,
int_lines.interest_charged,
ps.trx_date,
ps.cust_trx_type_id,
cr.receipt_date,
cust_site.last_accrue_charge_date,
cust_site.exchange_rate,
cust_site.exchange_rate_type,
cust_site.min_interest_charge,
cust_site.max_interest_charge,
nvl(int_lines.outstanding_amount,0),
nvl(int_lines.outstanding_amount,0),
decode(ps.class,'PMT',ps.trx_date,ps.due_date),
l_fin_charge_date ,
ps.actual_date_closed,
cust_site.late_charge_type,
cust_site.late_charge_term_id,
cust_site.interest_period_days,
cust_site.interest_calculation_period,
cust_site.charge_on_finance_charge_flag,
cust_site.message_text_id,
cust_site.interest_type,
cust_site.interest_rate,
cust_site.interest_schedule_id,
cust_site.min_fc_invoice_overdue_type,
cust_site.min_fc_invoice_amount,
cust_site.min_fc_invoice_percent,
cust_site.multiple_interest_rates_flag,
cust_site.hold_charged_invoices_flag,
ps.org_id,
cust_site.interest_fixed_amount,
ps.cash_receipt_id,
'OVERDUE',
decode(cust_site.interest_calculation_period,
'DAILY',ps.last_charge_date,
'MONTHLY',last_day(ps.last_charge_date)),
decode(cust_site.interest_calculation_period,
'DAILY',nvl(int_lines.payment_date,
decode(int_lines.finance_charge_charged,
0,int_lines.due_date,
int_lines.last_charge_date)),
'MONTHLY',first_day(nvl(int_lines.last_charge_date,
decode(int_lines.finance_charge_charged,
0,int_lines.due_date,
int_lines.last_charge_date)))),
decode(cust_site.interest_calculation_period,
'DAILY',int_lines.due_date,
'MONTHLY',first_day(int_lines.due_date))
) b,
ar_charge_schedule_hdrs sched_hdrs,
ar_charge_schedule_lines sched_lines,
ar_aging_bucket_lines bucket_lines
where b.interest_schedule_id = sched_hdrs.schedule_id(+)
and sched_hdrs.schedule_header_id = sched_lines.schedule_header_id(+)
and sched_hdrs.schedule_id = sched_lines.schedule_id(+)
and nvl(sched_hdrs.status,'A') = 'A'
and sched_lines.aging_bucket_id = bucket_lines.aging_bucket_id(+)
and sched_lines.aging_bucket_line_id = bucket_lines.aging_bucket_line_id(+)
/* Condition 1: days late should be between the bucket lines start and end days */
and (l_fin_charge_date- b.due_date) >= nvl(bucket_lines.days_start,(l_fin_charge_date- b.due_date))
and (l_fin_charge_date - b.due_date) <= nvl(bucket_lines.days_to,(l_fin_charge_date- b.due_date))
/* Condition 2:
Start_date of the schedule should be less than or equal to the finance charge date */
and nvl(sched_hdrs.start_date,l_fin_charge_date) <= l_fin_charge_date
/* condition 3:
If multiple interest rates have to be used, end date of the schedule should be greater than
or equal to the due date or the date from which we are calculating the charge
Otherwise, the end_date should either be null or it should be greater than the
due_date
*/
and (decode(b.multiple_interest_rates_flag,'Y',
decode(sched_hdrs.schedule_header_type,
'RATE',greatest(b.due_date,nvl(b.last_charge_date,b.due_date)),
b.due_date),
b.due_date) <= sched_hdrs.end_date
OR sched_hdrs.end_date IS NULL )
/* Condition 4: If multiple rates need not be used, we should pick up the rate
that is effective on the due_date of the transaction.
Also note that the multiple interest rates are used only for Interest
Calculation and only when rates are used*/
and decode(b.multiple_interest_rates_flag,'Y',
decode(sched_hdrs.schedule_header_type,
'RATE',sched_hdrs.start_date,
b.due_date),
b.due_date )>= nvl(sched_hdrs.start_date,b.due_date);
Update the amount by distributing applicable interest amount evenly
across the all late charge interest rows.
========================================================================*/
/*Late charge Case of charge per tier.*/
/*Enhancement 6469663*/
PROCEDURE update_interest_amt(p_line_type in VARCHAR2) IS
CURSOR recordPerCust(l_line_type IN VARCHAR2) IS
SELECT count(*) reccount,sum(amount_due_original) total_due_org,
CUSTOMER_ID,CUSTOMER_SITE_USE_ID,SCHEDULE_DAYS_START,SCHEDULE_DAYS_TO,CURRENCY_CODE,
LATE_CHARGE_AMOUNT,LATE_CHARGE_TYPE,ORG_ID
FROM AR_LATE_CHARGE_TRX_T
WHERE INTEREST_TYPE = 'CHARGE_PER_TIER'
/*and LATE_CHARGE_TYPE = l_charge_type /*'INV'*/
AND CHARGE_LINE_TYPE = l_line_type /*'INTEREST' /* l_line_type*/
AND OVERDUE_LATE_PAY_AMOUNT > 0
AND DECODE(charge_line_type,'INTEREST',DECODE (MIN_FC_INVOICE_OVERDUE_TYPE, /*Bug 11704667*/
'AMOUNT',MIN_FC_INVOICE_AMOUNT ,
'PERCENT',(nvl(MIN_FC_INVOICE_PERCENT,0) * AMOUNT_DUE_ORIGINAL/100),
nvl(ORIGINAL_BALANCE,0)),1) <= decode(charge_line_type,'INTEREST',ORIGINAL_BALANCE,1) /*Bug 11704667*/
GROUP BY
CUSTOMER_ID,
CUSTOMER_SITE_USE_ID,
SCHEDULE_DAYS_START,
SCHEDULE_DAYS_TO,
CURRENCY_CODE,
LATE_CHARGE_AMOUNT,
LATE_CHARGE_TYPE,
ORG_ID;
SELECT LATE_CHARGE_TRX_ID,CUSTOMER_ID,CUSTOMER_SITE_USE_ID,SCHEDULE_DAYS_START,SCHEDULE_DAYS_TO,CURRENCY_CODE,
LATE_CHARGE_TYPE,ORG_ID,AMOUNT_DUE_ORIGINAL
FROM AR_LATE_CHARGE_TRX_T
WHERE INTEREST_TYPE = 'CHARGE_PER_TIER'
/* and LATE_CHARGE_TYPE = l_charge_type /*'INV'*/
AND CHARGE_LINE_TYPE = l_line_type /*'INTEREST'; /* l_line_type;*/
debug('update interest amount+');
UPDATE ar_late_charge_trx_t SET late_charge_amount = 0 where amount_due_original < 0
AND INTEREST_TYPE = 'CHARGE_PER_TIER';
debug('update interest amount : count of record ' || p_lc_per_trx.count );
UPDATE ar_late_charge_trx_t set late_charge_amount = p_lc_per_trx(trxindex)
WHERE late_charge_trx_id = p_cust_sites_id(trxindex);
debug('update interest amount-');
END update_interest_amt;
select site_uses.site_use_id
into l_late_charge_site_use_id
from hz_cust_acct_sites acct_site,
hz_cust_site_uses site_uses
where acct_site.cust_account_id = p_customer_id
and site_uses.cust_acct_site_id = acct_site.cust_acct_site_id
and site_uses.site_use_code = 'LATE_CHARGE'
and site_uses.org_id = p_org_id
and site_uses.status = 'A';
select site_use_id
into l_profile_class_site_use_id
from hz_customer_profiles
where site_use_id = p_site_use_id;
select site_use_id
into l_profile_class_site_use_id
from hz_customer_profiles
where site_use_id in ( select site_use_id
from hz_cust_site_uses
where cust_acct_site_id =
( SELECT cust_acct_site_id
FROM hz_cust_site_uses
WHERE site_use_id = p_site_use_id)
and status = 'A'
and site_use_code in ('BILL_TO','LATE_CHARGE'));
select site_use_id
into l_profile_class_site_use_id
from hz_customer_profiles
where site_use_id in ( select site_use_id
from hz_cust_site_uses
where cust_acct_site_id =
( SELECT cust_acct_site_id
FROM hz_cust_site_uses
WHERE site_use_id = p_site_use_id)
and status = 'A'
and site_use_code = 'BILL_TO');
select cust_acct_site_id,
site_use_code
into l_cust_acct_site_id,
l_site_use_code
from hz_cust_site_uses
where site_use_id = p_site_use_id
and org_id = p_org_id;
select site_use_id
into l_bill_to_site_use_id
from hz_cust_site_uses
where cust_acct_site_id = l_cust_acct_site_id
and site_use_code = 'BILL_TO'
and org_id = p_org_id;
select site_use.site_use_id
into l_bill_to_site_use_id
from hz_cust_site_uses site_use,
hz_cust_acct_sites sites
where sites.cust_account_id = p_customer_id
and sites.bill_to_flag = 'P'
and sites.status ='A'
and sites.cust_acct_site_id = site_use.cust_acct_site_id
and site_use.site_use_code = 'BILL_TO'
and site_use.org_id = p_org_id;
select min(billing_date)-1
into l_next_bill_date
from ar_cons_inv
where customer_id = p_customer_id
and site_use_id = p_site_use_id
and currency_code = p_currency_code
and org_id = p_org_id
and billing_date > p_as_of_date
and billing_date <= p_fin_charge_date
and status in ('IMPORTED','ACCEPTED','FINAL');
select min(trx_date) -1
into l_next_date
from ar_payment_schedules ps
where customer_id = p_customer_id
and decode(ps.class,
'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
'PMT',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
ps.customer_site_use_id) = p_site_use_id
and ps.invoice_currency_code = p_currency_code
and ps.org_id = p_org_id
and decode(p_post_bill_debit,
'INCLUDE_DEBIT_ITEM','Y',
'EXCLUDE_DEBIT_ITEM',decode(ps.class,
'PMT','Y',
'CM','Y',
'N')) = 'Y'
and trx_date > p_as_of_date
and trx_date <= p_fin_charge_date;
select rt.receivables_trx_id
into p_int_receivables_trx_id
from ar_receivables_trx rt
where rt.receivables_trx_id =
( select decode(rsu_st.finchrg_receivables_trx_id,
'',decode(rsu_bt.finchrg_receivables_trx_id,
'',sp.finchrg_receivables_trx_id,
rsu_bt.finchrg_receivables_trx_id),
rsu_st.finchrg_receivables_trx_id
)
from ra_customer_trx ctrx,
hz_cust_site_uses rsu_st,
hz_cust_site_uses rsu_bt,
ar_system_parameters sp
where ctrx.customer_trx_id = p_customer_trx_id
and ctrx.org_id = p_org_id
and sp.org_id = p_org_id
and ctrx.bill_to_site_use_id = rsu_bt.site_use_id(+)
and ctrx.ship_to_site_use_id = rsu_st.site_use_id(+))
and rt.type = 'FINCHRG'
and nvl(rt.status,'A') = 'A'
and rt.org_id = p_org_id
and p_fin_charge_date >= nvl(rt.start_date_active,
p_fin_charge_date)
and p_fin_charge_date <= nvl(rt.end_date_active,
p_fin_charge_date);
select rt.receivables_trx_id
into l_penalty_receivables_trx_id
from ar_receivables_trx rt
where rt.receivables_trx_id = (select sp.penalty_rec_trx_id
from ar_system_parameters sp
where sp.org_id = p_org_id)
and rt.type = 'FINCHRG'
and nvl(rt.status,'A') = 'A'
and rt.org_id = p_org_id
and p_fin_charge_date >= nvl(rt.start_date_active,
p_fin_charge_date)
and p_fin_charge_date <= nvl(rt.end_date_active,
p_fin_charge_date);
select sum(amount_due_original), sum(fin_charge_charged)
into l_balance_due,l_fin_chrg_adjustment
from
(select amount_due_original,0 fin_charge_charged
from ar_payment_schedules
where payment_schedule_id = p_payment_schedule_id
union all
select nvl(-1 *(ra.amount_applied
+ nvl(ra.earned_discount_taken,0)
+ nvl(ra.unearned_discount_taken,0))
,0) amount_applied,
0 fin_charge_charged
from ar_receivable_applications ra,
ar_payment_schedules ps_cm_cr
where applied_payment_schedule_id = p_payment_schedule_id
and ra.status = 'APP'
and nvl(ra.confirmed_flag,'Y') = 'Y'
and ps_cm_cr.payment_schedule_id = ra.payment_schedule_id
and ps_cm_cr.trx_date <= p_as_of_date
union all
select nvl(ra.amount_applied_from, ra.amount_applied),
0 fin_charge_charged
from ar_receivable_applications ra
where payment_schedule_id = p_payment_schedule_id
and ra.apply_date <= p_as_of_date
and ra.status = 'APP'
and nvl(ra.confirmed_flag,'Y') = 'Y'
and p_class = 'CM'
and ra.application_type = 'CM'
union all
select adj.amount,
CASE WHEN adj.type ='CHARGES'
THEN adj.amount
ELSE 0 END fin_charge_charged
from ar_adjustments adj
where adj.payment_schedule_id = p_payment_schedule_id
and adj.apply_date <= p_as_of_date
and adj.status = 'A');
select ps.amount_due_remaining
into l_balance_due
from ar_payment_schedules ps
where ps.payment_schedule_id = p_payment_schedule_id
and ps.class ='PMT'
and nvl(ps.receipt_confirmed_flag,'Y') = 'Y';
select sum(bal_amount)
into l_cust_balance
from (
select sum(ending_balance) bal_amount
from ar_cons_inv cons_inv
where cons_inv.customer_id = p_customer_id
and cons_inv.site_use_id = p_site_use_id
and cons_inv.currency_code = p_currency_code
and cons_inv.org_id = p_org_id
and cons_inv.status in('FINAL', 'ACCEPTED','IMPORTED')
and cons_inv.billing_date = (select max(ci2.billing_date)
from ar_cons_inv ci2
where ci2.customer_id = p_customer_id
and ci2.site_use_id = p_site_use_id
and ci2.currency_code = p_currency_code
and ci2.org_id = p_org_id
and ci2.billing_date <= p_as_of_date
and ci2.status in ('FINAL', 'ACCEPTED','IMPORTED'))
union all
select sum(amount_due_original)
from ar_payment_schedules ps
where ps.customer_id = p_customer_id
and decode(ps.class,
'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
'PMT',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
ps.customer_site_use_id) = p_site_use_id
and ps.invoice_currency_code = p_currency_code
and ps.org_id = p_org_id
and decode(p_post_billing_debit,
'INCLUDE_DEBIT_ITEM','Y',
'EXCLUDE_DEBIT_ITEM',decode(ps.class,
'PMT','Y',
'CM','Y',
'N'),
'N') = 'Y'
and (ps.trx_date > (select max(ci2.billing_date)
from ar_cons_inv ci2
where ci2.customer_id = p_customer_id
and ci2.site_use_id = p_site_use_id
and ci2.currency_code = p_currency_code
and ci2.org_id = p_org_id
AND ci2.billing_date <= p_as_of_date
AND ci2.status in ('FINAL','ACCEPTED','IMPORTED'))
OR
/* No BFB exists for this customer.. for run date to run date option we
have to calculate the ADB even for the period before the first BFB is
created. i.e. There should not be any gaps in the ADB calculation */
(not exists (select cons_inv_id
from ar_cons_inv ci2
where ci2.customer_id = p_customer_id
and ci2.site_use_id = p_site_use_id
and ci2.currency_code = p_currency_code
and ci2.org_id = p_org_id
AND ci2.billing_date <= p_as_of_date
AND ci2.status in ('FINAL','ACCEPTED','IMPORTED'))))
and ps.trx_date <= p_as_of_date);
select sum(a.balance)
into l_cust_eligible_bal
from (
select sum(ci.ending_balance) balance
from ar_cons_inv ci
where ci.customer_id = p_customer_id
and ci.site_use_id = p_site_use_id
and ci.currency_code = p_currency_code
and ci.org_id = p_org_id
and ci.billing_date = (select max(ci2.billing_date)
from ar_cons_inv ci2
where ci2.customer_id = p_customer_id
and ci2.site_use_id = p_site_use_id
and ci2.currency_code = p_currency_code
and ci2.org_id = p_org_id
and ci2.billing_date <= p_fin_charge_date
and ci2.status in ('FINAL', 'ACCEPTED','IMPORTED'))
and ci.status in ('FINAL', 'ACCEPTED','IMPORTED')
union all
select sum(ps.amount_due_original)
from ar_cons_inv ci,
ar_payment_schedules ps
where ci.customer_id = p_customer_id
and ci.site_use_id = p_site_use_id
and ci.currency_code = p_currency_code
and ci.org_id = p_org_id
and ci.billing_date = (select max(ci2.billing_date)
from ar_cons_inv ci2
where ci2.customer_id = p_customer_id
and ci2.site_use_id = p_site_use_id
and ci2.currency_code = p_currency_code
and ci2.org_id = p_org_id
and ci2.billing_date <= p_fin_charge_date
and ci2.status in ('FINAL', 'ACCEPTED','IMPORTED'))
and ci.status in ('FINAL', 'ACCEPTED','IMPORTED')
and ps.customer_id = ci.customer_id
and decode(ps.class,
'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
'PMT',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
ps.customer_site_use_id) = ci.site_use_id
and ps.invoice_currency_code = ci.currency_code
and ps.org_id = ci.org_id
and ps.trx_date > ci.billing_date
/* As of now, don't consider the debit items for checking the eligibility,
as documented in FDD. This will cause incorrect results for Run Date to
Run Date option as this function will return N when there are no bills for
the customer. Waiting for the PM feedback on this.*/
and ps.class in ('PMT','CM')
and decode(ps.class,
'PMT',ci.due_date + nvl(p_receipt_grace_days,0),
'CM', ci.due_date + nvl(p_receipt_grace_days,0),
ci.due_date) >= ps.trx_date)a;
select min(trx_date)
into l_first_activity_date
from ar_payment_schedules ps
where ps.customer_id = p_customer_id
and decode(ps.class,
'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
'PMT',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
ps.customer_site_use_id) = p_site_use_id
and ps.invoice_currency_code = p_currency_code
and ps.org_id = p_org_id;
select substrb(party.party_name,1,50) name,lc_site.lc_cust_sites_id,lc_site.customer_id,lc_site.currency_code,lc_site.customer_site_use_id,
'INTEREST' type
FROM ar_lc_cust_sites_t lc_site,hz_cust_accounts cust_acct,hz_parties party,ar_charge_schedules c_schdl,ar_charge_schedule_headers_v h_schdl
WHERE lc_site.interest_type = 'CHARGE_PER_TIER'
AND lc_site.interest_schedule_id = c_schdl.schedule_id
AND c_schdl.schedule_id = h_schdl.schedule_id
AND fin_charge_date between h_schdl.start_date and nvl(h_schdl.end_date,to_date('31-12-4712','DD-MM-YYYY'))
AND h_schdl.SCHEDULE_HEADER_TYPE <> 'AMOUNT'
AND lc_site.customer_id = cust_acct.cust_account_id
AND cust_acct.party_id = party.party_id
UNION
select substrb(party.party_name,1,50) name,lc_site.lc_cust_sites_id,lc_site.customer_id,lc_site.currency_code,lc_site.customer_site_use_id,
'PENALTY' type
FROM ar_lc_cust_sites_t lc_site,hz_cust_accounts cust_acct,hz_parties party,ar_charge_schedules c_schdl,ar_charge_schedule_headers_v h_schdl
WHERE lc_site.penalty_type = 'CHARGE_PER_TIER'
AND lc_site.penalty_schedule_id = c_schdl.schedule_id
AND c_schdl.schedule_id = h_schdl.schedule_id
AND fin_charge_date between h_schdl.start_date and nvl(h_schdl.end_date,to_date('31-12-4712','DD-MM-YYYY'))
AND h_schdl.SCHEDULE_HEADER_TYPE <> 'AMOUNT'
AND lc_site.customer_id = cust_acct.cust_account_id
AND cust_acct.party_id = party.party_id
ORDER BY name,currency_code,customer_site_use_id,type;
'insert into ar_lc_cust_sites_t
(lc_cust_sites_id,
customer_id,
customer_site_use_id,
currency_code,
customer_profile_id,
collector_id,
late_charge_calculation_trx,
credit_items_flag,
disputed_transactions_flag,
payment_grace_days,
late_charge_type,
late_charge_term_id ,
interest_period_days,
interest_calculation_period,
charge_on_finance_charge_flag,
hold_charged_invoices_flag,
message_text_id,
multiple_interest_rates_flag,
charge_begin_date,
cons_inv_flag,
cons_bill_level,
cust_acct_profile_amt_id,
exchange_rate_type,
exchange_rate,
min_fc_invoice_overdue_type,
min_fc_invoice_amount,
min_fc_invoice_percent,
min_fc_balance_overdue_type,
min_fc_balance_amount,
min_fc_balance_percent,
min_interest_charge,
max_interest_charge,
interest_type,
interest_Rate,
interest_fixed_amount,
interest_schedule_id,
penalty_type,
penalty_rate,
penalty_fixed_amount,
penalty_schedule_id,
last_accrue_charge_date,
org_id,
request_id)
(select ar_lc_cust_sites_s.nextval,
customer_id,
customer_site_use_id,
currency_code,
cust_account_profile_id,
collector_id,
late_charge_calculation_trx,
credit_items_flag,
disputed_transaction_flag,
payment_grace_days,
late_charge_type,
late_charge_term_id,
interest_period_days,
interest_calculation_period,
charge_on_finance_charge_flag,
hold_charged_invoices_flag,
message_text_id,
multiple_interest_rates_flag,
charge_begin_date,
cons_inv_flag,
cons_bill_level,
cust_acct_profile_amt_id,
exchange_rate_type,
exchange_rate,
min_fc_invoice_overdue_type,
min_fc_invoice_amount,
min_fc_invoice_percent,
min_fc_balance_overdue_type,
min_fc_balance_amount,
min_fc_balance_percent,
min_interest_charge,
max_interest_charge,
interest_type ,
interest_rate,
interest_fixed_amount,
interest_schedule_id,
penalty_type,
penalty_rate,
penalty_fixed_amount,
penalty_schedule_id,
last_accrue_charge_date,
org_id,
:l_request_id
from
(select distinct
ps.customer_id,
decode(ps.class,
''PMT'',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
''BR'',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
ps.customer_site_use_id) customer_site_use_id,
ps.invoice_currency_code currency_code,
profiles.cust_account_profile_id,
profiles.collector_id,
profiles.late_charge_calculation_trx,
profiles.credit_items_flag,
profiles.disputed_transactions_flag disputed_transaction_flag,
profiles.payment_grace_days,
profiles.late_charge_type,
profiles.late_charge_term_id,
profiles.interest_period_days,
profiles.interest_calculation_period,
profiles.charge_on_finance_charge_flag,
profiles.hold_charged_invoices_flag,
profiles.message_text_id,
profiles.multiple_interest_rates_flag,
profiles.charge_begin_date,
profiles.cons_inv_flag,
profiles.cons_bill_level,
prof_amts.cust_acct_profile_amt_id,
decode(ps.exchange_rate_type,
NULL, NULL,
prof_amts.exchange_rate_type) exchange_rate_type,
NULL exchange_rate,
prof_amts.min_fc_invoice_overdue_type,
prof_amts.min_fc_invoice_amount,
prof_amts.min_fc_invoice_percent,
prof_amts.min_fc_balance_overdue_type,
prof_amts.min_fc_balance_amount,
prof_amts.min_fc_balance_percent,
prof_amts.min_interest_charge,
prof_amts.max_interest_charge,
prof_amts.interest_type ,
prof_amts.interest_rate,
prof_amts.interest_fixed_amount,
prof_amts.interest_schedule_id,
prof_amts.penalty_type ,
prof_amts.penalty_rate,
prof_amts.penalty_fixed_amount,
prof_amts.penalty_schedule_id,
site_use.last_accrue_charge_date,
ps.org_id
from ar_payment_schedules ps,
ar_transaction_history th,
ra_customer_trx trx,
hz_cust_accounts cust_acct,
hz_parties party,
hz_customer_profiles profiles,
hz_cust_profile_amts prof_amts,
hz_cust_site_uses site_use,
ar_system_parameters sysparam
WHERE cust_acct.party_id = party.party_id
'|| l_customer_name_where ||'
'|| l_customer_number_where || '
AND cust_acct.status = ''A''
AND ps.customer_id = cust_acct.cust_account_id
AND ps.customer_trx_id = th.customer_trx_id(+)
'|| l_currency_where ||'
'|| l_cust_site_where ||'
AND nvl(th.current_record_flag,''Y'') = ''Y''
AND nvl(th.status,''*'') not in (''PROTESTED'',''MATURED_PEND_RISK_ELIMINATION'',''CLOSED'', ''CANCELLED'')
AND ps.org_id = sysparam.org_id
'||l_org_where ||'
AND nvl(sysparam.allow_late_charges,''N'') = ''Y''
AND ps.customer_trx_id = trx.customer_trx_id(+)
AND nvl(ps.last_charge_date,ps.due_date) < :l_fin_charge_date
AND nvl(trx.finance_charges,decode(ps.class,''DEP'',''N'',''Y'')) = ''Y''
AND profiles.cust_account_id = cust_acct.cust_account_id
AND ((ar_calc_late_charge.get_profile_class_site_use_id
(decode(:l_use_late_charge_site ,
''Y'',ar_calc_late_charge.get_late_charge_site(ps.customer_id,
ps.org_id),
''N'',ps.customer_site_use_id)
,ps.org_id) IS NULL
and profiles.site_use_id is null)
OR profiles.site_use_id = ar_calc_late_charge.get_profile_class_site_use_id
(decode(:l_use_late_charge_site,
''Y'',ar_calc_late_charge.get_late_charge_site(ps.customer_id,
ps.org_id),
''N'',ps.customer_site_use_id)
,ps.org_id))
AND profiles.interest_charges = ''Y''
AND profiles.cust_account_profile_id = prof_amts.cust_account_profile_id
AND prof_amts.currency_code = ps.invoice_currency_code
AND site_use.site_use_id (+) = decode(ps.class,
''PMT'',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
''BR'',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
ps.customer_site_use_id)
AND mod(nvl(ps.customer_site_use_id,0),:l_total_workers) =
decode(:l_total_workers,:l_worker_number,0,:l_worker_number))a
/* Make sure that this customer, site and currency combination is not
part of a failed final batch */
WHERE not exists (select ''exists failed batch''
from ar_interest_headers hdr,
ar_interest_batches bat
where hdr.customer_id = a.customer_id
and hdr.customer_site_use_id = a.customer_site_use_id
and hdr.currency_code = a.currency_code
and hdr.org_id = a.org_id
and hdr.interest_batch_id = bat.interest_batch_id
and hdr.process_status <> ''S''
and bat.batch_status =''F''
and bat.transferred_status <> ''S''))';
UPDATE ar_lc_cust_sites_t set penalty_type = NULL
WHERE lc_cust_sites_id IN
( select lc_cust_sites_id
FROM ar_lc_cust_sites_t lc_site,hz_cust_accounts cust_acct,hz_parties party,ar_charge_schedules c_schdl,ar_charge_schedule_headers_v h_schdl
WHERE lc_site.penalty_type = 'CHARGE_PER_TIER'
AND lc_site.penalty_schedule_id = c_schdl.schedule_id
AND c_schdl.schedule_id = h_schdl.schedule_id
AND p_fin_charge_date between h_schdl.start_date and nvl(h_schdl.end_date,to_date('31-12-4712','DD-MM-YYYY'))
AND h_schdl.SCHEDULE_HEADER_TYPE <> 'AMOUNT'
AND lc_site.customer_id = cust_acct.cust_account_id
AND cust_acct.party_id = party.party_id
);
delete from ar_lc_cust_sites_t
where lc_cust_sites_id IN
(
select lc_cust_sites_id
FROM ar_lc_cust_sites_t lc_site,hz_cust_accounts cust_acct,hz_parties party,ar_charge_schedules c_schdl,ar_charge_schedule_headers_v h_schdl
WHERE lc_site.interest_type = 'CHARGE_PER_TIER'
AND lc_site.interest_schedule_id = c_schdl.schedule_id
AND c_schdl.schedule_id = h_schdl.schedule_id
AND p_fin_charge_date between h_schdl.start_date and nvl(h_schdl.end_date,to_date('31-12-4712','DD-MM-YYYY'))
AND h_schdl.SCHEDULE_HEADER_TYPE <> 'AMOUNT'
AND lc_site.customer_id = cust_acct.cust_account_id
AND cust_acct.party_id = party.party_id
);
PROCEDURE insert_credit_amount(p_fin_charge_date IN DATE,
p_worker_number IN NUMBER,
p_total_workers IN NUMBER) IS
l_fin_charge_date DATE;
debug( 'ar_calc_late_charge.insert_credit_amount()+' );
/* The sum of credit amount is inserted into ar_late_charge_credits_gt.
a) All Unapplied or On Account Receipts and the Open On Account Credit Memos
constitute the credit amount
b) Credits are inserted as positive amounts, for easiness of handling the applications
c) Credits are calculated for a customer_id, site_use_id, currency_code and legal_entity_id
combination
*/
insert into ar_late_charge_credits_gt
(customer_id,
customer_site_use_id,
currency_code,
legal_entity_id,
org_id,
credit_amount)
(
select customer_id,
customer_site_use_id,
currency_code,
legal_entity_id,
org_id,
sum(balance_due)
/* The receipt balance is taken as -1*amount_due_remaining from ar_payment
schedules as receipt date is considered for calculating the balances of
the transaction and not the application date */
from (select cr.pay_from_customer customer_id,
ar_calc_late_charge.get_bill_to_site_use_id(cr.pay_from_customer,
cr.customer_site_use_id,
cr.org_id) customer_site_use_id,
cr.currency_code,
cr.legal_entity_id,
cr.org_id,
-1* ps.amount_due_remaining balance_due
from ar_cash_receipts cr,
ar_payment_schedules ps,
ar_lc_cust_sites_t cust_site,
ar_late_charge_cust_balance_gt bal
where ps.actual_date_closed > l_fin_charge_date
and ps.class ='PMT'
and ps.trx_date <= l_fin_charge_date
and nvl(ps.last_charge_date,l_fin_charge_date-1) < l_fin_charge_date
and ps.customer_id = cust_site.customer_id
and ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id) = cust_site.customer_site_use_id
and ps.invoice_currency_code = cust_site.currency_code
and ps.org_id = cust_site.org_id
and cr.pay_from_customer = cust_site.customer_id
and cr.currency_code = cust_site.currency_code
and cr.org_id = cust_site.org_id
and NVL(cust_site.credit_items_flag,'N') = 'Y'
and cust_site.late_charge_type in ('ADJ','DM')
/* Apply Customer Level tolerances */
and cust_site.lc_cust_sites_id = bal.late_charge_cust_sites_id
and cust_site.org_id = bal.org_id
and decode(cust_site.min_fc_balance_overdue_type,
'PERCENT',(nvl(cust_site.min_fc_balance_percent,0)
* nvl(bal.customer_open_balance,0)/100),
'AMOUNT',nvl(cust_site.min_fc_balance_amount,0),
0) <= nvl(bal.customer_overdue_balance,0)
and cr.receipt_date < l_fin_charge_date
and cr.cash_receipt_id = ps.cash_receipt_id
and cr.org_id = ps.org_id
and cr.cash_receipt_id = ps.cash_receipt_id
and nvl(ps.receipt_confirmed_flag,'Y') ='Y'
and decode(cust_site.hold_charged_invoices_flag,
'Y', decode(ps.last_charge_date,
NULL,'Y','N'),
'N') = 'N'
UNION ALL
select ps.customer_id,
ps.customer_site_use_id,
ps.invoice_currency_code,
trx.legal_entity_id,
ps.org_id,
/* Always get the true balance of the CM as of the finance charge date. For that,
p_charge_on_finance_charge_flag is passed as Y */
-1*ar_calc_late_charge.get_balance_as_of(ps.payment_schedule_id,
l_fin_charge_date,
'CM',
'Y') balance_due
from ar_payment_schedules ps,
ra_customer_trx trx,
ra_cust_trx_types types,
ar_lc_cust_sites_t cust_site,
ar_late_charge_cust_balance_gt bal
where ps.customer_id = cust_site.customer_id
and ps.customer_site_use_id = cust_site.customer_site_use_id
and ps.invoice_currency_code = cust_site.currency_code
and mod(nvl(cust_site.customer_site_use_id,0),l_total_workers) =
decode(l_total_workers,l_worker_number,0,l_worker_number)
and ps.org_id = cust_site.org_id
and ps.actual_date_closed > l_fin_charge_date
and ps.class = 'CM'
and ps.trx_date <= l_fin_charge_date
and nvl(ps.last_charge_date,l_fin_charge_date-1) < l_fin_charge_date
and cust_site.late_charge_type in ('ADJ','DM')
/* Apply Customer Level tolerances */
and cust_site.lc_cust_sites_id = bal.late_charge_cust_sites_id
and cust_site.org_id = bal.org_id
and decode(cust_site.min_fc_balance_overdue_type,
'PERCENT',(nvl(cust_site.min_fc_balance_percent,0)
* nvl(bal.customer_open_balance,0)/100),
'AMOUNT',nvl(cust_site.min_fc_balance_amount,0),
0) <= nvl(bal.customer_overdue_balance,0)
and decode(cust_site.disputed_transactions_flag,'N',
decode(nvl(ps.amount_in_dispute,0), 0, 'Y','N'),'Y' ) = 'Y'
and NVL(cust_site.credit_items_flag,'N') = 'Y'
and decode(cust_site.hold_charged_invoices_flag,
'Y', decode(ps.last_charge_date,
NULL,'Y','N'),
'N') = 'N'
and trx.customer_trx_id = ps.customer_trx_id
and trx.org_id = ps.org_id
and nvl(trx.finance_charges,'Y') = 'Y'
and types.cust_trx_type_id = ps.cust_trx_type_id
and types.org_id = ps.org_id
and nvl(types.exclude_from_late_charges,'N') <> 'Y'
)
group by customer_id,
customer_site_use_id,
currency_code,
legal_entity_id,
org_id);
debug( 'ar_calc_late_charge.insert_credit_amount()-' );
debug('EXCEPTION: ar_calc_late_charge.insert_credit_amount' );
END insert_credit_amount;
| PROCEDURE insert_int_overdue_adj_dm |
| |
| DESCRIPTION |
| |
| This procedure calculates the overdue balance of the debit items and applies those |
| against the credit items in the order of the due_date. The Interest Amount is then |
| calculated on the remaining amount of those debit items and inserted into |
| ar_late_charge_trx_t |
| |
| PSEUDO CODE/LOGIC |
| |
| a) Get the overdue balances of the debit items as sum of |
| i) amount_due_remaining from ar_payment_schedules |
| ii) amount_applied + discount from ar_receivable_applications after the |
| finance charge date. Note that the trx_date of the credit items is considered |
| for determining this as compared to the application date |
| iii) amount_adjusted from ar_adjustments after the finance charge date |
| b) If simple / flat interest has to be computed, the finance charge computed before |
| finance charge date has to be deducted from the above amount. |
| c) From the above computed balance, the debit items are adjusted against the credit |
| amount in the order of the due date. If two debit items have the same due date, the |
| debit items are ordered in the order of their payment schedule ids |
| |
| PARAMETERS |
| |
| |
| KNOWN ISSUES |
| |
| NOTES |
| |
| Original_balance : This is the balance as of the finance charge date |
| Overdue_late_pay_amount : This is the amount on which finance charge is computed. |
| This could be different from original balance as the credits|
| could have been adjusted against this |
| |
| MODIFICATION HISTORY |
| Date Author Description of Changes |
| 15-FEB-2006 rkader Created |
| 19-JUL-2006 rkader Bug fix 5290709 : Credit items are also |
| selected with display_flag N. So the ordering |
| should be such that, the credit items will come |
| last. Debit items with positive sign should come|
| before the debit items with negative sign |
| |
*=========================================================================================*/
PROCEDURE insert_int_overdue_adj_dm(p_fin_charge_date IN DATE,
p_worker_number IN NUMBER,
p_total_workers IN NUMBER) IS
l_fin_charge_date DATE;
debug( 'ar_calc_late_charge.insert_int_overdue_adj_dm()+' );
insert into ar_late_charge_trx_t
(late_charge_trx_id,
customer_id,
customer_site_use_id,
currency_code,
customer_trx_id,
legal_entity_id,
payment_schedule_id,
class,
amount_due_original,
amount_due_remaining,
fin_charge_charged,
trx_date,
cust_trx_type_id,
last_charge_date,
exchange_rate_type,
min_interest_charge,
max_interest_charge,
overdue_late_pay_amount,
original_balance,
due_date,
receipt_date,
finance_charge_date,
charge_type,
actual_date_closed,
interest_rate,
interest_days,
rate_start_date,
rate_end_date,
schedule_days_start,
schedule_days_to,
late_charge_amount,
late_charge_type,
late_charge_term_id,
interest_period_days,
interest_calculation_period,
charge_on_finance_charge_flag,
message_text_id,
interest_type,
min_fc_invoice_overdue_type,
min_fc_invoice_amount,
min_fc_invoice_percent,
charge_line_type,
org_id,
request_id,
display_flag)
( select
ar_late_charge_trx_s.nextval,
c.customer_id,
c.customer_site_use_id ,
c.invoice_currency_code,
c.customer_trx_id,
c.legal_entity_id,
c.payment_schedule_id,
c.class ,
c.amount_due_original,
c.amount_due_remaining ,
c.fin_charge_charged,
c.trx_date,
c.cust_trx_type_id,
c.last_charge_date,
c.exchange_rate_type,
c.min_interest_charge,
c.max_interest_charge,
decode(c.class,
'CM',0,
'PMT',0,
decode(dense_rank() over(partition by c.customer_id,
c.customer_site_use_id,
c.invoice_currency_code,
c.legal_entity_id,
c.org_id
order by decode(c.class,
'PMT',99,
'CM',99,
decode(sign(c.overdue_amt),+1,-1,1)),
c.balance_rtotal), 1, c.balance_rtotal,
2, decode(sign(c.balance_rtotal-c.overdue_amt),
+1, c.overdue_amt, c.balance_rtotal),
c.overdue_amt)) overdue_amount,
c.original_balance,
c.due_date,
NULL receipt_date,
c.fin_charge_date,
c.charge_type,
c.actual_date_closed,
decode(c.interest_type,
'CHARGES_SCHEDULE',sched_lines.rate,
'FIXED_RATE',c.interest_rate, NULL) interest_rate,
least(decode(c.multiple_interest_rates_flag,
'Y',decode(sched_hdrs.schedule_header_type,
'RATE',
nvl(sched_hdrs.end_date,c.eff_fin_charge_date),
c.eff_fin_charge_date),
c.eff_fin_charge_date),c.eff_fin_charge_date) -
greatest(decode(c.multiple_interest_rates_flag,
'Y',decode(sched_hdrs.schedule_header_type,
'RATE',sched_hdrs.start_date-1,c.eff_due_date),
c.eff_due_date), c.eff_due_date,c.eff_last_charge_date) interest_days,
sched_hdrs.start_date rate_start_date,
sched_hdrs.end_date rate_end_date ,
bucket_lines.days_start schedule_days_start,
bucket_lines.days_to schedule_days_to ,
decode(c.class, 'PMT',0,'CM',0,
decode(decode(dense_rank()
over(partition by c.customer_id,
c.customer_site_use_id,
c.invoice_currency_code,
c.legal_entity_id,
c.org_id
order by c.balance_rtotal),
1, c.balance_rtotal,
2, decode(sign(c.balance_rtotal-c.overdue_amt),
+1, c.overdue_amt, c.balance_rtotal),
c.overdue_amt),
0,0,
decode(c.interest_type,'FIXED_AMOUNT',decode(c.class,'INV',decode(sign(c.original_balance),-1,0,c.interest_fixed_amount),c.interest_fixed_amount), /*Bug 8559863*/
'CHARGE_PER_TIER', sched_lines.amount, /*Late Charge Charge per tier Enhacement 6469663*/
decode(sched_hdrs.schedule_header_type,'AMOUNT', sched_lines.amount,
ar_calc_late_charge.calculate_interest(
decode(dense_rank()
over(partition by c.customer_id,
c.customer_site_use_id,
c.invoice_currency_code,
c.legal_entity_id,
c.org_id
order by decode(c.class,
'PMT',99,
'CM',99,
decode(sign(c.overdue_amt),+1,-1,1)),c.balance_rtotal),
1, c.balance_rtotal,
2, decode(sign(c.balance_rtotal-c.overdue_amt),
+1, c.overdue_amt, c.balance_rtotal),
c.overdue_amt),
c.charge_on_finance_charge_flag,
least(decode(c.multiple_interest_rates_flag,
'Y',decode(sched_hdrs.schedule_header_type,
'RATE',
nvl(sched_hdrs.end_date,c.eff_fin_charge_date),
c.eff_fin_charge_date),
c.eff_fin_charge_date),c.eff_fin_charge_date) -
greatest(decode(c.multiple_interest_rates_flag,
'Y',decode(sched_hdrs.schedule_header_type,
'RATE',sched_hdrs.start_date-1,
c.eff_due_date),
c.eff_due_date), c.eff_due_date,c.eff_last_charge_date),
decode(c.interest_type,
'CHARGES_SCHEDULE',sched_lines.rate,
'FIXED_RATE',c.interest_rate, NULL),
c.interest_period_days,
c.invoice_currency_code,
c.payment_schedule_id))))) late_charge_amount,
c.late_charge_type,
c.late_charge_term_id,
c.interest_period_days,
c.interest_calculation_period,
c.charge_on_finance_charge_flag,
c.message_text_id,
c.interest_type,
c.min_fc_invoice_overdue_type,
c.min_fc_invoice_amount,
c.min_fc_invoice_percent,
'INTEREST',
c.org_id,
l_request_id,
decode(c.class,
'PMT','N',
'CM','N',
decode(decode(dense_rank() over(partition by c.customer_id,
c.customer_site_use_id,
c.invoice_currency_code,
c.legal_entity_id,
c.org_id
order by decode(c.class,
'PMT',99,
'CM',99,
decode(sign(c.overdue_amt),+1,-1,1)),
c.balance_rtotal), 1, c.balance_rtotal,
2, decode(sign(c.balance_rtotal-c.overdue_amt),
+1, c.overdue_amt, c.balance_rtotal),
c.overdue_amt) ,
0,'N','Y')) display_flag
from
(select
b.customer_id,
b.customer_site_use_id ,
b.invoice_currency_code,
b.customer_trx_id,
b.legal_entity_id,
b.payment_schedule_id,
b.class ,
b.amount_due_original,
b.amount_due_remaining ,
b.fin_charge_charged,
b.trx_date,
b.cust_trx_type_id,
nvl(b.last_charge_date,
decode(b.fin_charge_charged,
0,NULL,
b.last_accrue_charge_date)) last_charge_date,
b.exchange_rate_type,
b.min_interest_charge,
b.max_interest_charge,
b.overdue_amt,
b.original_balance,
b.due_date,
b.fin_charge_date,
b.charge_type,
b.actual_date_closed,
b.late_charge_type,
b.late_charge_term_id,
b.interest_period_days,
b.interest_calculation_period,
b.charge_on_finance_charge_flag,
b.message_text_id,
nvl(credits.credit_amount,0) credit_amount,
b.interest_type,
b.min_fc_invoice_overdue_type,
b.min_fc_invoice_amount,
b.min_fc_invoice_percent,
b.interest_rate,
b.interest_schedule_id,
b.multiple_interest_rates_flag,
b.interest_fixed_amount,
b.org_id,
decode(b.interest_calculation_period,
'DAILY',l_fin_charge_date,
'MONTHLY',last_day(l_fin_charge_date)) eff_fin_charge_date,
decode(b.interest_calculation_period,
'DAILY',nvl(b.last_charge_date,
decode(b.fin_charge_charged,
0,b.due_date,
b.last_accrue_charge_date)),
'MONTHLY',first_day(nvl(b.last_charge_date,
decode(b.fin_charge_charged,
0,b.due_date,
b.last_accrue_charge_date)))) eff_last_charge_date,
decode(b.interest_calculation_period,
'DAILY',b.due_date,
'MONTHLY',first_day(b.due_date)) eff_due_date,
decode(sign(nvl(credits.credit_amount,0) - sum(b.overdue_amt)
over(partition by b.customer_id,
b.customer_site_use_id,
b.invoice_currency_code,
b.legal_entity_id,
b.org_id
order by decode(b.class,
'PMT',99,
'CM',99,
decode(sign(b.overdue_amt),+1,-1,1)),
b.due_date, b.payment_schedule_id)),+1,0,0,0,
(sum(b.overdue_amt)
over(partition by b.customer_id,
b.customer_site_use_id,
b.invoice_currency_code,
b.legal_entity_id,
b.org_id
order by decode(b.class,
'PMT',99,
'CM',99,
decode(sign(b.overdue_amt),+1,-1,1)),
b.due_date, b.payment_schedule_id) - nvl(credits.credit_amount,0))) balance_rtotal
from (
select
a.customer_id,
a.customer_site_use_id ,
a.invoice_currency_code,
a.customer_trx_id,
nvl(trx.legal_entity_id,cr.legal_entity_id) legal_entity_id,
a.payment_schedule_id,
a.class ,
a.amount_due_original,
a.amount_due_remaining ,
sum(a.fin_charge_charged) fin_charge_charged,
a.trx_date,
a.cust_trx_type_id,
a.last_charge_date,
a.last_accrue_charge_date,
a.exchange_rate_type,
a.min_interest_charge,
a.max_interest_charge,
sum(decode(a.charge_on_finance_charge_flag,'Y', a.overdue_amt,
a.overdue_amt- a.fin_charge_charged)) overdue_amt,
sum(a.overdue_amt) original_balance,
a.due_date,
a.fin_charge_date,
a.charge_type,
a.actual_date_closed,
a.late_charge_type,
a.late_charge_term_id,
a.interest_period_days,
a.interest_calculation_period,
a.charge_on_finance_charge_flag,
a.message_text_id,
--credits.credit_amount,
a.interest_type,
a.min_fc_invoice_overdue_type,
a.min_fc_invoice_amount,
a.min_fc_invoice_percent,
a.interest_rate,
a.interest_schedule_id,
a.multiple_interest_rates_flag,
a.hold_charged_invoices_flag,
a.interest_fixed_amount,
a.org_id
from (
select
ps.customer_id,
decode(ps.class,
'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
ps.customer_site_use_id) customer_site_use_id ,
ps.invoice_currency_code,
ps.customer_trx_id,
ps.payment_schedule_id,
ps.class ,
ps.amount_due_original,
ps.amount_due_remaining,
sum(case when adj.apply_date > l_fin_charge_date
then adj.amount*-1 else 0 end ) overdue_amt,
sum(case when adj.apply_date <= l_fin_charge_date then
case when adj.type ='CHARGES' then
adj.amount else 0 end
else 0 end) fin_charge_charged,
ps.trx_date,
ps.cust_trx_type_id,
ps.last_charge_date,
cust_site.exchange_rate,
cust_site.exchange_rate_type,
cust_site.min_interest_charge,
cust_site.max_interest_charge,
ps.due_date,
l_fin_charge_date fin_charge_date,
cust_site.late_charge_type,
cust_site.late_charge_term_id ,
cust_site.interest_period_days,
cust_site.interest_calculation_period,
cust_site.charge_on_finance_charge_flag,
cust_site.message_text_id,
ps.actual_date_closed,
cust_site.last_accrue_charge_date,
cust_site.interest_type,
cust_site.interest_rate,
cust_site.interest_fixed_amount,
cust_site.min_fc_invoice_overdue_type,
cust_site.min_fc_invoice_amount,
cust_site.min_fc_invoice_percent,
cust_site.interest_schedule_id interest_schedule_id,
cust_site.multiple_interest_rates_flag,
cust_site.hold_charged_invoices_flag,
ps.org_id,
ps.cash_receipt_id,
'OVERDUE' charge_type
from ar_payment_schedules ps,
ar_adjustments adj,
ar_lc_cust_sites_t cust_site,
ar_late_charge_cust_balance_gt bal
where ps.customer_id = cust_site.customer_id
and cust_site.customer_site_use_id = decode(ps.class,
'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
ps.customer_site_use_id)
and ps.invoice_currency_code = cust_site.currency_code
and mod(nvl(cust_site.customer_site_use_id,0),l_total_workers) =
decode(l_total_workers,l_worker_number,0,l_worker_number)
and ps.org_id = cust_site.org_id
and ps.actual_date_closed > l_fin_charge_date
and cust_site.late_charge_calculation_trx in ('OVERDUE_LATE','OVERDUE')
and cust_site.late_charge_type in ('ADJ','DM')
/* Apply Customer Level tolerances */
and cust_site.lc_cust_sites_id = bal.late_charge_cust_sites_id
and cust_site.org_id = bal.org_id
and decode(cust_site.min_fc_balance_overdue_type,
'PERCENT',(nvl(cust_site.min_fc_balance_percent,0)
* nvl(bal.customer_open_balance,0)/100),
'AMOUNT',nvl(cust_site.min_fc_balance_amount,0),
nvl(bal.customer_overdue_balance,0)) <= nvl(bal.customer_overdue_balance,0)
and decode(nvl(cust_site.disputed_transactions_flag,'N'),
'N',decode(nvl(ps.amount_in_dispute,0),
0, 'Y','N'),
'Y' ) = 'Y'
and decode(nvl(cust_site.credit_items_flag,'N'),
'N',decode(ps.class,'INV',decode(sign(ps.amount_due_original),-1,'N','Y'),'Y'),
'Y' ) = 'Y' /*Bug8559863*/
--and ps.class not in ('CM','PMT')
and ps.due_date < (l_fin_charge_date - nvl(cust_site.payment_grace_days,0))
and nvl(ps.last_charge_date,l_fin_charge_date-1) < l_fin_charge_date
and nvl(ps.receipt_confirmed_flag, 'Y') = 'Y'
and nvl(cust_site.charge_begin_date,ps.due_date) <= ps.due_date
and adj.payment_schedule_id = ps.payment_schedule_id
and adj.status = 'A'
group by
ps.customer_id,
decode(ps.class,
'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
ps.customer_site_use_id),
ps.invoice_currency_code,
ps.customer_trx_id,
ps.payment_schedule_id,
ps.class ,
ps.amount_due_original,
ps.amount_due_remaining,
ps.trx_date,
ps.cust_trx_type_id,
ps.last_charge_date,
cust_site.exchange_rate,
cust_site.exchange_rate_type,
cust_site.min_interest_charge,
cust_site.max_interest_charge,
ps.due_date,
l_fin_charge_date ,
cust_site.late_charge_type,
cust_site.late_charge_term_id ,
cust_site.interest_period_days,
cust_site.interest_calculation_period,
cust_site.charge_on_finance_charge_flag,
cust_site.message_text_id,
ps.actual_date_closed,
cust_site.interest_type,
cust_site.interest_rate,
cust_site.interest_fixed_amount,
cust_site.min_fc_invoice_overdue_type,
cust_site.min_fc_invoice_amount,
cust_site.min_fc_invoice_percent,
cust_site.interest_schedule_id,
cust_site.multiple_interest_rates_flag,
cust_site.hold_charged_invoices_flag,
ps.org_id,
ps.cash_receipt_id,
cust_site.last_accrue_charge_date
union all
select ps.customer_id,
decode(ps.class,
'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
ps.customer_site_use_id) customer_site_use_id,
ps.invoice_currency_code,
ps.customer_trx_id,
ps.payment_schedule_id,
ps.class ,
ps.amount_due_original,
ps.amount_due_remaining,
sum(app.amount_applied + nvl(app.earned_discount_taken,0)
+ nvl(app.unearned_discount_taken,0)) overdue_amt,
0 fin_charge_charged,
ps.trx_date,
ps.cust_trx_type_id,
ps.last_charge_date,
cust_site.exchange_rate,
cust_site.exchange_rate_type,
cust_site.min_interest_charge,
cust_site.max_interest_charge,
ps.due_date,
l_fin_charge_date ,
cust_site.late_charge_type,
cust_site.late_charge_term_id ,
cust_site.interest_period_days,
cust_site.interest_calculation_period,
cust_site.charge_on_finance_charge_flag,
cust_site.message_text_id,
ps.actual_date_closed,
cust_site.last_accrue_charge_date ,
cust_site.interest_type,
cust_site.interest_rate,
cust_site.interest_fixed_amount,
cust_site.min_fc_invoice_overdue_type,
cust_site.min_fc_invoice_amount,
cust_site.min_fc_invoice_percent,
cust_site.interest_schedule_id,
cust_site.multiple_interest_rates_flag,
cust_site.hold_charged_invoices_flag,
ps.org_id,
ps.cash_receipt_id,
'OVERDUE' charge_type
from ar_payment_schedules ps,
ar_receivable_applications app,
ar_payment_schedules ps_cm_cr,
ar_lc_cust_sites_t cust_site,
ar_late_charge_cust_balance_gt bal
where ps.customer_id = cust_site.customer_id
and cust_site.customer_site_use_id = decode(ps.class,
'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
ps.customer_site_use_id)
and ps.invoice_currency_code = cust_site.currency_code
and mod(nvl(cust_site.customer_site_use_id,0),l_total_workers) =
decode(l_total_workers,l_worker_number,0,l_worker_number)
and ps.org_id = cust_site.org_id
and ps.actual_date_closed > l_fin_charge_date
and cust_site.late_charge_calculation_trx in ('OVERDUE_LATE','OVERDUE')
and cust_site.late_charge_type in ('ADJ','DM')
/* Apply Customer Level tolerances */
and cust_site.lc_cust_sites_id = bal.late_charge_cust_sites_id
and cust_site.org_id = bal.org_id
and decode(cust_site.min_fc_balance_overdue_type,
'PERCENT',(nvl(cust_site.min_fc_balance_percent,0)
* nvl(bal.customer_open_balance,0)/100),
'AMOUNT',nvl(cust_site.min_fc_balance_amount,0),
nvl(bal.customer_overdue_balance,0)) <= nvl(bal.customer_overdue_balance,0)
and decode(nvl(cust_site.disputed_transactions_flag,'N'),
'N',decode(nvl(ps.amount_in_dispute,0),
0, 'Y','N'),
'Y' ) = 'Y'
and decode(nvl(cust_site.credit_items_flag,'N'),
'N',decode(ps.class,'INV',decode(sign(ps.amount_due_original),-1,'N','Y'),'Y'),
'Y' ) = 'Y' /*Bug8559863*/
and ps.due_date < (l_fin_charge_date - nvl(cust_site.payment_grace_days,0))
and nvl(ps.last_charge_date,l_fin_charge_date-1) < l_fin_charge_date
and nvl(ps.receipt_confirmed_flag, 'Y') = 'Y'
and app.applied_payment_schedule_id = ps.payment_schedule_id
--and ps.class not in ('CM','PMT')
and nvl(cust_site.charge_begin_date,ps.due_date) <= ps.due_date
and app.status = 'APP'
and nvl( app.confirmed_flag, 'Y' ) = 'Y'
/* The receipt or Credit Memo date should be considered for applications */
and ps_cm_cr.payment_schedule_id = app.payment_schedule_id
and ps_cm_cr.trx_date > l_fin_charge_date
group by ps.customer_id,
decode(ps.class,
'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
ps.customer_site_use_id),
ps.invoice_currency_code,
ps.customer_trx_id,
ps.payment_schedule_id,
ps.class ,
ps.amount_due_original,
ps.amount_due_remaining,
ps.trx_date,
ps.cust_trx_type_id,
ps.last_charge_date,
cust_site.exchange_rate,
cust_site.exchange_rate_type,
cust_site.min_interest_charge,
cust_site.max_interest_charge,
ps.due_date,
l_fin_charge_date ,
cust_site.late_charge_type,
cust_site.late_charge_term_id ,
cust_site.interest_period_days,
cust_site.interest_calculation_period,
cust_site.charge_on_finance_charge_flag,
cust_site.message_text_id,
ps.actual_date_closed ,
cust_site.interest_type,
cust_site.interest_rate,
cust_site.interest_fixed_amount,
cust_site.min_fc_invoice_overdue_type,
cust_site.min_fc_invoice_amount,
cust_site.min_fc_invoice_percent,
cust_site.interest_schedule_id,
cust_site.multiple_interest_rates_flag,
cust_site.hold_charged_invoices_flag,
ps.org_id,
ps.cash_receipt_id,
cust_site.last_accrue_charge_date
UNION ALL
select ps.customer_id,
decode(ps.class,
'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
ps.customer_site_use_id) customer_site_use_id,
ps.invoice_currency_code,
ps.customer_trx_id,
ps.payment_schedule_id,
ps.class ,
ps.amount_due_original,
ps.amount_due_remaining,
ps.amount_due_remaining overdue_amt,
0 fin_charge_charged,
ps.trx_date,
ps.cust_trx_type_id,
ps.last_charge_date,
cust_site.exchange_rate,
cust_site.exchange_rate_type,
cust_site.min_interest_charge,
cust_site.max_interest_charge,
ps.due_date,
l_fin_charge_date ,
cust_site.late_charge_type,
cust_site.late_charge_term_id ,
cust_site.interest_period_days,
cust_site.interest_calculation_period,
cust_site.charge_on_finance_charge_flag,
cust_site.message_text_id,
ps.actual_date_closed,
cust_site.last_accrue_charge_date ,
cust_site.interest_type,
cust_site.interest_rate,
cust_site.interest_fixed_amount,
cust_site.min_fc_invoice_overdue_type,
cust_site.min_fc_invoice_amount,
cust_site.min_fc_invoice_percent,
cust_site.interest_schedule_id,
cust_site.multiple_interest_rates_flag,
cust_site.hold_charged_invoices_flag,
ps.org_id,
ps.cash_receipt_id,
'OVERDUE' charge_type
from ar_payment_schedules ps,
ar_lc_cust_sites_t cust_site,
ar_late_charge_cust_balance_gt bal
where ps.customer_id = cust_site.customer_id
and cust_site.customer_site_use_id = decode(ps.class,
'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
ps.customer_site_use_id)
and ps.invoice_currency_code = cust_site.currency_code
and mod(nvl(cust_site.customer_site_use_id,0),l_total_workers) =
decode(l_total_workers,l_worker_number,0,l_worker_number)
and ps.org_id = cust_site.org_id
and ps.actual_date_closed > l_fin_charge_date
and cust_site.late_charge_calculation_trx in ('OVERDUE_LATE','OVERDUE')
and cust_site.late_charge_type in ('ADJ','DM')
/* Apply Customer Level tolerances */
and cust_site.lc_cust_sites_id = bal.late_charge_cust_sites_id
and cust_site.org_id = bal.org_id
and decode(cust_site.min_fc_balance_overdue_type,
'PERCENT',(nvl(cust_site.min_fc_balance_percent,0)
* nvl(bal.customer_open_balance,0)/100),
'AMOUNT',nvl(cust_site.min_fc_balance_amount,0),
nvl(bal.customer_overdue_balance,0)) <= nvl(bal.customer_overdue_balance,0)
and decode(nvl(cust_site.disputed_transactions_flag,'N'),
'N',decode(nvl(ps.amount_in_dispute,0),
0, 'Y','N'),
'Y' ) = 'Y'
and decode(nvl(cust_site.credit_items_flag,'N'),
'N',decode(ps.class,'INV',decode(sign(ps.amount_due_original),-1,'N','Y'),'Y'),
'Y' ) = 'Y' /*Bug8559863*/
--and ps.class not in ('PMT','CM')
and nvl(cust_site.charge_begin_date,ps.due_date) <= ps.due_date
and ps.due_date < (l_fin_charge_date - nvl(cust_site.payment_grace_days,0))
and nvl(ps.last_charge_date,l_fin_charge_date-1) < l_fin_charge_date
and nvl(ps.receipt_confirmed_flag, 'Y') = 'Y') a,
ra_customer_trx trx,
ar_transaction_history th,
ra_cust_trx_types types,
ar_cash_receipts cr
where trx.customer_trx_id(+) = a.customer_trx_id
and nvl(trx.finance_charges,decode(a.class,'DEP','N','Y')) = 'Y'
and a.customer_trx_id = th.customer_trx_id(+)
and nvl(th.current_record_flag,'Y') = 'Y'
and nvl(th.status,'*') not in ('PROTESTED','MATURED_PEND_RISK_ELIMINATION','CLOSED', 'CANCELLED')
and types.cust_trx_type_id(+) = a.cust_trx_type_id
and types.org_id(+) = a.org_id
and nvl(types.exclude_from_late_charges,'N') <> 'Y'
and cr.cash_receipt_id(+) = a.cash_receipt_id
group by
a.customer_id,
a.customer_site_use_id ,
a.invoice_currency_code,
a.customer_trx_id,
nvl(trx.legal_entity_id,cr.legal_entity_id),
a.payment_schedule_id,
a.class,
a.amount_due_original,
a.amount_due_remaining ,
a.trx_date,
a.cust_trx_type_id,
a.last_charge_date,
a.last_accrue_charge_date,
a.exchange_rate_type,
a.min_interest_charge,
a.max_interest_charge,
a.due_date,
a.fin_charge_date,
a.charge_type,
a.actual_date_closed,
a.late_charge_type,
a.late_charge_term_id,
a.interest_period_days,
a.interest_calculation_period,
a.charge_on_finance_charge_flag,
a.message_text_id,
--credits.credit_amount,
a.interest_type,
a.min_fc_invoice_overdue_type,
a.min_fc_invoice_amount,
a.min_fc_invoice_percent,
a.interest_rate,
a.interest_schedule_id,
a.multiple_interest_rates_flag,
a.hold_charged_invoices_flag,
a.org_id,
a.interest_fixed_amount)b,
ar_late_charge_credits_gt credits
where decode(b.hold_charged_invoices_flag,
'Y',decode(b.last_charge_date,
NULL,b.fin_charge_charged,1),
0) = 0
and b.customer_id = credits.customer_id(+)
and b.customer_site_use_id = credits.customer_site_use_id (+)
and b.invoice_currency_code = credits.currency_code (+)
and b.org_id = credits.org_id (+)
and b.legal_entity_id = credits.legal_entity_id(+))c,
ar_charge_schedule_hdrs sched_hdrs,
ar_charge_schedule_lines sched_lines,
ar_aging_bucket_lines bucket_lines
where c.interest_schedule_id = sched_hdrs.schedule_id(+)
and sched_hdrs.schedule_header_id = sched_lines.schedule_header_id(+)
and sched_hdrs.schedule_id = sched_lines.schedule_id(+)
and nvl(sched_hdrs.status,'A') = 'A'
and sched_lines.aging_bucket_id = bucket_lines.aging_bucket_id(+)
and sched_lines.aging_bucket_line_id = bucket_lines.aging_bucket_line_id(+)
/* Condition 1: days late should be between the bucket lines start and end days */
and (l_fin_charge_date- c.due_date) >= nvl(bucket_lines.days_start,(l_fin_charge_date- c.due_date))
and (l_fin_charge_date - c.due_date) <= nvl(bucket_lines.days_to,(l_fin_charge_date- c.due_date))
/* Condition 2: Start_date of the schedule should be less than or equal to the
finance charge date */
and nvl(sched_hdrs.start_date,l_fin_charge_date) <= l_fin_charge_date
/* condition 3:
If multiple interest rates have to be used, end date of the schedule should be greater than
or equal to the due date or the date from which we are calculating the charge
Otherwise, the end_date should either be null or it should be greater than the
due_date
*/
and (decode(c.multiple_interest_rates_flag,'Y',
decode(sched_hdrs.schedule_header_type,
'RATE',greatest(c.due_date,nvl(c.last_charge_date,c.due_date)),
c.due_date),
c.due_date) <= sched_hdrs.end_date
OR sched_hdrs.end_date IS NULL )
/* Condition 4: If multiple rates need not be used, we should pick up the rate
that is effective on the due date.
Also note that the multiple interest rates are used only for Interest
Calculation and only when rates are used*/
and decode(c.multiple_interest_rates_flag,'Y',
decode(sched_hdrs.schedule_header_type,
'RATE',sched_hdrs.start_date,
c.due_date),
c.due_date)>= nvl(sched_hdrs.start_date,c.due_date)
/* Make sure that this payment schedule is not part of a failed final batch */
and not exists (select payment_schedule_id
from ar_interest_lines lines,
ar_interest_headers hdrs,
ar_interest_batches bat
where lines.payment_schedule_id = c.payment_schedule_id
and lines.interest_header_id = hdrs.interest_header_id
and hdrs.interest_batch_id = bat.interest_batch_id
and bat.batch_status ='F'
and bat.transferred_status <> 'S'));
debug( 'ar_calc_late_charge.insert_int_overdue_adj_dm()-' );
debug('EXCEPTION: ar_calc_late_charge.insert_int_overdue_adj_dm' );
END insert_int_overdue_adj_dm;
| PROCEDURE insert_int_overdue_inv |
| |
| DESCRIPTION |
| |
| This procedure calculates the overdue balance of the debit and credit items. The |
| Interest Amount is then calculated on the overdue balance and inserted into |
| ar_late_charge_trx_t |
| |
| PSEUDO CODE/LOGIC |
| |
| a) Get the overdue balances of the items as sum of |
| i) amount_due_remaining from ar_payment_schedules |
| ii) amount_applied + discount from ar_receivable_applications after the |
| finance charge date. The data on which the credit item is created is used |
| instead of the application date |
| iii) amount_adjusted from ar_adjustments after the finance charge date |
| b) If simple / flat interest has to be computed, the finance charge computed before |
| finance charge date has to be deducted from the above amount |
| c) In this case, the Credit items are treated similar to Debit Items. Interest is |
| calculated on the credit items as done for debit items. |
| |
| PARAMETERS |
| |
| |
| KNOWN ISSUES |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| Date Author Description of Changes |
| 15-FEB-2006 rkader Created |
| |
*=========================================================================================*/
PROCEDURE insert_int_overdue_inv(p_fin_charge_date IN DATE,
p_worker_number IN NUMBER,
p_total_workers IN NUMBER) IS
l_fin_charge_date DATE;
debug( 'ar_calc_late_charge.insert_int_overdue_inv()+' );
insert into ar_late_charge_trx_t
(late_charge_trx_id,
customer_id,
customer_site_use_id,
currency_code,
customer_trx_id,
legal_entity_id,
payment_schedule_id,
class,
amount_due_original,
amount_due_remaining,
fin_charge_charged,
trx_date,
cust_trx_type_id,
last_charge_date,
exchange_rate_type,
min_interest_charge,
max_interest_charge,
overdue_late_pay_amount,
original_balance,
due_date,
receipt_date,
finance_charge_date,
charge_type,
actual_date_closed,
interest_rate,
interest_days,
rate_start_date,
rate_end_date,
schedule_days_start,
schedule_days_to,
late_charge_amount,
late_charge_type,
late_charge_term_id,
interest_period_days,
interest_calculation_period,
charge_on_finance_charge_flag,
message_text_id,
interest_type,
min_fc_invoice_overdue_type,
min_fc_invoice_amount,
min_fc_invoice_percent,
charge_line_type,
org_id,
request_id,
display_flag )
(
select ar_late_charge_trx_s.nextval,
b.customer_id,
b.customer_site_use_id ,
b.invoice_currency_code,
b.customer_trx_id,
b.legal_entity_id,
b.payment_schedule_id,
b.class ,
b.amount_due_original,
b.amount_due_remaining ,
b.fin_charge_charged,
b.trx_date,
b.cust_trx_type_id,
NVL(b.last_charge_date, decode(b.fin_charge_charged,
0, NULL,
b.last_accrue_charge_date)) last_charge_date,
b.exchange_rate_type,
b.min_interest_charge,
b.max_interest_charge,
b.overdue_amt,
b.original_balance,
b.due_date,
NULL,
b.fin_charge_date,
b.charge_type,
b.actual_date_closed,
decode(b.interest_type,
'CHARGES_SCHEDULE',sched_lines.rate,
'FIXED_RATE',b.interest_rate, NULL) interest_rate,
least(decode(b.multiple_interest_rates_flag,
'Y',decode(sched_hdrs.schedule_header_type,
'RATE',
nvl(sched_hdrs.end_date,b.eff_fin_charge_date),
b.eff_fin_charge_date),
b.eff_fin_charge_date),b.eff_fin_charge_date) -
greatest(decode(b.multiple_interest_rates_flag,
'Y',decode(sched_hdrs.schedule_header_type,
'RATE',sched_hdrs.start_date-1,b.eff_due_date),
b.eff_due_date), b.eff_due_date,b.eff_last_charge_date) interest_days,
sched_hdrs.start_date rate_start_date,
sched_hdrs.end_date rate_end_date,
bucket_lines.days_start schedule_days_start,
bucket_lines.days_to schedule_days_to,
decode(b.interest_type,
'FIXED_AMOUNT',decode(b.class,
'PMT', 0, /* -1* b.interest_fixed_amount,*/
'CM', 0, /* -1 * b.interest_fixed_amount,*/
'INV',decode(sign(b.original_balance),-1,0,b.interest_fixed_amount), /*Bug 8559863 Take 0 late charge for -ve invoices fixed_amount scenario */
b.interest_fixed_amount),
'CHARGE_PER_TIER', sched_lines.amount, /*Late charge case of charge per tier Enhacement 6469663*/
decode(sched_hdrs.schedule_header_type,
'AMOUNT',decode(b.class,
'PMT',-1* sched_lines.amount,
'CM', -1* sched_lines.amount,
sched_lines.amount),
ar_calc_late_charge.calculate_interest(
b.overdue_amt,
b.charge_on_finance_charge_flag,
least(decode(b.multiple_interest_rates_flag,
'Y',decode(sched_hdrs.schedule_header_type,
'RATE',
nvl(sched_hdrs.end_date,
b.eff_fin_charge_date),
b.eff_fin_charge_date),
b.eff_fin_charge_date),b.eff_fin_charge_date) -
greatest(decode(b.multiple_interest_rates_flag,
'Y',decode(sched_hdrs.schedule_header_type,
'RATE',sched_hdrs.start_date-1,
b.eff_due_date),
b.eff_due_date),b.eff_due_date,
b.eff_last_charge_date),
decode(b.interest_type,
'CHARGES_SCHEDULE',sched_lines.rate,
'FIXED_RATE',b.interest_rate, NULL),
b.interest_period_days,
b.invoice_currency_code,
b.payment_schedule_id))) late_charge_amount,
b.late_charge_type,
b.late_charge_term_id,
b.interest_period_days,
b.interest_calculation_period,
b.charge_on_finance_charge_flag,
b.message_text_id,
b.interest_type,
b.min_fc_invoice_overdue_type,
b.min_fc_invoice_amount,
b.min_fc_invoice_percent,
'INTEREST',
b.org_id,
l_request_id,
'Y'
from (
select a.customer_id,
a.customer_site_use_id ,
a.invoice_currency_code,
nvl(a.customer_trx_id, a.cash_receipt_id) customer_trx_id,
nvl(trx.legal_entity_id,cr.legal_entity_id) legal_entity_id,
a.payment_schedule_id,
a.class ,
a.amount_due_original,
a.amount_due_remaining ,
sum(a.fin_charge_charged) fin_charge_charged,
a.trx_date,
a.cust_trx_type_id,
a.last_charge_date,
a.last_accrue_charge_date,
a.exchange_rate_type,
a.min_interest_charge,
a.max_interest_charge,
sum(decode(a.charge_on_finance_charge_flag,'Y', a.overdue_amt,
a.overdue_amt- a.fin_charge_charged)) overdue_amt,
sum(a.overdue_amt) original_balance,
a.due_date,
a.fin_charge_date,
a.charge_type,
a.actual_date_closed,
a.late_charge_type,
a.late_charge_term_id,
a.interest_period_days,
a.interest_calculation_period,
a.charge_on_finance_charge_flag,
a.message_text_id,
a.interest_type,
a.interest_rate,
a.interest_schedule_id,
a.min_fc_invoice_overdue_type,
a.min_fc_invoice_amount,
a.min_fc_invoice_percent,
a.multiple_interest_rates_flag,
a.hold_charged_invoices_flag,
a.org_id,
a.interest_fixed_amount,
decode(a.interest_calculation_period,
'DAILY',l_fin_charge_date,
'MONTHLY',last_day(l_fin_charge_date)) eff_fin_charge_date,
decode(a.interest_calculation_period,
'DAILY',nvl(a.last_charge_date,
decode(a.fin_charge_charged,
0,a.due_date,
a.last_accrue_charge_date)),
'MONTHLY',first_day(nvl(a.last_charge_date,
decode(a.fin_charge_charged,
0,a.due_date,
a.last_accrue_charge_date)))) eff_last_charge_date,
decode(a.interest_calculation_period,
'DAILY',a.due_date,
'MONTHLY',first_day(a.due_date)) eff_due_date
from (
select
ps.customer_id,
decode(ps.class,
'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
'PMT',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
ps.customer_site_use_id) customer_site_use_id,
ps.invoice_currency_code,
ps.customer_trx_id,
ps.payment_schedule_id,
ps.class ,
ps.amount_due_original,
ps.amount_due_remaining,
sum(case when adj.apply_date > l_fin_charge_date
then adj.amount*-1 else 0 end ) overdue_amt,
sum(case when adj.apply_date <= l_fin_charge_date then
case when adj.type ='CHARGES' then
adj.amount else 0 end
else 0 end) fin_charge_charged,
ps.trx_date,
ps.cust_trx_type_id,
ps.last_charge_date,
cust_site.exchange_rate,
cust_site.exchange_rate_type,
cust_site.min_interest_charge,
cust_site.max_interest_charge,
decode(ps.class,'PMT',ps.trx_date,ps.due_date) due_date,
l_fin_charge_date fin_charge_date,
cust_site.late_charge_type,
cust_site.late_charge_term_id,
cust_site.interest_period_days,
cust_site.interest_calculation_period,
cust_site.charge_on_finance_charge_flag,
cust_site.message_text_id,
ps.actual_date_closed,
cust_site.last_accrue_charge_date,
cust_site.interest_type,
cust_site.interest_rate,
cust_site.interest_fixed_amount,
cust_site.interest_schedule_id interest_schedule_id,
cust_site.min_fc_invoice_overdue_type,
cust_site.min_fc_invoice_amount,
cust_site.min_fc_invoice_percent,
cust_site.multiple_interest_rates_flag,
cust_site.hold_charged_invoices_flag,
ps.org_id,
ps.cash_receipt_id,
'OVERDUE' charge_type
from ar_payment_schedules ps,
ar_adjustments adj,
ar_lc_cust_sites_t cust_site,
ar_late_charge_cust_balance_gt bal
where ps.customer_id = cust_site.customer_id
and cust_site.customer_site_use_id = decode(ps.class,
'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
'PMT',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
ps.customer_site_use_id)
and ps.invoice_currency_code = cust_site.currency_code
and mod(nvl(cust_site.customer_site_use_id,0),l_total_workers) =
decode(l_total_workers,l_worker_number,0,l_worker_number)
and ps.org_id = cust_site.org_id
and ps.actual_date_closed > l_fin_charge_date
and cust_site.late_charge_calculation_trx in ('OVERDUE_LATE','OVERDUE')
and cust_site.late_charge_type = 'INV'
/* Apply Customer Level tolerances */
and cust_site.lc_cust_sites_id = bal.late_charge_cust_sites_id
and cust_site.org_id = bal.org_id
and decode(cust_site.min_fc_balance_overdue_type,
'PERCENT',(nvl(cust_site.min_fc_balance_percent,0)
* nvl(bal.customer_open_balance,0)/100),
'AMOUNT',nvl(cust_site.min_fc_balance_amount,0),
nvl(bal.customer_overdue_balance,0)) <= nvl(bal.customer_overdue_balance,0)
and decode(nvl(cust_site.disputed_transactions_flag,'N'),
'N',decode(nvl(ps.amount_in_dispute,0),
0, 'Y','N'),
'Y' ) = 'Y'
/* Added NVL() for cust_site.credit_items_flag as part of Bug 10280458, manishri */
and decode(NVL(cust_site.credit_items_flag, 'N'),'N',
decode (ps.class, 'PMT','N','CM','N','Y'),'Y') = 'Y'
and decode(ps.class,
'PMT', ps.trx_date,
ps.due_date) < decode(ps.class,
'PMT', l_fin_charge_date,
'CM', l_fin_charge_date,
(l_fin_charge_date - nvl(cust_site.payment_grace_days,0)))
and nvl(ps.last_charge_date,l_fin_charge_date-1) < l_fin_charge_date
and nvl(cust_site.charge_begin_date,decode(ps.class,'PMT',ps.trx_date,ps.due_date))
<= decode(ps.class,'PMT',ps.trx_date,ps.due_date)
and nvl(ps.receipt_confirmed_flag, 'Y') = 'Y'
and adj.payment_schedule_id = ps.payment_schedule_id
and adj.status = 'A'
group by
ps.customer_id,
decode(ps.class,
'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
'PMT',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
ps.customer_site_use_id),
ps.invoice_currency_code,
ps.customer_trx_id,
ps.payment_schedule_id,
ps.class ,
ps.amount_due_original,
ps.amount_due_remaining,
ps.trx_date,
ps.cust_trx_type_id,
ps.last_charge_date,
cust_site.exchange_rate,
cust_site.exchange_rate_type,
cust_site.min_interest_charge,
cust_site.max_interest_charge,
decode(ps.class,'PMT',ps.trx_date,ps.due_date),
l_fin_charge_date ,
cust_site.late_charge_type,
cust_site.late_charge_term_id ,
cust_site.interest_period_days,
cust_site.interest_calculation_period,
cust_site.charge_on_finance_charge_flag,
cust_site.message_text_id,
ps.actual_date_closed,
cust_site.interest_type,
cust_site.interest_rate,
cust_site.interest_fixed_amount,
cust_site.interest_schedule_id,
cust_site.min_fc_invoice_overdue_type,
cust_site.min_fc_invoice_amount,
cust_site.min_fc_invoice_percent,
cust_site.multiple_interest_rates_flag,
cust_site.hold_charged_invoices_flag,
ps.org_id,
ps.cash_receipt_id,
cust_site.last_accrue_charge_date
UNION ALL
select ps.customer_id,
decode(ps.class,
'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
'PMT',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
ps.customer_site_use_id) customer_site_use_id,
ps.invoice_currency_code,
ps.customer_trx_id,
ps.payment_schedule_id,
ps.class ,
ps.amount_due_original,
ps.amount_due_remaining,
nvl(sum(app.amount_applied
+ nvl(app.earned_discount_taken,0)
+ nvl(app.unearned_discount_taken,0)),0) overdue_amt,
0 fin_charge_charged,
ps.trx_date,
ps.cust_trx_type_id,
ps.last_charge_date,
cust_site.exchange_rate,
cust_site.exchange_rate_type,
cust_site.min_interest_charge,
cust_site.max_interest_charge,
decode(ps.class,'PMT',ps.trx_date,ps.due_date) due_date,
l_fin_charge_date ,
cust_site.late_charge_type,
cust_site.late_charge_term_id ,
cust_site.interest_period_days,
cust_site.interest_calculation_period,
cust_site.charge_on_finance_charge_flag,
cust_site.message_text_id,
ps.actual_date_closed,
cust_site.last_accrue_charge_date ,
cust_site.interest_type,
cust_site.interest_rate,
cust_site.interest_fixed_amount,
cust_site.interest_schedule_id,
cust_site.min_fc_invoice_overdue_type,
cust_site.min_fc_invoice_amount,
cust_site.min_fc_invoice_percent,
cust_site.multiple_interest_rates_flag,
cust_site.hold_charged_invoices_flag,
ps.org_id,
ps.cash_receipt_id,
'OVERDUE' charge_type
from ar_payment_schedules ps,
ar_receivable_applications app,
ar_payment_schedules ps_cm_cr,
ar_lc_cust_sites_t cust_site,
ar_late_charge_cust_balance_gt bal
where ps.customer_id = cust_site.customer_id
and cust_site.customer_site_use_id = decode(ps.class,
'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
'PMT',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
ps.customer_site_use_id)
and ps.invoice_currency_code = cust_site.currency_code
and mod(nvl(cust_site.customer_site_use_id,0),l_total_workers) =
decode(l_total_workers,l_worker_number,0,l_worker_number)
and ps.org_id = cust_site.org_id
and ps.actual_date_closed > l_fin_charge_date
and cust_site.late_charge_calculation_trx in ('OVERDUE_LATE','OVERDUE')
and cust_site.late_charge_type = 'INV'
/* Apply Customer Level tolerances */
and cust_site.lc_cust_sites_id = bal.late_charge_cust_sites_id
and cust_site.org_id = bal.org_id
and decode(cust_site.min_fc_balance_overdue_type,
'PERCENT',(nvl(cust_site.min_fc_balance_percent,0)
* nvl(bal.customer_open_balance,0)/100),
'AMOUNT',nvl(cust_site.min_fc_balance_amount,0),
nvl(bal.customer_overdue_balance,0)) <= nvl(bal.customer_overdue_balance,0)
and decode(nvl(cust_site.disputed_transactions_flag,'N'),
'N',decode(nvl(ps.amount_in_dispute,0),
0, 'Y','N'),
'Y' ) = 'Y'
/* Added NVL() for cust_site.credit_items_flag as part of Bug 10280458, manishri */
and decode(NVL(cust_site.credit_items_flag, 'N'),'N',
decode (ps.class, 'PMT','N','CM','N','INV',decode(sign(ps.amount_due_original),-1,'N','Y'),'Y'),'Y') = 'Y'
and decode(ps.class,
'PMT', ps.trx_date,
ps.due_date) < decode(ps.class,
'PMT', l_fin_charge_date,
'CM', l_fin_charge_date,
(l_fin_charge_date - nvl(cust_site.payment_grace_days,0)))
and nvl(ps.last_charge_date,l_fin_charge_date-1) < l_fin_charge_date
and nvl(cust_site.charge_begin_date,decode(ps.class,'PMT',ps.trx_date,ps.due_date))
<= decode(ps.class,'PMT',ps.trx_date,ps.due_date)
and nvl(ps.receipt_confirmed_flag, 'Y') = 'Y'
and app.applied_payment_schedule_id = ps.payment_schedule_id
and app.status = 'APP'
and nvl( app.confirmed_flag, 'Y' ) = 'Y'
/* The receipt or Credit Memo date has to be compared for application date */
and ps_cm_cr.payment_schedule_id = app.payment_schedule_id
and ps_cm_cr.trx_date > l_fin_charge_date
group by ps.customer_id,
decode(ps.class,
'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
'PMT',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
ps.customer_site_use_id),
ps.invoice_currency_code,
ps.customer_trx_id,
ps.payment_schedule_id,
ps.class ,
ps.amount_due_original,
ps.amount_due_remaining,
ps.trx_date,
ps.cust_trx_type_id,
ps.last_charge_date,
cust_site.exchange_rate,
cust_site.exchange_rate_type,
cust_site.min_interest_charge,
cust_site.max_interest_charge,
decode(ps.class,'PMT',ps.trx_date,ps.due_date),
l_fin_charge_date ,
cust_site.late_charge_type,
cust_site.late_charge_term_id,
cust_site.interest_period_days,
cust_site.interest_calculation_period,
cust_site.charge_on_finance_charge_flag,
cust_site.message_text_id,
ps.actual_date_closed ,
cust_site.interest_type,
cust_site.interest_rate,
cust_site.interest_fixed_amount,
cust_site.interest_schedule_id,
cust_site.min_fc_invoice_overdue_type,
cust_site.min_fc_invoice_amount,
cust_site.min_fc_invoice_percent,
cust_site.multiple_interest_rates_flag,
cust_site.hold_charged_invoices_flag,
ps.org_id,
ps.cash_receipt_id,
cust_site.last_accrue_charge_date
UNION ALL
select ps.customer_id,
ps.customer_site_use_id,
ps.invoice_currency_code,
ps.customer_trx_id,
ps.payment_schedule_id,
ps.class ,
ps.amount_due_original,
ps.amount_due_remaining,
nvl(sum( nvl(-1*app.amount_applied_from, -1*app.amount_applied)),0) overdue_amt,
0 fin_charge_charged,
ps.trx_date,
ps.cust_trx_type_id,
ps.last_charge_date,
cust_site.exchange_rate,
cust_site.exchange_rate_type,
cust_site.min_interest_charge,
cust_site.max_interest_charge,
ps.due_date due_date,
l_fin_charge_date ,
cust_site.late_charge_type,
cust_site.late_charge_term_id ,
cust_site.interest_period_days,
cust_site.interest_calculation_period,
cust_site.charge_on_finance_charge_flag,
cust_site.message_text_id,
ps.actual_date_closed,
cust_site.last_accrue_charge_date ,
cust_site.interest_type,
cust_site.interest_rate,
cust_site.interest_fixed_amount,
cust_site.interest_schedule_id,
cust_site.min_fc_invoice_overdue_type,
cust_site.min_fc_invoice_amount,
cust_site.min_fc_invoice_percent,
cust_site.multiple_interest_rates_flag,
cust_site.hold_charged_invoices_flag,
ps.org_id,
ps.cash_receipt_id,
'OVERDUE' charge_type
from ar_payment_schedules ps,
ar_receivable_applications app,
ar_lc_cust_sites_t cust_site,
ar_late_charge_cust_balance_gt bal
where ps.customer_id = cust_site.customer_id
and cust_site.customer_site_use_id = ps.customer_site_use_id
and ps.invoice_currency_code = cust_site.currency_code
and mod(nvl(cust_site.customer_site_use_id,0),l_total_workers) =
decode(l_total_workers,l_worker_number,0,l_worker_number)
and ps.org_id = cust_site.org_id
and ps.actual_date_closed > l_fin_charge_date
and ps.class = 'CM'
and cust_site.late_charge_calculation_trx in ('OVERDUE_LATE','OVERDUE')
and cust_site.late_charge_type = 'INV'
/* Apply Customer Level tolerances */
and cust_site.lc_cust_sites_id = bal.late_charge_cust_sites_id
and cust_site.org_id = bal.org_id
and decode(cust_site.min_fc_balance_overdue_type,
'PERCENT',(nvl(cust_site.min_fc_balance_percent,0)
* nvl(bal.customer_open_balance,0)/100),
'AMOUNT',nvl(cust_site.min_fc_balance_amount,0),
nvl(bal.customer_overdue_balance,0)) <= nvl(bal.customer_overdue_balance,0)
and decode(nvl(cust_site.disputed_transactions_flag,'N'),
'N',decode(nvl(ps.amount_in_dispute,0),
0, 'Y','N'),
'Y' ) = 'Y'
/* Added NVL() for cust_site.credit_items_flag as part of Bug 10280458, manishri */
and decode(NVL(cust_site.credit_items_flag, 'N'),'N',
decode (ps.class, 'PMT','N','CM','N','INV',decode(sign(ps.amount_due_original),-1,'N','Y'),'Y'),'Y') = 'Y'
and ps.due_date < decode(ps.class,
'PMT', l_fin_charge_date,
'CM', l_fin_charge_date,
(l_fin_charge_date - nvl(cust_site.payment_grace_days,0)))
and nvl(ps.last_charge_date,l_fin_charge_date-1) < l_fin_charge_date
and nvl(cust_site.charge_begin_date,decode(ps.class,'PMT',ps.trx_date,ps.due_date))
<= decode(ps.class,'PMT',ps.trx_date,ps.due_date)
and nvl(ps.receipt_confirmed_flag, 'Y') = 'Y'
and app.payment_schedule_id = ps.payment_schedule_id
and app.status = 'APP'
and app.application_type = 'CM'
and nvl( app.confirmed_flag, 'Y' ) = 'Y'
and app.apply_date > l_fin_charge_date
group by ps.customer_id,
ps.customer_site_use_id,
ps.invoice_currency_code,
ps.customer_trx_id,
ps.payment_schedule_id,
ps.class ,
ps.amount_due_original,
ps.amount_due_remaining,
ps.trx_date,
ps.cust_trx_type_id,
ps.last_charge_date,
cust_site.exchange_rate,
cust_site.exchange_rate_type,
cust_site.min_interest_charge,
cust_site.max_interest_charge,
ps.due_date,
l_fin_charge_date ,
cust_site.late_charge_type,
cust_site.late_charge_term_id,
cust_site.interest_period_days,
cust_site.interest_calculation_period,
cust_site.charge_on_finance_charge_flag,
cust_site.message_text_id,
ps.actual_date_closed ,
cust_site.interest_type,
cust_site.interest_rate,
cust_site.interest_fixed_amount,
cust_site.interest_schedule_id,
cust_site.min_fc_invoice_overdue_type,
cust_site.min_fc_invoice_amount,
cust_site.min_fc_invoice_percent,
cust_site.multiple_interest_rates_flag,
cust_site.hold_charged_invoices_flag,
ps.org_id,
ps.cash_receipt_id,
cust_site.last_accrue_charge_date
UNION ALL
select ps.customer_id,
decode(ps.class,
'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
'PMT',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
ps.customer_site_use_id) customer_site_use_id,
ps.invoice_currency_code,
ps.customer_trx_id,
ps.payment_schedule_id,
ps.class ,
ps.amount_due_original,
ps.amount_due_remaining,
ps.amount_due_remaining overdue_amt,
0 fin_charge_charged,
ps.trx_date,
ps.cust_trx_type_id,
ps.last_charge_date,
cust_site.exchange_rate,
cust_site.exchange_rate_type,
cust_site.min_interest_charge,
cust_site.max_interest_charge,
decode(ps.class,'PMT',ps.trx_date,ps.due_date)due_date,
l_fin_charge_date ,
cust_site.late_charge_type,
cust_site.late_charge_term_id ,
cust_site.interest_period_days,
cust_site.interest_calculation_period,
cust_site.charge_on_finance_charge_flag,
cust_site.message_text_id,
ps.actual_date_closed,
cust_site.last_accrue_charge_date ,
cust_site.interest_type,
cust_site.interest_rate,
cust_site.interest_fixed_amount,
cust_site.interest_schedule_id,
cust_site.min_fc_invoice_overdue_type,
cust_site.min_fc_invoice_amount,
cust_site.min_fc_invoice_percent,
cust_site.multiple_interest_rates_flag,
cust_site.hold_charged_invoices_flag,
ps.org_id,
ps.cash_receipt_id,
'OVERDUE' charge_type
from ar_payment_schedules ps,
ar_lc_cust_sites_t cust_site,
ar_late_charge_cust_balance_gt bal
where ps.customer_id = cust_site.customer_id
and cust_site.customer_site_use_id = decode(ps.class,
'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
'PMT',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
ps.customer_site_use_id)
and ps.invoice_currency_code = cust_site.currency_code
and mod(nvl(cust_site.customer_site_use_id,0),l_total_workers) =
decode(l_total_workers,l_worker_number,0,l_worker_number)
and ps.org_id = cust_site.org_id
and ps.actual_date_closed > l_fin_charge_date
and cust_site.late_charge_calculation_trx in ('OVERDUE_LATE','OVERDUE')
and cust_site.late_charge_type = 'INV'
/* Apply Customer Level tolerances */
and cust_site.lc_cust_sites_id = bal.late_charge_cust_sites_id
and cust_site.org_id = bal.org_id
and decode(cust_site.min_fc_balance_overdue_type,
'PERCENT',(nvl(cust_site.min_fc_balance_percent,0)
* nvl(bal.customer_open_balance,0)/100),
'AMOUNT',nvl(cust_site.min_fc_balance_amount,0),
nvl(bal.customer_overdue_balance,0)) <= nvl(bal.customer_overdue_balance,0)
and decode(nvl(cust_site.disputed_transactions_flag,'N'),
'N',decode(nvl(ps.amount_in_dispute,0),
0, 'Y','N'),
'Y' ) = 'Y'
/* Added NVL() for cust_site.credit_items_flag as part of Bug 10280458, manishri */
and decode(NVL(cust_site.credit_items_flag, 'N'),'N',
decode (ps.class, 'PMT','N','CM','N','INV',decode(sign(ps.amount_due_original),-1,'N','Y'),'Y'),'Y') = 'Y' /*8559863*/
and decode(ps.class,
'PMT', ps.trx_date,
ps.due_date) < decode(ps.class,
'PMT', l_fin_charge_date,
'CM', l_fin_charge_date,
(l_fin_charge_date - nvl(cust_site.payment_grace_days,0)))
and nvl(ps.last_charge_date,l_fin_charge_date-1) < l_fin_charge_date
and nvl(cust_site.charge_begin_date,decode(ps.class,'PMT',ps.trx_date,ps.due_date))
<= decode(ps.class,'PMT',ps.trx_date,ps.due_date)
and decode(ps.class,
'PMT',ps.trx_date,
'CM',ps.trx_date,ps.due_date) <= l_fin_charge_date
and nvl(ps.receipt_confirmed_flag, 'Y') = 'Y') a,
ra_customer_trx trx,
ra_cust_trx_types types,
ar_transaction_history th,
ar_cash_receipts cr
where trx.customer_trx_id(+) = a.customer_trx_id
and nvl(trx.finance_charges,decode(a.class,'DEP','N','Y')) = 'Y'
and a.customer_trx_id = th.customer_trx_id(+)
and nvl(th.current_record_flag,'Y') = 'Y'
and nvl(th.status,'*') not in ('PROTESTED','MATURED_PEND_RISK_ELIMINATION','CLOSED', 'CANCELLED')
and types.cust_trx_type_id(+) = a.cust_trx_type_id
and types.org_id(+) = a.org_id
and nvl(types.exclude_from_late_charges,'N') <> 'Y'
and cr.cash_receipt_id(+) = a.cash_receipt_id
and decode(a.hold_charged_invoices_flag,
'Y',decode(a.last_charge_date,
NULL,a.fin_charge_charged,1),
0) = 0
group by a.customer_id,
a.customer_site_use_id ,
a.invoice_currency_code,
nvl(a.customer_trx_id,a.cash_receipt_id),
nvl(trx.legal_entity_id,cr.legal_entity_id),
a.payment_schedule_id,
a.class ,
a.amount_due_original,
a.amount_due_remaining ,
a.trx_date,
a.cust_trx_type_id,
a.last_charge_date,
a.last_accrue_charge_date,
a.exchange_rate_type,
a.min_interest_charge,
a.max_interest_charge,
a.due_date,
a.fin_charge_date,
a.charge_type,
a.actual_date_closed,
a.late_charge_type,
a.late_charge_term_id,
a.interest_period_days,
a.interest_calculation_period,
a.charge_on_finance_charge_flag,
a.message_text_id,
a.interest_type,
a.interest_rate,
a.interest_schedule_id,
a.min_fc_invoice_overdue_type,
a.min_fc_invoice_amount,
a.min_fc_invoice_percent,
a.multiple_interest_rates_flag,
a.hold_charged_invoices_flag,
a.org_id,
a.interest_fixed_amount,
decode(a.interest_calculation_period,
'DAILY',l_fin_charge_date,
'MONTHLY',last_day(l_fin_charge_date)),
decode(a.interest_calculation_period,
'DAILY',nvl(a.last_charge_date,
decode(a.fin_charge_charged,
0,a.due_date,
a.last_accrue_charge_date)),
'MONTHLY',first_day(nvl(a.last_charge_date,
decode(a.fin_charge_charged,
0,a.due_date,
a.last_accrue_charge_date)))),
decode(a.interest_calculation_period,
'DAILY',a.due_date,
'MONTHLY',first_day(a.due_date)))b,
ar_charge_schedule_hdrs sched_hdrs,
ar_charge_schedule_lines sched_lines,
ar_aging_bucket_lines bucket_lines
where b.interest_schedule_id = sched_hdrs.schedule_id(+)
and sched_hdrs.schedule_header_id = sched_lines.schedule_header_id(+)
and sched_hdrs.schedule_id = sched_lines.schedule_id(+)
and nvl(sched_hdrs.status,'A') = 'A'
and sched_lines.aging_bucket_id = bucket_lines.aging_bucket_id(+)
and sched_lines.aging_bucket_line_id = bucket_lines.aging_bucket_line_id(+)
/* Condition 1: days late should be between the bucket lines start and end days */
and (l_fin_charge_date- b.due_date) >= nvl(bucket_lines.days_start,(l_fin_charge_date- b.due_date))
and (l_fin_charge_date - b.due_date) <= nvl(bucket_lines.days_to,(l_fin_charge_date- b.due_date))
/* Condition 2:
Start_date of the schedule should be less than or equal to the finance charge date */
and nvl(sched_hdrs.start_date,l_fin_charge_date) <= l_fin_charge_date
/* condition 3:
If multiple interest rates have to be used, end date of the schedule should be greater than
or equal to the due date or the date from which we are calculating the charge
Otherwise, the end_date should either be null or it should be greater than the
due_date
*/
and (decode(b.multiple_interest_rates_flag,'Y',
decode(sched_hdrs.schedule_header_type,
'RATE',greatest(b.due_date,nvl(b.last_charge_date,b.due_date)),
b.due_date),
b.due_date) <= sched_hdrs.end_date
OR sched_hdrs.end_date IS NULL )
/* Condition 4: If multiple rates need not be used, we should pick up the rate
that is effective on the due_date of the transaction.
Also note that the multiple interest rates are used only for Interest
Calculation and only when rates are used*/
and decode(b.multiple_interest_rates_flag,'Y',
decode(sched_hdrs.schedule_header_type,
'RATE',sched_hdrs.start_date,
b.due_date),
b.due_date )>= nvl(sched_hdrs.start_date,b.due_date)
/* Make sure that this payment schedule is not part of a failed final batch */
and not exists (select payment_schedule_id
from ar_interest_lines lines,
ar_interest_headers hdrs,
ar_interest_batches bat
where lines.payment_schedule_id = b.payment_schedule_id
and lines.interest_header_id = hdrs.interest_header_id
and hdrs.interest_batch_id = bat.interest_batch_id
and bat.batch_status ='F'
and bat.transferred_status <> 'S'));
debug( 'ar_calc_late_charge.insert_int_overdue_inv()-' );
debug('EXCEPTION: ar_calc_late_charge.insert_int_overdue_inv' );
END insert_int_overdue_inv;
| PROCEDURE insert_int_late_pay |
| |
| DESCRIPTION |
| |
| This procedure finds out the late payments on any debit item. By late payment, we |
| mean the applications done on it after the due_date / last finance charge date . |
| Interest Amount is then calculated on the overdue balance and inserted into |
| ar_late_charge_trx_t |
| |
| PSEUDO CODE/LOGIC |
| |
| a) The Receipt Date is used for finding out the late applications on a debit item. So, |
| if an application is Reversed, that need not be considered as the application and |
| it's reversal will cancel out each other on that receipt date. |
| b) The finance charge that is already charged on this invoice is fetched from |
| ar_adjustments |
| c) Open Credit Items are not considered as we are tracking only the late applications |
| |
| PARAMETERS |
| |
| |
| KNOWN ISSUES |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| Date Author Description of Changes |
| 16-FEB-2006 rkader Created |
| 11-JUL-2006 rkader Bug 5290709 : Last charge date should not be |
| considered for the calculation of the interest. |
| The late payment is always from the due date |
| to the receipt date |
|24-JUN-2008 naneja Bug 7162382 : In above fix for bug 5290709 |
| Late charge was calculated in duplicate for |
| late payment and overdue invoice case. |
| Last run must have calculated interest for |
| amount of |
| late payment. Thus merge 5290709 fix of |
| late payment only fix with this fix. |
|22-JUN-2009 naneja Inserted data for new column cash_receipt_id |
| Bug 8556955 |
*=========================================================================================*/
PROCEDURE insert_int_late_pay(p_fin_charge_date IN DATE,
p_worker_number IN NUMBER,
p_total_workers IN NUMBER) IS
l_fin_charge_date DATE;
debug( 'ar_calc_late_charge.insert_int_late_pay()+' );
insert into ar_late_charge_trx_t
(late_charge_trx_id,
customer_id,
customer_site_use_id,
currency_code,
customer_trx_id,
legal_entity_id,
payment_schedule_id,
class,
amount_due_original,
amount_due_remaining,
fin_charge_charged,
trx_date,
cust_trx_type_id,
last_charge_date,
--exchange_rate,
exchange_rate_type,
min_interest_charge,
max_interest_charge,
overdue_late_pay_amount,
original_balance,
due_date,
receipt_date,
finance_charge_date,
charge_type,
actual_date_closed,
interest_rate,
interest_days,
rate_start_date,
rate_end_date,
schedule_days_start,
schedule_days_to,
late_charge_amount,
late_charge_type,
late_charge_term_id,
interest_period_days,
interest_calculation_period,
charge_on_finance_charge_flag,
message_text_id,
interest_type,
min_fc_invoice_overdue_type,
min_fc_invoice_amount,
min_fc_invoice_percent,
charge_line_type,
org_id,
request_id,
display_flag,
cash_receipt_id)
(select ar_late_charge_trx_s.nextval,
a.customer_id,
a.customer_site_use_id ,
a.invoice_currency_code,
a.customer_trx_id,
a.legal_entity_id,
a.payment_schedule_id,
a.class ,
a.amount_due_original,
a.amount_due_remaining,
a.fin_charge_charged,
a.trx_date,
a.cust_trx_type_id,
a.last_charge_date ,
--exchange_rate,
a.exchange_rate_type,
a.min_interest_charge,
a.max_interest_charge,
decode(sign(a.late_pay_amount - a.original_balance),
-1, a.late_pay_amount,
0, a.late_pay_amount,
a.original_balance) overdue_late_pay_amount,
a.original_balance,
a.due_date,
a.receipt_date,
a.finance_charge_date,
a.charge_type,
a.actual_date_closed,
decode(a.interest_type,
'CHARGES_SCHEDULE',sched_lines.rate,
'FIXED_RATE',a.interest_rate, NULL) interest_rate,
least(decode(a.multiple_interest_rates_flag,
'Y',decode(sched_hdrs.schedule_header_type,
'RATE', nvl(sched_hdrs.end_date,a.eff_apply_date),
a.eff_apply_date),
a.eff_apply_date),a.eff_apply_date) -
greatest(decode(a.multiple_interest_rates_flag,
'Y',decode(sched_hdrs.schedule_header_type,
'RATE',sched_hdrs.start_date-1,a.eff_due_date),
a.eff_due_date), a.eff_due_date,decode(a.eff_charge_type,'OVERDUE_LATE',a.eff_last_charge_date,a.eff_due_date)) interest_days, /*Merge-Bug fix 5290709 for overdue and late case*/
sched_hdrs.start_date rate_start_date,
sched_hdrs.end_date rate_end_date ,
bucket_lines.days_start schedule_days_start,
bucket_lines.days_to schedule_days_to,
decode(a.interest_type,
'FIXED_AMOUNT',a.interest_fixed_amount,
decode(sched_hdrs.schedule_header_type,
'AMOUNT',sched_lines.amount,
ar_calc_late_charge.calculate_interest(
decode(sign(a.late_pay_amount - a.original_balance),
-1,a.late_pay_amount,
0,a.late_pay_amount,
a.original_balance),
a.charge_on_finance_charge_flag,
least(decode(a.multiple_interest_rates_flag,
'Y',decode(sched_hdrs.schedule_header_type,
'RATE', nvl(sched_hdrs.end_date,a.eff_apply_date),
a.eff_apply_date),
a.eff_apply_date),a.eff_apply_date) -
greatest(decode(a.multiple_interest_rates_flag,
'Y',decode(sched_hdrs.schedule_header_type,
'RATE',sched_hdrs.start_date-1,a.eff_due_date),
a.eff_due_date), a.eff_due_date,decode(a.eff_charge_type,'OVERDUE_LATE',a.eff_last_charge_date,a.eff_due_date)),/* Merge fix 7162382 for overdue and late case -Bug fix 5290709 */
decode(a.interest_type,
'CHARGES_SCHEDULE',sched_lines.rate,
'FIXED_RATE',a.interest_rate, NULL),
a.interest_period_days,
a.invoice_currency_code,
a.payment_schedule_id))) late_charge_amount,
a.late_charge_type,
a.late_charge_term_id,
a.interest_period_days,
a.interest_calculation_period,
a.charge_on_finance_charge_flag,
a.message_text_id,
a.interest_type,
a.min_fc_invoice_overdue_type,
a.min_fc_invoice_amount,
a.min_fc_invoice_percent,
'INTEREST',
a.org_id,
l_request_id,
'Y',
a.cash_receipt_id
from
(
select ps.customer_id,
decode(ps.class,
'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
ps.customer_site_use_id) customer_site_use_id ,
ps.invoice_currency_code,
ps.customer_trx_id,
nvl(trx.legal_entity_id,cr.legal_entity_id) legal_entity_id,
ps.payment_schedule_id,
ps.class ,
ps.amount_due_original,
ps.amount_due_remaining,
nvl(adj.fin_charge_charged,0) fin_charge_charged,
ps.trx_date,
ps.cust_trx_type_id,
nvl(ps.last_charge_date,
decode(nvl(adj.fin_charge_charged,0),0,NULL,
cust_site.last_accrue_charge_date)) last_charge_date ,
cust_site.exchange_rate_type,
cust_site.min_interest_charge,
cust_site.max_interest_charge,
sum(app.amount_applied + nvl(app.earned_discount_taken,0) +
nvl(app.unearned_discount_taken,0)) late_pay_amount,
ar_calc_late_charge.get_balance_as_of(ps.payment_schedule_id,
cr.receipt_date-1,
ps.class,
cust_site.charge_on_finance_charge_flag) original_balance,
ps.due_date,
cr.receipt_date,
l_fin_charge_date finance_charge_date,
cust_site.late_charge_type,
ps.actual_date_closed,
cust_site.late_charge_term_id,
cust_site.interest_period_days,
cust_site.interest_calculation_period,
cust_site.charge_on_finance_charge_flag,
cust_site.message_text_id,
cust_site.last_accrue_charge_date,
cust_site.interest_type,
cust_site.interest_rate,
cust_site.interest_fixed_amount,
cust_site.interest_schedule_id interest_schedule_id,
cust_site.min_fc_invoice_overdue_type,
cust_site.min_fc_invoice_amount,
cust_site.min_fc_invoice_percent,
cust_site.multiple_interest_rates_flag,
cust_site.hold_charged_invoices_flag,
ps.org_id,
decode(cust_site.interest_calculation_period,
'DAILY',cr.receipt_date,
'MONTHLY',last_day(cr.receipt_date)) eff_apply_date,
decode(cust_site.interest_calculation_period,
'DAILY',nvl(ps.last_charge_date,
decode(nvl(adj.fin_charge_charged,0),0,ps.due_date,
nvl(cust_site.last_accrue_charge_date,ps.due_date))),
'MONTHLY',first_day(nvl(ps.last_charge_date,
decode(nvl(adj.fin_charge_charged,0),0,ps.due_date,
nvl(cust_site.last_accrue_charge_date,ps.due_date))))) eff_last_charge_date,
decode(cust_site.interest_calculation_period,
'DAILY',ps.due_date,
'MONTHLY',first_day(ps.due_date)) eff_due_date,
'LATE' charge_type,
cust_site.late_charge_calculation_trx eff_charge_type,
cr.cash_receipt_id
from ar_payment_schedules ps,
ar_lc_cust_sites_t cust_site,
ar_late_charge_cust_balance_gt bal,
ar_receivable_applications app,
ar_cash_receipts cr,
ra_cust_trx_types types,
ra_customer_trx trx,
ar_transaction_history th,
(select ps.payment_schedule_id ,sum(adj.amount) fin_charge_charged
from ar_payment_schedules ps,
ar_adjustments adj,
ar_lc_cust_sites_t cust_site
where ps.customer_id = cust_site.customer_id
and decode(cust_site.customer_site_use_id,'','X', ps.customer_site_use_id)
= decode(cust_site.customer_site_use_id,'','X', cust_site.customer_site_use_id)
and ps.invoice_currency_code = cust_site.currency_code
and mod(nvl(cust_site.customer_site_use_id,0),l_total_workers) =
decode(l_total_workers,l_worker_number,0,l_worker_number)
and ps.org_id = cust_site.org_id
and cust_site.late_charge_calculation_trx in ('OVERDUE_LATE','LATE')
and decode(cust_site.disputed_transactions_flag,'N',
decode(nvl(ps.amount_in_dispute,0), 0, 'Y','N'),'Y' ) = 'Y'
and ps.class not in ('CM','PMT')
and adj.payment_schedule_id = ps.payment_schedule_id
and adj.status = 'A'
and adj.apply_date <= l_fin_charge_date
and adj.type ='CHARGES'
group by ps.payment_schedule_id) adj
where ps.customer_id = cust_site.customer_id
and cust_site.customer_site_use_id = decode(ps.class,
'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
ps.customer_site_use_id)
and ps.invoice_currency_code = cust_site.currency_code
and mod(nvl(cust_site.customer_site_use_id,0),l_total_workers) =
decode(l_total_workers,l_worker_number,0,l_worker_number)
and ps.org_id = cust_site.org_id
and cust_site.late_charge_calculation_trx in ('OVERDUE_LATE','LATE')
and decode(nvl(cust_site.disputed_transactions_flag,'N'),
'N',decode(nvl(ps.amount_in_dispute,0),
0, 'Y','N'),
'Y' ) = 'Y'
/* Apply Customer Level tolerances */
and cust_site.lc_cust_sites_id = bal.late_charge_cust_sites_id
and cust_site.org_id = bal.org_id
/*Commenting as part of bug13632418 */
/* and decode(cust_site.min_fc_balance_overdue_type,
'PERCENT',(nvl(cust_site.min_fc_balance_percent,0)
* nvl(bal.customer_open_balance,0)/100),
'AMOUNT',nvl(cust_site.min_fc_balance_amount,0),
nvl(bal.customer_overdue_balance,0)) <= nvl(bal.customer_overdue_balance,0) */ /*Bug8464171*/
and app.applied_payment_schedule_id = ps.payment_schedule_id
and app.application_type = 'CASH'
and app.status = 'APP'
and app.reversal_gl_date IS NULL
and nvl(app.confirmed_flag, 'Y' ) = 'Y'
and cr.cash_receipt_id = app.cash_receipt_id
and ps.class not in ('CM','PMT')
and ps.due_date < (cr.receipt_date - nvl(cust_site.payment_grace_days,0))
and nvl(cust_site.charge_begin_date,ps.due_date) <= ps.due_date
and cr.receipt_date <= l_fin_charge_date
and cr.receipt_date > nvl(ps.last_charge_date,cr.receipt_date-1)
and adj.payment_schedule_id(+) = ps.payment_schedule_id
and decode(cust_site.hold_charged_invoices_flag,
'Y',decode(ps.last_charge_date,
NULL,nvl(adj.fin_charge_charged,0),
1),
0) = 0
/* The Payments are not fetched. So there can be a hard join with cust_trx_types */
and types.cust_trx_type_id = ps.cust_trx_type_id
and types.org_id = ps.org_id
and nvl(types.exclude_from_late_charges,'N') <> 'Y'
and trx.customer_trx_id(+) = ps.customer_trx_id
and nvl(trx.finance_charges,decode(ps.class,'DEP','N','Y')) = 'Y'
and th.customer_trx_id(+) = ps.customer_trx_id
and nvl(th.current_record_flag,'Y') = 'Y'
and nvl(th.status,'*') not in ('PROTESTED','MATURED_PEND_RISK_ELIMINATION','CLOSED', 'CANCELLED')
group by ps.customer_id,
decode(ps.class,
'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
ps.customer_site_use_id),
ps.invoice_currency_code,
ps.customer_trx_id,
nvl(trx.legal_entity_id,cr.legal_entity_id),
ps.payment_schedule_id,
ps.class ,
ps.amount_due_original,
ps.amount_due_remaining,
nvl(adj.fin_charge_charged,0),
ps.trx_date,
ps.cust_trx_type_id,
nvl(ps.last_charge_date,
decode(nvl(adj.fin_charge_charged,0),0,NULL,
cust_site.last_accrue_charge_date)),
cust_site.exchange_rate_type,
cust_site.min_interest_charge,
cust_site.max_interest_charge,
ar_calc_late_charge.get_balance_as_of(ps.payment_schedule_id,
cr.receipt_date-1,
ps.class,
cust_site.charge_on_finance_charge_flag),
ps.due_date,
cr.receipt_date,
l_fin_charge_date,
cust_site.late_charge_type,
ps.actual_date_closed,
cust_site.late_charge_term_id,
cust_site.interest_period_days,
cust_site.interest_calculation_period,
cust_site.charge_on_finance_charge_flag,
cust_site.message_text_id,
cust_site.last_accrue_charge_date,
cust_site.interest_type,
cust_site.interest_rate,
cust_site.interest_fixed_amount,
cust_site.interest_schedule_id,
cust_site.min_fc_invoice_overdue_type,
cust_site.min_fc_invoice_amount,
cust_site.min_fc_invoice_percent,
cust_site.multiple_interest_rates_flag,
cust_site.hold_charged_invoices_flag,
ps.org_id,
decode(cust_site.interest_calculation_period,
'DAILY',cr.receipt_date,
'MONTHLY',last_day(cr.receipt_date)),
decode(cust_site.interest_calculation_period,
'DAILY',nvl(ps.last_charge_date,
decode(nvl(adj.fin_charge_charged,0),0,ps.due_date,
nvl(cust_site.last_accrue_charge_date,ps.due_date))),
'MONTHLY',first_day(nvl(ps.last_charge_date,
decode(nvl(adj.fin_charge_charged,0),0,ps.due_date,
nvl(cust_site.last_accrue_charge_date,ps.due_date))))),
decode(cust_site.interest_calculation_period,
'DAILY',ps.due_date,
'MONTHLY',first_day(ps.due_date)),
cust_site.late_charge_calculation_trx,
cr.cash_receipt_id) a,
ar_charge_schedule_hdrs sched_hdrs,
ar_charge_schedule_lines sched_lines,
ar_aging_bucket_lines bucket_lines
where a.interest_schedule_id = sched_hdrs.schedule_id(+)
and sched_hdrs.schedule_header_id = sched_lines.schedule_header_id(+)
and sched_hdrs.schedule_id = sched_lines.schedule_id(+)
and nvl(sched_hdrs.status,'A') = 'A'
and sched_lines.aging_bucket_id = bucket_lines.aging_bucket_id(+)
and sched_lines.aging_bucket_line_id = bucket_lines.aging_bucket_line_id(+)
/* Condition 1: days late should be between the bucket lines start and end days */
and (a.receipt_date- a.due_date) >= nvl(bucket_lines.days_start,(a.receipt_date- a.due_date))
and (a.receipt_date - a.due_date) <= nvl(bucket_lines.days_to,(a.receipt_date- a.due_date))
/* Condition 2: Start_date of the schedule should be less than or equal to the
finance charge date */
and nvl(sched_hdrs.start_date,a.receipt_date) <= a.receipt_date
/* condition 3:
If multiple interest rates have to be used, end date of the schedule should be greater than
or equal to the due date or the date from which we are calculating the charge
Otherwise, the end_date should either be null or it should be greater than the
due_date to pick up the rate effective as of the due_date
Bug 8343193 For multiple interest rate for late payment we need to consider schedules from due date
*/
and (decode(a.multiple_interest_rates_flag,'Y',
decode(sched_hdrs.schedule_header_type,
'RATE',greatest(a.due_date,decode(a.eff_charge_type,'LATE',a.due_date,nvl(a.last_charge_date,a.due_date))),
a.due_date),
a.due_date) <= sched_hdrs.end_date
OR sched_hdrs.end_date IS NULL )
/* Condition 4: If multiple rates need not be used, we should pick up the rate
that is effective on the due date.
Also note that the multiple interest rates are used only for Interest
Calculation and only when rates are used*/
and decode(a.multiple_interest_rates_flag,'Y',
decode(sched_hdrs.schedule_header_type,
'RATE',sched_hdrs.start_date,
a.due_date),
a.due_date)>= nvl(sched_hdrs.start_date,a.due_date)
/* Make sure that this payment schedule is not part of a failed final batch */
and not exists (select payment_schedule_id
from ar_interest_lines lines,
ar_interest_headers hdrs,
ar_interest_batches bat
where lines.payment_schedule_id = a.payment_schedule_id
and lines.interest_header_id = hdrs.interest_header_id
and hdrs.interest_batch_id = bat.interest_batch_id
and bat.batch_status ='F'
and bat.transferred_status <> 'S'));
debug( 'ar_calc_late_charge.insert_int_late_pay()-' );
debug('EXCEPTION: ar_calc_late_charge.insert_int_late_pay' );
END insert_int_late_pay;
| PROCEDURE insert_int_avg_daily_bal |
| |
| DESCRIPTION |
| |
| This procedure computes the average daily balance of the debit items and calculates |
| Interest on this average daily balance and inserts the records into |
| ar_late_charge_trx_t |
| |
| PSEUDO CODE/LOGIC |
| |
| |
| PARAMETERS |
| |
| |
| KNOWN ISSUES |
| |
| NOTES |
| |
| 1. Average Daily Balance method involves three steps |
| a) Determinition |
| For determining if a customer, site and currency combination is to be charged, the|
| balance of the last bill is taken and the credit items upto the due_date plus |
| receipt grace days is subtracted from this. If this balance is atleast the min |
| customer balance threshold, charge will be calculated. |
| b) Charge computation |
| Based on the set up in post_billing_debit_items and late_charge_billing_calc_mode,|
| (in ar_system_parameters), different items and date range will be used to |
| calculate the average daily balance |
| c) Generation of the document in AR. |
| 2. The following fields are not used for Average Daily Balance |
| a) credit_items_flag |
| b) disputed_transaction_flag |
| c) multiple_interest_rates_flag |
| d) interest days per period |
| e) interest calculation period |
| f) hold_charged_invoices_flag |
| g) minimum invoice overdue + value |
| 3. The following fields have different validation for Average Daily Balance |
| a) Late charge type - Interest Invoices only |
| b) Interest Calculation Formula - Flat only |
| c) Interest Charge and Penalty Charge - can not be Charges Schedule |
| |
| MODIFICATION HISTORY |
| Date Author Description of Changes |
| |
| 05-APR-2006 rkader Created |
| |
*=========================================================================================*/
PROCEDURE insert_int_avg_daily_bal(p_fin_charge_date IN DATE,
p_worker_number IN NUMBER,
p_total_workers IN NUMBER) IS
l_fin_charge_date DATE;
debug( 'ar_calc_late_charge. insert_int_avg_daily_bal()+' );
/* Insert records for the system option set up RUN DATE TO RUN DATE */
insert into ar_late_charge_trx_t
(late_charge_trx_id,
customer_id,
customer_site_use_id,
currency_code,
customer_trx_id,
legal_entity_id,
payment_schedule_id,
class,
amount_due_original,
amount_due_remaining,
fin_charge_charged,
trx_date,
cust_trx_type_id,
last_charge_date,
exchange_rate_type,
min_interest_charge,
max_interest_charge,
overdue_late_pay_amount,
original_balance,
due_date,
receipt_date,
finance_charge_date,
charge_type,
actual_date_closed,
interest_rate,
interest_days,
rate_start_date,
rate_end_date,
schedule_days_start,
schedule_days_to,
late_charge_amount,
late_charge_type,
late_charge_term_id,
interest_period_days,
interest_calculation_period,
charge_on_finance_charge_flag,
message_text_id,
interest_type,
min_fc_invoice_overdue_type,
min_fc_invoice_amount,
min_fc_invoice_percent,
charge_line_type,
org_id,
request_id,
display_flag )
( select ar_late_charge_trx_s.nextval,
b.customer_id,
b.site_use_id,
b.currency_code,
NULL, -- customer_trx_id
NULL, -- How to determine this?
-99, -- payment_schedule_id
NULL, -- class, search for some look up for cons_inv
NULL, -- amount_due_original
NULL, -- amount_due_remaining
NULL, -- fin_charge_already_charged
NULL, -- trx_date
NULL, --cust_trx_type is not applicable
b.last_accrue_charge_date,
b.exchange_rate_type,
b.min_interest_charge,
b.max_interest_charge,
b.overdue_late_pay_amount,
b.overdue_late_pay_amount, -- original balance
NULL, --Due date not applicable
NULL, --receipt_date
l_fin_charge_date,
'AVERAGE_DAILY_BALANCE',
NULL,
b.interest_rate,
b.interest_days,
NULL, -- rate start_date,
NULL, -- rate end date
NULL, -- bucket days start
NULL, -- bucket days end
b.late_charge_amount,
'INV',
b.late_charge_term_id,
NULL, -- interest_period_days not applicable
NULL, -- interest_calculation_period not applicable
'F' , -- only flat rate is applicable
b.message_text_id,
b.interest_type,
NULL, -- invoice level tolerances are not applicable (min_fc_invoice_overdue_type)
NULL, -- min_fc_invoice_amount
NULL, -- min_fc_invoice_percent
'INTEREST',
b.org_id,
l_request_id,
'Y'
from
(
select
a.customer_id,
a.site_use_id,
a.currency_code,
a.last_accrue_charge_date,
a.exchange_rate_type,
a.min_interest_charge,
a.max_interest_charge,
ar_calc_late_charge.currency_round(sum(a.balance * (a.date_to - a.date_from+1))
/ sum(a.date_to - a.date_from+1),
a.currency_code) overdue_late_pay_amount,
decode(a.interest_type,
'FIXED_RATE',a.interest_rate, NULL) interest_rate,
sum(a.date_to - a.date_from+1) interest_days,
decode(a.interest_type,
'FIXED_AMOUNT', a.interest_fixed_amount,
'FIXED_RATE',
ar_calc_late_charge.currency_round(nvl(
((sum(a.balance * (a.date_to - a.date_from+1))/
sum(a.date_to - a.date_from+1))/100
*a.interest_rate
),
0), a.currency_code)) late_charge_amount,
a.late_charge_term_id,
a.message_text_id,
a.interest_type,
a.org_id,
a.payment_grace_days,
a.min_fc_balance_overdue_type,
a.min_fc_balance_amount,
a.min_fc_balance_percent
from
(
select cons_inv.customer_id,
cons_inv.site_use_id,
cons_inv.currency_code,
cons_inv.org_id,
cons_inv.billing_date date_from,
decode(sign(l_fin_charge_date -
ar_calc_late_charge.get_next_activity_date(cons_inv.customer_id,
cons_inv.site_use_id,
cons_inv.currency_code,
cons_inv.org_id,
sysparam.post_billing_item_inclusion,
cons_inv.billing_date,
l_fin_charge_date)),
-1,l_fin_charge_date,
ar_calc_late_charge.get_next_activity_date(cons_inv.customer_id,
cons_inv.site_use_id,
cons_inv.currency_code,
cons_inv.org_id,
sysparam.post_billing_item_inclusion,
cons_inv.billing_date,
l_fin_charge_date)) date_to,
cons_inv.ending_balance balance,
cust_sites.last_accrue_charge_date,
cust_sites.exchange_rate_type,
cust_sites.min_interest_charge,
cust_sites.max_interest_charge,
cust_sites.interest_type,
cust_sites.interest_rate,
cust_sites.interest_fixed_amount,
cust_sites.interest_schedule_id,
cust_sites.late_charge_term_id,
cust_sites.message_text_id,
cust_sites.payment_grace_days,
cust_sites.min_fc_balance_overdue_type,
cust_sites.min_fc_balance_amount,
cust_sites.min_fc_balance_percent
from ar_cons_inv cons_inv,
ar_lc_cust_sites_t cust_sites,
ar_system_parameters sysparam
where cons_inv.customer_id = cust_sites.customer_id
and cons_inv.site_use_id = cust_sites.customer_site_use_id
and cons_inv.currency_code = cust_sites.currency_code
and mod(nvl(cust_sites.customer_site_use_id,0),l_total_workers) =
decode(l_total_workers,l_worker_number,0,l_worker_number)
and cons_inv.org_id = cust_sites.org_id
and cons_inv.billing_date = (select max(ci2.billing_date)
from ar_cons_inv ci2
where ci2.customer_id = cust_sites.customer_id
and ci2.site_use_id = cust_sites.customer_site_use_id
and ci2.currency_code = cust_sites.currency_code
and ci2.org_id = cust_sites.org_id
and ci2.billing_date <= l_fin_charge_date
and ci2.status in ('FINAL', 'ACCEPTED','IMPORTED'))
and cust_sites.late_charge_calculation_trx = 'AVG_DAILY_BALANCE'
and sysparam.org_id = cons_inv.org_id
and sysparam.late_charge_billing_calc_mode = 'RUN_TO_RUN_DATE'
union
select cust_site.customer_id,
cust_site.customer_site_use_id,
cust_site.currency_code,
cust_site.org_id,
decode(cust_site.last_accrue_charge_date,
NULL, ar_calc_late_charge.get_first_activity_date(cust_site.customer_id,
cust_site.customer_site_use_id,
cust_site.currency_code,
cust_site.org_id),
cust_site.last_accrue_charge_date+1) date_from,
decode(sign(l_fin_charge_date -
ar_calc_late_charge.get_next_activity_date(cust_site.customer_id,
cust_site.customer_site_use_id,
cust_site.currency_code,
cust_site.org_id,
sysparam.post_billing_item_inclusion,
decode(cust_site.last_accrue_charge_date,
NULL,get_first_activity_date(cust_site.customer_id,
cust_site.customer_site_use_id,
cust_site.currency_code,
cust_site.org_id),
cust_site.last_accrue_charge_date+1),
l_fin_charge_date)),
-1, l_fin_charge_date,
ar_calc_late_charge.get_next_activity_date(cust_site.customer_id,
cust_site.customer_site_use_id,
cust_site.currency_code,
cust_site.org_id,
sysparam.post_billing_item_inclusion,
decode(cust_site.last_accrue_charge_date,
NULL,get_first_activity_date(cust_site.customer_id,
cust_site.customer_site_use_id, cust_site.currency_code,
cust_site.org_id),
cust_site.last_accrue_charge_date+1),
l_fin_charge_date)) date_to,
ar_calc_late_charge.get_cust_balance(cust_site.customer_id,
cust_site.customer_site_use_id,
cust_site.currency_code,
cust_site.org_id,
sysparam.post_billing_item_inclusion,
decode(cust_site.last_accrue_charge_date,
NULL,get_first_activity_date(cust_site.customer_id,
cust_site.customer_site_use_id,
cust_site.currency_code,
cust_site.org_id),
cust_site.last_accrue_charge_date+1)) balance,
cust_site.last_accrue_charge_date,
cust_site.exchange_rate_type,
cust_site.min_interest_charge,
cust_site.max_interest_charge,
cust_site.interest_type,
cust_site.interest_rate,
cust_site.interest_fixed_amount,
cust_site.interest_schedule_id,
cust_site.late_charge_term_id,
cust_site.message_text_id,
cust_site.payment_grace_days,
cust_site.min_fc_balance_overdue_type,
cust_site.min_fc_balance_amount,
cust_site.min_fc_balance_percent
from ar_lc_cust_sites_t cust_site,
ar_system_parameters sysparam
where sysparam.org_id = cust_site.org_id
and sysparam.late_charge_billing_calc_mode = 'RUN_TO_RUN_DATE'
and cust_site.late_charge_calculation_trx = 'AVG_DAILY_BALANCE'
and mod(nvl(cust_site.customer_site_use_id,0),l_total_workers) =
decode(l_total_workers,l_worker_number,0,l_worker_number)
union
/* select distinct : even if more than one item exists with the same trx_date,
consider this date only once */
select cust_sites.customer_id,
cust_sites.customer_site_use_id,
cust_sites.currency_code,
cust_sites.org_id,
ps.trx_date,
decode(sign(l_fin_charge_date -
ar_calc_late_charge.get_next_activity_date(ps.customer_id,
ps.customer_site_use_id,
ps.invoice_currency_code,
ps.org_id,
sysparam.post_billing_item_inclusion,
ps.trx_date,
l_fin_charge_date)),
-1, l_fin_charge_date,
ar_calc_late_charge.get_next_activity_date(ps.customer_id,
ps.customer_site_use_id,
ps.invoice_currency_code,
ps.org_id,
sysparam.post_billing_item_inclusion,
ps.trx_date,
l_fin_charge_date)) date_to,
ar_calc_late_charge.get_cust_balance(ps.customer_id,
ps.customer_site_use_id,
ps.invoice_currency_code,
ps.org_id,
sysparam.post_billing_item_inclusion,
ps.trx_date) balance,
cust_sites.last_accrue_charge_date,
cust_sites.exchange_rate_type,
cust_sites.min_interest_charge,
cust_sites.max_interest_charge,
cust_sites.interest_type,
cust_sites.interest_rate,
cust_sites.interest_fixed_amount,
cust_sites.interest_schedule_id,
cust_sites.late_charge_term_id,
cust_sites.message_text_id,
cust_sites.payment_grace_days,
cust_sites.min_fc_balance_overdue_type,
cust_sites.min_fc_balance_amount,
cust_sites.min_fc_balance_percent
from ar_payment_schedules ps,
ar_lc_cust_sites_t cust_sites,
ar_system_parameters sysparam
where ps.customer_id = cust_sites.customer_id
and decode(ps.class,
'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
'PMT',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
ps.customer_site_use_id) = cust_sites.customer_site_use_id
and ps.invoice_currency_code = cust_sites.currency_code
and ps.org_id = cust_sites.org_id
and cust_sites.late_charge_calculation_trx = 'AVG_DAILY_BALANCE'
and mod(nvl(cust_sites.customer_site_use_id,0),l_total_workers) =
decode(l_total_workers,l_worker_number,0,l_worker_number)
and sysparam.org_id = cust_sites.org_id
and sysparam.late_charge_billing_calc_mode = 'RUN_TO_RUN_DATE'
and decode(sysparam.post_billing_item_inclusion,
'INCLUDE_DEBIT_ITEM','Y',
'EXCLUDE_DEBIT_ITEM',decode(ps.class,
'PMT','Y',
'CM','Y',
'N'),
'N') = 'Y'
and ps.trx_date <= l_fin_charge_date
and ( ps.trx_date > (select max(ci2.billing_date)
from ar_cons_inv ci2
where ci2.customer_id = cust_sites.customer_id
and ci2.site_use_id = cust_sites.customer_site_use_id
and ci2.currency_code = cust_sites.currency_code
and ci2.org_id = cust_sites.org_id
and ci2.billing_date < l_fin_charge_date
and ci2.status in ('FINAL', 'ACCEPTED','IMPORTED'))
OR (
not exists (select ci2.billing_date
from ar_cons_inv ci2
where ci2.customer_id = cust_sites.customer_id
and ci2.site_use_id = cust_sites.customer_site_use_id
and ci2.currency_code = cust_sites.currency_code
and ci2.org_id = cust_sites.org_id
and ci2.billing_date < ps.trx_date
and ci2.status in ('FINAL', 'ACCEPTED','IMPORTED'))
and ps.trx_date >= decode(cust_sites.last_accrue_charge_date,
NULL,get_first_activity_date(cust_sites.customer_id,
cust_sites.customer_site_use_id,
cust_sites.currency_code,
cust_sites.org_id),
cust_sites.last_accrue_charge_date+1)))) a
where nvl(a.balance,0) <> 0
group by a.customer_id,
a.site_use_id,
a.currency_code,
a.org_id,
a.last_accrue_charge_date,
a.exchange_rate_type,
a.min_interest_charge,
a.max_interest_charge,
a.interest_type,
a.interest_rate,
a.interest_fixed_amount,
a.interest_schedule_id,
a.late_charge_term_id,
a.message_text_id,
a.payment_grace_days,
a.min_fc_balance_overdue_type,
a.min_fc_balance_amount,
a.min_fc_balance_percent)b
/* Apply the customer level tolerance - check for eligibility */
where ar_calc_late_charge.check_adb_eligibility(b.customer_id,
b.site_use_id,
b.currency_code,
b.org_id,
b.payment_grace_days,
b.min_fc_balance_overdue_type,
b.min_fc_balance_amount,
b.min_fc_balance_percent,
l_fin_charge_date) = 'Y');
/* Insert records for the system option set up DUE DATE TO RUN DATE */
insert into ar_late_charge_trx_t
(late_charge_trx_id,
customer_id,
customer_site_use_id,
currency_code,
customer_trx_id,
legal_entity_id,
payment_schedule_id,
class,
amount_due_original,
amount_due_remaining,
fin_charge_charged,
trx_date,
cust_trx_type_id,
last_charge_date,
exchange_rate_type,
min_interest_charge,
max_interest_charge,
overdue_late_pay_amount,
original_balance,
due_date,
receipt_date,
finance_charge_date,
charge_type,
actual_date_closed,
interest_rate,
interest_days,
rate_start_date,
rate_end_date,
schedule_days_start,
schedule_days_to,
late_charge_amount,
late_charge_type,
late_charge_term_id,
interest_period_days,
interest_calculation_period,
charge_on_finance_charge_flag,
message_text_id,
interest_type,
min_fc_invoice_overdue_type,
min_fc_invoice_amount,
min_fc_invoice_percent,
charge_line_type,
org_id,
request_id,
display_flag)
(
select ar_late_charge_trx_s.nextval,
b.customer_id,
b.site_use_id,
b.currency_code,
NULL, -- customer_trx_id
NULL, -- How to determine this?
-99, -- payment_schedule_id
NULL, -- class, search for some look up for cons_inv
NULL, -- amount_due_original
NULL, -- amount_due_remaining
NULL, -- fin_charge_already_charged
NULL, -- trx_date
NULL, --cust_trx_type is not applicable
b.last_accrue_charge_date,
b.exchange_rate_type,
b.min_interest_charge,
b.max_interest_charge,
b.overdue_late_pay_amount,
b.overdue_late_pay_amount original_balance,
NULL, --Due date not applicable
NULL, --receipt_date
l_fin_charge_date,
'AVERAGE_DAILY_BALANCE',
NULL,
decode(b.interest_type,
'FIXED_RATE',b.interest_rate, NULL) interest_rate,
b.tot_days interest_days,
NULL, -- rate start date
NULL, -- rate end date
NULL, -- bucket days start
NULL, -- bucket days to
decode(b.interest_type,
'FIXED_AMOUNT', b.interest_fixed_amount,
'FIXED_RATE',
ar_calc_late_charge.currency_round(nvl(b.overdue_late_pay_amt_org /100* b.interest_rate,0),
b.currency_code)) late_charge_amount,
'INV' ,
b.late_charge_term_id,
NULL, -- interest_period_days not applicable
NULL, -- interest_calculation_period not applicable
'F' , -- only flat rate is applicable
b.message_text_id,
b.interest_type,
NULL, -- invoice level tolerances are not applicable (min_fc_invoice_overdue_type)
NULL, -- min_fc_invoice_amount
NULL, -- min_fc_invoice_percent
'INTEREST',
b.org_id,
l_request_id,
'Y'
from (
select a.customer_id,
a.site_use_id,
a.currency_code,
a.org_id,
a.last_accrue_charge_date,
a.exchange_rate_type,
a.min_interest_charge,
a.max_interest_charge,
a.interest_type,
a.interest_rate,
a.interest_fixed_amount,
a.interest_schedule_id,
a.late_charge_term_id,
a.message_text_id,
a.payment_grace_days,
a.min_fc_balance_overdue_type,
a.min_fc_balance_amount,
a.min_fc_balance_percent,
ar_calc_late_charge.currency_round(sum(a.balance * (a.date_to - a.date_from+1)) /
sum(a.date_to - a.date_from+1),
a.currency_code) overdue_late_pay_amount,
sum(a.balance * (a.date_to - a.date_from+1)) /
sum(a.date_to - a.date_from+1) overdue_late_pay_amt_org,
sum(a.balance * (a.date_to - a.date_from+1)) tot_amt,
sum(a.date_to - a.date_from+1) tot_days
from
(
select cons_inv.customer_id,
cons_inv.site_use_id,
cons_inv.currency_code,
cons_inv.org_id,
cons_inv.due_date+1 date_from,
decode(sign(l_fin_charge_date-
ar_calc_late_charge.get_next_activity_date(cons_inv.customer_id,
cons_inv.site_use_id,
cons_inv.currency_code,
cons_inv.org_id,
sysparam.post_billing_item_inclusion,
cons_inv.due_date+1,
l_fin_charge_date)),
-1,l_fin_charge_date,
ar_calc_late_charge.get_next_activity_date(cons_inv.customer_id,
cons_inv.site_use_id,
cons_inv.currency_code,
cons_inv.org_id,
sysparam.post_billing_item_inclusion,
cons_inv.due_date+1,
l_fin_charge_date)) date_to,
ar_calc_late_charge.get_cust_balance(cons_inv.customer_id,
cons_inv.site_use_id,
cons_inv.currency_code,
cons_inv.org_id,
sysparam.post_billing_item_inclusion,
cons_inv.due_date) balance,
cust_sites.last_accrue_charge_date,
cust_sites.exchange_rate_type,
cust_sites.min_interest_charge,
cust_sites.max_interest_charge,
cust_sites.interest_type,
cust_sites.interest_rate,
cust_sites.interest_fixed_amount,
cust_sites.interest_schedule_id,
cust_sites.late_charge_term_id,
cust_sites.message_text_id,
cust_sites.payment_grace_days,
cust_sites.min_fc_balance_overdue_type,
cust_sites.min_fc_balance_amount,
cust_sites.min_fc_balance_percent
from ar_cons_inv cons_inv,
ar_lc_cust_sites_t cust_sites,
ar_system_parameters sysparam
where cons_inv.customer_id = cust_sites.customer_id
and cons_inv.site_use_id = cust_sites.customer_site_use_id
and cons_inv.currency_code = cust_sites.currency_code
and mod(nvl(cust_sites.customer_site_use_id,0),l_total_workers) =
decode(l_total_workers,l_worker_number,0,l_worker_number)
and cons_inv.org_id = cust_sites.org_id
and cons_inv.billing_date = (select max(ci2.billing_date)
from ar_cons_inv ci2
where ci2.customer_id = cust_sites.customer_id
and ci2.site_use_id = cust_sites.customer_site_use_id
and ci2.currency_code = cust_sites.currency_code
and ci2.org_id = cust_sites.org_id
and ci2.billing_date <= l_fin_charge_date
and ci2.status in ('FINAL', 'ACCEPTED','IMPORTED'))
and cust_sites.late_charge_calculation_trx = 'AVG_DAILY_BALANCE'
and sysparam.org_id = cons_inv.org_id
and sysparam.late_charge_billing_calc_mode = 'DUE_TO_RUN_DATE'
union
/* select distinct : even if more than one item exists with the same trx_date,
consider this date only once */
select cust_sites.customer_id,
cust_sites.customer_site_use_id,
cust_sites.currency_code,
cust_sites.org_id,
ps.trx_date,
decode(sign(l_fin_charge_date -
ar_calc_late_charge.get_next_activity_date(ps.customer_id,
ps.customer_site_use_id,
ps.invoice_currency_code,
ps.org_id,
sysparam.post_billing_item_inclusion,
ps.trx_date,
l_fin_charge_date)),
-1, l_fin_charge_date,
ar_calc_late_charge.get_next_activity_date(ps.customer_id,
ps.customer_site_use_id,
ps.invoice_currency_code,
ps.org_id,
sysparam.post_billing_item_inclusion,
ps.trx_date,
l_fin_charge_date)) date_to,
ar_calc_late_charge.get_cust_balance(ps.customer_id,
ps.customer_site_use_id,
ps.invoice_currency_code,
ps.org_id,
sysparam.post_billing_item_inclusion,
ps.trx_date) balance,
cust_sites.last_accrue_charge_date,
cust_sites.exchange_rate_type,
cust_sites.min_interest_charge,
cust_sites.max_interest_charge,
cust_sites.interest_type,
cust_sites.interest_rate,
cust_sites.interest_fixed_amount,
cust_sites.interest_schedule_id,
cust_sites.late_charge_term_id,
cust_sites.message_text_id,
cust_sites.payment_grace_days,
cust_sites.min_fc_balance_overdue_type,
cust_sites.min_fc_balance_amount,
cust_sites.min_fc_balance_percent
from ar_payment_schedules ps,
ar_lc_cust_sites_t cust_sites,
ar_system_parameters sysparam
where ps.customer_id = cust_sites.customer_id
and decode(ps.class,
'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
'PMT',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
ps.customer_site_use_id) = cust_sites.customer_site_use_id
and ps.invoice_currency_code = cust_sites.currency_code
and ps.org_id = cust_sites.org_id
and cust_sites.late_charge_calculation_trx = 'AVG_DAILY_BALANCE'
and mod(nvl(cust_sites.customer_site_use_id,0),l_total_workers) =
decode(l_total_workers,l_worker_number,0,l_worker_number)
and sysparam.org_id = cust_sites.org_id
and sysparam.late_charge_billing_calc_mode = 'DUE_TO_RUN_DATE'
and decode(sysparam.post_billing_item_inclusion,
'INCLUDE_DEBIT_ITEM','Y',
'EXCLUDE_DEBIT_ITEM',decode(ps.class,
'PMT','Y',
'CM','Y',
'N'),
'N') = 'Y'
and ps.trx_date <= l_fin_charge_date
and ps.trx_date > (select max(ci2.due_date)
from ar_cons_inv ci2
where ci2.customer_id = cust_sites.customer_id
and ci2.site_use_id = cust_sites.customer_site_use_id
and ci2.currency_code = cust_sites.currency_code
and ci2.org_id = cust_sites.org_id
and ci2.billing_date < l_fin_charge_date
and ci2.status in ('FINAL', 'ACCEPTED','IMPORTED')))a
where nvl(a.balance,0) <> 0
/* Apply the customer level tolerance - check for eligibility */
and ar_calc_late_charge.check_adb_eligibility(a.customer_id,
a.site_use_id,
a.currency_code,
a.org_id,
a.payment_grace_days,
a.min_fc_balance_overdue_type,
a.min_fc_balance_amount,
a.min_fc_balance_percent,
l_fin_charge_date) = 'Y'
group by a.customer_id,
a.site_use_id,
a.currency_code,
a.org_id,
a.last_accrue_charge_date,
a.exchange_rate_type,
a.min_interest_charge,
a.max_interest_charge,
a.interest_type,
a.interest_rate,
a.interest_fixed_amount,
a.interest_schedule_id,
a.late_charge_term_id,
a.message_text_id,
a.payment_grace_days,
a.min_fc_balance_overdue_type,
a.min_fc_balance_amount,
a.min_fc_balance_percent) b
/* Make sure that this customer, site and currency combination is not
part of a failed final batch */
where not exists (select 'exists failed batch'
from ar_interest_headers hdr,
ar_interest_batches bat
where hdr.customer_id = b.customer_id
and hdr.customer_site_use_id = b.site_use_id
and hdr.currency_code = b.currency_code
and hdr.interest_batch_id = bat.interest_batch_id
and bat.batch_status ='F'
and bat.transferred_status <> 'S'));
debug( 'ar_calc_late_charge. insert_int_avg_daily_bal()-' );
debug('EXCEPTION: ar_calc_late_charge. insert_int_avg_daily_bal' );
END insert_int_avg_daily_bal;
PROCEDURE insert_cust_balances(p_as_of_date IN DATE,
p_worker_number IN NUMBER,
p_total_workers IN NUMBER) IS
BEGIN
IF l_debug_flag = 'Y' THEN
debug( 'ar_calc_late_charge.insert_cust_balances()+');
INSERT INTO ar_late_charge_cust_balance_gt
(late_charge_cust_sites_id,
customer_id,
customer_site_use_id,
currency_code,
customer_open_balance,
customer_overdue_balance,
org_id)
(SELECT a.lc_cust_sites_id,
a.customer_id,
a.customer_site_use_id,
a.currency_code,
sum(open_bal) open_balance,
sum(overdue_bal) overdue_balance,
org_id
FROM
(SELECT /*+ cardinality(cust_site,1) leading(cust_site ps) use_nl(cust_site ps) index(ps AR_PAYMENT_SCHEDULES_N6) */
cust_site.lc_cust_sites_id,
ps.customer_id,
cust_site.customer_site_use_id,
ps.invoice_currency_code currency_code,
sum(ps.amount_due_remaining) open_bal,
sum((case when (decode(ps.class,'CM',ps.due_date,ps.due_date + NVL(cust_site.payment_grace_days,0))) < p_as_of_date then 1 else 0 end)
* ps.amount_due_remaining) overdue_bal,
ps.org_id
FROM ar_payment_schedules_all ps,
ar_lc_cust_sites_t cust_site,
ra_customer_trx ct,
ra_cust_trx_types typ
WHERE ps.customer_id = cust_site.customer_id
AND ps.customer_trx_id = ct.customer_trx_id --Bug12884574
AND ct.cust_trx_type_id = typ.cust_trx_type_id --Bug12884574
AND ct.org_id = typ.org_id --Bug12884574
AND DECODE(nvl(typ.exclude_from_late_charges, 'N'), 'Y', 'X','N', decode(nvl(ct.FINANCE_CHARGES,'Y'), 'Y','Y','N','X')) = 'Y' --Bug12884574
AND cust_site.customer_site_use_id = decode(ps.class,
'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
ps.customer_site_use_id)
AND ps.invoice_currency_code = cust_site.currency_code
AND mod(nvl(cust_site.customer_site_use_id,0),p_total_workers) =
decode(p_total_workers,p_worker_number,0,p_worker_number)
AND ps.org_id = cust_site.org_id
AND ps.payment_schedule_id+0 > 0
AND ps.actual_date_closed >= p_as_of_date
AND ps.class IN ('CB', 'CM','DEP','DM','GUAR','INV','BR')
AND ps.trx_date <= p_as_of_date
GROUP BY cust_site.lc_cust_sites_id,
ps.customer_id,
cust_site.customer_site_use_id,
ps.invoice_currency_code,
ps.org_id
UNION ALL
SELECT cust_site.lc_cust_sites_id,
ps.customer_id,
cust_site.customer_site_use_id,
ps.invoice_currency_code currency_code,
sum(ra.amount_applied + NVL(ra.earned_discount_taken,0)
+ NVL(ra.unearned_discount_taken,0))open_bal,
sum((case when ps.due_date + NVL(cust_site.payment_grace_days,0) < p_as_of_date then 1 else 0 end)
* (ra.amount_applied + NVL(ra.earned_discount_taken,0)
+ NVL(ra.unearned_discount_taken,0))) overdue_bal,
ps.org_id
FROM ar_payment_schedules ps,
ar_receivable_applications ra,
ar_payment_schedules ps_cm_cr,
ar_lc_cust_sites_t cust_site,
ra_customer_trx ct,
ra_cust_trx_types typ
WHERE ps.customer_id = cust_site.customer_id
AND ct.customer_trx_id = ps.customer_trx_id --Bug12884574
AND ct.cust_trx_type_id = typ.cust_trx_type_id --Bug12884574
AND ct.org_id = typ.org_id --Bug12884574
AND DECODE(nvl(typ.exclude_from_late_charges, 'N'), 'Y', 'X','N', decode(nvl(ct.FINANCE_CHARGES,'Y'), 'Y','Y','N','X')) = 'Y' --Bug12884574
AND cust_site.customer_site_use_id = decode(ps.class,
'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
ps.customer_site_use_id)
AND ps.invoice_currency_code = cust_site.currency_code
AND mod(nvl(cust_site.customer_site_use_id,0),p_total_workers) =
decode(p_total_workers,p_worker_number,0,p_worker_number)
AND ps.org_id = cust_site.org_id
AND ra.applied_payment_schedule_id = ps.payment_schedule_id
AND ps.actual_date_closed < p_as_of_date
AND ps.actual_date_closed >= nvl(cust_site.last_accrue_charge_date,ps.actual_date_closed)
AND ps.actual_date_closed > ps.due_date + nvl(cust_site.payment_grace_days,0)
AND nvl(ps.actual_date_closed,p_As_of_Date) between nvl(ps.last_charge_date,p_as_of_date) and p_as_of_date
/*bug 14202135, replaced this */
/*nvl(ps.last_charge_date,p_As_of_Date) between nvl(cust_site.last_accrue_charge_date,p_as_of_date) and p_as_of_date*/
AND ps.class IN ('CB','DEP','DM','GUAR','INV','BR')
AND ra.status = 'APP'
AND ps.trx_date <=ps.due_date
AND NVL(ra.confirmed_flag,'Y') = 'Y'
AND ps_cm_cr.payment_schedule_id = ra.payment_schedule_id
AND ps_cm_cr.trx_date > ps.due_date
AND cust_site.late_charge_calculation_trx <> 'OVERDUE'
GROUP BY cust_site.lc_cust_sites_id, ps.customer_id, cust_site.customer_site_use_id, ps.invoice_currency_code, ps.org_id
UNION ALL
SELECT cust_site.lc_cust_sites_id,
ps.customer_id,
cust_site.customer_site_use_id,
ps.invoice_currency_code currency_code,
sum(ra.amount_applied + NVL(ra.earned_discount_taken,0)
+ NVL(ra.unearned_discount_taken,0))open_bal,
sum((case when ps.due_date + NVL(cust_site.payment_grace_days,0) < p_as_of_date then 1 else 0 end)
* (ra.amount_applied + NVL(ra.earned_discount_taken,0)
+ NVL(ra.unearned_discount_taken,0))) overdue_bal,
ps.org_id
FROM ar_payment_schedules ps,
ar_receivable_applications ra,
ar_payment_schedules ps_cm_cr,
ar_lc_cust_sites_t cust_site,
ra_customer_trx ct,
ra_cust_trx_types typ
WHERE ps.customer_id = cust_site.customer_id
AND ct.customer_trx_id = ps.customer_trx_id --Bug12884574
AND ct.cust_trx_type_id = typ.cust_trx_type_id --Bug12884574
AND ct.org_id = typ.org_id --Bug12884574
AND DECODE(nvl(typ.exclude_from_late_charges, 'N'), 'Y', 'X','N', decode(nvl(ct.FINANCE_CHARGES,'Y'), 'Y','Y','N','X')) = 'Y' --Bug12884574
AND cust_site.customer_site_use_id = decode(ps.class,
'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
ps.customer_site_use_id)
AND ps.invoice_currency_code = cust_site.currency_code
AND mod(nvl(cust_site.customer_site_use_id,0),p_total_workers) =
decode(p_total_workers,p_worker_number,0,p_worker_number)
AND ps.org_id = cust_site.org_id
AND ra.applied_payment_schedule_id = ps.payment_schedule_id
AND ps.actual_date_closed >= p_as_of_date
AND ps.class IN ('CB', 'CM','DEP','DM','GUAR','INV','BR')
AND ra.status = 'APP'
AND ps.trx_date <= p_as_of_date
AND NVL(ra.confirmed_flag,'Y') = 'Y'
AND ps_cm_cr.payment_schedule_id = ra.payment_schedule_id
AND ps_cm_cr.trx_date > p_as_of_date
GROUP BY cust_site.lc_cust_sites_id,
ps.customer_id,
cust_site.customer_site_use_id,
ps.invoice_currency_code,
ps.org_id
UNION ALL
SELECT cust_site.lc_cust_sites_id,
ps.customer_id,
cust_site.customer_site_use_id,
ps.invoice_currency_code currency_code,
sum(-1*(ra.amount_applied + NVL(ra.earned_discount_taken,0)
+ NVL(ra.unearned_discount_taken,0))) open_bal,
sum (-1*(case when ps.due_date < p_as_of_date then 1 else 0 end)
*(ra.amount_applied + NVL(ra.earned_discount_taken,0)
+ NVL(ra.unearned_discount_taken,0))) overdue_bal,
ps.org_id
FROM ar_payment_schedules ps,
ar_receivable_applications ra,
ar_lc_cust_sites_t cust_site,
ra_customer_trx ct,
ra_cust_trx_types typ
WHERE ps.customer_id = cust_site.customer_id
AND ct.customer_trx_id = ps.customer_trx_id --Bug12884574
AND ct.cust_trx_type_id = typ.cust_trx_type_id --Bug12884574
AND ct.org_id = typ.org_id --Bug12884574
AND DECODE(nvl(typ.exclude_from_late_charges, 'N'), 'Y', 'X','N', decode(nvl(ct.FINANCE_CHARGES,'Y'), 'Y','Y','N','X')) = 'Y' --Bug12884574
AND cust_site.customer_site_use_id = decode(ps.class,
'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
ps.customer_site_use_id)
AND ps.invoice_currency_code = cust_site.currency_code
AND mod(nvl(cust_site.customer_site_use_id,0),p_total_workers) =
decode(p_total_workers,p_worker_number,0,p_worker_number)
AND ps.org_id = cust_site.org_id
AND ra.payment_schedule_id = ps.payment_schedule_id
AND ps.payment_schedule_id+0 > 0
AND ps.actual_date_closed >= p_as_of_date
AND ps.class = 'CM'
AND ra.apply_date > p_as_of_date
AND ra.status = 'APP'
AND ra.application_type = 'CM'
AND ps.trx_date <= p_as_of_date
AND NVL(ra.confirmed_flag,'Y') = 'Y'
GROUP BY cust_site.lc_cust_sites_id,
ps.customer_id,
cust_site.customer_site_use_id,
ps.invoice_currency_code,
ps.org_id
UNION ALL
SELECT cust_site.lc_cust_sites_id,
ps.customer_id,
cust_site.customer_site_use_id,
ps.invoice_currency_code currency_code,
sum(-1 *adj.amount) open_bal,
sum(-1*(case when (decode(ps.class,'CM',ps.due_date,ps.due_date + NVL(cust_site.payment_grace_days,0))) < p_as_of_date then 1 else 0 end)
*(adj.amount)) overdue_bal,
ps.org_id
FROM ar_adjustments adj ,
ar_payment_schedules ps,
ar_lc_cust_sites_t cust_site,
ra_customer_trx ct,
ra_cust_trx_types typ
WHERE ps.customer_id = cust_site.customer_id
AND ct.customer_trx_id = ps.customer_trx_id --Bug12884574
AND ct.cust_trx_type_id = typ.cust_trx_type_id --Bug12884574
AND ct.org_id = typ.org_id --Bug12884574
AND DECODE(nvl(typ.exclude_from_late_charges, 'N'), 'Y', 'X','N', decode(nvl(ct.FINANCE_CHARGES,'Y'), 'Y','Y','N','X')) = 'Y' --Bug12884574
AND cust_site.customer_site_use_id = decode(ps.class,
'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
ps.customer_site_use_id)
AND mod(nvl(cust_site.customer_site_use_id,0),p_total_workers) =
decode(p_total_workers,p_worker_number,0,p_worker_number)
AND ps.org_id = cust_site.org_id
AND ps.payment_schedule_id + 0 > 0
AND ps.actual_date_closed >= p_as_of_date
AND ps.class IN ('CB', 'CM','DEP','DM','GUAR','INV','BR')
AND ps.trx_date <= p_as_of_date
AND adj.payment_schedule_id = ps.payment_schedule_id
AND adj.apply_date > p_as_of_date
AND adj.status = 'A'
GROUP BY cust_site.lc_cust_sites_id,
ps.customer_id,
cust_site.customer_site_use_id,
ps.invoice_currency_code,
ps.org_id
UNION ALL
/* Unapplied Receipts
For receipts, consider the trx_date instead of the due_date*/
SELECT cust_site.lc_cust_sites_id,
ps.customer_id,
cust_site.customer_site_use_id,
ps.invoice_currency_code currency_code,
sum(ps.amount_due_remaining) open_bal,
sum(ps.amount_due_remaining) overdue_bal,
ps.org_id
FROM ar_payment_schedules ps,
ar_lc_cust_sites_t cust_site
WHERE ps.customer_id = cust_site.customer_id
AND cust_site.customer_site_use_id = ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id)
AND ps.invoice_currency_code = cust_site.currency_code
AND mod(nvl(cust_site.customer_site_use_id,0),p_total_workers) =
decode(p_total_workers,p_worker_number,0,p_worker_number)
AND ps.org_id = cust_site.org_id
AND ps.class = 'PMT'
AND ps.actual_date_closed >= p_as_of_date
AND nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y'
AND ps.trx_date <= p_as_of_date
GROUP BY cust_site.lc_cust_sites_id,
ps.customer_id,
cust_site.customer_site_use_id,
ps.invoice_currency_code,
ps.org_id
UNION ALL
/* Cancelled BR */
SELECT cust_site.lc_cust_sites_id,
ps.customer_id,
cust_site.customer_site_use_id,
ps.invoice_currency_code currency_code,
sum(decode(nvl(ard.amount_cr,0), 0, nvl(ard.amount_dr,0),
(ard.amount_cr * -1)))open_bal,
sum((case when ps.trx_date < p_as_of_date then 1 else 0 end)
*(decode(nvl(ard.amount_cr,0), 0, nvl(ard.amount_dr,0),
(ard.amount_cr * -1)))) overdue_bal,
ps.org_id
FROM ar_payment_schedules ps,
ar_distributions ard,
ar_transaction_history ath,
ra_customer_trx_lines lines,
ar_lc_cust_sites_t cust_site,
ra_customer_trx ct,
ra_cust_trx_types typ
WHERE ps.customer_id = cust_site.customer_id
AND ps.customer_trx_id = ct.customer_trx_id --Bug12884574
AND ct.cust_trx_type_id = typ.cust_trx_type_id --Bug12884574
AND ct.org_id = typ.org_id --Bug12884574
AND DECODE(nvl(typ.exclude_from_late_charges, 'N'), 'Y', 'X','N', decode(nvl(ct.FINANCE_CHARGES,'Y'), 'Y','Y','N','X')) = 'Y' --Bug12884574
AND cust_site.customer_site_use_id = decode(ps.class,
'BR',ar_calc_late_charge.get_bill_to_site_use_id(ps.customer_id,
ps.customer_site_use_id,
ps.org_id),
ps.customer_site_use_id)
AND ps.invoice_currency_code = cust_site.currency_code
AND mod(nvl(cust_site.customer_site_use_id,0),p_total_workers) =
decode(p_total_workers,p_worker_number,0,p_worker_number)
AND ps.org_id = cust_site.org_id
AND ps.payment_schedule_id+0 > 0
AND ps.actual_date_closed >= p_as_of_date
AND ps.class IN ( 'BR','CB', 'CM','DEP','DM','GUAR','INV')
AND ath.trx_date > p_as_of_date
AND ath.event = 'CANCELLED'
AND ps.trx_date <= p_as_of_date
AND ps.customer_trx_id = ath.customer_trx_id
AND ard.source_table = 'TH'
AND ard.source_id = ath.transaction_history_id
AND ps.customer_trx_id = lines.customer_trx_id
AND ard.source_id_secondary = lines.customer_trx_line_id
GROUP BY cust_site.lc_cust_sites_id,
ps.customer_id,
cust_site.customer_site_use_id,
ps.invoice_currency_code,
ps.org_id) a
GROUP BY a.lc_cust_sites_id,
a.customer_id,
a.customer_site_use_id,
a.currency_code,
a.org_id);
debug( 'ar_calc_late_charge.insert_cust_balances()-');
debug('EXCEPTION: ar_calc_late_charge.insert_cust_balances' );
END insert_cust_balances;
| PROCEDURE insert_penalty_lines |
| |
| DESCRIPTION |
| |
| This procedure calculates the penalty against a payment schedule by adding the |
| all the interest charged against it. The penalty is either a fixed amount or a |
| percentage of the interest charge. The calculated Penalty is inserted back into |
| ar_late_charge_trx_t |
| |
| PSEUDO CODE/LOGIC |
| |
| |
| PARAMETERS |
| |
| |
| KNOWN ISSUES |
| |
| NOTES |
| |
| Since we are storing the balance as of the receipt date in the Original balance column |
| in ar_late_charge_trx_t (in the case of LATE payments) , this column is not inserted |
| for PENALTY lines. The receipt_date is also inserted as NULL as there can be multiple |
| receipts against one payment schedule, but only one penalty line |
| |
| MODIFICATION HISTORY |
| Date Author Description of Changes |
| |
| 20-FEB-2006 rkader Created |
| |
*=========================================================================================*/
PROCEDURE insert_penalty_lines(p_worker_number IN NUMBER,
p_total_workers IN NUMBER) IS
BEGIN
IF l_debug_flag = 'Y' THEN
debug( 'ar_calc_late_charge.insert_penalty_lines()+' );
insert into ar_late_charge_trx_t
( late_charge_trx_id,
customer_id,
customer_site_use_id,
currency_code,
customer_trx_id,
legal_entity_id,
payment_schedule_id,
class,
amount_due_original,
amount_due_remaining,
fin_charge_charged,
trx_date,
cust_trx_type_id,
last_charge_date,
exchange_rate_type,
min_interest_charge,
max_interest_charge,
overdue_late_pay_amount,
original_balance,
due_date,
receipt_date,
finance_charge_date,
charge_type,
actual_date_closed,
interest_rate,
interest_days,
rate_start_date,
rate_end_date,
schedule_days_start,
schedule_days_to,
late_charge_amount,
late_charge_type,
late_charge_term_id,
interest_period_days,
interest_calculation_period,
charge_on_finance_charge_flag,
message_text_id,
interest_type,
charge_line_type,
org_id,
request_id,
display_flag)
(select ar_late_charge_trx_s.nextval,
a.customer_id,
a.customer_site_use_id,
a.currency_code,
a.customer_trx_id,
a.legal_entity_id,
a.payment_schedule_id,
a.class,
a.amount_due_original,
a.amount_due_remaining,
a.fin_charge_charged,
a.trx_date,
a.cust_trx_type_id,
a.last_charge_date,
a.exchange_rate_type,
a.min_interest_charge,
a.max_interest_charge,
a.interest,
a.original_balance,
a.due_date,
a.receipt_date,
a.finance_charge_date,
decode(a.charge_type,
'AVERAGE_DAILY_BALANCE', a.charge_type,
'PENALTY') charge_type,
a.actual_date_closed,
a.penalty_rate,
decode(a.charge_type,
'AVERAGE_DAILY_BALANCE',a.interest_days,
(a.finance_charge_date - nvl(a.last_charge_date, a.due_date))) interest_days,
a.rate_start_date,
a.rate_end_date,
a.schedule_days_start,
a.schedule_days_to,
NVL(a.penalty_amount,ar_calc_late_charge.currency_round(nvl(a.penalty_rate,0) * a.interest/100,
a.currency_code)),
a.late_charge_type,
a.late_charge_term_id,
a.interest_period_days,
a.interest_calculation_period,
a.charge_on_finance_charge_flag,
a.message_text_id,
a.penalty_type,
'PENALTY',
a.org_id,
l_request_id,
'Y'
from
(select trx.customer_id,
trx.customer_site_use_id,
trx.currency_code,
trx.customer_trx_id,
trx.legal_entity_id,
trx.payment_schedule_id,
trx.class,
trx.amount_due_original,
trx.amount_due_remaining,
trx.fin_charge_charged,
trx.trx_date,
trx.cust_trx_type_id,
trx.last_charge_date,
trx.exchange_rate_type,
trx.min_interest_charge,
trx.max_interest_charge,
sum(trx.late_charge_amount) interest,
NULL original_balance,
trx.due_date,
NULL receipt_date,
trx.finance_charge_date,
trx.actual_date_closed,
decode(cust_site.penalty_type, 'CHARGES_SCHEDULE', sched_lines.rate,
'FIXED_RATE', cust_site.penalty_rate,
NULL) penalty_rate,
sched_hdrs.start_date rate_start_date,
sched_hdrs.end_date rate_end_date ,
bucket_lines.days_start schedule_days_start,
bucket_lines.days_to schedule_days_to,
decode(cust_site.penalty_type,
'FIXED_AMOUNT',decode(trx.class,
'CM',-1 * cust_site.penalty_fixed_amount,
'PMT', -1*cust_site.penalty_fixed_amount,
cust_site.penalty_fixed_amount),
'CHARGES_SCHEDULE',decode(sched_hdrs.schedule_header_type,
'AMOUNT',decode(trx.class,
'CM', -1*sched_lines.amount,
'PMT',-1*sched_lines.amount,
sched_lines.amount),
NULL),
'CHARGE_PER_TIER',decode(sched_hdrs.schedule_header_type,
'AMOUNT',decode(trx.class,
'CM', -1*sched_lines.amount,
'PMT',-1*sched_lines.amount,
sched_lines.amount),
NULL), /*Enhacement 6469663*/
NULL) penalty_amount,
trx.late_charge_type,
trx.late_charge_term_id,
trx.interest_period_days,
trx.interest_calculation_period,
trx.charge_on_finance_charge_flag,
trx.message_text_id,
trx.org_id,
decode(trx.charge_type,'AVERAGE_DAILY_BALANCE',trx.interest_days,1) interest_days,
decode(trx.charge_type,'AVERAGE_DAILY_BALANCE',trx.charge_type, NULL) charge_type,
cust_site.penalty_type
from ar_lc_cust_sites_t cust_site,
ar_late_charge_trx_t trx,
ar_charge_schedule_hdrs sched_hdrs,
ar_charge_schedule_lines sched_lines,
ar_aging_bucket_lines bucket_lines
where cust_site.customer_id = trx.customer_id
and cust_site.customer_site_use_id = trx.customer_site_use_id
and cust_site.currency_code = trx.currency_code
and mod(nvl(cust_site.customer_site_use_id,0),p_total_workers) =
decode(p_total_workers,p_worker_number,0,p_worker_number)
and cust_site.org_id = trx.org_id
and cust_site.penalty_schedule_id = sched_hdrs.schedule_id(+)
and sched_hdrs.schedule_header_id = sched_lines.schedule_header_id(+)
and sched_hdrs.schedule_id = sched_lines.schedule_id(+)
and nvl(sched_hdrs.status,'A') = 'A'
and sched_lines.aging_bucket_id = bucket_lines.aging_bucket_id(+)
and sched_lines.aging_bucket_line_id = bucket_lines.aging_bucket_line_id(+)
/* Calculate the penalty only if the penalty type is defined for the customer */
and cust_site.penalty_type IS NOT NULL
/* Condition 1: days late should be between the bucket lines start and end days
For ADB, the interest_days should be used. */
and (( trx.charge_type = 'AVERAGE_DAILY_BALANCE'
and trx.interest_days >= nvl(bucket_lines.days_start,trx.interest_days)
and trx.interest_days <= nvl(bucket_lines.days_to,trx.interest_days))
OR
(trx.charge_type <> 'AVERAGE_DAILY_BALANCE'
and (trx.finance_charge_date- trx.due_date) >=nvl(bucket_lines.days_start,
(trx.finance_charge_date- trx.due_date))
and (trx.finance_charge_date- trx.due_date) <= nvl(bucket_lines.days_to,
(trx.finance_charge_date- trx.due_date))))
/* The rate effective on the due date should be picked up. So, the due
date should fall between the start date and end date of the charge schedule
Condition 2: Start_date of the schedule should be less than or equal to the
due date
Condition 3: End date of the schedule should be greater than or equal to the
due date or it should be NULL
For Average Daily Balance, the rate effective on the charge calculation should be
picked up */
and ((trx.charge_type = 'AVERAGE_DAILY_BALANCE'
and trx.finance_charge_date >= nvl(sched_hdrs.start_date,trx.finance_charge_date)
and (trx.finance_charge_date <= sched_hdrs.end_date
OR sched_hdrs.end_date IS NULL))
OR
(trx.charge_type <> 'AVERAGE_DAILY_BALANCE'
and nvl(sched_hdrs.start_date,trx.due_date) <= trx.due_date
and ( sched_hdrs.end_date >= trx.due_date
OR sched_hdrs.end_date IS NULL)))
/* Create the panalty lines only if the late charge documents will be created out
of this record */
and trx.display_flag = 'Y'
/* Do not populate the Penalty lines if the invoice level tolerances are not met
For Average Daily Balance, there is no invoice level tolerances*/
and decode(trx.charge_type,
'AVERAGE_DAILY_BALANCE', nvl(trx.original_balance,0),
decode(trx.class,
'CM', nvl(trx.original_balance,0),
'PMT',nvl(trx.original_balance,0),
decode(trx.min_fc_invoice_overdue_type,
'AMOUNT',nvl(trx.min_fc_invoice_amount,0),
'PERCENT',(nvl(trx.min_fc_invoice_percent,0)
* trx.amount_due_original/100),
nvl(trx.original_balance,0)))) <= nvl(trx.original_balance,0)
group by trx.customer_id,
trx.customer_site_use_id,
trx.currency_code,
trx.customer_trx_id,
trx.legal_entity_id,
trx.payment_schedule_id,
trx.class,
trx.amount_due_original,
trx.amount_due_remaining,
trx.fin_charge_charged,
trx.trx_date,
trx.cust_trx_type_id,
trx.last_charge_date,
trx.exchange_rate_type,
trx.min_interest_charge,
trx.max_interest_charge,
trx.due_date,
trx.finance_charge_date,
decode(trx.charge_type,'AVERAGE_DAILY_BALANCE',trx.charge_type, NULL),
trx.actual_date_closed,
decode(cust_site.penalty_type, 'CHARGES_SCHEDULE', sched_lines.rate,
'FIXED_RATE', cust_site.penalty_rate,
NULL),
sched_hdrs.start_date,
sched_hdrs.end_date,
bucket_lines.days_start,
bucket_lines.days_to,
decode(cust_site.penalty_type,
'FIXED_AMOUNT',decode(trx.class,
'CM',-1 * cust_site.penalty_fixed_amount,
'PMT', -1*cust_site.penalty_fixed_amount,
cust_site.penalty_fixed_amount),
'CHARGES_SCHEDULE',decode(sched_hdrs.schedule_header_type,
'AMOUNT',decode(trx.class,
'CM', -1*sched_lines.amount,
'PMT',-1*sched_lines.amount,
sched_lines.amount),
NULL),
'CHARGE_PER_TIER',decode(sched_hdrs.schedule_header_type,
'AMOUNT',decode(trx.class,
'CM', -1*sched_lines.amount,
'PMT',-1*sched_lines.amount,
sched_lines.amount),
NULL), /*Enhacement 6469663*/
NULL),
trx.late_charge_type,
trx.org_id,
decode(trx.charge_type,'AVERAGE_DAILY_BALANCE',trx.interest_days,1),
trx.late_charge_term_id,
trx.interest_period_days,
trx.interest_calculation_period,
trx.charge_on_finance_charge_flag,
trx.message_text_id,
cust_site.penalty_type) a);
debug( 'ar_calc_late_charge.insert_penalty_lines()-' );
debug('EXCEPTION: ar_calc_late_charge.insert_penalty_lines' );
END insert_penalty_lines;
PROCEDURE delete_draft_batches(p_worker_number IN NUMBER,
p_total_workers IN NUMBER) IS
BEGIN
IF l_debug_flag = 'Y' THEN
debug('ar_calc_late_charge.delete_draft_batches()+');
/* delete lines first */
delete from ar_interest_lines
where interest_header_id in (select hdr.interest_header_id
from ar_interest_batches batch,
ar_interest_headers hdr
where batch.batch_status = 'D'
and batch.interest_batch_id = hdr.interest_batch_id
and mod(nvl(hdr.customer_site_use_id,0),p_total_workers) =
decode(p_total_workers,p_worker_number,0,p_worker_number)
and batch.request_id <> l_request_id
and exists (select late_charge_trx_id
from ar_late_charge_trx_t trx
where trx.customer_id = hdr.customer_id
and trx.customer_site_use_id = hdr.customer_site_use_id
and trx.currency_code = hdr.currency_code
and nvl(trx.legal_entity_id,-99) = nvl(hdr.legal_entity_id,-99)
and trx.org_id = hdr.org_id));
/* delete headers */
delete from ar_interest_headers hdr
where not exists (select interest_line_id
from ar_interest_lines lines
where hdr.interest_header_id = lines.interest_header_id)
and hdr.request_id <> l_request_id;
/* Deleting the empty batches are done later in delete_empty_batches */
IF l_debug_flag = 'Y' THEN
debug('ar_calc_late_charge.delete_draft_batches()-');
debug('EXCEPTION : ar_calc_late_charge.delete_draft_batches()');
END delete_draft_batches;
PROCEDURE insert_int_batches(p_operating_unit_id IN NUMBER,
p_batch_name IN VARCHAR2,
p_fin_charge_date IN DATE,
p_batch_status IN VARCHAR2,
p_gl_date IN DATE,
p_request_id IN NUMBER) IS
l_operating_unit_id number;
debug( 'ar_calc_late_charge.insert_int_batches()+' );
select meaning
into l_batch_name
from ar_lookups
where lookup_type = 'AR_LATE_CHARGE_LABELS'
and lookup_code = 'LATE_CHARGE_BATCH';
insert into ar_interest_batches
( interest_batch_id,
batch_name,
calculate_interest_to_date,
batch_status,
gl_date,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date,
transferred_status,
request_id,
org_id,
object_version_number)
(select ar_interest_batches_s.nextval,
l_batch_name||' '||ar_interest_batches_s2.nextval
||' '||to_char(l_fin_charge_date,'DD-Mon-YYYY'),
l_fin_charge_date,
l_batch_status,
l_gl_date,
sysdate,
pg_last_updated_by,
pg_last_update_login,
pg_last_updated_by,
sysdate,
'N',
l_srs_request_id,
sysparam.org_id,
1
from ar_system_parameters sysparam
where nvl(l_operating_unit_id,sysparam.org_id) = sysparam.org_id);
debug( 'ar_calc_late_charge.insert_int_batches()-' );
debug('EXCEPTION: ar_calc_late_charge.insert_int_batches' );
END insert_int_batches;
PROCEDURE insert_int_headers(p_fin_charge_date IN DATE,
p_worker_number IN NUMBER,
p_total_workers IN NUMBER) IS
BEGIN
IF l_debug_flag = 'Y' THEN
debug( 'ar_calc_late_charge.insert_int_headers()+' );
insert into ar_interest_headers
(interest_header_id,
interest_batch_id,
customer_id,
customer_site_use_id,
header_type,
currency_code,
cust_trx_type_id,
late_charge_calculation_trx,
credit_items_flag,
disputed_transactions_flag,
payment_grace_days,
late_charge_term_id,
interest_period_days,
interest_calculation_period,
charge_on_finance_charge_flag,
hold_charged_invoices_flag,
message_text_id,
multiple_interest_rates_flag,
charge_begin_date,
cust_acct_profile_amt_id,
exchange_rate,
exchange_rate_type,
min_fc_invoice_overdue_type,
min_fc_invoice_amount,
min_fc_invoice_percent,
min_fc_balance_overdue_type,
min_fc_balance_amount,
min_fc_balance_percent,
min_interest_charge,
max_interest_charge,
interest_type,
interest_rate,
interest_fixed_amount,
interest_schedule_id,
penalty_type,
penalty_rate,
penalty_fixed_amount,
penalty_schedule_id,
last_accrue_charge_date,
finance_charge_date,
customer_profile_id,
collector_id,
legal_entity_id,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date,
process_status,
process_message,
request_id,
worker_num,
object_version_number,
org_id,
display_flag)
(select hdr.interest_header_id,
bat.interest_batch_id,
hdr.customer_id,
hdr.customer_site_use_id,
cust_site.late_charge_type header_type,
hdr.currency_code,
decode(cust_site.late_charge_type,
'INV', sysparam.late_charge_inv_type_id,
'DM',sysparam.late_charge_dm_type_id) cust_trx_type_id,
cust_site.late_charge_calculation_trx,
cust_site.credit_items_flag,
cust_site.disputed_transactions_flag,
cust_site.payment_grace_days,
cust_site.late_charge_term_id,
cust_site.interest_period_days,
cust_site.interest_calculation_period,
cust_site.charge_on_finance_charge_flag,
cust_site.hold_charged_invoices_flag,
cust_site.message_text_id,
cust_site.multiple_interest_rates_flag,
cust_site.charge_begin_date,
cust_site.cust_acct_profile_amt_id,
cust_site.exchange_rate,
cust_site.exchange_rate_type,
cust_site.min_fc_invoice_overdue_type,
cust_site.min_fc_invoice_amount,
cust_site.min_fc_invoice_percent,
cust_site.min_fc_balance_overdue_type,
cust_site.min_fc_balance_amount,
cust_site.min_fc_balance_percent,
cust_site.min_interest_charge,
cust_site.max_interest_charge,
cust_site.interest_type,
cust_site.interest_rate,
cust_site.interest_fixed_amount,
cust_site.interest_schedule_id,
cust_site.penalty_type,
cust_site.penalty_rate,
cust_site.penalty_fixed_amount,
cust_site.penalty_schedule_id,
cust_site.last_accrue_charge_date,
p_fin_charge_date,
cust_site.customer_profile_id,
cust_site.collector_id,
hdr.legal_entity_id,
sysdate,
pg_last_updated_by,
pg_last_update_login,
pg_last_updated_by,
sysdate,
'N',
NULL,
l_request_id,
p_worker_number,
1,
cust_site.org_id,
hdr.display_flag
from (select lines.interest_header_id,
trx.customer_id,
trx.customer_site_use_id,
trx.legal_entity_id,
trx.currency_code,
trx.late_charge_type,
trx.org_id,
trx.display_flag
from ar_interest_lines lines,
ar_late_charge_trx_t trx
where lines.payment_schedule_id = trx.payment_schedule_id
and lines.org_id = trx.org_id
and lines.type=trx.charge_type
and not exists (select interest_header_id
from ar_interest_headers headers
where headers.interest_header_id = lines.interest_header_id)
and mod(nvl(trx.customer_site_use_id,0),p_total_workers) =
decode(p_total_workers,p_worker_number,0,p_worker_number)
/*Bug fix 5290709: If display flag is No, we should not consider this record if there is another record
existing with display flag Yes */
and ((trx.display_flag = 'Y' and sign(trx.late_charge_amount) <> 0)
OR (trx.display_flag = 'N' and not exists (select 1
from ar_late_charge_trx_t trx1
where trx1.payment_schedule_id = trx.payment_schedule_id
and trx1.display_flag = 'Y')))
and nvl(lines.cash_receipt_id,-1) = nvl(trx.cash_receipt_id,-1)
group by lines.interest_header_id,
trx.customer_id,
trx.customer_site_use_id,
trx.legal_entity_id,
trx.currency_code,
trx.late_charge_type,
trx.org_id,
trx.display_flag) hdr,
ar_lc_cust_sites_t cust_site,
ar_interest_batches bat,
ar_system_parameters sysparam
where hdr.customer_id = cust_site.customer_id
and hdr.customer_site_use_id = cust_site.customer_site_use_id
and hdr.currency_code = cust_site.currency_code
and mod(nvl(cust_site.customer_site_use_id,0),p_total_workers) =
decode(p_total_workers,p_worker_number,0,p_worker_number)
and hdr.late_charge_type = cust_site.late_charge_type
and hdr.org_id = cust_site.org_id
and bat.org_id = cust_site.org_id
and sysparam.org_id = cust_site.org_id
and bat.request_id = l_request_id);
debug( 'ar_calc_late_charge.insert_int_headers()-' );
debug('EXCEPTION: ar_calc_late_charge.insert_int_headers' );
END insert_int_headers;
| PROCEDURE INSERT_INT_LINES |
| |
| DESCRIPTION |
| |
| This procedure is used to insert interest and penalty lines to ar_interest_lines |
| table. |
| |
| PSEUDO CODE/LOGIC |
| |
| |
| The tolerances should be applied on the interest lines before inserting into |
| ar_interest_lines, mainly max_interest_charge. |
| The logic: |
| |
| 1. Sort the interest lines in the order of charge_line_type, rate_start_date, |
| charge_type,receipt_date and fin_charge_charged |
| 2. Calculate the running total of the calculated interest charge in that order |
| (in the select statement, it is denoted as late_charge_rtot) |
| 3. Tolerances are applicable only on charge_line_type = INTEREST |
| 4. At any point in this order of calculation , |
| a) if the running total is less than or equal to the maximum allowed interest |
| charge, we should take the charge as the calculated charge itself. |
| b) if the running total is greater than the maximum allowed interest charge |
| i) if the maximum allowed interest charge is less than the running total till |
| the previous line, that means the maximum interest is already covered by |
| the previous line atleast. So, that charge should be 0 for the current line |
| ii) otherwise, it should be the least of (the difference between the maximum |
| interest charge and the running total till the previous line) and the max |
| interest charge |
| |
| PARAMETERS |
| |
| |
| KNOWN ISSUES |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| Date Author Description of Changes |
| 22-DEC-2005 rkader Created |
| 21-SEP-2006 rkader Bug 5556598. Modified the logic as explained |
| above |
| Bug8556955 |
|22-JUN-2009 naneja Inserted data for new column cash_receipt_id |
*=========================================================================================*/
PROCEDURE insert_int_lines(p_worker_number IN NUMBER,
p_total_workers IN NUMBER) IS
BEGIN
IF l_debug_flag = 'Y' THEN
debug( 'ar_calc_late_charge.insert_int_lines()+' );
insert into ar_interest_lines
(interest_line_id,
interest_header_id,
payment_schedule_id,
type,
original_trx_class,
daily_interest_charge,
outstanding_amount,
days_overdue_late,
days_of_interest,
interest_charged,
payment_date,
finance_charge_charged,
amount_due_original,
amount_due_remaining,
original_trx_id,
receivables_trx_id,
last_charge_date,
due_date,
actual_date_closed,
interest_rate,
rate_start_date,
rate_end_date,
schedule_days_from,
schedule_days_to,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date,
process_status,
process_message,
org_id,
object_version_number,
cash_receipt_id)
(select ar_interest_lines_s.nextval interest_line_id,
b.interest_header_id,
b.payment_schedule_id,
b.charge_type,
b.class,
decode(b.class,
'PMT',b.late_charge_amount,
'CM', b.late_charge_amount,
decode(b.charge_line_type,
'INTEREST',
decode(sign(b.late_charge_rtot - b.max_interest_charge),
-1, b.late_charge_amount,
0 ,b.late_charge_amount,
+1,decode(sign(b.max_interest_charge -
(b.late_charge_rtot - b.late_charge_amount)),
-1,0,
least((b.max_interest_charge -
(b.late_charge_rtot - b.late_charge_amount)),
b.max_interest_charge))),
'PENALTY', b.late_charge_amount))
/decode(b.days_of_interest,0,1,b.days_of_interest) daily_interest_charge,
b.outstanding_amount,
b.days_overdue_late,
b.days_of_interest,
decode(b.class,
'PMT',b.late_charge_amount,
'CM', b.late_charge_amount,
decode(b.charge_line_type,
'INTEREST',
decode(sign(b.late_charge_rtot - b.max_interest_charge),
-1, b.late_charge_amount,
0 ,b.late_charge_amount,
+1,decode(sign(b.max_interest_charge -
(b.late_charge_rtot - b.late_charge_amount)),
-1,0,
least((b.max_interest_charge -
(b.late_charge_rtot - b.late_charge_amount)),
b.max_interest_charge))),
'PENALTY', b.late_charge_amount)) interest_charged,
b.payment_date,
b.fin_charge_charged,
b.amount_due_original,
b.amount_due_remaining,
b.original_trx_id,
b.receivables_trx_id,
b.last_charge_date,
b.due_date,
b.actual_date_closed,
b.interest_rate,
b.rate_start_date,
b.rate_end_date,
b.schedule_days_start,
b.schedule_days_to,
sysdate,
pg_last_updated_by,
pg_last_update_login,
pg_last_updated_by,
sysdate,
'N',
NULL,
b.org_id,
1,
b.cash_receipt_id
from
(select
a.interest_header_id,
a.payment_schedule_id,
a.charge_type,
a.class,
a.outstanding_amount,
a.days_overdue_late,
a.days_of_interest,
a.late_charge_amount,
a.charge_line_type,
a.late_charge_rtot,
a.max_interest_charge,
a.payment_date,
a.fin_charge_charged,
a.amount_due_original,
a.amount_due_remaining,
a.original_trx_id,
a.receivables_trx_id,
a.last_charge_date,
a.due_date,
a.actual_date_closed,
a.interest_rate,
a.rate_start_date,
a.rate_end_date,
a.schedule_days_start,
a.schedule_days_to,
a.org_id,
a.cash_receipt_id
from
(select
hdr.interest_header_id,
trx.payment_schedule_id,
trx.charge_type,
trx.class,
trx.overdue_late_pay_amount outstanding_amount,
/*bug 7431976 for invoice picked under late payment used receipt date. Bug 10230536 for correcting overdue days for overdue case*/
(decode(trx.charge_type,'LATE',(nvl(trx.receipt_date,trx.finance_charge_date) - trx.due_date) ,(trx.finance_charge_date -trx.due_date))) days_overdue_late,
trx.interest_days days_of_interest,
trx.late_charge_amount,
trx.charge_line_type,
decode(trx.charge_line_type,
'INTEREST', sum(trx.late_charge_amount)
over (partition by trx.payment_schedule_id
order by trx.payment_schedule_id,
trx.charge_line_type,
trx.rate_start_date,
trx.charge_type,
trx.receipt_date,
trx.fin_charge_charged),
'PENALTY', NULL) late_charge_rtot,
trx.receipt_date payment_date,
nvl(trx.max_interest_charge,9999999999999999) max_interest_charge,
trx.fin_charge_charged,
trx.amount_due_original,
trx.amount_due_remaining,
trx.customer_trx_id original_trx_id,
decode(hdr.late_charge_type,
'ADJ',decode(trx.charge_line_type,
'PENALTY',ar_calc_late_charge.get_penalty_rec_trx_id(trx.finance_charge_date,
trx.org_id),
'INTEREST',ar_calc_late_charge.get_int_rec_trx_id(trx.customer_trx_id,
trx.finance_charge_date,
trx.org_id),
NULL),
NULL) receivables_trx_id,
trx.last_charge_date,
trx.due_date,
trx.actual_date_closed,
trx.interest_rate,
trx.rate_start_date,
trx.rate_end_date,
trx.schedule_days_start,
trx.schedule_days_to,
trx.org_id,
trx.cash_receipt_id
from
(select
ar_calc_late_charge.get_next_hdr_id interest_header_id,
a.customer_id,
a.customer_site_use_id,
a.currency_code,
a.legal_entity_id,
a.late_charge_type,
a.payment_schedule_id,
a.org_id,
a.display_flag
from
(select trx.customer_id,
trx.customer_site_use_id,
trx.currency_code,
trx.legal_entity_id,
trx.late_charge_type,
trx.org_id,
trx.display_flag,
decode(trx.late_charge_type,'INV',-99,trx.payment_schedule_id) payment_schedule_id
from ar_late_charge_trx_t trx
where mod(nvl(trx.customer_site_use_id,0),p_total_workers) =
decode(p_total_workers,p_worker_number,0,p_worker_number)
/*Bug fix 5290709: If display flag is No, we should not consider this record if there is another record
existing with display flag Yes */
and ((trx.display_flag = 'Y' and sign(trx.late_charge_amount) <> 0)
OR (trx.display_flag = 'N' and not exists (select 1
from ar_late_charge_trx_t trx1
where trx1.payment_schedule_id = trx.payment_schedule_id
and trx1.display_flag = 'Y')))
group by trx.customer_id,
trx.customer_site_use_id,
trx.currency_code,
trx.legal_entity_id,
trx.late_charge_type,
trx.org_id,
trx.display_flag,
decode(trx.late_charge_type,'INV',-99,trx.payment_schedule_id))a)hdr,
(select trx.payment_schedule_id,
sum(trx.late_charge_amount) total_interest
from ar_late_charge_trx_t trx
where trx.charge_line_type = 'INTEREST'
and mod(nvl(trx.customer_site_use_id,0),p_total_workers) =
decode(p_total_workers,p_worker_number,0,p_worker_number)
group by trx.payment_schedule_id) int_tab,
ar_late_charge_trx_t trx
/* Apply the invoice level tolerances */
where decode(trx.class,
'CM', nvl(trx.original_balance,0),
'PMT',nvl(trx.original_balance,0),
decode(trx.display_flag,
'N', nvl(trx.original_balance,0),
decode(trx.min_fc_invoice_overdue_type,
'AMOUNT',nvl(trx.min_fc_invoice_amount,0),
'PERCENT',(nvl(trx.min_fc_invoice_percent,0) * trx.amount_due_original/100),
nvl(trx.original_balance,0)))) <= nvl(trx.original_balance,0)
and trx.payment_schedule_id = int_tab.payment_schedule_id
/* Apply Min Interest charge tolerance Bug 8559863 Restrict tolerance application on negatvie invoice as well
Similar to CM case */
and decode(trx.class,
'CM',int_tab.total_interest,
'PMT',int_tab.total_interest,
'INV', decode(sign(trx.original_balance),-1,int_tab.total_interest,
decode(trx.display_flag,
'N', int_tab.total_interest,
nvl(trx.min_interest_charge,0))),
decode(trx.display_flag,
'N', int_tab.total_interest,
nvl(trx.min_interest_charge,0))) <= int_tab.total_interest
and mod(nvl(trx.customer_site_use_id,0),p_total_workers) =
decode(p_total_workers,p_worker_number,0,p_worker_number)
and hdr.customer_id = trx.customer_id
and hdr.customer_site_use_id = trx.customer_site_use_id
and hdr.currency_code = trx.currency_code
and hdr.legal_entity_id = trx.legal_entity_id
and hdr.late_charge_type = trx.late_charge_type
and hdr.org_id = trx.org_id
/*Bug fix 5290709: If display flag is No, we should not consider this record if there is another record
existing with display flag Yes */
and ((trx.display_flag = 'Y' and sign(trx.late_charge_amount) <> 0)
OR (trx.display_flag = 'N' and not exists (select 1
from ar_late_charge_trx_t trx1
where trx1.payment_schedule_id = trx.payment_schedule_id
and trx1.display_flag = 'Y')))
and hdr.payment_schedule_id = decode(trx.late_charge_type,'INV',-99,trx.payment_schedule_id)) a ) b);
debug( 'ar_calc_late_charge.insert_int_lines()-' );
debug('EXCEPTION: ar_calc_late_charge.insert_int_lines');
END insert_int_lines;
/* This procedure is used to insert records in ar_interest lines
when the calculation method is average daily balance */
PROCEDURE insert_int_lines_adb(p_worker_number IN NUMBER,
p_total_workers IN NUMBER) IS
BEGIN
IF l_debug_flag = 'Y' THEN
debug( 'ar_calc_late_charge.insert_int_lines_adb()+' );
insert into ar_interest_lines
(interest_line_id,
interest_header_id,
payment_schedule_id,
type,
original_trx_class,
daily_interest_charge,
outstanding_amount,
days_overdue_late,
days_of_interest,
interest_charged,
payment_date,
finance_charge_charged,
amount_due_original,
amount_due_remaining,
original_trx_id,
receivables_trx_id,
last_charge_date,
due_date,
actual_date_closed,
interest_rate,
rate_start_date,
rate_end_date,
schedule_days_from,
schedule_days_to,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date,
process_status,
process_message,
org_id,
object_version_number)
(select ar_interest_lines_s.nextval interest_line_id,
b.interest_header_id,
-99, --payment_schedule_id
decode(b.charge_line_type, 'PENALTY', b.charge_line_type,b.charge_type) charge_type,
b.class,
b.late_charge_amount/decode(b.days_of_interest,0,1,b.days_of_interest) daily_interest_charge,
b.outstanding_amount,
b.days_overdue_late,
b.days_of_interest,
b.late_charge_amount interest_charged,
b.payment_date,
b.fin_charge_charged,
b.amount_due_original,
b.amount_due_remaining,
b.original_trx_id,
b.receivables_trx_id,
b.last_charge_date,
b.due_date,
b.actual_date_closed,
b.interest_rate,
b.rate_start_date,
b.rate_end_date,
b.schedule_days_start,
b.schedule_days_to,
sysdate,
pg_last_updated_by,
pg_last_update_login,
pg_last_updated_by,
sysdate,
'N',
NULL,
b.org_id,
1
from
(select
a.interest_header_id,
a.payment_schedule_id,
a.charge_type,
a.class,
a.outstanding_amount,
a.days_overdue_late,
a.days_of_interest,
a.late_charge_amount,
a.charge_line_type,
a.payment_date,
a.fin_charge_charged,
a.amount_due_original,
a.amount_due_remaining,
a.original_trx_id,
a.receivables_trx_id,
a.last_charge_date,
a.due_date,
a.actual_date_closed,
a.interest_rate,
a.rate_start_date,
a.rate_end_date,
a.schedule_days_start,
a.schedule_days_to,
a.org_id
from
(
select
hdr.interest_header_id,
trx.payment_schedule_id,
trx.charge_type,
trx.class,
trx.overdue_late_pay_amount outstanding_amount,
trx.interest_days days_overdue_late,
trx.interest_days days_of_interest,
decode(trx.charge_line_type,
'INTEREST',decode(sign(nvl(trx.max_interest_charge,9999999999999999) -
trx.late_charge_amount),
+1,trx.late_charge_amount,
0, trx.late_charge_amount,
-1, nvl(trx.max_interest_charge,9999999999999999)),
'PENALTY',trx.late_charge_amount ) late_charge_amount,
trx.charge_line_type,
trx.receipt_date payment_date,
trx.fin_charge_charged,
trx.amount_due_original,
trx.amount_due_remaining,
trx.customer_trx_id original_trx_id,
NULL receivables_trx_id,
trx.last_charge_date,
trx.due_date,
trx.actual_date_closed,
trx.interest_rate,
trx.rate_start_date,
trx.rate_end_date,
trx.schedule_days_start,
trx.schedule_days_to,
trx.org_id
from
(select
ar_calc_late_charge.get_next_hdr_id interest_header_id,
a.customer_id,
a.customer_site_use_id,
a.currency_code,
a.legal_entity_id,
a.late_charge_type,
a.payment_schedule_id,
a.org_id
from
(select trx.customer_id,
trx.customer_site_use_id,
trx.currency_code,
trx.legal_entity_id,
trx.late_charge_type,
trx.org_id,
decode(trx.late_charge_type,'INV',-99,trx.payment_schedule_id) payment_schedule_id
from ar_late_charge_trx_t trx
where mod(nvl(trx.customer_site_use_id,0),p_total_workers) =
decode(p_total_workers,p_worker_number,0,p_worker_number)
group by trx.customer_id,
trx.customer_site_use_id,
trx.currency_code,
trx.legal_entity_id,
trx.late_charge_type,
trx.org_id,
decode(trx.late_charge_type,'INV',-99,trx.payment_schedule_id))a)hdr,
(select trx.payment_schedule_id,
sum(trx.late_charge_amount) total_interest
from ar_late_charge_trx_t trx
where trx.charge_line_type = 'INTEREST'
and mod(nvl(trx.customer_site_use_id,0),1) =
decode(1,1,0,1)
group by trx.payment_schedule_id) int_tab,
ar_late_charge_trx_t trx
/* Apply Min Interest charge tolerance */
where decode(trx.charge_line_type,
'INTEREST', nvl(trx.min_interest_charge,0),
'PENALTY',nvl(trx.late_charge_amount,0)) <= nvl(trx.late_charge_amount,0)
and mod(nvl(trx.customer_site_use_id,0),p_total_workers) =
decode(p_total_workers,p_worker_number,0,p_worker_number)
and trx.payment_schedule_id = int_tab.payment_schedule_id
and hdr.customer_id = trx.customer_id
and hdr.customer_site_use_id = trx.customer_site_use_id
and hdr.currency_code = trx.currency_code
and nvl(hdr.legal_entity_id,-99) = nvl(trx.legal_entity_id,-99)
and hdr.late_charge_type = trx.late_charge_type
and hdr.org_id = trx.org_id
and trx.charge_type = 'AVERAGE_DAILY_BALANCE'
and hdr.payment_schedule_id = decode(trx.late_charge_type,'INV',-99,trx.payment_schedule_id)) a )b);
debug( 'ar_calc_late_charge.insert_int_lines_adb()-' );
debug('EXCEPTION: ar_calc_late_charge.insert_int_lines_adb');
END insert_int_lines_adb;
PROCEDURE delete_empty_batches IS
BEGIN
IF l_debug_flag = 'Y' THEN
debug('ar_calc_late_charge.delete_empty_batches()+');
delete from ar_interest_batches bat
where not exists (select interest_header_id
from ar_interest_headers hdr
where bat.interest_batch_id = hdr.interest_batch_id)
and ( request_id = l_request_id
OR batch_status = 'D') ;
debug('ar_calc_late_charge.delete_empty_batches()-');
debug('EXCEPTION: ar_calc_late_charge.delete_empty_batches()');
END delete_empty_batches;
select interest_batch_id, batch_name, transferred_status
from ar_interest_batches
where request_id = l_request_id
for update of transferred_status nowait;
SELECT org_id,
lc_cust_sites_id,
customer_id,
customer_site_use_id,
currency_code,
late_charge_calculation_trx,
credit_items_flag,
disputed_transactions_flag,
payment_grace_days,
late_charge_type,
late_charge_term_id ,
interest_period_days,
interest_calculation_period,
charge_on_finance_charge_flag,
hold_charged_invoices_flag,
message_text_id,
multiple_interest_rates_flag,
charge_begin_date,
cust_acct_profile_amt_id,
exchange_rate_type,
min_fc_invoice_overdue_type,
min_fc_invoice_amount,
min_fc_invoice_percent,
min_fc_balance_overdue_type,
min_fc_balance_amount,
min_fc_balance_percent,
min_interest_charge,
max_interest_charge,
interest_type,
interest_Rate,
interest_fixed_amount,
interest_schedule_id,
penalty_type,
penalty_rate,
penalty_fixed_amount,
penalty_schedule_id,
last_accrue_charge_date
FROM ar_lc_cust_sites_t
ORDER BY org_id,
customer_id,
customer_site_use_id,
currency_code;
debug('Set Up Information of the Selected Customers');
select org_id,
customer_id,
customer_site_use_id,
currency_code,
customer_open_balance,
customer_overdue_balance
from ar_late_charge_cust_balance_gt
order by org_id,
customer_id,
customer_site_use_id,
currency_code;
debug('Balance Information of the selected customers');
select customer_id,
customer_site_use_id,
currency_code,
legal_entity_id,
credit_amount
from ar_late_charge_credits_gt
order by customer_id,
customer_site_use_id,
currency_code,
legal_entity_id;
debug('Credit Information of the selected customers');
select late_charge_trx_id,
customer_id,
customer_site_use_id,
currency_code,
customer_trx_id,
legal_entity_id,
payment_schedule_id,
class,
amount_due_original,
amount_due_remaining,
fin_charge_charged,
trx_date,
cust_trx_type_id,
last_charge_date,
exchange_rate_type,
min_interest_charge,
max_interest_charge,
overdue_late_pay_amount,
original_balance,
due_date,
receipt_date,
finance_charge_date,
charge_type,
actual_date_closed,
interest_rate,
interest_days,
rate_start_date,
rate_end_date,
schedule_days_start,
schedule_days_to,
late_charge_amount,
late_charge_type,
late_charge_term_id,
interest_period_days,
interest_calculation_period,
charge_on_finance_charge_flag,
message_text_id,
interest_type,
min_fc_invoice_overdue_type,
min_fc_invoice_amount,
min_fc_invoice_percent,
charge_line_type
from ar_late_charge_trx_t
order by customer_id,
customer_site_use_id,
currency_code,
legal_entity_id,
due_date,
payment_schedule_id,
rate_start_date,
charge_line_type;
debug('Selected Payment schedule IDs and the details');
| 3. Use Multiple Interest Rates : If this option is selected, and there are multiple |
| interest rates applicable during the period from the due_date to the finance charge |
| date, the interest will be calculated using all the applicable rates considering the |
| corresponding days. This option is used only if the Interest is defined as a |
| percentage rate. If fixed amounts are applicable during this period, only the rate |
| applicable as of the finance charge date is considered. If this option is unchecked, |
| the rate / amount applicable as of the finance charge date is considered. |
| It is also to be noted that the multiple interest rates are used only for the |
| interest computation and not for the penalty computation |
| |
| 4. For Receipts, the maturity_date is stored in the column due_date in payment schedules|
| So, the receipt date should be considered instead of the due_date for receipts |
| |
| 5. If the interest definition is to use amount and not rate (either as fixed amount or |
| as a schedule), and we are computing interest on a credit item, the interest amount |
| should be multiplied by -1. |
| |
| 6. Simple Vs. Compound Interest : If Compound Interest has to be computed, the balance |
| can be computed based on the amount_due_remaining in payment schedules. Otherwise,the|
| previously charged finance charge has to be deducted from amount_due_remaining. This |
| is the case the finance charges were created as Adjustments. |
| If we are creating interest invoices or Debit memos, then we have separate documents |
| having their own due dates. So these will always have interest on them - and this is |
| compound interest. We wouldn't compound the amount onto the original transaction |
| because we have created a new transaction and we are charging the additional interest|
| on that.So to have simple interest, you would have to ensure that the interest |
| invoice transaction type was excluded from finance charges - so that interest is not |
| calculated on the interest. |
| |
| 7. Penalty is computed on the computed Interest |
| |
| 8. Tolerances are applied only on the Interest and not on the Penalty. i.e.Penalty is |
| is levied regardless of the maximum tolerances. Only the interest charge is validated|
| against the maximum charge tolerances |
| |
| 9. Interest Calculation Period : Daily Vs Monthly |
| While using Daily method, the interest will be calculated exactly on the number of |
| days between the due date and the finance charge date. On the other hand, if Monthly |
| method is used, interest will be calculated for the number of days between the first |
| day of the month corresponding to due_date to the last day of the month corresponding|
| to the finance_charge_date |
| |
|10. Application of Tolerances |
| 1. Customer Level Tolerances : This could be a fixed amount or a Percentage |
| Since this set up is done at profile amount level, this is applicable for a |
| customer, site and currency combination. |
| a) Fixed Amount : If the set up is Amount, it means that the customer should be |
| charged an interest only if the total overdue balance for this customer, site |
| and currency combination is greater than or equal to the amount mentioned in |
| the set up. |
| b) Percentage : This means that, the customer should be changed an interest only |
| if the total overdue balance for this customer , site and currency combination |
| is greater than or equal to the given percentage of the total open balance for |
| this customer, site and currency combination. |
| Overdue Balance : Is the sum of balances of the debit and credit items which are |
| past due as of the finance charge date. These balances will be computed as of the |
| finance charge date |
| Open Balance : Is the sum of balances of the debit and credit items which are open|
| as of the finance charge date. These balances will be computed as of the finance |
| charge date |
| These computations are similar for Overdue Invoices and Late Payments. But for |
| Average Daily Balance, the application of tolerances are completely differemt. |
| For Overdue Invoices and Late Payments, consider the following example: |
| Customer xyz has the following invoices |
| |
| a) invoice 101 for 1000 USD with trx_date of 01-Dec-2005 and due_date of |
| 01-Jan-2006. There are receipt applications on this invoice on 15-Jan-2006 |
| for 400 USD and 10-Feb-2006 for 600 USD |
| b) Invoice 102 for 2000 USD with a trx_date of 01-Jan-2006 and due_date of |
| 01-Feb-2006. There are no applications on this invoice. |
| |
| We are calculating the finance charge as of 31-Jan-2006. As of this date, the |
| invoice 101 is overdue by 600 USD, where as the invoice 102 is open (but not |
| overdue) by 2000 USD. So the overdue balance will be 600 USD and the open balance |
| will be 2600 USD. |
| |
| 2. Invoice Level Tolerances : Similar to the case above. Instead of the overdue |
| customer balance, the overdue invoice amount will be used. Instead of the Open |
| customer balance, the original invoice amount will be used (amount_due_original |
| from ar_payment_schedules). |
| |
| In my example above, as of 31-Jan-2006, the invoice 101 is overdue by 600 USD and|
| the original invoice amount is 1000 USD . So 60% of this invoice is overdue as of |
| this date. Invoice 102 will not be considered as it is not overdue. |
| |
| 3. Min and Max Interest Charges: These tolerances will be applied after the interest |
| is calculated. These will be applied only on the interest charged and not on the |
| penalty. If the interest is less than the Min Interest Charge, no Interest or |
| Penalty Records will be created for this Payment schedule. On the other hand, if |
| the interest is more than the Max Interest Charge, the Interest portion will be |
| limited to the maximum amount, but the Penalty will be computed on the actual |
| Interest Charge. |
| For example, the maximum interest is defined as 1000. The following interest |
| charges and penalty cahrges are computed on a single payment schedule |
| interest interest_days rate_start_date rate_end_date type |
| -------- ------------- -------------- -------------- ------ |
| 40.00 4 7-JUN-2005 10-JUN-2005 INTEREST |
| 60.00 5 11-JUN-2005 15-JUN-2005 INTEREST |
| 1320.00 240 16-JUN-2005 INTEREST |
| 106.00 249 07-JUN-2005 10-JUN-2005 PENALTY |
| |
| After the application of the maximum interest tolerances, the following Interest |
| and penalty lines will be created |
| interest interest_days rate_start_date rate_end_date type |
| -------- ------------- -------------- -------------- ------ |
| 40.00 4 7-JUN-2005 10-JUN-2005 INTEREST |
| 60.00 5 11-JUN-2005 15-JUN-2005 INTEREST |
| 900.00 240 16-JUN-2005 INTEREST |
| 106.00 249 07-JUN-2005 10-JUN-2005 PENALTY |
| |
| PARAMETERS |
| |
| |
| KNOWN ISSUES |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| Date Author Description of Changes |
| 22-DEC-2005 rkader Created |
| |
*=========================================================================================*/
PROCEDURE create_late_charge_document
(errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER,
p_operating_unit_id IN VARCHAR2,
p_customer_name_from IN VARCHAR2,
p_customer_name_to IN VARCHAR2,
p_customer_num_from IN VARCHAR2,
p_customer_num_to IN VARCHAR2,
p_cust_site_use_id IN VARCHAR2,
p_gl_date IN VARCHAR2,
p_fin_charge_date IN VARCHAR2,
p_currency_code IN VARCHAR2,
p_mode IN VARCHAR2,
p_disputed_items IN VARCHAR2,
p_called_from IN VARCHAR2,
p_enable_debug IN VARCHAR2,
p_worker_number IN VARCHAR2,
p_total_workers IN VARCHAR2,
p_master_request_id IN VARCHAR2) IS
l_org_id number(15);
debug('pg_last_updated_by : '||pg_last_updated_by);
debug('pg_last_update_login : '||pg_last_update_login);
select count(*)
into l_num_batches
from ar_interest_batches
where request_id = l_request_id;
select allow_late_charges, set_of_books_id
into l_compute_late_charge, l_set_of_books_id
from ar_system_parameters
where org_id = l_org_id ;
and customer overdue balance for all the selected customer, site ,currency_code
and org combination */
insert_cust_balances(p_as_of_date => l_fin_charge_date,
p_worker_number => l_worker_number,
p_total_workers => l_total_workers);
up and inserted into ar_late_charge_credits_gt . These amounts should later be adjusted
against the open debit items in the order of oldest invoice first.*/
insert_credit_amount(p_fin_charge_date => l_fin_charge_date,
p_worker_number => l_worker_number,
p_total_workers => l_total_workers);
insert_int_overdue_adj_dm(p_fin_charge_date => l_fin_charge_date,
p_worker_number => l_worker_number,
p_total_workers => l_total_workers);
insert_int_overdue_inv(p_fin_charge_date => l_fin_charge_date,
p_worker_number => l_worker_number,
p_total_workers => l_total_workers);
/*Bug 8556955 call to insert reversed receipt late charges*/
Insert_int_rev_rect_overdue(p_fin_charge_date => l_fin_charge_date,
p_worker_number => l_worker_number,
p_total_workers => l_total_workers);
insert_int_late_pay(p_fin_charge_date => l_fin_charge_date,
p_worker_number => l_worker_number,
p_total_workers => l_total_workers);
insert_int_avg_daily_bal(p_fin_charge_date => l_fin_charge_date,
p_worker_number => l_worker_number,
p_total_workers => l_total_workers);
update_interest_amt('INTEREST');
insert_penalty_lines(p_worker_number => l_worker_number,
p_total_workers => l_total_workers);
update_interest_amt('PENALTY');
/* If there are records in ar_late_charge_trx_t, insert data into the preview tables
ar_interest_batches, ar_interest_headers and ar_interest_lines */
select count(*)
into l_count_int_lines
from ar_late_charge_trx_t;
/* Before inserting data into the preview tables, we will delete all the existing
Draft batches for this customer,site, currency, legal entity and org combination */
delete_draft_batches(p_worker_number => l_worker_number,
p_total_workers => l_total_workers);
/* insert_int_batches(p_batch_name => l_batch_name,
p_fin_charge_date => l_fin_charge_date,
p_batch_status => l_mode,
p_gl_date => l_gl_date); */
insert_int_lines(p_worker_number => l_worker_number,
p_total_workers => l_total_workers);
insert_int_lines_adb(p_worker_number => l_worker_number,
p_total_workers => l_total_workers);
insert_int_headers(p_fin_charge_date => l_fin_charge_date,
p_worker_number => l_worker_number,
p_total_workers => l_total_workers);
/* delete the data in the interim tables before commit */
delete from ar_lc_cust_sites_t;
delete from ar_late_charge_trx_t;
/* delete the data in the interim tables before commit */
delete from ar_lc_cust_sites_t;
delete from ar_late_charge_trx_t;
/* delete the data in the interim tables before commit */
delete from ar_lc_cust_sites_t;
delete from ar_late_charge_trx_t;
l_req_status_tab.DELETE;
select hp.party_name
into l_customer_name_from
from hz_parties hp,
hz_cust_accounts cust_acct
where hp.party_id = cust_acct.party_id
and cust_acct.cust_account_id = p_customer_id_from;
select hp.party_name
into l_customer_name_to
from hz_parties hp,
hz_cust_accounts cust_acct
where hp.party_id = cust_acct.party_id
and cust_acct.cust_account_id = p_customer_id_to;
select meaning
into l_late_charge_batch
from ar_lookups
where lookup_type = 'AR_LATE_CHARGE_LABELS'
and lookup_code = 'LATE_CHARGE_BATCH';
/* Insert one batch per OU. Since the processing is done by multiple workers,
the batch is created first */
insert_int_batches(p_operating_unit_id => p_operating_unit_id,
p_batch_name => l_batch_name,
p_fin_charge_date => l_fin_charge_date,
p_batch_status => p_mode,
p_gl_date => l_gl_date,
p_request_id => l_request_id);
select count(*)
into l_num_batches
from ar_interest_batches
where request_id = l_request_id;
/* We have one interest batch per OU. Delete all empty batches */
delete_empty_batches;
/* If the mode is Final, update the column transferred_status in ar_interest_bacthes
a) Update the transferred_status to S if the process_status of all the header
records corresponding to a given batch is S
b) Else the transferred_status should be E */
IF p_mode = 'F' THEN
update ar_interest_batches bat
set bat.transferred_status = 'S'
where not exists (select hdr.interest_header_id
from ar_interest_headers hdr
where bat.interest_batch_id = hdr.interest_batch_id
and hdr.process_status <> 'S')
and bat.request_id = l_request_id;
update ar_interest_batches bat
set bat.transferred_status = 'E'
where exists (select hdr.interest_header_id
from ar_interest_headers hdr
where bat.interest_batch_id = hdr.interest_batch_id
and hdr.process_status <> 'S')
and bat.request_id = l_request_id;
SELECT lower(iso_language),iso_territory
INTO l_iso_language,l_iso_territory
FROM FND_LANGUAGES
WHERE language_code = USERENV('LANG');
pg_last_updated_by := arp_global.last_updated_by;
pg_last_update_login := arp_global.last_update_login;