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 --
6 -- PRAGMAS
7 --
8
9 NONEXISTENT_TABLE exception;
10 pragma EXCEPTION_INIT(NONEXISTENT_TABLE, -942);
11
12 NONEXISTENT_TRIGGER exception;
13 pragma EXCEPTION_INIT(NONEXISTENT_TRIGGER, -4080);
14
15 APPLICATION_ERROR exception;
16 pragma EXCEPTION_INIT(APPLICATION_ERROR, -20001);
17
18 PLSQL_UNCOMPILED exception;
19 pragma EXCEPTION_INIT(PLSQL_UNCOMPILED, -6550);
20
21
22 --
23 -- GLOBALS
24 --
25
26 type CREATED_TRIGGERS_TYPE is table of varchar2(61)
27 index by BINARY_INTEGER;
28 type CREATED_TRIG_ONAME_TYPE is table of varchar2(30)
29 index by BINARY_INTEGER;
30 CREATED_TRIGGERS CREATED_TRIGGERS_TYPE;
31 CREATED_TRIG_ONAME CREATED_TRIG_ONAME_TYPE;
32 NUMBER_OF_TRIGGERS integer;
33
34 BAD_ORACLE_USERNAMES varchar2(320);
35
36 APPLSYS_SCHEMA varchar2(30) := NULL;
37 TARGET_APPL_SHORT_NAME varchar2(50) := NULL;
38 TARGET_APPL_ID number(15) := NULL;
39 MULTI_ORG_FLAG varchar2(1) := NULL;
40 DIAGNOSTICS varchar2(1) := NULL;
41 DEBUG_SEQ number(15) := 0;
42
43 -- ======================================================================
44 --
45 -- PRIVATE PROCEDURE/FUNCTIONS
46 --
47 -- ======================================================================
48
49 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
50
51 procedure CREATE_EVENT_DB_TRIGGER$1( -- prototype
52 APPL_ID in number,
53 ALR_ID in number,
54 TBL_APPLID in number,
55 TBL_NAME in varchar2,
56 OID in number, -- null = all ORACLE IDs
57 ONAME in varchar2,
58 EVENT_MODE in varchar2,
59 ENABLED_FLAG in varchar2);
60
61 procedure ALTER_EVENT_DB_TRIGGER$1( -- prototype
62 APPL_ID in number,
63 ALR_ID in number,
64 TBL_APPLID in number,
65 TBL_NAME in varchar2,
66 OID in number,
67 ONAME in varchar2,
68 EVENT_MODE in varchar2,
69 IS_ENABLE in varchar2);
70
71
72 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
73
74 procedure ALR_DEBUG(TXT varchar2) is
75 c INTEGER;
76 rows_processed INTEGER;
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'||
86 '(type,creation_date,stmt) values('''||
87 DEBUG_SEQ||''',sysdate,'''||TXT||''')';
88
89 c := dbms_sql.open_cursor;
90
91 begin
92 dbms_sql.parse(c, sqlstmt, dbms_sql.native);
93 rows_processed := dbms_sql.execute(c);
94 exception
95 when others then
96 NULL; -- do nothing
97 end;
98
99 dbms_sql.close_cursor(c);
100 end if;
101
102 end ALR_DEBUG;
103
104 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
105
106 procedure ALR_DEBUG_CLEAN is
107 c INTEGER;
108 rows_processed INTEGER;
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
118 end;
119
120 dbms_sql.close_cursor(c);
121
122 end ALR_DEBUG_CLEAN;
123
124 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
125
126 procedure INIT_GLOBALS (APPL_ID number) is
127
128 t_schema varchar2(30);
129 call_ok boolean;
130
131 begin
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
141 from FND_PRODUCT_INSTALLATIONS FPI,
142 FND_ORACLE_USERID FOU
143 where fpi.application_id = 0
144 and fpi.oracle_id = fou.oracle_id;
145
146 -- Could use FND_INSTALLATION.GET_APP_INFO as well,
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.
156 APP_EXCEPTION.RAISE_EXCEPTION;
157
158 end;
159 end if; -- APPLSYS_SCHEMA
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
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.
178 APP_EXCEPTION.RAISE_EXCEPTION;
179
180 end;
181 end if; -- TARGET_APPL_ID
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
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
200 -- Need to provide an error message here.
201 APP_EXCEPTION.RAISE_EXCEPTION;
202
203 end;
204 end if;
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
214 alr_dbtrigger.DIAGNOSTICS := 'N'; -- default
215 end if;
216
217 -- Try to delete the old debug records
218 ALR_DEBUG_CLEAN;
219
220 exception
221 when others then
222 alr_dbtrigger.DIAGNOSTICS := 'N';
223
224 end;
225 end if;
226
227 end INIT_GLOBALS;
228
229 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
230
231 function DISALLOWED_CONTEXT(
232 TBL_APPLID in number,
233 TBL_NAME in varchar2,
234 OID in number,
235 EVENT_MODE in varchar2)
236 return varchar2 is
237 OID_LIST varchar2(32000);
238 prefix_list varchar2(20);
239 oracle_id_str varchar2(100); /* max: oracle_id(30)+org_code(15) */
240 no_org_flag varchar2(1);
241
242 cursor C is
243 select DISTINCT ORACLE_USERNAME, DATA_GROUP_ID ORG_ID
244 from alr_alerts a,
245 alr_alert_installations i,
246 fnd_oracle_userid o
247 where a.alert_condition_type='E'
248 and 'Y'=decode(EVENT_MODE, 'I', a.insert_flag,
249 'U', a.update_flag,
250 'D', a.delete_flag)
251 and a.enabled_flag = 'Y'
252 and i.enabled_flag = 'Y'
253 and a.table_application_id = TBL_APPLID
254 and a.table_name = TBL_NAME
255 and i.application_id = a.application_id
256 and i.alert_id = a.alert_id
257 and i.oracle_id = o.oracle_id;
258
259 begin
260
261 -- Create a list of enabled ORACLE_ID + ORG_ID combinations
262 -- in the form of:
263 -- a) if USER||ORG_ID not in ('APPS123','APPS',...)
264 -- b) if USER not in ('APPS','APPS2',...)
265 --
266 -- The list will validate whether the database trigger was fired
267 -- from within one of the allowable schema/org combinations.
268 --
269 -- Case (a) is applicable when site is multi-org *and* there are
270 -- non-null org_id's associated with the oracle_id in the alert
271 -- installations screen.
272 --
273 -- Case (b) is applicable for other cases. ie. for non-multi-org,
274 -- as well as for multi-org but without associated org_id's.
275 --
276 -- First, we need to determine that if this is a multi-org site,
277 -- whether there is any associated org_id for all event alerts
278 -- on this table (since one db trigger services all event alerts
279 -- based on the same table).
280
281 prefix_list := 'USER not in (';
282
283 if MULTI_ORG_FLAG = 'Y' then
284
285 no_org_flag := 'Y';
286
287 begin
288
289 select 'N'
290 into no_org_flag
291 from dual
292 where exists
293 (select 1
294 from alr_alerts a,
295 alr_alert_installations i
296 where a.alert_condition_type='E'
297 and 'Y'=decode(EVENT_MODE, 'I', a.insert_flag,
298 'U', a.update_flag,
299 'D', a.delete_flag)
300 and a.enabled_flag = 'Y'
301 and i.enabled_flag = 'Y'
302 and a.table_application_id = TBL_APPLID
303 and a.table_name = TBL_NAME
304 and i.application_id = a.application_id
305 and i.alert_id = a.alert_id
306 and i.data_group_id is not null);
307
308 exception
309 when others then NULL; -- Ignore all errors
310 end;
311
312 if no_org_flag <> 'Y' then
313 prefix_list := 'USER||ORGID not in (';
314 end if;
315 end if;
316
317 OID_LIST := NULL;
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;
327
328 if OID_LIST is NOT NULL then
329 OID_LIST := OID_LIST || ',''' || oracle_id_str || '''';
330 else
331 OID_LIST := '''' || oracle_id_str || '''';
332 end if;
333 end loop;
334
335 if OID_LIST is NULL then
336 OID_LIST := '''NONE''';
337 end if;
338
339 OID_LIST := prefix_list || OID_LIST || ')';
340 return OID_LIST;
341
342 exception
343 when others then NULL; -- Ignore all errors
344
345 end DISALLOWED_CONTEXT;
346
347 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
348
349 procedure RUN_SQL(ROOT_STMT in varchar2,
350 TARGET_SCHEMA in varchar2,
351 SQLSTMT in varchar2,
352 OBJECT_NAME in varchar2) is
353
354 stmt_type integer;
355
356 begin
357
358 if ROOT_STMT = 'create' then
359 STMT_TYPE := ad_ddl.create_trigger;
360 elsif ROOT_STMT = 'alter' then
361 STMT_TYPE := ad_ddl.alter_trigger;
362 elsif ROOT_STMT = 'drop' then
363 STMT_TYPE := ad_ddl.drop_trigger;
364 else
365 -- Need to provide an error message here.
366 APP_EXCEPTION.RAISE_EXCEPTION;
367 end if;
368
369
370 /* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
371 ORACLE WORLDWIDE SUPPORT:
372
373 This portion can be used for debugging any errors during the
374 creation or modification of alert database triggers in the Define
375 Alert form (message "Event alert is inactive in ORACLE ID xxx").
376
377 This procedure is called for every DDL stmt done in this package.
378 The stmt can be stored in a debug table by the below INSERT so that
379 you can re-execute it manually from sqlplus.
380
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),
390 type varchar2(10),
391 stmt varchar2(32000));
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
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 /*
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;
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
423 4) Re-define the alert in the Oracle Alert form, or 'touch' the
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
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
437 reversing the "uncommenting" of the debug code above and repeat
438 step 3 above.
439
440 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
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);
450
451 end if;
452
453 -- when exception
454 -- Allow caller procedure to handle exceptions.
455
456 end RUN_SQL;
457
458
459 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
460
461 procedure GET_TRIGGER_NAME(
462 TBL_NAME in varchar2,
463 EVENT_MODE in varchar2,
464 TBL_APPLID in number,
465 TRIGGER_NAME out NOCOPY varchar2)
466 is
467 v_table_id number;
468 v_trigger_name varchar2(100);
469 v_table_name varchar2(100);
470 v_trigger_name_orig varchar2(100);
471 begin
472
473 v_trigger_name_orig := 'ALR_' || substr(TBL_NAME, 1, 22) ||
474 '_' || EVENT_MODE || 'AR';
475
476 SELECT table_id
477 INTO v_table_id
478 FROM fnd_tables
479 WHERE table_name = TBL_NAME
480 AND application_id = TBL_APPLID;
481
482 v_trigger_name := 'ALR_' || substr(TBL_NAME, 1, 22 - LENGTH(TO_CHAR(TBL_APPLID)) - LENGTH(TO_CHAR(v_table_id))- 2) ||
483 '_' || TO_CHAR(TBL_APPLID) ||
484 '_' || TO_CHAR(v_table_id) ||
485 '_' || EVENT_MODE || 'AR';
486
487 SELECT table_name
488 INTO v_table_name
489 FROM user_triggers
490 WHERE trigger_name = v_trigger_name_orig;
491
492 -- check whether a new trigger must be created or not
493 if (v_table_name = TBL_NAME) then
494 TRIGGER_NAME := v_trigger_name_orig;
495 else
496 TRIGGER_NAME := v_trigger_name;
497 end if;
498
499 -- exception handling for select ... from user_triggers
500 exception
501 when no_data_found then -- trigger does not exist
502 TRIGGER_NAME := v_trigger_name;
503 end GET_TRIGGER_NAME;
504
505
506 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
507
508 procedure DROP_EVENT_DB_TRIGGER (ONAME in varchar2,
509 TRIGGER_NAME in varchar2) is
510
511 begin
512
513 ALR_DEBUG('--->> Entering DROP_EVENT_DB_TRIGGER');
514
515 /* JWSMITH BUG 568664*/
516 /* Added ONAME to drop trigger line to support MSOB */
517
518 RUN_SQL('drop', ONAME,
519 'drop trigger ' ||ONAME||'.'|| TRIGGER_NAME,
520 TRIGGER_NAME);
521
522 ALR_DEBUG('<<--- Leaving DROP_EVENT_DB_TRIGGER');
523
524 exception
525 when NONEXISTENT_TRIGGER then
526 -- Ignore...
527 NULL;
528
529 when others then
530 -- dbms_output.put_line( 'Error calling AD_DDL.do_ddl' );
531 -- dbms_output.put_line( sqlerrm );
532 raise;
533
534 end DROP_EVENT_DB_TRIGGER;
535
536
537 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
538
539 procedure DELETE_EVENT_DB_TRIGGER$2( -- prototype
540 APPL_ID in number,
541 ALR_ID in number,
542 TBL_APPLID in number,
543 TBL_NAME in varchar2,
544 OID in number,
545 ONAME in varchar2,
546 EVENT_MODE in varchar2);
547
548
549 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
550
551 procedure DELETE_EVENT_DB_TRIGGER$1(
552 APPL_ID in number,
553 ALR_ID in number,
554 TBL_APPLID in number,
555 TBL_NAME in varchar2,
556 OID in number,
557 INSERT_FLAG in varchar2,
558 UPDATE_FLAG in varchar2,
559 DELETE_FLAG in varchar2) is
560
561 cursor c1 is
562 select DISTINCT
563 ORACLE_USERNAME, O.ORACLE_ID
564 from FND_ORACLE_USERID O, ALR_ALERT_INSTALLATIONS I
565 where O.ORACLE_ID = NVL(OID, O.ORACLE_ID)
566 and O.ORACLE_ID = I.ORACLE_ID
567 and I.APPLICATION_ID = APPL_ID
568 and I.ALERT_ID = ALR_ID
569 order by ORACLE_USERNAME;
570
571 begin
572
573 ALR_DEBUG('--->> Entering DELETE_EVENT_DB_TRIGGER$1');
574
575 for CREC in c1 loop
576
577 if INSERT_FLAG = 'Y' then
578 -- 3933639 added if (OID is null) so triggers will be dropped for alerts
579 -- containing multiple installations when it should
580 if (OID is null) then
581 DELETE_EVENT_DB_TRIGGER$2(APPL_ID, ALR_ID, TBL_APPLID, TBL_NAME, null, crec.ORACLE_USERNAME, 'I');
582 -- end of 3933639, did this for the 2 following if statements as well
583 else
584 DELETE_EVENT_DB_TRIGGER$2(APPL_ID, ALR_ID, TBL_APPLID, TBL_NAME,
585 crec.ORACLE_ID, crec.ORACLE_USERNAME, 'I');
586 end if;
587 end if;
588
589 if UPDATE_FLAG = 'Y' then
590 if (OID is null) then
591 DELETE_EVENT_DB_TRIGGER$2(APPL_ID, ALR_ID, TBL_APPLID, TBL_NAME, null, crec.ORACLE_USERNAME, 'U');
592 else
593 DELETE_EVENT_DB_TRIGGER$2(APPL_ID, ALR_ID, TBL_APPLID, TBL_NAME,
594 crec.ORACLE_ID, crec.ORACLE_USERNAME, 'U');
595 end if;
596 end if;
597
598 if DELETE_FLAG = 'Y' then
599 if (OID is null) then
600 DELETE_EVENT_DB_TRIGGER$2(APPL_ID, ALR_ID, TBL_APPLID, TBL_NAME, null, crec.ORACLE_USERNAME, 'D');
601 else
602 DELETE_EVENT_DB_TRIGGER$2(APPL_ID, ALR_ID, TBL_APPLID, TBL_NAME,
603 crec.ORACLE_ID, crec.ORACLE_USERNAME, 'D');
604 end if;
605 end if;
606
607 end loop;
608
609 ALR_DEBUG('<<--- Leaving DELETE_EVENT_DB_TRIGGER$1');
610
611 exception
612 when NONEXISTENT_TRIGGER then
613 -- Ignore...
614 NULL;
615
616 when others then
617 FND_MESSAGE.SET_NAME('FND', 'SQL-Generic error');
618 FND_MESSAGE.SET_TOKEN('ERRNO', SQLCODE, FALSE);
619 FND_MESSAGE.SET_TOKEN('REASON', SQLERRM, FALSE);
620 FND_MESSAGE.SET_TOKEN(
621 'ROUTINE', 'DROP_EVENT_DB_TRIGGER', FALSE);
622 APP_EXCEPTION.RAISE_EXCEPTION;
623
624 end DELETE_EVENT_DB_TRIGGER$1;
625
626
627 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
628
629 procedure DELETE_EVENT_DB_TRIGGER$2(
630 APPL_ID in number,
631 ALR_ID in number,
632 TBL_APPLID in number,
633 TBL_NAME in varchar2,
634 OID in number, -- never NULL here
635 ONAME in varchar2,
636 EVENT_MODE in varchar2) is
637
638 TRIGGER_NAME varchar2(61);
639 enabled_count number;
640
641 begin
642
643 ALR_DEBUG('--->> Entering DELETE_EVENT_DB_TRIGGER$2');
644
645 -- Drop trigger if this is the only one alert;
646 -- otherwise, simply attempt to disable.
647
648 select count(*) into enabled_count
649 from alr_alerts a, alr_alert_installations i
650 where alert_condition_type='E'
651 and 'Y'=decode(EVENT_MODE, 'I', insert_flag,
652 'U', update_flag,
653 'D', delete_flag)
654 and table_application_id = TBL_APPLID
655 and table_name = TBL_NAME
656 and i.application_id = a.application_id
657 and i.alert_id = a.alert_id
658 and i.oracle_id = OID;
659
660 ALR_DEBUG('DELETE_EVENT_DB_TRIGGER$2: enabled_count='||enabled_count);
661
662 -- 3933639 added if (OID is null) so trigger will be dropped properly when
663 -- there are multiple installations for an alert
664 if (OID is null) then
665 -- Drop trigger
666 GET_TRIGGER_NAME(TBL_NAME, EVENT_MODE, TBL_APPLID, TRIGGER_NAME);
667 DROP_EVENT_DB_TRIGGER(ONAME, TRIGGER_NAME);
668 else
669 if enabled_count > 1 then
670 -- One or more other alert defined: attempt to disable trigger
671 -- (count includes the one record to be deleted)
672
673 NULL; -- to be done on POST-DELETE form trigger
674 -- CREATE_EVENT_DB_TRIGGER$1(APPL_ID, ALR_ID, TBL_APPLID,
675 -- TBL_NAME, OID, ONAME, EVENT_MODE, 'N');
676 else
677 -- Drop trigger
678 GET_TRIGGER_NAME(TBL_NAME, EVENT_MODE, TBL_APPLID, TRIGGER_NAME);
679 DROP_EVENT_DB_TRIGGER(ONAME, TRIGGER_NAME);
680 end if;
681 end if;
682
683 ALR_DEBUG('<<--- Leaving DELETE_EVENT_DB_TRIGGER$2');
684
685 end DELETE_EVENT_DB_TRIGGER$2;
686
687
688 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
689
690 procedure ALTER_EVENT_DB_TRIGGER$1(
691 APPL_ID in number,
692 ALR_ID in number,
693 TBL_APPLID in number,
694 TBL_NAME in varchar2,
695 OID in number,
696 ONAME in varchar2,
697 EVENT_MODE in varchar2,
698 IS_ENABLE in varchar2) is
699
700 TRG_NAME varchar2(61);
701 TRG_STATUS varchar2(10);
702 TRG_ACTION varchar2(10);
703 other_enabled_count number;
704
705 begin
706
707 ALR_DEBUG('--->> Entering ALTER_EVENT_DB_TRIGGER$1');
708
709 -- Default values
710 TRG_STATUS := 'UNDEFINED';
711 TRG_ACTION := NULL;
712
713 -- Generate a unique trigger name
714 GET_TRIGGER_NAME(TBL_NAME, EVENT_MODE, TBL_APPLID, TRG_NAME);
715
716 -- Determine if need to ENABLE or DISABLE the trigger:
717 -- enable if IS_ENABLE='Y' and trigger currently not enabled;
718 -- disable if IS_ENABLE='N' and trigger currently not disabled and
719 -- all other alerts are disabled.
720
721 if IS_ENABLE = 'Y' and TRG_STATUS <> 'ENABLED' then
722 TRG_ACTION := 'ENABLE';
723
724 elsif IS_ENABLE = 'N' and TRG_STATUS <> 'DISABLED' then
725
726 select count(*) into other_enabled_count
727 from alr_alerts a, alr_alert_installations i
728 where alert_condition_type='E'
729 and 'Y'=decode(EVENT_MODE, 'I', insert_flag,
730 'U', update_flag,
731 'D', delete_flag)
732 and a.enabled_flag = 'Y'
733 and i.enabled_flag = 'Y'
734 and table_application_id = TBL_APPLID
735 and table_name = TBL_NAME
736 and a.application_id = i.application_id
737 and a.alert_id = i.alert_id
738 and i.oracle_id = OID;
739
740 ALR_DEBUG('ALTER_EVENT_DB_TRIGGER$1: other_enabled_count='||other_enabled_count);
741
742 if other_enabled_count=0 then
743 TRG_ACTION := 'DISABLE';
744 end if;
745
746 end if;
747
748 /* JWSMITH BUG 568664*/
749 /* Added ONAME to alter trigger statement to support MSOB */
750
751 if TRG_ACTION is NOT NULL then
752 RUN_SQL('alter', ONAME,
753 'alter trigger '||ONAME||'.'||TRG_NAME||' '||TRG_ACTION,
754 TRG_NAME);
755 end if;
756
757 -- no exception - let caller handle
758
759 ALR_DEBUG('<<--- Leaving ALTER_EVENT_DB_TRIGGER$1');
760
761 end ALTER_EVENT_DB_TRIGGER$1;
762
763
764 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
765
766 procedure CREATE_EVENT_DB_TRIGGER$2(
767 TBL_APPLID in number,
768 TBL_NAME in varchar2,
769 OID in number,
770 ONAME in varchar2,
771 TBLNM in varchar2,
772 EVENT_MODE in varchar2,
773 TRGNAM in out NOCOPY varchar2) is
774
775 SQLSTMT VARCHAR2(32000);
776 TRIGGER_TYPE varchar2(30) := '';
777 TRIGGER_NAME varchar2(61);
778 TBLNM_I varchar2(30);
779
780 begin
781
782 ALR_DEBUG('--->> Entering CREATE_EVENT_DB_TRIGGER$2');
783
784 -- Default values
785 TRGNAM := NULL;
786
787 -- generate a unique trigger name
788 GET_TRIGGER_NAME(TBLNM, EVENT_MODE, TBL_APPLID, TRIGGER_NAME);
789 TRGNAM := TRIGGER_NAME;
790
791 -- select trigger type
792 if EVENT_MODE = 'I' then TRIGGER_TYPE:='insert';
793 elsif EVENT_MODE = 'U' then TRIGGER_TYPE:='update';
794 elsif EVENT_MODE = 'D' then TRIGGER_TYPE:='delete';
795 end if;
796
797 -- submit ALECTC to the Concurrent Manager
798 SQLSTMT :=
799 /* JWSMITH - BUG 568664*/
800 /* Added ONAME to create trigger statement to support MSOB */
801 'create or replace trigger ' || ONAME || '.' || TRGNAM ||
802 ' after ' || TRIGGER_TYPE || ' on ' || ONAME || '.' || TBLNM ||
803 ' for each row ' ||
804
805 'declare ' ||
806 'MAILID varchar2(255):=null;' ||
807 'REQID NUMBER; RETVAL boolean;' ||
808 'ORGID varchar2(255);' ||
809 'MORGID number;' ||
810 'l_security_profile_id ' ||
811 'fnd_profile_option_values.profile_option_value%TYPE;' ||
812 'l_org_id fnd_profile_option_values.profile_option_value%TYPE;' ||
813 'default_org_id fnd_profile_option_values.profile_option_value%TYPE;' ||
814
815 'begin ' ||
816
817 -- JWSMITH Bug 6996306 - no longer user client_infor for org_id
818 -- Check if trigger is fired from enabled installations
819 --'select rtrim(substr(userenv(''CLIENT_INFO''),1,10)) '||
820 -- 'into ORGID from dual;' ||
821
822 -- bug 6996306
823 'select nvl(mo_global.get_current_org_id, 0) into MORGID from dual;' ||
824 'if (MORGID = 0) then ' ||
825 ' fnd_profile.get(''XLA_MO_SECURITY_PROFILE_LEVEL'',
826 l_security_profile_id);' ||
827 ' if (l_security_profile_id is NULL) then ' ||
828 'fnd_profile.get(''ORG_ID'', l_org_id);' ||
829 'ORGID := l_org_id;' ||
830 ' else ' ||
831 'fnd_profile.get(''DEFAULT_ORG_ID'', default_org_id);' ||
832 'ORGID := default_org_id;' ||
833 ' end if;' ||
834 'else ' ||
835 'ORGID := TO_CHAR(MORGID);' ||
836 'end if;' ||
837
838 'if ('|| DISALLOWED_CONTEXT(TBL_APPLID, TBL_NAME, OID, EVENT_MODE) ||
839 ') then ' ||
840 'return;' ||
841 'end if;' ||
842
843 -- Check if required profiles are present -- otherwise,
844 -- assume that foreign system has fired trigger and
845 -- exit quietly.
846
847 'fnd_profile.get(''EMAIL_ADDRESS'',MAILID);' ||
848
849 'if MAILID is null then ' ||
850
851 'if alr_profile.value(''DEFAULT_USER_MAIL_ACCOUNT'')!=''O'' '||
852 'then ' ||
853 'fnd_profile.get(''USERNAME'',MAILID);' ||
854 'else ' ||
855 'fnd_profile.get(''SIGNONAUDIT:LOGIN_NAME'',MAILID);' ||
856 'end if;' ||
857
858 'if MAILID is null then ' ||
859 'MAILID:=''MAILID'';' ||
860 'end if;' ||
861
862 'end if;' ||
863
864 -- Indicate that we're calling from a database trigger
865 'RETVAL:=FND_REQUEST.SET_MODE(DB_TRIGGER => TRUE);' ||
866
867 -- Set IMPLICIT=ERROR. Ignore error status code, if any.
868 'RETVAL:=FND_REQUEST.SET_OPTIONS(IMPLICIT => ''ERROR'');' ||
869
870 -- Bug 9196056 - Submit from framework pages
871 'if fnd_global.resp_id = -1 then ' ||
872 'RETVAL := fnd_request.set_options(datagroup=>''Standard'');' ||
873 'end if;' ||
874
875 -- Finally submit the request
876 'REQID:=FND_REQUEST.SUBMIT_REQUEST(''ALR'',''ALECTC'',''' ||
877 TBLNM || ''',NULL,FALSE,USER,''' ||
878 TBLNM || ''',rowidtochar(:new.rowid)' ||
879 ',''' || event_mode || ''',mailid,ORGID);' ||
880
881 'if REQID=0 then ' ||
882 'raise_application_error(-20160, FND_MESSAGE.GET);' ||
883 'end if;' ||
884
885 'end;';
886
887 RUN_SQL('create', ONAME, SQLSTMT, TRGNAM);
888
889 ALR_DEBUG('<<--- Leaving CREATE_EVENT_DB_TRIGGER$2');
890
891 end CREATE_EVENT_DB_TRIGGER$2;
892
893
894 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
895
896 procedure CREATE_EVENT_DB_TRIGGER$1(
897 APPL_ID in number,
898 ALR_ID in number,
899 TBL_APPLID in number,
900 TBL_NAME in varchar2,
901 OID in number, -- null = all ORACLE IDs
902 ONAME in varchar2,
903 EVENT_MODE in varchar2,
904 ENABLED_FLAG in varchar2) is
905
906 TRGNAM varchar2(61);
907
908 begin
909
910 ALR_DEBUG('--->> Entering CREATE_EVENT_DB_TRIGGER$1');
911
912 -- Create a database trigger regardless if other alerts based on
913 -- this table already exist.
914
915 CREATE_EVENT_DB_TRIGGER$2(TBL_APPLID, TBL_NAME,
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) :=
925 ONAME;
926 end if;
927
928 if ENABLED_FLAG = 'N' then
929 -- May need to disable trigger
930
931 ALTER_EVENT_DB_TRIGGER$1(APPL_ID, ALR_ID, TBL_APPLID,
932 TBL_NAME, OID, ONAME, EVENT_MODE, 'N');
933 end if;
934
935 ALR_DEBUG('<<--- Leaving CREATE_EVENT_DB_TRIGGER$1');
936
937 end CREATE_EVENT_DB_TRIGGER$1;
938
939
940
941 -- ======================================================================
942 --
943 -- PUBLIC PROCEDURE/FUNCTIONS (entry points)
944 --
945 -- ======================================================================
946
947 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
948
949 procedure CREATE_EVENT_DB_TRIGGER(
950 APPL_ID in number,
951 ALR_ID in number,
952 TBL_APPLID in number,
953 TBL_NAME in varchar2,
954 OID in number, -- null = all ORACLE IDs
955 INSERT_FLAG in varchar2,
956 UPDATE_FLAG in varchar2,
957 DELETE_FLAG in varchar2,
958 IS_ENABLE in varchar2) is
959
960 cursor C is
961 select DISTINCT
962 ORACLE_USERNAME, O.ORACLE_ID, I.ENABLED_FLAG ENABLED_INST
963 from FND_ORACLE_USERID O, ALR_ALERT_INSTALLATIONS I
964 where O.ORACLE_ID = NVL(OID, O.ORACLE_ID)
965 and O.ORACLE_ID = I.ORACLE_ID
966 and I.APPLICATION_ID = NVL(APPL_ID, I.APPLICATION_ID)
967 and I.ALERT_ID = NVL(ALR_ID, I.ALERT_ID)
968 and ORACLE_USERNAME NOT LIKE '%_MRC'
969 and ORACLE_USERNAME NOT LIKE '%_CED'
970 and ORACLE_USERNAME NOT LIKE '%OBT_AA'
971 order by ORACLE_USERNAME;
972
973 ENABLED_FINAL varchar2(1);
974 ERROR_ROLLBACK exception;
975
976 begin
977
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
987 if IS_ENABLE = 'N' or crec.ENABLED_INST = 'N' then
988 ENABLED_FINAL := 'N';
989 else
990 ENABLED_FINAL := 'Y';
991 end if;
992
993 begin
994
995 if INSERT_FLAG = 'Y' then
996 CREATE_EVENT_DB_TRIGGER$1(APPL_ID, ALR_ID, TBL_APPLID, TBL_NAME,
997 crec.ORACLE_ID, crec.ORACLE_USERNAME, 'I', ENABLED_FINAL);
998 end if;
999
1000 if UPDATE_FLAG = 'Y' then
1001 CREATE_EVENT_DB_TRIGGER$1(APPL_ID, ALR_ID, TBL_APPLID, TBL_NAME,
1002 crec.ORACLE_ID, crec.ORACLE_USERNAME, 'U', ENABLED_FINAL);
1003 end if;
1004
1005 if DELETE_FLAG = 'Y' then
1006 CREATE_EVENT_DB_TRIGGER$1(APPL_ID, ALR_ID, TBL_APPLID, TBL_NAME,
1007 crec.ORACLE_ID, crec.ORACLE_USERNAME, 'D', ENABLED_FINAL);
1008 end if;
1009
1010 ALR_DEBUG('<<--- Leaving CREATE_EVENT_DB_TRIGGER');
1011
1012 exception
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 :=
1022 alr_dbtrigger.BAD_ORACLE_USERNAMES || crec.ORACLE_USERNAME;
1023
1024 when others then
1025
1026 raise ERROR_ROLLBACK;
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
1036 exception
1037
1038 when NONEXISTENT_TABLE then
1039
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
1049 -- Save the error code/msg first
1050 FND_MESSAGE.SET_NAME('FND', 'SQL-Generic error');
1051 FND_MESSAGE.SET_TOKEN('ERRNO', SQLCODE, FALSE);
1052 FND_MESSAGE.SET_TOKEN('REASON', SQLERRM, FALSE);
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
1062 -- dropping trigger off different accounts.
1063 end loop;
1064
1065 APP_EXCEPTION.RAISE_EXCEPTION;
1066
1067 when others then
1068
1069 FND_MESSAGE.SET_NAME('FND', 'SQL-Generic error');
1070 FND_MESSAGE.SET_TOKEN('ERRNO', SQLCODE, FALSE);
1071 FND_MESSAGE.SET_TOKEN('REASON', SQLERRM, FALSE);
1072 FND_MESSAGE.SET_TOKEN(
1073 'ROUTINE', 'CREATE_EVENT_DB_TRIGGER', FALSE);
1074 APP_EXCEPTION.RAISE_EXCEPTION;
1075
1076 end CREATE_EVENT_DB_TRIGGER;
1077
1078
1079 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1080
1081 procedure ALTER_EVENT_DB_TRIGGER(
1082 APPL_ID in number,
1083 ALR_ID in number,
1084 TBL_APPLID in number,
1085 TBL_NAME in varchar2,
1086 OID in number,
1087 INSERT_FLAG in varchar2,
1088 UPDATE_FLAG in varchar2,
1089 DELETE_FLAG in varchar2,
1090 IS_ENABLE in varchar2) is
1091
1092 begin
1093
1094 ALR_DEBUG('--->> Entering ALTER_EVENT_DB_TRIGGER');
1095
1096 -- Any updates affecting an event alert's active ORACLE IDs or
1097 -- installations should cause a re-creation of the database trigger.
1098 -- Hence, always call CREATE_EVENT_DB_TRIGGER.
1099
1100 CREATE_EVENT_DB_TRIGGER(APPL_ID, ALR_ID, TBL_APPLID, TBL_NAME, OID,
1101 INSERT_FLAG, UPDATE_FLAG, DELETE_FLAG, IS_ENABLE);
1102
1103 ALR_DEBUG('<<--- Leaving ALTER_EVENT_DB_TRIGGER');
1104
1105 end ALTER_EVENT_DB_TRIGGER;
1106
1107
1108 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1109
1110 procedure DELETE_EVENT_DB_TRIGGER(
1111 APPL_ID in number,
1112 ALR_ID in number,
1113 OID in number) is
1114
1115 TBL_APPLID ALR_ALERTS.TABLE_APPLICATION_ID%type;
1116 TBL_NAME ALR_ALERTS.TABLE_NAME%type;
1117 IS_INSERT ALR_ALERTS.INSERT_FLAG%type;
1118 IS_UPDATE ALR_ALERTS.UPDATE_FLAG%type;
1119 IS_DELETE ALR_ALERTS.DELETE_FLAG%type;
1120 IS_ENABLED ALR_ALERTS.ENABLED_FLAG%type;
1121
1122
1123 begin
1124
1125 ALR_DEBUG('--->> Entering DELETE_EVENT_DB_TRIGGER');
1126
1127 begin
1128 select TABLE_APPLICATION_ID, TABLE_NAME, INSERT_FLAG,
1129 UPDATE_FLAG, nvl(DELETE_FLAG, 'N'), ENABLED_FLAG
1130 into TBL_APPLID, TBL_NAME, IS_INSERT,
1131 IS_UPDATE, IS_DELETE, IS_ENABLED
1132 from ALR_ALERTS
1133 where ALERT_ID = ALR_ID AND APPLICATION_ID = APPL_ID
1134 and ALERT_CONDITION_TYPE = 'E';
1135
1136 exception
1137 when others then
1138 if sql%found then
1139 FND_MESSAGE.SET_NAME('FND', 'SQL-Generic error');
1140 FND_MESSAGE.SET_TOKEN('ERRNO', SQLCODE, FALSE);
1141 FND_MESSAGE.SET_TOKEN('REASON', SQLERRM, FALSE);
1142 FND_MESSAGE.SET_TOKEN(
1143 'ROUTINE', 'DELETE_EVENT_DB_TRIGGER', FALSE);
1144 APP_EXCEPTION.RAISE_EXCEPTION;
1145 end if;
1146 end;
1147
1148 if sql%found then
1149
1150 INIT_GLOBALS (TBL_APPLID);
1151
1152 DELETE_EVENT_DB_TRIGGER$1(
1153 APPL_ID, ALR_ID, TBL_APPLID, TBL_NAME, OID,
1154 IS_INSERT, IS_UPDATE, IS_DELETE);
1155
1156 end if;
1157
1158 ALR_DEBUG('<<--- Leaving DELETE_EVENT_DB_TRIGGER');
1159
1160 end DELETE_EVENT_DB_TRIGGER;
1161
1162
1163 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1164
1165 procedure PRE_UPDATE_EVENT_ALERT(
1166 APPL_ID in number,
1167 ALR_ID in number,
1168 NEW_TABLE_APPLID in number,
1169 NEW_TABLE_NAME in varchar2,
1170 NEW_INSERT_FLAG in varchar2,
1171 NEW_UPDATE_FLAG in varchar2,
1172 NEW_DELETE_FLAG in varchar2,
1173 NEW_IS_ENABLE in varchar2) is
1174
1175 OLD_TBLAPPLID alr_alerts.table_application_id%type;
1176 OLD_TBLNM alr_alerts.TABLE_NAME%type;
1177 OLD_IFLAG char;
1178 OLD_UFLAG char;
1179 OLD_DFLAG char;
1180 OLD_TYPE char;
1181 IS_NONEXISTENT_TABLE boolean := FALSE;
1182
1183 begin
1184
1185 ALR_DEBUG('--->> Entering PRE_UPDATE_EVENT_ALERT');
1186
1187 begin
1188 select TABLE_APPLICATION_ID, TABLE_NAME, INSERT_FLAG, UPDATE_FLAG,
1189 nvl(DELETE_FLAG, 'N'), ALERT_CONDITION_TYPE
1190 into OLD_TBLAPPLID, OLD_TBLNM, OLD_IFLAG, OLD_UFLAG,
1191 OLD_DFLAG, OLD_TYPE
1192 from ALR_ALERTS
1193 where APPLICATION_ID=APPL_ID and ALERT_ID=ALR_ID;
1194
1195 exception
1196 when others then
1197 if sql%found then
1198 FND_MESSAGE.SET_NAME('FND', 'SQL-Generic error');
1199 FND_MESSAGE.SET_TOKEN('ERRNO', SQLCODE, FALSE);
1200 FND_MESSAGE.SET_TOKEN('REASON', SQLERRM, FALSE);
1201 FND_MESSAGE.SET_TOKEN(
1202 'ROUTINE', 'PRE_UPDATE_EVENT_ALERT', FALSE);
1203 APP_EXCEPTION.RAISE_EXCEPTION;
1204 end if;
1205 end;
1206
1207 begin
1208
1209 -- No need to explicitly call INIT_GLOBALS;
1210 -- will be done in CREATE_EVENT_DB_TRIGGER().
1211 -- INIT_GLOBALS (TBL_APPLID);
1212
1213 CREATE_EVENT_DB_TRIGGER(APPL_ID, ALR_ID,
1214 NEW_TABLE_APPLID, NEW_TABLE_NAME,
1215 null, NEW_INSERT_FLAG, NEW_UPDATE_FLAG, NEW_DELETE_FLAG,
1216 NEW_IS_ENABLE);
1217
1218 exception
1219 when APPLICATION_ERROR then
1220 -- catch exception to avoid aborting
1221 IS_NONEXISTENT_TABLE := TRUE;
1222 when others then
1223 raise;
1224 end;
1225
1226 if OLD_TYPE = 'E' then -- :old.alert_condition_type = :new = 'Event'
1227
1228 if OLD_TBLNM = NEW_TABLE_NAME then -- table name didn't change
1229
1230 if OLD_IFLAG = 'Y' and NEW_INSERT_FLAG = 'Y' then
1231 OLD_IFLAG := 'N';
1232 end if;
1233 if OLD_UFLAG = 'Y' and NEW_UPDATE_FLAG = 'Y' then
1234 OLD_UFLAG := 'N';
1235 end if;
1236 if OLD_DFLAG = 'Y' and NEW_DELETE_FLAG = 'Y' then
1237 OLD_DFLAG := 'N';
1238 end if;
1239 end if;
1240
1241 -- Delete triggers for the old table
1242
1243 -- Need to explicitly call INIT_GLOBALS since APPLID may be
1244 -- different than that in previous CREATE_EVENT_DB_TRIGGER().
1245 INIT_GLOBALS (OLD_TBLAPPLID);
1246
1247 DELETE_EVENT_DB_TRIGGER$1(
1248 APPL_ID, ALR_ID, OLD_TBLAPPLID, OLD_TBLNM, null,
1249 OLD_IFLAG, OLD_UFLAG, OLD_DFLAG);
1250
1251 end if;
1252
1253 if IS_NONEXISTENT_TABLE then
1254 if NEW_TABLE_NAME = OLD_TBLNM then
1255 FND_MESSAGE.CLEAR;
1256 else
1257 APP_EXCEPTION.RAISE_EXCEPTION;
1258 end if;
1259 end if;
1260
1261 ALR_DEBUG('<<--- Leaving PRE_UPDATE_EVENT_ALERT');
1262
1263 end PRE_UPDATE_EVENT_ALERT;
1264
1265 begin
1266
1267 ALR_DEBUG_CLEAN;
1268
1269 end ALR_DBTRIGGER;
[Home] [Help]