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;
分享到:
相关推荐
ORACLE EBS 物料和类别对应SQL
oracle ebs 11i物料反查 一个函数加SQL语句,欢迎再次完善程序。
Oracle EBS 操作手册
此针对于oracle ebs的值集进行详细的讲解
ebs的bom接口导入程序,从原来bom复制出来并加工数据导入生成一个新的bom,包括资源、用量的复制功能
本资源主要讲解Oracle EBS的 INV模块定义物料值集的维护
Oracle EBS物料清单管理系统简介.pptx
ORACLE ebs 各个模块的表之间的关联关系,方便大家熟悉ebs系统的表结构
OracleEBS中文数据字典
Oracle EBS中费用性采购的介绍
Oracle EBS中文数据字典.pdf
Integrating_EBS_with_Oracle_Internet_Directory_and_Oracle_Single_Sign-On Oracle EBS 单点登录 方案
win7 ORACLE ebs 需要的文件和具体设置步骤 详细
这是一份完整的Oracle ebs 项目制造模块培训文档,可以了解Oracle ebs 项目制造模块(PJM)的所有功能及流程。
ORACLE EBS R12 安装步骤详解,讲述ORACLE EBS R12的详细安装步骤,感觉不错,和大家一块分享
Oracle EBS 各模块详解 01采购培训 02库存培训 03销售培训 04应付培训 05总帐培训 06应收培训
ORACLE EBS查看库存现有量SQL
Oracle EBS开发文档(form, report), 写得很不错
主要内容是oracle EBS财务模块,就学习ERP挺好的材料,分为五个部分 Oracle EBS财务模块(一)基本功能 Oracle EBS财务模块(二)基本组成模块 Oracle EBS财务模块(三)总账功能 Oracle EBS财务模块(四)账套 ...
ORACLEEBS财务全模块操作手册中文版收集.pdf