eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
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;