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