[Home] [Help]
PACKAGE BODY: APPS.XLA_FLEX_PKG
Source
1 PACKAGE BODY xla_flex_pkg AS
2 /* $Header: xlacmflx.pkb 120.18 2005/02/26 02:06:40 weshen ship $ */
3 /*======================================================================+
4 | Copyright (c) 2001-2002 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +=======================================================================+
8 | PACKAGE NAME |
9 | xla_flex_pkg |
10 | |
11 | DESCRIPTION |
12 | XLA Flex Package |
13 | |
14 | HISTORY |
15 | 01-May-01 Dimple Shah Created |
16 | 18-Jun-01 P. Labrevois Added cache |
17 | Added table value set support |
18 | 23-May-03 Shishir Joshi Removed message number from the message |
19 | name |
20 | |
21 +======================================================================*/
22
23 --
24 -- Flex value set cache
25 --
26 g_flex_value_set_name VARCHAR2(80);
27 g_flex_value_set_id INTEGER;
28
29 --
30 -- Coa cache
31 --
32 g_coa_application_id INTEGER;
33 g_coa_flex_code VARCHAR2(4);
34 g_coa_id INTEGER;
35 g_coa_name VARCHAR2(80);
36
37 --
38 -- Flex value meaning cache
39 --
40 g_meaning_flex_value_set_id INTEGER;
41 g_meaning_flex_value VARCHAR2(4000);
42 g_meaning_meaning VARCHAR2(4000);
43
44 /*======================================================================+
45 | |
46 | Public Function |
47 | |
48 | get_value_set_name |
49 | |
50 | Get the value set name for the value set id |
51 | |
52 +======================================================================*/
53 FUNCTION get_value_set_name
54 (p_flex_value_set_id IN INTEGER)
55 RETURN VARCHAR2
56
57 IS
58
59 BEGIN
60 xla_utility_pkg.trace('> xla_flex_pkg.get_value_set_name' , 20);
61
62 xla_utility_pkg.trace('Value set id = '||p_flex_value_set_id , 40);
63
64 IF p_flex_value_set_id = g_flex_value_set_id THEN
65
66 NULL;
67
68 ELSE
69
70 SELECT flex_value_set_name
71 INTO g_flex_value_set_name
72 FROM fnd_flex_value_sets
73 WHERE flex_value_set_id = p_flex_value_set_id
74 ;
75
76 g_flex_value_set_id := p_flex_value_set_id;
77 END IF;
78
79 xla_utility_pkg.trace('Value set name = '||g_flex_value_set_name , 40);
80
81 xla_utility_pkg.trace('< xla_flex_pkg.get_value_set_name' , 20);
82
83 RETURN g_flex_value_set_name;
84
85 EXCEPTION
86 WHEN xla_exceptions_pkg.application_exception THEN
87 RAISE;
88 WHEN OTHERS THEN
89 xla_exceptions_pkg.raise_message
90 (p_location => 'xla_flex_pkg.get_value_set_name');
91 END get_value_set_name;
92
93
94 /*======================================================================+
95 | |
96 | Public Function |
97 | |
98 | get_chart_of_accounts_name |
99 | |
100 | Get the chart of accounts name for the chart of accounts id |
101 | |
102 +======================================================================*/
103 FUNCTION get_chart_of_accounts_name
104 (p_application_id IN INTEGER
105 ,p_flex_code IN VARCHAR2
106 ,p_chart_of_accounts_id IN INTEGER)
107 RETURN VARCHAR2
108 IS
109
110 l_chart_of_accounts_name VARCHAR2(80);
111
112 BEGIN
113 xla_utility_pkg.trace('> xla_flex_pkg.get_chart_of_accounts_name' , 20);
114
115 xla_utility_pkg.trace('Application id = '||p_application_id , 40);
116 xla_utility_pkg.trace('Flex code = '||p_flex_code , 40);
117 xla_utility_pkg.trace('Chart of accounts id = '||p_chart_of_accounts_id , 40);
118
119 IF (p_application_id = g_coa_application_id
120 AND p_flex_code = g_coa_flex_code
121 AND p_chart_of_accounts_id = g_coa_id) THEN
122
123 NULL;
124
125 ELSE
126
127 SELECT id_flex_structure_name
128 INTO g_coa_name
129 FROM fnd_id_flex_structures_v
130 WHERE application_id = p_application_id
131 AND id_flex_code = p_flex_code
132 AND id_flex_num = p_chart_of_accounts_id
133 ;
134
135 g_coa_application_id := p_application_id;
136 g_coa_flex_code := p_flex_code;
137 g_coa_id := p_chart_of_accounts_id;
138 END IF;
139
140 xla_utility_pkg.trace('Chart of accounts name = '||g_coa_name , 40);
141
142 xla_utility_pkg.trace('< xla_flex_pkg.get_chart_of_accounts_name' , 20);
143
144 RETURN g_coa_name;
145
146 EXCEPTION
147 WHEN xla_exceptions_pkg.application_exception THEN
148 RAISE;
149 WHEN OTHERS THEN
150 xla_exceptions_pkg.raise_message
151 (p_location => 'xla_flex_pkg.get_chart_of_accounts_name');
152 END get_chart_of_accounts_name;
153
154
155 /*======================================================================+
156 | |
157 | Public Function |
158 | |
159 | get_flexfield_segment_name |
160 | |
161 | Get the segment name for the segment code |
162 | |
163 +======================================================================*/
164 FUNCTION get_flexfield_segment_name
165 (p_application_id IN INTEGER
166 ,p_flex_code IN VARCHAR2
167 ,p_chart_of_accounts_id IN INTEGER
168 ,p_flexfield_segment_code IN VARCHAR2)
169
170 RETURN VARCHAR2
171 IS
172
173 l_flexfield_segment_name VARCHAR2(80);
174
175 CURSOR c_segment_name
176 IS
177 SELECT segment_name
178 FROM fnd_id_flex_segments_vl
179 WHERE application_id = p_application_id
180 AND id_flex_code = p_flex_code
181 AND id_flex_num = p_chart_of_accounts_id
182 AND application_column_name = p_flexfield_segment_code
183 ;
184
185 BEGIN
186
187 xla_utility_pkg.trace('> xla_flex_pkg.get_flexfield_segment_name' , 20);
188
189 xla_utility_pkg.trace('Application id = '||p_application_id , 40);
190 xla_utility_pkg.trace('Flex code = '||p_flex_code , 40);
191 xla_utility_pkg.trace('Chart of accounts id = '||p_chart_of_accounts_id , 40);
192 xla_utility_pkg.trace('Flexfield Segment Code = '||p_flexfield_segment_code , 40);
193
194 OPEN c_segment_name;
195
196 FETCH c_segment_name
197 INTO l_flexfield_segment_name;
198
199 CLOSE c_segment_name;
200
201 xla_utility_pkg.trace('Segment name = '||l_flexfield_segment_name , 40);
202
203 xla_utility_pkg.trace('< xla_flex_pkg.get_flexfield_segment_name' , 20);
204
205 RETURN l_flexfield_segment_name;
206
207 EXCEPTION
208 WHEN xla_exceptions_pkg.application_exception THEN
209 IF c_segment_name%ISOPEN THEN
210 CLOSE c_segment_name;
211 END IF;
212 RAISE;
213 WHEN OTHERS THEN
214 IF c_segment_name%ISOPEN THEN
215 CLOSE c_segment_name;
216 END IF;
217 xla_exceptions_pkg.raise_message
218 (p_location => 'xla_flex_pkg.get_flexfield_segment_name');
219 END get_flexfield_segment_name;
220
221
222 /*======================================================================+
223 | |
224 | Public Function |
225 | |
226 | get_flex_value_meaning |
227 | |
228 | Get the meaning for the flex_value |
229 | |
230 +======================================================================*/
231 FUNCTION get_flex_value_meaning
232 (p_flex_value_set_id IN INTEGER
233 ,p_flex_value IN VARCHAR2)
234
235 RETURN VARCHAR2
236 IS
237
238 l_validation_type VARCHAR2(1);
239 l_statement VARCHAR2(4000);
240 l_statement_run VARCHAR2(4000);
241 l_additional_where_clause VARCHAR2(4000);
242 l_number NUMBER;
243
244 BEGIN
245 xla_utility_pkg.trace('> xla_flex_pkg.get_flex_value_meaning' , 20);
246
247 xla_utility_pkg.trace('Flex_value_set_id = '||p_flex_value_set_id , 40);
248 xla_utility_pkg.trace('Flex_value = '||p_flex_value , 40);
249
250 IF (p_flex_value_set_id = g_meaning_flex_value_set_id
251 AND p_flex_value = g_meaning_flex_value) THEN
252
253 NULL;
254
255 ELSE
256
257 SELECT validation_type
258 INTO l_validation_type
259 FROM fnd_flex_value_sets
260 WHERE flex_value_set_id = p_flex_value_set_id;
261
262 IF l_validation_type in ('I','X') THEN
263
264 --
265 -- Independant value set
266 --
267 SELECT flex_value_meaning
268 INTO g_meaning_meaning
269 FROM fnd_flex_values_vl
270 WHERE flex_value_set_id = p_flex_value_set_id
271 AND flex_value = p_flex_value
272 ;
273
274 g_meaning_flex_value_set_id := p_flex_value_set_id;
275 g_meaning_flex_value := p_flex_value;
276
277 ELSIF l_validation_type = 'F' THEN
278
279 IF xla_flex_pkg.id_column_is_null(p_flex_value_set_id) THEN
280
281 g_meaning_meaning := p_flex_value;
282
283 ELSE
284
285 SELECT additional_where_clause
286 INTO l_additional_where_clause
287 FROM fnd_flex_validation_tables
288 WHERE flex_value_set_id = p_flex_value_set_id;
289
290 IF l_additional_where_clause is not null THEN
291
292 l_additional_where_clause :=
293 Ltrim(l_additional_where_clause);
294
295 l_number := Instr(Upper(l_additional_where_clause),'ORDER BY ');
296 IF (l_number = 1) THEN
297 l_additional_where_clause := null;
298 ELSE
299 l_number := Instr(Upper(l_additional_where_clause),'WHERE ');
300
301 IF (l_number = 1) THEN
302 l_additional_where_clause :=
303 Substr(l_additional_where_clause,7);
304 ELSE
305 l_additional_where_clause := l_additional_where_clause;
306 END IF;
307 END IF;
308 END IF;
309
310 IF l_additional_where_clause is null THEN
311
312 --
313 -- Table value set
314 --
315 SELECT 'SELECT '||value_column_name
316 || xla_environment_pkg.g_chr_newline
317 || 'FROM '||application_table_name
318 || xla_environment_pkg.g_chr_newline
319 || 'WHERE '||id_column_name || ' = :1'
320 INTO l_statement
321 FROM fnd_flex_validation_tables
322 WHERE flex_value_set_id = p_flex_value_set_id
323 ;
324
325 ELSE
326 --
327 -- Table value set
328 --
329 SELECT 'SELECT '||value_column_name
330 || xla_environment_pkg.g_chr_newline
331 || 'FROM '||application_table_name
332 || xla_environment_pkg.g_chr_newline
333 || 'WHERE '||id_column_name || ' = :1'
334 || ' AND '||l_additional_where_clause
335 INTO l_statement
336 FROM fnd_flex_validation_tables
337 WHERE flex_value_set_id = p_flex_value_set_id
338 ;
339
340
341 END IF;
342
343 xla_utility_pkg.trace('Statement = '||l_statement , 50);
344
345 --
346 -- Bug912223 with 8i
347 --
348 l_statement_run := l_statement;
349
350 EXECUTE IMMEDIATE l_statement_run
351 INTO g_meaning_meaning
352 USING p_flex_value;
353
354 g_meaning_flex_value_set_id := p_flex_value_set_id;
355 g_meaning_flex_value := p_flex_value;
356
357 END IF;
358
359
360
361 ELSE
362 xla_exceptions_pkg.raise_message
363 ('XLA' ,'XLA_COMMON_ERROR'
364 ,'ERROR' ,'Unsupported value set'
365 ,'LOCATION' ,'xla_flex_pkg.get_flex_value_meaning');
366 END IF;
367 END IF;
368
369 xla_utility_pkg.trace('Flex_value_meaning = '||g_meaning_meaning , 40);
370
371 xla_utility_pkg.trace('< xla_flex_pkg.get_flex_value_meaning' , 20);
372
373 RETURN g_meaning_meaning;
374
375 EXCEPTION
376 WHEN xla_exceptions_pkg.application_exception THEN
377 RAISE;
378 WHEN OTHERS THEN
379 xla_exceptions_pkg.raise_message
380 (p_location => 'xla_flex_pkg.get_flex_value_meaning');
381 END get_flex_value_meaning;
382
383
384 /*======================================================================+
385 | |
386 | Public Function |
387 | |
388 | get_flexfield_segment_info |
389 | |
390 | Get the segment name and display segment num for the segment code |
391 | |
392 +======================================================================*/
393 FUNCTION get_flexfield_segment_info
394 (p_application_id IN INTEGER
395 ,p_flex_code IN VARCHAR2
396 ,p_chart_of_accounts_id IN INTEGER
397 ,p_flexfield_segment_code IN VARCHAR2
398 ,p_flexfield_segment_name IN OUT NOCOPY VARCHAR2
399 ,p_flexfield_segment_num IN OUT NOCOPY INTEGER)
400
401 RETURN BOOLEAN
402 IS
403
404 this_segment_num NUMBER(38);
405
406 CURSOR c_segment_info
407 IS
408 SELECT segment_name
409 ,segment_num
410 FROM fnd_id_flex_segments_vl
411 WHERE application_id = p_application_id
412 AND id_flex_code = p_flex_code
413 AND id_flex_num = p_chart_of_accounts_id
414 AND application_column_name = p_flexfield_segment_code
415 AND enabled_flag = 'Y'
416 ;
417
418
419 CURSOR c_segment_num
420 IS
421 SELECT count(segment_num)
422 FROM fnd_id_flex_segments
423 WHERE application_id = p_application_id
424 AND id_flex_code = p_flex_code
425 AND id_flex_num = p_chart_of_accounts_id
426 AND enabled_flag = 'Y'
427 AND display_flag = 'Y'
428 AND segment_num <= this_segment_num
429 ;
430
431 BEGIN
432
433 xla_utility_pkg.trace('> xla_flex_pkg.get_flexfield_segment_info' , 20);
434
435 xla_utility_pkg.trace('Application id = '||p_application_id , 40);
436 xla_utility_pkg.trace('Flex code = '||p_flex_code , 40);
437 xla_utility_pkg.trace('Chart of accounts id = '||p_chart_of_accounts_id , 40);
438 xla_utility_pkg.trace('Flexfield Segment Code = '||p_flexfield_segment_code , 40);
439
440 OPEN c_segment_info;
441
442 FETCH c_segment_info
443 INTO p_flexfield_segment_name
444 ,this_segment_num;
445
446 CLOSE c_segment_info;
447
448 OPEN c_segment_num;
449
450 FETCH c_segment_num
451 INTO p_flexfield_segment_num;
452
453 CLOSE c_segment_num;
454
455 xla_utility_pkg.trace('Segment name = '||p_flexfield_segment_name , 40);
456 xla_utility_pkg.trace('Segment num = '||p_flexfield_segment_num , 40);
457
458 xla_utility_pkg.trace('< xla_flex_pkg.get_flexfield_segment_info' , 20);
459
460 RETURN TRUE;
461
462 EXCEPTION
463 WHEN xla_exceptions_pkg.application_exception THEN
464 IF c_segment_info%ISOPEN THEN
465 CLOSE c_segment_info;
466 END IF;
467 RAISE;
468 WHEN OTHERS THEN
469 IF c_segment_info%ISOPEN THEN
470 CLOSE c_segment_info;
471 END IF;
472 xla_exceptions_pkg.raise_message
473 (p_location => 'xla_flex_pkg.get_flexfield_segment_info');
474 END get_flexfield_segment_info;
475
476 /*======================================================================+
477 | |
478 | Public Procedure |
479 | |
480 | get_table_vset_select |
481 | |
482 | Get the select for a table validated valueset |
483 | |
484 +======================================================================*/
485 PROCEDURE get_table_vset_select
486 (p_flex_value_set_id IN INTEGER
487 ,p_select OUT NOCOPY VARCHAR2
488 ,p_mapping_code OUT NOCOPY VARCHAR2
489 ,p_success OUT NOCOPY NUMBER)
490
491 IS
492
493
494 BEGIN
495
496 xla_utility_pkg.trace('> xla_flex_pkg.get_table_vset_select' , 20);
497
498 xla_utility_pkg.trace('flex_value_set_id = '||p_flex_value_set_id , 40);
499
500 fnd_flex_val_api.get_table_vset_select
501 (p_value_set_id => p_flex_value_set_id
502 ,x_select => p_select
503 ,x_mapping_code => p_mapping_code
504 ,x_success => p_success);
505
506
507 xla_utility_pkg.trace('select = '||p_select , 40);
508 xla_utility_pkg.trace('mapping code = '||p_mapping_code , 40);
509 xla_utility_pkg.trace('success = '||p_success , 40);
510
511 xla_utility_pkg.trace('< xla_flex_pkg.get_table_vset_select' , 20);
512
513 EXCEPTION
514 WHEN xla_exceptions_pkg.application_exception THEN
515 RAISE;
516 WHEN OTHERS THEN
517 xla_exceptions_pkg.raise_message
518 (p_location => 'xla_flex_pkg.get_table_vset_select');
519 END get_table_vset_select;
520
521 /*======================================================================+
522 | |
523 | Public Function |
524 | |
525 | segment_qualifier_is_enabled |
526 | |
527 | Returns true if the segment qualifer is enabled for the coa specified |
528 | |
529 +======================================================================*/
530 FUNCTION segment_qualifier_is_enabled
531 (p_application_id IN INTEGER
532 ,p_flex_code IN VARCHAR2
533 ,p_chart_of_accounts_id IN INTEGER
534 ,p_flexfield_segment_code IN VARCHAR2)
535
536 RETURN BOOLEAN
537 IS
538
539 l_return BOOLEAN;
540 l_exist VARCHAR2(1);
541
542 CURSOR c_qualifier
543 IS
544 SELECT 'x'
545 FROM fnd_segment_attribute_values
546 WHERE application_id = p_application_id
547 AND id_flex_code = p_flex_code
548 AND id_flex_num = p_chart_of_accounts_id
549 AND segment_attribute_type = p_flexfield_segment_code
550 AND attribute_value = 'Y'
551 ;
552
553 BEGIN
554
555 xla_utility_pkg.trace('> xla_flex_pkg.segment_qualifier_is_enabled' , 20);
556
557 xla_utility_pkg.trace('Application id = '||p_application_id , 40);
558 xla_utility_pkg.trace('Flex code = '||p_flex_code , 40);
559 xla_utility_pkg.trace('Chart of accounts id = '||p_chart_of_accounts_id , 40);
560 xla_utility_pkg.trace('Flexfield segment code = '||p_flexfield_segment_code , 40);
561
562 IF p_flexfield_segment_code in ('GL_BALANCING','GL_ACCOUNT','GL_INTERCOMPANY',
563 'GL_MANAGEMENT','FA_COST_CTR') THEN
564 OPEN c_qualifier;
565 FETCH c_qualifier
566 INTO l_exist;
567 IF c_qualifier%found THEN
568 l_return := TRUE;
569 ELSE
570 l_return := FALSE;
571 END IF;
572 CLOSE c_qualifier;
573 ELSE
574 l_return := TRUE;
575 END IF;
576
577 xla_utility_pkg.trace('< xla_flex_pkg.segment_qualifier_is_enabled' , 20);
578
579 RETURN l_return;
580
581 EXCEPTION
582 WHEN xla_exceptions_pkg.application_exception THEN
583 IF c_qualifier%ISOPEN THEN
584 CLOSE c_qualifier;
585 END IF;
586 RAISE;
587 WHEN OTHERS THEN
588 IF c_qualifier%ISOPEN THEN
589 CLOSE c_qualifier;
590 END IF;
591 xla_exceptions_pkg.raise_message
592 (p_location => 'xla_flex_pkg.segment_qualifier_is_enabled');
593 END segment_qualifier_is_enabled;
594
595 /*======================================================================+
596 | |
597 | Public Function |
598 | |
599 | id_column_is_null |
600 | |
601 | Returns true if the id column is null |
602 | |
603 +======================================================================*/
604 FUNCTION id_column_is_null
605 (p_flex_value_set_id IN INTEGER)
606 RETURN BOOLEAN
607 IS
608
609 l_id_column_name varchar2(240);
610 l_return boolean;
611
612 BEGIN
613 xla_utility_pkg.trace('> xla_flex_pkg.id_column_is_null' , 20);
614
615 xla_utility_pkg.trace('Flex_value_set_id = '||p_flex_value_set_id , 40);
616
617 SELECT id_column_name
618 INTO l_id_column_name
619 FROM fnd_flex_validation_tables
620 WHERE flex_value_set_id = p_flex_value_set_id;
621
622 IF l_id_column_name is null THEN
623 l_return := TRUE;
624 ELSE
625 l_return := FALSE;
626 END IF;
627
628 xla_utility_pkg.trace('< xla_flex_pkg.id_column_is_null' , 20);
629
630 RETURN l_return;
631
632 EXCEPTION
633 WHEN xla_exceptions_pkg.application_exception THEN
634 RAISE;
635 WHEN OTHERS THEN
636 xla_exceptions_pkg.raise_message
637 (p_location => 'xla_flex_pkg.id_column_is_null');
638 END id_column_is_null;
639
640 /*======================================================================+
641 | |
642 | Public Function |
643 | |
644 | meaning_column_is_null |
645 | |
646 | Returns true if the meaning column is null |
647 | |
648 +======================================================================*/
649 FUNCTION meaning_column_is_null
650 (p_flex_value_set_id IN INTEGER)
651 RETURN BOOLEAN
652 IS
653
654 l_meaning_column_name varchar2(240);
655 l_return boolean;
656
657 BEGIN
658 xla_utility_pkg.trace('> xla_flex_pkg.meaning_column_is_null' , 20);
659
660 xla_utility_pkg.trace('Flex_value_set_id = '||p_flex_value_set_id , 40);
661
662 SELECT meaning_column_name
663 INTO l_meaning_column_name
664 FROM fnd_flex_validation_tables
665 WHERE flex_value_set_id = p_flex_value_set_id;
666
667 IF l_meaning_column_name is null THEN
668 l_return := TRUE;
669 ELSE
670 l_return := FALSE;
671 END IF;
672
673 xla_utility_pkg.trace('< xla_flex_pkg.meaning_column_is_null' , 20);
674
675 RETURN l_return;
676
677 EXCEPTION
678 WHEN xla_exceptions_pkg.application_exception THEN
679 RAISE;
680 WHEN OTHERS THEN
681 xla_exceptions_pkg.raise_message
682 (p_location => 'xla_flex_pkg.meaning_column_is_null');
683 END meaning_column_is_null;
684
685 /*======================================================================+
686 | |
687 | Public Function |
688 | |
689 | chk_additional_where_clause |
690 | |
691 | Returns true if the additional where caluse does not have $FLEX$ |
692 | |
693 +======================================================================*/
694 FUNCTION chk_additional_where_clause
695 (p_flex_value_set_id IN INTEGER)
696 RETURN VARCHAR2
697 IS
698
699 l_additional_where_clause varchar2(4000);
700 l_return varchar2(30);
701
702 BEGIN
703 xla_utility_pkg.trace('> xla_flex_pkg.chk_additional_where_clause' , 20);
704
705 xla_utility_pkg.trace('Flex_value_set_id = '||p_flex_value_set_id , 40);
706
707 BEGIN
708 SELECT additional_where_clause
709 INTO l_additional_where_clause
710 FROM fnd_flex_validation_tables
711 WHERE flex_value_set_id = p_flex_value_set_id;
712
713 IF l_additional_where_clause is not null THEN
714
715 IF instr(l_additional_where_clause, '$FLEX$',1,1) > 0 THEN
716 l_return := 'FALSE';
717 ELSE
718 l_return := 'TRUE';
719 END IF;
720 ELSE
721 l_return := 'TRUE';
722 END IF;
723 EXCEPTION
724 WHEN NO_DATA_FOUND THEN
725 l_return := 'TRUE';
726 WHEN VALUE_ERROR THEN
727 l_return := 'FALSE';
728 END;
729
730 xla_utility_pkg.trace('< xla_flex_pkg.chk_additional_where_clause' , 20);
731
732 RETURN l_return;
733
734 EXCEPTION
735 WHEN xla_exceptions_pkg.application_exception THEN
736 RAISE;
737 WHEN OTHERS THEN
738 xla_exceptions_pkg.raise_message
739 (p_location => 'xla_flex_pkg.chk_additional_where_clause');
740 END chk_additional_where_clause;
741
742 /*======================================================================+
743 | |
744 | Public Function |
745 | |
746 | get_qualifier_segment |
747 | |
748 | Returns the segment for the qualifier specified |
749 | |
750 +======================================================================*/
751 FUNCTION get_qualifier_segment
752 (p_application_id IN INTEGER
753 ,p_id_flex_code IN VARCHAR2
754 ,p_id_flex_num IN INTEGER
755 ,p_qualifier_segment IN VARCHAR2)
756 RETURN VARCHAR2
757 IS
758
759 l_application_column_name VARCHAR2(30);
760
761 CURSOR c_segment
762 IS
763 SELECT application_column_name
764 FROM fnd_segment_attribute_values
765 WHERE application_id = p_application_id
766 AND id_flex_code = p_id_flex_code
767 AND id_flex_num = p_id_flex_num
768 AND segment_attribute_type = p_qualifier_segment
769 AND attribute_value = 'Y'
770 ;
771
772 BEGIN
773
774 xla_utility_pkg.trace('> xla_flex_pkg.get_qualifier_segment' , 20);
775
776 xla_utility_pkg.trace('Application id = '||p_application_id , 40);
777 xla_utility_pkg.trace('Flex code = '||p_id_flex_code , 40);
778 xla_utility_pkg.trace('Chart of accounts id = '||p_id_flex_num , 40);
779 xla_utility_pkg.trace('Flexfield segment code = '||p_qualifier_segment , 40);
780
781 OPEN c_segment;
782 FETCH c_segment
783 INTO l_application_column_name;
784 CLOSE c_segment;
785
786 xla_utility_pkg.trace('< xla_flex_pkg.get_qualifier_segment' , 20);
787
788 RETURN l_application_column_name;
789
790 EXCEPTION
791 WHEN xla_exceptions_pkg.application_exception THEN
792 IF c_segment%ISOPEN THEN
793 CLOSE c_segment;
794 END IF;
795 RAISE;
796 WHEN OTHERS THEN
797 IF c_segment%ISOPEN THEN
798 CLOSE c_segment;
799 END IF;
800 xla_exceptions_pkg.raise_message
801 (p_location => 'xla_flex_pkg.get_qualifier_segment');
802 END get_qualifier_segment;
803
804 /*======================================================================+
805 | |
806 | Public Function |
807 | |
808 | get_segment_qualifier |
809 | |
810 | Returns the qualifier for the segment specified |
811 | |
812 +======================================================================*/
813 FUNCTION get_segment_qualifier
814 (p_application_id IN INTEGER
815 ,p_id_flex_code IN VARCHAR2
816 ,p_id_flex_num IN INTEGER
817 ,p_segment_code IN VARCHAR2)
818 RETURN VARCHAR2
819 IS
820
821 l_segment_attribute_type VARCHAR2(30);
822
823 CURSOR c_qualifier
824 IS
825 SELECT segment_attribute_type
826 FROM fnd_segment_attribute_values
827 WHERE application_id = p_application_id
828 AND id_flex_code = p_id_flex_code
829 AND id_flex_num = p_id_flex_num
830 AND application_column_name = p_segment_code
831 AND attribute_value = 'Y'
832 ;
833
834 BEGIN
835
836 xla_utility_pkg.trace('> xla_flex_pkg.get_segment_qualifier' , 20);
837
838 xla_utility_pkg.trace('Application id = '||p_application_id , 40);
839 xla_utility_pkg.trace('Flex code = '||p_id_flex_code , 40);
840 xla_utility_pkg.trace('Structure id = '||p_id_flex_num , 40);
841 xla_utility_pkg.trace('Flexfield segment code = '||p_segment_code , 40);
842
843 OPEN c_qualifier;
844 FETCH c_qualifier
845 INTO l_segment_attribute_type;
846 CLOSE c_qualifier;
847
848 xla_utility_pkg.trace('< xla_flex_pkg.get_segment_qualifier' , 20);
849
850 IF l_segment_attribute_type = null THEN
851 l_segment_attribute_type := 'X';
852 END IF;
853
854 RETURN l_segment_attribute_type;
855
856 EXCEPTION
857 WHEN xla_exceptions_pkg.application_exception THEN
858 IF c_qualifier%ISOPEN THEN
859 CLOSE c_qualifier;
860 END IF;
861 RAISE;
862 WHEN OTHERS THEN
863 IF c_qualifier%ISOPEN THEN
864 CLOSE c_qualifier;
865 END IF;
866 xla_exceptions_pkg.raise_message
867 (p_location => 'xla_flex_pkg.get_segment_qualifier');
868 END get_segment_qualifier;
869
870 /*======================================================================+
871 | |
872 | Public Function |
873 | |
874 | get_segment_valueset |
875 | |
876 | Returns the valuset for the segment specified |
877 | |
878 +======================================================================*/
879 FUNCTION get_segment_valueset
880 (p_application_id IN INTEGER
881 ,p_id_flex_code IN VARCHAR2
882 ,p_id_flex_num IN INTEGER
883 ,p_segment_code IN VARCHAR2)
884 RETURN NUMBER
885 IS
886
887 l_flex_value_set_id NUMBER(15);
888
889 CURSOR c_valueset
890 IS
891 SELECT flex_value_set_id
892 FROM fnd_id_flex_segments
893 WHERE application_id = p_application_id
894 AND id_flex_code = p_id_flex_code
895 AND id_flex_num = p_id_flex_num
896 AND application_column_name = p_segment_code
897 ;
898
899 BEGIN
900
901 xla_utility_pkg.trace('> xla_flex_pkg.get_segment_valueset' , 20);
902
903 xla_utility_pkg.trace('Application id = '||p_application_id , 40);
904 xla_utility_pkg.trace('Flex code = '||p_id_flex_code , 40);
905 xla_utility_pkg.trace('Structure id = '||p_id_flex_num , 40);
906 xla_utility_pkg.trace('Flexfield segment code = '||p_segment_code , 40);
907
908 OPEN c_valueset;
909 FETCH c_valueset
910 INTO l_flex_value_set_id;
911 CLOSE c_valueset;
912
913 xla_utility_pkg.trace('< xla_flex_pkg.get_segment_valueset' , 20);
914
915 IF l_flex_value_set_id = null THEN
916 l_flex_value_set_id := -99;
917 END IF;
918
919 RETURN l_flex_value_set_id;
920
921 EXCEPTION
922 WHEN xla_exceptions_pkg.application_exception THEN
923 IF c_valueset%ISOPEN THEN
924 CLOSE c_valueset;
925 END IF;
926 RAISE;
927 WHEN OTHERS THEN
928 IF c_valueset%ISOPEN THEN
929 CLOSE c_valueset;
930 END IF;
931 xla_exceptions_pkg.raise_message
932 (p_location => 'xla_flex_pkg.get_segment_valueset');
933 END get_segment_valueset;
934
935 /*======================================================================+
936 | |
937 | Public Function |
938 | |
939 | get_qualifier_name |
940 | |
941 | Returns the name for the flexfield qualifier |
942 | |
943 +======================================================================*/
944 FUNCTION get_qualifier_name
945 (p_application_id IN INTEGER
946 ,p_id_flex_code IN VARCHAR2
947 ,p_qualifier_segment IN VARCHAR2)
948 RETURN VARCHAR2
949 IS
950 l_segment_prompt VARCHAR2(80);
951
952 CURSOR c_segment
953 IS
954 SELECT segment_prompt
955 FROM fnd_segment_attribute_types
956 WHERE application_id = p_application_id
957 AND id_flex_code = p_id_flex_code
958 AND segment_attribute_type = p_qualifier_segment;
959
960 BEGIN
961
962 xla_utility_pkg.trace('> xla_flex_pkg.get_qualifier_name' , 20);
963
964 xla_utility_pkg.trace('Application id = '||p_application_id , 40);
965 xla_utility_pkg.trace('Flex code = '||p_id_flex_code , 40);
966 xla_utility_pkg.trace('Flexfield segment code = '||p_qualifier_segment , 40);
967
968 OPEN c_segment;
969 FETCH c_segment
970 INTO l_segment_prompt;
971 CLOSE c_segment;
972
973 xla_utility_pkg.trace('< xla_flex_pkg.get_qualifier_name' , 20);
974
975 RETURN l_segment_prompt;
976
977 EXCEPTION
978 WHEN xla_exceptions_pkg.application_exception THEN
979 IF c_segment%ISOPEN THEN
980 CLOSE c_segment;
981 END IF;
982 RAISE;
983 WHEN OTHERS THEN
984 IF c_segment%ISOPEN THEN
985 CLOSE c_segment;
986 END IF;
987 xla_exceptions_pkg.raise_message
988 (p_location => 'xla_flex_pkg.get_qualifier_name');
989 END get_qualifier_name;
990
991 /*======================================================================+
992 | |
993 | Public Function |
994 | |
995 | get_flexfield_structure |
996 | |
997 | Returns the flexfield structure for the key flexfields that support |
998 | single structure |
999 | |
1000 +======================================================================*/
1001 FUNCTION get_flexfield_structure
1002 (p_application_id IN INTEGER
1003 ,p_id_flex_code IN VARCHAR2)
1004 RETURN NUMBER
1005 IS
1006 l_id_flex_num NUMBER(15);
1007
1008 CURSOR c_struc
1009 IS
1010 SELECT id_flex_num
1011 FROM fnd_id_flex_structures_vl
1012 WHERE application_id = p_application_id
1013 AND id_flex_code = p_id_flex_code;
1014
1015 BEGIN
1016
1017 xla_utility_pkg.trace('> xla_flex_pkg.get_flexfield_structure' , 20);
1018
1019 xla_utility_pkg.trace('Application id = '||p_application_id , 40);
1020 xla_utility_pkg.trace('Flex code = '||p_id_flex_code , 40);
1021
1022 OPEN c_struc;
1023 FETCH c_struc
1024 INTO l_id_flex_num;
1025 CLOSE c_struc;
1026
1027 xla_utility_pkg.trace('< xla_flex_pkg.get_flexfield_structure' , 20);
1028
1029 RETURN l_id_flex_num;
1030
1031 EXCEPTION
1032 WHEN xla_exceptions_pkg.application_exception THEN
1033 IF c_struc%ISOPEN THEN
1034 CLOSE c_struc;
1035 END IF;
1036 RAISE;
1037 WHEN OTHERS THEN
1038 IF c_struc%ISOPEN THEN
1039 CLOSE c_struc;
1040 END IF;
1041 xla_exceptions_pkg.raise_message
1042 (p_location => 'xla_flex_pkg.get_flexfield_structure');
1043 END get_flexfield_structure;
1044
1045 END xla_flex_pkg;