create or replace package con_contract_change_req_pkg is -- Author : GAOYANG -- Created : 2013/9/24 14:55:07 -- Purpose : 变更申请 -- Version : 20150820 procedure bm_check_after_save(p_change_req_id in number, p_user_id in number); procedure bm_check_before_delete(p_change_req_id in number, p_user_id in number); function get_con_change_req_number(p_document_type varchar2, p_document_category varchar2, p_business_type varchar2 default null, p_company_id number, p_req_date date, p_user_id number) return varchar2; 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; function get_ccr_start_times(p_change_con_rec in con_contract%rowtype, p_date in date) return number; procedure calc_ccr_data(p_change_req_rec in out nocopy con_contract_change_req%rowtype, p_change_con_rec in out nocopy con_contract%rowtype, p_user_id in number); procedure change_req_calc(p_change_req_id in number, p_calc_session_id in out number, p_calc_successful out varchar2, p_warning_message out varchar2, p_recreate_h_formula in varchar2 default 'Y', p_recreate_l_formula in varchar2 default 'Y', p_recreate_l_std in varchar2 default 'Y', p_user_id in number); procedure change_req_submit(p_change_req_id in number, p_ccr_fee in varchar2 default null, p_ccr_penalty in varchar2 default null, p_btb_payment_date in date default null, p_layout_code in varchar2 default null, p_user_id in number); procedure change_req_confirm(p_change_req_id in number, p_user_id in number); procedure change_req_reject(p_change_req_id in number, p_user_id in number); procedure change_req_cancel(p_change_req_id in number, p_user_id in number); --从正式环境搬过来的insert 过程 procedure submit_con_change_req(p_change_req_id con_contract_change_req.change_req_id%type, p_user_id con_contract_change_req.created_by%type); procedure insert_con_change_req(p_change_req_id out con_contract_change_req.change_req_id%type, p_batch_id con_contract_change_req.batch_id%type, p_change_req_number con_contract_change_req.change_req_number%type default null, p_document_type con_contract_change_req.document_type%type, p_document_category con_contract_change_req.document_category%type, p_business_type con_contract_change_req.business_type%type, p_company_id con_contract_change_req.company_id%type, p_contract_id con_contract_change_req.contract_id%type, p_req_status con_contract_change_req.req_status%type, p_req_date con_contract_change_req.req_date%type, p_change_req_profile con_contract_change_req.change_req_profile%type default null, p_reschedule_start_date con_contract_change_req.reschedule_start_date%type default null, p_reschedule_start_times con_contract_change_req.reschedule_start_times%type default null, p_reschedule_new_date con_contract_change_req.reschedule_new_date%type default null, p_reschedule_total_times con_contract_change_req.reschedule_total_times%type default null, p_reschedule_last_time con_contract_change_req.reschedule_last_time%type default null, p_reschedule_balance con_contract_change_req.reschedule_balance%type default null, p_reschedule_direction con_contract_change_req.reschedule_direction%type default null, p_reschedule_days con_contract_change_req.reschedule_days%type default null, p_reschedule_fee con_contract_change_req.reschedule_fee%type default null, p_calculate_rate con_contract_change_req.calculate_rate%type default null, p_termination_date con_contract_change_req.termination_date%type default null, p_overdue_amount con_contract_change_req.overdue_amount%type default null, p_penalty con_contract_change_req.penalty%type default null, p_rentals_payable con_contract_change_req.rentals_payable%type default null, p_discount_total_amount con_contract_change_req.discount_total_amount%type default null, p_undue_principal con_contract_change_req.undue_principal%type default null, p_et_interest con_contract_change_req.et_interest%type default null, p_mgt_fee_after_discount con_contract_change_req.mgt_fee_after_discount%type default null, p_et_fee con_contract_change_req.et_fee%type default null, p_residual_value con_contract_change_req.residual_value%type default null, p_et_total_amount con_contract_change_req.et_total_amount%type default null, p_last_paydue_date con_contract_change_req.last_paydue_date%type default null, p_last_paydue_times con_contract_change_req.last_paydue_times%type default null, p_discount_days con_contract_change_req.discount_days%type default null, p_submit_date con_contract_change_req.submit_date%type default null, p_approved_date con_contract_change_req.approved_date%type default null, p_description con_contract_change_req.description%type default null, p_calc_session_id con_contract_change_req.calc_session_id%type default null, p_wfl_instance_id con_contract_change_req.wfl_instance_id%type default null, p_user_id con_contract_change_req.created_by%type, p_ref_v01 con_contract_change_req.ref_v01%type default null, p_ref_v02 con_contract_change_req.ref_v02%type default null, p_ref_v03 con_contract_change_req.ref_v03%type default null, p_ref_v04 con_contract_change_req.ref_v04%type default null, p_ref_v05 con_contract_change_req.ref_v05%type default null, p_ref_v06 con_contract_change_req.ref_v06%type default null, p_ref_v07 con_contract_change_req.ref_v07%type default null, p_ref_v08 con_contract_change_req.ref_v08%type default null, p_ref_v09 con_contract_change_req.ref_v09%type default null, p_ref_v10 con_contract_change_req.ref_v10%type default null, p_ref_n01 con_contract_change_req.ref_n01%type default null, p_ref_n02 con_contract_change_req.ref_n02%type default null, p_ref_n03 con_contract_change_req.ref_n03%type default null, p_ref_n04 con_contract_change_req.ref_n04%type default null, p_ref_n05 con_contract_change_req.ref_n05%type default null, p_ref_n06 con_contract_change_req.ref_n06%type default null, p_ref_n07 con_contract_change_req.ref_n07%type default null, p_ref_n08 con_contract_change_req.ref_n08%type default null, p_ref_n09 con_contract_change_req.ref_n09%type default null, p_ref_n10 con_contract_change_req.ref_n10%type default null, p_ref_d01 con_contract_change_req.ref_d01%type default null, p_ref_d02 con_contract_change_req.ref_d02%type default null, p_ref_d03 con_contract_change_req.ref_d03%type default null, p_ref_d04 con_contract_change_req.ref_d04%type default null, p_ref_d05 con_contract_change_req.ref_d05%type default null, p_ref_d06 con_contract_change_req.ref_d06%type default null, p_ref_d07 con_contract_change_req.ref_d07%type default null, p_ref_d08 con_contract_change_req.ref_d08%type default null, p_ref_d09 con_contract_change_req.ref_d09%type default null, p_ref_d10 con_contract_change_req.ref_d10%type default null); --合同变更(内部) procedure change_req_in_confirm(p_change_req_id in number, p_user_id in number); procedure change_req_confirm_d(p_change_req_id in number, p_user_id in number); --modify by lpc 9874 合同承租人变更计算应缴纳金额 procedure calc_leasse_date(p_contract_id number, p_btb_payment_date date, p_ccr_leasse_rental out number, --应缴纳租金 p_ccr_leasse_principal out number, --应缴纳本金 p_ccr_leasse_interest out number, --应缴纳利息 p_ccr_leasse_penalty out number, p_ccr_outstanding_cld out number); --应缴纳罚息 procedure calc_leasse_pay(p_contract_id number, p_ccr_start_times number, p_btb_payment_date date default null, p_interim_rental number default null, p_layout_code varchar2, p_ccr_outstanding_times out number, p_ccr_dept out varchar2, p_ccr_outstanding_incld out varchar2, p_ccr_fee out number); procedure calc_penalty(p_cashflow_rec con_contract_cashflow%rowtype, p_contract_rec con_contract%rowtype, p_calc_date date, p_calc_method varchar2, p_total_penalty_amt out number); --插入变更手续费304.承租人期数1,租赁物期数2 procedure insert_ccr_cashflow(p_contract_id number, p_ccr_document_type varchar2, p_btb_payment_date date default null, p_bp_id number default null, p_lease_item_id number default null, p_pattern varchar2 default null, p_machine_number varchar2 default null, p_interim_times number default null, p_ccr_start_times number default null, p_hd_user_col_v04 varchar2 default null, p_btb_finance_amount number default null, --部分中途偿还本金 p_user_id varchar2); procedure insert_ccr_fee(p_contract_id number, p_user_id number, p_document_type varchar2 default null, p_cf_item varchar2 default null, p_cf_type varchar2 default null, p_due_date date default null, p_ccr_fee number); procedure update_change_req(p_contract_id number, p_ccr_start_times number, p_btb_payment_date date); end con_contract_change_req_pkg; / create or replace package body con_contract_change_req_pkg is e_lock_table exception; pragma exception_init(e_lock_table, -54); procedure bm_check_after_save(p_change_req_id in number, p_user_id in number) is begin null; end; procedure bm_check_before_delete(p_change_req_id in number, p_user_id in number) is begin null; end; function get_con_change_req_number(p_document_type varchar2, p_document_category varchar2, p_business_type varchar2 default null, p_company_id number, p_req_date date, p_user_id number) return varchar2 is v_document_number varchar2(100); begin v_document_number := fnd_code_rule_pkg.get_rule_next_auto_num(p_document_category => p_document_category, p_document_type => p_document_type, p_company_id => p_company_id, p_operation_unit_id => null, p_operation_date => p_req_date, p_created_by => p_user_id); if v_document_number = fnd_code_rule_pkg.c_error then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CON_CONTRACT_CHANGE_REQ_PKG.CON_CHANGE_REQ_NUMBER_NULL', p_created_by => p_user_id, p_package_name => 'con_contract_change_req_pkg', p_procedure_function_name => 'get_con_change_req_number'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end if; return v_document_number; 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 => 'con_contract_change_req_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; function get_ccr_start_times(p_change_con_rec in con_contract%rowtype, p_date in date) return number is v_times number; begin select min(times) into v_times from con_contract_cashflow where contract_id = p_change_con_rec.contract_id and cf_item = 1 and cf_direction = 'INFLOW' and due_date > p_date; if v_times is null then if p_change_con_rec.inception_of_lease is null then v_times := 1; else v_times := null; end if; end if; return v_times; end; procedure calc_ccr_data(p_change_req_rec in out nocopy con_contract_change_req%rowtype, p_change_con_rec in out nocopy con_contract%rowtype, p_user_id in number) is v_ccr_outstanding_times number; -- 到期未支付租金 v_ccr_overdue_rental number; -- 到期未支付租金(逾期租金) v_ccr_overdue_prin number; -- 到期未支付本金(逾期本金) v_ccr_overdue_interest number; -- 到期未支付利息(逾期利息) v_ccr_outs_rental number; -- 未到期租金 v_ccr_outs_prin_tax_incld number; -- 自变更期起的剩余本金 v_ccr_outs_prin_ti_total number; -- 所有期的剩余本金 v_ccr_other_receivables number; -- 剩余其他应收 v_ccr_penalty number; -- v_ccr_deposit_balance number; -- begin select lease_times - p_change_con_rec.ccr_start_times + 1 into v_ccr_outstanding_times from con_contract where contract_id = p_change_con_rec.contract_id; -- 到期未支付租金 select nvl(sum(a.due_amount - nvl(a.received_amount, 0)), 0), nvl(sum(a.principal - nvl(a.received_principal, 0)), 0), nvl(sum(a.interest - nvl(a.received_interest, 0)), 0) into v_ccr_overdue_rental, v_ccr_overdue_prin, v_ccr_overdue_interest from con_contract_cashflow a where a.contract_id = p_change_req_rec.contract_id and a.cf_type = 1 and a.times > 0 and a.times < p_change_con_rec.ccr_start_times and a.cf_direction = 'INFLOW'; -- 未到期租金 select nvl(sum(a.due_amount - nvl(a.received_amount, 0)), 0) into v_ccr_outs_rental from con_contract_cashflow a where a.contract_id = p_change_req_rec.contract_id and a.cf_type = 1 and a.times >= p_change_con_rec.ccr_start_times and a.cf_direction = 'INFLOW'; --自变更期起的剩余本金 select nvl(sum(a.principal - nvl(a.received_principal, 0)), 0) into v_ccr_outs_prin_tax_incld from con_contract_cashflow a where a.contract_id = p_change_req_rec.contract_id and a.cf_type = 1 and a.times >= p_change_con_rec.ccr_start_times and a.cf_direction = 'INFLOW'; --所有期的剩余本金 select nvl(sum(a.principal - nvl(a.received_principal, 0)), 0) into v_ccr_outs_prin_ti_total from con_contract_cashflow a where a.contract_id = p_change_req_rec.contract_id and a.cf_type = 1 and a.times > 0 and a.cf_direction = 'INFLOW'; /* 未收逾期罚息 提供2种算法, 1) 从以计算的现金流(9) 中取数 2) 通过罚息计算,获取变更日当时的罚息(调用罚息试算,更新罚息现金流) con_overdue_penalty_pkg.overdue_dayend(p_contract_id => p_change_req_rec.change_req_id, p_calc_date => p_et_date-1, p_user_id => v_old_contract_rec.created_by, p_calc_method => con_overdue_penalty_pkg.c_penalty_calc); */ select nvl(sum(a.due_amount - nvl(a.received_amount, 0)), 0) into v_ccr_penalty from con_contract_cashflow a where a.contract_id = p_change_req_rec.change_req_id and a.cf_type = 9 and a.times > 0 and a.cf_direction = 'INFLOW'; --保证金余额 select nvl(sum(a.due_amount - nvl(a.received_amount, 0)), 0) into v_ccr_deposit_balance from con_contract_cashflow a where a.contract_id = p_change_req_rec.contract_id and a.cf_type = 5 -- 未必,尚未考虑第三方保证金,anthony and a.cf_item = 52 --排除第三方保证金 and a.cf_direction = 'OUTFLOW'; --剩余其他应收 select nvl(sum(a.due_amount - nvl(a.received_amount, 0)), 0) into v_ccr_other_receivables from con_contract_cashflow a where a.contract_id = p_change_req_rec.contract_id and a.cf_type not in (1, 5, 9) and a.cf_direction = 'INFLOW'; p_change_con_rec.ccr_original_finance_amount := p_change_con_rec.finance_amount; p_change_con_rec.ccr_outstanding_prin_tax_incld := v_ccr_outs_prin_tax_incld; p_change_con_rec.ccr_overdue_rental := v_ccr_overdue_rental; p_change_con_rec.ccr_overdue_prin := v_ccr_overdue_prin; p_change_con_rec.ccr_overdue_interest := v_ccr_overdue_interest; p_change_con_rec.ccr_penalty := v_ccr_penalty; p_change_con_rec.ccr_outstanding_prin_ti_total := v_ccr_outs_prin_ti_total; p_change_con_rec.ccr_outstanding_times := v_ccr_outstanding_times; p_change_con_rec.ccr_outstanding_rental := v_ccr_outs_rental; p_change_con_rec.ccr_other_receivables := v_ccr_other_receivables; p_change_con_rec.ccr_deposit_balance := v_ccr_deposit_balance; update con_contract set row = p_change_con_rec where contract_id = p_change_con_rec.contract_id; if p_change_con_rec.ccr_start_times is not null and p_change_con_rec.ccr_financing_overdue_rental = '1' then for c_before_cf in (select * from con_contract_cashflow a where a.contract_id = p_change_req_rec.change_req_id and a.times < p_change_con_rec.ccr_start_times and a.cf_direction = 'INFLOW' and a.cf_type = 1) loop if nvl(c_before_cf.interest, 0) = 0 then null; else c_before_cf.vat_interest := round(c_before_cf.vat_interest * (nvl(c_before_cf.received_interest, 0) / c_before_cf.interest), 2); c_before_cf.interest := nvl(c_before_cf.received_interest, 0); c_before_cf.net_interest := c_before_cf.interest - nvl(c_before_cf.vat_interest, 0); end if; if nvl(c_before_cf.principal, 0) = 0 then null; else c_before_cf.vat_principal := round(c_before_cf.vat_principal * (nvl(c_before_cf.received_principal, 0) / c_before_cf.principal), 2); c_before_cf.principal := nvl(c_before_cf.received_principal, 0); c_before_cf.net_principal := c_before_cf.principal - nvl(c_before_cf.vat_principal, 0); end if; if nvl(c_before_cf.due_amount, 0) = 0 then null; else c_before_cf.vat_due_amount := round(c_before_cf.vat_due_amount * (nvl(c_before_cf.received_amount, 0) / c_before_cf.due_amount), 2); c_before_cf.due_amount := nvl(c_before_cf.received_amount, 0); c_before_cf.net_due_amount := c_before_cf.due_amount - nvl(c_before_cf.vat_due_amount, 0); end if; c_before_cf.write_off_flag := 'FULL'; c_before_cf.penalty_process_status := 'SUSPEND'; c_before_cf.full_write_off_date := c_before_cf.last_received_date; c_before_cf.last_updated_by := p_user_id; c_before_cf.last_update_date := sysdate; update con_contract_cashflow a set row = c_before_cf where cashflow_id = c_before_cf.cashflow_id; end loop; end if; end; procedure change_req_calc(p_change_req_id in number, p_calc_session_id in out number, p_calc_successful out varchar2, p_warning_message out varchar2, p_recreate_h_formula in varchar2 default 'Y', p_recreate_l_formula in varchar2 default 'Y', p_recreate_l_std in varchar2 default 'Y', p_user_id in number) is begin hls_fin_calculator_ccr_pkg.create_calculate(p_change_req_id => p_change_req_id, p_calc_session_id => p_calc_session_id, p_recreate_h_formula => nvl(p_recreate_h_formula, 'Y'), p_recreate_l_std => nvl(p_recreate_l_std, 'Y'), p_recreate_l_formula => nvl(p_recreate_l_formula, 'Y'), p_calc_successful => p_calc_successful, p_warning_message => p_warning_message, p_user_id => p_user_id); update con_contract_change_req set is_calc_flag = 'Y' where change_req_id = p_change_req_id; end; procedure change_req_submit(p_change_req_id in number, p_ccr_fee in varchar2 default null, p_ccr_penalty in varchar2 default null, p_btb_payment_date in date default null, p_layout_code in varchar2 default null, p_user_id in 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; v_calc_hd_rec hls_fin_calculator_hd%rowtype; v_success_flag varchar2(1); e_change_req_status_error exception; e_calc_error exception; e_simulation_error exception; e_calc_session_id_notfound exception; e_is_calc_flag_err exception; v_request_num number; e_attachment_error exception; v_cf_count number; v_rate number; v_total_interest number; v_vat_total_interest number; begin r_change_req_rec := get_con_change_req_rec(p_change_req_id => p_change_req_id, p_user_id => p_user_id); --冬雨季,承租人,债务再调整走起案,只计算变更手续费 if r_change_req_rec.document_type not in ('CONEXTHAG', 'DEBT_CHAG', 'LEASSE_CHAG') then if r_change_req_rec.simulation_flag = 'Y' then raise e_simulation_error; end if; /*add by Harry 9952 2017/1/19 purpose: verify the atttachment required property when submit the document */ --应deron业务需要去除校验 /* select count(*) into v_request_num from prj_cdd_item_doc_ref dr, prj_cdd_item_check ic, prj_cdd_item_list_grp_tab gt where dr.document_table = 'PRJ_PROJECT' and dr.document_id = (select c.project_id from con_contract c where c.contract_id = r_change_req_rec.contract_id) and dr.check_id = ic.check_id and ic.cdd_item_id = gt.cdd_item_id and gt.important_flag = 'Y' and not exists (select 1 from fnd_atm_attachment_multi fam where fam.table_name = 'PRJ_CDD_ITEM_CHECK' and fam.table_pk_value = ic.check_id); if v_request_num > 0 then raise e_attachment_error; end if;*/ r_change_con_rec := con_contract_pkg.get_contract_rec(p_contract_id => p_change_req_id, p_user_id => p_user_id); --r_change_req_rec.req_status := 'NEW'; if r_change_req_rec.req_status not in ('NEW', 'REJECT') then raise e_change_req_status_error; end if; --当是罚息变更申请的时候,没有存contract_id,所以不需要走这一步 if r_change_req_rec.ccr_price_list is not null then if r_change_req_rec.ccr_calc_session_id is null then raise e_calc_session_id_notfound; else select calc_successful into v_success_flag from hls_fin_calculator_hd where calc_session_id = r_change_req_rec.ccr_calc_session_id; if nvl(v_success_flag, 'N') <> 'Y' then raise e_calc_error; end if; end if; if nvl(r_change_req_rec.is_calc_flag, 'N') = 'N' then raise e_is_calc_flag_err; end if; 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); if r_change_req_rec.document_type = 'DEBT_CHAG' then if to_number(p_ccr_fee) < 2000 then update con_contract t set t.fee_waiver = 'Y' where t.contract_id = p_change_req_id; else update con_contract t set t.fee_waiver = 'N' where t.contract_id = p_change_req_id; end if; elsif r_change_req_rec.document_type in ('CONEXTHAG', 'LEASEHOLD_CHAG', 'LEASSE_CHAG') then if to_number(p_ccr_fee) < 1000 then update con_contract t set t.fee_waiver = 'Y' where t.contract_id = p_change_req_id; else update con_contract t set t.fee_waiver = 'N' where t.contract_id = p_change_req_id; end if; elsif r_change_req_rec.document_type = 'PART_CHAG' then if to_number(p_ccr_fee) < 500 then update con_contract t set t.fee_waiver = 'Y' where t.contract_id = p_change_req_id; else update con_contract t set t.fee_waiver = 'N' where t.contract_id = p_change_req_id; end if; end if; --还款日变更不插手续费更新现金流cf_item = 1 或者103的利息字段 if r_change_req_rec.document_type <> 'PAYDATE_CHAG' then --债权债务待调整插入罚息总额 if r_change_req_rec.document_type = 'DEBT_CHAG' then --插入手续费日期为基准日期 insert_ccr_fee(p_contract_id => p_change_req_id, p_user_id => p_user_id, p_cf_item => 304, p_cf_type => 3, p_document_type => r_change_req_rec.document_type, p_due_date => p_btb_payment_date, p_ccr_fee => p_ccr_fee); insert_ccr_fee(p_contract_id => p_change_req_id, p_user_id => p_user_id, p_cf_item => 10, p_cf_type => 9, p_document_type => r_change_req_rec.document_type, p_due_date => p_btb_payment_date, p_ccr_fee => p_ccr_penalty); elsif r_change_req_rec.document_type <> 'LEASEHOLD_CHAG' then --变更插入变更手续费现金流(租赁物不加手续费,modify by liuhaojie) insert_ccr_fee(p_contract_id => p_change_req_id, p_user_id => p_user_id, p_cf_item => 304, p_cf_type => 3, p_document_type => r_change_req_rec.document_type, p_due_date => sysdate, p_ccr_fee => p_ccr_fee); end if; else select count(1) into v_cf_count from con_contract_cashflow ci where ci.contract_id = p_change_req_id and ci.cf_item = 1 and ci.times = r_change_con_rec.ccr_start_times; --取税率的方法 select con_credit_report_pkg.get_tax_type_rate(p_change_req_id) into v_rate from dual; --cf_item = 1 租金的due_amount=原有金额+延期手续费。 --net_due_amount=due_amount-vat_due_amount --Vat_due_amount=原有vat_due_amount+round(延期手续费*税率/(1+税率),2) --Interest=延期手续费+原有的利息 --Vat_interest= 原有vat_interest+round(延期手续费*税率/(1+税率),2) --Net_interest=interest-vat_interest --net_interest_implicit=net_interest if v_cf_count > 0 then update con_contract_cashflow ccc set ccc.due_amount = ccc.due_amount + p_ccr_fee, ccc.net_due_amount = (ccc.due_amount + p_ccr_fee) - (ccc.vat_due_amount + round(p_ccr_fee * v_rate / (1 + v_rate), 2)), ccc.vat_due_amount = ccc.vat_due_amount + round(p_ccr_fee * v_rate / (1 + v_rate), 2), ccc.interest = ccc.interest + p_ccr_fee, ccc.net_interest = (ccc.interest + p_ccr_fee) - (ccc.vat_interest + round(p_ccr_fee * v_rate / (1 + v_rate), 2)), ccc.vat_interest = ccc.vat_interest + round(p_ccr_fee * v_rate / (1 + v_rate), 2), ccc.net_interest_implicit = (ccc.interest + p_ccr_fee) - (ccc.vat_interest + round(p_ccr_fee * v_rate / (1 + v_rate), 2)) where ccc.contract_id = p_change_req_id and ccc.cf_direction = 'INFLOW' and ccc.cf_status = 'RELEASE' and ccc.cf_item = 1 and ccc.times = r_change_con_rec.ccr_start_times; begin select cc1.total_interest into v_total_interest from con_contract cc1 where cc1.contract_id = (select ccr.contract_id from con_contract_change_req ccr where ccr.change_req_id = p_change_req_id); exception when no_data_found then v_total_interest := 0; end; update con_contract cc set cc.total_interest = nvl(v_total_interest, 0) + nvl(p_ccr_fee, 0), cc.vat_total_interest = (select (sum(nvl(ccc.vat_interest, 0))) from con_contract_cashflow ccc where ccc.contract_id = p_change_req_id and ccc.cf_direction = 'INFLOW' and ccc.cf_status = 'RELEASE' and ccc.cf_item = 1), cc.net_total_interest = (select (sum(nvl(ccc.net_interest, 0))) from con_contract_cashflow ccc where ccc.contract_id = p_change_req_id and ccc.cf_direction = 'INFLOW' and ccc.cf_status = 'RELEASE' and ccc.cf_item = 1) where cc.contract_id = p_change_req_id; update con_contract cc set cc.repayment_date_day = extract(day from p_btb_payment_date) where cc.contract_id = p_change_req_id; else --cf_item = 103 update con_contract_cashflow ccc set ccc.due_amount = p_ccr_fee + ccc.due_amount, ccc.net_due_amount = (ccc.due_amount + p_ccr_fee) - (ccc.vat_due_amount + round(p_ccr_fee * v_rate / (1 + v_rate), 2)), ccc.vat_due_amount = ccc.vat_due_amount + round(p_ccr_fee * v_rate / (1 + v_rate), 2) where ccc.contract_id = p_change_req_id and ccc.cf_direction = 'INFLOW' and ccc.cf_status = 'RELEASE' and ccc.cf_item = 103 and ccc.times = r_change_con_rec.ccr_start_times; begin select cc1.total_interest, cc1.vat_total_interest into v_total_interest, v_vat_total_interest from con_contract cc1 where cc1.contract_id = (select ccr.contract_id from con_contract_change_req ccr where ccr.change_req_id = p_change_req_id); exception when no_data_found then v_total_interest := 0; v_vat_total_interest := 0; end; update con_contract cc set cc.total_interest = nvl(v_total_interest, 0) + nvl(p_ccr_fee, 0), cc.vat_total_interest = nvl(v_vat_total_interest, 0) + (round(p_ccr_fee * v_rate / (1 + v_rate), 2)), cc.net_total_interest = nvl(v_total_interest, 0) + nvl(p_ccr_fee, 0) - (nvl(v_vat_total_interest, 0) + (round(p_ccr_fee * v_rate / (1 + v_rate), 2))) where cc.contract_id = p_change_req_id; update con_contract cc set cc.repayment_date_day = extract(day from p_btb_payment_date) where cc.contract_id = p_change_req_id; end if; end if; select approval_method into v_approval_method from hls_document_type where document_category = r_change_req_rec.document_category and document_type = r_change_req_rec.document_type; if v_approval_method = 'WORK_FLOW' then con_contract_change_req_wf_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); else update con_contract_change_req t set t.req_status = 'APPROVING', t.submit_date = sysdate, last_updated_by = p_user_id, last_update_date = sysdate where t.change_req_id = p_change_req_id; change_req_confirm(p_change_req_id => p_change_req_id, p_user_id => p_user_id); end if; end if; exception when e_attachment_error then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'PRJ_PROJECT_WORKFLOW_PKG.ATTACHMENT_ERROR', p_created_by => p_user_id, p_package_name => 'con_contract_change_req_pkg', p_procedure_function_name => 'change_req_submit'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); when e_change_req_status_error then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CON_CONTRACT_CHANGE_REQ_PKG.CHANGE_REQ_STATUS_ERROR', p_created_by => p_user_id, p_package_name => 'con_contract_change_req_pkg', p_procedure_function_name => 'change_req_submit'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); when e_calc_error then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CON_CONTRACT_CHANGE_REQ_PKG.CALCULATOR_STATUS_ERROR', p_created_by => p_user_id, p_package_name => 'con_contract_change_req_pkg', p_procedure_function_name => 'change_req_submit'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); when e_is_calc_flag_err then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CON_CONTRACT_CHANGE_REQ_PKG.IS_CALC_FLAG_ERR', p_created_by => p_user_id, p_package_name => 'con_contract_change_req_pkg', p_procedure_function_name => 'change_req_submit'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); when e_calc_session_id_notfound then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CON_CONTRACT_CHANGE_REQ_PKG.CALC_SESSION_ID_NOTFOUND', p_created_by => p_user_id, p_package_name => 'con_contract_change_req_pkg', p_procedure_function_name => 'change_req_submit'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); when e_simulation_error then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CON_CONTRACT_CHANGE_REQ_PKG.SIMULATION_FLAG_ERROR', p_created_by => p_user_id, p_package_name => 'con_contract_change_req_pkg', p_procedure_function_name => 'change_req_submit'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end; --合同变更申请同意 procedure change_req_confirm(p_change_req_id in number, p_user_id in number) is r_change_req_rec con_contract_change_req%rowtype; r_change_con_rec con_contract%rowtype; v_create_je_flag number; v_times number; v_down_payment_ratio number; v_instance_id number; v_start_period number; v_bill_bp_id number; v_start_times number; v_start_req_time date; v_before_change_bp_id number; v_bp_id number; begin 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); -- 1) 生成变更凭证 --LEASE_CHAG承租人期数1,LEASEHOLD_CHAG租赁物期数2,CONEXTHAG冬雨季期数3,DEBT_CHAG债务再调整期数4,PART_CHAG部分中途偿还期数5,PAY_CHAG非月转月付期数6 --PAYDATE_CHAG还款日变更 ,ET_CHAG提前结清,REPUR回购申请,CUT_CHAG租金计划变更 if nvl(r_change_req_rec.create_je_flag, 'N') = 'N' and r_change_req_rec.document_type in ('LEASE_CHAG', 'DEBT_CHAG', 'PART_CHAG', 'PAY_CHAG', 'CONEXTHAG', 'PAYDATE_CHAG', 'CUT_CHAG', 'ET_CHAG', 'REPUR') then v_create_je_flag := gld_je_template_pkg.create_je(p_je_transaction_code => 'CON_CHANGE_REQ_CONFIRM', p_company_id => r_change_req_rec.company_id, p_je_company_id => r_change_req_rec.company_id, p_user_id => p_user_id, /*凭证头id*/ p_journal_header_id => null, /*以下为业务参数*/ p_hd_source_table => 'CON_CONTRACT_CHANGE_REQ', p_hd_source_id => p_change_req_id, p_ln_source_table => 'CON_CONTRACT_CHANGE_REQ', p_ln_source_id => p_change_req_id, p_journal_date => trunc(sysdate), p_transaction_date => trunc(sysdate), p_currency_code => r_change_con_rec.currency, p_exchange_rate_quotation => 'DIRECT QUOTATION', p_hd_exchange_rate_type => 'MANUAL', p_hd_exchange_rate => 1, p_ln_exchange_rate_type => 'MANUAL', p_ln_exchange_rate => 1); if v_create_je_flag = 0 then update con_contract_change_req set create_je_flag = 'Y' where change_req_id = p_change_req_id; end if; end if; --1.1 把合同头表上的bp_id_tenant更改为行上的承租人,(以防出现变更承租人,头行不一致) select cb.bp_id into v_bp_id from con_contract_bp cb where cb.contract_id = p_change_req_id and cb.bp_category = 'TENANT' and cb.enabled_flag = 'Y'; update con_contract cc set cc.bp_id_tenant = v_bp_id where cc.contract_id = p_change_req_id; --add by chenlingfeng --在覆盖数据前,獲取變更前 tenant_bp_id select cc.bp_id_tenant into v_before_change_bp_id from con_contract cc where cc.contract_id = r_change_req_rec.contract_id; -- 2) 确认数据,覆盖原始合同 con_contract_history_pkg.change_req_confirm(p_change_req_id => p_change_req_id, p_user_id => p_user_id); --覆蓋billing_bp_id update con_contract_cashflow t set t.billing_bp_id = v_before_change_bp_id where t.contract_id = r_change_req_rec.contract_id and t.billing_bp_id is null; --将变更后的承租人id更新现金流表的开票人id --add by zhuxianfei select t.bp_id_tenant, t.btb_payment_date into v_bill_bp_id, v_start_req_time from con_contract t where t.contract_id = r_change_req_rec.contract_id; update con_contract_cashflow ccc set ccc.billing_bp_id = v_bill_bp_id where ccc.contract_id = r_change_req_rec.contract_id and ccc.cf_status = 'RELEASE' and ccc.due_date > v_start_req_time; --3) 更新合同表历史合同id字段 add by Harry 9952 2017/1/18 update con_contract c set c.history_contract_id = (select max(h.document_history_id) from hls_document_history h where h.document_id = r_change_req_rec.contract_id and h.usage_code = 'HISTORY') where c.contract_id in (r_change_con_rec.contract_id, r_change_req_rec.change_req_id); --承租人变更结束更新CALL_DETAIL的bp_id,modify by lpc for doosan if r_change_req_rec.document_type = 'LEASSE_CHAG' then update call_detail cd set cd.bp_id = (select t.bp_id_tenant from con_contract t where t.contract_id = r_change_req_rec.contract_id) where cd.wo_id in (select wo_id from work_orders where business_type <> 6 and contract_id = r_change_req_rec.contract_id) and cd.call_detail_type in ('TENANT', 'AFTERLEASE'); --只有在承租人变更通过的时候,这4个字段deduct_bank, bankacctname, bankacctid,expireddate要删除 update con_contract c set c.deduct_bank = null, c.bankacctname = null, c.bankacctid = null, c.expireddate = null where c.contract_id = r_change_req_rec.contract_id; end if; --add end --add by chenlingfeng --更新从app来的担保人信息 if r_change_req_rec.document_type = 'LEASSE_CHAG' then ds_document_interface_pkg.set_app_guarantor_to_con(p_contract_id => r_change_req_rec.contract_id, p_user_id => p_user_id); end if; /*SELECT ci.lease_times, ci.down_payment_ratio INTO v_times, v_down_payment_ratio FROM con_contract_lease_item ci WHERE ci.contract_id = p_change_req_id;*/ select cc.wfl_instance_id into v_instance_id from con_contract cc where cc.contract_id = p_change_req_id; /*UPDATE zj_wfl_workflow_instance zi SET zi.times = v_times, zi.down_payment_ratio = v_down_payment_ratio WHERE zi.instance_id = v_instance_id;*/ --承租人变更、回款账户变更不进行收入确认处理 add by 20191012 Spencer 3893 if r_change_req_rec.document_type not in ('LEASE_CHAG', 'ACC_CHAG') then begin select to_number(to_char(min(cf.due_date), 'yyyymm')) into v_start_period from con_contract_cashflow cf, con_contract cc where cf.contract_id = p_change_req_id and cc.contract_id = cf.contract_id and cf.cf_direction = 'INFLOW' and cf.times = cc.ccr_start_times; --收益分摊 con_unearned_fin_income_pkg.allocate_unearned_fin_income(p_biz_type => 'LEASE', p_contract_id => r_change_req_rec.contract_id, p_start_internal_period_num => v_start_period, p_user_id => p_user_id); exception when no_data_found then null; --找不到期间不做处理 --chenlingfeng end; end if; exception when others then sys_raise_app_error_pkg.raise_sys_others_error(p_message => dbms_utility.format_error_backtrace || ' ' || sqlerrm, p_created_by => p_user_id, p_package_name => 'con_contract_change_req_pkg', p_procedure_function_name => 'change_req_cinfirm'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end; --合同变更申请拒绝 procedure change_req_reject(p_change_req_id in number, p_user_id in number) is begin con_contract_history_pkg.change_req_reject(p_change_req_id => p_change_req_id, p_user_id => p_user_id); exception when others then sys_raise_app_error_pkg.raise_sys_others_error(p_message => dbms_utility.format_error_backtrace || ' ' || sqlerrm, p_created_by => p_user_id, p_package_name => 'con_contract_change_req_pkg', p_procedure_function_name => 'change_req_reject'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end; --合同变更申请取消 procedure change_req_cancel(p_change_req_id in number, p_user_id in number) is begin con_contract_history_pkg.change_req_cancel(p_change_req_id => p_change_req_id, p_user_id => p_user_id); exception when others then sys_raise_app_error_pkg.raise_sys_others_error(p_message => dbms_utility.format_error_backtrace || ' ' || sqlerrm, p_created_by => p_user_id, p_package_name => 'con_contract_change_req_pkg', p_procedure_function_name => 'change_req_cancel'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end; --从正式环境搬过来的insert 过程 procedure submit_con_change_req(p_change_req_id con_contract_change_req.change_req_id%type, p_user_id con_contract_change_req.created_by%type) is v_change_req_rec con_contract_change_req%rowtype; v_contract_rec con_contract%rowtype; v_approval_method hls_document_type.approval_method%type; v_calc_hd_rec hls_fin_calculator_hd%rowtype; e_change_req_status_error exception; e_con_status_error exception; e_calc_error exception; e_simulation_error exception; v_bp_id_tenant number; begin v_change_req_rec := get_con_change_req_rec(p_change_req_id => p_change_req_id, p_user_id => p_user_id); if v_change_req_rec.simulation_flag = 'Y' then raise e_simulation_error; end if; --当是罚息变更申请的时候,没有存contract_id,所以不需要走这一步 if v_change_req_rec.contract_id is not null then v_contract_rec := con_contract_pkg.get_contract_rec(p_contract_id => v_change_req_rec.contract_id, p_user_id => p_user_id); if v_change_req_rec.req_status not in ('NEW', 'REJECT') then raise e_change_req_status_error; end if; --导入的历史数据 不校验 if nvl(v_contract_rec.history_flag, 'N') <> 'Y' then begin select * into v_calc_hd_rec from hls_fin_calculator_hd where calc_session_id = v_contract_rec.calc_session_id; if nvl(v_calc_hd_rec.calc_successful, 'N') = 'N' or v_calc_hd_rec.price_list <> v_contract_rec.price_list then raise e_calc_error; end if; exception when no_data_found then null; end; end if; end if; /*if v_contract_rec.contract_status not in ('NEW', 'PAID','SIGN','PRINTED') then raise e_con_status_error; end if;*/ --如果变更承租人,需要更新合同头 begin select b.bp_id into v_bp_id_tenant from con_contract_bp b where b.contract_id = p_change_req_id and b.enabled_flag = 'Y' and b.bp_category = 'TENANT' and rownum = 1; update con_contract a set a.bp_id_tenant = v_bp_id_tenant where a.contract_id = p_change_req_id; exception when no_data_found then null; end; select approval_method into v_approval_method from hls_document_type where document_category = v_change_req_rec.document_category and document_type = v_change_req_rec.document_type; if v_approval_method = 'WORK_FLOW' then con_contract_chg_req_wfl_pkg.workflow_start(p_change_req_id => p_change_req_id, p_user_id => p_user_id); else update con_contract_change_req t set t.req_status = 'APPROVING', t.submit_date = sysdate, last_updated_by = p_user_id, last_update_date = sysdate where t.change_req_id = p_change_req_id; end if; exception when e_change_req_status_error then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CON_CONTRACT_CHANGE_REQ_PKG.CHANGE_REQ_STATUS_ERROR', p_created_by => p_user_id, p_package_name => 'con_contract_change_req_pkg', p_procedure_function_name => 'submit_con_change_req'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); when e_con_status_error then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CON_CONTRACT_CHANGE_REQ_PKG.CON_STATUS_ERROR', p_created_by => p_user_id, p_package_name => 'con_contract_change_req_pkg', p_procedure_function_name => 'submit_con_change_req'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); when e_calc_error then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CON_CONTRACT_CHANGE_REQ_PKG.CALCULATOR_STATUS_ERROR', p_created_by => p_user_id, p_package_name => 'con_contract_change_req_pkg', p_procedure_function_name => 'submit_con_change_req'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); when e_simulation_error then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CON_CONTRACT_CHANGE_REQ_PKG.SIMULATION_FLAG_ERROR', p_created_by => p_user_id, p_package_name => 'con_contract_change_req_pkg', p_procedure_function_name => 'submit_con_change_req'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end; function get_con_contract_change_req_s return number is v_con_contract_change_req_id number; begin select con_contract_change_req_s.nextval into v_con_contract_change_req_id from dual; return v_con_contract_change_req_id; end; procedure insert_con_change_req(p_change_req_id out con_contract_change_req.change_req_id%type, p_batch_id con_contract_change_req.batch_id%type, p_change_req_number con_contract_change_req.change_req_number%type default null, p_document_type con_contract_change_req.document_type%type, p_document_category con_contract_change_req.document_category%type, p_business_type con_contract_change_req.business_type%type, p_company_id con_contract_change_req.company_id%type, p_contract_id con_contract_change_req.contract_id%type, p_req_status con_contract_change_req.req_status%type, p_req_date con_contract_change_req.req_date%type, p_change_req_profile con_contract_change_req.change_req_profile%type default null, p_reschedule_start_date con_contract_change_req.reschedule_start_date%type default null, p_reschedule_start_times con_contract_change_req.reschedule_start_times%type default null, p_reschedule_new_date con_contract_change_req.reschedule_new_date%type default null, p_reschedule_total_times con_contract_change_req.reschedule_total_times%type default null, p_reschedule_last_time con_contract_change_req.reschedule_last_time%type default null, p_reschedule_balance con_contract_change_req.reschedule_balance%type default null, p_reschedule_direction con_contract_change_req.reschedule_direction%type default null, p_reschedule_days con_contract_change_req.reschedule_days%type default null, p_reschedule_fee con_contract_change_req.reschedule_fee%type default null, p_calculate_rate con_contract_change_req.calculate_rate%type default null, p_termination_date con_contract_change_req.termination_date%type default null, p_overdue_amount con_contract_change_req.overdue_amount%type default null, p_penalty con_contract_change_req.penalty%type default null, p_rentals_payable con_contract_change_req.rentals_payable%type default null, p_discount_total_amount con_contract_change_req.discount_total_amount%type default null, p_undue_principal con_contract_change_req.undue_principal%type default null, p_et_interest con_contract_change_req.et_interest%type default null, p_mgt_fee_after_discount con_contract_change_req.mgt_fee_after_discount%type default null, p_et_fee con_contract_change_req.et_fee%type default null, p_residual_value con_contract_change_req.residual_value%type default null, p_et_total_amount con_contract_change_req.et_total_amount%type default null, p_last_paydue_date con_contract_change_req.last_paydue_date%type default null, p_last_paydue_times con_contract_change_req.last_paydue_times%type default null, p_discount_days con_contract_change_req.discount_days%type default null, p_submit_date con_contract_change_req.submit_date%type default null, p_approved_date con_contract_change_req.approved_date%type default null, p_description con_contract_change_req.description%type default null, p_calc_session_id con_contract_change_req.calc_session_id%type default null, p_wfl_instance_id con_contract_change_req.wfl_instance_id%type default null, p_user_id con_contract_change_req.created_by%type, p_ref_v01 con_contract_change_req.ref_v01%type default null, p_ref_v02 con_contract_change_req.ref_v02%type default null, p_ref_v03 con_contract_change_req.ref_v03%type default null, p_ref_v04 con_contract_change_req.ref_v04%type default null, p_ref_v05 con_contract_change_req.ref_v05%type default null, p_ref_v06 con_contract_change_req.ref_v06%type default null, p_ref_v07 con_contract_change_req.ref_v07%type default null, p_ref_v08 con_contract_change_req.ref_v08%type default null, p_ref_v09 con_contract_change_req.ref_v09%type default null, p_ref_v10 con_contract_change_req.ref_v10%type default null, p_ref_n01 con_contract_change_req.ref_n01%type default null, p_ref_n02 con_contract_change_req.ref_n02%type default null, p_ref_n03 con_contract_change_req.ref_n03%type default null, p_ref_n04 con_contract_change_req.ref_n04%type default null, p_ref_n05 con_contract_change_req.ref_n05%type default null, p_ref_n06 con_contract_change_req.ref_n06%type default null, p_ref_n07 con_contract_change_req.ref_n07%type default null, p_ref_n08 con_contract_change_req.ref_n08%type default null, p_ref_n09 con_contract_change_req.ref_n09%type default null, p_ref_n10 con_contract_change_req.ref_n10%type default null, p_ref_d01 con_contract_change_req.ref_d01%type default null, p_ref_d02 con_contract_change_req.ref_d02%type default null, p_ref_d03 con_contract_change_req.ref_d03%type default null, p_ref_d04 con_contract_change_req.ref_d04%type default null, p_ref_d05 con_contract_change_req.ref_d05%type default null, p_ref_d06 con_contract_change_req.ref_d06%type default null, p_ref_d07 con_contract_change_req.ref_d07%type default null, p_ref_d08 con_contract_change_req.ref_d08%type default null, p_ref_d09 con_contract_change_req.ref_d09%type default null, p_ref_d10 con_contract_change_req.ref_d10%type default null) is v_batch_id number; v_change_req_number con_contract_change_req.change_req_number%type; begin p_change_req_id := get_con_contract_change_req_s; if p_batch_id is null then v_batch_id := get_con_contract_change_req_s; else v_batch_id := p_batch_id; end if; if p_change_req_number is null then v_change_req_number := get_con_change_req_number(p_document_type => p_document_type, p_document_category => p_document_category, p_company_id => p_company_id, p_req_date => p_req_date, p_user_id => p_user_id); else v_change_req_number := p_change_req_number; end if; insert into con_contract_change_req (change_req_id, batch_id, change_req_number, document_type, document_category, business_type, company_id, contract_id, req_status, req_date, change_req_profile, reschedule_start_date, reschedule_start_times, reschedule_new_date, reschedule_total_times, reschedule_last_time, reschedule_balance, reschedule_direction, reschedule_days, reschedule_fee, calculate_rate, termination_date, overdue_amount, penalty, rentals_payable, discount_total_amount, undue_principal, et_interest, mgt_fee_after_discount, et_fee, residual_value, et_total_amount, last_paydue_date, last_paydue_times, discount_days, submit_date, approved_date, description, calc_session_id, wfl_instance_id, created_by, creation_date, last_updated_by, last_update_date, ref_v01, ref_v02, ref_v03, ref_v04, ref_v05, ref_v06, ref_v07, ref_v08, ref_v09, ref_v10, ref_n01, ref_n02, ref_n03, ref_n04, ref_n05, ref_n06, ref_n07, ref_n08, ref_n09, ref_n10, ref_d01, ref_d02, ref_d03, ref_d04, ref_d05, ref_d06, ref_d07, ref_d08, ref_d09, ref_d10) values (p_change_req_id, v_batch_id, v_change_req_number, p_document_type, p_document_category, p_business_type, p_company_id, p_contract_id, p_req_status, p_req_date, p_change_req_profile, p_reschedule_start_date, p_reschedule_start_times, p_reschedule_new_date, p_reschedule_total_times, p_reschedule_last_time, p_reschedule_balance, p_reschedule_direction, p_reschedule_days, p_reschedule_fee, p_calculate_rate, p_termination_date, p_overdue_amount, p_penalty, p_rentals_payable, p_discount_total_amount, p_undue_principal, p_et_interest, p_mgt_fee_after_discount, p_et_fee, p_residual_value, p_et_total_amount, p_last_paydue_date, p_last_paydue_times, p_discount_days, p_submit_date, p_approved_date, p_description, p_calc_session_id, p_wfl_instance_id, p_user_id, sysdate, p_user_id, sysdate, p_ref_v01, p_ref_v02, p_ref_v03, p_ref_v04, p_ref_v05, p_ref_v06, p_ref_v07, p_ref_v08, p_ref_v09, p_ref_v10, p_ref_n01, p_ref_n02, p_ref_n03, p_ref_n04, p_ref_n05, p_ref_n06, p_ref_n07, p_ref_n08, p_ref_n09, p_ref_n10, p_ref_d01, p_ref_d02, p_ref_d03, p_ref_d04, p_ref_d05, p_ref_d06, p_ref_d07, p_ref_d08, p_ref_d09, p_ref_d10); /*aut_document_authority_pkg.insert_trx_user_authority (p_companY_id => p_company_id, p_owner_user_id => p_owner_user_id, p_trx_category => p_document_category, p_trx_id => p_change_req_id, p_start_date => trunc(sysdate), p_end_date => null, p_user_id => p_user_id); */ end; procedure change_req_in_submit(p_change_req_id in number, p_user_id in 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; v_calc_hd_rec hls_fin_calculator_hd%rowtype; v_success_flag varchar2(1); e_change_req_status_error exception; e_calc_error exception; e_simulation_error exception; e_calc_session_id_notfound exception; e_is_calc_flag_err exception; begin r_change_req_rec := get_con_change_req_rec(p_change_req_id => p_change_req_id, p_user_id => p_user_id); if r_change_req_rec.simulation_flag = 'Y' then raise e_simulation_error; end if; r_change_con_rec := con_contract_pkg.get_contract_rec(p_contract_id => p_change_req_id, p_user_id => p_user_id); --r_change_req_rec.req_status := 'NEW'; if r_change_req_rec.req_status not in ('NEW', 'REJECT') then raise e_change_req_status_error; end if; --当是罚息变更申请的时候,没有存contract_id,所以不需要走这一步 if r_change_req_rec.ccr_price_list is not null then if r_change_req_rec.ccr_calc_session_id is null then raise e_calc_session_id_notfound; else select calc_successful into v_success_flag from hls_fin_calculator_hd where calc_session_id = r_change_req_rec.ccr_calc_session_id; if nvl(v_success_flag, 'N') <> 'Y' then raise e_calc_error; end if; end if; if nvl(r_change_req_rec.is_calc_flag, 'N') = 'N' then raise e_is_calc_flag_err; end if; 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); select approval_method into v_approval_method from hls_document_type where document_category = r_change_req_rec.document_category and document_type = r_change_req_rec.document_type; update con_contract_change_req t set t.req_status = 'APPROVING', t.submit_date = sysdate, last_updated_by = p_user_id, last_update_date = sysdate where t.change_req_id = p_change_req_id; exception when e_change_req_status_error then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CON_CONTRACT_CHANGE_REQ_PKG.CHANGE_REQ_STATUS_ERROR', p_created_by => p_user_id, p_package_name => 'con_contract_change_req_pkg', p_procedure_function_name => 'change_req_in_submit'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); when e_calc_error then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CON_CONTRACT_CHANGE_REQ_PKG.CALCULATOR_STATUS_ERROR', p_created_by => p_user_id, p_package_name => 'con_contract_change_req_pkg', p_procedure_function_name => 'change_req_in_submit'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); when e_is_calc_flag_err then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CON_CONTRACT_CHANGE_REQ_PKG.IS_CALC_FLAG_ERR', p_created_by => p_user_id, p_package_name => 'con_contract_change_req_pkg', p_procedure_function_name => 'change_req_in_submit'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); when e_calc_session_id_notfound then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CON_CONTRACT_CHANGE_REQ_PKG.CALC_SESSION_ID_NOTFOUND', p_created_by => p_user_id, p_package_name => 'con_contract_change_req_pkg', p_procedure_function_name => 'change_req_in_submit'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); when e_simulation_error then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CON_CONTRACT_CHANGE_REQ_PKG.SIMULATION_FLAG_ERROR', p_created_by => p_user_id, p_package_name => 'con_contract_change_req_pkg', p_procedure_function_name => 'change_req_in_submit'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end change_req_in_submit; --合同变更(内部) procedure change_req_in_confirm(p_change_req_id in number, p_user_id in number) is 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); change_req_in_submit(p_change_req_id => p_change_req_id, p_user_id => p_user_id); change_req_confirm(p_change_req_id => p_change_req_id, p_user_id => p_user_id); end change_req_in_confirm; --合同变更申请同意 add by fengyong for derong procedure change_req_confirm_d(p_change_req_id in number, p_user_id in number) is r_change_req_rec con_contract_change_req%rowtype; r_change_con_rec con_contract%rowtype; v_create_je_flag number; v_start_period number; begin 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); --modify by fengyong 更新审批通过日期 update con_contract_change_req q set q.approved_date = sysdate where change_req_id = p_change_req_id; --modify by fengyong at derong把生成变更凭证放到 “确认数据,覆盖原始合同”之前, --因为如果覆盖数据后,req的数据和normal的数据相同了,无法生成变更利息差额凭证 -- 1) 生成变更凭证 if nvl(r_change_req_rec.create_je_flag, 'N') = 'N' and r_change_req_rec.document_type = 'CONEXTHAG' then v_create_je_flag := gld_je_template_pkg.create_je(p_je_transaction_code => 'CON_CHANGE_REQ_CONFIRM', p_company_id => r_change_req_rec.company_id, p_je_company_id => r_change_req_rec.company_id, p_user_id => p_user_id, /*凭证头id*/ p_journal_header_id => null, /*以下为业务参数*/ p_hd_source_table => 'CON_CONTRACT_CHANGE_REQ', p_hd_source_id => p_change_req_id, p_ln_source_table => 'CON_CONTRACT_CHANGE_REQ', p_ln_source_id => p_change_req_id, p_journal_date => trunc(sysdate), p_transaction_date => trunc(sysdate), p_currency_code => r_change_con_rec.currency, p_exchange_rate_quotation => 'DIRECT QUOTATION', p_hd_exchange_rate_type => 'MANUAL', p_hd_exchange_rate => 1, p_ln_exchange_rate_type => 'MANUAL', p_ln_exchange_rate => 1); --IF v_create_je_flag = 0 THEN update con_contract_change_req set create_je_flag = 'Y' where change_req_id = p_change_req_id; --END IF; end if; -- 2) 确认数据,覆盖原始合同 con_contract_history_pkg.change_req_confirm(p_change_req_id => p_change_req_id, p_user_id => p_user_id); select to_number(to_char(cf.due_date, 'yyyymm')) into v_start_period from con_contract_cashflow cf, con_contract cc where cf.contract_id = p_change_req_id and cc.contract_id = cf.contract_id and cf.cf_item = 1 and cf.cf_direction = 'INFLOW' and cf.times = cc.ccr_start_times; --收益分摊 con_unearned_fin_income_pkg.allocate_unearned_fin_income(p_biz_type => 'LEASE', p_contract_id => r_change_req_rec.contract_id, p_start_internal_period_num => v_start_period, p_user_id => p_user_id); exception when others then sys_raise_app_error_pkg.raise_sys_others_error(p_message => dbms_utility.format_error_backtrace || ' ' || sqlerrm, p_created_by => p_user_id, p_package_name => 'con_contract_change_req_pkg', p_procedure_function_name => 'change_req_cinfirm'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end; --modify by lpc 9874 合同承租人变更计算应缴纳金额 procedure calc_leasse_date(p_contract_id number, p_btb_payment_date date, p_ccr_leasse_rental out number, --应缴纳租金 p_ccr_leasse_principal out number, --应缴纳本金 p_ccr_leasse_interest out number, --应缴纳利息 p_ccr_leasse_penalty out number, p_ccr_outstanding_cld out number) is --应缴纳罚息 v_contract_rec con_contract%rowtype; v_con_contract_change_req con_contract_change_req%rowtype; v_total_penalty_amt number; v_times number; v_received_penalty number; v_count number; v_count1 number; begin select * into v_con_contract_change_req from con_contract_change_req where change_req_id = p_contract_id; --如果开始日期之后有发生核销或者第一期之前的则不能选 select count(1) into v_count from con_contract_cashflow ccc where ccc.contract_id = p_contract_id and ccc.due_date > p_btb_payment_date and ccc.cf_item = 1 and ccc.times = 1 and ccc.cf_direction = 'INFLOW' and ccc.cf_status = 'RELEASE'; if v_con_contract_change_req.document_type <> 'LEASSE_CHAG' then if v_count > 0 then sys_raise_app_error_pkg.raise_sys_others_error(p_message => '变更开始日期要大于第一期租金的应收日,请选择正确的日期!', p_created_by => -1, p_package_name => 'con_contract_change_req_pkg', p_procedure_function_name => 'calc_leasse_date'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end if; end if; select count(1) into v_count1 from con_contract_cashflow ccc where ccc.contract_id = p_contract_id and ccc.due_date > p_btb_payment_date and ccc.cf_item <> 304 and ccc.cf_direction = 'INFLOW' and ccc.cf_status = 'RELEASE' and ccc.write_off_flag <> 'NOT'; if v_count1 > 0 then sys_raise_app_error_pkg.raise_sys_others_error(p_message => '变更开始日期之后的期数存在核销,请选择正确的日期或者进行核销反冲!', p_created_by => -1, p_package_name => 'con_contract_change_req_pkg', p_procedure_function_name => 'calc_leasse_date'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end if; --根据结算日期计算应缴纳金额,结算日期之前未完全核销租金 select nvl(sum(nvl(ccc.due_amount, 0) - nvl(ccc.received_amount, 0)), 0) sum_ccr_leasse_rental, --应缴纳租金 nvl(sum(nvl(ccc.principal, 0) - nvl(ccc.received_principal, 0)), 0) sum_ccr_leasse_principal, --应缴纳本金 nvl(sum(nvl(ccc.interest, 0) - nvl(ccc.received_interest, 0)), 0) sum_ccr_leasse_interest --应缴纳利息 into p_ccr_leasse_rental, p_ccr_leasse_principal, p_ccr_leasse_interest from con_contract_cashflow ccc where ccc.contract_id = p_contract_id and ccc.write_off_flag <> 'FULL' and ccc.cf_item = 1 and ccc.cf_direction = 'INFLOW' and ccc.cf_status = 'RELEASE' and ccc.due_date <= p_btb_payment_date; --计算应缴罚息 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_btb_payment_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) loop --计算penalty calc_penalty(p_cashflow_rec => c_contract_cashflow, p_contract_rec => v_contract_rec, p_calc_date => p_btb_payment_date, p_calc_method => 'DAY_END', p_total_penalty_amt => v_total_penalty_amt); p_ccr_leasse_penalty := nvl(p_ccr_leasse_penalty, 0) + v_total_penalty_amt; end loop; /*--计算已核销的逾期罚息 Select nvl(Sum(nvl(ccc.received_amount, 0)), 0) Into v_received_penalty From con_contract_cashflow ccc Where ccc.due_date < p_btb_payment_date And ccc.cf_item = 9 And ccc.contract_id = p_contract_id And ccc.cf_direction = 'INFLOW' And ccc.cf_status = 'RELEASE'; --去除已核销的罚息 p_ccr_leasse_penalty := p_ccr_leasse_penalty - v_received_penalty;*/ --计算未到期本金 --获取当前变更日期是第几期 select min(ccc.times) into v_times from con_contract_cashflow ccc where ccc.due_date > p_btb_payment_date and ccc.cf_item = 1 and ccc.contract_id = p_contract_id and ccc.cf_direction = 'INFLOW' and ccc.cf_status = 'RELEASE'; --根据开始期数计算未到期本金 select nvl(sum(nvl(ccc.principal, 0) - nvl(ccc.received_principal, 0)), 0) sum_ccr_outstanding_incld into p_ccr_outstanding_cld from con_contract_cashflow ccc where ccc.contract_id = (select ccr.contract_id from con_contract_change_req ccr where ccr.change_req_id = p_contract_id) and ccc.write_off_flag <> 'FULL' and ccc.cf_item = 1 and ccc.cf_direction = 'INFLOW' and ccc.cf_status = 'RELEASE' and ccc.times >= v_times; end; procedure calc_leasse_pay(p_contract_id number, p_ccr_start_times number, p_btb_payment_date date default null, p_interim_rental number default null, p_layout_code varchar2, p_ccr_outstanding_times out number, p_ccr_dept out varchar2, p_ccr_outstanding_incld out varchar2, p_ccr_fee out number) is v_contract_rec con_contract%rowtype; v_his_due_date date; v_count number; begin select t.* into v_contract_rec from con_contract t where t.contract_id = p_contract_id; -- 非月付变更时计算变更费用 p_ccr_outstanding_times := v_contract_rec.lease_times - p_ccr_start_times + 1; --根据开始期数计算债权余额和未到期本金 select nvl(sum(nvl(ccc.due_amount, 0)), 0) sum_ccr_dept into p_ccr_dept from con_contract_cashflow ccc where ccc.contract_id = (select cr.contract_id from con_contract_change_req cr where cr.change_req_id = p_contract_id) and ccc.write_off_flag <> 'FULL' and ccc.cf_item in (1, 8) and ccc.cf_direction = 'INFLOW' and ccc.cf_status = 'RELEASE' and ccc.times >= p_ccr_start_times; --根据开始期数计算未到期本金 select nvl(sum(nvl(ccc.principal, 0) - nvl(ccc.received_principal, 0)), 0) sum_ccr_outstanding_incld into p_ccr_outstanding_incld from con_contract_cashflow ccc where ccc.contract_id = (select cr.contract_id from con_contract_change_req cr where cr.change_req_id = p_contract_id) and ccc.write_off_flag <> 'FULL' and ccc.cf_item = 1 and ccc.cf_direction = 'INFLOW' and ccc.cf_status = 'RELEASE' and ccc.times >= p_ccr_start_times; --还款日变更 if p_layout_code = 'CCR_UNIVERSAL_PAYDATE' then --核销的不能进行更改,要从没核销的期数开始修改 select count(1) into v_count from con_contract_cashflow ccc where ccc.contract_id = p_contract_id and ccc.times >= p_ccr_start_times and ccc.cf_item <> 304 and ccc.cf_direction = 'INFLOW' and ccc.cf_status = 'RELEASE' and ccc.write_off_flag <> 'NOT'; if v_count > 0 then sys_raise_app_error_pkg.raise_sys_others_error(p_message => '延期开始期数之后的期数存在核销,请选择正确的日期或者进行核销反冲!', p_created_by => -1, p_package_name => 'con_contract_change_req_pkg', p_procedure_function_name => 'calc_leasse_pay'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end if; --p_ccr_outstanding_times := v_contract_rec.lease_times - p_ccr_start_times + 1; --变更手续费,录入或修改延期开始期数,还款日,延期利率的时候重算,计算逻辑如果开始期数是第一期,用首期支付日,即round((变更后的还款日期-合同上的first_pay_date)*延期利率* 剩余本金/360,2),如果不是第一期 ,就取round(变更后的还款日期-(原合同的上一期的应付日+1个月),2),*延期利率*剩余本金/360,2) if p_ccr_start_times = 1 then p_ccr_fee := round((p_btb_payment_date - v_contract_rec.first_pay_date) * p_interim_rental * p_ccr_outstanding_incld / 360, 2); else --取原合同现金流延期开始期数的应收日 select min(ccc.due_date) into v_his_due_date from con_contract_cashflow ccc where ccc.contract_id = (select cr.contract_id from con_contract_change_req cr where cr.change_req_id = p_contract_id) and ccc.times = p_ccr_start_times and ccc.cf_direction = 'INFLOW' and ccc.cf_status = 'RELEASE'; if v_his_due_date > p_btb_payment_date then sys_raise_app_error_pkg.raise_sys_others_error(p_message => '变更后首期还款日不能小于原合同现金流延期开始期数的应收日!', p_created_by => -1, p_package_name => 'con_contract_change_req_pkg', p_procedure_function_name => 'calc_leasse_pay'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end if; p_ccr_fee := round((p_btb_payment_date - v_his_due_date) * p_interim_rental * p_ccr_outstanding_incld / 360, 2); end if; end if; end; procedure calc_penalty(p_cashflow_rec con_contract_cashflow%rowtype, p_contract_rec con_contract%rowtype, p_calc_date date, p_calc_method varchar2, 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; 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 := round(c_write_off.write_off_due_amount * v_penalty_days * p_contract_rec.penalty_rate, nvl(p_contract_rec.currency_precision, 2)); end if; 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 := round((p_cashflow_rec.due_amount - nvl(p_cashflow_rec.received_amount, 0)) * (v_penalty_days) * p_contract_rec.penalty_rate, nvl(p_contract_rec.currency_precision, 2)); end if; v_total_penalty_amt := v_total_penalty_amt + v_penalty_amt; end if; p_total_penalty_amt := v_total_penalty_amt; end; --LEASSE_CHAG承租人期数1,LEASEHOLD_CHAG租赁物期数2,CONEXTHAG冬雨季期数3,DEBT_CHAG债务再调整期数4,PART_CHAG部分中途偿还期数5,PAY_CHAG非月转月付期数6 procedure insert_ccr_cashflow(p_contract_id number, p_ccr_document_type varchar2, --变更类型 p_btb_payment_date date default null, p_bp_id number default null, p_lease_item_id number default null, p_pattern varchar2 default null, p_machine_number varchar2 default null, p_interim_times number default null, --延期期数 p_ccr_start_times number default null, --延期开始期数 p_hd_user_col_v04 varchar2 default null, p_btb_finance_amount number default null, --部分中途偿还本金 p_user_id varchar2) is v_con_contract con_contract%rowtype; v_bp_master_rec hls_bp_master%rowtype; v_count number; v_count1 number; v_sql varchar2(32767); v_column_list varchar2(32767); v_from_doc_pk_column varchar2(1000); v_to_doc_pk_column varchar2(1000); --目标头表主键列 v_pk_value number; v_project_number varchar2(100); v_lease_times number; v_times number; v_irr number; v_conexthag_count number; v_contract_id number; v_lease_item_count number; v_ccr_start_date date; --ADDED BY FENG FOR DS 20181109 v_due_date date; v_not_contract_id varchar2(100); v_con_bp_id number; begin select * into v_con_contract from con_contract where contract_id = p_contract_id; --取原合同的总期数 select t.lease_times, t.irr, t.contract_id into v_lease_times, v_irr, v_contract_id from con_contract t where t.contract_id = (select cr.contract_id from con_contract_change_req cr where cr.change_req_id = p_contract_id); --当报价计算时将变更日期存放到合同表中,应李媛要求 if p_ccr_document_type not in ('LEASSE_CHAG', 'LEASEHOLD_CHAG') then update con_contract t set t.hd_user_col_d01 = (select tt.req_date from con_contract_change_req tt where tt.change_req_id = p_contract_id) where t.contract_id = p_contract_id; end if; --承租人变更 if p_ccr_document_type = 'LEASSE_CHAG' then --如果没有变更之后的主承租人记录则删除原有主承租人,插入现有承租人 select * into v_bp_master_rec from hls_bp_master t1 where t1.bp_id = p_bp_id; begin select b.record_id into v_con_bp_id from con_contract_bp b where b.bp_category = 'TENANT' and b.bp_type = 'TENANT' and b.enabled_flag = 'Y' and b.contract_id = p_contract_id; exception when no_data_found then v_con_bp_id := con_contract_bp_s.nextval; end; hls_document_transfer_pkg.doc_to_doc(p_from_doc_table => 'HLS_BP_MASTER', p_from_doc_pk => v_bp_master_rec.bp_id, p_to_doc_table => 'CON_CONTRACT_BP', p_to_doc_pk => v_con_bp_id, p_to_doc_column_1 => 'CONTRACT_ID', p_to_doc_column_1_value => p_CONTRACT_id, p_to_doc_column_2 => 'CONTRACT_SEQ', p_to_doc_column_2_value => '1', p_to_doc_column_3 => 'BP_SEQ', p_to_doc_column_3_value => '1', p_to_doc_column_4 => 'CREAT_BP_FLG', p_to_doc_column_4_value => 'N', p_to_doc_column_5 => 'BP_CATEGORY', p_to_doc_column_5_value => 'TENANT', p_to_doc_column_6 => 'BP_TYPE', p_to_doc_column_6_value => 'TENANT', p_user_id => p_user_id); /* DELETE FROM con_contract_bp t WHERE t.contract_id = p_contract_id AND t.bp_category = 'TENANT'; --获取from_doc_table to_doc_table主键字段 v_from_doc_pk_column := contract_change_req_pkg.get_pk_column(p_table_name => 'HLS_BP_MASTER'); v_to_doc_pk_column := contract_change_req_pkg.get_pk_column(p_table_name => 'CON_CONTRACT_BP'); --获取表list v_pk_value := con_contract_bp_s.nextval; v_column_list := contract_change_req_pkg.get_column_list(p_from_doc_table => 'HLS_BP_MASTER', p_to_doc_table => 'CON_CONTRACT_BP'); v_sql := 'insert into CON_CONTRACT_BP (' || v_to_doc_pk_column || ',' || v_column_list || ',contract_id,bp_id,bp_code ,created_by,creation_date,last_updated_by,last_update_date' || ')' || ' ( select ' || v_pk_value || ',' || v_column_list || ', ' || p_contract_id || ',' || p_bp_id || ',''' || v_bp_master_rec.bp_code || ''',' || p_user_id || ',sysdate' || ',' || p_user_id || ',sysdate ' || ' from HLS_BP_MASTER where ' || v_from_doc_pk_column || ' = ' || p_bp_id || ')'; \*INSERT INTO ccx_temp_logs (log_id, msg, creation_date, created_by, last_update_date, last_updated_by) VALUES (ccx_temp_logs_s.nextval, v_sql, SYSDATE, 1, SYSDATE, 1); COMMIT;*\ EXECUTE IMMEDIATE v_sql;*/ select p.project_number into v_project_number from prj_project p where p.project_id = (select t.project_id from con_contract t where t.contract_id = p_contract_id); --更新合同名称 update con_contract t set t.contract_name = '斗山租赁-' || v_bp_master_rec.bp_name || '-' || v_project_number where t.contract_id = p_contract_id; --租赁物变更 elsif p_ccr_document_type = 'LEASEHOLD_CHAG' then select count(1) into v_lease_item_count from con_contract_lease_item ci where ci.contract_id not in ((select cr.change_req_id from con_contract_change_req cr where cr.contract_id = v_contract_id union all select v_contract_id from dual)) and (ci.machine_number || '-' || ci.pattern) = (p_machine_number || '-' || p_pattern) and ci.equipment_type = 'MAIN' and exists (select 1 from con_contract c where c.contract_id = ci.contract_id and c.data_class = 'NORMAL' and c.contract_status <> ('CANCEL')); if v_lease_item_count > 0 then sys_raise_app_error_pkg.raise_sys_others_error(p_message => '租赁物变更的机型机号不能与系统中其他合同的机型机号相同!', p_created_by => p_user_id, p_package_name => 'con_contract_change_req_pkg', p_procedure_function_name => 'insert_ccr_cashflow'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); else --更新con_contract_lease_item表租赁物id,型号,机号 update con_contract_lease_item t set t.lease_item_id = p_lease_item_id, t.pattern = p_pattern, t.machine_number = p_machine_number where t.contract_id = p_contract_id and t.equipment_type = 'MAIN'; end if; --冬雨季变更 elsif p_ccr_document_type = 'CONEXTHAG' then --变更开始期数不能填租金计划的前3期和后3期 if p_ccr_start_times in (1, 2, 3, v_con_contract.lease_times - 2, v_con_contract.lease_times - 1, v_con_contract.lease_times) then sys_raise_app_error_pkg.raise_sys_others_error(p_message => '变更开始期数不能填租金计划的前3期和后3期!', p_created_by => p_user_id, p_package_name => 'con_contract_change_req_pkg', p_procedure_function_name => 'insert_ccr_cashflow'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end if; --延期期数不能超过3期 if p_interim_times > 3 then sys_raise_app_error_pkg.raise_sys_others_error(p_message => '延期期数不能超过3期!', p_created_by => p_user_id, p_package_name => 'con_contract_change_req_pkg', p_procedure_function_name => 'insert_ccr_cashflow'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end if; --如果起始期数之后有发生核销的则不能选 select count(1) into v_count1 from con_contract_cashflow ccc where ccc.contract_id = p_contract_id and ccc.times > p_ccr_start_times and ccc.cf_item <> 304 and ccc.cf_direction = 'INFLOW' and ccc.cf_status = 'RELEASE' and ccc.write_off_flag <> 'NOT'; if v_count1 > 0 then sys_raise_app_error_pkg.raise_sys_others_error(p_message => '变更开始期数之后的期数存在核销,请选择正确的期数!', p_created_by => p_user_id, p_package_name => 'con_contract_change_req_pkg', p_procedure_function_name => 'insert_ccr_cashflow'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end if; --顺延,INTERIM_TIMES延期期数 CCR_START_TIMES变更开始期数 LEASE_TIMES 还款期数 CCR_OUTSTANDING_TIMES剩余期数 =INTERIM_TIMES延期期数+(LEASE_TIMES-CCR_START_TIMES+1) if p_hd_user_col_v04 = 'DELAY' then --更新合同表的剩余期数 update con_contract t set t.ccr_outstanding_times = p_interim_times + (v_lease_times - p_ccr_start_times + 1), t.lease_times = v_lease_times + p_interim_times where t.contract_id = p_contract_id; --平摊,AVERAGE平摊时 原期数不变 ,CCR_OUTSTANDING_TIMES剩余期数=总期数-开始期数+1 elsif p_hd_user_col_v04 = 'AVERAGE' then update con_contract t set t.ccr_outstanding_times = v_lease_times - p_ccr_start_times + 1 where t.contract_id = p_contract_id; end if; --缩期变更 elsif p_ccr_document_type = 'CUT_CHAG' then --如果起始期数之后有发生核销的则不能选 select count(1) into v_count1 from con_contract_cashflow ccc where ccc.contract_id = p_contract_id and ccc.times > p_ccr_start_times and ccc.cf_item <> 304 and ccc.cf_direction = 'INFLOW' and ccc.cf_status = 'RELEASE' and ccc.write_off_flag <> 'NOT'; if v_count1 > 0 then sys_raise_app_error_pkg.raise_sys_others_error(p_message => '变更开始期数之后的期数存在核销,请选择正确的期数!', p_created_by => p_user_id, p_package_name => 'con_contract_change_req_pkg', p_procedure_function_name => 'insert_ccr_cashflow'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end if; --更新合同表的剩余期数 update con_contract t set t.ccr_outstanding_times = v_lease_times - p_ccr_start_times - p_interim_times + 1, t.lease_times = v_lease_times + p_interim_times where t.contract_id = p_contract_id; --月付变更 elsif p_ccr_document_type = 'PAY_CHAG' then /*--原期数不变 ,CCR_OUTSTANDING_TIMES剩余期数=总期数-开始期数+1 UPDATE con_contract t SET t.ccr_outstanding_times = v_lease_times - p_ccr_start_times + 1 WHERE t.contract_id = p_contract_id;*/ --起始期数的应收日要大于当前系统日期 select ccc.due_date into v_due_date from con_contract_cashflow ccc where ccc.contract_id = p_contract_id and ccc.times = p_ccr_start_times and ccc.cf_item = 1 and ccc.cf_direction = 'INFLOW' and ccc.cf_status = 'RELEASE'; if v_due_date <= sysdate then sys_raise_app_error_pkg.raise_sys_others_error(p_message => '起始期数的应收日要大于当前系统日期,请选择正确的期数!', p_created_by => p_user_id, p_package_name => 'con_contract_change_req_pkg', p_procedure_function_name => 'insert_ccr_cashflow'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end if; --如果起始期数之后有发生核销的则不能选 select count(1) into v_count1 from con_contract_cashflow ccc where ccc.contract_id = p_contract_id and ccc.times > p_ccr_start_times and ccc.cf_item <> 304 and ccc.cf_direction = 'INFLOW' and ccc.cf_status = 'RELEASE' and ccc.write_off_flag <> 'NOT'; if v_count1 > 0 then sys_raise_app_error_pkg.raise_sys_others_error(p_message => '变更开始期数之后的期数存在核销,请选择正确的期数!', p_created_by => p_user_id, p_package_name => 'con_contract_change_req_pkg', p_procedure_function_name => 'insert_ccr_cashflow'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end if; --部分中途偿还期数 elsif p_ccr_document_type = 'PART_CHAG' then --部分中途偿还本金金额不超过剩余本金 if p_btb_finance_amount >= v_con_contract.ccr_outstanding_prin_tax_incld then sys_raise_app_error_pkg.raise_sys_others_error(p_message => '部分中途偿还金额不能超过剩余本金!', p_created_by => p_user_id, p_package_name => 'con_contract_change_req_pkg', p_procedure_function_name => 'insert_ccr_cashflow'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end if; --获取当前变更日期是第几期 select min(ccc.times) into v_times from con_contract_cashflow ccc where ccc.due_date > p_btb_payment_date and ccc.cf_item = 1 and ccc.contract_id = v_contract_id --用原来的合同判断 modify by chenlingfeng and ccc.cf_direction = 'INFLOW' and ccc.cf_status = 'RELEASE'; --原期数加1 ,CCR_OUTSTANDING_TIMES剩余期数=总期数-开始期数+2 update con_contract t set t.ccr_outstanding_times = v_lease_times - v_times + 2, t.ccr_start_times = v_times, t.lease_times = v_lease_times + 1 where t.contract_id = p_contract_id; --债务再调整期数 elsif p_ccr_document_type = 'DEBT_CHAG' then --获取冬雨季延期变更通过的总次数 select count(1) into v_conexthag_count from con_contract_change_req cr where cr.contract_id = p_contract_id and cr.document_type = 'CONEXTHAG' and cr.req_status = 'APPROVED'; --历史冬雨季延期申请次数等于1的话,延期期数不能大于4 if v_conexthag_count = 1 and p_interim_times > 4 then sys_raise_app_error_pkg.raise_sys_others_error(p_message => '历史冬雨季延期申请次数等于1,延期期数不能大于4!', p_created_by => p_user_id, p_package_name => 'CON_CHANGE_REQ_CALC_ITFC_PKG', p_procedure_function_name => 'create_change_req'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end if; --历史冬雨季延期申请次数等于0的话,延期期数不能大于6 if v_conexthag_count = 0 and p_interim_times > 6 then sys_raise_app_error_pkg.raise_sys_others_error(p_message => '历史冬雨季延期申请次数等于0,延期期数不能大于6!', p_created_by => p_user_id, p_package_name => 'CON_CHANGE_REQ_CALC_ITFC_PKG', p_procedure_function_name => 'create_change_req'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end if; --获取当前变更日期是第几期 select min(ccc.times) into v_times from con_contract_cashflow ccc where ccc.due_date > p_btb_payment_date and ccc.cf_item = 1 and ccc.contract_id = v_contract_id --用原来的合同判断 modify by chenlingfeng and ccc.cf_direction = 'INFLOW' and ccc.cf_status = 'RELEASE'; --ADDED BY FENG FOR DS 20181109 --获取变更开始日期 select due_date into v_ccr_start_date from con_contract_cashflow ccc where ccc.contract_id = v_contract_id --用原来的合同判断 modify by chenlingfeng and ccc.cf_item = 1 and ccc.times = v_times - 1; --延期开始日期小于原日程第6期的日期,则不能申请 if v_times < 7 then sys_raise_app_error_pkg.raise_sys_others_error(p_message => '延期开始日期小于原日程第6期的日期,不能发起申请!', p_created_by => p_user_id, p_package_name => 'CON_CHANGE_REQ_CALC_ITFC_PKG', p_procedure_function_name => 'create_change_req'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end if; --原期数加1 ,CCR_OUTSTANDING_TIMES剩余期数=总期数-开始期数+1 update con_contract t set t.ccr_outstanding_times = p_interim_times + (v_lease_times - v_times + 1), t.ccr_start_times = v_times, t.ccr_start_date = v_ccr_start_date, --ADDED BY FENG FOR DS 20181109 t.lease_times = v_lease_times + p_interim_times, t.int_rate_display = v_irr --将原合同的irr更新到变更的利率 where t.contract_id = p_contract_id; end if; end; --变更插入手续费 procedure insert_ccr_fee(p_contract_id number, p_user_id number, p_document_type varchar2 default null, p_cf_item varchar2 default null, p_cf_type varchar2 default null, p_due_date date default null, p_ccr_fee number) is v_contract_cashflow_rec con_contract_cashflow%rowtype; v_con_contract con_contract%rowtype; v_con_contract_cashflow_id number; v_count number; v_times number; v_time number; v_tax_rate number; begin select * into v_con_contract from con_contract where contract_id = p_contract_id; --当手续费为空或0时不插 if p_ccr_fee = 0 or p_ccr_fee is null then null; else begin --获取当前变更日期是第几期 select nvl(min(ccc.times), v_con_contract.lease_times) into v_times from con_contract_cashflow ccc where ccc.due_date > p_due_date and ccc.cf_item = 1 and ccc.contract_id = p_contract_id and ccc.cf_direction = 'INFLOW' and ccc.cf_status = 'RELEASE'; --获取当前期数变更手续费次数 select count(1) into v_count from con_contract_cashflow where contract_id = p_contract_id and (times between v_times and v_times + 1) and cf_item = '304'; if p_document_type = 'DEBT_CHAG' then --获取期数 select decode(p_cf_item, '10', v_times, to_number(v_times || '.' || (v_count + 1))) into v_time from dual; else select to_number(v_times || '.' || (v_count + 1)) into v_time from dual; end if; --获取税率 select decode(p_cf_item, '304', con_contract_pkg.get_tax_rate(p_contract_id => p_contract_id, p_cf_item => p_cf_item), '10', con_contract_pkg.get_tax_rate(p_contract_id => p_contract_id, p_cf_item => p_cf_item)) into v_tax_rate from dual; select con_contract_cashflow_s.nextval into v_con_contract_cashflow_id from dual; v_contract_cashflow_rec.cashflow_id := v_con_contract_cashflow_id; v_contract_cashflow_rec.contract_id := p_contract_id; --当前合同ID v_contract_cashflow_rec.cf_item := p_cf_item; --计算器行表的列,所配置的现金流项目 v_contract_cashflow_rec.cf_type := p_cf_type; -- 根据现金流项目取得现金流类型 v_contract_cashflow_rec.cf_direction := 'INFLOW'; v_contract_cashflow_rec.cf_status := 'RELEASE'; v_contract_cashflow_rec.times := v_time; --期数 v_contract_cashflow_rec.calc_date := to_date(to_char(p_due_date, 'yyyy-mm-dd'), 'yyyy-mm-dd'); --计算日 --modify by liuhaojie 原为p_due_date v_contract_cashflow_rec.due_date := to_date(to_char(p_due_date, 'yyyy-mm-dd'), 'yyyy-mm-dd'); --到期日 v_contract_cashflow_rec.fin_income_date := to_date(to_char(p_due_date, 'yyyy-mm-dd'), 'yyyy-mm-dd'); --收入计算日 v_contract_cashflow_rec.due_amount := p_ccr_fee; --租金 v_contract_cashflow_rec.principal := null; --本金 v_contract_cashflow_rec.interest := null; --利息 v_contract_cashflow_rec.overdue_status := 'N'; v_contract_cashflow_rec.write_off_flag := 'NOT'; v_contract_cashflow_rec.penalty_process_status := 'NORMAL'; v_contract_cashflow_rec.billing_status := 'NOT'; v_contract_cashflow_rec.vat_due_amount := round(p_ccr_fee / (1 + v_tax_rate) * v_tax_rate, 2); v_contract_cashflow_rec.net_due_amount := p_ccr_fee - v_contract_cashflow_rec.vat_due_amount; v_contract_cashflow_rec.created_by := p_user_id; --当前用户 v_contract_cashflow_rec.creation_date := sysdate; --系统时间 v_contract_cashflow_rec.last_updated_by := p_user_id; --当前用户 v_contract_cashflow_rec.last_update_date := sysdate; --系统时间 con_contract_pkg.insert_contract_cashflow(p_contract_cashflow_rec => v_contract_cashflow_rec); end; end if; end; --还款日变更更新还款日 procedure update_change_req(p_contract_id number, p_ccr_start_times number, p_btb_payment_date date) is v_contract_rec con_contract%rowtype; v_count number; begin select t.* into v_contract_rec from con_contract t where t.contract_id = p_contract_id; --2.录入开始变更的期数,从这一期开始变,将支付日变成变更后还款日,之后每一期都顺延一个月,add_months(变更后还款日,12/H6),5.刷新现金流上日期,从变更开始期数修改 +,开始期数的日期为变更后开始的还款日,然后之后每期顺延,过个现金流,例如 租金1 ,客户手续费3 延期利息102,103,104 罚息9 相同期数的日期刷成一样的 for c_rec in p_ccr_start_times .. v_contract_rec.lease_times + 1 loop v_count := c_rec - p_ccr_start_times; update con_contract_cashflow ccc set ccc.due_date = add_months(p_btb_payment_date, v_count), ccc.calc_date = add_months(p_btb_payment_date, v_count), ccc.fin_income_date = add_months(p_btb_payment_date, v_count) where ccc.contract_id = p_contract_id and ccc.times = c_rec and ccc.cf_item <> '9' and ccc.cf_direction = 'INFLOW' and ccc.cf_status = 'RELEASE'; end loop; exception when others then sys_raise_app_error_pkg.raise_sys_others_error(p_message => dbms_utility.format_error_backtrace || ' ' || sqlerrm, p_created_by => -1, p_package_name => 'con_contract_change_req_pkg', p_procedure_function_name => 'update_change_req'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end; end con_contract_change_req_pkg; /