[Home] [Help]
PACKAGE BODY: APPS.XLA_DESCRIPT_DETAILS_F_PKG
Source
1 PACKAGE BODY xla_descript_details_f_pkg AS
2 /* $Header: xlathded.pkb 120.18.12010000.1 2008/07/29 10:09:17 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_descript_details |
10 | |
11 | DESCRIPTION |
12 | Forms PL/SQL Wrapper for xla_descript_details |
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_descript_details_f_pkg';
31
32 g_log_level NUMBER;
33 g_log_enabled BOOLEAN;
34
35 PROCEDURE trace
36 (p_msg IN VARCHAR2
37 ,p_module IN VARCHAR2
38 ,p_level IN NUMBER) IS
39 BEGIN
40 ----------------------------------------------------------------------------
41 -- Following is for FND log.
42 ----------------------------------------------------------------------------
43 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
44 fnd_log.message(p_level, p_module);
45 ELSIF p_level >= g_log_level THEN
46 fnd_log.string(p_level, p_module, p_msg);
47 END IF;
48
49 EXCEPTION
50 WHEN xla_exceptions_pkg.application_exception THEN
51 RAISE;
52
53 WHEN OTHERS THEN
54 xla_exceptions_pkg.raise_message
55 (p_location => 'xla_descript_details_f_pkg.trace');
56 END trace;
57
58
59
60 /*======================================================================+
61 | |
62 | Procedure insert_row |
63 | |
64 +======================================================================*/
65 PROCEDURE insert_row
66 (x_rowid IN OUT NOCOPY VARCHAR2
67 ,x_description_detail_id IN NUMBER
68 ,x_amb_context_code IN VARCHAR2
69 ,x_flexfield_segment_code IN VARCHAR2
70 ,x_display_description_flag IN VARCHAR2
71 ,x_description_prio_id IN NUMBER
72 ,x_user_sequence IN NUMBER
73 ,x_value_type_code IN VARCHAR2
74 ,x_source_application_id IN NUMBER
75 ,x_source_type_code IN VARCHAR2
76 ,x_source_code IN VARCHAR2
77 ,x_literal IN VARCHAR2
78 ,x_creation_date IN DATE
79 ,x_created_by IN NUMBER
80 ,x_last_update_date IN DATE
81 ,x_last_updated_by IN NUMBER
82 ,x_last_update_login IN NUMBER)
83
84 IS
85
86 CURSOR c IS
87 SELECT rowid
88 FROM xla_descript_details_b
89 WHERE description_detail_id = x_description_detail_id
90 ;
91
92 l_log_module VARCHAR2(240);
93 BEGIN
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_descript_details_b
106 (creation_date
107 ,created_by
108 ,amb_context_code
109 ,flexfield_segment_code
110 ,display_description_flag
111 ,description_detail_id
112 ,description_prio_id
113 ,user_sequence
114 ,value_type_code
115 ,source_application_id
116 ,source_type_code
117 ,source_code
118 ,last_update_date
119 ,last_updated_by
120 ,last_update_login)
121 VALUES
122 (x_creation_date
123 ,x_created_by
124 ,x_amb_context_code
125 ,x_flexfield_segment_code
126 ,x_display_description_flag
127 ,x_description_detail_id
128 ,x_description_prio_id
129 ,x_user_sequence
130 ,x_value_type_code
131 ,x_source_application_id
132 ,x_source_type_code
133 ,x_source_code
134 ,x_last_update_date
135 ,x_last_updated_by
136 ,x_last_update_login)
137 ;
138
139 INSERT INTO xla_descript_details_tl
140 (description_detail_id
141 ,literal
142 ,creation_date
143 ,created_by
144 ,last_update_date
145 ,last_updated_by
146 ,last_update_login
147 ,amb_context_code
148 ,language
149 ,source_lang)
150 SELECT
151 x_description_detail_id
152 ,x_literal
153 ,x_creation_date
154 ,x_created_by
155 ,x_last_update_date
156 ,x_last_updated_by
157 ,x_last_update_login
158 ,x_amb_context_code
159 ,l.language_code
160 ,USERENV('LANG')
161 FROM fnd_languages l
162 WHERE l.installed_flag IN ('I', 'B')
163 AND NOT EXISTS
164 (SELECT NULL
165 FROM xla_descript_details_tl t
166 WHERE t.description_detail_id = x_description_detail_id
167 AND t.language = l.language_code);
168
169 OPEN c;
170 FETCH c INTO x_rowid;
171
172 IF (c%NOTFOUND) THEN
173 CLOSE c;
174 RAISE NO_DATA_FOUND;
175 END IF;
176 CLOSE c;
177
178 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
179 trace(p_msg => 'END of procedure insert_row',
180 p_module => l_log_module,
181 p_level => C_LEVEL_PROCEDURE);
182 END IF;
183
184 END insert_row;
185
186 /*======================================================================+
187 | |
188 | Procedure lock_row |
189 | |
190 +======================================================================*/
191 PROCEDURE lock_row
192 (x_description_detail_id IN NUMBER
193 ,x_amb_context_code IN VARCHAR2
194 ,x_flexfield_segment_code IN VARCHAR2
195 ,x_display_description_flag IN VARCHAR2
196 ,x_description_prio_id IN NUMBER
197 ,x_user_sequence IN NUMBER
198 ,x_value_type_code IN VARCHAR2
199 ,x_source_application_id IN NUMBER
200 ,x_source_type_code IN VARCHAR2
201 ,x_source_code IN VARCHAR2
202 ,x_literal IN VARCHAR2)
203
204 IS
205
206 CURSOR c IS
207 SELECT last_update_login
208 ,amb_context_code
209 ,flexfield_segment_code
210 ,display_description_flag
211 ,description_prio_id
212 ,user_sequence
213 ,value_type_code
214 ,source_application_id
215 ,source_type_code
216 ,source_code
217 FROM xla_descript_details_b
218 WHERE description_detail_id = x_description_detail_id
219 FOR UPDATE OF description_detail_id NOWAIT;
220
221 recinfo c%ROWTYPE;
222
223 CURSOR c1 IS
224 SELECT description_detail_id
225 ,literal
226 ,DECODE(language , USERENV('LANG'), 'Y', 'N') baselang
227 FROM xla_descript_details_tl
228 WHERE description_detail_id = X_description_detail_id
229 AND USERENV('LANG') IN (language ,source_lang)
230 FOR UPDATE OF description_detail_id NOWAIT;
231
232 l_log_module VARCHAR2(240);
233 BEGIN
234 IF g_log_enabled THEN
235 l_log_module := C_DEFAULT_MODULE||'.lock_row';
236 END IF;
237
238 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
239 trace(p_msg => 'BEGIN of procedure lock_row',
240 p_module => l_log_module,
241 p_level => C_LEVEL_PROCEDURE);
242 END IF;
243
244
245 OPEN c;
246 FETCH c INTO recinfo;
247
248 IF (c%NOTFOUND) THEN
249 CLOSE c;
250 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
251 app_exception.raise_exception;
252 END IF;
253 CLOSE c;
254
255 IF ( (recinfo.amb_context_code = x_amb_context_code)
256 AND ((recinfo.flexfield_segment_code = X_flexfield_segment_code)
257 OR ((recinfo.flexfield_segment_code IS NULL)
258 AND (x_flexfield_segment_code IS NULL)))
259 AND ((recinfo.display_description_flag = X_display_description_flag)
260 OR ((recinfo.display_description_flag IS NULL)
261 AND (x_display_description_flag IS NULL)))
262 AND (recinfo.description_prio_id = x_description_prio_id)
263 AND (recinfo.user_sequence = x_user_sequence)
264 AND ((recinfo.value_type_code = X_value_type_code)
265 OR ((recinfo.value_type_code IS NULL)
266 AND (x_value_type_code IS NULL)))
267 AND ((recinfo.source_application_id = X_source_application_id)
268 OR ((recinfo.source_application_id IS NULL)
269 AND (x_source_application_id IS NULL)))
270 AND ((recinfo.source_type_code = X_source_type_code)
271 OR ((recinfo.source_type_code IS NULL)
272 AND (x_source_type_code IS NULL)))
273 AND ((recinfo.source_code = X_source_code)
274 OR ((recinfo.source_code IS NULL)
275 AND (x_source_code IS NULL)))
276 ) THEN
277 NULL;
278 ELSE
279 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
280 app_exception.raise_exception;
281 END IF;
282
283 FOR tlinfo IN c1 LOOP
284 IF (tlinfo.baselang = 'Y') THEN
285 IF ( ((tlinfo.literal = X_literal)
286 OR ((tlinfo.literal is null)
287 AND (X_literal is null)))
288 ) THEN
289 NULL;
290 ELSE
291 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
292 app_exception.raise_exception;
293 END IF;
294 END IF;
295 END LOOP;
296
297
298 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
299 trace(p_msg => 'END of procedure lock_row',
300 p_module => l_log_module,
301 p_level => C_LEVEL_PROCEDURE);
302 END IF;
303
304 RETURN;
305
306 END lock_row;
307
308 /*======================================================================+
309 | |
310 | Procedure update_row |
311 | |
312 +======================================================================*/
313 PROCEDURE update_row
314 (x_description_detail_id IN NUMBER
315 ,x_amb_context_code IN VARCHAR2
316 ,x_flexfield_segment_code IN VARCHAR2
317 ,x_display_description_flag IN VARCHAR2
318 ,x_description_prio_id IN NUMBER
319 ,x_user_sequence IN NUMBER
320 ,x_value_type_code IN VARCHAR2
321 ,x_source_application_id IN NUMBER
322 ,x_source_type_code IN VARCHAR2
323 ,x_source_code IN VARCHAR2
324 ,x_literal IN VARCHAR2
325 ,x_last_update_date IN DATE
326 ,x_last_updated_by IN NUMBER
327 ,x_last_update_login IN NUMBER)
328
329 IS
330
331 l_log_module VARCHAR2(240);
332 BEGIN
333 IF g_log_enabled THEN
334 l_log_module := C_DEFAULT_MODULE||'.update_row';
335 END IF;
336
337 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
338 trace(p_msg => 'BEGIN of procedure update_row',
339 p_module => l_log_module,
340 p_level => C_LEVEL_PROCEDURE);
341 END IF;
342
343 UPDATE xla_descript_details_b
344 SET
345 last_update_date = x_last_update_date
346 ,amb_context_code = x_amb_context_code
347 ,flexfield_segment_code = x_flexfield_segment_code
348 ,display_description_flag = x_display_description_flag
349 ,description_prio_id = x_description_prio_id
350 ,user_sequence = x_user_sequence
351 ,value_type_code = x_value_type_code
355 ,last_updated_by = x_last_updated_by
352 ,source_application_id = x_source_application_id
353 ,source_type_code = x_source_type_code
354 ,source_code = x_source_code
356 ,last_update_login = x_last_update_login
357 WHERE description_detail_id = X_description_detail_id;
358
359 IF (SQL%NOTFOUND) THEN
360 RAISE NO_DATA_FOUND;
361 END IF;
362
363 UPDATE xla_descript_details_tl
364 SET
365 last_update_date = x_last_update_date
366 ,literal = X_literal
367 ,last_updated_by = x_last_updated_by
368 ,last_update_login = x_last_update_login
369 ,source_lang = USERENV('LANG')
370 WHERE description_detail_id = X_description_detail_id
371 AND USERENV('LANG') IN (language, source_lang);
372
373 IF (SQL%NOTFOUND) THEN
374 RAISE NO_DATA_FOUND;
375 END IF;
376
377 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
378 trace(p_msg => 'END of procedure update_row',
379 p_module => l_log_module,
380 p_level => C_LEVEL_PROCEDURE);
381 END IF;
382
383 END update_row;
384
385 /*======================================================================+
386 | |
387 | Procedure delete_row |
388 | |
389 +======================================================================*/
390 PROCEDURE delete_row
391 (x_description_detail_id IN NUMBER)
392
393 IS
394
395 l_log_module VARCHAR2(240);
396 BEGIN
397 IF g_log_enabled THEN
398 l_log_module := C_DEFAULT_MODULE||'.delete_row';
399 END IF;
400
401 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
402 trace(p_msg => 'BEGIN of procedure delete_row',
403 p_module => l_log_module,
404 p_level => C_LEVEL_PROCEDURE);
405 END IF;
406
407 DELETE FROM xla_descript_details_tl
408 WHERE description_detail_id = x_description_detail_id;
409
410
411 IF (SQL%NOTFOUND) THEN
412 RAISE NO_DATA_FOUND;
413 END IF;
414
415 DELETE FROM xla_descript_details_b
416 WHERE description_detail_id = x_description_detail_id;
417
418
419 IF (SQL%NOTFOUND) THEN
420 RAISE NO_DATA_FOUND;
421 END IF;
422
423
424 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
425 trace(p_msg => 'END of procedure delete_row',
426 p_module => l_log_module,
427 p_level => C_LEVEL_PROCEDURE);
428 END IF;
429
430 END delete_row;
431
432 /*======================================================================+
433 | |
434 | Procedure add_language |
435 | |
436 +======================================================================*/
437 PROCEDURE add_language
438
439 IS
440
441 l_log_module VARCHAR2(240);
442 BEGIN
443 IF g_log_enabled THEN
444 l_log_module := C_DEFAULT_MODULE||'.add_language';
445 END IF;
446
447 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
448 trace(p_msg => 'BEGIN of procedure add_language',
449 p_module => l_log_module,
450 p_level => C_LEVEL_PROCEDURE);
451 END IF;
452
453 DELETE FROM xla_descript_details_tl T
454 WHERE NOT EXISTS
455 (SELECT NULL
456 FROM xla_descript_details_b b
457 WHERE b.description_detail_id = t.description_detail_id);
458
459 UPDATE xla_descript_details_tl t
460 SET (literal)
461 = (SELECT b.literal
462 FROM xla_descript_details_tl b
463 WHERE b.description_detail_id = t.description_detail_id
464 AND b.language = t.source_lang)
465 WHERE (t.description_detail_id
466 ,t.language)
467 IN (SELECT subt.description_detail_id
468 ,subt.language
469 FROM xla_descript_details_tl subb
470 ,xla_descript_details_tl subt
471 WHERE subb.description_detail_id = subt.description_detail_id
472 AND subb.language = subt.source_lang
473 AND (SUBB.literal <> SUBT.literal
474 OR (subb.literal IS NULL
475 AND subt.literal IS NOT NULL)
476 OR (subb.literal IS NOT NULL
477 AND subt.literal IS NULL)
478 ))
479 ;
480
481 INSERT INTO xla_descript_details_tl
482 (description_detail_id
483 ,literal
484 ,creation_date
485 ,created_by
486 ,last_update_date
487 ,last_updated_by
488 ,last_update_login
489 ,amb_context_code
490 ,language
491 ,source_lang)
492 SELECT /*+ ORDERED */
493 b.description_detail_id
497 ,b.last_update_date
494 ,b.literal
495 ,b.creation_date
496 ,b.created_by
498 ,b.last_updated_by
499 ,b.last_update_login
500 ,b.amb_context_code
501 ,l.language_code
502 ,b.source_lang
503 FROM xla_descript_details_tl b
504 ,fnd_languages l
505 WHERE l.installed_flag IN ('I', 'B')
506 AND b.language = userenv('LANG')
507 AND NOT EXISTS
508 (SELECT NULL
509 FROM xla_descript_details_tl t
510 WHERE t.description_detail_id = b.description_detail_id
511 AND t.language = l.language_code);
512
513 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
514 trace(p_msg => 'END of procedure add_language',
515 p_module => l_log_module,
516 p_level => C_LEVEL_PROCEDURE);
517 END IF;
518
519 END add_language;
520
521 /*======================================================================+
522 * | |
523 * | Procedure translate_row |
524 * | |
525 * +======================================================================*/
526 PROCEDURE translate_row
527 (p_application_short_name IN VARCHAR2
528 ,p_amb_context_code IN VARCHAR2
529 ,p_description_type_code IN VARCHAR2
530 ,p_description_code IN VARCHAR2
531 ,p_priority_num IN VARCHAR2
532 ,p_user_sequence IN VARCHAR2
533 ,p_literal IN VARCHAR2
534 ,p_owner IN VARCHAR2
535 ,p_last_update_date IN VARCHAR2
536 ,p_custom_mode IN VARCHAR2)
537 IS
538 CURSOR c_app_id IS
539 SELECT application_id
540 FROM fnd_application
541 WHERE application_short_name = p_application_short_name;
542
543 l_application_id INTEGER;
544 l_description_prio_id INTEGER;
545 l_description_detail_id INTEGER;
546
547 CURSOR c_prio IS
548 SELECT description_prio_id
549 FROM xla_desc_priorities
550 WHERE application_id = l_application_id
551 AND amb_context_code = p_amb_context_code
552 AND description_type_code = p_description_type_code
553 AND description_code = p_description_code
554 AND user_sequence = p_priority_num;
555
556 CURSOR c_dtl IS
557 SELECT description_detail_id
558 FROM xla_descript_details_b
559 WHERE amb_context_code = p_amb_context_code
560 AND description_prio_id = l_description_prio_id
561 AND user_sequence = p_user_sequence;
562
563 l_rowid ROWID;
564 l_exist VARCHAR2(1);
565 f_luby NUMBER; -- entity owner in file
566 f_ludate DATE; -- entity update date in file
567 db_luby NUMBER; -- entity owner in db
568 db_ludate DATE; -- entity update date in db
569 l_log_module VARCHAR2(240);
570 BEGIN
571
572 IF g_log_enabled THEN
573 l_log_module := C_DEFAULT_MODULE||'.translate_row';
574 END IF;
575
576 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
577 trace(p_msg => 'BEGIN of procedure translate_row',
578 p_module => l_log_module,
579 p_level => C_LEVEL_PROCEDURE);
580 END IF;
581
582 -- Translate owner to file_last_updated_by
583 f_luby := fnd_load_util.owner_id(p_owner);
584
585 -- Translate char last_update_date to date
586 f_ludate := nvl(to_date(p_last_update_date, 'YYYY/MM/DD'), sysdate);
587
588 OPEN c_app_id;
589 FETCH c_app_id INTO l_application_id;
590 CLOSE c_app_id;
591
592 OPEN c_prio;
593 FETCH c_prio INTO l_description_prio_id;
594 CLOSE c_prio;
595
596 OPEN c_dtl;
597 FETCH c_dtl INTO l_description_detail_id;
598 CLOSE c_dtl;
599
600 BEGIN
601 SELECT last_updated_by, last_update_date
602 INTO db_luby, db_ludate
603 FROM xla_descript_details_tl
604 WHERE amb_context_code = p_amb_context_code
605 AND description_detail_id = l_description_detail_id
606 AND language = userenv('LANG');
607
608 IF (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
609 db_ludate, p_custom_mode)) then
610 UPDATE xla_descript_details_tl
611 SET literal = p_literal
612 ,last_update_date = f_ludate
613 ,last_updated_by = f_luby
614 ,last_update_login = 0
615 ,source_lang = userenv('LANG')
616 WHERE userenv('LANG') IN (language, source_lang)
617 AND amb_context_code = p_amb_context_code
618 AND description_detail_id = l_description_detail_id;
619
620 END IF;
621
622 END;
623 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
624 trace(p_msg => 'END of procedure translate_row',
625 p_module => l_log_module,
626 p_level => C_LEVEL_PROCEDURE);
627 END IF;
628
629
630 END translate_row;
631
632 --=============================================================================
633 --
634 -- Following code is executed when the package body is referenced for the first
635 -- time
636 --
637 --=============================================================================
638 BEGIN
639 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
640 g_log_enabled := fnd_log.test
641 (log_level => g_log_level
642 ,module => C_DEFAULT_MODULE);
643
644 IF NOT g_log_enabled THEN
645 g_log_level := C_LEVEL_LOG_DISABLED;
646 END IF;
647
648
649 end xla_descript_details_f_PKG;