1 package body ALR_DBTRIGGER as -- package body
2 /* $Header: ALREDBTB.pls 120.5.12010000.2 2008/11/14 21:35:51 jwsmith 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(2000);
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(2000);
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(2000));
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
428 regeneration of the ALR_DBTRIGGER package.)
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
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
580 if (OID is null) then
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
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
724 elsif IS_ENABLE = 'N' and TRG_STATUS <> 'DISABLED' then
721 if IS_ENABLE = 'Y' and TRG_STATUS <> 'ENABLED' then
722 TRG_ACTION := 'ENABLE';
723
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(2000);
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 'if fnd_profile.value(''RESP_ID'') is not null then ' ||
848 'fnd_profile.get(''EMAIL_ADDRESS'',MAILID);' ||
849
850 'if MAILID is null then ' ||
851
852 'if alr_profile.value(''DEFAULT_USER_MAIL_ACCOUNT'')!=''O'' '||
853 'then ' ||
854 'fnd_profile.get(''USERNAME'',MAILID);' ||
855 'else ' ||
856 'fnd_profile.get(''SIGNONAUDIT:LOGIN_NAME'',MAILID);' ||
857 'end if;' ||
858
862
859 'if MAILID is null then ' ||
860 'MAILID:=''MAILID'';' ||
861 'end if;' ||
863 'end if;' ||
864
865 -- Indicate that we're calling from a database trigger
866 'RETVAL:=FND_REQUEST.SET_MODE(DB_TRIGGER => TRUE);' ||
867
868 -- Set IMPLICIT=ERROR. Ignore error status code, if any.
869 'RETVAL:=FND_REQUEST.SET_OPTIONS(IMPLICIT => ''ERROR'');' ||
870
871 -- Finally submit the request
872 'REQID:=FND_REQUEST.SUBMIT_REQUEST(''ALR'',''ALECTC'',''' ||
873 TBLNM || ''',NULL,FALSE,USER,''' ||
874 TBLNM || ''',rowidtochar(:new.rowid)' ||
875 ',''' || event_mode || ''',mailid,ORGID);' ||
876
877 'if REQID=0 then ' ||
878 'raise_application_error(-20160, FND_MESSAGE.GET);' ||
879 'end if;' ||
880
881 'end if;' ||
882
883 'end;';
884
885 RUN_SQL('create', ONAME, SQLSTMT, TRGNAM);
886
887 ALR_DEBUG('<<--- Leaving CREATE_EVENT_DB_TRIGGER$2');
888
889 end CREATE_EVENT_DB_TRIGGER$2;
890
891
892 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
893
894 procedure CREATE_EVENT_DB_TRIGGER$1(
895 APPL_ID in number,
896 ALR_ID in number,
897 TBL_APPLID in number,
898 TBL_NAME in varchar2,
899 OID in number, -- null = all ORACLE IDs
900 ONAME in varchar2,
901 EVENT_MODE in varchar2,
902 ENABLED_FLAG in varchar2) is
903
904 TRGNAM varchar2(61);
905
906 begin
907
908 ALR_DEBUG('--->> Entering CREATE_EVENT_DB_TRIGGER$1');
909
910 -- Create a database trigger regardless if other alerts based on
911 -- this table already exist.
912
913 CREATE_EVENT_DB_TRIGGER$2(TBL_APPLID, TBL_NAME,
914 OID, ONAME, TBL_NAME, EVENT_MODE, TRGNAM);
915
916 if TRGNAM is not NULL then
917 -- Trigger created successfully (enabled)
918 alr_dbtrigger.NUMBER_OF_TRIGGERS :=
919 alr_dbtrigger.NUMBER_OF_TRIGGERS + 1;
920 alr_dbtrigger.CREATED_TRIGGERS(alr_dbtrigger.NUMBER_OF_TRIGGERS) :=
921 TRGNAM;
922 alr_dbtrigger.CREATED_TRIG_ONAME(alr_dbtrigger.NUMBER_OF_TRIGGERS) :=
923 ONAME;
924 end if;
925
926 if ENABLED_FLAG = 'N' then
927 -- May need to disable trigger
928
929 ALTER_EVENT_DB_TRIGGER$1(APPL_ID, ALR_ID, TBL_APPLID,
930 TBL_NAME, OID, ONAME, EVENT_MODE, 'N');
931 end if;
932
933 ALR_DEBUG('<<--- Leaving CREATE_EVENT_DB_TRIGGER$1');
934
935 end CREATE_EVENT_DB_TRIGGER$1;
936
937
938
939 -- ======================================================================
940 --
941 -- PUBLIC PROCEDURE/FUNCTIONS (entry points)
942 --
943 -- ======================================================================
944
945 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
946
947 procedure CREATE_EVENT_DB_TRIGGER(
948 APPL_ID in number,
949 ALR_ID in number,
950 TBL_APPLID in number,
951 TBL_NAME in varchar2,
952 OID in number, -- null = all ORACLE IDs
953 INSERT_FLAG in varchar2,
954 UPDATE_FLAG in varchar2,
955 DELETE_FLAG in varchar2,
956 IS_ENABLE in varchar2) is
957
958 cursor C is
959 select DISTINCT
960 ORACLE_USERNAME, O.ORACLE_ID, I.ENABLED_FLAG ENABLED_INST
961 from FND_ORACLE_USERID O, ALR_ALERT_INSTALLATIONS I
962 where O.ORACLE_ID = NVL(OID, O.ORACLE_ID)
963 and O.ORACLE_ID = I.ORACLE_ID
964 and I.APPLICATION_ID = NVL(APPL_ID, I.APPLICATION_ID)
965 and I.ALERT_ID = NVL(ALR_ID, I.ALERT_ID)
966 and ORACLE_USERNAME NOT LIKE '%_MRC'
967 and ORACLE_USERNAME NOT LIKE '%_CED'
968 order by ORACLE_USERNAME;
969
970 ENABLED_FINAL varchar2(1);
971 ERROR_ROLLBACK exception;
972
973 begin
974
975 ALR_DEBUG('--->> Entering CREATE_EVENT_DB_TRIGGER');
976
977 INIT_GLOBALS (TBL_APPLID);
978
979 alr_dbtrigger.NUMBER_OF_TRIGGERS:=0;
980 alr_dbtrigger.BAD_ORACLE_USERNAMES:='';
981
982 for CREC in C loop -- process all ORACLE IDs
983
984 if IS_ENABLE = 'N' or crec.ENABLED_INST = 'N' then
985 ENABLED_FINAL := 'N';
986 else
987 ENABLED_FINAL := 'Y';
988 end if;
989
990 begin
991
992 if INSERT_FLAG = 'Y' then
993 CREATE_EVENT_DB_TRIGGER$1(APPL_ID, ALR_ID, TBL_APPLID, TBL_NAME,
994 crec.ORACLE_ID, crec.ORACLE_USERNAME, 'I', ENABLED_FINAL);
995 end if;
996
997 if UPDATE_FLAG = 'Y' then
998 CREATE_EVENT_DB_TRIGGER$1(APPL_ID, ALR_ID, TBL_APPLID, TBL_NAME,
999 crec.ORACLE_ID, crec.ORACLE_USERNAME, 'U', ENABLED_FINAL);
1000 end if;
1001
1002 if DELETE_FLAG = 'Y' then
1003 CREATE_EVENT_DB_TRIGGER$1(APPL_ID, ALR_ID, TBL_APPLID, TBL_NAME,
1004 crec.ORACLE_ID, crec.ORACLE_USERNAME, 'D', ENABLED_FINAL);
1005 end if;
1006
1007 ALR_DEBUG('<<--- Leaving CREATE_EVENT_DB_TRIGGER');
1008
1009 exception
1010
1011 when NONEXISTENT_TABLE or PLSQL_UNCOMPILED then
1012
1016 alr_dbtrigger.BAD_ORACLE_USERNAMES||', ';
1013 -- Save ORACLE ID to display to user
1014 if alr_dbtrigger.BAD_ORACLE_USERNAMES is NOT NULL then
1015 alr_dbtrigger.BAD_ORACLE_USERNAMES :=
1017 end if;
1018 alr_dbtrigger.BAD_ORACLE_USERNAMES :=
1019 alr_dbtrigger.BAD_ORACLE_USERNAMES || crec.ORACLE_USERNAME;
1020
1021 when others then
1022
1023 raise ERROR_ROLLBACK;
1024 end;
1025
1026 end loop;
1027
1028 if alr_dbtrigger.BAD_ORACLE_USERNAMES is NOT NULL then
1029
1030 raise NONEXISTENT_TABLE;
1031 end if;
1032
1033 exception
1034
1035 when NONEXISTENT_TABLE then
1036
1037 FND_MESSAGE.SET_NAME('ALR', 'TRIGGER-NO TABLE IN ACCOUNT');
1038 -- this message is uppercased in fnd_new_messages and fnd_messages
1039 FND_MESSAGE.SET_TOKEN('TABLE_NAME', TBL_NAME);
1040 FND_MESSAGE.SET_TOKEN('ORACLE_USERNAME',
1041 alr_dbtrigger.BAD_ORACLE_USERNAMES, FALSE);
1042 APP_EXCEPTION.RAISE_EXCEPTION;
1043
1044 when ERROR_ROLLBACK then
1045
1046 -- Save the error code/msg first
1047 FND_MESSAGE.SET_NAME('FND', 'SQL-Generic error');
1048 FND_MESSAGE.SET_TOKEN('ERRNO', SQLCODE, FALSE);
1049 FND_MESSAGE.SET_TOKEN('REASON', SQLERRM, FALSE);
1050 FND_MESSAGE.SET_TOKEN(
1051 'ROUTINE', 'CREATE_EVENT_DB_TRIGGER', FALSE);
1052
1053 -- "Rollback"
1054 for I in 1 .. alr_dbtrigger.NUMBER_OF_TRIGGERS loop
1055 DROP_EVENT_DB_TRIGGER
1056 (alr_dbtrigger.CREATED_TRIG_ONAME(I),
1057 alr_dbtrigger.CREATED_TRIGGERS(I));
1058 -- Note that table appl is the same even when
1059 -- dropping trigger off different accounts.
1060 end loop;
1061
1062 APP_EXCEPTION.RAISE_EXCEPTION;
1063
1064 when others then
1065
1066 FND_MESSAGE.SET_NAME('FND', 'SQL-Generic error');
1067 FND_MESSAGE.SET_TOKEN('ERRNO', SQLCODE, FALSE);
1068 FND_MESSAGE.SET_TOKEN('REASON', SQLERRM, FALSE);
1069 FND_MESSAGE.SET_TOKEN(
1070 'ROUTINE', 'CREATE_EVENT_DB_TRIGGER', FALSE);
1071 APP_EXCEPTION.RAISE_EXCEPTION;
1072
1073 end CREATE_EVENT_DB_TRIGGER;
1074
1075
1076 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1077
1078 procedure ALTER_EVENT_DB_TRIGGER(
1079 APPL_ID in number,
1080 ALR_ID in number,
1081 TBL_APPLID in number,
1082 TBL_NAME in varchar2,
1083 OID in number,
1084 INSERT_FLAG in varchar2,
1085 UPDATE_FLAG in varchar2,
1086 DELETE_FLAG in varchar2,
1087 IS_ENABLE in varchar2) is
1088
1089 begin
1090
1091 ALR_DEBUG('--->> Entering ALTER_EVENT_DB_TRIGGER');
1092
1093 -- Any updates affecting an event alert's active ORACLE IDs or
1094 -- installations should cause a re-creation of the database trigger.
1095 -- Hence, always call CREATE_EVENT_DB_TRIGGER.
1096
1097 CREATE_EVENT_DB_TRIGGER(APPL_ID, ALR_ID, TBL_APPLID, TBL_NAME, OID,
1098 INSERT_FLAG, UPDATE_FLAG, DELETE_FLAG, IS_ENABLE);
1099
1100 ALR_DEBUG('<<--- Leaving ALTER_EVENT_DB_TRIGGER');
1101
1102 end ALTER_EVENT_DB_TRIGGER;
1103
1104
1105 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1106
1107 procedure DELETE_EVENT_DB_TRIGGER(
1108 APPL_ID in number,
1109 ALR_ID in number,
1110 OID in number) is
1111
1112 TBL_APPLID ALR_ALERTS.TABLE_APPLICATION_ID%type;
1113 TBL_NAME ALR_ALERTS.TABLE_NAME%type;
1114 IS_INSERT ALR_ALERTS.INSERT_FLAG%type;
1115 IS_UPDATE ALR_ALERTS.UPDATE_FLAG%type;
1116 IS_DELETE ALR_ALERTS.DELETE_FLAG%type;
1117 IS_ENABLED ALR_ALERTS.ENABLED_FLAG%type;
1118
1119
1120 begin
1121
1122 ALR_DEBUG('--->> Entering DELETE_EVENT_DB_TRIGGER');
1123
1124 begin
1125 select TABLE_APPLICATION_ID, TABLE_NAME, INSERT_FLAG,
1126 UPDATE_FLAG, nvl(DELETE_FLAG, 'N'), ENABLED_FLAG
1127 into TBL_APPLID, TBL_NAME, IS_INSERT,
1128 IS_UPDATE, IS_DELETE, IS_ENABLED
1129 from ALR_ALERTS
1130 where ALERT_ID = ALR_ID AND APPLICATION_ID = APPL_ID
1131 and ALERT_CONDITION_TYPE = 'E';
1132
1133 exception
1134 when others then
1135 if sql%found then
1136 FND_MESSAGE.SET_NAME('FND', 'SQL-Generic error');
1137 FND_MESSAGE.SET_TOKEN('ERRNO', SQLCODE, FALSE);
1138 FND_MESSAGE.SET_TOKEN('REASON', SQLERRM, FALSE);
1139 FND_MESSAGE.SET_TOKEN(
1140 'ROUTINE', 'DELETE_EVENT_DB_TRIGGER', FALSE);
1141 APP_EXCEPTION.RAISE_EXCEPTION;
1142 end if;
1143 end;
1144
1145 if sql%found then
1146
1147 INIT_GLOBALS (TBL_APPLID);
1148
1149 DELETE_EVENT_DB_TRIGGER$1(
1150 APPL_ID, ALR_ID, TBL_APPLID, TBL_NAME, OID,
1151 IS_INSERT, IS_UPDATE, IS_DELETE);
1152
1153 end if;
1154
1155 ALR_DEBUG('<<--- Leaving DELETE_EVENT_DB_TRIGGER');
1156
1157 end DELETE_EVENT_DB_TRIGGER;
1158
1159
1160 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1161
1162 procedure PRE_UPDATE_EVENT_ALERT(
1163 APPL_ID in number,
1164 ALR_ID in number,
1165 NEW_TABLE_APPLID in number,
1166 NEW_TABLE_NAME in varchar2,
1170 NEW_IS_ENABLE in varchar2) is
1167 NEW_INSERT_FLAG in varchar2,
1168 NEW_UPDATE_FLAG in varchar2,
1169 NEW_DELETE_FLAG in varchar2,
1171
1172 OLD_TBLAPPLID alr_alerts.table_application_id%type;
1173 OLD_TBLNM alr_alerts.TABLE_NAME%type;
1174 OLD_IFLAG char;
1175 OLD_UFLAG char;
1176 OLD_DFLAG char;
1177 OLD_TYPE char;
1178 IS_NONEXISTENT_TABLE boolean := FALSE;
1179
1180 begin
1181
1182 ALR_DEBUG('--->> Entering PRE_UPDATE_EVENT_ALERT');
1183
1184 begin
1185 select TABLE_APPLICATION_ID, TABLE_NAME, INSERT_FLAG, UPDATE_FLAG,
1186 nvl(DELETE_FLAG, 'N'), ALERT_CONDITION_TYPE
1187 into OLD_TBLAPPLID, OLD_TBLNM, OLD_IFLAG, OLD_UFLAG,
1188 OLD_DFLAG, OLD_TYPE
1189 from ALR_ALERTS
1190 where APPLICATION_ID=APPL_ID and ALERT_ID=ALR_ID;
1191
1192 exception
1193 when others then
1194 if sql%found then
1195 FND_MESSAGE.SET_NAME('FND', 'SQL-Generic error');
1196 FND_MESSAGE.SET_TOKEN('ERRNO', SQLCODE, FALSE);
1197 FND_MESSAGE.SET_TOKEN('REASON', SQLERRM, FALSE);
1198 FND_MESSAGE.SET_TOKEN(
1199 'ROUTINE', 'PRE_UPDATE_EVENT_ALERT', FALSE);
1200 APP_EXCEPTION.RAISE_EXCEPTION;
1201 end if;
1202 end;
1203
1204 begin
1205
1206 -- No need to explicitly call INIT_GLOBALS;
1207 -- will be done in CREATE_EVENT_DB_TRIGGER().
1208 -- INIT_GLOBALS (TBL_APPLID);
1209
1210 CREATE_EVENT_DB_TRIGGER(APPL_ID, ALR_ID,
1211 NEW_TABLE_APPLID, NEW_TABLE_NAME,
1212 null, NEW_INSERT_FLAG, NEW_UPDATE_FLAG, NEW_DELETE_FLAG,
1213 NEW_IS_ENABLE);
1214
1215 exception
1216 when APPLICATION_ERROR then
1217 -- catch exception to avoid aborting
1218 IS_NONEXISTENT_TABLE := TRUE;
1219 when others then
1220 raise;
1221 end;
1222
1223 if OLD_TYPE = 'E' then -- :old.alert_condition_type = :new = 'Event'
1224
1225 if OLD_TBLNM = NEW_TABLE_NAME then -- table name didn't change
1226
1227 if OLD_IFLAG = 'Y' and NEW_INSERT_FLAG = 'Y' then
1228 OLD_IFLAG := 'N';
1229 end if;
1230 if OLD_UFLAG = 'Y' and NEW_UPDATE_FLAG = 'Y' then
1231 OLD_UFLAG := 'N';
1232 end if;
1233 if OLD_DFLAG = 'Y' and NEW_DELETE_FLAG = 'Y' then
1234 OLD_DFLAG := 'N';
1235 end if;
1236 end if;
1237
1238 -- Delete triggers for the old table
1239
1240 -- Need to explicitly call INIT_GLOBALS since APPLID may be
1241 -- different than that in previous CREATE_EVENT_DB_TRIGGER().
1242 INIT_GLOBALS (OLD_TBLAPPLID);
1243
1244 DELETE_EVENT_DB_TRIGGER$1(
1245 APPL_ID, ALR_ID, OLD_TBLAPPLID, OLD_TBLNM, null,
1246 OLD_IFLAG, OLD_UFLAG, OLD_DFLAG);
1247
1248 end if;
1249
1250 if IS_NONEXISTENT_TABLE then
1251 if NEW_TABLE_NAME = OLD_TBLNM then
1252 FND_MESSAGE.CLEAR;
1253 else
1254 APP_EXCEPTION.RAISE_EXCEPTION;
1255 end if;
1256 end if;
1257
1258 ALR_DEBUG('<<--- Leaving PRE_UPDATE_EVENT_ALERT');
1259
1260 end PRE_UPDATE_EVENT_ALERT;
1261
1262 begin
1263
1264 ALR_DEBUG_CLEAN;
1265
1266 end ALR_DBTRIGGER;
[Home] [Help]