create or replace package cus_con_change_req_pkg is -- Author : Spencer 3893 -- Created : 9/23/2019 4:25:37 PM -- Purpose : 合同變更申請 --创建时维护默认的用印信息 procedure insert_hls_document_use_seal(p_change_req_id number, p_document_table varchar2, p_instruments_types varchar2, p_common_seal varchar2, p_corporate_visa_seal varchar2, p_legal_seal varchar2, p_con_special_seal varchar2, p_financial_seal varchar2, p_supervisio_seal varchar2, p_seal_number varchar2, p_user_id number); procedure create_change_req(p_contract_id number, p_req_date date, p_req_type varchar2, p_description varchar2, p_user_id number, p_simulation_flag varchar2 default 'N', p_ref_v01 number, p_change_req_id out number); --報價計算 procedure calc_change_req(p_change_req_id in number, p_ccr_start_times in number, p_ccr_outstanding_times in number, p_calc_session_id in out number, p_calc_successful out varchar2, p_warning_message out varchar2, p_user_id in number); --承租人變更日計算應繳金額相關信息 procedure calc_tenant_change_req(p_change_req_id in number, p_ccr_lease_date in date, p_ccr_lease_rental out number, --应交租金 p_ccr_lease_principal out number, --应交本金 p_ccr_lease_interest out number, --应交利息 p_ccr_lease_penalty out number, --应交罚息 p_user_id in number); --提交工作流 procedure submit_change_req(p_change_req_id in number, p_layout_code in varchar2, p_user_id in number); end cus_con_change_req_pkg; / create or replace package body cus_con_change_req_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_change_req_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_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; --创建时维护默认的用印信息 procedure insert_hls_document_use_seal(p_change_req_id number, p_document_table varchar2, p_instruments_types varchar2, p_common_seal varchar2, p_corporate_visa_seal varchar2, p_legal_seal varchar2, p_con_special_seal varchar2, p_financial_seal varchar2, p_supervisio_seal varchar2, p_seal_number varchar2, p_user_id number) is begin insert into hls_document_use_seal (document_use_seal_id, document_table, document_id, instruments_types, common_seal, corporate_visa_seal, legal_seal, con_special_seal, financial_seal, supervisio_seal, seal_number, created_by, creation_date, last_updated_by, last_update_date) values (hls_document_use_seal_s.nextval, p_document_table, p_change_req_id, p_instruments_types, p_common_seal, p_corporate_visa_seal, p_legal_seal, p_con_special_seal, p_financial_seal, p_supervisio_seal, p_seal_number, p_user_id, sysdate, p_user_id, sysdate); end insert_hls_document_use_seal; procedure create_change_req(p_contract_id number, p_req_date date, p_req_type varchar2, p_description varchar2, p_user_id number, p_simulation_flag varchar2 default 'N', p_ref_v01 number, 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 in ('CUT_CHAG', 'LEASE_CHAG', 'ACC_CHAG') then sys_raise_app_error_pkg.raise_sys_others_error(p_message => '只有起租状态的合同才能进行合同变更申请!', p_created_by => p_user_id, p_package_name => 'cus_con_change_req_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; con_contract_history_pkg.create_change_req(p_contract_id => p_contract_id, p_change_req_id => v_change_req_id, p_req_date => p_req_date, p_ref_v01 => p_ref_v01, p_description => p_description, 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_cashflow_change_flag => 'Y', p_user_id => p_user_id); if p_req_type = 'LEASE_CHAG' then 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_TW_NUMBER', p_user_id => p_user_id); end if; p_change_req_id := v_change_req_id; end create_change_req; --報價計算 procedure calc_change_req(p_change_req_id in number, p_ccr_start_times in number, p_ccr_outstanding_times in number, p_calc_session_id in out number, p_calc_successful out varchar2, p_warning_message out varchar2, p_user_id in number) is r_change_req con_contract_change_req%rowtype; r_contract_rec con_contract%rowtype; v_ccr_outstanding_incld number; v_exists number; v_count number; begin r_change_req := get_con_change_req_rec(p_change_req_id, p_user_id); r_contract_rec := get_contract_rec(p_change_req_id, p_user_id); --根据开始期数计算未到期本金 select nvl(sum(nvl(ccc.principal, 0) - nvl(ccc.received_principal, 0)), 0) sum_ccr_outstanding_incld into v_ccr_outstanding_incld from con_contract_cashflow ccc where ccc.contract_id = r_change_req.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; --核销的不能进行更改,要从没核销的期数开始修改 select count(1) into v_count from con_contract_cashflow ccc where ccc.contract_id = r_change_req.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 => 'cus_con_change_req_pkg', p_procedure_function_name => 'calc_change_req'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end if; if r_change_req.ccr_start_times is not null then begin select 1 into v_exists from dual where exists (select 1 from con_contract_cashflow a where contract_id = p_change_req_id and cf_type = 1 and times >= r_change_req.ccr_start_times and nvl(a.received_amount, 0) <> 0); sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CON_CHANGE_REQ_CALC_ITFC_PKG.CCR_START_TIMES_ERROR', p_created_by => p_user_id, p_package_name => 'cus_con_change_req_pkg', p_procedure_function_name => 'calc_change_req'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); exception when no_data_found then null; end; end if; select hls_fin_calculator_hd_s.nextval into r_contract_rec.calc_session_id from dual; hls_fin_calculator_ccr_pkg.create_calculate(p_change_req_id => p_change_req_id, p_calc_session_id => r_contract_rec.calc_session_id, p_recreate_h_formula => 'Y', --modify 2017年12月18日16:40:01 p_recreate_l_std => 'Y', 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 calc_session_id = r_contract_rec.calc_session_id, ccr_calc_session_id = r_contract_rec.calc_session_id, is_calc_flag = 'Y' where change_req_id = p_change_req_id; update con_contract set calc_session_id = r_contract_rec.calc_session_id where contract_id = p_change_req_id; p_calc_session_id := r_contract_rec.calc_session_id; end calc_change_req; --承租人變更日計算應繳金額相關信息 procedure calc_tenant_change_req(p_change_req_id in number, p_ccr_lease_date in date, p_ccr_lease_rental out number, --应交租金 p_ccr_lease_principal out number, --应交本金 p_ccr_lease_interest out number, --应交利息 p_ccr_lease_penalty out number, --应交罚息 p_user_id in number) is v_ccr_lease_rental number; --应交租金 v_ccr_lease_principal number; --应交本金 v_ccr_lease_interest number; --应交利息 v_ccr_lease_penalty number; --应交罚息 begin select nvl(sum(f.due_amount), 0), nvl(sum(f.principal), 0), nvl(sum(f.interest), 0) into v_ccr_lease_rental, v_ccr_lease_principal, v_ccr_lease_interest from con_contract_cashflow f where f.due_date <= p_ccr_lease_date and f.write_off_flag = 'NOT' and f.cf_item = 1 and f.cf_type = 1 and f.cf_direction = 'INFLOW' and f.contract_id = p_change_req_id; select nvl(sum(f.due_amount), 0) into v_ccr_lease_penalty from con_contract_cashflow f where f.due_date <= p_ccr_lease_date and f.write_off_flag = 'NOT' and f.cf_item = 9 and f.cf_type = 9 and f.cf_direction = 'INFLOW' and f.contract_id = p_change_req_id; p_ccr_lease_rental := v_ccr_lease_rental; p_ccr_lease_principal := v_ccr_lease_principal; p_ccr_lease_interest := v_ccr_lease_interest; p_ccr_lease_penalty := v_ccr_lease_penalty; exception when others then null; end calc_tenant_change_req; --提交工作流 procedure submit_change_req(p_change_req_id in number, p_layout_code in varchar2, 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; e_change_req_status_error exception; e_is_calc_flag_err 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; --租金計劃變更需要校驗是否計算報價 if p_layout_code = 'CON_CHANGE_MODIFY' then 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); 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); 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 => 'cus_con_change_req_pkg', p_procedure_function_name => 'submit_change_req'); 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 => 'cus_con_change_req_pkg', p_procedure_function_name => 'submit_change_req'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end submit_change_req; end cus_con_change_req_pkg; /