create or replace package cus_con_deposit_pkg is
-- Author : Spencer 3893
-- Created : 2019/10/14 20:51:45
-- Purpose : 保证金
--罚息测算
procedure calc_overdue_dayend(p_calc_date date,
p_session_id number,
p_user_id number);
end cus_con_deposit_pkg;
/
create or replace package body cus_con_deposit_pkg is
g_currency_precision constant number := 2;
procedure log(p_log_desc varchar2, p_error_message varchar2) is
begin
hls_dayend_pkg.log(p_log_desc => p_log_desc,
p_error_message => p_error_message);
end;
procedure f_calc_penalty(p_calc_amount number,
p_contract_rec con_contract%rowtype,
p_calc_date date,
p_penalty_calc_date date,
p_user_id number,
p_total_penalty_amt out number) is
v_penalty_days number;
v_penalty_amt number;
v_total_penalty_amt number := 0;
begin
v_penalty_days := trunc(p_calc_date) - trunc(p_penalty_calc_date) -
nvl(p_contract_rec.grace_period, 0);
-- 未核销金额 * 延迟天数 * 延迟履行金率
if v_penalty_days <= 0 then
v_penalty_amt := 0;
else
v_penalty_days := v_penalty_days +
nvl(p_contract_rec.grace_period, 0);
v_penalty_amt := p_calc_amount * (v_penalty_days) *
p_contract_rec.penalty_rate;
end if;
/*log(p_log_desc => '未收金额'||to_char(p_cashflow_rec.due_amount - nvl(p_cashflow_rec.received_amount,0)),
p_error_message => '延迟天数='||v_penalty_days||',延迟金'||v_penalty_amt);
*/
v_total_penalty_amt := v_total_penalty_amt + v_penalty_amt;
p_total_penalty_amt := round(v_total_penalty_amt,
nvl(p_contract_rec.currency_precision,
g_currency_precision));
end;
procedure calc_penalty(p_cashflow_rec con_contract_cashflow%rowtype,
p_contract_rec con_contract%rowtype,
p_calc_date date,
p_user_id number,
p_total_penalty_amt out number) is
v_penalty_days number;
v_penalty_amt number;
v_total_penalty_amt number := 0;
v_last_creation_date date;
begin
select trunc(max(a.penalty_calc_date))
into v_last_creation_date
from csh_write_off a
where a.cashflow_id = p_cashflow_rec.cashflow_id;
--modify by wcs
if not (p_cashflow_rec.penalty_process_status = 'NORMAL') then
--不满足计算条件
return;
end if;
--已收部分
for c_write_off in (select a.*
from csh_write_off a
where a.contract_id = p_contract_rec.contract_id
and a.cashflow_id = p_cashflow_rec.cashflow_id
and trunc(a.penalty_calc_date) <=
trunc(p_calc_date)
order by a.penalty_calc_date, a.write_off_date) loop
--核销金额 * 延迟天数 * 延迟履行金率
v_penalty_days := trunc(c_write_off.penalty_calc_date) -
trunc(nvl(p_cashflow_rec.ln_user_col_d01,
p_cashflow_rec.due_date)) -
nvl(p_contract_rec.grace_period, 0);
if v_penalty_days <= 0 then
v_penalty_amt := 0;
else
v_penalty_days := v_penalty_days +
nvl(p_contract_rec.grace_period, 0);
v_penalty_amt := c_write_off.write_off_due_amount * v_penalty_days *
p_contract_rec.penalty_rate;
end if;
/*log(p_log_desc => to_char(c_write_off.penalty_calc_date,'YYYYMMDD')||'收款核销'||c_write_off.write_off_due_amount,
p_error_message => '延迟天数='||v_penalty_days||',延迟金'||v_penalty_amt);
*/
v_total_penalty_amt := v_total_penalty_amt + v_penalty_amt;
end loop;
--未收部分
if nvl(p_cashflow_rec.received_amount, 0) < p_cashflow_rec.due_amount then
v_penalty_days := trunc(p_calc_date) -
trunc(nvl(p_cashflow_rec.ln_user_col_d01,
p_cashflow_rec.due_date)) -
nvl(p_contract_rec.grace_period, 0);
-- 未核销金额 * 延迟天数 * 延迟履行金率
if v_penalty_days <= 0 then
v_penalty_amt := 0;
else
v_penalty_days := v_penalty_days +
nvl(p_contract_rec.grace_period, 0);
v_penalty_amt := (p_cashflow_rec.due_amount -
nvl(p_cashflow_rec.received_amount, 0)) *
(v_penalty_days) * p_contract_rec.penalty_rate;
end if;
/*log(p_log_desc => '未收金额'||to_char(p_cashflow_rec.due_amount - nvl(p_cashflow_rec.received_amount,0)),
p_error_message => '延迟天数='||v_penalty_days||',延迟金'||v_penalty_amt);
*/
v_total_penalty_amt := v_total_penalty_amt + v_penalty_amt;
end if;
p_total_penalty_amt := round(v_total_penalty_amt,
nvl(p_contract_rec.currency_precision,
g_currency_precision));
end;
procedure overdue_dayend(p_contract_cashflow_rec con_contract_cashflow%rowtype,
p_contract_rec con_contract%rowtype,
p_calc_date date,
p_session_id number,
p_user_id number) is
v_total_penalty_amt number;
v_total_penalty_amt_tmp number;
v_last_creation_date date;
v_deposit_calc_overdue_tmp_rec CUS_DEPOSIT_CALC_OVERDUE_TMP%rowtype;
begin
select trunc(max(a.penalty_calc_date))
into v_last_creation_date
from csh_write_off a
where a.cashflow_id = p_contract_cashflow_rec.cashflow_id;
--计算营业数据overdue
--逾期
if p_contract_cashflow_rec.overdue_status = 'Y' then
--已逾期
if p_contract_cashflow_rec.due_amount >
nvl(p_contract_cashflow_rec.received_amount, 0) then
--未收完款
v_deposit_calc_overdue_tmp_rec.calc_id := CUS_DEPOSIT_CALC_OVERDUE_TMP_s.Nextval;
v_deposit_calc_overdue_tmp_rec.session_id := p_session_id;
v_deposit_calc_overdue_tmp_rec.calc_date := p_calc_date;
v_deposit_calc_overdue_tmp_rec.due_date := p_contract_cashflow_rec.due_date;
v_deposit_calc_overdue_tmp_rec.contract_id := p_contract_rec.contract_id;
v_deposit_calc_overdue_tmp_rec.cashflow_id := p_contract_cashflow_rec.cashflow_id;
v_deposit_calc_overdue_tmp_rec.overdue_max_days := trunc(p_calc_date) -
trunc(p_contract_cashflow_rec.due_date);
v_deposit_calc_overdue_tmp_rec.overdue_amount := p_contract_cashflow_rec.due_amount -
nvl(p_contract_cashflow_rec.received_amount,
0);
v_deposit_calc_overdue_tmp_rec.overdue_principal := p_contract_cashflow_rec.principal -
nvl(p_contract_cashflow_rec.received_principal,
0);
v_deposit_calc_overdue_tmp_rec.overdue_interest := p_contract_cashflow_rec.interest -
nvl(p_contract_cashflow_rec.received_interest,
0);
v_deposit_calc_overdue_tmp_rec.created_by := p_user_id;
v_deposit_calc_overdue_tmp_rec.creation_date := sysdate;
insert into CUS_DEPOSIT_CALC_OVERDUE_TMP
values v_deposit_calc_overdue_tmp_rec;
end if;
else
--未逾期
if p_contract_cashflow_rec.due_amount >
nvl(p_contract_cashflow_rec.received_amount, 0) then
v_deposit_calc_overdue_tmp_rec.calc_id := CUS_DEPOSIT_CALC_OVERDUE_TMP_s.Nextval;
v_deposit_calc_overdue_tmp_rec.session_id := p_session_id;
v_deposit_calc_overdue_tmp_rec.calc_date := p_calc_date;
v_deposit_calc_overdue_tmp_rec.due_date := p_contract_cashflow_rec.due_date;
v_deposit_calc_overdue_tmp_rec.contract_id := p_contract_rec.contract_id;
v_deposit_calc_overdue_tmp_rec.cashflow_id := p_contract_cashflow_rec.cashflow_id;
v_deposit_calc_overdue_tmp_rec.overdue_max_days := trunc(p_calc_date) -
trunc(p_contract_cashflow_rec.due_date);
v_deposit_calc_overdue_tmp_rec.overdue_amount := p_contract_cashflow_rec.due_amount -
nvl(p_contract_cashflow_rec.received_amount,
0);
v_deposit_calc_overdue_tmp_rec.overdue_principal := p_contract_cashflow_rec.principal -
nvl(p_contract_cashflow_rec.received_principal,
0);
v_deposit_calc_overdue_tmp_rec.overdue_interest := p_contract_cashflow_rec.interest -
nvl(p_contract_cashflow_rec.received_interest,
0);
v_deposit_calc_overdue_tmp_rec.created_by := p_user_id;
v_deposit_calc_overdue_tmp_rec.creation_date := sysdate;
insert into CUS_DEPOSIT_CALC_OVERDUE_TMP
values v_deposit_calc_overdue_tmp_rec;
end if;
end if;
--计算penalty
if p_contract_cashflow_rec.write_off_flag <> 'FULL' then
calc_penalty(p_cashflow_rec => p_contract_cashflow_rec,
p_contract_rec => p_contract_rec,
p_calc_date => p_calc_date,
p_total_penalty_amt => v_total_penalty_amt,
p_user_id => p_user_id);
--会计逾期金额默认营业数据，当会计没核销实际上和营业逾期一致
update CUS_DEPOSIT_CALC_OVERDUE_TMP
set OVERDUE_PENALTY_AMT = v_total_penalty_amt,
f_overdue_amount = overdue_amount,
f_overdue_penalty_amt = v_total_penalty_amt
where calc_id = v_deposit_calc_overdue_tmp_rec.calc_id;
end if;
--计算会计逾期数据,取会计核销的最大期与营业现金流数据比较
for c_f in (select gwo.cf_item,
gwo.times,
sum(gwo.write_off_amount) write_off_amount,
gwo.penalty_calc_date
from GLD_WRITE_OFF gwo
where /*gwo.create_je_flag = 'Y'
AND */
gwo.reversed_flag = 'N'
and gwo.contract_id = p_contract_rec.contract_id
and gwo.times =
(select max(gwo.times) max_times
from GLD_WRITE_OFF gwo
where /*gwo.create_je_flag = 'Y'
AND */
gwo.reversed_flag = 'N'
and gwo.contract_id = p_contract_rec.contract_id)
group by gwo.cf_item, gwo.times, gwo.penalty_calc_date) loop
--会计出凭证的数据同类型同期现金流，并营业应收金额大于会计出凭证的和及营业应收日小于计算日。就视为这一期现金流 会计逾期
if p_contract_cashflow_rec.cf_item = c_f.cf_item and
p_contract_cashflow_rec.times = c_f.times and
p_contract_cashflow_rec.due_amount > c_f.write_off_amount and
p_contract_cashflow_rec.due_date < p_calc_date then
--会计已收收计算罚息
f_calc_penalty(p_calc_amount => c_f.write_off_amount,
p_contract_rec => p_contract_rec,
p_calc_date => p_calc_date,
p_penalty_calc_date => c_f.penalty_calc_date,
p_user_id => p_user_id,
p_total_penalty_amt => v_total_penalty_amt_tmp);
--会计未收计算罚息
f_calc_penalty(p_calc_amount => p_contract_cashflow_rec.due_amount -
c_f.write_off_amount,
p_contract_rec => p_contract_rec,
p_calc_date => p_calc_date,
p_penalty_calc_date => p_contract_cashflow_rec.due_date,
p_user_id => p_user_id,
p_total_penalty_amt => v_total_penalty_amt);
--更新会计逾期租金=营业应收租金-会计核销租金
update CUS_DEPOSIT_CALC_OVERDUE_TMP t
set t.f_overdue_amount = p_contract_cashflow_rec.due_amount -
c_f.write_off_amount,
t.f_overdue_penalty_amt = round(v_total_penalty_amt_tmp +
v_total_penalty_amt,
2)
where t.cashflow_id = p_contract_cashflow_rec.cashflow_id
and t.contract_id = p_contract_rec.contract_id
and t.session_id = p_session_id;
--会计出凭证的数据同类型及会计未来期现金流，并营业应收日小于计算日。就视为这一期现金流 会计逾期
elsif p_contract_cashflow_rec.cf_item = c_f.cf_item and
p_contract_cashflow_rec.times > c_f.times and
p_contract_cashflow_rec.due_date < p_calc_date then
--会计未收计算罚息
f_calc_penalty(p_calc_amount => p_contract_cashflow_rec.due_amount,
p_contract_rec => p_contract_rec,
p_calc_date => p_calc_date,
p_penalty_calc_date => p_contract_cashflow_rec.due_date,
p_user_id => p_user_id,
p_total_penalty_amt => v_total_penalty_amt);
--更新会计逾期租金=营业应收租金
update CUS_DEPOSIT_CALC_OVERDUE_TMP t
set t.f_overdue_amount = p_contract_cashflow_rec.due_amount,
t.f_overdue_penalty_amt = v_total_penalty_amt
where t.cashflow_id = p_contract_cashflow_rec.cashflow_id
and t.contract_id = p_contract_rec.contract_id
and t.session_id = p_session_id;
end if;
end loop;
end;
procedure overdue_dayend(p_contract_id number,
p_calc_date date,
p_session_id number,
p_user_id number) is
v_contract_rec con_contract%rowtype;
begin
begin
select *
into v_contract_rec
from con_contract
where contract_id = p_contract_id
and contract_status = 'INCEPT'
for update nowait;
exception
when no_data_found then
return;
end;
for c_contract_cashflow in (select a.*
from con_contract_cashflow a,
con_contract_cf_item b
where a.contract_id = p_contract_id
and a.cf_status = 'RELEASE'
and a.due_date <= p_calc_date
--只有正常的才计算罚息。
and a.penalty_process_status = 'NORMAL'
and a.cf_direction = 'INFLOW'
and a.contract_id = b.contract_id
and a.cf_item = b.cf_item
and b.calc_penalty = 'Y'
order by times, due_date
for update nowait) loop
overdue_dayend(p_contract_cashflow_rec => c_contract_cashflow,
p_contract_rec => v_contract_rec,
p_calc_date => p_calc_date,
p_session_id => p_session_id,
p_user_id => p_user_id);
end loop;
end;
--罚息测算
procedure calc_overdue_dayend(p_calc_date date,
p_session_id number,
p_user_id number) is
v_count number;
begin
-- 同样的罚息测算日如果存在数据，不在进行计算
select count(*)
into v_count
from CUS_DEPOSIT_CALC_OVERDUE_TMP t
where t.calc_date = p_calc_date
--and t.session_id = p_session_id
;
if v_count >= 1 then
null;
else
-- 之前的计算数据
delete from CUS_DEPOSIT_CALC_OVERDUE_TMP t
where t.calc_date <= p_calc_date;
--t.session_id = p_session_id;
--根据测算日重算罚息
for c_con in (select *
from con_contract t
where t.contract_status = 'INCEPT') loop
overdue_dayend(p_contract_id => c_con.contract_id,
p_calc_date => p_calc_date,
p_session_id => p_session_id,
p_user_id => p_user_id);
end loop;
end if;
end calc_overdue_dayend;
end cus_con_deposit_pkg;
/