1 PACKAGE BODY xla_conditions_pkg AS
2 /* $Header: xlaamcon.pkb 120.21.12010000.2 2008/09/08 18:21:07 vdamerla ship $ */
3 /*======================================================================+
4 | Copyright (c) 1995-2002 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +=======================================================================+
8 | PACKAGE NAME |
9 | xla_conditions_pkg |
10 | |
11 | DESCRIPTION |
12 | XLA Conditions Package |
13 | |
14 | HISTORY |
15 | 01-May-01 Dimple Shah Created |
16 | |
17 +======================================================================*/
18
19 -- Constants
20
21 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
22 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
23 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
24 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
25 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
26 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
27 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
28 C_DEFAULT_MODULE CONSTANT VARCHAR2(240) := 'xla.plsql.xla_conditions_pkg';
29
30
31 -- Global variables for debugging
32 g_log_level PLS_INTEGER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
33 g_log_enabled BOOLEAN := fnd_log.test
34 (log_level => g_log_level
35 ,module => C_DEFAULT_MODULE);
36
37
38 PROCEDURE trace (p_msg IN VARCHAR2
39 ,p_level IN NUMBER
40 ,p_module IN VARCHAR2) IS
41 BEGIN
42
43 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
44 fnd_log.message(p_level, p_module);
45 ELSIF p_level >= g_log_level THEN
46 fnd_log.string(p_level, p_module, p_msg);
47 END IF;
48 EXCEPTION
49 WHEN xla_exceptions_pkg.application_exception THEN
50 RAISE;
51 WHEN OTHERS THEN
52 xla_exceptions_pkg.raise_message
53 (p_location => 'xla_acct_setup_pub_pkg.trace');
54 END trace;
55
56 /*======================================================================+
57 | |
58 | Public Procedure |
59 | |
60 | delete_condition |
61 | |
62 | Deletes all conditions attached to the parent |
63 | |
64 +======================================================================*/
65
66 PROCEDURE delete_condition
67 (p_context IN VARCHAR2
68 ,p_application_id IN NUMBER DEFAULT NULL
69 ,p_amb_context_code IN VARCHAR2 DEFAULT NULL
70 ,p_entity_code IN VARCHAR2 DEFAULT NULL
71 ,p_event_class_code IN VARCHAR2 DEFAULT NULL
72 ,p_accounting_line_type_code IN VARCHAR2 DEFAULT NULL
73 ,p_accounting_line_code IN VARCHAR2 DEFAULT NULL
74 ,p_segment_rule_detail_id IN NUMBER DEFAULT NULL
75 ,p_description_prio_id IN NUMBER DEFAULT NULL)
76
77 IS
78 l_log_module VARCHAR2(240);
79 BEGIN
80
81 IF g_log_enabled THEN
82 l_log_module := C_DEFAULT_MODULE||'.delete_condition';
83 END IF;
84
85 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
86 trace('delete_condition.Begin',C_LEVEL_PROCEDURE,l_log_module);
87 END IF;
88
89 trace('context = '||p_context , C_LEVEL_STATEMENT,l_log_module);
90 trace('application_id = '||p_application_id , C_LEVEL_STATEMENT,l_log_module);
91 trace('entity_code = '||p_entity_code , C_LEVEL_STATEMENT,l_log_module);
92 trace('event_class_code = '||p_event_class_code , C_LEVEL_STATEMENT,l_log_module);
93 trace('accounting_line_type_code = '||p_accounting_line_type_code , C_LEVEL_STATEMENT,l_log_module);
94 trace('accounting_line_code = '||p_accounting_line_code , C_LEVEL_STATEMENT,l_log_module);
95 trace('segment_rule_detail_id = '||p_segment_rule_detail_id , C_LEVEL_STATEMENT,l_log_module);
96 trace('description_prio_id = '||p_description_prio_id , C_LEVEL_STATEMENT,l_log_module);
97
98 IF p_context = 'A' THEN
99
100 DELETE
101 FROM xla_conditions
102 WHERE application_id = p_application_id
103 AND amb_context_code = p_amb_context_code
104 AND entity_code = p_entity_code
105 AND event_class_code = p_event_class_code
106 AND accounting_line_type_code = p_accounting_line_type_code
107 AND accounting_line_code = p_accounting_line_code;
108
109 ELSIF p_context = 'S' THEN
110
111 DELETE
112 FROM xla_conditions
113 WHERE segment_rule_detail_id = p_segment_rule_detail_id;
114
115 ELSIF p_context = 'D' THEN
116
117 DELETE
118 FROM xla_conditions
119 WHERE description_prio_id = p_description_prio_id;
120 END IF;
121
122 trace('delete_condition.End',C_LEVEL_PROCEDURE,l_log_module);
123
124 EXCEPTION
125 WHEN xla_exceptions_pkg.application_exception THEN
126 RAISE;
127 WHEN OTHERS THEN
128 xla_exceptions_pkg.raise_message
129 (p_location => 'xla_conditions_pkg.delete_condition');
130
131 END delete_condition;
132
133 /*======================================================================+
134 | |
135 | Public Function |
136 | |
137 | display_condition |
138 | |
139 | Returns condition for the parent |
140 | |
141 +======================================================================*/
142
143 FUNCTION display_condition
144 (p_application_id IN NUMBER DEFAULT NULL
145 ,p_amb_context_code IN VARCHAR2 DEFAULT NULL
146 ,p_entity_code IN VARCHAR2 DEFAULT NULL
147 ,p_event_class_code IN VARCHAR2 DEFAULT NULL
148 ,p_accounting_line_type_code IN VARCHAR2 DEFAULT NULL
149 ,p_accounting_line_code IN VARCHAR2 DEFAULT NULL
150 ,p_segment_rule_detail_id IN NUMBER DEFAULT NULL
151 ,p_description_prio_id IN NUMBER DEFAULT NULL
152 ,p_chart_of_accounts_id IN NUMBER DEFAULT NULL
153 ,p_context IN VARCHAR2)
154 RETURN VARCHAR2
155
156 IS
157
158 CURSOR c_conditions
159 IS
160 SELECT user_sequence, bracket_left_code, bracket_right_code, value_type_code,
161 source_application_id, source_type_code, source_code,
162 flexfield_segment_code, value_flexfield_segment_code,
163 value_source_application_id, value_source_type_code,
164 value_source_code, value_constant, line_operator_code,
165 logical_operator_code, independent_value_constant
166 FROM xla_conditions
167 WHERE segment_rule_detail_id = p_segment_rule_detail_id
168 AND p_context = 'S'
169 UNION
170 SELECT user_sequence, bracket_left_code, bracket_right_code, value_type_code,
171 source_application_id, source_type_code, source_code,
172 flexfield_segment_code, value_flexfield_segment_code,
173 value_source_application_id, value_source_type_code,
174 value_source_code, value_constant, line_operator_code,
175 logical_operator_code, independent_value_constant
176 FROM xla_conditions
177 WHERE application_id = p_application_id
178 AND amb_context_code = p_amb_context_code
179 AND entity_code = p_entity_code
180 AND event_class_code = p_event_class_code
181 AND accounting_line_type_code = p_accounting_line_type_code
182 AND accounting_line_code = p_accounting_line_code
183 AND p_context = 'A'
184 UNION
185 SELECT user_sequence, bracket_left_code, bracket_right_code, value_type_code,
186 source_application_id, source_type_code, source_code,
187 flexfield_segment_code, value_flexfield_segment_code,
188 value_source_application_id, value_source_type_code,
189 value_source_code, value_constant, line_operator_code,
190 logical_operator_code, independent_value_constant
191 FROM xla_conditions
192 WHERE description_prio_id = p_description_prio_id
193 AND p_context = 'D'
194 ORDER BY user_sequence;
195
196 CURSOR c_source_name
197 (p_application_id IN NUMBER
198 ,p_source_type_code IN VARCHAR2
199 ,p_source_code IN VARCHAR2)
200 IS
201 SELECT name, flex_value_set_id, datatype_code, view_application_id, lookup_type,
202 flexfield_application_id, id_flex_code, segment_code
203 FROM xla_sources_vl
204 WHERE application_id = p_application_id
205 AND source_type_code = p_source_type_code
206 AND source_code = p_source_code;
207
208 CURSOR c_value_source_name
209 (p_application_id IN NUMBER
210 ,p_source_type_code IN VARCHAR2
211 ,p_source_code IN VARCHAR2)
212 IS
213 SELECT name, flexfield_application_id, id_flex_code
214 FROM xla_sources_vl
215 WHERE application_id = p_application_id
216 AND source_type_code = p_source_type_code
217 AND source_code = p_source_code;
218
219 CURSOR c_meaning
220 (p_view_application_id IN NUMBER
221 ,p_lookup_type IN VARCHAR2
222 ,p_lookup_code IN VARCHAR2)
223 IS
224 SELECT meaning
225 FROM fnd_lookup_values_vl
226 WHERE view_application_id = p_view_application_id
227 AND lookup_type = p_lookup_type
228 AND lookup_code = p_lookup_code;
229
230 CURSOR c_appl
231 (p_application_id IN NUMBER)
232 IS
233 SELECT application_short_name
234 FROM fnd_application_vl
235 WHERE application_id = p_application_id;
236
237 --
238 -- Local variables
239 --
240 l_condition c_conditions%rowtype;
241
242 l_condition_dsp VARCHAR2(20000) := NULL;
243 l_source_name VARCHAR2(80);
244 l_source_datatype_code VARCHAR2(1);
245 l_value_dsp VARCHAR2(2000);
246 l_flex_value_set_id INTEGER;
247 l_value_flex_value_set_id INTEGER;
248 l_flexfield_segment_name VARCHAR2(80);
249 l_value_flexfield_segment_name VARCHAR2(80);
250 l_line_operator_dsp VARCHAR2(80);
251 l_logical_operator_dsp VARCHAR2(80);
252 l_dummy_date DATE;
253 l_view_application_id INTEGER;
254 l_lookup_type VARCHAR2(30);
255 l_source_flex_appl_id NUMBER(15);
256 l_source_id_flex_num NUMBER(15);
257 l_source_id_flex_code VARCHAR2(30);
258 l_source_segment_code VARCHAR2(30);
259 l_value_source_flex_appl_id NUMBER(15);
260 l_value_source_id_flex_num NUMBER(15);
261 l_value_source_id_flex_code VARCHAR2(30);
262 l_appl_short_name VARCHAR2(50);
263 l_independent_value_dsp VARCHAR2(2000);
264
265 l_return BOOLEAN;
266 l_log_module VARCHAR2(240);
267 BEGIN
268
269 IF g_log_enabled THEN
270 l_log_module := C_DEFAULT_MODULE||'.display_condition';
271 END IF;
272
273 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
274 trace('display_condition.Begin',C_LEVEL_PROCEDURE,l_log_module);
275 END IF;
276
277 trace('application_id = '||p_application_id , C_LEVEL_STATEMENT,l_log_module);
278 trace('entity_code = '||p_entity_code , C_LEVEL_STATEMENT,l_log_module);
279 trace('event_class_code = '||p_event_class_code , C_LEVEL_STATEMENT,l_log_module);
280 trace('accounting_line_type_code = '||p_accounting_line_type_code , C_LEVEL_STATEMENT,l_log_module);
281 trace('accounting_line_code = '||p_accounting_line_code , C_LEVEL_STATEMENT,l_log_module);
282 trace('segment_rule_detail_id = '||p_segment_rule_detail_id , C_LEVEL_STATEMENT,l_log_module);
283 trace('description_prio_id = '||p_description_prio_id , C_LEVEL_STATEMENT,l_log_module);
284 trace('chart_of_accounts_id = '||p_chart_of_accounts_id , C_LEVEL_STATEMENT,l_log_module);
285
286 OPEN c_conditions;
287 LOOP
288 FETCH c_conditions
289 INTO l_condition;
290 EXIT WHEN c_conditions%notfound;
291
292 BEGIN
293
294 l_source_name := null;
295 l_value_dsp := null;
296 l_flex_value_set_id := null;
297 l_value_flex_value_set_id := null;
298 l_flexfield_segment_name := null;
299 l_value_flexfield_segment_name := null;
300 l_line_operator_dsp := null;
301 l_logical_operator_dsp := null;
302
303 IF l_condition.bracket_left_code is not null THEN
304 l_condition_dsp := rtrim(l_condition_dsp)||' '||
305 l_condition.bracket_left_code;
306 END IF;
307 --
308 -- Get source name
309 --
310 IF l_condition.source_code is not null THEN
311 OPEN c_source_name
312 (l_condition.source_application_id
313 ,l_condition.source_type_code
314 ,l_condition.source_code);
315 FETCH c_source_name
316 INTO l_source_name, l_flex_value_set_id, l_source_datatype_code,
317 l_view_application_id, l_lookup_type,
318 l_source_flex_appl_id, l_source_id_flex_code, l_source_segment_code;
319 CLOSE c_source_name;
320
321 l_condition_dsp := rtrim(l_condition_dsp)||' '||
322 l_source_name;
323
324 END IF;
325
326 --
327 -- Get flexfield_segment_name
328 --
329 IF l_condition.flexfield_segment_code is not null THEN
330
331 IF l_source_flex_appl_id = 101 and l_source_id_flex_code = 'GL#' THEN
332 l_flexfield_segment_name := xla_flex_pkg.get_flexfield_segment_name
333 (p_application_id => 101
334 ,p_flex_code => 'GL#'
335 ,p_chart_of_accounts_id => p_chart_of_accounts_id
336 ,p_flexfield_segment_code => l_condition.flexfield_segment_code);
337
338 IF l_flexfield_segment_name is null THEN
339 l_flexfield_segment_name := xla_flex_pkg.get_qualifier_name
340 (p_application_id => 101
341 ,p_id_flex_code => 'GL#'
342 ,p_qualifier_segment => l_condition.flexfield_segment_code);
343
347
344
345 END IF;
346 ELSE
348 l_source_id_flex_num := xla_flex_pkg.get_flexfield_structure
349 (p_application_id => l_source_flex_appl_id
350 ,p_id_flex_code => l_source_id_flex_code);
351
352 l_flexfield_segment_name := xla_flex_pkg.get_flexfield_segment_name
353 (p_application_id => l_source_flex_appl_id
354 ,p_flex_code => l_source_id_flex_code
355 ,p_chart_of_accounts_id => l_source_id_flex_num
356 ,p_flexfield_segment_code => l_condition.flexfield_segment_code);
357 END IF;
358
359 l_condition_dsp := rtrim(l_condition_dsp)||','||
360 l_flexfield_segment_name;
361 END IF;
362
363 --
364 -- Get line_operator_dsp
365 --
366 IF l_condition.line_operator_code is not null THEN
367 -- bugfix 6024311: since Meaning in lookup table will be translated,
368 -- do not use get_meaning() for meanings that are 'operators'.
369
370 IF(l_condition.logical_operator_code = 'N') THEN
371 l_condition_dsp := rtrim(l_condition_dsp) ||' IS NULL ';
372 ELSIF(l_condition.logical_operator_code = 'X') THEN
373 l_condition_dsp := rtrim(l_condition_dsp) ||' IS NOT NULL ';
374 ELSE
375 l_line_operator_dsp := xla_lookups_pkg.get_meaning
376 (p_lookup_type => 'XLA_LINE_OPERATOR_TYPE'
377 ,p_lookup_code => l_condition.line_operator_code);
378
379 l_condition_dsp := rtrim(l_condition_dsp)||' '||
380 l_line_operator_dsp;
381 END IF;
382 END IF;
383
384 --
385 -- Get value_dsp
386 --
387 IF l_condition.value_type_code = 'S' THEN
388
389 OPEN c_value_source_name
390 (l_condition.value_source_application_id
391 ,l_condition.value_source_type_code
392 ,l_condition.value_source_code);
393 FETCH c_value_source_name
394 INTO l_value_dsp, l_value_source_flex_appl_id, l_value_source_id_flex_code;
395 CLOSE c_value_source_name;
396
397 l_condition_dsp := rtrim(l_condition_dsp)||' '||
398 l_value_dsp;
399
400 ELSIF l_condition.value_type_code = 'C' THEN
401
402 IF l_flex_value_set_id is not null THEN
403
404 l_value_dsp := xla_flex_pkg.get_flex_value_meaning
405 (p_flex_value_set_id => l_flex_value_set_id
406 ,p_flex_value => l_condition.value_constant);
407
408 ELSIF l_view_application_id is not null THEN
409
410 OPEN c_meaning
411 (p_view_application_id => l_view_application_id
412 ,p_lookup_type => l_lookup_type
413 ,p_lookup_code => l_condition.value_constant);
414 FETCH c_meaning
415 INTO l_value_dsp;
416 CLOSE c_meaning;
417
418 ELSE
419 IF l_source_flex_appl_id is not null THEN
420 IF l_source_segment_code is not null or l_condition.flexfield_segment_code is not null THEN
421 l_value_dsp := l_condition.value_constant;
422
423 ELSIF l_source_flex_appl_id = 101 and l_source_id_flex_code = 'GL#' THEN
424 l_value_dsp := fnd_flex_ext.get_segs(application_short_name => 'SQLGL'
425 ,key_flex_code => 'GL#'
426 ,structure_number => p_chart_of_accounts_id
427 ,combination_id => to_number(l_condition.value_constant));
428
429 ELSE
430
431 l_source_id_flex_num := xla_flex_pkg.get_flexfield_structure
432 (p_application_id => l_source_flex_appl_id
433 ,p_id_flex_code => l_source_id_flex_code);
434
435 OPEN c_appl(l_source_flex_appl_id);
436 FETCH c_appl
437 INTO l_appl_short_name;
438 CLOSE c_appl;
439
440 l_value_dsp := fnd_flex_ext.get_segs(application_short_name => l_appl_short_name
441 ,key_flex_code => l_source_id_flex_code
442 ,structure_number => l_source_id_flex_num
443 ,combination_id => to_number(l_condition.value_constant));
444 END IF;
445
446 ELSIF l_source_datatype_code = 'N' THEN
447 l_value_dsp := fnd_number.canonical_to_number(l_condition.value_constant);
448 ELSIF l_source_datatype_code = 'D' THEN
449 l_dummy_date := fnd_date.canonical_to_date(l_condition.value_constant);
453 END IF;
450 l_value_dsp := fnd_date.date_to_displaydate(l_dummy_date);
451 ELSE
452 l_value_dsp := l_condition.value_constant;
454 END IF;
455
456 --ksvenkat
457 IF l_source_datatype_code = 'N' then
458 l_condition_dsp := rtrim(l_condition_dsp)||
459 l_value_dsp;
460 ELSE
461 l_condition_dsp := rtrim(l_condition_dsp)||' '''||
462 l_value_dsp||'''';
463 END IF;
464 END IF;
465
466 --
467 -- Get value_flexfield_segment_name
468 --
469 IF l_condition.value_flexfield_segment_code is not null THEN
470
471 IF l_value_source_flex_appl_id = 101 and l_value_source_id_flex_code = 'GL#' THEN
472 l_value_flexfield_segment_name := xla_flex_pkg.get_flexfield_segment_name
473 (p_application_id => 101
474 ,p_flex_code => 'GL#'
475 ,p_chart_of_accounts_id => p_chart_of_accounts_id
476 ,p_flexfield_segment_code => l_condition.value_flexfield_segment_code);
477
478 IF l_value_flexfield_segment_name is null THEN
479 l_value_flexfield_segment_name := xla_flex_pkg.get_qualifier_name
480 (p_application_id => 101
481 ,p_id_flex_code => 'GL#'
482 ,p_qualifier_segment => l_condition.value_flexfield_segment_code);
483
484 END IF;
485 ELSE
486
487 l_value_source_id_flex_num := xla_flex_pkg.get_flexfield_structure
488 (p_application_id => l_value_source_flex_appl_id
489 ,p_id_flex_code => l_value_source_id_flex_code);
490
491 l_value_flexfield_segment_name := xla_flex_pkg.get_flexfield_segment_name
492 (p_application_id => l_value_source_flex_appl_id
493 ,p_flex_code => l_value_source_id_flex_code
494 ,p_chart_of_accounts_id => l_value_source_id_flex_num
495 ,p_flexfield_segment_code => l_condition.value_flexfield_segment_code);
496 END IF;
497
498 l_condition_dsp := rtrim(l_condition_dsp)||','||
499 l_value_flexfield_segment_name;
500 END IF;
501
502 IF l_condition.bracket_right_code is not null THEN
503 l_condition_dsp := rtrim(l_condition_dsp)||' '||
504 l_condition.bracket_right_code;
505 END IF;
506
507 --
508 -- Get logical_operator_dsp
509 --
510 IF l_condition.logical_operator_code is not null THEN
511 -- bugfix 6024311: since Meaning in lookup table will be translated,
512 -- do not use get_meaning() for lookup_type XLA_LOGICAL_OPERATOR_TYPE
513 /*
514 l_logical_operator_dsp := xla_lookups_pkg.get_meaning
515 (p_lookup_type => 'XLA_LOGICAL_OPERATOR_TYPE'
516 ,p_lookup_code => l_condition.logical_operator_code);
517 l_condition_dsp := rtrim(l_condition_dsp)||' '||
518 l_logical_operator_dsp;
519 */
520 IF(l_condition.logical_operator_code = 'A') THEN
521 l_condition_dsp := rtrim(l_condition_dsp) ||' AND ';
522 ELSIF(l_condition.logical_operator_code = 'O') THEN
523 l_condition_dsp := rtrim(l_condition_dsp) ||' OR ';
524 END IF;
525
526 END IF;
527
528 EXCEPTION
529 WHEN VALUE_ERROR THEN
530 xla_exceptions_pkg.raise_message
531 ('XLA'
532 ,'XLA_AB_COND_TOO_LONG'
533 ,'PROCEDURE'
534 ,'xla_conditions_pkg.display_condition'
535 ,'ERROR'
536 ,sqlerrm
537 );
538 END;
539
540 END LOOP;
541 CLOSE c_conditions;
542
543 trace('delete_condition.End',C_LEVEL_PROCEDURE,l_log_module);
544
545 RETURN l_condition_dsp;
546
547 EXCEPTION
548 WHEN xla_exceptions_pkg.application_exception THEN
549 IF c_conditions%ISOPEN THEN
550 CLOSE c_conditions;
551 END IF;
552 IF c_source_name%ISOPEN THEN
553 CLOSE c_source_name;
554 END IF;
555 IF c_value_source_name%ISOPEN THEN
556 CLOSE c_value_source_name;
557 END IF;
558 RAISE;
559 WHEN OTHERS THEN
560 IF c_conditions%ISOPEN THEN
561 CLOSE c_conditions;
562 END IF;
563 IF c_source_name%ISOPEN THEN
564 CLOSE c_source_name;
565 END IF;
566 IF c_value_source_name%ISOPEN THEN
567 CLOSE c_value_source_name;
568 END IF;
569 xla_exceptions_pkg.raise_message
570 (p_location => 'xla_conditions_pkg.display_condition');
571
572 END display_condition;
573
574 /*======================================================================+
575 | |
576 | Public Function |
577 | |
578 | desc_condition_is_invalid |
579 | |
580 | Returns true if condition is invalid |
581 | |
582 +======================================================================*/
583
584 FUNCTION desc_condition_is_invalid
585 (p_application_id IN NUMBER
586 ,p_amb_context_code IN VARCHAR2
587 ,p_description_type_code IN VARCHAR2
588 ,p_description_code IN VARCHAR2
589 ,p_message_name IN OUT NOCOPY VARCHAR2)
590 RETURN BOOLEAN
591
592 IS
593 --
594 -- Variable declarations
595 --
596 l_exist varchar2(1);
597 l_return boolean;
598 l_description_prio_id number(38);
599 l_desc_user_sequence number(38);
600 l_desc_max_left_seq number(38);
601 l_desc_max_right_seq number(38);
602 l_desc_min_left_seq number(38);
603 l_desc_min_right_seq number(38);
604 l_count_1 number(38);
605 l_count_2 number(38);
606 l_log_module VARCHAR2(240);
607
608 CURSOR c_desc_brackets
609 IS
610 SELECT 'x'
611 FROM xla_desc_priorities d
612 WHERE d.application_id = p_application_id
613 AND d.amb_context_code = p_amb_context_code
614 AND d.description_type_code = p_description_type_code
615 AND d.description_code = p_description_code
616 AND exists(SELECT count(1)
617 FROM xla_conditions c
618 WHERE c.description_prio_id = d.description_prio_id
619 AND c.bracket_left_code is not null
620 MINUS
621 SELECT count(1)
622 FROM xla_conditions c1
623 WHERE c1.description_prio_id = d.description_prio_id
624 AND c1.bracket_right_code is not null);
625
626
627 CURSOR c_description_prio_id
628 IS
629 SELECT description_prio_id
630 FROM xla_desc_priorities d
631 WHERE d.application_id = p_application_id
632 AND d.amb_context_code = p_amb_context_code
633 AND d.description_type_code = p_description_type_code
634 AND d.description_code = p_description_code
635 AND exists (SELECT 'y'
636 FROM xla_conditions c
637 WHERE c.description_prio_id = d.description_prio_id);
638
639 CURSOR c_desc_max_left_seq(p_description_prio_id NUMBER)
640 IS
641 SELECT max(user_sequence)
642 FROM xla_conditions c
643 WHERE c.description_prio_id = p_description_prio_id
644 AND c.bracket_left_code is not null;
645
646 CURSOR c_desc_max_right_seq(p_description_prio_id NUMBER)
647 IS
648 SELECT max(user_sequence)
649 FROM xla_conditions c
650 WHERE c.description_prio_id = p_description_prio_id
651 AND c.bracket_right_code is not null;
652
653 CURSOR c_desc_min_left_seq(p_description_prio_id NUMBER)
654 IS
655 SELECT min(user_sequence)
656 FROM xla_conditions c
657 WHERE c.description_prio_id = p_description_prio_id
658 AND c.bracket_left_code is not null;
659
660 CURSOR c_desc_min_right_seq(p_description_prio_id NUMBER)
661 IS
662 SELECT min(user_sequence)
663 FROM xla_conditions c
664 WHERE c.description_prio_id = p_description_prio_id
665 AND c.bracket_right_code is not null;
666
670 SELECT 'x'
667 -- Check if any empty rows exist with just the sequence number
668 CURSOR c_source(p_description_prio_id NUMBER)
669 IS
671 FROM xla_conditions c
672 WHERE c.description_prio_id = p_description_prio_id
673 AND c.bracket_left_code is null
674 AND c.bracket_right_code is null
675 AND c.source_code is null;
676
677 -- Check if any rows exist with just left and right bracket
678 CURSOR c_left_right_bracket(p_description_prio_id NUMBER)
679 IS
680 SELECT 'x'
681 FROM xla_conditions c
682 WHERE c.description_prio_id = p_description_prio_id
683 AND c.bracket_left_code is not null
684 AND c.bracket_right_code is not null
685 AND c.source_code is null;
686
687 -- Get the sequence for the last row
688 CURSOR c_desc_sequence(p_description_prio_id NUMBER)
689 IS
690 SELECT max(user_sequence)
691 FROM xla_conditions c
692 WHERE c.description_prio_id = p_description_prio_id;
693
694 -- Check if last row has logical operator
695 CURSOR c_desc_last_operator(p_description_prio_id NUMBER)
696 IS
697 SELECT 'x'
698 FROM xla_conditions c
699 WHERE c.description_prio_id = p_description_prio_id
700 AND c.user_sequence = l_desc_user_sequence
701 AND c.logical_operator_code is not null;
702
703 -- Check if any rows exist with just left bracket and logical operator
704 CURSOR c_left_bracket_operator(p_description_prio_id NUMBER)
705 IS
706 SELECT 'x'
707 FROM xla_conditions c
708 WHERE c.description_prio_id = p_description_prio_id
709 AND c.bracket_left_code is not null
710 AND c.source_code is null
711 AND c.logical_operator_code is not null;
712
713 -- Get all rows which are not the last row or rows with just left bracket
714 -- and have no logical operator
715 CURSOR c_no_logical_operator(p_description_prio_id NUMBER)
716 IS
717 SELECT user_sequence
718 FROM xla_conditions c
719 WHERE c.description_prio_id = p_description_prio_id
720 AND (c.source_code is not null
721 OR c.bracket_right_code is not null)
722 AND c.logical_operator_code is null
723 AND c.user_sequence <> l_desc_user_sequence;
724
725 l_no_logical_operator c_no_logical_operator%rowtype;
726
727 -- Check if next row has only right bracket
728 -- and have no logical operator
729 CURSOR c_only_right_bracket(p_description_prio_id NUMBER)
730 IS
731 SELECT 'x'
732 FROM xla_conditions c
733 WHERE c.description_prio_id = p_description_prio_id
734 AND c.source_code is null
735 AND c.bracket_right_code is not null
736 AND c.user_sequence = l_no_logical_operator.user_sequence + 1;
737
738 -- Get all rows which have just left bracket and no source
739 CURSOR c_no_source_bracket(p_description_prio_id NUMBER)
740 IS
741 SELECT user_sequence
742 FROM xla_conditions c
743 WHERE c.description_prio_id = p_description_prio_id
744 AND c.source_code is null
745 AND c.bracket_left_code is not null;
746
747 l_no_source_bracket c_no_source_bracket%rowtype;
748
749 -- Check if next row has only left bracket
750 CURSOR c_only_left_bracket(p_description_prio_id NUMBER)
751 IS
752 SELECT 'x'
753 FROM xla_conditions c
754 WHERE c.description_prio_id = p_description_prio_id
755 AND c.bracket_left_code is not null
756 AND c.user_sequence = l_no_source_bracket.user_sequence + 1;
757
758 -- Get all rows with logical operator not null
759 CURSOR c_log_op_not_null(p_description_prio_id NUMBER)
760 IS
761 SELECT user_sequence
762 FROM xla_conditions c
763 WHERE c.description_prio_id = p_description_prio_id
764 AND c.logical_operator_code is not null;
765
766 l_log_op_not_null c_log_op_not_null%rowtype;
767
768 -- Check if next row has only right bracket
769 CURSOR c_right_bracket(p_description_prio_id NUMBER)
770 IS
771 SELECT 'x'
772 FROM xla_conditions c
773 WHERE c.description_prio_id = p_description_prio_id
774 AND c.source_code is null
775 AND c.bracket_right_code is not null
776 AND c.user_sequence = l_log_op_not_null.user_sequence + 1;
777
778 BEGIN
782 trace('description_type_code = '||p_description_type_code , C_LEVEL_STATEMENT,l_log_module);
779 trace('desc_condition_is_invalid.Begin',C_LEVEL_PROCEDURE,l_log_module);
780
781 trace('application_id = '||p_application_id , C_LEVEL_STATEMENT,l_log_module);
783 trace('description_code = '||p_description_code , C_LEVEL_STATEMENT,l_log_module);
784
785 --
786 -- Check if condition has unequal brackets
787 --
788 OPEN c_desc_brackets;
789 FETCH c_desc_brackets
790 INTO l_exist;
791 IF c_desc_brackets%found then
792 p_message_name := 'XLA_AB_CON_UNEQUAL_BRCKT';
793 l_return := TRUE;
794 ELSE
795 p_message_name := NULL;
796 l_return := FALSE;
797 END IF;
798 CLOSE c_desc_brackets;
799
800 IF l_return = FALSE THEN
801
802 OPEN c_description_prio_id;
803 LOOP
804 FETCH c_description_prio_id
805 INTO l_description_prio_id;
806 EXIT WHEN c_description_prio_id%notfound or l_return = TRUE;
807
808 --
809 -- Check if right bracket sequence is less than left bracket sequence
810 --
811 OPEN c_desc_max_left_seq(l_description_prio_id);
812 FETCH c_desc_max_left_seq
813 INTO l_desc_max_left_seq;
814 CLOSE c_desc_max_left_seq;
815
816 OPEN c_desc_max_right_seq(l_description_prio_id);
817 FETCH c_desc_max_right_seq
818 INTO l_desc_max_right_seq;
819 CLOSE c_desc_max_right_seq;
820
821 IF l_desc_max_right_seq < l_desc_max_left_seq then
822 p_message_name := 'XLA_AB_CON_UNEQUAL_BRCKT';
823 l_return := TRUE;
824 ELSE
825 p_message_name := NULL;
826 l_return := FALSE;
827 END IF;
828
829 IF l_return = FALSE THEN
830 OPEN c_desc_min_left_seq(l_description_prio_id);
831 FETCH c_desc_min_left_seq
832 INTO l_desc_min_left_seq;
833 CLOSE c_desc_min_left_seq;
834
835 OPEN c_desc_min_right_seq(l_description_prio_id);
836 FETCH c_desc_min_right_seq
837 INTO l_desc_min_right_seq;
838 CLOSE c_desc_min_right_seq;
839
840 IF l_desc_min_right_seq < l_desc_min_left_seq then
841 p_message_name := 'XLA_AB_CON_UNEQUAL_BRCKT';
842 l_return := TRUE;
843 ELSE
844 p_message_name := NULL;
845 l_return := FALSE;
846 END IF;
847 END IF;
848
849 --
850 -- Check if condition has a row with no brackets and no source
851 --
852 IF l_return = FALSE THEN
853
854 OPEN c_source(l_description_prio_id);
855 FETCH c_source
856 INTO l_exist;
857
858 IF c_source%found then
859 p_message_name := 'XLA_AB_NO_BRCKT_SOURCE';
860 l_return := TRUE;
861 ELSE
862 p_message_name := NULL;
863 l_return := FALSE;
864 END IF;
865 CLOSE c_source;
866 END IF;
867
868 --
869 -- Check if any rows exist with just left and right bracket
870 --
871 IF l_return = FALSE THEN
872
873 OPEN c_left_right_bracket(l_description_prio_id);
874 FETCH c_left_right_bracket
875 INTO l_exist;
876
877 IF c_left_right_bracket%found then
878 p_message_name := 'XLA_AB_ONLY_LEFT_RIGHT_BRCKT';
879 l_return := TRUE;
880 ELSE
881 p_message_name := NULL;
882 l_return := FALSE;
883 END IF;
884 CLOSE c_left_right_bracket;
885 END IF;
886
887 --
888 -- Check if any rows exist with just left bracket and logical operator
889 --
890 IF l_return = FALSE THEN
891
892 OPEN c_left_bracket_operator(l_description_prio_id);
893 FETCH c_left_bracket_operator
894 INTO l_exist;
895
896 IF c_left_bracket_operator%found then
897 p_message_name := 'XLA_AB_LEFT_BRCKT_OPERATOR';
898 l_return := TRUE;
899 ELSE
900 p_message_name := NULL;
901 l_return := FALSE;
902 END IF;
903 CLOSE c_left_bracket_operator;
904 END IF;
905
906 IF l_return = FALSE THEN
907 --
908 -- Get all rows with no source and only left bracket
909 --
910 OPEN c_no_source_bracket(l_description_prio_id);
911 LOOP
912 FETCH c_no_source_bracket
913 INTO l_no_source_bracket;
914 EXIT WHEN c_no_source_bracket%notfound or l_return = TRUE;
915
916 -- Check if next row has only left bracket
917 OPEN c_only_left_bracket(l_description_prio_id);
918 FETCH c_only_left_bracket
919 INTO l_exist;
920
921 IF c_only_left_bracket%found then
922 p_message_name := null;
923 l_return := FALSE;
924 ELSE
925 p_message_name := 'XLA_AB_ONLY_LEFT_RIGHT_BRCKT';
926 l_return := TRUE;
927 END IF;
928 CLOSE c_only_left_bracket;
929 END LOOP;
930 CLOSE c_no_source_bracket;
931 END IF;
932
933 IF l_return = FALSE THEN
934 --
935 -- Get all rows with logical operator not null
936 --
937 OPEN c_log_op_not_null(l_description_prio_id);
938 LOOP
939 FETCH c_log_op_not_null
940 INTO l_log_op_not_null;
941 EXIT WHEN c_log_op_not_null%notfound or l_return = TRUE;
942
943 -- Check if next row has only right bracket
944 OPEN c_right_bracket(l_description_prio_id);
945 FETCH c_right_bracket
946 INTO l_exist;
947
948 IF c_right_bracket%found then
949 p_message_name := 'XLA_AB_CON_UNEQUAL_OPRTR';
950 l_return := TRUE;
951 ELSE
952 p_message_name := null;
953 l_return := FALSE;
954 END IF;
955 CLOSE c_right_bracket;
956 END LOOP;
957 CLOSE c_log_op_not_null;
958 END IF;
959
960 --
961 -- Check if condition has wrong number of logical operators
962 --
963 IF l_return = FALSE THEN
964
965 -- Get last row sequence
966 OPEN c_desc_sequence(l_description_prio_id);
967 FETCH c_desc_sequence
968 INTO l_desc_user_sequence;
969 CLOSE c_desc_sequence;
970
971 --
972 -- Check if last sequence has a not null logical operator
973 --
974 OPEN c_desc_last_operator(l_description_prio_id);
975 FETCH c_desc_last_operator
976 INTO l_exist;
977
978 IF c_desc_last_operator%found then
979 p_message_name := 'XLA_AB_CON_UNEQUAL_OPRTR';
980 l_return := TRUE;
981 ELSE
982 p_message_name := NULL;
983 l_return := FALSE;
984 END IF;
985 CLOSE c_desc_last_operator;
986
987 IF l_return = FALSE THEN
988 --
989 -- Get all rows which are not the last row or rows with just left bracket
990 -- and have no logical operator
991 --
992 OPEN c_no_logical_operator(l_description_prio_id);
993 LOOP
994 FETCH c_no_logical_operator
995 INTO l_no_logical_operator;
996 EXIT WHEN c_no_logical_operator%notfound or l_return = TRUE;
997
998 -- Check if next row has only right bracket
999 OPEN c_only_right_bracket(l_description_prio_id);
1000 FETCH c_only_right_bracket
1001 INTO l_exist;
1002
1003 IF c_only_right_bracket%found then
1004 p_message_name := null;
1005 l_return := FALSE;
1006 ELSE
1007 p_message_name := 'XLA_AB_CON_UNEQUAL_OPRTR';
1008 l_return := TRUE;
1009 END IF;
1010 CLOSE c_only_right_bracket;
1011 END LOOP;
1012 CLOSE c_no_logical_operator;
1013 END IF;
1014 END IF;
1015 END LOOP;
1016 CLOSE c_description_prio_id;
1017 END IF;
1018
1019 trace('p_message_name = '||p_message_name , C_LEVEL_STATEMENT,l_log_module);
1020 trace('desc_condition_is_invalid.End',C_LEVEL_PROCEDURE,l_Log_module);
1021
1022 RETURN l_return;
1023
1024 EXCEPTION
1025 WHEN xla_exceptions_pkg.application_exception THEN
1026 IF c_desc_brackets%ISOPEN THEN
1027 CLOSE c_desc_brackets;
1028 END IF;
1029 IF c_description_prio_id%ISOPEN THEN
1030 CLOSE c_description_prio_id;
1031 END IF;
1032 IF c_desc_last_operator%ISOPEN THEN
1036 CLOSE c_desc_max_left_seq;
1033 CLOSE c_desc_last_operator;
1034 END IF;
1035 IF c_desc_max_left_seq%ISOPEN THEN
1037 END IF;
1038 IF c_desc_max_right_seq%ISOPEN THEN
1039 CLOSE c_desc_max_right_seq;
1040 END IF;
1041 IF c_desc_min_left_seq%ISOPEN THEN
1042 CLOSE c_desc_min_left_seq;
1043 END IF;
1044 IF c_desc_min_right_seq%ISOPEN THEN
1045 CLOSE c_desc_min_right_seq;
1046 END IF;
1047
1048 RAISE;
1049
1050 WHEN OTHERS THEN
1051 IF c_desc_brackets%ISOPEN THEN
1052 CLOSE c_desc_brackets;
1053 END IF;
1054 IF c_description_prio_id%ISOPEN THEN
1055 CLOSE c_description_prio_id;
1056 END IF;
1057 IF c_desc_last_operator%ISOPEN THEN
1058 CLOSE c_desc_last_operator;
1059 END IF;
1060 IF c_desc_max_left_seq%ISOPEN THEN
1061 CLOSE c_desc_max_left_seq;
1062 END IF;
1063 IF c_desc_max_right_seq%ISOPEN THEN
1064 CLOSE c_desc_max_right_seq;
1065 END IF;
1066 IF c_desc_min_left_seq%ISOPEN THEN
1067 CLOSE c_desc_min_left_seq;
1068 END IF;
1069 IF c_desc_min_right_seq%ISOPEN THEN
1070 CLOSE c_desc_min_right_seq;
1071 END IF;
1072
1073 xla_exceptions_pkg.raise_message
1074 (p_location => 'xla_conditions_pkg.desc_condition_is_invalid');
1075
1076 END desc_condition_is_invalid;
1077
1078 /*======================================================================+
1079 | |
1080 | Public Function |
1081 | |
1082 | seg_condition_is_invalid |
1083 | |
1084 | Returns true if condition is invalid |
1085 | |
1086 +======================================================================*/
1087
1088 FUNCTION seg_condition_is_invalid
1089 (p_application_id IN NUMBER
1090 ,p_amb_context_code IN VARCHAR2
1091 ,p_segment_rule_type_code IN VARCHAR2
1092 ,p_segment_rule_code IN VARCHAR2
1093 ,p_message_name IN OUT NOCOPY VARCHAR2)
1094 RETURN BOOLEAN
1095
1096 IS
1097 --
1098 -- Variable declarations
1099 --
1100 l_exist varchar2(1);
1101 l_return boolean;
1102 l_segment_rule_detail_id number(38);
1103 l_seg_user_sequence number(38);
1104 l_seg_max_right_seq number(38);
1105 l_seg_max_left_seq number(38);
1106 l_seg_min_right_seq number(38);
1107 l_seg_min_left_seq number(38);
1108 l_count_1 number(38);
1109 l_count_2 number(38);
1110 l_log_module VARCHAR2(240);
1111 --
1112 -- Cursor declarations
1113 --
1114
1115 CURSOR c_seg_brackets
1116 IS
1117 SELECT 'x'
1118 FROM xla_seg_rule_details d
1119 WHERE d.application_id = p_application_id
1120 AND d.amb_context_code = p_amb_context_code
1121 AND d.segment_rule_type_code = p_segment_rule_type_code
1122 AND d.segment_rule_code = p_segment_rule_code
1123 AND exists(SELECT count(1)
1124 FROM xla_conditions c
1125 WHERE c.segment_rule_detail_id = d.segment_rule_detail_id
1126 AND c.bracket_left_code is not null
1127 MINUS
1128 SELECT count(1)
1129 FROM xla_conditions c1
1130 WHERE c1.segment_rule_detail_id = d.segment_rule_detail_id
1131 AND c1.bracket_right_code is not null);
1132
1133 CURSOR c_segment_rule_detail_id
1134 IS
1135 SELECT segment_rule_detail_id
1136 FROM xla_seg_rule_details d
1137 WHERE d.application_id = p_application_id
1138 AND d.amb_context_code = p_amb_context_code
1139 AND d.segment_rule_type_code = p_segment_rule_type_code
1140 AND d.segment_rule_code = p_segment_rule_code
1141 AND exists (SELECT 'y'
1142 FROM xla_conditions c
1143 WHERE c.segment_rule_detail_id = d.segment_rule_detail_id);
1144
1145 CURSOR c_seg_max_left_seq(p_segment_rule_detail_id NUMBER)
1149 WHERE c.segment_rule_detail_id = p_segment_rule_detail_id
1146 IS
1147 SELECT max(user_sequence)
1148 FROM xla_conditions c
1150 AND c.bracket_left_code is not null;
1151
1152 CURSOR c_seg_max_right_seq(p_segment_rule_detail_id NUMBER)
1153 IS
1154 SELECT max(user_sequence)
1155 FROM xla_conditions c
1156 WHERE c.segment_rule_detail_id = p_segment_rule_detail_id
1157 AND c.bracket_right_code is not null;
1158
1159 CURSOR c_seg_min_left_seq(p_segment_rule_detail_id NUMBER)
1160 IS
1161 SELECT min(user_sequence)
1162 FROM xla_conditions c
1163 WHERE c.segment_rule_detail_id = p_segment_rule_detail_id
1164 AND c.bracket_left_code is not null;
1165
1166 CURSOR c_seg_min_right_seq(p_segment_rule_detail_id NUMBER)
1167 IS
1168 SELECT min(user_sequence)
1169 FROM xla_conditions c
1170 WHERE c.segment_rule_detail_id = p_segment_rule_detail_id
1171 AND c.bracket_right_code is not null;
1172
1173 -- Check if any empty rows exist with just the sequence number
1174 CURSOR c_source(p_segment_rule_detail_id NUMBER)
1175 IS
1176 SELECT 'x'
1177 FROM xla_conditions c
1178 WHERE c.segment_rule_detail_id = p_segment_rule_detail_id
1179 AND c.bracket_left_code is null
1180 AND c.bracket_right_code is null
1181 AND c.source_code is null;
1182
1183 -- Check if any rows exist with just left and right bracket
1184 CURSOR c_left_right_bracket(p_segment_rule_detail_id NUMBER)
1185 IS
1186 SELECT 'x'
1187 FROM xla_conditions c
1188 WHERE c.segment_rule_detail_id = p_segment_rule_detail_id
1189 AND c.bracket_left_code is not null
1190 AND c.bracket_right_code is not null
1191 AND c.source_code is null;
1192
1193 -- Get the sequence for the last row
1194 CURSOR c_seg_sequence(p_segment_rule_detail_id NUMBER)
1195 IS
1196 SELECT max(user_sequence)
1197 FROM xla_conditions c
1198 WHERE c.segment_rule_detail_id = p_segment_rule_detail_id;
1199
1200 -- Check if last row has logical operator
1201 CURSOR c_seg_last_operator(p_segment_rule_detail_id NUMBER)
1202 IS
1203 SELECT 'x'
1204 FROM xla_conditions c
1205 WHERE c.segment_rule_detail_id = p_segment_rule_detail_id
1206 AND c.user_sequence = l_seg_user_sequence
1207 AND c.logical_operator_code is not null;
1208
1209 -- Check if any rows exist with just left bracket and logical operator
1210 CURSOR c_left_bracket_operator(p_segment_rule_detail_id NUMBER)
1211 IS
1212 SELECT 'x'
1213 FROM xla_conditions c
1214 WHERE c.segment_rule_detail_id = p_segment_rule_detail_id
1215 AND c.bracket_left_code is not null
1216 AND c.source_code is null
1217 AND c.logical_operator_code is not null;
1218
1219 -- Get all rows which are not the last row or rows with just left bracket
1220 -- and have no logical operator
1221 CURSOR c_no_logical_operator(p_segment_rule_detail_id NUMBER)
1222 IS
1223 SELECT user_sequence
1224 FROM xla_conditions c
1225 WHERE c.segment_rule_detail_id = p_segment_rule_detail_id
1226 AND (c.source_code is not null
1227 OR c.bracket_right_code is not null)
1228 AND c.logical_operator_code is null
1229 AND c.user_sequence <> l_seg_user_sequence;
1230
1231 l_no_logical_operator c_no_logical_operator%rowtype;
1232
1233 -- Check if next row has only right bracket
1234 -- and have no logical operator
1235 CURSOR c_only_right_bracket(p_segment_rule_detail_id NUMBER)
1236 IS
1237 SELECT 'x'
1238 FROM xla_conditions c
1239 WHERE c.segment_rule_detail_id = p_segment_rule_detail_id
1240 AND c.source_code is null
1241 AND c.bracket_right_code is not null
1242 AND c.user_sequence = l_no_logical_operator.user_sequence + 1;
1243
1244 -- Get all rows which have just left bracket and no source
1245 CURSOR c_no_source_bracket(p_segment_rule_detail_id NUMBER)
1246 IS
1247 SELECT user_sequence
1248 FROM xla_conditions c
1249 WHERE c.segment_rule_detail_id = p_segment_rule_detail_id
1250 AND c.source_code is null
1251 AND c.bracket_left_code is not null;
1252
1253 l_no_source_bracket c_no_source_bracket%rowtype;
1254
1255 -- Check if next row has only left bracket
1256 CURSOR c_only_left_bracket(p_segment_rule_detail_id NUMBER)
1257 IS
1258 SELECT 'x'
1259 FROM xla_conditions c
1260 WHERE c.segment_rule_detail_id = p_segment_rule_detail_id
1261 AND c.bracket_left_code is not null
1262 AND c.user_sequence = l_no_source_bracket.user_sequence + 1;
1263
1264 -- Get all rows with logical operator not null
1265 CURSOR c_log_op_not_null(p_segment_rule_detail_id NUMBER)
1266 IS
1267 SELECT user_sequence
1268 FROM xla_conditions c
1269 WHERE c.segment_rule_detail_id = p_segment_rule_detail_id
1270 AND c.logical_operator_code is not null;
1271
1272 l_log_op_not_null c_log_op_not_null%rowtype;
1273
1274 -- Check if next row has only right bracket
1275 CURSOR c_right_bracket(p_segment_rule_detail_id NUMBER)
1276 IS
1277 SELECT 'x'
1278 FROM xla_conditions c
1279 WHERE c.segment_rule_detail_id = p_segment_rule_detail_id
1283
1280 AND c.source_code is null
1281 AND c.bracket_right_code is not null
1282 AND c.user_sequence = l_log_op_not_null.user_sequence + 1;
1284 BEGIN
1285 trace('> xla_conditions_pkg.seg_condition_is_invalid' , C_LEVEL_PROCEDURE,l_log_module);
1286
1287 trace('application_id = '||p_application_id , C_LEVEL_STATEMENT,l_log_module);
1288 trace('segment_rule_type_code = '||p_segment_rule_type_code , C_LEVEL_STATEMENT,l_log_module);
1289 trace('segment_rule_code = '||p_segment_rule_code , C_LEVEL_STATEMENT,l_log_module);
1290
1291
1292 --
1293 -- Check if brackets are equal
1294 --
1295 OPEN c_seg_brackets;
1296 FETCH c_seg_brackets
1297 INTO l_exist;
1298 IF c_seg_brackets%found then
1299 p_message_name := 'XLA_AB_CON_UNEQUAL_BRCKT';
1300 l_return := TRUE;
1301 ELSE
1302 p_message_name := NULL;
1303 l_return := FALSE;
1304 END IF;
1305 CLOSE c_seg_brackets;
1306
1307 IF l_return = FALSE THEN
1308
1309 OPEN c_segment_rule_detail_id;
1310 LOOP
1311 FETCH c_segment_rule_detail_id
1312 INTO l_segment_rule_detail_id;
1313 EXIT WHEN c_segment_rule_detail_id%notfound or l_return = TRUE;
1314
1315 --
1316 -- Check if sequence for right bracket is less than sequence for left bracket
1317 --
1318 OPEN c_seg_max_left_seq(l_segment_rule_detail_id);
1319 FETCH c_seg_max_left_seq
1320 INTO l_seg_max_left_seq;
1321 CLOSE c_seg_max_left_seq;
1322
1323 OPEN c_seg_max_right_seq(l_segment_rule_detail_id);
1324 FETCH c_seg_max_right_seq
1325 INTO l_seg_max_right_seq;
1326 CLOSE c_seg_max_right_seq;
1327
1328 IF l_seg_max_right_seq < l_seg_max_left_seq then
1329 p_message_name := 'XLA_AB_CON_UNEQUAL_BRCKT';
1330 l_return := TRUE;
1331 ELSE
1332 p_message_name := NULL;
1333 l_return := FALSE;
1334 END IF;
1335
1336 IF l_return = FALSE THEN
1337 OPEN c_seg_min_left_seq(l_segment_rule_detail_id);
1338 FETCH c_seg_min_left_seq
1339 INTO l_seg_min_left_seq;
1340 CLOSE c_seg_min_left_seq;
1341
1342 OPEN c_seg_min_right_seq(l_segment_rule_detail_id);
1343 FETCH c_seg_min_right_seq
1344 INTO l_seg_min_right_seq;
1345 CLOSE c_seg_min_right_seq;
1346
1347 IF l_seg_min_right_seq < l_seg_min_left_seq then
1348 p_message_name := 'XLA_AB_CON_UNEQUAL_BRCKT';
1349 l_return := TRUE;
1350 ELSE
1351 p_message_name := NULL;
1352 l_return := FALSE;
1353 END IF;
1354 END IF;
1355
1356 --
1357 -- Check if condition has a row with no brackets and no source
1358 --
1359 IF l_return = FALSE THEN
1360
1361 OPEN c_source(l_segment_rule_detail_id);
1362 FETCH c_source
1363 INTO l_exist;
1364
1365 IF c_source%found then
1366 p_message_name := 'XLA_AB_NO_BRCKT_SOURCE';
1367 l_return := TRUE;
1368 ELSE
1369 p_message_name := NULL;
1370 l_return := FALSE;
1371 END IF;
1372 CLOSE c_source;
1373 END IF;
1374
1375 --
1376 -- Check if any rows exist with just left and right bracket
1377 --
1378 IF l_return = FALSE THEN
1379
1380 OPEN c_left_right_bracket(l_segment_rule_detail_id);
1381 FETCH c_left_right_bracket
1382 INTO l_exist;
1383
1384 IF c_left_right_bracket%found then
1385 p_message_name := 'XLA_AB_ONLY_LEFT_RIGHT_BRCKT';
1386 l_return := TRUE;
1387 ELSE
1388 p_message_name := NULL;
1389 l_return := FALSE;
1390 END IF;
1391 CLOSE c_left_right_bracket;
1392 END IF;
1393
1394 --
1395 -- Check if any rows exist with just left bracket and logical operator
1396 --
1397 IF l_return = FALSE THEN
1398
1399 OPEN c_left_bracket_operator(l_segment_rule_detail_id);
1400 FETCH c_left_bracket_operator
1401 INTO l_exist;
1402
1403 IF c_left_bracket_operator%found then
1404 p_message_name := 'XLA_AB_LEFT_BRCKT_OPERATOR';
1405 l_return := TRUE;
1406 ELSE
1407 p_message_name := NULL;
1408 l_return := FALSE;
1409 END IF;
1410 CLOSE c_left_bracket_operator;
1411 END IF;
1412
1413 IF l_return = FALSE THEN
1414 --
1415 -- Get all rows with no source and only left bracket
1416 --
1417 OPEN c_no_source_bracket(l_segment_rule_detail_id);
1418 LOOP
1419 FETCH c_no_source_bracket
1420 INTO l_no_source_bracket;
1421 EXIT WHEN c_no_source_bracket%notfound or l_return = TRUE;
1422
1423 -- Check if next row has only left bracket
1427
1424 OPEN c_only_left_bracket(l_segment_rule_detail_id);
1425 FETCH c_only_left_bracket
1426 INTO l_exist;
1428 IF c_only_left_bracket%found then
1429 p_message_name := null;
1430 l_return := FALSE;
1431 ELSE
1432 p_message_name := 'XLA_AB_ONLY_LEFT_RIGHT_BRCKT';
1433 l_return := TRUE;
1434 END IF;
1435 CLOSE c_only_left_bracket;
1436 END LOOP;
1437 CLOSE c_no_source_bracket;
1438 END IF;
1439
1440 IF l_return = FALSE THEN
1441 --
1442 -- Get all rows with logical operator not null
1443 --
1444 OPEN c_log_op_not_null(l_segment_rule_detail_id);
1445 LOOP
1446 FETCH c_log_op_not_null
1447 INTO l_log_op_not_null;
1448 EXIT WHEN c_log_op_not_null%notfound or l_return = TRUE;
1449
1450 -- Check if next row has only right bracket
1451 OPEN c_right_bracket(l_segment_rule_detail_id);
1452 FETCH c_right_bracket
1453 INTO l_exist;
1454
1455 IF c_right_bracket%found then
1456 p_message_name := 'XLA_AB_CON_UNEQUAL_OPRTR';
1457 l_return := TRUE;
1458 ELSE
1459 p_message_name := null;
1460 l_return := FALSE;
1461 END IF;
1462 CLOSE c_right_bracket;
1463 END LOOP;
1464 CLOSE c_log_op_not_null;
1465 END IF;
1466
1467 --
1468 -- Check if condition has wrong number of logical operators
1469 --
1470 IF l_return = FALSE THEN
1471
1472 -- Get last row sequence
1473 OPEN c_seg_sequence(l_segment_rule_detail_id);
1474 FETCH c_seg_sequence
1475 INTO l_seg_user_sequence;
1476 CLOSE c_seg_sequence;
1477
1478 --
1479 -- Check if last sequence has a not null logical operator
1480 --
1481 OPEN c_seg_last_operator(l_segment_rule_detail_id);
1482 FETCH c_seg_last_operator
1483 INTO l_exist;
1484
1485 IF c_seg_last_operator%found then
1486 p_message_name := 'XLA_AB_CON_UNEQUAL_OPRTR';
1487 l_return := TRUE;
1488 ELSE
1489 p_message_name := NULL;
1490 l_return := FALSE;
1491 END IF;
1492 CLOSE c_seg_last_operator;
1493
1494 IF l_return = FALSE THEN
1495 --
1496 -- Get all rows which are not the last row or rows with just left bracket
1497 -- and have no logical operator
1498 --
1499 OPEN c_no_logical_operator(l_segment_rule_detail_id);
1500 LOOP
1501 FETCH c_no_logical_operator
1502 INTO l_no_logical_operator;
1503 EXIT WHEN c_no_logical_operator%notfound or l_return = TRUE;
1504
1505 -- Check if next row has only right bracket
1506 OPEN c_only_right_bracket(l_segment_rule_detail_id);
1507 FETCH c_only_right_bracket
1508 INTO l_exist;
1509
1510 IF c_only_right_bracket%found then
1511 p_message_name := null;
1512 l_return := FALSE;
1513 ELSE
1514 p_message_name := 'XLA_AB_CON_UNEQUAL_OPRTR';
1515 l_return := TRUE;
1516 END IF;
1517 CLOSE c_only_right_bracket;
1518 END LOOP;
1519 CLOSE c_no_logical_operator;
1520 END IF;
1521 END IF;
1522 END LOOP;
1523 CLOSE c_segment_rule_detail_id;
1524 END IF;
1525
1526 trace('p_message_name = '||p_message_name , C_LEVEL_STATEMENT,l_log_module);
1527 trace('< xla_conditions_pkg.seg_condition_is_invalid' , C_LEVEL_PROCEDURE,l_log_module);
1528
1529 RETURN l_return;
1530
1531 EXCEPTION
1532 WHEN xla_exceptions_pkg.application_exception THEN
1533
1534 IF c_seg_brackets%ISOPEN THEN
1535 CLOSE c_seg_brackets;
1536 END IF;
1537 IF c_segment_rule_detail_id%ISOPEN THEN
1538 CLOSE c_segment_rule_detail_id;
1539 END IF;
1540 IF c_seg_last_operator%ISOPEN THEN
1541 CLOSE c_seg_last_operator;
1542 END IF;
1543 IF c_seg_max_left_seq%ISOPEN THEN
1544 CLOSE c_seg_max_left_seq;
1545 END IF;
1546 IF c_seg_max_right_seq%ISOPEN THEN
1547 CLOSE c_seg_max_right_seq;
1548 END IF;
1549 IF c_seg_min_left_seq%ISOPEN THEN
1550 CLOSE c_seg_min_left_seq;
1551 END IF;
1552 IF c_seg_min_right_seq%ISOPEN THEN
1553 CLOSE c_seg_min_right_seq;
1554 END IF;
1555
1556 RAISE;
1557
1558 WHEN OTHERS THEN
1559
1560 IF c_seg_brackets%ISOPEN THEN
1561 CLOSE c_seg_brackets;
1562 END IF;
1563 IF c_segment_rule_detail_id%ISOPEN THEN
1564 CLOSE c_segment_rule_detail_id;
1565 END IF;
1569 IF c_seg_max_left_seq%ISOPEN THEN
1566 IF c_seg_last_operator%ISOPEN THEN
1567 CLOSE c_seg_last_operator;
1568 END IF;
1570 CLOSE c_seg_max_left_seq;
1571 END IF;
1572 IF c_seg_max_right_seq%ISOPEN THEN
1573 CLOSE c_seg_max_right_seq;
1574 END IF;
1575 IF c_seg_min_left_seq%ISOPEN THEN
1576 CLOSE c_seg_min_left_seq;
1577 END IF;
1578 IF c_seg_min_right_seq%ISOPEN THEN
1579 CLOSE c_seg_min_right_seq;
1580 END IF;
1581
1582 xla_exceptions_pkg.raise_message
1583 (p_location => 'xla_conditions_pkg.seg_condition_is_invalid');
1584
1585 END seg_condition_is_invalid;
1586
1587 /*======================================================================+
1588 | |
1589 | Public Function |
1590 | |
1591 | acct_condition_is_invalid |
1592 | |
1593 | Returns true if condition is invalid |
1594 | |
1595 +======================================================================*/
1596
1597 FUNCTION acct_condition_is_invalid
1598 (p_application_id IN NUMBER
1599 ,p_amb_context_code IN VARCHAR2
1600 ,p_entity_code IN VARCHAR2
1601 ,p_event_class_code IN VARCHAR2
1602 ,p_accounting_line_type_code IN VARCHAR2
1603 ,p_accounting_line_code IN VARCHAR2
1604 ,p_message_name IN OUT NOCOPY VARCHAR2)
1605 RETURN BOOLEAN
1606
1607 IS
1608 --
1609 -- Variable declarations
1610 --
1611 l_exist varchar2(1);
1612 l_return boolean;
1613 l_acct_user_sequence number(38);
1614 l_acct_max_left_seq number(38);
1615 l_acct_max_right_seq number(38);
1616 l_acct_min_left_seq number(38);
1617 l_acct_min_right_seq number(38);
1618 l_application_id number(38);
1619 l_entity_code varchar2(30);
1620 l_event_class_code varchar2(30);
1621 l_source_code varchar2(30);
1622 l_count_1 number(38);
1623 l_count_2 number(38);
1624 l_log_module VARCHAR2(240);
1625 --
1626 -- Cursor declarations
1627 --
1628 CURSOR c_condition_exist
1629 IS
1630 SELECT 'x'
1631 FROM xla_conditions c
1632 WHERE c.application_id = p_application_id
1633 AND c.amb_context_code = p_amb_context_code
1634 AND c.entity_code = p_entity_code
1635 AND c.event_class_code = p_event_class_code
1636 AND c.accounting_line_type_code = p_accounting_line_type_code
1637 AND c.accounting_line_code = p_accounting_line_code;
1638
1639 CURSOR c_acct_brackets
1640 IS
1641 SELECT 'x'
1642 FROM xla_acct_line_types_b d
1643 WHERE d.application_id = p_application_id
1644 AND d.amb_context_code = p_amb_context_code
1645 AND d.entity_code = p_entity_code
1646 AND d.event_class_code = p_event_class_code
1647 AND d.accounting_line_type_code = p_accounting_line_type_code
1648 AND d.accounting_line_code = p_accounting_line_code
1649 AND exists(SELECT count(1)
1650 FROM xla_conditions c
1651 WHERE c.application_id = d.application_id
1652 AND c.amb_context_code = d.amb_context_code
1653 AND c.entity_code = d.entity_code
1654 AND c.event_class_code = d.event_class_code
1655 AND c.accounting_line_type_code = d.accounting_line_type_code
1656 AND c.accounting_line_code = d.accounting_line_code
1657 AND c.bracket_left_code is not null
1658 MINUS
1659 SELECT count(1)
1660 FROM xla_conditions c1
1661 WHERE c1.application_id = d.application_id
1662 AND c1.amb_context_code = d.amb_context_code
1663 AND c1.entity_code = d.entity_code
1664 AND c1.event_class_code = d.event_class_code
1665 AND c1.accounting_line_type_code = d.accounting_line_type_code
1666 AND c1.accounting_line_code = d.accounting_line_code
1667 AND c1.bracket_right_code is not null);
1668
1669 CURSOR c_acct_max_left_seq
1670 IS
1671 SELECT max(user_sequence)
1672 FROM xla_conditions c
1673 WHERE c.application_id = p_application_id
1674 AND c.amb_context_code = p_amb_context_code
1675 AND c.entity_code = p_entity_code
1676 AND c.event_class_code = p_event_class_code
1677 AND c.accounting_line_type_code = p_accounting_line_type_code
1678 AND c.accounting_line_code = p_accounting_line_code
1679 AND c.bracket_left_code is not null;
1680
1681 CURSOR c_acct_max_right_seq
1682 IS
1683 SELECT max(user_sequence)
1684 FROM xla_conditions c
1688 AND c.event_class_code = p_event_class_code
1685 WHERE c.application_id = p_application_id
1686 AND c.amb_context_code = p_amb_context_code
1687 AND c.entity_code = p_entity_code
1689 AND c.accounting_line_type_code = p_accounting_line_type_code
1690 AND c.accounting_line_code = p_accounting_line_code
1691 AND c.bracket_right_code is not null;
1692
1693 CURSOR c_acct_min_left_seq
1694 IS
1695 SELECT min(user_sequence)
1696 FROM xla_conditions c
1697 WHERE c.application_id = p_application_id
1698 AND c.amb_context_code = p_amb_context_code
1699 AND c.entity_code = p_entity_code
1700 AND c.event_class_code = p_event_class_code
1701 AND c.accounting_line_type_code = p_accounting_line_type_code
1702 AND c.accounting_line_code = p_accounting_line_code
1703 AND c.bracket_left_code is not null;
1704
1705 CURSOR c_acct_min_right_seq
1706 IS
1707 SELECT min(user_sequence)
1708 FROM xla_conditions c
1709 WHERE c.application_id = p_application_id
1710 AND c.amb_context_code = p_amb_context_code
1711 AND c.entity_code = p_entity_code
1712 AND c.event_class_code = p_event_class_code
1713 AND c.accounting_line_type_code = p_accounting_line_type_code
1714 AND c.accounting_line_code = p_accounting_line_code
1715 AND c.bracket_right_code is not null;
1716
1717 -- Check if any empty rows exist with just the sequence number
1718 CURSOR c_source
1719 IS
1720 SELECT 'x'
1721 FROM xla_conditions c
1722 WHERE c.application_id = p_application_id
1723 AND c.amb_context_code = p_amb_context_code
1724 AND c.entity_code = p_entity_code
1725 AND c.event_class_code = p_event_class_code
1726 AND c.accounting_line_type_code = p_accounting_line_type_code
1727 AND c.accounting_line_code = p_accounting_line_code
1728 AND c.bracket_left_code is null
1729 AND c.bracket_right_code is null
1730 AND c.source_code is null;
1731
1732 -- Check if any rows exist with just left and right bracket
1733 CURSOR c_left_right_bracket
1734 IS
1735 SELECT 'x'
1736 FROM xla_conditions c
1737 WHERE c.application_id = p_application_id
1738 AND c.amb_context_code = p_amb_context_code
1739 AND c.entity_code = p_entity_code
1740 AND c.event_class_code = p_event_class_code
1741 AND c.accounting_line_type_code = p_accounting_line_type_code
1742 AND c.accounting_line_code = p_accounting_line_code
1743 AND c.bracket_left_code is not null
1744 AND c.bracket_right_code is not null
1745 AND c.source_code is null;
1746
1747 -- Get the sequence for the last row
1748 CURSOR c_acct_sequence
1749 IS
1750 SELECT max(user_sequence)
1751 FROM xla_conditions c
1752 WHERE c.application_id = p_application_id
1753 AND c.amb_context_code = p_amb_context_code
1754 AND c.entity_code = p_entity_code
1755 AND c.event_class_code = p_event_class_code
1756 AND c.accounting_line_type_code = p_accounting_line_type_code
1757 AND c.accounting_line_code = p_accounting_line_code;
1758
1759 -- Check if last row has logical operator
1760 CURSOR c_acct_last_operator
1761 IS
1762 SELECT 'x'
1763 FROM xla_conditions c
1764 WHERE c.application_id = p_application_id
1765 AND c.amb_context_code = p_amb_context_code
1766 AND c.entity_code = p_entity_code
1767 AND c.event_class_code = p_event_class_code
1768 AND c.accounting_line_type_code = p_accounting_line_type_code
1769 AND c.accounting_line_code = p_accounting_line_code
1770 AND c.user_sequence = l_acct_user_sequence
1771 AND c.logical_operator_code is not null;
1772
1773 -- Check if any rows exist with just left bracket and logical operator
1774 CURSOR c_left_bracket_operator
1775 IS
1776 SELECT 'x'
1777 FROM xla_conditions c
1778 WHERE c.application_id = p_application_id
1779 AND c.amb_context_code = p_amb_context_code
1780 AND c.entity_code = p_entity_code
1781 AND c.event_class_code = p_event_class_code
1782 AND c.accounting_line_type_code = p_accounting_line_type_code
1783 AND c.accounting_line_code = p_accounting_line_code
1784 AND c.bracket_left_code is not null
1785 AND c.source_code is null
1786 AND c.logical_operator_code is not null;
1787
1788 -- Get all rows which are not the last row or rows with just left bracket
1789 -- and have no logical operator
1790 CURSOR c_no_logical_operator
1791 IS
1792 SELECT user_sequence
1793 FROM xla_conditions c
1794 WHERE c.application_id = p_application_id
1795 AND c.amb_context_code = p_amb_context_code
1796 AND c.entity_code = p_entity_code
1797 AND c.event_class_code = p_event_class_code
1798 AND c.accounting_line_type_code = p_accounting_line_type_code
1799 AND c.accounting_line_code = p_accounting_line_code
1800 AND (c.source_code is not null
1801 OR c.bracket_right_code is not null)
1802 AND c.logical_operator_code is null
1806
1803 AND c.user_sequence <> l_acct_user_sequence;
1804
1805 l_no_logical_operator c_no_logical_operator%rowtype;
1807 -- Check if next row has only right bracket
1808 -- and have no logical operator
1809 CURSOR c_only_right_bracket
1810 IS
1811 SELECT 'x'
1812 FROM xla_conditions c
1813 WHERE c.application_id = p_application_id
1814 AND c.amb_context_code = p_amb_context_code
1815 AND c.entity_code = p_entity_code
1816 AND c.event_class_code = p_event_class_code
1817 AND c.accounting_line_type_code = p_accounting_line_type_code
1818 AND c.accounting_line_code = p_accounting_line_code
1819 AND c.source_code is null
1820 AND c.bracket_right_code is not null
1821 AND c.user_sequence = l_no_logical_operator.user_sequence + 1;
1822
1823 -- Get all rows which have just left bracket and no source
1824 CURSOR c_no_source_bracket
1825 IS
1826 SELECT user_sequence
1827 FROM xla_conditions c
1828 WHERE c.application_id = p_application_id
1829 AND c.amb_context_code = p_amb_context_code
1830 AND c.entity_code = p_entity_code
1831 AND c.event_class_code = p_event_class_code
1832 AND c.accounting_line_type_code = p_accounting_line_type_code
1833 AND c.accounting_line_code = p_accounting_line_code
1834 AND c.source_code is null
1835 AND c.bracket_left_code is not null;
1836
1837 l_no_source_bracket c_no_source_bracket%rowtype;
1838
1839 -- Check if next row has only left bracket
1840 CURSOR c_only_left_bracket
1841 IS
1842 SELECT 'x'
1843 FROM xla_conditions c
1844 WHERE c.application_id = p_application_id
1845 AND c.amb_context_code = p_amb_context_code
1846 AND c.entity_code = p_entity_code
1847 AND c.event_class_code = p_event_class_code
1848 AND c.accounting_line_type_code = p_accounting_line_type_code
1849 AND c.accounting_line_code = p_accounting_line_code
1850 AND c.bracket_left_code is not null
1851 AND c.user_sequence = l_no_source_bracket.user_sequence + 1;
1852
1853 -- Get all rows with logical operator not null
1854 CURSOR c_log_op_not_null
1855 IS
1856 SELECT user_sequence
1857 FROM xla_conditions c
1858 WHERE c.application_id = p_application_id
1859 AND c.amb_context_code = p_amb_context_code
1860 AND c.entity_code = p_entity_code
1861 AND c.event_class_code = p_event_class_code
1862 AND c.accounting_line_type_code = p_accounting_line_type_code
1863 AND c.accounting_line_code = p_accounting_line_code
1864 AND c.logical_operator_code is not null;
1865
1866 l_log_op_not_null c_log_op_not_null%rowtype;
1867
1868 -- Check if next row has only right bracket
1869 CURSOR c_right_bracket
1870 IS
1871 SELECT 'x'
1872 FROM xla_conditions c
1873 WHERE c.application_id = p_application_id
1874 AND c.amb_context_code = p_amb_context_code
1875 AND c.entity_code = p_entity_code
1876 AND c.event_class_code = p_event_class_code
1877 AND c.accounting_line_type_code = p_accounting_line_type_code
1878 AND c.accounting_line_code = p_accounting_line_code
1879 AND c.source_code is null
1880 AND c.bracket_right_code is not null
1881 AND c.user_sequence = l_log_op_not_null.user_sequence + 1;
1882
1883 BEGIN
1884 l_log_module := C_DEFAULT_MODULE||'.acct_condition_is_invalid';
1885
1886 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1887 trace('acct_condition_is_invalid.Begin',C_LEVEL_PROCEDURE,l_log_module);
1888 END IF;
1889
1890 trace('application_id = '||p_application_id , C_LEVEL_STATEMENT,l_log_module);
1891 trace('entity_code = '||p_entity_code , C_LEVEL_STATEMENT,l_log_module);
1892 trace('event_class_code = '||p_event_class_code , C_LEVEL_STATEMENT,l_log_module);
1893 trace('accounting_line_type_code = '||p_accounting_line_type_code , C_LEVEL_STATEMENT,l_log_module);
1894 trace('accounting_line_code = '||p_accounting_line_code , C_LEVEL_STATEMENT,l_log_module);
1895
1896 l_application_id := p_application_id;
1897 l_entity_code := p_entity_code;
1898 l_event_class_code := p_event_class_code;
1899 --
1900 -- Check if accounting line type conditions exist
1901 --
1902
1903 OPEN c_condition_exist;
1904 FETCH c_condition_exist
1905 INTO l_exist;
1906 IF c_condition_exist%found then
1907 trace('c_condition_exist%found ',C_LEVEL_STATEMENT,l_Log_module);
1908 --
1909 -- check if condition has unequal brackets
1910 --
1911 OPEN c_acct_brackets;
1912 FETCH c_acct_brackets
1913 INTO l_exist;
1914 IF c_acct_brackets%found THEN
1915 trace('c_acct_brackets%found ',C_LEVEL_STATEMENT,l_Log_module);
1916 p_message_name := 'XLA_AB_CON_UNEQUAL_BRCKT';
1917 l_return := TRUE;
1918 ELSE
1919 trace('c_acct_brackets%notfound ',C_LEVEL_STATEMENT,l_Log_module);
1920 p_message_name := NULL;
1921 l_return := FALSE;
1922 END IF;
1923 CLOSE c_acct_brackets;
1924
1925 --
1926 -- check if sequence for right bracket is less than left bracket
1927 --
1931 INTO l_acct_max_left_seq;
1928 IF l_return = FALSE THEN
1929 OPEN c_acct_max_left_seq;
1930 FETCH c_acct_max_left_seq
1932 CLOSE c_acct_max_left_seq;
1933
1934 OPEN c_acct_max_right_seq;
1935 FETCH c_acct_max_right_seq
1936 INTO l_acct_max_right_seq;
1937 CLOSE c_acct_max_right_seq;
1938
1939 IF l_acct_max_right_seq < l_acct_max_left_seq then
1940 p_message_name := 'XLA_AB_CON_UNEQUAL_BRCKT';
1941 l_return := TRUE;
1942 ELSE
1943 p_message_name := NULL;
1944 l_return := FALSE;
1945 END IF;
1946 END IF;
1947
1948 IF l_return = FALSE THEN
1949 OPEN c_acct_min_left_seq;
1950 FETCH c_acct_min_left_seq
1951 INTO l_acct_min_left_seq;
1952 CLOSE c_acct_min_left_seq;
1953
1954 OPEN c_acct_min_right_seq;
1955 FETCH c_acct_min_right_seq
1956 INTO l_acct_min_right_seq;
1957 CLOSE c_acct_min_right_seq;
1958
1959 IF l_acct_min_right_seq < l_acct_min_left_seq then
1960 p_message_name := 'XLA_AB_CON_UNEQUAL_BRCKT';
1961 l_return := TRUE;
1962 ELSE
1963 p_message_name := NULL;
1964 l_return := FALSE;
1965 END IF;
1966 END IF;
1967
1968 --
1969 -- Check if condition has a row with no brackets and no source
1970 --
1971 IF l_return = FALSE THEN
1972
1973 OPEN c_source;
1974 FETCH c_source
1975 INTO l_exist;
1976
1977 IF c_source%found then
1978 trace('c_source%found',C_LEVEL_STATEMENT,l_Log_module);
1979 p_message_name := 'XLA_AB_NO_BRCKT_SOURCE';
1980 l_return := TRUE;
1981 ELSE
1982 trace('c_source%notfound',C_LEVEL_STATEMENT,l_Log_module);
1983 p_message_name := NULL;
1984 l_return := FALSE;
1985 END IF;
1986 CLOSE c_source;
1987 END IF;
1988
1989 --
1990 -- Check if any rows exist with just left and right bracket
1991 --
1992 IF l_return = FALSE THEN
1993
1994 OPEN c_left_right_bracket;
1995 FETCH c_left_right_bracket
1996 INTO l_exist;
1997
1998 IF c_left_right_bracket%found THEN
1999 trace('c_left_right_bracket%found',C_LEVEL_STATEMENT,l_Log_module);
2000 p_message_name := 'XLA_AB_ONLY_LEFT_RIGHT_BRCKT';
2001 l_return := TRUE;
2002 ELSE
2003 trace('c_left_right_bracket%notfound',C_LEVEL_STATEMENT,l_Log_module);
2004 p_message_name := NULL;
2005 l_return := FALSE;
2006 END IF;
2007 CLOSE c_left_right_bracket;
2008 END IF;
2009
2010 --
2011 -- Check if any rows exist with just left bracket and logical operator
2012 --
2013 IF l_return = FALSE THEN
2014
2015 OPEN c_left_bracket_operator;
2016 FETCH c_left_bracket_operator
2017 INTO l_exist;
2018
2019 IF c_left_bracket_operator%found THEN
2020 trace('c_left_bracket_operator%found',C_LEVEL_STATEMENT,l_Log_module);
2021 p_message_name := 'XLA_AB_LEFT_BRCKT_OPERATOR';
2022 l_return := TRUE;
2023 ELSE
2024 trace('c_left_bracket_operator%notfound',C_LEVEL_STATEMENT,l_Log_module);
2025 p_message_name := NULL;
2026 l_return := FALSE;
2027 END IF;
2028 CLOSE c_left_bracket_operator;
2029 END IF;
2030
2031 /*
2032 IF l_return = FALSE THEN
2033 --
2034 -- Get all rows with no source and only left bracket
2035 --
2036
2037 OPEN c_no_source_bracket;
2038 LOOP
2039 FETCH c_no_source_bracket
2040 INTO l_no_source_bracket;
2041 EXIT WHEN c_no_source_bracket%notfound or l_return = TRUE;
2042
2043 -- Check if next row has only left bracket
2044 OPEN c_only_left_bracket;
2045 FETCH c_only_left_bracket
2046 INTO l_exist;
2047
2048 IF c_only_left_bracket%found then
2049 trace('c_only_left_bracket%found',C_LEVEL_STATEMENT,l_Log_module);
2050
2051 p_message_name := null;
2052 l_return := FALSE;
2053 ELSE
2054 trace('c_only_left_bracket%notfound',C_LEVEL_STATEMENT,l_Log_module);
2055 p_message_name := 'XLA_AB_ONLY_LEFT_RIGHT_BRCKT';
2056 l_return := TRUE;
2057 END IF;
2058 CLOSE c_only_left_bracket;
2059 END LOOP;
2060 CLOSE c_no_source_bracket;
2061 END IF;
2062 */
2063 IF l_return = FALSE THEN
2064 --
2065 -- Get all rows with logical operator not null
2066 --
2067 OPEN c_log_op_not_null;
2068 LOOP
2069 FETCH c_log_op_not_null
2070 INTO l_log_op_not_null;
2071 EXIT WHEN c_log_op_not_null%notfound or l_return = TRUE;
2075 FETCH c_right_bracket
2072
2073 -- Check if next row has only right bracket
2074 OPEN c_right_bracket;
2076 INTO l_exist;
2077
2078 IF c_right_bracket%found THEN
2079 trace('c_right_bracket%found ',C_LEVEL_STATEMENT,l_Log_module);
2080 p_message_name := 'XLA_AB_CON_UNEQUAL_OPRTR';
2081 l_return := TRUE;
2082 ELSE
2083 trace('c_right_bracket%notfound ',C_LEVEL_STATEMENT,l_Log_module);
2084
2085 p_message_name := null;
2086 l_return := FALSE;
2087 END IF;
2088 CLOSE c_right_bracket;
2089 END LOOP;
2090 CLOSE c_log_op_not_null;
2091 END IF;
2092
2093 --
2094 -- Check if condition has wrong number of logical operators
2095 --
2096 /*
2097 IF l_return = FALSE THEN
2098
2099 -- Get last row sequence
2100 OPEN c_acct_sequence;
2101 FETCH c_acct_sequence
2102 INTO l_acct_user_sequence;
2103 CLOSE c_acct_sequence;
2104
2105 --
2106 -- Check if last sequence has a not null logical operator
2107 --
2108 OPEN c_acct_last_operator;
2109 FETCH c_acct_last_operator
2110 INTO l_exist;
2111
2112 IF c_acct_last_operator%found then
2113 trace('c_acct_last_operator%found ',C_LEVEL_STATEMENT,l_Log_module);
2114 p_message_name := 'XLA_AB_CON_UNEQUAL_OPRTR';
2115 l_return := TRUE;
2116 ELSE
2117 trace('c_acct_last_operator%notfound ',C_LEVEL_STATEMENT,l_Log_module);
2118 p_message_name := NULL;
2119 l_return := FALSE;
2120 END IF;
2121 CLOSE c_acct_last_operator;
2122
2123 IF l_return = FALSE THEN
2124 --
2125 -- Get all rows which are not the last row or rows with just left bracket
2126 -- and have no logical operator
2127 --
2128
2129 OPEN c_no_logical_operator;
2130 LOOP
2131 FETCH c_no_logical_operator
2132 INTO l_no_logical_operator;
2133 EXIT WHEN c_no_logical_operator%notfound or l_return = TRUE;
2134 trace('c_no_logical_operator%found ',C_LEVEL_STATEMENT,l_Log_module);
2135
2136 -- Check if next row has only right bracket
2137 OPEN c_only_right_bracket;
2138 FETCH c_only_right_bracket
2139 INTO l_exist;
2140
2141 IF c_only_right_bracket%found THEN
2142 trace('c_only_right_bracket%found ',C_LEVEL_STATEMENT,l_Log_module);
2143 p_message_name := null;
2144 l_return := FALSE;
2145 ELSE
2146 trace('c_only_right_bracket%notfound ',C_LEVEL_STATEMENT,l_Log_module);
2147 p_message_name := 'XLA_AB_CON_UNEQUAL_OPRTR';
2148 l_return := TRUE;
2149 END IF;
2150 CLOSE c_only_right_bracket;
2151 END LOOP;
2152 CLOSE c_no_logical_operator;
2153 END IF;
2154 END IF;
2155 */
2156 END IF;
2157
2158 CLOSE c_condition_exist;
2159
2160 trace('p_message_name = '||p_message_name ,C_LEVEL_STATEMENT,l_log_module);
2161 trace('acct_condition_is_invalid.End',C_LEVEL_PROCEDURE,l_log_module);
2162
2163 RETURN l_return;
2164
2165 EXCEPTION
2166 WHEN xla_exceptions_pkg.application_exception THEN
2167
2168 IF c_acct_brackets%ISOPEN THEN
2169 CLOSE c_acct_brackets;
2170 END IF;
2171 IF c_acct_last_operator%ISOPEN THEN
2172 CLOSE c_acct_last_operator;
2173 END IF;
2174 IF c_condition_exist%ISOPEN THEN
2175 CLOSE c_condition_exist;
2176 END IF;
2177 IF c_acct_max_left_seq%ISOPEN THEN
2178 CLOSE c_acct_max_left_seq;
2179 END IF;
2180 IF c_acct_max_right_seq%ISOPEN THEN
2181 CLOSE c_acct_max_right_seq;
2182 END IF;
2183 IF c_acct_min_left_seq%ISOPEN THEN
2184 CLOSE c_acct_min_left_seq;
2185 END IF;
2186 IF c_acct_min_right_seq%ISOPEN THEN
2187 CLOSE c_acct_min_right_seq;
2188 END IF;
2189
2190 RAISE;
2191
2192 WHEN OTHERS THEN
2193
2194 IF c_acct_brackets%ISOPEN THEN
2195 CLOSE c_acct_brackets;
2196 END IF;
2197 IF c_acct_last_operator%ISOPEN THEN
2198 CLOSE c_acct_last_operator;
2199 END IF;
2200 IF c_condition_exist%ISOPEN THEN
2201 CLOSE c_condition_exist;
2202 END IF;
2203 IF c_acct_max_left_seq%ISOPEN THEN
2204 CLOSE c_acct_max_left_seq;
2205 END IF;
2206 IF c_acct_max_right_seq%ISOPEN THEN
2207 CLOSE c_acct_max_right_seq;
2208 END IF;
2209 IF c_acct_min_left_seq%ISOPEN THEN
2210 CLOSE c_acct_min_left_seq;
2211 END IF;
2212 IF c_acct_min_right_seq%ISOPEN THEN
2213 CLOSE c_acct_min_right_seq;
2214 END IF;
2215
2216 xla_exceptions_pkg.raise_message
2217 (p_location => 'xla_conditions_pkg.acct_condition_is_invalid');
2218
2219 END acct_condition_is_invalid;
2220
2221 BEGIN
2222 -- l_log_module := C_DEFAULT_MODULE;
2223 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2224 g_log_enabled := fnd_log.test
2225 (log_level => g_log_level
2226 ,module => C_DEFAULT_MODULE);
2227
2228 IF NOT g_log_enabled THEN
2229 g_log_level := C_LEVEL_LOG_DISABLED;
2230 END IF;
2231 END xla_conditions_pkg;
[Home] [Help]