eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.ALR_DBTRIGGER dependencies on ALR_DBTRIGGER

Line 1: package body ALR_DBTRIGGER as -- package body

1: package body ALR_DBTRIGGER as -- package body
2: /* $Header: ALREDBTB.pls 120.11.12020000.3 2012/07/17 19:22:28 rarmaly ship $ */
3:
4:
5: --

Line 81: if alr_dbtrigger.DIAGNOSTICS = 'Y' then

77: sqlstmt VARCHAR2(32000);
78: begin
79:
80: -- Only do debug logging if Diagnostics is enabled
81: if alr_dbtrigger.DIAGNOSTICS = 'Y' then
82:
83: DEBUG_SEQ := DEBUG_SEQ + 1;
84:
85: sqlstmt := 'insert into alr_dbtrigger_debug'||

Line 85: sqlstmt := 'insert into alr_dbtrigger_debug'||

81: if alr_dbtrigger.DIAGNOSTICS = 'Y' then
82:
83: DEBUG_SEQ := DEBUG_SEQ + 1;
84:
85: sqlstmt := 'insert into alr_dbtrigger_debug'||
86: '(type,creation_date,stmt) values('''||
87: DEBUG_SEQ||''',sysdate,'''||TXT||''')';
88:
89: c := dbms_sql.open_cursor;

Line 113: dbms_sql.parse(c, 'delete alr_dbtrigger_debug', dbms_sql.native);

109: begin
110: c := dbms_sql.open_cursor;
111:
112: begin
113: dbms_sql.parse(c, 'delete alr_dbtrigger_debug', dbms_sql.native);
114: rows_processed := dbms_sql.execute(c);
115: exception
116: when others then
117: NULL; -- do nothing

Line 136: if alr_dbtrigger.APPLSYS_SCHEMA is NULL then

132:
133: --
134: -- Fetch the APPLSYS schema, only once.
135: --
136: if alr_dbtrigger.APPLSYS_SCHEMA is NULL then
137: begin
138:
139: select fou.oracle_username
140: into t_schema

Line 151: alr_dbtrigger.APPLSYS_SCHEMA := t_schema;

147: -- but this code is shared with aluddt.sql, called by
148: -- AutoInstall before FND_INSTALLATION gets recreated
149: -- during upgrade.
150:
151: alr_dbtrigger.APPLSYS_SCHEMA := t_schema;
152:
153: exception
154: when others then
155: -- Need to provide an error message here.

Line 164: if (alr_dbtrigger.TARGET_APPL_ID is NULL) or

160:
161: --
162: -- Fetch the appl shortname for the passed appl ID
163: --
164: if (alr_dbtrigger.TARGET_APPL_ID is NULL) or
165: (alr_dbtrigger.TARGET_APPL_ID <> APPL_ID) then
166: begin
167:
168: select application_short_name

Line 165: (alr_dbtrigger.TARGET_APPL_ID <> APPL_ID) then

161: --
162: -- Fetch the appl shortname for the passed appl ID
163: --
164: if (alr_dbtrigger.TARGET_APPL_ID is NULL) or
165: (alr_dbtrigger.TARGET_APPL_ID <> APPL_ID) then
166: begin
167:
168: select application_short_name
169: into alr_dbtrigger.TARGET_APPL_SHORT_NAME

Line 169: into alr_dbtrigger.TARGET_APPL_SHORT_NAME

165: (alr_dbtrigger.TARGET_APPL_ID <> APPL_ID) then
166: begin
167:
168: select application_short_name
169: into alr_dbtrigger.TARGET_APPL_SHORT_NAME
170: from fnd_application
171: where application_id = APPL_ID;
172:
173: alr_dbtrigger.TARGET_APPL_ID := APPL_ID;

Line 173: alr_dbtrigger.TARGET_APPL_ID := APPL_ID;

169: into alr_dbtrigger.TARGET_APPL_SHORT_NAME
170: from fnd_application
171: where application_id = APPL_ID;
172:
173: alr_dbtrigger.TARGET_APPL_ID := APPL_ID;
174:
175: exception
176: when others then
177: -- Need to provide an error message here.

Line 186: if alr_dbtrigger.MULTI_ORG_FLAG is NULL then

182:
183: --
184: -- Fetch the multi-org flag
185: --
186: if alr_dbtrigger.MULTI_ORG_FLAG is NULL then
187: begin
188:
189: select multi_org_flag
190: into alr_dbtrigger.MULTI_ORG_FLAG

Line 190: into alr_dbtrigger.MULTI_ORG_FLAG

186: if alr_dbtrigger.MULTI_ORG_FLAG is NULL then
187: begin
188:
189: select multi_org_flag
190: into alr_dbtrigger.MULTI_ORG_FLAG
191: from fnd_product_groups
192: where rownum=1; -- there should only be one row, but just in case
193:
194: if alr_dbtrigger.MULTI_ORG_FLAG is NULL then

Line 194: if alr_dbtrigger.MULTI_ORG_FLAG is NULL then

190: into alr_dbtrigger.MULTI_ORG_FLAG
191: from fnd_product_groups
192: where rownum=1; -- there should only be one row, but just in case
193:
194: if alr_dbtrigger.MULTI_ORG_FLAG is NULL then
195: alr_dbtrigger.MULTI_ORG_FLAG := 'N'; -- default
196: end if;
197:
198: exception

Line 195: alr_dbtrigger.MULTI_ORG_FLAG := 'N'; -- default

191: from fnd_product_groups
192: where rownum=1; -- there should only be one row, but just in case
193:
194: if alr_dbtrigger.MULTI_ORG_FLAG is NULL then
195: alr_dbtrigger.MULTI_ORG_FLAG := 'N'; -- default
196: end if;
197:
198: exception
199: when others then

Line 209: if alr_dbtrigger.DIAGNOSTICS is NULL then

205:
206: --
207: -- Fetch the Diagnostics flag
208: --
209: if alr_dbtrigger.DIAGNOSTICS is NULL then
210: begin
211: alr_dbtrigger.DIAGNOSTICS := FND_PROFILE.VALUE('DIAGNOSTICS');
212:
213: if alr_dbtrigger.DIAGNOSTICS is NULL then

Line 211: alr_dbtrigger.DIAGNOSTICS := FND_PROFILE.VALUE('DIAGNOSTICS');

207: -- Fetch the Diagnostics flag
208: --
209: if alr_dbtrigger.DIAGNOSTICS is NULL then
210: begin
211: alr_dbtrigger.DIAGNOSTICS := FND_PROFILE.VALUE('DIAGNOSTICS');
212:
213: if alr_dbtrigger.DIAGNOSTICS is NULL then
214: alr_dbtrigger.DIAGNOSTICS := 'N'; -- default
215: end if;

Line 213: if alr_dbtrigger.DIAGNOSTICS is NULL then

209: if alr_dbtrigger.DIAGNOSTICS is NULL then
210: begin
211: alr_dbtrigger.DIAGNOSTICS := FND_PROFILE.VALUE('DIAGNOSTICS');
212:
213: if alr_dbtrigger.DIAGNOSTICS is NULL then
214: alr_dbtrigger.DIAGNOSTICS := 'N'; -- default
215: end if;
216:
217: -- Try to delete the old debug records

Line 214: alr_dbtrigger.DIAGNOSTICS := 'N'; -- default

210: begin
211: alr_dbtrigger.DIAGNOSTICS := FND_PROFILE.VALUE('DIAGNOSTICS');
212:
213: if alr_dbtrigger.DIAGNOSTICS is NULL then
214: alr_dbtrigger.DIAGNOSTICS := 'N'; -- default
215: end if;
216:
217: -- Try to delete the old debug records
218: ALR_DEBUG_CLEAN;

Line 222: alr_dbtrigger.DIAGNOSTICS := 'N';

218: ALR_DEBUG_CLEAN;
219:
220: exception
221: when others then
222: alr_dbtrigger.DIAGNOSTICS := 'N';
223:
224: end;
225: end if;
226:

Line 322: alr_dbtrigger.MULTI_ORG_FLAG = 'Y' then

318:
319: for CREC in C loop -- fetch all ORACLE IDs
320:
321: if crec.ORG_ID is not NULL and
322: alr_dbtrigger.MULTI_ORG_FLAG = 'Y' then
323: oracle_id_str := crec.ORACLE_USERNAME || crec.ORG_ID;
324: else
325: oracle_id_str := crec.ORACLE_USERNAME;
326: end if;

Line 385: create table alr_dbtrigger_debug

381: 1) Create the debug table under the APPLSYS account as follows:
382:
383: connect applsys/apps
384:
385: create table alr_dbtrigger_debug
386: (creation_date date,
387: applsys varchar2(30),
388: appsname varchar2(30),
389: target varchar2(30),

Line 396: grant all privileges on alr_dbtrigger_debug to APPS;

392:
393: Grant access to the APPS account and other APPS accounts
394: to be debugged:
395:
396: grant all privileges on alr_dbtrigger_debug to APPS;
397:
398: Create a synonym under the APPS account and other APPS accounts
399: to be debugged:
400:

Line 403: create synonym alr_dbtrigger_debug for APPLSYS.alr_dbtrigger_debug;

399: to be debugged:
400:
401: connect apps/apps
402:
403: create synonym alr_dbtrigger_debug for APPLSYS.alr_dbtrigger_debug;
404:
405: 2) Uncomment the below INSERT statement and save this file. */
406:
407: /*

Line 408: insert into alr_dbtrigger_debug values

404:
405: 2) Uncomment the below INSERT statement and save this file. */
406:
407: /*
408: insert into alr_dbtrigger_debug values
409: (Sysdate,
410: alr_dbtrigger.APPLSYS_SCHEMA,
411: alr_dbtrigger.TARGET_APPL_SHORT_NAME,
412: TARGET_SCHEMA,

Line 410: alr_dbtrigger.APPLSYS_SCHEMA,

406:
407: /*
408: insert into alr_dbtrigger_debug values
409: (Sysdate,
410: alr_dbtrigger.APPLSYS_SCHEMA,
411: alr_dbtrigger.TARGET_APPL_SHORT_NAME,
412: TARGET_SCHEMA,
413: DEBUG_SEQ,
414: SQLSTMT);

Line 411: alr_dbtrigger.TARGET_APPL_SHORT_NAME,

407: /*
408: insert into alr_dbtrigger_debug values
409: (Sysdate,
410: alr_dbtrigger.APPLSYS_SCHEMA,
411: alr_dbtrigger.TARGET_APPL_SHORT_NAME,
412: TARGET_SCHEMA,
413: DEBUG_SEQ,
414: SQLSTMT);
415: commit;

Line 418: /* 3) Recreate ALR_DBTRIGGER package defined by this file:

414: SQLSTMT);
415: commit;
416: */
417:
418: /* 3) Recreate ALR_DBTRIGGER package defined by this file:
419:
420: sqlplus apps/apps @ALREDBTB.pls
421: : (and in any other APPS accounts)
422:

Line 428: regeneration of the ALR_DBTRIGGER package.)

424: problemed alert definition by simply disabling and then
425: re-enabling it. This will attempt to recreate the underlying
426: database trigger(s). If ORA-4068 occurs, try this step once
427: more. (This is not the original error and is caused by the
428: regeneration of the ALR_DBTRIGGER package.)
429:
430: When the error is reproduced, there should be one or more records
431: inserted in the ALR_DBTRIGGER_DEBUG table. Select the records and
432: examine the SQL stmt stored in ALR_DBTRIGGER_DEBUG.STMT. Execute

Line 431: inserted in the ALR_DBTRIGGER_DEBUG table. Select the records and

427: more. (This is not the original error and is caused by the
428: regeneration of the ALR_DBTRIGGER package.)
429:
430: When the error is reproduced, there should be one or more records
431: inserted in the ALR_DBTRIGGER_DEBUG table. Select the records and
432: examine the SQL stmt stored in ALR_DBTRIGGER_DEBUG.STMT. Execute
433: the statement directly in sqlplus while connected to the APPS
434: account to determine the exact cause.
435:

Line 432: examine the SQL stmt stored in ALR_DBTRIGGER_DEBUG.STMT. Execute

428: regeneration of the ALR_DBTRIGGER package.)
429:
430: When the error is reproduced, there should be one or more records
431: inserted in the ALR_DBTRIGGER_DEBUG table. Select the records and
432: examine the SQL stmt stored in ALR_DBTRIGGER_DEBUG.STMT. Execute
433: the statement directly in sqlplus while connected to the APPS
434: account to determine the exact cause.
435:
436: Once the issue is resolved, please undo the debugging setup by

Line 445: AD_DDL.do_ddl (alr_dbtrigger.APPLSYS_SCHEMA,

441:
442: if ROOT_STMT = 'create' then
443: AD_DDL.create_trigger_in_schema(TARGET_SCHEMA, SQLSTMT);
444: else
445: AD_DDL.do_ddl (alr_dbtrigger.APPLSYS_SCHEMA,
446: alr_dbtrigger.TARGET_APPL_SHORT_NAME,
447: STMT_TYPE,
448: SQLSTMT,
449: OBJECT_NAME);

Line 446: alr_dbtrigger.TARGET_APPL_SHORT_NAME,

442: if ROOT_STMT = 'create' then
443: AD_DDL.create_trigger_in_schema(TARGET_SCHEMA, SQLSTMT);
444: else
445: AD_DDL.do_ddl (alr_dbtrigger.APPLSYS_SCHEMA,
446: alr_dbtrigger.TARGET_APPL_SHORT_NAME,
447: STMT_TYPE,
448: SQLSTMT,
449: OBJECT_NAME);
450:

Line 920: alr_dbtrigger.NUMBER_OF_TRIGGERS :=

916: OID, ONAME, TBL_NAME, EVENT_MODE, TRGNAM);
917:
918: if TRGNAM is not NULL then
919: -- Trigger created successfully (enabled)
920: alr_dbtrigger.NUMBER_OF_TRIGGERS :=
921: alr_dbtrigger.NUMBER_OF_TRIGGERS + 1;
922: alr_dbtrigger.CREATED_TRIGGERS(alr_dbtrigger.NUMBER_OF_TRIGGERS) :=
923: TRGNAM;
924: alr_dbtrigger.CREATED_TRIG_ONAME(alr_dbtrigger.NUMBER_OF_TRIGGERS) :=

Line 921: alr_dbtrigger.NUMBER_OF_TRIGGERS + 1;

917:
918: if TRGNAM is not NULL then
919: -- Trigger created successfully (enabled)
920: alr_dbtrigger.NUMBER_OF_TRIGGERS :=
921: alr_dbtrigger.NUMBER_OF_TRIGGERS + 1;
922: alr_dbtrigger.CREATED_TRIGGERS(alr_dbtrigger.NUMBER_OF_TRIGGERS) :=
923: TRGNAM;
924: alr_dbtrigger.CREATED_TRIG_ONAME(alr_dbtrigger.NUMBER_OF_TRIGGERS) :=
925: ONAME;

Line 922: alr_dbtrigger.CREATED_TRIGGERS(alr_dbtrigger.NUMBER_OF_TRIGGERS) :=

918: if TRGNAM is not NULL then
919: -- Trigger created successfully (enabled)
920: alr_dbtrigger.NUMBER_OF_TRIGGERS :=
921: alr_dbtrigger.NUMBER_OF_TRIGGERS + 1;
922: alr_dbtrigger.CREATED_TRIGGERS(alr_dbtrigger.NUMBER_OF_TRIGGERS) :=
923: TRGNAM;
924: alr_dbtrigger.CREATED_TRIG_ONAME(alr_dbtrigger.NUMBER_OF_TRIGGERS) :=
925: ONAME;
926: end if;

Line 924: alr_dbtrigger.CREATED_TRIG_ONAME(alr_dbtrigger.NUMBER_OF_TRIGGERS) :=

920: alr_dbtrigger.NUMBER_OF_TRIGGERS :=
921: alr_dbtrigger.NUMBER_OF_TRIGGERS + 1;
922: alr_dbtrigger.CREATED_TRIGGERS(alr_dbtrigger.NUMBER_OF_TRIGGERS) :=
923: TRGNAM;
924: alr_dbtrigger.CREATED_TRIG_ONAME(alr_dbtrigger.NUMBER_OF_TRIGGERS) :=
925: ONAME;
926: end if;
927:
928: if ENABLED_FLAG = 'N' then

Line 982: alr_dbtrigger.NUMBER_OF_TRIGGERS:=0;

978: ALR_DEBUG('--->> Entering CREATE_EVENT_DB_TRIGGER');
979:
980: INIT_GLOBALS (TBL_APPLID);
981:
982: alr_dbtrigger.NUMBER_OF_TRIGGERS:=0;
983: alr_dbtrigger.BAD_ORACLE_USERNAMES:='';
984:
985: for CREC in C loop -- process all ORACLE IDs
986:

Line 983: alr_dbtrigger.BAD_ORACLE_USERNAMES:='';

979:
980: INIT_GLOBALS (TBL_APPLID);
981:
982: alr_dbtrigger.NUMBER_OF_TRIGGERS:=0;
983: alr_dbtrigger.BAD_ORACLE_USERNAMES:='';
984:
985: for CREC in C loop -- process all ORACLE IDs
986:
987: if IS_ENABLE = 'N' or crec.ENABLED_INST = 'N' then

Line 1017: if alr_dbtrigger.BAD_ORACLE_USERNAMES is NOT NULL then

1013:
1014: when NONEXISTENT_TABLE or PLSQL_UNCOMPILED then
1015:
1016: -- Save ORACLE ID to display to user
1017: if alr_dbtrigger.BAD_ORACLE_USERNAMES is NOT NULL then
1018: alr_dbtrigger.BAD_ORACLE_USERNAMES :=
1019: alr_dbtrigger.BAD_ORACLE_USERNAMES||', ';
1020: end if;
1021: alr_dbtrigger.BAD_ORACLE_USERNAMES :=

Line 1018: alr_dbtrigger.BAD_ORACLE_USERNAMES :=

1014: when NONEXISTENT_TABLE or PLSQL_UNCOMPILED then
1015:
1016: -- Save ORACLE ID to display to user
1017: if alr_dbtrigger.BAD_ORACLE_USERNAMES is NOT NULL then
1018: alr_dbtrigger.BAD_ORACLE_USERNAMES :=
1019: alr_dbtrigger.BAD_ORACLE_USERNAMES||', ';
1020: end if;
1021: alr_dbtrigger.BAD_ORACLE_USERNAMES :=
1022: alr_dbtrigger.BAD_ORACLE_USERNAMES || crec.ORACLE_USERNAME;

Line 1019: alr_dbtrigger.BAD_ORACLE_USERNAMES||', ';

1015:
1016: -- Save ORACLE ID to display to user
1017: if alr_dbtrigger.BAD_ORACLE_USERNAMES is NOT NULL then
1018: alr_dbtrigger.BAD_ORACLE_USERNAMES :=
1019: alr_dbtrigger.BAD_ORACLE_USERNAMES||', ';
1020: end if;
1021: alr_dbtrigger.BAD_ORACLE_USERNAMES :=
1022: alr_dbtrigger.BAD_ORACLE_USERNAMES || crec.ORACLE_USERNAME;
1023:

Line 1021: alr_dbtrigger.BAD_ORACLE_USERNAMES :=

1017: if alr_dbtrigger.BAD_ORACLE_USERNAMES is NOT NULL then
1018: alr_dbtrigger.BAD_ORACLE_USERNAMES :=
1019: alr_dbtrigger.BAD_ORACLE_USERNAMES||', ';
1020: end if;
1021: alr_dbtrigger.BAD_ORACLE_USERNAMES :=
1022: alr_dbtrigger.BAD_ORACLE_USERNAMES || crec.ORACLE_USERNAME;
1023:
1024: when others then
1025:

Line 1022: alr_dbtrigger.BAD_ORACLE_USERNAMES || crec.ORACLE_USERNAME;

1018: alr_dbtrigger.BAD_ORACLE_USERNAMES :=
1019: alr_dbtrigger.BAD_ORACLE_USERNAMES||', ';
1020: end if;
1021: alr_dbtrigger.BAD_ORACLE_USERNAMES :=
1022: alr_dbtrigger.BAD_ORACLE_USERNAMES || crec.ORACLE_USERNAME;
1023:
1024: when others then
1025:
1026: raise ERROR_ROLLBACK;

Line 1031: if alr_dbtrigger.BAD_ORACLE_USERNAMES is NOT NULL then

1027: end;
1028:
1029: end loop;
1030:
1031: if alr_dbtrigger.BAD_ORACLE_USERNAMES is NOT NULL then
1032:
1033: raise NONEXISTENT_TABLE;
1034: end if;
1035:

Line 1044: alr_dbtrigger.BAD_ORACLE_USERNAMES, FALSE);

1040: FND_MESSAGE.SET_NAME('ALR', 'TRIGGER-NO TABLE IN ACCOUNT');
1041: -- this message is uppercased in fnd_new_messages and fnd_messages
1042: FND_MESSAGE.SET_TOKEN('TABLE_NAME', TBL_NAME);
1043: FND_MESSAGE.SET_TOKEN('ORACLE_USERNAME',
1044: alr_dbtrigger.BAD_ORACLE_USERNAMES, FALSE);
1045: APP_EXCEPTION.RAISE_EXCEPTION;
1046:
1047: when ERROR_ROLLBACK then
1048:

Line 1057: for I in 1 .. alr_dbtrigger.NUMBER_OF_TRIGGERS loop

1053: FND_MESSAGE.SET_TOKEN(
1054: 'ROUTINE', 'CREATE_EVENT_DB_TRIGGER', FALSE);
1055:
1056: -- "Rollback"
1057: for I in 1 .. alr_dbtrigger.NUMBER_OF_TRIGGERS loop
1058: DROP_EVENT_DB_TRIGGER
1059: (alr_dbtrigger.CREATED_TRIG_ONAME(I),
1060: alr_dbtrigger.CREATED_TRIGGERS(I));
1061: -- Note that table appl is the same even when

Line 1059: (alr_dbtrigger.CREATED_TRIG_ONAME(I),

1055:
1056: -- "Rollback"
1057: for I in 1 .. alr_dbtrigger.NUMBER_OF_TRIGGERS loop
1058: DROP_EVENT_DB_TRIGGER
1059: (alr_dbtrigger.CREATED_TRIG_ONAME(I),
1060: alr_dbtrigger.CREATED_TRIGGERS(I));
1061: -- Note that table appl is the same even when
1062: -- dropping trigger off different accounts.
1063: end loop;

Line 1060: alr_dbtrigger.CREATED_TRIGGERS(I));

1056: -- "Rollback"
1057: for I in 1 .. alr_dbtrigger.NUMBER_OF_TRIGGERS loop
1058: DROP_EVENT_DB_TRIGGER
1059: (alr_dbtrigger.CREATED_TRIG_ONAME(I),
1060: alr_dbtrigger.CREATED_TRIGGERS(I));
1061: -- Note that table appl is the same even when
1062: -- dropping trigger off different accounts.
1063: end loop;
1064:

Line 1269: end ALR_DBTRIGGER;

1265: begin
1266:
1267: ALR_DEBUG_CLEAN;
1268:
1269: end ALR_DBTRIGGER;