create or replace package cus_con_contract_repo_pkg is -- Author : Spencer 3893 -- Created : 9/10/2019 2:21:13 PM -- Purpose : 合同回购 function get_contract_repo_number(p_document_type varchar2, p_document_date date, p_company_id number, p_user_id number, p_contract_id number default null) return varchar2; --add by Spencer 3893 20190911 回购申请 procedure create_change_req_repo(p_contract_id in number, p_req_type in varchar2, p_user_id in number, p_simulation_flag in varchar2 default 'N', p_change_req_id out number); --回购现金流计算 procedure calc_repo_cashflows(p_contract_id in number, p_user_id in number); --通过回购解约日获取回购相关信息 procedure calc_repo_date_amount(p_contract_id in number, p_repurchase_date date, p_last_rent_due_date out date, p_sum_received_rent_amount out number, p_sum_unreceived_rent_amount out number, p_sum_overdue_amount out number, p_sum_unreceived_principal out number, p_due_amount out number, p_user_id in number); --提交工作流 procedure submit_change_req_repo(p_change_req_id in number, p_layout_code in varchar2, p_user_id in number); end cus_con_contract_repo_pkg; / create or replace package body cus_con_contract_repo_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_contract_repo_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_contract_repo_number(p_document_type varchar2, p_document_date date, p_company_id number, p_user_id number, p_contract_id number default null) return varchar2 is v_no con_contract.repurchase_contract_number%type; v_agent_bp_code varchar2(30); v_price_ref_v05 varchar2(200); v_product_code varchar2(2000); e_product_code_err exception; e_get_contract_number_err exception; begin begin select hbm.bp_code into v_agent_bp_code from con_contract_bp t, hls_bp_master hbm where t.contract_id = p_contract_id and t.bp_category = 'AGENT' and t.enabled_flag = 'Y' and t.bp_id = hbm.bp_id and rownum = 1; exception when no_data_found then null; end; begin select l.ref_v05 into v_price_ref_v05 from con_contract t, hls_price_list l where t.contract_id = p_contract_id and t.price_list = l.price_list; exception when no_data_found then null; end; v_no := fnd_code_rule_pkg.get_rule_next_auto_num(p_document_category => 'CHANGE_REQUEST', p_document_type => p_document_type, p_company_id => p_company_id, p_operation_unit_id => null, p_operation_date => p_document_date, p_agent_code => v_agent_bp_code, p_price_ref_v05 => v_price_ref_v05, p_product_code => v_product_code, p_created_by => p_user_id); if v_no = fnd_code_rule_pkg.c_error then raise e_get_contract_number_err; end if; return v_no; exception when e_get_contract_number_err then sys_raise_app_error_pkg.raise_sys_others_error(p_message => '获取编码失败,请联系系统管理员!', p_created_by => p_user_id, p_package_name => 'cus_con_contract_repo_pkg', p_procedure_function_name => 'get_contract_repo_number'); 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_contract_repo_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; --add by Spencer 3893 20190911 回购申请 procedure create_change_req_repo(p_contract_id in number, p_req_type in varchar2, p_user_id in number, p_simulation_flag in varchar2 default 'N', 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 = 'REPUR' then sys_raise_app_error_pkg.raise_sys_others_error(p_message => '只有起租状态的合同才能进行回购申请!', p_created_by => p_user_id, p_package_name => 'cus_con_contract_repo_pkg', p_procedure_function_name => 'create_change_req_repo'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end if; con_contract_history_pkg.create_change_req(p_contract_id => p_contract_id, p_change_req_id => v_change_req_id, p_req_date => sysdate, p_ref_v01 => p_user_id, p_description => null, p_change_quotation => null, p_change_lease_item => null, p_change_bp => null, p_req_doc_type => p_req_type, p_simulation_flag => p_simulation_flag, p_repo_change_flag => 'Y', p_user_id => p_user_id); --生成回购编号 v_contract_rec.repurchase_contract_number := get_contract_repo_number(p_document_type => p_req_type, p_document_date => sysdate, p_company_id => v_contract_rec.company_id, p_user_id => p_user_id); --更新回购编号到变更流程中的新合同 update con_contract c set c.repurchase_contract_number = v_contract_rec.repurchase_contract_number, c.last_updated_by = p_user_id, c.last_update_date = sysdate where c.contract_id = v_change_req_id; cus_con_change_req_pkg.insert_hls_document_use_seal(p_change_req_id => v_change_req_id, p_document_table => 'CON_CONTRACT_CHANGE_REQ', p_instruments_types => '解除融资租赁合同协议', p_common_seal => 'Y', p_corporate_visa_seal => 'Y', p_legal_seal => 'N', p_con_special_seal => 'N', p_financial_seal => 'N', p_supervisio_seal => 'N', p_seal_number => 'SEAL_O_F_NUMBER', p_user_id => p_user_id); cus_con_change_req_pkg.insert_hls_document_use_seal(p_change_req_id => v_change_req_id, p_document_table => 'CON_CONTRACT_CHANGE_REQ', p_instruments_types => '《融资租赁合同》之补充协议(回购合同)', p_common_seal => 'Y', p_corporate_visa_seal => 'Y', p_legal_seal => 'N', p_con_special_seal => 'N', p_financial_seal => 'N', p_supervisio_seal => 'N', p_seal_number => 'SEAL_O_F_NUMBER', p_user_id => p_user_id); p_change_req_id := v_change_req_id; end; procedure delete_cashflow(p_contract_id number, p_times number) is begin delete from con_contract_cashflow cf where cf.times > p_times and cf.cf_item in (1, 9) and cf.contract_id = p_contract_id; end; -- 手续费处理 procedure sc_cashflow_hander(p_contract_id number, p_service_charges number, p_paymengt_deadlinedate date, p_times number, p_user_id in number) is v_cashflow_rec con_contract_cashflow%rowtype; v_outstanding_principal number; --剩余本金 v_principal number; --本金 v_interest number; --利息 v_due_date date; begin v_interest := round(p_service_charges * 0.13 / 1.13, 2); v_principal := round(p_service_charges / 1.13, 2); v_outstanding_principal := 0; v_due_date := p_paymengt_deadlinedate; v_cashflow_rec.cashflow_id := con_contract_cashflow_s.nextval; v_cashflow_rec.contract_id := p_contract_id; v_cashflow_rec.cf_item := 304; v_cashflow_rec.cf_type := 3; v_cashflow_rec.cf_direction := 'INFLOW'; v_cashflow_rec.cf_status := 'RELEASE'; v_cashflow_rec.times := p_times; v_cashflow_rec.calc_date := v_due_date; v_cashflow_rec.due_date := v_due_date; v_cashflow_rec.due_amount := p_service_charges; v_cashflow_rec.net_due_amount := round(p_service_charges / 1.13, 2); v_cashflow_rec.vat_due_amount := round(p_service_charges * 0.13 / 1.13, 2); v_cashflow_rec.principal := v_principal; v_cashflow_rec.net_principal := round(v_principal / 1.13, 2); v_cashflow_rec.vat_principal := round(v_principal * 0.13 / 1.13, 2); v_cashflow_rec.interest := v_interest; v_cashflow_rec.net_interest := round(v_interest / 1.13, 2); v_cashflow_rec.vat_interest := round(v_interest * 0.13 / 1.13, 2); v_cashflow_rec.received_amount := 0; v_cashflow_rec.received_principal := 0; v_cashflow_rec.received_interest := 0; v_cashflow_rec.outstanding_prin_tax_incld := v_outstanding_principal; v_cashflow_rec.write_off_flag := 'NOT'; v_cashflow_rec.overdue_status := 'NOT'; v_cashflow_rec.penalty_process_status := 'NORMAL'; v_cashflow_rec.billing_status := 'N'; v_cashflow_rec.generated_source := 'MANUAL'; v_cashflow_rec.created_by := p_user_id; v_cashflow_rec.creation_date := sysdate; v_cashflow_rec.last_updated_by := p_user_id; v_cashflow_rec.last_update_date := sysdate; insert into con_contract_cashflow values v_cashflow_rec; end sc_cashflow_hander; -- Refactored procedure fund_prossession_cost_hander procedure fund_prossession_cost_hander(p_contract_id number, p_fund_possession_cost number, p_paymengt_deadlinedate date, p_times number, p_user_id number) is v_cashflow_rec con_contract_cashflow%rowtype; v_outstanding_principal number; --剩余本金 v_principal number; --本金 v_interest number; --利息 v_due_date date; begin v_interest := round(p_fund_possession_cost * 0.13 / 1.13, 2); v_principal := round(p_fund_possession_cost / 1.13, 2); v_outstanding_principal := 0; v_due_date := p_paymengt_deadlinedate; v_cashflow_rec.cashflow_id := con_contract_cashflow_s.nextval; v_cashflow_rec.contract_id := p_contract_id; v_cashflow_rec.cf_item := 14; v_cashflow_rec.cf_type := 14; v_cashflow_rec.cf_direction := 'INFLOW'; v_cashflow_rec.cf_status := 'RELEASE'; v_cashflow_rec.times := p_times; v_cashflow_rec.calc_date := v_due_date; v_cashflow_rec.due_date := v_due_date; v_cashflow_rec.due_amount := p_fund_possession_cost; v_cashflow_rec.net_due_amount := round(p_fund_possession_cost / 1.13, 2); v_cashflow_rec.vat_due_amount := round(p_fund_possession_cost * 0.13 / 1.13, 2); v_cashflow_rec.principal := v_principal; v_cashflow_rec.net_principal := round(v_principal / 1.13, 2); v_cashflow_rec.vat_principal := round(v_principal * 0.13 / 1.13, 2); v_cashflow_rec.interest := v_interest; v_cashflow_rec.net_interest := round(v_interest / 1.13, 2); v_cashflow_rec.vat_interest := round(v_interest * 0.13 / 1.13, 2); v_cashflow_rec.received_amount := 0; v_cashflow_rec.received_principal := 0; v_cashflow_rec.received_interest := 0; v_cashflow_rec.outstanding_prin_tax_incld := v_outstanding_principal; v_cashflow_rec.write_off_flag := 'NOT'; v_cashflow_rec.overdue_status := 'NOT'; v_cashflow_rec.penalty_process_status := 'NORMAL'; v_cashflow_rec.billing_status := 'N'; v_cashflow_rec.generated_source := 'MANUAL'; v_cashflow_rec.created_by := p_user_id; v_cashflow_rec.creation_date := sysdate; v_cashflow_rec.last_updated_by := p_user_id; v_cashflow_rec.last_update_date := sysdate; insert into con_contract_cashflow values v_cashflow_rec; end fund_prossession_cost_hander; --回购现金流计算 procedure calc_repo_cashflows(p_contract_id in number, p_user_id in number) is v_contract_rec con_contract%rowtype; v_cashflow_rec con_contract_cashflow%rowtype; v_due_amount number; --每期租金 v_outstanding_principal number; --剩余本金 v_principal number; --本金 v_interest number; --利息 v_due_date date; v_calc_due_date date; v_times number; v_calc_days number; e_due_amount_error exception; e_repo_calc_amount_error exception; begin --回购合同 v_contract_rec := get_contract_rec(p_contract_id, p_user_id); select * into v_cashflow_rec from con_contract_cashflow f where f.due_date = v_contract_rec.last_rent_due_date and f.cf_item = 1 and f.cf_type = 1 and f.contract_id = p_contract_id; --计算前删除本次变更起始期及之后期, delete_cashflow(p_contract_id, v_cashflow_rec.times); -- 一次性回购 if v_contract_rec.repurchase_types = 'ONE_TIME_REPURCHASE' then v_times := v_cashflow_rec.times + 1; v_cashflow_rec := null; v_interest := round(v_contract_rec.repurchase_tatal_amount * 0.13 / 1.13, 2); v_principal := round(v_contract_rec.repurchase_tatal_amount / 1.13, 2); v_outstanding_principal := 0; v_due_date := v_contract_rec.paymengt_deadlinedate; v_cashflow_rec.cashflow_id := con_contract_cashflow_s.nextval; v_cashflow_rec.contract_id := v_contract_rec.contract_id; v_cashflow_rec.cf_item := 1; v_cashflow_rec.cf_type := 1; v_cashflow_rec.cf_direction := 'INFLOW'; v_cashflow_rec.cf_status := 'RELEASE'; v_cashflow_rec.times := v_times; v_cashflow_rec.calc_date := v_due_date; v_cashflow_rec.due_date := v_due_date; v_cashflow_rec.due_amount := v_contract_rec.repurchase_tatal_amount; v_cashflow_rec.net_due_amount := round(v_contract_rec.repurchase_tatal_amount / 1.13, 2); v_cashflow_rec.vat_due_amount := round(v_contract_rec.repurchase_tatal_amount * 0.13 / 1.13, 2); v_cashflow_rec.principal := v_principal; v_cashflow_rec.net_principal := round(v_principal / 1.13, 2); v_cashflow_rec.vat_principal := round(v_principal * 0.13 / 1.13, 2); v_cashflow_rec.interest := v_interest; v_cashflow_rec.net_interest := round(v_interest / 1.13, 2); v_cashflow_rec.vat_interest := round(v_interest * 0.13 / 1.13, 2); v_cashflow_rec.received_amount := 0; v_cashflow_rec.received_principal := 0; v_cashflow_rec.received_interest := 0; v_cashflow_rec.outstanding_prin_tax_incld := v_outstanding_principal; v_cashflow_rec.write_off_flag := 'NOT'; v_cashflow_rec.overdue_status := 'NOT'; v_cashflow_rec.penalty_process_status := 'NORMAL'; v_cashflow_rec.billing_status := 'N'; v_cashflow_rec.generated_source := 'MANUAL'; v_cashflow_rec.created_by := p_user_id; v_cashflow_rec.creation_date := sysdate; v_cashflow_rec.last_updated_by := p_user_id; v_cashflow_rec.last_update_date := sysdate; insert into con_contract_cashflow values v_cashflow_rec; --手续费 if v_contract_rec.repurchase_service_charges is not null and v_contract_rec.repurchase_service_charges > 0 then v_cashflow_rec := null; sc_cashflow_hander(p_contract_id => v_contract_rec.contract_id, p_service_charges => v_contract_rec.repurchase_service_charges, p_paymengt_deadlinedate => v_contract_rec.paymengt_deadlinedate, p_times => v_times, p_user_id => p_user_id); end if; --资金占用费 if v_contract_rec.fund_possession_cost is not null and v_contract_rec.fund_possession_cost > 0 then v_cashflow_rec := null; fund_prossession_cost_hander(p_contract_id => v_contract_rec.contract_id, p_fund_possession_cost => v_contract_rec.fund_possession_cost, p_paymengt_deadlinedate => v_contract_rec.paymengt_deadlinedate, p_times => v_times, p_user_id => p_user_id); end if; --分期回购 elsif v_contract_rec.repurchase_types = 'STAGE_REPURCHASE' then v_outstanding_principal := v_contract_rec.repurchase_tatal_amount; --回购价款总额 if v_outstanding_principal > 0 then -- 计算租金 select round(abs(hls_financial_calculate_pkg.pmt(nvl(v_contract_rec.repurchase_interest_rate, v_contract_rec.int_rate_display) / 12, --租赁年利率/支付频率 v_contract_rec.payment_period, --租赁期限 v_outstanding_principal, --剩余本金 0, 0)), 2) into v_due_amount from dual; if nvl(v_due_amount, 0) <= 0 then hls_sys_log_pkg.log('回购合同计算租金异常,计算利率:' || v_contract_rec.repurchase_interest_rate || ' ,支付频率:' || v_contract_rec.payment_frequency || ' ,租赁期限:' || v_contract_rec.payment_period || ' ,回购价款总额:' || v_outstanding_principal); raise e_due_amount_error; end if; v_due_date := v_contract_rec.paymengt_deadlinedate; v_times := v_cashflow_rec.times + 1; for cur in 1 .. v_contract_rec.payment_period loop v_cashflow_rec := null; --最后一期 if cur = v_contract_rec.payment_period then v_interest := v_due_amount - v_outstanding_principal; v_principal := v_outstanding_principal; v_outstanding_principal := 0; else if cur >= 2 then v_calc_due_date := add_months(v_due_date, 1); v_calc_days := v_calc_due_date - v_due_date; v_interest := round(v_outstanding_principal * nvl(v_calc_days, 1) * (nvl(v_contract_rec.repurchase_interest_rate, v_contract_rec.int_rate_display) / 360), 2); else --第一期利息天数 v_interest := round(v_outstanding_principal * 1 * (nvl(v_contract_rec.repurchase_interest_rate, v_contract_rec.int_rate_display) / 360), 2); end if; v_principal := v_due_amount - v_interest; v_outstanding_principal := v_outstanding_principal - v_principal; end if; v_cashflow_rec.cashflow_id := con_contract_cashflow_s.nextval; v_cashflow_rec.contract_id := v_contract_rec.contract_id; v_cashflow_rec.cf_item := 1; v_cashflow_rec.cf_type := 1; v_cashflow_rec.cf_direction := 'INFLOW'; v_cashflow_rec.cf_status := 'RELEASE'; v_cashflow_rec.times := v_times; v_cashflow_rec.calc_date := v_due_date; v_cashflow_rec.due_date := v_due_date; v_cashflow_rec.due_amount := v_due_amount; v_cashflow_rec.net_due_amount := round(v_due_amount / 1.13, 2); v_cashflow_rec.vat_due_amount := round(v_due_amount * 0.13 / 1.13, 2); v_cashflow_rec.principal := v_principal; v_cashflow_rec.net_principal := round(v_principal / 1.13, 2); v_cashflow_rec.vat_principal := round(v_principal * 0.13 / 1.13, 2); v_cashflow_rec.interest := v_interest; v_cashflow_rec.net_interest := round(v_interest / 1.13, 2); v_cashflow_rec.vat_interest := round(v_interest * 0.13 / 1.13, 2); v_cashflow_rec.received_amount := 0; v_cashflow_rec.received_principal := 0; v_cashflow_rec.received_interest := 0; v_cashflow_rec.outstanding_prin_tax_incld := v_outstanding_principal; v_cashflow_rec.write_off_flag := 'NOT'; v_cashflow_rec.overdue_status := 'NOT'; v_cashflow_rec.penalty_process_status := 'NORMAL'; v_cashflow_rec.billing_status := 'N'; v_cashflow_rec.generated_source := 'MANUAL'; v_cashflow_rec.created_by := p_user_id; v_cashflow_rec.creation_date := sysdate; v_cashflow_rec.last_updated_by := p_user_id; v_cashflow_rec.last_update_date := sysdate; insert into con_contract_cashflow values v_cashflow_rec; --最后期不需要校验剩余本金是否小于0 if cur < 6 then v_due_date := add_months(v_due_date, 1); v_times := v_times + 1; if v_outstanding_principal < 0 then raise e_repo_calc_amount_error; end if; end if; end loop; --手续费 if v_contract_rec.repurchase_service_charges is not null and v_contract_rec.repurchase_service_charges > 0 then v_cashflow_rec := null; sc_cashflow_hander(p_contract_id => v_contract_rec.contract_id, p_service_charges => v_contract_rec.repurchase_service_charges, p_paymengt_deadlinedate => v_contract_rec.paymengt_deadlinedate, p_times => v_times, p_user_id => p_user_id); end if; --资金占用费 if v_contract_rec.fund_possession_cost is not null and v_contract_rec.fund_possession_cost > 0 then v_cashflow_rec := null; fund_prossession_cost_hander(p_contract_id => v_contract_rec.contract_id, p_fund_possession_cost => v_contract_rec.fund_possession_cost, p_paymengt_deadlinedate => v_contract_rec.paymengt_deadlinedate, p_times => v_times, p_user_id => p_user_id); end if; elsif v_outstanding_principal = 0 then raise e_repo_calc_amount_error; end if; end if; exception when e_repo_calc_amount_error then sys_raise_app_error_pkg.raise_sys_others_error(p_message => '回购价款总额小于0,请检查!', p_created_by => -1, p_package_name => 'cus_con_contract_repo_pkg', p_procedure_function_name => 'calc_repo_cashflows'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); when e_due_amount_error then sys_raise_app_error_pkg.raise_sys_others_error(p_message => '回购合同计算租金出错,请联系系统管理员!', p_created_by => p_user_id, p_package_name => 'cus_con_contract_repo_pkg', p_procedure_function_name => 'calc_repo_cashflows'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end calc_repo_cashflows; --通过回购解约日获取回购相关信息 procedure calc_repo_date_amount(p_contract_id in number, p_repurchase_date date, p_last_rent_due_date out date, p_sum_received_rent_amount out number, p_sum_unreceived_rent_amount out number, p_sum_overdue_amount out number, p_sum_unreceived_principal out number, p_due_amount out number, p_user_id in number) is v_contract_rec con_contract%rowtype; v_cashflow_rec con_contract_cashflow%rowtype; v_times number; v_last_rent_due_date date; --已回收租金 v_sum_received_rent_amount number; --剩余租金 v_sum_unreceived_rent_amount number; --逾期租金 v_sum_overdue_amount number; --剩余本金 v_sum_unreceived_principal number; -- 逾期罚息 v_due_amount number; v_overdue_max_days number; e_write_off_flagg_err exception; begin select max(a.times) into v_times from con_contract_cashflow a where a.due_date >= p_repurchase_date and a.cf_item in (1, 9) and a.write_off_flag in ('PARTIAL', 'FULL') and a.contract_id = p_contract_id; if v_times >= 1 then raise e_write_off_flagg_err; end if; --回购日上一期due_date select f.due_date, f.outstanding_rental_tax_incld into v_last_rent_due_date, v_sum_unreceived_principal from con_contract_cashflow f where f.times = (select max(f.times) from con_contract_cashflow f where f.due_date < p_repurchase_date and f.cf_item = 1 and f.cf_type = 1 and f.contract_id = p_contract_id) and f.due_date <= p_repurchase_date and f.cf_item = 1 and f.cf_type = 1 and f.contract_id = p_contract_id; p_last_rent_due_date := v_last_rent_due_date; --已回收租金 select sum(nvl(F.RECEIVED_AMOUNT, 0)) into v_sum_received_rent_amount from con_contract_cashflow f where f.write_off_flag != 'NOT' and f.cf_item = 1 and f.cf_type = 1 and f.contract_id = p_contract_id; p_sum_received_rent_amount := v_sum_received_rent_amount; --剩余租金 select sum(f.due_amount) - sum(f.received_amount) into v_sum_unreceived_rent_amount from con_contract_cashflow f where f.cf_item = 1 and f.cf_type = 1 and f.contract_id = p_contract_id; p_sum_unreceived_rent_amount := v_sum_unreceived_rent_amount; --逾期租金 select sum(nvl(f.overdue_amount, 0)) into v_sum_overdue_amount from con_contract_cashflow f where f.write_off_flag != 'FULL' and f.overdue_status = 'Y' and f.cf_item = 1 and f.cf_direction != 'NONCASH' and f.contract_id = p_contract_id; p_sum_overdue_amount := nvl(v_sum_overdue_amount, 0); --剩余本金 p_sum_unreceived_principal := v_sum_unreceived_principal; v_contract_rec := get_contract_rec(p_contract_id, p_user_id); -- 逾期天数 overdue_max_days+现在至回购日天数 v_overdue_max_days := nvl(v_contract_rec.overdue_max_days, 0) + trunc(p_repurchase_date - sysdate); -- due_amount 逾期罚息,逾期罚息=逾期租金* 逾期天数 * 0.0004。回购总额中罚息由系统自动计算,可直接进行手动调整,不需要通过罚息减免流程;逾期罚息,计算到回购解约日为止 v_due_amount := p_sum_overdue_amount * v_overdue_max_days * 0.0004; p_due_amount := v_due_amount; exception when e_write_off_flagg_err then sys_raise_app_error_pkg.raise_sys_others_error(p_message => '第' || v_times || '期已有核销记录,请重新选择期数' || v_times || '之后数据发起回购!', p_created_by => p_user_id, p_package_name => 'cus_con_contract_repo_pkg', p_procedure_function_name => 'calc_repo_date_amount'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); when others then null; end calc_repo_date_amount; --提交工作流 procedure submit_change_req_repo(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; 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; e_attachment_error exception; begin hls_document_compare_pkg.con_contract_compare(p_table_name => 'CON_CONTRACT', p_change_req_id => p_change_req_id, p_from_table_pk => '', p_to_table_pk => p_change_req_id, p_user_id => p_user_id); r_change_req_rec := get_con_change_req_rec(p_change_req_id => p_change_req_id, p_user_id => p_user_id); /*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); 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); 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_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 => 'cus_con_contract_repo_pkg', p_procedure_function_name => 'submit_change_req_repo'); 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 => 'cus_con_contract_repo_pkg', p_procedure_function_name => 'submit_change_req_repo'); 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 => 'cus_con_contract_repo_pkg', p_procedure_function_name => 'submit_change_req_repo'); 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_contract_repo_pkg', p_procedure_function_name => 'submit_change_req_repo'); 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 => 'cus_con_contract_repo_pkg', p_procedure_function_name => 'submit_change_req_repo'); 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 => 'cus_con_contract_repo_pkg', p_procedure_function_name => 'submit_change_req_repo'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end submit_change_req_repo; end cus_con_contract_repo_pkg; /