TRIGGER是这样的：
CREATE OR REPLACE TRIGGER OPLK_TR_PLA_IU_PRICE
AFTER INSERT OR UPDATE OF UNIT_PRICE
ON PO.PO_LINES_ALL
FOR EACH ROW
DECLARE
L_ITEM VARCHAR2(40);
L_RATE NUMBER;
B_RATE NUMBER;
L_PRICE NUMBER;
L_COST NUMBER;
BEGIN
IF :NEW.UNIT_PRICE IS NOT NULL THEN
SELECT
DECODE(PHA.CURRENCY_CODE, 'CNY', :NEW.UNIT_PRICE, :NEW.UNIT_PRICE*PHA.RATE) as UNIT_PRICE INTO L_PRICE
FROM PO_HEADERS_ALL PHA
WHERE PHA.PO_HEADER_ID=:NEW.PO_HEADER_ID;
SELECT CONVERSION_RATE INTO L_RATE
FROMMTL_UOM_CONVERSIONS WHERE UNIT_OF_MEASURE=:NEW.UNIT_MEAS_LOOKUP_CODE;
SELECT MUC.CONVERSION_RATE INTO B_RATE
FROMMTL_UOM_CONVERSIONS MUC, MTL_SYSTEM_ITEMS_B B
WHERE MUC.UNIT_OF_MEASURE=B.PRIMARY_UNIT_OF_MEASURE
AND B.INVENTORY_ITEM_ID(+)=:NEW.ITEM_ID
AND B.ORGANIZATION_ID=26;
SELECT ITEM_COST INTO L_COST FROM CST_ITEM_COSTS WHERE INVENTORY_ITEM_ID(+) =:NEW.ITEM_ID AND ORGANIZATION_ID=26
AND COST_TYPE_ID=1;
SELECT SEGMENT1 INTO L_ITEM
FROM MTL_SYSTEM_ITEMS_B
WHERE INVENTORY_ITEM_ID(+)=:NEW.ITEM_ID
AND ORGANIZATION_ID=26;
IF L_PRICE/L_RATE > L_COST/B_RATE*1.05 and :NEW.ATTRIBUTE4 IS NULL THEN
RAISE_APPLICATION_ERROR(-20001,'The unit price of the item '||L_ITEM||' exceeds the item cost over 5%, please indicate the reason in PO Lines Comment. ');
END IF;
END IF;
exception
when OTHERS then
NULL;
END;
/
|
