create or replace package "FND_CODE_RULE_PKG" is

  -- Author  : Tengjianbo
  -- Created : 2009-5-14 14:44:24
  -- Purpose :

  --Ver: 1.11
  -- Mantis 0025577:编码规则取日期相关时,即使按期间重置,也不校验期间状态是否打开。
  --Modify by bobo

  --Ver: 1.12
  --规则明细界面增加一列是否显示DISPLAY_FLAG
  --Modify by bobo 2009.09.08

  --Ver: 1.15
  --WEB界面查询用函数:get_document_type_name 中的sql对company_id加上nvl
  --否则系统级单据类型会取不到描述
  --Modify by Razgriz.Tang @2010-04-26

  --Ver: 1.16
  --32996: 取期间时,排除调整期间

  c_function_code constant varchar2(30) := 'FND1910';
  --编码规则获取失败
  c_error constant varchar2(30) := 'ERROR';

  --编码长度超过30位 error_code :FND_CODING_RULE_MAX_LENGTH_ERROR
  --c_max_length_error constant varchar2(30) := 'MAX_LENGTH_ERROR';

  --编码规则对象定义
  procedure insert_fnd_coding_rule_objects(p_document_category     varchar2,
                                           p_document_type         varchar2,
                                           p_company_id            number,
                                           p_operation_unit_id     number,
                                           p_enabled_flag          varchar2,
                                           p_coding_rule_id        number,
                                           p_priority              number,
                                           p_created_by            number,
                                           p_coding_rule_object_id out number);

  procedure update_fnd_coding_rule_objects(p_coding_rule_object_id number,
                                           p_coding_rule_id        number,
                                           p_enabled_flag          varchar2,
                                           p_priority              number,
                                           p_last_updated_by       number);

  /*--编码规则只能有一个启用
  procedure coding_rules_check(p_coding_rule_object_id number,
                               p_created_by            number);*/

  function get_coding_rule_object_id(p_document_category        varchar2,
                                     p_document_type            varchar2,
                                     p_company_id               number,
                                     p_operation_unit_id        number,
                                     p_object_document_type     out varchar2,
                                     p_object_company_id        out number,
                                     p_object_operation_unit_id out number,
                                     p_object_coding_rule_id    out number,
                                     p_reset_frequence          out varchar2)
    return number;
  --编码规则
  procedure insert_fnd_coding_rules(p_document_category varchar2,
                                    p_coding_rule_code  varchar2,
                                    p_description       varchar2,
                                    p_note              varchar2,
                                    p_enabled_flag      varchar2,
                                    p_created_by        number,
                                    p_reset_frequence   varchar2,
                                    p_coding_rule_id    out number);

  procedure update_fnd_coding_rules(p_coding_rule_id  number,
                                    p_description     varchar2,
                                    p_note            varchar2,
                                    p_enabled_flag    varchar2,
                                    p_last_updated_by number,
                                    p_reset_frequence varchar2);

  procedure delete_fnd_coding_rules(p_coding_rule_id  number,
                                    p_last_updated_by number);

  --规则明细
  procedure insert_fnd_coding_rule_details(p_coding_rule_id number,
                                           p_sequence       number,
                                           p_segment_type   varchar2,
                                           p_segment_value  varchar2,
                                           p_length         number,
                                           p_incremental    number,
                                           p_start_value    number,
                                           p_created_by     number,
                                           p_date_format    varchar2,
                                           p_display_flag   varchar2);

  procedure update_fnd_coding_rule_details(p_coding_rule_line_id number,
                                           p_coding_rule_id      number,
                                           p_sequence            number,
                                           p_segment_type        varchar2,
                                           p_segment_value       varchar2,
                                           p_length              number,
                                           p_incremental         number,
                                           p_start_value         number,
                                           p_last_updated_by     number,
                                           p_date_format         varchar2,
                                           p_display_flag        varchar2);

  procedure delete_fnd_coding_rule_details(p_coding_rule_line_id number,
                                           p_coding_rule_id      number,
                                           p_last_updated_by     number);

  function get_rule_next_auto_num(p_document_category varchar2,
                                  p_document_type     varchar2,
                                  p_company_id        number,
                                  p_operation_unit_id number,
                                  p_operation_date    date,
                                  p_division          varchar2 default null,
                                  p_bp_info           varchar2 default null,
                                  p_province_id       number default null,
                                  p_agent_code        varchar2 default null,
                                  p_price_ref_v05     varchar2 default null,
                                  p_product_code      varchar2 default null, -- 产品编号 add  by liukang
                                  p_created_by        number) return varchar2;

