存储过程三：
一.procedures
create or replace procedure PR_BOM is /* * 功能:从中间表获取BOM及其明细信息 * 作者：KAIQI.ZHAO * 创建日期 2019-06-06 * 最后修改时间 2019-06-06 14:35:29 */ v_con number; V_MSG varchar2(1000); --定义异常 SIGING_EXCEPTION EXCEPTION; --本过程异常 OTHERS_EXCEPTION EXCEPTION; --包特殊异常 PRAGMA EXCEPTION_INIT(OTHERS_EXCEPTION, -20999); begin SELECT count(1) into v_con FROM JSBOM WHERE IS_READ = '0'; if v_con > 0 then for cur_temp in (SELECT ID, MATERIAL_CODE, BOM_CODE FROM JSBOM WHERE IS_READ = '0') loop begin --循环抓取BOM信息 PA_BOM.sp_bom(cur_temp.ID, cur_temp.material_code, cur_temp.bom_code, V_MSG); exception when OTHERS_EXCEPTION then ROLLBACK; V_MSG := '执行异常:' || SQLCODE || ':' || SQLERRM || ':' || SUBSTR(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, 1, 244); --记录异常日志、 LOG_PKG.SAVE_ERROR_LOG(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, V_MSG); WHEN OTHERS THEN ROLLBACK; V_MSG := '执行异常:' || SQLCODE || ':' || SQLERRM || ':' || SUBSTR(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, 1, 244); --记录异常日志、 LOG_PKG.SAVE_ERROR_LOG(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, V_MSG); end; end loop; end if; ----------------------------抓取中间表明细数据转存WMS----------------------------------------------------------- SELECT count(1) into v_con FROM JSBOM_ITEM WHERE IS_READ = '0'; if v_con > 0 then for cur_temp in (SELECT BOM_ID, ITEM_ID, ITEM_CODE, MATERIAL_CODE FROM JSBOM_ITEM WHERE IS_READ = '0') loop begin --循环抓取BOM信息 PA_BOM.sp_bom_detail(cur_temp.bom_id, cur_temp.item_id, cur_temp.item_code, cur_temp.material_code, V_MSG); exception when OTHERS_EXCEPTION then ROLLBACK; V_MSG := '执行异常:' || SQLCODE || ':' || SQLERRM || ':' || SUBSTR(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, 1, 244); --记录异常日志、 LOG_PKG.SAVE_ERROR_LOG(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, V_MSG); WHEN OTHERS THEN ROLLBACK; V_MSG := '执行异常:' || SQLCODE || ':' || SQLERRM || ':' || SUBSTR(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, 1, 244); --记录异常日志、 LOG_PKG.SAVE_ERROR_LOG(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, V_MSG); end; end loop; end if; --记录监听日志 PA_BOM.sp_bom_listener_log(SYSDATE, ''); EXCEPTION WHEN SIGING_EXCEPTION THEN ROLLBACK; WHEN OTHERS THEN ROLLBACK; V_MSG := '执行异常:' || SQLCODE || ':' || SQLERRM || ':' || SUBSTR(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, 1, 244); --记录异常日志 LOG_PKG.SAVE_ERROR_LOG(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, V_MSG); --记录监听日志 PA_BOM.sp_bom_listener_log(SYSDATE, V_MSG); end PR_BOM;
二.packages
create or replace package PA_BOM is /* * 功能:BOM表数据接口 * 作者：KAIQI.ZHAO * 创建日期 2019-06-06 * 最后修改时间 2019-06-06 */ --根据传入的id,来插入更新BOM基础资料 PROCEDURE sp_bom(v_bom_id IN varchar2, material_code IN varchar2, bom_code IN varchar2, V_MSG OUT VARCHAR2); --根据job定时器来记录调用的监听记录 PROCEDURE sp_bom_listener_log(v_Date in date, V_MSG IN VARCHAR2); --根据传入的code,来插入更新BOM_DETAIL基础资料 PROCEDURE sp_bom_detail(v_bom_id IN varchar2, v_item_id IN varchar2, v_item_code IN varchar2, material_code IN varchar2, V_MSG OUT VARCHAR2); end PA_BOM;
三.package Bodies
create or replace package body PA_BOM is /* * 功能:获取中间表数据,插入更新BOM信息 * 作者：KAIQI.ZHAO * 创建日期 2019-06-06 * 最后修改时间 2019-06-06 */ procedure sp_bom(v_bom_id IN varchar2, material_code IN varchar2, bom_code IN varchar2, V_MSG OUT VARCHAR2) --传入code,传出异常信息,执行状态,处理时间 is v_con number; v_item_id number; v_con_code number; --v_con_sum number; PRAGMA AUTONOMOUS_TRANSACTION; --开启自定义事务 SIGING_EXCEPTION EXCEPTION; --自定义异常 begin SELECT ID into v_item_id FROM WMS_ITEM where CODE = material_code; --SELECT count(1) into v_con_sum FROM WMS_BOM; if v_item_id is null then V_MSG := '物料信息不存在！'; raise SIGING_EXCEPTION; else SELECT count(1) into v_con FROM WMS_BOM where ID = v_bom_id; if v_con = 0 then SELECT count(1) into v_con_code FROM WMS_BOM where CODE = bom_code; if v_con_code > 0 then V_MSG := 'BOM编码已存在！'; raise SIGING_EXCEPTION; else INSERT INTO WMS_BOM (ID, BE_DELETE, CREATED_TIME, UPDATE_TIME, CODE, COMPANY_ID, STATUS, BOM_NAME, ITEM_ID, QTY, VERSION, PRICE) SELECT TO_NUMBER(v_bom_id), 'N', ERP_IMPORT_TIME, LAST_CHG_TIME, BOM_CODE, 1, 'E', BOM_NAME, v_item_id, QTY, 1, 0f FROM JSBOM WHERE ID = v_bom_id; end if; else --否则，更新WMSBOM表中此ID的数据 SELECT count(1) into v_con_code FROM WMS_BOM where CODE = bom_code AND ID <> TO_NUMBER(v_bom_id); if v_con_code > 0 then V_MSG := 'BOM编码已存在于其他BOM_ID的数据中，请检查数据ID和编码是否于历史数据重复！'; raise SIGING_EXCEPTION; else UPDATE Wms_Bom SET (CREATED_TIME, UPDATE_TIME, BOM_NAME, Qty, Item_Id) = (SELECT ERP_IMPORT_TIME, LAST_CHG_TIME, BOM_NAME, QTY, v_item_id FROM JSBOM WHERE CODE = bom_code); end if; end if; end if; --更新中间表中的是否已读字段，将未读改为已读 UPDATE JSBOM SET IS_READ = '1', WMS_RECEIVE_TIME = TO_CHAR(SYSDATE()) WHERE ID = v_bom_id; COMMIT; EXCEPTION WHEN SIGING_EXCEPTION THEN ROLLBACK; RAISE_APPLICATION_ERROR(-20001, V_MSG); COMMIT; WHEN OTHERS THEN ROLLBACK; RAISE_APPLICATION_ERROR(-20999, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE); COMMIT; end sp_bom; /* * 功能:获取中间表数据,插入更新BOM_DETAIL信息 * 作者：KAIQI.ZHAO * 创建日期 2019-06-06 * 最后修改时间 2019-06-06 */ procedure sp_bom_detail(v_bom_id IN varchar2, v_item_id IN varchar2, v_item_code IN varchar2, material_code IN varchar2, V_MSG OUT VARCHAR2) --传入code,传出异常信息,执行状态,处理时间 is --定义变量 v_con_bom number; v_con_bom_detail number; v_con_bom_detail_code number; v_li_item_id number; v_be_inventory VARCHAR2(1 CHAR); v_item_bom_id VARCHAR2(1 CHAR); v_con_parent_bom number; v_con_code number; PRAGMA AUTONOMOUS_TRANSACTION; --开启自定义事务 SIGING_EXCEPTION EXCEPTION; --自定义异常 begin --查询物料是否存在 SELECT ID into v_li_item_id FROM WMS_ITEM where CODE = material_code; if v_li_item_id is null then V_MSG := '物料信息不存在！'; raise SIGING_EXCEPTION; else --查询BOM是否存在 SELECT count(1) into v_con_bom FROM WMS_BOM where ID = TO_NUMBER(v_bom_id); if v_con_bom < 0 then V_MSG := 'BOM信息不存在！'; raise SIGING_EXCEPTION; else --根据ID查询detail是否存在 SELECT count(1) into v_con_bom_detail FROM Wms_Bom_Detail where ID = TO_NUMBER(v_item_id); --查是否虚拟件 SELECT IS_INVENTORY into v_be_inventory FROM JSBOM_ITEM where ITEM_ID = v_item_id; --查父BOMID SELECT ITEM_BOM_ID into v_item_bom_id FROM JSBOM_ITEM where ITEM_ID = v_item_id; if v_be_inventory <> '1' then v_be_inventory := 'Y'; else v_be_inventory := 'N'; end if; --判断父BOM是否存在 if v_item_bom_id is not null then SELECT count(1) into v_con_parent_bom FROM Wms_Bom where ID = TO_NUMBER(v_item_bom_id); if v_con_parent_bom = 0 then V_MSG := '父BOM信息不存在！'; raise SIGING_EXCEPTION; end if; end if; if v_con_bom_detail = 0 then --如果不存在，那么根据DETAIL_CODE查明细编码是否已经存在了 SELECT count(1) into v_con_bom_detail_code FROM Wms_Bom_Detail where NUMBER_CODE = v_item_code; if v_con_bom_detail_code > 0 then V_MSG := '分录编码已存在于其他BOM_DETAIL_ID的数据中，请检查数据ID和编码是否于历史数据重复！'; raise SIGING_EXCEPTION; else INSERT INTO WMS_BOM_DETAIL (ID, BE_DELETE, CREATED_TIME, UPDATE_TIME, VERSION, BOM_ID, ITEM_ID, QTY, BEINVENTORY, NUMBER_CODE, NUMBER_NAME, PARENT_BOM_ID) SELECT TO_NUMBER(v_item_id), 'N', ERP_IMPORT_TIME, SYSDATE(), 1, TO_NUMBER(v_bom_id), v_li_item_id, QTY, v_be_inventory, v_item_code, ITEM_NAME, ITEM_BOM_ID FROM JSBOM_ITEM WHERE ITEM_ID = v_item_id; end if; else --更新 SELECT count(1) into v_con_code FROM WMS_BOM_DETAIL where NUMBER_CODE = v_item_code AND ID <> TO_NUMBER(v_item_id); if v_con_code > 0 then V_MSG := '分录编码已存在于其他BOM_DETAIL_ID的数据中，请检查数据ID和编码是否于历史数据重复！'; raise SIGING_EXCEPTION; else UPDATE Wms_Bom_Detail SET (CREATED_TIME, UPDATE_TIME, BOM_ID, ITEM_ID, BEINVENTORY, QTY, NUMBER_CODE, NUMBER_NAME, PARENT_BOM_ID) = (SELECT ERP_IMPORT_TIME, SYSDATE(), TO_NUMBER(bom_id), v_li_item_id, v_be_inventory, QTY, v_item_code, ITEM_NAME, ITEM_BOM_ID FROM JSBOM_ITEM WHERE ITEM_ID = v_item_id); end if; end if; end if; end if; --更新中间表中的是否已读字段，将未读改为已读 UPDATE JSBOM_ITEM SET IS_READ = '1', WMS_RECEIVE_TIME = TO_CHAR(SYSDATE()) WHERE ITEM_ID = v_item_id; COMMIT; EXCEPTION WHEN SIGING_EXCEPTION THEN ROLLBACK; RAISE_APPLICATION_ERROR(-20001, V_MSG); COMMIT; WHEN OTHERS THEN ROLLBACK; RAISE_APPLICATION_ERROR(-20999, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE); COMMIT; end sp_bom_detail; /* * 功能:job运行监听 * 作者：KAIQI.ZHAO * 创建日期 2019-06-06 * 最后修改时间 2019-06-06 14:35:29 */ procedure sp_bom_listener_log(v_Date in date, V_MSG IN VARCHAR2) is v_con number; PRAGMA AUTONOMOUS_TRANSACTION; begin select count(1) into v_con from TMS_INTERFACE_LISTENER where code = 'sp_bom_and_detail'; if v_con = 0 then INSERT INTO TMS_INTERFACE_LISTENER (ID, CODE, TASK_NAME, LAST_EXECUTE_DATE, INTERVAL_VALUE, STATUS, EXCEPTION_LOG) values (seq_interfacelistener.nextval, 'sp_bom_and_detail', 'BOM基础资料及其明细抓取', v_Date, 3, 'ENABLED', V_MSG); COMMIT; else update TMS_INTERFACE_LISTENER set LAST_EXECUTE_DATE = v_date, EXCEPTION_LOG = V_MSG WHERE code = 'sp_bom_and_detail'; COMMIT; end if; EXCEPTION WHEN OTHERS THEN ROLLBACK; end sp_bom_listener_log; end PA_BOM;