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