WIP模块：工单状态完成不能更改回来

原因分析：

    相关业务人员违规操作，将已完成的工单更改状态后又重新发料，为控制此种情况的发生，利用触发器来控制。

解决方案与操作步骤

CREATE OR REPLACE TRIGGER CUX_DISCRETE_JOBS_UPD
BEFORE UPDATE OF STATUS_TYPE ON WIP.WIP_DISCRETE_JOBS
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW

DECLARE
L_COUNT NUMBER;
BEGIN

-- 状态从 "发放" 改为 "未发放"
-- if (:OLD.status_type <> 3 or :NEW.status_type <> 1) then
-- return;
-- end if;

-- 06-12-28
-- 1-未发放 7-已取消
IF (:NEW.STATUS_TYPE = 1 OR :NEW.STATUS_TYPE = 7) THEN

SELECT COUNT(1)
INTO L_COUNT
FROM DUAL
WHERE EXISTS
(SELECT 'x'
FROM MTL_MATERIAL_TRANSACTIONS
WHERE TRANSACTION_SOURCE_TYPE_ID = 5
AND ((TRANSACTION_TYPE_ID = 35 AND TRANSACTION_ACTION_ID = 1) OR
(TRANSACTION_TYPE_ID = 43 AND TRANSACTION_ACTION_ID = 27))
AND TRANSACTION_SOURCE_ID = :NEW.WIP_ENTITY_ID
GROUP BY INVENTORY_ITEM_ID
HAVING SUM(TRANSACTION_QUANTITY) < 0);
IF L_COUNT > 0 THEN
RAISE_APPLICATION_ERROR(-20000,
'任务已经发料,状态不能更改, 请联系相关人员,谢谢');
END IF;

SELECT COUNT(1)
INTO L_COUNT
FROM DUAL
WHERE EXISTS
(SELECT 'x'
FROM MTL_MATERIAL_TRANSACTIONS
WHERE TRANSACTION_SOURCE_TYPE_ID = 5
AND ((TRANSACTION_TYPE_ID = 35 AND TRANSACTION_ACTION_ID = 1) OR
(TRANSACTION_TYPE_ID = 43 AND TRANSACTION_ACTION_ID = 27))
AND TRANSACTION_SOURCE_ID = :NEW.WIP_ENTITY_ID
AND COSTED_FLAG IN ('N', 'E'));
IF L_COUNT > 0 THEN
RAISE_APPLICATION_ERROR(-20000,
'任务已经做了发料和发料退回,但未计算成本,等计算成本后再修改任务的状态. 请联系相关人员,谢谢');
END IF;

END IF;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/41594/viewspace-410080/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/41594/viewspace-410080/