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