CREATE OR REPLACE TRIGGER check_bom_exists_trig
BEFORE INSERT OR UPDATE ON XXMEL_SPARES_BOM_LOOKUP
FOR EACH ROW
DECLARE
-- bom must exist in msib before it can be inserted
new_target VARCHAR2(50);
new_source VARCHAR2(50);
v_target_exists VARCHAR2(1) := 'N';
v_source_exists VARCHAR2(1) := 'N';
v_error_text VARCHAR2(1000);
invalid_item EXCEPTION;
CURSOR check_target_cur IS
SELECT msib.segment1
FROM mtl_system_items_b msib
WHERE msib.segment1 = :NEW.target_bom
AND msib.organization_id IN (168,169);
CURSOR check_source_cur IS
SELECT msib.segment1
FROM mtl_system_items_b msib
WHERE msib.segment1 = :NEW.source_bom
AND msib.organization_id IN (26,148);
BEGIN
FOR check_target_r IN check_target_cur
LOOP
v_target_exists := 'Y';
END LOOP;
FOR check_source_r IN check_source_cur
LOOP
v_source_exists := 'Y';
END LOOP;
IF v_target_exists = 'N'
OR v_source_exists = 'N'
THEN
RAISE invalid_item;
END IF;
EXCEPTION
WHEN invalid_item
THEN
IF v_target_exists = 'N' AND v_source_exists = 'Y' THEN
v_error_text := 'The target BOM does not exist in Oracle';
ELSIF v_source_exists = 'N' AND v_target_exists = 'Y' THEN
v_error_text := 'The source BOM does not exist in Oracle';
ELSIF v_target_exists = 'N' AND v_source_exists = 'N' THEN
v_error_text := 'Neither the source BOM nor the target BOM exist
is Oracle';
END IF;
RAISE_APPLICATION_ERROR(-20012, v_error_text);
END;
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
