一.存储过程
CREATE OR REPLACE PACKAGE BODY PA_MATERIALS IS
/*
* 功能:获取中间表数据,插入更新物料信息
* 作者:KAIQI.ZHAO
* 创建日期 2019-06-05
* 最后修改时间 2019-06-05
*/
procedure sp_material(v_material_id IN varchar2, V_MSG OUT VARCHAR2)
--传入id,传出异常信息,执行状态,处理时间
is
v_con number;
PRAGMA AUTONOMOUS_TRANSACTION; --开启自定义事务
SIGING_EXCEPTION EXCEPTION; --自定义异常
begin
--根据id判断WMS是否有该物料,如果有更新,没有插入
SELECT count(1) into v_con FROM WMS_ITEM where id = v_material_id;
if v_con = 0 then
INSERT INTO WMS_ITEM
(ID,
CREATED_TIME,
UPDATE_TIME,
BASE_UNIT,
CODE,
NAME,
MATERIAL_TYPE_CODE,
MATERIALSPECS,
MATERIALTYPE)
SELECT MATERIAL_ID,
ERP_IMPORT_TIME,
LAST_CHG_TIME,
UNIT_NAME,
MATERIAL_CODE,
MATERIAL_NAME,
MATERIAL_TYPE_CODE,
MATERIAL_SPECS,
MATERIAL_TYPE
FROM MATERIALS material
WHERE MATERIAL_ID = v_material_id;
else
--否则,更新WMS物料表中此ID的数据
UPDATE WMS_ITEM
SET (CREATED_TIME,
UPDATE_TIME,
BASE_UNIT,
CODE,
NAME,
MATERIAL_TYPE_CODE,
MATERIALSPECS,
MATERIALTYPE) =
(SELECT ERP_IMPORT_TIME,
LAST_CHG_TIME,
UNIT_NAME,
MATERIAL_CODE,
MATERIAL_NAME,
MATERIAL_TYPE_CODE,
MATERIAL_SPECS,
MATERIAL_TYPE
FROM MATERIALS
WHERE MATERIAL_ID = v_material_id)
WHERE ID = TO_NUMBER(v_material_id);
end if;
--更新中间表中的是否已读字段,将未读改为已读
UPDATE MATERIALS
SET IS_READ = '1', WMS_RECEIVE_TIME = TO_CHAR(SYSDATE())
WHERE MATERIAL_ID = v_material_id;
COMMIT;
EXCEPTION
WHEN SIGING_EXCEPTION THEN
ROLLBACK;
--RAISE_APPLICATION_ERROR(-20001, V_MSG);
--UPDATE WMS_OTM_CARRIER
--SET READFLAG = 'Y', READDATE = SYSDATE, ERRORINFO = V_MSG
--WHERE CARRIER_ID = v_code;
--COMMIT;
WHEN OTHERS THEN
ROLLBACK;
--RAISE_APPLICATION_ERROR(-20999, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
--UPDATE WMS_OTM_CARRIER
--SET READFLAG = 'Y', READDATE = SYSDATE, ERRORINFO = V_MSG
--WHERE CARRIER_ID = v_code;
--COMMIT;
end sp_material;
END PA_MATERIALS;
二.包
CREATE OR REPLACE PACKAGE PA_MATERIALS IS
/*
* 功能:物料表数据接口
* 作者:KAIQI.ZHAO
* 创建日期 2019-06-05
* 最后修改时间 2019-06-05
*/
--根据传入的id,来插入更新物料基础资料
PROCEDURE sp_material(v_material_id IN varchar2, V_MSG OUT VARCHAR2);
END PA_MATERIALS;
三.包的实体
CREATE OR REPLACE PACKAGE BODY PA_MATERIALS IS
/*
* 功能:获取中间表数据,插入更新物料信息
* 作者:KAIQI.ZHAO
* 创建日期 2019-06-05
* 最后修改时间 2019-06-05
*/
procedure sp_material(v_material_id IN varchar2, V_MSG OUT VARCHAR2)
--传入id,传出异常信息,执行状态,处理时间
is
v_con number;
PRAGMA AUTONOMOUS_TRANSACTION; --开启自定义事务
SIGING_EXCEPTION EXCEPTION; --自定义异常
begin
--根据id判断WMS是否有该物料,如果有更新,没有插入
SELECT count(1) into v_con FROM WMS_ITEM where id = v_material_id;
if v_con = 0 then
INSERT INTO WMS_ITEM
(ID,
CREATED_TIME,
UPDATE_TIME,
BASE_UNIT,
CODE,
NAME,
MATERIAL_TYPE_CODE,
MATERIALSPECS,
MATERIALTYPE)
SELECT MATERIAL_ID,
ERP_IMPORT_TIME,
LAST_CHG_TIME,
UNIT_NAME,
MATERIAL_CODE,
MATERIAL_NAME,
MATERIAL_TYPE_CODE,
MATERIAL_SPECS,
MATERIAL_TYPE
FROM MATERIALS material
WHERE MATERIAL_ID = v_material_id;
else
--否则,更新WMS物料表中此ID的数据
UPDATE WMS_ITEM
SET (CREATED_TIME,
UPDATE_TIME,
BASE_UNIT,
CODE,
NAME,
MATERIAL_TYPE_CODE,
MATERIALSPECS,
MATERIALTYPE) =
(SELECT ERP_IMPORT_TIME,
LAST_CHG_TIME,
UNIT_NAME,
MATERIAL_CODE,
MATERIAL_NAME,
MATERIAL_TYPE_CODE,
MATERIAL_SPECS,
MATERIAL_TYPE
FROM MATERIALS
WHERE MATERIAL_ID = v_material_id)
WHERE ID = TO_NUMBER(v_material_id);
end if;
--更新中间表中的是否已读字段,将未读改为已读
UPDATE MATERIALS
SET IS_READ = '1', WMS_RECEIVE_TIME = TO_CHAR(SYSDATE())
WHERE MATERIAL_ID = v_material_id;
COMMIT;
EXCEPTION
WHEN SIGING_EXCEPTION THEN
ROLLBACK;
--RAISE_APPLICATION_ERROR(-20001, V_MSG);
--UPDATE WMS_OTM_CARRIER
--SET READFLAG = 'Y', READDATE = SYSDATE, ERRORINFO = V_MSG
--WHERE CARRIER_ID = v_code;
--COMMIT;
WHEN OTHERS THEN
ROLLBACK;
--RAISE_APPLICATION_ERROR(-20999, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
--UPDATE WMS_OTM_CARRIER
--SET READFLAG = 'Y', READDATE = SYSDATE, ERRORINFO = V_MSG
--WHERE CARRIER_ID = v_code;
--COMMIT;
end sp_material;
END PA_MATERIALS;