Oracle Database Discussions

Hi All,

We are unable to create Trigger from custom schema and getting the above error.

Please see the below code attached.

CREATE OR REPLACE TRIGGER bolinf.xxeq_fa_mass_additions_bri
BEFORE INSERT
ON fa.FA_MASS_ADDITIONS
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
v_org_id NUMBER;
v_resp_id NUMBER;
v_attr4 HR_ALL_ORGANIZATION_UNITS.ATTRIBUTE4%TYPE;
v_book_type_code FA_MASS_ADDITIONS.BOOK_TYPE_CODE%TYPE;
BEGIN
IF :new.feeder_system_name ='ORACLE PROJECTS'
and :new.queue_name ='POST'
and :new.posting_status ='POST'
and :new.date_placed_in_service IS NOT NULL
and :new.project_id IS NOT NULL
THEN
BEGIN
SELECT organization_id,haou.attribute4
INTO v_org_id, v_attr4
FROM FND_LOOKUP_TYPES fltv, FND_LOOKUP_VALUES flv,HR_ALL_ORGANIZATION_UNITS haou
WHERE fltv.lookup_type = 'BT_LATAM3_ASSET_AUTOMATION'
AND fltv.lookup_type = flv.lookup_type
AND flv.lookup_code = haou.attribute4
AND haou.organization_id = (select org_id
from PA_PROJECTS_ALL
where project_id=:new.project_id)
AND flv.language='US';
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN;
END;

IF :new.serial_number IS NULL AND v_attr4='2430' THEN
:NEW.GLOBAL_ATTRIBUTE_CATEGORY:='JL.AR.FAXMADDS.FA_MASS_ADD';
:NEW.global_attribute1 := fnd_date.date_to_canonical(:new.date_placed_in_service);
ELSIF :new.serial_number IS NOT NULL THEN
SELECT responsibility_id
INTO v_resp_id
FROM fnd_responsibility_tl
WHERE responsibility_id in ( SELECT level_value
FROM fnd_profile_option_values
WHERE profile_option_id in ( select profile_option_id
from fnd_profile_options
where profile_option_name='ORG_ID')
AND level_id=10003
AND profile_option_value = to_char(v_org_id))
AND responsibility_name like '%Fixed Assets%'
AND language='US'
AND rownum<2;
select BOOK_TYPE_CODE
into v_book_type_code
from FA_BOOK_CONTROLS
where book_class='CORPORATE'
and org_id= (select organization_id from PER_SECURITY_PROFILES
where security_profile_id = (select profile_option_value
from fnd_profile_option_values
where profile_option_id in
(select profile_option_id
from fnd_profile_options
where profile_option_name = 'FA_SECURITY_PROFILE_ID')
and level_id=10003
and level_value=to_char(v_resp_id)));
IF (:NEW.BOOK_TYPE_CODE=v_book_type_code) THEN
:NEW.POSTING_STATUS:='ON HOLD';
:NEW.QUEUE_NAME :='ON HOLD';
:NEW.REVIEWER_COMMENTS := 'BT Update DPIS in Mass Additions - Program yet to run ';
END IF;
END IF;
END IF;
END;


Regards,
Suresh Reddy