[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
sqlstmt := 'insert into alr_dbtrigger_debug'||
'(type,creation_date,stmt) values('''||
DEBUG_SEQ||''',sysdate,'''||TXT||''')';
dbms_sql.parse(c, 'delete alr_dbtrigger_debug', dbms_sql.native);
select fou.oracle_username
into t_schema
from FND_PRODUCT_INSTALLATIONS FPI,
FND_ORACLE_USERID FOU
where fpi.application_id = 0
and fpi.oracle_id = fou.oracle_id;
select application_short_name
into alr_dbtrigger.TARGET_APPL_SHORT_NAME
from fnd_application
where application_id = APPL_ID;
select multi_org_flag
into alr_dbtrigger.MULTI_ORG_FLAG
from fnd_product_groups
where rownum=1; -- there should only be one row, but just in case
select DISTINCT ORACLE_USERNAME, DATA_GROUP_ID ORG_ID
from alr_alerts a,
alr_alert_installations i,
fnd_oracle_userid o
where a.alert_condition_type='E'
and 'Y'=decode(EVENT_MODE, 'I', a.insert_flag,
'U', a.update_flag,
'D', a.delete_flag)
and a.enabled_flag = 'Y'
and i.enabled_flag = 'Y'
and a.table_application_id = TBL_APPLID
and a.table_name = TBL_NAME
and i.application_id = a.application_id
and i.alert_id = a.alert_id
and i.oracle_id = o.oracle_id;
select 'N'
into no_org_flag
from dual
where exists
(select 1
from alr_alerts a,
alr_alert_installations i
where a.alert_condition_type='E'
and 'Y'=decode(EVENT_MODE, 'I', a.insert_flag,
'U', a.update_flag,
'D', a.delete_flag)
and a.enabled_flag = 'Y'
and i.enabled_flag = 'Y'
and a.table_application_id = TBL_APPLID
and a.table_name = TBL_NAME
and i.application_id = a.application_id
and i.alert_id = a.alert_id
and i.data_group_id is not null);
The stmt can be stored in a debug table by the below INSERT so that
you can re-execute it manually from sqlplus.
1) Create the debug table under the APPLSYS account as follows:
connect applsys/apps
create table alr_dbtrigger_debug
(creation_date date,
applsys varchar2(30),
appsname varchar2(30),
target varchar2(30),
type varchar2(10),
stmt varchar2(2000));
2) Uncomment the below INSERT statement and save this file. */
/*
insert into alr_dbtrigger_debug values
(Sysdate,
alr_dbtrigger.APPLSYS_SCHEMA,
alr_dbtrigger.TARGET_APPL_SHORT_NAME,
TARGET_SCHEMA,
DEBUG_SEQ,
SQLSTMT);
inserted in the ALR_DBTRIGGER_DEBUG table. Select the records and
examine the SQL stmt stored in ALR_DBTRIGGER_DEBUG.STMT. Execute
the statement directly in sqlplus while connected to the APPS
account to determine the exact cause.
Once the issue is resolved, please undo the debugging setup by
reversing the "uncommenting" of the debug code above and repeat
step 3 above.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
if ROOT_STMT = 'create' then
AD_DDL.create_trigger_in_schema(TARGET_SCHEMA, SQLSTMT);
SELECT table_id
INTO v_table_id
FROM fnd_tables
WHERE table_name = TBL_NAME
AND application_id = TBL_APPLID;
SELECT table_name
INTO v_table_name
FROM user_triggers
WHERE trigger_name = v_trigger_name_orig;
procedure DELETE_EVENT_DB_TRIGGER$2( -- prototype
APPL_ID in number,
ALR_ID in number,
TBL_APPLID in number,
TBL_NAME in varchar2,
OID in number,
ONAME in varchar2,
EVENT_MODE in varchar2);
procedure DELETE_EVENT_DB_TRIGGER$1(
APPL_ID in number,
ALR_ID in number,
TBL_APPLID in number,
TBL_NAME in varchar2,
OID in number,
INSERT_FLAG in varchar2,
UPDATE_FLAG in varchar2,
DELETE_FLAG in varchar2) is
cursor c1 is
select DISTINCT
ORACLE_USERNAME, O.ORACLE_ID
from FND_ORACLE_USERID O, ALR_ALERT_INSTALLATIONS I
where O.ORACLE_ID = NVL(OID, O.ORACLE_ID)
and O.ORACLE_ID = I.ORACLE_ID
and I.APPLICATION_ID = APPL_ID
and I.ALERT_ID = ALR_ID
order by ORACLE_USERNAME;
ALR_DEBUG('--->> Entering DELETE_EVENT_DB_TRIGGER$1');
if INSERT_FLAG = 'Y' then
-- 3933639 added if (OID is null) so triggers will be dropped for alerts
-- containing multiple installations when it should
if (OID is null) then
DELETE_EVENT_DB_TRIGGER$2(APPL_ID, ALR_ID, TBL_APPLID, TBL_NAME, null, crec.ORACLE_USERNAME, 'I');
DELETE_EVENT_DB_TRIGGER$2(APPL_ID, ALR_ID, TBL_APPLID, TBL_NAME,
crec.ORACLE_ID, crec.ORACLE_USERNAME, 'I');
if UPDATE_FLAG = 'Y' then
if (OID is null) then
DELETE_EVENT_DB_TRIGGER$2(APPL_ID, ALR_ID, TBL_APPLID, TBL_NAME, null, crec.ORACLE_USERNAME, 'U');
DELETE_EVENT_DB_TRIGGER$2(APPL_ID, ALR_ID, TBL_APPLID, TBL_NAME,
crec.ORACLE_ID, crec.ORACLE_USERNAME, 'U');
if DELETE_FLAG = 'Y' then
if (OID is null) then
DELETE_EVENT_DB_TRIGGER$2(APPL_ID, ALR_ID, TBL_APPLID, TBL_NAME, null, crec.ORACLE_USERNAME, 'D');
DELETE_EVENT_DB_TRIGGER$2(APPL_ID, ALR_ID, TBL_APPLID, TBL_NAME,
crec.ORACLE_ID, crec.ORACLE_USERNAME, 'D');
ALR_DEBUG('<<--- Leaving DELETE_EVENT_DB_TRIGGER$1');
end DELETE_EVENT_DB_TRIGGER$1;
procedure DELETE_EVENT_DB_TRIGGER$2(
APPL_ID in number,
ALR_ID in number,
TBL_APPLID in number,
TBL_NAME in varchar2,
OID in number, -- never NULL here
ONAME in varchar2,
EVENT_MODE in varchar2) is
TRIGGER_NAME varchar2(61);
ALR_DEBUG('--->> Entering DELETE_EVENT_DB_TRIGGER$2');
select count(*) into enabled_count
from alr_alerts a, alr_alert_installations i
where alert_condition_type='E'
and 'Y'=decode(EVENT_MODE, 'I', insert_flag,
'U', update_flag,
'D', delete_flag)
and table_application_id = TBL_APPLID
and table_name = TBL_NAME
and i.application_id = a.application_id
and i.alert_id = a.alert_id
and i.oracle_id = OID;
ALR_DEBUG('DELETE_EVENT_DB_TRIGGER$2: enabled_count='||enabled_count);
NULL; -- to be done on POST-DELETE form trigger
ALR_DEBUG('<<--- Leaving DELETE_EVENT_DB_TRIGGER$2');
end DELETE_EVENT_DB_TRIGGER$2;
select count(*) into other_enabled_count
from alr_alerts a, alr_alert_installations i
where alert_condition_type='E'
and 'Y'=decode(EVENT_MODE, 'I', insert_flag,
'U', update_flag,
'D', delete_flag)
and a.enabled_flag = 'Y'
and i.enabled_flag = 'Y'
and table_application_id = TBL_APPLID
and table_name = TBL_NAME
and a.application_id = i.application_id
and a.alert_id = i.alert_id
and i.oracle_id = OID;
if EVENT_MODE = 'I' then TRIGGER_TYPE:='insert';
elsif EVENT_MODE = 'U' then TRIGGER_TYPE:='update';
elsif EVENT_MODE = 'D' then TRIGGER_TYPE:='delete';
'select nvl(mo_global.get_current_org_id, 0) into MORGID from dual;' ||
INSERT_FLAG in varchar2,
UPDATE_FLAG in varchar2,
DELETE_FLAG in varchar2,
IS_ENABLE in varchar2) is
cursor C is
select DISTINCT
ORACLE_USERNAME, O.ORACLE_ID, I.ENABLED_FLAG ENABLED_INST
from FND_ORACLE_USERID O, ALR_ALERT_INSTALLATIONS I
where O.ORACLE_ID = NVL(OID, O.ORACLE_ID)
and O.ORACLE_ID = I.ORACLE_ID
and I.APPLICATION_ID = NVL(APPL_ID, I.APPLICATION_ID)
and I.ALERT_ID = NVL(ALR_ID, I.ALERT_ID)
and ORACLE_USERNAME NOT LIKE '%_MRC'
and ORACLE_USERNAME NOT LIKE '%_CED'
order by ORACLE_USERNAME;
if INSERT_FLAG = 'Y' then
CREATE_EVENT_DB_TRIGGER$1(APPL_ID, ALR_ID, TBL_APPLID, TBL_NAME,
crec.ORACLE_ID, crec.ORACLE_USERNAME, 'I', ENABLED_FINAL);
if UPDATE_FLAG = 'Y' then
CREATE_EVENT_DB_TRIGGER$1(APPL_ID, ALR_ID, TBL_APPLID, TBL_NAME,
crec.ORACLE_ID, crec.ORACLE_USERNAME, 'U', ENABLED_FINAL);
if DELETE_FLAG = 'Y' then
CREATE_EVENT_DB_TRIGGER$1(APPL_ID, ALR_ID, TBL_APPLID, TBL_NAME,
crec.ORACLE_ID, crec.ORACLE_USERNAME, 'D', ENABLED_FINAL);
INSERT_FLAG in varchar2,
UPDATE_FLAG in varchar2,
DELETE_FLAG in varchar2,
IS_ENABLE in varchar2) is
begin
ALR_DEBUG('--->> Entering ALTER_EVENT_DB_TRIGGER');
INSERT_FLAG, UPDATE_FLAG, DELETE_FLAG, IS_ENABLE);
procedure DELETE_EVENT_DB_TRIGGER(
APPL_ID in number,
ALR_ID in number,
OID in number) is
TBL_APPLID ALR_ALERTS.TABLE_APPLICATION_ID%type;
IS_INSERT ALR_ALERTS.INSERT_FLAG%type;
IS_UPDATE ALR_ALERTS.UPDATE_FLAG%type;
IS_DELETE ALR_ALERTS.DELETE_FLAG%type;
ALR_DEBUG('--->> Entering DELETE_EVENT_DB_TRIGGER');
select TABLE_APPLICATION_ID, TABLE_NAME, INSERT_FLAG,
UPDATE_FLAG, nvl(DELETE_FLAG, 'N'), ENABLED_FLAG
into TBL_APPLID, TBL_NAME, IS_INSERT,
IS_UPDATE, IS_DELETE, IS_ENABLED
from ALR_ALERTS
where ALERT_ID = ALR_ID AND APPLICATION_ID = APPL_ID
and ALERT_CONDITION_TYPE = 'E';
'ROUTINE', 'DELETE_EVENT_DB_TRIGGER', FALSE);
DELETE_EVENT_DB_TRIGGER$1(
APPL_ID, ALR_ID, TBL_APPLID, TBL_NAME, OID,
IS_INSERT, IS_UPDATE, IS_DELETE);
ALR_DEBUG('<<--- Leaving DELETE_EVENT_DB_TRIGGER');
end DELETE_EVENT_DB_TRIGGER;
procedure PRE_UPDATE_EVENT_ALERT(
APPL_ID in number,
ALR_ID in number,
NEW_TABLE_APPLID in number,
NEW_TABLE_NAME in varchar2,
NEW_INSERT_FLAG in varchar2,
NEW_UPDATE_FLAG in varchar2,
NEW_DELETE_FLAG in varchar2,
NEW_IS_ENABLE in varchar2) is
OLD_TBLAPPLID alr_alerts.table_application_id%type;
ALR_DEBUG('--->> Entering PRE_UPDATE_EVENT_ALERT');
select TABLE_APPLICATION_ID, TABLE_NAME, INSERT_FLAG, UPDATE_FLAG,
nvl(DELETE_FLAG, 'N'), ALERT_CONDITION_TYPE
into OLD_TBLAPPLID, OLD_TBLNM, OLD_IFLAG, OLD_UFLAG,
OLD_DFLAG, OLD_TYPE
from ALR_ALERTS
where APPLICATION_ID=APPL_ID and ALERT_ID=ALR_ID;
'ROUTINE', 'PRE_UPDATE_EVENT_ALERT', FALSE);
null, NEW_INSERT_FLAG, NEW_UPDATE_FLAG, NEW_DELETE_FLAG,
NEW_IS_ENABLE);
if OLD_IFLAG = 'Y' and NEW_INSERT_FLAG = 'Y' then
OLD_IFLAG := 'N';
if OLD_UFLAG = 'Y' and NEW_UPDATE_FLAG = 'Y' then
OLD_UFLAG := 'N';
if OLD_DFLAG = 'Y' and NEW_DELETE_FLAG = 'Y' then
OLD_DFLAG := 'N';
-- Delete triggers for the old table
-- Need to explicitly call INIT_GLOBALS since APPLID may be
-- different than that in previous CREATE_EVENT_DB_TRIGGER().
INIT_GLOBALS (OLD_TBLAPPLID);
DELETE_EVENT_DB_TRIGGER$1(
APPL_ID, ALR_ID, OLD_TBLAPPLID, OLD_TBLNM, null,
OLD_IFLAG, OLD_UFLAG, OLD_DFLAG);
ALR_DEBUG('<<--- Leaving PRE_UPDATE_EVENT_ALERT');
end PRE_UPDATE_EVENT_ALERT;