end fnd_code_rule_pkg;
/
create or replace package body "FND_CODE_RULE_PKG" is

  e_fnd_c_r_status_error      exception;
  e_fnd_c_r_max_length_error  exception;
  e_fnd_c_r_start_value_error exception;
  e_fnd_c_r_incremental_error exception;
  e_fnd_c_r_segment_error     exception;
  e_fnd_segment_type_06_err   exception;
  e_fnd_segment_type_02_err   exception; --Data format error
  e_binary_integer_error      exception;

  g_company_id        number;
  g_document_category varchar2(30);
  g_document_type     varchar2(30);
  g_operation_unit_id number;
  g_operation_date    date;

  procedure error_log(p_message varchar2, p_user_id number) is
    pragma autonomous_transaction;
  begin
    insert into fnd_coding_rule_error_logs
      (company_id,
       document_category,
       document_type,
       operation_unit_id,
       operation_date,
       message,
       created_by,
       creation_date,
       last_updated_by,
       last_update_date)
    values
      (g_company_id,
       g_document_category,
       g_document_type,
       g_operation_unit_id,
       g_operation_date,
       p_message,
       p_user_id,
       sysdate,
       p_user_id,
       sysdate);
    commit;
  end error_log;

  --编码规则对象定义
  procedure insert_fnd_coding_rule_objects(p_document_category     varchar2,
                                           p_document_type         varchar2,
                                           p_company_id            number,
                                           p_operation_unit_id     number,
                                           p_enabled_flag          varchar2,
                                           p_coding_rule_id        number,
                                           p_priority              number,
                                           p_created_by            number,
                                           p_coding_rule_object_id out number) is
    v_count                 number;
    v_coding_rule_object_id fnd_coding_rule_objects.coding_rule_object_id%type;
  
    e_priority_duplicat exception;
  begin
  
    select fnd_coding_rule_objects_s.nextval
      into v_coding_rule_object_id
      from dual;
  
    insert into fnd_coding_rule_objects
      (coding_rule_object_id,
       document_category,
       coding_rule_id,
       document_type,
       company_id,
       operation_unit_id,
       priority,
       enabled_flag,
       created_by,
       creation_date,
       last_updated_by,
       last_update_date)
    values
      (v_coding_rule_object_id,
       p_document_category,
       p_coding_rule_id,
       p_document_type,
       p_company_id,
       p_operation_unit_id,
       p_priority,
       p_enabled_flag,
       p_created_by,
       sysdate,
       p_created_by,
       sysdate);
    p_coding_rule_object_id := v_coding_rule_object_id;
  exception
    when dup_val_on_index then
      --捕获 唯一索引错误
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'FND_CODING_RULE_OBJECTS_DUPLICATE',
                                                      p_created_by              => p_created_by,
                                                      p_package_name            => 'fnd_code_rule_pkg',
                                                      p_procedure_function_name => 'insert_fnd_coding_rule_objects');
      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_created_by,
                                                     p_package_name            => 'fnd_code_rule_pkg',
                                                     p_procedure_function_name => 'insert_fnd_coding_rule_objects');
    
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  end insert_fnd_coding_rule_objects;

  procedure update_fnd_coding_rule_objects(p_coding_rule_object_id number,
                                           p_coding_rule_id        number,
                                           p_enabled_flag          varchar2,
                                           p_priority              number,
                                           p_last_updated_by       number) is
  begin
  
    update fnd_coding_rule_objects o
       set o.last_updated_by  = p_last_updated_by,
           o.last_update_date = sysdate,
           o.coding_rule_id   = p_coding_rule_id,
           o.priority         = p_priority,
           o.enabled_flag     = p_enabled_flag
     where o.coding_rule_object_id = p_coding_rule_object_id;
  end update_fnd_coding_rule_objects;

  --校验编码规则是否已被引用
  function coding_rule_details_modify_chk(p_coding_rule_id number)
    return varchar2 is
    v_ret varchar2(1);
  begin
    select 'N'
      into v_ret
      from dual
     where exists (select 1
              from fnd_coding_rule_values v
             where v.coding_rule_id = p_coding_rule_id);
  
    return v_ret;
  exception
    when no_data_found then
      return 'Y';
  end coding_rule_details_modify_chk;

  /*--编码规则只能有一个启用
  procedure coding_rules_check(p_coding_rule_object_id number,
                               p_created_by            number) is
    v_count number;
  
    e_fnd_c_r_enabled_error exception;
  begin
    select count(1)
      into v_count
      from fnd_coding_rules r
     where r.coding_rule_object_id = p_coding_rule_object_id
       and r.enabled_flag = 'Y';
  
    if v_count > 1 then
      raise e_fnd_c_r_enabled_error;
    end if;
  
  exception
    when e_fnd_c_r_enabled_error then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'FND_CODING_RULE_ENABLED_FLAG_DUPLICATE',
                                                      p_created_by              => p_created_by,
                                                      p_package_name            => 'fnd_code_rule_pkg',
                                                      p_procedure_function_name => 'coding_rules_check');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  end coding_rules_check;*/

  --编码规则
  procedure insert_fnd_coding_rules(p_document_category varchar2,
                                    p_coding_rule_code  varchar2,
                                    p_description       varchar2,
                                    p_note              varchar2,
                                    p_enabled_flag      varchar2,
                                    p_created_by        number,
                                    p_reset_frequence   varchar2,
                                    p_coding_rule_id    out number) is
    v_description_id fnd_coding_rules.description_id%type;
  begin
    v_description_id := fnd_description_pkg.get_fnd_description_id;
    select fnd_coding_rules_s.nextval into p_coding_rule_id from dual;
    insert into fnd_coding_rules
      (document_category,
       coding_rule_id,
       coding_rule_code,
       description_id,
       note,
       enabled_flag,
       created_by,
       creation_date,
       last_updated_by,
       last_update_date,
       reset_frequence)
    values
      (p_document_category,
       p_coding_rule_id,
       upper(p_coding_rule_code),
       v_description_id,
       p_note,
       p_enabled_flag,
       p_created_by,
       sysdate,
       p_created_by,
       sysdate,
       p_reset_frequence);
  
    fnd_description_pkg.reset_fnd_descriptions(p_description_id   => v_description_id,
                                               p_ref_table        => 'FND_CODING_RULES',
                                               p_ref_field        => 'DESCRIPTION_ID',
                                               p_description_text => p_description,
                                               p_function_name    => c_function_code,
                                               p_created_by       => p_created_by,
                                               p_last_updated_by  => p_created_by,
                                               p_language_code    => userenv('lang'));
  
  exception
    when dup_val_on_index then
      --捕获 唯一索引错误
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'FND_CODING_RULE_DUPLICATE',
                                                      p_created_by              => p_created_by,
                                                      p_package_name            => 'fnd_code_rule_pkg',
                                                      p_procedure_function_name => 'insert_fnd_coding_rules');
      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_created_by,
                                                     p_package_name            => 'fnd_code_rule_pkg',
                                                     p_procedure_function_name => 'insert_fnd_coding_rules');
    
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  end insert_fnd_coding_rules;

  procedure update_fnd_coding_rules(p_coding_rule_id  number,
                                    p_description     varchar2,
                                    p_note            varchar2,
                                    p_enabled_flag    varchar2,
                                    p_last_updated_by number,
                                    p_reset_frequence varchar2) is
    v_description_id fnd_coding_rules.description_id%type;
  
    v_reset_frequence fnd_coding_rules.reset_frequence%type;
  begin
  
    select description_id, reset_frequence
      into v_description_id, v_reset_frequence
      from fnd_coding_rules
     where coding_rule_id = p_coding_rule_id;
  
    if p_reset_frequence <> v_reset_frequence and
       coding_rule_details_modify_chk(p_coding_rule_id) = 'N' then
      raise e_fnd_c_r_status_error;
    end if;
  
    update fnd_coding_rules r
       set r.last_updated_by  = p_last_updated_by,
           r.reset_frequence  = p_reset_frequence,
           r.last_update_date = sysdate,
           r.enabled_flag     = p_enabled_flag,
           r.note             = p_note
     where coding_rule_id = p_coding_rule_id;
  
    fnd_description_pkg.reset_fnd_descriptions(p_description_id   => v_description_id,
                                               p_ref_table        => 'FND_CODING_RULES',
                                               p_ref_field        => 'DESCRIPTION_ID',
                                               p_description_text => p_description,
                                               p_function_name    => c_function_code,
                                               p_created_by       => p_last_updated_by,
                                               p_last_updated_by  => p_last_updated_by,
                                               p_language_code    => userenv('lang'));
  
  exception
    when e_fnd_c_r_status_error then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'FND_CODING_RULE_STATUS_CHECK_ERROR',
                                                      p_created_by              => p_last_updated_by,
                                                      p_package_name            => 'fnd_code_rule_pkg',
                                                      p_procedure_function_name => 'update_fnd_coding_rules');
      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_last_updated_by,
                                                     p_package_name            => 'fnd_code_rule_pkg',
                                                     p_procedure_function_name => 'update_fnd_coding_rules');
    
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  end update_fnd_coding_rules;

  procedure delete_fnd_coding_rules(p_coding_rule_id  number,
                                    p_last_updated_by number) is
  begin
    if coding_rule_details_modify_chk(p_coding_rule_id) = 'Y' then
    
      delete from fnd_coding_rule_details d
       where d.coding_rule_id = p_coding_rule_id;
    
      delete from fnd_coding_rules d
       where d.coding_rule_id = p_coding_rule_id;
    else
      raise e_fnd_c_r_status_error;
    end if;
  
  exception
    when e_fnd_c_r_status_error then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'FND_CODING_RULE_STATUS_CHECK_ERROR',
                                                      p_created_by              => p_last_updated_by,
                                                      p_package_name            => 'fnd_code_rule_pkg',
                                                      p_procedure_function_name => 'delete_fnd_coding_rules');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  end delete_fnd_coding_rules;

  procedure binary_integer_check(p_nubmber number) is
    v_temp number;
  begin
    if p_nubmber <= 0 then
      raise e_binary_integer_error;
    end if;
  
    select instr(p_nubmber, '.') into v_temp from dual;
    if v_temp > 0 then
      raise e_binary_integer_error;
    end if;
  
  end binary_integer_check;

  --规则长度校验
  procedure coding_detail_rules_check(p_coding_rule_id number,
                                      p_segment_type   varchar2,
                                      p_length         number,
                                      p_incremental    number,
                                      p_start_value    number) is
    v_max_length number;
    v_count      number;
  begin
    --06 序列号校验
    if p_segment_type = '06' and
       (p_length is null or p_incremental is null or p_start_value is null) then
      raise e_fnd_segment_type_06_err;
    end if;
  
    if p_segment_type = '06' then
      if length(p_start_value) > p_length then
        raise e_fnd_c_r_start_value_error;
      end if;
    
      if length(p_incremental) > p_length then
        raise e_fnd_c_r_incremental_error;
      end if;
    end if;
  
    --(日期格式 + 序列号位数 + 字符串 )总长度校验
    select sum(sub_length) + (decode(p_segment_type, '06', p_length, 0))
      into v_max_length
      from (select nvl(length(d.segment_value), 0) +
                   nvl(length(d.date_format), 0) sub_length
              from fnd_coding_rule_details d
             where d.coding_rule_id = p_coding_rule_id);
  
    if v_max_length > 30 then
      raise e_fnd_c_r_max_length_error;
    end if;
  
    --序列号只能有一个
    select count(1)
      into v_count
      from fnd_coding_rule_details d
     where d.coding_rule_id = p_coding_rule_id
       and d.segment_type = '06';
  
    if v_count > 1 then
      raise e_fnd_c_r_segment_error;
    end if;
  
  end coding_detail_rules_check;

  --规则明细
  procedure insert_fnd_coding_rule_details(p_coding_rule_id number,
                                           p_sequence       number,
                                           p_segment_type   varchar2,
                                           p_segment_value  varchar2,
                                           p_length         number,
                                           p_incremental    number,
                                           p_start_value    number,
                                           p_created_by     number,
                                           p_date_format    varchar2,
                                           p_display_flag   varchar2) is
  begin
    if coding_rule_details_modify_chk(p_coding_rule_id) = 'Y' then
    
      if p_segment_type = '02' and p_date_format is null then
        raise e_fnd_segment_type_02_err;
      end if;
    
      binary_integer_check(p_sequence);
      binary_integer_check(p_length);
      binary_integer_check(p_incremental);
      binary_integer_check(p_start_value);
    
      insert into fnd_coding_rule_details
        (coding_rule_line_id,
         coding_rule_id,
         sequence,
         segment_type,
         segment_value,
         length,
         incremental,
         start_value,
         created_by,
         creation_date,
         last_updated_by,
         last_update_date,
         date_format,
         display_flag)
      values
        (fnd_coding_rule_details_s.nextval,
         p_coding_rule_id,
         p_sequence,
         p_segment_type,
         p_segment_value,
         p_length,
         p_incremental,
         p_start_value,
         p_created_by,
         sysdate,
         p_created_by,
         sysdate,
         p_date_format,
         p_display_flag);
    
      coding_detail_rules_check(p_coding_rule_id => p_coding_rule_id,
                                p_segment_type   => p_segment_type,
                                p_length         => p_length,
                                p_incremental    => p_incremental,
                                p_start_value    => p_start_value);
    else
      raise e_fnd_c_r_status_error;
    end if;
  
  exception
    when e_binary_integer_error then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'FND_CODING_RULE_BINARY_INTEGER_ERR',
                                                      p_created_by              => p_created_by,
                                                      p_package_name            => 'fnd_code_rule_pkg',
                                                      p_procedure_function_name => 'insert_fnd_coding_rule_details');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    when e_fnd_segment_type_06_err then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'FND_CODING_RULE_SEGMENT_TYPE_06_ERR',
                                                      p_created_by              => p_created_by,
                                                      p_package_name            => 'fnd_code_rule_pkg',
                                                      p_procedure_function_name => 'insert_fnd_coding_rule_details');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    when e_fnd_segment_type_02_err then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'FND_CODING_RULE_SEGMENT_TYPE_02_ERR',
                                                      p_created_by              => p_created_by,
                                                      p_package_name            => 'fnd_code_rule_pkg',
                                                      p_procedure_function_name => 'insert_fnd_coding_rule_details');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    when e_fnd_c_r_status_error then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'FND_CODING_RULE_STATUS_CHECK_ERROR',
                                                      p_created_by              => p_created_by,
                                                      p_package_name            => 'fnd_code_rule_pkg',
                                                      p_procedure_function_name => 'insert_fnd_coding_rule_details');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    when e_fnd_c_r_segment_error then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'FND_CODING_RULE_DETAILS_SEGMENT_DUPLICATE',
                                                      p_created_by              => p_created_by,
                                                      p_package_name            => 'fnd_code_rule_pkg',
                                                      p_procedure_function_name => 'insert_fnd_coding_rule_details');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    when e_fnd_c_r_max_length_error then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'FND_CODING_RULE_MAX_LENGTH_ERROR',
                                                      p_created_by              => p_created_by,
                                                      p_package_name            => 'fnd_code_rule_pkg',
                                                      p_procedure_function_name => 'insert_fnd_coding_rule_details');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    when e_fnd_c_r_start_value_error then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'FND_CODING_RULES_TART_VALUE_ERROR',
                                                      p_created_by              => p_created_by,
                                                      p_package_name            => 'fnd_code_rule_pkg',
                                                      p_procedure_function_name => 'insert_fnd_coding_rule_details');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    when e_fnd_c_r_incremental_error then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'FND_CODING_RULE_INCREMENTAL_ERROR',
                                                      p_created_by              => p_created_by,
                                                      p_package_name            => 'fnd_code_rule_pkg',
                                                      p_procedure_function_name => 'insert_fnd_coding_rule_details');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    when dup_val_on_index then
      --捕获 唯一索引错误
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'FND_CODING_RULE_DETAILS_SEQ_DUPLICATE',
                                                      p_created_by              => p_created_by,
                                                      p_package_name            => 'fnd_code_rule_pkg',
                                                      p_procedure_function_name => 'insert_fnd_coding_rule_details');
      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_created_by,
                                                     p_package_name            => 'fnd_code_rule_pkg',
                                                     p_procedure_function_name => 'insert_fnd_coding_rule_details');
    
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  end insert_fnd_coding_rule_details;

  procedure update_fnd_coding_rule_details(p_coding_rule_line_id number,
                                           p_coding_rule_id      number,
                                           p_sequence            number,
                                           p_segment_type        varchar2,
                                           p_segment_value       varchar2,
                                           p_length              number,
                                           p_incremental         number,
                                           p_start_value         number,
                                           p_last_updated_by     number,
                                           p_date_format         varchar2,
                                           p_display_flag        varchar2) is
  
  begin
    if coding_rule_details_modify_chk(p_coding_rule_id) = 'Y' then
      if p_segment_type = '02' and p_date_format is null then
        raise e_fnd_segment_type_02_err;
      end if;
    
      binary_integer_check(p_sequence);
      binary_integer_check(p_length);
      binary_integer_check(p_incremental);
      binary_integer_check(p_start_value);
    
      update fnd_coding_rule_details d
         set d.sequence         = p_sequence,
             d.segment_type     = p_segment_type,
             d.segment_value    = p_segment_value,
             d.length           = p_length,
             d.incremental      = p_incremental,
             d.start_value      = p_start_value,
             d.last_updated_by  = p_last_updated_by,
             d.last_update_date = sysdate,
             d.date_format      = p_date_format,
             d.display_flag     = p_display_flag
       where d.coding_rule_line_id = p_coding_rule_line_id;
    
      coding_detail_rules_check(p_coding_rule_id => p_coding_rule_id,
                                p_segment_type   => p_segment_type,
                                p_length         => p_length,
                                p_incremental    => p_incremental,
                                p_start_value    => p_start_value);
    else
      raise e_fnd_c_r_status_error;
    end if;
  exception
    when e_binary_integer_error then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'FND_CODING_RULE_BINARY_INTEGER_ERR',
                                                      p_created_by              => p_last_updated_by,
                                                      p_package_name            => 'fnd_code_rule_pkg',
                                                      p_procedure_function_name => 'update_fnd_coding_rule_details');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    when e_fnd_segment_type_02_err then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'FND_CODING_RULE_SEGMENT_TYPE_02_ERR',
                                                      p_created_by              => p_last_updated_by,
                                                      p_package_name            => 'fnd_code_rule_pkg',
                                                      p_procedure_function_name => 'update_fnd_coding_rule_details');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    when e_fnd_segment_type_06_err then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'FND_CODING_RULE_SEGMENT_TYPE_06_ERR',
                                                      p_created_by              => p_last_updated_by,
                                                      p_package_name            => 'fnd_code_rule_pkg',
                                                      p_procedure_function_name => 'update_fnd_coding_rule_details');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    when e_fnd_c_r_segment_error then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'FND_CODING_RULE_DETAILS_SEGMENT_DUPLICATE',
                                                      p_created_by              => p_last_updated_by,
                                                      p_package_name            => 'fnd_code_rule_pkg',
                                                      p_procedure_function_name => 'update_fnd_coding_rule_details');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    when e_fnd_c_r_max_length_error then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'FND_CODING_RULE_MAX_LENGTH_ERROR',
                                                      p_created_by              => p_last_updated_by,
                                                      p_package_name            => 'fnd_code_rule_pkg',
                                                      p_procedure_function_name => 'update_fnd_coding_rule_details');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    when e_fnd_c_r_start_value_error then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'FND_CODING_RULES_TART_VALUE_ERROR',
                                                      p_created_by              => p_last_updated_by,
                                                      p_package_name            => 'fnd_code_rule_pkg',
                                                      p_procedure_function_name => 'update_fnd_coding_rule_details');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    when e_fnd_c_r_incremental_error then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'FND_CODING_RULE_INCREMENTAL_ERROR',
                                                      p_created_by              => p_last_updated_by,
                                                      p_package_name            => 'fnd_code_rule_pkg',
                                                      p_procedure_function_name => 'update_fnd_coding_rule_details');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    when e_fnd_c_r_status_error then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'FND_CODING_RULE_STATUS_CHECK_ERROR',
                                                      p_created_by              => p_last_updated_by,
                                                      p_package_name            => 'fnd_code_rule_pkg',
                                                      p_procedure_function_name => 'update_fnd_coding_rule_details');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    when dup_val_on_index then
    
      --捕获 唯一索引错误
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'FND_CODING_RULE_DETAILS_SEQ_DUPLICATE',
                                                      p_created_by              => p_last_updated_by,
                                                      p_package_name            => 'fnd_code_rule_pkg',
                                                      p_procedure_function_name => 'update_fnd_coding_rule_details');
      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_last_updated_by,
                                                     p_package_name            => 'fnd_code_rule_pkg',
                                                     p_procedure_function_name => 'update_fnd_coding_rule_details');
    
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  end update_fnd_coding_rule_details;

  procedure delete_fnd_coding_rule_details(p_coding_rule_line_id number,
                                           p_coding_rule_id      number,
                                           p_last_updated_by     number) is
  begin
    if coding_rule_details_modify_chk(p_coding_rule_id) = 'Y' then
      delete from fnd_coding_rule_details d
       where d.coding_rule_line_id = p_coding_rule_line_id;
    else
      raise e_fnd_c_r_status_error;
    end if;
  
  exception
  
    when e_fnd_c_r_status_error then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'FND_CODING_RULE_STATUS_CHECK_ERROR',
                                                      p_created_by              => p_last_updated_by,
                                                      p_package_name            => 'fnd_code_rule_pkg',
                                                      p_procedure_function_name => 'delete_fnd_coding_rule_details');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  end delete_fnd_coding_rule_details;

  --编码规则当前值
  procedure insert_fnd_coding_rule_values(p_coding_rule_id    number,
                                          p_company_id        number,
                                          p_year              number,
                                          p_period_name       varchar2,
                                          p_current_value     number,
                                          p_created_by        number,
                                          p_document_category varchar2,
                                          p_document_type     varchar2,
                                          p_operation_unit_id number,
                                          p_bp_info           varchar2,
                                          p_province_id       number,
                                          p_days              varchar2,
                                          --p_product_code     varchar2,
                                          p_agent_value varchar2 default null) is
  begin
    insert into fnd_coding_rule_values
      (coding_rule_id,
       company_id,
       year,
       period_name,
       current_value,
       created_by,
       creation_date,
       last_updated_by,
       last_update_date,
       document_category,
       document_type,
       operation_unit_id,
       agent_value,
       bp_info,
       province_id,
       days)
    values
      (p_coding_rule_id,
       '', --p_company_id,
       p_year,
       p_period_name,
       p_current_value,
       p_created_by,
       sysdate,
       p_created_by,
       sysdate,
       p_document_category,
       null, --p_document_type,
       null, --p_operation_unit_id
       p_agent_value,
       p_bp_info,
       p_province_id,
       p_days);
  end insert_fnd_coding_rule_values;

  function get_coding_rule_object_id(p_document_category        varchar2,
                                     p_document_type            varchar2,
                                     p_company_id               number,
                                     p_operation_unit_id        number,
                                     p_object_document_type     out varchar2,
                                     p_object_company_id        out number,
                                     p_object_operation_unit_id out number,
                                     p_object_coding_rule_id    out number,
                                     p_reset_frequence          out varchar2)
    return number is
    cursor cur_priority is
      select o.coding_rule_object_id,
             o.priority,
             o.document_type,
             o.company_id,
             o.operation_unit_id,
             o.coding_rule_id,
             r.reset_frequence
        from fnd_coding_rule_objects o, fnd_coding_rules r
       where o.document_category = p_document_category
         and o.enabled_flag = 'Y'
         and o.coding_rule_id = r.coding_rule_id
         and r.enabled_flag = 'Y'
       order by o.priority;
  
    v_coding_rule_object_id fnd_coding_rule_objects.coding_rule_object_id%type;
  begin
    v_coding_rule_object_id := null;
    if p_document_type is null then
      for c_priority in cur_priority loop
        if nvl(c_priority.document_type, nvl(p_document_type, -1)) =
           nvl(p_document_type, -1) and
           nvl(c_priority.company_id, nvl(p_company_id, -1)) =
           nvl(p_company_id, -1) and
           nvl(c_priority.operation_unit_id, nvl(p_operation_unit_id, -1)) =
           nvl(p_operation_unit_id, -1) then
        
          v_coding_rule_object_id := c_priority.coding_rule_object_id;
        
          p_object_document_type     := c_priority.document_type;
          p_object_company_id        := c_priority.company_id;
          p_object_operation_unit_id := c_priority.operation_unit_id;
          p_object_coding_rule_id    := c_priority.coding_rule_id;
          p_reset_frequence          := c_priority.reset_frequence;
          exit;
        
        end if;
      
      end loop;
    else
      for c_coding_rules in (select o.coding_rule_object_id,
                                    o.priority,
                                    o.document_type,
                                    o.company_id,
                                    o.operation_unit_id,
                                    o.coding_rule_id,
                                    r.reset_frequence
                               from fnd_coding_rule_objects o,
                                    fnd_coding_rules        r
                              where o.document_category =
                                    p_document_category
                                and o.document_type = p_document_type
                                and o.enabled_flag = 'Y'
                                and o.coding_rule_id = r.coding_rule_id
                                and r.enabled_flag = 'Y'
                              order by o.priority) loop
        if nvl(c_coding_rules.company_id, nvl(p_company_id, -1)) =
           nvl(p_company_id, -1) and
           nvl(c_coding_rules.operation_unit_id,
               nvl(p_operation_unit_id, -1)) = nvl(p_operation_unit_id, -1) then
        
          v_coding_rule_object_id := c_coding_rules.coding_rule_object_id;
        
          p_object_document_type     := c_coding_rules.document_type;
          p_object_company_id        := c_coding_rules.company_id;
          p_object_operation_unit_id := c_coding_rules.operation_unit_id;
          p_object_coding_rule_id    := c_coding_rules.coding_rule_id;
          p_reset_frequence          := c_coding_rules.reset_frequence;
          exit;
        end if;
      end loop;
    end if;
  
    return v_coding_rule_object_id;
  exception
    when no_data_found then
      return null;
  end get_coding_rule_object_id;

  /*function get_coding_rule_id(p_coding_rule_object_id number,
                              p_reset_frequence       out varchar2)
    return number is
    v_coding_rule_id fnd_coding_rules.coding_rule_id%type;
  begin
    select coding_rule_id, reset_frequence
      into v_coding_rule_id, p_reset_frequence
      from fnd_coding_rules
     where coding_rule_object_id = p_coding_rule_object_id
       and enabled_flag = 'Y';
  
    return v_coding_rule_id;
  
  exception
    when no_data_found then
      return null;
  end get_coding_rule_id;*/

  function get_current_value(p_coding_rule_id    number,
                             p_company_id        number,
                             p_year              number,
                             p_period_name       varchar2,
                             p_document_category varchar2,
                             p_document_type     varchar2,
                             p_operation_unit_id number,
                             p_bp_info           varchar2 default null,
                             p_province_id       number default null,
                             p_days              varchar2,
                             -- p_product_code      varchar2, -- add by liukang
                             p_agent_value varchar2 default null)
    return number is
    v_current_value fnd_coding_rule_values.current_value%type;
  begin
    select current_value
      into v_current_value
      from fnd_coding_rule_values v
     where coding_rule_id = p_coding_rule_id
       and v.document_category = p_document_category
       and (bp_info = p_bp_info or (bp_info is null and p_bp_info is null))
       and (province_id = p_province_id or
           (province_id is null and p_province_id is null))
          --AND (operation_unit_id = p_operation_unit_id OR
          --    (operation_unit_id IS NULL AND p_operation_unit_id IS NULL))
       and (v.year = p_year or (v.year is null and p_year is null))
       and (period_name = p_period_name or
           (period_name is null and p_period_name is null))
       and (agent_value = p_agent_value or (p_agent_value is null))
       and (v.days = p_days or (v.days is null and p_days is null));
    --add by liukang
    --and v.product_code = p_product_code;
  
    return v_current_value;
  exception
    when no_data_found then
      return null;
  end get_current_value;

  function get_gld_period_name(p_company_id number, p_operation_date date)
    return varchar2 is
    v_period_name gld_periods.period_name%type;
  begin
    select g.period_name
      into v_period_name
      from gld_periods g
    --, gld_period_status p
     where g.start_date <= trunc(p_operation_date)
       and g.end_date >= trunc(p_operation_date)
          --and g.period_set_code = p.period_set_code
          --and p.company_id = p_company_id
          --and p.internal_period_num = g.internal_period_num
          --and p.period_status_code = 'O'  --for mantis 0025577
       and g.period_set_code =
           (select b.period_set_code
              from gld_set_of_books b
             where b.set_of_books_id =
                   (select f.set_of_books_id
                      from fnd_companies f
                     where f.company_id = p_company_id))
       and g.adjustment_flag = 'N';
  
    return v_period_name;
  
  exception
    when no_data_found then
      return null;
  end get_gld_period_name;

  function get_company_code(p_company_id number) return varchar2 is
    v_company_code fnd_companies.company_code%type;
  begin
    select company_code
      into v_company_code
      from fnd_companies
     where company_id = p_company_id;
    return v_company_code;
  exception
    when no_data_found then
      return null;
  end get_company_code;

  --获取经营单位代码
  function get_operation_unit_code(p_operation_unit_id number)
    return varchar2 is
    v_operation_unit_code fnd_operation_units.operation_unit_code%type;
  begin
    select operation_unit_code
      into v_operation_unit_code
      from fnd_operation_units o
     where o.operation_unit_id = p_operation_unit_id;
  
    return v_operation_unit_code;
  exception
    when no_data_found then
      return null;
  end get_operation_unit_code;

  function get_detail_segment_value(p_coding_rule_id number,
                                    p_segment_type   varchar2) return boolean is
    v_exists number;
  begin
    select 1
      into v_exists
      from dual
     where exists (select 1
              from fnd_coding_rule_details d
             where d.coding_rule_id = p_coding_rule_id
               and d.segment_type = p_segment_type);
  
    return true;
  exception
    when no_data_found then
      return false;
    
  end get_detail_segment_value;

  --获取厂商代码  add by liukang 20160617
  function get_product_code(p_product_plan_id number) return varchar2 is
    v_product_code varchar2(2000);
  begin
    select hp.product_code
      into v_product_code
      from hls_product_plan_definition hp
     where hp.product_plan_id = p_product_plan_id;
  
    return v_product_code;
  exception
    when no_data_found then
      return null;
  end get_product_code;

  function get_rule_next_auto_num(p_document_category varchar2,
                                  p_document_type     varchar2,
                                  p_company_id        number,
                                  p_operation_unit_id number,
                                  p_operation_date    date,
                                  p_division          varchar2 default null,
                                  p_bp_info           varchar2 default null,
                                  p_province_id       number default null,
                                  p_agent_code        varchar2 default null,
                                  p_price_ref_v05     varchar2 default null,
                                  p_product_code      varchar2 default null, -- 产品编号 add  by liukang
                                  p_created_by        number) return varchar2 is
  
    pragma autonomous_transaction;
  
    cursor cur_details(p_coding_rule_id number) is
      select d.segment_type,
             d.segment_value,
             d.date_format,
             nvl(d.display_flag, 'N') display_flag
        from fnd_coding_rule_details d
       where d.coding_rule_id = p_coding_rule_id
       order by d.sequence
         for update;
    v_details                  cur_details%rowtype;
    v_coding_rule_object_id    fnd_coding_rule_objects.coding_rule_object_id%type;
    v_object_document_type     fnd_coding_rule_objects.document_type%type;
    v_object_company_id        fnd_coding_rule_objects.company_id%type;
    v_object_operation_unit_id fnd_coding_rule_objects.operation_unit_id%type;
  
    v_reset_frequence fnd_coding_rules.reset_frequence%type;
  
    v_coding_rule_id fnd_coding_rules.coding_rule_id%type;
  
    v_length      fnd_coding_rule_details.length%type;
    v_incremental fnd_coding_rule_details.incremental%type;
    v_start_value fnd_coding_rule_details.start_value%type;
  
    v_current_value fnd_coding_rule_values.current_value%type;
    v_year          fnd_coding_rule_values.year%type;
    v_period_name   fnd_coding_rule_values.period_name%type;
  
    --当前值
    v_cur_document_type     fnd_coding_rule_values.document_type%type;
    v_cur_operation_unit_id fnd_coding_rule_values.operation_unit_id%type;
    v_cur_company_id        fnd_coding_rule_values.company_id%type;
  
    --规则明细定义的段值
    v_segment_document_type     fnd_coding_rule_values.document_type%type;
    v_segment_operation_unit_id fnd_coding_rule_values.operation_unit_id%type;
    v_segment_company_id        fnd_coding_rule_values.company_id%type;
  
    v_operation_date date;
    v_doc_number     varchar2(500);
    v_doc_number_tmp varchar2(30);
    v_agent_value    varchar2(30);
    v_days           varchar2(30);
  
    e_null_error              exception;
    e_codeing_rule_null_error exception;
  
    v_err_msg varchar2(2000);
  
    --产品编号 add by liukang
    --v_product_code varchar2(2000);
  
  begin
  
    --v_product_code := get_product_code(p_product_plan_id => p_product_plan_id);
    -- 获取产品编号 add by liukang
  
    g_company_id        := p_company_id;
    g_document_category := p_document_category;
    g_document_type     := p_document_type;
    g_operation_unit_id := p_operation_unit_id;
    g_operation_date    := p_operation_date;
  
    --获取编码规则对象
    v_coding_rule_object_id := get_coding_rule_object_id(p_document_category        => p_document_category,
                                                         p_document_type            => p_document_type,
                                                         p_company_id               => p_company_id,
                                                         p_operation_unit_id        => p_operation_unit_id,
                                                         p_object_document_type     => v_object_document_type,
                                                         p_object_company_id        => v_object_company_id,
                                                         p_object_operation_unit_id => v_object_operation_unit_id,
                                                         p_object_coding_rule_id    => v_coding_rule_id,
                                                         p_reset_frequence          => v_reset_frequence);
  
    if v_coding_rule_object_id is null then
      error_log(p_message => sys_message_pkg.get_string('FND_CODING_RULE_OBJECT_NULL'),
                p_user_id => p_created_by);
      rollback;
      -- raise e_codeing_rule_null_error;
      return c_error;
    end if;
  
    if v_coding_rule_id is null then
      error_log(p_message => sys_message_pkg.get_string('FND_CODING_RULE_NULL'),
                p_user_id => p_created_by);
      rollback;
      -- raise e_codeing_rule_null_error;
      return c_error;
    end if;
  
    --锁表
    open cur_details(v_coding_rule_id);
  
    --获取开始值、步长、位数
    begin
      select length, incremental, start_value
        into v_length, v_incremental, v_start_value
        from fnd_coding_rule_details d
       where d.coding_rule_id = v_coding_rule_id
         and d.segment_type = '06';
    exception
      when no_data_found then
        error_log(p_message => sys_message_pkg.get_string('FND_CODING_RULE_SEQUENCE_NULL'),
                  p_user_id => p_created_by);
        raise e_null_error;
    end;
  
    if p_operation_date is null then
      select to_date(to_char(sysdate, 'YYYYMMDD'), 'YYYYMMDD')
        into v_operation_date
        from dual;
    else
      v_operation_date := p_operation_date;
    end if;
  
    if v_reset_frequence = 'NEVER' then
      v_year        := '';
      v_period_name := '';
    elsif v_reset_frequence = 'PERIOD' then
      v_year        := '';
      v_period_name := get_gld_period_name(p_company_id     => p_company_id,
                                           p_operation_date => v_operation_date);
      if v_period_name is null then
        error_log(p_message => sys_message_pkg.get_string('FND_CODING_RULE_PERIOD_NAME_NULL'),
                  p_user_id => p_created_by);
        raise e_null_error;
      end if;
    
    elsif v_reset_frequence = 'YEAR' then
      v_period_name := '';
      v_year        := to_number(to_char(v_operation_date, 'YYYY'));
    elsif v_reset_frequence = 'DAY' then
      v_days := to_char(v_operation_date, 'YYYY-MM-DD');
    end if;
  
    --规则明细界面 段值
    if get_detail_segment_value(v_coding_rule_id, '04') then
      v_segment_company_id := p_company_id;
    end if;
  
    if get_detail_segment_value(v_coding_rule_id, '05') then
      v_segment_operation_unit_id := p_operation_unit_id;
    end if;
  
    if get_detail_segment_value(v_coding_rule_id, '03') then
      v_segment_document_type := p_document_type;
    end if;
  
    --取明细界面段值
    v_cur_company_id        := v_segment_company_id;
    v_cur_document_type     := v_segment_document_type;
    v_cur_operation_unit_id := v_segment_operation_unit_id;
  
    --取不到明细值,再取规则对象界面中的值
    if v_cur_company_id is null then
      v_cur_company_id := v_object_company_id;
    end if;
    if v_cur_document_type is null then
      v_cur_document_type := v_object_document_type;
    end if;
    if v_cur_operation_unit_id is null then
      v_cur_operation_unit_id := v_object_operation_unit_id;
    end if;
  
    --商业伙伴特俗编码规则
    if p_bp_info is not null and p_province_id is not null then
      if p_bp_info = 'GROUP_IN' then
        v_agent_value := 'R';
      elsif p_bp_info = 'GROUP_OUT' then
        v_agent_value := 'S';
      end if;
    
      select v_agent_value || a.province_abbr
        into v_agent_value
        from fnd_province a
       where a.province_id = p_province_id;
    else
      v_agent_value := null;
    end if;
  
    v_current_value := get_current_value(p_coding_rule_id    => v_coding_rule_id,
                                         p_company_id        => v_cur_company_id,
                                         p_year              => v_year,
                                         p_period_name       => v_period_name,
                                         p_document_category => p_document_category,
                                         p_document_type     => v_cur_document_type,
                                         p_operation_unit_id => v_cur_operation_unit_id,
                                         p_bp_info           => p_bp_info,
                                         p_province_id       => p_province_id,
                                         p_days              => v_days,
                                         --p_product_code   => v_product_code, -- add by liukang
                                         p_agent_value => v_agent_value);
  
    if v_current_value is null then
      v_current_value := v_start_value;
      insert_fnd_coding_rule_values(p_coding_rule_id    => v_coding_rule_id,
                                    p_company_id        => v_cur_company_id,
                                    p_year              => v_year,
                                    p_period_name       => v_period_name,
                                    p_current_value     => v_current_value,
                                    p_created_by        => p_created_by,
                                    p_document_category => p_document_category,
                                    p_document_type     => v_cur_document_type,
                                    p_operation_unit_id => v_cur_operation_unit_id,
                                    p_bp_info           => p_bp_info,
                                    p_province_id       => p_province_id,
                                    p_days              => v_days,
                                    --p_product_code   => v_product_code, -- add by liukang
                                    p_agent_value => v_agent_value);
    else
    
      --当前值非空,判断是否达到最大位数
      if length(v_current_value + v_incremental) > v_length then
        --v_current_value := v_current_value;
        error_log(p_message => sys_message_pkg.get_string('FND_CODING_RULE_SEQUENCE_MAX'),
                  p_user_id => p_created_by);
        rollback;
        return c_error;
      else
        v_current_value := v_current_value + v_incremental;
      end if;
    
      update fnd_coding_rule_values v
         set v.current_value    = v_current_value,
             v.last_update_date = sysdate,
             v.last_updated_by  = p_created_by
       where coding_rule_id = v_coding_rule_id
         and v.document_category = p_document_category
         and (bp_info = p_bp_info or
             (bp_info is null and p_bp_info is null))
         and (province_id = p_province_id or
             (province_id is null and p_province_id is null))
            /* AND (operation_unit_id = v_cur_operation_unit_id OR
            (operation_unit_id IS NULL AND
            v_cur_operation_unit_id IS NULL))*/
         and (v.year = v_year or (v.year is null and v_year is null))
         and (v.days = v_days or (v.days is null and v_days is null))
         and (period_name = v_period_name or
             (period_name is null and v_period_name is null));
      -- add by liukang    
      --and v.product_code = v_product_code;
    end if;
  
    loop
      fetch cur_details
        into v_details;
      exit when cur_details%notfound;
    
      v_doc_number_tmp := '';
      if v_details.segment_type = '03' then
        --获取单据类型
        if p_company_id is null or p_document_type is null then
          close cur_details;
          rollback;
          error_log(p_message => sys_message_pkg.get_string('FND_CODING_RULE_SEGMENT_TYPE_06_ERROR'),
                    p_user_id => p_created_by);
          return c_error;
          sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'FND_CODING_RULE_GET_DOC_TYPE_CODE_ERR',
                                                          p_created_by              => p_created_by,
                                                          p_package_name            => 'fnd_code_rule_pkg',
                                                          p_procedure_function_name => 'get_rule_next_auto_num',
                                                          p_token_1                 => '#COMPANY_ID',
                                                          p_token_value_1           => p_company_id,
                                                          p_token_2                 => '#DOCUMENT_TYPE',
                                                          p_token_value_2           => p_document_type);
          raise_application_error(sys_raise_app_error_pkg.c_error_number,
                                  sys_raise_app_error_pkg.g_err_line_id);
        end if;
      
        --HLS修改
        --直接使用document_type作为单据类型
        --不通过value进行关联
        v_doc_number_tmp := p_document_type;
      
      elsif v_details.segment_type = '04' then
        if v_details.display_flag = 'Y' then
          --获取公司编码
          v_doc_number_tmp := get_company_code(p_company_id);
        end if;
      elsif v_details.segment_type = '06' then
      
        --左面补零
        select lpad(v_current_value, v_length, '0')
          into v_doc_number_tmp
          from dual;
      elsif v_details.segment_type = '02' then
        --日期格式
        v_doc_number_tmp := to_char(v_operation_date, v_details.date_format);
      elsif v_details.segment_type = '01' then
        v_doc_number_tmp := v_details.segment_value;
      elsif v_details.segment_type = '05' then
        --获取经营单位代码
        v_doc_number_tmp := get_operation_unit_code(p_operation_unit_id);
      
        -- 内外部
      elsif v_details.segment_type = '07' then
        if p_bp_info = 'GROUP_IN' then
          v_doc_number_tmp := 'R';
        elsif p_bp_info = 'GROUP_OUT' then
          v_doc_number_tmp := 'S';
        end if;
      elsif v_details.segment_type = '08' then
        select a.province_abbr
          into v_doc_number_tmp
          from fnd_province a
         where a.province_id = p_province_id;
      elsif v_details.segment_type = '09' then
        v_doc_number_tmp := p_agent_code;
      elsif v_details.segment_type = '10' then
        v_doc_number_tmp := p_price_ref_v05;
      elsif v_details.segment_type = '11' then
        v_doc_number_tmp := p_product_code;
      end if;
      if v_doc_number is null then
        v_doc_number := v_doc_number_tmp;
      else
        v_doc_number := v_doc_number || v_doc_number_tmp;
      end if;
    
    end loop;
  
    close cur_details;
  
    if length(v_doc_number) > 30 then
      rollback;
      error_log(p_message => sys_message_pkg.get_string('FND_CODING_RULE_MAX_LENGTH'),
                p_user_id => p_created_by);
      --return c_max_length_error;
      return c_error;
    end if;
  
    evt_event_core_pkg.fire_event(p_event_name  => 'FND_CODE_RULE_NEXT',
                                  p_event_param => null,
                                  p_param1      => v_doc_number,
                                  p_param2      => p_document_category,
                                  p_created_by  => p_created_by);
  
    commit;
  
    return v_doc_number;
  
  exception
    when e_codeing_rule_null_error then
      if cur_details%isopen then
        close cur_details;
      end if;
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'FND1910_CODING_RULE_NULL',
                                                      p_created_by              => p_created_by,
                                                      p_package_name            => 'fnd_code_rule_pkg',
                                                      p_procedure_function_name => 'get_rule_next_auto_num');
      return c_error;
    when e_null_error then
      if cur_details%isopen then
        close cur_details;
      end if;
    
      rollback;
      return c_error;
    when others then
      if cur_details%isopen then
        close cur_details;
      end if;
    
      v_err_msg := dbms_utility.format_error_backtrace || ' ' || sqlerrm;
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => v_err_msg,
                                                     p_created_by              => p_created_by,
                                                     p_package_name            => 'fnd_code_rule_pkg',
                                                     p_procedure_function_name => 'get_rule_next_auto_num');
      rollback;
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
      return c_error;
    
  end get_rule_next_auto_num;

end fnd_code_rule_pkg;
/