[Home] [Help]
PACKAGE BODY: APPS.XLA_EVENT_CLASSES_F_PKG
Source
1 PACKAGE BODY xla_event_classes_f_pkg AS
2 /* $Header: xlathevc.pkb 120.18.12010000.1 2008/07/29 10:09:35 appldev ship $ */
3 /*======================================================================+
4 | Copyright (c) 2001-2002 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +=======================================================================+
8 | PACKAGE NAME |
9 | xla_event_classes |
10 | |
11 | DESCRIPTION |
12 | Forms PL/SQL Wrapper for xla_event_classes |
13 | |
14 | HISTORY |
15 | Generated from XLAUTB. |
16 | |
17 +======================================================================*/
18
19
20 --=============================================================================
21 -- *********** Local Trace Routine **********
22 --=============================================================================
23 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
24 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
25 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
26 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
27 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
28 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
29
30 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
31 C_DEFAULT_MODULE CONSTANT VARCHAR2(240) := 'xla.plsql.xla_event_classes_f_pkg';
32
33 g_debug_flag VARCHAR2(1) :=
34 NVL(fnd_profile.value('XLA_DEBUG_TRACE'),'N');
35
36 g_log_level NUMBER;
37 g_log_enabled BOOLEAN;
38
39 PROCEDURE trace
40 (p_msg IN VARCHAR2
41 ,p_module IN VARCHAR2
42 ,p_level IN NUMBER) IS
43 BEGIN
44 ----------------------------------------------------------------------------
45 -- Following is for FND log.
46 ----------------------------------------------------------------------------
47 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
48 fnd_log.message(p_level, p_module);
49 ELSIF p_level >= g_log_level THEN
50 fnd_log.string(p_level, p_module, p_msg);
51 END IF;
52
53 EXCEPTION
54 WHEN xla_exceptions_pkg.application_exception THEN
55 RAISE;
56
57 WHEN OTHERS THEN
58 xla_exceptions_pkg.raise_message
59 (p_location => 'xla_event_classes_f_pkg.trace');
60 END trace;
61
62 /*======================================================================+
63 | |
64 | Procedure insert_row |
65 | |
66 +======================================================================*/
67 PROCEDURE insert_row
68 (x_rowid IN OUT NOCOPY VARCHAR2
69 ,x_application_id IN NUMBER
70 ,x_entity_code IN VARCHAR2
71 ,x_event_class_code IN VARCHAR2
72 ,x_enabled_flag IN VARCHAR2
73 ,x_name IN VARCHAR2
74 ,x_description IN VARCHAR2
75 ,x_creation_date IN DATE
76 ,x_created_by IN NUMBER
77 ,x_last_update_date IN DATE
78 ,x_last_updated_by IN NUMBER
79 ,x_last_update_login IN NUMBER)
80
81 IS
82
83 CURSOR c IS
84 SELECT rowid
85 FROM xla_event_classes_b
86 WHERE application_id = x_application_id
87 AND entity_code = x_entity_code
88 AND event_class_code = x_event_class_code
89 ;
90
91 l_log_module VARCHAR2(240);
92 BEGIN
93
94 IF g_log_enabled THEN
95 l_log_module := C_DEFAULT_MODULE||'.insert_row';
96 END IF;
97
98 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
99 trace(p_msg => 'BEGIN of procedure insert_row',
100 p_module => l_log_module,
101 p_level => C_LEVEL_PROCEDURE);
102 END IF;
103
104
105 INSERT INTO xla_event_classes_b
106 (creation_date
107 ,created_by
108 ,application_id
109 ,entity_code
110 ,event_class_code
111 ,enabled_flag
112 ,last_update_date
113 ,last_updated_by
114 ,last_update_login)
115 VALUES
116 (x_creation_date
117 ,x_created_by
118 ,x_application_id
119 ,x_entity_code
120 ,x_event_class_code
121 ,x_enabled_flag
122 ,x_last_update_date
123 ,x_last_updated_by
124 ,x_last_update_login)
125 ;
126
127 INSERT INTO xla_event_classes_tl
128 (creation_date
129 ,entity_code
130 ,event_class_code
131 ,name
132 ,description
133 ,application_id
134 ,last_update_date
135 ,last_updated_by
136 ,last_update_login
137 ,created_by
138 ,language
139 ,source_lang)
140 SELECT
141 x_creation_date
142 ,x_entity_code
143 ,x_event_class_code
144 ,x_name
145 ,x_description
146 ,x_application_id
147 ,x_last_update_date
148 ,x_last_updated_by
149 ,x_last_update_login
150 ,x_created_by
151 ,l.language_code
152 ,USERENV('LANG')
153 FROM fnd_languages l
154 WHERE l.installed_flag IN ('I', 'B')
155 AND NOT EXISTS
156 (SELECT NULL
157 FROM xla_event_classes_tl t
158 WHERE t.application_id = x_application_id
159 AND t.entity_code = x_entity_code
160 AND t.event_class_code = x_event_class_code
161 AND t.language = l.language_code);
162
163 OPEN c;
164 FETCH c INTO x_rowid;
165
166 IF (c%NOTFOUND) THEN
167 CLOSE c;
168 RAISE NO_DATA_FOUND;
169 END IF;
170 CLOSE c;
171
172 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
173 trace(p_msg => 'END of procedure insert_row',
174 p_module => l_log_module,
175 p_level => C_LEVEL_PROCEDURE);
176 END IF;
177
178 END insert_row;
179
180 /*======================================================================+
181 | |
182 | Procedure lock_row |
183 | |
184 +======================================================================*/
185 PROCEDURE lock_row
186 (x_application_id IN NUMBER
187 ,x_entity_code IN VARCHAR2
188 ,x_event_class_code IN VARCHAR2
189 ,x_enabled_flag IN VARCHAR2
190 ,x_name IN VARCHAR2
191 ,x_description IN VARCHAR2)
192
193 IS
194
195 CURSOR c IS
196 SELECT application_id
197 ,enabled_flag
198 FROM xla_event_classes_b
199 WHERE application_id = x_application_id
200 AND entity_code = x_entity_code
201 AND event_class_code = x_event_class_code
202 FOR UPDATE OF application_id NOWAIT;
203
204 recinfo c%ROWTYPE;
205
206 CURSOR c1 IS
207 SELECT source_lang
208 ,name
209 ,description
210 ,DECODE(language , USERENV('LANG'), 'Y', 'N') baselang
211 FROM xla_event_classes_tl
212 WHERE application_id = X_application_id
213 AND entity_code = X_entity_code
214 AND event_class_code = X_event_class_code
215 AND USERENV('LANG') IN (language ,source_lang)
216 FOR UPDATE OF application_id NOWAIT;
217
218 l_log_module VARCHAR2(240);
219 BEGIN
220
221 IF g_log_enabled THEN
222 l_log_module := C_DEFAULT_MODULE||'.lock_row';
223 END IF;
224
225 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
226 trace(p_msg => 'BEGIN of procedure lock_row',
227 p_module => l_log_module,
228 p_level => C_LEVEL_PROCEDURE);
229 END IF;
230
231 OPEN c;
232 FETCH c INTO recinfo;
233
234 IF (c%NOTFOUND) THEN
235 CLOSE c;
236 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
237 app_exception.raise_exception;
238 END IF;
239 CLOSE c;
240
241 IF ( (recinfo.enabled_flag = x_enabled_flag)
242 ) THEN
243 NULL;
244 ELSE
245 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
246 app_exception.raise_exception;
247 END IF;
248
249 FOR tlinfo IN c1 LOOP
250 IF (tlinfo.baselang = 'Y') THEN
251 IF ( (tlinfo.name = X_name)
252 AND ((tlinfo.description = X_description)
253 OR ((tlinfo.description is null)
254 AND (X_description is null)))
255 ) THEN
256 NULL;
257 ELSE
258 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
259 app_exception.raise_exception;
260 END IF;
261 END IF;
262 END LOOP;
263
264 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
265 trace(p_msg => 'END of procedure lock_row',
266 p_module => l_log_module,
267 p_level => C_LEVEL_PROCEDURE);
268 END IF;
269
270 RETURN;
271
272 END lock_row;
273
274 /*======================================================================+
275 | |
276 | Procedure update_row |
277 | |
278 +======================================================================*/
279 PROCEDURE update_row
280 (x_application_id IN NUMBER
281 ,x_entity_code IN VARCHAR2
282 ,x_event_class_code IN VARCHAR2
283 ,x_enabled_flag IN VARCHAR2
284 ,x_name IN VARCHAR2
285 ,x_description IN VARCHAR2
286 ,x_last_update_date IN DATE
287 ,x_last_updated_by IN NUMBER
288 ,x_last_update_login IN NUMBER)
289
290 IS
291
292 l_log_module VARCHAR2(240);
293 BEGIN
294
295 IF g_log_enabled THEN
296 l_log_module := C_DEFAULT_MODULE||'.update_row';
297 END IF;
298
299 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
300 trace(p_msg => 'BEGIN of procedure update_row',
301 p_module => l_log_module,
302 p_level => C_LEVEL_PROCEDURE);
303 END IF;
304
305 UPDATE xla_event_classes_b
306 SET
307 last_update_date = x_last_update_date
308 ,enabled_flag = x_enabled_flag
309 ,last_updated_by = x_last_updated_by
310 ,last_update_login = x_last_update_login
311 WHERE application_id = X_application_id
312 AND entity_code = X_entity_code
313 AND event_class_code = X_event_class_code;
314
315 IF (SQL%NOTFOUND) THEN
316 RAISE NO_DATA_FOUND;
317 END IF;
318
319 UPDATE xla_event_classes_tl
320 SET
321 last_update_date = x_last_update_date
322 ,name = X_name
323 ,description = X_description
324 ,last_updated_by = x_last_updated_by
325 ,last_update_login = x_last_update_login
326 ,source_lang = USERENV('LANG')
327 WHERE application_id = X_application_id
328 AND entity_code = X_entity_code
329 AND event_class_code = X_event_class_code
330 AND USERENV('LANG') IN (language, source_lang);
331
332 IF (SQL%NOTFOUND) THEN
333 RAISE NO_DATA_FOUND;
334 END IF;
335
336 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
337 trace(p_msg => 'END of procedure update_row',
338 p_module => l_log_module,
339 p_level => C_LEVEL_PROCEDURE);
340 END IF;
341
342 END update_row;
343
344 /*======================================================================+
345 | |
346 | Procedure delete_row |
347 | |
348 +======================================================================*/
349 PROCEDURE delete_row
350 (x_application_id IN NUMBER
351 ,x_entity_code IN VARCHAR2
352 ,x_event_class_code IN VARCHAR2)
353
354 IS
355
356 l_log_module VARCHAR2(240);
357 BEGIN
358
359 IF g_log_enabled THEN
360 l_log_module := C_DEFAULT_MODULE||'.delete_row';
361 END IF;
362
363 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
364 trace(p_msg => 'BEGIN of procedure delete_row',
365 p_module => l_log_module,
366 p_level => C_LEVEL_PROCEDURE);
367 END IF;
368
369 DELETE FROM xla_event_classes_tl
370 WHERE application_id = x_application_id
371 AND entity_code = x_entity_code
372 AND event_class_code = x_event_class_code;
373
374
375 IF (SQL%NOTFOUND) THEN
376 RAISE NO_DATA_FOUND;
377 END IF;
378
379 DELETE FROM xla_event_classes_b
380 WHERE application_id = x_application_id
381 AND entity_code = x_entity_code
385 IF (SQL%NOTFOUND) THEN
382 AND event_class_code = x_event_class_code;
383
384
386 RAISE NO_DATA_FOUND;
387 END IF;
388
389 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
390 trace(p_msg => 'END of procedure delete_row',
391 p_module => l_log_module,
392 p_level => C_LEVEL_PROCEDURE);
393 END IF;
394
395 END delete_row;
396
397 /*======================================================================+
398 | |
399 | Procedure add_language |
400 | |
401 +======================================================================*/
402 PROCEDURE add_language
403 IS
404 l_log_module VARCHAR2(240);
405 BEGIN
406
407 IF g_log_enabled THEN
408 l_log_module := C_DEFAULT_MODULE||'.add_language';
409 END IF;
410
411 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
412 trace(p_msg => 'BEGIN of procedure add_language',
413 p_module => l_log_module,
414 p_level => C_LEVEL_PROCEDURE);
415 END IF;
416
417 DELETE FROM xla_event_classes_tl T
418 WHERE NOT EXISTS
419 (SELECT NULL
420 FROM xla_event_classes_b b
421 WHERE b.application_id = t.application_id
422 AND b.entity_code = t.entity_code
423 AND b.event_class_code = t.event_class_code);
424
425 UPDATE xla_event_classes_tl t
426 SET (name
427 ,description)
428 = (SELECT b.name
429 ,b.description
430 FROM xla_event_classes_tl b
431 WHERE b.application_id = t.application_id
432 AND b.entity_code = t.entity_code
433 AND b.event_class_code = t.event_class_code
434 AND b.language = t.source_lang)
435 WHERE (t.application_id
436 ,t.entity_code
437 ,t.event_class_code
438 ,t.language)
439 IN (SELECT subt.application_id
440 ,subt.entity_code
441 ,subt.event_class_code
442 ,subt.language
443 FROM xla_event_classes_tl subb
444 ,xla_event_classes_tl subt
445 WHERE subb.application_id = subt.application_id
446 AND subb.entity_code = subt.entity_code
447 AND subb.event_class_code = subt.event_class_code
448 AND subb.language = subt.source_lang
449 AND (SUBB.name <> SUBT.name
450 OR SUBB.description <> SUBT.description
451 OR (subb.description IS NULL
452 AND subt.description IS NOT NULL)
453 OR (subb.description IS NOT NULL
454 AND subt.description IS NULL)
455 ))
456 ;
457
458 INSERT INTO xla_event_classes_tl
459 (creation_date
460 ,entity_code
461 ,event_class_code
462 ,name
463 ,description
464 ,application_id
465 ,last_update_date
466 ,last_updated_by
467 ,last_update_login
468 ,created_by
469 ,language
470 ,source_lang)
471 SELECT /*+ ORDERED */
472 b.creation_date
473 ,b.entity_code
474 ,b.event_class_code
475 ,b.name
476 ,b.description
477 ,b.application_id
478 ,b.last_update_date
479 ,b.last_updated_by
480 ,b.last_update_login
481 ,b.created_by
482 ,l.language_code
483 ,b.source_lang
484 FROM xla_event_classes_tl b
485 ,fnd_languages l
486 WHERE l.installed_flag IN ('I', 'B')
487 AND b.language = userenv('LANG')
488 AND NOT EXISTS
489 (SELECT NULL
490 FROM xla_event_classes_tl t
491 WHERE t.application_id = b.application_id
492 AND t.entity_code = b.entity_code
493 AND t.event_class_code = b.event_class_code
494 AND t.language = l.language_code);
495
496 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
497 trace(p_msg => 'END of procedure add_language',
498 p_module => l_log_module,
499 p_level => C_LEVEL_PROCEDURE);
500 END IF;
501
502 END add_language;
503
504 --=============================================================================
505 --
506 -- Name: load_row
507 -- Description: To be used by FNDLOAD to upload a row to the table
508 --
509 --=============================================================================
510 PROCEDURE load_row
511 (p_application_short_name IN VARCHAR2
512 ,p_entity_code IN VARCHAR2
513 ,p_event_class_code IN VARCHAR2
514 ,p_enabled_flag IN VARCHAR2
515 ,p_owner IN VARCHAR2
516 ,p_last_update_date IN VARCHAR2
520 CURSOR c_app_id IS
517 ,p_name IN VARCHAR2
518 ,p_description IN VARCHAR2)
519 IS
521 SELECT application_id
522 FROM fnd_application
523 WHERE application_short_name = p_application_short_name;
524
525 l_application_id INTEGER;
526 l_rowid ROWID;
527 l_exist VARCHAR2(1);
528 f_luby NUMBER; -- entity owner in file
529 f_ludate DATE; -- entity update date in file
530 db_luby NUMBER; -- entity owner in db
531 db_ludate DATE; -- entity update date in db
532 l_log_module VARCHAR2(240);
533 BEGIN
534
535 IF g_log_enabled THEN
536 l_log_module := C_DEFAULT_MODULE||'.load_row';
537 END IF;
538
539 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
540 trace(p_msg => 'BEGIN of procedure load_row',
541 p_module => l_log_module,
542 p_level => C_LEVEL_PROCEDURE);
543 END IF;
544
545 -- Translate owner to file_last_updated_by
546 f_luby := fnd_load_util.owner_id(p_owner);
547
548 -- Translate char last_update_date to date
549 f_ludate := nvl(to_date(p_last_update_date, 'YYYY/MM/DD'), sysdate);
550
551 OPEN c_app_id;
552 FETCH c_app_id INTO l_application_id;
553 CLOSE c_app_id;
554
555 BEGIN
556
557 SELECT last_updated_by, last_update_date
558 INTO db_luby, db_ludate
559 FROM xla_event_classes_vl
560 WHERE application_id = l_application_id
561 AND entity_code = p_entity_code
562 AND event_class_code = p_event_class_code;
563
564 IF (fnd_load_util.upload_test(f_luby, f_ludate, db_luby, db_ludate, NULL)) THEN
565 xla_event_classes_f_pkg.update_row
566 (x_application_id => l_application_id
567 ,x_entity_code => p_entity_code
568 ,x_event_class_code => p_event_class_code
569 ,x_enabled_flag => p_enabled_flag
570 ,x_name => p_name
571 ,x_description => p_description
572 ,x_last_update_date => f_ludate
573 ,x_last_updated_by => f_luby
574 ,x_last_update_login => 0);
575
576 END IF;
577
578 EXCEPTION
579 WHEN NO_DATA_FOUND THEN
580 xla_event_classes_f_pkg.insert_row
581 (x_rowid => l_rowid
582 ,x_application_id => l_application_id
583 ,x_entity_code => p_entity_code
584 ,x_event_class_code => p_event_class_code
585 ,x_enabled_flag => p_enabled_flag
586 ,x_name => p_name
587 ,x_description => p_description
588 ,x_creation_date => f_ludate
589 ,x_created_by => f_luby
590 ,x_last_update_date => f_ludate
591 ,x_last_updated_by => f_luby
592 ,x_last_update_login => 0);
593 END;
594
595 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
596 trace(p_msg => 'END of procedure load_row',
597 p_module => l_log_module,
598 p_level => C_LEVEL_PROCEDURE);
599 END IF;
600
601 EXCEPTION
602 WHEN NO_DATA_FOUND THEN
603 null;
604 WHEN OTHERS THEN
605 xla_exceptions_pkg.raise_message
606 (p_location => 'xla_event_classes_f_pkg.load_row');
607
608 END load_row;
609
610 --=============================================================================
611 --
612 -- Name: translate_row
613 -- Description: To be used by FNDLOAD to upload a translated row
614 --
615 --=============================================================================
616 PROCEDURE translate_row
617 (p_application_short_name IN VARCHAR2
618 ,p_entity_code IN VARCHAR2
619 ,p_event_class_code IN VARCHAR2
620 ,p_name IN VARCHAR2
621 ,p_description IN VARCHAR2
622 ,p_owner IN VARCHAR2
623 ,p_last_update_date IN VARCHAR2
624 ,p_custom_mode IN VARCHAR2)
625 IS
626 CURSOR c_app_id IS
627 SELECT application_id
628 FROM fnd_application
629 WHERE application_short_name = p_application_short_name;
630
631 l_application_id INTEGER;
632 l_rowid ROWID;
633 l_exist VARCHAR2(1);
634 f_luby NUMBER; -- entity owner in file
635 f_ludate DATE; -- entity update date in file
636 db_luby NUMBER; -- entity owner in db
637 db_ludate DATE; -- entity update date in db
638 l_log_module VARCHAR2(240);
639 BEGIN
640
641 IF g_log_enabled THEN
642 l_log_module := C_DEFAULT_MODULE||'.translate_row';
643 END IF;
644
645 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
646 trace(p_msg => 'BEGIN of procedure translate_row',
647 p_module => l_log_module,
648 p_level => C_LEVEL_PROCEDURE);
649 END IF;
650
651 -- Translate owner to file_last_updated_by
652 f_luby := fnd_load_util.owner_id(p_owner);
653
654 -- Translate char last_update_date to date
655 f_ludate := nvl(to_date(p_last_update_date, 'YYYY/MM/DD'), sysdate);
656
657 OPEN c_app_id;
658 FETCH c_app_id INTO l_application_id;
659 CLOSE c_app_id;
660
661 BEGIN
662 SELECT last_updated_by, last_update_date
663 INTO db_luby, db_ludate
664 FROM xla_event_classes_tl
665 WHERE application_id = l_application_id
666 AND entity_code = p_entity_code
667 AND event_class_code = p_event_class_code
668 AND language = userenv('LANG');
669
670 IF (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
671 db_ludate, p_custom_mode)) then
672 UPDATE xla_event_classes_tl
673 SET name = p_name
674 ,description = p_description
675 ,last_update_date = f_ludate
676 ,last_updated_by = f_luby
677 ,last_update_login = 0
678 ,source_lang = userenv('LANG')
679 WHERE userenv('LANG') IN (language, source_lang)
680 AND application_id = l_application_id
681 AND entity_code = p_entity_code
682 AND event_class_code = p_event_class_code;
683
684 END IF;
685
686
687
688 END;
689
690 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
691 trace(p_msg => 'END of procedure translate_row',
692 p_module => l_log_module,
693 p_level => C_LEVEL_PROCEDURE);
694 END IF;
695
696
697
698 END translate_row;
699
700
701 --=============================================================================
702 --
703 -- Following code is executed when the package body is referenced for the first
704 -- time
705 --
706 --=============================================================================
707 BEGIN
708 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
709 g_log_enabled := fnd_log.test
710 (log_level => g_log_level
711 ,module => C_DEFAULT_MODULE);
712
713 IF NOT g_log_enabled THEN
714 g_log_level := C_LEVEL_LOG_DISABLED;
715 END IF;
716
717 end xla_event_classes_f_PKG;