[Home] [Help]
PACKAGE BODY: APPS.XLA_ANALYTICAL_CRITERIA_PKG
Source
1 PACKAGE BODY XLA_ANALYTICAL_CRITERIA_PKG AS
2 /* $Header: xlabaacr.pkb 120.26 2011/06/06 12:30:16 sgullape ship $ */
3 /*======================================================================+
4 | Copyright (c) 1995-2002 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +=======================================================================+
8 | PACKAGE NAME |
9 | xla_analytical_criteria_pkg |
10 | |
11 | DESCRIPTION |
12 | XLA Analytical Criteria Package |
13 | |
14 | HISTORY |
15 | 27-AUG-02 A. Quaglia Created |
16 | 02-APR-03 A. Quaglia Major revisions |
17 | 10-APR-03 A. Quaglia Final adjustments |
18 | 08-SEP-03 A. Quaglia Included the following functions/procs: |
19 | get_first_free_view_col_number |
20 | compile_criterion |
21 | build_criteria_view |
22 | 16-SEP-03 A. Quaglia In build_criteria_view removed condition |
23 | on enabled_flag in lc_displayable_details |
24 | 18-SEP-03 A. Quaglia insert_line_detail: delete dummy line |
25 | detail before inserting the detail |
26 | delete_line_detail: created. It all |
27 | details removed for a line, the dummy line|
28 | detail is inserted. |
29 | 27-JAN-03 A. Quaglia bug3402449: removed previous changes |
30 | delete_line_detail left since |
31 | the code is cleaner. |
32 | Changed trace handling as per Sandeep's |
33 | code. |
34 | 12-Feb-04 Shishir Joshi Replaced hh24miss with HH24MISS to improve|
35 | the performance. |
36 | 26-MAR-04 A.Quaglia Fixed debug changes issues: |
37 | -Replaced global variable for trace |
38 | with local one |
39 | -Fixed issue with SQL%ROWCOUNT which is |
40 | modified after calling debug proc |
41 | 14-APR-04 A.Quaglia Performance changes in get_detail_value_id|
42 | Removed hardcoded APPS in |
43 | build_criteria_view |
44 | 30-JUN-05 W.Chan Fix bug 4299125 - Modify compile_criteria |
45 | to use the same view_column for details of|
46 | the same amb context. Modify |
47 | build_criteria_views not to use amb |
48 | context when building the views |
49 | 01-SEP-05 W.Chan Fix bug 4583524 - Fix |
50 | get_view_column_number to ignore rows |
51 | NULL is in the view_column_num |
52 | |
53 +======================================================================*/
54
55
56 --
57 -- Private exceptions
58 --
59 le_resource_busy EXCEPTION;
60 PRAGMA exception_init(le_resource_busy, -00054);
61
62 --
63 -- Private constants
64 --
65 --accounting line is eligible for analytical criteria balance calc.
66 C_ANALYTICAL_BAL_FLAG_PEND CONSTANT VARCHAR2(1) := 'P';
67 --accounting line is not eligible for analytical criteria balance calc.
68 C_ANALYTICAL_BAL_FLAG_NO CONSTANT VARCHAR2(1) := NULL;
69 --accounting line has been processed by analytical criteria balance calc.
70 C_ANALYTICAL_BAL_FLAG_DONE CONSTANT VARCHAR2(1) := 'Y';
71
72
73 --Number of slots available for each datatype of analytical criteria details
74 C_MAX_MAPPABLE_VARCHAR_DETAILS CONSTANT INTEGER := 50; --400;
75 C_MAX_MAPPABLE_DATE_DETAILS CONSTANT INTEGER := 10; --200;
76 C_MAX_MAPPABLE_NUMBER_DETAILS CONSTANT INTEGER := 40; --200;
77 --Total number of slots available
78 C_MAX_MAPPABLE_DETAILS CONSTANT INTEGER := C_MAX_MAPPABLE_VARCHAR_DETAILS
79 +C_MAX_MAPPABLE_DATE_DETAILS
80 +C_MAX_MAPPABLE_NUMBER_DETAILS;
81 --Offset to add to the view column number contained in xla_analytical_dtls_b
82 --to get the actual column position in xla_analytical_criteria_v
83 C_MAPPABLE_VARCHAR_OFFSET CONSTANT INTEGER := 0;
84 C_MAPPABLE_DATE_OFFSET CONSTANT INTEGER := C_MAPPABLE_VARCHAR_OFFSET
85 +C_MAX_MAPPABLE_VARCHAR_DETAILS;
86 C_MAPPABLE_NUMBER_OFFSET CONSTANT INTEGER := C_MAPPABLE_DATE_OFFSET
87 +C_MAX_MAPPABLE_DATE_DETAILS;
88 -- Mizuru
89 C_AC_DELIMITER CONSTANT VARCHAR2(2) := '(]';
90
91 --
92 -- Private variables
93 --
94 g_user_id INTEGER := xla_environment_pkg.g_usr_id;
95 g_login_id INTEGER := xla_environment_pkg.g_login_id;
96 g_date DATE := SYSDATE;
97
98 g_hdr_ac_count PLS_INTEGER;
99 g_line_ac_count PLS_INTEGER;
100
101 --Cache structures
102 C_ANACRI_CACHE_MAX_SIZE CONSTANT INTEGER := 10;
103 g_anacri_cache_next_avail_pos INTEGER := 1;
104
105 TYPE gt_anacri_id IS TABLE OF INTEGER
106 INDEX BY BINARY_INTEGER;
107 TYPE gt_anacri_code IS TABLE OF VARCHAR2(30)
108 INDEX BY BINARY_INTEGER;
109 TYPE gt_anacri_type_code IS TABLE OF VARCHAR2(1)
110 INDEX BY BINARY_INTEGER;
111 TYPE gt_amb_context_code IS TABLE OF VARCHAR2(30)
112 INDEX BY BINARY_INTEGER;
113 TYPE gt_anacri_detail_char IS TABLE OF VARCHAR2(240)
114 INDEX BY BINARY_INTEGER;
115 TYPE gt_anacri_detail_date IS TABLE OF DATE
116 INDEX BY BINARY_INTEGER;
117 TYPE gt_anacri_detail_number IS TABLE OF NUMBER
118 INDEX BY BINARY_INTEGER;
119
120
121 ga_anacri_id gt_anacri_id ;
122 ga_anacri_code gt_anacri_code ;
123 ga_anacri_type_code gt_anacri_type_code ;
124 ga_amb_context_code gt_amb_context_code ;
125
126 ga_anacri_detail_char_1 gt_anacri_detail_char ;
127 ga_anacri_detail_char_2 gt_anacri_detail_char ;
128 ga_anacri_detail_char_3 gt_anacri_detail_char ;
129 ga_anacri_detail_char_4 gt_anacri_detail_char ;
130 ga_anacri_detail_char_5 gt_anacri_detail_char ;
131
132 ga_anacri_detail_date_1 gt_anacri_detail_date ;
133 ga_anacri_detail_date_2 gt_anacri_detail_date ;
134 ga_anacri_detail_date_3 gt_anacri_detail_date ;
135 ga_anacri_detail_date_4 gt_anacri_detail_date ;
136 ga_anacri_detail_date_5 gt_anacri_detail_date ;
137
138 ga_anacri_detail_number_1 gt_anacri_detail_number ;
139 ga_anacri_detail_number_2 gt_anacri_detail_number ;
140 ga_anacri_detail_number_3 gt_anacri_detail_number ;
141 ga_anacri_detail_number_4 gt_anacri_detail_number ;
142 ga_anacri_detail_number_5 gt_anacri_detail_number ;
143
144 --
145 -- Cursor declarations
146 --
147
148 --=============================================================================
149 -- *********** Local Trace Routine **********
150 --=============================================================================
151 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
152 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
153 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
154 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
155 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
156 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
157
158 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
159 C_DEFAULT_MODULE CONSTANT VARCHAR2(240) := 'xla.plsql.xla_analytical_criteria_pkg';
160
161 g_log_level NUMBER;
162 g_log_enabled BOOLEAN;
163
164 --1-STATEMENT, 2-PROCEDURE, 3-EVENT, 4-EXCEPTION, 5-ERROR, 6-UNEXPECTED
165
166 PROCEDURE trace
167 ( p_module IN VARCHAR2 DEFAULT C_DEFAULT_MODULE
168 ,p_msg IN VARCHAR2
169 ,p_level IN NUMBER
170 ) IS
171 BEGIN
172 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
173 fnd_log.message(p_level, p_module);
174 ELSIF p_level >= g_log_level THEN
175 fnd_log.string(p_level, p_module, p_msg);
176 END IF;
177
178 EXCEPTION
179 WHEN xla_exceptions_pkg.application_exception THEN
180 RAISE;
181 WHEN OTHERS THEN
182 xla_exceptions_pkg.raise_message
183 (p_location => 'xla_analytical_criteria_pkg.trace');
184 END trace;
185
186 /*
187
188 ******* Obsolete in R12 + ********
189
190 */
191
192
193 FUNCTION insert_detail_value
194 ( p_anacri_code IN VARCHAR2
195 ,p_anacri_type_code IN VARCHAR2
196 ,p_amb_context_code IN VARCHAR2
197 ,p_detail_char_1 IN VARCHAR2 DEFAULT NULL
198 ,p_detail_char_2 IN VARCHAR2 DEFAULT NULL
199 ,p_detail_char_3 IN VARCHAR2 DEFAULT NULL
200 ,p_detail_char_4 IN VARCHAR2 DEFAULT NULL
201 ,p_detail_char_5 IN VARCHAR2 DEFAULT NULL
202 ,p_detail_date_1 IN DATE DEFAULT NULL
203 ,p_detail_date_2 IN DATE DEFAULT NULL
204 ,p_detail_date_3 IN DATE DEFAULT NULL
205 ,p_detail_date_4 IN DATE DEFAULT NULL
206 ,p_detail_date_5 IN DATE DEFAULT NULL
207 ,p_detail_number_1 IN NUMBER DEFAULT NULL
208 ,p_detail_number_2 IN NUMBER DEFAULT NULL
209 ,p_detail_number_3 IN NUMBER DEFAULT NULL
210 ,p_detail_number_4 IN NUMBER DEFAULT NULL
211 ,p_detail_number_5 IN NUMBER DEFAULT NULL
212 ,p_detail_char_id IN INTEGER DEFAULT NULL
213 ) RETURN INTEGER
214 IS
215 /*======================================================================+
216 | |
217 | Private Procedure |
218 | |
219 | Description |
220 | ----------- |
221 | Inserts a new record in the table xla_analytical_dtl_vals. |
222 | |
223 +======================================================================*/
224
225 l_detail_value_id INTEGER;
226 l_log_module VARCHAR2 (2000);
227
228 BEGIN
229 IF g_log_enabled THEN
230 l_log_module := C_DEFAULT_MODULE||'.insert_detail_value';
231 END IF;
232
233 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
234 trace
235 (p_module => l_log_module
236 ,p_msg => 'BEGIN ' || l_log_module
237 ,p_level => C_LEVEL_PROCEDURE);
238 END IF;
239
240 IF p_detail_char_id IS NULL THEN
241 INSERT INTO xla_analytical_dtl_vals
242 ( analytical_detail_value_id
243 ,analytical_criterion_code
244 ,analytical_criterion_type_code
245 ,amb_context_code
246 ,analytical_detail_char_1
247 ,analytical_detail_char_2
248 ,analytical_detail_char_3
249 ,analytical_detail_char_4
250 ,analytical_detail_char_5
251 ,analytical_detail_date_1
252 ,analytical_detail_date_2
253 ,analytical_detail_date_3
254 ,analytical_detail_date_4
255 ,analytical_detail_date_5
256 ,analytical_detail_number_1
257 ,analytical_detail_number_2
258 ,analytical_detail_number_3
259 ,analytical_detail_number_4
260 ,analytical_detail_number_5
261 ,creation_date
262 ,created_by
263 ,last_update_date
264 ,last_updated_by
265 ,last_update_login
266 )
267 VALUES
268 ( xla_analytical_dtl_vals_s.nextval
269 ,p_anacri_code
270 ,p_anacri_type_code
271 ,p_amb_context_code
272 ,p_detail_char_1
273 ,p_detail_char_2
274 ,p_detail_char_3
275 ,p_detail_char_4
276 ,p_detail_char_5
277 ,p_detail_date_1
278 ,p_detail_date_2
279 ,p_detail_date_3
280 ,p_detail_date_4
281 ,p_detail_date_5
282 ,p_detail_number_1
283 ,p_detail_number_2
284 ,p_detail_number_3
285 ,p_detail_number_4
286 ,p_detail_number_5
287 ,g_date
288 ,g_user_id
289 ,g_date
290 ,g_user_id
291 ,g_login_id
292 )
293 RETURNING analytical_detail_value_id
294 INTO l_detail_value_id;
295
296 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
297 trace
298 ( p_module => l_log_module
299 ,p_msg => SQL%ROWCOUNT ||
300 ' row(s) inserted into xla_analytical_dtl_vals '
301 ,p_level => C_LEVEL_STATEMENT);
302 END IF;
303
304 ELSE
305 l_detail_value_id := p_detail_char_id;
306
307 INSERT INTO xla_analytical_dtl_vals
308 ( analytical_detail_value_id
309 ,analytical_criterion_code
310 ,analytical_criterion_type_code
311 ,amb_context_code
312 ,analytical_detail_char_1
313 ,analytical_detail_char_2
314 ,analytical_detail_char_3
315 ,analytical_detail_char_4
316 ,analytical_detail_char_5
317 ,analytical_detail_date_1
318 ,analytical_detail_date_2
319 ,analytical_detail_date_3
320 ,analytical_detail_date_4
321 ,analytical_detail_date_5
322 ,analytical_detail_number_1
323 ,analytical_detail_number_2
324 ,analytical_detail_number_3
325 ,analytical_detail_number_4
326 ,analytical_detail_number_5
327 ,creation_date
328 ,created_by
329 ,last_update_date
330 ,last_updated_by
331 ,last_update_login
332 )
333 VALUES
334 ( l_detail_value_id
335 ,p_anacri_code
336 ,p_anacri_type_code
337 ,p_amb_context_code
338 ,p_detail_char_1
339 ,p_detail_char_2
340 ,p_detail_char_3
341 ,p_detail_char_4
342 ,p_detail_char_5
343 ,p_detail_date_1
344 ,p_detail_date_2
345 ,p_detail_date_3
346 ,p_detail_date_4
347 ,p_detail_date_5
348 ,p_detail_number_1
349 ,p_detail_number_2
350 ,p_detail_number_3
351 ,p_detail_number_4
352 ,p_detail_number_5
353 ,g_date
354 ,g_user_id
355 ,g_date
356 ,g_user_id
357 ,g_login_id
358 );
359
360 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
361 trace
362 ( p_module => l_log_module
363 ,p_msg => SQL%ROWCOUNT
364 || ' row(s) inserted into xla_analytical_dtl_vals '
365 ,p_level => C_LEVEL_STATEMENT);
366 END IF;
367 END IF;
368
369 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
370 trace
371 (p_module => l_log_module
372 ,p_msg => 'END ' || l_log_module
376 RETURN l_detail_value_id;
373 ,p_level => C_LEVEL_PROCEDURE);
374 END IF;
375
377
378 EXCEPTION
379 WHEN xla_exceptions_pkg.application_exception THEN
380 RAISE;
381 WHEN OTHERS THEN
382 xla_exceptions_pkg.raise_message
383 (p_location => 'xla_analytical_criteria_pkg.insert_detail_value');
384 END insert_detail_value;
385
386
387 FUNCTION format_detail_value ( p_detail_char VARCHAR2
388 ,p_detail_date DATE
389 ,p_detail_number NUMBER
390 )
391 RETURN VARCHAR2
392 IS
393 /*======================================================================+
394 | |
395 | Public Function |
396 | |
397 | Description |
398 | ----------- |
399 | It returns the first nonnull among the given params, formatted to |
400 | VARCHAR2 |
401 | |
402 | MUST BE KEPT IN SYNCH WITH THE FUNCTION USED IN THE FUNCTION-BASED |
403 | INDEX ON XLA_ANALYTICAL_DTL_VALS. |
404 +======================================================================*/
405 l_log_module VARCHAR2 (2000);
406 BEGIN
407 IF g_log_enabled THEN
408 l_log_module := C_DEFAULT_MODULE||'.format_detail_value';
409 END IF;
410
411 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
412 trace
413 ( p_module => l_log_module
414 ,p_msg => 'BEGIN+END ' || l_log_module
415 ,p_level => C_LEVEL_PROCEDURE);
416 END IF;
417
418 RETURN SUBSTRB(
419 NVL( p_detail_char
420 ,NVL( TO_CHAR( p_detail_date
421 ,'YYYY/MM/DD HH24:MI:SS'
422 )
423 ,TO_CHAR( p_detail_number
424 ,'TM'
425 ,'NLS_NUMERIC_CHARACTERS = ''.,'''
426 )
427 )
428 )
429 ,1,30); -- MAX 30 characters
430
431 EXCEPTION
432 WHEN xla_exceptions_pkg.application_exception THEN
433 RAISE;
434 WHEN OTHERS THEN
435 xla_exceptions_pkg.raise_message
436 (p_location => 'xla_analytical_criteria_pkg.format_detail_value');
437 END format_detail_value;
438
439
440 FUNCTION maintain_detail_values
441 ( p_anacri_code IN VARCHAR2
442 ,p_anacri_type_code IN VARCHAR2
443 ,p_amb_context_code IN VARCHAR2
444 ,p_detail_char_1 IN VARCHAR2
445 ,p_detail_char_2 IN VARCHAR2
446 ,p_detail_char_3 IN VARCHAR2
447 ,p_detail_char_4 IN VARCHAR2
448 ,p_detail_char_5 IN VARCHAR2
449 ,p_detail_date_1 IN DATE
450 ,p_detail_date_2 IN DATE
451 ,p_detail_date_3 IN DATE
452 ,p_detail_date_4 IN DATE
453 ,p_detail_date_5 IN DATE
454 ,p_detail_number_1 IN NUMBER
455 ,p_detail_number_2 IN NUMBER
456 ,p_detail_number_3 IN NUMBER
457 ,p_detail_number_4 IN NUMBER
458 ,p_detail_number_5 IN NUMBER
459 ) RETURN INTEGER
460 IS
461 /*======================================================================+
462 | |
463 | Private Function |
464 | |
465 | Description |
466 | ----------- |
467 | Wrapper for accessing the table xla_analytical_dtl_vals. |
468 | |
469 | Returns the detail_value_id of the analytical detail value if it |
470 | exists, or inserts a new record. |
471 | |
472 | No validation is done on the parameters. |
473 | |
474 | |
475 +======================================================================*/
476
477 l_detail_value_id INTEGER;
478 l_log_module VARCHAR2 (2000);
479
480 BEGIN
481 IF g_log_enabled THEN
482 l_log_module := C_DEFAULT_MODULE||'.maintain_detail_values';
483 END IF;
484
485 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
486 trace
487 (p_module => l_log_module
488 ,p_msg => 'BEGIN ' || l_log_module
489 ,p_level => C_LEVEL_PROCEDURE);
490 END IF;
491
492 BEGIN
493 SELECT analytical_detail_value_id
494 INTO l_detail_value_id
495 FROM xla_analytical_dtl_vals
496 WHERE analytical_criterion_code = p_anacri_code
497 AND analytical_criterion_type_code = p_anacri_type_code
498 AND amb_context_code = p_amb_context_code
499 --Detail 1
500 AND NVL( analytical_detail_char_1
501 ,NVL( TO_CHAR( analytical_detail_date_1
502 ,'J'||'.'||'HH24MISS'
503 )
507 )
504 ,NVL( TO_CHAR( analytical_detail_number_1
505 ,'TM'
506 ,'NLS_NUMERIC_CHARACTERS = ''.,'''
508 ,'%'
509 )
510 )
511 )
512 = NVL( p_detail_char_1
513 ,NVL( TO_CHAR( p_detail_date_1
514 ,'J'||'.'||'HH24MISS'
515 )
516 ,NVL( TO_CHAR( p_detail_number_1
517 ,'TM'
518 ,'NLS_NUMERIC_CHARACTERS = ''.,'''
519 )
520 ,'%'
521 )
522 )
523 )
524 --Detail 2
525 AND NVL( analytical_detail_char_2
526 ,NVL( TO_CHAR( analytical_detail_date_2
527 ,'J'||'.'||'HH24MISS'
528 )
529 ,NVL( TO_CHAR( analytical_detail_number_2
530 ,'TM'
531 ,'NLS_NUMERIC_CHARACTERS = ''.,'''
532 )
533 ,'%'
534 )
535 )
536 )
537 = NVL( p_detail_char_2
538 ,NVL( TO_CHAR( p_detail_date_2
539 ,'J'||'.'||'HH24MISS'
540 )
541 ,NVL( TO_CHAR( p_detail_number_2
542 ,'TM'
543 ,'NLS_NUMERIC_CHARACTERS = ''.,'''
544 )
545 ,'%'
546 )
547 )
548 )
549 --Detail 3
550 AND NVL( analytical_detail_char_3
551 ,NVL( TO_CHAR( analytical_detail_date_3
552 ,'J'||'.'||'HH24MISS'
553 )
554 ,NVL( TO_CHAR( analytical_detail_number_3
555 ,'TM'
556 ,'NLS_NUMERIC_CHARACTERS = ''.,'''
557 )
558 ,'%'
559 )
560 )
561 )
562 = NVL( p_detail_char_3
563 ,NVL( TO_CHAR( p_detail_date_3
564 ,'J'||'.'||'HH24MISS'
565 )
566 ,NVL( TO_CHAR( p_detail_number_3
567 ,'TM'
568 ,'NLS_NUMERIC_CHARACTERS = ''.,'''
569 )
570 ,'%'
571 )
572 )
573 )
574 --Detail 4
575 AND NVL( analytical_detail_char_4
576 ,NVL( TO_CHAR( analytical_detail_date_4
577 ,'J'||'.'||'HH24MISS'
578 )
579 ,NVL( TO_CHAR( analytical_detail_number_4
580 ,'TM'
581 ,'NLS_NUMERIC_CHARACTERS = ''.,'''
582 )
583 ,'%'
584 )
585 )
586 )
587 = NVL( p_detail_char_4
588 ,NVL( TO_CHAR( p_detail_date_4
589 ,'J'||'.'||'HH24MISS'
590 )
591 ,NVL( TO_CHAR( p_detail_number_4
592 ,'TM'
593 ,'NLS_NUMERIC_CHARACTERS = ''.,'''
594 )
595 ,'%'
596 )
597 )
598 )
599 --Detail 5
600 AND NVL( analytical_detail_char_5
601 ,NVL( TO_CHAR( analytical_detail_date_5
602 ,'J'||'.'||'HH24MISS'
603 )
604 ,NVL( TO_CHAR( analytical_detail_number_5
605 ,'TM'
606 ,'NLS_NUMERIC_CHARACTERS = ''.,'''
607 )
608 ,'%'
609 )
610 )
611 )
612 = NVL( p_detail_char_5
613 ,NVL( TO_CHAR( p_detail_date_5
614 ,'J'||'.'||'HH24MISS'
615 )
616 ,NVL( TO_CHAR( p_detail_number_5
617 ,'TM'
618 ,'NLS_NUMERIC_CHARACTERS = ''.,'''
619 )
620 ,'%'
621 )
622 )
623 )
624 ;
625
626 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
627 trace
628 ( p_module => l_log_module
629 ,p_msg => SQL%ROWCOUNT
630 || ' row(s) selected from xla_analytical_dtl_vals '
631 ,p_level => C_LEVEL_STATEMENT);
632 END IF;
633
634 EXCEPTION
635 WHEN NO_DATA_FOUND THEN
636 l_detail_value_id := NULL;
637 END;
638
642 ( p_anacri_code => p_anacri_code
639 IF l_detail_value_id IS NULL
640 THEN
641 l_detail_value_id := insert_detail_value
643 ,p_anacri_type_code => p_anacri_type_code
644 ,p_amb_context_code => p_amb_context_code
645 ,p_detail_char_1 => p_detail_char_1
646 ,p_detail_char_2 => p_detail_char_2
647 ,p_detail_char_3 => p_detail_char_3
648 ,p_detail_char_4 => p_detail_char_4
649 ,p_detail_char_5 => p_detail_char_5
650 ,p_detail_date_1 => p_detail_date_1
651 ,p_detail_date_2 => p_detail_date_2
652 ,p_detail_date_3 => p_detail_date_3
653 ,p_detail_date_4 => p_detail_date_4
654 ,p_detail_date_5 => p_detail_date_5
655 ,p_detail_number_1 => p_detail_number_1
656 ,p_detail_number_2 => p_detail_number_2
657 ,p_detail_number_3 => p_detail_number_3
658 ,p_detail_number_4 => p_detail_number_4
659 ,p_detail_number_5 => p_detail_number_5
660 );
661 END IF;
662
663 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
664 trace
665 (p_module => l_log_module
666 ,p_msg => 'END ' || l_log_module
667 ,p_level => C_LEVEL_PROCEDURE);
668 END IF;
669
670 RETURN l_detail_value_id;
671
672 EXCEPTION
673 WHEN xla_exceptions_pkg.application_exception THEN
674 RAISE;
675 WHEN OTHERS THEN
676 xla_exceptions_pkg.raise_message
677 (p_location => 'xla_analytical_criteria_pkg.maintain_detail_values');
678 END maintain_detail_values;
679
680
681 PROCEDURE insert_header_detail
682 ( p_ae_header_id IN INTEGER
683 ,p_analytical_detail_value_id IN INTEGER
684 )
685 IS
686 /*======================================================================+
687 | |
688 | Private Procedure |
689 | |
690 | Description |
691 | ----------- |
692 | Inserts a new record in the table xla_ae_header_details. |
693 | |
694 +======================================================================*/
695 l_log_module VARCHAR2 (2000);
696 BEGIN
697 IF g_log_enabled THEN
698 l_log_module := C_DEFAULT_MODULE||'.insert_header_detail';
699 END IF;
700
701 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
702 trace
703 (p_module => l_log_module
704 ,p_msg => 'BEGIN ' || l_log_module
705 ,p_level => C_LEVEL_PROCEDURE);
706 END IF;
707
708 INSERT INTO xla_ae_header_details
709 ( ae_header_id
710 ,analytical_detail_value_id
711 )
712 VALUES
713 ( p_ae_header_id
714 ,p_analytical_detail_value_id
715 );
716
717 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
718 trace
719 ( p_module => l_log_module
720 ,p_msg => SQL%ROWCOUNT ||
721 ' row(s) inserted into xla_ae_headers_details '
722 ,p_level => C_LEVEL_STATEMENT);
723 END IF;
724
725 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
726 trace
727 (p_module => l_log_module
728 ,p_msg => 'END ' || l_log_module
729 ,p_level => C_LEVEL_PROCEDURE);
730 END IF;
731
732 EXCEPTION
733 WHEN xla_exceptions_pkg.application_exception THEN
734 RAISE;
735 WHEN OTHERS THEN
736 xla_exceptions_pkg.raise_message
737 (p_location => 'xla_analytical_criteria_pkg.insert_header_detail');
738 END insert_header_detail;
739
740
741 PROCEDURE insert_line_detail
742 ( p_ae_header_id IN INTEGER
743 ,p_ae_line_num IN INTEGER
744 ,p_analytical_detail_value_id IN INTEGER
745 )
746 IS
747 /*======================================================================+
748 | |
749 | Private Procedure |
750 | |
751 | Description |
752 | ----------- |
753 | Inserts a new record in the table xla_ae_line_details |
754 | |
755 | |
756 | Open issues |
757 | ----------- |
758 +======================================================================*/
759 l_log_module VARCHAR2 (2000);
760 BEGIN
761 IF g_log_enabled THEN
762 l_log_module := C_DEFAULT_MODULE||'.insert_line_detail';
763 END IF;
764
765 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
766 trace
767 (p_module => l_log_module
768 ,p_msg => 'BEGIN ' || l_log_module
769 ,p_level => C_LEVEL_PROCEDURE);
770 END IF;
771
772 INSERT INTO xla_ae_line_details
776 )
773 ( ae_header_id
774 ,ae_line_num
775 ,analytical_detail_value_id
777 VALUES
778 ( p_ae_header_id
779 ,p_ae_line_num
780 ,p_analytical_detail_value_id
781 );
782
783 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
784 trace
785 ( p_module => l_log_module
786 ,p_msg => SQL%ROWCOUNT ||
787 ' row(s) inserted into xla_ae_line_details '
788 ,p_level => C_LEVEL_STATEMENT);
789 END IF;
790
791 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
792 trace
793 (p_module => l_log_module
794 ,p_msg => 'END ' || l_log_module
795 ,p_level => C_LEVEL_PROCEDURE);
796 END IF;
797
798 EXCEPTION
799 WHEN xla_exceptions_pkg.application_exception THEN
800 RAISE;
801 WHEN OTHERS THEN
802 xla_exceptions_pkg.raise_message
803 (p_location => 'xla_analytical_criteria_pkg.insert_line_detail');
804 END insert_line_detail;
805
806
807 PROCEDURE delete_line_details
808 ( p_ae_header_id IN INTEGER
809 ,p_ae_line_num IN INTEGER
810 ,p_analytical_detail_value_id IN INTEGER
811 )
812 IS
813 /*======================================================================+
814 | |
815 | Private Procedure |
816 | |
817 | Description |
818 | ----------- |
819 | Deletes one or all (if none specified) of the line details of a |
820 | journal entry line.
821 | |
822 | Open issues |
823 | ----------- |
824 +======================================================================*/
825
826 l_details_count INTEGER;
827 l_log_module VARCHAR2 (2000);
828
829 BEGIN
830 IF g_log_enabled THEN
831 l_log_module := C_DEFAULT_MODULE||'.delete_line_details';
832 END IF;
833
834 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
835 trace
836 (p_module => l_log_module
837 ,p_msg => 'BEGIN ' || l_log_module
838 ,p_level => C_LEVEL_PROCEDURE);
839 END IF;
840
841 --If p_analytical_detail_value_id is null
842 IF p_analytical_detail_value_id IS NULL
843 THEN
844 --Remove all the line details
845 DELETE
846 FROM xla_ae_line_details
847 WHERE ae_header_id = p_ae_header_id
848 AND ae_line_num = p_ae_line_num;
849
850 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
851 trace
852 ( p_module => l_log_module
853 ,p_msg => SQL%ROWCOUNT
854 || ' row(s) deleted from xla_ae_line_details'
855 ,p_level => C_LEVEL_STATEMENT);
856 END IF;
857
858 --Else (p_analytical_detail_value_id is not null)
859 ELSE
860 --Remove only the detail specified
861 DELETE
862 FROM xla_ae_line_details
863 WHERE ae_header_id = p_ae_header_id
864 AND ae_line_num = p_ae_line_num
865 AND analytical_detail_value_id = p_analytical_detail_value_id;
866
867 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
868 trace
869 ( p_module => l_log_module
870 ,p_msg => SQL%ROWCOUNT
871 || ' row(s) deleted from xla_ae_line_details'
872 ,p_level => C_LEVEL_STATEMENT);
873 END IF;
874
875 END IF;
876
877 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
878 trace
879 (p_module => l_log_module
880 ,p_msg => 'END ' || l_log_module
881 ,p_level => C_LEVEL_PROCEDURE);
882 END IF;
883
884 EXCEPTION
885 WHEN xla_exceptions_pkg.application_exception THEN
886 RAISE;
887 WHEN OTHERS THEN
888 xla_exceptions_pkg.raise_message
889 (p_location => 'xla_analytical_criteria_pkg.delete_line_details');
890 END delete_line_details;
891
892
893 FUNCTION add_criterion
894 ( p_application_id IN INTEGER
895 ,p_ae_header_id IN INTEGER
896 ,p_ae_line_num IN INTEGER
897 ,p_anacri_code IN VARCHAR2
898 ,p_anacri_type_code IN VARCHAR2
899 ,p_amb_context_code IN VARCHAR2
900 ,p_detail_char_1 IN VARCHAR2
901 ,p_detail_char_2 IN VARCHAR2
902 ,p_detail_char_3 IN VARCHAR2
903 ,p_detail_char_4 IN VARCHAR2
904 ,p_detail_char_5 IN VARCHAR2
905 ,p_detail_date_1 IN DATE
906 ,p_detail_date_2 IN DATE
907 ,p_detail_date_3 IN DATE
908 ,p_detail_date_4 IN DATE
909 ,p_detail_date_5 IN DATE
910 ,p_detail_number_1 IN NUMBER
911 ,p_detail_number_2 IN NUMBER
912 ,p_detail_number_3 IN NUMBER
913 ,p_detail_number_4 IN NUMBER
914 ,p_detail_number_5 IN NUMBER
915 )
916 RETURN BOOLEAN
917 IS
921 | Obsolete in R12+ Supporting References Re-Architecture |
918 /*======================================================================+
919 | |
920 | Private Function |
922 | Description |
923 | ----------- |
924 | Adds a criterion to a journal entry header or line. |
925 | If p_ae_line_num is null the criterion is added to the header |
926 | otherwise to the line. |
927 | If one or more lines have already contributed to analytical balances |
928 | their contribution is removed from control and analytical balances. |
929 | |
930 +======================================================================*/
931
932 CURSOR lc_lock_ae_line_details
933 ( cp_ae_header_id INTEGER
934 ,cp_ae_line_num INTEGER
935 )
936 IS
937 SELECT 1
938 FROM xla_ae_line_details xald
939 WHERE xald.ae_header_id = cp_ae_header_id
940 AND xald.ae_line_num = cp_ae_line_num
941 FOR UPDATE NOWAIT;
942
943 l_balancing_flag VARCHAR2(1);
944 l_detail_value_id INTEGER;
945 l_analytical_balance_flag VARCHAR2(1);
946 l_log_module VARCHAR2 (2000);
947
948 BEGIN
949 IF g_log_enabled THEN
950 l_log_module := C_DEFAULT_MODULE||'.add_criterion';
951 END IF;
952
953 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
954 trace
955 (p_module => l_log_module
956 ,p_msg => 'BEGIN ' || l_log_module
957 ,p_level => C_LEVEL_PROCEDURE);
958 END IF;
959
960 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
961 trace
962 ( p_module => l_log_module
963 ,p_msg => 'p_application_id:' || p_application_id
964 ,p_level => C_LEVEL_STATEMENT);
965 trace
966 ( p_module => l_log_module
967 ,p_msg => 'p_ae_header_id:' || p_ae_header_id
968 ,p_level => C_LEVEL_STATEMENT);
969 trace
970 ( p_module => l_log_module
971 ,p_msg => 'p_anacri_code:' || p_anacri_code
972 ,p_level => C_LEVEL_STATEMENT);
973 trace
974 ( p_module => l_log_module
975 ,p_msg => 'p_anacri_type_code:' || p_anacri_type_code
976 ,p_level => C_LEVEL_STATEMENT);
977 trace
978 ( p_module => l_log_module
979 ,p_msg => 'p_amb_context_code:' || p_amb_context_code
980 ,p_level => C_LEVEL_STATEMENT);
981 END IF;
982
983 IF p_application_id IS NULL
984 OR p_ae_header_id IS NULL
985 OR p_anacri_code IS NULL
986 OR p_anacri_type_code IS NULL
987 OR p_amb_context_code IS NULL
988 THEN
989 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
990 trace
991 ( p_module => l_log_module
992 ,p_msg => 'EXCEPTION:'
993 || 'p_application_id,p_ae_header_id,p_anacri_code,'
994 || 'p_anacri_type_code, p_amb_context_code'
995 || 'cannot be NULL '
996 ,p_level => C_LEVEL_EXCEPTION);
997 END IF;
998 xla_exceptions_pkg.raise_message
999 (p_location => 'xla_analytical_criteria_pkg.add_criterion');
1000 END IF;
1001
1002 SELECT xah.balancing_flag
1003 INTO l_balancing_flag
1004 FROM xla_analytical_hdrs_b xah
1005 WHERE xah.analytical_criterion_code = p_anacri_code
1006 AND xah.analytical_criterion_type_code = p_anacri_type_code
1007 AND xah.amb_context_code = p_amb_context_code
1008 AND xah.enabled_flag = 'Y';
1009
1010 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1011 trace
1012 ( p_module => l_log_module
1013 ,p_msg => 'balancing_flag from xla_analytical_hdrs_b:'
1014 || l_balancing_flag
1015 ,p_level => C_LEVEL_STATEMENT);
1016 END IF;
1017
1018 IF p_ae_line_num IS NULL
1019 THEN
1020 --Retrieve/Create the detail value
1021 l_detail_value_id := get_detail_value_id
1022 ( p_anacri_code => p_anacri_code
1023 ,p_anacri_type_code => p_anacri_type_code
1024 ,p_amb_context_code => p_amb_context_code
1025 ,p_detail_char_1 => p_detail_char_1
1026 ,p_detail_char_2 => p_detail_char_2
1027 ,p_detail_char_3 => p_detail_char_3
1028 ,p_detail_char_4 => p_detail_char_4
1029 ,p_detail_char_5 => p_detail_char_5
1030 ,p_detail_date_1 => p_detail_date_1
1031 ,p_detail_date_2 => p_detail_date_2
1032 ,p_detail_date_3 => p_detail_date_3
1033 ,p_detail_date_4 => p_detail_date_4
1034 ,p_detail_date_5 => p_detail_date_5
1035 ,p_detail_number_1 => p_detail_number_1
1036 ,p_detail_number_2 => p_detail_number_2
1037 ,p_detail_number_3 => p_detail_number_3
1038 ,p_detail_number_4 => p_detail_number_4
1039 ,p_detail_number_5 => p_detail_number_5
1040 );
1041
1042 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1043 trace
1047 END IF;
1044 ( p_module => l_log_module
1045 ,p_msg => 'l_detail_value_id: ' || l_detail_value_id
1046 ,p_level => C_LEVEL_STATEMENT);
1048
1049 insert_header_detail
1050 ( p_ae_header_id => p_ae_header_id
1051 ,p_analytical_detail_value_id => l_detail_value_id
1052 );
1053 ELSE --p_ae_line_num IS NOT NULL
1054 IF l_balancing_flag = 'Y'
1055 THEN
1056 OPEN lc_lock_ae_line_details
1057 ( cp_ae_header_id => p_ae_header_id
1058 ,cp_ae_line_num => p_ae_line_num
1059 );
1060 CLOSE lc_lock_ae_line_details;
1061
1062 SELECT xal.analytical_balance_flag
1063 INTO l_analytical_balance_flag
1064 FROM xla_ae_lines xal
1065 WHERE xal.application_id = p_application_id
1066 AND xal.ae_header_id = p_ae_header_id
1067 AND xal.ae_line_num = p_ae_line_num;
1068
1069 IF l_analytical_balance_flag = C_ANALYTICAL_BAL_FLAG_DONE
1070 THEN
1071 IF fnd_profile.value('XLA_BAL_PARALLEL_MODE') IS NULL THEN
1072 IF NOT xla_balances_pkg.single_update
1073 ( p_application_id => p_application_id
1074 ,p_ae_header_id => p_ae_header_id
1075 ,p_ae_line_num => p_ae_line_num
1076 ,p_update_mode => 'D'
1077 )
1078 THEN
1079 IF (C_LEVEL_ERROR >= g_log_level) THEN
1080 trace
1081 ( p_module => l_log_module
1082 ,p_msg => 'Balance removal unsuccessful'
1083 ,p_level => C_LEVEL_ERROR);
1084 trace
1085 ( p_module => l_log_module
1086 ,p_msg => 'Cannot remove the details'
1087 ,p_level => C_LEVEL_ERROR);
1088 END IF;
1089 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1090 trace
1091 ( p_module => l_log_module
1092 ,p_msg => 'END ' || l_log_module
1093 ,p_level => C_LEVEL_PROCEDURE);
1094 END IF;
1095 RETURN FALSE;
1096 END IF;
1097 ELSE
1098 IF NOT xla_balances_calc_pkg.single_update
1099 ( p_application_id => p_application_id
1100 ,p_ae_header_id => p_ae_header_id
1101 ,p_ae_line_num => p_ae_line_num
1102 ,p_update_mode => 'D'
1103 )
1104 THEN
1105 IF (C_LEVEL_ERROR >= g_log_level) THEN
1106 trace
1107 ( p_module => l_log_module
1108 ,p_msg => 'Balance removal unsuccessful'
1109 ,p_level => C_LEVEL_ERROR);
1110 trace
1111 ( p_module => l_log_module
1112 ,p_msg => 'Cannot remove the details'
1113 ,p_level => C_LEVEL_ERROR);
1114 END IF;
1115 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1116 trace
1117 ( p_module => l_log_module
1118 ,p_msg => 'END ' || l_log_module
1119 ,p_level => C_LEVEL_PROCEDURE);
1120 END IF;
1121 RETURN FALSE;
1122 END IF;
1123 END IF;
1124 END IF; --l_analytical_balance_flag = C_ANALYTICAL_BAL_FLAG_DONE
1125 END IF; --l_balancing_flag = 'Y'
1126
1127 --Retrieve/Create the detail value
1128 l_detail_value_id := get_detail_value_id
1129 ( p_anacri_code => p_anacri_code
1130 ,p_anacri_type_code => p_anacri_type_code
1131 ,p_amb_context_code => p_amb_context_code
1132 ,p_detail_char_1 => p_detail_char_1
1133 ,p_detail_char_2 => p_detail_char_2
1134 ,p_detail_char_3 => p_detail_char_3
1135 ,p_detail_char_4 => p_detail_char_4
1136 ,p_detail_char_5 => p_detail_char_5
1137 ,p_detail_date_1 => p_detail_date_1
1138 ,p_detail_date_2 => p_detail_date_2
1139 ,p_detail_date_3 => p_detail_date_3
1140 ,p_detail_date_4 => p_detail_date_4
1141 ,p_detail_date_5 => p_detail_date_5
1142 ,p_detail_number_1 => p_detail_number_1
1143 ,p_detail_number_2 => p_detail_number_2
1144 ,p_detail_number_3 => p_detail_number_3
1145 ,p_detail_number_4 => p_detail_number_4
1146 ,p_detail_number_5 => p_detail_number_5
1147 );
1148
1149 insert_line_detail
1150 ( p_ae_header_id => p_ae_header_id
1151 ,p_ae_line_num => p_ae_line_num
1152 ,p_analytical_detail_value_id => l_detail_value_id
1153 );
1154
1155 IF l_balancing_flag = 'Y'
1156 THEN
1157 UPDATE xla_ae_lines xal
1158 SET xal.analytical_balance_flag = C_ANALYTICAL_BAL_FLAG_PEND
1159 WHERE xal.application_id = p_application_id
1160 AND xal.ae_header_id = p_ae_header_id
1161 AND xal.ae_line_num = p_ae_line_num;
1162 END IF;
1163
1164 END IF;
1165
1166 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1167 trace
1168 (p_module => l_log_module
1169 ,p_msg => 'END ' || l_log_module
1170 ,p_level => C_LEVEL_PROCEDURE);
1171 END IF;
1172
1173 RETURN TRUE;
1174
1175 EXCEPTION
1176 WHEN xla_exceptions_pkg.application_exception THEN
1177 RAISE;
1178 WHEN OTHERS THEN
1179 xla_exceptions_pkg.raise_message
1180 (p_location => 'xla_analytical_criteria_pkg.add_criterion');
1181
1182 END add_criterion;
1183
1184
1188 | Added for R12+ Supporting Reference Re-Architecture |
1185 /*======================================================================+
1186 | |
1187 | Private Function |
1189 | |
1190 | Description |
1191 | ----------- |
1192 | If one or more lines have already contributed to analytical balances |
1193 | their contribution is removed from control and analytical balances. |
1194 | |
1195 +======================================================================*/
1196 FUNCTION add_criterion
1197 ( p_application_id IN INTEGER
1198 ,p_ae_header_id IN INTEGER
1199 ,p_ae_line_num IN INTEGER
1200 ,p_anacri_code IN VARCHAR2
1201 ,p_anacri_type_code IN VARCHAR2
1202 ,p_amb_context_code IN VARCHAR2
1203 )
1204 RETURN BOOLEAN
1205 IS
1206
1207 CURSOR lc_lock_ae_line_details
1208 ( cp_ae_header_id INTEGER
1209 ,cp_ae_line_num INTEGER
1210 )
1211 IS
1212 SELECT 1
1213 FROM xla_ae_line_acs xald
1214 WHERE xald.ae_header_id = cp_ae_header_id
1215 AND xald.ae_line_num = cp_ae_line_num
1216 FOR UPDATE NOWAIT;
1217
1218 l_balancing_flag VARCHAR2(1);
1219 l_detail_value_id INTEGER;
1220 l_analytical_balance_flag VARCHAR2(1);
1221 l_log_module VARCHAR2 (2000);
1222
1223 BEGIN
1224 IF g_log_enabled THEN
1225 l_log_module := C_DEFAULT_MODULE||'.add_criterion';
1226 END IF;
1227
1228 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1229 trace
1230 (p_module => l_log_module
1231 ,p_msg => 'BEGIN ' || l_log_module
1232 ,p_level => C_LEVEL_PROCEDURE);
1233 END IF;
1234
1235 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1236 trace
1237 ( p_module => l_log_module
1238 ,p_msg => 'p_application_id:' || p_application_id
1239 ,p_level => C_LEVEL_STATEMENT);
1240 trace
1241 ( p_module => l_log_module
1242 ,p_msg => 'p_ae_header_id:' || p_ae_header_id
1243 ,p_level => C_LEVEL_STATEMENT);
1244 trace
1245 ( p_module => l_log_module
1246 ,p_msg => 'p_anacri_code:' || p_anacri_code
1247 ,p_level => C_LEVEL_STATEMENT);
1248 trace
1249 ( p_module => l_log_module
1250 ,p_msg => 'p_anacri_type_code:' || p_anacri_type_code
1251 ,p_level => C_LEVEL_STATEMENT);
1252 trace
1253 ( p_module => l_log_module
1254 ,p_msg => 'p_amb_context_code:' || p_amb_context_code
1255 ,p_level => C_LEVEL_STATEMENT);
1256 END IF;
1257
1258 IF p_application_id IS NULL
1259 OR p_ae_header_id IS NULL
1260 OR p_anacri_code IS NULL
1261 OR p_anacri_type_code IS NULL
1262 OR p_amb_context_code IS NULL
1263 THEN
1264 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
1265 trace
1266 ( p_module => l_log_module
1267 ,p_msg => 'EXCEPTION:'
1268 || 'p_application_id,p_ae_header_id,p_anacri_code,'
1269 || 'p_anacri_type_code, p_amb_context_code'
1270 || 'cannot be NULL '
1271 ,p_level => C_LEVEL_EXCEPTION);
1272 END IF;
1273 xla_exceptions_pkg.raise_message
1274 (p_location => 'xla_analytical_criteria_pkg.add_criterion');
1275 END IF;
1276
1277 SELECT xah.balancing_flag
1278 INTO l_balancing_flag
1279 FROM xla_analytical_hdrs_b xah
1280 WHERE xah.analytical_criterion_code = p_anacri_code
1281 AND xah.analytical_criterion_type_code = p_anacri_type_code
1282 AND xah.amb_context_code = p_amb_context_code
1283 AND xah.enabled_flag = 'Y';
1284
1285 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1286 trace
1287 ( p_module => l_log_module
1288 ,p_msg => 'balancing_flag from xla_analytical_hdrs_b:'
1289 || l_balancing_flag
1290 ,p_level => C_LEVEL_STATEMENT);
1291 END IF;
1292
1293 IF p_ae_line_num IS NOT NULL THEN
1294 IF l_balancing_flag = 'Y'
1295 THEN
1296 OPEN lc_lock_ae_line_details
1297 ( cp_ae_header_id => p_ae_header_id
1298 ,cp_ae_line_num => p_ae_line_num
1299 );
1300 CLOSE lc_lock_ae_line_details;
1301
1302 SELECT xal.analytical_balance_flag
1303 INTO l_analytical_balance_flag
1304 FROM xla_ae_lines xal
1305 WHERE xal.application_id = p_application_id
1306 AND xal.ae_header_id = p_ae_header_id
1307 AND xal.ae_line_num = p_ae_line_num;
1308
1309 IF l_analytical_balance_flag = C_ANALYTICAL_BAL_FLAG_DONE
1310 THEN
1311 IF fnd_profile.value('XLA_BAL_PARALLEL_MODE') IS NULL THEN
1312 IF NOT xla_balances_pkg.single_update
1313 ( p_application_id => p_application_id
1314 ,p_ae_header_id => p_ae_header_id
1315 ,p_ae_line_num => p_ae_line_num
1316 ,p_update_mode => 'D'
1317 )
1318 THEN
1319 IF (C_LEVEL_ERROR >= g_log_level) THEN
1320 trace
1321 ( p_module => l_log_module
1325 ( p_module => l_log_module
1322 ,p_msg => 'Balance removal unsuccessful'
1323 ,p_level => C_LEVEL_ERROR);
1324 trace
1326 ,p_msg => 'Cannot remove the details'
1327 ,p_level => C_LEVEL_ERROR);
1328 END IF;
1329 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1330 trace
1331 ( p_module => l_log_module
1332 ,p_msg => 'END ' || l_log_module
1333 ,p_level => C_LEVEL_PROCEDURE);
1334 END IF;
1335 RETURN FALSE;
1336 END IF;
1337 ELSE
1338 IF NOT xla_balances_calc_pkg.single_update
1339 ( p_application_id => p_application_id
1340 ,p_ae_header_id => p_ae_header_id
1341 ,p_ae_line_num => p_ae_line_num
1342 ,p_update_mode => 'D'
1343 )
1344 THEN
1345 IF (C_LEVEL_ERROR >= g_log_level) THEN
1346 trace
1347 ( p_module => l_log_module
1348 ,p_msg => 'Balance removal unsuccessful'
1349 ,p_level => C_LEVEL_ERROR);
1350 trace
1351 ( p_module => l_log_module
1352 ,p_msg => 'Cannot remove the details'
1353 ,p_level => C_LEVEL_ERROR);
1354 END IF;
1355 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1356 trace
1357 ( p_module => l_log_module
1358 ,p_msg => 'END ' || l_log_module
1359 ,p_level => C_LEVEL_PROCEDURE);
1360 END IF;
1361 RETURN FALSE;
1362 END IF;
1363 END IF;
1364 END IF; --l_analytical_balance_flag = C_ANALYTICAL_BAL_FLAG_DONE
1365 END IF; --l_balancing_flag = 'Y'
1366
1367 IF l_balancing_flag = 'Y'
1368 THEN
1369 UPDATE xla_ae_lines xal
1370 SET xal.analytical_balance_flag = C_ANALYTICAL_BAL_FLAG_PEND
1371 WHERE xal.application_id = p_application_id
1372 AND xal.ae_header_id = p_ae_header_id
1373 AND xal.ae_line_num = p_ae_line_num;
1374 END IF;
1375
1376 ELSE -- IF p_ae_line_num IS NOT NULL THEN
1377 --
1378 -- Do nothing for header supporting references
1379 --
1380 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1381 trace
1382 ( p_module => l_log_module
1383 ,p_msg => 'No balance update for headers.'
1384 ,p_level => C_LEVEL_STATEMENT);
1385 END IF;
1386
1387 END IF;
1388
1389 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1390 trace
1391 (p_module => l_log_module
1392 ,p_msg => 'END ' || l_log_module
1393 ,p_level => C_LEVEL_PROCEDURE);
1394 END IF;
1395
1396 RETURN TRUE;
1397
1398 EXCEPTION
1399 WHEN xla_exceptions_pkg.application_exception THEN
1400 RAISE;
1401 WHEN OTHERS THEN
1402 xla_exceptions_pkg.raise_message
1403 (p_location => 'xla_analytical_criteria_pkg.add_criterion');
1404
1405 END add_criterion;
1406
1407
1408 FUNCTION remove_criterion
1409 ( p_application_id IN INTEGER
1410 ,p_ae_header_id IN INTEGER
1411 ,p_ae_line_num IN INTEGER
1412 ,p_anacri_code IN VARCHAR2
1413 ,p_anacri_type_code IN VARCHAR2
1414 ,p_amb_context_code IN VARCHAR2
1415 ,p_analytical_detail_value_id IN INTEGER
1416 )
1417
1418 RETURN BOOLEAN
1419 IS
1420 /*======================================================================+
1421 | |
1422 | Private Function |
1423 | Obsolete in R12+ Re-Architecture. Refer to the override function | |
1424 | |
1425 | Description |
1426 | ----------- |
1427 | Removes one or all the criteria from a journal entry header or line. |
1428 | If p_ae_header_id, p_ae_line_num and p_analytical_detail_value_id |
1429 | are null ALL header and line details are removed. |
1430 | If p_ae_line_num is null and p_ae_header_id is not null header |
1431 | details are affected. |
1432 | If p_ae_line_num is not null line details are affected. |
1433 | If p_analytical_detail_value_id is not null, only that specific |
1434 | detail value is affected. |
1435 | If one or more lines have already contributed to analytical balances |
1436 | their contribution is removed from control and analytical balances. |
1437 +======================================================================*/
1438
1439 CURSOR lc_lock_ae_header_details
1440 ( cp_ae_header_id INTEGER
1441 )
1442 IS
1443 SELECT 1
1444 FROM xla_ae_header_details xahd
1445 WHERE xahd.ae_header_id = cp_ae_header_id
1446 FOR UPDATE NOWAIT;
1447
1448 CURSOR lc_lock_ae_header_detail
1449 ( cp_ae_header_id INTEGER
1450 ,cp_analytical_detail_value_id INTEGER
1451 )
1452 IS
1453 SELECT 1
1454 FROM xla_ae_header_details xahd
1455 WHERE xahd.ae_header_id = cp_ae_header_id
1456 AND xahd.analytical_detail_value_id = cp_analytical_detail_value_id
1457 FOR UPDATE NOWAIT;
1458
1459
1460 CURSOR lc_lock_ae_lines_and_details
1461 ( cp_ae_header_id INTEGER
1462 ,cp_application_id INTEGER
1463 )
1464 IS
1468 WHERE xal.ae_header_id = cp_ae_header_id
1465 SELECT 1
1466 FROM xla_ae_lines xal
1467 ,xla_ae_line_details xald
1469 AND xal.application_id = cp_application_id
1470 AND xald.ae_header_id = xal.ae_header_id
1471 FOR UPDATE NOWAIT;
1472
1473
1474 CURSOR lc_lock_ae_line_details
1475 ( cp_ae_header_id INTEGER
1476 ,cp_ae_line_num INTEGER
1477 )
1478 IS
1479 SELECT 1
1480 FROM xla_ae_line_details xald
1481 WHERE xald.ae_header_id = cp_ae_header_id
1482 AND xald.ae_line_num = cp_ae_line_num
1483 FOR UPDATE NOWAIT;
1484
1485 l_accounting_line_type_code VARCHAR2(1);
1486 l_balancing_flag VARCHAR2(1);
1487 l_analytical_balance_flag VARCHAR2(1);
1488 l_detail_value_id INTEGER;
1489 l_balanced_lined_count INTEGER;
1490 l_log_module VARCHAR2 (2000);
1491
1492 BEGIN
1493 IF g_log_enabled THEN
1494 l_log_module := C_DEFAULT_MODULE||'.remove_criterion';
1495 END IF;
1496
1497 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1498 trace
1499 (p_module => l_log_module
1500 ,p_msg => 'BEGIN ' || l_log_module
1501 ,p_level => C_LEVEL_PROCEDURE);
1502 END IF;
1503
1504 IF p_anacri_code IS NULL
1505 THEN
1506 IF p_ae_header_id IS NULL
1507 THEN
1508 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
1509 trace
1510 ( p_module => l_log_module
1511 ,p_msg => 'EXCEPTION:'
1512 ||'p_anacri_code and p_header_id cannot be both NULL'
1513 ,p_level => C_LEVEL_EXCEPTION
1514 );
1515 END IF;
1516 xla_exceptions_pkg.raise_message
1517 (p_location => 'xla_analytical_criteria_pkg.remove_criterion');
1518 ELSE
1519 IF p_ae_line_num IS NULL
1520 THEN
1521 IF p_analytical_detail_value_id IS NULL
1522 THEN
1523 --All header and line criteria must be removed
1524 OPEN lc_lock_ae_header_details
1525 ( cp_ae_header_id => p_ae_header_id
1526 );
1527 CLOSE lc_lock_ae_header_details;
1528
1529 OPEN lc_lock_ae_lines_and_details
1530 ( cp_ae_header_id => p_ae_header_id
1531 ,cp_application_id => p_application_id
1532 );
1533 CLOSE lc_lock_ae_lines_and_details;
1534
1535 SELECT COUNT(*)
1536 INTO l_balanced_lined_count
1537 FROM xla_ae_lines xal
1538 WHERE xal.application_id = p_application_id
1539 AND xal.ae_header_id = p_ae_header_id
1540 AND xal.analytical_balance_flag = C_ANALYTICAL_BAL_FLAG_DONE;
1541
1542 IF l_balanced_lined_count > 0
1543 THEN
1544 IF fnd_profile.value('XLA_BAL_PARALLEL_MODE') IS NULL THEN
1545 IF NOT xla_balances_pkg.single_update
1546 ( p_application_id => p_application_id
1547 ,p_ae_header_id => p_ae_header_id
1548 ,p_ae_line_num => NULL
1549 ,p_update_mode => 'D'
1550 )
1551 THEN
1552 IF (C_LEVEL_ERROR >= g_log_level) THEN
1553 trace
1554 ( p_module => l_log_module
1555 ,p_msg => 'Balance removal unsuccessful'
1556 ,p_level => C_LEVEL_ERROR);
1557 trace
1558 ( p_msg => 'Cannot remove the details'
1559 ,p_level => C_LEVEL_ERROR);
1560 END IF;
1561 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1562 trace
1563 (p_module => l_log_module
1564 ,p_msg => 'END ' || l_log_module
1565 ,p_level => C_LEVEL_PROCEDURE);
1566 END IF;
1567
1568 RETURN FALSE;
1569 END IF;
1570 ELSE
1571 IF NOT xla_balances_calc_pkg.single_update
1572 ( p_application_id => p_application_id
1573 ,p_ae_header_id => p_ae_header_id
1574 ,p_ae_line_num => NULL
1575 ,p_update_mode => 'D'
1576 )
1577 THEN
1578 IF (C_LEVEL_ERROR >= g_log_level) THEN
1579 trace
1580 ( p_module => l_log_module
1581 ,p_msg => 'Balance removal unsuccessful'
1582 ,p_level => C_LEVEL_ERROR);
1583 trace
1584 ( p_msg => 'Cannot remove the details'
1585 ,p_level => C_LEVEL_ERROR);
1586 END IF;
1587 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1588 trace
1589 (p_module => l_log_module
1590 ,p_msg => 'END ' || l_log_module
1591 ,p_level => C_LEVEL_PROCEDURE);
1592 END IF;
1593
1594 RETURN FALSE;
1595 END IF;
1596 END IF;
1597 END IF; --l_balanced_lined_count > 0
1598
1599 --delete all the header details
1600 DELETE
1601 FROM xla_ae_header_details xhd
1602 WHERE xhd.ae_header_id = p_ae_header_id;
1603 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1604 trace
1605 ( p_module => l_log_module
1606 ,p_msg => SQL%ROWCOUNT
1607 || ' row(s) deleted from xla_ae_header_details'
1608 ,p_level => C_LEVEL_STATEMENT);
1609 END IF;
1610
1611 --loop on all the journal entry lines
1612 FOR i IN (
1616 WHERE xah.application_id = p_application_id
1613 SELECT xal.ae_line_num
1614 FROM xla_ae_headers xah
1615 ,xla_ae_lines xal
1617 AND xah.ae_header_id = p_ae_header_id
1618 AND xal.application_id = xah.application_id
1619 AND xal.ae_header_id = xah.ae_header_id
1620 )
1621 LOOP
1622 --call delete_line_details
1623 delete_line_details
1624 ( p_ae_header_id => p_ae_header_id
1625 ,p_ae_line_num => i.ae_line_num
1626 ,p_analytical_detail_value_id => NULL
1627 );
1628 END LOOP;
1629
1630 --update the balance flag of the lines to NULL
1631 UPDATE xla_ae_lines xal
1632 SET xal.analytical_balance_flag = C_ANALYTICAL_BAL_FLAG_NO
1633 WHERE xal.application_id = p_application_id
1634 AND xal.ae_header_id = p_ae_header_id;
1635
1636 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1637 trace
1638 ( p_module => l_log_module
1639 ,p_msg => SQL%ROWCOUNT
1640 || ' row(s) updated to '
1641 || NVL(C_ANALYTICAL_BAL_FLAG_NO, 'NULL')
1642 || ' in xla_ae_lines'
1643 ,p_level => C_LEVEL_STATEMENT);
1644 END IF;
1645
1646 ELSE --p_analytical_detail_value_id IS NOT NULL
1647 OPEN lc_lock_ae_header_detail
1648
1649 ( cp_ae_header_id => p_ae_header_id
1650 ,cp_analytical_detail_value_id => p_analytical_detail_value_id
1651 );
1652 CLOSE lc_lock_ae_header_detail;
1653
1654 --delete the specified header detail
1655 DELETE
1656 FROM xla_ae_header_details xahd
1657 WHERE xahd.ae_header_id = p_ae_header_id
1658 AND xahd.analytical_detail_value_id = p_analytical_detail_value_id;
1659
1660 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1661 trace
1662 ( p_module => l_log_module
1663 ,p_msg => SQL%ROWCOUNT
1664 || ' row(s) deleted from xla_ae_header_details'
1665 ,p_level => C_LEVEL_STATEMENT);
1666 END IF;
1667 END IF;
1668
1669 ELSE --p_ae_line_num IS NOT NULL
1670
1671 OPEN lc_lock_ae_line_details
1672 ( cp_ae_header_id => p_ae_header_id
1673 ,cp_ae_line_num => p_ae_line_num
1674 );
1675
1676 CLOSE lc_lock_ae_line_details;
1677
1678 SELECT xal.analytical_balance_flag
1679 INTO l_analytical_balance_flag
1680 FROM xla_ae_lines xal
1681 WHERE xal.application_id = p_application_id
1682 AND xal.ae_header_id = p_ae_header_id
1683 AND xal.ae_line_num = p_ae_line_num;
1684
1685 IF l_analytical_balance_flag = C_ANALYTICAL_BAL_FLAG_DONE
1686 THEN
1687 IF fnd_profile.value('XLA_BAL_PARALLEL_MODE') IS NULL THEN
1688 IF NOT xla_balances_pkg.single_update
1689 ( p_application_id => p_application_id
1690 ,p_ae_header_id => p_ae_header_id
1691 ,p_ae_line_num => p_ae_line_num
1692 ,p_update_mode => 'D'
1693 )
1694 THEN
1695 IF (C_LEVEL_ERROR >= g_log_level) THEN
1696 trace
1697 ( p_module => l_log_module
1698 ,p_msg => 'Balance removal unsuccessful.'
1699 || 'Cannot remove the details.'
1700 ,p_level => C_LEVEL_ERROR
1701 );
1702 END IF;
1703 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1704 trace
1705 (p_module => l_log_module
1706 ,p_msg => 'END ' || l_log_module
1707 ,p_level => C_LEVEL_PROCEDURE);
1708 END IF;
1709 RETURN FALSE;
1710 END IF;
1711 ELSE
1712 IF NOT xla_balances_calc_pkg.single_update
1713 ( p_application_id => p_application_id
1714 ,p_ae_header_id => p_ae_header_id
1715 ,p_ae_line_num => p_ae_line_num
1716 ,p_update_mode => 'D'
1717 )
1718 THEN
1719 IF (C_LEVEL_ERROR >= g_log_level) THEN
1720 trace
1721 ( p_module => l_log_module
1722 ,p_msg => 'Balance removal unsuccessful.'
1723 || 'Cannot remove the details.'
1724 ,p_level => C_LEVEL_ERROR
1725 );
1726 END IF;
1727 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1728 trace
1729 (p_module => l_log_module
1730 ,p_msg => 'END ' || l_log_module
1731 ,p_level => C_LEVEL_PROCEDURE);
1732 END IF;
1733 RETURN FALSE;
1734 END IF;
1735 END IF;
1736 END IF;
1737
1738 IF p_analytical_detail_value_id IS NULL
1739 THEN
1740 --delete all the line details of the line
1741 delete_line_details
1742 ( p_ae_header_id => p_ae_header_id
1743 ,p_ae_line_num => p_ae_line_num
1744 ,p_analytical_detail_value_id => NULL
1745 );
1746
1747 UPDATE xla_ae_lines xal
1748 SET xal.analytical_balance_flag = C_ANALYTICAL_BAL_FLAG_NO
1749 WHERE xal.application_id = p_application_id
1753 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1750 AND xal.ae_header_id = p_ae_header_id
1751 AND xal.ae_line_num = p_ae_line_num;
1752
1754 trace
1755 ( p_module => l_log_module
1756 ,p_msg => SQL%ROWCOUNT
1757 || ' row(s) updated in xla_ae_lines: '
1758 || 'analytical_balance_flag updated to '
1759 || NVL(C_ANALYTICAL_BAL_FLAG_NO, 'NULL')
1760 ,p_level => C_LEVEL_STATEMENT
1761 );
1762 END IF;
1763
1764 ELSE --p_analytical_detail_value_id IS NOT NULL
1765
1766 --delete the specified line detail
1767 delete_line_details
1768 ( p_ae_header_id => p_ae_header_id
1769 ,p_ae_line_num => p_ae_line_num
1770 ,p_analytical_detail_value_id => p_analytical_detail_value_id
1771 );
1772
1773 --set balance flag for the line to NULL if no details left
1774 --disregarded indentation for readability
1775 UPDATE xla_ae_lines xal
1776 SET xal.analytical_balance_flag = C_ANALYTICAL_BAL_FLAG_NO
1777 WHERE xal.application_id = p_application_id
1778 AND xal.ae_header_id = p_ae_header_id
1779 AND xal.ae_line_num = p_ae_line_num
1780 AND 0 =
1781 (
1782 SELECT count(xald.ae_line_num)
1783 FROM xla_ae_line_details xald
1784 ,xla_analytical_dtl_vals xadv
1785 ,xla_analytical_hdrs_b xahb
1786 WHERE xald.ae_header_id = p_ae_header_id
1787 AND xald.ae_line_num = p_ae_line_num
1788 AND xadv.analytical_detail_value_id = xald.analytical_detail_value_id
1789 AND xahb.amb_context_code = xadv.amb_context_code
1790 AND xahb.analytical_criterion_code = xadv.analytical_criterion_code
1791 AND xahb.analytical_criterion_type_code = xadv.analytical_criterion_type_code
1792 AND xahb.balancing_flag = 'Y'
1793 );
1794
1795 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1796 trace
1797 ( p_module => l_log_module
1798 ,p_msg => SQL%ROWCOUNT
1799 || ' row(s) updated in xla_ae_lines: '
1800 || 'analytical_balance_flag updated to '
1801 || NVL(C_ANALYTICAL_BAL_FLAG_NO, 'NULL')
1802 ,p_level => C_LEVEL_STATEMENT
1803 );
1804 END IF;
1805
1806 END IF;--p_analytical_detail_value_id IS NULL
1807 END IF;--p_ae_line_num IS NULL
1808 END IF; --p_ae_header_id IS NULL
1809 ELSE --p_anacri_code IS NOT NULL
1810 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
1811 trace
1812 ( p_module => l_log_module
1813 ,p_msg => 'EXCEPTION:'
1814 ||'p_anacri_code NOT NULL currently supported.'
1815 ,p_level => C_LEVEL_EXCEPTION
1816 );
1817 END IF;
1818 xla_exceptions_pkg.raise_message
1819 (p_location => 'xla_analytical_criteria_pkg.remove_criterion');
1820 END IF; --p_anacri_code IS NOT NULL
1821
1822 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1823 trace
1824 (p_module => l_log_module
1825 ,p_msg => 'END ' || l_log_module
1826 ,p_level => C_LEVEL_PROCEDURE);
1827 END IF;
1828
1829 RETURN TRUE;
1830
1831 EXCEPTION
1832 WHEN le_resource_busy
1833 THEN
1834 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
1835 trace
1836 ( p_module => l_log_module
1837 ,p_msg => 'EXCEPTION:'
1838 ||'Unable to lock the records'
1839 ,p_level => C_LEVEL_EXCEPTION);
1840 END IF;
1841 RETURN FALSE;
1842 WHEN xla_exceptions_pkg.application_exception THEN
1843 RAISE;
1844 WHEN OTHERS THEN
1845 xla_exceptions_pkg.raise_message
1846 (p_location => 'xla_analytical_criteria_pkg.remove_criterion');
1847
1848 END remove_criterion;
1849
1850 FUNCTION remove_criterion
1851 ( p_application_id IN INTEGER
1852 ,p_ae_header_id IN INTEGER
1853 ,p_ae_line_num IN INTEGER
1854 ,p_anacri_code IN VARCHAR2
1855 ,p_anacri_type_code IN VARCHAR2
1856 ,p_amb_context_code IN VARCHAR2
1857 ,p_ac1 IN VARCHAR2
1858 ,p_ac2 IN VARCHAR2
1859 ,p_ac3 IN VARCHAR2
1860 ,p_ac4 IN VARCHAR2
1861 ,p_ac5 IN VARCHAR2
1862 )
1863
1864 RETURN BOOLEAN
1865 IS
1866 /*======================================================================+
1867 | |
1868 | Private Function |
1869 | Added for R12+ Re-Architecture. Refer to the override function | |
1870 | |
1871 | Description |
1872 | ----------- |
1873 | If p_ac<N> is not null, only that specific detail value is affected. | |
1874 | If one or more lines have already contributed to analytical balances |
1875 | their contribution is removed from control and analytical balances. |
1879 ( cp_ae_header_id INTEGER
1876 +======================================================================*/
1877
1878 CURSOR lc_lock_ae_header_details
1880 )
1881 IS
1882 SELECT 1
1883 FROM xla_ae_header_acs xahd
1884 WHERE xahd.ae_header_id = cp_ae_header_id
1885 FOR UPDATE NOWAIT;
1886
1887 CURSOR lc_lock_ae_header_detail
1888 ( cp_ae_header_id INTEGER
1889 ,cp_anacri_code VARCHAR2
1890 ,cp_anacri_type_code VARCHAR2
1891 ,cp_amb_context_code VARCHAR2
1892 )
1893 IS
1894 SELECT 1
1895 FROM xla_ae_header_acs xahd
1896 WHERE xahd.ae_header_id = cp_ae_header_id
1897 AND xahd.analytical_criterion_code = cp_anacri_code
1898 AND xahd.analytical_criterion_type_code = cp_anacri_type_code
1899 AND xahd.amb_context_code = cp_amb_context_code
1900
1901 FOR UPDATE NOWAIT;
1902
1903
1904 CURSOR lc_lock_ae_lines_and_details
1905 ( cp_ae_header_id INTEGER
1906 ,cp_application_id INTEGER
1907 )
1908 IS
1909 SELECT 1
1910 FROM xla_ae_lines xal
1911 ,xla_ae_line_acs xald
1912 WHERE xal.ae_header_id = cp_ae_header_id
1913 AND xal.application_id = cp_application_id
1914 AND xald.ae_header_id = xal.ae_header_id
1915 AND xald.ae_line_num = xal.ae_line_num ---BugNo 10408339 Added for performance issue.
1916 FOR UPDATE NOWAIT;
1917
1918
1919 CURSOR lc_lock_ae_line_details
1920 ( cp_ae_header_id INTEGER
1921 ,cp_ae_line_num INTEGER
1922 )
1923 IS
1924 SELECT 1
1925 FROM xla_ae_line_acs xald
1926 WHERE xald.ae_header_id = cp_ae_header_id
1927 AND xald.ae_line_num = cp_ae_line_num
1928 FOR UPDATE NOWAIT;
1929
1930 l_accounting_line_type_code VARCHAR2(1);
1931 l_balancing_flag VARCHAR2(1);
1932 l_analytical_balance_flag VARCHAR2(1);
1933 l_detail_value_id INTEGER;
1934 l_balanced_lined_count INTEGER;
1935 l_log_module VARCHAR2 (2000);
1936
1937 BEGIN
1938 IF g_log_enabled THEN
1939 l_log_module := C_DEFAULT_MODULE||'.remove_criterion';
1940 END IF;
1941
1942 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1943 trace
1944 (p_module => l_log_module
1945 ,p_msg => 'BEGIN ' || l_log_module
1946 ,p_level => C_LEVEL_PROCEDURE);
1947 END IF;
1948
1949 IF p_anacri_code IS NULL
1950 THEN
1951 IF p_ae_header_id IS NULL
1952 THEN
1953 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
1954 trace
1955 ( p_module => l_log_module
1956 ,p_msg => 'EXCEPTION:'
1957 ||'p_anacri_code and p_header_id cannot be both NULL'
1958 ,p_level => C_LEVEL_EXCEPTION
1959 );
1960 END IF;
1961 xla_exceptions_pkg.raise_message
1962 (p_location => 'xla_analytical_criteria_pkg.remove_criterion');
1963 ELSE
1964 IF p_ae_line_num IS NULL
1965 THEN
1966 IF (p_anacri_code IS NULL)
1967 THEN
1968 --All header and line criteria must be removed
1969 OPEN lc_lock_ae_header_details
1970 ( cp_ae_header_id => p_ae_header_id
1971 );
1972 CLOSE lc_lock_ae_header_details;
1973
1974 OPEN lc_lock_ae_lines_and_details
1975 ( cp_ae_header_id => p_ae_header_id
1976 ,cp_application_id => p_application_id
1977 );
1978 CLOSE lc_lock_ae_lines_and_details;
1979
1980 SELECT COUNT(*)
1981 INTO l_balanced_lined_count
1982 FROM xla_ae_lines xal
1983 WHERE xal.application_id = p_application_id
1984 AND xal.ae_header_id = p_ae_header_id
1985 AND xal.analytical_balance_flag = C_ANALYTICAL_BAL_FLAG_DONE;
1986
1987 IF l_balanced_lined_count > 0
1988 THEN
1989 IF fnd_profile.value('XLA_BAL_PARALLEL_MODE') IS NULL THEN
1990 IF NOT xla_balances_pkg.single_update
1991 ( p_application_id => p_application_id
1992 ,p_ae_header_id => p_ae_header_id
1993 ,p_ae_line_num => NULL
1994 ,p_update_mode => 'D'
1995 )
1996 THEN
1997 IF (C_LEVEL_ERROR >= g_log_level) THEN
1998 trace
1999 ( p_module => l_log_module
2000 ,p_msg => 'Balance removal unsuccessful'
2001 ,p_level => C_LEVEL_ERROR);
2002 trace
2003 ( p_msg => 'Cannot remove the details'
2004 ,p_level => C_LEVEL_ERROR);
2005 END IF;
2006 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2007 trace
2008 (p_module => l_log_module
2009 ,p_msg => 'END ' || l_log_module
2010 ,p_level => C_LEVEL_PROCEDURE);
2011 END IF;
2012
2013 RETURN FALSE;
2014 END IF;
2015 ELSE
2016 IF NOT xla_balances_calc_pkg.single_update
2017 ( p_application_id => p_application_id
2018 ,p_ae_header_id => p_ae_header_id
2019 ,p_ae_line_num => NULL
2020 ,p_update_mode => 'D'
2021 )
2022 THEN
2023 IF (C_LEVEL_ERROR >= g_log_level) THEN
2024 trace
2025 ( p_module => l_log_module
2029 ( p_msg => 'Cannot remove the details'
2026 ,p_msg => 'Balance removal unsuccessful'
2027 ,p_level => C_LEVEL_ERROR);
2028 trace
2030 ,p_level => C_LEVEL_ERROR);
2031 END IF;
2032 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2033 trace
2034 (p_module => l_log_module
2035 ,p_msg => 'END ' || l_log_module
2036 ,p_level => C_LEVEL_PROCEDURE);
2037 END IF;
2038
2039 RETURN FALSE;
2040 END IF;
2041 END IF;
2042 END IF; --l_balanced_lined_count > 0
2043
2044 --update the balance flag of the lines to NULL
2045 UPDATE xla_ae_lines xal
2046 SET xal.analytical_balance_flag = C_ANALYTICAL_BAL_FLAG_NO
2047 WHERE xal.application_id = p_application_id
2048 AND xal.ae_header_id = p_ae_header_id;
2049
2050 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2051 trace
2052 ( p_module => l_log_module
2053 ,p_msg => SQL%ROWCOUNT
2054 || ' row(s) updated to '
2055 || NVL(C_ANALYTICAL_BAL_FLAG_NO, 'NULL')
2056 || ' in xla_ae_lines'
2057 ,p_level => C_LEVEL_STATEMENT);
2058 END IF;
2059
2060 END IF;
2061
2062 ELSE --p_ae_line_num IS NOT NULL
2063
2064 OPEN lc_lock_ae_line_details
2065 ( cp_ae_header_id => p_ae_header_id
2066 ,cp_ae_line_num => p_ae_line_num
2067 );
2068
2069 CLOSE lc_lock_ae_line_details;
2070
2071 SELECT xal.analytical_balance_flag
2072 INTO l_analytical_balance_flag
2073 FROM xla_ae_lines xal
2074 WHERE xal.application_id = p_application_id
2075 AND xal.ae_header_id = p_ae_header_id
2076 AND xal.ae_line_num = p_ae_line_num;
2077
2078 IF l_analytical_balance_flag = C_ANALYTICAL_BAL_FLAG_DONE
2079 THEN
2080 IF fnd_profile.value('XLA_BAL_PARALLEL_MODE') IS NULL THEN
2081 IF NOT xla_balances_pkg.single_update
2082 ( p_application_id => p_application_id
2083 ,p_ae_header_id => p_ae_header_id
2084 ,p_ae_line_num => p_ae_line_num
2085 ,p_update_mode => 'D'
2086 )
2087 THEN
2088 IF (C_LEVEL_ERROR >= g_log_level) THEN
2089 trace
2090 ( p_module => l_log_module
2091 ,p_msg => 'Balance removal unsuccessful.'
2092 || 'Cannot remove the details.'
2093 ,p_level => C_LEVEL_ERROR
2094 );
2095 END IF;
2096 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2097 trace
2098 (p_module => l_log_module
2099 ,p_msg => 'END ' || l_log_module
2100 ,p_level => C_LEVEL_PROCEDURE);
2101 END IF;
2102 RETURN FALSE;
2103 END IF;
2104 ELSE
2105 IF NOT xla_balances_calc_pkg.single_update
2106 ( p_application_id => p_application_id
2107 ,p_ae_header_id => p_ae_header_id
2108 ,p_ae_line_num => p_ae_line_num
2109 ,p_update_mode => 'D'
2110 )
2111 THEN
2112 IF (C_LEVEL_ERROR >= g_log_level) THEN
2113 trace
2114 ( p_module => l_log_module
2115 ,p_msg => 'Balance removal unsuccessful.'
2116 || 'Cannot remove the details.'
2117 ,p_level => C_LEVEL_ERROR
2118 );
2119 END IF;
2120 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2121 trace
2122 (p_module => l_log_module
2123 ,p_msg => 'END ' || l_log_module
2124 ,p_level => C_LEVEL_PROCEDURE);
2125 END IF;
2126 RETURN FALSE;
2127 END IF;
2128 END IF;
2129 END IF;
2130
2131 IF (p_anacri_code IS NULL)
2132 THEN
2133
2134 UPDATE xla_ae_lines xal
2135 SET xal.analytical_balance_flag = C_ANALYTICAL_BAL_FLAG_NO
2136 WHERE xal.application_id = p_application_id
2137 AND xal.ae_header_id = p_ae_header_id
2138 AND xal.ae_line_num = p_ae_line_num;
2139
2140 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2141 trace
2142 ( p_module => l_log_module
2143 ,p_msg => SQL%ROWCOUNT
2144 || ' row(s) updated in xla_ae_lines: '
2145 || 'analytical_balance_flag updated to '
2146 || NVL(C_ANALYTICAL_BAL_FLAG_NO, 'NULL')
2147 ,p_level => C_LEVEL_STATEMENT
2148 );
2149 END IF;
2150
2151 ELSE --p_anacri_code IS NOT NULL
2152
2153 --set balance flag for the line to NULL if no details left
2154 --disregarded indentation for readability
2155 UPDATE xla_ae_lines xal
2156 SET xal.analytical_balance_flag = C_ANALYTICAL_BAL_FLAG_NO
2157 WHERE xal.application_id = p_application_id
2158 AND xal.ae_header_id = p_ae_header_id
2159 AND xal.ae_line_num = p_ae_line_num
2160 AND 0 =
2161 (
2162 SELECT count(xald.ae_line_num)
2163 FROM xla_ae_line_acs xald
2164 ,xla_analytical_hdrs_b xahb
2165 WHERE xald.ae_header_id = p_ae_header_id
2166 AND xald.ae_line_num = p_ae_line_num
2167 AND xahb.amb_context_code = xald.amb_context_code
2171 );
2168 AND xahb.analytical_criterion_code = xald.analytical_criterion_code
2169 AND xahb.analytical_criterion_type_code = xald.analytical_criterion_type_code
2170 AND xahb.balancing_flag = 'Y'
2172
2173 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2174 trace
2175 ( p_module => l_log_module
2176 ,p_msg => SQL%ROWCOUNT
2177 || ' row(s) updated in xla_ae_lines: '
2178 || 'analytical_balance_flag updated to '
2179 || NVL(C_ANALYTICAL_BAL_FLAG_NO, 'NULL')
2180 ,p_level => C_LEVEL_STATEMENT
2181 );
2182 END IF;
2183
2184 END IF;--p_anacri_code IS NULL
2185 END IF;--p_ae_line_num IS NULL
2186 END IF; --p_ae_header_id IS NULL
2187 ELSE --p_anacri_code IS NOT NULL
2188 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
2189 trace
2190 ( p_module => l_log_module
2191 ,p_msg => 'EXCEPTION:'
2192 ||'p_anacri_code NOT NULL currently supported.'
2193 ,p_level => C_LEVEL_EXCEPTION
2194 );
2195 END IF;
2196 xla_exceptions_pkg.raise_message
2197 (p_location => 'xla_analytical_criteria_pkg.remove_criterion');
2198 END IF; --p_anacri_code IS NOT NULL
2199
2200 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2201 trace
2202 (p_module => l_log_module
2203 ,p_msg => 'END ' || l_log_module
2204 ,p_level => C_LEVEL_PROCEDURE);
2205 END IF;
2206
2207 RETURN TRUE;
2208
2209 EXCEPTION
2210 WHEN le_resource_busy
2211 THEN
2212 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
2213 trace
2214 ( p_module => l_log_module
2215 ,p_msg => 'EXCEPTION:'
2216 ||'Unable to lock the records'
2217 ,p_level => C_LEVEL_EXCEPTION);
2218 END IF;
2219 RETURN FALSE;
2220 WHEN xla_exceptions_pkg.application_exception THEN
2221 RAISE;
2222 WHEN OTHERS THEN
2223 xla_exceptions_pkg.raise_message
2224 (p_location => 'xla_analytical_criteria_pkg.remove_criterion');
2225
2226 END remove_criterion;
2227
2228 FUNCTION update_detail_value ( p_application_id IN INTEGER
2229 ,p_ae_header_id IN INTEGER
2230 ,p_ae_line_num IN INTEGER
2231 ,p_list_of_criteria IN OUT NOCOPY t_list_of_criteria
2232 ,p_update_mode IN VARCHAR2
2233 )
2234 RETURN BOOLEAN
2235 IS
2236 /*======================================================================+
2237 | |
2238 | Public Function |
2239 | |
2240 | Description |
2241 | ----------- |
2242 | Refer to the Detail Level Design document |
2243 +======================================================================*/
2244
2245 CURSOR lc_lock_ae_header
2246 ( cp_application_id INTEGER
2247 ,cp_ae_header_id INTEGER
2248 )
2249 IS
2250 SELECT 1
2251 FROM xla_ae_headers xah
2252 WHERE xah.application_id = cp_application_id
2253 AND xah.ae_header_id = cp_ae_header_id
2254 FOR UPDATE NOWAIT;
2255
2256 CURSOR lc_lock_ae_header_and_line
2257 ( cp_application_id INTEGER
2258 ,cp_ae_header_id INTEGER
2259 ,cp_ae_line_num INTEGER
2260 )
2261 IS
2262 SELECT 1
2263 FROM xla_ae_headers xah
2264 ,xla_ae_lines xal
2265 WHERE xah.application_id = cp_application_id
2266 AND xah.ae_header_id = cp_ae_header_id
2267 AND xal.application_id = xah.application_id
2268 AND xal.ae_header_id = xah.ae_header_id
2269 AND xal.ae_line_num = cp_ae_line_num
2270 FOR UPDATE NOWAIT;
2271
2272 l_return_value BOOLEAN;
2273 l_analytical_balance_flag VARCHAR2( 1);
2274 l_ae_line_rowid UROWID;
2275 l_count_balanced_rows INTEGER;
2276
2277 l_log_module VARCHAR2 (2000);
2278
2279 BEGIN
2280 IF g_log_enabled THEN
2281 l_log_module := C_DEFAULT_MODULE||'.update_detail_value';
2282 END IF;
2283
2284 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2285 trace
2286 (p_module => l_log_module
2287 ,p_msg => 'BEGIN ' || l_log_module
2288 ,p_level => C_LEVEL_PROCEDURE);
2289 END IF;
2290
2291 IF p_application_id IS NULL
2292 THEN
2293 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
2294 trace
2295 ( p_module => l_log_module
2296 ,p_msg => 'EXCEPTION:'
2297 ||'p_application_id cannot be NULL'
2298 ,p_level => C_LEVEL_EXCEPTION
2299 );
2300 END IF;
2301
2302 xla_exceptions_pkg.raise_message
2303 (p_location => 'xla_analytical_criteria_pkg.update_detail_value');
2304 END IF;
2305
2306 --Lock the headers (and the lines if necessary)
2307 IF p_ae_line_num IS NULL
2308 THEN
2309 --lock the header
2310 OPEN lc_lock_ae_header
2311 ( cp_application_id => p_application_id
2312 ,cp_ae_header_id => p_ae_header_id
2313 );
2317 --lock the header and the line
2314 CLOSE lc_lock_ae_header;
2315
2316 ELSE --p_ae_line_num IS NOT NULL
2318 OPEN lc_lock_ae_header_and_line
2319 ( cp_application_id => p_application_id
2320 ,cp_ae_header_id => p_ae_header_id
2321 ,cp_ae_line_num => p_ae_line_num
2322 );
2323 CLOSE lc_lock_ae_header_and_line;
2324
2325 END IF;
2326
2327 IF p_update_mode = 'A'
2328 THEN
2329 IF p_list_of_criteria IS NULL
2330 OR p_list_of_criteria.LAST IS NULL
2331 THEN
2332 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
2333 trace
2334 ( p_module => l_log_module
2335 ,p_msg => 'EXCEPTION:'
2336 ||'p_list_of_criteria is empty'
2337 ,p_level => C_LEVEL_EXCEPTION
2338 );
2339 END IF;
2340 xla_exceptions_pkg.raise_message
2341 (p_location => 'xla_analytical_criteria_pkg.update_detail_value');
2342 END IF;
2343
2344 FOR i IN 1..p_list_of_criteria.LAST
2345 LOOP
2346 IF ( p_list_of_criteria(i).list_of_detail_chars.LAST
2347 <> p_list_of_criteria(i).list_of_detail_dates.LAST
2348 )
2349 OR ( p_list_of_criteria(i).list_of_detail_chars.LAST
2350 <> p_list_of_criteria(i).list_of_detail_numbers.LAST
2351 )
2352 THEN
2353 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
2354 trace
2355 ( p_module => l_log_module
2356 ,p_msg => 'EXCEPTION:'
2357 ||'detail list must be initialized for all datatypes'
2358 ,p_level => C_LEVEL_EXCEPTION
2359 );
2360 END IF;
2361 xla_exceptions_pkg.raise_message
2362 (p_location => 'xla_analytical_criteria_pkg.update_detail_value');
2363 END IF;
2364
2365 CASE p_list_of_criteria(i).list_of_detail_chars.LAST
2366 WHEN 0
2367 THEN
2368 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2369 trace
2370 ( p_module => l_log_module
2371 ,p_msg => 'No detail values'
2372 ,p_level => C_LEVEL_STATEMENT
2373 );
2374 END IF;
2375 l_return_value := FALSE;
2376 WHEN 1
2377 THEN
2378 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2379 trace
2380 ( p_module => l_log_module
2381 ,p_msg => 'One detail value'
2382 ,p_level => C_LEVEL_STATEMENT
2383 );
2384 END IF;
2385 l_return_value :=
2386 add_criterion
2387 ( p_application_id => p_application_id
2388 ,p_ae_header_id => p_ae_header_id
2389 ,p_ae_line_num => p_ae_line_num
2390 ,p_anacri_code => p_list_of_criteria(i).anacri_code
2391 ,p_anacri_type_code => p_list_of_criteria(i).anacri_type_code
2392 ,p_amb_context_code => p_list_of_criteria(i).amb_context_code
2393 ,p_detail_char_1 => p_list_of_criteria(i).list_of_detail_chars(1)
2394 ,p_detail_char_2 => NULL
2395 ,p_detail_char_3 => NULL
2396 ,p_detail_char_4 => NULL
2397 ,p_detail_char_5 => NULL
2398 ,p_detail_date_1 => p_list_of_criteria(i).list_of_detail_dates(1)
2399 ,p_detail_date_2 => NULL
2400 ,p_detail_date_3 => NULL
2401 ,p_detail_date_4 => NULL
2402 ,p_detail_date_5 => NULL
2403 ,p_detail_number_1 => p_list_of_criteria(i).list_of_detail_numbers(1)
2404 ,p_detail_number_2 => NULL
2405 ,p_detail_number_3 => NULL
2406 ,p_detail_number_4 => NULL
2407 ,p_detail_number_5 => NULL
2408 );
2409 WHEN 2
2410 THEN
2411 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2412 trace
2413 ( p_module => l_log_module
2414 ,p_msg => 'Two detail values'
2415 ,p_level => C_LEVEL_STATEMENT
2416 );
2417 END IF;
2418 l_return_value :=
2419 add_criterion
2420 ( p_application_id => p_application_id
2421 ,p_ae_header_id => p_ae_header_id
2422 ,p_ae_line_num => p_ae_line_num
2423 ,p_anacri_code => p_list_of_criteria(i).anacri_code
2424 ,p_anacri_type_code => p_list_of_criteria(i).anacri_type_code
2425 ,p_amb_context_code => p_list_of_criteria(i).amb_context_code
2426 ,p_detail_char_1 => p_list_of_criteria(i).list_of_detail_chars(1)
2427 ,p_detail_char_2 => p_list_of_criteria(i).list_of_detail_chars(2)
2428 ,p_detail_char_3 => NULL
2429 ,p_detail_char_4 => NULL
2430 ,p_detail_char_5 => NULL
2431 ,p_detail_date_1 => p_list_of_criteria(i).list_of_detail_dates(1)
2432 ,p_detail_date_2 => p_list_of_criteria(i).list_of_detail_dates(2)
2433 ,p_detail_date_3 => NULL
2434 ,p_detail_date_4 => NULL
2435 ,p_detail_date_5 => NULL
2439 ,p_detail_number_4 => NULL
2436 ,p_detail_number_1 => p_list_of_criteria(i).list_of_detail_numbers(1)
2437 ,p_detail_number_2 => p_list_of_criteria(i).list_of_detail_numbers(2)
2438 ,p_detail_number_3 => NULL
2440 ,p_detail_number_5 => NULL
2441 );
2442 WHEN 3
2443 THEN
2444 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2445 trace
2446 ( p_module => l_log_module
2447 ,p_msg => 'Three detail values'
2448 ,p_level => C_LEVEL_STATEMENT
2449 );
2450 END IF;
2451
2452 l_return_value :=
2453 add_criterion
2454 ( p_application_id => p_application_id
2455 ,p_ae_header_id => p_ae_header_id
2456 ,p_ae_line_num => p_ae_line_num
2457 ,p_anacri_code => p_list_of_criteria(i).anacri_code
2458 ,p_anacri_type_code => p_list_of_criteria(i).anacri_type_code
2459 ,p_amb_context_code => p_list_of_criteria(i).amb_context_code
2460 ,p_detail_char_1 => p_list_of_criteria(i).list_of_detail_chars(1)
2461 ,p_detail_char_2 => p_list_of_criteria(i).list_of_detail_chars(2)
2462 ,p_detail_char_3 => p_list_of_criteria(i).list_of_detail_chars(3)
2463 ,p_detail_char_4 => NULL
2464 ,p_detail_char_5 => NULL
2465 ,p_detail_date_1 => p_list_of_criteria(i).list_of_detail_dates(1)
2466 ,p_detail_date_2 => p_list_of_criteria(i).list_of_detail_dates(2)
2467 ,p_detail_date_3 => p_list_of_criteria(i).list_of_detail_dates(3)
2468 ,p_detail_date_4 => NULL
2469 ,p_detail_date_5 => NULL
2470 ,p_detail_number_1 => p_list_of_criteria(i).list_of_detail_numbers(1)
2471 ,p_detail_number_2 => p_list_of_criteria(i).list_of_detail_numbers(2)
2472 ,p_detail_number_3 => p_list_of_criteria(i).list_of_detail_numbers(3)
2473 ,p_detail_number_4 => NULL
2474 ,p_detail_number_5 => NULL
2475 );
2476 WHEN 4
2477 THEN
2478 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2479 trace
2480 ( p_module => l_log_module
2481 ,p_msg => 'Four detail values'
2482 ,p_level => C_LEVEL_STATEMENT
2483 );
2484 END IF;
2485 l_return_value :=
2486 add_criterion
2487 ( p_application_id => p_application_id
2488 ,p_ae_header_id => p_ae_header_id
2489 ,p_ae_line_num => p_ae_line_num
2490 ,p_anacri_code => p_list_of_criteria(i).anacri_code
2491 ,p_anacri_type_code => p_list_of_criteria(i).anacri_type_code
2492 ,p_amb_context_code => p_list_of_criteria(i).amb_context_code
2493 ,p_detail_char_1 => p_list_of_criteria(i).list_of_detail_chars(1)
2494 ,p_detail_char_2 => p_list_of_criteria(i).list_of_detail_chars(2)
2495 ,p_detail_char_3 => p_list_of_criteria(i).list_of_detail_chars(3)
2496 ,p_detail_char_4 => p_list_of_criteria(i).list_of_detail_chars(4)
2497 ,p_detail_char_5 => NULL
2498 ,p_detail_date_1 => p_list_of_criteria(i).list_of_detail_dates(1)
2499 ,p_detail_date_2 => p_list_of_criteria(i).list_of_detail_dates(2)
2500 ,p_detail_date_3 => p_list_of_criteria(i).list_of_detail_dates(3)
2501 ,p_detail_date_4 => p_list_of_criteria(i).list_of_detail_dates(4)
2502 ,p_detail_date_5 => NULL
2503 ,p_detail_number_1 => p_list_of_criteria(i).list_of_detail_numbers(1)
2504 ,p_detail_number_2 => p_list_of_criteria(i).list_of_detail_numbers(2)
2505 ,p_detail_number_3 => p_list_of_criteria(i).list_of_detail_numbers(3)
2506 ,p_detail_number_4 => p_list_of_criteria(i).list_of_detail_numbers(4)
2507 ,p_detail_number_5 => NULL
2508 );
2509 WHEN 5
2510 THEN
2511 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2512 trace
2513 ( p_module => l_log_module
2514 ,p_msg => 'Five detail values'
2515 ,p_level => C_LEVEL_STATEMENT
2516 );
2517 END IF;
2518 l_return_value :=
2519 add_criterion
2520 ( p_application_id => p_application_id
2521 ,p_ae_header_id => p_ae_header_id
2522 ,p_ae_line_num => p_ae_line_num
2523 ,p_anacri_code => p_list_of_criteria(i).anacri_code
2524 ,p_anacri_type_code => p_list_of_criteria(i).anacri_type_code
2525 ,p_amb_context_code => p_list_of_criteria(i).amb_context_code
2526 ,p_detail_char_1 => p_list_of_criteria(i).list_of_detail_chars(1)
2527 ,p_detail_char_2 => p_list_of_criteria(i).list_of_detail_chars(2)
2528 ,p_detail_char_3 => p_list_of_criteria(i).list_of_detail_chars(3)
2529 ,p_detail_char_4 => p_list_of_criteria(i).list_of_detail_chars(4)
2533 ,p_detail_date_3 => p_list_of_criteria(i).list_of_detail_dates(3)
2530 ,p_detail_char_5 => p_list_of_criteria(i).list_of_detail_chars(5)
2531 ,p_detail_date_1 => p_list_of_criteria(i).list_of_detail_dates(1)
2532 ,p_detail_date_2 => p_list_of_criteria(i).list_of_detail_dates(2)
2534 ,p_detail_date_4 => p_list_of_criteria(i).list_of_detail_dates(4)
2535 ,p_detail_date_5 => p_list_of_criteria(i).list_of_detail_chars(5)
2536 ,p_detail_number_1 => p_list_of_criteria(i).list_of_detail_numbers(1)
2537 ,p_detail_number_2 => p_list_of_criteria(i).list_of_detail_numbers(2)
2538 ,p_detail_number_3 => p_list_of_criteria(i).list_of_detail_numbers(3)
2539 ,p_detail_number_4 => p_list_of_criteria(i).list_of_detail_numbers(4)
2540 ,p_detail_number_5 => p_list_of_criteria(i).list_of_detail_chars(5)
2541 );
2542 ELSE
2543 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
2544 trace
2545 ( p_module => l_log_module
2546 ,p_msg => 'EXCEPTION:'
2547 ||'Unable to handle ' ||
2548 p_list_of_criteria(i).list_of_detail_chars.LAST
2549 || ' details.'
2550 ,p_level => C_LEVEL_EXCEPTION
2551 );
2552 END IF;
2553 xla_exceptions_pkg.raise_message
2554 (p_location => 'xla_analytical_criteria_pkg.update_detail_value');
2555 END CASE;
2556 IF NOT l_return_value
2557 THEN
2558 EXIT;
2559 END IF;
2560 END LOOP;
2561 ELSIF p_update_mode = 'D'
2562 THEN
2563 IF p_list_of_criteria IS NULL
2564 OR p_list_of_criteria.LAST IS NULL
2565 THEN
2566 l_return_value :=remove_criterion
2567 ( p_application_id => p_application_id
2568 ,p_ae_header_id => p_ae_header_id
2569 ,p_ae_line_num => p_ae_line_num
2570 ,p_anacri_code => NULL
2571 ,p_anacri_type_code => NULL
2572 ,p_amb_context_code => NULL
2573 ,p_analytical_detail_value_id => NULL
2574 );
2575 ELSE --specific criteria must be removed
2576 FOR i IN 1..p_list_of_criteria.LAST
2577 LOOP
2578 IF NOT
2579 remove_criterion
2580 ( p_application_id => p_application_id
2581 ,p_ae_header_id => p_ae_header_id
2582 ,p_ae_line_num => p_ae_line_num
2583 ,p_anacri_code => p_list_of_criteria(i).anacri_code
2584 ,p_anacri_type_code => p_list_of_criteria(i).anacri_type_code
2585 ,p_amb_context_code => p_list_of_criteria(i).amb_context_code
2586 ,p_analytical_detail_value_id => NULL
2587 )
2588 THEN
2589 l_return_value := FALSE;
2590 EXIT;
2591 END IF;
2592 END LOOP;
2593 END IF;
2594 ELSE
2595 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
2596 trace
2597 ( p_module => l_log_module
2598 ,p_msg => 'EXCEPTION:'
2599 ||'Unkown p_update_mode value: ' || p_update_mode
2600 ,p_level => C_LEVEL_EXCEPTION
2601 );
2602 END IF;
2603 xla_exceptions_pkg.raise_message
2604 (p_location => 'xla_analytical_criteria_pkg.update_detail_value');
2605 END IF;
2606
2607 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2608 trace
2609 (p_module => l_log_module
2610 ,p_msg => 'END ' || l_log_module
2611 ,p_level => C_LEVEL_PROCEDURE);
2612 END IF;
2613
2614 RETURN l_return_value;
2615
2616 EXCEPTION
2617 WHEN le_resource_busy
2618 THEN
2619 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
2620 trace
2621 ( p_module => l_log_module
2622 ,p_msg => 'EXCEPTION:'
2623 ||'Unable to lock the records'
2624 ,p_level => C_LEVEL_EXCEPTION);
2625 END IF;
2626 RETURN FALSE;
2627 WHEN xla_exceptions_pkg.application_exception THEN
2628 RAISE;
2629 WHEN OTHERS THEN
2630 xla_exceptions_pkg.raise_message
2631 (p_location => 'xla_analytical_criteria_pkg.update_detail_value');
2632
2633 END update_detail_value;
2634
2635
2636 FUNCTION single_update_detail_value
2637 ( p_application_id IN INTEGER
2638 ,p_ae_header_id IN INTEGER
2639 ,p_ae_line_num IN INTEGER
2640 ,p_analytical_detail_value_id IN INTEGER
2641 ,p_anacri_code IN VARCHAR2
2642 ,p_anacri_type_code IN VARCHAR2
2643 ,p_amb_context_code IN VARCHAR2
2644 ,p_update_mode IN VARCHAR2
2645 ,p_detail_char_1 IN VARCHAR2 DEFAULT NULL
2646 ,p_detail_date_1 IN DATE DEFAULT NULL
2647 ,p_detail_number_1 IN NUMBER DEFAULT NULL
2648 ,p_detail_char_2 IN VARCHAR2 DEFAULT NULL
2649 ,p_detail_date_2 IN DATE DEFAULT NULL
2650 ,p_detail_number_2 IN NUMBER DEFAULT NULL
2654 ,p_detail_char_4 IN VARCHAR2 DEFAULT NULL
2651 ,p_detail_char_3 IN VARCHAR2 DEFAULT NULL
2652 ,p_detail_date_3 IN DATE DEFAULT NULL
2653 ,p_detail_number_3 IN NUMBER DEFAULT NULL
2655 ,p_detail_date_4 IN DATE DEFAULT NULL
2656 ,p_detail_number_4 IN NUMBER DEFAULT NULL
2657 ,p_detail_char_5 IN VARCHAR2 DEFAULT NULL
2658 ,p_detail_date_5 IN DATE DEFAULT NULL
2659 ,p_detail_number_5 IN NUMBER DEFAULT NULL
2660 )
2661 RETURN BOOLEAN
2662 IS
2663 /*======================================================================+
2664 | |
2665 | Public Function |
2666 | Obsolete in R12+ Supporting References Re-Architecture |
2667 | No need to maintain xla_analytical_dtl_vals
2668 | From AeLineAcEOImpl.java, call update_balances. |
2669 | |
2670 | Description |
2671 | ----------- |
2672 | Replaced with update_balances in R12+ Re-Architecture |
2673 +======================================================================*/
2674 CURSOR lc_lock_ae_header
2675 ( cp_application_id INTEGER
2676 ,cp_ae_header_id INTEGER
2677 )
2678 IS
2679 SELECT 1
2680 FROM xla_ae_headers xah
2681 WHERE xah.application_id = cp_application_id
2682 AND xah.ae_header_id = cp_ae_header_id
2683 FOR UPDATE NOWAIT;
2684
2685 CURSOR lc_lock_ae_header_and_line
2686 ( cp_application_id INTEGER
2687 ,cp_ae_header_id INTEGER
2688 ,cp_ae_line_num INTEGER
2689 )
2690 IS
2691 SELECT 1
2692 FROM xla_ae_headers xah
2693 ,xla_ae_lines xal
2694 WHERE xah.application_id = cp_application_id
2695 AND xah.ae_header_id = cp_ae_header_id
2696 AND xal.application_id = xah.application_id
2697 AND xal.ae_header_id = xah.ae_header_id
2698 AND xal.ae_line_num = cp_ae_line_num
2699 FOR UPDATE NOWAIT;
2700
2701 l_return_value BOOLEAN;
2702 l_application_id INTEGER;
2703 l_analytical_balance_flag VARCHAR2( 1);
2704 l_log_module VARCHAR2 (2000);
2705
2706 BEGIN
2707 IF g_log_enabled THEN
2708 l_log_module := C_DEFAULT_MODULE||'.single_update_detail_value';
2709 END IF;
2710
2711 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2712 trace
2713 (p_module => l_log_module
2714 ,p_msg => 'BEGIN ' || l_log_module
2715 ,p_level => C_LEVEL_PROCEDURE);
2716 END IF;
2717
2718 IF p_ae_header_id IS NULL
2719 THEN
2720 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
2721 trace
2722 ( p_module => l_log_module
2723 ,p_msg => 'EXCEPTION:'
2724 ||'p_ae_header_id cannot be NULL.'
2725 ,p_level => C_LEVEL_EXCEPTION
2726 );
2727 END IF;
2728 xla_exceptions_pkg.raise_message
2729 (p_location => 'xla_analytical_criteria_pkg.single_update_detail_value');
2730 END IF;
2731
2732 IF p_ae_line_num IS NULL
2733 THEN
2734 --lock the header
2735 OPEN lc_lock_ae_header
2736 ( cp_application_id => p_application_id
2737 ,cp_ae_header_id => p_ae_header_id
2738 );
2739 CLOSE lc_lock_ae_header;
2740
2741 ELSE --p_ae_line_num IS NOT NULL
2742 --lock the header and the line
2743 OPEN lc_lock_ae_header_and_line
2744 ( cp_application_id => p_application_id
2745 ,cp_ae_header_id => p_ae_header_id
2746 ,cp_ae_line_num => p_ae_line_num
2747 );
2748 CLOSE lc_lock_ae_header_and_line;
2749
2750 END IF; --p_ae_line_num IS NULL
2751
2752 IF p_update_mode = 'A'
2753 THEN
2754 IF p_anacri_code IS NULL
2755 OR p_anacri_type_code IS NULL
2756 OR p_amb_context_code IS NULL
2757 THEN
2758 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
2759 trace
2760 ( p_module => l_log_module
2761 ,p_msg => 'EXCEPTION:'
2762 ||'When adding, p_anacri_code, p_anacri_type_code and'
2763 ||'p_amb_context_code cannot be NULL'
2764 ,p_level => C_LEVEL_EXCEPTION
2765 );
2766 END IF;
2767 xla_exceptions_pkg.raise_message
2768 (p_location => 'xla_analytical_criteria_pkg.single_update_detail_value');
2769 END IF;
2770 IF p_analytical_detail_value_id IS NOT NULL
2771 THEN
2772 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
2773 trace
2774 ( p_module => l_log_module
2775 ,p_msg => 'EXCEPTION:'
2776 ||'When adding p_analytical_detail_value_id ' ||
2777 'must be NULL'
2778 ,p_level => C_LEVEL_EXCEPTION
2779 );
2780 END IF;
2781 xla_exceptions_pkg.raise_message
2782 (p_location => 'xla_analytical_criteria_pkg.single_update_detail_value');
2783 END IF;
2784
2785 l_return_value :=
2786 add_criterion
2787 ( p_application_id => p_application_id
2791 ,p_anacri_type_code => p_anacri_type_code
2788 ,p_ae_header_id => p_ae_header_id
2789 ,p_ae_line_num => p_ae_line_num
2790 ,p_anacri_code => p_anacri_code
2792 ,p_amb_context_code => p_amb_context_code
2793 ,p_detail_char_1 => p_detail_char_1
2794 ,p_detail_char_2 => p_detail_char_2
2795 ,p_detail_char_3 => p_detail_char_3
2796 ,p_detail_char_4 => p_detail_char_4
2797 ,p_detail_char_5 => p_detail_char_5
2798 ,p_detail_date_1 => p_detail_date_1
2799 ,p_detail_date_2 => p_detail_date_2
2800 ,p_detail_date_3 => p_detail_date_3
2801 ,p_detail_date_4 => p_detail_date_4
2802 ,p_detail_date_5 => p_detail_date_5
2803 ,p_detail_number_1 => p_detail_number_1
2804 ,p_detail_number_2 => p_detail_number_2
2805 ,p_detail_number_3 => p_detail_number_3
2806 ,p_detail_number_4 => p_detail_number_4
2807 ,p_detail_number_5 => p_detail_number_5
2808 );
2809 ELSIF p_update_mode = 'D'
2810 THEN
2811 IF p_ae_header_id IS NULL
2812 THEN
2813 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
2814 trace
2815 ( p_module => l_log_module
2816 ,p_msg => 'EXCEPTION:'
2817 ||'When deleting p_ae_header_id cannot be NULL: ' ||
2818 p_ae_header_id
2819 ,p_level => C_LEVEL_EXCEPTION
2820 );
2821 END IF;
2822 xla_exceptions_pkg.raise_message
2823 (p_location => 'xla_analytical_criteria_pkg.single_update_detail_value');
2824 ELSE
2825 IF p_anacri_code IS NOT NULL
2826 OR p_anacri_type_code IS NOT NULL
2827 OR p_amb_context_code IS NOT NULL
2828 THEN
2829 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
2830 trace
2831 ( p_module => l_log_module
2832 ,p_msg => 'EXCEPTION:'
2833 ||'When deleting, p_anacri_code ,p_anacri_type_code '
2834 || 'and p_amb_context_code must be NULL.'
2835 ,p_level => C_LEVEL_EXCEPTION
2836 );
2837 END IF;
2838 xla_exceptions_pkg.raise_message
2839 (p_location => 'xla_analytical_criteria_pkg.single_update_detail_value');
2840 END IF;
2841
2842 IF p_detail_char_1 IS NOT NULL
2843 OR p_detail_char_2 IS NOT NULL
2844 OR p_detail_char_3 IS NOT NULL
2845 OR p_detail_char_4 IS NOT NULL
2846 OR p_detail_char_5 IS NOT NULL
2847 OR p_detail_date_1 IS NOT NULL
2848 OR p_detail_date_2 IS NOT NULL
2849 OR p_detail_date_3 IS NOT NULL
2850 OR p_detail_date_4 IS NOT NULL
2851 OR p_detail_date_5 IS NOT NULL
2852 OR p_detail_number_1 IS NOT NULL
2853 OR p_detail_number_2 IS NOT NULL
2854 OR p_detail_number_3 IS NOT NULL
2855 OR p_detail_number_4 IS NOT NULL
2856 OR p_detail_number_5 IS NOT NULL
2857 THEN
2858 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
2859 trace
2860 ( p_module => l_log_module
2861 ,p_msg => 'EXCEPTION:'
2862 ||'When deleting, all DETAILS must be NULL.'
2863 ,p_level => C_LEVEL_EXCEPTION );
2864 trace
2865 ( p_module => l_log_module
2866 ,p_msg => 'p_detail_char_1: ' || p_detail_char_1
2867 ,p_level => C_LEVEL_EXCEPTION );
2868 trace
2869 ( p_module => l_log_module
2870 ,p_msg => 'p_detail_char_2: ' || p_detail_char_2
2871 ,p_level => C_LEVEL_EXCEPTION );
2872 trace
2873 ( p_module => l_log_module
2874 ,p_msg => 'p_detail_char_3: ' || p_detail_char_3
2875 ,p_level => C_LEVEL_EXCEPTION );
2876 trace
2877 ( p_module => l_log_module
2878 ,p_msg => 'p_detail_char_4: ' || p_detail_char_4
2879 ,p_level => C_LEVEL_EXCEPTION );
2880 trace
2881 ( p_module => l_log_module
2882 ,p_msg => 'p_detail_char_5: ' || p_detail_char_5
2883 ,p_level => C_LEVEL_EXCEPTION );
2884 trace
2885 ( p_module => l_log_module
2886 ,p_msg => 'p_detail_date_1: ' || p_detail_date_1
2887 ,p_level => C_LEVEL_EXCEPTION );
2888 trace
2889 ( p_module => l_log_module
2890 ,p_msg => 'p_detail_date_2: ' || p_detail_date_2
2891 ,p_level => C_LEVEL_EXCEPTION );
2892 trace
2893 ( p_module => l_log_module
2894 ,p_msg => 'p_detail_date_3: ' || p_detail_date_3
2895 ,p_level => C_LEVEL_EXCEPTION );
2896 trace
2897 ( p_module => l_log_module
2898 ,p_msg => 'p_detail_date_4: ' || p_detail_date_4
2899 ,p_level => C_LEVEL_EXCEPTION );
2900 trace
2901 ( p_module => l_log_module
2902 ,p_msg => 'p_detail_date_5: ' || p_detail_date_5
2903 ,p_level => C_LEVEL_EXCEPTION );
2904 trace
2905 ( p_module => l_log_module
2906 ,p_msg => 'p_detail_number_1: ' || p_detail_number_1
2907 ,p_level => C_LEVEL_EXCEPTION );
2911 ,p_level => C_LEVEL_EXCEPTION );
2908 trace
2909 ( p_module => l_log_module
2910 ,p_msg => 'p_detail_number_2: ' || p_detail_number_2
2912 trace
2913 ( p_module => l_log_module
2914 ,p_msg => 'p_detail_number_3: ' || p_detail_number_3
2915 ,p_level => C_LEVEL_EXCEPTION );
2916 trace
2917 ( p_module => l_log_module
2918 ,p_msg => 'p_detail_number_4: ' || p_detail_number_4
2919 ,p_level => C_LEVEL_EXCEPTION );
2920 trace
2921 ( p_module => l_log_module
2922 ,p_msg => 'p_detail_number_5: ' || p_detail_number_5
2923 ,p_level => C_LEVEL_EXCEPTION );
2924 END IF;
2925
2926 xla_exceptions_pkg.raise_message
2927 (p_location => 'xla_analytical_criteria_pkg.single_update_detail_value');
2928 END IF;
2929
2930 l_return_value :=
2931 remove_criterion
2932 ( p_ae_header_id => p_ae_header_id
2933 ,p_ae_line_num => p_ae_line_num
2934 ,p_application_id => p_application_id
2935 ,p_anacri_code => p_anacri_code
2936 ,p_anacri_type_code => p_anacri_type_code
2937 ,p_amb_context_code => p_amb_context_code
2938 ,p_analytical_detail_value_id => p_analytical_detail_value_id
2939 );
2940 END IF;
2941 ELSE
2942 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
2943 trace
2944 ( p_module => l_log_module
2945 ,p_msg => 'EXCEPTION:'
2946 ||'Unkown p_update_mode value: '
2947 || p_update_mode
2948 ,p_level => C_LEVEL_EXCEPTION
2949 );
2950 END IF;
2951 xla_exceptions_pkg.raise_message
2952 (p_location => 'xla_analytical_criteria_pkg.single_update_detail_value');
2953 END IF;
2954
2955 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2956 trace
2957 (p_module => l_log_module
2958 ,p_msg => 'END ' || l_log_module
2959 ,p_level => C_LEVEL_PROCEDURE);
2960 END IF;
2961 RETURN l_return_value;
2962
2963 EXCEPTION
2964 WHEN le_resource_busy
2965 THEN
2966 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
2967 trace
2968 ( p_module => l_log_module
2969 ,p_msg => 'EXCEPTION:'
2970 ||'Unable to lock the records'
2971 ,p_level => C_LEVEL_EXCEPTION);
2972 END IF;
2973 RETURN FALSE;
2974 WHEN xla_exceptions_pkg.application_exception THEN
2975 RAISE;
2976 WHEN OTHERS THEN
2977 xla_exceptions_pkg.raise_message
2978 (p_location => 'xla_analytical_criteria_pkg.single_update_detail_value');
2979 END single_update_detail_value;
2980
2981
2982 FUNCTION single_update_detail_value
2983 ( p_application_id IN INTEGER
2984 ,p_ae_header_id IN INTEGER
2985 ,p_ae_line_num IN INTEGER
2986 ,p_anacri_code IN VARCHAR2
2987 ,p_anacri_type_code IN VARCHAR2
2988 ,p_amb_context_code IN VARCHAR2
2989 ,p_update_mode IN VARCHAR2
2990 ,p_ac1 IN VARCHAR2 DEFAULT NULL
2991 ,p_ac2 IN VARCHAR2 DEFAULT NULL
2992 ,p_ac3 IN VARCHAR2 DEFAULT NULL
2993 ,p_ac4 IN VARCHAR2 DEFAULT NULL
2994 ,p_ac5 IN VARCHAR2 DEFAULT NULL
2995 )
2996 RETURN BOOLEAN IS
2997 /*======================================================================+
2998 | |
2999 | Public Function |
3000 |
3001 | Description |
3002 | ----------- |
3003 | Added for R12+ Supporting References Re-Architecture |
3004 | Called From: |
3005 | - AeLineAcEOImpl.java |
3006 +======================================================================*/
3007
3008 CURSOR lc_lock_ae_header
3009 ( cp_application_id INTEGER
3010 ,cp_ae_header_id INTEGER
3011 )
3012 IS
3013 SELECT 1
3014 FROM xla_ae_headers xah
3015 WHERE xah.application_id = cp_application_id
3016 AND xah.ae_header_id = cp_ae_header_id
3017 FOR UPDATE NOWAIT;
3018
3019 CURSOR lc_lock_ae_header_and_line
3020 ( cp_application_id INTEGER
3021 ,cp_ae_header_id INTEGER
3022 ,cp_ae_line_num INTEGER
3023 )
3024 IS
3025 SELECT 1
3026 FROM xla_ae_headers xah
3027 ,xla_ae_lines xal
3028 WHERE xah.application_id = cp_application_id
3029 AND xah.ae_header_id = cp_ae_header_id
3030 AND xal.application_id = xah.application_id
3031 AND xal.ae_header_id = xah.ae_header_id
3032 AND xal.ae_line_num = cp_ae_line_num
3033 FOR UPDATE NOWAIT;
3034
3035 l_return_value BOOLEAN;
3036 l_application_id INTEGER;
3040 BEGIN
3037 l_analytical_balance_flag VARCHAR2( 1);
3038 l_log_module VARCHAR2 (2000);
3039
3041 IF g_log_enabled THEN
3042 l_log_module := C_DEFAULT_MODULE||'.update_balances';
3043 END IF;
3044
3045 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3046 trace
3047 (p_module => l_log_module
3048 ,p_msg => 'BEGIN ' || l_log_module
3049 ,p_level => C_LEVEL_PROCEDURE);
3050 END IF;
3051
3052 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3053 trace
3054 ( p_module => l_log_module
3055 ,p_msg => 'p_ac1: ' || p_ac1
3056 ,p_level => C_LEVEL_EXCEPTION );
3057 trace
3058 ( p_module => l_log_module
3059 ,p_msg => 'p_ac2: ' || p_ac2
3060 ,p_level => C_LEVEL_EXCEPTION );
3061 trace
3062 ( p_module => l_log_module
3063 ,p_msg => 'p_ac3: ' || p_ac3
3064 ,p_level => C_LEVEL_EXCEPTION );
3065 trace
3066 ( p_module => l_log_module
3067 ,p_msg => 'p_ac4: ' || p_ac4
3068 ,p_level => C_LEVEL_EXCEPTION );
3069 trace
3070 ( p_module => l_log_module
3071 ,p_msg => 'p_ac5: ' || p_ac5
3072 ,p_level => C_LEVEL_EXCEPTION );
3073
3074 END IF;
3075
3076
3077 IF p_ae_header_id IS NULL
3078 THEN
3079 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
3080 trace
3081 ( p_module => l_log_module
3082 ,p_msg => 'EXCEPTION:'
3083 ||'p_ae_header_id cannot be NULL.'
3084 ,p_level => C_LEVEL_EXCEPTION
3085 );
3086 END IF;
3087 xla_exceptions_pkg.raise_message
3088 (p_location => 'xla_analytical_criteria_pkg.single_update_detail_value');
3089 END IF;
3090
3091 IF p_ae_line_num IS NULL
3092 THEN
3093 --lock the header
3094 OPEN lc_lock_ae_header
3095 ( cp_application_id => p_application_id
3096 ,cp_ae_header_id => p_ae_header_id
3097 );
3098 CLOSE lc_lock_ae_header;
3099
3100 ELSE --p_ae_line_num IS NOT NULL
3101 --lock the header and the line
3102 OPEN lc_lock_ae_header_and_line
3103 ( cp_application_id => p_application_id
3104 ,cp_ae_header_id => p_ae_header_id
3105 ,cp_ae_line_num => p_ae_line_num
3106 );
3107 CLOSE lc_lock_ae_header_and_line;
3108
3109 END IF; --p_ae_line_num IS NULL
3110
3111 IF p_update_mode = 'A'
3112 THEN
3113 IF p_anacri_code IS NULL
3114 OR p_anacri_type_code IS NULL
3115 OR p_amb_context_code IS NULL
3116 THEN
3117 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
3118 trace
3119 ( p_module => l_log_module
3120 ,p_msg => 'EXCEPTION:'
3121 ||'When adding, p_anacri_code, p_anacri_type_code and'
3122 ||'p_amb_context_code cannot be NULL'
3123 ,p_level => C_LEVEL_EXCEPTION
3124 );
3125 END IF;
3126 xla_exceptions_pkg.raise_message
3127 (p_location => 'xla_analytical_criteria_pkg.single_update_detail_value');
3128 END IF;
3129
3130 l_return_value :=
3131 add_criterion
3132 ( p_application_id => p_application_id
3133 ,p_ae_header_id => p_ae_header_id
3134 ,p_ae_line_num => p_ae_line_num
3135 ,p_anacri_code => p_anacri_code
3136 ,p_anacri_type_code => p_anacri_type_code
3137 ,p_amb_context_code => p_amb_context_code
3138 );
3139
3140 ELSIF p_update_mode = 'D'
3141 THEN
3142 IF p_ae_header_id IS NULL
3143 THEN
3144 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
3145 trace
3146 ( p_module => l_log_module
3147 ,p_msg => 'EXCEPTION:'
3148 ||'When deleting p_ae_header_id cannot be NULL: ' ||
3149 p_ae_header_id
3150 ,p_level => C_LEVEL_EXCEPTION
3151 );
3152 END IF;
3153 xla_exceptions_pkg.raise_message
3154 (p_location => 'xla_analytical_criteria_pkg.single_update_detail_value');
3155 ELSE
3156 IF p_anacri_code IS NOT NULL
3157 OR p_anacri_type_code IS NOT NULL
3158 OR p_amb_context_code IS NOT NULL
3159 THEN
3160 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
3161 trace
3162 ( p_module => l_log_module
3163 ,p_msg => 'EXCEPTION:'
3164 ||'When deleting, p_anacri_code ,p_anacri_type_code '
3165 || 'and p_amb_context_code must be NULL.'
3166 ,p_level => C_LEVEL_EXCEPTION
3167 );
3168 END IF;
3169 xla_exceptions_pkg.raise_message
3170 (p_location => 'xla_analytical_criteria_pkg.single_update_detail_value');
3171 END IF;
3172
3173 l_return_value :=
3174 remove_criterion
3175 ( p_ae_header_id => p_ae_header_id
3176 ,p_ae_line_num => p_ae_line_num
3177 ,p_application_id => p_application_id
3178 ,p_anacri_code => p_anacri_code
3179 ,p_anacri_type_code => p_anacri_type_code
3180 ,p_amb_context_code => p_amb_context_code
3184 ,p_ac4 => p_ac4
3181 ,p_ac1 => p_ac1
3182 ,p_ac2 => p_ac2
3183 ,p_ac3 => p_ac3
3185 ,p_ac5 => p_ac5
3186 );
3187 END IF;
3188 ELSE
3189 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
3190 trace
3191 ( p_module => l_log_module
3192 ,p_msg => 'EXCEPTION:'
3193 ||'Unkown p_update_mode value: '
3194 || p_update_mode
3195 ,p_level => C_LEVEL_EXCEPTION
3196 );
3197 END IF;
3198 xla_exceptions_pkg.raise_message
3199 (p_location => 'xla_analytical_criteria_pkg.single_update_detail_value');
3200 END IF;
3201
3202 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3203 trace
3204 (p_module => l_log_module
3205 ,p_msg => 'END ' || l_log_module
3206 ,p_level => C_LEVEL_PROCEDURE);
3207 END IF;
3208 RETURN l_return_value;
3209
3210 EXCEPTION
3211 WHEN le_resource_busy
3212 THEN
3213 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
3214 trace
3215 ( p_module => l_log_module
3216 ,p_msg => 'EXCEPTION:'
3217 ||'Unable to lock the records'
3218 ,p_level => C_LEVEL_EXCEPTION);
3219 END IF;
3220 RETURN FALSE;
3221 WHEN xla_exceptions_pkg.application_exception THEN
3222 RAISE;
3223 WHEN OTHERS THEN
3224 xla_exceptions_pkg.raise_message
3225 (p_location => 'xla_analytical_criteria_pkg.single_update_detail_value');
3226
3227 END single_update_detail_value;
3228
3229
3230
3231 FUNCTION get_detail_value_id
3232 ( p_anacri_code IN VARCHAR2
3233 ,p_anacri_type_code IN VARCHAR2
3234 ,p_amb_context_code IN VARCHAR2
3235 ,p_detail_char_1 IN VARCHAR2 DEFAULT NULL
3236 ,p_detail_date_1 IN DATE DEFAULT NULL
3237 ,p_detail_number_1 IN NUMBER DEFAULT NULL
3238 ,p_detail_char_2 IN VARCHAR2 DEFAULT NULL
3239 ,p_detail_date_2 IN DATE DEFAULT NULL
3240 ,p_detail_number_2 IN NUMBER DEFAULT NULL
3241 ,p_detail_char_3 IN VARCHAR2 DEFAULT NULL
3242 ,p_detail_date_3 IN DATE DEFAULT NULL
3243 ,p_detail_number_3 IN NUMBER DEFAULT NULL
3244 ,p_detail_char_4 IN VARCHAR2 DEFAULT NULL
3245 ,p_detail_date_4 IN DATE DEFAULT NULL
3246 ,p_detail_number_4 IN NUMBER DEFAULT NULL
3247 ,p_detail_char_5 IN VARCHAR2 DEFAULT NULL
3248 ,p_detail_date_5 IN DATE DEFAULT NULL
3249 ,p_detail_number_5 IN NUMBER DEFAULT NULL
3250 )
3251 RETURN INTEGER
3252 IS
3253 /*======================================================================+
3254 | |
3255 | Public Function |
3256 | |
3257 | Description |
3258 | ----------- |
3259 | Refer to the Detail Level Design document |
3260 +======================================================================*/
3261
3262 l_detail_value_id INTEGER;
3263 l_loop_count PLS_INTEGER := 0;
3264 l_current_pos PLS_INTEGER := 0;
3265 l_cache_elm_count PLS_INTEGER;
3266
3267 l_log_module VARCHAR2 (2000);
3268 l_det_1 VARCHAR2(240);
3269 l_det_2 VARCHAR2(240);
3270 l_det_3 VARCHAR2(240);
3271 l_det_4 VARCHAR2(240);
3272 l_det_5 VARCHAR2(240);
3273
3274 BEGIN
3275
3276 IF g_log_enabled THEN
3277 l_log_module := C_DEFAULT_MODULE||'.get_detail_value_id';
3278 END IF;
3279
3280 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3281 trace
3282 (p_module => l_log_module
3283 ,p_msg => 'BEGIN ' || l_log_module
3284 ,p_level => C_LEVEL_PROCEDURE);
3285 END IF;
3286
3287 IF p_anacri_code IS NULL
3288 OR p_anacri_type_code IS NULL
3289 OR p_amb_context_code IS NULL
3290 THEN
3291 IF (C_LEVEL_EXCEPTION >= g_log_level)
3292 THEN
3293 trace
3294 ( p_module => l_log_module
3295 ,p_msg => 'EXCEPTION:'
3296 ||'When deleting p_anacri_code, p_anacri_type_code, ' ||
3297 'p_amb_context_code cannot be NULL.'
3298 ,p_level => C_LEVEL_EXCEPTION
3299 );
3300 END IF;
3301 xla_exceptions_pkg.raise_message
3302 (p_location => 'xla_analytical_criteria_pkg..get_detail_value_id');
3303 END IF;
3304
3305 IF (C_LEVEL_STATEMENT >= g_log_level)
3306 THEN
3307 trace
3308 ( p_module => l_log_module
3309 ,p_msg => 'Input parameters:'
3310 ,p_level => C_LEVEL_STATEMENT );
3311 trace
3312 ( p_module => l_log_module
3313 ,p_msg => 'p_detail_char_1 :' || p_detail_char_1
3314 ,p_level => C_LEVEL_STATEMENT );
3315 trace
3316 ( p_module => l_log_module
3320 ( p_module => l_log_module
3317 ,p_msg => 'p_detail_date_1 : ' || p_detail_date_1
3318 ,p_level => C_LEVEL_STATEMENT );
3319 trace
3321 ,p_msg => 'p_detail_number_1: ' || p_detail_number_1
3322 ,p_level => C_LEVEL_STATEMENT );
3323 trace
3324 ( p_module => l_log_module
3325 ,p_msg => 'p_detail_char_2 : ' || p_detail_char_2
3326 ,p_level => C_LEVEL_STATEMENT );
3327 trace
3328 ( p_module => l_log_module
3329 ,p_msg => 'p_detail_date_2 : ' || p_detail_date_2
3330 ,p_level => C_LEVEL_STATEMENT );
3331 trace
3332 ( p_module => l_log_module
3333 ,p_msg => 'p_detail_number_2: ' || p_detail_number_2
3334 ,p_level => C_LEVEL_STATEMENT );
3335 trace
3336 ( p_module => l_log_module
3337 ,p_msg => 'p_detail_char_3 : ' || p_detail_char_3
3338 ,p_level => C_LEVEL_STATEMENT );
3339 trace
3340 ( p_module => l_log_module
3341 ,p_msg => 'p_detail_date_3 : ' || p_detail_date_3
3342 ,p_level => C_LEVEL_STATEMENT );
3343 trace
3344 ( p_module => l_log_module
3345 ,p_msg => 'p_detail_number_3: ' || p_detail_number_3
3346 ,p_level => C_LEVEL_STATEMENT );
3347 trace
3348 ( p_module => l_log_module
3349 ,p_msg => 'p_detail_char_4 : ' || p_detail_char_4
3350 ,p_level => C_LEVEL_STATEMENT );
3351 trace
3352 ( p_module => l_log_module
3353 ,p_msg => 'p_detail_date_4 : ' || p_detail_date_4
3354 ,p_level => C_LEVEL_STATEMENT );
3355 trace
3356 ( p_module => l_log_module
3357 ,p_msg => 'p_detail_number_4: ' || p_detail_number_4
3358 ,p_level => C_LEVEL_STATEMENT );
3359 trace
3360 ( p_module => l_log_module
3361 ,p_msg => 'p_detail_char_5 : ' || p_detail_char_5
3362 ,p_level => C_LEVEL_STATEMENT );
3363 trace
3364 ( p_module => l_log_module
3365 ,p_msg => 'p_detail_date_5 : ' || p_detail_date_5
3366 ,p_level => C_LEVEL_STATEMENT );
3367 trace
3368 ( p_module => l_log_module
3369 ,p_msg => 'p_detail_number_5: ' || p_detail_number_5
3370 ,p_level => C_LEVEL_STATEMENT );
3371 END IF;
3372
3373 IF ( p_detail_char_1 IS NOT NULL AND ( p_detail_date_1 IS NOT NULL
3374 OR p_detail_number_1 IS NOT NULL)
3375 )
3376 OR ( p_detail_char_2 IS NOT NULL AND ( p_detail_date_2 IS NOT NULL
3377 OR p_detail_number_2 IS NOT NULL)
3378 )
3379 OR ( p_detail_char_3 IS NOT NULL AND ( p_detail_date_3 IS NOT NULL
3380 OR p_detail_number_3 IS NOT NULL)
3381 )
3382 OR ( p_detail_char_4 IS NOT NULL AND ( p_detail_date_4 IS NOT NULL
3383 OR p_detail_number_4 IS NOT NULL)
3384 )
3385 OR ( p_detail_char_5 IS NOT NULL AND ( p_detail_date_5 IS NOT NULL
3386 OR p_detail_number_5 IS NOT NULL)
3387 )
3388 OR (p_detail_date_1 IS NOT NULL AND p_detail_number_1 IS NOT NULL)
3389 OR (p_detail_date_2 IS NOT NULL AND p_detail_number_2 IS NOT NULL)
3390 OR (p_detail_date_3 IS NOT NULL AND p_detail_number_3 IS NOT NULL)
3391 OR (p_detail_date_4 IS NOT NULL AND p_detail_number_4 IS NOT NULL)
3392 OR (p_detail_date_5 IS NOT NULL AND p_detail_number_5 IS NOT NULL)
3393 THEN
3394 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
3395 trace
3396 ( p_module => l_log_module
3397 ,p_msg => 'EXCEPTION:'
3398 ||'At most one detail of each triple can have a value'
3399 ,p_level => C_LEVEL_EXCEPTION
3400 );
3401 END IF;
3402 xla_exceptions_pkg.raise_message
3403 (p_location => 'xla_analytical_criteria_pkg..get_detail_value_id');
3404 END IF;
3405 l_cache_elm_count := ga_anacri_code.COUNT;
3406 l_current_pos := g_anacri_cache_next_avail_pos - 1 ;
3407
3408 --Retrieve the converted segment values
3409 l_det_1 := format_detail_value ( p_detail_char => p_detail_char_1
3410 ,p_detail_date => p_detail_date_1
3411 ,p_detail_number => p_detail_number_1
3412 );
3413 l_det_2 := format_detail_value ( p_detail_char => p_detail_char_2
3414 ,p_detail_date => p_detail_date_2
3415 ,p_detail_number => p_detail_number_2
3416 );
3417 l_det_3 := format_detail_value ( p_detail_char => p_detail_char_3
3418 ,p_detail_date => p_detail_date_3
3419 ,p_detail_number => p_detail_number_3
3420 );
3421 l_det_4 := format_detail_value ( p_detail_char => p_detail_char_4
3422 ,p_detail_date => p_detail_date_4
3423 ,p_detail_number => p_detail_number_4
3424 );
3425 l_det_5 := format_detail_value ( p_detail_char => p_detail_char_5
3426 ,p_detail_date => p_detail_date_5
3427 ,p_detail_number => p_detail_number_5
3428 );
3429
3430 WHILE l_loop_count < l_cache_elm_count
3431 LOOP
3432 IF l_current_pos = 0
3433 THEN
3434 l_current_pos := l_cache_elm_count;
3435 END IF;
3439 AND ga_amb_context_code (l_current_pos) = p_amb_context_code
3436
3437 IF ga_anacri_code (l_current_pos) = p_anacri_code
3438 AND ga_anacri_type_code (l_current_pos) = p_anacri_type_code
3440 AND l_det_1
3441 = ga_anacri_detail_char_1 (l_current_pos)
3442
3443 AND l_det_2
3444 = ga_anacri_detail_char_2 (l_current_pos)
3445
3446 AND l_det_3
3447 = ga_anacri_detail_char_3 (l_current_pos)
3448
3449 AND l_det_4
3450 = ga_anacri_detail_char_4 (l_current_pos)
3451
3452 AND l_det_5
3453 = ga_anacri_detail_char_5 (l_current_pos)
3454
3455 THEN
3456 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3457 trace
3458 ( p_module => l_log_module
3459 ,p_msg => 'Cache hit: POS(' || l_current_pos || '), ID: '
3460 || ga_anacri_id(l_current_pos)
3461 ,p_level => C_LEVEL_STATEMENT
3462 );
3463 END IF;
3464 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3465 trace
3466 (p_module => l_log_module
3467 ,p_msg => 'END ' || l_log_module
3468 ,p_level => C_LEVEL_PROCEDURE);
3469 END IF;
3470 RETURN ga_anacri_id(l_current_pos);
3471 END IF;
3472
3473 l_loop_count := l_loop_count + 1;
3474 l_current_pos := l_current_pos - 1;
3475
3476 IF l_current_pos = 0
3477 THEN
3478 l_current_pos := l_cache_elm_count;
3479 END IF;
3480
3481 END LOOP;
3482
3483 l_detail_value_id := maintain_detail_values
3484 ( p_anacri_code => p_anacri_code
3485 ,p_anacri_type_code => p_anacri_type_code
3486 ,p_amb_context_code => p_amb_context_code
3487 ,p_detail_char_1 => p_detail_char_1
3488 ,p_detail_char_2 => p_detail_char_2
3489 ,p_detail_char_3 => p_detail_char_3
3490 ,p_detail_char_4 => p_detail_char_4
3491 ,p_detail_char_5 => p_detail_char_5
3492 ,p_detail_date_1 => p_detail_date_1
3493 ,p_detail_date_2 => p_detail_date_2
3494 ,p_detail_date_3 => p_detail_date_3
3495 ,p_detail_date_4 => p_detail_date_4
3496 ,p_detail_date_5 => p_detail_date_5
3497 ,p_detail_number_1 => p_detail_number_1
3498 ,p_detail_number_2 => p_detail_number_2
3499 ,p_detail_number_3 => p_detail_number_3
3500 ,p_detail_number_4 => p_detail_number_4
3501 ,p_detail_number_5 => p_detail_number_5
3502 );
3503
3504 ga_anacri_id (g_anacri_cache_next_avail_pos)
3505 := l_detail_value_id;
3506 ga_anacri_code (g_anacri_cache_next_avail_pos)
3507 := p_anacri_code;
3508 ga_anacri_type_code (g_anacri_cache_next_avail_pos)
3509 := p_anacri_type_code;
3510 ga_amb_context_code (g_anacri_cache_next_avail_pos)
3511 := p_amb_context_code;
3512
3513 ga_anacri_detail_char_1 (g_anacri_cache_next_avail_pos)
3514 := l_det_1;
3515 ga_anacri_detail_char_2 (g_anacri_cache_next_avail_pos)
3516 := l_det_2;
3517
3518 ga_anacri_detail_char_3 (g_anacri_cache_next_avail_pos)
3519 := l_det_3;
3520
3521 ga_anacri_detail_char_4 (g_anacri_cache_next_avail_pos)
3522 := l_det_4;
3523
3524 ga_anacri_detail_char_5 (g_anacri_cache_next_avail_pos)
3525 := l_det_5;
3526
3527 IF g_anacri_cache_next_avail_pos = C_ANACRI_CACHE_MAX_SIZE
3528 THEN
3529 g_anacri_cache_next_avail_pos := 1;
3530 ELSE
3531 g_anacri_cache_next_avail_pos := g_anacri_cache_next_avail_pos + 1;
3532 END IF;
3533
3534 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3535 trace
3536 ( p_module => l_log_module
3537 ,p_msg => 'Returned ID: ' || l_detail_value_id
3538 ,p_level => C_LEVEL_STATEMENT
3539 );
3540 END IF;
3541
3542 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3543 trace
3544 (p_module => l_log_module
3545 ,p_msg => 'END ' || l_log_module
3546 ,p_level => C_LEVEL_PROCEDURE);
3547 END IF;
3548
3549 RETURN l_detail_value_id;
3550
3551 EXCEPTION
3552 WHEN xla_exceptions_pkg.application_exception THEN
3553 RAISE;
3554 WHEN OTHERS THEN
3555 xla_exceptions_pkg.raise_message
3556 (p_location => 'xla_analytical_criteria_pkg.get_detail_value_id');
3557
3558
3559 END get_detail_value_id;
3560
3561 FUNCTION concat_detail_values
3562 ( p_anacri_code IN VARCHAR2
3563 ,p_anacri_type_code IN VARCHAR2
3564 ,p_amb_context_code IN VARCHAR2
3565 ,p_detail_char_1 IN VARCHAR2 DEFAULT NULL
3566 ,p_detail_date_1 IN DATE DEFAULT NULL
3567 ,p_detail_number_1 IN NUMBER DEFAULT NULL
3568 ,p_detail_char_2 IN VARCHAR2 DEFAULT NULL
3569 ,p_detail_date_2 IN DATE DEFAULT NULL
3570 ,p_detail_number_2 IN NUMBER DEFAULT NULL
3571 ,p_detail_char_3 IN VARCHAR2 DEFAULT NULL
3572 ,p_detail_date_3 IN DATE DEFAULT NULL
3573 ,p_detail_number_3 IN NUMBER DEFAULT NULL
3574 ,p_detail_char_4 IN VARCHAR2 DEFAULT NULL
3575 ,p_detail_date_4 IN DATE DEFAULT NULL
3579 ,p_detail_number_5 IN NUMBER DEFAULT NULL
3576 ,p_detail_number_4 IN NUMBER DEFAULT NULL
3577 ,p_detail_char_5 IN VARCHAR2 DEFAULT NULL
3578 ,p_detail_date_5 IN DATE DEFAULT NULL
3580 )
3581 RETURN VARCHAR2
3582 IS
3583 /*======================================================================+
3584 | |
3585 | Public Function |
3586 | |
3587 | Description |
3588 | ----------- |
3589 | Refer to the Detail Level Design document |
3590 +======================================================================*/
3591
3592 l_detail_value_id INTEGER;
3593 l_detail_value VARCHAR2(240);
3594
3595 l_loop_count PLS_INTEGER := 0;
3596 l_current_pos PLS_INTEGER := 0;
3597 l_cache_elm_count PLS_INTEGER;
3598
3599 l_log_module VARCHAR2 (2000);
3600 l_det_1 VARCHAR2(240);
3601 l_det_2 VARCHAR2(240);
3602 l_det_3 VARCHAR2(240);
3603 l_det_4 VARCHAR2(240);
3604 l_det_5 VARCHAR2(240);
3605
3606 BEGIN
3607
3608 IF g_log_enabled THEN
3609 l_log_module := C_DEFAULT_MODULE||'.get_detail_value_id';
3610 END IF;
3611
3612 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3613 trace
3614 (p_module => l_log_module
3615 ,p_msg => 'BEGIN ' || l_log_module
3616 ,p_level => C_LEVEL_PROCEDURE);
3617 END IF;
3618
3619 IF p_anacri_code IS NULL
3620 OR p_anacri_type_code IS NULL
3621 OR p_amb_context_code IS NULL
3622 THEN
3623 IF (C_LEVEL_EXCEPTION >= g_log_level)
3624 THEN
3625 trace
3626 ( p_module => l_log_module
3627 ,p_msg => 'EXCEPTION:'
3628 ||'When deleting p_anacri_code, p_anacri_type_code, ' ||
3629 'p_amb_context_code cannot be NULL.'
3630 ,p_level => C_LEVEL_EXCEPTION
3631 );
3632 END IF;
3633 xla_exceptions_pkg.raise_message
3634 (p_location => 'xla_analytical_criteria_pkg..get_detail_value_id');
3635 END IF;
3636
3637 IF (C_LEVEL_STATEMENT >= g_log_level)
3638 THEN
3639 trace
3640 ( p_module => l_log_module
3641 ,p_msg => 'Input parameters:'
3642 ,p_level => C_LEVEL_STATEMENT );
3643 trace
3644 ( p_module => l_log_module
3645 ,p_msg => 'p_detail_char_1 :' || p_detail_char_1
3646 ,p_level => C_LEVEL_STATEMENT );
3647 trace
3648 ( p_module => l_log_module
3649 ,p_msg => 'p_detail_date_1 : ' || p_detail_date_1
3650 ,p_level => C_LEVEL_STATEMENT );
3651 trace
3652 ( p_module => l_log_module
3653 ,p_msg => 'p_detail_number_1: ' || p_detail_number_1
3654 ,p_level => C_LEVEL_STATEMENT );
3655 trace
3656 ( p_module => l_log_module
3657 ,p_msg => 'p_detail_char_2 : ' || p_detail_char_2
3658 ,p_level => C_LEVEL_STATEMENT );
3659 trace
3660 ( p_module => l_log_module
3661 ,p_msg => 'p_detail_date_2 : ' || p_detail_date_2
3662 ,p_level => C_LEVEL_STATEMENT );
3663 trace
3664 ( p_module => l_log_module
3665 ,p_msg => 'p_detail_number_2: ' || p_detail_number_2
3666 ,p_level => C_LEVEL_STATEMENT );
3667 trace
3668 ( p_module => l_log_module
3669 ,p_msg => 'p_detail_char_3 : ' || p_detail_char_3
3670 ,p_level => C_LEVEL_STATEMENT );
3671 trace
3672 ( p_module => l_log_module
3673 ,p_msg => 'p_detail_date_3 : ' || p_detail_date_3
3674 ,p_level => C_LEVEL_STATEMENT );
3675 trace
3676 ( p_module => l_log_module
3677 ,p_msg => 'p_detail_number_3: ' || p_detail_number_3
3678 ,p_level => C_LEVEL_STATEMENT );
3679 trace
3680 ( p_module => l_log_module
3681 ,p_msg => 'p_detail_char_4 : ' || p_detail_char_4
3682 ,p_level => C_LEVEL_STATEMENT );
3683 trace
3684 ( p_module => l_log_module
3685 ,p_msg => 'p_detail_date_4 : ' || p_detail_date_4
3686 ,p_level => C_LEVEL_STATEMENT );
3687 trace
3688 ( p_module => l_log_module
3689 ,p_msg => 'p_detail_number_4: ' || p_detail_number_4
3690 ,p_level => C_LEVEL_STATEMENT );
3691 trace
3692 ( p_module => l_log_module
3693 ,p_msg => 'p_detail_char_5 : ' || p_detail_char_5
3694 ,p_level => C_LEVEL_STATEMENT );
3695 trace
3696 ( p_module => l_log_module
3697 ,p_msg => 'p_detail_date_5 : ' || p_detail_date_5
3698 ,p_level => C_LEVEL_STATEMENT );
3699 trace
3700 ( p_module => l_log_module
3701 ,p_msg => 'p_detail_number_5: ' || p_detail_number_5
3702 ,p_level => C_LEVEL_STATEMENT );
3703 END IF;
3704
3705 IF ( p_detail_char_1 IS NOT NULL AND ( p_detail_date_1 IS NOT NULL
3706 OR p_detail_number_1 IS NOT NULL)
3707 )
3708 OR ( p_detail_char_2 IS NOT NULL AND ( p_detail_date_2 IS NOT NULL
3712 OR p_detail_number_3 IS NOT NULL)
3709 OR p_detail_number_2 IS NOT NULL)
3710 )
3711 OR ( p_detail_char_3 IS NOT NULL AND ( p_detail_date_3 IS NOT NULL
3713 )
3714 OR ( p_detail_char_4 IS NOT NULL AND ( p_detail_date_4 IS NOT NULL
3715 OR p_detail_number_4 IS NOT NULL)
3716 )
3717 OR ( p_detail_char_5 IS NOT NULL AND ( p_detail_date_5 IS NOT NULL
3718 OR p_detail_number_5 IS NOT NULL)
3719 )
3720 OR (p_detail_date_1 IS NOT NULL AND p_detail_number_1 IS NOT NULL)
3721 OR (p_detail_date_2 IS NOT NULL AND p_detail_number_2 IS NOT NULL)
3722 OR (p_detail_date_3 IS NOT NULL AND p_detail_number_3 IS NOT NULL)
3723 OR (p_detail_date_4 IS NOT NULL AND p_detail_number_4 IS NOT NULL)
3724 OR (p_detail_date_5 IS NOT NULL AND p_detail_number_5 IS NOT NULL)
3725 THEN
3726 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
3727 trace
3728 ( p_module => l_log_module
3729 ,p_msg => 'EXCEPTION:'
3730 ||'At most one detail of each triple can have a value'
3731 ,p_level => C_LEVEL_EXCEPTION
3732 );
3733 END IF;
3734 xla_exceptions_pkg.raise_message
3735 (p_location => 'xla_analytical_criteria_pkg..get_detail_value_id');
3736 END IF;
3737
3738 --Retrieve the converted segment values
3739 l_det_1 := format_detail_value (
3740 p_detail_char => p_detail_char_1
3741 ,p_detail_date => p_detail_date_1
3742 ,p_detail_number => p_detail_number_1
3743 );
3744 l_det_2 := format_detail_value (
3745 p_detail_char => p_detail_char_2
3746 ,p_detail_date => p_detail_date_2
3747 ,p_detail_number => p_detail_number_2
3748 );
3749 l_det_3 := format_detail_value ( p_detail_char => p_detail_char_3
3750 ,p_detail_date => p_detail_date_3
3751 ,p_detail_number => p_detail_number_3
3752 );
3753 l_det_4 := format_detail_value ( p_detail_char => p_detail_char_4
3754 ,p_detail_date => p_detail_date_4
3755 ,p_detail_number => p_detail_number_4
3756 );
3757 l_det_5 := format_detail_value ( p_detail_char => p_detail_char_5
3758 ,p_detail_date => p_detail_date_5
3759 ,p_detail_number => p_detail_number_5
3760 );
3761 IF l_det_1 IS NULL
3762 AND l_det_2 IS NULL
3763 AND l_det_3 IS NULL
3764 AND l_det_4 IS NULL
3765 AND l_det_5 IS NULL
3766 THEN
3767 l_detail_value := null;
3768 ELSE
3769 l_detail_value := p_anacri_code
3770 || C_AC_DELIMITER
3771 || p_anacri_type_code
3772 || C_AC_DELIMITER
3773 || p_amb_context_code
3774 || C_AC_DELIMITER
3775 || l_det_1
3776 || C_AC_DELIMITER
3777 || l_det_2
3778 || C_AC_DELIMITER
3779 || l_det_3
3780 || C_AC_DELIMITER
3781 || l_det_4
3782 || C_AC_DELIMITER
3783 || l_det_5;
3784 END IF;
3785
3786 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3787 trace
3788 ( p_module => l_log_module
3789 ,p_msg => 'Returned ID: ' || l_detail_value_id
3790 ,p_level => C_LEVEL_STATEMENT
3791 );
3792 END IF;
3793
3794 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3795 trace
3796 (p_module => l_log_module
3797 ,p_msg => 'END ' || l_log_module
3798 ,p_level => C_LEVEL_PROCEDURE);
3799 END IF;
3800
3801 RETURN l_detail_value;
3802
3803 EXCEPTION
3804 WHEN xla_exceptions_pkg.application_exception THEN
3805 RAISE;
3806 WHEN OTHERS THEN
3807 xla_exceptions_pkg.raise_message
3808 (p_location => 'xla_analytical_criteria_pkg.get_detail_value_id');
3809
3810
3811 END concat_detail_values;
3812
3813 FUNCTION get_first_free_view_col_number (
3814 p_data_type_code IN VARCHAR2
3815 ,p_balance_flag IN VARCHAR2)
3816
3817 RETURN INTEGER
3818 IS
3819
3820 /*======================================================================+
3821 | |
3822 | Public Function |
3823 | |
3824 | Description |
3825 | ----------- |
3826 | Returns a first free view column nuumber. |
3827 | Due to R12+ re-architecture, Details with balances are not displayed |
3828 | in Lines Inquiry (use 1000+ as view column numbers). |
3829 | e.g. |
3830 | Detail Code Balance Flag Data Type View Column Number |
3831 | ----------- ------------ --------- ------------------ | |
3832 | AC_DTL_1 N C 1 |
3833 | AC_DTL_2 N C 2 |
3834 | AC_DTL_3 N N 1 |
3838 | --------------- -------------------- ----------------------------- |
3835 | AC_DTL_1B Y C 1001 |
3836 | |
3837 | Input Data Type Balance Flag Output First Free View Column Number |
3839 | C N 3 |
3840 | C Y 1002 |
3841 | N N 2 |
3842 | N Y 1001 |
3843 | D N 1 |
3844 | D Y 1 |
3845 +======================================================================*/
3846
3847 --locks all the record needed to assign a new view_column_num
3848 --for the specified data type
3849 --note that the case in which slot = 1 is free and subsequent ones are not
3850 --must be treated separately
3851 CURSOR lc_next_free_view_col_number
3852 ( cp_data_type_code VARCHAR2
3853 )
3854 IS
3855 SELECT xdtb.view_column_num
3856 FROM xla_analytical_dtls_b xdtb
3857 ,xla_analytical_hdrs_b xhtb
3858 WHERE xdtb.analytical_criterion_code = xhtb.analytical_criterion_code
3859 AND xdtb.analytical_criterion_type_code = xhtb.analytical_criterion_type_code
3860 AND xhtb.balancing_flag = p_balance_flag
3861 AND xdtb.data_type_code = cp_data_type_code
3862 AND NVL(xdtb.view_column_num, -1) =
3863 (SELECT NVL( MIN(xdtb2.view_column_num), -1)
3864 FROM xla_analytical_dtls_b xdtb2
3865 ,xla_analytical_dtls_b xdtb3
3866 ,xla_analytical_hdrs_b xhtb2
3867 WHERE xdtb2.data_type_code = xdtb.data_type_code
3868 AND xdtb2.analytical_criterion_code = xhtb2.analytical_criterion_code
3869 AND xdtb2.analytical_criterion_type_code = xhtb2.analytical_criterion_type_code
3870 AND xhtb2.balancing_flag = p_balance_flag
3871 AND xdtb3.data_type_code (+)= xdtb2.data_type_code
3872 AND xdtb3.view_column_num (+)= xdtb2.view_column_num + 1
3873 AND xdtb3.rowid IS NULL
3874 )
3875 FOR UPDATE NOWAIT;
3876
3877 l_next_free_view_col_number INTEGER;
3878 l_max_mappable_details INTEGER;
3879 l_return_value INTEGER;
3880 l_count_in_slot_1 INTEGER;
3881
3882 l_log_module VARCHAR2 (2000);
3883
3884 BEGIN
3885 IF g_log_enabled THEN
3886 l_log_module := C_DEFAULT_MODULE||'.get_first_free_view_col_number';
3887 END IF;
3888
3889 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3890 trace
3891 (p_module => l_log_module
3892 ,p_msg => 'BEGIN ' || l_log_module
3893 ,p_level => C_LEVEL_PROCEDURE);
3894 END IF;
3895
3896 --pick the limit of details assignable for the given datatype
3897 IF p_data_type_code = 'C'
3898 THEN
3899 l_max_mappable_details := C_MAX_MAPPABLE_VARCHAR_DETAILS;
3900 ELSIF p_data_type_code = 'D'
3901 THEN
3902 l_max_mappable_details := C_MAX_MAPPABLE_DATE_DETAILS;
3903 ELSIF p_data_type_code = 'N'
3904 THEN
3905 l_max_mappable_details := C_MAX_MAPPABLE_NUMBER_DETAILS;
3906 ELSE
3907 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
3908 trace
3909 ( p_module => l_log_module
3910 ,p_msg => 'EXCEPTION:'
3911 ||'Invalid value for parameter p_data_type_code:'
3912 || p_data_type_code
3913 ,p_level => C_LEVEL_EXCEPTION
3914 );
3915 END IF;
3916 xla_exceptions_pkg.raise_message
3917 (p_location => 'xla_analytical_criteria_pkg.get_first_free_view_col_number');
3918 END IF;
3919
3920 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3921 trace
3922 ( p_module => l_log_module
3923 ,p_msg => 'max assignable details for this datatype: '
3924 || l_max_mappable_details
3925 ,p_level => C_LEVEL_STATEMENT
3926 );
3927 END IF;
3928
3929 --Lock(NW) row in xla_analytical_dtls_with the lowest
3930 --view_column_num with no successor (for the given datatype only)
3931 --or lock all of them if all are NULL (for the given datatype only)
3932
3933 BEGIN
3934 OPEN lc_next_free_view_col_number (cp_data_type_code => p_data_type_code);
3935 FETCH lc_next_free_view_col_number
3936 INTO l_next_free_view_col_number;
3937 CLOSE lc_next_free_view_col_number;
3938
3939 EXCEPTION
3940 WHEN le_resource_busy
3941 THEN
3942 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
3943 trace
3944 ( p_module => l_log_module
3945 ,p_msg => 'EXCEPTION:'
3946 ||'Unable to lock the records'
3947 ,p_level => C_LEVEL_EXCEPTION);
3948 END IF;
3949 l_return_value := C_CANNOT_LOCK_DETAIL_ROW;
3950 END;
3951
3952 --see if slot 1 is free for this data type
3953 SELECT count(*)
3954 INTO l_count_in_slot_1
3955 FROM xla_analytical_dtls_b xdtb
3956 WHERE xdtb.data_type_code = p_data_type_code
3957 AND xdtb.view_column_num = 1;
3958
3959 --if it is free override the selection
3960 IF l_count_in_slot_1 = 0
3961 THEN
3962 l_next_free_view_col_number := NULL;
3963 END IF;
3964
3965 --if the record(s) could be locked (no err code in l_return_value) then
3966 --if the value found is equal to the limit for the datatype
3967 --or the NVL(limit, 0) <= 0 then return -1
3971 THEN
3968 --elsif the value is null then return 1
3969 --else return the max + 1
3970 IF l_return_value IS NULL
3972 IF (l_next_free_view_col_number = l_max_mappable_details
3973 OR NVL(l_max_mappable_details, 0) <= 0) and p_balance_flag = 'N'
3974 THEN
3975 l_return_value := C_NO_AVAILABLE_VIEW_COLUMN;
3976 ELSIF l_next_free_view_col_number IS NULL
3977 THEN
3978 -- R12+ Re-Architecture
3979 IF p_balance_flag = 'Y' THEN
3980 l_return_value := 1001;
3981 ELSE
3982 l_return_value := 1;
3983 END IF;
3984 ELSE
3985 l_return_value := l_next_free_view_col_number + 1;
3986 END IF;
3987 END IF;
3988
3989 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3990 trace
3991 ( p_module => l_log_module
3992 ,p_msg => 'Returned value: ' || l_return_value
3993 ,p_level => C_LEVEL_STATEMENT
3994 );
3995 END IF;
3996
3997 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3998 trace
3999 (p_module => l_log_module
4000 ,p_msg => 'END ' || l_log_module
4001 ,p_level => C_LEVEL_PROCEDURE);
4002 END IF;
4003
4004 RETURN l_return_value;
4005
4006 EXCEPTION
4007 WHEN xla_exceptions_pkg.application_exception THEN
4008 RAISE;
4009 WHEN OTHERS THEN
4010 xla_exceptions_pkg.raise_message
4011 (p_location => 'xla_analytical_criteria_pkg.get_first_free_view_col_number');
4012
4013 END get_first_free_view_col_number;
4014
4015
4016 FUNCTION get_view_column_number
4017 (p_anacri_code IN VARCHAR2
4018 ,p_anacri_type_code IN VARCHAR2
4019 ,p_anacri_detail_code IN VARCHAR2
4020 ,p_data_type_code IN VARCHAR2)
4021 RETURN INTEGER
4022 IS
4023 CURSOR c IS
4024 SELECT view_column_num
4025 FROM xla_analytical_dtls_b
4026 WHERE analytical_criterion_code = p_anacri_code
4027 AND analytical_criterion_type_code = p_anacri_type_code
4028 AND analytical_detail_code = p_anacri_detail_code
4029 AND data_type_code = p_data_type_code
4030 AND view_column_num IS NOT NULL; -- bug 4583524
4031
4032 l_return_value INTEGER;
4033 l_log_module VARCHAR2 (2000);
4034
4035 BEGIN
4036 IF g_log_enabled THEN
4037 l_log_module := C_DEFAULT_MODULE||'.get_view_column_number';
4038 END IF;
4039
4040 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4041 trace
4042 (p_module => l_log_module
4043 ,p_msg => 'BEGIN ' || l_log_module ||
4044 ' - '||p_anacri_code||','||p_anacri_type_code||
4045 ','||p_anacri_detail_code||','||p_data_type_code
4046 ,p_level => C_LEVEL_PROCEDURE);
4047 END IF;
4048
4049 OPEN c;
4050 FETCH c INTO l_return_value;
4051 CLOSE c;
4052
4053 IF (l_return_value IS NULL) THEN
4054 l_return_value := -1;
4055 END IF;
4056
4057 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4058 trace
4059 (p_module => l_log_module
4060 ,p_msg => 'END ' || l_log_module || ' : ' || l_return_value
4061 ,p_level => C_LEVEL_PROCEDURE);
4062 END IF;
4063
4064 RETURN l_return_value;
4065
4066 EXCEPTION
4067 WHEN xla_exceptions_pkg.application_exception THEN
4068 RAISE;
4069 WHEN OTHERS THEN
4070 xla_exceptions_pkg.raise_message
4071 (p_location => 'xla_analytical_criteria_pkg.get_view_column_number');
4072
4073 END get_view_column_number;
4074
4075
4076
4077 FUNCTION compile_criterion ( p_anacri_code IN VARCHAR2
4078 ,p_anacri_type_code IN VARCHAR2
4079 ,p_amb_context_code IN VARCHAR2
4080 )
4081
4082 RETURN INTEGER
4083 IS
4084
4085 /*======================================================================+
4086 | |
4087 | Public Function |
4088 | |
4089 | Description |
4090 | ----------- |
4091 | Refer to the Detail Level Design document |
4092 +======================================================================*/
4093
4094
4095 CURSOR lc_details
4096 ( cp_anacri_code VARCHAR2
4097 ,cp_anacri_type_code VARCHAR2
4098 ,cp_amb_context_code VARCHAR2
4099 )
4100 IS
4101 SELECT xdtb.analytical_detail_code
4102 ,xdtb.view_column_num
4103 ,xdtb.data_type_code
4104 FROM xla_analytical_dtls_b xdtb
4105 WHERE xdtb.analytical_criterion_code = cp_anacri_code
4106 AND xdtb.analytical_criterion_type_code = cp_anacri_type_code
4107 AND xdtb.amb_context_code = cp_amb_context_code
4108 ORDER BY xdtb.grouping_order
4109 FOR UPDATE NOWAIT;
4110
4111 l_enabled_flag VARCHAR2(1);
4112 l_display_in_inquiries_flag VARCHAR2(1);
4113 l_balance_flag VARCHAR2(1);
4114 l_view_column_number INTEGER;
4115 l_update_required BOOLEAN;
4116 l_count_exist_dvals INTEGER;
4117 l_return_value INTEGER;
4118
4119 l_log_module VARCHAR2 (2000);
4120
4121 BEGIN
4122 IF g_log_enabled THEN
4126 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4123 l_log_module := C_DEFAULT_MODULE||'.compile_criterion';
4124 END IF;
4125
4127 trace
4128 (p_module => l_log_module
4129 ,p_msg => 'BEGIN ' || l_log_module
4130 ,p_level => C_LEVEL_PROCEDURE);
4131 END IF;
4132
4133 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
4134 trace
4135 ( p_module => l_log_module
4136 ,p_msg => 'p_anacri_code: ' || p_anacri_code
4137 ,p_level => C_LEVEL_STATEMENT
4138 );
4139 trace
4140 ( p_module => l_log_module
4141 ,p_msg => 'p_anacri_type_code: ' || p_anacri_type_code
4142 ,p_level => C_LEVEL_STATEMENT
4143 );
4144 trace
4145 ( p_module => l_log_module
4146 ,p_msg => 'p_amb_context_code: ' || p_amb_context_code
4147 ,p_level => C_LEVEL_STATEMENT
4148 );
4149 END IF;
4150
4151 SAVEPOINT START_SAVEPOINT;
4152
4153 --begin a new block that traps the resource busy exception
4154 BEGIN
4155 --retrieve enabled_flag and display_in_inquiries_flag
4156 --from the criterion header, locking it
4157 SELECT xhdb.enabled_flag
4158 ,xhdb.display_in_inquiries_flag
4159 ,xhdb.balancing_flag
4160 INTO l_enabled_flag
4161 ,l_display_in_inquiries_flag
4162 ,l_balance_flag
4163 FROM xla_analytical_hdrs_b xhdb
4164 WHERE xhdb.analytical_criterion_code = p_anacri_code
4165 AND xhdb.analytical_criterion_type_code = p_anacri_type_code
4166 AND xhdb.amb_context_code = p_amb_context_code
4167 FOR UPDATE NOWAIT;
4168
4169 --check if there is at least one detail value for the criterion
4170 SELECT count(*)
4171 INTO l_count_exist_dvals
4172 FROM xla_analytical_dtl_vals xadv
4173 WHERE xadv.analytical_criterion_code = p_anacri_code
4174 AND xadv.analytical_criterion_type_code = p_anacri_type_code
4175 AND xadv.amb_context_code = p_amb_context_code
4176 AND ROWNUM = 1;
4177
4178 --loop on the criterion details
4179 FOR current_detail IN lc_details
4180 ( cp_anacri_code => p_anacri_code
4181 ,cp_anacri_type_code => p_anacri_type_code
4182 ,cp_amb_context_code => p_amb_context_code
4183 )
4184 LOOP
4185 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
4186 trace
4187 ( p_module => l_log_module
4188 ,p_msg => 'analytical_detail_code: '||current_detail.analytical_detail_code
4189 ,p_level => C_LEVEL_STATEMENT
4190 );
4191 trace
4192 ( p_module => l_log_module
4193 ,p_msg => 'l_display_in_inquiries_flag: '||l_display_in_inquiries_flag
4194 ,p_level => C_LEVEL_STATEMENT
4195 );
4196 trace
4197 ( p_module => l_log_module
4198 ,p_msg => 'l_enabled_flag: '||l_enabled_flag
4199 ,p_level => C_LEVEL_STATEMENT
4200 );
4201 trace
4202 ( p_module => l_log_module
4203 ,p_msg => 'view_column_num: '||current_detail.view_column_num
4204 ,p_level => C_LEVEL_STATEMENT
4205 );
4206 trace
4207 ( p_module => l_log_module
4208 ,p_msg => 'data_type_code: '||current_detail.data_type_code
4209 ,p_level => C_LEVEL_STATEMENT
4210 );
4211 END IF;
4212
4213 IF l_display_in_inquiries_flag = 'N' THEN
4214
4215 IF current_detail.view_column_num IS NOT NULL THEN
4216 l_view_column_number := NULL;
4217 l_update_required := TRUE;
4218
4219 --Remove lookup value for the reporting tool
4220 --TBD
4221 END IF;
4222 ELSIF l_display_in_inquiries_flag = 'Y' THEN
4223 IF l_enabled_flag = 'Y' THEN
4224 IF current_detail.data_type_code IS NOT NULL AND
4225 current_detail.view_column_num IS NULL THEN
4226 l_view_column_number := get_view_column_number
4227 (p_anacri_code => p_anacri_code
4228 ,p_anacri_type_code => p_anacri_type_code
4229 ,p_anacri_detail_code => current_detail.analytical_detail_code
4230 ,p_data_type_code => current_detail.data_type_code);
4231
4232 --try to get the next free view column number for the datatype
4233 IF (l_view_column_number < 0) THEN
4234 l_view_column_number := get_first_free_view_col_number
4235 (p_data_type_code => current_detail.data_type_code
4236 ,p_balance_flag => l_balance_flag);
4237 END IF;
4238
4239 --handle error return values setting the final return value
4240 IF l_view_column_number = C_CANNOT_LOCK_DETAIL_ROW THEN
4241 --resource busy
4242 l_return_value := C_CANNOT_LOCK_DETAIL_ROW;
4243 EXIT;
4244 ELSIF l_view_column_number = C_NO_AVAILABLE_VIEW_COLUMN THEN
4245 --no free column available
4246 l_return_value := C_NO_AVAILABLE_VIEW_COLUMN;
4247 EXIT;
4248 ELSE
4249 --success
4250 l_update_required := TRUE;
4251 --Add lookup value for the reporting tool
4252 --TBD
4253
4254 END IF;
4258 END IF;
4255 --else current view_column_number IS NOT NULL
4256 ELSE
4257 l_update_required := FALSE;
4259 ELSIF l_enabled_flag = 'N' THEN
4260 --if current view_column_number IS NOT NULL
4261 --we remove the view column number only if no detail value exists
4262 IF current_detail.view_column_num IS NOT NULL AND
4263 l_count_exist_dvals = 0 THEN
4264 l_view_column_number := NULL;
4265 l_update_required := TRUE;
4266 --Remove lookup value for the reporting tool
4267 --TBD
4268 ELSE
4269 l_update_required := FALSE;
4270 END IF;
4271 ELSE
4272 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
4273 trace
4274 ( p_module => l_log_module
4275 ,p_msg => 'EXCEPTION:'
4276 ||'Unsupported value for enabled_flag:'
4277 || l_enabled_flag
4278 ,p_level => C_LEVEL_EXCEPTION
4279 );
4280 END IF;
4281 xla_exceptions_pkg.raise_message
4282 (p_location => 'xla_analytical_criteria_pkg.compile_criterion');
4283 END IF;
4284 ELSE
4285 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
4286 trace
4287 ( p_module => l_log_module
4288 ,p_msg => 'EXCEPTION:'
4289 ||'Unsupported value for'
4290 || ' display_in_inquiries_flag:'
4291 || l_display_in_inquiries_flag
4292 ,p_level => C_LEVEL_EXCEPTION
4293 );
4294 END IF;
4295 xla_exceptions_pkg.raise_message
4296 (p_location => 'xla_analytical_criteria_pkg.compile_criterion');
4297
4298 END IF;
4299
4300 IF l_update_required THEN
4301 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
4302 trace
4303 ( p_module => l_log_module
4304 ,p_msg => 'l_updated_required: '||current_detail.analytical_detail_code||
4305 ', l_view_column_number = '||l_view_column_number
4306 ,p_level => C_LEVEL_STATEMENT
4307 );
4308 END IF;
4309
4310 UPDATE xla_analytical_dtls_b xdtb
4311 SET xdtb.view_column_num = l_view_column_number
4312 WHERE xdtb.analytical_criterion_code = p_anacri_code
4313 AND xdtb.analytical_criterion_type_code = p_anacri_type_code
4314 AND xdtb.amb_context_code = p_amb_context_code
4315 AND xdtb.analytical_detail_code = current_detail.analytical_detail_code;
4316 END IF;
4317
4318 END LOOP;
4319
4320 EXCEPTION
4321 WHEN le_resource_busy
4322 THEN
4323 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
4324 trace
4325 ( p_module => l_log_module
4326 ,p_msg => 'EXCEPTION:'
4327 ||'Unable to lock the records'
4328 ,p_level => C_LEVEL_EXCEPTION);
4329 END IF;
4330 l_return_value := C_CANNOT_LOCK_DETAIL_ROW;
4331 END;
4332
4333 IF l_return_value <> C_SUCCESS
4334 THEN
4335 ROLLBACK TO START_SAVEPOINT;
4336 END IF;
4337
4338 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
4339 trace
4340 ( p_module => l_log_module
4341 ,p_msg => 'Returned value: ' || l_return_value
4342 ,p_level => C_LEVEL_STATEMENT
4343 );
4344 END IF;
4345
4346 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4347 trace
4348 (p_module => l_log_module
4349 ,p_msg => 'END ' || l_log_module
4350 ,p_level => C_LEVEL_PROCEDURE);
4351 END IF;
4352
4353 RETURN l_return_value;
4354
4355 EXCEPTION
4356 WHEN xla_exceptions_pkg.application_exception THEN
4357 RAISE;
4358 WHEN OTHERS THEN
4359 xla_exceptions_pkg.raise_message
4360 (p_location => 'xla_analytical_criteria_pkg.compile_criterion');
4361
4362 END compile_criterion;
4363
4364 FUNCTION build_criteria_view
4365 RETURN INTEGER
4366 IS
4367
4368 /*======================================================================+
4369 | |
4370 | Public Function |
4371 | |
4372 | Description |
4373 | ----------- |
4374 | Refer to the Detail Level Design document |
4375 +======================================================================*/
4376
4377 CURSOR lc_displayable_details
4378 ( cp_data_type_code VARCHAR2
4379 )
4380 IS
4381 SELECT xhdb.analytical_criterion_code
4382 ,xhdb.analytical_criterion_type_code
4383 --,xhdb.amb_context_code
4384 ,xdtb.analytical_detail_code
4385 ,xdtb.grouping_order
4386 ,xdtb.view_column_num
4387 FROM xla_analytical_hdrs_b xhdb
4388 ,xla_analytical_dtls_b xdtb
4389 WHERE xhdb.display_in_inquiries_flag = 'Y'
4390 AND xdtb.analytical_criterion_code = xhdb.analytical_criterion_code
4391 AND xdtb.analytical_criterion_type_code = xhdb.analytical_criterion_type_code
4395 ,xhdb.analytical_criterion_type_code
4392 AND xdtb.amb_context_code = xhdb.amb_context_code
4393 AND xdtb.data_type_code = NVL(cp_data_type_code, xdtb.data_type_code)
4394 GROUP BY xhdb.analytical_criterion_code
4396 --,xhdb.amb_context_code
4397 ,xdtb.analytical_detail_code
4398 ,xdtb.grouping_order
4399 ,xdtb.view_column_num
4400 ORDER BY xdtb.view_column_num
4401 ;
4402 --FOR UPDATE NOWAIT;
4403
4404
4405 l_statement DBMS_SQL.VARCHAR2S;
4406 --l_hdr_statement DBMS_SQL.VARCHAR2S;
4407 l_rep_ln_statement DBMS_SQL.VARCHAR2S;
4408 l_rep_hdr_statement DBMS_SQL.VARCHAR2S;
4409 l_cursor_handle NUMBER;
4410 l_view_column_number INTEGER;
4411
4412 l_current_anacri_code VARCHAR2(30);
4413 l_current_anacri_type_code VARCHAR2(1);
4414 --l_current_amb_context_code VARCHAR2(30);
4415 l_current_anacri_detail_code VARCHAR2(30);
4416 l_current_grouping_order INTEGER;
4417 l_current_view_column_number INTEGER;
4418
4419 l_current_line VARCHAR2(256);
4420 l_current_detail_field_name VARCHAR2(256);
4421 l_current_detail_string_id VARCHAR2(256);
4422
4423 l_ln_fixed_part_header VARCHAR2(256) :=
4424 'CREATE OR REPLACE VIEW xla_analytical_criteria_v (
4425 ae_header_id
4426 ,ae_line_num
4427 ';
4428
4429 l_hdr_fixed_part_header VARCHAR2(256) :=
4430 'CREATE OR REPLACE VIEW xla_analytical_criteria_hdr_v (
4431 ae_header_id
4432 ';
4433
4434 l_rep_ln_fixed_part_header VARCHAR2(256) :=
4435 'CREATE OR REPLACE VIEW xla_ac_lines_v AS
4436 SELECT
4437 xald.ae_header_id AE_HEADER_ID
4438 ,xald.ae_line_num AE_LINE_NUM
4439 ';
4440 l_rep_hdr_fixed_part_header VARCHAR2(256) :=
4441 'CREATE OR REPLACE VIEW xla_ac_headers_v AS
4442 SELECT
4443 xahd.ae_header_id AE_HEADER_ID
4444 ';
4445
4446 l_selected_field_name_template VARCHAR2(256) :=
4447 'analytical_detail_<N>';
4448
4449
4450 l_select_row_template VARCHAR2(256) :=
4451 ',<SELECTED_FIELD_NAME>';
4452
4453
4454 l_ln_fixed_part_middle VARCHAR2(256) :=
4455 '
4456 )
4457 AS
4458 SELECT xald.ae_header_id
4459 ,xald.ae_line_num';
4460
4461 l_hdr_fixed_part_middle VARCHAR2(256) :=
4462 '
4463 )
4464 AS
4465 SELECT xahd.ae_header_id';
4466
4467 l_field_name_template VARCHAR2(256) :=
4468 'analytical_detail_<DATATYPE>_<N>';
4469
4470 l_field_template_fixed_part VARCHAR2(256) :=
4471 ' ,MAX( DECODE( xadv.analytical_criterion_type_code
4472 --|| RPAD(xadv.amb_context_code, 30)
4473 || xadv.analytical_criterion_code';
4474
4475 l_field_template_variable_part VARCHAR2(256) :=
4476 ' ,''<ANALYTICAL_CRITERION_STRING_ID>'',xadv.<ANALYTICAL_DETAIL_FIELD_NAME>))';
4477
4478 l_field_template_null VARCHAR2(256) :=
4479 ' ,MAX(DECODE( 1, 2, xadv.<ANALYTICAL_DETAIL_FIELD_NAME>))';
4480
4481 l_ln_fixed_part_footer_1 VARCHAR2(256) :=
4482 'FROM xla_ae_line_details xald
4483 ,xla_analytical_dtl_vals xadv';
4484
4485 l_hdr_fixed_part_footer_1 VARCHAR2(256) :=
4486 'FROM xla_ae_header_details xahd
4487 ,xla_analytical_dtl_vals xadv';
4488
4489
4490 l_hdr_fixed_part_footer_2 VARCHAR2(256) :=
4491 'WHERE xadv.analytical_detail_value_id = xahd.analytical_detail_value_id
4492 GROUP BY xahd.ae_header_id';
4493
4494 l_ln_fixed_part_footer_2 VARCHAR2(256) :=
4495 'WHERE xadv.analytical_detail_value_id = xald.analytical_detail_value_id
4496 GROUP BY xald.ae_header_id
4497 ,xald.ae_line_num';
4498
4499
4500 l_no_more_details BOOLEAN;
4501 l_return_value INTEGER;
4502
4503 l_log_module VARCHAR2 (2000);
4504
4505
4506 BEGIN
4507 /*Stubbed the below function as these xla_ae_header_details and xla_ae_line_details tables are not used in 12.2, commented the code and just returning success.
4508 Bug 12619876 :- Logged "Import Application Accounting Definitions' program with Import Option as Merge completes in error.
4509 While building the view xla_ac_lines_v we are getting the ORA error ie Duplicate Column Name.
4510 This error occurrs when two supporting refrences have same Analytical Detal Code.
4511 Resolution :- In order to resolved this issue we can define a variable at the begining of this procedure which defines list of all columns.
4512 And the same variable we can concatenate while buliding the columns(Header and Line Columns).
4513 Before Changes
4514 -----------------
4515 l_rep_ln_statement (l_rep_ln_statement.LAST + 1) := l_current_line ||
4516 ' L_'||l_current_anacri_type_code||'_'||l_current_anacri_detail_code;
4517 l_rep_hdr_statement (l_rep_hdr_statement.LAST + 1) := l_current_line||
4518 ' H_'||l_current_anacri_type_code||'_'||l_current_anacri_detail_code;
4519
4520 After Changes
4521 -----------------
4522 -- l_column_seq -- New Column added
4523 l_rep_ln_statement (l_rep_ln_statement.LAST + 1) := l_current_line ||
4524 ' L_'||l_column_seq||'_'||l_current_anacri_type_code||'_'||l_current_anacri_detail_code;
4525 l_rep_hdr_statement (l_rep_hdr_statement.LAST + 1) := l_current_line||
4526 ' H_'||l_column_seq||'_'||l_current_anacri_type_code||'_'||l_current_anacri_detail_code;
4527 */
4528
4529 RETURN C_SUCCESS;
4530
4531 /*
4532 IF g_log_enabled THEN
4533 l_log_module := C_DEFAULT_MODULE||'.build_criteria_view';
4537 trace
4534 END IF;
4535
4536 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4538 (p_module => l_log_module
4539 ,p_msg => 'BEGIN ' || l_log_module
4540 ,p_level => C_LEVEL_PROCEDURE);
4541 END IF;
4542
4543 --Initialize collection with the first fixed part of the SELECT stmt
4544 l_statement(1) := l_ln_fixed_part_header;
4545 --l_hdr_statement(1) := l_hdr_fixed_part_header;
4546 l_rep_ln_statement(1) := l_rep_ln_fixed_part_header;
4547 l_rep_hdr_statement(1) := l_rep_hdr_fixed_part_header;
4548
4549
4550 --Add the selected fields
4551 FOR i IN 1..C_MAX_MAPPABLE_DETAILS
4552 LOOP
4553 l_current_detail_field_name := REPLACE(l_selected_field_name_template, '<N>', i);
4554 l_current_line := REPLACE(l_select_row_template, '<SELECTED_FIELD_NAME>', l_current_detail_field_name);
4555 l_statement (l_statement.LAST + 1) := l_current_line;
4556 --l_hdr_statement (l_hdr_statement.LAST + 1) := l_current_line;
4557 END LOOP;
4558
4559 l_statement (l_statement.LAST + 1) := l_ln_fixed_part_middle ;
4560 --l_hdr_statement (l_hdr_statement.LAST + 1) := l_hdr_fixed_part_middle ;
4561
4562 --Read details belonging to headers that have
4563 --display in inquiries and reports = yes
4564
4565 DECLARE
4566 --function that generates the selected fields in the inner SELECT stmt
4567 FUNCTION loop_on_details ( p_data_type_code VARCHAR2
4568 ,p_data_type_suffix VARCHAR2
4569 ,p_max_details INTEGER
4570 ,p_column_offset INTEGER
4571 )
4572 RETURN INTEGER
4573 IS
4574 BEGIN
4575
4576
4577
4578
4579
4580 OPEN lc_displayable_details
4581 ( cp_data_type_code => p_data_type_code
4582 );
4583 FETCH lc_displayable_details
4584 INTO l_current_anacri_code
4585 ,l_current_anacri_type_code
4586 --,l_current_amb_context_code
4587 ,l_current_anacri_detail_code
4588 ,l_current_grouping_order
4589 ,l_current_view_column_number;
4590
4591 IF lc_displayable_details%NOTFOUND
4592 THEN
4593 l_no_more_details := TRUE;
4594 ELSE
4595 l_no_more_details := FALSE;
4596 END IF;
4597
4598 FOR i IN 1..p_max_details LOOP
4599 IF l_no_more_details THEN
4600 --generate string for unassigned field
4601 l_current_detail_field_name := REPLACE( l_field_name_template
4602 ,'<DATATYPE>'
4603 ,p_data_type_suffix
4604 );
4605 l_current_detail_field_name := REPLACE( l_current_detail_field_name
4606 ,'<N>'
4607 ,1
4608 );
4609
4610 l_current_line := REPLACE( l_field_template_null
4611 ,'<ANALYTICAL_DETAIL_FIELD_NAME>'
4612 ,l_current_detail_field_name
4613 );
4614
4615 l_statement (l_statement.LAST + 1) := l_current_line;
4616 --l_hdr_statement (l_hdr_statement.LAST + 1) := l_current_line;
4617 ELSE
4618 IF i = l_current_view_column_number THEN
4619 --generate string for assigned field
4620 --add the fixed part of the column extract statement
4621 l_statement (l_statement.LAST + 1) := '--'
4622 || (i + p_column_offset) || ': '
4623 || l_current_anacri_code
4624 || ', ' || l_current_anacri_detail_code ;
4625 l_statement (l_statement.LAST + 1) :=
4626 l_field_template_fixed_part;
4627
4628 l_rep_ln_statement(l_rep_ln_statement.LAST + 1) := '-- '
4629 || l_current_anacri_code
4630 || ', ' || l_current_anacri_detail_code ;
4631 l_rep_ln_statement(l_rep_ln_statement.LAST + 1) :=
4632 l_field_template_fixed_part;
4633
4634 l_rep_hdr_statement(l_rep_hdr_statement.LAST + 1) := '-- '
4635 || l_current_anacri_code
4636 || ', ' || l_current_anacri_detail_code ;
4637 l_rep_hdr_statement(l_rep_hdr_statement.LAST + 1) :=
4638 l_field_template_fixed_part;
4639
4640 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
4641 trace
4642 ( p_module => l_log_module
4643 ,p_msg => 'current detail: ' || l_current_anacri_detail_code
4644 ,p_level => C_LEVEL_STATEMENT
4645 );
4646 END IF;
4647
4648 l_current_detail_field_name :=REPLACE( l_field_name_template
4649 ,'<DATATYPE>'
4650 , p_data_type_suffix
4651 );
4652 l_current_detail_field_name :=REPLACE( l_current_detail_field_name
4653 , '<N>'
4654 , l_current_grouping_order
4655 );
4656
4660
4657 l_current_detail_string_id := l_current_anacri_type_code
4658 --|| RPAD(l_current_amb_context_code, 30)
4659 || l_current_anacri_code;
4661 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
4662 trace
4663 ( p_module => l_log_module
4664 ,p_msg => 'l_current_detail_string_id: '
4665 || l_current_detail_string_id
4666 ,p_level => C_LEVEL_STATEMENT
4667 );
4668 END IF;
4669
4670 l_current_line := REPLACE(
4671 l_field_template_variable_part
4672 ,'<ANALYTICAL_CRITERION_STRING_ID>'
4673 ,l_current_detail_string_id
4674 );
4675
4676 l_current_line := REPLACE(
4677 l_current_line
4678 ,'<ANALYTICAL_DETAIL_FIELD_NAME>'
4679 ,l_current_detail_field_name
4680 );
4681
4682
4683 l_statement (l_statement.LAST + 1) := l_current_line;
4684 --l_hdr_statement (l_hdr_statement.LAST + 1) := l_current_line;
4685
4686 l_rep_ln_statement (l_rep_ln_statement.LAST + 1) := l_current_line ||
4687 ' L_'||l_current_anacri_type_code||'_'||l_current_anacri_detail_code;
4688 l_rep_hdr_statement (l_rep_hdr_statement.LAST + 1) := l_current_line||
4689 ' H_'||l_current_anacri_type_code||'_'||l_current_anacri_detail_code;
4690
4691
4692 --fetch following detail
4693 FETCH lc_displayable_details
4694 INTO l_current_anacri_code
4695 ,l_current_anacri_type_code
4696 --,l_current_amb_context_code
4697 ,l_current_anacri_detail_code
4698 ,l_current_grouping_order
4699 ,l_current_view_column_number;
4700
4701 IF lc_displayable_details%NOTFOUND THEN
4702 l_no_more_details := TRUE;
4703 ELSE
4704 l_no_more_details := FALSE;
4705 END IF;
4706 ELSE --i=
4707 --generate string for unassigned field
4708 l_current_detail_field_name := REPLACE( l_field_name_template
4709 ,'<DATATYPE>'
4710 ,p_data_type_suffix
4711 );
4712 l_current_detail_field_name := REPLACE( l_current_detail_field_name
4713 ,'<N>'
4714 ,1
4715 );
4716
4717 l_current_line := REPLACE( l_field_template_null
4718 ,'<ANALYTICAL_DETAIL_FIELD_NAME>'
4719 ,l_current_detail_field_name
4720 );
4721
4722 l_statement (l_statement.LAST + 1) := l_current_line;
4723 --l_hdr_statement (l_hdr_statement.LAST + 1) := l_current_line;
4724
4725 --if the view column number is null log a message
4726 IF l_current_view_column_number IS NULL
4727 THEN
4728 IF (C_LEVEL_ERROR >= g_log_level) THEN
4729 trace
4730 ( p_module => l_log_module
4731 ,p_msg =>
4732 'WARNING: view column number is null for Analytical Criterion code '
4733 || l_current_anacri_code || ', detail code ' || l_current_anacri_detail_code
4734 ,p_level => C_LEVEL_ERROR);
4735 END IF;
4736
4737 --fetch following detail
4738 FETCH lc_displayable_details
4739 INTO l_current_anacri_code
4740 ,l_current_anacri_type_code
4741 --,l_current_amb_context_code
4742 ,l_current_anacri_detail_code
4743 ,l_current_grouping_order
4744 ,l_current_view_column_number;
4745
4746 IF lc_displayable_details%NOTFOUND
4747 THEN
4748 l_no_more_details := TRUE;
4749 ELSE
4750 l_no_more_details := FALSE;
4751 END IF;
4752
4753 END IF;
4754 END IF;
4755 END IF;
4756 END LOOP;
4757
4758 CLOSE lc_displayable_details;
4759
4760 RETURN C_SUCCESS;
4761
4762 END loop_on_details;
4763
4764 --begin a new block that traps the resource busy exception
4765 BEGIN
4766
4767 --try to get a lock on all the displayable details
4768 --that must be included in the view
4769 OPEN lc_displayable_details
4770 ( cp_data_type_code => NULL
4771 );
4772 CLOSE lc_displayable_details;
4773
4774 --loop on the details with data type 'C'
4775 l_return_value := loop_on_details( p_data_type_code => 'C'
4776 ,p_data_type_suffix => 'char'
4777 ,p_max_details => C_MAX_MAPPABLE_VARCHAR_DETAILS
4781 --loop on the details with data type 'D'
4778 ,p_column_offset => C_MAPPABLE_VARCHAR_OFFSET
4779 );
4780
4782 l_return_value := loop_on_details( p_data_type_code => 'D'
4783 ,p_data_type_suffix => 'date'
4784 ,p_max_details => C_MAX_MAPPABLE_DATE_DETAILS
4785 ,p_column_offset => C_MAPPABLE_DATE_OFFSET
4786 );
4787
4788
4789 --loop on the details with data type 'N'
4790 l_return_value := loop_on_details( p_data_type_code => 'N'
4791 ,p_data_type_suffix => 'number'
4792 ,p_max_details => C_MAX_MAPPABLE_NUMBER_DETAILS
4793 ,p_column_offset => C_MAPPABLE_NUMBER_OFFSET
4794 );
4795
4796
4797 EXCEPTION
4798 WHEN le_resource_busy
4799 THEN
4800 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
4801 trace
4802 ( p_module => l_log_module
4803 ,p_msg => 'EXCEPTION:'
4804 ||'Unable to lock the records'
4805 ,p_level => C_LEVEL_EXCEPTION);
4806 END IF;
4807 l_return_value := C_CANNOT_LOCK_DETAIL_ROW;
4808 END;
4809
4810 IF l_return_value = C_SUCCESS
4811 THEN
4812
4813 l_statement (l_statement.LAST + 1) := l_ln_fixed_part_footer_1;
4814 l_statement (l_statement.LAST + 1) := l_ln_fixed_part_footer_2;
4815 -- l_hdr_statement (l_hdr_statement.LAST + 1) := l_hdr_fixed_part_footer_1;
4816 -- l_hdr_statement (l_hdr_statement.LAST + 1) := l_hdr_fixed_part_footer_2;
4817 l_rep_ln_statement (l_rep_ln_statement.LAST + 1) := l_ln_fixed_part_footer_1;
4818 l_rep_ln_statement (l_rep_ln_statement.LAST + 1) := l_ln_fixed_part_footer_2;
4819 l_rep_hdr_statement (l_rep_hdr_statement.LAST + 1) := l_hdr_fixed_part_footer_1;
4820 l_rep_hdr_statement (l_rep_hdr_statement.LAST + 1) := l_hdr_fixed_part_footer_2;
4821
4822 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
4823 FOR i IN 1..l_statement.LAST
4824 LOOP
4825 trace
4826 ( p_module => l_log_module
4827 ,p_msg => 'View creation SQL statement:'
4828 ,p_level => C_LEVEL_STATEMENT
4829 );
4830 trace
4831 ( p_module => l_log_module
4832 ,p_msg => l_statement(i)
4833 ,p_level => C_LEVEL_STATEMENT
4834 );
4835 END LOOP;
4836 /*
4837 FOR i IN 1..l_hdr_statement.LAST
4838 LOOP
4839 trace
4840 ( p_module => l_log_module
4841 ,p_msg => 'View creation SQL statement:'
4842 ,p_level => C_LEVEL_STATEMENT
4843 );
4844 trace
4845 ( p_module => l_log_module
4846 ,p_msg => l_hdr_statement(i)
4847 ,p_level => C_LEVEL_STATEMENT
4848 );
4849 END LOOP;
4850 */
4851 /*
4852 END IF;
4853
4854 l_cursor_handle:= dbms_sql.open_cursor;
4855 --parse the statement
4856 dbms_sql.parse (
4857 c => l_cursor_handle
4858 ,statement => l_statement
4859 ,lb => 1
4860 ,ub => l_statement.LAST
4861 ,lfflg => TRUE --line feed
4862 ,language_flag => DBMS_SQL.NATIVE);
4863 dbms_sql.close_cursor(l_cursor_handle);
4864
4865 l_cursor_handle:= dbms_sql.open_cursor;
4866 --parse the statement
4867 dbms_sql.parse (
4868 c => l_cursor_handle
4869 ,statement => l_rep_ln_statement
4870 ,lb => 1
4871 ,ub => l_rep_ln_statement.LAST
4872 ,lfflg => TRUE --line feed
4873 ,language_flag => DBMS_SQL.NATIVE);
4874 dbms_sql.close_cursor(l_cursor_handle);
4875
4876 l_cursor_handle:= dbms_sql.open_cursor;
4877 l_cursor_handle:= dbms_sql.open_cursor;
4878 --parse the statement
4879 dbms_sql.parse (
4880 c => l_cursor_handle
4881 ,statement => l_rep_hdr_statement
4882 ,lb => 1
4883 ,ub => l_rep_hdr_statement.LAST
4884 ,lfflg => TRUE --line feed
4885 ,language_flag => DBMS_SQL.NATIVE);
4886 dbms_sql.close_cursor(l_cursor_handle);
4887
4888 /*
4889 l_cursor_handle:= dbms_sql.open_cursor;
4890 --parse the statement
4891 dbms_sql.parse (
4892 c => l_cursor_handle
4893 ,statement => l_hdr_statement
4894 ,lb => 1
4895 ,ub => l_hdr_statement.LAST
4896 ,lfflg => TRUE --line feed
4897 ,language_flag => DBMS_SQL.NATIVE);
4898 dbms_sql.close_cursor(l_cursor_handle);
4899 */
4900 /* END IF;
4901
4902 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4903 trace
4904 (p_module => l_log_module
4905 ,p_msg => 'END ' || l_log_module
4906 ,p_level => C_LEVEL_PROCEDURE);
4907 END IF;
4908
4909 RETURN l_return_value;
4910
4911 EXCEPTION
4912 WHEN xla_exceptions_pkg.application_exception THEN
4913 RAISE;
4917 */
4914 WHEN OTHERS THEN
4915 xla_exceptions_pkg.raise_message
4916 (p_location => 'xla_analytical_criteria_pkg.build_criteria_view');
4918
4919 END build_criteria_view;
4920
4921 FUNCTION get_hdr_ac_count
4922 RETURN INTEGER IS
4923
4924 l_hdr_ac_count PLS_INTEGER;
4925 l_mpa_hdr_ac_count PLS_INTEGER;
4926 l_log_module VARCHAR2(255);
4927
4928 BEGIN
4929
4930 IF g_log_enabled THEN
4931 l_log_module := C_DEFAULT_MODULE||'.get_hdr_ac_count';
4932 END IF;
4933
4934 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4935 trace
4936 (p_module => l_log_module
4937 ,p_msg => 'BEGIN ' || l_log_module
4938 ,p_level => C_LEVEL_PROCEDURE);
4939 END IF;
4940
4941 IF g_hdr_ac_count IS NOT NULL THEN
4942
4943 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4944 trace
4945 (p_module => l_log_module
4946 ,p_msg => 'g_hdr_ac_count(cached): ' || g_hdr_ac_count
4947 ,p_level => C_LEVEL_PROCEDURE);
4948 trace
4949 (p_module => l_log_module
4950 ,p_msg => 'END ' || l_log_module
4951 ,p_level => C_LEVEL_PROCEDURE);
4952 END IF;
4953
4954 RETURN g_hdr_ac_count;
4955
4956 ELSE
4957
4958 --
4959 -- MAX(COUNT(1)) with GROUP BY could return null
4960 -- when there is no row in the table. Added NVL.
4961 --
4962 SELECT NVL(MAX(COUNT(1)),0)
4963 INTO l_hdr_ac_count
4964 FROM xla_aad_header_ac_assgns
4965 GROUP BY
4966 amb_context_code
4967 ,application_id
4968 ,product_rule_type_code
4969 ,product_rule_code
4970 ,event_class_code
4971 ,event_type_code;
4972
4973 -- MPA Header ACs
4974 SELECT NVL(MAX(COUNT(1)),0)
4975 INTO l_mpa_hdr_ac_count
4976 FROM xla_mpa_header_ac_assgns
4977 GROUP BY
4978 amb_context_code
4979 ,application_id
4980 ,event_class_code
4981 ,event_type_code
4982 ,line_definition_owner_code
4983 ,line_definition_code
4984 ,accounting_line_type_code
4985 ,accounting_line_code;
4986
4987 IF l_hdr_ac_count >= l_mpa_hdr_ac_count THEN
4988 g_hdr_ac_count := l_hdr_ac_count;
4989 ELSE
4990 g_hdr_ac_count := l_mpa_hdr_ac_count;
4991 END IF;
4992
4993 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4994 trace
4995 (p_module => l_log_module
4996 ,p_msg => 'g_hdr_ac_count(db): ' || g_hdr_ac_count
4997 ,p_level => C_LEVEL_PROCEDURE);
4998 trace
4999 (p_module => l_log_module
5000 ,p_msg => 'END ' || l_log_module
5001 ,p_level => C_LEVEL_PROCEDURE);
5002 END IF;
5003
5004 RETURN g_hdr_ac_count;
5005
5006 END IF;
5007
5008 EXCEPTION
5009 WHEN xla_exceptions_pkg.application_exception THEN
5010 RAISE;
5011 WHEN OTHERS THEN
5012 xla_exceptions_pkg.raise_message
5013 (p_location => 'xla_analytical_criteria_pkg.get_hdr_ac_count');
5014
5015 END get_hdr_ac_count;
5016
5017 FUNCTION get_line_ac_count
5018 RETURN INTEGER IS
5019
5020 l_line_ac_count PLS_INTEGER;
5021 l_mpa_line_ac_count PLS_INTEGER;
5022 l_log_module VARCHAR2(255);
5023
5024 BEGIN
5025
5026 IF g_log_enabled THEN
5027 l_log_module := C_DEFAULT_MODULE||'.get_line_ac_count';
5028 END IF;
5029
5030 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
5031 trace
5032 (p_module => l_log_module
5033 ,p_msg => 'BEGIN ' || l_log_module
5034 ,p_level => C_LEVEL_PROCEDURE);
5035 END IF;
5036
5037 IF g_line_ac_count IS NOT NULL THEN
5038
5039 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
5040 trace
5041 (p_module => l_log_module
5042 ,p_msg => 'g_line_ac_count(cached): ' || g_line_ac_count
5043 ,p_level => C_LEVEL_PROCEDURE);
5044 trace
5045 (p_module => l_log_module
5046 ,p_msg => 'END ' || l_log_module
5047 ,p_level => C_LEVEL_PROCEDURE);
5048 END IF;
5049
5050 RETURN g_line_ac_count;
5051
5052 ELSE
5053
5054 SELECT NVL(MAX(COUNT(1)),0)
5055 INTO l_line_ac_count
5056 FROM xla_line_defn_ac_assgns
5057 GROUP BY
5058 amb_context_code
5059 ,application_id
5060 ,event_class_code
5061 ,event_type_code
5062 ,line_definition_owner_code
5063 ,line_definition_code
5064 ,accounting_line_type_code
5065 ,accounting_line_code;
5066
5067 SELECT NVL(MAX(COUNT(1)),0)
5068 INTO l_mpa_line_ac_count
5069 FROM xla_mpa_jlt_ac_assgns
5070 GROUP BY
5071 amb_context_code
5072 ,application_id
5073 ,event_class_code
5074 ,event_type_code
5075 ,line_definition_owner_code
5076 ,line_definition_code
5077 ,accounting_line_type_code
5078 ,accounting_line_code;
5079
5080 IF l_line_ac_count >= l_mpa_line_ac_count THEN
5081 g_line_ac_count := l_line_ac_count;
5082 ELSE
5083 g_line_ac_count := l_mpa_line_ac_count;
5087
5084 END IF;
5085
5086 END IF;
5088 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
5089 trace
5090 (p_module => l_log_module
5091 ,p_msg => 'g_line_ac_count(db): ' || g_line_ac_count
5092 ,p_level => C_LEVEL_PROCEDURE);
5093 trace
5094 (p_module => l_log_module
5095 ,p_msg => 'END ' || l_log_module
5096 ,p_level => C_LEVEL_PROCEDURE);
5097 END IF;
5098
5099 RETURN g_line_ac_count;
5100
5101 EXCEPTION
5102 WHEN xla_exceptions_pkg.application_exception THEN
5103 RAISE;
5104 WHEN OTHERS THEN
5105 xla_exceptions_pkg.raise_message
5106 (p_location => 'xla_analytical_criteria_pkg.get_line_ac_count');
5107 END get_line_ac_count;
5108
5109 /*
5110 FUNCTION uncompile_product_rules ( p_anacri_code IN VARCHAR2
5111 ,p_anacri_type_code IN VARCHAR2
5112 ,p_amb_context_code IN VARCHAR2
5113 )
5114 RETURN INTEGER
5115 IS
5116 */
5117 /*======================================================================+
5118 | |
5119 | Public Function |
5120 | |
5121 | Description |
5122 | ----------- |
5123 | Refer to the Detail Level Design document |
5124 +======================================================================*/
5125 /*
5126 CURSOR lc_assigned_rules
5127 SELECT xpr.name
5128 FROM xla_product_rules_vl xpr
5129 WHERE ( xpr.application_id
5130 ,xpr.amb_context_code
5131 ,xpr.product_rule_type_code
5132 ,xpr.product_rule_code
5133 )
5134 IN ( SELECT DISTINCT
5135 xaa.application_id
5136 ,xaa.amb_context_code
5137 ,xaa.product_rule_type_code
5138 ,xaa.product_rule_code
5139 FROM xla_analytical_assgns xaa
5140 WHERE xaa.amb_context_code = p_amb_context_code
5141 AND xaa.analytical_criterion_code = p_anacri_code
5142 AND xaa.analytical_criterion_type_code = p_anacri_type_code
5143 )
5144 AND xpr.compile_status_code IN ('E','N','Y')
5145 -- AND xpr.locking_status_flag = 'N'
5146 FOR UPDATE of compile_status_code NOWAIT;
5147
5148 l_log_module VARCHAR2 (2000);
5149
5150 BEGIN
5151 IF g_log_enabled THEN
5152 l_log_module := C_DEFAULT_MODULE||'.uncompile_product_rules';
5153 END IF;
5154
5155 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
5156 trace
5157 (p_module => l_log_module
5158 ,p_msg => 'BEGIN ' || l_log_module
5159 ,p_level => C_LEVEL_PROCEDURE);
5160 END IF;
5161
5162 --Loop on all the accounting definitions the criterion is assigned to
5163 FOR i IN lc_assigned_rules
5164 LOOP
5165 If the
5166 IF i.locking_status_flag = 'Y'
5167 THEN
5168 --store the
5169 END IF;
5170
5171 END LOOP;
5172
5173 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
5174 trace
5175 (p_module => l_log_module
5176 ,p_msg => 'END ' || l_log_module
5177 ,p_level => C_LEVEL_PROCEDURE);
5178 END IF;
5179
5180 RETURN l_return_value;
5181
5182 EXCEPTION
5183 WHEN le_resource_busy
5184 THEN
5185 trace('get_first_free_view_col_number could not lock the records', 20);
5186 l_return_value := ;
5187 WHEN xla_exceptions_pkg.application_exception THEN
5188 RAISE;
5189 WHEN OTHERS THEN
5190 xla_exceptions_pkg.raise_message
5191 (p_location => 'xla_analytical_criteria_pkg.uncompile_product_rules');
5192
5193 END uncompile_product_rules;
5194 */
5195
5196 BEGIN
5197 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
5198 g_log_enabled := fnd_log.test
5199 (log_level => g_log_level
5200 ,module => C_DEFAULT_MODULE);
5201
5202 IF NOT g_log_enabled THEN
5203 g_log_level := C_LEVEL_LOG_DISABLED;
5204 END IF;
5205
5206
5207 END xla_analytical_criteria_pkg;