[Home] [Help]
PACKAGE BODY: APPS.XLA_SEG_RULES_PKG
Source
1 PACKAGE BODY xla_seg_rules_pkg AS
2 /* $Header: xlaamadr.pkb 120.25 2006/01/19 21:10:15 dcshah ship $ */
3 /*======================================================================+
4 | Copyright (c) 1995-2002 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +=======================================================================+
8 | PACKAGE NAME |
9 | xla_seg_rules_pkg |
10 | |
11 | DESCRIPTION |
12 | XLA Segment Rules Package |
13 | |
14 | HISTORY |
15 | 01-May-01 Dimple Shah Created |
16 | 20-Oct-04 Wynne Chan Updated for Journal Lines Definitions |
17 | |
18 +======================================================================*/
19
20 TYPE t_array_codes IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
21 TYPE t_array_type_codes IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
22
23 /*======================================================================+
24 | |
25 | Public Procedure |
26 | |
27 | delete_seg_rule_details |
28 | |
29 | Deletes all details of the segment rule |
30 | |
31 +======================================================================*/
32
33 PROCEDURE delete_seg_rule_details
34 (p_application_id IN NUMBER
35 ,p_amb_context_code IN VARCHAR2
36 ,p_segment_rule_type_code IN VARCHAR2
37 ,p_segment_rule_code IN VARCHAR2)
38 IS
39
40 l_segment_rule_detail_id NUMBER(38);
41
42 CURSOR c_seg_rule_details
43 IS
44 SELECT segment_rule_detail_id
45 FROM xla_seg_rule_details
46 WHERE application_id = p_application_id
47 AND amb_context_code = p_amb_context_code
48 AND segment_rule_type_code = p_segment_rule_type_code
49 AND segment_rule_code = p_segment_rule_code;
50
51 BEGIN
52
53 xla_utility_pkg.trace('> xla_seg_rules_pkg.delete_seg_rule_details' , 10);
54
55 xla_utility_pkg.trace('application_id = '||p_application_id , 20);
56 xla_utility_pkg.trace('segment_rule_type_code = '||p_segment_rule_type_code , 20);
57 xla_utility_pkg.trace('segment_rule_code = '||p_segment_rule_code , 20);
58
59 OPEN c_seg_rule_details;
60 LOOP
61 FETCH c_seg_rule_details
62 INTO l_segment_rule_detail_id;
63 EXIT WHEN c_seg_rule_details%notfound;
64
65 xla_conditions_pkg.delete_condition
66 (p_context => 'S'
67 ,p_segment_rule_detail_id => l_segment_rule_detail_id);
68
69 END LOOP;
70 CLOSE c_seg_rule_details;
71
72 DELETE
73 FROM xla_seg_rule_details
74 WHERE application_id = p_application_id
75 AND amb_context_code = p_amb_context_code
76 AND segment_rule_type_code = p_segment_rule_type_code
77 AND segment_rule_code = p_segment_rule_code;
78
79 xla_utility_pkg.trace('< xla_seg_rules_pkg.delete_seg_rule_details' , 10);
80
81 EXCEPTION
82 WHEN xla_exceptions_pkg.application_exception THEN
83 IF c_seg_rule_details%ISOPEN THEN
84 CLOSE c_seg_rule_details;
85 END IF;
86
87 RAISE;
88 WHEN OTHERS THEN
89 IF c_seg_rule_details%ISOPEN THEN
90 CLOSE c_seg_rule_details;
91 END IF;
92
93 xla_exceptions_pkg.raise_message
94 (p_location => 'xla_seg_rules_pkg.delete_seg_rule_details');
95
96 END delete_seg_rule_details;
97
98 /*======================================================================+
99 | |
100 | Public Procedure |
101 | |
102 | copy_seg_rule_details |
103 | |
104 | Copies details of a segment rule into a new segment rule |
105 | |
106 +======================================================================*/
107
108 PROCEDURE copy_seg_rule_details
109 (p_application_id IN NUMBER
110 ,p_amb_context_code IN VARCHAR2
111 ,p_old_segment_rule_type_code IN VARCHAR2
112 ,p_old_segment_rule_code IN VARCHAR2
113 ,p_new_segment_rule_type_code IN VARCHAR2
114 ,p_new_segment_rule_code IN VARCHAR2
115 ,p_old_transaction_coa_id IN NUMBER
116 ,p_new_transaction_coa_id IN NUMBER)
117 IS
118
119 l_condition_id integer;
120 l_new_segment_rule_detail_id integer;
121 l_creation_date DATE;
122 l_last_update_date DATE;
123 l_created_by INTEGER;
124 l_last_update_login INTEGER;
125 l_last_updated_by INTEGER;
126 l_value_flexfield_segment_code VARCHAR2(30);
127 l_value_flexfield_segment_name VARCHAR2(80);
128 l_con_flexfield_segment_code VARCHAR2(30);
129 l_con_flexfield_segment_name VARCHAR2(80);
130 l_con_v_flexfield_segment_code VARCHAR2(30);
131 l_con_v_flexfield_segment_name VARCHAR2(80);
132 l_inp_flex_appl_id NUMBER(15);
133 l_inp_id_flex_code VARCHAR2(30);
134 l_source_flex_appl_id NUMBER(15);
135 l_source_id_flex_code VARCHAR2(30);
136 l_value_source_flex_appl_id NUMBER(15);
137 l_value_source_id_flex_code VARCHAR2(30);
138
139 CURSOR c_seg_rule_details
140 IS
141 SELECT segment_rule_detail_id, user_sequence,
142 value_type_code, value_source_application_id, value_source_type_code,
143 value_source_code, value_constant, value_code_combination_id,
144 value_mapping_set_code,
145 value_flexfield_segment_code, input_source_application_id,
146 input_source_type_code, input_source_code,
147 value_segment_rule_appl_id, value_segment_rule_type_code,
148 value_segment_rule_code, value_adr_version_num
149 FROM xla_seg_rule_details
150 WHERE application_id = p_application_id
151 AND amb_context_code = p_amb_context_code
152 AND segment_rule_type_code = p_old_segment_rule_type_code
153 AND segment_rule_code = p_old_segment_rule_code;
154
155 l_seg_rule_detail c_seg_rule_details%rowtype;
156
157 CURSOR c_input_source
158 IS
159 SELECT flexfield_application_id, id_flex_code
160 FROM xla_sources_b
161 WHERE application_id = l_seg_rule_detail.input_source_application_id
162 AND source_type_code = l_seg_rule_detail.input_source_type_code
163 AND source_code = l_seg_rule_detail.input_source_code;
164
165
166 CURSOR c_detail_conditions
167 IS
168 SELECT user_sequence, bracket_left_code, bracket_right_code, value_type_code,
169 source_application_id, source_type_code, source_code,
170 flexfield_segment_code, value_flexfield_segment_code,
171 value_source_application_id, value_source_type_code,
172 value_source_code, value_constant, line_operator_code,
173 logical_operator_code, independent_value_constant
174 FROM xla_conditions
175 WHERE segment_rule_detail_id = l_seg_rule_detail.segment_rule_detail_id;
176
177 l_detail_condition c_detail_conditions%rowtype;
178
179 CURSOR c_source
180 IS
181 SELECT flexfield_application_id, id_flex_code
182 FROM xla_sources_b
183 WHERE application_id = l_detail_condition.source_application_id
184 AND source_type_code = l_detail_condition.source_type_code
185 AND source_code = l_detail_condition.source_code;
186
187 CURSOR c_value_source
188 IS
189 SELECT flexfield_application_id, id_flex_code
190 FROM xla_sources_b
191 WHERE application_id = l_detail_condition.value_source_application_id
192 AND source_type_code = l_detail_condition.value_source_type_code
193 AND source_code = l_detail_condition.value_source_code;
194
195 BEGIN
196
197 xla_utility_pkg.trace('> xla_seg_rules_pkg.copy_seg_rule_details' , 10);
198
199 xla_utility_pkg.trace('application_id = '||p_application_id , 20);
200 xla_utility_pkg.trace('segment_rule_type_code = '||p_old_segment_rule_type_code , 20);
201 xla_utility_pkg.trace('segment_rule_code = '||p_old_segment_rule_code , 20);
202 xla_utility_pkg.trace('segment_rule_type_code = '||p_new_segment_rule_type_code , 20);
203 xla_utility_pkg.trace('segment_rule_code = '||p_new_segment_rule_code , 20);
204
205
206 l_creation_date := sysdate;
207 l_last_update_date := sysdate;
208 l_created_by := xla_environment_pkg.g_usr_id;
209 l_last_update_login := xla_environment_pkg.g_login_id;
210 l_last_updated_by := xla_environment_pkg.g_usr_id;
211
212 OPEN c_seg_rule_details;
213 LOOP
214 FETCH c_seg_rule_details
215 INTO l_seg_rule_detail;
216 EXIT WHEN c_seg_rule_details%notfound;
217
218 IF l_seg_rule_detail.value_flexfield_segment_code is not null THEN
219 IF l_seg_rule_detail.value_type_code = 'S' THEN
220 IF p_new_transaction_coa_id is not null and p_old_transaction_coa_id is null THEN
221
222 l_value_flexfield_segment_code := xla_flex_pkg.get_qualifier_segment
223 (p_application_id => 101
224 ,p_id_flex_code => 'GL#'
225 ,p_id_flex_num => p_new_transaction_coa_id
226 ,p_qualifier_segment => l_seg_rule_detail.value_flexfield_segment_code);
227 ELSE
228 l_value_flexfield_segment_code := l_seg_rule_detail.value_flexfield_segment_code;
229 END IF;
230
231 ELSIF l_seg_rule_detail.value_type_code = 'M' THEN
232 -- value_type_code = 'M'
233
234 OPEN c_input_source;
235 FETCH c_input_source
236 INTO l_inp_flex_appl_id, l_inp_id_flex_code;
237 CLOSE c_input_source;
238
239 IF l_inp_flex_appl_id = 101 and l_inp_id_flex_code = 'GL#' THEN
240
241 IF p_new_transaction_coa_id is not null and p_old_transaction_coa_id is null THEN
242 l_value_flexfield_segment_code := xla_flex_pkg.get_qualifier_segment
243 (p_application_id => 101
244 ,p_id_flex_code => 'GL#'
245 ,p_id_flex_num => p_new_transaction_coa_id
246 ,p_qualifier_segment => l_seg_rule_detail.value_flexfield_segment_code);
247
248 ELSE
249 l_value_flexfield_segment_code := l_seg_rule_detail.value_flexfield_segment_code;
250 END IF;
251 ELSE
252 -- Other key flexfield segment
253 l_value_flexfield_segment_code := l_seg_rule_detail.value_flexfield_segment_code;
254 END IF;
255 END IF;
256 ELSE
257 -- value_flexfield_segment_code is null
258 l_value_flexfield_segment_code := l_seg_rule_detail.value_flexfield_segment_code;
259 END IF;
260
261 SELECT xla_seg_rule_details_s.nextval
262 INTO l_new_segment_rule_detail_id
263 FROM DUAL;
264
265 INSERT INTO xla_seg_rule_details
266 (segment_rule_detail_id
267 ,application_id
268 ,amb_context_code
269 ,segment_rule_type_code
270 ,segment_rule_code
271 ,user_sequence
272 ,value_type_code
273 ,value_source_application_id
274 ,value_source_type_code
275 ,value_source_code
276 ,value_constant
277 ,value_mapping_set_code
278 ,value_flexfield_segment_code
279 ,input_source_application_id
280 ,input_source_type_code
281 ,input_source_code
282 ,creation_date
283 ,created_by
284 ,last_update_date
285 ,last_updated_by
286 ,last_update_login
287 ,value_code_combination_id
288 ,value_segment_rule_appl_id
289 ,value_segment_rule_type_code
290 ,value_segment_rule_code
291 ,value_adr_version_num
292 )
293 VALUES
294 (l_new_segment_rule_detail_id
295 ,p_application_id
296 ,p_amb_context_code
297 ,p_new_segment_rule_type_code
298 ,p_new_segment_rule_code
299 ,l_seg_rule_detail.user_sequence
300 ,l_seg_rule_detail.value_type_code
301 ,l_seg_rule_detail.value_source_application_id
302 ,l_seg_rule_detail.value_source_type_code
303 ,l_seg_rule_detail.value_source_code
304 ,l_seg_rule_detail.value_constant
305 ,l_seg_rule_detail.value_mapping_set_code
306 ,l_value_flexfield_segment_code
307 ,l_seg_rule_detail.input_source_application_id
308 ,l_seg_rule_detail.input_source_type_code
309 ,l_seg_rule_detail.input_source_code
310 ,l_creation_date
311 ,l_created_by
312 ,l_last_update_date
313 ,l_last_updated_by
314 ,l_last_update_login
315 ,l_seg_rule_detail.value_code_combination_id
316 ,l_seg_rule_detail.value_segment_rule_appl_id
317 ,l_seg_rule_detail.value_segment_rule_type_code
318 ,l_seg_rule_detail.value_segment_rule_code
319 ,l_seg_rule_detail.value_adr_version_num
320 );
321
322 OPEN c_detail_conditions;
323 LOOP
324 FETCH c_detail_conditions
325 INTO l_detail_condition;
326 EXIT WHEN c_detail_conditions%notfound;
327
328 IF l_detail_condition.flexfield_segment_code is not null THEN
329
330 OPEN c_source;
331 FETCH c_source
332 INTO l_source_flex_appl_id, l_source_id_flex_code;
333 CLOSE c_source;
334
335 IF l_source_flex_appl_id = 101 and l_source_id_flex_code = 'GL#' THEN
336
337 IF p_new_transaction_coa_id is not null and p_old_transaction_coa_id is null THEN
338 l_con_flexfield_segment_code := xla_flex_pkg.get_qualifier_segment
339 (p_application_id => 101
340 ,p_id_flex_code => 'GL#'
341 ,p_id_flex_num => p_new_transaction_coa_id
342 ,p_qualifier_segment => l_detail_condition.flexfield_segment_code);
343
344 ELSE
345 l_con_flexfield_segment_code := l_detail_condition.flexfield_segment_code;
346 END IF;
347
348 ELSE
349 -- Other key flexfield segment
350 l_con_flexfield_segment_code := l_detail_condition.flexfield_segment_code;
351 END IF;
352 ELSE
353 l_con_flexfield_segment_code := l_detail_condition.flexfield_segment_code;
354 END IF;
355
356 -- check value_flexfield_segment_code
357 IF l_detail_condition.value_flexfield_segment_code is not null THEN
358
359 OPEN c_value_source;
360 FETCH c_value_source
361 INTO l_value_source_flex_appl_id, l_value_source_id_flex_code;
362 CLOSE c_value_source;
363
364 IF l_value_source_flex_appl_id = 101 and l_value_source_id_flex_code = 'GL#' THEN
365
366 IF p_new_transaction_coa_id is not null and p_old_transaction_coa_id is null THEN
367 l_con_v_flexfield_segment_code := xla_flex_pkg.get_qualifier_segment
368 (p_application_id => 101
369 ,p_id_flex_code => 'GL#'
370 ,p_id_flex_num => p_new_transaction_coa_id
371 ,p_qualifier_segment => l_detail_condition.value_flexfield_segment_code);
372
373 ELSE
374 l_con_v_flexfield_segment_code := l_detail_condition.value_flexfield_segment_code;
375 END IF;
376
377 ELSE
378 -- Other key flexfield segment
379 l_con_v_flexfield_segment_code := l_detail_condition.value_flexfield_segment_code;
380 END IF;
381 ELSE
382 l_con_v_flexfield_segment_code := l_detail_condition.value_flexfield_segment_code;
383 END IF;
384
385 SELECT xla_conditions_s.nextval
386 INTO l_condition_id
387 FROM DUAL;
388
389 INSERT INTO xla_conditions
390 (condition_id
391 ,user_sequence
392 ,application_id
393 ,amb_context_code
394 ,segment_rule_detail_id
395 ,bracket_left_code
396 ,bracket_right_code
397 ,value_type_code
398 ,source_application_id
399 ,source_type_code
400 ,source_code
401 ,flexfield_segment_code
402 ,value_flexfield_segment_code
403 ,value_source_application_id
404 ,value_source_type_code
405 ,value_source_code
406 ,value_constant
407 ,line_operator_code
408 ,logical_operator_code
409 ,creation_date
410 ,created_by
411 ,last_update_date
412 ,last_updated_by
413 ,last_update_login
414 ,independent_value_constant)
415 VALUES
416 (l_condition_id
417 ,l_detail_condition.user_sequence
418 ,p_application_id
419 ,p_amb_context_code
420 ,l_new_segment_rule_detail_id
421 ,l_detail_condition.bracket_left_code
422 ,l_detail_condition.bracket_right_code
423 ,l_detail_condition.value_type_code
424 ,l_detail_condition.source_application_id
425 ,l_detail_condition.source_type_code
426 ,l_detail_condition.source_code
427 ,l_con_flexfield_segment_code
428 ,l_con_v_flexfield_segment_code
429 ,l_detail_condition.value_source_application_id
430 ,l_detail_condition.value_source_type_code
431 ,l_detail_condition.value_source_code
432 ,l_detail_condition.value_constant
433 ,l_detail_condition.line_operator_code
434 ,l_detail_condition.logical_operator_code
435 ,l_creation_date
436 ,l_created_by
437 ,l_last_update_date
438 ,l_last_updated_by
439 ,l_last_update_login
440 ,l_detail_condition.independent_value_constant);
441
442 END LOOP;
443 CLOSE c_detail_conditions;
444
445 END LOOP;
446 CLOSE c_seg_rule_details;
447
448 xla_utility_pkg.trace('< xla_seg_rules_pkg.copy_seg_rule_details' , 10);
449
450 EXCEPTION
451 WHEN xla_exceptions_pkg.application_exception THEN
452 IF c_detail_conditions%ISOPEN THEN
453 CLOSE c_detail_conditions;
454 END IF;
455 IF c_seg_rule_details%ISOPEN THEN
456 CLOSE c_seg_rule_details;
457 END IF;
458 RAISE;
459 WHEN OTHERS THEN
460 IF c_detail_conditions%ISOPEN THEN
461 CLOSE c_detail_conditions;
462 END IF;
463 IF c_seg_rule_details%ISOPEN THEN
464 CLOSE c_seg_rule_details;
465 END IF;
466 xla_exceptions_pkg.raise_message
467 (p_location => 'xla_seg_rules_pkg.copy_seg_rule_details');
468
469 END copy_seg_rule_details;
470
471 /*======================================================================+
472 | |
473 | Public Function |
474 | |
475 | rule_in_use |
476 | |
477 | Returns true if the rule is in use by an accounting line type |
478 | |
479 +======================================================================*/
480
481 FUNCTION rule_in_use
482 (p_event IN VARCHAR2
483 ,p_application_id IN NUMBER
484 ,p_amb_context_code IN VARCHAR2
485 ,p_segment_rule_type_code IN VARCHAR2
486 ,p_segment_rule_code IN VARCHAR2
487 ,x_line_definition_name IN OUT NOCOPY VARCHAR2
488 ,x_line_definition_owner IN OUT NOCOPY VARCHAR2)
489 RETURN BOOLEAN
490 IS
491
492 l_return BOOLEAN;
493 l_exist VARCHAR2(1);
494 l_line_definition_name varchar2(80) := null;
495 l_line_definition_owner varchar2(80) := null;
496
497 CURSOR c_assignment_exist
498 IS
499 SELECT event_class_code, event_type_code, line_definition_owner_code, line_definition_code
500 FROM xla_line_defn_adr_assgns
501 WHERE application_id = p_application_id
502 AND amb_context_code = p_amb_context_code
503 AND segment_rule_type_code = p_segment_rule_type_code
504 AND segment_rule_code = p_segment_rule_code;
505
506 l_assignment_exist c_assignment_exist%rowtype;
507
508 CURSOR c_active_assignment_exist
509 IS
510 SELECT event_class_code, event_type_code, line_definition_owner_code, line_definition_code
511 FROM xla_line_defn_adr_assgns s
512 WHERE application_id = p_application_id
513 AND amb_context_code = p_amb_context_code
514 AND segment_rule_type_code = p_segment_rule_type_code
515 AND segment_rule_code = p_segment_rule_code
516 AND exists (SELECT 'y'
517 FROM xla_line_defn_jlt_assgns p
518 WHERE p.application_id = s.application_id
519 AND p.amb_context_code = s.amb_context_code
520 AND p.event_class_code = s.event_class_code
521 AND p.event_type_code = s.event_type_code
522 AND p.line_definition_owner_code = s.line_definition_owner_code
523 AND p.line_definition_code = s.line_definition_code
524 AND p.accounting_line_type_code = s.accounting_line_type_code
525 AND p.accounting_line_code = s.accounting_line_code
526 AND active_flag = 'Y');
527
528 l_active_assignment_exist c_active_assignment_exist%rowtype;
529
530 BEGIN
531
532 xla_utility_pkg.trace('> xla_seg_rules_pkg.rule_in_use' , 10);
533
534 xla_utility_pkg.trace('event = '||p_event , 20);
535 xla_utility_pkg.trace('application_id = '||p_application_id , 20);
536 xla_utility_pkg.trace('segment_rule_type_code = '||p_segment_rule_type_code , 20);
537 xla_utility_pkg.trace('segment_rule_code = '||p_segment_rule_code , 20);
538
539 IF p_event in ('DELETE','UPDATE') THEN
540 OPEN c_assignment_exist;
541 FETCH c_assignment_exist
542 INTO l_assignment_exist;
543 IF c_assignment_exist%found then
544
545 xla_line_definitions_pvt.get_line_definition_info
546 (p_application_id => p_application_id
547 ,p_amb_context_code => p_amb_context_code
548 ,p_event_class_code => l_assignment_exist.event_class_code
549 ,p_event_type_code => l_assignment_exist.event_type_code
550 ,p_line_definition_owner_code => l_assignment_exist.line_definition_owner_code
551 ,p_line_definition_code => l_assignment_exist.line_definition_code
552 ,x_line_definition_name => l_line_definition_name
553 ,x_line_definition_owner => l_line_definition_owner);
554
555 l_return := TRUE;
556 ELSE
557 l_return := FALSE;
558 END IF;
559 CLOSE c_assignment_exist;
560
561 ELSIF p_event = ('DISABLE') THEN
562 OPEN c_active_assignment_exist;
563 FETCH c_active_assignment_exist
564 INTO l_active_assignment_exist;
565 IF c_active_assignment_exist%found then
566
567 xla_line_definitions_pvt.get_line_definition_info
568 (p_application_id => p_application_id
569 ,p_amb_context_code => p_amb_context_code
570 ,p_event_class_code => l_active_assignment_exist.event_class_code
571 ,p_event_type_code => l_active_assignment_exist.event_type_code
572 ,p_line_definition_owner_code => l_active_assignment_exist.line_definition_owner_code
573 ,p_line_definition_code => l_active_assignment_exist.line_definition_code
574 ,x_line_definition_name => l_line_definition_name
575 ,x_line_definition_owner => l_line_definition_owner);
576
577 l_return := TRUE;
578 ELSE
579 l_return := FALSE;
580 END IF;
581 CLOSE c_active_assignment_exist;
582
583 ELSE
584 xla_exceptions_pkg.raise_message
585 ('XLA' ,'XLA_COMMON_ERROR'
586 ,'ERROR' ,'Invalid event passed'
587 ,'LOCATION' ,'xla_seg_rules_pkg.rule_in_use');
588
589 END IF;
590
591 x_line_definition_name := l_line_definition_name;
592 x_line_definition_owner := l_line_definition_owner;
593
594 xla_utility_pkg.trace('< xla_seg_rules_pkg.rule_in_use' , 10);
595
596 return l_return;
597
598 EXCEPTION
599 WHEN xla_exceptions_pkg.application_exception THEN
600 IF c_assignment_exist%ISOPEN THEN
601 CLOSE c_assignment_exist;
602 END IF;
603 IF c_active_assignment_exist%ISOPEN THEN
604 CLOSE c_active_assignment_exist;
605 END IF;
606
607 RAISE;
608 WHEN OTHERS THEN
609 IF c_assignment_exist%ISOPEN THEN
610 CLOSE c_assignment_exist;
611 END IF;
612 IF c_active_assignment_exist%ISOPEN THEN
613 CLOSE c_active_assignment_exist;
614 END IF;
615
616 xla_exceptions_pkg.raise_message
617 (p_location => 'xla_seg_rules_pkg.rule_in_use');
618
619 END rule_in_use;
620
621 /*======================================================================+
622 | |
623 | Public Function |
624 | |
625 | rule_is_invalid |
626 | |
627 | Returns true if the rule is invalid |
628 | |
629 +======================================================================*/
630
631 FUNCTION rule_is_invalid
632 (p_application_id IN NUMBER
633 ,p_amb_context_code IN VARCHAR2
634 ,p_segment_rule_type_code IN VARCHAR2
635 ,p_segment_rule_code IN VARCHAR2
636 ,p_message_name OUT NOCOPY VARCHAR2)
637 RETURN BOOLEAN
638 IS
639
640 l_return BOOLEAN;
641 l_exist VARCHAR2(1);
642 l_segment_rule_detail_id NUMBER(38);
643 l_count_all NUMBER(10) := 0;
644 l_count_only NUMBER(10) := 0;
645 l_count NUMBER(10) := 0;
646 l_message_name VARCHAR2(30);
647 l_application_id NUMBER(38);
648 l_amb_context_code VARCHAR2(30);
649 l_segment_rule_type_code VARCHAR2(1);
650 l_segment_rule_code VARCHAR2(30);
651
652 BEGIN
653
654 xla_utility_pkg.trace('> xla_seg_rules_pkg.rule_is_invalid' , 10);
655
656 xla_utility_pkg.trace('application_id = '||p_application_id , 20);
657 xla_utility_pkg.trace('segment_rule_type_code = '||p_segment_rule_type_code , 20);
658 xla_utility_pkg.trace('segment_rule_code = '||p_segment_rule_code , 20);
659
660 l_application_id := p_application_id;
661 l_amb_context_code := p_amb_context_code;
662 l_segment_rule_type_code := p_segment_rule_type_code;
663 l_segment_rule_code := p_segment_rule_code;
664
665 IF xla_conditions_pkg.seg_condition_is_invalid
666 (p_application_id => l_application_id
667 ,p_amb_context_code => l_amb_context_code
668 ,p_segment_rule_type_code => l_segment_rule_type_code
669 ,p_segment_rule_code => l_segment_rule_code
670 ,p_message_name => l_message_name)
671 THEN
672 p_message_name := l_message_name;
673 l_return := TRUE;
674 ELSE
675 p_message_name := NULL;
676 l_return := FALSE;
677 END IF;
678
679 xla_utility_pkg.trace('p_message_name = '||p_message_name , 20);
680 xla_utility_pkg.trace('< xla_seg_rules_pkg.rule_is_invalid' , 10);
681
682 return l_return;
683
684 EXCEPTION
685 WHEN xla_exceptions_pkg.application_exception THEN
686 RAISE;
687
688 WHEN OTHERS THEN
689 xla_exceptions_pkg.raise_message
690 (p_location => 'xla_seg_rules_pkg.rule_is_invalid');
691
692 END rule_is_invalid;
693
694 /*======================================================================+
695 | |
696 | Public Function |
697 | |
698 | seg_rule_is_locked |
699 | |
700 | Returns true if the rule is in use by a locked journal line definition|
701 | |
702 +======================================================================*/
703
704 FUNCTION seg_rule_is_locked
705 (p_application_id IN NUMBER
706 ,p_amb_context_code IN VARCHAR2
707 ,p_segment_rule_type_code IN VARCHAR2
708 ,p_segment_rule_code IN VARCHAR2)
709 RETURN BOOLEAN
710 IS
711
712 l_return BOOLEAN;
713 l_exist VARCHAR2(1);
714
715 CURSOR c_frozen_assignment_exist
716 IS
717 SELECT 'x'
718 FROM xla_line_defn_adr_assgns s
719 WHERE application_id = p_application_id
720 AND amb_context_code = p_amb_context_code
721 AND segment_rule_type_code = p_segment_rule_type_code
722 AND segment_rule_code = p_segment_rule_code
723 AND exists (SELECT 'x'
724 FROM xla_aad_line_defn_assgns a
725 ,xla_prod_acct_headers h
726 WHERE h.application_id = a.application_id
727 AND h.amb_context_code = a.amb_context_code
728 AND h.product_rule_type_code = a.product_rule_type_code
729 AND h.product_rule_code = a.product_rule_code
730 AND h.event_class_code = a.event_class_code
731 AND h.event_type_code = a.event_type_code
732 AND h.locking_status_flag = 'Y'
733 AND a.application_id = s.application_id
734 AND a.amb_context_code = s.amb_context_code
735 AND a.event_class_code = s.event_class_code
736 AND a.event_type_code = s.event_type_code
737 AND a.line_definition_owner_code = s.line_definition_owner_code
738 AND a.line_definition_code = s.line_definition_code);
739
740 CURSOR c_tab_assignment_exist
741 IS
742 SELECT 'x'
743 FROM xla_tab_acct_def_details s
744 WHERE application_id = p_application_id
745 AND amb_context_code = p_amb_context_code
746 AND segment_rule_type_code = p_segment_rule_type_code
747 AND segment_rule_code = p_segment_rule_code
748 AND exists (SELECT 'x'
749 FROM xla_tab_acct_defs_b a
750 WHERE a.application_id = s.application_id
751 AND a.amb_context_code = s.amb_context_code
752 AND a.account_definition_type_code = s.account_definition_type_code
753 AND a.account_definition_code = s.account_definition_code
754 AND a.locking_status_flag = 'Y');
755
756 BEGIN
757
758 xla_utility_pkg.trace('> xla_seg_rules_pkg.seg_rule_is_locked' , 10);
759
760 xla_utility_pkg.trace('application_id = '||p_application_id , 20);
761 xla_utility_pkg.trace('segment_rule_type_code = '||p_segment_rule_type_code , 20);
762 xla_utility_pkg.trace('segment_rule_code = '||p_segment_rule_code , 20);
763
764 OPEN c_frozen_assignment_exist;
765 FETCH c_frozen_assignment_exist
766 INTO l_exist;
767 IF c_frozen_assignment_exist%found then
768 l_return := TRUE;
769 ELSE
770 l_return := FALSE;
771 END IF;
772 CLOSE c_frozen_assignment_exist;
773
774 IF l_return = FALSE THEN
775 OPEN c_tab_assignment_exist;
776 FETCH c_tab_assignment_exist
777 INTO l_exist;
778 IF c_tab_assignment_exist%found then
779 l_return := TRUE;
780 ELSE
781 l_return := FALSE;
782 END IF;
783 CLOSE c_tab_assignment_exist;
784 END IF;
785
786 xla_utility_pkg.trace('< xla_seg_rules_pkg.seg_rule_is_locked' , 10);
787
788 return l_return;
789
790 EXCEPTION
791 WHEN xla_exceptions_pkg.application_exception THEN
792 IF c_frozen_assignment_exist%ISOPEN THEN
793 CLOSE c_frozen_assignment_exist;
794 END IF;
795
796 RAISE;
797 WHEN OTHERS THEN
798 IF c_frozen_assignment_exist%ISOPEN THEN
799 CLOSE c_frozen_assignment_exist;
800 END IF;
801
802 xla_exceptions_pkg.raise_message
803 (p_location => 'xla_seg_rules_pkg.seg_rule_is_locked');
804
805 END seg_rule_is_locked;
806
807 /*======================================================================+
808 | |
809 | Public Function |
810 | |
811 | uncompile_definitions |
812 | |
813 | Returns true if all the application accounting definitions and |
814 | journal line definitions using this segment rule are uncompiled |
815 | |
816 +======================================================================*/
817
818 FUNCTION uncompile_definitions
819 (p_application_id IN NUMBER
820 ,p_amb_context_code IN VARCHAR2
821 ,p_segment_rule_type_code IN VARCHAR2
822 ,p_segment_rule_code IN VARCHAR2
823 ,x_product_rule_name IN OUT NOCOPY VARCHAR2
824 ,x_product_rule_type IN OUT NOCOPY VARCHAR2
825 ,x_event_class_name IN OUT NOCOPY VARCHAR2
826 ,x_event_type_name IN OUT NOCOPY VARCHAR2
827 ,x_locking_status_flag IN OUT NOCOPY VARCHAR2)
828 RETURN BOOLEAN
829 IS
830 l_return BOOLEAN := TRUE;
831 l_exist VARCHAR2(1);
832
833 l_application_name varchar2(240) := null;
834 l_product_rule_name varchar2(80) := null;
835 l_product_rule_type varchar2(80) := null;
836 l_event_class_name varchar2(80) := null;
837 l_event_type_name varchar2(80) := null;
838 l_locking_status_flag varchar2(1) := null;
839
840 -- Retrive any event class/type assignment of an AAD that refer
841 -- to the ADR
842 CURSOR c_lock_aads IS
843 SELECT xpa.entity_code
844 , xpa.event_class_code
845 , xpa.event_type_code
846 , xpa.product_rule_type_code
847 , xpa.product_rule_code
848 , xpa.locking_status_flag
849 , xpa.validation_status_code
850 FROM xla_line_defn_adr_assgns xld
851 ,xla_aad_line_defn_assgns xal
852 ,xla_prod_acct_headers xpa
853 WHERE xpa.application_id = xal.application_id
854 AND xpa.amb_context_code = xal.amb_context_code
855 AND xpa.product_rule_type_code = xal.product_rule_type_code
856 AND xpa.product_rule_code = xal.product_rule_code
857 AND xpa.event_class_code = xal.event_class_code
858 AND xpa.event_type_code = xal.event_type_code
859 AND xal.application_id = xld.application_id
860 AND xal.amb_context_code = xld.amb_context_code
861 AND xal.event_class_code = xld.event_class_code
862 AND xal.event_type_code = xld.event_type_code
863 AND xal.line_definition_owner_code = xld.line_definition_owner_code
864 AND xal.line_definition_code = xld.line_definition_code
865 AND xld.application_id = p_application_id
866 AND xld.amb_context_code = p_amb_context_code
867 AND xld.segment_rule_type_code = p_segment_rule_type_code
868 AND xld.segment_rule_code = p_segment_rule_code
869 FOR UPDATE NOWAIT;
870
871 CURSOR c_update_aads IS
872 SELECT distinct xal.event_class_code
873 , xal.product_rule_type_code
874 , xal.product_rule_code
875 FROM xla_line_defn_adr_assgns xad
876 ,xla_aad_line_defn_assgns xal
877 ,xla_prod_acct_headers xpa
878 WHERE xpa.application_id = xal.application_id
879 AND xpa.amb_context_code = xal.amb_context_code
880 AND xpa.event_class_code = xal.event_class_code
881 AND xpa.event_type_code = xal.event_type_code
882 AND xal.application_id = xad.application_id
883 AND xal.amb_context_code = xad.amb_context_code
884 AND xal.event_class_code = xad.event_class_code
885 AND xal.event_type_code = xad.event_type_code
886 AND xal.line_definition_owner_code = xad.line_definition_owner_code
887 AND xal.line_definition_code = xad.line_definition_code
888 AND xad.application_id = p_application_id
889 AND xad.amb_context_code = p_amb_context_code
890 AND xad.segment_rule_type_code = p_segment_rule_type_code
891 AND xad.segment_rule_code = p_segment_rule_code;
892
893 l_event_class_codes t_array_codes;
894 l_product_rule_type_codes t_array_type_codes;
895 l_product_rule_codes t_array_codes;
896 BEGIN
897
898 xla_utility_pkg.trace('> xla_seg_rules_pkg.uncompile_definitions' , 10);
899
900 xla_utility_pkg.trace('application_id = '||p_application_id , 20);
901 xla_utility_pkg.trace('amb_context_code = '||p_amb_context_code , 20);
902 xla_utility_pkg.trace('segment_rule_type_code = '||p_segment_rule_type_code , 20);
903 xla_utility_pkg.trace('segment_rule_code = '||p_segment_rule_code , 20);
904
905 l_return := TRUE;
906
907 FOR l_lock_aad IN c_lock_aads LOOP
908 IF (l_lock_aad.validation_status_code NOT IN ('E', 'Y', 'N') OR
909 l_lock_aad.locking_status_flag = 'Y') THEN
910
911 xla_validations_pkg.get_product_rule_info
912 (p_application_id => p_application_id
913 ,p_amb_context_code => p_amb_context_code
914 ,p_product_rule_type_code => l_lock_aad.product_rule_type_code
915 ,p_product_rule_code => l_lock_aad.product_rule_code
916 ,p_application_name => l_application_name
917 ,p_product_rule_name => l_product_rule_name
918 ,p_product_rule_type => l_product_rule_type);
919
920 xla_validations_pkg.get_event_class_info
921 (p_application_id => p_application_id
922 ,p_entity_code => l_lock_aad.entity_code
923 ,p_event_class_code => l_lock_aad.event_class_code
924 ,p_event_class_name => l_event_class_name);
925
926 xla_validations_pkg.get_event_type_info
927 (p_application_id => p_application_id
928 ,p_entity_code => l_lock_aad.entity_code
929 ,p_event_class_code => l_lock_aad.event_class_code
930 ,p_event_type_code => l_lock_aad.event_type_code
931 ,p_event_type_name => l_event_type_name);
932
933 l_locking_status_flag := l_lock_aad.locking_status_flag;
934 l_return := FALSE;
935 EXIT;
936 END IF;
937 END LOOP;
938
939 IF (l_return) THEN
940
941 UPDATE xla_line_definitions_b xld
942 SET validation_status_code = 'N'
943 , last_update_date = sysdate
944 , last_updated_by = xla_environment_pkg.g_usr_id
945 , last_update_login = xla_environment_pkg.g_login_id
946 WHERE xld.application_id = p_application_id
947 AND xld.amb_context_code = p_amb_context_code
948 AND xld.validation_status_code <> 'N'
949 AND EXISTS
950 (SELECT 1
951 FROM xla_line_defn_adr_assgns xad
952 WHERE xad.application_id = p_application_id
953 AND xad.amb_context_code = p_amb_context_code
954 AND xad.segment_rule_type_code = p_segment_rule_type_code
955 AND xad.segment_rule_code = p_segment_rule_code
956 AND xad.event_class_code = xld.event_class_code
957 AND xad.event_type_code = xld.event_type_code
958 AND xad.line_definition_owner_code = xld.line_definition_owner_code
959 AND xad.line_definition_code = xld.line_definition_code);
960
961 OPEN c_update_aads;
962 FETCH c_update_aads BULK COLLECT INTO l_event_class_codes
963 ,l_product_rule_type_codes
964 ,l_product_rule_codes;
965 CLOSE c_update_aads;
966
967 IF (l_event_class_codes.count > 0) THEN
968
969 FORALL i IN 1..l_event_class_codes.LAST
970 UPDATE xla_product_rules_b
971 SET compile_status_code = 'N'
972 , updated_flag = 'Y'
973 , last_update_date = sysdate
974 , last_updated_by = xla_environment_pkg.g_usr_id
975 , last_update_login = xla_environment_pkg.g_login_id
976 WHERE application_id = p_application_id
977 AND amb_context_code = p_amb_context_code
978 AND product_rule_type_code = l_product_rule_type_codes(i)
979 AND product_rule_code = l_product_rule_codes(i)
980 AND (compile_status_code <> 'N' OR
981 updated_flag <> 'Y');
982
983 FORALL i IN 1..l_event_class_codes.LAST
984 UPDATE xla_prod_acct_headers xpa
985 SET validation_status_code = 'N'
986 , last_update_date = sysdate
987 , last_updated_by = xla_environment_pkg.g_usr_id
988 , last_update_login = xla_environment_pkg.g_login_id
989 WHERE application_id = p_application_id
990 AND amb_context_code = p_amb_context_code
991 AND event_class_code = l_event_class_codes(i)
992 AND product_rule_type_code = l_product_rule_type_codes(i)
993 AND product_rule_code = l_product_rule_codes(i)
994 AND validation_status_code <> 'N';
995
996 END IF;
997
998 UPDATE xla_appli_amb_contexts
999 SET updated_flag = 'Y'
1000 , last_update_date = sysdate
1001 , last_updated_by = xla_environment_pkg.g_usr_id
1002 , last_update_login = xla_environment_pkg.g_login_id
1003 WHERE application_id = p_application_id
1004 AND amb_context_code = p_amb_context_code
1005 AND updated_flag <> 'Y';
1006
1007 END IF;
1008
1009 x_product_rule_name := l_product_rule_name;
1010 x_product_rule_type := l_product_rule_type;
1011 x_event_class_name := l_event_class_name;
1012 x_event_type_name := l_event_type_name;
1013 x_locking_status_flag := l_locking_status_flag;
1014
1015 xla_utility_pkg.trace('< xla_seg_rules_pkg.uncompile_definitions' , 10);
1016
1017 return l_return;
1018
1019 EXCEPTION
1020 WHEN xla_exceptions_pkg.application_exception THEN
1021 IF c_lock_aads%ISOPEN THEN
1022 CLOSE c_lock_aads;
1023 END IF;
1024 IF c_update_aads%ISOPEN THEN
1025 CLOSE c_update_aads;
1026 END IF;
1027
1028 RAISE;
1029 WHEN OTHERS THEN
1030 IF c_lock_aads%ISOPEN THEN
1031 CLOSE c_lock_aads;
1032 END IF;
1033 IF c_update_aads%ISOPEN THEN
1034 CLOSE c_update_aads;
1035 END IF;
1036
1037 xla_exceptions_pkg.raise_message
1038 (p_location => 'xla_seg_rules_pkg.uncompile_definitions');
1039
1040 END uncompile_definitions;
1041
1042 /*======================================================================+
1043 | |
1044 | Public Function |
1045 | |
1046 | rule_in_use_by_tab |
1047 | |
1048 | Returns true if the rule is in use by a transaction account definition|
1049 | |
1050 +======================================================================*/
1051
1052 FUNCTION rule_in_use_by_tab
1053 (p_event IN VARCHAR2
1054 ,p_application_id IN NUMBER
1055 ,p_amb_context_code IN VARCHAR2
1056 ,p_segment_rule_type_code IN VARCHAR2
1057 ,p_segment_rule_code IN VARCHAR2
1058 ,p_trx_acct_def IN OUT NOCOPY VARCHAR2
1059 ,p_trx_acct_def_type IN OUT NOCOPY VARCHAR2
1060 ,p_trx_acct_type IN OUT NOCOPY VARCHAR2)
1061 RETURN BOOLEAN
1062 IS
1063
1064 l_return BOOLEAN;
1065 l_exist VARCHAR2(1);
1066 l_application_name varchar2(240) := null;
1067 l_trx_acct_def varchar2(80) := null;
1068 l_trx_acct_def_type varchar2(80) := null;
1069 l_trx_acct_type varchar2(80) := null;
1070
1071 CURSOR c_assignment_exist
1072 IS
1073 SELECT application_id, amb_context_code, account_definition_code,
1074 account_definition_type_code,
1075 account_type_code
1076 FROM xla_tab_acct_def_details
1077 WHERE application_id = p_application_id
1078 AND amb_context_code = p_amb_context_code
1079 AND segment_rule_type_code = p_segment_rule_type_code
1080 AND segment_rule_code = p_segment_rule_code;
1081
1082 l_assignment_exist c_assignment_exist%rowtype;
1083
1084 BEGIN
1085
1086 xla_utility_pkg.trace('> xla_seg_rules_pkg.rule_in_use_by_tab' , 10);
1087
1088 xla_utility_pkg.trace('event = '||p_event , 20);
1089 xla_utility_pkg.trace('application_id = '||p_application_id , 20);
1090 xla_utility_pkg.trace('segment_rule_type_code = '||p_segment_rule_type_code
1091 , 20);
1092 xla_utility_pkg.trace('segment_rule_code = '||p_segment_rule_code ,
1093 20);
1094
1095 IF p_event in ('DELETE','UPDATE','DISABLE') THEN
1096 OPEN c_assignment_exist;
1097 FETCH c_assignment_exist
1098 INTO l_assignment_exist;
1099 IF c_assignment_exist%found then
1100
1101 xla_validations_pkg.get_trx_acct_def_info
1102 (p_application_id => l_assignment_exist.application_id
1103 ,p_amb_context_code => l_assignment_exist.amb_context_code
1104 ,p_account_definition_type_code => l_assignment_exist.account_definition_type_code
1105 ,p_account_definition_code => l_assignment_exist.account_definition_code
1106 ,p_application_name => l_application_name
1107 ,p_trx_acct_def => l_trx_acct_def
1108 ,p_trx_acct_def_type => l_trx_acct_def_type);
1109
1110 xla_validations_pkg.get_trx_acct_type_info
1111 (p_application_id => l_assignment_exist.application_id
1112 ,p_account_type_code => l_assignment_exist.account_type_code
1113 ,p_trx_acct_type => l_trx_acct_type);
1114
1115 l_return := TRUE;
1116 ELSE
1117 l_return := FALSE;
1118 END IF;
1119 CLOSE c_assignment_exist;
1120
1121 ELSE
1122 xla_exceptions_pkg.raise_message
1123 ('XLA' ,'XLA_COMMON_ERROR'
1124 ,'ERROR' ,'Invalid event passed'
1125 ,'LOCATION' ,'xla_seg_rules_pkg.rule_in_use_by_tab');
1126
1127 END IF;
1128
1129 p_trx_acct_def := l_trx_acct_def;
1130 p_trx_acct_def_type := l_trx_acct_def_type;
1131 p_trx_acct_type := l_trx_acct_type;
1132
1133 xla_utility_pkg.trace('< xla_seg_rules_pkg.rule_in_use_by_tab' , 10);
1134
1135 return l_return;
1136
1137 EXCEPTION
1138 WHEN xla_exceptions_pkg.application_exception THEN
1139 IF c_assignment_exist%ISOPEN THEN
1140 CLOSE c_assignment_exist;
1141 END IF;
1142
1143 RAISE;
1144 WHEN OTHERS THEN
1145 IF c_assignment_exist%ISOPEN THEN
1146 CLOSE c_assignment_exist;
1147 END IF;
1148
1149 xla_exceptions_pkg.raise_message
1150 (p_location => 'xla_seg_rules_pkg.rule_in_use_by_tab');
1151
1152 END rule_in_use_by_tab;
1153
1154 /*======================================================================+
1155 | |
1156 | Public Function |
1157 | |
1158 | uncompile_tran_acct_def |
1159 | |
1160 | Returns true if all the transaction account definitions using |
1161 | the segment rule are uncompiled |
1162 | |
1163 +======================================================================*/
1164
1165 FUNCTION uncompile_tran_acct_def
1166 (p_application_id IN NUMBER
1167 ,p_amb_context_code IN VARCHAR2
1168 ,p_segment_rule_type_code IN VARCHAR2
1169 ,p_segment_rule_code IN VARCHAR2
1170 ,p_application_name IN OUT NOCOPY VARCHAR2
1171 ,p_trx_acct_def IN OUT NOCOPY VARCHAR2
1172 ,p_trx_acct_def_type IN OUT NOCOPY VARCHAR2)
1173 RETURN BOOLEAN
1174
1175 IS
1176
1177 l_return BOOLEAN := TRUE;
1178 l_exist VARCHAR2(1);
1179
1180 l_application_name varchar2(240) := null;
1181 l_trx_acct_def varchar2(80) := null;
1182 l_trx_acct_def_type varchar2(80) := null;
1183
1184 CURSOR c_trx_defs
1185 IS
1186 SELECT application_id, amb_context_code, account_definition_type_code,
1187 account_definition_code
1188 FROM xla_tab_acct_defs_b p
1189 WHERE exists (SELECT 'x'
1190 FROM xla_tab_acct_def_details s
1191 WHERE s.application_id = p_application_id
1192 AND s.amb_context_code = p_amb_context_code
1193 AND s.segment_rule_type_code = p_segment_rule_type_code
1194 AND s.segment_rule_code = p_segment_rule_code
1195 AND s.application_id = p.application_id
1196 AND s.amb_context_code = p.amb_context_code
1197 AND s.account_definition_type_code = p.account_definition_type_code
1198 AND s.account_definition_code = p.account_definition_code);
1199
1200 l_trx_def c_trx_defs%rowtype;
1201
1202 BEGIN
1203
1204 xla_utility_pkg.trace('> xla_seg_rules_pkg.uncompile_tran_acct_def' , 10);
1205
1206 xla_utility_pkg.trace('application_id = '||p_application_id , 20);
1207 xla_utility_pkg.trace('segment_rule_type_code = '||p_segment_rule_type_code
1208 , 20);
1209 xla_utility_pkg.trace('segment_rule_code = '||p_segment_rule_code ,
1210 20);
1211
1212 OPEN c_trx_defs;
1213 LOOP
1214 FETCH c_trx_defs
1215 INTO l_trx_def;
1216 EXIT WHEN c_trx_defs%NOTFOUND or l_return=FALSE;
1217
1218 IF xla_tab_acct_defs_pkg.uncompile_tran_acct_def
1219 (p_application_id => l_trx_def.application_id
1220 ,p_amb_context_code => l_trx_def.amb_context_code
1221 ,p_account_definition_type_code => l_trx_def.account_definition_type_code
1222 ,p_account_definition_code => l_trx_def.account_definition_code) THEN
1223
1224 l_return := TRUE;
1225 ELSE
1226
1227 xla_validations_pkg.get_trx_acct_def_info
1228 (p_application_id => l_trx_def.application_id
1229 ,p_amb_context_code => l_trx_def.amb_context_code
1230 ,p_account_definition_type_code => l_trx_def.account_definition_type_code
1231 ,p_account_definition_code => l_trx_def.account_definition_code
1232 ,p_application_name => l_application_name
1233 ,p_trx_acct_def => l_trx_acct_def
1234 ,p_trx_acct_def_type => l_trx_acct_def_type);
1235
1236 l_return := FALSE;
1237 END IF;
1238 END LOOP;
1239 CLOSE c_trx_defs;
1240
1241 p_application_name := l_application_name;
1242 p_trx_acct_def := l_trx_acct_def;
1243 p_trx_acct_def_type := l_trx_acct_def_type;
1244
1245 xla_utility_pkg.trace('< xla_seg_rules_pkg.uncompile_tran_acct_def' , 10);
1246
1247 return l_return;
1248
1249 EXCEPTION
1250 WHEN xla_exceptions_pkg.application_exception THEN
1251 IF c_trx_defs%ISOPEN THEN
1252 CLOSE c_trx_defs;
1253 END IF;
1254
1255 RAISE;
1256 WHEN OTHERS THEN
1257 IF c_trx_defs%ISOPEN THEN
1258 CLOSE c_trx_defs;
1259 END IF;
1260
1261 xla_exceptions_pkg.raise_message
1262 (p_location => 'xla_seg_rules_pkg.uncompile_tran_acct_def');
1263
1264 END uncompile_tran_acct_def;
1265
1266 /*======================================================================+
1267 | |
1268 | Public Function |
1269 | |
1270 | check_copy_seg_rule_details |
1271 | |
1272 | Checks if the segment rule details can be copied into the new one |
1273 | |
1274 +======================================================================*/
1275
1276 FUNCTION check_copy_seg_rule_details
1277 (p_application_id IN NUMBER
1278 ,p_amb_context_code IN VARCHAR2
1279 ,p_old_segment_rule_type_code IN VARCHAR2
1280 ,p_old_segment_rule_code IN VARCHAR2
1281 ,p_old_transaction_coa_id IN NUMBER
1282 ,p_new_transaction_coa_id IN NUMBER
1283 ,p_old_flex_value_set_id IN NUMBER
1284 ,p_new_flex_value_set_id IN NUMBER
1285 ,p_message IN OUT NOCOPY VARCHAR2
1286 ,p_token_1 IN OUT NOCOPY VARCHAR2
1287 ,p_value_1 IN OUT NOCOPY VARCHAR2)
1288 RETURN BOOLEAN
1289 IS
1290
1291 l_value_flexfield_segment_code VARCHAR2(30);
1292 l_value_flexfield_segment_name VARCHAR2(80);
1293 l_con_flexfield_segment_code VARCHAR2(30);
1294 l_con_flexfield_segment_name VARCHAR2(80);
1295 l_con_v_flexfield_segment_code VARCHAR2(30);
1296 l_con_v_flexfield_segment_name VARCHAR2(80);
1297 l_inp_flex_appl_id NUMBER(15);
1298 l_inp_id_flex_code VARCHAR2(30);
1299 l_source_flex_appl_id NUMBER(15);
1300 l_source_id_flex_code VARCHAR2(30);
1301 l_value_source_flex_appl_id NUMBER(15);
1302 l_value_source_id_flex_code VARCHAR2(30);
1303 l_return BOOLEAN := TRUE;
1304
1305
1306 CURSOR c_flex_value
1307 IS
1308 SELECT value_constant
1309 FROM xla_seg_rule_details seg
1310 WHERE application_id = p_application_id
1311 AND amb_context_code = p_amb_context_code
1312 AND segment_rule_type_code = p_old_segment_rule_type_code
1313 AND segment_rule_code = p_old_segment_rule_code
1314 AND not exists (SELECT 'x'
1315 FROM fnd_flex_values ffv
1316 WHERE ffv.flex_value_set_id = p_new_flex_value_set_id
1317 AND ffv.flex_value = seg.value_constant);
1318
1319 l_flex_value c_flex_value%rowtype;
1320
1321 CURSOR c_seg_rule_details
1322 IS
1323 SELECT segment_rule_detail_id, user_sequence,
1324 value_type_code, value_source_application_id, value_source_type_code,
1325 value_source_code, value_constant, value_code_combination_id,
1326 value_mapping_set_code,
1327 value_flexfield_segment_code, input_source_application_id,
1328 input_source_type_code, input_source_code
1329 FROM xla_seg_rule_details
1330 WHERE application_id = p_application_id
1331 AND amb_context_code = p_amb_context_code
1332 AND segment_rule_type_code = p_old_segment_rule_type_code
1333 AND segment_rule_code = p_old_segment_rule_code;
1334
1335 l_seg_rule_detail c_seg_rule_details%rowtype;
1336
1337 CURSOR c_input_source
1338 IS
1339 SELECT flexfield_application_id, id_flex_code
1340 FROM xla_sources_b
1341 WHERE application_id = l_seg_rule_detail.input_source_application_id
1342 AND source_type_code = l_seg_rule_detail.input_source_type_code
1343 AND source_code = l_seg_rule_detail.input_source_code;
1344
1345 CURSOR c_detail_conditions
1346 IS
1347 SELECT user_sequence, bracket_left_code, bracket_right_code, value_type_code,
1348 source_application_id, source_type_code, source_code,
1349 flexfield_segment_code, value_flexfield_segment_code,
1350 value_source_application_id, value_source_type_code,
1351 value_source_code, value_constant, line_operator_code,
1352 logical_operator_code, independent_value_constant
1353 FROM xla_conditions
1354 WHERE segment_rule_detail_id = l_seg_rule_detail.segment_rule_detail_id;
1355
1356 l_detail_condition c_detail_conditions%rowtype;
1357
1358 CURSOR c_source
1359 IS
1360 SELECT flexfield_application_id, id_flex_code
1361 FROM xla_sources_b
1362 WHERE application_id = l_detail_condition.source_application_id
1363 AND source_type_code = l_detail_condition.source_type_code
1364 AND source_code = l_detail_condition.source_code;
1365
1366 CURSOR c_value_source
1367 IS
1368 SELECT flexfield_application_id, id_flex_code
1369 FROM xla_sources_b
1370 WHERE application_id = l_detail_condition.value_source_application_id
1371 AND source_type_code = l_detail_condition.value_source_type_code
1372 AND source_code = l_detail_condition.value_source_code;
1373
1374 BEGIN
1375
1376 xla_utility_pkg.trace('> xla_seg_rules_pkg.check_copy_seg_rule_details' , 10);
1377
1378 xla_utility_pkg.trace('application_id = '||p_application_id , 20);
1379 xla_utility_pkg.trace('segment_rule_type_code = '||p_old_segment_rule_type_code , 20);
1380 xla_utility_pkg.trace('segment_rule_code = '||p_old_segment_rule_code , 20);
1381
1382 IF p_new_flex_value_set_id is not null then
1383 IF p_old_flex_value_set_id <> p_new_flex_value_set_id THEN
1384 OPEN c_flex_value;
1385 FETCH c_flex_value
1386 INTO l_flex_value;
1387 IF c_flex_value%found THEN
1388
1389 p_message := 'XLA_AB_FLEX_VALUE_NOT_EXIST';
1390 p_token_1 := 'FLEX_VALUE';
1391 p_value_1 := l_flex_value.value_constant;
1392 l_return := FALSE;
1393
1394 END IF;
1395 CLOSE c_flex_value;
1396 END IF;
1397 END IF;
1398
1399 IF l_return = TRUE THEN
1400
1401 OPEN c_seg_rule_details;
1402 LOOP
1403 FETCH c_seg_rule_details
1404 INTO l_seg_rule_detail;
1405 EXIT WHEN c_seg_rule_details%notfound or l_return = FALSE;
1406
1407 IF l_seg_rule_detail.value_flexfield_segment_code is not null THEN
1408 IF l_seg_rule_detail.value_type_code = 'S' THEN
1409 IF p_new_transaction_coa_id is not null and p_old_transaction_coa_id is null THEN
1410
1411 l_value_flexfield_segment_code := xla_flex_pkg.get_qualifier_segment
1412 (p_application_id => 101
1413 ,p_id_flex_code => 'GL#'
1414 ,p_id_flex_num => p_new_transaction_coa_id
1415 ,p_qualifier_segment => l_seg_rule_detail.value_flexfield_segment_code);
1416
1417 IF l_value_flexfield_segment_code is null THEN
1418 l_value_flexfield_segment_name := xla_flex_pkg.get_qualifier_name
1419 (p_application_id => 101
1420 ,p_id_flex_code => 'GL#'
1421 ,p_qualifier_segment => l_seg_rule_detail.value_flexfield_segment_code);
1422
1423 p_message := 'XLA_AB_TRX_COA_NO_QUAL';
1424 p_token_1 := 'QUALIFIER_NAME';
1425 p_value_1 := l_value_flexfield_segment_name;
1426 l_return := FALSE;
1427
1428 END IF;
1429 END IF;
1430
1431 ELSIF l_seg_rule_detail.value_type_code = 'M' THEN
1432 -- value_type_code = 'M'
1433
1434 OPEN c_input_source;
1435 FETCH c_input_source
1436 INTO l_inp_flex_appl_id, l_inp_id_flex_code;
1437 CLOSE c_input_source;
1438
1439 IF l_inp_flex_appl_id = 101 and l_inp_id_flex_code = 'GL#' THEN
1440
1441 IF p_new_transaction_coa_id is not null and p_old_transaction_coa_id is null THEN
1442 l_value_flexfield_segment_code := xla_flex_pkg.get_qualifier_segment
1443 (p_application_id => 101
1444 ,p_id_flex_code => 'GL#'
1445 ,p_id_flex_num => p_new_transaction_coa_id
1446 ,p_qualifier_segment => l_seg_rule_detail.value_flexfield_segment_code);
1447
1448 IF l_value_flexfield_segment_code is null THEN
1449 l_value_flexfield_segment_name := xla_flex_pkg.get_qualifier_name
1450 (p_application_id => 101
1451 ,p_id_flex_code => 'GL#'
1452 ,p_qualifier_segment => l_seg_rule_detail.value_flexfield_segment_code);
1453
1454 p_message := 'XLA_AB_TRX_COA_NO_QUAL';
1455 p_token_1 := 'QUALIFIER_NAME';
1456 p_value_1 := l_value_flexfield_segment_name;
1457 l_return := FALSE;
1458
1459 END IF;
1460 END IF;
1461 END IF;
1462 END IF;
1463 END IF;
1464
1465 IF l_return = TRUE THEN
1466
1467 OPEN c_detail_conditions;
1468 LOOP
1469 FETCH c_detail_conditions
1470 INTO l_detail_condition;
1471 EXIT WHEN c_detail_conditions%notfound or l_return = FALSE;
1472
1473 IF l_detail_condition.flexfield_segment_code is not null THEN
1474
1475 OPEN c_source;
1476 FETCH c_source
1477 INTO l_source_flex_appl_id, l_source_id_flex_code;
1478 CLOSE c_source;
1479
1480 IF l_source_flex_appl_id = 101 and l_source_id_flex_code = 'GL#' THEN
1481
1482 IF p_new_transaction_coa_id is not null and p_old_transaction_coa_id is null THEN
1483 l_con_flexfield_segment_code := xla_flex_pkg.get_qualifier_segment
1484 (p_application_id => 101
1485 ,p_id_flex_code => 'GL#'
1486 ,p_id_flex_num => p_new_transaction_coa_id
1487 ,p_qualifier_segment => l_detail_condition.flexfield_segment_code);
1488
1489 IF l_con_flexfield_segment_code is null THEN
1490 l_con_flexfield_segment_name := xla_flex_pkg.get_qualifier_name
1491 (p_application_id => 101
1492 ,p_id_flex_code => 'GL#'
1493 ,p_qualifier_segment => l_detail_condition.flexfield_segment_code);
1494
1495 p_message := 'XLA_AB_TRX_COA_NO_QUAL';
1496 p_token_1 := 'QUALIFIER_NAME';
1497 p_value_1 := l_con_flexfield_segment_name;
1498 l_return := FALSE;
1499
1500 END IF;
1501 END IF;
1502 END IF;
1503 END IF;
1504
1505 -- check value_flexfield_segment_code
1506 IF l_return = TRUE THEN
1507 IF l_detail_condition.value_flexfield_segment_code is not null THEN
1508
1509 OPEN c_value_source;
1510 FETCH c_value_source
1511 INTO l_value_source_flex_appl_id, l_value_source_id_flex_code;
1512 CLOSE c_value_source;
1513
1514 IF l_value_source_flex_appl_id = 101 and l_value_source_id_flex_code = 'GL#' THEN
1515
1516 IF p_new_transaction_coa_id is not null and p_old_transaction_coa_id is null THEN
1517 l_con_v_flexfield_segment_code := xla_flex_pkg.get_qualifier_segment
1518 (p_application_id => 101
1519 ,p_id_flex_code => 'GL#'
1520 ,p_id_flex_num => p_new_transaction_coa_id
1521 ,p_qualifier_segment => l_detail_condition.value_flexfield_segment_code);
1522
1523 IF l_con_v_flexfield_segment_code is null THEN
1524 l_con_v_flexfield_segment_name := xla_flex_pkg.get_qualifier_name
1525 (p_application_id => 101
1526 ,p_id_flex_code => 'GL#'
1527 ,p_qualifier_segment => l_detail_condition.value_flexfield_segment_code);
1528
1529 p_message := 'XLA_AB_TRX_COA_NO_QUAL';
1530 p_token_1 := 'QUALIFIER_NAME';
1531 p_value_1 := l_con_v_flexfield_segment_name;
1532 l_return := FALSE;
1533
1534 END IF;
1535 END IF;
1536 END IF;
1537 END IF;
1538 END IF;
1539
1540 END LOOP;
1541 CLOSE c_detail_conditions;
1542 END IF;
1543 END LOOP;
1544 CLOSE c_seg_rule_details;
1545 END IF;
1546
1547 xla_utility_pkg.trace('< xla_seg_rules_pkg.check_copy_seg_rule_details' , 10);
1548
1549 return l_return;
1550
1551 EXCEPTION
1552 WHEN xla_exceptions_pkg.application_exception THEN
1553 IF c_detail_conditions%ISOPEN THEN
1554 CLOSE c_detail_conditions;
1555 END IF;
1556 IF c_seg_rule_details%ISOPEN THEN
1557 CLOSE c_seg_rule_details;
1558 END IF;
1559 RAISE;
1560 WHEN OTHERS THEN
1561 IF c_detail_conditions%ISOPEN THEN
1562 CLOSE c_detail_conditions;
1563 END IF;
1564 IF c_seg_rule_details%ISOPEN THEN
1565 CLOSE c_seg_rule_details;
1566 END IF;
1567 xla_exceptions_pkg.raise_message
1568 (p_location => 'xla_seg_rules_pkg.check_copy_seg_rule_details');
1569
1570 END check_copy_seg_rule_details;
1571
1572 /*======================================================================+
1573 | |
1574 | Public Function |
1575 | |
1576 | rule_in_use_by_adr |
1577 | |
1578 | Checks if the segment rule is used by another ADR |
1579 | |
1580 +======================================================================*/
1581 FUNCTION rule_in_use_by_adr
1582 (p_event IN VARCHAR2
1583 ,p_application_id IN NUMBER
1584 ,p_amb_context_code IN VARCHAR2
1585 ,p_segment_rule_type_code IN VARCHAR2
1586 ,p_segment_rule_code IN VARCHAR2
1587 ,p_parent_seg_rule_appl_name IN OUT NOCOPY VARCHAR2
1588 ,p_parent_segment_rule_type IN OUT NOCOPY VARCHAR2
1589 ,p_parent_segment_rule_name IN OUT NOCOPY VARCHAR2)
1590 RETURN BOOLEAN
1591 IS
1592
1593 CURSOR c_adr_exist
1594 IS
1595 SELECT application_id
1596 ,amb_context_code
1597 ,segment_rule_type_code
1598 ,segment_rule_code
1599 FROM xla_seg_rule_details
1600 WHERE amb_context_code = p_amb_context_code
1601 AND value_segment_rule_appl_id = p_application_id
1602 AND value_segment_rule_type_code = p_segment_rule_type_code
1603 AND value_segment_rule_code = p_segment_rule_code;
1604
1605 l_adr c_adr_exist%rowtype;
1606 l_application_name varchar2(240);
1607 l_segment_rule_type varchar2(80);
1608 l_segment_rule_name varchar2(80);
1609
1610 l_return BOOLEAN;
1611
1612
1613 BEGIN
1614
1615 OPEN c_adr_exist;
1616 FETCH c_adr_exist
1617 INTO l_adr;
1618 IF c_adr_exist%found then
1619
1620 xla_validations_pkg.get_segment_rule_info
1621 (p_application_id => l_adr.application_id
1622 ,p_amb_context_code => l_adr.amb_context_code
1623 ,p_segment_rule_type_code => l_adr.segment_rule_type_code
1624 ,p_segment_rule_code => l_adr.segment_rule_code
1625 ,p_application_name => l_application_name
1626 ,p_segment_rule_name => l_segment_rule_name
1627 ,p_segment_rule_type => l_segment_rule_type);
1628 l_return := TRUE;
1629 ELSE
1630 l_return := FALSE;
1631 END IF;
1632 CLOSE c_adr_exist;
1633
1634 p_parent_seg_rule_appl_name := l_application_name;
1635 p_parent_segment_rule_name := l_segment_rule_name;
1636 p_parent_segment_rule_type := l_segment_rule_type;
1637
1638 return l_return;
1639
1640 EXCEPTION
1641 WHEN xla_exceptions_pkg.application_exception THEN
1642 RAISE;
1643 WHEN OTHERS THEN
1644 xla_exceptions_pkg.raise_message
1645 (p_location => 'xla_seg_rules_pkg.rule_in_use_by_adr');
1646
1647 END rule_in_use_by_adr;
1648
1649 /*======================================================================+
1650 | |
1651 | Public Function |
1652 | |
1653 | parent_seg_rule_is_locked |
1654 | |
1655 | Checks if the segment rule is used by a locked ADR |
1656 | |
1657 +======================================================================*/
1658 FUNCTION parent_seg_rule_is_locked
1659 (p_application_id IN NUMBER
1660 ,p_amb_context_code IN VARCHAR2
1661 ,p_segment_rule_type_code IN VARCHAR2
1662 ,p_segment_rule_code IN VARCHAR2)
1663 RETURN BOOLEAN
1664 IS
1665
1666 CURSOR c_parent_seg_rules
1667 IS
1668 SELECT application_id
1669 ,amb_context_code
1670 ,segment_rule_type_code
1671 ,segment_rule_code
1672 FROM xla_seg_rule_details s
1673 WHERE value_segment_rule_appl_id = p_application_id
1674 AND value_segment_rule_type_code = p_segment_rule_type_code
1675 AND value_segment_rule_code = p_segment_rule_code;
1676
1677 l_parent_seg_rules c_parent_seg_rules%rowtype;
1678
1679 l_return BOOLEAN := FALSE;
1680
1681 BEGIN
1682
1683 OPEN c_parent_seg_rules;
1684 LOOP
1685 FETCH c_parent_seg_rules
1686 INTO l_parent_seg_rules;
1687 EXIT WHEN c_parent_seg_rules%notfound or l_return = TRUE;
1688
1689 IF seg_rule_is_locked
1690 (p_application_id => l_parent_seg_rules.application_id
1691 ,p_amb_context_code => l_parent_seg_rules.amb_context_code
1692 ,p_segment_rule_type_code => l_parent_seg_rules.segment_rule_type_code
1693 ,p_segment_rule_code => l_parent_seg_rules.segment_rule_code) THEN
1694
1695 l_return := TRUE;
1696
1697 END IF;
1698 END LOOP;
1699 CLOSE c_parent_seg_rules;
1700
1701 return l_return;
1702
1703 EXCEPTION
1704 WHEN xla_exceptions_pkg.application_exception THEN
1705 RAISE;
1706 WHEN OTHERS THEN
1707 xla_exceptions_pkg.raise_message
1708 (p_location => 'xla_seg_rules_pkg.parent_seg_rule_is_locked');
1709
1710 END parent_seg_rule_is_locked;
1711
1712
1713 END xla_seg_rules_pkg;