create or replace package hls_fin_calculator_ccr_pkg is -- Author : ZHANGLEI -- Created : 2014/10/20 9:15:42 -- Purpose : procedure create_calculate(p_change_req_id in number, p_calc_session_id in number, p_recreate_h_formula in varchar2 default 'N', p_recreate_l_std in varchar2 default 'N', p_recreate_l_formula in varchar2 default 'N', p_calc_successful out varchar2, p_warning_message out varchar2, p_user_id in number); end hls_fin_calculator_ccr_pkg; / create or replace package body hls_fin_calculator_ccr_pkg is function replace_k(p_source varchar2, p_replace_j number) return varchar2 is begin return hls_fin_calculator_itfc_pkg.replace_k(p_source => p_source, p_replace_j => p_replace_j); end; function replace_n(p_source varchar2, p_replace_j number) return varchar2 is begin return hls_fin_calculator_itfc_pkg.replace_n(p_source => p_source, p_replace_j => p_replace_j); end; procedure create_h_data(p_calc_session_id number, p_contract_id number, p_change_req_id number, p_price_list varchar2, p_ccr_start_times number, p_ccr_lease_times number, p_user_id number) is r_hls_fin_calculator_hd hls_fin_calculator_hd%rowtype; begin hls_document_transfer_pkg.doc_to_doc(p_from_doc_table => 'CON_CONTRACT', p_from_doc_pk => p_change_req_id, p_to_doc_table => 'HLS_FIN_CALCULATOR_HD', p_to_doc_pk => p_calc_session_id, p_to_doc_column_1 => 'source_doc_category', p_to_doc_column_1_value => 'CHANGE_REQ', p_to_doc_column_2 => 'source_doc_id', p_to_doc_column_2_value => p_change_req_id, p_to_doc_column_3 => 'price_list', p_to_doc_column_3_value => p_price_list, p_user_id => p_user_id); -- replace hd.lease_times update hls_fin_calculator_hd set lease_times = p_ccr_start_times + p_ccr_lease_times - 1, business_type = (select BUSINESS_TYPE from con_contract where contract_id = p_contract_id) where calc_session_id = p_calc_session_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 => 'HLS_FIN_CALCULATOR_HD_FORMULA', p_to_doc_pk => p_calc_session_id, p_user_id => p_user_id); hls_fin_calculator_itfc_pkg.calculator_h_formula(p_calc_session_id => p_calc_session_id, p_user_id => p_user_id); end; procedure create_l_data(p_calc_session_id number, p_contract_id number, p_change_req_id number, p_price_list varchar2, p_ccr_start_times number, p_ccr_lease_times number, p_recreate_l_std varchar2, p_recreate_l_formula varchar2, p_user_id number) is n number; --原合同总期数 k number; --变更后总期数 i number; v_lease_times number; t_column_cf_item hls_fin_calculator_itfc_pkg.column_cf_item_t; r_calculator_ln hls_fin_calculator_ln%rowtype; r_calculator_ln_formula hls_fin_calculator_ln_formula%rowtype; ln_formula_record hls_fin_calculator_itfc_pkg.ln_formula_type; v_cf_item varchar2(100); v_calc_line_id number; v_exists varchar2(1); v_sql varchar2(32767); v_matrix_j_initial number; v_matrix_j_interval number; v_formula varchar2(4000); v_old_calc_session_id number; e_lease_times_error exception; e_times_formula_error exception; function get_column_value(p_source in varchar2, p_j in number) return number is i number := 0; v_instr number; v_text varchar2(2000); v_sql varchar2(32767); v_value varchar2(1000); v_parent_column varchar2(100); e_ignore_flag exception; begin if substr(ltrim(p_source), 1, 1) <> '=' then return p_source; end if; v_text := p_source; --检索头数组 H loop i := i + 1; v_instr := instr(upper(v_text), hls_fin_calculator_core_pkg.c_h, 1, i); if v_instr = 0 or v_instr is null then exit; end if; v_parent_column := null; for i in 1 .. length(v_text) - v_instr loop begin if substr(v_text, v_instr + i, 1) not in ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9') then if i = 1 then raise e_ignore_flag; --H数组第一个字符必须是数组 end if; v_parent_column := hls_fin_calculator_core_pkg.c_h || substr(v_text, v_instr + 1, i - 1); begin select b.column_name into v_parent_column from hls_fin_calc_config b where b.layout_area_seq = 'H' and b.price_list = p_price_list and b.column_code = v_parent_column; begin v_sql := 'select ' || v_parent_column || ' from hls_fin_calculator_hd where calc_session_id = ' || p_calc_session_id; execute immediate v_sql into v_value; v_text := substr(v_text, 1, v_instr - 1) || v_value || substr(v_text, v_instr + i); --v_text := replace(v_text,v_parent_column,v_value); exception when others then raise e_ignore_flag; end; exception when no_data_found then raise e_ignore_flag; end; exit; end if; if i = (length(v_text) - v_instr) then v_parent_column := hls_fin_calculator_core_pkg.c_h || substr(v_text, v_instr + 1); begin select b.column_name into v_parent_column from hls_fin_calc_config b where b.layout_area_seq = 'H' and b.price_list = p_price_list and b.column_code = v_parent_column; begin v_sql := 'select ' || v_parent_column || ' from hls_fin_calculator_hd where calc_session_id = ' || p_calc_session_id; execute immediate v_sql into v_value; v_text := substr(v_text, 1, v_instr - 1) || v_value; --v_text := replace(v_text,v_parent_column,v_value); exception when others then raise e_ignore_flag; end; exception when no_data_found then raise e_ignore_flag; end; end if; exception when e_ignore_flag then exit; end; end loop; end loop; v_value := hls_fin_calculator_itfc_pkg.execute_formula(v_text, p_calc_session_id, p_price_list); return v_value; exception when others then return null; end; begin select lease_times into v_lease_times from hls_fin_calculator_hd where calc_session_id = p_calc_session_id; if p_ccr_lease_times is null or p_ccr_lease_times < 0 or p_ccr_start_times is null or p_ccr_start_times < 0 then raise e_lease_times_error; end if; if p_recreate_l_std = 'Y' then delete from hls_fin_calculator_ln where calc_session_id = p_calc_session_id and to_number(times) < p_ccr_start_times; delete from hls_fin_calculator_ln_formula where calc_session_id = p_calc_session_id and to_number(times) < p_ccr_start_times; t_column_cf_item := hls_fin_calculator_itfc_pkg.get_column_cf_item(p_price_list => p_price_list); -------------------------------------- --step 1 将调整之前的期数回写到计算器行表 -------------------------------------- i := t_column_cf_item.first; while t_column_cf_item.exists(i) loop v_cf_item := t_column_cf_item(i).cf_item; for c_cf in (select a.cashflow_id, a.times from con_contract_cashflow a where a.contract_id = p_change_req_id and a.cf_item = v_cf_item and a.times < p_ccr_start_times) loop if v_cf_item = 1 then for c_column in (select c.DATA_TYPE, c.COLUMN_NAME, c.COLUMN_NAME as COLUMN_NAME_cf, cc.column_id as matrix_i from user_tab_columns c, hls_fin_calc_column_code cc where c.TABLE_NAME = 'CON_CONTRACT_CASHFLOW' and cc.table_name = 'HLS_FIN_CALCULATOR_LN' and c.COLUMN_NAME = cc.column_name union select c.data_type, c.column_name, 'DUE_AMOUNT' as column_name_cf, c.column_id as matrix_i from hls_fin_calc_config c where c.price_list = p_price_list and c.column_name = 'RENTAL' and c.layout_area_seq = 'L' union select c.data_type, c.column_name, 'NET_DUE_AMOUNT' as column_name_cf, c.column_id as matrix_i from hls_fin_calc_config c where c.price_list = p_price_list and c.column_name = 'NET_RENTAL' and c.layout_area_seq = 'L' union select c.data_type, c.column_name, 'VAT_DUE_AMOUNT' as column_name_cf, c.column_id as matrix_i from hls_fin_calc_config c where c.price_list = p_price_list and c.column_name = 'VAT_RENTAL' and c.layout_area_seq = 'L') loop execute immediate 'select ' || c_column.column_name_cf || ' from con_contract_cashflow where cashflow_id=' || c_cf.cashflow_id into v_formula; ln_formula_record(c_cf.times).n(c_column.matrix_i).column_name := c_column.column_name; ln_formula_record(c_cf.times).n(c_column.matrix_i).data_type := c_column.data_type; ln_formula_record(c_cf.times).n(c_column.matrix_i).formula := v_formula; end loop; elsif v_cf_item = 0 then for c_column in (select c.DATA_TYPE, c.COLUMN_NAME, c.COLUMN_NAME as COLUMN_NAME_cf, cc.column_id as matrix_i from user_tab_columns c, hls_fin_calc_column_code cc where c.TABLE_NAME = 'CON_CONTRACT_CASHFLOW' and cc.table_name = 'HLS_FIN_CALCULATOR_LN' and c.column_name = cc.column_name and c.column_name <> t_column_cf_item(i) .column_name union select c.data_type, c.column_name, 'DUE_AMOUNT' as column_name_cf, c.column_id as matrix_i from hls_fin_calc_config c where c.price_list = p_price_list and c.layout_area_seq = 'L' and c.column_name = t_column_cf_item(i) .column_name) loop execute immediate 'select ' || c_column.column_name_cf || ' from con_contract_cashflow where cashflow_id=' || c_cf.cashflow_id into v_formula; ln_formula_record(c_cf.times).n(c_column.matrix_i).column_name := c_column.column_name; ln_formula_record(c_cf.times).n(c_column.matrix_i).data_type := c_column.data_type; ln_formula_record(c_cf.times).n(c_column.matrix_i).formula := v_formula; end loop; else for c_column in (select c.data_type, c.column_name, 'DUE_AMOUNT' as column_name_cf, c.column_id as matrix_i from hls_fin_calc_config c where c.price_list = p_price_list and c.layout_area_seq = 'L' and c.column_name = t_column_cf_item(i) .column_name union select c.data_type, c.column_name, 'NET_DUE_AMOUNT' as column_name_cf, c.column_id as matrix_i from hls_fin_calc_config c where c.price_list = p_price_list and c.layout_area_seq = 'L' and c.column_name = t_column_cf_item(i) .net_column_name union select c.data_type, c.column_name, 'VAT_DUE_AMOUNT' as column_name_cf, c.column_id as matrix_i from hls_fin_calc_config c where c.price_list = p_price_list and c.layout_area_seq = 'L' and c.column_name = t_column_cf_item(i) .vat_column_name ) loop execute immediate 'select ' || c_column.column_name_cf || ' from con_contract_cashflow where cashflow_id=' || c_cf.cashflow_id into v_formula; ln_formula_record(c_cf.times).n(c_column.matrix_i).column_name := c_column.column_name; ln_formula_record(c_cf.times).n(c_column.matrix_i).data_type := c_column.data_type; ln_formula_record(c_cf.times).n(c_column.matrix_i).formula := v_formula; end loop; end if; end loop; i := t_column_cf_item.next(i); end loop; end if; -------------------------------------- --step 2 按新公式填写剩余的计算器行表 -------------------------------------- if p_recreate_l_formula = 'Y' then delete from hls_fin_calculator_ln where calc_session_id = p_calc_session_id and to_number(times) >= p_ccr_start_times; delete from hls_fin_calculator_ln_formula where calc_session_id = p_calc_session_id and to_number(times) >= p_ccr_start_times; for c_l_column in (select a.column_name, a.config_id, a.data_type from hls_fin_calc_config a where a.price_list = p_price_list and a.layout_area_seq = 'L' and a.enabled_flag = 'Y') loop for c_l_formula in (select b.matrix_i, b.matrix_j_type, b.matrix_j_initial, b.matrix_j_interval, b.calc_formula, b.loop_remain_times_only from hls_fin_calc_config_formula b where b.config_id = c_l_column.config_id and b.enabled_flag = 'Y' order by b.matrix_i asc, b.calc_seq asc, decode(b.matrix_j_type, 'CYCLE', 1, 2) asc ) loop /* 起始值字段如果没有输入=,自动补等号 */ if substr(ltrim(c_l_formula.matrix_j_initial), 1, 1) <> '=' then c_l_formula.matrix_j_initial := '=' || c_l_formula.matrix_j_initial; end if; /* 间隔 字段如果没有输入=,自动补等号 */ if substr(ltrim(c_l_formula.matrix_j_interval), 1, 1) <> '=' then c_l_formula.matrix_j_interval := '=' || c_l_formula.matrix_j_interval; end if; if c_l_formula.matrix_j_type = 'CYCLE' then v_matrix_j_initial := get_column_value(c_l_formula.matrix_j_initial, null); v_matrix_j_interval := get_column_value(c_l_formula.matrix_j_interval, null); if v_matrix_j_initial is null then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'HLS050_MATRIX_J_INITIAL_VALUE_ERROR', p_token_1 => '', p_token_value_1 => c_l_column.column_name, p_token_2 => '', p_token_value_2 => c_l_formula.matrix_j_initial, p_created_by => -1, p_package_name => 'con_change_req_calc_itfc_pkg', p_procedure_function_name => 'create_l_data'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end if; if v_matrix_j_interval is null then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'HLS050_MATRIX_J_INTERVAL_VALUE_ERROR', p_token_1 => '', p_token_value_1 => c_l_column.column_name, p_token_2 => '', p_token_value_2 => c_l_formula.matrix_j_interval, p_created_by => -1, p_package_name => 'con_change_req_calc_itfc_pkg', p_procedure_function_name => 'create_l_data'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end if; if v_matrix_j_interval > 0 then --从小到大循环 if c_l_formula.loop_remain_times_only = 'Y' then for j in v_matrix_j_initial .. p_ccr_lease_times loop if v_matrix_j_initial is not null and v_matrix_j_interval is not null then --能计算循环周期 if j >= v_matrix_j_initial and mod(j - v_matrix_j_initial, v_matrix_j_interval) = 0 then k := p_ccr_start_times + j - 1; v_formula := replace_k(c_l_formula.calc_formula, k); ln_formula_record(k).n(c_l_formula.matrix_i).column_name := c_l_column.column_name; ln_formula_record(k).n(c_l_formula.matrix_i).data_type := c_l_column.data_type; if c_l_column.column_name = 'TIMES' then v_formula := get_column_value(v_formula, k); if v_formula is null then raise e_times_formula_error; end if; end if; ln_formula_record(k).n(c_l_formula.matrix_i).formula := v_formula; end if; end if; end loop; else for j in v_matrix_j_initial .. v_lease_times loop if v_matrix_j_initial is not null and v_matrix_j_interval is not null then --能计算循环周期 if j >= v_matrix_j_initial and mod(j - v_matrix_j_initial, v_matrix_j_interval) = 0 then n := j; v_formula := replace_n(c_l_formula.calc_formula, n); ln_formula_record(n).n(c_l_formula.matrix_i).column_name := c_l_column.column_name; ln_formula_record(n).n(c_l_formula.matrix_i).data_type := c_l_column.data_type; if c_l_column.column_name = 'TIMES' then v_formula := get_column_value(v_formula, n); if v_formula is null then raise e_times_formula_error; end if; end if; ln_formula_record(n).n(c_l_formula.matrix_i).formula := v_formula; end if; end if; end loop; end if; elsif v_matrix_j_interval < 0 then --从大到小循环 if c_l_formula.loop_remain_times_only = 'Y' then for j in 0 .. v_matrix_j_initial loop if v_matrix_j_initial is not null and v_matrix_j_interval is not null then --能计算循环周期 if j <= v_matrix_j_initial and mod(v_matrix_j_initial - j, v_matrix_j_interval) = 0 then k := p_ccr_start_times + j - 1; v_formula := replace_k(c_l_formula.calc_formula, k); ln_formula_record(k).n(c_l_formula.matrix_i).column_name := c_l_column.column_name; ln_formula_record(k).n(c_l_formula.matrix_i).data_type := c_l_column.data_type; if c_l_column.column_name = 'TIMES' then v_formula := get_column_value(v_formula, k); if v_formula is null then raise e_times_formula_error; end if; end if; ln_formula_record(k).n(c_l_formula.matrix_i).formula := v_formula; end if; end if; end loop; else for j in 0 .. v_matrix_j_initial loop if v_matrix_j_initial is not null and v_matrix_j_interval is not null then --能计算循环周期 if j <= v_matrix_j_initial and mod(v_matrix_j_initial - j, v_matrix_j_interval) = 0 then n := j; v_formula := replace_n(c_l_formula.calc_formula, n); ln_formula_record(n).n(c_l_formula.matrix_i).column_name := c_l_column.column_name; ln_formula_record(n).n(c_l_formula.matrix_i).data_type := c_l_column.data_type; if c_l_column.column_name = 'TIMES' then v_formula := get_column_value(v_formula, n); if v_formula is null then raise e_times_formula_error; end if; end if; ln_formula_record(n).n(c_l_formula.matrix_i).formula := v_formula; end if; end if; end loop; end if; end if; elsif c_l_formula.matrix_j_type = 'FIXED' then v_matrix_j_initial := get_column_value(c_l_formula.matrix_j_initial, null); if v_matrix_j_initial is null then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'HLS050_MATRIX_J_INITIAL_VALUE_ERROR', p_token_1 => '', p_token_value_1 => c_l_column.column_name, p_token_2 => '', p_token_value_2 => c_l_formula.matrix_j_initial, p_created_by => -1, p_package_name => 'con_change_req_calc_itfc_pkg', p_procedure_function_name => 'create_l_data'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end if; if c_l_formula.loop_remain_times_only = 'Y' then k := v_matrix_j_initial + p_ccr_start_times - 1; if k is not null then v_formula := replace_k(c_l_formula.calc_formula, k); if c_l_column.column_name = 'TIMES' then v_formula := get_column_value(v_formula, k); if v_formula is null then raise e_times_formula_error; end if; end if; ln_formula_record(k).n(c_l_formula.matrix_i).data_type := c_l_column.data_type; ln_formula_record(k).n(c_l_formula.matrix_i).column_name := c_l_column.column_name; ln_formula_record(k).n(c_l_formula.matrix_i).formula := v_formula; end if; else n := v_matrix_j_initial; if n is not null then v_formula := replace_n(c_l_formula.calc_formula, n); if c_l_column.column_name = 'TIMES' then v_formula := get_column_value(v_formula, n); if v_formula is null then raise e_times_formula_error; end if; end if; ln_formula_record(n).n(c_l_formula.matrix_i).data_type := c_l_column.data_type; ln_formula_record(n).n(c_l_formula.matrix_i).column_name := c_l_column.column_name; ln_formula_record(n).n(c_l_formula.matrix_i).formula := v_formula; end if; end if; end if; end loop; end loop; end if; hls_fin_calculator_itfc_pkg.save_l_formula(p_ln_formula_record => ln_formula_record, p_user_id => p_user_id, p_calc_session_id => p_calc_session_id); --计算复制于源价目表字段 if p_recreate_l_std = 'Y' then select calc_session_id into v_old_calc_session_id from con_contract a where a.contract_id = p_contract_id; for cur in (select a.column_name from hls_fin_calc_config a where a.layout_area_seq = 'L' and a.price_list = p_price_list and a.ref_v03 = 'Y') loop v_sql := 'update hls_fin_calculator_ln_formula l set ' || cur.column_name || ' = (select ' || cur.column_name || ' from hls_fin_calculator_ln ol where ol.calc_session_id = ' || v_old_calc_session_id || ' and ol.times = l.times)' || ' where calc_session_id = ' || p_calc_session_id || ' and to_number(times) <' || p_ccr_start_times; execute immediate v_sql; end loop; end if; end; procedure create_calculate(p_change_req_id in number, p_calc_session_id in number, p_recreate_h_formula in varchar2 default 'N', p_recreate_l_std in varchar2 default 'N', p_recreate_l_formula in varchar2 default 'N', p_calc_successful out varchar2, p_warning_message out varchar2, p_user_id in number) is column_list_array hls_fin_calculator_core_pkg.column_list_type; v_error_count number; r_hls_fin_calc_hd_rec hls_fin_calculator_hd%rowtype; r_contract con_contract%rowtype; v_contract_id number; e_ccr_start_times_error exception; begin if p_calc_session_id is null then return; else execute immediate 'alter session set nls_date_format = ''yyyy-mm-dd'''; r_contract := con_contract_pkg.get_contract_rec(p_contract_id => p_change_req_id, p_user_id => p_user_id); if r_contract.ccr_start_times is null or r_contract.ccr_outstanding_times is null then raise e_ccr_start_times_error; end if; if r_contract.ccr_start_times < 1 then raise e_ccr_start_times_error; end if; select contract_id into v_contract_id from con_contract_change_req where change_req_id = p_change_req_id; if p_recreate_h_formula = 'Y' then delete from hls_fin_calculator_hd where calc_session_id = p_calc_session_id; delete from hls_fin_calculator_hd_formula where calc_session_id = p_calc_session_id; --create h create_h_data(p_calc_session_id => p_calc_session_id, p_contract_id => v_contract_id, p_change_req_id => p_change_req_id, p_price_list => r_contract.price_list, p_ccr_start_times => r_contract.ccr_start_times, p_ccr_lease_times => r_contract.ccr_outstanding_times, p_user_id => p_user_id); end if; select * into r_hls_fin_calc_hd_rec from hls_fin_calculator_hd where calc_session_id = p_calc_session_id; if not (r_hls_fin_calc_hd_rec.source_doc_category = 'CHANGE_REQ' and r_hls_fin_calc_hd_rec.source_doc_id is not null) then return; end if; if p_recreate_l_formula = 'Y' then update hls_fin_calculator_hd_formula set lease_times = r_hls_fin_calc_hd_rec.ccr_start_times + r_hls_fin_calc_hd_rec.ccr_outstanding_times - 1 where calc_session_id = p_calc_session_id; update hls_fin_calculator_hd hd set lease_times = r_hls_fin_calc_hd_rec.ccr_start_times + r_hls_fin_calc_hd_rec.ccr_outstanding_times - 1 where calc_session_id = p_calc_session_id; --create l create_l_data(p_calc_session_id => p_calc_session_id, p_contract_id => v_contract_id, p_change_req_id => p_change_req_id, p_price_list => r_hls_fin_calc_hd_rec.price_list, p_ccr_start_times => r_hls_fin_calc_hd_rec.ccr_start_times, p_ccr_lease_times => r_hls_fin_calc_hd_rec.ccr_outstanding_times, p_recreate_l_std => p_recreate_l_std, p_recreate_l_formula => p_recreate_l_formula, p_user_id => p_user_id); end if; end if; hls_fin_calculator_itfc_pkg.calculate_main(p_calc_session_id => p_calc_session_id, p_calc_successful => p_calc_successful, p_warning_message => p_warning_message, p_user_id => p_user_id); exception when e_ccr_start_times_error then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'HLS_FIN_CALCULATOR_CCR_PKG.CCR_START_TIMES_ERROR', p_created_by => p_user_id, p_package_name => 'hls_fin_calculator_ccr_pkg', p_procedure_function_name => 'create_calculate'); 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 => 'hls_fin_calculator_ccr_pkg', p_procedure_function_name => 'create_calculate'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end; end hls_fin_calculator_ccr_pkg; /