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