create or replace package con_contract_pkg is -- Author : ZHANGLEI -- Created : 2013/6/6 10:25:44 -- Purpose : -- Version : 1.74 -- 0426 添加 v_through_flag 字段 v_incept_tolerance_days constant number := 3; function get_contract_rec(p_contract_id number, p_user_id number) return con_contract%rowtype; function get_cashflow_rec(p_cashflow_id number, p_user_id number) return con_contract_cashflow%rowtype; procedure update_contract_cashflow(p_contract_cashflow_rec con_contract_cashflow%rowtype); procedure insert_contract_cashflow(p_contract_cashflow_rec con_contract_cashflow%rowtype); procedure save_contract_cashflow(p_column_cf_item hls_fin_calculator_itfc_pkg.column_cf_item_t, p_fin_calculator_hd_rec hls_fin_calculator_hd%rowtype, p_fin_calculator_ln_rec hls_fin_calculator_ln%rowtype, p_contract_id number, p_user_id number); procedure save_contract_lease_num(p_contract_id number); procedure save_contract_from_project(p_project_id number, p_contract_seq number, p_bp_contract_seq number, p_con_document_type varchar2, p_con_contract_name varchar2, p_billing_method varchar2, p_contract_id out number, p_contract_number out varchar2, p_user_id number); procedure save_contract_from_calculator(p_contract_id in number, p_calc_session_id in number, p_save_contract_hd in varchar2 default 'Y', p_save_contract_ln in varchar2 default 'Y', p_user_id in number); --add by jack wu 2014-10-23 procedure insert_contract_item_detail(p_contract_id number, p_user_id number); procedure recreate_cf_duedate(p_company_id number default null, p_contract_id number default null, p_contract_status varchar2 default null, p_start_date date, p_end_date date, p_user_id number); procedure contract_update_check(p_contract_id number, p_user_id number, p_contract_number out varchar2); procedure contract_print(p_contract_id number, p_content_id number, p_user_id number); procedure contract_sign(p_contract_id number, p_exchange_rate_quotation varchar2, p_exchange_rate_type varchar2, p_exchange_rate number, p_signing_date date, p_user_id number); procedure contract_incept(p_contract_id number, p_exchange_rate_quotation varchar2, p_exchange_rate_type varchar2, p_exchange_rate number, p_inception_of_lease date, p_base_rate_new number default null, p_Refresh_cashflow_only_flag varchar2 default null, p_user_id number); procedure contract_incept_rd_wf(p_contract_id number, p_user_id number); --合同取消 procedure contract_cancel(p_contract_id number, p_user_id number); --更新现金流含税金额 procedure update_cashflow_net_amount(p_contract_id number, p_user_id number); procedure update_contract_file_date(p_file_date date, p_contract_id number, p_user_id number); function get_tax_rate(p_contract_id number, p_cf_item number) return number; --hongquan.dai 20180104 取tax_rate_id function get_tax_rate_id(p_contract_id number, p_cf_item number) return number; function get_tax_type_rate(p_tax_type_id number default null) return number; procedure create_cf_date(p_contract_rec con_contract%rowtype, p_inception_of_lease date, p_inception_50_flag varchar2 default null); procedure save_contract_cf_item(p_contract_id number, p_company_id number, p_user_id number); procedure save_contract_billing_method(p_contract_id number, p_billing_method varchar2, p_user_id number); procedure contract_print(p_contract_id number, p_content_id number, p_file_path varchar2, p_file_name varchar2, p_type varchar2, p_user_id number); function get_contract_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; -- 合同维护判断是否为NP procedure get_bp_class(p_bp_id_tenant number, p_bp_class_flag in out varchar2); /*add by xuls 2016-12-24 更改合同商业伙伴序号*/ procedure update_contract_bp_seq(p_contract_id number, p_bp_category varchar2); procedure income_stat(p_date_from date, p_date_to date, p_user_id number); function get_fin_amount(p_bp_id number) return number; function check_grt_amount(p_user_id number) return varchar2; procedure ACCOUNT_QUERY(p_account_type varchar2, p_date_from date, p_date_to date, p_user_id number); procedure save_cdd_item_doc_ref(p_source_document_table varchar2, p_source_document_id number, p_to_document_table varchar2, p_to_document_id number, p_user_id number); --FUNCTION get_inception_of_lease(p_inception_of_lease DATE) RETURN DATE; --是否逾期 违约金状态 function get_if_overdue(p_cashflow_id number) return varchar2; --获取担保人 function get_guarantor(p_contract_id number) return varchar2; procedure default_idtype_idcode_mobile(p_bp_id_tenant number, p_user_id number, p_idtype out varchar2, p_idtype_n out varchar2, p_idcode out varchar2, p_mobile out varchar2); --得到产品是否自定义 procedure get_if_self_definition(p_product_id number, p_user_id number, p_if_self_definition out varchar2); function get_first_contract_day(p_contract_id number) return varchar2; procedure update_send_info(p_contract_id number, p_cert_recipient01 varchar2, p_cert_tracknum01 varchar2, p_cert_send_date01 date, p_cert_recipient02 varchar2, p_cert_tracknum02 varchar2, p_cert_send_date02 date, p_trans_recipient01 varchar2, p_trans_tracknum01 varchar2, p_trans_send_date01 date, p_trans_recipient02 varchar2, p_trans_tracknum02 varchar2, p_trans_send_date02 date, p_note varchar2, p_user_id number); function get_inception_of_lease(p_date date) return date; function get_inception_of_lease_8(p_date date) return date; procedure delete_con_contract_bp(p_record_id number, p_user_id number); procedure update_contract_info_sign(p_contract_id number); end con_contract_pkg; / create or replace package body con_contract_pkg is g_document_category constant varchar2(100) := 'CONTRACT'; e_get_contract_number_err exception; e_bp_id_tenant_err exception; e_quotation_created_err exception; e_quotation_status_err exception; e_calculator_not_success_err exception; e_calc_method_notfound_err exception; e_lock_table exception; pragma exception_init(e_lock_table, -54); function get_contract_id return number is v_id number; begin select con_contract_s.nextval into v_id from dual; --dbms_output.put_line(v_id); return v_id; end; function get_quotation_id return number is v_id number; begin select prj_quotation_s.nextval into v_id from dual; return v_id; end; 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 => 'con_contract_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; procedure update_contract_bp_seq(p_contract_id number, p_bp_category varchar2) is v_num number := 1; begin for c_bps in (select * from con_contract_bp cb where cb.contract_id = p_contract_id and cb.bp_category = p_bp_category and cb.bp_type not in ('GUTA_SP') --排除配偶担保 order by cb.record_id) loop update con_contract_bp b set b.bp_seq = v_num where b.record_id = c_bps.record_id; v_num := v_num + 1; end loop; end update_contract_bp_seq; function get_cashflow_rec(p_cashflow_id number, p_user_id number) return con_contract_cashflow%rowtype is v_con_cashflow_rec con_contract_cashflow%rowtype; begin select * into v_con_cashflow_rec from con_contract_cashflow t where t.cashflow_id = p_cashflow_id for update nowait; return v_con_cashflow_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 => 'con_contract_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_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.contract_number%type; v_agent_bp_code varchar2(30); v_price_ref_v05 varchar2(200); v_product_code varchar2(2000); product_code_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; begin select hp.product_code into v_product_code from con_contract_lease_item_lv ccl, hls_product_plan_definition hp where ccl.contract_id = p_contract_id and ccl.product_plan_id = hp.product_plan_id; exception when no_data_found then raise product_code_err; when product_code_err then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CON_CONTRACT_PKG.GET_CONTRACT_NUMBER', p_created_by => p_user_id, p_package_name => 'CON_CONTRACT_PKG', p_procedure_function_name => 'get_contract_number'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end; v_no := fnd_code_rule_pkg.get_rule_next_auto_num(p_document_category => 'CONTRACT', 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 => 'CON_CONTRACT_PKG', p_procedure_function_name => 'get_contract_number'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end; procedure insert_contract_cashflow(p_contract_cashflow_rec con_contract_cashflow%rowtype) is begin insert into con_contract_cashflow values p_contract_cashflow_rec; /*exception when dup_val_on_index then haha_test_pkg.log(p_contract_cashflow_rec.times || '-' ||p_contract_cashflow_rec.cf_item); */ end; procedure update_contract_cashflow(p_contract_cashflow_rec con_contract_cashflow%rowtype) is begin update con_contract_cashflow set row = p_contract_cashflow_rec where cashflow_id = p_contract_cashflow_rec.cashflow_id; end; procedure insert_rental_cashflow(p_calculator_ln_rec hls_fin_calculator_ln%rowtype, p_price_list varchar2, p_contract_id number, p_company_id number, p_user_id number) is v_contract_cashflow_rec con_contract_cashflow%rowtype; v_cf_item hls_cashflow_item.cf_item%type; v_cf_type hls_cashflow_item.cf_type%type; v_cf_direction hls_cashflow_item.cf_direction%type; v_cf_status con_contract_cashflow.cf_status%type; v_con_contract_cashflow_id number; begin begin select b.cf_item, b.cf_type, b.cf_direction, nvl(c.default_cf_status, 'RELEASE') into v_cf_item, v_cf_type, v_cf_direction, v_cf_status from hls_fin_calc_config a, hls_cashflow_item b, hls_cashflow_item_company c where a.price_list = p_price_list and a.column_name = 'RENTAL' and a.cf_item = b.cf_item and c.company_id(+) = p_company_id and b.cf_item = c.cf_item(+); exception when no_data_found then return; end; --cf_direction begin select * into v_contract_cashflow_rec from con_contract_cashflow where contract_id = p_contract_id and times = p_calculator_ln_rec.times and cf_item = v_cf_item --and generated_source = 'CALCULATOR' --and calc_line_id = p_calculator_ln_rec.calc_line_id ; v_contract_cashflow_rec.times := p_calculator_ln_rec.times; --期数 v_contract_cashflow_rec.calc_date := to_date(p_calculator_ln_rec.calc_date, 'YYYY-MM-DD'); --计算日 v_contract_cashflow_rec.due_date := to_date(p_calculator_ln_rec.due_date, 'YYYY-MM-DD'); --到期日 v_contract_cashflow_rec.fin_income_date := to_date(p_calculator_ln_rec.fin_income_date, 'YYYY-MM-DD'); --收入计算日 v_contract_cashflow_rec.due_amount := nvl(p_calculator_ln_rec.rental, 0); --租金 v_contract_cashflow_rec.principal := nvl(p_calculator_ln_rec.principal, 0); --本金 v_contract_cashflow_rec.interest := nvl(p_calculator_ln_rec.interest, 0); --利息 v_contract_cashflow_rec.outstanding_rental := p_calculator_ln_rec.outstanding_rental; --剩余租金 v_contract_cashflow_rec.outstanding_principal := p_calculator_ln_rec.outstanding_principal; --剩余本金 v_contract_cashflow_rec.outstanding_interest := p_calculator_ln_rec.outstanding_interest; --剩余利息 v_contract_cashflow_rec.interest_accrual_balance := p_calculator_ln_rec.interest_accrual_balance; --计息余额 v_contract_cashflow_rec.principal_implicit_rate := p_calculator_ln_rec.principal_implicit_rate; --实际利率法本金 v_contract_cashflow_rec.interest_implicit_rate := p_calculator_ln_rec.interest_implicit_rate; --实际利率法利息 v_contract_cashflow_rec.accumulated_unpaid_interest := p_calculator_ln_rec.accumulated_unpaid_interest; --累计未偿还利息 v_contract_cashflow_rec.interest_period_days := p_calculator_ln_rec.interest_period_days; --计息天数(利息期天数) v_contract_cashflow_rec.discounting_days := p_calculator_ln_rec.discounting_days; --贴现天数 v_contract_cashflow_rec.vat_due_amount := nvl(p_calculator_ln_rec.vat_rental, 0); v_contract_cashflow_rec.vat_principal := nvl(p_calculator_ln_rec.vat_principal, 0); v_contract_cashflow_rec.vat_interest := nvl(p_calculator_ln_rec.vat_interest, 0); v_contract_cashflow_rec.vat_principal_implicit := p_calculator_ln_rec.vat_principal_implicit; --实际利率法本金增值税额 v_contract_cashflow_rec.vat_interest_implicit := p_calculator_ln_rec.vat_interest_implicit; --实际利率法利息增值税额 v_contract_cashflow_rec.net_due_amount := nvl(p_calculator_ln_rec.net_rental, 0); v_contract_cashflow_rec.net_principal := nvl(p_calculator_ln_rec.net_principal, 0); v_contract_cashflow_rec.net_interest := nvl(p_calculator_ln_rec.net_interest, 0); v_contract_cashflow_rec.net_principal_implicit := p_calculator_ln_rec.net_principal_implicit; --实际利率法税后本金 v_contract_cashflow_rec.net_interest_implicit := p_calculator_ln_rec.net_interest_implicit; --实际利率法税后利息 v_contract_cashflow_rec.fix_principal_flag := p_calculator_ln_rec.fix_principal_flag; --固定本金标志 v_contract_cashflow_rec.fix_rental_flag := p_calculator_ln_rec.fix_rental_flag; --固定租金标志 v_contract_cashflow_rec.interest_only_flag := p_calculator_ln_rec.interest_only_flag; --仅支付利息 v_contract_cashflow_rec.equal_flag := p_calculator_ln_rec.equal_flag; v_contract_cashflow_rec.manual_flag := p_calculator_ln_rec.manual_flag; v_contract_cashflow_rec.beginning_of_lease_year := p_calculator_ln_rec.beginning_of_lease_year; --每个租赁年度的开始 v_contract_cashflow_rec.salestax := p_calculator_ln_rec.salestax; --营业税 --用户字段日期1 if p_calculator_ln_rec.ln_user_col_d01 is not null then v_contract_cashflow_rec.ln_user_col_d01 := to_date(p_calculator_ln_rec.ln_user_col_d01, 'YYYY-MM-DD'); else v_contract_cashflow_rec.ln_user_col_d01 := null; end if; if p_calculator_ln_rec.ln_user_col_d02 is not null then v_contract_cashflow_rec.ln_user_col_d02 := to_date(p_calculator_ln_rec.ln_user_col_d02, 'YYYY-MM-DD'); else v_contract_cashflow_rec.ln_user_col_d02 := null; end if; if p_calculator_ln_rec.ln_user_col_d03 is not null then v_contract_cashflow_rec.ln_user_col_d03 := to_date(p_calculator_ln_rec.ln_user_col_d03, 'YYYY-MM-DD'); else v_contract_cashflow_rec.ln_user_col_d03 := null; end if; if p_calculator_ln_rec.ln_user_col_d04 is not null then v_contract_cashflow_rec.ln_user_col_d04 := to_date(p_calculator_ln_rec.ln_user_col_d04, 'YYYY-MM-DD'); else v_contract_cashflow_rec.ln_user_col_d04 := null; end if; if p_calculator_ln_rec.ln_user_col_d05 is not null then v_contract_cashflow_rec.ln_user_col_d05 := to_date(p_calculator_ln_rec.ln_user_col_d05, 'YYYY-MM-DD'); else v_contract_cashflow_rec.ln_user_col_d05 := null; end if; v_contract_cashflow_rec.ln_user_col_v01 := p_calculator_ln_rec.ln_user_col_v01; --用户字段字符1 v_contract_cashflow_rec.ln_user_col_v02 := p_calculator_ln_rec.ln_user_col_v02; v_contract_cashflow_rec.ln_user_col_v03 := p_calculator_ln_rec.ln_user_col_v03; v_contract_cashflow_rec.ln_user_col_v04 := p_calculator_ln_rec.ln_user_col_v04; v_contract_cashflow_rec.ln_user_col_v05 := p_calculator_ln_rec.ln_user_col_v05; v_contract_cashflow_rec.ln_user_col_n01 := p_calculator_ln_rec.ln_user_col_n01; --用户字段数字1 v_contract_cashflow_rec.ln_user_col_n02 := p_calculator_ln_rec.ln_user_col_n02; v_contract_cashflow_rec.ln_user_col_n03 := p_calculator_ln_rec.ln_user_col_n03; v_contract_cashflow_rec.ln_user_col_n04 := p_calculator_ln_rec.ln_user_col_n04; v_contract_cashflow_rec.ln_user_col_n05 := p_calculator_ln_rec.ln_user_col_n05; v_contract_cashflow_rec.ln_user_col_n06 := p_calculator_ln_rec.ln_user_col_n06; v_contract_cashflow_rec.ln_user_col_n07 := p_calculator_ln_rec.ln_user_col_n07; v_contract_cashflow_rec.ln_user_col_n08 := p_calculator_ln_rec.ln_user_col_n08; v_contract_cashflow_rec.ln_user_col_n09 := p_calculator_ln_rec.ln_user_col_n09; v_contract_cashflow_rec.ln_user_col_n10 := p_calculator_ln_rec.ln_user_col_n10; v_contract_cashflow_rec.ln_user_col_n11 := p_calculator_ln_rec.ln_user_col_n11; v_contract_cashflow_rec.ln_user_col_n12 := p_calculator_ln_rec.ln_user_col_n12; v_contract_cashflow_rec.ln_user_col_n13 := p_calculator_ln_rec.ln_user_col_n13; v_contract_cashflow_rec.ln_user_col_n14 := p_calculator_ln_rec.ln_user_col_n14; v_contract_cashflow_rec.ln_user_col_n15 := p_calculator_ln_rec.ln_user_col_n15; v_contract_cashflow_rec.ln_user_col_n16 := p_calculator_ln_rec.ln_user_col_n16; v_contract_cashflow_rec.ln_user_col_n17 := p_calculator_ln_rec.ln_user_col_n17; v_contract_cashflow_rec.ln_user_col_n18 := p_calculator_ln_rec.ln_user_col_n18; v_contract_cashflow_rec.ln_user_col_n19 := p_calculator_ln_rec.ln_user_col_n19; v_contract_cashflow_rec.ln_user_col_n20 := p_calculator_ln_rec.ln_user_col_n20; v_contract_cashflow_rec.rental_eq_pymt_raw := p_calculator_ln_rec.rental_eq_pymt_raw; --初始租金(等额) v_contract_cashflow_rec.rental_eq_pymt_adj := p_calculator_ln_rec.rental_eq_pymt_adj; --调整租金(等额) v_contract_cashflow_rec.interest_eq_pymt_raw := p_calculator_ln_rec.interest_eq_pymt_raw; --初始利息(等额) v_contract_cashflow_rec.interest_eq_pymt_adj := p_calculator_ln_rec.interest_eq_pymt_adj; --调整利息(等额) v_contract_cashflow_rec.principal_eq_pymt_raw := p_calculator_ln_rec.principal_eq_pymt_raw; --初始本金(等额) v_contract_cashflow_rec.principal_eq_pymt_adj := p_calculator_ln_rec.principal_eq_pymt_adj; --调整本金(等额) v_contract_cashflow_rec.rental_eq_prin_raw := p_calculator_ln_rec.rental_eq_prin_raw; --初始租金(等本) v_contract_cashflow_rec.rental_eq_prin_adj := p_calculator_ln_rec.rental_eq_prin_adj; --调整租金(等本) v_contract_cashflow_rec.interest_eq_prin_raw := p_calculator_ln_rec.interest_eq_prin_raw; --初始利息(等本) v_contract_cashflow_rec.interest_eq_prin_adj := p_calculator_ln_rec.interest_eq_prin_adj; --调整利息(等本) v_contract_cashflow_rec.principal_eq_prin_raw := p_calculator_ln_rec.principal_eq_prin_raw; --初始本金(等本) v_contract_cashflow_rec.principal_eq_prin_adj := p_calculator_ln_rec.principal_eq_prin_adj; --调整本金(等本) v_contract_cashflow_rec.outstanding_rental_tax_incld := p_calculator_ln_rec.outstanding_rental_tax_incld; v_contract_cashflow_rec.outstanding_prin_tax_incld := p_calculator_ln_rec.outstanding_prin_tax_incld; v_contract_cashflow_rec.outstanding_int_tax_incld := p_calculator_ln_rec.outstanding_int_tax_incld; v_contract_cashflow_rec.interest_accrual_bal_tax_incl := p_calculator_ln_rec.interest_accrual_bal_tax_incl; v_contract_cashflow_rec.accumulated_unpd_int_tax_incl := p_calculator_ln_rec.accumulated_unpd_int_tax_incl; v_contract_cashflow_rec.exchange_rate_type := p_calculator_ln_rec.exchange_rate_type; v_contract_cashflow_rec.exchange_rate_quotation := p_calculator_ln_rec.exchange_rate_quotation; v_contract_cashflow_rec.exchange_rate := p_calculator_ln_rec.exchange_rate; v_contract_cashflow_rec.due_amount_cny := p_calculator_ln_rec.due_amount_cny; v_contract_cashflow_rec.main_business_income := p_calculator_ln_rec.main_business_income; v_contract_cashflow_rec.main_business_cost := p_calculator_ln_rec.main_business_cost; v_contract_cashflow_rec.financing_cost := p_calculator_ln_rec.financing_cost; v_contract_cashflow_rec.last_updated_by := p_user_id; --当前用户 v_contract_cashflow_rec.last_update_date := sysdate; --系统时间 -- liukang v_contract_cashflow_rec.payment_deduction_flag := 'Y'; v_contract_cashflow_rec.payment_deduction_amount := v_contract_cashflow_rec.due_amount; --add by chenlingfeng 2019年4月9日 v_contract_cashflow_rec.tax_type_id := p_calculator_ln_rec.tax_type_id; v_contract_cashflow_rec.tax_type_rate := p_calculator_ln_rec.tax_type_rate; update_contract_cashflow(p_contract_cashflow_rec => v_contract_cashflow_rec); exception when no_data_found then 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 := v_cf_item; --计算器行表的列,所配置的现金流项目 v_contract_cashflow_rec.cf_type := v_cf_type; -- 根据现金流项目取得现金流类型 v_contract_cashflow_rec.cf_direction := v_cf_direction; v_contract_cashflow_rec.cf_status := v_cf_status; v_contract_cashflow_rec.times := p_calculator_ln_rec.times; --期数 v_contract_cashflow_rec.calc_date := to_date(p_calculator_ln_rec.calc_date, 'YYYY-MM-DD'); --计算日 v_contract_cashflow_rec.due_date := to_date(p_calculator_ln_rec.due_date, 'YYYY-MM-DD'); --到期日 v_contract_cashflow_rec.fin_income_date := to_date(p_calculator_ln_rec.fin_income_date, 'YYYY-MM-DD'); --收入计算日 v_contract_cashflow_rec.due_amount := p_calculator_ln_rec.rental; --租金 v_contract_cashflow_rec.principal := p_calculator_ln_rec.principal; --本金 v_contract_cashflow_rec.interest := p_calculator_ln_rec.interest; --利息 v_contract_cashflow_rec.outstanding_rental := p_calculator_ln_rec.outstanding_rental; --剩余租金 v_contract_cashflow_rec.outstanding_principal := p_calculator_ln_rec.outstanding_principal; --剩余本金 v_contract_cashflow_rec.outstanding_interest := p_calculator_ln_rec.outstanding_interest; --剩余利息 v_contract_cashflow_rec.interest_accrual_balance := p_calculator_ln_rec.interest_accrual_balance; --计息余额 v_contract_cashflow_rec.principal_implicit_rate := p_calculator_ln_rec.principal_implicit_rate; --实际利率法本金 v_contract_cashflow_rec.interest_implicit_rate := p_calculator_ln_rec.interest_implicit_rate; --实际利率法利息 v_contract_cashflow_rec.accumulated_unpaid_interest := p_calculator_ln_rec.accumulated_unpaid_interest; --累计未偿还利息 v_contract_cashflow_rec.interest_period_days := p_calculator_ln_rec.interest_period_days; --计息天数(利息期天数) v_contract_cashflow_rec.discounting_days := p_calculator_ln_rec.discounting_days; --贴现天数 v_contract_cashflow_rec.vat_due_amount := p_calculator_ln_rec.vat_rental; v_contract_cashflow_rec.vat_principal := p_calculator_ln_rec.vat_principal; v_contract_cashflow_rec.vat_interest := p_calculator_ln_rec.vat_interest; v_contract_cashflow_rec.vat_principal_implicit := p_calculator_ln_rec.vat_principal_implicit; --实际利率法本金增值税额 v_contract_cashflow_rec.vat_interest_implicit := p_calculator_ln_rec.vat_interest_implicit; --实际利率法利息增值税额 v_contract_cashflow_rec.net_due_amount := p_calculator_ln_rec.net_rental; v_contract_cashflow_rec.net_principal := p_calculator_ln_rec.net_principal; v_contract_cashflow_rec.net_interest := p_calculator_ln_rec.net_interest; v_contract_cashflow_rec.net_principal_implicit := p_calculator_ln_rec.net_principal_implicit; --实际利率法税后本金 v_contract_cashflow_rec.net_interest_implicit := p_calculator_ln_rec.net_interest_implicit; --实际利率法税后利息 v_contract_cashflow_rec.fix_principal_flag := p_calculator_ln_rec.fix_principal_flag; --固定本金标志 v_contract_cashflow_rec.fix_rental_flag := p_calculator_ln_rec.fix_rental_flag; --固定租金标志 v_contract_cashflow_rec.interest_only_flag := p_calculator_ln_rec.interest_only_flag; --仅支付利息 v_contract_cashflow_rec.equal_flag := p_calculator_ln_rec.equal_flag; v_contract_cashflow_rec.manual_flag := p_calculator_ln_rec.manual_flag; v_contract_cashflow_rec.beginning_of_lease_year := p_calculator_ln_rec.beginning_of_lease_year; --每个租赁年度的开始 v_contract_cashflow_rec.salestax := p_calculator_ln_rec.salestax; --营业税 v_contract_cashflow_rec.generated_source := 'CALCULATOR'; --现金流由计算器创建 v_contract_cashflow_rec.calc_line_id := p_calculator_ln_rec.calc_line_id; v_contract_cashflow_rec.overdue_status := 'N'; v_contract_cashflow_rec.overdue_book_date := null; v_contract_cashflow_rec.overdue_amount := null; v_contract_cashflow_rec.overdue_principal := null; v_contract_cashflow_rec.overdue_interest := null; v_contract_cashflow_rec.overdue_remark := null; v_contract_cashflow_rec.received_amount := null; v_contract_cashflow_rec.received_principal := null; v_contract_cashflow_rec.received_interest := null; v_contract_cashflow_rec.write_off_flag := 'NOT'; v_contract_cashflow_rec.last_received_date := null; v_contract_cashflow_rec.full_write_off_date := null; v_contract_cashflow_rec.penalty_process_status := 'NORMAL'; v_contract_cashflow_rec.billing_status := 'NOT'; --用户字段日期1 if p_calculator_ln_rec.ln_user_col_d01 is not null then v_contract_cashflow_rec.ln_user_col_d01 := to_date(p_calculator_ln_rec.ln_user_col_d01, 'YYYY-MM-DD'); else v_contract_cashflow_rec.ln_user_col_d01 := null; end if; if p_calculator_ln_rec.ln_user_col_d02 is not null then v_contract_cashflow_rec.ln_user_col_d02 := to_date(p_calculator_ln_rec.ln_user_col_d02, 'YYYY-MM-DD'); else v_contract_cashflow_rec.ln_user_col_d02 := null; end if; if p_calculator_ln_rec.ln_user_col_d03 is not null then v_contract_cashflow_rec.ln_user_col_d03 := to_date(p_calculator_ln_rec.ln_user_col_d03, 'YYYY-MM-DD'); else v_contract_cashflow_rec.ln_user_col_d03 := null; end if; if p_calculator_ln_rec.ln_user_col_d04 is not null then v_contract_cashflow_rec.ln_user_col_d04 := to_date(p_calculator_ln_rec.ln_user_col_d04, 'YYYY-MM-DD'); else v_contract_cashflow_rec.ln_user_col_d04 := null; end if; if p_calculator_ln_rec.ln_user_col_d05 is not null then v_contract_cashflow_rec.ln_user_col_d05 := to_date(p_calculator_ln_rec.ln_user_col_d05, 'YYYY-MM-DD'); else v_contract_cashflow_rec.ln_user_col_d05 := null; end if; v_contract_cashflow_rec.ln_user_col_v01 := p_calculator_ln_rec.ln_user_col_v01; --用户字段字符1 v_contract_cashflow_rec.ln_user_col_v02 := p_calculator_ln_rec.ln_user_col_v02; v_contract_cashflow_rec.ln_user_col_v03 := p_calculator_ln_rec.ln_user_col_v03; v_contract_cashflow_rec.ln_user_col_v04 := p_calculator_ln_rec.ln_user_col_v04; v_contract_cashflow_rec.ln_user_col_v05 := p_calculator_ln_rec.ln_user_col_v05; v_contract_cashflow_rec.ln_user_col_n01 := p_calculator_ln_rec.ln_user_col_n01; --用户字段数字1 v_contract_cashflow_rec.ln_user_col_n02 := p_calculator_ln_rec.ln_user_col_n02; v_contract_cashflow_rec.ln_user_col_n03 := p_calculator_ln_rec.ln_user_col_n03; v_contract_cashflow_rec.ln_user_col_n04 := p_calculator_ln_rec.ln_user_col_n04; v_contract_cashflow_rec.ln_user_col_n05 := p_calculator_ln_rec.ln_user_col_n05; v_contract_cashflow_rec.ln_user_col_n06 := p_calculator_ln_rec.ln_user_col_n06; v_contract_cashflow_rec.ln_user_col_n07 := p_calculator_ln_rec.ln_user_col_n07; v_contract_cashflow_rec.ln_user_col_n08 := p_calculator_ln_rec.ln_user_col_n08; v_contract_cashflow_rec.ln_user_col_n09 := p_calculator_ln_rec.ln_user_col_n09; v_contract_cashflow_rec.ln_user_col_n10 := p_calculator_ln_rec.ln_user_col_n10; v_contract_cashflow_rec.ln_user_col_n11 := p_calculator_ln_rec.ln_user_col_n11; v_contract_cashflow_rec.ln_user_col_n12 := p_calculator_ln_rec.ln_user_col_n12; v_contract_cashflow_rec.ln_user_col_n13 := p_calculator_ln_rec.ln_user_col_n13; v_contract_cashflow_rec.ln_user_col_n14 := p_calculator_ln_rec.ln_user_col_n14; v_contract_cashflow_rec.ln_user_col_n15 := p_calculator_ln_rec.ln_user_col_n15; v_contract_cashflow_rec.ln_user_col_n16 := p_calculator_ln_rec.ln_user_col_n16; v_contract_cashflow_rec.ln_user_col_n17 := p_calculator_ln_rec.ln_user_col_n17; v_contract_cashflow_rec.ln_user_col_n18 := p_calculator_ln_rec.ln_user_col_n18; v_contract_cashflow_rec.ln_user_col_n19 := p_calculator_ln_rec.ln_user_col_n19; v_contract_cashflow_rec.ln_user_col_n20 := p_calculator_ln_rec.ln_user_col_n20; v_contract_cashflow_rec.rental_eq_pymt_raw := p_calculator_ln_rec.rental_eq_pymt_raw; --初始租金(等额) v_contract_cashflow_rec.rental_eq_pymt_adj := p_calculator_ln_rec.rental_eq_pymt_adj; --调整租金(等额) v_contract_cashflow_rec.interest_eq_pymt_raw := p_calculator_ln_rec.interest_eq_pymt_raw; --初始利息(等额) v_contract_cashflow_rec.interest_eq_pymt_adj := p_calculator_ln_rec.interest_eq_pymt_adj; --调整利息(等额) v_contract_cashflow_rec.principal_eq_pymt_raw := p_calculator_ln_rec.principal_eq_pymt_raw; --初始本金(等额) v_contract_cashflow_rec.principal_eq_pymt_adj := p_calculator_ln_rec.principal_eq_pymt_adj; --调整本金(等额) v_contract_cashflow_rec.rental_eq_prin_raw := p_calculator_ln_rec.rental_eq_prin_raw; --初始租金(等本) v_contract_cashflow_rec.rental_eq_prin_adj := p_calculator_ln_rec.rental_eq_prin_adj; --调整租金(等本) v_contract_cashflow_rec.interest_eq_prin_raw := p_calculator_ln_rec.interest_eq_prin_raw; --初始利息(等本) v_contract_cashflow_rec.interest_eq_prin_adj := p_calculator_ln_rec.interest_eq_prin_adj; --调整利息(等本) v_contract_cashflow_rec.principal_eq_prin_raw := p_calculator_ln_rec.principal_eq_prin_raw; --初始本金(等本) v_contract_cashflow_rec.principal_eq_prin_adj := p_calculator_ln_rec.principal_eq_prin_adj; --调整本金(等本) v_contract_cashflow_rec.outstanding_rental_tax_incld := p_calculator_ln_rec.outstanding_rental_tax_incld; v_contract_cashflow_rec.outstanding_prin_tax_incld := p_calculator_ln_rec.outstanding_prin_tax_incld; v_contract_cashflow_rec.outstanding_int_tax_incld := p_calculator_ln_rec.outstanding_int_tax_incld; v_contract_cashflow_rec.interest_accrual_bal_tax_incl := p_calculator_ln_rec.interest_accrual_bal_tax_incl; v_contract_cashflow_rec.accumulated_unpd_int_tax_incl := p_calculator_ln_rec.accumulated_unpd_int_tax_incl; v_contract_cashflow_rec.exchange_rate_type := p_calculator_ln_rec.exchange_rate_type; v_contract_cashflow_rec.exchange_rate_quotation := p_calculator_ln_rec.exchange_rate_quotation; v_contract_cashflow_rec.exchange_rate := p_calculator_ln_rec.exchange_rate; v_contract_cashflow_rec.due_amount_cny := p_calculator_ln_rec.due_amount_cny; v_contract_cashflow_rec.main_business_income := p_calculator_ln_rec.main_business_income; v_contract_cashflow_rec.main_business_cost := p_calculator_ln_rec.main_business_cost; v_contract_cashflow_rec.financing_cost := p_calculator_ln_rec.financing_cost; 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; --系统时间 -- liukang v_contract_cashflow_rec.payment_deduction_flag := 'Y'; v_contract_cashflow_rec.payment_deduction_amount := v_contract_cashflow_rec.due_amount; --add by chenlingfeng 2019年4月9日 v_contract_cashflow_rec.tax_type_id := p_calculator_ln_rec.tax_type_id; v_contract_cashflow_rec.tax_type_rate := p_calculator_ln_rec.tax_type_rate; insert_contract_cashflow(p_contract_cashflow_rec => v_contract_cashflow_rec); end; end; procedure insert_btb_cashflow(p_calculator_ln_rec hls_fin_calculator_ln%rowtype, p_price_list varchar2, p_contract_id number, p_company_id number, p_user_id number) is v_contract_cashflow_rec con_contract_cashflow%rowtype; v_cf_item hls_cashflow_item.cf_item%type; v_cf_type hls_cashflow_item.cf_type%type; v_cf_direction hls_cashflow_item.cf_direction%type; v_cf_status con_contract_cashflow.cf_status%type; v_con_contract_cashflow_id number; begin begin select b.cf_item, b.cf_type, b.cf_direction, nvl(c.default_cf_status, 'RELEASE') into v_cf_item, v_cf_type, v_cf_direction, v_cf_status from hls_fin_calc_config a, hls_cashflow_item b, hls_cashflow_item_company c where a.price_list = p_price_list and a.column_name = 'BTB_REPAYMENT' and a.cf_item = b.cf_item and c.company_id(+) = p_company_id and b.cf_item = c.cf_item(+); exception when no_data_found then return; end; begin select * into v_contract_cashflow_rec from con_contract_cashflow where contract_id = p_contract_id and times = p_calculator_ln_rec.times and cf_item = v_cf_item --and generated_source = 'CALCULATOR' --and calc_line_id = p_calculator_ln_rec.calc_line_id ; v_contract_cashflow_rec.times := p_calculator_ln_rec.times; --期数 v_contract_cashflow_rec.calc_date := to_date(p_calculator_ln_rec.calc_date, 'YYYY-MM-DD'); --计算日 v_contract_cashflow_rec.due_date := to_date(p_calculator_ln_rec.due_date, 'YYYY-MM-DD'); --到期日 v_contract_cashflow_rec.fin_income_date := to_date(p_calculator_ln_rec.fin_income_date, 'YYYY-MM-DD'); --收入计算日 --btb fin_income_date 待考虑 v_contract_cashflow_rec.due_amount := p_calculator_ln_rec.btb_repayment; -- (背靠背交易) 还款额 v_contract_cashflow_rec.principal := p_calculator_ln_rec.btb_principal; -- (背靠背交易) 本金 v_contract_cashflow_rec.interest := p_calculator_ln_rec.btb_interest; --(背靠背交易)利息 v_contract_cashflow_rec.outstanding_rental := p_calculator_ln_rec.outstanding_rental; --剩余租金 v_contract_cashflow_rec.outstanding_principal := p_calculator_ln_rec.outstanding_principal; --剩余本金 v_contract_cashflow_rec.outstanding_interest := p_calculator_ln_rec.outstanding_interest; --剩余利息 v_contract_cashflow_rec.interest_accrual_balance := p_calculator_ln_rec.interest_accrual_balance; --计息余额 v_contract_cashflow_rec.principal_implicit_rate := null; --实际利率法本金 v_contract_cashflow_rec.interest_implicit_rate := null; --实际利率法利息 v_contract_cashflow_rec.accumulated_unpaid_interest := null; --累计未偿还利息 v_contract_cashflow_rec.interest_period_days := null; --计息天数(利息期天数) v_contract_cashflow_rec.discounting_days := p_calculator_ln_rec.btb_discounting_days; -- (背靠背交易) 贴现天数 v_contract_cashflow_rec.vat_due_amount := p_calculator_ln_rec.btb_vat_repayment; -- (背靠背交易) v_contract_cashflow_rec.vat_principal := p_calculator_ln_rec.btb_vat_principal; v_contract_cashflow_rec.vat_interest := p_calculator_ln_rec.btb_vat_interest; v_contract_cashflow_rec.vat_principal_implicit := null; --实际利率法本金增值税额 v_contract_cashflow_rec.vat_interest_implicit := null; --实际利率法利息增值税额 v_contract_cashflow_rec.net_due_amount := null; v_contract_cashflow_rec.net_principal := p_calculator_ln_rec.btb_principal_after_tax; v_contract_cashflow_rec.net_interest := p_calculator_ln_rec.btb_interest_after_tax; v_contract_cashflow_rec.net_principal_implicit := null; --实际利率法税后本金 v_contract_cashflow_rec.net_interest_implicit := null; --实际利率法税后利息 v_contract_cashflow_rec.fix_principal_flag := p_calculator_ln_rec.fix_principal_flag; --固定本金标志 v_contract_cashflow_rec.fix_rental_flag := p_calculator_ln_rec.fix_rental_flag; --固定租金标志 v_contract_cashflow_rec.interest_only_flag := p_calculator_ln_rec.interest_only_flag; --仅支付利息 v_contract_cashflow_rec.equal_flag := p_calculator_ln_rec.equal_flag; v_contract_cashflow_rec.manual_flag := p_calculator_ln_rec.manual_flag; v_contract_cashflow_rec.beginning_of_lease_year := p_calculator_ln_rec.beginning_of_lease_year; --每个租赁年度的开始 v_contract_cashflow_rec.salestax := p_calculator_ln_rec.salestax; --营业税 --用户字段日期1 if p_calculator_ln_rec.ln_user_col_d01 is not null then v_contract_cashflow_rec.ln_user_col_d01 := to_date(p_calculator_ln_rec.ln_user_col_d01, 'YYYY-MM-DD'); else v_contract_cashflow_rec.ln_user_col_d01 := null; end if; if p_calculator_ln_rec.ln_user_col_d02 is not null then v_contract_cashflow_rec.ln_user_col_d02 := to_date(p_calculator_ln_rec.ln_user_col_d02, 'YYYY-MM-DD'); else v_contract_cashflow_rec.ln_user_col_d02 := null; end if; if p_calculator_ln_rec.ln_user_col_d03 is not null then v_contract_cashflow_rec.ln_user_col_d03 := to_date(p_calculator_ln_rec.ln_user_col_d03, 'YYYY-MM-DD'); else v_contract_cashflow_rec.ln_user_col_d03 := null; end if; if p_calculator_ln_rec.ln_user_col_d04 is not null then v_contract_cashflow_rec.ln_user_col_d04 := to_date(p_calculator_ln_rec.ln_user_col_d04, 'YYYY-MM-DD'); else v_contract_cashflow_rec.ln_user_col_d04 := null; end if; if p_calculator_ln_rec.ln_user_col_d05 is not null then v_contract_cashflow_rec.ln_user_col_d05 := to_date(p_calculator_ln_rec.ln_user_col_d05, 'YYYY-MM-DD'); else v_contract_cashflow_rec.ln_user_col_d05 := null; end if; v_contract_cashflow_rec.ln_user_col_v01 := p_calculator_ln_rec.ln_user_col_v01; --用户字段字符1 v_contract_cashflow_rec.ln_user_col_v02 := p_calculator_ln_rec.ln_user_col_v02; v_contract_cashflow_rec.ln_user_col_v03 := p_calculator_ln_rec.ln_user_col_v03; v_contract_cashflow_rec.ln_user_col_v04 := p_calculator_ln_rec.ln_user_col_v04; v_contract_cashflow_rec.ln_user_col_v05 := p_calculator_ln_rec.ln_user_col_v05; v_contract_cashflow_rec.ln_user_col_n01 := p_calculator_ln_rec.ln_user_col_n01; --用户字段数字1 v_contract_cashflow_rec.ln_user_col_n02 := p_calculator_ln_rec.ln_user_col_n02; v_contract_cashflow_rec.ln_user_col_n03 := p_calculator_ln_rec.ln_user_col_n03; v_contract_cashflow_rec.ln_user_col_n04 := p_calculator_ln_rec.ln_user_col_n04; v_contract_cashflow_rec.ln_user_col_n05 := p_calculator_ln_rec.ln_user_col_n05; v_contract_cashflow_rec.ln_user_col_n06 := p_calculator_ln_rec.ln_user_col_n06; v_contract_cashflow_rec.ln_user_col_n07 := p_calculator_ln_rec.ln_user_col_n07; v_contract_cashflow_rec.ln_user_col_n08 := p_calculator_ln_rec.ln_user_col_n08; v_contract_cashflow_rec.ln_user_col_n09 := p_calculator_ln_rec.ln_user_col_n09; v_contract_cashflow_rec.ln_user_col_n10 := p_calculator_ln_rec.ln_user_col_n10; v_contract_cashflow_rec.ln_user_col_n11 := p_calculator_ln_rec.ln_user_col_n11; v_contract_cashflow_rec.ln_user_col_n12 := p_calculator_ln_rec.ln_user_col_n12; v_contract_cashflow_rec.ln_user_col_n13 := p_calculator_ln_rec.ln_user_col_n13; v_contract_cashflow_rec.ln_user_col_n14 := p_calculator_ln_rec.ln_user_col_n14; v_contract_cashflow_rec.ln_user_col_n15 := p_calculator_ln_rec.ln_user_col_n15; v_contract_cashflow_rec.ln_user_col_n16 := p_calculator_ln_rec.ln_user_col_n16; v_contract_cashflow_rec.ln_user_col_n17 := p_calculator_ln_rec.ln_user_col_n17; v_contract_cashflow_rec.ln_user_col_n18 := p_calculator_ln_rec.ln_user_col_n18; v_contract_cashflow_rec.ln_user_col_n19 := p_calculator_ln_rec.ln_user_col_n19; v_contract_cashflow_rec.ln_user_col_n20 := p_calculator_ln_rec.ln_user_col_n20; v_contract_cashflow_rec.outstanding_rental_tax_incld := p_calculator_ln_rec.outstanding_rental_tax_incld; v_contract_cashflow_rec.outstanding_prin_tax_incld := p_calculator_ln_rec.outstanding_prin_tax_incld; v_contract_cashflow_rec.outstanding_int_tax_incld := p_calculator_ln_rec.outstanding_int_tax_incld; v_contract_cashflow_rec.interest_accrual_bal_tax_incl := p_calculator_ln_rec.interest_accrual_bal_tax_incl; v_contract_cashflow_rec.accumulated_unpd_int_tax_incl := p_calculator_ln_rec.accumulated_unpd_int_tax_incl; v_contract_cashflow_rec.exchange_rate_type := p_calculator_ln_rec.exchange_rate_type; v_contract_cashflow_rec.exchange_rate_quotation := p_calculator_ln_rec.exchange_rate_quotation; v_contract_cashflow_rec.exchange_rate := p_calculator_ln_rec.exchange_rate; v_contract_cashflow_rec.due_amount_cny := p_calculator_ln_rec.due_amount_cny; v_contract_cashflow_rec.main_business_income := p_calculator_ln_rec.main_business_income; v_contract_cashflow_rec.main_business_cost := p_calculator_ln_rec.main_business_cost; v_contract_cashflow_rec.financing_cost := p_calculator_ln_rec.financing_cost; v_contract_cashflow_rec.last_updated_by := p_user_id; --当前用户 v_contract_cashflow_rec.last_update_date := sysdate; --系统时间 --liukang v_contract_cashflow_rec.payment_deduction_flag := 'Y'; v_contract_cashflow_rec.payment_deduction_amount := v_contract_cashflow_rec.due_amount; update_contract_cashflow(p_contract_cashflow_rec => v_contract_cashflow_rec); exception when no_data_found then 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 := v_cf_item; --计算器行表的列,所配置的现金流项目 v_contract_cashflow_rec.cf_type := v_cf_type; -- 根据现金流项目取得现金流类型 v_contract_cashflow_rec.cf_direction := v_cf_direction; v_contract_cashflow_rec.cf_status := v_cf_status; v_contract_cashflow_rec.times := p_calculator_ln_rec.times; --期数 v_contract_cashflow_rec.calc_date := to_date(p_calculator_ln_rec.calc_date, 'YYYY-MM-DD'); --计算日 v_contract_cashflow_rec.due_date := to_date(p_calculator_ln_rec.due_date, 'YYYY-MM-DD'); --到期日 v_contract_cashflow_rec.fin_income_date := to_date(p_calculator_ln_rec.fin_income_date, 'YYYY-MM-DD'); --收入计算日 --btb fin_income_date 待考虑 v_contract_cashflow_rec.due_amount := p_calculator_ln_rec.btb_repayment; -- (背靠背交易) 还款额 v_contract_cashflow_rec.principal := p_calculator_ln_rec.btb_principal; -- (背靠背交易) 本金 v_contract_cashflow_rec.interest := p_calculator_ln_rec.btb_interest; --(背靠背交易)利息 v_contract_cashflow_rec.outstanding_rental := p_calculator_ln_rec.outstanding_rental; --剩余租金 v_contract_cashflow_rec.outstanding_principal := p_calculator_ln_rec.outstanding_principal; --剩余本金 v_contract_cashflow_rec.outstanding_interest := p_calculator_ln_rec.outstanding_interest; --剩余利息 v_contract_cashflow_rec.interest_accrual_balance := p_calculator_ln_rec.interest_accrual_balance; --计息余额 v_contract_cashflow_rec.principal_implicit_rate := null; --实际利率法本金 v_contract_cashflow_rec.interest_implicit_rate := null; --实际利率法利息 v_contract_cashflow_rec.accumulated_unpaid_interest := null; --累计未偿还利息 v_contract_cashflow_rec.interest_period_days := null; --计息天数(利息期天数) v_contract_cashflow_rec.discounting_days := p_calculator_ln_rec.btb_discounting_days; -- (背靠背交易) 贴现天数 v_contract_cashflow_rec.vat_due_amount := p_calculator_ln_rec.btb_vat_repayment; -- (背靠背交易) v_contract_cashflow_rec.vat_principal := p_calculator_ln_rec.btb_vat_principal; v_contract_cashflow_rec.vat_interest := p_calculator_ln_rec.btb_vat_interest; v_contract_cashflow_rec.vat_principal_implicit := null; --实际利率法本金增值税额 v_contract_cashflow_rec.vat_interest_implicit := null; --实际利率法利息增值税额 v_contract_cashflow_rec.net_due_amount := null; v_contract_cashflow_rec.net_principal := p_calculator_ln_rec.btb_principal_after_tax; v_contract_cashflow_rec.net_interest := p_calculator_ln_rec.btb_interest_after_tax; v_contract_cashflow_rec.net_principal_implicit := null; --实际利率法税后本金 v_contract_cashflow_rec.net_interest_implicit := null; --实际利率法税后利息 v_contract_cashflow_rec.fix_principal_flag := p_calculator_ln_rec.fix_principal_flag; --固定本金标志 v_contract_cashflow_rec.fix_rental_flag := p_calculator_ln_rec.fix_rental_flag; --固定租金标志 v_contract_cashflow_rec.interest_only_flag := p_calculator_ln_rec.interest_only_flag; --仅支付利息 v_contract_cashflow_rec.equal_flag := p_calculator_ln_rec.equal_flag; v_contract_cashflow_rec.manual_flag := p_calculator_ln_rec.manual_flag; v_contract_cashflow_rec.beginning_of_lease_year := p_calculator_ln_rec.beginning_of_lease_year; --每个租赁年度的开始 v_contract_cashflow_rec.salestax := p_calculator_ln_rec.salestax; --营业税 v_contract_cashflow_rec.generated_source := 'CALCULATOR'; --现金流由计算器创建 v_contract_cashflow_rec.calc_line_id := p_calculator_ln_rec.calc_line_id; v_contract_cashflow_rec.overdue_status := 'N'; v_contract_cashflow_rec.overdue_book_date := null; v_contract_cashflow_rec.overdue_amount := null; v_contract_cashflow_rec.overdue_principal := null; v_contract_cashflow_rec.overdue_interest := null; v_contract_cashflow_rec.overdue_remark := null; v_contract_cashflow_rec.received_amount := null; v_contract_cashflow_rec.received_principal := null; v_contract_cashflow_rec.received_interest := null; v_contract_cashflow_rec.write_off_flag := 'NOT'; v_contract_cashflow_rec.last_received_date := null; v_contract_cashflow_rec.full_write_off_date := null; v_contract_cashflow_rec.penalty_process_status := 'NORMAL'; v_contract_cashflow_rec.billing_status := 'NOT'; --用户字段日期1 if p_calculator_ln_rec.ln_user_col_d01 is not null then v_contract_cashflow_rec.ln_user_col_d01 := to_date(p_calculator_ln_rec.ln_user_col_d01, 'YYYY-MM-DD'); else v_contract_cashflow_rec.ln_user_col_d01 := null; end if; if p_calculator_ln_rec.ln_user_col_d02 is not null then v_contract_cashflow_rec.ln_user_col_d02 := to_date(p_calculator_ln_rec.ln_user_col_d02, 'YYYY-MM-DD'); else v_contract_cashflow_rec.ln_user_col_d02 := null; end if; if p_calculator_ln_rec.ln_user_col_d03 is not null then v_contract_cashflow_rec.ln_user_col_d03 := to_date(p_calculator_ln_rec.ln_user_col_d03, 'YYYY-MM-DD'); else v_contract_cashflow_rec.ln_user_col_d03 := null; end if; if p_calculator_ln_rec.ln_user_col_d04 is not null then v_contract_cashflow_rec.ln_user_col_d04 := to_date(p_calculator_ln_rec.ln_user_col_d04, 'YYYY-MM-DD'); else v_contract_cashflow_rec.ln_user_col_d04 := null; end if; if p_calculator_ln_rec.ln_user_col_d05 is not null then v_contract_cashflow_rec.ln_user_col_d05 := to_date(p_calculator_ln_rec.ln_user_col_d05, 'YYYY-MM-DD'); else v_contract_cashflow_rec.ln_user_col_d05 := null; end if; v_contract_cashflow_rec.ln_user_col_v01 := p_calculator_ln_rec.ln_user_col_v01; --用户字段字符1 v_contract_cashflow_rec.ln_user_col_v02 := p_calculator_ln_rec.ln_user_col_v02; v_contract_cashflow_rec.ln_user_col_v03 := p_calculator_ln_rec.ln_user_col_v03; v_contract_cashflow_rec.ln_user_col_v04 := p_calculator_ln_rec.ln_user_col_v04; v_contract_cashflow_rec.ln_user_col_v05 := p_calculator_ln_rec.ln_user_col_v05; v_contract_cashflow_rec.ln_user_col_n01 := p_calculator_ln_rec.ln_user_col_n01; --用户字段数字1 v_contract_cashflow_rec.ln_user_col_n02 := p_calculator_ln_rec.ln_user_col_n02; v_contract_cashflow_rec.ln_user_col_n03 := p_calculator_ln_rec.ln_user_col_n03; v_contract_cashflow_rec.ln_user_col_n04 := p_calculator_ln_rec.ln_user_col_n04; v_contract_cashflow_rec.ln_user_col_n05 := p_calculator_ln_rec.ln_user_col_n05; v_contract_cashflow_rec.ln_user_col_n06 := p_calculator_ln_rec.ln_user_col_n06; v_contract_cashflow_rec.ln_user_col_n07 := p_calculator_ln_rec.ln_user_col_n07; v_contract_cashflow_rec.ln_user_col_n08 := p_calculator_ln_rec.ln_user_col_n08; v_contract_cashflow_rec.ln_user_col_n09 := p_calculator_ln_rec.ln_user_col_n09; v_contract_cashflow_rec.ln_user_col_n10 := p_calculator_ln_rec.ln_user_col_n10; v_contract_cashflow_rec.ln_user_col_n11 := p_calculator_ln_rec.ln_user_col_n11; v_contract_cashflow_rec.ln_user_col_n12 := p_calculator_ln_rec.ln_user_col_n12; v_contract_cashflow_rec.ln_user_col_n13 := p_calculator_ln_rec.ln_user_col_n13; v_contract_cashflow_rec.ln_user_col_n14 := p_calculator_ln_rec.ln_user_col_n14; v_contract_cashflow_rec.ln_user_col_n15 := p_calculator_ln_rec.ln_user_col_n15; v_contract_cashflow_rec.ln_user_col_n16 := p_calculator_ln_rec.ln_user_col_n16; v_contract_cashflow_rec.ln_user_col_n17 := p_calculator_ln_rec.ln_user_col_n17; v_contract_cashflow_rec.ln_user_col_n18 := p_calculator_ln_rec.ln_user_col_n18; v_contract_cashflow_rec.ln_user_col_n19 := p_calculator_ln_rec.ln_user_col_n19; v_contract_cashflow_rec.ln_user_col_n20 := p_calculator_ln_rec.ln_user_col_n20; v_contract_cashflow_rec.rental_eq_pymt_raw := null; --初始租金(等额) v_contract_cashflow_rec.rental_eq_pymt_adj := null; --调整租金(等额) v_contract_cashflow_rec.interest_eq_pymt_raw := null; --初始利息(等额) v_contract_cashflow_rec.interest_eq_pymt_adj := null; --调整利息(等额) v_contract_cashflow_rec.principal_eq_pymt_raw := null; --初始本金(等额) v_contract_cashflow_rec.principal_eq_pymt_adj := null; --调整本金(等额) v_contract_cashflow_rec.rental_eq_prin_raw := null; --初始租金(等本) v_contract_cashflow_rec.rental_eq_prin_adj := null; --调整租金(等本) v_contract_cashflow_rec.interest_eq_prin_raw := null; --初始利息(等本) v_contract_cashflow_rec.interest_eq_prin_adj := null; --调整利息(等本) v_contract_cashflow_rec.principal_eq_prin_raw := null; --初始本金(等本) v_contract_cashflow_rec.principal_eq_prin_adj := null; --调整本金(等本) v_contract_cashflow_rec.outstanding_rental_tax_incld := p_calculator_ln_rec.outstanding_rental_tax_incld; v_contract_cashflow_rec.outstanding_prin_tax_incld := p_calculator_ln_rec.outstanding_prin_tax_incld; v_contract_cashflow_rec.outstanding_int_tax_incld := p_calculator_ln_rec.outstanding_int_tax_incld; v_contract_cashflow_rec.interest_accrual_bal_tax_incl := p_calculator_ln_rec.interest_accrual_bal_tax_incl; v_contract_cashflow_rec.accumulated_unpd_int_tax_incl := p_calculator_ln_rec.accumulated_unpd_int_tax_incl; v_contract_cashflow_rec.exchange_rate_type := p_calculator_ln_rec.exchange_rate_type; v_contract_cashflow_rec.exchange_rate_quotation := p_calculator_ln_rec.exchange_rate_quotation; v_contract_cashflow_rec.exchange_rate := p_calculator_ln_rec.exchange_rate; v_contract_cashflow_rec.due_amount_cny := p_calculator_ln_rec.due_amount_cny; v_contract_cashflow_rec.main_business_income := p_calculator_ln_rec.main_business_income; v_contract_cashflow_rec.main_business_cost := p_calculator_ln_rec.main_business_cost; v_contract_cashflow_rec.financing_cost := p_calculator_ln_rec.financing_cost; 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; --系统时间 --liukang v_contract_cashflow_rec.payment_deduction_flag := 'Y'; v_contract_cashflow_rec.payment_deduction_amount := v_contract_cashflow_rec.due_amount; insert_contract_cashflow(p_contract_cashflow_rec => v_contract_cashflow_rec); end; end; procedure insert_others_cashflow(p_calculator_ln_rec hls_fin_calculator_ln%rowtype, p_price_list varchar2, p_contract_id number, p_company_id number, p_column_name varchar2, p_due_amount number, p_vat_due_amount number, p_net_due_amount number, p_user_id number) is v_contract_cashflow_rec con_contract_cashflow%rowtype; v_cf_item hls_cashflow_item.cf_item%type; v_cf_type hls_cashflow_item.cf_type%type; v_cf_direction hls_cashflow_item.cf_direction%type; v_cf_status con_contract_cashflow.cf_status%type; v_con_contract_cashflow_id number; begin begin select b.cf_item, b.cf_type, b.cf_direction, nvl(c.default_cf_status, 'RELEASE') into v_cf_item, v_cf_type, v_cf_direction, v_cf_status from hls_fin_calc_config a, hls_cashflow_item b, hls_cashflow_item_company c where a.price_list = p_price_list and a.column_name = p_column_name and a.cf_item = b.cf_item and c.company_id(+) = p_company_id and b.cf_item = c.cf_item(+); exception when no_data_found then return; end; if v_cf_item = 2 then null; end if; begin select * into v_contract_cashflow_rec from con_contract_cashflow where contract_id = p_contract_id and times = p_calculator_ln_rec.times and cf_item = v_cf_item --and generated_source = 'CALCULATOR' --and calc_line_id = p_calculator_ln_rec.calc_line_id ; v_contract_cashflow_rec.times := p_calculator_ln_rec.times; --期数 v_contract_cashflow_rec.calc_date := to_date(p_calculator_ln_rec.calc_date, 'YYYY-MM-DD'); --计算日 v_contract_cashflow_rec.due_date := to_date(p_calculator_ln_rec.due_date, 'YYYY-MM-DD'); --到期日 v_contract_cashflow_rec.fin_income_date := to_date(p_calculator_ln_rec.fin_income_date, 'YYYY-MM-DD'); --收入计算日 v_contract_cashflow_rec.due_amount := p_due_amount; --租金 v_contract_cashflow_rec.principal := null; --本金 v_contract_cashflow_rec.interest := null; --利息 if p_column_name = 'LEASE_ITEM_AMOUNT' then v_contract_cashflow_rec.outstanding_rental_tax_incld := p_calculator_ln_rec.outstanding_rental_tax_incld; v_contract_cashflow_rec.outstanding_prin_tax_incld := p_calculator_ln_rec.outstanding_prin_tax_incld; v_contract_cashflow_rec.outstanding_int_tax_incld := p_calculator_ln_rec.outstanding_int_tax_incld; v_contract_cashflow_rec.interest_accrual_bal_tax_incl := p_calculator_ln_rec.interest_accrual_bal_tax_incl; v_contract_cashflow_rec.accumulated_unpd_int_tax_incl := p_calculator_ln_rec.accumulated_unpd_int_tax_incl; v_contract_cashflow_rec.outstanding_rental := p_calculator_ln_rec.outstanding_rental; --剩余租金 v_contract_cashflow_rec.outstanding_principal := p_calculator_ln_rec.outstanding_principal; --剩余本金 v_contract_cashflow_rec.outstanding_interest := p_calculator_ln_rec.outstanding_interest; --剩余利息 v_contract_cashflow_rec.interest_accrual_balance := null; --计息余额 v_contract_cashflow_rec.accumulated_unpaid_interest := null; --累计未偿还利息 end if; v_contract_cashflow_rec.principal_implicit_rate := null; --实际利率法本金 v_contract_cashflow_rec.interest_implicit_rate := null; --实际利率法利息 v_contract_cashflow_rec.interest_period_days := p_calculator_ln_rec.interest_period_days; --计息天数(利息期天数) v_contract_cashflow_rec.discounting_days := p_calculator_ln_rec.discounting_days; --贴现天数 v_contract_cashflow_rec.vat_due_amount := p_vat_due_amount; v_contract_cashflow_rec.vat_principal := null; v_contract_cashflow_rec.vat_interest := null; v_contract_cashflow_rec.vat_principal_implicit := null; --实际利率法本金增值税额 v_contract_cashflow_rec.vat_interest_implicit := null; --实际利率法利息增值税额 v_contract_cashflow_rec.net_due_amount := p_net_due_amount; v_contract_cashflow_rec.net_principal := null; v_contract_cashflow_rec.net_interest := null; v_contract_cashflow_rec.net_principal_implicit := null; --实际利率法税后本金 v_contract_cashflow_rec.net_interest_implicit := null; --实际利率法税后利息 v_contract_cashflow_rec.fix_principal_flag := p_calculator_ln_rec.fix_principal_flag; --固定本金标志 v_contract_cashflow_rec.fix_rental_flag := p_calculator_ln_rec.fix_rental_flag; --固定租金标志 v_contract_cashflow_rec.interest_only_flag := p_calculator_ln_rec.interest_only_flag; --仅支付利息 v_contract_cashflow_rec.equal_flag := p_calculator_ln_rec.equal_flag; v_contract_cashflow_rec.manual_flag := p_calculator_ln_rec.manual_flag; v_contract_cashflow_rec.beginning_of_lease_year := p_calculator_ln_rec.beginning_of_lease_year; --每个租赁年度的开始 v_contract_cashflow_rec.salestax := null; --营业税 --用户字段日期1 if p_calculator_ln_rec.ln_user_col_d01 is not null then v_contract_cashflow_rec.ln_user_col_d01 := to_date(p_calculator_ln_rec.ln_user_col_d01, 'YYYY-MM-DD'); else v_contract_cashflow_rec.ln_user_col_d01 := null; end if; if p_calculator_ln_rec.ln_user_col_d02 is not null then v_contract_cashflow_rec.ln_user_col_d02 := to_date(p_calculator_ln_rec.ln_user_col_d02, 'YYYY-MM-DD'); else v_contract_cashflow_rec.ln_user_col_d02 := null; end if; if p_calculator_ln_rec.ln_user_col_d03 is not null then v_contract_cashflow_rec.ln_user_col_d03 := to_date(p_calculator_ln_rec.ln_user_col_d03, 'YYYY-MM-DD'); else v_contract_cashflow_rec.ln_user_col_d03 := null; end if; if p_calculator_ln_rec.ln_user_col_d04 is not null then v_contract_cashflow_rec.ln_user_col_d04 := to_date(p_calculator_ln_rec.ln_user_col_d04, 'YYYY-MM-DD'); else v_contract_cashflow_rec.ln_user_col_d04 := null; end if; if p_calculator_ln_rec.ln_user_col_d05 is not null then v_contract_cashflow_rec.ln_user_col_d05 := to_date(p_calculator_ln_rec.ln_user_col_d05, 'YYYY-MM-DD'); else v_contract_cashflow_rec.ln_user_col_d05 := null; end if; v_contract_cashflow_rec.ln_user_col_v01 := p_calculator_ln_rec.ln_user_col_v01; --用户字段字符1 v_contract_cashflow_rec.ln_user_col_v02 := p_calculator_ln_rec.ln_user_col_v02; v_contract_cashflow_rec.ln_user_col_v03 := p_calculator_ln_rec.ln_user_col_v03; v_contract_cashflow_rec.ln_user_col_v04 := p_calculator_ln_rec.ln_user_col_v04; v_contract_cashflow_rec.ln_user_col_v05 := p_calculator_ln_rec.ln_user_col_v05; v_contract_cashflow_rec.ln_user_col_n01 := p_calculator_ln_rec.ln_user_col_n01; --用户字段数字1 v_contract_cashflow_rec.ln_user_col_n02 := p_calculator_ln_rec.ln_user_col_n02; v_contract_cashflow_rec.ln_user_col_n03 := p_calculator_ln_rec.ln_user_col_n03; v_contract_cashflow_rec.ln_user_col_n04 := p_calculator_ln_rec.ln_user_col_n04; v_contract_cashflow_rec.ln_user_col_n05 := p_calculator_ln_rec.ln_user_col_n05; v_contract_cashflow_rec.ln_user_col_n06 := p_calculator_ln_rec.ln_user_col_n06; v_contract_cashflow_rec.ln_user_col_n07 := p_calculator_ln_rec.ln_user_col_n07; v_contract_cashflow_rec.ln_user_col_n08 := p_calculator_ln_rec.ln_user_col_n08; v_contract_cashflow_rec.ln_user_col_n09 := p_calculator_ln_rec.ln_user_col_n09; v_contract_cashflow_rec.ln_user_col_n10 := p_calculator_ln_rec.ln_user_col_n10; v_contract_cashflow_rec.ln_user_col_n11 := p_calculator_ln_rec.ln_user_col_n11; v_contract_cashflow_rec.ln_user_col_n12 := p_calculator_ln_rec.ln_user_col_n12; v_contract_cashflow_rec.ln_user_col_n13 := p_calculator_ln_rec.ln_user_col_n13; v_contract_cashflow_rec.ln_user_col_n14 := p_calculator_ln_rec.ln_user_col_n14; v_contract_cashflow_rec.ln_user_col_n15 := p_calculator_ln_rec.ln_user_col_n15; v_contract_cashflow_rec.ln_user_col_n16 := p_calculator_ln_rec.ln_user_col_n16; v_contract_cashflow_rec.ln_user_col_n17 := p_calculator_ln_rec.ln_user_col_n17; v_contract_cashflow_rec.ln_user_col_n18 := p_calculator_ln_rec.ln_user_col_n18; v_contract_cashflow_rec.ln_user_col_n19 := p_calculator_ln_rec.ln_user_col_n19; v_contract_cashflow_rec.ln_user_col_n20 := p_calculator_ln_rec.ln_user_col_n20; v_contract_cashflow_rec.exchange_rate_type := p_calculator_ln_rec.exchange_rate_type; v_contract_cashflow_rec.exchange_rate_quotation := p_calculator_ln_rec.exchange_rate_quotation; v_contract_cashflow_rec.exchange_rate := p_calculator_ln_rec.exchange_rate; v_contract_cashflow_rec.due_amount_cny := p_calculator_ln_rec.due_amount_cny; v_contract_cashflow_rec.main_business_income := p_calculator_ln_rec.main_business_income; v_contract_cashflow_rec.main_business_cost := p_calculator_ln_rec.main_business_cost; v_contract_cashflow_rec.financing_cost := p_calculator_ln_rec.financing_cost; v_contract_cashflow_rec.last_updated_by := p_user_id; --当前用户 v_contract_cashflow_rec.last_update_date := sysdate; --系统时间 --liukang v_contract_cashflow_rec.payment_deduction_flag := 'Y'; v_contract_cashflow_rec.payment_deduction_amount := v_contract_cashflow_rec.due_amount; if v_contract_cashflow_rec.cf_type = 1 then --add by chenlingfeng 2019年4月9日 v_contract_cashflow_rec.tax_type_id := p_calculator_ln_rec.tax_type_id; v_contract_cashflow_rec.tax_type_rate := p_calculator_ln_rec.tax_type_rate; end if; update_contract_cashflow(p_contract_cashflow_rec => v_contract_cashflow_rec); exception when no_data_found then 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 := v_cf_item; --计算器行表的列,所配置的现金流项目 v_contract_cashflow_rec.cf_type := v_cf_type; -- 根据现金流项目取得现金流类型 v_contract_cashflow_rec.cf_direction := v_cf_direction; v_contract_cashflow_rec.cf_status := v_cf_status; v_contract_cashflow_rec.times := p_calculator_ln_rec.times; --期数 v_contract_cashflow_rec.calc_date := to_date(p_calculator_ln_rec.calc_date, 'YYYY-MM-DD'); --计算日 v_contract_cashflow_rec.due_date := to_date(p_calculator_ln_rec.due_date, 'YYYY-MM-DD'); --到期日 v_contract_cashflow_rec.fin_income_date := to_date(p_calculator_ln_rec.fin_income_date, 'YYYY-MM-DD'); --收入计算日 v_contract_cashflow_rec.due_amount := p_due_amount; --租金 v_contract_cashflow_rec.principal := null; --本金 v_contract_cashflow_rec.interest := null; --利息 if p_column_name = 'LEASE_ITEM_AMOUNT' then v_contract_cashflow_rec.outstanding_rental_tax_incld := p_calculator_ln_rec.outstanding_rental_tax_incld; v_contract_cashflow_rec.outstanding_prin_tax_incld := p_calculator_ln_rec.outstanding_prin_tax_incld; v_contract_cashflow_rec.outstanding_int_tax_incld := p_calculator_ln_rec.outstanding_int_tax_incld; v_contract_cashflow_rec.interest_accrual_bal_tax_incl := p_calculator_ln_rec.interest_accrual_bal_tax_incl; v_contract_cashflow_rec.accumulated_unpd_int_tax_incl := p_calculator_ln_rec.accumulated_unpd_int_tax_incl; v_contract_cashflow_rec.outstanding_rental := p_calculator_ln_rec.outstanding_rental; --剩余租金 v_contract_cashflow_rec.outstanding_principal := p_calculator_ln_rec.outstanding_principal; --剩余本金 v_contract_cashflow_rec.outstanding_interest := p_calculator_ln_rec.outstanding_interest; --剩余利息 v_contract_cashflow_rec.interest_accrual_balance := null; --计息余额 v_contract_cashflow_rec.accumulated_unpaid_interest := null; --累计未偿还利息 end if; v_contract_cashflow_rec.principal_implicit_rate := null; --实际利率法本金 v_contract_cashflow_rec.interest_implicit_rate := null; --实际利率法利息 v_contract_cashflow_rec.interest_period_days := p_calculator_ln_rec.interest_period_days; --计息天数(利息期天数) v_contract_cashflow_rec.discounting_days := p_calculator_ln_rec.discounting_days; --贴现天数 v_contract_cashflow_rec.vat_due_amount := p_vat_due_amount; v_contract_cashflow_rec.vat_principal := null; v_contract_cashflow_rec.vat_interest := null; v_contract_cashflow_rec.vat_principal_implicit := null; --实际利率法本金增值税额 v_contract_cashflow_rec.vat_interest_implicit := null; --实际利率法利息增值税额 v_contract_cashflow_rec.net_due_amount := p_net_due_amount; v_contract_cashflow_rec.net_principal := null; v_contract_cashflow_rec.net_interest := null; v_contract_cashflow_rec.net_principal_implicit := null; --实际利率法税后本金 v_contract_cashflow_rec.net_interest_implicit := null; --实际利率法税后利息 v_contract_cashflow_rec.fix_principal_flag := p_calculator_ln_rec.fix_principal_flag; --固定本金标志 v_contract_cashflow_rec.fix_rental_flag := p_calculator_ln_rec.fix_rental_flag; --固定租金标志 v_contract_cashflow_rec.interest_only_flag := p_calculator_ln_rec.interest_only_flag; --仅支付利息 v_contract_cashflow_rec.equal_flag := p_calculator_ln_rec.equal_flag; v_contract_cashflow_rec.manual_flag := p_calculator_ln_rec.manual_flag; v_contract_cashflow_rec.beginning_of_lease_year := p_calculator_ln_rec.beginning_of_lease_year; --每个租赁年度的开始 v_contract_cashflow_rec.salestax := null; --营业税 v_contract_cashflow_rec.generated_source := 'CALCULATOR'; --现金流由计算器创建 v_contract_cashflow_rec.calc_line_id := p_calculator_ln_rec.calc_line_id; v_contract_cashflow_rec.overdue_status := 'N'; v_contract_cashflow_rec.overdue_book_date := null; v_contract_cashflow_rec.overdue_amount := null; v_contract_cashflow_rec.overdue_principal := null; v_contract_cashflow_rec.overdue_interest := null; v_contract_cashflow_rec.overdue_remark := null; v_contract_cashflow_rec.received_amount := null; v_contract_cashflow_rec.received_principal := null; v_contract_cashflow_rec.received_interest := null; v_contract_cashflow_rec.write_off_flag := 'NOT'; v_contract_cashflow_rec.last_received_date := null; v_contract_cashflow_rec.full_write_off_date := null; v_contract_cashflow_rec.penalty_process_status := 'NORMAL'; v_contract_cashflow_rec.billing_status := 'NOT'; --用户字段日期1 if p_calculator_ln_rec.ln_user_col_d01 is not null then v_contract_cashflow_rec.ln_user_col_d01 := to_date(p_calculator_ln_rec.ln_user_col_d01, 'YYYY-MM-DD'); else v_contract_cashflow_rec.ln_user_col_d01 := null; end if; if p_calculator_ln_rec.ln_user_col_d02 is not null then v_contract_cashflow_rec.ln_user_col_d02 := to_date(p_calculator_ln_rec.ln_user_col_d02, 'YYYY-MM-DD'); else v_contract_cashflow_rec.ln_user_col_d02 := null; end if; if p_calculator_ln_rec.ln_user_col_d03 is not null then v_contract_cashflow_rec.ln_user_col_d03 := to_date(p_calculator_ln_rec.ln_user_col_d03, 'YYYY-MM-DD'); else v_contract_cashflow_rec.ln_user_col_d03 := null; end if; if p_calculator_ln_rec.ln_user_col_d04 is not null then v_contract_cashflow_rec.ln_user_col_d04 := to_date(p_calculator_ln_rec.ln_user_col_d04, 'YYYY-MM-DD'); else v_contract_cashflow_rec.ln_user_col_d04 := null; end if; if p_calculator_ln_rec.ln_user_col_d05 is not null then v_contract_cashflow_rec.ln_user_col_d05 := to_date(p_calculator_ln_rec.ln_user_col_d05, 'YYYY-MM-DD'); else v_contract_cashflow_rec.ln_user_col_d05 := null; end if; v_contract_cashflow_rec.ln_user_col_v01 := p_calculator_ln_rec.ln_user_col_v01; --用户字段字符1 v_contract_cashflow_rec.ln_user_col_v02 := p_calculator_ln_rec.ln_user_col_v02; v_contract_cashflow_rec.ln_user_col_v03 := p_calculator_ln_rec.ln_user_col_v03; v_contract_cashflow_rec.ln_user_col_v04 := p_calculator_ln_rec.ln_user_col_v04; v_contract_cashflow_rec.ln_user_col_v05 := p_calculator_ln_rec.ln_user_col_v05; v_contract_cashflow_rec.ln_user_col_n01 := p_calculator_ln_rec.ln_user_col_n01; --用户字段数字1 v_contract_cashflow_rec.ln_user_col_n02 := p_calculator_ln_rec.ln_user_col_n02; v_contract_cashflow_rec.ln_user_col_n03 := p_calculator_ln_rec.ln_user_col_n03; v_contract_cashflow_rec.ln_user_col_n04 := p_calculator_ln_rec.ln_user_col_n04; v_contract_cashflow_rec.ln_user_col_n05 := p_calculator_ln_rec.ln_user_col_n05; v_contract_cashflow_rec.ln_user_col_n06 := p_calculator_ln_rec.ln_user_col_n06; v_contract_cashflow_rec.ln_user_col_n07 := p_calculator_ln_rec.ln_user_col_n07; v_contract_cashflow_rec.ln_user_col_n08 := p_calculator_ln_rec.ln_user_col_n08; v_contract_cashflow_rec.ln_user_col_n09 := p_calculator_ln_rec.ln_user_col_n09; v_contract_cashflow_rec.ln_user_col_n10 := p_calculator_ln_rec.ln_user_col_n10; v_contract_cashflow_rec.ln_user_col_n11 := p_calculator_ln_rec.ln_user_col_n11; v_contract_cashflow_rec.ln_user_col_n12 := p_calculator_ln_rec.ln_user_col_n12; v_contract_cashflow_rec.ln_user_col_n13 := p_calculator_ln_rec.ln_user_col_n13; v_contract_cashflow_rec.ln_user_col_n14 := p_calculator_ln_rec.ln_user_col_n14; v_contract_cashflow_rec.ln_user_col_n15 := p_calculator_ln_rec.ln_user_col_n15; v_contract_cashflow_rec.ln_user_col_n16 := p_calculator_ln_rec.ln_user_col_n16; v_contract_cashflow_rec.ln_user_col_n17 := p_calculator_ln_rec.ln_user_col_n17; v_contract_cashflow_rec.ln_user_col_n18 := p_calculator_ln_rec.ln_user_col_n18; v_contract_cashflow_rec.ln_user_col_n19 := p_calculator_ln_rec.ln_user_col_n19; v_contract_cashflow_rec.ln_user_col_n20 := p_calculator_ln_rec.ln_user_col_n20; v_contract_cashflow_rec.rental_eq_pymt_raw := null; --初始租金(等额) v_contract_cashflow_rec.rental_eq_pymt_adj := null; --调整租金(等额) v_contract_cashflow_rec.interest_eq_pymt_raw := null; --初始利息(等额) v_contract_cashflow_rec.interest_eq_pymt_adj := null; --调整利息(等额) v_contract_cashflow_rec.principal_eq_pymt_raw := null; --初始本金(等额) v_contract_cashflow_rec.principal_eq_pymt_adj := null; --调整本金(等额) v_contract_cashflow_rec.rental_eq_prin_raw := null; --初始租金(等本) v_contract_cashflow_rec.rental_eq_prin_adj := null; --调整租金(等本) v_contract_cashflow_rec.interest_eq_prin_raw := null; --初始利息(等本) v_contract_cashflow_rec.interest_eq_prin_adj := null; --调整利息(等本) v_contract_cashflow_rec.principal_eq_prin_raw := null; --初始本金(等本) v_contract_cashflow_rec.principal_eq_prin_adj := null; --调整本金(等本) v_contract_cashflow_rec.exchange_rate_type := p_calculator_ln_rec.exchange_rate_type; v_contract_cashflow_rec.exchange_rate_quotation := p_calculator_ln_rec.exchange_rate_quotation; v_contract_cashflow_rec.exchange_rate := p_calculator_ln_rec.exchange_rate; v_contract_cashflow_rec.due_amount_cny := p_calculator_ln_rec.due_amount_cny; v_contract_cashflow_rec.main_business_income := p_calculator_ln_rec.main_business_income; v_contract_cashflow_rec.main_business_cost := p_calculator_ln_rec.main_business_cost; v_contract_cashflow_rec.financing_cost := p_calculator_ln_rec.financing_cost; 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; --系统时间 --liukang v_contract_cashflow_rec.payment_deduction_flag := 'Y'; v_contract_cashflow_rec.payment_deduction_amount := v_contract_cashflow_rec.due_amount; if v_contract_cashflow_rec.cf_type = 1 then --add by chenlingfeng 2019年4月9日 v_contract_cashflow_rec.tax_type_id := p_calculator_ln_rec.tax_type_id; v_contract_cashflow_rec.tax_type_rate := p_calculator_ln_rec.tax_type_rate; end if; insert_contract_cashflow(p_contract_cashflow_rec => v_contract_cashflow_rec); end; end; procedure insert_quotation(p_quotation_rec prj_quotation%rowtype) is begin insert into prj_quotation values p_quotation_rec; end; procedure insert_contract(p_contract_rec con_contract%rowtype) is begin insert into con_contract values p_contract_rec; end; procedure insert_contract_bp(p_contract_bp_rec con_contract_bp%rowtype) is begin insert into con_contract_bp values p_contract_bp_rec; end; procedure insert_contract_lease_item(p_contract_lease_item_rec con_contract_lease_item%rowtype) is begin insert into con_contract_lease_item values p_contract_lease_item_rec; end; procedure recreate_cf_duedate(p_company_id number default null, p_contract_id number default null, p_contract_status varchar2 default null, p_start_date date, p_end_date date, p_user_id number) is begin for c_con in (select contract_id, biz_day_convention from con_contract where company_id = nvl(p_company_id, company_id) and contract_id = nvl(p_contract_id, contract_id) and contract_status = nvl(p_contract_status, contract_status) and data_class = 'NORMAL' and biz_day_convention is not null) loop for c_cf in (select a.cashflow_id, a.due_date from con_contract_cashflow a where a.contract_id = c_con.contract_id and due_date is not null and due_date between p_start_date and nvl(p_end_date, due_date)) loop update con_contract_cashflow set due_date = nextduedate(c_cf.due_date, 0, c_con.biz_day_convention) where cashflow_id = c_cf.cashflow_id; end loop; end loop; end; --add by wcs 2014-10-30 --purpose : calculate the due date for rental function calculate_rental_due_date(p_date date, p_annual_pay_times number) return date is v_date date; v_days varchar2(2); begin v_date := add_months(p_date, p_annual_pay_times); v_days := to_char(v_date, 'dd'); if p_annual_pay_times = 0 then return p_date; end if; if (to_number(v_days) >= 28) then v_date := to_date(to_char(v_date, 'yyyy-mm') || '28', 'yyyy-mm-dd'); end if; return v_date; end; procedure create_cf_date(p_contract_rec con_contract%rowtype, p_inception_of_lease date, p_inception_50_flag varchar2 default null) is v_calc_date date; v_due_date date; v_fin_date date; i number; v_exists number; v_annual_pay_times number; v_times number; v_min_times number; v_current_times number; v_last_times number; v_month_num number; begin --add by wangwei if p_contract_rec.annual_pay_times is not null then v_annual_pay_times := p_contract_rec.annual_pay_times; else select max(ccc.times) into v_times from con_contract_cashflow ccc where ccc.contract_id = p_contract_rec.contract_id and ccc.cf_item = 1; v_annual_pay_times := v_times / p_contract_rec.lease_term; end if; begin select 1 into v_exists from dual where exists (select 1 from con_contract_cashflow where contract_id = p_contract_rec.contract_id and cf_item = 1 and cf_direction = 'INFLOW' and (calc_date is null or due_date is null or fin_income_date is null)); exception when no_data_found then if p_contract_rec.inception_of_lease = p_inception_of_lease then return; --不计算 end if; end; --modify by wcs 2014-10-30 --purpose: individual designted for YONDA, the pay type is prepayment model forever if p_contract_rec.pay_type = 0 then --后付 i := 12 / p_contract_rec.annual_pay_times; elsif p_contract_rec.pay_type = 1 then --先付 i := 0; end if; --add by zyx 2017-03-17 select min(ccc.times) into v_min_times from con_contract_cashflow ccc where ccc.contract_id = p_contract_rec.contract_id -- AND ccc.cf_item = 1 and times > 0 and ccc.cf_direction = 'INFLOW'; i := i + (12 / v_annual_pay_times) * (v_min_times - 1); v_last_times := v_min_times - 1; for cur_cf in (select --cashflow_id, times from con_contract_cashflow where contract_id = p_contract_rec.contract_id -- AND cf_item = 1 and cf_direction = 'INFLOW' and times > 0 group by times order by times --modify by chenlingfeng 同一期可能没有cf_item=1 /*SELECT cashflow_id, times FROM con_contract_cashflow WHERE contract_id = p_contract_rec.contract_id AND cf_item = 1 AND cf_direction = 'INFLOW' ORDER BY times*/ ) loop v_current_times := cur_cf.times; --第一期租金取付款申请创建时录入的首期还款日 --隔期支付租金 v_month_num := (12 / v_annual_pay_times) * (v_current_times - v_last_times - 1); i := i + v_month_num; v_calc_date := add_months(p_inception_of_lease, (i - 1)); v_due_date := nextduedate(v_calc_date, 0, p_contract_rec.biz_day_convention); v_fin_date := v_calc_date; /*UPDATE con_contract_cashflow SET calc_date = v_calc_date, due_date = v_due_date, fin_income_date = v_fin_date, ln_user_col_d01 = v_calc_date WHERE cashflow_id = cur_cf.cashflow_id;*/ --更新同期现金流 update con_contract_cashflow set calc_date = v_calc_date, due_date = v_due_date, fin_income_date = v_fin_date, ln_user_col_d01 = v_calc_date where contract_id = p_contract_rec.contract_id and times = cur_cf.times -- AND cf_item <> 1 /*and (due_date is null or calc_date is null or fin_income_date is null)*/ ; i := i + (12 / v_annual_pay_times); v_last_times := cur_cf.times; end loop; end; procedure save_cdd_item_doc_ref(p_source_document_table varchar2, p_source_document_id number, p_to_document_table varchar2, p_to_document_id number, p_user_id number) is begin --save prj_cdd_item_doc_ref insert into prj_cdd_item_doc_ref (doc_ref_id, document_table, document_id, check_id, created_by, creation_date, last_updated_by, last_update_date) (select prj_cdd_item_doc_ref_s.nextval, p_to_document_table, p_to_document_id, check_id, p_user_id, sysdate, p_user_id, sysdate from prj_cdd_item_doc_ref where document_table = p_source_document_table and document_id = p_source_document_id); end; --更新项目后督表数据到合同后督表 modify by lpc 9874 2017/12/6 procedure save_file_info(p_source_document_id number, p_to_document_id number, p_user_id number) is begin --save prj_cdd_item_doc_ref insert into con_file_info (file_info_id, contract_id, condition_req, department_id, req_status, expiration_date, req_description, req_flag, req_date, created_by, creation_date, last_updated_by, last_update_date, raise_status) (select con_file_info_s.nextval, p_to_document_id, condition_req, department_id, req_status, expiration_date, req_description, req_flag, req_date, p_user_id, sysdate, p_user_id, sysdate, raise_status from prj_file_info where project_id = p_source_document_id); end; procedure save_contract(p_project_rec prj_project%rowtype, p_calculator_hd_rec hls_fin_calculator_hd%rowtype, p_contract_id number, p_contract_seq number, p_con_document_type varchar2, p_con_contract_name varchar2, p_billing_method varchar2, p_con_contract_number out varchar2, p_user_id number) is v_contract_rec con_contract%rowtype; v_con_document_type varchar2(30); v_bp_id_tenant number; v_contract_number varchar2(30); v_file_count number; v_count number; v_bp_class varchar2(50); v_num_year varchar2(10); v_num_seq varchar2(10); v_num_div varchar2(10); r_quotation_rec prj_quotation%rowtype; begin begin select bp_id into v_bp_id_tenant from prj_project_bp where project_id = p_project_rec.project_id and bp_category = 'TENANT' and bp_id is not null; -- and contract_seq = p_contract_seq; exception when no_data_found then begin select bp_id_tenant into v_bp_id_tenant from prj_project where project_id = p_project_rec.project_id; --2017年12月05日14:40:53 for Deron exception when no_data_found then raise e_bp_id_tenant_err; end; end; select hbm.bp_class into v_bp_class from hls_bp_master hbm where hbm.bp_id = v_bp_id_tenant; --缺经销商类别的商业伙伴 v_contract_rec.contract_id := p_contract_id; -- 合同ID / PK v_contract_rec.project_id := p_project_rec.project_id; v_contract_rec.document_type := p_con_document_type; -- 合同类型 v_contract_rec.document_category := g_document_category; -- 合同类别 --直接从项目上取相应的商业模式 --update by ww select business_type into v_contract_rec.business_type from prj_project a where a.project_id = p_project_rec.project_id; v_contract_rec.data_class := 'NORMAL'; --hongquan.dai 20180108 取项目编号 /* select replace(pp.project_number, 'SQ', 'ZL') into v_contract_number from prj_project pp where pp.project_id = p_project_rec.project_id;*/ /* v_contract_number := 'CON' || substr(p_project_rec.project_number, 4); --hongquan.dai 20180105 SELECT COUNT(1) + 1 INTO v_count FROM con_contract c WHERE c.project_id = p_project_rec.project_id AND c.data_class = 'NORMAL'; v_contract_number := v_contract_number || '-' || v_count; */ /*年度(218) + 挖掘机(EX)/装载机(WL)/二手机(IE)+ 营业税(1)/17%增值税(2)/16%增值税(3) + 5位序列号 第6位新系统取值为3 */ hls_document_save_pkg.get_doc_field(p_document_category => 'CONTRACT', p_document_type => 'CARCON', p_company_id => 1, p_function_code => null, p_function_usage => null, p_user_id => 1, p_document_number => v_contract_number); --2018CT00001 /* select replace(substr(v_contract_number, 1, 4), '0', '') into v_num_year from dual; select substr(v_contract_number, 7, length(v_contract_number)) into v_num_seq from dual; select t.ref_v05 into v_num_div from hls_division t where t.division = p_project_rec.division; v_contract_number := v_num_year || v_num_div || '3' || v_num_seq; v_contract_rec.contract_number := v_contract_number;*/ /* v_contract_rec.contract_number := get_contract_number(p_document_type => p_con_document_type, p_document_date => SYSDATE, p_company_id => p_project_rec.company_id, p_user_id => p_user_id); -- 合同编号*/ v_contract_rec.contract_name := p_con_contract_name; -- 合同名称 v_contract_rec.contract_number :=v_contract_number; v_contract_rec.company_id := p_project_rec.company_id; -- 公司ID v_contract_rec.lease_organization := p_project_rec.lease_organization; -- 租赁组织 v_contract_rec.lease_channel := p_project_rec.lease_channel; -- 渠道 v_contract_rec.district := p_project_rec.district; v_contract_rec.division := p_project_rec.division; -- 产品线 v_contract_rec.bp_id_tenant := v_bp_id_tenant; -- 主承租人ID v_contract_rec.bp_id_agent_level1 := ''; -- 经销商ID(第1层) v_contract_rec.bp_id_agent_level2 := ''; -- 经销商ID(第2层) v_contract_rec.bp_id_agent_level3 := ''; -- 经销商ID(第3层) v_contract_rec.owner_user_id := p_project_rec.owner_user_id; -- 单据所有者(用户ID) v_contract_rec.employee_id := p_project_rec.employee_id; -- 业务员(用户ID) v_contract_rec.unit_id := p_project_rec.unit_id; -- 部门ID v_contract_rec.employee_id_of_manager := p_project_rec.employee_id_of_manager; -- 部门经理(用户ID) v_contract_rec.description := p_project_rec.description; -- 说明 v_contract_rec.contract_status := 'NEW'; -- 合同状态(新建 NEW、签约 SIGN、起租 INCEPT、正常结清 TERMINATE、提前结清 ET、回购 REPUR、取消 CANCEL) v_contract_rec.user_status_1 := 'UNDEFINE'; -- 合同状态(预留1) v_contract_rec.user_status_2 := 'UNDEFINE'; -- 合同状态(预留2) v_contract_rec.user_status_3 := 'UNDEFINE'; -- 合同状态(预留3) v_contract_rec.overdue_status := 'N'; v_contract_rec.print_status := 'NOT'; -- 打印状态 v_contract_rec.print_times := ''; -- 打印次数 v_contract_rec.first_print_date := ''; -- 打印日期 v_contract_rec.first_print_by := ''; -- 打印者 v_contract_rec.delivery_status := 'NOT'; -- 租赁物交付状态(已交付、已验收) v_contract_rec.delivery_date := ''; -- 交付日期 v_contract_rec.billing_method := p_billing_method; -- 开票方式(设备票、分期、本金收据利息票) v_contract_rec.billing_status := 'NOT'; -- 开票状态(NOT/PARTIAL/FULL) v_contract_rec.calc_session_id := p_calculator_hd_rec.calc_session_id; -- 租金计算ID v_contract_rec.price_list := p_calculator_hd_rec.price_list; -- 价目表 v_contract_rec.calc_method := ''; -- 计算方式:等本/等额 v_contract_rec.invoice_price := p_project_rec.invoice_price; ---add by zlf v_contract_rec.owner_user_tel := p_project_rec.owner_user_tel; v_contract_rec.version := 1; v_contract_rec.version_date := trunc(sysdate); v_contract_rec.version_display := 'v1'; v_contract_rec.version_reason := 'CONTRACT_CREATION'; v_contract_rec.created_by := p_user_id; v_contract_rec.creation_date := sysdate; v_contract_rec.last_updated_by := p_user_id; v_contract_rec.last_update_date := sysdate; v_contract_rec.reg_status := '10'; --合同归档状态 v_contract_rec.archive_status := '10'; --登记证归档状态 -- v_contract_rec.wfl_instance_id := p_project_rec.wfl_instance_id; -- 插实例ID --使用经销商1字段 v_contract_rec.bp_id_agent_level1 := p_project_rec.invoice_agent_id; v_contract_rec.fin_manager := p_project_rec.fin_manager; --modify by lpc for BISINESS_DIVISION v_contract_rec.bisiness_division := p_project_rec.bisiness_division; -- v_contract_rec.bank_account_id := 25678; --默认收款银行 A_德融融资租赁_建行_0466 --add by lijingjing 2019-09-24 v_contract_rec.lease_item_location :=p_project_rec.lease_item_location; v_contract_rec.delivery_address :=p_project_rec.delivery_address; v_contract_rec.payment_deduction :=p_project_rec.payment_deduction; v_contract_rec.secondary_lease :=p_project_rec.secondary_lease; v_contract_rec.pay_method :=p_project_rec.pay_method; v_contract_rec.bank_branch_name :=p_project_rec.bank_branch_name; v_contract_rec.bank_branch_num :=p_project_rec.bank_branch_num; v_contract_rec.bank_account_name :=p_project_rec.bank_account_name; v_contract_rec.score_level :=p_project_rec.score_level; v_contract_rec.score_result :=p_project_rec.score_result; v_contract_rec.wfl_contract_status :='NEW'; insert_contract(p_contract_rec => v_contract_rec); --单据复制 hls_document_transfer_pkg.doc_to_doc(p_from_doc_table => 'PRJ_PROJECT', p_from_doc_pk => p_project_rec.project_id, p_to_doc_table => 'CON_CONTRACT', p_to_doc_pk => p_contract_id, p_function_code => null, p_function_usage => null, p_user_id => p_user_id); --add by chenlingfeng 2018年8月6日 select * into r_quotation_rec from prj_quotation where document_category = 'PROJECT' and document_id = p_project_rec.project_id /*and enabled_flag = 'Y'*/ --and contract_seq = p_contract_seq and calc_session_id is not null /*and internal_confirm = 'Y' and external_confirm = 'Y'*/ ; hls_document_transfer_pkg.doc_to_doc(p_from_doc_table => 'PRJ_QUOTATION', p_from_doc_pk => r_quotation_rec.quotation_id, p_to_doc_table => 'CON_CONTRACT', p_to_doc_pk => p_contract_id, p_function_code => null, p_function_usage => null, p_user_id => p_user_id); --end --单据复制 hls_document_transfer_pkg.doc_to_doc(p_from_doc_table => 'HLS_FIN_CALCULATOR_HD', p_from_doc_pk => p_calculator_hd_rec.calc_session_id, p_to_doc_table => 'CON_CONTRACT', p_to_doc_pk => p_contract_id, p_function_code => null, p_function_usage => null, p_user_id => p_user_id); update con_contract set billing_method = p_billing_method, billing_way = decode(v_bp_class, 'NP', '10', 'ORG', '20', '10') where contract_id = p_contract_id; --在 prj_project 表中新增一个字段 create_con_date update prj_project pp set pp.create_con_date = sysdate where pp.project_id = p_project_rec.project_id; --save penalty profile update con_contract a set (penalty_profile, grace_period, penalty_rate, penalty_calc_base, penalty_total_base_ratio) = (select penalty_profile, grace_period, penalty_rate, penalty_calc_base, penalty_total_base_ratio from con_penalty_profile b where b.penalty_profile = nvl(a.penalty_profile, 'PENALTY_STD')) where a.contract_id = p_contract_id; --add by wcs 2015-04-28 --SOLVE THE BUG about special contract than annual pay times filed is null select * into v_contract_rec from con_contract t where t.contract_id = p_contract_id; if v_contract_rec.annual_pay_times is null then select count(*) into v_contract_rec.lease_times from hls_fin_calculator_ln t where t.times != '0' and t.calc_session_id = v_contract_rec.calc_session_id; v_contract_rec.annual_pay_times := v_contract_rec.lease_times / v_contract_rec.lease_term; update con_contract t set t.annual_pay_times = v_contract_rec.annual_pay_times, t.lease_times = v_contract_rec.lease_times where t.contract_id = p_contract_id; end if; save_cdd_item_doc_ref(p_source_document_table => 'PRJ_PROJECT', p_source_document_id => p_project_rec.project_id, p_to_document_table => 'CON_CONTRACT', p_to_document_id => p_contract_id, p_user_id => p_user_id); --更新项目后督表数据到合同后督表 modify by lpc 9874 2017/12/6 begin select count(1) into v_file_count from prj_file_info pf where pf.project_id = p_project_rec.project_id; if v_file_count > 0 then save_file_info(p_source_document_id => p_project_rec.project_id, p_to_document_id => p_contract_id, p_user_id => p_user_id); end if; end; aut_document_authority_pkg.copy_trx_user_authority(p_from_doc_category => 'PROJECT', p_from_doc_id => p_project_rec.project_id, p_to_doc_category => 'CONTRACT', p_to_doc_id => p_contract_id, p_user_id => p_user_id); con_contract_custom_pkg.after_save_contract(p_contract_id => p_contract_id, p_user_id => p_user_id); p_con_contract_number := v_contract_rec.contract_number; end; procedure save_contract_lease_num(p_contract_id number) is r_contract_rec con_contract%rowtype; v_lease_year number; v_quarter_num number; v_period_num number; v_quarter_step number; v_period_step number; j number := 0; begin select * into r_contract_rec from con_contract where contract_id = p_contract_id; if r_contract_rec.annual_pay_times = 1 then --年付 v_quarter_step := 0.25; v_period_step := 12; elsif r_contract_rec.annual_pay_times = 2 then --半年付 v_quarter_step := 0.5; v_period_step := 6; elsif r_contract_rec.annual_pay_times = 4 then --季付 v_quarter_step := 1; v_period_step := 3; elsif r_contract_rec.annual_pay_times = 12 then --月付 v_quarter_step := 3; v_period_step := 1; end if; for i in 0 .. r_contract_rec.lease_times loop if i = 0 then update con_contract_cashflow set lease_year = 0, quarter_num = 0, period_num = 0 where contract_id = p_contract_id and times = i; else j := j + 1; --j是每个年度内的 支付期数顺序号 v_lease_year := ceil(i / r_contract_rec.annual_pay_times); v_quarter_num := ceil(j / v_quarter_step); v_period_num := j * v_period_step; update con_contract_cashflow set lease_year = v_lease_year, quarter_num = v_quarter_num, period_num = v_period_num where contract_id = p_contract_id and times = i; end if; if j = r_contract_rec.annual_pay_times then --重置 j := 0; end if; end loop; end; procedure save_contract_cashflow(p_column_cf_item hls_fin_calculator_itfc_pkg.column_cf_item_t, p_fin_calculator_hd_rec hls_fin_calculator_hd%rowtype, p_fin_calculator_ln_rec hls_fin_calculator_ln%rowtype, p_contract_id number, p_user_id number) is t_column_cf_item hls_fin_calculator_itfc_pkg.column_cf_item_t; i number; v_cf_item varchar2(100); v_sql varchar2(32767); v_column_name varchar2(100); v_column_name_vat varchar2(100); v_column_name_net varchar2(100); v_due_amount number; v_vat_due_amount number; v_net_due_amount number; begin if p_column_cf_item.count = 0 then t_column_cf_item := hls_fin_calculator_itfc_pkg.get_column_cf_item(p_price_list => p_fin_calculator_hd_rec.price_list); else t_column_cf_item := p_column_cf_item; end if; i := t_column_cf_item.first; while t_column_cf_item.exists(i) loop v_cf_item := t_column_cf_item(i).cf_item; if v_cf_item = '1' then --租金 if to_number(p_fin_calculator_ln_rec.times) >= 1 then if nvl(p_fin_calculator_ln_rec.rental, 0) <> 0 then insert_rental_cashflow(p_calculator_ln_rec => p_fin_calculator_ln_rec, p_price_list => p_fin_calculator_hd_rec.price_list, p_contract_id => p_contract_id, p_company_id => p_fin_calculator_hd_rec.company_id, p_user_id => p_user_id); end if; end if; else --其他现金流 v_column_name := t_column_cf_item(i).column_name; v_column_name_net := t_column_cf_item(i).net_column_name; v_column_name_vat := t_column_cf_item(i).vat_column_name; --添加对手续费和贴息的处理 add by Spencer 3893 20160818 --remove by chenlingfeng for ds /* --modify by fengyong for 德融,不需要对保证金和贴息特殊处理。贴息正常按期收取 IF v_cf_item = '3' AND p_fin_calculator_ln_rec.times = '0' THEN v_sql := 'begin select lease_charge,net_lease_charge,vat_lease_charge into :1,:2,:3 from hls_fin_calculator_hd where calc_session_id = ' || p_fin_calculator_hd_rec.calc_session_id || ';end;'; \* ELSIF v_cf_item = '912' AND p_fin_calculator_ln_rec.times = '0' THEN v_sql := 'begin select notarial_fee_payable,net_notarial_fee,vat_notarial_fee into :1,:2,:3 from hls_fin_calculator_hd where calc_session_id = ' || p_fin_calculator_hd_rec.calc_session_id || ';end;';*\ ELSE*/ v_sql := 'begin select ' || v_column_name || ',' || nvl(v_column_name_net, 'null') || ',' || nvl(v_column_name_vat, 'null') || ' into :1,:2,:3 from hls_fin_calculator_ln where calc_line_id = ' || p_fin_calculator_ln_rec.calc_line_id || ';end;'; /* END IF;*/ execute immediate v_sql using out v_due_amount, out v_net_due_amount, out v_vat_due_amount; if nvl(v_due_amount, 0) <> 0 then --remove by chenlingfeng for ds /* modify by fengyong for 德融不对贴息处理,正常按照每期收取。 IF v_cf_item IN ('3', '912')*/ /* IF v_cf_item IN ('3') AND p_fin_calculator_ln_rec.times = '0' THEN insert_others_cashflow(p_calculator_ln_rec => p_fin_calculator_ln_rec, p_price_list => p_fin_calculator_hd_rec.price_list, p_contract_id => p_contract_id, p_company_id => p_fin_calculator_hd_rec.company_id, p_column_name => v_column_name, p_due_amount => round(v_due_amount, 2), p_vat_due_amount => round(v_vat_due_amount, 2), p_net_due_amount => round(v_net_due_amount, 2), p_user_id => p_user_id); ELSE*/ insert_others_cashflow(p_calculator_ln_rec => p_fin_calculator_ln_rec, p_price_list => p_fin_calculator_hd_rec.price_list, p_contract_id => p_contract_id, p_company_id => p_fin_calculator_hd_rec.company_id, p_column_name => v_column_name, p_due_amount => v_due_amount, p_vat_due_amount => v_vat_due_amount, p_net_due_amount => v_net_due_amount, p_user_id => p_user_id); /* END IF;*/ end if; end if; i := t_column_cf_item.next(i); end loop; end; procedure save_contract_cashflow(p_contract_id number, p_fin_calculator_hd_rec hls_fin_calculator_hd%rowtype, p_user_id number) is t_column_cf_item hls_fin_calculator_itfc_pkg.column_cf_item_t; begin t_column_cf_item := hls_fin_calculator_itfc_pkg.get_column_cf_item(p_price_list => p_fin_calculator_hd_rec.price_list); for c_hls_fin_calculator_ln in (select * from hls_fin_calculator_ln where calc_session_id = p_fin_calculator_hd_rec.calc_session_id order by times) loop save_contract_cashflow(p_column_cf_item => t_column_cf_item, p_fin_calculator_hd_rec => p_fin_calculator_hd_rec, p_fin_calculator_ln_rec => c_hls_fin_calculator_ln, p_contract_id => p_contract_id, p_user_id => p_user_id); end loop; end; procedure save_contract_cashflow_bak(p_contract_id number, p_fin_calculator_hd_rec hls_fin_calculator_hd%rowtype, p_user_id number) is begin --插入合同行表 for c_hls_fin_calculator_ln in (select * from hls_fin_calculator_ln where calc_session_id = p_fin_calculator_hd_rec.calc_session_id order by times) loop --租金 --if nvl(c_hls_fin_calculator_ln.rental, 0) <> 0 then if to_number(c_hls_fin_calculator_ln.times) >= 1 then insert_rental_cashflow(p_calculator_ln_rec => c_hls_fin_calculator_ln, p_price_list => p_fin_calculator_hd_rec.price_list, p_contract_id => p_contract_id, p_company_id => p_fin_calculator_hd_rec.company_id, p_user_id => p_user_id); end if; --其他现金流金额 if nvl(c_hls_fin_calculator_ln.lease_item_amount, 0) <> 0 then insert_others_cashflow(p_calculator_ln_rec => c_hls_fin_calculator_ln, p_price_list => p_fin_calculator_hd_rec.price_list, p_contract_id => p_contract_id, p_company_id => p_fin_calculator_hd_rec.company_id, p_column_name => 'LEASE_ITEM_AMOUNT', p_due_amount => c_hls_fin_calculator_ln.lease_item_amount, p_vat_due_amount => null, --c_hls_fin_calculator_ln.vat_input, p_net_due_amount => null, --c_hls_fin_calculator_ln.net_lease_item_amount, p_user_id => p_user_id); end if; -- if nvl(c_hls_fin_calculator_ln.down_payment, 0) <> 0 then insert_others_cashflow(p_calculator_ln_rec => c_hls_fin_calculator_ln, p_price_list => p_fin_calculator_hd_rec.price_list, p_contract_id => p_contract_id, p_company_id => p_fin_calculator_hd_rec.company_id, p_column_name => 'DOWN_PAYMENT', p_due_amount => c_hls_fin_calculator_ln.down_payment, p_vat_due_amount => c_hls_fin_calculator_ln.vat_down_payment, p_net_due_amount => c_hls_fin_calculator_ln.net_down_payment, p_user_id => p_user_id); end if; -- if nvl(c_hls_fin_calculator_ln.finance_amount, 0) <> 0 then insert_others_cashflow(p_calculator_ln_rec => c_hls_fin_calculator_ln, p_price_list => p_fin_calculator_hd_rec.price_list, p_contract_id => p_contract_id, p_company_id => p_fin_calculator_hd_rec.company_id, p_column_name => 'FINANCE_AMOUNT', p_due_amount => c_hls_fin_calculator_ln.finance_amount, p_vat_due_amount => c_hls_fin_calculator_ln.vat_finance_amount, p_net_due_amount => c_hls_fin_calculator_ln.net_finance_amount, p_user_id => p_user_id); end if; -- /*if nvl(c_hls_fin_calculator_ln.net_finance_amount, 0) <> 0 then insert_others_cashflow(p_calculator_ln_rec => c_hls_fin_calculator_ln, p_price_list => p_fin_calculator_hd_rec.price_list, p_contract_id => p_contract_id, p_company_id => p_fin_calculator_hd_rec.company_id, p_column_name => 'NET_FINANCE_AMOUNT', p_due_amount => c_hls_fin_calculator_ln.net_finance_amount, p_vat_due_amount => round(c_hls_fin_calculator_ln.net_finance_amount * p_fin_calculator_hd_rec.vat_rate / (1 + p_fin_calculator_hd_rec.vat_rate), p_fin_calculator_hd_rec.currency_precision), p_user_id => p_user_id); end if; */ -- if nvl(c_hls_fin_calculator_ln.lease_charge, 0) <> 0 then insert_others_cashflow(p_calculator_ln_rec => c_hls_fin_calculator_ln, p_price_list => p_fin_calculator_hd_rec.price_list, p_contract_id => p_contract_id, p_company_id => p_fin_calculator_hd_rec.company_id, p_column_name => 'LEASE_CHARGE', p_due_amount => c_hls_fin_calculator_ln.lease_charge, p_vat_due_amount => c_hls_fin_calculator_ln.vat_lease_charge, p_net_due_amount => c_hls_fin_calculator_ln.net_lease_charge, p_user_id => p_user_id); end if; -- if nvl(c_hls_fin_calculator_ln.lease_mgt_fee, 0) <> 0 then insert_others_cashflow(p_calculator_ln_rec => c_hls_fin_calculator_ln, p_price_list => p_fin_calculator_hd_rec.price_list, p_contract_id => p_contract_id, p_company_id => p_fin_calculator_hd_rec.company_id, p_column_name => 'LEASE_MGT_FEE', p_due_amount => c_hls_fin_calculator_ln.lease_mgt_fee, p_vat_due_amount => c_hls_fin_calculator_ln.vat_lease_mgt_fee, p_net_due_amount => c_hls_fin_calculator_ln.net_lease_mgt_fee, p_user_id => p_user_id); end if; -- if nvl(c_hls_fin_calculator_ln.deposit, 0) <> 0 then insert_others_cashflow(p_calculator_ln_rec => c_hls_fin_calculator_ln, p_price_list => p_fin_calculator_hd_rec.price_list, p_contract_id => p_contract_id, p_company_id => p_fin_calculator_hd_rec.company_id, p_column_name => 'DEPOSIT', p_due_amount => c_hls_fin_calculator_ln.deposit, p_vat_due_amount => null, p_net_due_amount => null, p_user_id => p_user_id); end if; -- if nvl(c_hls_fin_calculator_ln.residual_value, 0) <> 0 then insert_others_cashflow(p_calculator_ln_rec => c_hls_fin_calculator_ln, p_price_list => p_fin_calculator_hd_rec.price_list, p_contract_id => p_contract_id, p_company_id => p_fin_calculator_hd_rec.company_id, p_column_name => 'RESIDUAL_VALUE', p_due_amount => c_hls_fin_calculator_ln.residual_value, p_vat_due_amount => c_hls_fin_calculator_ln.vat_residual_value, p_net_due_amount => c_hls_fin_calculator_ln.net_residual_value, p_user_id => p_user_id); end if; -- if nvl(c_hls_fin_calculator_ln.insurance_fee, 0) <> 0 then insert_others_cashflow(p_calculator_ln_rec => c_hls_fin_calculator_ln, p_price_list => p_fin_calculator_hd_rec.price_list, p_contract_id => p_contract_id, p_company_id => p_fin_calculator_hd_rec.company_id, p_column_name => 'INSURANCE_FEE', p_due_amount => c_hls_fin_calculator_ln.insurance_fee, p_vat_due_amount => c_hls_fin_calculator_ln.vat_insurance_fee, p_net_due_amount => c_hls_fin_calculator_ln.net_insurance_fee, p_user_id => p_user_id); end if; -- if nvl(c_hls_fin_calculator_ln.commission_payable, 0) <> 0 then insert_others_cashflow(p_calculator_ln_rec => c_hls_fin_calculator_ln, p_price_list => p_fin_calculator_hd_rec.price_list, p_contract_id => p_contract_id, p_company_id => p_fin_calculator_hd_rec.company_id, p_column_name => 'COMMISSION_PAYABLE', p_due_amount => c_hls_fin_calculator_ln.commission_payable, p_vat_due_amount => null, p_net_due_amount => null, p_user_id => p_user_id); end if; -- if nvl(c_hls_fin_calculator_ln.commission_receivable, 0) <> 0 then insert_others_cashflow(p_calculator_ln_rec => c_hls_fin_calculator_ln, p_price_list => p_fin_calculator_hd_rec.price_list, p_contract_id => p_contract_id, p_company_id => p_fin_calculator_hd_rec.company_id, p_column_name => 'COMMISSION_RECEIVABLE', p_due_amount => c_hls_fin_calculator_ln.commission_receivable, p_vat_due_amount => c_hls_fin_calculator_ln.vat_commission_receivable, p_net_due_amount => c_hls_fin_calculator_ln.net_commission_receivable, p_user_id => p_user_id); end if; -- if nvl(c_hls_fin_calculator_ln.third_party_deposit, 0) <> 0 then insert_others_cashflow(p_calculator_ln_rec => c_hls_fin_calculator_ln, p_price_list => p_fin_calculator_hd_rec.price_list, p_contract_id => p_contract_id, p_company_id => p_fin_calculator_hd_rec.company_id, p_column_name => 'THIRD_PARTY_DEPOSIT', p_due_amount => c_hls_fin_calculator_ln.third_party_deposit, p_vat_due_amount => null, p_net_due_amount => null, p_user_id => p_user_id); end if; -- if nvl(c_hls_fin_calculator_ln.deposit_refund, 0) <> 0 then insert_others_cashflow(p_calculator_ln_rec => c_hls_fin_calculator_ln, p_price_list => p_fin_calculator_hd_rec.price_list, p_contract_id => p_contract_id, p_company_id => p_fin_calculator_hd_rec.company_id, p_column_name => 'DEPOSIT_REFUND', p_due_amount => c_hls_fin_calculator_ln.deposit_refund, p_vat_due_amount => null, p_net_due_amount => null, p_user_id => p_user_id); end if; -- if nvl(c_hls_fin_calculator_ln.third_party_deposit_refund, 0) <> 0 then insert_others_cashflow(p_calculator_ln_rec => c_hls_fin_calculator_ln, p_price_list => p_fin_calculator_hd_rec.price_list, p_contract_id => p_contract_id, p_company_id => p_fin_calculator_hd_rec.company_id, p_column_name => 'THIRD_PARTY_DEPOSIT_REFUND', p_due_amount => c_hls_fin_calculator_ln.third_party_deposit_refund, p_vat_due_amount => null, p_net_due_amount => null, p_user_id => p_user_id); end if; -- if nvl(c_hls_fin_calculator_ln.promise_to_pay, 0) <> 0 then insert_others_cashflow(p_calculator_ln_rec => c_hls_fin_calculator_ln, p_price_list => p_fin_calculator_hd_rec.price_list, p_contract_id => p_contract_id, p_company_id => p_fin_calculator_hd_rec.company_id, p_column_name => 'PROMISE_TO_PAY', p_due_amount => c_hls_fin_calculator_ln.promise_to_pay, p_vat_due_amount => null, p_net_due_amount => null, p_user_id => p_user_id); end if; -- if nvl(c_hls_fin_calculator_ln.other_fee, 0) <> 0 then insert_others_cashflow(p_calculator_ln_rec => c_hls_fin_calculator_ln, p_price_list => p_fin_calculator_hd_rec.price_list, p_contract_id => p_contract_id, p_company_id => p_fin_calculator_hd_rec.company_id, p_column_name => 'OTHER_FEE', p_due_amount => c_hls_fin_calculator_ln.other_fee, p_vat_due_amount => c_hls_fin_calculator_ln.vat_other_fee, p_net_due_amount => null, p_user_id => p_user_id); end if; -- if nvl(c_hls_fin_calculator_ln.other_payment, 0) <> 0 then insert_others_cashflow(p_calculator_ln_rec => c_hls_fin_calculator_ln, p_price_list => p_fin_calculator_hd_rec.price_list, p_contract_id => p_contract_id, p_company_id => p_fin_calculator_hd_rec.company_id, p_column_name => 'OTHER_PAYMENT', p_due_amount => c_hls_fin_calculator_ln.other_payment, p_vat_due_amount => null, p_net_due_amount => null, p_user_id => p_user_id); end if; -- -- if nvl(c_hls_fin_calculator_ln.cashflow_total, 0) <> 0 then insert_others_cashflow(p_calculator_ln_rec => c_hls_fin_calculator_ln, p_price_list => p_fin_calculator_hd_rec.price_list, p_contract_id => p_contract_id, p_company_id => p_fin_calculator_hd_rec.company_id, p_column_name => 'CASHFLOW_TOTAL', p_due_amount => c_hls_fin_calculator_ln.cashflow_total, p_vat_due_amount => null, p_net_due_amount => null, p_user_id => p_user_id); end if; if nvl(c_hls_fin_calculator_ln.notarial_fee, 0) <> 0 then insert_others_cashflow(p_calculator_ln_rec => c_hls_fin_calculator_ln, p_price_list => p_fin_calculator_hd_rec.price_list, p_contract_id => p_contract_id, p_company_id => p_fin_calculator_hd_rec.company_id, p_column_name => 'NOTARIAL_FEE', p_due_amount => c_hls_fin_calculator_ln.notarial_fee, p_vat_due_amount => c_hls_fin_calculator_ln.vat_notarial_fee, p_net_due_amount => c_hls_fin_calculator_ln.net_notarial_fee, p_user_id => p_user_id); end if; if nvl(c_hls_fin_calculator_ln.notarial_fee_payable, 0) <> 0 then insert_others_cashflow(p_calculator_ln_rec => c_hls_fin_calculator_ln, p_price_list => p_fin_calculator_hd_rec.price_list, p_contract_id => p_contract_id, p_company_id => p_fin_calculator_hd_rec.company_id, p_column_name => 'NOTARIAL_FEE_PAYABLE', p_due_amount => c_hls_fin_calculator_ln.notarial_fee_payable, p_vat_due_amount => null, p_net_due_amount => null, p_user_id => p_user_id); end if; -- if nvl(c_hls_fin_calculator_ln.btb_fee, 0) <> 0 then insert_others_cashflow(p_calculator_ln_rec => c_hls_fin_calculator_ln, p_price_list => p_fin_calculator_hd_rec.price_list, p_contract_id => p_contract_id, p_company_id => p_fin_calculator_hd_rec.company_id, p_column_name => 'BTB_FEE', p_due_amount => c_hls_fin_calculator_ln.btb_fee, p_vat_due_amount => null, p_net_due_amount => null, p_user_id => p_user_id); end if; --BTB if nvl(c_hls_fin_calculator_ln.btb_repayment, 0) <> 0 then insert_btb_cashflow(p_calculator_ln_rec => c_hls_fin_calculator_ln, p_price_list => p_fin_calculator_hd_rec.price_list, p_contract_id => p_contract_id, p_company_id => p_fin_calculator_hd_rec.company_id, p_user_id => p_user_id); end if; end loop; save_contract_lease_num(p_contract_id => p_contract_id); /*自定义出口程序 */ con_contract_custom_pkg.save_contract_cashflow(p_fin_calculator_hd_rec => p_fin_calculator_hd_rec, p_contract_id => p_contract_id, p_user_id => p_user_id); end; procedure save_contract_bp(p_project_id number, p_contract_id number, p_contract_seq number, p_user_id number) is r_contract_bp_rec con_contract_bp%rowtype; v_con_contract_bp_id number; r_doc_pk_list hls_document_transfer_pkg.r_doc_pk_list; v_doc_pk_list hls_document_transfer_pkg.t_doc_pk_list; i number := 0; v_sec_seq number := 1; v_np_seq number := 1; v_org_seq number := 1; begin --update by : zhangxing5129 --update date:2014-7-18 --update msg: 去掉合同号关联条件 。将项目上的主承租人、次承租人都生成至项目中 for c_project_bp in (select * from prj_project_bp b where b.project_id = p_project_id and b.bp_id is not null) loop /*IF c_project_bp.bp_type = 'TENANT_SEC' THEN SELECT COUNT(1) INTO v_count FROM prj_project_bp t WHERE t.project_id = p_project_id AND t.bp_type = c_project_bp.bp_type; IF v_count > 0 THEN r_contract_bp_rec.seq := v_sec_seq; v_sec_seq := v_sec_seq + 1; END IF; END IF; IF c_project_bp.bp_type = 'GUTA_NP' THEN SELECT COUNT(1) INTO v_count FROM prj_project_bp t WHERE t.project_id = p_project_id AND t.bp_type = c_project_bp.bp_type; IF v_count > 0 THEN r_contract_bp_rec.seq := v_np_seq; v_sec_seq := v_np_seq + 1; END IF; END IF; IF c_project_bp.bp_type = 'GUTA_ORG' THEN SELECT COUNT(1) INTO v_count FROM prj_project_bp t WHERE t.project_id = p_project_id AND t.bp_type = c_project_bp.bp_type; IF v_count > 0 THEN r_contract_bp_rec.seq := v_org_seq; v_sec_seq := v_org_seq + 1; END IF; END IF;*/ i := i + 1; select con_contract_bp_s.nextval into v_con_contract_bp_id from dual; r_contract_bp_rec.record_id := v_con_contract_bp_id; r_contract_bp_rec.contract_id := p_contract_id; r_contract_bp_rec.bp_type := c_project_bp.bp_type; r_contract_bp_rec.bp_class := c_project_bp.bp_class; r_contract_bp_rec.bp_name := c_project_bp.bp_name; r_contract_bp_rec.bp_code := c_project_bp.bp_code; r_contract_bp_rec.bp_category := c_project_bp.bp_category; r_contract_bp_rec.bp_id := c_project_bp.bp_id; -- r_contract_bp_rec.app_level := c_project_bp.app_level; -- r_contract_bp_rec.as_level := c_project_bp.as_level; -- r_contract_bp_rec.manage_level := c_project_bp.manage_level; r_contract_bp_rec.pay_method := c_project_bp.pay_method; r_contract_bp_rec.score_level := c_project_bp.score_level; r_contract_bp_rec.score_result := c_project_bp.score_result; r_contract_bp_rec.funnel_score := c_project_bp.funnel_score; r_contract_bp_rec.bs_level := c_project_bp.bs_level; r_contract_bp_rec.enabled_flag := 'Y'; --add by liukang 20160427 start --将prj_project_bp表中的字段赋值给con_contract_bp表 --申请人信息 insert_contract_bp(p_contract_bp_rec => r_contract_bp_rec); r_doc_pk_list.from_doc_pk := c_project_bp.prj_bp_id; r_doc_pk_list.to_doc_pk := v_con_contract_bp_id; v_doc_pk_list(i) := r_doc_pk_list; save_cdd_item_doc_ref(p_source_document_table => 'PRJ_PROJECT_BP', p_source_document_id => c_project_bp.prj_bp_id, p_to_document_table => 'CON_CONTRACT', p_to_document_id => p_contract_id, p_user_id => p_user_id); end loop; if i > 0 then hls_document_transfer_pkg.doc_to_doc(p_from_doc_table => 'PRJ_PROJECT_BP', p_to_doc_table => 'CON_CONTRACT_BP', p_doc_pk_list => v_doc_pk_list, p_to_doc_column_1 => 'contract_id', p_to_doc_column_1_value => p_contract_id, p_copy_method => hls_document_transfer_pkg.c_doc_to_history, p_user_id => p_user_id); end if; --更新bp 带出默认值 update con_contract_bp t set (t.bp_code, t.bp_class, t.bp_name, t.id_type, t.id_card_no, t.organization_code, t.tax_registry_num, t.cell_phone, t.id_card_address, t.SEND_ADDRESS, t.phone, t.phone_2, t.bp_name_sp, t.id_no_sp, t.id_card_address_sp, t.cell_phone_sp, t.bp_name_leg, t.id_card_no_leg) = (select t1.bp_code, t1.bp_class, t1.bp_name, t1.id_type, t1.id_card_no, t1.organization_code, t1.tax_registry_num, nvl(t.cell_phone, t1.cell_phone), nvl(t.id_card_address, t1.id_card_address), nvl(t.SEND_ADDRESS, t1.SEND_ADDRESS), nvl(t.phone, t1.phone), nvl(t.phone_2, t1.phone_2), t1.bp_name_sp, nvl(t.id_no_sp, t1.id_no_sp), nvl(t.id_card_address_sp, t1.id_card_address_sp), nvl(t.cell_phone_sp, t1.cell_phone_sp), t1.bp_name_leg, nvl(t.id_card_no_leg, t1.id_card_no_leg) from hls_bp_master t1 where t1.bp_id = t.bp_id) where t.contract_id = p_contract_id; -- end; procedure save_ast_from_prj(p_project_id number, p_contract_id number, p_contract_seq number, p_user_id number) is r_ast_con ast_con_insurance%rowtype; v_ast_con_car_insurance_id number; begin for r_ast_prj in (select * from ast_car_insurance t where t.project_id = p_project_id) loop select ast_con_insurance_s.nextval into v_ast_con_car_insurance_id from dual; hls_document_transfer_pkg.doc_to_doc(p_from_doc_table => 'AST_CAR_INSURANCE', p_from_doc_pk => r_ast_prj.ast_car_insurance_id, p_to_doc_table => 'AST_CON_INSURANCE', p_to_doc_pk => v_ast_con_car_insurance_id, p_copy_method => 'DOC_TO_HISTORY', p_user_id => p_user_id, p_to_doc_column_1 => 'CONTRACT_ID', p_to_doc_column_1_value => p_contract_id); end loop; end; procedure save_con_bp_from_agent(p_project_id number, p_contract_id number, p_contract_seq number, p_user_id number) is r_contract_bp_rec con_contract_bp%rowtype; v_con_contract_bp_id number; r_prj_project prj_project%rowtype; begin --通过项目invoice_agent_id插入合同BP表 select * into r_prj_project from prj_project t where t.project_id = p_project_id; if r_prj_project.invoice_agent_id is not null then select con_contract_bp_s.nextval into v_con_contract_bp_id from dual; /* BEGIN SELECT bm.through_flag INTO v_through_flag FROM hls_bp_master bm WHERE bm.bp_id = r_prj_project.invoice_agent_id; EXCEPTION WHEN no_data_found THEN v_through_flag := ''; NULL; END;*/ r_contract_bp_rec.record_id := v_con_contract_bp_id; r_contract_bp_rec.contract_id := p_contract_id; r_contract_bp_rec.bp_category := 'AGENT'; r_contract_bp_rec.bp_class := 'ORG'; r_contract_bp_rec.bp_id := r_prj_project.invoice_agent_id; r_contract_bp_rec.enabled_flag := 'Y'; r_contract_bp_rec.created_by := p_user_id; r_contract_bp_rec.creation_date := sysdate; r_contract_bp_rec.last_updated_by := p_user_id; r_contract_bp_rec.last_update_date := sysdate; insert_contract_bp(p_contract_bp_rec => r_contract_bp_rec); end if; end; procedure save_contract_lease_item(p_project_id number, p_contract_id number, p_contract_seq number, p_user_id number) is r_contract_lease_item_rec con_contract_lease_item%rowtype; v_con_contract_lease_item_id number; v_division varchar2(10); i number := 0; e_lease_item_number_error exception; begin --库存融资业务和保理业务全部复制租赁物--其他产品线则复制所填写租赁物 2017年12月05日14:00:05 for Deron select cc.division into v_division from con_contract cc where cc.contract_id = p_contract_id; for c_project_lease_item in (select * from prj_project_lease_item where project_id = p_project_id) loop -- i := 0; --检查数据 --已创建 /* SELECT COUNT(1) INTO v_exist_count FROM con_contract_lease_item ci WHERE ci.project_lease_item_id = c_project_lease_item.project_lease_item_id; */ --remove by chenlingfeng 2018-7-19 13:23:57 --去除数量确认 /*if nvl(c_project_lease_item.vehicles_number, 0) + v_exist_count > c_project_lease_item.quantity then raise e_lease_item_number_error; end if;*/ --本次确认数量 -- if nvl(c_project_lease_item.vehicles_number, 0) > 0 then /* LOOP i := i + 1;*/ /* select v.code_value into r_contract_lease_item_rec.manufacturer from sys_code_values_v v where v.code = 'MANUFACTURER_CON' and v.ref_v01 like '%' || v_division || '%';*/ select con_contract_lease_item_s.nextval into v_con_contract_lease_item_id from dual; r_contract_lease_item_rec.contract_lease_item_id := v_con_contract_lease_item_id; hls_document_transfer_pkg.doc_to_doc(p_from_doc_table => 'PRJ_PROJECT_LEASE_ITEM', p_from_doc_pk => c_project_lease_item.project_lease_item_id, p_to_doc_table => 'CON_CONTRACT_LEASE_ITEM', p_to_doc_pk => v_con_contract_lease_item_id, p_copy_method => 'DOC_TO_HISTORY', p_user_id => p_user_id, p_to_doc_column_1 => 'CONTRACT_ID', p_to_doc_column_1_value => p_contract_id, p_to_doc_column_2 => 'QUANTITY', --modify by lpc 拆分时数量为1 p_to_doc_column_2_value => 1, p_to_doc_column_3 => 'ENABLED_FLAG', p_to_doc_column_3_value => 'Y'); update con_contract_lease_item t set MANUFACTURER = r_contract_lease_item_rec.manufacturer where t.contract_lease_item_id = v_con_contract_lease_item_id; save_cdd_item_doc_ref(p_source_document_table => 'PRJ_PROJECT_LEASE_ITEM', p_source_document_id => c_project_lease_item.project_lease_item_id, p_to_document_table => 'CON_CONTRACT', p_to_document_id => p_contract_id, p_user_id => p_user_id); /* EXIT WHEN i >= c_project_lease_item.vehicles_number; END LOOP;*/ -- end if; -- update prj_project_lease_item pi set pi.vehicles_number = 0 where pi.project_lease_item_id = c_project_lease_item.project_lease_item_id; end loop; exception when e_lease_item_number_error then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CON_CONTRACT_PKG.LEASE_ITEM_NUMBER_ERROR', p_created_by => p_user_id, p_package_name => 'CON_CONTRACT_PKG', p_procedure_function_name => 'save_contract_lease_item'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end; --从合同lease_item到合同bp procedure save_con_bp_from_lease_item(p_contract_id number, p_user_id number) is r_contract_bp_rec con_contract_bp%rowtype; v_con_contract_bp_id number; begin for c_con_bp_rec in (select l.bp_id_vender from con_contract_lease_item l where l.contract_id = p_contract_id and l.bp_id_vender is not null and not exists (select 1 from con_contract_bp b where b.contract_id = p_contract_id and b.bp_category = 'VENDER' and b.bp_id = l.bp_id_vender) group by l.bp_id_vender) loop select con_contract_bp_s.nextval into v_con_contract_bp_id from dual; r_contract_bp_rec.record_id := v_con_contract_bp_id; r_contract_bp_rec.contract_id := p_contract_id; r_contract_bp_rec.bp_category := 'VENDER'; r_contract_bp_rec.bp_id := c_con_bp_rec.bp_id_vender; r_contract_bp_rec.created_by := p_user_id; r_contract_bp_rec.creation_date := sysdate; r_contract_bp_rec.last_updated_by := p_user_id; r_contract_bp_rec.last_update_date := sysdate; insert_contract_bp(p_contract_bp_rec => r_contract_bp_rec); end loop; end; --从项目PRJ_PROJECT_ACT_CTRLER_HD到合同bp procedure save_con_bp_from_act_ctrler(p_project_id number, p_contract_id number, p_contract_seq number, p_user_id number) is r_contract_bp_rec con_contract_bp%rowtype; v_con_contract_bp_id number; begin for c_con_bp_rec in (select * from prj_project_act_ctrler_hd h where h.project_id = p_project_id and h.contract_seq = p_contract_seq and h.bp_id is not null and not exists (select 1 from con_contract_bp b where b.contract_id = p_contract_id and b.bp_category = h.bp_category and b.bp_id = h.bp_id)) loop select con_contract_bp_s.nextval into v_con_contract_bp_id from dual; r_contract_bp_rec.record_id := v_con_contract_bp_id; r_contract_bp_rec.contract_id := p_contract_id; r_contract_bp_rec.bp_category := c_con_bp_rec.bp_category; r_contract_bp_rec.bp_id := c_con_bp_rec.bp_id; r_contract_bp_rec.created_by := p_user_id; r_contract_bp_rec.creation_date := sysdate; r_contract_bp_rec.last_updated_by := p_user_id; r_contract_bp_rec.last_update_date := sysdate; insert_contract_bp(p_contract_bp_rec => r_contract_bp_rec); end loop; end; --从项目PRJ_PROJECT_MORTGAGE到合同bp procedure save_con_bp_from_mortgage(p_project_id number, p_contract_id number, p_contract_seq number, p_user_id number) is r_contract_bp_rec con_contract_bp%rowtype; v_con_contract_bp_id number; begin for c_con_bp_rec in (select h.bp_id_mortgagor from prj_project_mortgage h where h.project_id = p_project_id and h.contract_seq = p_contract_seq and h.bp_id_mortgagor is not null and not exists (select 1 from con_contract_bp b where b.contract_id = p_contract_id and b.bp_category = 'PLEDGER' and b.bp_id = h.bp_id_mortgagor) group by h.bp_id_mortgagor) loop select con_contract_bp_s.nextval into v_con_contract_bp_id from dual; r_contract_bp_rec.record_id := v_con_contract_bp_id; r_contract_bp_rec.contract_id := p_contract_id; r_contract_bp_rec.bp_category := 'PLEDGER'; r_contract_bp_rec.bp_id := c_con_bp_rec.bp_id_mortgagor; r_contract_bp_rec.created_by := p_user_id; r_contract_bp_rec.creation_date := sysdate; r_contract_bp_rec.last_updated_by := p_user_id; r_contract_bp_rec.last_update_date := sysdate; insert_contract_bp(p_contract_bp_rec => r_contract_bp_rec); end loop; end; --插入hls_lease_item并更新PRJ_PROJECT_LEASE_ITEM procedure save_hls_lease_item(p_project_id number, p_contract_seq number, p_user_id number) is v_lease_item_id number; v_lease_item_code hls_lease_item.lease_item_code%type; begin for c_hls_lease_item_rec in (select i.*, p.company_id, p.owner_user_id, p.division project_division --添加别名project_division,因为租赁物表有同样列名,在游标中编译错误 from prj_project_lease_item i, prj_project p where i.project_id = p_project_id and i.project_id = p.project_id and i.lease_item_id is null and i.contract_seq = nvl(p_contract_seq, i.contract_seq)) loop hls_lease_item_pkg.ins_hls_lease_item(p_lease_item_id => v_lease_item_id, p_owner_user_id => c_hls_lease_item_rec.owner_user_id, p_lease_item_code => v_lease_item_code, p_short_name => c_hls_lease_item_rec.short_name, p_full_name => c_hls_lease_item_rec.full_name, p_lease_item_type => '', p_enabled_flag => 'Y', p_search_term => '', p_serial_number => c_hls_lease_item_rec.serial_number, p_pattern => c_hls_lease_item_rec.pattern, p_specification => c_hls_lease_item_rec.specification, p_uom => c_hls_lease_item_rec.uom, p_quantity => c_hls_lease_item_rec.quantity, p_currency => c_hls_lease_item_rec.currency, p_price => c_hls_lease_item_rec.price, p_original_asset_value => c_hls_lease_item_rec.original_asset_value, p_net_asset_value => c_hls_lease_item_rec.net_asset_value, p_accumulated_depreciation => c_hls_lease_item_rec.accumulated_depreciation, p_detention => '', p_manufacturer_id => c_hls_lease_item_rec.manufacturer_id, p_manufacturer_name => c_hls_lease_item_rec.manufacturer_name, p_manufacturing_date => c_hls_lease_item_rec.manufacturing_date, p_vender_id => c_hls_lease_item_rec.vender_id, p_vender_name => c_hls_lease_item_rec.vender_name, p_installation_site => c_hls_lease_item_rec.installation_site, p_fixed_assets_site => c_hls_lease_item_rec.fixed_assets_site, p_description => c_hls_lease_item_rec.description, p_division => c_hls_lease_item_rec.project_division, p_abc_class => '', p_lease_item_group => '', p_authority_group => '', p_company_enabled_flag => 'Y', p_company_id => c_hls_lease_item_rec.company_id, p_user_id => p_user_id, p_invoice_amt => c_hls_lease_item_rec.invoice_amt, p_invoice_num => c_hls_lease_item_rec.invoice_num, p_invoice_date => c_hls_lease_item_rec.invoice_date); update prj_project_lease_item t set t.lease_item_id = v_lease_item_id, t.last_update_date = sysdate, t.last_updated_by = p_user_id where t.project_lease_item_id = c_hls_lease_item_rec.project_lease_item_id; update prj_project_lease_item_list t1 set t1.lease_item_id = v_lease_item_id, t1.last_update_date = sysdate, t1.last_updated_by = p_user_id where t1.project_lease_item_id = c_hls_lease_item_rec.project_lease_item_id; for c_hls_lease_item_list_rec in (select * from prj_project_lease_item_list lil where lil.project_lease_item_id = c_hls_lease_item_rec.project_lease_item_id) loop hls_lease_item_pkg.ins_lease_item_list(p_lease_item_id => v_lease_item_id, p_seq => c_hls_lease_item_list_rec.seq, p_asset_num => c_hls_lease_item_list_rec.asset_num, p_asset_type => c_hls_lease_item_list_rec.asset_type, p_asset_name => c_hls_lease_item_list_rec.asset_name, p_posted_date => c_hls_lease_item_list_rec.posted_date, p_specification => c_hls_lease_item_list_rec.specification, p_vender => c_hls_lease_item_list_rec.vender, p_manufacturer => c_hls_lease_item_list_rec.manufacturer, p_quantity => c_hls_lease_item_list_rec.quantity, p_uom => c_hls_lease_item_list_rec.uom, p_original_asset_value => c_hls_lease_item_list_rec.original_asset_value, p_net_asset_value => c_hls_lease_item_list_rec.net_asset_value, p_price => c_hls_lease_item_list_rec.price, p_total_amount => c_hls_lease_item_list_rec.total_amount, p_accumulated_depreciation => c_hls_lease_item_list_rec.accumulated_depreciation, p_currency => c_hls_lease_item_list_rec.currency, p_installation_site => c_hls_lease_item_list_rec.installation_site, p_invoice_amt => c_hls_lease_item_list_rec.invoice_amt, p_invoice_amt_after_tax => c_hls_lease_item_list_rec.invoice_amt_after_tax, p_invoice_num => c_hls_lease_item_list_rec.invoice_num, p_invoice_date => c_hls_lease_item_list_rec.invoice_date, p_user_id => p_user_id, p_ref_v01 => c_hls_lease_item_list_rec.ref_v01, p_ref_v02 => c_hls_lease_item_list_rec.ref_v02, p_ref_v03 => c_hls_lease_item_list_rec.ref_v03, p_ref_v04 => c_hls_lease_item_list_rec.ref_v04, p_ref_v05 => c_hls_lease_item_list_rec.ref_v05, p_ref_n01 => c_hls_lease_item_list_rec.ref_n01, p_ref_n02 => c_hls_lease_item_list_rec.ref_n02, p_ref_n03 => c_hls_lease_item_list_rec.ref_n03, p_ref_n04 => c_hls_lease_item_list_rec.ref_n04, p_ref_n05 => c_hls_lease_item_list_rec.ref_n05, p_ref_d01 => c_hls_lease_item_list_rec.ref_d01, p_ref_d02 => c_hls_lease_item_list_rec.ref_d02, p_ref_d03 => c_hls_lease_item_list_rec.ref_d03, p_ref_d04 => c_hls_lease_item_list_rec.ref_d04, p_ref_d05 => c_hls_lease_item_list_rec.ref_d05); end loop; end loop; end; --插入hls_mortgage并更新PPRJ_PROJECT_MORTGAGE procedure save_hls_mortgage(p_project_id number, p_contract_seq number, p_user_id number) is v_mortgage_id hls_mortgage.mortgage_id%type; begin for c_hls_mortgage in (select i.*, p.company_id, p.owner_user_id from prj_project_mortgage i, prj_project p where i.project_id = p_project_id and i.project_id = p.project_id and i.mortgage_id is null and i.contract_seq = p_contract_seq) loop hls_mortgage_pkg.insert_mortgage(p_mortgage_id => v_mortgage_id, p_owner_user_id => c_hls_mortgage.owner_user_id, p_mortgage_code => '', p_mortgage_name => c_hls_mortgage.mortgage_name, p_mortgage_type => c_hls_mortgage.mortgage_type, p_mortgage_ast_classfication => c_hls_mortgage.mortgage_ast_classfication, p_mortgage_asset_detail => c_hls_mortgage.mortgage_asset_detail, p_mortgage_contract_no => c_hls_mortgage.mortgage_contract_no, p_project_id => c_hls_mortgage.project_id, p_mortgage_register_no => c_hls_mortgage.mortgage_register_no, p_mortgage_registed_dept => c_hls_mortgage.mortgage_registed_dept, p_bp_id_mortgagor => c_hls_mortgage.bp_id_mortgagor, p_mortgagor_name => c_hls_mortgage.mortgage_name, p_value => c_hls_mortgage.value, p_currency => c_hls_mortgage.currency, p_uom => c_hls_mortgage.uom, p_quantity => c_hls_mortgage.quantity, p_mortgage_status => '', p_start_date => c_hls_mortgage.start_date, p_end_date => c_hls_mortgage.end_date, p_bp_id_evaluator => c_hls_mortgage.bp_id_evaluator, p_evaluator_name => c_hls_mortgage.evaluator_name, p_ownership => c_hls_mortgage.ownership, p_occupied => c_hls_mortgage.occupied, p_location => c_hls_mortgage.location, p_estate_license_code => c_hls_mortgage.estate_license_code, p_housing_area => c_hls_mortgage.housing_area, p_use => c_hls_mortgage.use, p_construction_date => c_hls_mortgage.construction_date, p_building_structure => c_hls_mortgage.building_structure, p_land_certificate_code => c_hls_mortgage.land_certificate_code, p_land_area => c_hls_mortgage.land_area, p_land_use_rights_type => c_hls_mortgage.land_use_rights_type, p_land_use_rights_start_date => c_hls_mortgage.land_use_rights_start_date, p_usable_period => c_hls_mortgage.usable_period, p_construction_area => c_hls_mortgage.construction_area, p_status_of_land_use => c_hls_mortgage.status_of_land_use, p_company_name_stock => c_hls_mortgage.company_name_stock, p_stock_name => c_hls_mortgage.stock_name, p_stock_code => c_hls_mortgage.stock_code, p_stock_amount => c_hls_mortgage.stock_amount, p_stock_ratio => c_hls_mortgage.stock_ratio, p_fair_value => c_hls_mortgage.fair_value, p_appraise_date => c_hls_mortgage.appraise_date, p_total_value => c_hls_mortgage.total_value, p_bvps => c_hls_mortgage.bvps, p_equipment_type => c_hls_mortgage.equipment_type, p_pattern => c_hls_mortgage.pattern, p_specification => c_hls_mortgage.specification, p_enabled_flag => 'Y', p_note => c_hls_mortgage.note, p_user_id => p_user_id, p_company_id => c_hls_mortgage.company_id, p_ref_v01 => c_hls_mortgage.ref_v01, p_ref_v02 => c_hls_mortgage.ref_v02, p_ref_v03 => c_hls_mortgage.ref_v03, p_ref_v04 => c_hls_mortgage.ref_v04, p_ref_v05 => c_hls_mortgage.ref_v05, p_ref_n01 => c_hls_mortgage.ref_n01, p_ref_n02 => c_hls_mortgage.ref_n02, p_ref_n03 => c_hls_mortgage.ref_n03, p_ref_n04 => c_hls_mortgage.ref_n04, p_ref_n05 => c_hls_mortgage.ref_n05, p_ref_d01 => c_hls_mortgage.ref_d01, p_ref_d02 => c_hls_mortgage.ref_d02, p_ref_d03 => c_hls_mortgage.ref_d03, p_ref_d04 => c_hls_mortgage.ref_d04, p_ref_d05 => c_hls_mortgage.ref_d05); update prj_project_mortgage t set t.mortgage_id = v_mortgage_id, t.last_update_date = sysdate, t.last_updated_by = p_user_id where t.prj_mortgage_id = c_hls_mortgage.prj_mortgage_id; end loop; end; procedure save_quotation(p_project_rec prj_project%rowtype, p_contract_id number, p_contract_seq number, p_con_calc_session_id out number, p_user_id number) is r_quotation_rec prj_quotation%rowtype; r_hls_fin_calculator_hd hls_fin_calculator_hd%rowtype; begin begin select * into r_quotation_rec from prj_quotation where document_category = 'PROJECT' and document_id = p_project_rec.project_id /*and enabled_flag = 'Y'*/ --and contract_seq = p_contract_seq and calc_session_id is not null /*and internal_confirm = 'Y' and external_confirm = 'Y'*/ --modify by zhuxianfei DERON for update nowait; exception when no_data_found then raise e_quotation_status_err; when too_many_rows then raise e_quotation_status_err; end; --do before create --add by chenlingfeng update prj_project pp set pp.product_code = r_quotation_rec.product_code, pp.product_name = r_quotation_rec.product_name, pp.sub_price_list = r_quotation_rec.sub_price_list, pp.sub_price_list_name = r_quotation_rec.sub_price_list_name, pp.product_type = r_quotation_rec.product_type, pp.sub_price_list_code = r_quotation_rec.sub_price_list_code where pp.project_id = p_project_rec.project_id; --do before create --add by chenlingfeng update hls_fin_calculator_hd pp set pp.product_code = r_quotation_rec.product_code, pp.product_name = r_quotation_rec.product_name, pp.sub_price_list = r_quotation_rec.sub_price_list, pp.sub_price_list_name = r_quotation_rec.sub_price_list_name, pp.product_type = r_quotation_rec.product_type --, -- pp.sub_price_list_code = r_quotation_rec.sub_price_list_code where pp.calc_session_id = r_quotation_rec.calc_session_id; /*if r_quotation_rec.create_contract_flag = 'Y' then raise e_quotation_created_err; end if;*/ select * into r_hls_fin_calculator_hd from hls_fin_calculator_hd where calc_session_id = r_quotation_rec.calc_session_id; if r_hls_fin_calculator_hd.calc_successful = 'Y' then --复制计算器4张表的数据 hls_fin_calculator_itfc_pkg.copy_calculator(p_from_calc_session_id => r_quotation_rec.calc_session_id, p_to_calc_session_id => p_con_calc_session_id, p_user_id => p_user_id); update hls_fin_calculator_hd set source_doc_category = 'CONTRACT', source_doc_id = p_contract_id where calc_session_id = p_con_calc_session_id; else raise e_calculator_not_success_err; end if; if r_hls_fin_calculator_hd.calc_method is null then raise e_calc_method_notfound_err; end if; update prj_quotation set create_contract_flag = 'Y' where quotation_id = r_quotation_rec.quotation_id; end; procedure save_contract_billing_method(p_contract_id number, p_billing_method varchar2, p_user_id number) is begin insert into con_contract_billing_method (contract_id, billing_method, equipment_billing, rental_billing, principal_billing, principal_receipt, interest_billing, average_split, invoice_split_limit, split_rounding, bill_of_sale, invoice_line_limit, created_by, creation_date, last_updated_by, last_update_date, vat_invoice_split_limit, sales_tax_invoice_split_limit) (select p_contract_id, billing_method, equipment_billing, rental_billing, principal_billing, principal_receipt, interest_billing, average_split, invoice_split_limit, split_rounding, bill_of_sale, invoice_line_limit, p_user_id, sysdate, p_user_id, sysdate, vat_invoice_split_limit, sales_tax_invoice_split_limit from con_billing_method where billing_method = p_billing_method); end; procedure save_contract_cf_item(p_contract_id number, p_company_id number, p_user_id number) is begin insert into con_contract_cf_item (contract_id, cf_item, write_off_order, calc_penalty, created_by, creation_date, last_updated_by, last_update_date) (select p_contract_id, cf_item, write_off_order, calc_penalty, p_user_id, sysdate, p_user_id, sysdate from hls_cashflow_item_company where company_id = p_company_id); end; --合同序号和BP合同序号不一致,更新BP合同序号 procedure update_seq_check_different(p_project_id number, p_contract_seq number, p_bp_contract_seq number, p_user_id number) is v_bp_id_tenant number; begin if p_contract_seq <> p_bp_contract_seq then begin select bp_id into v_bp_id_tenant from prj_project_bp where project_id = p_project_id and bp_category = 'TENANT' and bp_id is not null and contract_seq = p_contract_seq; raise e_bp_id_tenant_err; exception when no_data_found then update prj_project_bp t set t.contract_seq = p_contract_seq, t.last_updated_by = p_user_id, t.last_update_date = sysdate where t.project_id = p_project_id and t.bp_category = 'TENANT' and t.bp_id is not null and t.contract_seq = p_bp_contract_seq; end; end if; end; --项目创建合同后更新prj_bp相关信息 procedure update_prj_bp_after_save(p_project_id prj_project.project_id%type, p_user_id number) is begin for r_prj_bp in (select d.bp_id, d.prj_bp_id from prj_project_bp d where d.project_id = p_project_id and d.bp_id is not null) loop hls_document_transfer_pkg.doc_to_doc(p_from_doc_table => 'HLS_BP_MASTER', p_from_doc_pk => r_prj_bp.bp_id, p_to_doc_table => 'PRJ_PROJECT_BP', p_to_doc_pk => r_prj_bp.prj_bp_id, p_function_code => 'PRJ505', p_function_usage => 'CREATE', p_user_id => p_user_id); end loop; end; --更新项目的prj_project_act_ctrler_hd procedure update_act_ctrler_after_save(p_project_id prj_project.project_id%type, p_user_id number) is begin for r_act_ctrler in (select d.bp_id, d.prj_bp_id from prj_project_act_ctrler_hd d where d.project_id = p_project_id and d.bp_id is not null) loop hls_document_transfer_pkg.doc_to_doc(p_from_doc_table => 'HLS_BP_MASTER', p_from_doc_pk => r_act_ctrler.bp_id, p_to_doc_table => 'PRJ_PROJECT_ACT_CTRLER_HD', p_to_doc_pk => r_act_ctrler.prj_bp_id, p_function_code => 'PRJ505', p_function_usage => 'CREATE', p_user_id => p_user_id); end loop; end; ---add by zlf procedure update_contract_file_date(p_file_date date, p_contract_id number, p_user_id number) is begin update con_contract a set a.contract_file_date = p_file_date, a.last_updated_by = p_user_id, a.last_update_date = sysdate where a.contract_id = p_contract_id; --插入小状态 yonda_doc_history_pkg.yonda_insert_doc_status(p_document_id => p_contract_id, p_document_category => 'CONTRACT', p_doc_status => '430', 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_PKG', p_procedure_function_name => 'UPDATE_CONTRACT_FILE_DATE'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end; procedure update_cashflow_net_amount(p_contract_id number, p_user_id number) is v_tax_type_id number; v_tax_type_rate number; v_net_due_amount number; v_net_principal number; v_net_interest number; begin for cur in (select cf.cashflow_id, cf.cf_item, cf.cf_type, cf.due_amount, cf.principal, cf.interest, cc.billing_method, hm.rental_billing_flag, hm.rental_tax_rate, hm.principal_billing_flag, hm.principal_tax_rate, hm.interest_billing_flag, hm.interest_tax_rate, hm.other_billing_flag, hm.other_tax_rate from con_contract_cashflow cf, con_contract cc, hls_billing_method hm where cf.contract_id = p_contract_id and cf.contract_id = cc.contract_id and cc.billing_method = hm.billing_method and cf.cf_direction = 'INFLOW') loop --租金 if cur.cf_type = 1 then --租金开票 if cur.rental_billing_flag = 'Y' then v_tax_type_id := cur.rental_tax_rate; v_tax_type_rate := get_tax_type_rate(v_tax_type_id); v_net_due_amount := round(cur.due_amount / (1 + v_tax_type_rate), 2); v_net_principal := round(cur.principal / (1 + v_tax_type_rate), 2); v_net_interest := round(cur.interest / (1 + v_tax_type_rate), 2); else --本金开票 if cur.principal_billing_flag = 'Y' then v_tax_type_id := cur.principal_tax_rate; v_tax_type_rate := get_tax_type_rate(v_tax_type_id); v_net_principal := round(cur.principal / (1 + v_tax_type_rate), 2); end if; --利息开票 if cur.interest_billing_flag = 'Y' then v_tax_type_id := cur.interest_tax_rate; v_tax_type_rate := get_tax_type_rate(v_tax_type_id); v_net_interest := round(cur.interest / (1 + v_tax_type_rate), 2); end if; v_net_due_amount := v_net_principal + v_net_interest; end if; else --判断行上是否定义 begin select hb.tax_rate into v_tax_type_id from hls_billing_method_cf hb where hb.cf_item = cur.cf_item and hb.billing_method = cur.billing_method and hb.enabled_flag = 'Y'; v_tax_type_rate := get_tax_type_rate(v_tax_type_id); v_net_due_amount := round(cur.due_amount / (1 + v_tax_type_rate), 2); v_net_principal := round(cur.principal / (1 + v_tax_type_rate), 2); v_net_interest := round(cur.interest / (1 + v_tax_type_rate), 2); exception when no_data_found then --其他开票 if cur.other_billing_flag = 'Y' then v_tax_type_id := cur.other_tax_rate; v_tax_type_rate := get_tax_type_rate(v_tax_type_id); v_net_due_amount := round(cur.due_amount / (1 + v_tax_type_rate), 2); v_net_principal := round(cur.principal / (1 + v_tax_type_rate), 2); v_net_interest := round(cur.interest / (1 + v_tax_type_rate), 2); end if; end; end if; --更新cashflow if v_net_due_amount is not null or v_net_principal is not null or v_net_interest is not null then update con_contract_cashflow cf set cf.net_due_amount = v_net_due_amount, cf.net_principal = v_net_principal, cf.net_interest = v_net_interest, cf.last_update_date = sysdate, cf.last_updated_by = p_user_id where cf.cashflow_id = cur.cashflow_id; end if; end loop; end; function get_tax_rate(p_contract_id number, p_cf_item number) return number is v_rec_billing_method hls_billing_method%rowtype; v_tax_type_id number; v_tax_type_rate number; e_error exception; begin select hm.* into v_rec_billing_method from hls_billing_method hm where hm.billing_method = (select cc.billing_method from con_contract cc where cc.contract_id = p_contract_id); --租金 if p_cf_item = 1 then select cc.tax_type_id into v_tax_type_id from con_contract cc where cc.contract_id = p_contract_id; --租金开票 if v_rec_billing_method.rental_billing_flag = 'Y' then v_tax_type_id := v_rec_billing_method.rental_tax_rate; v_tax_type_rate := get_tax_type_rate(v_tax_type_id); --deron 未定义租金开票规则的时候 返回利息开票信息 elsif v_rec_billing_method.interest_billing_flag = 'Y' then v_tax_type_id := v_rec_billing_method.interest_tax_rate; v_tax_type_rate := get_tax_type_rate(v_tax_type_id); elsif v_rec_billing_method.other_billing_flag = 'Y' then v_tax_type_id := v_rec_billing_method.other_tax_rate; v_tax_type_rate := get_tax_type_rate(v_tax_type_id); end if; /*elsif p_cf_item = 100 then --本金开票 if v_rec_billing_method.principal_billing_flag = 'Y' then v_tax_type_id := v_rec_billing_method.principal_tax_rate; v_tax_type_rate := get_tax_type_rate(v_tax_type_id); elsif v_rec_billing_method.other_billing_flag = 'Y' then v_tax_type_id := v_rec_billing_method.OTHER_TAX_RATE; v_tax_type_rate := get_tax_type_rate(v_tax_type_id); end if; elsif p_cf_item = 101 then --利息开票 if v_rec_billing_method.interest_billing_flag = 'Y' then v_tax_type_id := v_rec_billing_method.interest_tax_rate; v_tax_type_rate := get_tax_type_rate(v_tax_type_id); elsif v_rec_billing_method.other_billing_flag = 'Y' then v_tax_type_id := v_rec_billing_method.OTHER_TAX_RATE; v_tax_type_rate := get_tax_type_rate(v_tax_type_id); end if;*/ else --判断行上是否定义 begin select hb.tax_rate into v_tax_type_id from hls_billing_method_cf hb where hb.cf_item = p_cf_item and hb.billing_method = v_rec_billing_method.billing_method and hb.enabled_flag = 'Y'; v_tax_type_rate := get_tax_type_rate(v_tax_type_id); exception when no_data_found then --其他开票 if v_rec_billing_method.other_billing_flag = 'Y' then v_tax_type_id := v_rec_billing_method.other_tax_rate; v_tax_type_rate := get_tax_type_rate(v_tax_type_id); end if; end; end if; if v_tax_type_rate is null then raise e_error; else return v_tax_type_rate; end if; exception when e_error then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CON_CONTRACT_PKG.GET_TAX_RATE_ERROR', p_created_by => -1, p_package_name => 'CON_CONTRACT_PKG', p_procedure_function_name => 'GET_TAX_RATE'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end; --hongquan.dai 20180104 取tax_rate_id function get_tax_rate_id(p_contract_id number, p_cf_item number) return number is v_rec_billing_method hls_billing_method%rowtype; v_tax_type_id number; e_error exception; begin select hm.* into v_rec_billing_method from hls_billing_method hm where hm.billing_method = (select cc.billing_method from con_contract cc where cc.contract_id = p_contract_id); --租金 if p_cf_item = 1 then --租金开票 if v_rec_billing_method.rental_billing_flag = 'Y' then v_tax_type_id := v_rec_billing_method.rental_tax_rate; --deron 未定义租金开票规则的时候 返回利息开票信息 elsif v_rec_billing_method.interest_billing_flag = 'Y' then --update by doosan 税改 if v_rec_billing_method.interest_tax_rate_tp = '10' then select fc.tax_type_id into v_tax_type_id from con_contract cc, fnd_tax_type_codes fc where cc.vat_rate_of_interest = fc.tax_type_rate and cc.contract_id = p_contract_id and fc.tax_type_code like '%OUT%'; else v_tax_type_id := v_rec_billing_method.interest_tax_rate; end if; elsif v_rec_billing_method.other_billing_flag = 'Y' then v_tax_type_id := v_rec_billing_method.other_tax_rate; end if; else --判断行上是否定义 begin select hb.tax_rate into v_tax_type_id from hls_billing_method_cf hb where hb.cf_item = p_cf_item and hb.billing_method = v_rec_billing_method.billing_method and hb.enabled_flag = 'Y'; exception when no_data_found then --其他开票 if v_rec_billing_method.other_billing_flag = 'Y' then v_tax_type_id := v_rec_billing_method.other_tax_rate; end if; end; end if; if v_tax_type_id is null then raise e_error; else return v_tax_type_id; end if; exception when e_error then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CON_CONTRACT_PKG.GET_TAX_RATE_ERROR', p_created_by => -1, p_package_name => 'CON_CONTRACT_PKG', p_procedure_function_name => 'GET_TAX_RATE_ID'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end; function get_tax_type_rate(p_tax_type_id number default null) return number is v_tax_type_rate number; begin if p_tax_type_id is not null then select t.tax_type_rate into v_tax_type_rate from fnd_tax_type_codes t where t.tax_type_id = p_tax_type_id; else v_tax_type_rate := 0; end if; return v_tax_type_rate; end; --add by jack wu 2014-10-23 procedure insert_contract_item_detail(p_contract_id number, p_user_id number) as v_item_detail_id number; v_count number; --租赁物的数量 v_gps_install_flag varchar2(30); v_con_contract_rec con_contract%rowtype; v_external_flag varchar2(30); begin --DELETE FROM con_contract_lease_item WHERE contract_id = p_contract_id; ---modify by zlf v_con_contract_rec := get_contract_rec(p_contract_id => p_contract_id, p_user_id => p_user_id); v_gps_install_flag := 'Y'; --商业模式为管理人员,不用装gps if v_con_contract_rec.lease_channel = '02' then v_gps_install_flag := 'N'; end if; --商业模式为试乘试驾时,体系内的可以不装gps,体系外的要装 v_external_flag := hls_bp_credit_pkg.get_external_business_flag(p_contract_id => p_contract_id); if v_external_flag = 'N' and v_con_contract_rec.lease_channel = '00' then v_gps_install_flag := 'N'; end if; for cur_item in (select ccli.contract_lease_item_id, ccli.contract_id, ccli.frame_for_prj, ccli.engine_for_prj, nvl(ccli.quantity, 1) quantity from con_contract_lease_item ccli where ccli.contract_id = p_contract_id) loop select count(*) into v_count from con_contract_item_detail t where t.contract_lease_item_id = cur_item.contract_lease_item_id; if v_count < cur_item.quantity then for cur_num in v_count + 1 .. cur_item.quantity loop select con_contract_item_detail_s.nextval into v_item_detail_id from dual; --modify by zlf --项目类租赁物导入可能有车架号和发动机号 insert into con_contract_item_detail t (item_detail_id, contract_lease_item_id, contract_id, line_number, status, item_frame_number, item_engine_number, gps_install_flag, created_by, creation_date, last_updated_by, last_update_date) values (v_item_detail_id, cur_item.contract_lease_item_id, p_contract_id, cur_num, 'NORMAL', cur_item.frame_for_prj, cur_item.engine_for_prj, v_gps_install_flag, p_user_id, sysdate, p_user_id, sysdate); end loop; elsif v_count > cur_item.quantity then delete from con_contract_item_detail t where t.contract_lease_item_id = cur_item.contract_lease_item_id and t.line_number > cur_item.quantity; end if; end loop; end; procedure save_contract_check(p_project_rec prj_project%rowtype, p_user_id number) is v_quantity number; v_exist_count number; v_vehicles_number number; e_lease_item_check_error exception; e_lease_item_vehicles_error exception; begin select sum(pi.quantity), nvl(sum(pi.vehicles_number), 0) into v_quantity, v_vehicles_number from prj_project_lease_item pi where pi.project_id = p_project_rec.project_id; /* SELECT COUNT(1) INTO v_exist_count FROM con_contract_lease_item ci WHERE ci.project_lease_item_id IN (SELECT pi.project_lease_item_id FROM prj_project_lease_item pi WHERE pi.project_id = p_project_rec.project_id); IF v_quantity <= v_exist_count THEN RAISE e_lease_item_check_error; END IF;*/ --hongquan.dai 20170110 保理不需要校验 --remove by chenlingfeng for ds 2018-7-12 16:26:17 /*If p_project_rec.division <> '09' Then If v_vehicles_number <= 0 Then Raise e_lease_item_vehicles_error; End If; End If;*/ exception when e_lease_item_check_error then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CON_CONTRACT_PKG.LEASE_CHECK_ERROR', p_created_by => p_user_id, p_package_name => 'CON_CONTRACT_PKG', p_procedure_function_name => 'SAVE_CONTRACT_CHECK'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); when e_lease_item_vehicles_error then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CON_CONTRACT_PKG.LEASE_VEHICLES_ERROR', p_created_by => p_user_id, p_package_name => 'CON_CONTRACT_PKG', p_procedure_function_name => 'SAVE_CONTRACT_CHECK'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end; --复制项目层的保理应收账款信息 honguqan.dai 20180116 procedure save_contract_factoring_info(p_project_id number, p_contract_id number, p_user_id number) is r_contract_factoring_info contract_factoring_info%rowtype; begin for rec in (select * from prj_project_factor_info t where t.project_id = p_project_id) loop r_contract_factoring_info.factor_contract_id := contract_factoring_info_s.nextval; r_contract_factoring_info.creation_date := sysdate; r_contract_factoring_info.created_by := p_user_id; r_contract_factoring_info.last_update_date := sysdate; r_contract_factoring_info.last_updated_by := p_user_id; r_contract_factoring_info.contract_id := p_contract_id; r_contract_factoring_info.business_type := rec.business_type; r_contract_factoring_info.accounts_amount := rec.accounts_amount; r_contract_factoring_info.accounts_object := rec.accounts_object; r_contract_factoring_info.policy_amount := rec.policy_amount; r_contract_factoring_info.insurance_company := rec.insurance_company; r_contract_factoring_info.note := rec.note; insert into contract_factoring_info values r_contract_factoring_info; end loop; end; procedure save_contract_from_project(p_project_id number, p_contract_seq number, p_bp_contract_seq number, p_con_document_type varchar2, p_con_contract_name varchar2, p_billing_method varchar2, p_contract_id out number, p_contract_number out varchar2, p_user_id number) is v_project_rec prj_project%rowtype; v_contract_id number; r_calculator_hd_rec hls_fin_calculator_hd%rowtype; v_calc_session_id number; e_project_status_err exception; e_fund_not_reverved exception; e_quotation_status_error exception; e_lock_error exception; v_document_type varchar2(30); v_document_category varchar2(30); pragma exception_init(e_lock_error, -54); v_ref_v02 varchar2(30); v_calc_successful varchar2(30); v_warning_message varchar2(2000); v_exists_flag varchar2(5); v_insurance_method con_contract.insurance_method%type; v_insurer_id number; v_exists number; v_record_id number; begin select * into v_project_rec from prj_project where project_id = p_project_id for update nowait; --状态检查 --remove by chenlingfeng for ds 2018-7-12 16:18:06 /*If Not v_project_rec.project_status In ('APPROVED', 'RECONSIDER') Then Raise e_project_status_err; End If;*/ --DERON CHECK SOMTHING if v_project_rec.division <> '02' then --库容业务不校验 modify zxf save_contract_check(p_project_rec => v_project_rec, p_user_id => p_user_id); end if; if v_project_rec.division <> '02' and v_project_rec.division <> '09' then --modify by lpc for deron bisiness_division select d.ref_v02 into v_ref_v02 from hls_division d where d.division = v_project_rec.division; update prj_project p set p.bisiness_division = v_ref_v02 where p.project_id = p_project_id; --end end if; --dbms_output.put_line(v_project_rec.project_status); -- if nvl(v_project_rec.reservation_status, 'NOT') != 'RESERVED' then -- raise e_fund_not_reverved; -- end if; --状态检查 v_contract_id := get_contract_id; --插入报价表 同时插入 contract,contract_cashflow -- save_quotation(p_project_rec => v_project_rec, p_contract_id => v_contract_id, p_contract_seq => p_contract_seq, p_con_calc_session_id => v_calc_session_id, p_user_id => p_user_id); --复制项目层的保理应收账款信息 honguqan.dai 20180116 if v_project_rec.division = '09' then save_contract_factoring_info(p_project_id => p_project_id, p_contract_id => v_contract_id, p_user_id => p_user_id); end if; -- select * into r_calculator_hd_rec from hls_fin_calculator_hd where calc_session_id = v_calc_session_id; --合同序号和BP合同序号不一致,更新BP合同序号 update_seq_check_different(p_project_id => p_project_id, p_contract_seq => p_contract_seq, p_bp_contract_seq => p_bp_contract_seq, p_user_id => p_user_id); --save contract save_contract(p_project_rec => v_project_rec, p_calculator_hd_rec => r_calculator_hd_rec, p_contract_id => v_contract_id, p_contract_seq => p_contract_seq, p_con_document_type => p_con_document_type, p_con_contract_name => p_con_contract_name, p_billing_method => nvl(p_billing_method, r_calculator_hd_rec.billing_method), p_con_contract_number => p_contract_number, p_user_id => p_user_id); p_contract_id := v_contract_id; --modify by lpc 9874 for deron 项目拆分合同,生成合同之后重新计算合同报价 /*IF v_project_rec.division NOT IN ('02', '09') THEN IF r_calculator_hd_rec.price_list = 'DR_PRICE_25' THEN --进来先把行表的数据插入到临时表中,如果计算成功之后则删除数据 INSERT INTO hls_fin_calculator_ln_temp SELECT * FROM hls_fin_calculator_ln WHERE calc_session_id = v_calc_session_id; --重算报价 hls_fin_calculator_itfc_pkg.calculate(p_calc_session_id => v_calc_session_id, p_recreate_h_formula => 'Y', p_recreate_l_formula => 'Y', p_calc_successful => v_calc_successful, p_warning_message => v_warning_message, p_user_id => p_user_id); --更新报价行配置表的本金和利息 FOR c_rec IN (SELECT lp.times, lp.principal, lp.interest FROM hls_fin_calculator_ln_temp lp WHERE lp.calc_session_id = v_calc_session_id AND lp.times <> 0) LOOP IF c_rec.times <> r_calculator_hd_rec.lease_times THEN IF c_rec.interest IS NULL THEN --如果利息为空就不更新 UPDATE hls_fin_calculator_ln_formula lf SET lf.principal = c_rec.principal WHERE lf.times = c_rec.times AND lf.calc_session_id = v_calc_session_id; ELSE UPDATE hls_fin_calculator_ln_formula lf SET lf.principal = c_rec.principal, lf.interest = c_rec.interest WHERE lf.times = c_rec.times AND lf.calc_session_id = v_calc_session_id; END IF; ELSIF c_rec.times = r_calculator_hd_rec.lease_times THEN --任意现金流,如果是最后一期的逻辑则不更新本金,用来修正现金流 IF c_rec.interest IS NOT NULL THEN UPDATE hls_fin_calculator_ln_formula lf SET lf.interest = c_rec.interest WHERE lf.times = c_rec.times AND lf.calc_session_id = v_calc_session_id; END IF; END IF; END LOOP; --更新完之后删除临时表信息并且计算报价 DELETE hls_fin_calculator_ln_temp WHERE calc_session_id = v_calc_session_id; hls_fin_calculator_itfc_pkg.calculate(p_calc_session_id => v_calc_session_id, p_recreate_h_formula => 'N', p_recreate_l_formula => 'N', p_calc_successful => v_calc_successful, p_warning_message => v_warning_message, p_user_id => p_user_id); ELSE --报价方案重算计 modify by lpc 9874 --remove by chenlingfeng for ds 2018-7-12 18:01:40 \*hls_fin_calculator_itfc_pkg.calculate(p_calc_session_id => v_calc_session_id, p_recreate_h_formula => 'Y', p_recreate_l_formula => 'Y', p_calc_successful => v_calc_successful, p_warning_message => v_warning_message, p_user_id => p_user_id);*\ IF r_calculator_hd_rec.calc_successful = 'N' THEN RAISE e_quotation_status_error; END IF; END IF; END IF;*/ hls_document_flow_pkg.insert_document_flow(p_doc_category => 'CONTRACT', p_doc_id => v_contract_id, p_doc_line_id => null, p_doc_number => p_contract_number, p_source_doc_category => 'PROJECT', p_source_doc_id => p_project_id, p_source_doc_line_id => null, p_source_doc_number => v_project_rec.project_number, p_user_id => p_user_id); --save contract_cashflow save_contract_cashflow(p_contract_id => v_contract_id, p_fin_calculator_hd_rec => r_calculator_hd_rec, p_user_id => p_user_id); /*********德融二开程序 计算不含税金额 ********/ /*update_cashflow_net_amount(p_contract_id => v_contract_id, p_user_id => p_user_id);*/ /********************end******************/ --插入合同bp表 save_contract_bp(p_project_id => v_project_rec.project_id, p_contract_id => v_contract_id, p_contract_seq => p_contract_seq, p_user_id => p_user_id); --add by chenlingfeng --加一个默认的保险公司 select cc.insurance_method into v_insurance_method from con_contract cc where cc.contract_id = v_contract_id; if v_insurance_method = 'DS' then begin select 1 into v_exists from dual where exists (select 1 from con_contract_bp t where t.bp_category = 'INSURER' and t.contract_id = p_contract_id); exception when no_data_found then begin select h.bp_id into v_insurer_id from hls_bp_master h where h.bp_category = 'INSURER' and rownum = 1; v_record_id := con_contract_bp_s.nextval; hls_document_transfer_pkg.doc_to_doc(p_from_doc_table => 'HLS_BP_MASTER', p_from_doc_pk => v_insurer_id, p_to_doc_table => 'CON_CONTRACT_BP', p_to_doc_pk => v_record_id, p_to_doc_column_1 => 'CONTRACT_ID', p_to_doc_column_1_value => p_contract_id, p_copy_method => hls_document_transfer_pkg.c_doc_to_history, p_user_id => p_user_id); exception when no_data_found then null; end; end; end if; --end --通过项目invoice_agent_id插入合同代理商BP --modify by zhuxianfei 20180731 注释原因 生成合同经销商重复报错 /*save_con_bp_from_agent(p_project_id => v_project_rec.project_id, p_contract_id => v_contract_id, p_contract_seq => p_contract_seq, p_user_id => p_user_id);*/ save_ast_from_prj(p_project_id => v_project_rec.project_id, p_contract_id => v_contract_id, p_contract_seq => p_contract_seq, p_user_id => p_user_id); --插入hls_lease_item并更新PRJ_PROJECT_LEASE_ITEM save_hls_lease_item(p_project_id => v_project_rec.project_id, p_contract_seq => p_contract_seq, p_user_id => p_user_id); --插入hls_mortgage并更新PRJ_PROJECT_MORTGAGE save_hls_mortgage(p_project_id => v_project_rec.project_id, p_contract_seq => p_contract_seq, p_user_id => p_user_id); --插入合同lease_item表 save_contract_lease_item(p_project_id => v_project_rec.project_id, p_contract_id => v_contract_id, p_contract_seq => p_contract_seq, p_user_id => p_user_id); --add by jack wu 2014-10-23 /*insert_contract_item_detail(p_contract_id => p_contract_id, p_user_id => p_user_id);*/ --从合同lease_item到合同bp save_con_bp_from_lease_item(p_contract_id => v_contract_id, p_user_id => p_user_id); --从项目PRJ_PROJECT_ACT_CTRLER_HD到合同bp save_con_bp_from_act_ctrler(p_project_id => v_project_rec.project_id, p_contract_id => v_contract_id, p_contract_seq => p_contract_seq, p_user_id => p_user_id); --从项目PRJ_PROJECT_MORTGAGE到合同bp save_con_bp_from_mortgage(p_project_id => v_project_rec.project_id, p_contract_id => v_contract_id, p_contract_seq => p_contract_seq, p_user_id => p_user_id); --con_contract_cf_item; save_contract_cf_item(p_contract_id => v_contract_id, p_company_id => v_project_rec.company_id, p_user_id => p_user_id); --con_contract_billing_method save_contract_billing_method(p_contract_id => v_contract_id, p_billing_method => nvl(p_billing_method, r_calculator_hd_rec.billing_method), p_user_id => p_user_id); --更新项目的bp表 update_prj_bp_after_save(p_project_id => v_project_rec.project_id, p_user_id => p_user_id); --更新项目的prj_project_act_ctrler_hd update_act_ctrler_after_save(p_project_id => v_project_rec.project_id, p_user_id => p_user_id); --add by xuls 2016-12-24 更新合同担保人序号 update_contract_bp_seq(p_contract_id, 'GUARANTOR'); --插入项目操作历史 hls_doc_operate_history_pkg.insert_doc_operate_history(p_document_category => 'PROJECT', p_document_id => p_project_id, p_operation_code => 'CON_CREATED', p_user_id => p_user_id, p_operation_time => sysdate, p_description => null); --插入合同操作历史 hls_doc_operate_history_pkg.insert_doc_operate_history(p_document_category => 'CONTRACT', p_document_id => v_contract_id, p_operation_code => hls_doc_operate_history_pkg.c_generate, p_user_id => p_user_id, p_operation_time => sysdate, p_description => null); --2014-10-30 for doc_status select t.document_type, t.document_category into v_document_type, v_document_category from prj_project t where t.project_id = p_project_id; yonda_doc_history_pkg.yonda_insert_doc_status(p_document_id => p_project_id, p_document_type => v_document_type, p_document_category => v_document_category, p_doc_status => yonda_doc_history_pkg.yonda_prj_con_create, p_instance_id => null, p_user_id => p_user_id); select t.document_type, t.document_category into v_document_type, v_document_category from con_contract t where t.contract_id = v_contract_id; yonda_doc_history_pkg.yonda_insert_doc_status(p_document_id => v_contract_id, p_document_type => v_document_type, p_document_category => v_document_category, p_doc_status => yonda_doc_history_pkg.yonda_con_create, p_instance_id => null, p_user_id => p_user_id); --2017年12月07日10:16:07 注释for Deron /* --插入合同打印匹配条件的other_tem_clause写死 --管理人员汽车租赁 --汽车类报价信息存在lease_item上 SELECT * INTO v_lease_item_rec FROM prj_project_lease_item a WHERE a.project_id = p_project_id; IF v_project_rec.lease_channel = '02' AND v_project_rec.document_type = 'CARLS' THEN --年付 IF v_lease_item_rec.annual_pay_times = '1' THEN UPDATE con_contract a SET a.other_tmpt_clause = '10' WHERE a.contract_id = v_contract_id; END IF; --按月 IF v_lease_item_rec.annual_pay_times = '12' THEN IF v_lease_item_rec.lease_start_date IS NOT NULL AND v_lease_item_rec.pmt IS NOT NULL THEN --一月不等 UPDATE con_contract a SET a.other_tmpt_clause = '20' WHERE a.contract_id = v_contract_id; ELSE --月平均 UPDATE con_contract a SET a.other_tmpt_clause = '30' WHERE a.contract_id = v_contract_id; END IF; END IF; END IF; --零售业务一年无息(汽车) IF v_project_rec.lease_channel = '01' AND v_project_rec.document_type = 'CARLS' THEN IF (to_number(v_lease_item_rec.lease_times) / to_number(v_lease_item_rec.annual_pay_times)) = 1 THEN UPDATE con_contract a SET a.other_tmpt_clause = '40' WHERE a.contract_id = v_contract_id; END IF; END IF;*/ --add by Harry 9952 2018/10/15 更新直销手续费标志 begin select 'Y' into v_exists_flag from con_contract_bp b where b.contract_id = v_contract_id and b.bp_category = 'AGENT' and b.bp_name like '%直销%' and rownum = 1; if v_exists_flag = 'Y' then update con_contract c set c.direct_lease_charge_flag = 'Y' where c.contract_id = v_contract_id; end if; exception when no_data_found then null; end; exception when e_fund_not_reverved then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'PRJ_PROJECT_PKG.FUND_NOT_RESERVED', p_created_by => p_user_id, p_package_name => 'CON_CONTRACT_PKG', p_procedure_function_name => 'SAVE_CONTRACT_FROM_PROJECT'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); when e_quotation_created_err then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'PRJ_PROJECT_PKG.QUOTATION_IS_CREATED_ERROR', p_created_by => p_user_id, p_package_name => 'CON_CONTRACT_PKG', p_procedure_function_name => 'SAVE_CONTRACT_FROM_PROJECT'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); /* when e_quotation_status_err then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'PRJ_PROJECT_PKG.QUOTATION_STATUS_ERROR', p_created_by => p_user_id, p_package_name => 'CON_CONTRACT_PKG', p_procedure_function_name => 'SAVE_CONTRACT_FROM_PROJECT');*/ raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); when e_get_contract_number_err then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CON_CONTRACT_PKG.GET_CON_CONTRACT_NUMBER_ERROR', p_created_by => p_user_id, p_package_name => 'CON_CONTRACT_PKG', p_procedure_function_name => 'SAVE_CONTRACT_FROM_PROJECT'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); when e_project_status_err then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CON_CONTRACT_PKG.PRJ_PROJECT_STATUS_CHECK_ERROR', p_created_by => p_user_id, p_package_name => 'CON_CONTRACT_PKG', p_procedure_function_name => 'SAVE_CONTRACT_FROM_PROJECT'); --DBMS_OUTPUT.PUT_LINE('e_project_status_err'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); when e_lock_error then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CON_CONTRACT_PKG.LOCK_PRJ_PROJECT_ERROR', p_created_by => p_user_id, p_package_name => 'CON_CONTRACT_PKG', p_procedure_function_name => 'SAVE_CONTRACT_FROM_PROJECT'); --DBMS_OUTPUT.PUT_LINE('e_lock_error'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); when e_bp_id_tenant_err then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CON_CONTRACT_PKG.BP_ID_TENANT_ERROR', p_created_by => p_user_id, p_package_name => 'CON_CONTRACT_PKG', p_procedure_function_name => 'SAVE_CONTRACT_FROM_PROJECT'); --DBMS_OUTPUT.PUT_LINE('e_bp_id_tenant_err'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); when e_calculator_not_success_err then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CON_CONTRACT_PKG.CALCULATOR_NOT_SUCCESSFUL_ERROR', p_created_by => p_user_id, p_package_name => 'CON_CONTRACT_PKG', p_procedure_function_name => 'SAVE_CONTRACT_FROM_PROJECT'); --DBMS_OUTPUT.PUT_LINE('e_bp_id_tenant_err'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); when e_calc_method_notfound_err then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CON_CONTRACT_PKG.CALC_METHOD_NOTFOUND_ERROR', p_created_by => p_user_id, p_package_name => 'CON_CONTRACT_PKG', p_procedure_function_name => 'SAVE_CONTRACT_FROM_CALCULATOR'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); when e_quotation_status_error then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CON_CONTRACT_PKG.QUOTATION_STATUS_ERROR', p_created_by => p_user_id, p_package_name => 'CON_CONTRACT_PKG', p_procedure_function_name => 'SAVE_CONTRACT_FROM_CALCULATOR'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); 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_PKG', p_procedure_function_name => 'SAVE_CONTRACT_FROM_PROJECT'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end; procedure save_contract_from_calculator(p_contract_id in number, p_calc_session_id in number, p_save_contract_hd in varchar2 default 'Y', p_save_contract_ln in varchar2 default 'Y', p_user_id in number) is r_calculator_hd_rec hls_fin_calculator_hd%rowtype; v_adj_amt number; v_0_disburse_cf_id number; v_0_disburse_amt number; v_before_0_disburse_count number; v_exists number; v_bill_method varchar2(30); begin select * into r_calculator_hd_rec from hls_fin_calculator_hd where calc_session_id = p_calc_session_id; if r_calculator_hd_rec.calc_successful = 'N' then raise e_calculator_not_success_err; end if; if r_calculator_hd_rec.calc_method is null then raise e_calc_method_notfound_err; -- 合同的计算(本利分摊)方式不能为空 end if; begin select 1 into v_exists from dual where exists (select 1 from con_contract_cashflow where contract_id = p_contract_id and times > 0 and (write_off_flag in ('PARTIAL', 'FULL') or received_amount is not null or received_principal is not null or received_interest is not null)); --有过收付款的cf行,不能进行计算 sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CON_CONTRACT_PKG.CONTRACT_SAVE_EXISTS_WRITEOFF_ERROR', p_created_by => p_user_id, p_package_name => 'CON_CONTRACT_PKG', p_procedure_function_name => 'SAVE_CONTRACT_FROM_CALCULATOR'); --DBMS_OUTPUT.PUT_LINE('e_bp_id_tenant_err'); 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; --单据复制 if p_save_contract_hd = 'Y' then select cc.billing_method into v_bill_method from con_contract cc where cc.contract_id = p_contract_id; hls_document_transfer_pkg.doc_to_doc(p_from_doc_table => 'HLS_FIN_CALCULATOR_HD', p_from_doc_pk => p_calc_session_id, p_to_doc_table => 'CON_CONTRACT', p_to_doc_pk => p_contract_id, p_function_code => null, p_function_usage => null, p_to_doc_column_1 => 'BUSINESS_TYPE', p_to_doc_column_1_value => r_calculator_hd_rec.business_type, p_to_doc_column_2 => 'BILLING_METHOD', p_to_doc_column_2_value => v_bill_method, p_user_id => p_user_id); end if; if p_save_contract_ln = 'Y' then delete from con_contract_cashflow where contract_id = p_contract_id and times > 0; begin select 1 into v_exists from dual where exists (select 1 from con_contract_cashflow where contract_id = p_contract_id and times = 0 and cf_item in ( --客户保证金 51, -- 客户保证金退还 52, -- 代理商代付保证金 508, -- 代理商代付保证金退还 509, -- 客户手续费 3, -- 代理商手续费 301) and (write_off_flag in ('PARTIAL', 'FULL') or received_amount is not null or received_principal is not null or received_interest is not null)); --有过收付款的cf行,不能进行计算 sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CON_CONTRACT_PKG.CONTRACT_SAVE_EXISTS_WRITEOFF_ERROR', p_created_by => p_user_id, p_package_name => 'CON_CONTRACT_PKG', p_procedure_function_name => 'SAVE_CONTRACT_FROM_CALCULATOR'); --DBMS_OUTPUT.PUT_LINE('e_bp_id_tenant_err'); 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; delete from con_contract_cashflow where contract_id = p_contract_id and times = 0 and cf_item in ( --客户保证金 51, -- 客户保证金退还 52, -- 代理商代付保证金 508, -- 代理商代付保证金退还 509, -- 客户手续费 3, -- 代理商手续费 301); save_contract_cashflow(p_contract_id => p_contract_id, p_fin_calculator_hd_rec => r_calculator_hd_rec, p_user_id => p_user_id); end if; --自动处理0期投放额 start --由于吉利全部使用的是5. 所以改成5, 以前是cf_item=0 begin select cashflow_id into v_0_disburse_cf_id from con_contract_cashflow where contract_id = p_contract_id and times = 0 and cf_item = 0; exception when no_data_found then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CON_CONTRACT_PKG.CONTRACT_SAVE_0_DISBURSE_NOTFOUND_ERROR', p_created_by => p_user_id, p_package_name => 'CON_CONTRACT_PKG', p_procedure_function_name => 'SAVE_CONTRACT_FROM_CALCULATOR'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end; select nvl(to_number(r_calculator_hd_rec.lease_item_amount), 0) - nvl(sum(due_amount), 0), count(1) into v_0_disburse_amt, v_before_0_disburse_count from con_contract_cashflow where contract_id = p_contract_id and cf_item = 5 and times < 0; if v_before_0_disburse_count > 0 then if v_0_disburse_amt < 0 then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CON_CONTRACT_PKG.CONTRACT_SAVE_0_DISBURSE_AMOUNT_ERROR', p_created_by => p_user_id, p_package_name => 'CON_CONTRACT_PKG', p_procedure_function_name => 'SAVE_CONTRACT_FROM_CALCULATOR'); 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_cashflow set due_amount = v_0_disburse_amt where cashflow_id = v_0_disburse_cf_id; end if; --自动处理0期投放额 end begin select 1 into v_exists from dual where exists (select 1 from con_contract_cashflow where contract_id = p_contract_id and times <= 0 and received_amount > 0 and nvl(received_amount, 0) > due_amount); --有过收付款的cf行,不能进行计算 sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CON_CONTRACT_PKG.CONTRACT_SAVE_WRITEOFF_AMOUNT_ERROR', p_created_by => p_user_id, p_package_name => 'CON_CONTRACT_PKG', p_procedure_function_name => 'SAVE_CONTRACT_FROM_CALCULATOR'); --DBMS_OUTPUT.PUT_LINE('e_bp_id_tenant_err'); 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; exception when e_calculator_not_success_err then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CON_CONTRACT_PKG.CALCULATOR_NOT_SUCCESSFUL_ERROR', p_created_by => p_user_id, p_package_name => 'CON_CONTRACT_PKG', p_procedure_function_name => 'SAVE_CONTRACT_FROM_CALCULATOR'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); when e_calc_method_notfound_err then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CON_CONTRACT_PKG.CALC_METHOD_NOTFOUND_ERROR', p_created_by => p_user_id, p_package_name => 'CON_CONTRACT_PKG', p_procedure_function_name => 'SAVE_CONTRACT_FROM_CALCULATOR'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); 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_PKG', p_procedure_function_name => 'SAVE_CONTRACT_FROM_CALCULATOR'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end; procedure contract_print(p_contract_id number, p_content_id number, p_user_id number) is r_con_contract_rec con_contract%rowtype; r_con_content_rec con_contract_content%rowtype; v_document_type varchar2(30); v_document_category varchar2(30); begin r_con_contract_rec := get_contract_rec(p_contract_id => p_contract_id, p_user_id => p_user_id); con_contract_content_pkg.get_contract_content_rec(p_content_id => p_content_id, p_user_id => p_user_id, p_contract_content_rec => r_con_content_rec); /*check contract status*/ con_contract_verify_pkg.contract_verify(p_contract_rec => r_con_contract_rec, p_action_type => con_contract_verify_pkg.c_print, p_user_id => p_user_id); ---modify by zlf 2015-1-6 /*UPDATE con_contract SET print_status = 'PRINTED', print_times = nvl(print_times, 0) + 1, first_print_date = SYSDATE, last_update_date = SYSDATE, last_updated_by = p_user_id WHERE contract_id = p_contract_id;*/ update con_contract_content t set t.content_print_flag = 'Y', t.last_update_date = sysdate, t.last_updated_by = p_user_id where t.content_id = p_content_id; --插入合同操作历史 hls_doc_operate_history_pkg.insert_doc_operate_history(p_document_category => 'CONTRACT', p_document_id => p_contract_id, p_operation_code => 'CON_PRINT', p_user_id => p_user_id, p_operation_time => sysdate, p_description => null); --2014-10-30 for doc_status select t.document_type, t.document_category into v_document_type, v_document_category from con_contract t where t.contract_id = p_contract_id; yonda_doc_history_pkg.yonda_insert_doc_status(p_document_id => p_contract_id, p_document_type => v_document_type, p_document_category => v_document_category, p_doc_status => yonda_doc_history_pkg.yonda_con_doc_printed, p_instance_id => null, 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_PKG', p_procedure_function_name => 'CONTRACT_PRINT'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end; procedure contract_print(p_contract_id number, p_content_id number, p_file_path varchar2, p_file_name varchar2, p_type varchar2, p_user_id number) is r_con_contract_rec con_contract%rowtype; r_con_content_rec con_contract_content%rowtype; v_count number; begin r_con_contract_rec := get_contract_rec(p_contract_id => p_contract_id, p_user_id => p_user_id); con_contract_content_pkg.get_contract_content_rec(p_content_id => p_content_id, p_user_id => p_user_id, p_contract_content_rec => r_con_content_rec); /*check contract status*/ con_contract_verify_pkg.contract_verify(p_contract_rec => r_con_contract_rec, p_action_type => con_contract_verify_pkg.c_print, p_user_id => p_user_id); update con_contract_content t set t.content_print_flag = 'Y', t.file_path = p_file_path, t.file_name = p_file_name || '.' || p_type, t.last_update_date = sysdate, t.last_updated_by = p_user_id, t.print_date = sysdate where t.content_id = p_content_id; select count(*) into v_count from con_contract_content t where t.contract_id = p_contract_id and nvl(t.content_print_flag, 'N') = 'N'; if v_count = 0 then update con_contract set print_status = 'PRINTED', print_times = nvl(print_times, 0) + 1, last_update_date = sysdate, last_updated_by = p_user_id where contract_id = p_contract_id; end if; --插入合同操作历史 hls_doc_operate_history_pkg.insert_doc_operate_history(p_document_category => 'CONTRACT', p_document_id => p_contract_id, p_operation_code => 'CON_PRINT', p_user_id => p_user_id, p_operation_time => sysdate, p_description => null); 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_PKG', p_procedure_function_name => 'CONTRACT_PRINT'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end; procedure contract_sign(p_contract_id number, p_exchange_rate_quotation varchar2, p_exchange_rate_type varchar2, p_exchange_rate number, p_signing_date date, p_user_id number) is r_con_contract_rec con_contract%rowtype; v_calc_successful varchar2(1); v_warning_message varchar2(2000); v_approval_method varchar2(30); e_calc_quotation_err exception; begin /*r_con_contract_rec := get_contract_rec(p_contract_id => p_contract_id, p_user_id => p_user_id); * / /*check contract status*/ /* con_contract_verify_pkg.contract_verify(p_contract_rec => r_con_contract_rec, p_action_type => con_contract_verify_pkg.c_sign, p_user_id => p_user_id);*/ /*update contract status*/ /* update con_contract set contract_status = 'SIGN', signing_date = p_signing_date, last_updated_by = p_user_id, last_update_date = sysdate where contract_id = p_contract_id;*/ /*二开程序*/ /*con_contract_custom_pkg.after_contract_sign(p_contract_id => p_contract_id, p_signing_date => p_signing_date, p_user_id => p_user_id);*/ --插入合同操作历史 /*hls_doc_operate_history_pkg.insert_doc_operate_history(p_document_category => 'CONTRACT', p_document_id => p_contract_id, p_operation_code => 'CON_SIGN', p_user_id => p_user_id, p_operation_time => sysdate, p_description => null);*/ --add by:zhangxing5129 --add date :2014-05-26 --走工作流签约 r_con_contract_rec := get_contract_rec(p_contract_id => p_contract_id, p_user_id => p_user_id); select approval_method into v_approval_method from hls_document_type where document_category = r_con_contract_rec.document_category and document_type = r_con_contract_rec.document_type; if v_approval_method = 'WORK_FLOW' then con_contract_sign_workflow_pkg.start_workflow(p_contract_id => p_contract_id, p_user_id => p_user_id, p_signing_date => p_signing_date); else update con_contract c set c.contract_status = 'APPROVING', c.signing_date = p_signing_date, c.last_updated_by = p_user_id, c.last_update_date = sysdate where c.contract_id = p_contract_id; end if; exception when e_calc_quotation_err then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CON_CONTRACT_PKG.CALCULATOR_NOT_SUCCESSFUL_ERROR', p_created_by => p_user_id, p_package_name => 'CON_CONTRACT_PKG', p_procedure_function_name => 'CONTRACT_SIGN'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); 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_PKG', p_procedure_function_name => 'CONTRACT_SIGN'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end; --合同起租 procedure contract_incept(p_contract_id number, p_exchange_rate_quotation varchar2, p_exchange_rate_type varchar2, p_exchange_rate number, p_inception_of_lease date, p_base_rate_new number default null, p_Refresh_cashflow_only_flag varchar2 default null, --add by chenlingfeng p_user_id number) is r_con_contract_rec con_contract%rowtype; v_calc_successful varchar2(1); v_warning_message varchar2(2000); v_change_req_id number; e_period_notfound_err exception; e_calc_quotation_err exception; v_inception_of_lease date; v_inception_50_flag varchar2(1); v_first_inception_of_lease date; v_lease_end_date date; v_repayment_date_day number; v_first_pay_date date; e_full_write_off_error exception; v_count_cf_item number; begin /***************************************************************************/ r_con_contract_rec := get_contract_rec(p_contract_id => p_contract_id, p_user_id => p_user_id); /*check contract status*/ con_contract_verify_pkg.contract_verify(p_contract_rec => r_con_contract_rec, p_action_type => con_contract_verify_pkg.c_incept, p_user_id => p_user_id); --add by Harry 9952 2018/10/16 --若不含直销手续费,则需要完全核销后才可以执行; 若含直销手续费,则代理商手续费可以不必完全核销 /*select count(*) into v_count_301 from con_contract_cashflow cc where cc.contract_id = p_contract_id and cc.cf_item = 301 and cc.times = 0; if v_count_301 <> 0 then if r_con_contract_rec.direct_lease_charge_flag = 'Y' then null; else select count(*) into v_write_off_count from con_contract_cashflow cc where cc.contract_id = p_contract_id and cc.cf_item = 301 and cc.times = 0 and cc.write_off_flag <> 'FULL'; if v_write_off_count <> 0 then raise e_full_write_off_error; end if; end if; end if;*/ /*save history*/ /*con_contract_history_pkg.create_history(p_contract_id => p_contract_id, p_usage_code => 'CONTRACT_INCEPT', p_data_class => 'HISTORY', p_user_id => p_user_id); */ --************************************************************************* --改为在前台判断逢五逢零 /* --获取首期还款日 SELECT MAX(ch.first_payment_date) INTO v_first_inception_of_lease FROM csh_payment_req_hd ch WHERE ch.contract_id = p_contract_id AND ch.approval_status = 'APPROVED' AND EXISTS (SELECT 1 FROM csh_payment_req_ln cl, con_contract_cashflow ccc WHERE cl.payment_req_id = ch.payment_req_id AND cl.ref_doc_line_id = ccc.cashflow_id AND ccc.write_off_flag = 'FULL' AND ccc.cf_item = 5); --获取放款日 SELECT MAX(ch.loan_date) INTO v_inception_of_lease FROM csh_payment_req_hd ch WHERE ch.contract_id = p_contract_id AND ch.approval_status = 'APPROVED' AND EXISTS (SELECT 1 FROM csh_payment_req_ln cl, con_contract_cashflow ccc WHERE cl.payment_req_id = ch.payment_req_id AND cl.ref_doc_line_id = ccc.cashflow_id AND ccc.write_off_flag = 'FULL' AND ccc.cf_item = 5);*/ v_inception_of_lease := nvl(p_inception_of_lease, r_con_contract_rec.inception_of_lease); /*确认的还款日逻辑是: 交付日期在1号-20号的合同,还款日是次月15号; 交付日期在21号到月底的合同,还款日是次次月的8号。 判断 可从price_list上判断 为DS_PRICE_60时 ,起租刷新日期 为 1-20号为次月的8号, 21号以后为次次月8号 add by chenlingfeng for ds 2018年8月2日*/ if r_con_contract_rec.price_list = 'DS_PRICE_60' then v_first_inception_of_lease := con_contract_pkg. get_inception_of_lease_8(v_inception_of_lease); else v_first_inception_of_lease := con_contract_pkg.get_inception_of_lease(v_inception_of_lease); -- end if; v_repayment_date_day := to_number(to_char(v_first_inception_of_lease, 'dd')); --更新现金流 create_cf_date(p_contract_rec => r_con_contract_rec, p_inception_of_lease => v_first_inception_of_lease, p_inception_50_flag => v_inception_50_flag); --modify by lpc 9874 for deron 更新车款投放的起租日期 /*UPDATE con_contract_cashflow SET calc_date = v_first_inception_of_lease, due_date = v_first_inception_of_lease, fin_income_date = v_first_inception_of_lease, ln_user_col_d01 = v_first_inception_of_lease WHERE contract_id = p_contract_id AND cf_item = 5 AND times = 0;*/ --modfiy by chenlingfeng 更新0期所有现金流 update con_contract_cashflow set calc_date = r_con_contract_rec.lease_execution_date, due_date = r_con_contract_rec.lease_execution_date, fin_income_date = r_con_contract_rec.lease_execution_date, ln_user_col_d01 = r_con_contract_rec.lease_execution_date where contract_id = p_contract_id and times = 0; --end con_contract_custom_pkg.before_contract_incept(p_contract_id => p_contract_id, p_inception_of_lease => v_inception_of_lease, p_lease_execution_date => r_con_contract_rec.lease_execution_date, p_user_id => p_user_id); --v_first_pay_date select min(ccc.due_date) into v_first_pay_date from con_contract_cashflow ccc where ccc.cf_status = 'RELEASE' and ccc.cf_direction = 'INFLOW' and ccc.due_date >= r_con_contract_rec.lease_execution_date and ccc.times <> 0 and ccc.contract_id = p_contract_id; /*update contract status*/ update con_contract set first_pay_date = v_first_pay_date, --v_first_inception_of_lease, last_updated_by = p_user_id, last_update_date = sysdate where contract_id = p_contract_id; if p_Refresh_cashflow_only_flag = 'Y' then return; end if; begin select max(ccc.calc_date) into v_lease_end_date from con_contract_cashflow ccc where ccc.contract_id = p_contract_id and ccc.cf_item = '1' and ccc.cf_direction <> 'NONCASH'; exception when others then null; end; /*update contract status*/ update con_contract set contract_status = 'INCEPT', exchange_rate_quotation = nvl(p_exchange_rate_quotation, 'DIRECT QUOTATION'), exchange_rate_type = nvl(p_exchange_rate_type, 'MANUAL'), exchange_rate = nvl(p_exchange_rate, 1), inception_of_lease = v_inception_of_lease, lease_start_date = v_inception_of_lease, lease_end_date = nvl(v_lease_end_date, add_months(v_first_inception_of_lease, round(r_con_contract_rec.lease_term * 12, 0)) - 1), lease_execution_flag = 'Y', first_pay_date = v_first_pay_date, --v_first_inception_of_lease, repayment_date_day = v_repayment_date_day, --还款日8 th/15 th last_updated_by = p_user_id, last_update_date = sysdate where contract_id = p_contract_id; update con_contract_cashflow set exchange_rate_quotation = nvl(p_exchange_rate_quotation, 'DIRECT QUOTATION'), exchange_rate_type = nvl(p_exchange_rate_type, 'MANUAL'), exchange_rate = nvl(p_exchange_rate, 1) where contract_id = p_contract_id; if r_con_contract_rec.currency <> gld_common_pkg.get_company_currency_code(p_company_id => r_con_contract_rec.company_id) then --重新计算计算器 update hls_fin_calculator_hd_formula set exchange_rate_quotation = nvl(p_exchange_rate_quotation, 'DIRECT QUOTATION'), exchange_rate_type = nvl(p_exchange_rate_type, 'MANUAL'), exchange_rate = nvl(p_exchange_rate, 1), inception_of_lease = to_char(p_inception_of_lease, 'YYYY-MM-DD'), lease_start_date = to_char(p_inception_of_lease, 'YYYY-MM-DD') where calc_session_id = r_con_contract_rec.calc_session_id; hls_fin_calculator_itfc_pkg.calculate(p_calc_session_id => r_con_contract_rec.calc_session_id, p_recreate_h_formula => 'N', p_recreate_l_formula => 'N', p_calc_successful => v_calc_successful, p_warning_message => v_warning_message, p_user_id => p_user_id); if v_calc_successful <> 'Y' then raise e_calc_quotation_err; end if; /*save contract from calculator*/ save_contract_from_calculator(p_contract_id => p_contract_id, p_calc_session_id => r_con_contract_rec.calc_session_id, p_save_contract_hd => 'Y', p_save_contract_ln => 'Y', p_user_id => p_user_id); end if; /* 二开程序 */ con_contract_custom_pkg.after_contract_incept(p_contract_id => p_contract_id, p_exchange_rate_quotation => p_exchange_rate_quotation, p_exchange_rate_type => p_exchange_rate_type, p_exchange_rate => p_exchange_rate, p_inception_of_lease => p_inception_of_lease, p_user_id => p_user_id); --add by chenlingfeng --把这段逻辑提到前面 /*con_contract_custom_pkg.before_contract_incept(p_contract_id => p_contract_id, p_inception_of_lease => p_inception_of_lease, p_lease_execution_date => r_con_contract_rec.lease_execution_date, p_user_id => p_user_id);*/ --modify by wcs 2014-12-2 if r_con_contract_rec.int_rate_type = 'FLOATING' then /*floating_rate_adj*/ con_floating_interest_rate_pkg.floating_rate_adj(p_contract_id => p_contract_id, p_adj_date => p_inception_of_lease, p_base_rate_new => p_base_rate_new, p_change_req_id => v_change_req_id, p_user_id => p_user_id); if v_change_req_id is not null then con_floating_interest_rate_pkg.floating_rate_adj_submit(p_change_req_id => v_change_req_id, p_user_id => p_user_id); con_floating_interest_rate_pkg.floating_rate_adj_confirm(p_change_req_id => v_change_req_id, p_user_id => p_user_id); /*else \*calc_cny_amt*\ con_floating_interest_rate_pkg.save_change_req(p_change_req_id => v_change_req_id, p_contract_rec => r_con_contract_rec, p_adj_date => p_inception_of_lease, p_base_rate_new => r_con_contract_rec.base_rate, p_adj_start_times => 1, p_user_id => p_user_id); begin select * into r_hls_fin_calc_method_rec from hls_fin_calc_method where calc_method = r_con_contract_rec.calc_method; exception when no_data_found then r_hls_fin_calc_method_rec.split_pi := 'N'; end; con_floating_interest_rate_pkg.calc_cny_amt(p_contract_rec => r_con_contract_rec, p_change_req_id => v_change_req_id, p_adj_start_times => 1, p_hls_fin_calc_method_rec => r_hls_fin_calc_method_rec, p_user_id => p_user_id); con_floating_interest_rate_pkg.floating_rate_adj_submit(p_change_req_id => v_change_req_id, p_user_id => p_user_id); con_floating_interest_rate_pkg.floating_rate_adj_confirm(p_change_req_id => v_change_req_id, p_user_id => p_user_id); */ end if; end if; /* unearned_fin_income */ /*begin select a.internal_period_num into v_start_internal_period_num from gld_periods a, fnd_companies b, gld_set_of_books c where b.company_id = r_con_contract_rec.company_id and b.set_of_books_id = c.set_of_books_id and c.period_set_code = a.period_set_code and a.adjustment_flag = 'N' and p_inception_of_lease between a.start_date and a.end_date; exception when no_data_found then raise e_period_notfound_err; end;*/ -- ?? con_unearned_fin_income_pkg.allocate_unearned_fin_income(p_contract_id => p_contract_id, p_start_internal_period_num => null, p_user_id => p_user_id); --modify by lpc 9874 for doosan 手续费分摊 --modify by chenlingfeng 添加在通用分摊中 /*SELECT COUNT(*) INTO v_count_cf_item FROM con_contract_cashflow cc WHERE cc.contract_id = p_contract_id AND cf_item IN (3, 301); IF v_count_cf_item > 0 THEN con_unearned_fin_income_pkg.fee_sharing(p_contract_id => p_contract_id, p_user_id => p_user_id); end if;*/ /* create lease_incept je */ con_contract_je_pkg.create_lease_incept_je(p_contract_id => p_contract_id, p_user_id => p_user_id); --add by chenlingfeng --发送短信 ds_send_sms_core_pkg.send_con_new_customer_sms(p_contract_id => p_contract_id, p_user_id => p_user_id); --end --插入合同操作历史 hls_doc_operate_history_pkg.insert_doc_operate_history(p_document_category => 'CONTRACT', p_document_id => p_contract_id, p_operation_code => 'CON_INCEPT', p_user_id => p_user_id, p_operation_time => sysdate, p_description => null); yonda_doc_history_pkg.yonda_insert_doc_status(p_document_id => r_con_contract_rec.contract_id, p_document_type => r_con_contract_rec.document_type, p_document_category => r_con_contract_rec.document_category, p_doc_status => yonda_doc_history_pkg.yonda_con_incept, p_instance_id => null, p_user_id => p_user_id); exception when e_calc_quotation_err then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CON_CONTRACT_PKG.CALCULATOR_NOT_SUCCESSFUL_ERROR', p_created_by => p_user_id, p_package_name => 'CON_CONTRACT_PKG', p_procedure_function_name => 'CONTRACT_INCEPT'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); when e_period_notfound_err then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CON_CONTRACT_PKG.CONTRACT_INCEPTION_PERIOD_NOTFOUND_ERROR', p_created_by => p_user_id, p_package_name => 'CON_CONTRACT_PKG', p_procedure_function_name => 'CONTRACT_INCEPT'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); when e_full_write_off_error then sys_raise_app_error_pkg.raise_sys_others_error(p_message => '代理商手续费未完全核销,请检查!', p_created_by => p_user_id, p_package_name => 'CON_CONTRACT_PKG', p_procedure_function_name => 'CONTRACT_INCEPT'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); 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_PKG', p_procedure_function_name => 'CONTRACT_INCEPT'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end; procedure contract_incept_rd_wf(p_contract_id number, p_user_id number) is begin contract_incept(p_contract_id => p_contract_id, p_exchange_rate_quotation => null, p_exchange_rate_type => null, p_exchange_rate => 1, p_inception_of_lease => trunc(sysdate), --暂时传入当前日,待需求去确认后更改 p_base_rate_new => null, p_user_id => p_user_id); end; procedure contract_update_check(p_contract_id number, p_user_id number, p_contract_number out varchar2) is r_con_contract_rec con_contract%rowtype; v_contract_number varchar2(100); v_dealer_cd varchar2(50); v_chance_no varchar2(50); v_Machine_Type varchar2(50); -- v_num number; -- e_many_rows_err exception; -- e_test_err exception; begin r_con_contract_rec := get_contract_rec(p_contract_id => p_contract_id, p_user_id => p_user_id); --执行审查无误更新时间 if r_con_contract_rec.execution_status = 'REVIEW_PASS' then update con_contract cc set cc.submit_date = nvl(cc.submit_date, trunc(sysdate)) where cc.contract_id = r_con_contract_rec.contract_id; else update con_contract cc set cc.submit_date = null where cc.contract_id = r_con_contract_rec.contract_id; end if; --更新bp 带出默认值 update con_contract_bp t set (t.bp_code, t.bp_class, t.bp_name, t.id_type, t.id_card_no, t.organization_code, t.tax_registry_num, t.cell_phone, t.id_card_address, t.SEND_ADDRESS, t.liv_street, t.phone, t.phone_2, t.bp_name_sp, t.id_no_sp, t.id_card_address_sp, t.cell_phone_sp, t.bp_name_leg, t.id_card_no_leg, t.default_value_flag) = (select t1.bp_code, t1.bp_class, t1.bp_name, t1.id_type, t1.id_card_no, t1.organization_code, t1.tax_registry_num, nvl(t.cell_phone, t1.cell_phone), nvl(t.id_card_address, t1.id_card_address), nvl(t.SEND_ADDRESS, t1.SEND_ADDRESS), nvl(t.liv_street, t1.liv_street), nvl(t.phone, t1.phone), nvl(t.phone_2, t1.phone_2), t1.bp_name_sp, nvl(t.id_no_sp, t1.id_no_sp), nvl(t.id_card_address_sp, t1.id_card_address_sp), nvl(t.cell_phone_sp, t1.cell_phone_sp), t1.bp_name_leg, nvl(t.id_card_no_leg, t1.id_card_no_leg), 'Y' from hls_bp_master t1 where t1.bp_id = t.bp_id) where t.contract_id = r_con_contract_rec.contract_id and nvl(t.default_value_flag, 'N') = 'N'; -- v_contract_number := r_con_contract_rec.contract_number; p_contract_number := v_contract_number; -- 50节点 防止并发处理 20160808 start -- 1.首保与GPS上传 --if v_num > 1 then -- raise e_many_rows_err; --end if; -- 2.附件信息 -- end /*IF r_con_contract_rec.contract_status = 'NEW' THEN RETURN; END IF;*/ begin select t2.dealer_cd, t2.chance_no, T2.Machine_Type into v_dealer_cd, v_chance_no, v_Machine_Type from con_contract t1, prj_project t2 where t1.project_id = t2.project_id and t1.contract_id = p_contract_id; ds_document_interface_pkg.dcs_insert_prj_lease_item(p_dealer_cd => v_dealer_cd, p_chance_no => v_chance_no, p_machine_type => v_Machine_Type); 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_PKG', p_procedure_function_name => 'CONTRACT_UPDATE_CHECK'); end; /*check contract status*/ con_contract_verify_pkg.contract_verify(p_contract_rec => r_con_contract_rec, p_action_type => con_contract_verify_pkg.c_update_check, p_user_id => p_user_id); aut_document_authority_pkg.insert_trx_user_authority(p_company_id => r_con_contract_rec.company_id, p_owner_user_id => r_con_contract_rec.owner_user_id, p_trx_category => 'CONTRACT', p_trx_id => p_contract_id, p_start_date => trunc(sysdate), p_end_date => to_date('30000101', 'yyyymmdd'), p_user_id => p_user_id); exception /* when e_many_rows_err then rollback; sys_raise_app_error_pkg.raise_sys_others_error(p_message => '页面存在并发,请关闭页面再操作!', p_created_by => p_user_id, p_package_name => 'CON_CONTRACT_PKG', p_procedure_function_name => 'contract_update_check'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); return;*/ 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_PKG', p_procedure_function_name => 'CONTRACT_UPDATE_CHECK'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end; procedure contract_cancel(p_contract_id number, p_user_id number) is r_con_contract_rec con_contract%rowtype; begin r_con_contract_rec := get_contract_rec(p_contract_id => p_contract_id, p_user_id => p_user_id); /*check contract status*/ con_contract_verify_pkg.contract_verify(p_contract_rec => r_con_contract_rec, p_action_type => con_contract_verify_pkg.c_cancel, p_user_id => p_user_id); /*update contract status*/ update con_contract set contract_status = 'CANCEL', last_updated_by = p_user_id, last_update_date = sysdate where contract_id = p_contract_id; --插入合同操作历史 hls_doc_operate_history_pkg.insert_doc_operate_history(p_document_category => 'CONTRACT', p_document_id => p_contract_id, p_operation_code => 'CON_CANCEL', p_user_id => p_user_id, p_operation_time => sysdate, p_description => null); 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_PKG', p_procedure_function_name => 'CONTRACT_CANCEL'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end contract_cancel; --计算期数为0的due_date procedure create_cf0_date(p_con_contract_rec con_contract%rowtype, p_due_date date, p_user_id number) is v_calc_date date; v_due_date date; v_fin_date date; begin for v_record in (select * from con_contract_cashflow t where t.contract_id = p_con_contract_rec.contract_id and t.times = 0 and t.cf_status = 'RELEASE') loop v_calc_date := p_due_date + 3; v_due_date := nextduedate(v_calc_date, 0, p_con_contract_rec.biz_day_convention); v_fin_date := v_calc_date; update con_contract_cashflow t set t.calc_date = v_calc_date, t.due_date = v_due_date, t.fin_income_date = v_fin_date, t.ln_user_col_d01 = v_calc_date, t.last_updated_by = p_user_id, t.last_update_date = sysdate where cashflow_id = v_record.cashflow_id; end loop; end; -- 签约提交 改变信审、及法务审核状态 function get_fin_amount(p_bp_id number) return number is v_fin_amount number; v_agent_type hls_bp_master.agent_type%type; begin select hm.agent_type into v_agent_type from hls_bp_master hm where hm.bp_id = p_bp_id; if v_agent_type = 'AGENT_GRT_DEPOSIT' then select sum(nvl(ccc.due_amount,0) - nvl(ccc.received_amount, 0)) into v_fin_amount from con_contract_cashflow ccc where ccc.times <> 0 and (ccc.cf_item = 1 or ccc.cf_item = 913) and ccc.cf_status = 'RELEASE' and ccc.contract_id in (select c1.contract_id from con_contract c1 where c1.bp_id_agent_level1 = p_bp_id and c1.contract_status = 'INCEPT' and c1.data_class = 'NORMAL' union all select c.contract_id from con_contract c, hls_bp_master h where c.bp_id_agent_level1 = h.bp_id and c.contract_status = 'INCEPT' and c.data_class = 'NORMAL' and h.parent_id = p_bp_id); return v_fin_amount; else return 99999999999; end if; end get_fin_amount; -- 合同维护判断是否为NP procedure get_bp_class(p_bp_id_tenant number, p_bp_class_flag in out varchar2) is v_bp_class_flag varchar2(100); begin select hb.bp_class into v_bp_class_flag from hls_bp_master hb where hb.bp_id = p_bp_id_tenant; p_bp_class_flag := v_bp_class_flag; end; procedure income_stat(p_date_from date, p_date_to date, p_user_id number) is v_date date := trunc(p_date_from, 'MONTH'); v_discount number; v_write_off_principal number; v_write_off_interest number; v_discount_agent number; v_deposit_agent number; v_deposit_tenant number; begin delete from income_stat_temp; loop select sum(al.total_amount) into v_write_off_principal from acr_invoice_ln al, acr_invoice_hd ah where ah.invoice_date >= v_date and ah.invoice_date <= last_day(v_date) and ah.invoice_hd_id = al.invoice_hd_id and al.cf_item = 100; select sum(al.total_amount) into v_discount_agent from acr_invoice_ln al, acr_invoice_hd ah where ah.invoice_date >= v_date and ah.invoice_date <= last_day(v_date) and ah.invoice_hd_id = al.invoice_hd_id and al.cf_item = 912; select sum(al.total_amount) into v_discount from acr_invoice_ln al, acr_invoice_hd ah where ah.invoice_date >= v_date and ah.invoice_date <= last_day(v_date) and ah.invoice_hd_id = al.invoice_hd_id and al.cf_item = 913; select sum(al.total_amount) into v_write_off_interest from acr_invoice_ln al, acr_invoice_hd ah where ah.invoice_date >= v_date and ah.invoice_date <= last_day(v_date) and ah.invoice_hd_id = al.invoice_hd_id and al.cf_item = 101; select sum(al.total_amount) into v_deposit_agent from acr_invoice_ln al, acr_invoice_hd ah where ah.invoice_date >= v_date and ah.invoice_date <= last_day(v_date) and ah.invoice_hd_id = al.invoice_hd_id and al.cf_item = 55; select sum(al.total_amount) into v_deposit_tenant from acr_invoice_ln al, acr_invoice_hd ah where ah.invoice_date >= v_date and ah.invoice_date <= last_day(v_date) and ah.invoice_hd_id = al.invoice_hd_id and al.cf_item = 52; insert into income_stat_temp values (to_char(v_date, 'yyyy-mm'), nvl(v_discount, 0), nvl(v_deposit_agent, 0), nvl(v_discount, 0) + nvl(v_write_off_interest, 0) + nvl(v_write_off_principal, 0) + nvl(v_deposit_agent, 0), nvl(v_deposit_tenant, 0), nvl(v_discount, 0) + nvl(v_write_off_interest, 0) + nvl(v_write_off_principal, 0) + nvl(v_deposit_agent, 0) + nvl(v_deposit_tenant, 0), nvl(v_write_off_principal, 0), nvl(v_write_off_interest, 0), nvl(v_discount_agent, 0)); v_date := add_months(v_date, 1); exit when v_date > p_date_to; end loop; end income_stat; function check_grt_amount(p_user_id number) return varchar2 is v_check_flag varchar2(1); v_agent_type hls_bp_master.agent_type%type; v_grt_amount number; v_fin_amount number; p_bp_id number; begin select su.bp_id into p_bp_id from sys_user su where su.user_id = p_user_id; select hm.agent_type into v_agent_type from hls_bp_master hm where hm.bp_id = p_bp_id; if v_agent_type = 'AGENT_GRT_DEPOSIT' then select nvl(a.transaction_amount, 0) - nvl(a.write_off_amount, 0) - nvl(a.returned_amount, 0) into v_grt_amount from csh_transaction a where a.transaction_type = 'DEPOSIT' and a.returned_flag != 'RETURN' and a.bp_id = p_bp_id; select sum(nvl(ccc.due_amount,0) - nvl(ccc.received_amount, 0)) into v_fin_amount from con_contract_cashflow ccc where ccc.times <> 0 and (ccc.cf_item = 1 or ccc.cf_item = 913) and ccc.cf_status = 'RELEASE' and ccc.contract_id in (select c1.contract_id from con_contract c1 where c1.bp_id_agent_level1 = p_bp_id and c1.contract_status = 'INCEPT' and c1.data_class = 'NORMAL' union all select c.contract_id from con_contract c, hls_bp_master h where c.bp_id_agent_level1 = h.bp_id and c.contract_status = 'INCEPT' and c.data_class = 'NORMAL' and h.parent_id = p_bp_id); if nvl(v_grt_amount, 0) >= 0.1 * nvl(v_fin_amount, 0) then v_check_flag := 'Y'; else v_check_flag := 'N'; end if; elsif v_agent_type = 'AGENT_NO_REBATE' then select nvl(a.transaction_amount, 0) - nvl(a.write_off_amount, 0) - nvl(a.returned_amount, 0) into v_grt_amount from csh_transaction a, hls_bp_master hm where a.transaction_type = 'DEPOSIT' and a.bp_id = hm.parent_id and a.returned_flag != 'RETURN' and hm.bp_id = p_bp_id; select sum(nvl(ccc.due_amount,0) - nvl(ccc.received_amount, 0)) into v_fin_amount from con_contract_cashflow ccc where ccc.times <> 0 and (ccc.cf_item = 1 or ccc.cf_item = 913) and ccc.cf_status = 'RELEASE' and ccc.contract_id in (select c1.contract_id from con_contract c1 where c1.bp_id_agent_level1 in (select hm.bp_id from hls_bp_master hm where hm.parent_id = (select h.parent_id from hls_bp_master h where h.bp_id = p_bp_id)) and c1.contract_status = 'INCEPT' and c1.data_class = 'NORMAL' union all select c.contract_id from con_contract c, hls_bp_master h where c.bp_id_agent_level1 = h.parent_id and c.contract_status = 'INCEPT' and c.data_class = 'NORMAL' and h.bp_id = p_bp_id); if nvl(v_grt_amount, 0) >= 0.1 * nvl(v_fin_amount, 0) then v_check_flag := 'Y'; else v_check_flag := 'N'; end if; end if; return v_check_flag; end check_grt_amount; procedure ACCOUNT_QUERY(p_account_type varchar2, --1:按照创建日期last_update_DATE统计查询 2:按照凭证日期JOURNAL_DATE统计查询查询 p_date_from date, p_date_to date, p_user_id number) is v_date date; V_OPENING_AMOUNT number; v_opening_amount_initial number; v_amount_dr number; v_amount_cr number; V_CLOSING_AMOUNT number; V_DAY_AMOUNT_DR number; V_DAY_AMOUNT_CR number; V_CNT number; begin ---根据创建日期last_update_DATE统计查询2019/02/18 if (p_account_type = 1) then delete from account_query_temp where open_flag <> 1; for rec in (select distinct hd.account_id from hls_journal_detail hd where not exists (select * from hls_journal_header a where a.journal_header_id = hd.journal_header_id and a.merge_parent_flag = 'Y')) loop v_date := p_date_from; if V_DATE <= P_DATE_TO then select sum(nvl(hd.amount_dr, 0) - nvl(hd.amount_cr, 0)) into v_opening_amount from HLS_JOURNAL_DETAIL HD where (select to_char(h.last_update_DATE, 'yyyyMMdd') from hls_journal_header h where h.journal_header_id = hd.journal_header_id) <= to_char(v_date, 'yyyyMMdd') and hd.account_id = rec.account_id; select count(*) into V_CNT from ACCOUNT_QUERY_INITIAL AQT where AQT.OPEN_FLAG = 1 and AQT.ACCOUNT_ID = REC.ACCOUNT_ID; if V_CNT > 0 then select NVL(DAY_AMOUNT_DR, 0) - NVL(DAY_AMOUNT_CR, 0) into V_OPENING_AMOUNT_INITIAL from ACCOUNT_QUERY_INITIAL AQT where AQT.OPEN_FLAG = 1 and AQT.ACCOUNT_ID = REC.ACCOUNT_ID; else V_OPENING_AMOUNT_INITIAL := 0; end if; /* SELECT SUM(nvl(hd.amount_dr, 0) - nvl(hd.amount_cr, 0)) INTO v_closing_amount FROM hls_journal_detail hd WHERE (SELECT h.CREATION_DATE FROM hls_journal_header h WHERE h.journal_header_id = hd.journal_header_id) <= p_date_to AND HD.ACCOUNT_ID = REC.ACCOUNT_ID; */ --借方金额汇总取自凭证表按照CREATION_DATE统计 select sum(nvl(hd.amount_dr, 0)) into v_amount_dr from hls_journal_detail hd where (select TO_CHAR(h.last_update_DATE, 'yyyyMMDD') from hls_journal_header h where H.JOURNAL_HEADER_ID = HD.JOURNAL_HEADER_ID) >= to_char(v_date, 'yyyyMMdd') and (select TO_CHAR(h.last_update_DATE, 'yyyyMMDD') from hls_journal_header h where H.JOURNAL_HEADER_ID = HD.JOURNAL_HEADER_ID) <= to_char(p_date_to, 'yyyyMMdd') and HD.ACCOUNT_ID = REC.ACCOUNT_ID; ----贷方金额汇总取自凭证表按照CREATION_DATE统计 select sum(nvl(hd.amount_cr, 0)) into v_amount_cr from hls_journal_detail hd where (select TO_CHAR(h.last_update_DATE, 'yyyyMMDD') from hls_journal_header h where h.journal_header_id = hd.journal_header_id) >= to_char(v_date, 'yyyyMMdd') and (select TO_CHAR(h.last_update_DATE, 'yyyyMMDD') from hls_journal_header h where h.journal_header_id = hd.journal_header_id) <= to_char(p_date_to, 'yyyyMMdd') and HD.ACCOUNT_ID = REC.ACCOUNT_ID; --借方金额汇总取自凭证表按照p_date_to统计 select sum(NVL(HD.AMOUNT_DR, 0)) into v_day_amount_dr from HLS_JOURNAL_DETAIL HD where (select TO_CHAR(h.last_update_DATE, 'yyyyMMDD') from HLS_JOURNAL_HEADER H where H.JOURNAL_HEADER_ID = HD.JOURNAL_HEADER_ID) = to_char(p_date_to, 'yyyyMMdd') and HD.ACCOUNT_ID = REC.ACCOUNT_ID; ----贷方金额汇总取自凭证表按照p_date_to统计 select sum(NVL(HD.AMOUNT_CR, 0)) into v_day_amount_cr from HLS_JOURNAL_DETAIL HD where (select TO_CHAR(h.last_update_DATE, 'yyyyMMDD') from HLS_JOURNAL_HEADER H where H.JOURNAL_HEADER_ID = HD.JOURNAL_HEADER_ID) = to_char(p_date_to, 'yyyyMMdd') and hd.account_id = rec.account_id; insert into ACCOUNT_QUERY_TEMP (ACCOUNT_ID, TRANSACTION_DATE, OPENING_BALANCE, AMOUNT_DR, AMOUNT_CR, DAY_AMOUNT_DR, DAY_AMOUNT_CR, OPEN_FLAG, PERIOD_FROM, PERIOD_TO, ACCOUNT_TYPE) values (rec.account_id, V_DATE, nvl(v_opening_amount_initial, 0) + nvl(v_opening_amount, 0), v_amount_dr, V_AMOUNT_CR, V_DAY_AMOUNT_DR, V_DAY_AMOUNT_CR, 0, p_date_from, p_date_to, p_account_type); end if; end loop; ---根据凭证日期JOURNAL_DATE统计查询2019/02/18 else delete from account_query_temp; for rec in (select distinct hd.account_id from hls_journal_detail hd where not exists (select * from hls_journal_header a where a.journal_header_id = hd.journal_header_id and a.merge_parent_flag = 'Y')) loop v_date := p_date_from; if V_DATE <= P_DATE_TO then select sum(nvl(hd.amount_dr, 0) - nvl(hd.amount_cr, 0)) into v_opening_amount from HLS_JOURNAL_DETAIL HD where (select to_char(h.JOURNAL_DATE, 'yyyyMMdd') from hls_journal_header h where h.journal_header_id = hd.journal_header_id) <= to_char(v_date, 'yyyyMMdd') and hd.account_id = rec.account_id; select count(*) into V_CNT from ACCOUNT_QUERY_INITIAL AQT where AQT.OPEN_FLAG = 1 and AQT.ACCOUNT_ID = REC.ACCOUNT_ID; if V_CNT > 0 then select NVL(DAY_AMOUNT_DR, 0) - NVL(DAY_AMOUNT_CR, 0) into V_OPENING_AMOUNT_INITIAL from ACCOUNT_QUERY_INITIAL AQT where AQT.OPEN_FLAG = 1 and AQT.ACCOUNT_ID = REC.ACCOUNT_ID; else V_OPENING_AMOUNT_INITIAL := 0; end if; /* SELECT SUM(nvl(hd.amount_dr, 0) - nvl(hd.amount_cr, 0)) INTO v_closing_amount FROM hls_journal_detail hd WHERE (SELECT h.CREATION_DATE FROM hls_journal_header h WHERE h.journal_header_id = hd.journal_header_id) <= p_date_to AND HD.ACCOUNT_ID = REC.ACCOUNT_ID; */ --借方金额汇总取自凭证表按照CREATION_DATE统计 select sum(nvl(hd.amount_dr, 0)) into v_amount_dr from hls_journal_detail hd where (select TO_CHAR(h.JOURNAL_DATE, 'yyyyMMDD') from hls_journal_header h where H.JOURNAL_HEADER_ID = HD.JOURNAL_HEADER_ID) >= to_char(v_date, 'yyyyMMdd') and (select TO_CHAR(h.JOURNAL_DATE, 'yyyyMMDD') from hls_journal_header h where H.JOURNAL_HEADER_ID = HD.JOURNAL_HEADER_ID) <= to_char(p_date_to, 'yyyyMMdd') and HD.ACCOUNT_ID = REC.ACCOUNT_ID; ----贷方金额汇总取自凭证表按照CREATION_DATE统计 select sum(nvl(hd.amount_cr, 0)) into v_amount_cr from hls_journal_detail hd where (select TO_CHAR(h.JOURNAL_DATE, 'yyyyMMDD') from hls_journal_header h where h.journal_header_id = hd.journal_header_id) >= to_char(v_date, 'yyyyMMdd') and (select TO_CHAR(h.JOURNAL_DATE, 'yyyyMMDD') from hls_journal_header h where h.journal_header_id = hd.journal_header_id) <= to_char(p_date_to, 'yyyyMMdd') and HD.ACCOUNT_ID = REC.ACCOUNT_ID; --借方金额汇总取自凭证表按照p_date_to统计 select sum(NVL(HD.AMOUNT_DR, 0)) into v_day_amount_dr from HLS_JOURNAL_DETAIL HD where (select TO_CHAR(h.JOURNAL_DATE, 'yyyyMMDD') from HLS_JOURNAL_HEADER H where H.JOURNAL_HEADER_ID = HD.JOURNAL_HEADER_ID) = to_char(p_date_to, 'yyyyMMdd') and HD.ACCOUNT_ID = REC.ACCOUNT_ID; ----贷方金额汇总取自凭证表按照p_date_to统计 select sum(NVL(HD.AMOUNT_CR, 0)) into v_day_amount_cr from HLS_JOURNAL_DETAIL HD where (select TO_CHAR(h.JOURNAL_DATE, 'yyyyMMDD') from HLS_JOURNAL_HEADER H where H.JOURNAL_HEADER_ID = HD.JOURNAL_HEADER_ID) = to_char(p_date_to, 'yyyyMMdd') and hd.account_id = rec.account_id; insert into ACCOUNT_QUERY_TEMP (ACCOUNT_ID, TRANSACTION_DATE, OPENING_BALANCE, AMOUNT_DR, AMOUNT_CR, DAY_AMOUNT_DR, DAY_AMOUNT_CR, OPEN_FLAG, PERIOD_FROM, PERIOD_TO, ACCOUNT_TYPE) values (rec.account_id, V_DATE, nvl(v_opening_amount_initial, 0) + nvl(v_opening_amount, 0), v_amount_dr, V_AMOUNT_CR, V_DAY_AMOUNT_DR, V_DAY_AMOUNT_CR, 0, p_date_from, p_date_to, p_account_type); end if; end loop; end if; end account_query; --是否逾期 function get_if_overdue(p_cashflow_id number) return varchar2 is v_count number; v_if_overdue varchar2(200); begin select count(1) into v_count from con_contract_cashflow co where co.cashflow_id = p_cashflow_id and co.due_date < sysdate and nvl(co.due_amount, 0) > nvl(co.received_amount, 0); if v_count = 0 then v_if_overdue := 'NORMAL'; else v_if_overdue := 'OVERDUE'; end if; return v_if_overdue; end; --获取担保人 function get_guarantor(p_contract_id number) return varchar2 is v_bp_guarantor varchar2(2000); --担保人 begin for rec in (select t.bp_name from con_contract_bp_lv t where bp_category = 'GUARANTOR' and t.contract_id = p_contract_id and t.enabled_flag = 'Y') loop v_bp_guarantor := v_bp_guarantor || rec.bp_name || ';'; end loop; return v_bp_guarantor; end; procedure default_idtype_idcode_mobile(p_bp_id_tenant number, p_user_id number, p_idtype out varchar2, p_idtype_n out varchar2, p_idcode out varchar2, p_mobile out varchar2) is begin select l.id_type as idtype, l.id_type_n as idtype_n, l.id_card_no as idcode, l.phone as mobile into p_idtype, p_idtype_n, p_idcode, p_mobile from hls_bp_master_lv l where l.bp_id = p_bp_id_tenant; exception when others then null; end; --得到产品是否自定义 procedure get_if_self_definition(p_product_id number, p_user_id number, p_if_self_definition out varchar2) is begin select if_self_definition into p_if_self_definition from hls_product_plan_definition h where h.product_plan_id = p_product_id; exception when others then null; end; --modify by lpc 9874 for deron 逢5逢0计算第一个合同的起租日 function get_first_contract_day(p_contract_id number) return varchar2 is v_first_contract_day varchar2(10); v_count number; v_first_contract_id number; begin select count(1) into v_count from con_contract ct where ct.project_id = (select cc.project_id from con_contract cc where cc.contract_id = p_contract_id) and ct.data_class = 'NORMAL' and ct.contract_status = 'INCEPT' and ct.inception_of_lease is not null; if v_count > 0 then select t1.contract_id into v_first_contract_id from (select cc.contract_id from con_contract cc where cc.project_id = (select cc.project_id from con_contract cc where cc.contract_id = p_contract_id) and cc.data_class = 'NORMAL' and cc.inception_of_lease is not null order by cc.creation_date) t1 where rownum = 1; --取付款申请头表中审批通过的有车款投放日的首期付款日期 select to_char(max(ch.first_payment_date), 'dd') into v_first_contract_day from csh_payment_req_hd ch where ch.contract_id = v_first_contract_id and ch.approval_status = 'APPROVED' and exists (select 1 from csh_payment_req_ln cl, con_contract_cashflow ccc where cl.payment_req_id = ch.payment_req_id and cl.ref_doc_line_id = ccc.cashflow_id and ccc.write_off_flag = 'FULL' and ccc.cf_item = 5); end if; return v_first_contract_day; end; --结清资料退还 procedure update_send_info(p_contract_id number, p_cert_recipient01 varchar2, p_cert_tracknum01 varchar2, p_cert_send_date01 date, p_cert_recipient02 varchar2, p_cert_tracknum02 varchar2, p_cert_send_date02 date, p_trans_recipient01 varchar2, p_trans_tracknum01 varchar2, p_trans_send_date01 date, p_trans_recipient02 varchar2, p_trans_tracknum02 varchar2, p_trans_send_date02 date, p_note varchar2, p_user_id number) is v_count number; begin select count(1) into v_count from con_contract_et_document a where a.contract_id = p_contract_id; if v_count < 1 then insert into con_contract_et_document (doc_id, contract_id, apply_id, print_flag, cert_recipient01, cert_tracknum01, cert_send_date01, cert_recipient02, cert_tracknum02, cert_send_date02, trans_recipient01, trans_tracknum01, trans_send_date01, trans_recipient02, trans_tracknum02, trans_send_date02, send_flag, note, created_by, creation_date, last_updated_by, last_update_date) values (con_contract_et_document_s.nextval, p_contract_id, null, 'N', p_cert_recipient01, p_cert_tracknum01, p_cert_send_date01, p_cert_recipient02, p_cert_tracknum02, p_cert_send_date02, p_trans_recipient01, p_trans_tracknum01, p_trans_send_date01, p_trans_recipient02, p_trans_tracknum02, p_trans_send_date02, 'N', p_note, p_user_id, sysdate, p_user_id, sysdate); else update con_contract_et_document t set t.cert_recipient01 = p_cert_recipient01, t.cert_tracknum01 = p_cert_tracknum01, t.cert_send_date01 = p_cert_send_date01, t.cert_recipient02 = p_cert_recipient02, t.cert_tracknum02 = p_cert_tracknum02, t.cert_send_date02 = p_cert_send_date02, t.trans_recipient01 = p_trans_recipient01, t.trans_tracknum01 = p_trans_tracknum01, t.trans_send_date01 = p_trans_send_date01, t.trans_recipient02 = p_trans_recipient02, t.trans_tracknum02 = p_trans_tracknum02, t.trans_send_date02 = p_trans_send_date02, t.note = p_note, t.last_updated_by = p_user_id, t.last_update_date = sysdate where t.contract_id = p_contract_id; end if; exception when others then null; end; --doosan计算起租日逻辑 function get_inception_of_lease(p_date date) return date is v_inception_of_lease date; v_day number; begin select to_char(p_date, 'dd') into v_day from dual; if v_day <= 20 then v_inception_of_lease := to_date(to_char(add_months(p_date, 1), 'yyyy-mm') || '-' || '15', 'yyyy-mm-dd'); else v_inception_of_lease := to_date(to_char(add_months(p_date, 2), 'yyyy-mm') || '-' || '8', 'yyyy-mm-dd'); end if; return v_inception_of_lease; end; --doosan计算起租日逻辑8号 function get_inception_of_lease_8(p_date date) return date is v_inception_of_lease date; v_day number; begin select to_char(p_date, 'dd') into v_day from dual; if v_day <= 20 then v_inception_of_lease := to_date(to_char(add_months(p_date, 1), 'yyyy-mm') || '-' || '8', 'yyyy-mm-dd'); else v_inception_of_lease := to_date(to_char(add_months(p_date, 2), 'yyyy-mm') || '-' || '8', 'yyyy-mm-dd'); end if; return v_inception_of_lease; end; --删除合同bp信息 modify by lpc 9874 for doosan procedure delete_con_contract_bp(p_record_id number, p_user_id number) is begin delete from con_contract_bp c where c.record_id = p_record_id and c.bp_category <> 'TENANT'; exception when no_data_found then null; end; --更新信息录入标志及事前确认工单 --add by haojie.liu for doosan procedure update_contract_info_sign(p_contract_id number) is v_count number := 0; begin update con_contract c set c.information_completed = 'Y' where c.contract_id = p_contract_id; select count(*) into v_count from work_orders wo where wo.contract_id = p_contract_id; if v_count >= 1 then update work_orders wo set wo.call_status = 'N' where wo.contract_id = p_contract_id and wo.business_type = 0; end if; exception when no_data_found then null; end; end con_contract_pkg; /