CREATE OR REPLACE PACKAGE BODY hcm_demand_pck IS -- Purpose : HCM-B-P10 -- DO Import Trace -- Created : 2009-11-18 9:01:33 hand -- Modified: g_user_id NUMBER; /*========================================================== HCM-B-P10-01 SAVE_TRACE 保存系统参数记录 参数： p_new_record 新记录标志。Y：新增记录，N(or NULL)：修改记录。 p_user_id 用户ID p_customer_id 客户ID p_product_category 产品类别 p_period_value 时段类型 p_period_num 时段数 p_start_date 生效日期 p_end_date 失效日期 返回值：HCM_PUBLIC_PCK.G_SUCCESS 保存成功 其它值 保存失败时的信息ID Created : 2008-8-20 9:01:33 hand Modified: ==========================================================*/ FUNCTION save_trace(p_new_record IN VARCHAR2 DEFAULT 'N', p_user_id IN NUMBER, p_customer_id IN NUMBER, p_product_category IN VARCHAR2, p_period_value IN VARCHAR2, p_period_num IN NUMBER, p_start_date IN DATE, p_end_date IN DATE) RETURN NUMBER IS ln_count NUMBER := 0; ln_message_id NUMBER := hcm_public_pck.g_success; l_api_name VARCHAR2(100) := 'save_trace'; l_rt_msg VARCHAR2(1000); BEGIN --产品类别不能为空！ hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_DI_CAT_NOT_BE_NULL'); l_rt_msg := hcm_api.get_message(-1); IF p_product_category IS NULL THEN RETURN hcm_public_pck.insert_message(p_user_id, l_rt_msg, 'HCM_DEMAND_TRACE_PCK.SAVE_TRACE'); END IF; --客户不能为空！ hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_DI_CUST_NOT_BE_NULL'); l_rt_msg := hcm_api.get_message(-1); IF p_customer_id IS NULL THEN RETURN hcm_public_pck.insert_message(p_user_id, l_rt_msg, 'HCM_DEMAND_TRACE_PCK.SAVE_TRACE'); END IF; --时段类型不能为空！ hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_DI_PER_NOT_BE_NULL'); l_rt_msg := hcm_api.get_message(-1); IF p_period_value IS NULL THEN RETURN hcm_public_pck.insert_message(p_user_id, l_rt_msg, 'HCM_DEMAND_TRACE_PCK.SAVE_TRACE'); END IF; IF p_new_record = 'I' THEN SELECT COUNT(1) INTO ln_count FROM hcm_demand_trace_type hdtt WHERE hdtt.customer_id = p_customer_id AND hdtt.product_category = p_product_category AND hdtt.period_type = p_period_value; IF ln_count > 0 THEN --当前【客户 + 类别 + 时段类型】组合 已存在！ hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_DI_EXIST_COMB'); l_rt_msg := hcm_api.get_message(-1); RETURN hcm_public_pck.insert_message(p_user_id, l_rt_msg, 'HCM_DEMAND_TRACE_PCK.SAVE_TRACE'); ELSE INSERT INTO hcm_demand_trace_type (trace_type_id, customer_id, product_category, period_type, period_num, start_date, end_date, creation_date, created_by, last_updated_by, last_update_date, last_update_login) VALUES (hcm_demand_trace_type_s.nextval, p_customer_id, p_product_category, p_period_value, p_period_num, p_start_date, nvl(p_end_date, to_date('9999-12-31', 'yyyy-MM-dd')), SYSDATE, p_user_id, p_user_id, SYSDATE, p_user_id); END IF; ELSIF p_new_record = 'U' THEN UPDATE hcm_demand_trace_type SET start_date = p_start_date, end_date = nvl(p_end_date, to_date('9999-12-31', 'yyyy-MM-dd')), period_num = p_period_num WHERE customer_id = p_customer_id AND period_type = p_period_value AND product_category = p_product_category; ELSE DELETE FROM hcm_demand_trace_type hdtt WHERE hdtt.customer_id = p_customer_id AND hdtt.period_type = p_period_value AND hdtt.product_category = p_product_category; END IF; RETURN ln_message_id; EXCEPTION WHEN OTHERS THEN RETURN hcm_public_pck.insert_message(p_user_id, SQLCODE || ':' || SQLERRM, 'HCM_DEMAND_TRACE_PCK.SAVE_TRACE'); END save_trace; /*========================================================== HCM-B-P10-01 SAVE_TRACE_FOR_UI 保存系统参数记录 参数： p_new_record 新记录标志。Y：新增记录，N(or NULL)：修改记录。 p_user_id 用户ID p_customer_id 客户ID p_product_category 产品类别 p_period_value 时段类型 p_period_num 时段数 p_start_date 生效日期 p_end_date 失效日期 返回值：HCM_PUBLIC_PCK.G_SUCCESS 保存成功 其它值 保存失败时的信息ID Created : 2008-8-20 9:01:33 hand Modified: ==========================================================*/ FUNCTION save_trace_for_ui(p_new_record IN VARCHAR2 DEFAULT 'N', p_user_id IN VARCHAR2, p_customer_id IN VARCHAR2, p_product_category IN VARCHAR2, p_period_value IN VARCHAR2, p_period_num IN VARCHAR2, p_start_date IN VARCHAR2, p_end_date IN VARCHAR2) RETURN VARCHAR2 IS ln_message_id NUMBER := hcm_public_pck.g_success; l_user_id NUMBER; l_customer_id NUMBER; l_period_num NUMBER; l_start_date DATE; l_end_date DATE; BEGIN l_user_id := to_number(p_user_id); l_customer_id := to_number(p_customer_id); l_period_num := to_number(p_period_num); l_start_date := hcm_public_pck.string_date_convert((p_start_date)); l_end_date := hcm_public_pck.string_date_convert(p_end_date); ln_message_id := save_trace(p_new_record => p_new_record, p_user_id => l_user_id, p_customer_id => l_customer_id, p_product_category => p_product_category, p_period_value => p_period_value, p_period_num => l_period_num, p_start_date => l_start_date, p_end_date => l_end_date); RETURN hcm_public_pck.get_message(ln_message_id); END save_trace_for_ui; --取p_in_date是第几周 FUNCTION get_week(p_in_date IN DATE) RETURN NUMBER IS l_weeks NUMBER; l_first_date DATE; BEGIN --客户化取周数 /* SELECT to_date(to_char(p_in_date, 'yyyy') || '0101', 'yyyymmdd') INTO l_first_date FROM dual; SELECT ceil((to_char(p_in_date, 'ddd') - decode(to_char(l_first_date, 'D'), 1, 1, 7 - to_char(l_first_date, 'D') + 1)) / 7) + 1 INTO l_weeks FROM dual;*/ --标准取周数 SELECT to_number(to_char(p_in_date, 'IW')) INTO l_weeks FROM dual; RETURN l_weeks; END get_week; FUNCTION get_ver_ft_time(p_in_date IN DATE) RETURN VARCHAR2 IS l_dy NUMBER; l_weeks NUMBER; BEGIN l_weeks := get_week(p_in_date); RETURN REPLACE(substr(to_char(p_in_date, 'yyyy'), 3, 2) || 'W' || to_char(l_weeks, '09'), ' ', ''); END get_ver_ft_time; /* PROCEDURE get_day_of_week(p_in_date IN VARCHAR2, x_out_date OUT NOCOPY NUMBER) IS BEGIN IF instr(p_in_date, 'W') > 0 THEN SELECT to_char(trunc((SELECT trunc(decode(ww, 53, to_date(yy || '3112', 'yyyyddmm'), to_date(yy || '-' || to_char(ww * 7), 'yyyy-ddd')), 'd') last_day FROM (SELECT substr(p_in_date, 1, 4) yy, to_number(substr(p_in_date, 6)) ww FROM dual)), 'yyyy'), 'd') INTO x_out_date FROM dual; END IF; END get_day_of_week;*/ PROCEDURE get_date(p_in_date VARCHAR2, x_out_date1 OUT NOCOPY DATE, x_out_date2 OUT NOCOPY DATE) IS l_dy NUMBER; l_num NUMBER; l_curday DATE; l_count NUMBER; BEGIN IF instr(p_in_date, 'W') > 0 THEN l_dy := to_number(substr(p_in_date, 6)); -- 01 l_curday := to_date(substr(p_in_date, 1, 4) || '01-01', 'yyyy-mm-dd'); -- SELECT to_char(l_curday, 'd') INTO l_num FROM dual; l_count := 7 - l_num; IF l_num > 5 THEN l_dy := l_dy; ELSE l_dy := l_dy - 1; END IF; SELECT l_curday + (l_dy) * 7 + l_count + 1 INTO x_out_date2 FROM dual; x_out_date1 := x_out_date2 - 6; /* GET_DAY_OF_WEEK(to_date(substr(p_in_date,1,4) || '01-01','yyyy-mm-dd'),l_dy); IF l_dy > 5 THEN x_out_date := x_out_date + 7; END IF; */ /* l_dy := to_number(substr(p_in_date, 6)); l_curday := to_date(substr(p_in_date, 1, 4) || '01-01', 'yyyy-mm-dd'); SELECT to_char(l_curday, 'd') INTO l_num FROM dual; x_out_date1 := CASE l_num WHEN 1 THEN (l_dy - 1) * 7 - 6 + l_curday WHEN 2 THEN (l_dy - 1) * 7 - 6 + 6 + l_curday WHEN 3 THEN (l_dy - 1) * 7 - 6 + 5 + l_curday WHEN 4 THEN (l_dy - 1) * 7 - 6 + 4 + l_curday WHEN 5 THEN (l_dy - 1) * 7 - 6 + 3 + l_curday WHEN 6 THEN (l_dy - 1) * 7 - 6 + 2 + l_curday WHEN 7 THEN (l_dy - 1) * 7 - 6 + 1 + l_curday ELSE l_curday END; x_out_date2 := x_out_date1 + 6; IF x_out_date1 < to_date(substr(p_in_date, 1, 4) || '01-01', 'yyyy-mm-dd') THEN x_out_date1 := to_date(substr(p_in_date, 1, 4) || '01-01', 'yyyy-mm-dd'); END IF; IF x_out_date2 > to_date(substr(p_in_date, 1, 4) || '12-31', 'yyyy-mm-dd') THEN x_out_date2 := to_date(substr(p_in_date, 1, 4) || '12-31', 'yyyy-mm-dd'); END IF;*/ ELSIF instr(p_in_date, 'M') > 0 THEN SELECT to_date(yy || '-' || to_char(ww) || '-01', 'yyyy-mm-dd') first_day INTO x_out_date1 FROM (SELECT substr(p_in_date, 1, 4) yy, to_number(substr(p_in_date, 6)) ww FROM dual); ELSE SELECT to_date(p_in_date, 'yyyy-mm-dd') INTO x_out_date1 FROM dual; END IF; EXCEPTION WHEN OTHERS THEN x_out_date1 := NULL; x_out_date2 := NULL; END get_date; --支持批量操作 --从一个字符串中取出一个字段，并返回剩下的字符串 PROCEDURE popup_field(p_string IN OUT CLOB, x_field OUT CLOB, x_next_null_flag OUT VARCHAR2) IS l_first_char VARCHAR2(10); l_pos NUMBER; BEGIN IF p_string IS NULL THEN x_field := NULL; RETURN; END IF; l_first_char := substr(p_string, 1, length(g_default_col_separator)); IF l_first_char = g_col_separator THEN x_field := NULL; p_string := substr(p_string, length(g_default_col_separator) + 1); IF p_string IS NULL THEN x_next_null_flag := 'Y'; END IF; ELSE l_pos := instr(p_string, g_default_col_separator); IF l_pos > 0 THEN x_field := substr(p_string, 1, l_pos - 1); p_string := substr(p_string, l_pos + length(g_default_col_separator)); IF p_string IS NULL THEN x_next_null_flag := 'Y'; END IF; ELSE x_field := p_string; p_string := NULL; END IF; END IF; END popup_field; --把一行CSV数据，分割成每个字段。样例数据： --"123,44",4343,zd,周哦,343.454,"sdfsd,343" PROCEDURE split_to_field(p_string IN CLOB, x_field_array OUT large_field_array_type) IS l_field_count NUMBER := 0; l_string CLOB; l_field CLOB; x_next_null_flag VARCHAR2(1); BEGIN l_string := p_string; WHILE l_string IS NOT NULL LOOP popup_field(l_string, l_field, x_next_null_flag); l_field_count := l_field_count + 1; x_field_array(l_field_count) := REPLACE(REPLACE(REPLACE(TRIM(l_field), g_col_replacer, g_col_separator), g_c13_replacer, chr(13)), g_c10_replacer, chr(10)); IF x_next_null_flag = 'Y' THEN l_field_count := l_field_count + 1; x_field_array(l_field_count) := NULL; END IF; END LOOP; WHILE l_field_count < g_default_in_col_count LOOP l_field_count := l_field_count + 1; x_field_array(l_field_count) := NULL; END LOOP; END; --从一个字符串中取出一行，并返回剩下的字符串 PROCEDURE popup_line(x_text IN OUT CLOB, x_string OUT CLOB) IS l_pos NUMBER; BEGIN IF x_text IS NULL THEN x_string := NULL; RETURN; END IF; l_pos := instr(x_text, g_default_row_separator); IF l_pos > 0 THEN x_string := substr(x_text, 1, l_pos - 1); x_text := substr(x_text, l_pos + length(g_default_row_separator)); ELSE x_string := x_text; x_text := NULL; END IF; END; --把一个文本分成一行行 PROCEDURE split_parameter(p_in_parameter IN CLOB) IS x_text CLOB; x_string CLOB; x_in_parameter_array large_field_array_type; BEGIN g_in_parameter_table.delete; g_in_record_count := 0; x_text := p_in_parameter; WHILE x_text IS NOT NULL LOOP popup_line(x_text => x_text, x_string => x_string); split_to_field(x_string, x_in_parameter_array); g_in_record_count := g_in_record_count + 1; g_in_parameter_table(g_in_record_count) := x_in_parameter_array; END LOOP; END; /*========================================================== HCM-B-P02-05 参数： p_prod_model 机型 p_demand_date 需要验证的导入时间 行头或列头的时间 返回值：x_msg_data 物料ID x_msg_date 时间格式的 p_demand_date Created : 2009-10-09 hand ==========================================================*/ PROCEDURE get_latest_item(p_prod_model IN VARCHAR2, p_demand_date IN VARCHAR2, x_msg_data OUT NOCOPY NUMBER, x_msg_date OUT NOCOPY DATE, --传出周数开始日期 x_msg_date2 OUT NOCOPY DATE) --传出周期结束日期 IS l_item_id NUMBER; l_prod_date DATE; --l_item_code VARCHAR2(50); BEGIN --机型/去向在相应的时间段内是否存在对应的最近的物料 get_date(p_demand_date, l_prod_date, x_msg_date2); x_msg_date := l_prod_date; /* BEGIN SELECT * INTO l_item_id FROM (SELECT hiv.item_id FROM hcm_main_item_v hiv WHERE nvl(hiv.product_model, hiv.item_code) = p_prod_model AND nvl2(hiv.attribute5, to_date(hiv.attribute5, 'yyyy-mm-dd'), to_date('2000-1-1', 'yyyy-mm-dd')) <= l_prod_date ORDER BY hiv.attribute5 DESC) temp WHERE rownum = 1; x_msg_data := l_item_id; EXCEPTION WHEN no_data_found THEN x_msg_data := NULL; END;*/ BEGIN SELECT * INTO l_item_id FROM (SELECT hiv.item_id FROM hcm_main_item_v hiv WHERE hiv.product_model = p_prod_model AND nvl2(hiv.attribute5, to_date(hiv.attribute5, 'yyyy-mm-dd'), to_date('2000-1-1', 'yyyy-mm-dd')) <= l_prod_date AND hiv.enable_flag = 'Y' ORDER BY hiv.attribute5 DESC) temp WHERE rownum = 1; x_msg_data := l_item_id; EXCEPTION WHEN no_data_found THEN BEGIN SELECT * INTO l_item_id FROM (SELECT hiv.item_id FROM hcm_main_item_v hiv WHERE hiv.item_code = p_prod_model --p_prod_model有可能是物料 AND nvl2(hiv.attribute5, to_date(hiv.attribute5, 'yyyy-mm-dd'), to_date('2000-1-1', 'yyyy-mm-dd')) <= l_prod_date AND hiv.enable_flag = 'Y' ORDER BY hiv.attribute5 DESC) temp WHERE rownum = 1; x_msg_data := l_item_id; EXCEPTION WHEN no_data_found THEN x_msg_data := NULL; END; END; END get_latest_item; /*========================================================== HCM-B-P02-05 获取物料的产品分类 参数： p_item_id 物料 p_plant_id 工厂 返回值：x_msg_data Created : 2009-10-09 hand ==========================================================*/ FUNCTION get_product_category(p_item_id IN NUMBER, p_plant_id IN NUMBER DEFAULT NULL) RETURN VARCHAR2 IS l_prod_type VARCHAR2(100) := NULL; BEGIN BEGIN SELECT hic.segment2 INTO l_prod_type FROM hcm_item_categories_v hic WHERE hic.inventory_item_id = p_item_id AND hic.category_set_id = 1 /*AND hic.segment1 LIKE '9%'*/ AND hic.organization_id = nvl(p_plant_id, hcm_public_pck.get_user_parameter(NULL, 'U', NULL, 'DEFAULT_MAIN_PLANT_ID')) AND rownum = 1; EXCEPTION WHEN no_data_found THEN l_prod_type := NULL; END; RETURN l_prod_type; END get_product_category; /*============================================ HCM-B-P02-05 验证BOM是否存在 参数： p_item_code p_date p_item_id 返回值： Created : hand ===============================================*/ FUNCTION check_bom(p_item_code VARCHAR2 DEFAULT NULL, p_date DATE DEFAULT NULL, p_item_id NUMBER DEFAULT NULL) RETURN BOOLEAN IS l_count NUMBER; BEGIN IF p_date IS NOT NULL THEN IF p_item_id IS NULL THEN SELECT COUNT(1) INTO l_count FROM hcm_bom hb, hcm_main_item_v item WHERE hb.assembly_item_id = item.item_id AND item.item_code = p_item_code AND item.enable_flag = 'Y' AND hb.start_date <= p_date AND hb.end_date >= p_date AND hb.plant_id = item.attribute8; --物料生产工厂 ELSE SELECT COUNT(1) INTO l_count FROM hcm_bom hb, hcm_main_item_v item WHERE hb.assembly_item_id = p_item_id AND item.enable_flag = 'Y' AND hb.start_date <= p_date AND hb.end_date >= p_date AND item.item_id = p_item_id AND hb.plant_id = item.attribute8; --物料生产工厂 END IF; ELSE SELECT COUNT(1) INTO l_count FROM hcm_bom hb WHERE hb.assembly_item_id = p_item_id; END IF; IF l_count = 0 THEN RETURN FALSE; ELSE RETURN TRUE; END IF; END check_bom; /*============================================ HCM-B-P02-05 验证物料是否存在 参数： p_item_code p_date p_item_id 返回值： Created : hand ===============================================*/ FUNCTION check_item(p_item_code VARCHAR2 DEFAULT NULL, p_item_id NUMBER DEFAULT NULL) RETURN BOOLEAN IS l_count NUMBER := 0; BEGIN IF p_item_id IS NOT NULL THEN SELECT COUNT(1) INTO l_count FROM hcm_main_item_v hmi WHERE hmi.item_id = p_item_id AND hmi.enable_flag = 'Y'; ELSE SELECT COUNT(1) INTO l_count FROM hcm_main_item_v hmi WHERE hmi.item_code = p_item_code AND hmi.enable_flag = 'Y'; END IF; IF l_count = 0 THEN RETURN FALSE; ELSE RETURN TRUE; END IF; END check_item; /*========================================================== HCM-B-P02-05 验证数据 客户 参数： p_customer 客户 返回值：x_msg_data 客户名称 系统中不存在 该客户 NULL 存在 该客户 Created : 2009-10-09 hand ==========================================================*/ PROCEDURE check_customer(p_customer IN VARCHAR2, x_msg_data OUT NOCOPY VARCHAR2) IS l_count NUMBER := 0; BEGIN --modify by kungan.huang,20100818, --修改取客户的逻辑,取-以后的字符 SELECT COUNT(1) INTO l_count FROM hcm_customer_v hcv WHERE hcv.customer_alias = substr(p_customer, instr(p_customer, '-') + 1); IF l_count > 0 THEN x_msg_data := NULL; ELSE x_msg_data := p_customer; END IF; END check_customer; FUNCTION check_customer_for_ui(p_in_parameter IN CLOB) RETURN VARCHAR2 IS l_record_count NUMBER; l_customer VARCHAR2(50); l_msg_data VARCHAR2(500); l_msg_return VARCHAR2(4000); BEGIN split_parameter(p_in_parameter => p_in_parameter); l_record_count := hcm_demand_pck.g_in_record_count; --批处理开始 FOR i IN 1 .. l_record_count LOOP --取参数 l_customer := hcm_demand_pck.g_in_parameter_table(i) (1); l_msg_data := NULL; --调用客户化物理盘点过程 check_customer(l_customer, l_msg_data); --错误处理 IF l_msg_data IS NOT NULL THEN IF l_msg_return IS NOT NULL THEN l_msg_return := l_msg_return || '#' || l_msg_data; ELSE l_msg_return := l_msg_data; END IF; END IF; END LOOP; RETURN l_msg_return; END check_customer_for_ui; /*============================================ HCM-B-P02-05 验证机型是否存在 参数： p_prodmodel 返回值： Created : hand ===============================================*/ FUNCTION check_prodmodel_exist(p_prodmodel IN VARCHAR2) RETURN BOOLEAN IS l_count NUMBER; BEGIN --p_prodmodel 有可能是物料 -- SELECT COUNT(1) INTO l_count FROM hcm_main_item_v hiv WHERE nvl(hiv.product_model,hiv.item_code) = p_prodmodel; SELECT COUNT(1) INTO l_count FROM hcm_main_item_v hiv WHERE hiv.product_model = p_prodmodel AND hiv.enable_flag = 'Y'; IF l_count = 0 THEN SELECT COUNT(1) INTO l_count FROM hcm_main_item_v hiv WHERE hiv.item_code = p_prodmodel AND hiv.enable_flag = 'Y'; END IF; IF l_count = 0 THEN RETURN FALSE; ELSE RETURN TRUE; END IF; EXCEPTION WHEN OTHERS THEN RETURN FALSE; END check_prodmodel_exist; /*========================================================== HCM-B-P02-05 验证数据 机型 是否存在相应的BOM 参数： p_prodmodel 机型 p_proddate 需求时间 返回值：x_msg_data S@成功 数据库中存在机型同时也存在该机型对应的BOM E@对应的BOM不存在 数据库中存在机型但不存在该机型对应的BOM E@机型不存在 数据库中不存在机型也不存在该机型对应的BOM Created : 2009-10-09 hand ==========================================================*/ PROCEDURE check_prodmodel(p_prodmodel IN VARCHAR2, p_proddate IN VARCHAR2, x_msg_data OUT NOCOPY VARCHAR2, x_next_row OUT NOCOPY BOOLEAN) IS l_count NUMBER := 0; l_item_id NUMBER; l_item_code VARCHAR2(50); l_item_date DATE; l_prod_date DATE; l_nouse_date DATE; l_existed BOOLEAN; l_product_category VARCHAR2(100); l_msg_data VARCHAR2(2000); BEGIN x_next_row := FALSE; IF p_prodmodel IS NOT NULL THEN --判断机型是否存在 l_existed := check_prodmodel_exist(p_prodmodel); IF l_existed = FALSE THEN --F@机型不存在 hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_DI_PM_NOT_EXIST'); x_msg_data := 'F@' || hcm_api.get_message(-1); x_next_row := TRUE; RETURN; END IF; ELSE /* hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_COMMON_SUCCESS'); x_msg_data := 'S@' || hcm_api.get_message(-1);*/ x_next_row := TRUE; RETURN; END IF; --机型/去向在相应的时间段内是否存在对应的物料 get_latest_item(p_prod_model => p_prodmodel, p_demand_date => p_proddate, x_msg_data => l_item_id, x_msg_date => l_prod_date, --传出周数开始日期 x_msg_date2 => l_nouse_date); --传出周期结束日期 IF l_item_id IS NULL THEN hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_DI_PM_PER_NOT_EXIST'); x_msg_data := 'E@' || hcm_api.get_message(-1); RETURN; END IF; --验证该物料是否存在BOM l_existed := check_bom(p_item_code => NULL, p_date => l_prod_date, p_item_id => l_item_id); IF l_existed = TRUE THEN SELECT COUNT(1) INTO l_count FROM hcm_main_item_v hmi WHERE hmi.item_id = l_item_id AND hmi.item_type = hcm_profile.value('PHANTOM_ITEM_TYPE'); --如果是虚拟件 验证组件 IF l_count > 0 THEN FOR lr_comps IN (SELECT comp.component_item_id, hmi2.product_model, hmi2.item_code FROM hcm_bom_component_v comp, hcm_bom bom, hcm_main_item_v hmi, hcm_main_item_v hmi2 WHERE comp.bom_id = bom.bom_id AND bom.assembly_item_id = l_item_id AND hmi.item_id = bom.assembly_item_id AND bom.alternate IS NULL AND bom.plant_id = hmi.attribute8 --物料的生产工厂 AND hmi2.item_id = comp.component_item_id) LOOP IF lr_comps.product_model IS NULL THEN hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_DI_ITEM_PM_NOT_EXIST', p_token1 => 'ITEM_CODE', p_token1_value => lr_comps.item_code); l_msg_data := l_msg_data || hcm_api.get_message(-1); ELSE l_existed := check_bom(p_item_code => NULL, p_date => l_prod_date, p_item_id => lr_comps.component_item_id); IF l_existed = FALSE THEN hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_DI_BOM_NOT_EXIST', p_token1 => 'ITEM_CODE', p_token1_value => lr_comps.item_code); l_msg_data := l_msg_data || hcm_api.get_message(-1); ELSE NULL; END IF; END IF; END LOOP; IF l_msg_data IS NULL THEN --S@成功 hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_COMMON_SUCCESS'); x_msg_data := 'S@' || hcm_api.get_message(-1); ELSE x_msg_data := 'E@' || l_msg_data; END IF; ELSE --S@成功 hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_COMMON_SUCCESS'); x_msg_data := 'S@' || hcm_api.get_message(-1); END IF; ELSE SELECT hmi.item_code INTO l_item_code FROM hcm_main_item_v hmi WHERE hmi.item_id = l_item_id; --E@对应的BOM不存在 hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_DI_BOM_NOT_EXIST', p_token1 => 'ITEM_CODE', p_token1_value => l_item_code); x_msg_data := 'E@' || hcm_api.get_message(-1); END IF; END check_prodmodel; FUNCTION check_prodmodel_for_ui(p_in_parameter IN CLOB) RETURN VARCHAR2 IS l_record_count NUMBER; l_prodmodel VARCHAR2(50); l_proddate VARCHAR2(20); l_msg_data VARCHAR2(500); l_msg_return VARCHAR2(4000); l_next_row BOOLEAN; l_line_number NUMBER; l_previous_number NUMBER := -1; l_prod_existed VARCHAR2(1) := 'Y'; BEGIN split_parameter(p_in_parameter => p_in_parameter); l_record_count := hcm_demand_pck.g_in_record_count; --批处理开始 FOR i IN 1 .. l_record_count LOOP --取参数 l_line_number := hcm_demand_pck.g_in_parameter_table(i) (1); l_prodmodel := hcm_demand_pck.g_in_parameter_table(i) (2); l_proddate := hcm_demand_pck.g_in_parameter_table(i) (3); l_msg_data := NULL; IF (l_prod_existed = 'Y' AND l_previous_number = l_line_number) OR l_previous_number <> l_line_number THEN IF l_previous_number <> l_line_number THEN l_msg_return := l_msg_return || '$'; END IF; --验证机型 check_prodmodel(l_prodmodel, l_proddate, l_msg_data, l_next_row); --错误处理 -- IF l_msg_data IS NOT NULL THEN IF l_previous_number <> l_line_number THEN l_msg_return := l_msg_return || l_msg_data; ELSE l_msg_return := l_msg_return || '#' || l_msg_data; END IF; /*IF l_msg_return IS NOT NULL THEN l_msg_return := l_msg_return || '#' || l_msg_data; ELSE l_msg_return := l_msg_data; END IF;*/ IF l_next_row THEN l_prod_existed := 'N'; --RETURN l_msg_return; ELSE l_prod_existed := 'Y'; END IF; --END IF; ELSE l_msg_return := l_msg_return || '#'; END IF; l_previous_number := l_line_number; --added by liuqin 2011.12.8 IF length(l_proddate) < 7 THEN l_msg_return := l_msg_return || '#' || ' 周别格式错误'; END IF; --end added END LOOP; --TRIM('$' RETURN TRIM('$' FROM l_msg_return); END check_prodmodel_for_ui; /*============================================ HCM-B-P02-05 验证p_trace_num是否存在 参数： p_trace_num 返回值： Created : hand ===============================================*/ FUNCTION check_trace_num(p_trace_num IN VARCHAR2) RETURN BOOLEAN IS l_count NUMBER; BEGIN SELECT COUNT(1) INTO l_count FROM hcm_demand_trace_num hdtn WHERE hdtn.trace_num = p_trace_num; IF l_count = 0 THEN RETURN FALSE; ELSE RETURN TRUE; END IF; EXCEPTION WHEN OTHERS THEN RETURN FALSE; END check_trace_num; /*============================================ HCM-B-P02-05 根据p_trace_num取数据 参数： p_trace_num 返回值：取到的数据 Created : hand ===============================================*/ PROCEDURE get_data_by_trace_num(p_trace_num IN VARCHAR2, x_item_code OUT NOCOPY VARCHAR, x_period OUT NOCOPY VARCHAR, x_item_id OUT NUMBER) IS BEGIN SELECT i.item_code, hdtn.period, i.item_id INTO x_item_code, x_period, x_item_id FROM hcm_demand_trace_num hdtn, hcm_main_item_v i WHERE hdtn.trace_num = p_trace_num AND i.item_id = hdtn.item_id AND hdtn.attribute2 IS NULL; EXCEPTION WHEN OTHERS THEN x_item_code := NULL; x_period := NULL; END get_data_by_trace_num; PROCEDURE check_project_imp(p_itemcode IN VARCHAR2 DEFAULT NULL, p_tracenum IN VARCHAR2 DEFAULT NULL, p_demand_date IN VARCHAR2, x_msg_data OUT NOCOPY VARCHAR2) IS l_count NUMBER := 0; /*l_itemid NUMBER := 0;*/ l_count2 NUMBER := 0; l_trace_item_code VARCHAR2(50); l_trace_period VARCHAR2(20); l_trace_item_id NUMBER; l_existed BOOLEAN; l_demand_start_date DATE; l_demand_end_date DATE; l_combine_existed BOOLEAN := TRUE; BEGIN ------------------------- --取日期 get_date(p_demand_date, l_demand_start_date, l_demand_end_date); ------------------------- --验证追踪号 IF p_tracenum IS NOT NULL THEN l_existed := check_trace_num(p_tracenum); IF l_existed = FALSE THEN --0@系统中不存在该追踪号 hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_DI_TRACE_NUM_NOT_EXIST'); x_msg_data := '|0@' || hcm_api.get_message(-1); l_combine_existed := FALSE; END IF; END IF; ------------------------- --验证物料 IF p_itemcode IS NOT NULL THEN l_existed := check_item(p_itemcode); IF l_existed = FALSE THEN hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_DI_ITEM_IS_NULL'); x_msg_data := x_msg_data || '|1@' || hcm_api.get_message(-1); l_combine_existed := FALSE; RETURN; END IF; --验证bom l_existed := check_bom(p_item_code => p_itemcode, p_date => l_demand_start_date); IF l_existed = FALSE THEN hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_DI_ITEM_BOM_NOT_EXIST'); x_msg_data := x_msg_data || '|1@' || hcm_api.get_message(-1); RETURN; END IF; --验证物料与追踪号是否对应 IF p_tracenum IS NOT NULL AND l_combine_existed = TRUE THEN get_data_by_trace_num(p_trace_num => p_tracenum, x_item_code => l_trace_item_code, x_period => l_trace_period, x_item_id => l_trace_item_id); IF p_itemcode <> l_trace_item_code THEN --0@追踪号与BOM NO不对应' hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_DI_TRACE_NOT_MATCH_ITEM'); x_msg_data := '|0@' || hcm_api.get_message(-1); END IF; END IF; END IF; END check_project_imp; /*========================================================== HCM-B-P02-05 验证Project型导入 物料、追踪号 参数： p_in_parameter 格式如:'机型1,时间1,机型2，时间2.......'‘ 返回值： | 列分隔符 # 行分隔符 E@不存在BOM|E@追踪号不存在#E@物料不存在|E@追踪号与BOM NO 不对应 Created : 2009-10-09 hand ==========================================================*/ FUNCTION check_projimp_for_ui(p_in_parameter IN CLOB) RETURN VARCHAR2 IS l_record_count NUMBER; l_itemcode VARCHAR2(50); l_tracenum VARCHAR2(20); l_demand_date_char VARCHAR2(20); l_msg_data VARCHAR2(500); l_msg_return VARCHAR2(4000); BEGIN split_parameter(p_in_parameter => p_in_parameter); l_record_count := hcm_demand_pck.g_in_record_count; --批处理开始 FOR i IN 1 .. l_record_count LOOP --取参数 l_itemcode := hcm_demand_pck.g_in_parameter_table(i) (1); l_tracenum := hcm_demand_pck.g_in_parameter_table(i) (2); l_demand_date_char := hcm_demand_pck.g_in_parameter_table(i) (3); l_msg_data := NULL; --验证机型 check_project_imp(l_itemcode, l_tracenum, l_demand_date_char, l_msg_data); --错误处理 IF i = 1 THEN l_msg_return := l_msg_data; ELSE l_msg_return := l_msg_return || '#' || l_msg_data; END IF; END LOOP; RETURN l_msg_return; END check_projimp_for_ui; /*========================================================== HCM-B-P02-05 验证数据 备件信息 参数： p_in_parameter 备件信息 08***|09***:30 返回值：x_msg_data Created : 2009-10-09 hand ==========================================================*/ PROCEDURE check_stockinfo(p_in_parameter IN VARCHAR2, x_msg_data OUT NOCOPY VARCHAR2) IS l_text VARCHAR2(200); l_pos NUMBER; l_count NUMBER; l_stockinfo VARCHAR2(500); -- 料号*数量 l_stock_code VARCHAR2(50); -- 料号 l_stock_qty NUMBER; -- 数量 TYPE stockinfo_tp IS RECORD( stock_code VARCHAR2(50)); --末件完工时间); TYPE stockinfo_array IS TABLE OF stockinfo_tp INDEX BY BINARY_INTEGER; l_stockinfo_array stockinfo_array; l_index NUMBER := 0; l_existed BOOLEAN := FALSE; BEGIN l_text := TRIM(REPLACE(REPLACE(TRIM(p_in_parameter), chr(13)), chr(10))); WHILE l_text IS NOT NULL LOOP l_pos := instr(l_text, '|'); IF l_pos > 0 THEN l_stockinfo := substr(l_text, 1, l_pos - 1); l_text := substr(l_text, l_pos + length('|')); ELSE l_stockinfo := l_text; l_text := NULL; END IF; IF l_stockinfo IS NOT NULL THEN -------------------------------------- -- Add by hand -- -- 2009.12.23 BEGIN l_stock_code := TRIM(substr(l_stockinfo, 1, instr(l_stockinfo, '*') - 1)); l_stock_qty := to_number(TRIM(substr(l_stockinfo, instr(l_stockinfo, '*') + 1))); IF l_stock_qty <= 0 THEN --4@物料' || l_item_code || '的数量' || l_qty || '不正确' hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_DI_ITEM_QTY_ERROR', p_token1 => 'ITEM_CODE', p_token1_value => l_stock_code, p_token2 => 'QTY', p_token2_value => l_stock_qty); x_msg_data := x_msg_data || '|4@' || hcm_api.get_message(-1); END IF; EXCEPTION WHEN OTHERS THEN hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_DI_ITEM_QTY_ERROR', p_token1 => 'ITEM_CODE', p_token1_value => l_stock_code, p_token2 => 'QTY', p_token2_value => l_stock_qty); x_msg_data := x_msg_data || '|4@' || hcm_api.get_message(-1); END; -- Add End -- -------------------------------------- SELECT COUNT(1) INTO l_count FROM hcm_main_item_v hiv WHERE hiv.item_code = l_stock_code AND hiv.enable_flag = 'Y'; --验证备件是否重复 FOR i IN 1 .. l_stockinfo_array.count LOOP IF l_stockinfo_array(i).stock_code = l_stock_code THEN l_existed := TRUE; EXIT; END IF; END LOOP; IF l_existed = FALSE THEN l_index := l_index + 1; l_stockinfo_array(l_index).stock_code := l_stock_code; ELSE hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_DI_STOCK_RPT', p_token1 => 'CODE', p_token1_value => l_stock_code); x_msg_data := x_msg_data || '|4@' || hcm_api.get_message(-1); END IF; IF l_count = 0 THEN hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_DI_ITEM_NOT_EXIST', p_token1 => 'ITEM_CODE', p_token1_value => l_stock_code); x_msg_data := x_msg_data || '|4@' || hcm_api.get_message(-1); END IF; END IF; END LOOP; END check_stockinfo; /*========================================================== HCM-B-P02-05 验证数据 ODF和SP公用的验证 包括：客户是否存在/机型是否存在/trace_num/物料是否一致/是否存在BOM 参数： p_itemcode 物料BOM NO. p_tracenum 追踪号 p_prodmodel 机型 p_stockinfo 备件信息 p_tempinfo 样机信息 p_demanddate 需求时间 返回值：x_msg_data 1 机型 不存在或..... 2 追踪号............ 3 BOM NO............ 4 备件信息.......... Created : 2009-12-23 Shaowei.Zhang ==========================================================*/ PROCEDURE check_common_data(p_customer IN VARCHAR2, p_itemcode IN VARCHAR2 DEFAULT NULL, p_tracenum IN VARCHAR2, p_prodmodel IN VARCHAR2, p_period IN VARCHAR2 DEFAULT NULL, p_cust_site_name IN VARCHAR2 DEFAULT NULL, x_msg_data OUT NOCOPY VARCHAR2) IS l_prod_item_code VARCHAR2(50); --机型对应的物料 l_prod_item_id NUMBER; l_trace_item_code VARCHAR2(50); --追踪对应的物料 l_trace_item_id NUMBER; l_demand_start_date DATE; --Trace 表上的 需求时间 l_demand_end_date DATE; --Trace 表上的 需求时间 l_count NUMBER; l_period VARCHAR2(30); l_existed BOOLEAN; l_combine_existed BOOLEAN := TRUE; -- Trace_Num + ProdModel + ItemCode 三者的验证结果 l_prod_model_existed BOOLEAN := TRUE; l_item_existed BOOLEAN := TRUE; l_msg_data VARCHAR2(4000); BEGIN ------------------------------ --驗證客戶是否存在 check_customer(p_customer, l_msg_data); IF l_msg_data IS NOT NULL THEN hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_DI_CUST_NOT_EXIST'); l_msg_data := l_msg_data || '|0@' || hcm_api.get_message(-1); ELSE IF p_cust_site_name IS NOT NULL THEN SELECT COUNT(1) INTO l_count FROM hcm_customer_sites_v hcs WHERE hcs.customer_alias = p_customer AND nvl(upper(hcs.customer_site_name), '~~###~~') = nvl(upper(p_cust_site_name), '~~###~~'); --客户地点验证不区分大小写 IF l_count < 1 THEN --hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_DI_CUST_NOT_EXIST'); l_msg_data := l_msg_data || '|6@客户地点不存在！'; END IF; END IF; NULL; END IF; ------------------------------ --判断机型是否存在 IF p_prodmodel IS NOT NULL THEN l_prod_model_existed := check_prodmodel_exist(p_prodmodel => p_prodmodel); IF l_prod_model_existed = FALSE THEN hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_DI_PM_NOT_EXIST'); l_msg_data := l_msg_data || '|1@' || hcm_api.get_message(-1); l_combine_existed := FALSE; END IF; ELSE l_prod_model_existed := FALSE; END IF; ------------------------------ IF p_tracenum IS NOT NULL THEN --判断追踪号 get_data_by_trace_num(p_tracenum, l_trace_item_code, l_period, l_trace_item_id); IF l_trace_item_code IS NULL THEN hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_DI_TRACE_NUM_NOT_EXIST'); l_msg_data := l_msg_data || '|2@' || hcm_api.get_message(-1); l_combine_existed := FALSE; END IF; END IF; IF p_itemcode IS NOT NULL THEN l_existed := check_item(p_item_code => p_itemcode); IF l_existed = FALSE THEN hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_DI_ITEM_NOT_EXIST', p_token1 => 'ITEM_CODE', p_token1_value => p_itemcode); l_msg_data := l_msg_data || '|3@' || hcm_api.get_message(-1); l_combine_existed := FALSE; END IF; END IF; ------------------------------ --如果机型验证通过 并且 有需求时间 IF l_prod_model_existed = TRUE AND nvl(p_period, l_period) IS NOT NULL THEN get_latest_item(p_prod_model => p_prodmodel, p_demand_date => nvl(p_period, l_period), x_msg_data => l_prod_item_id, x_msg_date => l_demand_start_date, x_msg_date2 => l_demand_end_date); BEGIN SELECT hiv.item_code INTO l_prod_item_code FROM hcm_main_item_v hiv WHERE hiv.item_id = l_prod_item_id; EXCEPTION WHEN no_data_found THEN hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_DI_TD_DATE_NO_ITEM'); l_msg_data := l_msg_data || '|1@' || hcm_api.get_message(-1); l_item_existed := FALSE; END; IF l_item_existed <> FALSE THEN ------------------- --判断物料是否存在BOM l_existed := check_bom(p_item_id => l_prod_item_id, p_date => l_demand_start_date); IF l_existed = FALSE THEN --该追踪号对应的物料BOM不存在！ hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_DI_TI_NOT_EXIST'); l_msg_data := l_msg_data || '|1@' || hcm_api.get_message(-1); END IF; END IF; END IF; ------------------------------ -- 如果Trace_Num + ProdModel + ItemCode 三者验证通过 IF l_combine_existed = TRUE AND p_tracenum IS NOT NULL THEN ------------------- --判断追踪号对应的物料，机型对应的物料是否一致 IF l_prod_item_code <> l_trace_item_code THEN hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_DI_PM_ITEM_NOT_MATCH'); l_msg_data := l_msg_data || '|1@' || hcm_api.get_message(-1); END IF; IF p_period IS NOT NULL THEN SELECT COUNT(1) INTO l_count FROM hcm_demand_trace_num hdtn WHERE hdtn.trace_num = p_tracenum AND hdtn.period = p_period; IF l_count = 0 THEN --需求时间与追踪时段不一致！ hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_DI_DM_DATE_NOT_MATCH'); l_msg_data := l_msg_data || '|2@' || hcm_api.get_message(-1); END IF; END IF; END IF; ------------------------------ IF l_combine_existed = TRUE AND p_itemcode IS NOT NULL AND l_prod_item_code <> p_itemcode THEN -- insert into liuqin_test(memo) values (l_prod_item_id || ' ' || l_prod_item_code || ' ' || p_itemcode); --BOM NO和机型对应的物料不相符！ hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_DI_BOM_ITEM_NOT_MATCH'); l_msg_data := l_msg_data || '|3@' || hcm_api.get_message(-1); END IF; x_msg_data := l_msg_data; END check_common_data; /*========================================================== HCM-B-P02-05 验证数据 机型 参数： p_prodmodel 机型 p_proddate 需求时间 返回值：x_msg_data S@成功 数据库中存在机型同时也存在该机型对应的BOM E@对应的BOM不存在 数据库中存在机型但不存在该机型对应的BOM E@机型不存在 数据库中不存在机型也不存在该机型对应的BOM Created : 2009-10-09 hand ==========================================================*/ PROCEDURE check_spimp(p_customer IN VARCHAR2, p_prodmodel IN VARCHAR2, p_proddate IN VARCHAR2, p_tracenum IN VARCHAR2, p_cust_site_name IN VARCHAR2, p_batch_id IN VARCHAR2, p_last_flag IN VARCHAR2, x_msg_data OUT NOCOPY VARCHAR2) IS l_prod_date DATE; l_item_id NUMBER; --机型在追踪时段内最近的物料ID l_trace_item_id NUMBER; --追踪号对应的物料ID x_no_use BOOLEAN; l_traceid NUMBER; l_item_date DATE; l_demand_date DATE; l_trace_qty NUMBER; l_msg_data VARCHAR2(50); l_count NUMBER; l_common_data_msg VARCHAR2(2000); l_sp_qty_sum NUMBER; l_odf_qty_sum NUMBER; BEGIN --验证客户、机型、物料、跟踪号 check_common_data(p_customer => p_customer, p_itemcode => NULL, p_tracenum => p_tracenum, p_prodmodel => p_prodmodel, p_period => NULL, p_cust_site_name => p_cust_site_name, x_msg_data => l_common_data_msg); --根据trace_num 取没有验证qty的数据 SELECT COUNT(1) INTO l_count FROM hcm_check_sp_imp_temp hcs, hcm_demand_trace_num hdt WHERE hcs.trace_num = p_tracenum AND hcs.checked = 'N' AND hdt.trace_num = hcs.trace_num AND hdt.attribute2 IS NULL AND hcs.batch_id = p_batch_id; --如果没验证 IF l_count > 0 THEN SELECT nvl(SUM(a.shipping_qty), 0) INTO l_sp_qty_sum FROM hcm_check_sp_imp_temp a WHERE a.checked = 'N' AND a.trace_num = p_tracenum AND a.batch_id = p_batch_id; --- 汇总主需求数量 SELECT nvl(SUM(h.shipping_qty), 0) INTO l_odf_qty_sum FROM hcm_demand_info h, hcm_demand_trace_num t WHERE h.trace_id = t.trace_id AND t.trace_num = p_tracenum AND t.attribute2 IS NULL -- 主需求attribute2 为空 AND h.demand_type = 'ORDER'; --打标记为已验证 UPDATE hcm_check_sp_imp_temp a SET a.checked = 'Y' WHERE a.trace_num = p_tracenum AND a.batch_id = p_batch_id; IF l_sp_qty_sum > l_odf_qty_sum THEN --本次导入的Shipping Plan数量之和大于已经发布的ORDER数量 hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_DI_MORE_SP_QTY'); l_common_data_msg := l_common_data_msg || '|5@' || hcm_api.get_message(-1); END IF; END IF; --返回结果 x_msg_data := x_msg_data || l_common_data_msg; END check_spimp; /*========================================================== HCM-B-P02-05 验证Shpping Plan型导入 物料、追踪号 参数： p_in_parameter 格式如:'机型1,时间1,机型2，时间2.......'‘ 返回值： Created : 2009-10-09 hand ==========================================================*/ FUNCTION check_spimp_for_ui(p_in_parameter IN CLOB, p_batch_id IN VARCHAR2, p_last_flag IN VARCHAR2) RETURN VARCHAR2 IS PRAGMA AUTONOMOUS_TRANSACTION; l_record_count NUMBER; l_itemmodel VARCHAR2(50); l_itemdate VARCHAR2(20); l_customer VARCHAR2(50); l_tracenum VARCHAR2(50); l_sp_qty NUMBER; l_msg_data VARCHAR2(500); l_msg_return VARCHAR2(30000); l_cust_site_name VARCHAR2(2000); l_count NUMBER := 0; BEGIN split_parameter(p_in_parameter => p_in_parameter); l_record_count := hcm_demand_pck.g_in_record_count; --批处理开始 FOR i IN 1 .. l_record_count LOOP l_customer := hcm_demand_pck.g_in_parameter_table(i) (1); l_itemmodel := hcm_demand_pck.g_in_parameter_table(i) (2); l_itemdate := hcm_demand_pck.g_in_parameter_table(i) (3); l_tracenum := hcm_demand_pck.g_in_parameter_table(i) (4); BEGIN l_sp_qty := to_number(hcm_demand_pck.g_in_parameter_table(i) (5)); EXCEPTION WHEN OTHERS THEN l_sp_qty := NULL; NULL; END; l_cust_site_name := hcm_demand_pck.g_in_parameter_table(i) (6); INSERT INTO hcm_check_sp_imp_temp (customer, -- item_model, item_date, trace_num, shipping_qty, cust_site_name, checked, batch_id, temp_id) VALUES (l_customer, -- l_itemmodel, l_itemdate, l_tracenum, l_sp_qty, l_cust_site_name, 'N', to_number(p_batch_id), hcm_demand_temp_s.nextval); END LOOP; IF p_last_flag = 'Y' THEN FOR lr_temp IN (SELECT * FROM hcm_check_sp_imp_temp t WHERE t.batch_id = p_batch_id ORDER BY temp_id ASC) LOOP l_count := l_count + 1; --取参数 l_customer := lr_temp.customer; l_itemmodel := lr_temp.item_model; l_itemdate := lr_temp.item_date; l_tracenum := lr_temp.trace_num; -- l_sp_qty := hcm_demand_pck.g_in_parameter_table(i) (5); l_cust_site_name := lr_temp.cust_site_name; l_msg_data := NULL; --验证 check_spimp(l_customer, l_itemmodel, l_itemdate, l_tracenum, l_cust_site_name, p_batch_id, p_last_flag, l_msg_data); --错误处理 IF l_count = 1 THEN l_msg_return := l_msg_data; ELSE l_msg_return := l_msg_return || '#' || l_msg_data; END IF; END LOOP; DELETE FROM hcm_check_sp_imp_temp csi WHERE csi.batch_id = p_batch_id; END IF; COMMIT; RETURN l_msg_return; EXCEPTION WHEN OTHERS THEN ROLLBACK; RETURN '|5@出现异常，原因：' || SQLERRM; END check_spimp_for_ui; /* \*========================================================== HCM-B-P02-05 验证ODF型导入 物料、追踪号 参数： p_in_parameter 格式如:'机型1,时间1,机型2，时间2.......'‘ 返回值： Created : 2009-10-09 hand ==========================================================*\ FUNCTION CHECK_ODFIMP_FOR_UI ( p_in_parameter IN CLOB) RETURN VARCHAR2 IS l_record_count NUMBER; l_itemcode VARCHAR2(50); l_itemdate VARCHAR2(20); l_msg_data VARCHAR2(50); l_msg_return VARCHAR2(4000); BEGIN split_parameter(p_in_parameter => p_in_parameter); l_record_count := HCM_DEMAND_PCK.g_in_record_count; --批处理开始 FOR i IN 1 .. l_record_count LOOP --取参数 l_itemcode := HCM_DEMAND_PCK.g_in_parameter_table(i) (1); l_itemdate := HCM_DEMAND_PCK.g_in_parameter_table(i) (2); l_msg_data := NULL; --验证机型 CHECK_SPIMP(l_itemcode,l_itemdate,l_msg_data); --错误处理 IF i = 1 THEN l_msg_return := l_msg_data; ELSE l_msg_return := l_msg_return || '#' || l_msg_data ; END IF; END LOOP; RETURN l_msg_return; END CHECK_ODFIMP_FOR_UI;*/ /*========================================================== HCM-B-P02-05 验证数据 机型 参数： p_itemcode 物料BOM NO. p_tracenum 追踪号 p_prodmodel 机型 p_stockinfo 备件信息 p_tempinfo 样机信息 p_demanddate 需求时间 返回值：x_msg_data 1 机型 不存在或..... 2 追踪号............ 3 BOM NO............ 4 备件信息.......... Created : 2009-10-09 hand ==========================================================*/ PROCEDURE check_odfimp(p_customer IN VARCHAR2, p_itemcode IN VARCHAR2, p_tracenum IN VARCHAR2, p_prodmodel IN VARCHAR2, p_stockinfo IN VARCHAR2, p_demanddate IN VARCHAR2, x_msg_data OUT NOCOPY VARCHAR2) IS l_prod_date DATE; x_stockinfo VARCHAR2(200); l_nouse_date DATE; l_common_data_msg VARCHAR(2000); BEGIN --取需求时间 --get_date(p_demanddate, l_prod_date, l_nouse_date); --验证客户、机型、物料、跟踪号 check_common_data(p_customer => p_customer, p_itemcode => p_itemcode, p_tracenum => p_tracenum, p_prodmodel => p_prodmodel, p_period => p_demanddate, x_msg_data => l_common_data_msg); -- IF p_tracenum IS NOT NULL THEN NULL; END IF; --验证备件信息 check_stockinfo(p_stockinfo, x_stockinfo); --返回结果 x_msg_data := x_msg_data || l_common_data_msg || x_stockinfo; END check_odfimp; /*========================================================== HCM-B-P02-05 验证ODF型导入 物料、追踪号 参数： p_in_parameter 格式如:'机型1,时间1,机型2，时间2.......'‘ 返回值： 1 机型 不存在或..... 2 追踪号............ 3 BOM NO............ 4 备件信息.......... 5 样机信息.......... Created : 2009-10-09 hand ==========================================================*/ FUNCTION check_odfimp_for_ui(p_in_parameter IN CLOB) RETURN VARCHAR2 IS l_record_count NUMBER; l_itemcode VARCHAR2(50); --BOM NO. l_tracenum VARCHAR2(50); --追踪号 l_prodmodel VARCHAR2(50); --机型 l_stockinfo VARCHAR2(500); --备件信息 l_demanddate VARCHAR2(50); --需求时间 l_customer VARCHAR2(50); --客戶 l_msg_data VARCHAR2(500); l_msg_return VARCHAR2(4000); BEGIN split_parameter(p_in_parameter => p_in_parameter); l_record_count := hcm_demand_pck.g_in_record_count; --批处理开始 FOR i IN 1 .. l_record_count LOOP --取参数 l_customer := hcm_demand_pck.g_in_parameter_table(i) (1); l_itemcode := hcm_demand_pck.g_in_parameter_table(i) (2); l_tracenum := hcm_demand_pck.g_in_parameter_table(i) (3); l_prodmodel := hcm_demand_pck.g_in_parameter_table(i) (4); l_stockinfo := hcm_demand_pck.g_in_parameter_table(i) (5); l_demanddate := hcm_demand_pck.g_in_parameter_table(i) (6); l_msg_data := NULL; --验证机型 check_odfimp(l_customer, l_itemcode, l_tracenum, l_prodmodel, l_stockinfo, l_demanddate, l_msg_data); --错误处理 IF i = 1 THEN l_msg_return := l_msg_data; ELSE l_msg_return := l_msg_return || '#' || l_msg_data; END IF; END LOOP; RETURN TRIM(trailing '#' FROM l_msg_return); END check_odfimp_for_ui; /*==============================================================================================================*/ /*========================================================== HCM-B-P02-05 判断列头需求时间是否在追踪时段范围内 参数： p_item_id 物料ID p_customer_id 客户ID p_trace_start_date 追踪起始时间 EXCEL题头的时间 p_check_date 需要验证的导入时间 行头或列头的时间 返回值：x_msg_data bool Created : 2009-10-09 hand ==========================================================*/ PROCEDURE check_imp_date(p_item_id IN NUMBER, p_customer_id IN NUMBER, p_trace_start_date IN DATE, p_need_check_date IN DATE, p_period_type IN VARCHAR2, /*p_user_id IN NUMBER,*/ x_msg_flag OUT NOCOPY BOOLEAN, x_msg_date OUT NOCOPY DATE) IS l_prod_type VARCHAR2(30); --产品类别 l_period_num NUMBER; --时段数 l_period_type VARCHAR2(10); --时段类型 l_trace_end_date DATE; --追踪结束日期 BEGIN BEGIN /*SELECT hic.segment2 INTO l_prod_type FROM hcm_item_categories_v hic WHERE hic.inventory_item_id = p_item_id AND hic.category_set_id = 1 \*AND hic.segment1 LIKE '9%'*\ AND hic.organization_id = hcm_public_pck.get_user_parameter(NULL, 'U', p_user_id, 'DEFAULT_MAIN_PLANT_ID') AND rownum = 1;*/ l_prod_type := get_product_category(p_item_id => p_item_id); --根据客户及物料的产品类别 得到 时段类型与时段数 SELECT hdtt.period_type, hdtt.period_num INTO l_period_type, l_period_num FROM hcm_demand_trace_type hdtt WHERE hdtt.customer_id = p_customer_id AND hdtt.product_category = l_prod_type AND hdtt.period_type = p_period_type; --判断需求 时间是否在追踪时段范围内 l_trace_end_date := CASE TRIM(l_period_type) WHEN 'D' THEN p_trace_start_date + l_period_num WHEN 'M' THEN add_months(p_trace_start_date, l_period_num) WHEN 'Y' THEN add_months(p_trace_start_date, l_period_num * 12) WHEN 'W' THEN p_trace_start_date + l_period_num * 7 ELSE p_trace_start_date END; IF p_need_check_date >= p_trace_start_date AND p_need_check_date <= l_trace_end_date THEN x_msg_flag := TRUE; x_msg_date := l_trace_end_date; END IF; EXCEPTION WHEN no_data_found THEN x_msg_flag := FALSE; x_msg_date := NULL; END; END check_imp_date; /*========================================================== HCM-B-P02-05 根据客户生成追踪号 参数： p_itemcode 物料BOM NO. p_tracenum 追踪号 p_prodmodel 机型 p_stockinfo 备件信息 p_tempinfo 样机信息 p_demanddate 需求时间 返回值：x_msg_data Created : 2009-12-23 Shaowei.Zhang ==========================================================*/ FUNCTION build_trace_num(p_customer_id IN NUMBER, p_demand_type VARCHAR2, p_trace_start_date DATE, p_demand_date2 IN VARCHAR2, p_period_type VARCHAR2, p_proj_phase IN VARCHAR2 DEFAULT NULL, p_customer_name IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2 IS l_week NUMBER; l_year NUMBER; l_trace_number VARCHAR2(20); l_trace_max_number VARCHAR2(20); l_trace VARCHAR2(20); l_short_name VARCHAR2(30); l_nouse_date DATE; l_imp_date DATE; BEGIN get_date(p_demand_date2, l_imp_date, l_nouse_date); IF p_demand_type IN ('FCST', 'ORDER') THEN /*如果客户字段带有“-”符号，客户简称取“-”符号之前的前两位字符，不足两位以“-”填充；如果客户字段不带有“-” 符号户简称取自HCM_CUSTOMER_V中的“名称发音*/ /* SELECT nvl(substr(cust.short_name, 1, 2), 'TL') --ben 2010-07-14 changed INTO l_short_name FROM hcm_customer_v cust WHERE cust.customer_id = p_customer_id;*/ IF (instr(p_customer_name, '-') <> 0) THEN l_short_name := rpad(substr(p_customer_name, 1, instr(p_customer_name, '-') - 1), 2, '-'); ELSE SELECT nvl(substr(cust.short_name, 1, 2), 'XL') --ben 2010-07-14 changed INTO l_short_name FROM hcm_customer_v cust WHERE cust.customer_id = p_customer_id; END IF; IF p_period_type = 'Y' THEN l_week := 1; ELSIF p_period_type = 'M' THEN l_week := get_week(p_in_date => trunc(l_imp_date, 'MM')); ELSIF p_period_type = 'W' THEN l_week := get_week(p_in_date => l_imp_date); ELSIF p_period_type = 'D' THEN l_week := get_week(p_in_date => l_imp_date); END IF; IF to_char(l_imp_date, 'YYYY-MM-DD') = '2012-12-31' THEN SELECT l_short_name || '13' || lpad(l_week, 2, '0') INTO l_trace FROM dual; ELSE SELECT l_short_name || to_char(l_imp_date, 'YY') || lpad(l_week, 2, '0') INTO l_trace FROM dual; END IF; BEGIN SELECT trace_num INTO l_trace_max_number FROM (SELECT t.trace_num FROM hcm_demand_trace_num t WHERE substr(t.trace_num, 1, 6) = l_trace ORDER BY trace_num DESC) WHERE rownum = 1; -- FOR UPDATE wait 30; --added by liuqin 2011-1-21 避免一个traceid 对应2个tracenum SELECT l_trace || lpad(to_number(substr(l_trace_max_number, 7, 9)) + 1, 3, '0') INTO l_trace_number FROM dual; EXCEPTION WHEN no_data_found THEN l_trace_number := l_trace || '001'; END; ELSIF p_demand_type IN ('PROJECT') THEN l_week := get_week(p_in_date => l_imp_date); IF to_char(l_imp_date, 'YYYY-MM-DD') = '2012-12-31' THEN SELECT p_proj_phase || '13' || lpad(l_week, 2, '0') INTO l_trace FROM dual; ELSE SELECT p_proj_phase || to_char(l_imp_date, 'YY') || lpad(l_week, 2, '0') INTO l_trace FROM dual; END IF; BEGIN /* SELECT lpad(MAX(t.trace_num) + 1, 2, '0') INTO l_trace_number FROM hcm_demand_trace_num t WHERE substr(t.trace_num, 1, 7) = l_trace; */ SELECT trace_num INTO l_trace_max_number FROM (SELECT t.trace_num FROM hcm_demand_trace_num t WHERE substr(t.trace_num, 1, 7) = l_trace ORDER BY trace_num DESC) WHERE rownum = 1; SELECT l_trace || lpad(to_number(substr(l_trace_max_number, 8, 10)) + 1, 2, '0') INTO l_trace_number FROM dual; EXCEPTION WHEN no_data_found THEN l_trace_number := l_trace || '01'; WHEN OTHERS THEN l_trace_number := NULL; END; END IF; --dbms_output.put_line('l_trace_number' || l_trace_number); RETURN l_trace_number; END build_trace_num; /*========================================================== HCM-B-P02-05 取或生成追踪号 参数： p_prod_model 机型 p_demand_date1 需求时间 EXCEL题头的时间 p_demand_date2 需要验证的导入时间 行头或列头的时间 返回值：x_msg_data Created : 2009-10-09 hand ==========================================================*/ PROCEDURE get_trace_num(p_user_id IN NUMBER, p_customer IN VARCHAR2 DEFAULT NULL, p_item_id IN VARCHAR2, p_demand_type IN VARCHAR2, p_demand_date1 IN VARCHAR2, p_demand_date2 IN VARCHAR2, p_buyer IN VARCHAR2 DEFAULT NULL, p_attribute2 IN VARCHAR2 DEFAULT NULL, p_demand_time IN DATE, p_week_end_time IN DATE, p_proj_phase IN VARCHAR2 DEFAULT NULL, x_trace_num IN OUT NOCOPY VARCHAR2, x_customer_id OUT NOCOPY NUMBER, x_trace_id OUT NOCOPY NUMBER, x_demand_date OUT NOCOPY DATE) IS l_item_id NUMBER; l_customer_id NUMBER; l_trace_id NUMBER; l_trace_num VARCHAR2(20); l_period_type_new VARCHAR2(10); --时段类型 l_period_type VARCHAR2(10); --时段类型 l_trace_start_date DATE; --追踪开始日期 l_trace_end_date DATE; --追踪结束日期 l_need_check_date DATE; --需要验证的日期 l_imp_date_sucess BOOLEAN; --验证列头或行头时间 是否在 追踪时段范围内 l_trace_demand_date DATE; l_trace_demand_date_new DATE; l_nouse_date DATE; l_trace_num_new VARCHAR2(50); l_item_no VARCHAR2(50); --add by kungan.huang,20100818 l_customer_name_prefix VARCHAR2(80) := substr(p_customer, 1, instr(p_customer, '-') - 1); BEGIN --dbms_output.put_line(p_demand_date1); --客户ID IF p_customer IS NOT NULL THEN SELECT hcv.customer_id INTO l_customer_id FROM hcm_customer_v hcv WHERE hcv.customer_alias = substr(p_customer, instr(p_customer, '-') + 1, length(p_customer)); END IF; --当追踪号不为空的情况下 IF x_trace_num IS NULL THEN get_date(p_demand_date1, l_trace_start_date, l_nouse_date); ----物料ID l_item_id := p_item_id; --取物料code liuqin 2012.7.27防止追踪号重复 l_item_no := ''; SELECT DISTINCT hi.item_code INTO l_item_no FROM hcm_item_v hi WHERE hi.item_id = l_item_id; IF substr(l_item_no, 1, 1) = '9' THEN l_item_no := '9'; END IF; l_need_check_date := p_demand_time; IF l_item_id IS NOT NULL THEN -- IF instr(p_demand_date2, 'W') > 0 THEN l_period_type_new := 'W'; l_trace_demand_date_new := p_week_end_time; ELSIF instr(p_demand_date2, 'M') > 0 THEN l_period_type_new := 'M'; l_trace_demand_date_new := add_months(l_need_check_date, 1) - 1; ELSIF instr(p_demand_date2, 'D') > 0 THEN l_period_type_new := 'D'; l_trace_demand_date_new := l_need_check_date; ELSIF instr(p_demand_date2, 'Y') > 0 THEN l_period_type_new := 'Y'; l_trace_demand_date_new := add_months(l_need_check_date, 12) - 1; ELSE l_period_type_new := 'D'; l_trace_demand_date_new := l_need_check_date; END IF; --判断该物料+时段是否在hcm_demand_trace_num中存在 --增加attribute3的判断, attribte3记录客户名称-字符前的字符 BEGIN SELECT hdtn.trace_id, hdtn.trace_num, hdtn.demand_start_date, hdtn.period_type INTO l_trace_id, l_trace_num, l_trace_demand_date, l_period_type FROM hcm_demand_trace_num hdtn WHERE hdtn.period = p_demand_date2 AND hdtn.item_id = l_item_id AND hdtn.period_type = l_period_type_new AND nvl(hdtn.attribute1, '&#&') = nvl(p_buyer, '&#&') AND nvl(hdtn.attribute2, '&#&') = nvl(p_attribute2, '&#&') AND nvl(hdtn.attribute3, '&#&') = nvl(l_customer_name_prefix, '&#&'); EXCEPTION WHEN no_data_found THEN --在hcm_demand_trace_num中不存在追踪ID时先插一条记录然后再判断行头或列头时间是否在追踪时段范围内，若在则生成追踪号 l_period_type := l_period_type_new; l_trace_demand_date := l_trace_demand_date_new; SELECT hcm_demand_trace_num_s.nextval INTO l_trace_id FROM dual; INSERT INTO hcm_demand_trace_num (trace_id, period, period_type, trace_num, description, demand_start_date, demand_end_date, item_id, attribute1, attribute2, attribute3, creation_date, created_by, last_updated_by, last_update_date, last_update_login, attribute9, attribute10) VALUES (l_trace_id, p_demand_date2, l_period_type, NULL, '', l_need_check_date, l_trace_demand_date, l_item_id, p_buyer, p_attribute2, l_customer_name_prefix, --attbibute3 SYSDATE, p_user_id, p_user_id, SYSDATE, p_user_id, l_item_no, 'ins10'); END; IF l_trace_num IS NULL THEN check_imp_date(p_item_id => l_item_id, p_customer_id => l_customer_id, p_trace_start_date => l_trace_start_date, p_need_check_date => l_need_check_date, p_period_type => l_period_type, /*p_user_id => p_user_id,*/ x_msg_flag => l_imp_date_sucess, x_msg_date => l_trace_end_date); IF l_imp_date_sucess OR p_proj_phase IS NOT NULL THEN --在hcm_demand_trace_num中更新该记录的追踪号 l_trace_num_new := build_trace_num(p_customer_id => l_customer_id, p_demand_type => p_demand_type, p_trace_start_date => l_trace_start_date, p_demand_date2 => p_demand_date2, p_period_type => l_period_type, p_proj_phase => p_proj_phase, p_customer_name => p_customer); UPDATE hcm_demand_trace_num hdtn SET hdtn.trace_num = l_trace_num_new, hdtn.attribute10 = l_customer_id || ';' || p_demand_type || ';' || l_trace_start_date || ';' || p_demand_date2 || ';' || l_period_type || ';' || p_proj_phase || ';' || p_customer -- to_char(l_trace_id) WHERE hdtn.trace_id = l_trace_id; END IF; x_trace_num := l_trace_num_new; ELSE x_trace_num := l_trace_num; END IF; ELSE NULL; END IF; ELSE BEGIN SELECT hdtn.trace_id INTO l_trace_id FROM hcm_demand_trace_num hdtn WHERE hdtn.trace_num = x_trace_num AND nvl(hdtn.attribute2, '~##~') = nvl(p_attribute2, '~##~') AND hdtn.item_id = p_item_id; END; END IF; x_trace_id := l_trace_id; x_customer_id := l_customer_id; x_demand_date := l_trace_demand_date; END get_trace_num; /*========================================================== HCM-B-P02-05 版本号生成 参数： p_prod_model 机型 p_demand_date 行头或列头 需求时间 p_trace_num 返回值：x_msg_data Created : 2009-10-09 hand ==========================================================*/ PROCEDURE get_version(p_prod_model IN VARCHAR2, p_lot IN VARCHAR2, x_version_id OUT NUMBER, x_msg_data OUT NOCOPY VARCHAR2) IS l_version1 VARCHAR2(30); l_version VARCHAR2(30); l_systime VARCHAR2(10); l_trace_date DATE; l_version_exist VARCHAR(1) := 'N'; l_version_rec hcm_demand_version%ROWTYPE; BEGIN --先从临时表里面取 BEGIN SELECT temp.version, temp.version_id INTO x_msg_data, x_version_id FROM hcm_demand_version_temp temp WHERE temp.prod_model = p_prod_model; l_version_exist := 'Y'; EXCEPTION WHEN no_data_found THEN l_version_exist := 'N'; END; --临时表里面没有，根据规则生成 IF l_version_exist = 'N' THEN l_systime := get_ver_ft_time(SYSDATE); l_version1 := /*trim(p_prod_model) || '_' ||*/ l_systime; BEGIN SELECT * INTO l_version FROM (SELECT hdv.version FROM hcm_demand_version hdv WHERE hdv.product = TRIM(p_prod_model) AND hdv.version LIKE l_version1 || '%' ORDER BY hdv.version DESC) temp WHERE rownum = 1; x_msg_data := REPLACE(l_version1 || to_char(to_number(substr(l_version, length(l_version) - 1, 2)) + 1, '00'), ' ', ''); EXCEPTION WHEN no_data_found THEN x_msg_data := l_version1 || '00'; END; SELECT hcm_demand_version_s.nextval INTO x_version_id FROM dual; l_version_rec.version_id := x_version_id; l_version_rec.version := x_msg_data; l_version_rec.product := p_prod_model; l_version_rec.lot := p_lot; l_version_rec.creation_date := SYSDATE; l_version_rec.created_by := g_user_id; l_version_rec.last_updated_by := g_user_id; l_version_rec.last_update_date := SYSDATE; l_version_rec.last_update_login := g_user_id; l_version_rec.enter_date := SYSDATE; INSERT INTO hcm_demand_version VALUES l_version_rec; --把生成的版本号插入到临时表 INSERT INTO hcm_demand_version_temp VALUES (p_prod_model, x_msg_data, x_version_id); END IF; END get_version; /*========================================================== HCM-B-P02-05 版本号生成 参数： p_prod_model 机型 p_demand_date 行头或列头 需求时间 p_trace_num 返回值：x_msg_data Created : 2009-10-09 hand ==========================================================*/ FUNCTION get_remark(p_remark IN VARCHAR2) RETURN VARCHAR2 IS l_remark VARCHAR2(500) := NULL; BEGIN IF p_remark IS NOT NULL THEN l_remark := REPLACE(REPLACE(p_remark, '@', ','), '|', chr(10)); END IF; RETURN l_remark; END get_remark; /*========================================================== HCM-B-P02-05 FCST 导入 参数： p_customer 客户 返回值：x_msg_data 客户名称 系统中不存在 该客户 NULL 存在 该客户 Created : 2009-10-09 hand ==========================================================*/ /* PROCEDURE check_fcst_imp(p_customer IN VARCHAR2, x_msg_data OUT NOCOPY VARCHAR2) IS l_count NUMBER := 0; BEGIN SELECT COUNT(1) INTO l_count FROM hcm_customer_v hcv WHERE hcv.customer_alias = p_customer; IF l_count > 0 THEN x_msg_data := NULL; ELSE x_msg_data := p_customer; END IF; END check_fcst_imp;*/ PROCEDURE append_clob(p_batch_id IN NUMBER, p_clob CLOB) IS PRAGMA AUTONOMOUS_TRANSACTION; l_clob_new CLOB; l_clob_old CLOB; l_exists_flag VARCHAR2(1); l_new_line VARCHAR2(30) := chr(13) || chr(10); BEGIN BEGIN SELECT hict.text, 'Y' INTO l_clob_old, l_exists_flag FROM hcm_imp_clob_temp hict WHERE hict.batch_id = p_batch_id; EXCEPTION WHEN OTHERS THEN NULL; END; IF l_exists_flag = 'Y' THEN dbms_lob.createtemporary(lob_loc => l_clob_new, cache => TRUE); dbms_lob.writeappend(lob_loc => l_clob_new, amount => length(l_clob_old), buffer => l_clob_old); dbms_lob.writeappend(lob_loc => l_clob_new, amount => lengthb(l_new_line), buffer => l_new_line); dbms_lob.writeappend(lob_loc => l_clob_new, amount => length(p_clob), buffer => p_clob); UPDATE hcm_imp_clob_temp hict SET hict.text = l_clob_new WHERE hict.batch_id = p_batch_id; ELSE INSERT INTO hcm_imp_clob_temp (batch_id, text) VALUES (p_batch_id, p_clob); END IF; COMMIT; END append_clob; FUNCTION get_clob(p_batch_id IN NUMBER) RETURN CLOB IS PRAGMA AUTONOMOUS_TRANSACTION; l_clob CLOB; BEGIN FOR l_rec IN (SELECT * FROM hcm_imp_clob_temp t WHERE t.batch_id = p_batch_id) LOOP l_clob := l_clob || l_rec.text; END LOOP; DELETE FROM hcm_imp_clob_temp t WHERE t.batch_id = p_batch_id; COMMIT; RETURN l_clob; END get_clob; /*========================================================== HCM-B-P02-05 FCST需求导入 参数： p_in_parameter 格式如:'机型,时间,客户,数量，备注\n时间2.......'‘ p_demand_date 题头导入时间 '2009W31_FCST' p_user_id 用户ID 返回值： 0 客戶.............. 1 机型 不存在或..... 2 追踪号............ 3 BOM NO............ 4 备件信息.......... 5 样机信息.......... Created : 2009-10-09 hand ==========================================================*/ FUNCTION fcst_imp_for_ui(p_in_parameter IN CLOB, p_user_id IN VARCHAR2, p_demand_date IN VARCHAR2, p_batch_id IN VARCHAR2 DEFAULT NULL, p_last_flag IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2 IS l_record_count NUMBER; l_user_id NUMBER; l_prod_model VARCHAR2(50); l_customer VARCHAR2(50); l_qty NUMBER; l_demand_qty NUMBER; l_imp_date VARCHAR2(20); l_remark VARCHAR2(500); l_version VARCHAR2(30) := NULL; l_msg_return VARCHAR2(4000); l_msg_compt VARCHAR2(4000); l_version_rec hcm_demand_version%ROWTYPE; l_history_rec hcm_demand_info_history%ROWTYPE; l_item_id NUMBER; l_imp_time DATE; --列头时间 时间格式 l_trace_num VARCHAR2(20); l_customer_id NUMBER; l_trace_id NUMBER; l_version_id NUMBER; l_model_code VARCHAR2(50); l_buyer VARCHAR2(240); l_demand_date DATE; --用于hcm_demand_info_history插入离厂时间 l_trace_date VARCHAR2(20); --题头p_demand_date截取'_'字符之前的字符串 l_pos NUMBER; l_week_end_date DATE; --传出周数结束日期 l_in_parameter CLOB; l_j_date NUMBER; --ETD减去对应的天数，飞利浦客户-3，其它还是减8 p_phantom_item_type VARCHAR2(50); CURSOR cur_demand_item(p_item_id NUMBER) IS SELECT comp.component_item_id item_id, comp.component_quantity, 'Y' comps_flag, hmi.product_model FROM hcm_bom_component_v comp, hcm_bom bom, hcm_main_item_v hmi WHERE comp.bom_id = bom.bom_id AND hmi.item_id = bom.assembly_item_id AND bom.assembly_item_id = p_item_id AND bom.plant_id = hmi.attribute8 --物料的生产工厂 AND hmi.item_type = p_phantom_item_type UNION ALL SELECT hmi.item_id item_id, 0 component_quantity, 'N' comps_flag, hmi.product_model FROM hcm_main_item_v hmi WHERE hmi.item_id = p_item_id AND hmi.item_type <> p_phantom_item_type; BEGIN /* IF p_last_flag = 'Y' THEN append_clob(p_batch_id => p_batch_id,p_clob => p_in_parameter); l_in_parameter := get_clob(p_batch_id => p_batch_id); ELSE append_clob(p_batch_id => p_batch_id,p_clob => p_in_parameter); RETURN 'S@'; END IF;*/ split_parameter(p_in_parameter => p_in_parameter); l_record_count := hcm_demand_pck.g_in_record_count; l_user_id := to_number(p_user_id); l_pos := instr(p_demand_date, '_'); g_user_id := l_user_id; IF l_pos > 0 THEN l_trace_date := substr(p_demand_date, 1, l_pos - 1); END IF; p_phantom_item_type := hcm_profile.value('PHANTOM_ITEM_TYPE'); FOR i IN 1 .. l_record_count LOOP --取参数 l_prod_model := hcm_demand_pck.g_in_parameter_table(i) (1); l_customer := hcm_demand_pck.g_in_parameter_table(i) (2); l_buyer := hcm_demand_pck.g_in_parameter_table(i) (3); l_model_code := hcm_demand_pck.g_in_parameter_table(i) (4); l_imp_date := hcm_demand_pck.g_in_parameter_table(i) (5); l_demand_qty := to_number(hcm_demand_pck.g_in_parameter_table(i) (6)); l_remark := hcm_demand_pck.g_in_parameter_table(i) (7); l_customer_id := NULL; l_trace_id := NULL; l_trace_num := NULL; l_imp_time := NULL; IF upper(l_customer) = 'PHILIPS' THEN l_j_date := 3; ELSE l_j_date := 8; END IF; IF l_demand_qty IS NOT NULL THEN get_latest_item(p_prod_model => l_prod_model, p_demand_date => l_imp_date, x_msg_data => l_item_id, x_msg_date => l_imp_time, x_msg_date2 => l_week_end_date); FOR lr_demand_item IN cur_demand_item(l_item_id) LOOP l_trace_num := NULL; l_item_id := lr_demand_item.item_id; IF lr_demand_item.comps_flag = 'Y' THEN l_qty := lr_demand_item.component_quantity * l_demand_qty; l_prod_model := lr_demand_item.product_model; ELSE l_qty := l_demand_qty; END IF; IF l_version IS NULL THEN get_version(p_prod_model => l_prod_model, p_lot => p_demand_date, x_version_id => l_version_id, x_msg_data => l_version); END IF; IF l_version IS NOT NULL THEN --往hcm_demand_version中插入一条记录 /* get_latest_item(p_prod_model => l_prod_model, p_demand_date => l_imp_date, x_msg_data => l_item_id, x_msg_date => l_imp_time, x_msg_date2 => l_week_end_date);*/ IF l_item_id IS NOT NULL THEN get_trace_num(p_user_id => l_user_id, p_customer => l_customer, p_demand_type => 'FCST', p_item_id => l_item_id, p_demand_time => l_imp_time, p_week_end_time => l_week_end_date, p_demand_date1 => l_trace_date, ---加工以下只取_前面的字符串 p_demand_date2 => l_imp_date, p_buyer => l_buyer, x_trace_num => l_trace_num, x_customer_id => l_customer_id, x_trace_id => l_trace_id, x_demand_date => l_demand_date); SELECT hcm_demand_info_history_s.nextval INTO l_history_rec.history_id FROM dual; l_history_rec.item_id := l_item_id; l_history_rec.customer_id := l_customer_id; l_history_rec.trace_id := l_trace_id; l_history_rec.demand_type := 'FCST'; l_history_rec.shipping_date := l_imp_time - 8 /*7*/ ; ---导入的需求日期减去一周,为与HJIT日历保持一致，减去8天 l_history_rec.demand_qty := l_qty; l_history_rec.shipping_qty := l_qty; l_history_rec.version_id := l_version_id; l_history_rec.remark := get_remark(l_remark); l_history_rec.attribute1 := l_model_code; l_history_rec.attribute2 := l_buyer; l_history_rec.attribute3 := NULL; l_history_rec.attribute4 := NULL; l_history_rec.attribute5 := NULL; l_history_rec.attribute6 := NULL; l_history_rec.attribute7 := NULL; l_history_rec.attribute8 := NULL; l_history_rec.attribute9 := NULL; l_history_rec.attribute10 := NULL; l_history_rec.creation_date := SYSDATE; l_history_rec.created_by := l_user_id; l_history_rec.last_updated_by := l_user_id; l_history_rec.last_update_date := SYSDATE; l_history_rec.last_update_login := l_user_id; INSERT INTO hcm_demand_info_history VALUES l_history_rec; ELSE ROLLBACK; --没有对应的物料#！ hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_DI_ITEM_IS_NULL'); l_msg_compt := l_msg_compt || hcm_api.get_message(-1) || '#'; END IF; ELSE --无法生成版本号#！ hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_DI_CAN_NOT_RBD_VERS'); l_msg_compt := l_msg_compt || hcm_api.get_message(-1) || '#'; END IF; END LOOP; IF l_msg_compt IS NOT NULL THEN l_msg_return := l_msg_return || '|E@' || l_msg_compt; ELSE hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_COMMON_SUCCESS'); l_msg_return := l_msg_return || '|S@' || hcm_api.get_message(-1) || '#'; END IF; ELSE --无数量！ hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_DI_QTY_IS_NULL'); l_msg_return := l_msg_return || '|S@' || hcm_api.get_message(-1) || '#'; END IF; END LOOP; RETURN TRIM('#' FROM l_msg_return); END fcst_imp_for_ui; /*========================================================== HCM-B-P02-05 ODF需求导入 参数： p_in_parameter 格式如:'机型,时间,数量\n时间2.......'‘ p_demand_date 题头导入时间 p_user_id 用户ID 返回值： 0 客戶............. 1 机型 不存在或..... 2 追踪号............ 3 BOM NO............ 4 备件信息.......... 5 样机信息.......... Created : 2009-10-09 hand ==========================================================*/ FUNCTION odf_imp_for_ui(p_in_parameter IN CLOB, p_user_id IN VARCHAR2, p_demand_date IN VARCHAR2, p_batch_id IN VARCHAR2 DEFAULT NULL, p_last_flag IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2 IS l_record_count NUMBER; l_user_id NUMBER; l_prod_model VARCHAR2(50); l_customer VARCHAR2(50); l_qty NUMBER; l_imp_date VARCHAR2(20); l_remark VARCHAR2(500); --备注 【注意：备注信息中的逗号要做替换】 l_version VARCHAR2(30); l_msg_return VARCHAR2(4000); l_version_rec hcm_demand_version%ROWTYPE; l_history_rec hcm_demand_info_history%ROWTYPE; l_history_comps_rec hcm_demand_info_history%ROWTYPE; l_history_stock_rec hcm_demand_info_history%ROWTYPE; l_history_model_rec hcm_demand_info_history%ROWTYPE; l_item_id NUMBER; l_imp_time DATE; --列头时间 时间格式 l_trace_num VARCHAR2(20); l_customer_id NUMBER; l_trace_id NUMBER; l_version_id NUMBER; l_model_code VARCHAR2(50); l_buyer VARCHAR2(240); l_item_code VARCHAR2(50); --BOM NO. l_dest_pos VARCHAR2(100); --目的地 l_left_time VARCHAR2(20); --离厂时间 l_stock_info_all VARCHAR2(200); --备件信息 eg: 物料*数量;物料*数量 l_stock_info VARCHAR2(100); -- 拆开后的每个备件信息 eg 物料*数量 l_model_info VARCHAR2(200); --样机信息 l_demand_date DATE; --用于FCST hcm_demand_info_history插入离厂时间 l_trace_date VARCHAR2(20); --题头p_demand_date截取'_'字符之前的字符串 l_pos NUMBER; l_week_end_date DATE; --传出周数结束日期 l_stock_num VARCHAR2(100); l_stock_id NUMBER; l_stock_qty NUMBER; l_stock_trace_num VARCHAR2(20); l_stock_trace_id NUMBER; l_stock_version VARCHAR2(100); l_model_trace_id NUMBER; l_model_trace_num VARCHAR2(20); l_item_type VARCHAR2(20); l_plant_id NUMBER; l_comps_code VARCHAR2(40); l_comps_product_model VARCHAR2(40); l_comps_trace_num VARCHAR2(20); l_comps_trace_id NUMBER; l_in_parameter CLOB; BEGIN IF p_last_flag = 'Y' THEN append_clob(p_batch_id => p_batch_id, p_clob => p_in_parameter); l_in_parameter := get_clob(p_batch_id => p_batch_id); ELSE append_clob(p_batch_id => p_batch_id, p_clob => p_in_parameter); RETURN 'S@'; END IF; split_parameter(p_in_parameter => l_in_parameter); l_record_count := hcm_demand_pck.g_in_record_count; --dbms_output.put_line('l_record_count:'||l_record_count); l_user_id := to_number(p_user_id); l_pos := instr(p_demand_date, '_'); g_user_id := l_user_id; IF l_pos > 0 THEN l_trace_date := substr(p_demand_date, 1, l_pos - 1); END IF; --批处理开始 FOR i IN 1 .. l_record_count LOOP --取参数 l_trace_num := hcm_demand_pck.g_in_parameter_table(i) (1); l_prod_model := hcm_demand_pck.g_in_parameter_table(i) (2); l_customer := hcm_demand_pck.g_in_parameter_table(i) (3); l_qty := to_number(hcm_demand_pck.g_in_parameter_table(i) (4)); --dbms_output.put_line('l_qty:'||l_qty); l_imp_date := hcm_demand_pck.g_in_parameter_table(i) (5); l_model_code := hcm_demand_pck.g_in_parameter_table(i) (6); l_buyer := hcm_demand_pck.g_in_parameter_table(i) (7); l_item_code := hcm_demand_pck.g_in_parameter_table(i) (8); l_dest_pos := hcm_demand_pck.g_in_parameter_table(i) (9); l_left_time := hcm_demand_pck.g_in_parameter_table(i) (10); l_stock_info_all := hcm_demand_pck.g_in_parameter_table(i) (11); l_model_info := hcm_demand_pck.g_in_parameter_table(i) (12); l_remark := hcm_demand_pck.g_in_parameter_table(i) (13); l_customer_id := NULL; l_trace_id := NULL; --l_trace_num := NULL; Comment by hand l_imp_time := NULL; l_item_id := NULL; l_stock_info_all := TRIM(REPLACE(REPLACE(TRIM(l_stock_info_all), chr(13)), chr(10))); ------------------------------------ -- 实际需求数量 = 需求数量 - 样机 l_qty := l_qty - nvl(to_number(l_model_info), 0); get_version(p_prod_model => l_prod_model, p_lot => p_demand_date, x_version_id => l_version_id, x_msg_data => l_version); IF l_version IS NOT NULL THEN get_latest_item(p_prod_model => l_prod_model, p_demand_date => l_imp_date, x_msg_data => l_item_id, x_msg_date => l_imp_time, x_msg_date2 => l_week_end_date); IF l_item_id IS NOT NULL THEN SELECT i.item_type, i.attribute8 INTO l_item_type, l_plant_id FROM hcm_main_item_v i WHERE i.item_id = l_item_id; get_trace_num(p_user_id => l_user_id, p_customer => l_customer, p_demand_type => 'ORDER', p_item_id => l_item_id, p_demand_time => l_imp_time, p_week_end_time => l_week_end_date, p_demand_date1 => l_trace_date, ---加工以下只取_前面的字符串 p_demand_date2 => l_imp_date, p_buyer => l_buyer, x_trace_num => l_trace_num, x_customer_id => l_customer_id, x_trace_id => l_trace_id, x_demand_date => l_demand_date); SELECT hcm_demand_info_history_s.nextval INTO l_history_rec.history_id FROM dual; l_history_rec.item_id := l_item_id; l_history_rec.customer_id := l_customer_id; l_history_rec.trace_id := l_trace_id; l_history_rec.demand_type := 'ORDER'; l_history_rec.shipping_date := to_date(l_left_time, 'yyyy-mm-dd hh24:mi:ss'); l_history_rec.demand_qty := l_qty; l_history_rec.shipping_qty := l_qty; l_history_rec.version_id := l_version_id; l_history_rec.remark := get_remark(l_remark); l_history_rec.attribute1 := l_model_code; l_history_rec.attribute2 := l_buyer; l_history_rec.attribute3 := l_dest_pos; l_history_rec.attribute4 := NULL; l_history_rec.attribute5 := l_stock_info_all; l_history_rec.attribute6 := l_model_info; l_history_rec.attribute7 := NULL; l_history_rec.attribute8 := NULL; l_history_rec.attribute9 := NULL; l_history_rec.attribute10 := NULL; l_history_rec.creation_date := SYSDATE; l_history_rec.created_by := l_user_id; l_history_rec.last_updated_by := l_user_id; l_history_rec.last_update_date := SYSDATE; l_history_rec.last_update_login := l_user_id; INSERT INTO hcm_demand_info_history VALUES l_history_rec; --------------------------- --样机和备件 l_history_model_rec := l_history_rec; l_history_stock_rec := l_history_rec; --------------------------- --备件 WHILE l_stock_info_all IS NOT NULL LOOP l_pos := instr(l_stock_info_all, '|'); IF l_pos > 0 THEN l_stock_info := substr(l_stock_info_all, 1, l_pos - 1); l_stock_info_all := substr(l_stock_info_all, l_pos + length('|')); ELSE l_stock_info := l_stock_info_all; l_stock_info_all := NULL; END IF; --备件 l_stock_num := TRIM(substr(l_stock_info, 1, instr(l_stock_info, '*') - 1)); --备件数量 l_stock_qty := to_number(TRIM(substr(l_stock_info, instr(l_stock_info, '*') + 1))); IF l_stock_num IS NOT NULL THEN get_version(p_prod_model => l_stock_num, p_lot => p_demand_date, x_version_id => l_version_rec.version_id, x_msg_data => l_stock_version); SELECT hai.item_id INTO l_stock_id FROM hcm_main_item_v hai WHERE hai.item_code = l_stock_num AND hai.enable_flag = 'Y'; --初始下 l_stock_trace_num := NULL; get_trace_num(p_user_id => l_user_id, p_customer => l_customer, p_demand_type => 'ORDER', p_item_id => l_stock_id, p_demand_time => l_imp_time, p_week_end_time => l_week_end_date, p_demand_date1 => l_trace_date, ---加工以下只取_前面的字符串 p_demand_date2 => l_imp_date, p_buyer => l_buyer, p_attribute2 => 'SPARE', x_trace_num => l_stock_trace_num, x_customer_id => l_customer_id, x_trace_id => l_stock_trace_id, x_demand_date => l_demand_date); UPDATE hcm_demand_trace_num dtn SET dtn.trace_num = l_trace_num, reference_trace_id = l_trace_id, attribute2 = 'SPARE', dtn.attribute10 = 'new2' WHERE dtn.trace_id = l_stock_trace_id; SELECT hcm_demand_info_history_s.nextval INTO l_history_stock_rec.history_id FROM dual; l_history_stock_rec.item_id := l_stock_id; l_history_stock_rec.demand_qty := l_stock_qty; l_history_stock_rec.shipping_qty := l_stock_qty; l_history_stock_rec.remark := l_trace_id; l_history_stock_rec.version_id := l_version_rec.version_id; l_history_stock_rec.trace_id := l_stock_trace_id; l_history_stock_rec.remark := NULL; l_history_stock_rec.attribute5 := NULL; l_history_stock_rec.attribute6 := NULL; INSERT INTO hcm_demand_info_history VALUES l_history_stock_rec; END IF; END LOOP; --------------------------- --样机 IF l_model_info IS NOT NULL THEN --初始下 l_model_trace_num := NULL; get_trace_num(p_user_id => l_user_id, p_customer => l_customer, p_demand_type => 'ORDER', p_item_id => l_item_id, p_demand_time => l_imp_time, p_week_end_time => l_week_end_date, p_demand_date1 => l_trace_date, ---加工以下只取_前面的字符串 p_demand_date2 => l_imp_date, p_buyer => l_buyer, p_attribute2 => 'SAMPLE', x_trace_num => l_model_trace_num, x_customer_id => l_customer_id, x_trace_id => l_model_trace_id, x_demand_date => l_demand_date); SELECT hcm_demand_info_history_s.nextval INTO l_history_model_rec.history_id FROM dual; l_history_model_rec.demand_qty := to_number(l_model_info); l_history_model_rec.shipping_qty := 0; l_history_model_rec.version_id := l_version_id; l_history_model_rec.trace_id := l_model_trace_id; l_history_model_rec.remark := NULL; l_history_model_rec.attribute5 := NULL; l_history_model_rec.attribute6 := NULL; INSERT INTO hcm_demand_info_history VALUES l_history_model_rec; UPDATE hcm_demand_trace_num dtn SET dtn.trace_num = l_trace_num, reference_trace_id = l_trace_id, attribute2 = 'SAMPLE', dtn.attribute10 = 'new3' WHERE dtn.trace_id = l_model_trace_id; END IF; --------------------------- --如果是虚拟件，导入组件需求 /* IF l_item_type = 'PH' THEN FOR l_comps IN (SELECT comp.component_item_id, comp.component_quantity FROM hcm_bom_component comp, hcm_bom bom WHERE comp.bom_id = bom.bom_id AND bom.assembly_item_id = l_item_id AND bom.alternate IS NULL AND bom.plant_id = l_plant_id) LOOP --取机型和组件code SELECT nvl(hmi.product_model, hmi.item_code), hmi.item_code INTO l_comps_product_model, l_comps_code FROM hcm_main_item_v hmi WHERE hmi.item_id = l_comps.component_item_id; get_version(p_prod_model => l_comps_product_model, p_trace_num => NULL, x_msg_data => l_version); IF l_version IS NOT NULL THEN --往hcm_demand_version中插入一条记录 SELECT hcm_demand_version_s.nextval INTO l_version_id FROM dual; l_version_rec.version_id := l_version_id; l_version_rec.version := l_version; l_version_rec.lot := p_demand_date; l_version_rec.product := l_comps_product_model; l_version_rec.creation_date := SYSDATE; l_version_rec.created_by := l_user_id; l_version_rec.last_updated_by := l_user_id; l_version_rec.last_update_date := SYSDATE; l_version_rec.last_update_login := l_user_id; l_version_rec.enter_date := SYSDATE; INSERT INTO hcm_demand_version VALUES l_version_rec; IF l_item_id IS NOT NULL THEN get_trace_num(p_user_id => l_user_id, p_customer => l_customer, p_demand_type => 'ORDER', p_item_id => l_comps.component_item_id, p_demand_time => l_imp_time, p_week_end_time => l_week_end_date, p_demand_date1 => l_trace_date, ---加工以下只取_前面的字符串 p_demand_date2 => l_imp_date, p_buyer => NULL, x_trace_num => l_comps_trace_num, x_customer_id => l_customer_id, x_trace_id => l_comps_trace_id, x_demand_date => l_demand_date); SELECT hcm_demand_info_history_s.nextval INTO l_history_rec.history_id FROM dual; l_history_rec.item_id := l_comps.component_item_id; l_history_rec.customer_id := l_customer_id; l_history_rec.trace_id := l_comps_trace_id; l_history_rec.demand_type := 'ORDER'; l_history_rec.shipping_date := to_date(l_left_time, 'yyyy-mm-dd'); l_history_rec.demand_qty := l_qty * l_comps.component_quantity; l_history_rec.shipping_qty := l_qty * l_comps.component_quantity; l_history_rec.version_id := l_version_id; l_history_rec.remark := get_remark(l_remark); -- l_history_rec.attribute1 := l_model_code; -- l_history_rec.attribute2 := l_buyer; -- l_history_rec.attribute3 := l_dest_pos; l_history_rec.attribute4 := NULL; -- l_history_rec.attribute5 := l_stock_info; --l_history_rec.attribute6 := l_model_info; l_history_rec.attribute7 := NULL; l_history_rec.attribute8 := NULL; l_history_rec.attribute9 := NULL; l_history_rec.attribute10 := NULL; l_history_rec.creation_date := SYSDATE; l_history_rec.created_by := l_user_id; l_history_rec.last_updated_by := l_user_id; l_history_rec.last_update_date := SYSDATE; l_history_rec.last_update_login := l_user_id; INSERT INTO hcm_demand_info_history VALUES l_history_rec; END IF; END IF; END LOOP; END IF;*/ --PH -- COMMIT; IF l_msg_return IS NULL THEN --导入成功 hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_COMMON_SUCCESS'); l_msg_return := l_msg_return || '|S@' || hcm_api.get_message(-1) || '#'; ELSE --导入成功 hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_COMMON_SUCCESS'); l_msg_return := l_msg_return || '|S@' || hcm_api.get_message(-1) || '#'; END IF; ELSE ROLLBACK; IF l_msg_return IS NULL THEN --没有对应的物料 hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_DI_ITEM_IS_NULL'); l_msg_return := l_msg_return || '|E@' || hcm_api.get_message(-1) || '#'; ELSE --没有对应的物料 hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_DI_ITEM_IS_NULL'); l_msg_return := l_msg_return || '|E@' || hcm_api.get_message(-1) || '#'; END IF; END IF; ELSE IF l_msg_return IS NULL THEN --无法生成版本号# hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_DI_CAN_NOT_RBD_VERS'); l_msg_return := l_msg_return || '|E@' || hcm_api.get_message(-1) || '#'; ELSE --无法生成版本号# hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_DI_CAN_NOT_RBD_VERS'); l_msg_return := l_msg_return || '|E@' || hcm_api.get_message(-1) || '#'; END IF; END IF; END LOOP; RETURN l_msg_return; END odf_imp_for_ui; /*========================================================== HCM-B-P02-05 SP需求导入 参数： p_in_parameter 格式如:'机型,时间,数量\n时间2.......'‘ p_demand_date 题头导入时间 p_user_id 用户ID 返回值： 0 客戶............. 1 机型 不存在或..... 2 追踪号............ 3 BOM NO............ 4 备件信息.......... 5 样机信息.......... Created : 2009-10-09 hand ==========================================================*/ FUNCTION sp_imp_for_ui(p_in_parameter IN CLOB, p_user_id IN VARCHAR2, p_demand_date IN VARCHAR2, p_batch_id IN VARCHAR2 DEFAULT NULL, p_last_flag IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2 IS l_record_count NUMBER; l_user_id NUMBER; l_prod_model VARCHAR2(50); --机型 l_customer VARCHAR2(50); --客户 l_qty NUMBER; --走货数量 l_imp_date VARCHAR2(20); --导入行头时间 【暂时无】 l_version VARCHAR2(30); l_msg_return VARCHAR2(4000); l_version_rec hcm_demand_version%ROWTYPE; l_history_rec hcm_demand_info_history%ROWTYPE; l_item_id NUMBER; l_imp_time DATE; --列头时间 时间格式 通过函数进行转换后的值 l_customer_id NUMBER; l_trace_id NUMBER; l_version_id NUMBER; l_dest_pos VARCHAR2(100); --目的地 l_left_time VARCHAR2(20); --离厂时间 l_arear VARCHAR2(20); --地区 l_dest_port VARCHAR2(50); --目的港 l_custom_order VARCHAR2(50); --客户订单号 l_trace_num VARCHAR2(50); --追踪号 l_container_num VARCHAR2(50); --货柜号 l_container_size VARCHAR2(50); --货柜尺寸 l_shipmode VARCHAR2(50); --发运方式 l_remark VARCHAR2(500); --备注 l_demand_date DATE; --用于FCST hcm_demand_info_history插入离厂时间 l_trace_date VARCHAR2(20); --题头p_demand_date截取'_'字符之前的字符串 l_pos NUMBER; l_week_end_date DATE; --传出周数结束日期 l_in_parameter CLOB; BEGIN IF p_last_flag = 'Y' THEN append_clob(p_batch_id => p_batch_id, p_clob => p_in_parameter); l_in_parameter := get_clob(p_batch_id => p_batch_id); ELSE append_clob(p_batch_id => p_batch_id, p_clob => p_in_parameter); RETURN 'S@'; END IF; split_parameter(p_in_parameter => l_in_parameter); l_record_count := hcm_demand_pck.g_in_record_count; l_user_id := to_number(p_user_id); l_pos := instr(p_demand_date, '_'); g_user_id := l_user_id; IF l_pos > 0 THEN l_trace_date := substr(p_demand_date, 1, l_pos - 1); END IF; --批处理开始 FOR i IN 1 .. l_record_count LOOP --取参数 l_prod_model := hcm_demand_pck.g_in_parameter_table(i) (1); l_customer := hcm_demand_pck.g_in_parameter_table(i) (2); l_qty := to_number(hcm_demand_pck.g_in_parameter_table(i) (3)); l_arear := hcm_demand_pck.g_in_parameter_table(i) (4); l_dest_port := hcm_demand_pck.g_in_parameter_table(i) (5); l_dest_pos := hcm_demand_pck.g_in_parameter_table(i) (6); l_custom_order := hcm_demand_pck.g_in_parameter_table(i) (7); l_trace_num := hcm_demand_pck.g_in_parameter_table(i) (8); l_left_time := hcm_demand_pck.g_in_parameter_table(i) (9); l_container_num := hcm_demand_pck.g_in_parameter_table(i) (10); l_container_size := hcm_demand_pck.g_in_parameter_table(i) (11); l_shipmode := hcm_demand_pck.g_in_parameter_table(i) (12); l_remark := hcm_demand_pck.g_in_parameter_table(i) (13); l_customer_id := NULL; l_trace_id := NULL; l_imp_time := NULL; l_item_id := NULL; --get_version(p_prod_model => l_prod_model, p_trace_num => NULL, x_msg_data => l_version); get_version(p_prod_model => l_prod_model, p_lot => p_demand_date, x_version_id => l_version_id, x_msg_data => l_version); IF l_version IS NOT NULL THEN SELECT hdtn.item_id INTO l_item_id FROM hcm_demand_trace_num hdtn WHERE hdtn.trace_num = l_trace_num AND hdtn.attribute2 IS NULL; --限定只选择主需求 IF l_item_id IS NOT NULL THEN get_trace_num(p_user_id => l_user_id, p_customer => l_customer, p_demand_type => 'SHIPPING', p_item_id => l_item_id, p_demand_time => NULL, p_week_end_time => NULL, p_demand_date1 => l_trace_date, ---加工以下只取_前面的字符串 p_demand_date2 => NULL, x_trace_num => l_trace_num, x_customer_id => l_customer_id, x_trace_id => l_trace_id, x_demand_date => l_demand_date); SELECT hcm_demand_info_history_s.nextval INTO l_history_rec.history_id FROM dual; l_history_rec.item_id := l_item_id; l_history_rec.customer_id := l_customer_id; l_history_rec.trace_id := l_trace_id; l_history_rec.demand_type := 'SHIPPING'; l_history_rec.shipping_date := to_date(l_left_time, 'yyyy-mm-dd'); l_history_rec.demand_qty := l_qty; l_history_rec.shipping_qty := l_qty; l_history_rec.version_id := l_version_id; l_history_rec.remark := get_remark(l_remark); l_history_rec.attribute1 := l_dest_pos; l_history_rec.attribute2 := l_container_num; l_history_rec.attribute3 := l_shipmode; l_history_rec.attribute4 := l_custom_order; l_history_rec.attribute5 := l_arear; l_history_rec.attribute6 := l_dest_port; l_history_rec.attribute7 := l_container_size; l_history_rec.attribute8 := NULL; l_history_rec.attribute9 := NULL; l_history_rec.attribute10 := NULL; l_history_rec.creation_date := SYSDATE; l_history_rec.created_by := l_user_id; l_history_rec.last_updated_by := l_user_id; l_history_rec.last_update_date := SYSDATE; l_history_rec.last_update_login := l_user_id; INSERT INTO hcm_demand_info_history VALUES l_history_rec; -- COMMIT; IF l_msg_return IS NULL THEN --导入成功 hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_COMMON_SUCCESS'); l_msg_return := l_msg_return || '|S@' || hcm_api.get_message(-1) || '#'; ELSE --导入成功 hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_COMMON_SUCCESS'); l_msg_return := l_msg_return || '|S@' || hcm_api.get_message(-1) || '#'; END IF; ELSE ROLLBACK; IF l_msg_return IS NULL THEN --没有对应的物料 hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_DI_ITEM_IS_NULL'); l_msg_return := l_msg_return || '|E@' || hcm_api.get_message(-1) || '#'; ELSE --没有对应的物料 hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_DI_ITEM_IS_NULL'); l_msg_return := l_msg_return || '|E@' || hcm_api.get_message(-1) || '#'; END IF; END IF; ELSE IF l_msg_return IS NULL THEN --无法生成版本号# hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_DI_CAN_NOT_RBD_VERS'); l_msg_return := l_msg_return || '|E@' || hcm_api.get_message(-1) || '#'; ELSE --无法生成版本号# hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_DI_CAN_NOT_RBD_VERS'); l_msg_return := l_msg_return || '|E@' || hcm_api.get_message(-1) || '#'; END IF; END IF; END LOOP; RETURN l_msg_return; END sp_imp_for_ui; /*========================================================== HCM-B-P02-05 ODF需求导入 参数： p_in_parameter 格式如:'机型,时间,数量\n时间2.......'‘ p_demand_date 题头导入时间 p_user_id 用户ID 返回值： 0 客戶............. 1 机型 不存在或..... 2 追踪号............ 3 BOM NO............ 4 备件信息.......... 5 样机信息.......... Created : 2009-10-09 hand ==========================================================*/ FUNCTION proj_imp_for_ui(p_in_parameter IN CLOB, p_user_id IN VARCHAR2, p_demand_date IN VARCHAR2, p_batch_id IN VARCHAR2 DEFAULT NULL, p_last_flag IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2 IS l_record_count NUMBER; l_user_id NUMBER; l_item_code VARCHAR2(50); l_qty NUMBER; l_imp_date VARCHAR2(20); --试产日期 l_remark VARCHAR2(500); l_version VARCHAR2(30); l_msg_return VARCHAR2(4000); l_version_rec hcm_demand_version%ROWTYPE; l_history_rec hcm_demand_info_history%ROWTYPE; l_item_id NUMBER; l_customer_id NUMBER; l_imp_time DATE; --列头时间 时间格式 l_trace_num VARCHAR2(20); l_trace_id NUMBER; l_version_id NUMBER; l_exp_site VARCHAR2(50); l_proj_phase VARCHAR2(50); l_prod_model VARCHAR2(50); --机型 l_demand_date DATE; --用于FCST hcm_demand_info_history插入离厂时间 l_trace_date VARCHAR2(20); --题头p_demand_date截取'_'字符之前的字符串 l_pos NUMBER; l_week_end_date DATE; --传出周数结束日期 l_in_parameter CLOB; BEGIN IF p_last_flag = 'Y' THEN append_clob(p_batch_id => p_batch_id, p_clob => p_in_parameter); l_in_parameter := get_clob(p_batch_id => p_batch_id); ELSE append_clob(p_batch_id => p_batch_id, p_clob => p_in_parameter); RETURN 'S@'; END IF; --p_in_parameter := ''; split_parameter(p_in_parameter => l_in_parameter); /*split_parameter(p_in_parameter => ',93G3235BB-BR41C,GTR,2009-12-20,工程部,90,08-VP3254-SWY|08-TDM03I-D01|46-40270C-24X|46-FF010T-06P|以上物料不用采购，用PGH606批次做的物料 ,93G3235BB-BR41C,GTR,2009-12-21,工程部,94,新增GTR');*/ l_record_count := hcm_demand_pck.g_in_record_count; l_user_id := to_number(p_user_id); g_user_id := l_user_id; -- l_user_id := to_number('-1'); l_pos := instr(p_demand_date, '_'); IF l_pos > 0 THEN l_trace_date := substr(p_demand_date, 2, l_pos - 2); END IF; --批处理开始 FOR i IN 1 .. l_record_count LOOP --取参数 l_trace_num := hcm_demand_pck.g_in_parameter_table(i) (1); l_item_code := hcm_demand_pck.g_in_parameter_table(i) (2); l_proj_phase := hcm_demand_pck.g_in_parameter_table(i) (3); l_imp_date := hcm_demand_pck.g_in_parameter_table(i) (4); l_exp_site := hcm_demand_pck.g_in_parameter_table(i) (5); l_qty := to_number(hcm_demand_pck.g_in_parameter_table(i) (6)); l_remark := hcm_demand_pck.g_in_parameter_table(i) (7); l_customer_id := NULL; /*l_trace_id := NULL; l_trace_num := NULL;*/ l_imp_time := NULL; l_item_id := NULL; SELECT nvl(hiv.product_model, hiv.item_code), hiv.item_id INTO l_prod_model, l_item_id FROM hcm_main_item_v hiv WHERE hiv.item_code = l_item_code AND hiv.enable_flag = 'Y' AND rownum = 1; get_version(p_prod_model => l_prod_model, p_lot => p_demand_date, x_version_id => l_version_id, x_msg_data => l_version); IF l_version IS NOT NULL THEN get_latest_item(p_prod_model => l_prod_model, p_demand_date => l_imp_date, x_msg_data => l_item_id, x_msg_date => l_imp_time, x_msg_date2 => l_week_end_date); IF l_item_id IS NOT NULL THEN get_trace_num(p_user_id => l_user_id, p_customer => NULL, p_demand_type => 'PROJECT', p_item_id => l_item_id, p_demand_time => l_imp_time, p_week_end_time => l_week_end_date, p_proj_phase => l_proj_phase, p_demand_date1 => l_trace_date, ---加工以下只取_前面的字符串 p_demand_date2 => l_imp_date, x_trace_num => l_trace_num, x_customer_id => l_customer_id, x_trace_id => l_trace_id, x_demand_date => l_demand_date); SELECT hcm_demand_info_history_s.nextval INTO l_history_rec.history_id FROM dual; l_history_rec.item_id := l_item_id; l_history_rec.customer_id := l_customer_id; l_history_rec.trace_id := l_trace_id; l_history_rec.demand_type := 'PROJECT'; l_history_rec.shipping_date := l_imp_time; --- l_history_rec.demand_qty := l_qty; l_history_rec.shipping_qty := l_qty; l_history_rec.version_id := l_version_id; l_history_rec.remark := get_remark(l_remark); l_history_rec.attribute1 := l_proj_phase; l_history_rec.attribute2 := l_imp_date; l_history_rec.attribute3 := l_exp_site; l_history_rec.attribute4 := NULL; l_history_rec.attribute5 := NULL; l_history_rec.attribute6 := NULL; l_history_rec.attribute7 := NULL; l_history_rec.attribute8 := NULL; l_history_rec.attribute9 := NULL; l_history_rec.attribute10 := NULL; l_history_rec.creation_date := SYSDATE; l_history_rec.created_by := l_user_id; l_history_rec.last_updated_by := l_user_id; l_history_rec.last_update_date := SYSDATE; l_history_rec.last_update_login := l_user_id; INSERT INTO hcm_demand_info_history VALUES l_history_rec; --COMMIT; IF l_msg_return IS NULL THEN --导入成功 hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_COMMON_SUCCESS'); l_msg_return := l_msg_return || '|S@' || hcm_api.get_message(-1) || '#'; ELSE --导入成功 hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_COMMON_SUCCESS'); l_msg_return := l_msg_return || '|S@' || hcm_api.get_message(-1) || '#'; END IF; ELSE ROLLBACK; IF l_msg_return IS NULL THEN --没有对应的物料 hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_DI_ITEM_IS_NULL'); l_msg_return := l_msg_return || '|E@' || hcm_api.get_message(-1) || '#'; ELSE --没有对应的物料 hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_DI_ITEM_IS_NULL'); l_msg_return := l_msg_return || '|E@' || hcm_api.get_message(-1) || '#'; END IF; END IF; ELSE IF l_msg_return IS NULL THEN --无法生成版本号# hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_DI_CAN_NOT_RBD_VERS'); l_msg_return := l_msg_return || '|E@' || hcm_api.get_message(-1) || '#'; ELSE --无法生成版本号# hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_DI_CAN_NOT_RBD_VERS'); l_msg_return := l_msg_return || '|E@' || hcm_api.get_message(-1) || '#'; END IF; END IF; END LOOP; RETURN l_msg_return; END proj_imp_for_ui; /*========================================================== HCM-B-P02-05 插临时表 参数： p_in_parameter 格式如:'historyid1,batchid\nhistoryid22.......'‘ 返回值： Created : 2009-10-09 hand ==========================================================*/ FUNCTION insert_iemp_for_ui(p_in_parameter IN CLOB, p_batch_id IN VARCHAR2) RETURN VARCHAR2 IS l_history_id NUMBER; l_record_count NUMBER; BEGIN split_parameter(p_in_parameter => p_in_parameter); l_record_count := hcm_demand_pck.g_in_record_count; FOR i IN 1 .. l_record_count LOOP --取参数 l_history_id := hcm_demand_pck.g_in_parameter_table(i) (1); BEGIN INSERT INTO hcm_demand_temp_publish (history_id, batch_id) VALUES (l_history_id, p_batch_id); EXCEPTION WHEN OTHERS THEN ROLLBACK; --插入异常 hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_DI_INSERT_ERROR'); RETURN '-1@' || hcm_api.get_message(-1); END; END LOOP; COMMIT; --成功 hcm_api.set_message(p_app_name => 'HCM', p_msg_name => 'HCM_COMMON_SUCCESS'); RETURN '0@' || hcm_api.get_message(-1); END insert_iemp_for_ui; END hcm_demand_pck;
浙公网安备 33010602011771号