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