Oracle EBS Item Import 物料导入 （2） 源代码 成功执行

downmovies

CREATE OR REPLACE PACKAGE PkgImportItems IS
-- Created : 7/27/2012 11:40:03 AM
-- Auther  : Wang Lin
-- Purpose : Import Items
-- Clear the Items data which process is 1 and should reload
FUNCTION Load_ITEMS RETURN NUMBER;
-- Check Items data NULL
FUNCTION Check_Items_Null RETURN NUMBER;
-- Check Items data organization
FUNCTION Check_Items_organization RETURN NUMBER;
-- Check Items data unit
FUNCTION Check_Items_unit RETURN NUMBER;
-- Insert Items data from the interface table
FUNCTION Operate_ITEMS RETURN NUMBER;
--main
PROCEDURE MAIN(ERRBUF OUT VARCHAR2, RETCODE OUT VARCHAR2);
END PkgImportItems;
CREATE OR REPLACE PACKAGE BODY PKGIMPORTITEMS IS
MASTER_ORG_CUR_FLAG NUMBER; --1:该物料org_id为主组织    0：该物料org_id为次组织
MASTER_ORG_SYS_FLAG NUMBER; --1:该物料item_no主组织存在 0：该物料item_no主组织不存在
CURREN_ORG_SYS_FLAG NUMBER; --1:该物料item_no次组织存在 0：该物料item_no次组织不存在
L_IFACE_REC         INV.MTL_SYSTEM_ITEMS_INTERFACE%ROWTYPE; --当前行记录
L_MST_ORG_ID        NUMBER; --该物料所在主组织id
L_CUR_ORG_ID        NUMBER; --该物料所在子组织id，如果不分配到子组织则没有数据
-- Clear the Items data which process is 1 and should reload
FUNCTION Load_ITEMS RETURN NUMBER IS
L_Count NUMBER;
BEGIN
wl_iface_pkg.to_log('Load Items Data Start',2,'*');
wl_iface_pkg.to_log('',2,'*');
--清空接口表中数据
DELETE FROM MTL_SYSTEM_ITEMS_INTERFACE a
WHERE a.process_flag = 1
AND TRIM (a.segment1) IN (
SELECT TRIM(b.segment1) FROM cux_items_temp b);
COMMIT;
--判断临时表是否存在，防止没有表的情况下删除表发生错误
SELECT count(1) INTO L_Count
FROM user_tables
WHERE table_name=UPPER('cux_items_temp1');
--增加行列、删除标识列，用于方便定位、剔除不合格数据
--行列对应Excel中的行号，唯一标识||不合格数据标识位设置为1，其他的设置为0
IF(L_Count=0) THEN
EXECUTE IMMEDIATE 'CREATE TABLE cux_items_temp1 AS
SELECT ROWNUM line,0 del_flag, a.* FROM cux_items_temp a';
ELSE
EXECUTE IMMEDIATE 'DROP TABLE cux_items_temp1';
COMMIT;
EXECUTE IMMEDIATE 'CREATE TABLE cux_items_temp1 AS
SELECT ROWNUM line,0 del_flag, a.* FROM cux_items_temp a';
END IF;
COMMIT;
wl_iface_pkg.to_log('Load Items Data End.',2,'*');
wl_iface_pkg.to_log('',2,'*');
RETURN 1;
EXCEPTION
WHEN OTHERS THEN
wl_iface_pkg.to_log('Unexpected error occurred when Load Items Data.',2,'*');
wl_iface_pkg.sql_error_message;
wl_iface_pkg.to_log('',2,'*');
END Load_ITEMS;
--Check Items Data Null Value
FUNCTION CHECK_ITEMS_NULL RETURN NUMBER IS
L_Result_Flag    NUMBER := 0;   --执行结果标识
L_Total_Rows     NUMBER;        --总行数
L_Total_Columns  NUMBER;        --总列数
L_Curr_Col_Name  VARCHAR2 (30); --当前列名称
L_Curr_Col_Value VARCHAR2 (240);--当前列值
L_Loop_Number    NUMBER;        --循环变量
L_Exec_Sql       VARCHAR2 (240);--拼接用于执行动态sql语句变量
L_Msg_Flag       NUMBER;        --输出日志标识
L_Curr_Line      NUMBER;        --当前行号
BEGIN
wl_iface_pkg.to_log('Check Items Data Null Value Start',2,'*');
L_Msg_Flag := 0;
--计算总行数
SELECT COUNT(1) INTO L_Total_Rows
FROM cux_items_temp1;
--计算总列数
SELECT COUNT(1) INTO L_Total_Columns
FROM USER_TAB_Columns A
WHERE A.TABLE_NAME = UPPER('cux_items_temp1');
--
wl_iface_pkg.to_log('',2,'*');
--循环每一列
FOR cc IN (SELECT utcs.Column_NAME
FROM User_Tab_Columns utcs
WHERE utcs.TABLE_NAME = UPPER('cux_items_temp1')) LOOP
L_Curr_Col_Name := cc.Column_name;
--循环每一行
FOR L_Loop_Number IN 2..L_Total_Rows+1 LOOP
--如果为空替换为######
L_Exec_Sql :=  'SELECT DECODE('||L_Curr_Col_Name||',null,''######''),b.line1 FROM (SELECT '
||L_Curr_Col_Name||',a.line line1 FROM cux_items_temp1 a WHERE ROWNUM <'
||L_Loop_Number||' ORDER BY a.line DESC) b WHERE ROWNUM =1';
--读取出当前的行列交集的值和行号
L_Curr_Col_Value := NULL;     
L_Curr_Line      := NULL;
EXECUTE IMMEDIATE L_Exec_Sql
INTO L_Curr_Col_Value,L_Curr_Line;
--
IF(L_Curr_Col_Value='######') THEN
--输出日志，定位错误信息
L_Msg_Flag :=L_Msg_Flag+1;
wl_iface_pkg.to_log('Message '||to_char(L_Msg_Flag)||':Row:'
||to_char(L_Loop_Number-1)||' Column:'
||to_char(L_Curr_Col_Name)||' is null,please check!',2,'*');
wl_iface_pkg.to_log('',2,'*');
--更新删除标准为1
L_Exec_Sql := 'UPDATE cux_items_temp1 SET del_flag=1 WHERE line='||L_Curr_Line;
EXECUTE IMMEDIATE L_Exec_Sql;   
END IF;
END LOOP;
END LOOP;
COMMIT;
IF(L_Msg_Flag >0) THEN
wl_iface_pkg.to_log('Check Null End.There have some messages!',2,'*');
wl_iface_pkg.to_log('',2,'*');
ELSE
L_Result_Flag := 1;
wl_iface_pkg.to_log('Check Null End.There have no message!',2,'*');
wl_iface_pkg.to_log('',2,'*');
END IF;
RETURN L_Result_Flag;
EXCEPTION
WHEN OTHERS THEN
wl_iface_pkg.to_log('Unexpected error occurred when Check Items Data Null Value.',2,'*');
wl_iface_pkg.sql_error_message;
wl_iface_pkg.to_log('',2,'*');
END CHECK_ITEMS_NULL;
-- Check Items data organization
FUNCTION CHECK_ITEMS_ORGANIZATION RETURN NUMBER IS
L_Result_Flag NUMBER := 0;   --执行结果标识
L_Msg_Flag    NUMBER := 0;   --输出日志标识
L_Exec_Sql    VARCHAR2 (240);--拼接用于执行动态sql语句变量
Rec_Org       CUX_ITEMS_TEMP1%ROWTYPE;--当前行记录
--游标存储系统的组织表中不存在的记录
CURSOR Cur_Org IS
SELECT * FROM CUX_ITEMS_TEMP1 CIT
WHERE NOT EXISTS
(SELECT *
FROM ORG_ORGANIZATION_DEFINITIONS OOD
WHERE CIT.ORGANIZATION_ID = OOD.ORGANIZATION_ID
);
BEGIN
wl_iface_pkg.to_log('Check [Organization Id] Start',2,'*');
wl_iface_pkg.to_log('',2,'*');
OPEN cur_org;
--这些记录都是有问题的 都需要处理 所有下面没有判断 直接处理
LOOP
FETCH Cur_Org INTO Rec_Org;
EXIT WHEN Cur_Org%NOTFOUND;
L_Msg_Flag := L_Msg_Flag + 1;
wl_iface_pkg.to_log('Message '||to_char(L_Msg_Flag)
||':Row:'||Rec_Org.line
||': [Organization Id] is not available',2,'*');
wl_iface_pkg.to_log('',2,'*');
--更新删除标准为1
L_Exec_Sql := 'UPDATE cux_items_temp1 SET del_flag=1 WHERE line='||Rec_Org.line;
EXECUTE IMMEDIATE L_Exec_Sql;
END LOOP;
COMMIT;
IF L_Msg_Flag >0 THEN
wl_iface_pkg.to_log('Check [Organization Id] End.There have some messages!',2,'*');
wl_iface_pkg.to_log('',2,'*');
ELSE
L_Result_Flag :=1;
wl_iface_pkg.to_log('Check [Organization Id] End.There have no messages!',2,'*');
wl_iface_pkg.to_log('',2,'*');
END IF;
RETURN L_Result_Flag;
EXCEPTION
WHEN OTHERS THEN
wl_iface_pkg.to_log('Unexpected error occurred when Check [Organization Id].',2,'*');
wl_iface_pkg.sql_error_message;
wl_iface_pkg.to_log('',2,'*');
END CHECK_ITEMS_ORGANIZATION;
-- Check Items data unit
FUNCTION CHECK_ITEMS_UNIT RETURN NUMBER IS
FLAG       NUMBER :=0;    --执行结果标识
L_Msg_Flag NUMBER :=0;    --输出日志标识
L_Exec_Sql VARCHAR2 (240);--拼接用于执行动态sql语句变量
Rec_Uom    CUX_ITEMS_TEMP1%ROWTYPE; --当前行记录
--游标存储系统的单位表中不存在的记录
CURSOR Cur_Uom IS
SELECT *
FROM CUX_ITEMS_TEMP1 PUC
WHERE NOT EXISTS
(SELECT UOM.UOM_CODE
FROM INV.MTL_UNITS_OF_MEASURE_TL UOM
WHERE PUC.PRIMARY_UOM_CODE = UOM.UOM_CODE);
BEGIN
wl_iface_pkg.to_log('Check [Primary UOM Code] Start',2,'*');
wl_iface_pkg.to_log('',2,'*');
OPEN Cur_Uom;
--这些记录都是有问题的 都需要处理 所有下面没有判断 直接处理
LOOP
FETCH Cur_Uom INTO Rec_Uom;
EXIT WHEN Cur_Uom%NOTFOUND;
L_Msg_Flag := L_Msg_Flag + 1;
wl_iface_pkg.to_log('Message '||to_char(L_Msg_Flag)||':Row:'
||Rec_Uom.line||': [Primary UOM Code] is not available',2,'*');
wl_iface_pkg.to_log('',2,'*');
--更新删除标准为1
L_Exec_Sql := 'UPDATE cux_items_temp1 SET del_flag=1 WHERE line='||Rec_Uom.line;
EXECUTE IMMEDIATE L_Exec_Sql;
END LOOP;
COMMIT;
IF L_Msg_Flag >0 THEN
wl_iface_pkg.to_log('Check [Primary UOM Code] End.There have some messages!',2,'*');
wl_iface_pkg.to_log('',2,'*');
ELSE
Flag :=1;
wl_iface_pkg.to_log('Check [Primary UOM Code] End.There have no messages!',2,'*');
wl_iface_pkg.to_log('',2,'*');
END IF;
RETURN Flag;
EXCEPTION
WHEN OTHERS THEN
wl_iface_pkg.to_log('Unexpected error occurred when Check [Primary UOM Code].',2,'*');
wl_iface_pkg.sql_error_message;
wl_iface_pkg.to_log('',2,'*');
END CHECK_ITEMS_UNIT;
-- Operate Items data to the interface table
FUNCTION Operate_ITEMS RETURN NUMBER IS
FLAG    NUMBER; --执行结果标识
V_COUNT NUMBER; --用于保存接口表中数据导入不成功的数据的数量
BEGIN
wl_iface_pkg.to_log('Operate Items data to the interface table Start',2,'*');
wl_iface_pkg.to_log('',2,'*');
--清除表中不合格数据
EXECUTE IMMEDIATE 'DELETE FROM CUX_ITEMS_TEMP1 WHERE del_flag = 1';
COMMIT;
--
FOR C_ITEM IN (SELECT * FROM CUX_ITEMS_TEMP1) LOOP
--初始化变量
MASTER_ORG_CUR_FLAG ：= -1；
MASTER_ORG_SYS_FLAG ：= -1；
CURREN_ORG_SYS_FLAG ：= -1；
L_MST_ORG_ID ：= -1；
L_CUR_ORG_ID ：= -1；
--不用逻辑判断的属性1:5个who
L_IFACE_REC.LAST_UPDATE_DATE  := SYSDATE;
L_IFACE_REC.LAST_UPDATED_BY   := 0;
L_IFACE_REC.CREATION_DATE     := SYSDATE;
L_IFACE_REC.CREATED_BY        := 0;
L_IFACE_REC.LAST_UPDATE_logIN := -1;
--不用逻辑判断的属性2：固定的
L_IFACE_REC.DESCRIPTION      := C_ITEM.DESCRIPTION;
L_IFACE_REC.SEGMENT1         := C_ITEM.SEGMENT1;
L_IFACE_REC.PRIMARY_UOM_CODE := C_ITEM.PRIMARY_UOM_CODE;
L_IFACE_REC.PROCESS_FLAG     := C_ITEM.PROCESS_FLAG;
L_IFACE_REC.SET_PROCESS_ID   := C_ITEM.SET_PROCESS_ID;
L_IFACE_REC.TEMPLATE_ID      := C_ITEM.TEMPLATE_ID;
L_IFACE_REC.TRANSACTION_TYPE := C_ITEM.TRANSACTION_TYPE;
--计算物料的组织编号是否为主组织
SELECT COUNT(1)
INTO MASTER_ORG_CUR_FLAG
FROM MTL_PARAMETERS A
WHERE A.ORGANIZATION_ID = A.MASTER_ORGANIZATION_ID
AND A.ORGANIZATION_ID = C_ITEM.ORGANIZATION_ID;
--该物料org_id为主组织
IF (MASTER_ORG_CUR_FLAG = 1) THEN
--计算物料item_no在主组织中是否存在
SELECT COUNT(1)
INTO MASTER_ORG_SYS_FLAG
FROM MTL_SYSTEM_ITEMS_B A
WHERE A.SEGMENT1 = C_ITEM.SEGMENT1
AND A.ORGANIZATION_ID = L_MST_ORG_ID;
L_MST_ORG_ID := C_ITEM.ORGANIZATION_ID;
--该物料item_no在主组织不存在:只CREATE到主组织
IF (MASTER_ORG_SYS_FLAG = 0) THEN
L_IFACE_REC.TRANSACTION_TYPE := 'CREATE';
L_IFACE_REC.ORGANIZATION_ID  := L_MST_ORG_ID;
--
INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE
VALUES L_IFACE_REC;
--该物料item_no在主组织存在：只UPDATE主组织中原item
ELSE
L_IFACE_REC.TRANSACTION_TYPE := 'UPDATE';
L_IFACE_REC.ORGANIZATION_ID  := L_MST_ORG_ID;
--
INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE
VALUES L_IFACE_REC;
END IF;
--该物料org_id为次组织
ELSE
L_CUR_ORG_ID ：= C_ITEM.ORGANIZATION_ID;
--找出该子组织所在的主组织
SELECT A.MASTER_ORGANIZATION_ID
INTO L_MST_ORG_ID
FROM MTL_PARAMETERS A
WHERE A.ORGANIZATION_ID = L_CUR_ORG_ID;
--计算物料item_no在主组织中是否存在
SELECT COUNT(1)
INTO MASTER_ORG_SYS_FLAG
FROM MTL_SYSTEM_ITEMS_B A
WHERE A.SEGMENT1 = C_ITEM.SEGMENT1
AND A.ORGANIZATION_ID = L_MST_ORG_ID;
--该物料item_no在主组织中不存在 ok
IF (MASTER_ORG_SYS_FLAG = 0) THEN
--该物料item_no子组织中肯定不存在;因为子组织的item存在的前提是主组织必须有了该item
L_IFACE_REC.TRANSACTION_TYPE := 'CREATE';
--插入主组织
L_IFACE_REC.ORGANIZATION_ID := L_MST_ORG_ID;
--
INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE
VALUES L_IFACE_REC;
--插入子组织                  
L_IFACE_REC.ORGANIZATION_ID := L_CUR_ORG_ID;
--
INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE
VALUES L_IFACE_REC;
ELSE
--该物料item_no在主组织中存在
--计算物料item_no子组织中是否存在
SELECT COUNT(1)
INTO CURREN_ORG_SYS_FLAG
FROM MTL_SYSTEM_ITEMS_B A
WHERE A.SEGMENT1 = C_ITEM.SEGMENT1
AND A.ORGANIZATION_ID = L_CUR_ORG_ID;
--该物料item_no子组织中不存在 ok
IF (CURREN_ORG_SYS_FLAG = 0) THEN
L_IFACE_REC.TRANSACTION_TYPE := 'CREATE';
L_IFACE_REC.ORGANIZATION_ID  := L_CUR_ORG_ID;
--
INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE
VALUES L_IFACE_REC;
--该物料item_no子组织中存在,更新主组织中item并更新子组织item
ELSE
--更新主组织
L_IFACE_REC.TRANSACTION_TYPE := 'UPDATE';
L_IFACE_REC.ORGANIZATION_ID  := L_MST_ORG_ID;
--
INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE
VALUES L_IFACE_REC;
--更新子组织
L_IFACE_REC.TRANSACTION_TYPE := 'UPDATE';
L_IFACE_REC.ORGANIZATION_ID  := L_CUR_ORG_ID;
--
INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE
VALUES L_IFACE_REC;
END IF;
END IF;
END IF;
END LOOP;
COMMIT;
--查询接口表中数据导入不成功的数据
SELECT COUNT(*)
INTO V_COUNT
FROM MTL_SYSTEM_ITEMS_INTERFACE a
WHERE a.process_flag<>7
AND a.segment1 IN (
--保证数据和本次操作有关
SELECT b.segment1 FROM cux_items_temp b);
IF (V_COUNT = 0) THEN
FLAG := 0;
wl_iface_pkg.to_log('Operate Items data to the interface table End.There have some messages!',2,'*');
wl_iface_pkg.to_log('',2,'*');
ELSE
FLAG := 1;
wl_iface_pkg.to_log('Operate Items data to the interface table End.There have no messages!',2,'*');
wl_iface_pkg.to_log('',2,'*');
END IF;
RETURN FLAG;
EXCEPTION
WHEN OTHERS THEN
wl_iface_pkg.to_log('Unexpected error when Operate Items.',2,'*');
wl_iface_pkg.sql_error_message;
wl_iface_pkg.to_log('',2,'*');
END Operate_ITEMS;
--
PROCEDURE MAIN(ERRBUF OUT VARCHAR2, RETCODE OUT VARCHAR2) IS
T NUMBER;
BEGIN
wl_iface_pkg.to_log('',2,'*');
wl_iface_pkg.to_log('+----------------------Start '
|| to_char(SYSDATE,'YYYY-MM-DD hh24:mm:ss')
|| '----------------------+',1,'');
wl_iface_pkg.to_log('',2,'*');
T := Load_ITEMS;
T := T + Check_Items_Null;
T := T + Check_Items_organization;
T := T + Check_Items_unit;
T := T + Operate_ITEMS;
--如果有步骤有问题 则结果就不是5 都正确才会是5
IF T <> 5 THEN
wl_iface_pkg.to_log('Import items some messages,please check the Excel data.',2,'*');
wl_iface_pkg.to_log('',2,'*');
ELSE
wl_iface_pkg.to_log('Import items successful.',2,'*');
wl_iface_pkg.to_log('',2,'*');
END IF;
wl_iface_pkg.to_log('+----------------------End '
||to_char(SYSDATE,'YYYY-MM-DD hh24:mm:ss')
||'----------------------+',1,'');
wl_iface_pkg.to_log('',2,'*');
END MAIN;
BEGIN
NULL;
END PKGIMPORTITEMS;