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