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