[Home] [Help]
PACKAGE BODY: APPS.XLA_ENTITY_TYPES_F_PKG
Source
1 PACKAGE BODY xla_entity_types_f_pkg AS
2 /* $Header: xlathent.pkb 120.19.12010000.1 2008/07/29 10:09:29 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_entity_types |
10 | |
11 | DESCRIPTION |
12 | Forms PL/SQL Wrapper for xla_entity_types |
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_entity_types_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_entity_types_f_pkg.trace');
59 END trace;
60
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_enabled_flag IN VARCHAR2
72 ,x_enable_gapless_events_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_entity_types_b
86 WHERE application_id = x_application_id
87 AND entity_code = x_entity_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
104 INSERT INTO xla_entity_types_b
105 (creation_date
106 ,created_by
107 ,application_id
108 ,entity_code
109 ,enabled_flag
110 ,enable_gapless_events_flag
111 ,last_update_date
112 ,last_updated_by
113 ,last_update_login)
114 VALUES
115 (x_creation_date
116 ,x_created_by
117 ,x_application_id
118 ,x_entity_code
119 ,x_enabled_flag
120 ,x_enable_gapless_events_flag
121 ,x_last_update_date
122 ,x_last_updated_by
123 ,x_last_update_login)
124 ;
125
126 INSERT INTO xla_entity_types_tl
127 (created_by
128 ,last_update_date
129 ,last_updated_by
130 ,last_update_login
131 ,application_id
132 ,entity_code
133 ,name
134 ,description
135 ,creation_date
136 ,language
137 ,source_lang)
138 SELECT
139 x_created_by
140 ,x_last_update_date
141 ,x_last_updated_by
142 ,x_last_update_login
143 ,x_application_id
144 ,x_entity_code
145 ,x_name
146 ,x_description
147 ,x_creation_date
148 ,l.language_code
149 ,USERENV('LANG')
150 FROM fnd_languages l
151 WHERE l.installed_flag IN ('I', 'B')
152 AND NOT EXISTS
153 (SELECT NULL
154 FROM xla_entity_types_tl t
155 WHERE t.application_id = x_application_id
156 AND t.entity_code = x_entity_code
157 AND t.language = l.language_code);
158
159 OPEN c;
160 FETCH c INTO x_rowid;
161
162 IF (c%NOTFOUND) THEN
163 CLOSE c;
164 RAISE NO_DATA_FOUND;
165 END IF;
166 CLOSE c;
167
168 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
169 trace(p_msg => 'END of procedure insert_row',
170 p_module => l_log_module,
171 p_level => C_LEVEL_PROCEDURE);
172 END IF;
173
174 END insert_row;
175
176 /*======================================================================+
177 | |
178 | Procedure lock_row |
179 | |
180 +======================================================================*/
181 PROCEDURE lock_row
182 (x_application_id IN NUMBER
183 ,x_entity_code IN VARCHAR2
184 ,x_enabled_flag IN VARCHAR2
185 ,x_enable_gapless_events_flag IN VARCHAR2
186 ,x_name IN VARCHAR2
187 ,x_description IN VARCHAR2)
188
189 IS
190
191 CURSOR c IS
192 SELECT application_id
193 ,enabled_flag
194 ,enable_gapless_events_flag
195 FROM xla_entity_types_b
196 WHERE application_id = x_application_id
197 AND entity_code = x_entity_code
198 FOR UPDATE OF application_id NOWAIT;
199
200 recinfo c%ROWTYPE;
201
202 CURSOR c1 IS
203 SELECT created_by
204 ,name
205 ,description
206 ,DECODE(language , USERENV('LANG'), 'Y', 'N') baselang
207 FROM xla_entity_types_tl
208 WHERE application_id = X_application_id
209 AND entity_code = X_entity_code
210 AND USERENV('LANG') IN (language ,source_lang)
211 FOR UPDATE OF application_id NOWAIT;
212
213 l_log_module VARCHAR2(240);
214 BEGIN
215
216 IF g_log_enabled THEN
217 l_log_module := C_DEFAULT_MODULE||'.lock_row';
218 END IF;
219
220 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
221 trace(p_msg => 'BEGIN of procedure lock_row',
222 p_module => l_log_module,
223 p_level => C_LEVEL_PROCEDURE);
224 END IF;
225
226 OPEN c;
227 FETCH c INTO recinfo;
228
229 IF (c%NOTFOUND) THEN
230 CLOSE c;
231 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
232 app_exception.raise_exception;
233 END IF;
234 CLOSE c;
235
236 IF ( (recinfo.enabled_flag = x_enabled_flag)
237 AND (recinfo.enable_gapless_events_flag = x_enable_gapless_events_flag)
238 ) THEN
239 NULL;
240 ELSE
241 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
242 app_exception.raise_exception;
243 END IF;
244
245 FOR tlinfo IN c1 LOOP
246 IF (tlinfo.baselang = 'Y') THEN
247 IF ( (tlinfo.name = X_name)
248 AND ((tlinfo.description = X_description)
249 OR ((tlinfo.description is null)
250 AND (X_description is null)))
251 ) THEN
252 NULL;
253 ELSE
254 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
255 app_exception.raise_exception;
256 END IF;
257 END IF;
258 END LOOP;
259
260 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
261 trace(p_msg => 'END of procedure lock_row',
262 p_module => l_log_module,
263 p_level => C_LEVEL_PROCEDURE);
264 END IF;
265
266 RETURN;
267
268 END lock_row;
269
270 /*======================================================================+
271 | |
272 | Procedure update_row |
273 | |
274 +======================================================================*/
275 PROCEDURE update_row
276 (x_application_id IN NUMBER
277 ,x_entity_code IN VARCHAR2
278 ,x_enabled_flag IN VARCHAR2
279 ,x_enable_gapless_events_flag IN VARCHAR2
280 ,x_name IN VARCHAR2
281 ,x_description IN VARCHAR2
282 ,x_last_update_date IN DATE
283 ,x_last_updated_by IN NUMBER
284 ,x_last_update_login IN NUMBER)
285
286 IS
287 l_log_module VARCHAR2(240);
288 BEGIN
289
290 IF g_log_enabled THEN
291 l_log_module := C_DEFAULT_MODULE||'.update_row';
292 END IF;
293
294 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
295 trace(p_msg => 'BEGIN of procedure update_row',
296 p_module => l_log_module,
297 p_level => C_LEVEL_PROCEDURE);
298 END IF;
299
300 UPDATE xla_entity_types_b
301 SET
302 last_update_date = x_last_update_date
303 ,enabled_flag = x_enabled_flag
304 ,enable_gapless_events_flag = x_enable_gapless_events_flag
305 ,last_updated_by = x_last_updated_by
306 ,last_update_login = x_last_update_login
307 WHERE application_id = X_application_id
308 AND entity_code = X_entity_code;
309
310 IF (SQL%NOTFOUND) THEN
311 RAISE NO_DATA_FOUND;
312 END IF;
313
314 UPDATE xla_entity_types_tl
315 SET
316 last_update_date = x_last_update_date
317 ,name = X_name
318 ,description = X_description
319 ,last_updated_by = x_last_updated_by
320 ,last_update_login = x_last_update_login
321 ,source_lang = USERENV('LANG')
322 WHERE application_id = X_application_id
323 AND entity_code = X_entity_code
324 AND USERENV('LANG') IN (language, source_lang);
325
326 IF (SQL%NOTFOUND) THEN
327 RAISE NO_DATA_FOUND;
328 END IF;
329
330 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
331 trace(p_msg => 'END of procedure update_row',
332 p_module => l_log_module,
333 p_level => C_LEVEL_PROCEDURE);
334 END IF;
335
336 END update_row;
337
338 /*======================================================================+
339 | |
340 | Procedure delete_row |
341 | |
342 +======================================================================*/
343 PROCEDURE delete_row
344 (x_application_id IN NUMBER
345 ,x_entity_code IN VARCHAR2)
346 IS
347 l_log_module VARCHAR2(240);
348 BEGIN
349
350 IF g_log_enabled THEN
351 l_log_module := C_DEFAULT_MODULE||'.delete_row';
352 END IF;
353
354 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
355 trace(p_msg => 'BEGIN of procedure delete_row',
356 p_module => l_log_module,
357 p_level => C_LEVEL_PROCEDURE);
358 END IF;
359
360 DELETE FROM xla_entity_types_tl
361 WHERE application_id = x_application_id
362 AND entity_code = x_entity_code;
363
364
365 IF (SQL%NOTFOUND) THEN
366 RAISE NO_DATA_FOUND;
367 END IF;
368
369 DELETE FROM xla_entity_types_b
370 WHERE application_id = x_application_id
371 AND entity_code = x_entity_code;
372
373
374 IF (SQL%NOTFOUND) THEN
375 RAISE NO_DATA_FOUND;
376 END IF;
377
378 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
379 trace(p_msg => 'END of procedure delete_row',
380 p_module => l_log_module,
381 p_level => C_LEVEL_PROCEDURE);
382 END IF;
383
384 END delete_row;
385
386 /*======================================================================+
387 | |
388 | Procedure add_language |
389 | |
390 +======================================================================*/
391 PROCEDURE add_language
392 IS
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_entity_types_tl T
407 WHERE NOT EXISTS
408 (SELECT NULL
409 FROM xla_entity_types_b b
410 WHERE b.application_id = t.application_id
411 AND b.entity_code = t.entity_code);
412
416 = (SELECT b.name
413 UPDATE xla_entity_types_tl t
414 SET (name
415 ,description)
417 ,b.description
418 FROM xla_entity_types_tl b
419 WHERE b.application_id = t.application_id
420 AND b.entity_code = t.entity_code
421 AND b.language = t.source_lang)
422 WHERE (t.application_id
423 ,t.entity_code
424 ,t.language)
425 IN (SELECT subt.application_id
426 ,subt.entity_code
427 ,subt.language
428 FROM xla_entity_types_tl subb
429 ,xla_entity_types_tl subt
430 WHERE subb.application_id = subt.application_id
431 AND subb.entity_code = subt.entity_code
432 AND subb.language = subt.source_lang
433 AND (SUBB.name <> SUBT.name
434 OR SUBB.description <> SUBT.description
435 OR (subb.description IS NULL
436 AND subt.description IS NOT NULL)
437 OR (subb.description IS NOT NULL
438 AND subt.description IS NULL)
439 ))
440 ;
441
442 INSERT INTO xla_entity_types_tl
443 (created_by
444 ,last_update_date
445 ,last_updated_by
446 ,last_update_login
447 ,application_id
448 ,entity_code
449 ,name
450 ,description
451 ,creation_date
452 ,language
453 ,source_lang)
454 SELECT /*+ ORDERED */
455 b.created_by
456 ,b.last_update_date
457 ,b.last_updated_by
458 ,b.last_update_login
459 ,b.application_id
460 ,b.entity_code
461 ,b.name
462 ,b.description
463 ,b.creation_date
464 ,l.language_code
465 ,b.source_lang
466 FROM xla_entity_types_tl b
467 ,fnd_languages l
468 WHERE l.installed_flag IN ('I', 'B')
469 AND b.language = userenv('LANG')
470 AND NOT EXISTS
471 (SELECT NULL
472 FROM xla_entity_types_tl t
473 WHERE t.application_id = b.application_id
474 AND t.entity_code = b.entity_code
475 AND t.language = l.language_code);
476
477 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
478 trace(p_msg => 'END of procedure add_language',
479 p_module => l_log_module,
480 p_level => C_LEVEL_PROCEDURE);
481 END IF;
482
483 END add_language;
484
485 --=============================================================================
486 --
487 -- Name: load_row
488 -- Description: To be used by FNDLOAD to upload a row to the table
489 --
490 --=============================================================================
491 PROCEDURE load_row
492 (p_application_short_name IN VARCHAR2
493 ,p_entity_code IN VARCHAR2
494 ,p_enabled_flag IN VARCHAR2
495 ,p_enable_gapless_events_flag IN VARCHAR2
496 ,p_owner IN VARCHAR2
497 ,p_last_update_date IN VARCHAR2
498 ,p_name IN VARCHAR2
499 ,p_description IN VARCHAR2)
500 IS
501 CURSOR c_app_id IS
502 SELECT application_id
503 FROM fnd_application
504 WHERE application_short_name = p_application_short_name;
505
506 l_application_id INTEGER;
507 l_rowid ROWID;
508 l_exist VARCHAR2(1);
509 f_luby NUMBER; -- entity owner in file
510 f_ludate DATE; -- entity update date in file
511 db_luby NUMBER; -- entity owner in db
512 db_ludate DATE; -- entity update date in db
513 l_log_module VARCHAR2(240);
514 BEGIN
515
516 IF g_log_enabled THEN
517 l_log_module := C_DEFAULT_MODULE||'.load_row';
518 END IF;
519
520 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
521 trace(p_msg => 'BEGIN of procedure load_row',
522 p_module => l_log_module,
523 p_level => C_LEVEL_PROCEDURE);
524 END IF;
525
526 -- Translate owner to file_last_updated_by
527 f_luby := fnd_load_util.owner_id(p_owner);
528
529 -- Translate char last_update_date to date
530 f_ludate := nvl(to_date(p_last_update_date, 'YYYY/MM/DD'), sysdate);
531
532 OPEN c_app_id;
533 FETCH c_app_id INTO l_application_id;
534 CLOSE c_app_id;
535
536 BEGIN
537
538 SELECT last_updated_by, last_update_date
539 INTO db_luby, db_ludate
540 FROM xla_entity_types_vl
541 WHERE application_id = l_application_id
542 AND entity_code = p_entity_code;
543
544 IF (fnd_load_util.upload_test(f_luby, f_ludate, db_luby, db_ludate, NULL)) THEN
545 xla_entity_types_f_pkg.update_row
546 (x_application_id => l_application_id
550 ,x_name => p_name
547 ,x_entity_code => p_entity_code
548 ,x_enabled_flag => p_enabled_flag
549 ,x_enable_gapless_events_flag => p_enable_gapless_events_flag
551 ,x_description => p_description
552 ,x_last_update_date => f_ludate
553 ,x_last_updated_by => f_luby
554 ,x_last_update_login => 0);
555
556 END IF;
557
558 EXCEPTION
559 WHEN NO_DATA_FOUND THEN
560 xla_entity_types_f_pkg.insert_row
561 (x_rowid => l_rowid
562 ,x_application_id => l_application_id
563 ,x_entity_code => p_entity_code
564 ,x_enabled_flag => p_enabled_flag
565 ,x_enable_gapless_events_flag => p_enable_gapless_events_flag
566 ,x_name => p_name
567 ,x_description => p_description
568 ,x_creation_date => f_ludate
569 ,x_created_by => f_luby
570 ,x_last_update_date => f_ludate
571 ,x_last_updated_by => f_luby
572 ,x_last_update_login => 0);
573
574 END;
575
576 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
577 trace(p_msg => 'END of procedure load_row',
578 p_module => l_log_module,
579 p_level => C_LEVEL_PROCEDURE);
580 END IF;
581
582 EXCEPTION
583 WHEN NO_DATA_FOUND THEN
584 null;
585 WHEN OTHERS THEN
586 xla_exceptions_pkg.raise_message
587 (p_location => 'xla_entity_types_f_pkg.load_row');
588
589 END load_row;
590
591 --=============================================================================
592 --
593 -- Name: translate_row
594 -- Description: To be used by FNDLOAD to upload a translated row
595 --
596 --=============================================================================
597 PROCEDURE translate_row
598 (p_application_short_name IN VARCHAR2
599 ,p_entity_code IN VARCHAR2
600 ,p_name IN VARCHAR2
601 ,p_description IN VARCHAR2
602 ,p_owner IN VARCHAR2
603 ,p_last_update_date IN VARCHAR2
604 ,p_custom_mode IN VARCHAR2)
605 IS
606 CURSOR c_app_id IS
607 SELECT application_id
608 FROM fnd_application
609 WHERE application_short_name = p_application_short_name;
610
611 l_application_id INTEGER;
612 l_rowid ROWID;
613 l_exist VARCHAR2(1);
614 f_luby NUMBER; -- entity owner in file
615 f_ludate DATE; -- entity update date in file
616 db_luby NUMBER; -- entity owner in db
617 db_ludate DATE; -- entity update date in db
618 l_log_module VARCHAR2(240);
619 BEGIN
620
621 IF g_log_enabled THEN
622 l_log_module := C_DEFAULT_MODULE||'.translate_row';
623 END IF;
624
625 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
626 trace(p_msg => 'BEGIN of procedure translate_row',
627 p_module => l_log_module,
628 p_level => C_LEVEL_PROCEDURE);
629 END IF;
630
631 -- Translate owner to file_last_updated_by
632 f_luby := fnd_load_util.owner_id(p_owner);
633
634 -- Translate char last_update_date to date
635 f_ludate := nvl(to_date(p_last_update_date, 'YYYY/MM/DD'), sysdate);
636
637 OPEN c_app_id;
638 FETCH c_app_id INTO l_application_id;
639 CLOSE c_app_id;
640
641 BEGIN
642 SELECT last_updated_by, last_update_date
643 INTO db_luby, db_ludate
644 FROM xla_entity_types_tl
645 WHERE application_id = l_application_id
646 AND entity_code = p_entity_code
647 AND language = userenv('LANG');
648
649 IF (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
650 db_ludate, p_custom_mode)) then
651 UPDATE xla_entity_types_tl
652 SET name = p_name
653 ,description = p_description
654 ,last_update_date = f_ludate
655 ,last_updated_by = f_luby
656 ,last_update_login = 0
657 ,source_lang = userenv('LANG')
658 WHERE userenv('LANG') IN (language, source_lang)
659 AND application_id = l_application_id
660 AND entity_code = p_entity_code;
661
662 END IF;
663
664
665
666 END;
667
668 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
669 trace(p_msg => 'END of procedure translate_row',
670 p_module => l_log_module,
671 p_level => C_LEVEL_PROCEDURE);
672 END IF;
673
674
675
676 END translate_row;
677
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_entity_types_f_PKG;