create or replace package cus_con_et_pkg is -- Author : Spencer 3893 -- Created : 9/19/2019 3:40:32 PM -- Purpose : 提前结清 procedure create_change_req_et(p_contract_id number, p_req_type varchar2, p_user_id number, p_simulation_flag varchar2, p_change_req_id out number); --通过结清日获取结清相关信息 procedure calc_et_date_amount(p_contract_id in number, p_et_date date, p_et_fee in out number, p_et_interest_rate in number, p_et_total_amount out number, p_et_due_amount out number, p_overdue_amount out number, p_penalty out number, p_fund_possession_time out number, p_fund_possession_cost out number, p_SUM_UNRECEIVED_PRINCIPAL out number, p_user_id in number); --结清现金流计算 procedure calc_et_cashflows(p_contract_id in number, p_user_id in number); --结清提交 procedure submit_change_req_et(p_change_req_id number, p_layout_code varchar2, p_user_id number); end cus_con_et_pkg; / create or replace package body cus_con_et_pkg is e_lock_table exception; pragma exception_init(e_lock_table, -54); function get_contract_rec(p_contract_id number, p_user_id number) return con_contract%rowtype is v_con_contract_rec con_contract%rowtype; begin select * into v_con_contract_rec from con_contract t where t.contract_id = p_contract_id for update nowait; return v_con_contract_rec; exception when e_lock_table then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CON_CONTRACT_PKG.CONTRACT_LOCK_ERROR', p_created_by => p_user_id, p_package_name => 'cus_con_et_pkg', p_procedure_function_name => 'get_contract_rec'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end; function get_con_change_req_rec(p_change_req_id con_contract_change_req.change_req_id%type, p_user_id con_contract_change_req.created_by%type) return con_contract_change_req%rowtype is v_change_req_rec con_contract_change_req%rowtype; begin select * into v_change_req_rec from con_contract_change_req t where t.change_req_id = p_change_req_id for update nowait; return v_change_req_rec; exception when e_lock_table then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CON_CONTRACT_CHANGE_REQ_PKG.CON_CHANGE_REQ_LOCK', p_created_by => p_user_id, p_package_name => 'cus_con_et_pkg', p_procedure_function_name => 'get_con_change_req_rec'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end; procedure create_change_req_et(p_contract_id number, p_req_type varchar2, p_user_id number, p_simulation_flag varchar2, p_change_req_id out number) is v_change_req_id number; v_contract_rec con_contract%rowtype; begin v_contract_rec := get_contract_rec(p_contract_id, p_user_id); if v_contract_rec.contract_status <> 'INCEPT' and p_req_type = 'ET_CHAG' then sys_raise_app_error_pkg.raise_sys_others_error(p_message => '只有起租状态的合同才能进行提前结清申请!', p_created_by => p_user_id, p_package_name => 'cus_con_et_pkg', p_procedure_function_name => 'create_change_req_et'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end if; con_contract_history_pkg.create_change_req(p_contract_id => p_contract_id, p_change_req_id => v_change_req_id, p_req_date => sysdate, p_ref_v01 => p_user_id, p_description => null, p_change_quotation => null, p_change_lease_item => null, p_change_bp => null, p_req_doc_type => p_req_type, p_simulation_flag => p_simulation_flag, p_et_flag => 'Y', p_user_id => p_user_id); cus_con_change_req_pkg.insert_hls_document_use_seal(p_change_req_id => v_change_req_id, p_document_table => 'CON_CONTRACT_CHANGE_REQ', p_instruments_types => '《融资租赁合同》之补充协议(提前结清)', p_common_seal => 'Y', p_corporate_visa_seal => 'Y', p_legal_seal => 'N', p_con_special_seal => 'N', p_financial_seal => 'N', p_supervisio_seal => 'N', p_seal_number => 'SEAL_O_F_NUMBER', p_user_id => p_user_id); p_change_req_id := v_change_req_id; end create_change_req_et; --通过结清日获取结清相关信息 procedure calc_et_date_amount(p_contract_id in number, p_et_date date, p_et_fee in out number, p_et_interest_rate in number, p_et_total_amount out number, p_et_due_amount out number, p_overdue_amount out number, p_penalty out number, p_fund_possession_time out number, p_fund_possession_cost out number, p_sum_unreceived_principal out number, p_user_id in number) is v_contract_rec con_contract%rowtype; v_times number; --剩余本金 v_sum_unreceived_principal number; v_fund_possession_cost number; v_fund_possession_time number; v_sum_overdue_amount number; v_penalty number; v_et_due_amount number; v_et_total_amount number; v_overdue_max_days number; e_write_off_flagg_err exception; begin select max(a.times) into v_times from con_contract_cashflow a where a.due_date >= p_et_date and a.cf_item in (1, 9) and a.write_off_flag in ('PARTIAL', 'FULL') and a.contract_id = p_contract_id; if v_times >= 1 then raise e_write_off_flagg_err; end if; v_contract_rec := get_contract_rec(p_contract_id, p_user_id); --结清日最近的未核销现金流,获取剩余本金 select nvl(f.outstanding_prin_tax_incld, 0) into v_sum_unreceived_principal from con_contract_cashflow f where f.times = (select max(f.times) from con_contract_cashflow f where f.due_date <= p_et_date and f.cf_item = 1 and f.cf_type = 1 and f.contract_id = p_contract_id) and f.due_date <= p_et_date and f.write_off_flag = 'NOT' and f.cf_item = 1 and f.cf_type = 1 and f.contract_id = p_contract_id; --上一次完全核销现金流 begin select (p_et_date - f.due_date + 1) fund_possession_time into v_fund_possession_time from con_contract_cashflow f where f.due_date > p_et_date and f.write_off_flag = 'FULL' and f.cf_item = 1 and f.cf_type = 1 and f.cf_direction != 'NONCASH' and f.contract_id = p_contract_id; exception when no_data_found then v_fund_possession_time := 1; end; v_fund_possession_cost := v_sum_unreceived_principal * nvl(v_fund_possession_time, 1) * NVL(p_et_interest_rate, v_contract_rec.int_rate_display) / 360; --逾期租金 select nvl(sum(f.overdue_amount), 0) into v_sum_overdue_amount from con_contract_cashflow f where f.write_off_flag != 'FULL' and f.overdue_status = 'Y' and f.cf_item = 1 and f.cf_type = 1 and f.cf_direction != 'NONCASH' and f.contract_id = p_contract_id; -- 逾期天数 overdue_max_days+现在至回购日天数 v_overdue_max_days := nvl(v_contract_rec.overdue_max_days, 0) + v_fund_possession_time; -- v_penalty 逾期罚息,逾期罚息=逾期租金* 逾期天数 * 0.0004。回购总额中罚息由系统自动计算,可直接进行手动调整,不需要通过罚息减免流程;逾期罚息,计算到回购解约日为止 v_penalty := v_sum_overdue_amount * v_overdue_max_days * 0.0004; v_et_due_amount := v_sum_unreceived_principal + v_sum_overdue_amount; v_et_total_amount := v_sum_unreceived_principal + p_et_fee + v_fund_possession_cost + nvl(v_contract_rec.residual_value, 0) + v_sum_overdue_amount; --回写字段 p_et_total_amount := v_et_total_amount; p_et_due_amount := v_et_due_amount; p_overdue_amount := v_sum_overdue_amount; p_penalty := v_penalty; p_fund_possession_time := v_fund_possession_time; p_fund_possession_cost := v_fund_possession_cost; p_sum_unreceived_principal := v_sum_unreceived_principal; exception when e_write_off_flagg_err then sys_raise_app_error_pkg.raise_sys_others_error(p_message => '第' || v_times || '期已有核销记录,请重新选择期数' || v_times || '之后数据发起结清!', p_created_by => p_user_id, p_package_name => 'cus_con_et_pkg', p_procedure_function_name => 'calc_et_date_amount'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end calc_et_date_amount; procedure delete_cashflow(p_contract_id number, p_times number) is begin delete from con_contract_cashflow cf where cf.times > p_times and cf.cf_item in (1, 9) and cf.contract_id = p_contract_id; end; --结清现金流计算 procedure calc_et_cashflows(p_contract_id in number, p_user_id in number) is v_change_req_rec con_contract_change_req%rowtype; v_cashflow_rec con_contract_cashflow%rowtype; v_principal number; --本金 v_interest number; --利息 v_due_date date; v_times number; begin v_change_req_rec := get_con_change_req_rec(p_contract_id, p_user_id); select * into v_cashflow_rec from con_contract_cashflow f where f.times = (select max(f.times) from con_contract_cashflow f where f.due_date <= v_change_req_rec.termination_date and f.cf_item = 1 and f.cf_type = 1 and f.contract_id = p_contract_id) and f.due_date <= v_change_req_rec.termination_date and f.cf_item = 1 and f.cf_type = 1 and f.contract_id = p_contract_id; v_times := v_cashflow_rec.times + 1; --计算前删除本次变更起始期及之后期, delete_cashflow(p_contract_id, v_cashflow_rec.times); --创建提前结清款现金流 v_cashflow_rec := null; v_interest := round(v_change_req_rec.et_total_amount * 0.13 / 1.13, 2); v_principal := round(v_change_req_rec.et_total_amount / 1.13, 2); v_due_date := v_change_req_rec.termination_date; v_cashflow_rec.cashflow_id := con_contract_cashflow_s.nextval; v_cashflow_rec.contract_id := p_contract_id; v_cashflow_rec.cf_item := 200; v_cashflow_rec.cf_type := 1; v_cashflow_rec.cf_direction := 'INFLOW'; v_cashflow_rec.cf_status := 'RELEASE'; v_cashflow_rec.times := v_times; v_cashflow_rec.calc_date := v_due_date; v_cashflow_rec.due_date := v_due_date; v_cashflow_rec.due_amount := v_change_req_rec.et_total_amount; v_cashflow_rec.net_due_amount := round(v_change_req_rec.et_total_amount / 1.13, 2); v_cashflow_rec.vat_due_amount := round(v_change_req_rec.et_total_amount * 0.13 / 1.13, 2); v_cashflow_rec.principal := v_principal; v_cashflow_rec.net_principal := round(v_principal / 1.13, 2); v_cashflow_rec.vat_principal := round(v_principal * 0.13 / 1.13, 2); v_cashflow_rec.interest := v_interest; v_cashflow_rec.net_interest := round(v_interest / 1.13, 2); v_cashflow_rec.vat_interest := round(v_interest * 0.13 / 1.13, 2); v_cashflow_rec.received_amount := 0; v_cashflow_rec.received_principal := 0; v_cashflow_rec.received_interest := 0; v_cashflow_rec.outstanding_prin_tax_incld := 0; v_cashflow_rec.write_off_flag := 'NOT'; v_cashflow_rec.overdue_status := 'NOT'; v_cashflow_rec.penalty_process_status := 'NORMAL'; v_cashflow_rec.billing_status := 'N'; v_cashflow_rec.generated_source := 'MANUAL'; v_cashflow_rec.created_by := p_user_id; v_cashflow_rec.creation_date := sysdate; v_cashflow_rec.last_updated_by := p_user_id; v_cashflow_rec.last_update_date := sysdate; insert into con_contract_cashflow values v_cashflow_rec; --创建提前结清手续费现金流 v_cashflow_rec := null; v_interest := round(v_change_req_rec.et_fee * 0.13 / 1.13, 2); v_principal := round(v_change_req_rec.et_fee / 1.13, 2); v_due_date := v_change_req_rec.termination_date; v_cashflow_rec.cashflow_id := con_contract_cashflow_s.nextval; v_cashflow_rec.contract_id := p_contract_id; v_cashflow_rec.cf_item := 11; v_cashflow_rec.cf_type := 11; v_cashflow_rec.cf_direction := 'INFLOW'; v_cashflow_rec.cf_status := 'RELEASE'; v_cashflow_rec.times := v_times; v_cashflow_rec.calc_date := v_due_date; v_cashflow_rec.due_date := v_due_date; v_cashflow_rec.due_amount := v_change_req_rec.et_fee; v_cashflow_rec.net_due_amount := round(v_change_req_rec.et_fee / 1.13, 2); v_cashflow_rec.vat_due_amount := round(v_change_req_rec.et_fee * 0.13 / 1.13, 2); v_cashflow_rec.principal := v_principal; v_cashflow_rec.net_principal := round(v_principal / 1.13, 2); v_cashflow_rec.vat_principal := round(v_principal * 0.13 / 1.13, 2); v_cashflow_rec.interest := v_interest; v_cashflow_rec.net_interest := round(v_interest / 1.13, 2); v_cashflow_rec.vat_interest := round(v_interest * 0.13 / 1.13, 2); v_cashflow_rec.received_amount := 0; v_cashflow_rec.received_principal := 0; v_cashflow_rec.received_interest := 0; v_cashflow_rec.outstanding_prin_tax_incld := 0; v_cashflow_rec.write_off_flag := 'NOT'; v_cashflow_rec.overdue_status := 'NOT'; v_cashflow_rec.penalty_process_status := 'NORMAL'; v_cashflow_rec.billing_status := 'N'; v_cashflow_rec.generated_source := 'MANUAL'; v_cashflow_rec.created_by := p_user_id; v_cashflow_rec.creation_date := sysdate; v_cashflow_rec.last_updated_by := p_user_id; v_cashflow_rec.last_update_date := sysdate; insert into con_contract_cashflow values v_cashflow_rec; --创建提前结清资金占用费现金流 v_cashflow_rec := null; v_interest := round(v_change_req_rec.fund_possession_cost * 0.13 / 1.13, 2); v_principal := round(v_change_req_rec.fund_possession_cost / 1.13, 2); v_due_date := v_change_req_rec.termination_date; v_cashflow_rec.cashflow_id := con_contract_cashflow_s.nextval; v_cashflow_rec.contract_id := p_contract_id; v_cashflow_rec.cf_item := 14; v_cashflow_rec.cf_type := 14; v_cashflow_rec.cf_direction := 'INFLOW'; v_cashflow_rec.cf_status := 'RELEASE'; v_cashflow_rec.times := v_times; v_cashflow_rec.calc_date := v_due_date; v_cashflow_rec.due_date := v_due_date; v_cashflow_rec.due_amount := v_change_req_rec.fund_possession_cost; v_cashflow_rec.net_due_amount := round(v_change_req_rec.fund_possession_cost / 1.13, 2); v_cashflow_rec.vat_due_amount := round(v_change_req_rec.fund_possession_cost * 0.13 / 1.13, 2); v_cashflow_rec.principal := v_principal; v_cashflow_rec.net_principal := round(v_principal / 1.13, 2); v_cashflow_rec.vat_principal := round(v_principal * 0.13 / 1.13, 2); v_cashflow_rec.interest := v_interest; v_cashflow_rec.net_interest := round(v_interest / 1.13, 2); v_cashflow_rec.vat_interest := round(v_interest * 0.13 / 1.13, 2); v_cashflow_rec.received_amount := 0; v_cashflow_rec.received_principal := 0; v_cashflow_rec.received_interest := 0; v_cashflow_rec.outstanding_prin_tax_incld := 0; v_cashflow_rec.write_off_flag := 'NOT'; v_cashflow_rec.overdue_status := 'NOT'; v_cashflow_rec.penalty_process_status := 'NORMAL'; v_cashflow_rec.billing_status := 'N'; v_cashflow_rec.generated_source := 'MANUAL'; v_cashflow_rec.created_by := p_user_id; v_cashflow_rec.creation_date := sysdate; v_cashflow_rec.last_updated_by := p_user_id; v_cashflow_rec.last_update_date := sysdate; insert into con_contract_cashflow values v_cashflow_rec; end calc_et_cashflows; --结清提交 procedure submit_change_req_et(p_change_req_id number, p_layout_code varchar2, p_user_id number) is r_change_req_rec con_contract_change_req%rowtype; r_change_con_rec con_contract%rowtype; v_approval_method hls_document_type.approval_method%type; e_change_req_status_error exception; begin hls_document_compare_pkg.con_contract_compare(p_table_name => 'CON_CONTRACT', p_change_req_id => p_change_req_id, p_from_table_pk => '', p_to_table_pk => p_change_req_id, p_user_id => p_user_id); r_change_req_rec := get_con_change_req_rec(p_change_req_id => p_change_req_id, p_user_id => p_user_id); r_change_con_rec := con_contract_pkg.get_contract_rec(p_contract_id => p_change_req_id, p_user_id => p_user_id); if r_change_req_rec.req_status not in ('NEW', 'REJECT') then raise e_change_req_status_error; end if; con_change_req_custom_pkg.before_submit_change_req(p_change_req_id => p_change_req_id, p_user_id => p_user_id); con_contract_history_pkg.calc_change_history(p_change_req_id => p_change_req_id, p_user_id => p_user_id); cus_con_change_req_wfl_pkg.workflow_start(p_change_req_rec => r_change_req_rec, p_change_con_rec => r_change_con_rec, p_layout_code => p_layout_code, p_user_id => p_user_id); end submit_change_req_et; end cus_con_et_pkg; /