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;
/