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

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;