Java中间表接口_包含头和明细的接口存储过程（从中间表读取数据到系统）

create or replace package body PA_BOM is

/*

*功能:获取中间表数据,插入更新BOM信息*作者：KAIQI.ZHAO* 创建日期 2019-06-06

* 最后修改时间 2019-06-06

*/

proceduresp_bom(v_bom_id IN varchar2,

material_code IN varchar2,

bom_code IN varchar2,

V_MSG OUT VARCHAR2)--传入code,传出异常信息,执行状态,处理时间isv_con number;

v_item_id number;

v_con_code number;--v_con_sum number;

PRAGMA AUTONOMOUS_TRANSACTION;--开启自定义事务

SIGING_EXCEPTION EXCEPTION;--自定义异常beginSELECT 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 thenV_MSG := '物料信息不存在！';raiseSIGING_EXCEPTION;elseSELECT count(1) into v_con FROM WMS_BOM where ID =v_bom_id;if v_con = 0 thenSELECT count(1) into v_con_code FROM WMS_BOM where CODE =bom_code;if v_con_code > 0 thenV_MSG := 'BOM编码已存在！';raiseSIGING_EXCEPTION;elseINSERT 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 thenV_MSG := 'BOM编码已存在于其他BOM_ID的数据中，请检查数据ID和编码是否于历史数据重复！';raiseSIGING_EXCEPTION;elseUPDATE 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;endsp_bom;/*

*功能:获取中间表数据,插入更新BOM_DETAIL信息*作者：KAIQI.ZHAO* 创建日期 2019-06-06

* 最后修改时间 2019-06-06

*/

proceduresp_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(1CHAR);

v_item_bom_id VARCHAR2(1CHAR);

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 thenV_MSG := '物料信息不存在！';raiseSIGING_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 thenV_MSG := 'BOM信息不存在！';raiseSIGING_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' thenv_be_inventory := 'Y';elsev_be_inventory := 'N';end if;--判断父BOM是否存在if v_item_bom_id is not null thenSELECT count(1)

into v_con_parent_bom

FROM Wms_Bom

where ID=TO_NUMBER(v_item_bom_id);if v_con_parent_bom = 0 thenV_MSG := '父BOM信息不存在！';raiseSIGING_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 thenV_MSG := '分录编码已存在于其他BOM_DETAIL_ID的数据中，请检查数据ID和编码是否于历史数据重复！';raiseSIGING_EXCEPTION;elseINSERT 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 thenV_MSG := '分录编码已存在于其他BOM_DETAIL_ID的数据中，请检查数据ID和编码是否于历史数据重复！';raiseSIGING_EXCEPTION;elseUPDATE 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;endsp_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) isv_con number;

PRAGMA AUTONOMOUS_TRANSACTION;beginselect count(1)

into v_con

from TMS_INTERFACE_LISTENER

where code= 'sp_bom_and_detail';if v_con = 0 thenINSERT 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;elseupdate TMS_INTERFACE_LISTENERset LAST_EXECUTE_DATE = v_date, EXCEPTION_LOG =V_MSG

WHERE code= 'sp_bom_and_detail';

COMMIT;end if;

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;endsp_bom_listener_log;end PA_BOM;