[Home] [Help]
PACKAGE BODY: APPS.HR_FLEXFIELD_INFO
Source
1 Package Body hr_flexfield_info as
2 /* $Header: peffinfo.pkb 120.1 2005/09/23 17:03:39 svittal noship $ */
3 --
4 -- Constants.
5 --
6 c_newline constant varchar2(1) :=
7 '
8 ';
9 --
10 -- Package Variables
11 --
12 g_package varchar2(33) := ' hr_flexfield_info';
13 g_psegment_count number := 0;
14 g_app_col_placeholder VARCHAR2(200) := '&'||hr_api.g_varchar2||'.';
15 g_debug boolean := hr_utility.debug_enabled;
16 --
17 -- ----------------------------------------------------------------------------
18 -- |------------------------------< initialize >------------------------------|
19 -- ----------------------------------------------------------------------------
20 --
21 procedure initialize is
22 begin
23 g_psegment_count :=0;
24 end initialize;
25 --
26 -- ----------------------------------------------------------------------------
27 -- |------------------------< structure_column_name >-------------------------|
28 -- ----------------------------------------------------------------------------
29 --
30 procedure structure_column_name(
31 p_appl_short_name in VARCHAR2
32 ,p_flex_name IN VARCHAR2
33 ,p_column_name OUT NOCOPY VARCHAR2
34 ,p_column_name_prompt OUT NOCOPY VARCHAR2
35 ,p_dcontext_field OUT NOCOPY VARCHAR2
36 ,p_default_context_value OUT NOCOPY VARCHAR2) IS
37 --
38 l_flexfield fnd_dflex.dflex_r;
39 l_flexinfo fnd_dflex.dflex_dr;
40
41 BEGIN
42
43 --FND_DFLEX.GET_FLEXFIELD(appl_short_name=>p_appl_short_name,
44 -- flexfield_name=>p_flex_name,
45 -- flexfield=>l_flexfield,
46 -- flexinfo=>l_flexinfo);
47
48 select context_column_name,form_context_prompt,default_context_field_name,default_context_value
49 into p_column_name,p_column_name_prompt,p_dcontext_field,p_default_context_value
50 from fnd_descriptive_flexs_vl fdf
51 ,fnd_application fa
52 where descriptive_flexfield_name = p_flex_name
53 and fa.application_id = fdf.application_id
54 and fa.application_short_name = p_appl_short_name;
55
56 END structure_column_name;
57 --
58 -- ----------------------------------------------------------------------------
59 -- |--------------------------< get_ak_region_code >--------------------------|
60 -- ----------------------------------------------------------------------------
61 --
62 function get_ak_region_code(p_flex_type in VARCHAR2,
63 p_flexfield_name IN VARCHAR2,
64 p_context_code IN VARCHAR2,
65 p_segment_info IN hr_segments_info,
66 p_segment_number IN BINARY_INTEGER) return varchar2 is
67 --
68 -- Cursor for AK data
69 --
70 cursor c_ak_region(p_lookup_code in fnd_common_lookups.lookup_code%type) is
71 select meaning
72 from fnd_common_lookups
73 where upper(lookup_code) = upper(p_lookup_code)
74 and lookup_type = 'AK_WEB_REGION_CODES'
75 and application_id = 800
76 and enabled_flag = 'Y';
77
78 --
79 -- Local Variables
80 --
81 l_lookup_code fnd_common_lookups.lookup_code%TYPE;
82 l_ak_web_region_code fnd_common_lookups.meaning%TYPE;
83 l_proc varchar2(72);
84 --
85 begin
86 --
87
88 IF g_debug THEN
89 l_proc := g_package||'get_ak_region_code';
90 hr_utility.set_location('Entering:'|| l_proc, 10);
91 END IF;
92
93 --
94 -- Construct the right look up code for the AK region data based
95 -- on the flexfield name, the context code and the application
96 -- column name.
97 -- ** This should be stored in a table really, and not as a lookup
98 --
99 -- Confirmed that this lookup code does not use any translatable
100 -- information. Also, aware that there is a possibility of clashes
101 -- because of the substr calls.
102 l_lookup_code :=
103 gen_ak_web_region_code
104 (p_flex_type => p_flex_type,
105 p_flexfield => p_flexfield_name
106 ,p_context_or_id_flex_num => p_context_code
107 ,p_segment =>
108 p_segment_info.application_column_name(p_segment_number)
109 );
110
111 open c_ak_region(l_lookup_code);
112 fetch c_ak_region into l_ak_web_region_code;
113 if c_ak_region%notfound then
114 close c_ak_region;
115 l_ak_web_region_code := NULL;
116 else
117 close c_ak_region;
118 end if;
119
120 --
121
122 IF g_debug THEN
123 hr_utility.set_location('Leaving:'|| l_proc, 20);
124 END IF;
125
126 --
127 --return nvl(l_ak_web_region_code,'Not found!');
128 return l_ak_web_region_code;
129
130 exception
131 WHEN others then
132 return null;
133
134 end get_ak_region_code;
135 --
136 --
137 -- ----------------------------------------------------------------------------
138 -- |--------------------------< get_segment_parents >-------------------------|
139 -- ----------------------------------------------------------------------------
140 --
141 PROCEDURE get_segment_parents(
142 p_segment_info IN OUT NOCOPY hr_segments_info,
143 p_segment_number IN BINARY_INTEGER,
144 p_sql_text IN OUT NOCOPY LONG,
145 p_number_parents OUT NOCOPY BINARY_INTEGER) is
146 --
147 -- Declare local variables
148 --
149 l_count BINARY_INTEGER;
150 l_str_start NUMBER;
151 l_parent_loc NUMBER;
152 l_end_replace_string NUMBER;
153 l_length_rep_string NUMBER;
154 l_replace_string VARCHAR2(2000);
155 l_segment_or_vs VARCHAR2(2000);
156 l_placeholder_identifier VARCHAR2(10) := ':$FLEX$.';
157 l_flex_value_set_name fnd_flex_value_sets.flex_value_set_name%TYPE;
158 l_token_value VARCHAR2(2000) := '';
159 l_cr_check NUMBER;
160 l_cr_char NUMBER;
161 l_proc varchar2(72);
162 --
163 begin
164 --
165
166 IF g_debug THEN
167 l_proc := g_package||'get_segment_parents';
168 hr_utility.set_location('Entering:'|| l_proc, 10);
169 END IF;
170
171 --
172 l_count :=0;
173 l_parent_loc := 1;
174 --
175 -- Find Carriage return character
176 --
177 p_sql_text := replace(p_sql_text,c_newline,' ');
178 --
179 -- Loop over the number of segments, looking for parent segments
180 -- by checking for $FLEX$ strings in the SQL text. The application
181 -- column name, segment name, or value set name that follows this
182 -- place holder identifies the parent segment
183 --
184
185 IF g_debug THEN
186 hr_utility.set_location(l_proc, 20);
187 END IF;
188
189 --
190 LOOP
191 l_parent_loc := INSTR(p_sql_text,l_placeholder_identifier,(l_parent_loc+
192 length(l_placeholder_identifier)));
193 EXIT WHEN l_parent_loc < 1;
194 --
195
196 IF g_debug THEN
197 hr_utility.set_location(l_proc, 30);
198 END IF;
199
200 --
201 p_segment_info.psegment_pointer(p_segment_number):=g_psegment_count+1;
202 --
203 l_count := l_count+1;
204 l_token_value := '';
205 l_end_replace_string := INSTR(p_sql_text, ' ',l_parent_loc);
206 l_length_rep_string := l_end_replace_string - l_parent_loc;
207 if(l_end_replace_string > 0) then
208 l_replace_string := substr(p_sql_text, l_parent_loc,
209 l_length_rep_string);
210 else
211 l_replace_string := substr(p_sql_text,l_parent_loc);
212 end if;
213 if(INSTR(l_replace_string,c_newline)) > 0 then
214 l_replace_string := substr(l_replace_string,1,INSTR(
215 l_replace_string,c_newline)-1);
216 end if;
217 if(INSTR(l_replace_string,',')) > 0 then
218 l_replace_string := substr(l_replace_string,1,INSTR(
219 l_replace_string,',')-1);
220 end if;
221 l_segment_or_vs := substr(l_replace_string,(length(
222 l_placeholder_identifier)+1));
223 --
224 -- Handle cases where the replacement string is in either of the
225 -- forms:
226 -- :$FLEX$.VALUE_SET.VALUE
227 -- :$FLEX$.VALUE_SET.ID
228 -- Need to get rid of the suffixes.
229 --
230 if upper(l_segment_or_vs) like '%.VALUE' then
231 l_segment_or_vs :=
232 substr(l_segment_or_vs, 1, length(l_segment_or_vs)-length('.VALUE'));
233 elsif upper(l_segment_or_vs) like '%.ID' then
234 l_segment_or_vs :=
235 substr(l_segment_or_vs, 1, length(l_segment_or_vs)-length('.ID'));
236 end if;
237 --
238 FOR i in 1..p_segment_info.nsegments LOOP
239 --
240 -- Next substitute the $FLEX$ text with the application column name
241 -- this can be a value set, or a segment name.
242 --
243
244 IF g_debug THEN
245 hr_utility.set_location(l_proc, 40);
246 END IF;
247
248 --
249 if (p_segment_info.value_set(i) > 0) then
250 select flex_value_set_name into l_flex_value_set_name
251 from fnd_flex_value_sets
252 where flex_value_set_id = p_segment_info.value_set(i);
253 if (l_flex_value_set_name = l_segment_or_vs) then
254 l_token_value := g_app_col_placeholder||p_segment_info.
255 application_column_name(i)||' ';
256 p_segment_info.parent_segments((l_count+g_psegment_count)) :=
257 p_segment_info.application_column_name(i);
258 --
259 -- Set the parent segment number of children component,
260 -- so that the flex code will know to include JS onChange event
261 -- Note it doesn't matter how many children the parent segment
262 -- has only that it has non-zero children.
263 --
264 p_segment_info.number_children(i) := 1;
265 exit;
266 end if;
267 end if;
268 --
269 END LOOP;
270 --
271 -- The text after $FLEX$ was not a value set name, so just check for
272 -- segment name. This is not to standards, it ought to be the
273 -- value set name, but as a secondary check, perform this step
274 --
275
276 IF g_debug THEN
277 hr_utility.set_location(l_proc, 50);
278 END IF;
279
280 --
281 if(l_token_value is null) then
282 --
283 FOR i in 1..p_segment_info.nsegments LOOP
284 --
285
286 IF g_debug THEN
287 hr_utility.set_location(l_proc, 60);
288 END IF;
289
290 --
291 if (p_segment_info.segment_name(i) = l_segment_or_vs) then
292 l_token_value := g_app_col_placeholder||p_segment_info.
293 application_column_name(i)||' ';
294 p_segment_info.parent_segments((l_count+g_psegment_count)) :=
295 p_segment_info.application_column_name(i);
296 exit;
297 end if;
298 --
299 END LOOP;
300 --
301
302 IF g_debug THEN
303 hr_utility.set_location(l_proc, 70);
304 END IF;
305
306 --
307 end if;
308 --
309 -- Replace all occurances of the $FLEX$ placeholder string with the
310 -- the application column name. This can be used in build_sql_text
311 -- to replace this with the value set in by the web (user)
312 --
313 p_sql_text := replace(p_sql_text,l_replace_string,l_token_value);
314
315 END LOOP;
316 --
317
318 IF g_debug THEN
319 hr_utility.set_location(l_proc, 80);
320 END IF;
321
322 --
323 -- Up date the number of parents and segment counts.
324 --
325 p_number_parents := l_count;
326 g_psegment_count := g_psegment_count + l_count;
327 --
328
329 IF g_debug THEN
330 hr_utility.set_location('Leaving: '||l_proc, 80);
331 END IF;
332
333 --
334 end get_segment_parents;
335 --
336 -- ----------------------------------------------------------------------------
337 -- |--------------------------< substitute_profiles >-------------------------|
338 -- ----------------------------------------------------------------------------
339 --
340 procedure substitute_profiles
341 (p_sql_text in out nocopy long
342 ) is
343 l_proc varchar2(72);
344 c_profile constant varchar2(20) default ':$PROFILES$.';
345 l_placeholder varchar2(32767);
346 l_placeholder_len number;
347 l_end_char varchar2(2000);
348 l_profile_start number;
349 l_profile_end number;
350 l_profile_name varchar2(32767) := '';
351 l_profile_value varchar2(32767);
352 l_sql_text varchar2(32767);
353 l_sql_text_rep varchar2(32767);
354 l_comma_pos number;
355 l_end varchar2(32767);
356 l_next_name_count number;
357 l_next_name_char varchar2(2);
358 l_message long;
359
360 begin
361
362 IF g_debug THEN
363 l_proc := g_package||'substitute_profiles';
364 hr_utility.set_location('Entering:'|| l_proc, 10);
365 END IF;
366
367 --
368 -- Don't strictly need to do this if get_segment_parents is called
369 -- before this procedure.
370 --
371 l_sql_text := replace(p_sql_text, c_newline, ' ');
372 --
373 loop
374 --
375 -- Look for next occurrence of :$PROFILE$.profile_name placeholder.
376 --
377 l_profile_start := instr(l_sql_text, c_profile, 1);
378 exit when l_profile_start = 0;
379 --
380 -- Found a placeholder.
381 --
382
383 IF g_debug THEN
384 hr_utility.set_location(l_proc, 20);
385 END IF;
386
387 /* AR 6/7/99
388 Adjust Adnan's code here, to check for any following special character
389 profile names can be made up from alpha numeric character, and an
390 underscore '_'. Anything else will terminate the profile name.
391 */
392 l_next_name_count := l_profile_start+length(c_profile);
393 l_sql_text_rep := translate(upper(l_sql_text),
394 '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ_',
395 '&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&');
396 l_end := ltrim(substr(l_sql_text_rep,l_next_name_count),'&');
397
398 if l_end is null then
399 l_profile_name := substr(l_sql_text,l_next_name_count);
400 else
401 l_profile_name := substr(l_sql_text,l_next_name_count,
402 instr(l_sql_text_rep,l_end)-l_next_name_count);
403 end if;
404 l_placeholder := c_profile||l_profile_name;
405 l_placeholder_len := length(l_placeholder);
406 --
407 -- Get the profile value.
408 --
409 fnd_profile.get(upper(l_profile_name), l_profile_value);
410 /*
411 AR 8/7/99
412 Profile values can be null. There is no need to raise an error
413 here. If the profile can not be found, then the above flex
414 routine will raise an error
415
416 if l_profile_value is null then
417
418 IF g_debug THEN
419 hr_utility.set_location('Leaving ' || l_proc, 30);
420 END IF;
421
422 --
423 -- Using an AOL message here!
424 --
425 hr_utility.set_message(0, 'FLEX-INVALID PROFILE');
426 hr_utility.set_message_token('BTOKEN', upper(l_profile_name));
427 hr_utility.raise_error;
428 end if;
429 */
430 --
431 -- Replace the profile value completely in the string. Note:
432 -- cannot do a simple REPLACE call because one placeholder
433 -- may be a substring of another placeholder.
434 --
435 l_profile_value := l_profile_value;
436
437 loop
438 --
439 -- Find the placeholder in the SQL text.
440 --
441 l_profile_start := instr(l_sql_text, l_placeholder, l_profile_start);
442 exit when l_profile_start = 0;
443 --
444 -- Only substitute the profile value if the placeholder ends
445 -- with a space or a comma.
446 --
447 /* AR 9/7/99
448 Alter Adnan's code - the profile can end with any non-alphanumeric
449 character, not just space or ,
450 */
451 l_end_char :=
452 substr(l_sql_text, l_profile_start + l_placeholder_len, 1);
453 l_sql_text :=
454 substr(l_sql_text, 1, l_profile_start - 1) ||
455 '''' || l_profile_value || '''' ||
456 substr(l_sql_text, l_profile_start + l_placeholder_len);
457 end loop;
458 end loop;
459 p_sql_text := l_sql_text;
460
461 IF g_debug THEN
462 hr_utility.set_location('Leaving ' || l_proc, 40);
463 END IF;
464
465
466 EXCEPTION
467 WHEN OTHERS THEN
468 hr_utility.trace(' Exception in hr_flexfield_info.substitute_profiles ' || SQLERRM );
469 raise;
470
471 end substitute_profiles;
472 --
473 -- ----------------------------------------------------------------------------
474 -- |-----------------------------< get_contexts >-----------------------------|
475 -- ----------------------------------------------------------------------------
476 --
477
478 PROCEDURE get_contexts
479 (p_appl_short_name IN
480 fnd_application.application_short_name%TYPE,
481 p_flexfield_name IN
482 fnd_descriptive_flexs_vl.descriptive_flexfield_name%TYPE,
483 p_contexts OUT NOCOPY fnd_dflex.contexts_dr)
484 is
485 --
486 -- Declare local variables
487 --
488 l_flexfield fnd_dflex.dflex_r;
489 l_flexinfo fnd_dflex.dflex_dr;
490 l_proc varchar2(72);
491 --
492 begin
493 --
494
495 IF g_debug THEN
496 l_proc := g_package||'get_contexts';
497 hr_utility.set_location('Entering:'|| l_proc, 5);
498 END IF;
499
500 --
501
502 fnd_dflex.get_flexfield(p_appl_short_name,
503 p_flexfield_name,
504 l_flexfield,
505 l_flexinfo);
506
507 --
508
509 IF g_debug THEN
510 hr_utility.set_location(l_proc, 10);
511 END IF;
512
513 --
514 fnd_dflex.get_contexts(l_flexfield, p_contexts);
515 --
516
517 IF g_debug THEN
518 hr_utility.set_location(' Leaving:'||l_proc, 15);
519 END IF;
520
521 --
522 end get_contexts;
523 --
524 -- ----------------------------------------------------------------------------
525 -- |------------------------< get_concatenated_contexts >---------------------|
526 -- ----------------------------------------------------------------------------
527 --
528
529 PROCEDURE get_concatenated_contexts
530 (p_appl_short_name IN
531 fnd_application.application_short_name%TYPE,
532 p_flexfield_name IN
533 fnd_descriptive_flexs_vl.descriptive_flexfield_name%TYPE,
534 p_enabled_only IN BOOLEAN,
535 p_concatenation_chr IN VARCHAR2,
536 p_context_list OUT NOCOPY LONG)
537 is
538 --
539 -- Declare local variables
540 --
541 l_contexts fnd_dflex.contexts_dr;
542 l_context_list long;
543 i binary_integer;
544 l_proc varchar2(72);
545 --
546 begin
547 --
548 g_debug := hr_utility.debug_enabled;
549 IF g_debug THEN
550 l_proc := g_package||'get_concatenated_contexts';
551 hr_utility.set_location('Entering:'|| l_proc, 5);
552 END IF;
553
554 --
555 hr_flexfield_info.get_contexts(p_appl_short_name,
556 p_flexfield_name,
557 l_contexts);
558 --
559
560 IF g_debug THEN
561 hr_utility.set_location( l_proc, 10);
562 END IF;
563
564 --
565 for i in 1 .. l_contexts.ncontexts
566 loop
567 if l_contexts.context_code(i) <> 'Global Data Elements' then
568 if p_enabled_only then
569 if l_contexts.is_enabled(i) then
570 l_context_list := l_context_list ||
571 p_concatenation_chr ||
572 l_contexts.context_code(i);
573 end if;
574 else
575 l_context_list := l_context_list ||
576 p_concatenation_chr ||
577 l_contexts.context_code(i);
578 end if;
579 end if;
580 end loop;
581 --
582 p_context_list := ltrim(l_context_list,p_concatenation_chr);
583 --
584
585 IF g_debug THEN
586 hr_utility.set_location(' Leaving:'||l_proc, 15);
587 END IF;
588
589 --
590 end get_concatenated_contexts;
591
592 --
593 -- ----------------------------------------------------------------------------
594 -- |---------------------< get_dependent_parent_column >----------------------|
595 -- ----------------------------------------------------------------------------
596 --
597 FUNCTION get_dependent_parent_column
598 (p_segment_info IN OUT NOCOPY hr_segments_info,
599 p_segment_number IN NUMBER)
600 RETURN VARCHAR2 is
601
602 cursor c_parent_set(p_value_set_id in fnd_flex_value_sets.flex_value_set_id%TYPE)
603 is
604 select parent_flex_value_set_id
605 from fnd_flex_value_sets
606 where flex_value_set_id = p_value_set_id;
607
608 l_application_column_name fnd_columns.column_name%TYPE;
609 l_dep_value_set_id fnd_flex_value_sets.flex_value_set_id%TYPE;
610 l_indep_value_set_id fnd_flex_value_sets.flex_value_set_id%TYPE;
611 l_count number := 1;
612
613 BEGIN
614
615 open c_parent_set(p_segment_info.value_set(p_segment_number));
616 fetch c_parent_set into l_indep_value_set_id;
617
618 if c_parent_set%FOUND then
619 close c_parent_set;
620 WHILE ((p_segment_info.value_set(l_count) <> l_indep_value_set_id)
621 AND (l_count < p_segment_info.application_column_name.count)) LOOP
622 l_count := l_count +1;
623 END LOOP;
624 if l_count < p_segment_info.application_column_name.count then
625 g_psegment_count := g_psegment_count +1;
626 --
627 -- Set the return variable to the parent application column name
628 --
629 l_application_column_name := p_segment_info.application_column_name(l_count);
630 --
631 -- Set the parent segment pointer to point at the right segment
632 --
633 p_segment_info.psegment_pointer(p_segment_number):=
634 g_psegment_count;
635 --
636 -- Set the parent segment name
637 --
638 p_segment_info.parent_segments(g_psegment_count) :=
639 l_application_column_name;
640 --
641 -- Set the parent segment number of children component,
642 -- so that the flex code will know to include trigger (for forms)
643 -- or JS onChange event (for self service)
644 -- Note it doesn't matter how many children the parent segment has,
645 -- only that it has non-zero children.
646 --
647 p_segment_info.number_children(l_count) := 1;
648
649 else
650 l_application_column_name := hr_api.g_varchar2;
651 end if;
652 else
653 close c_parent_set;
654 l_application_column_name := hr_api.g_varchar2;
655 end if;
656
657
658 RETURN l_application_column_name;
659
660 end get_dependent_parent_column;
661 --
662 -- ----------------------------------------------------------------------------
663 -- |--------------------------< get_validation_info >-------------------------|
664 -- ----------------------------------------------------------------------------
665 --
666 PROCEDURE get_validation_info
667 (p_segment_info IN OUT NOCOPY hr_segments_info,
668 p_session_date IN DATE,
669 p_context_code IN VARCHAR2,
670 p_flexfield_name IN VARCHAR2,
671 p_flex_type IN VARCHAR2)
672 is
673 --
674 -- Declare local variables
675 --
676 l_valueset_r fnd_vset.valueset_r;
677 l_valueset_dr fnd_vset.valueset_dr;
678 l_value_set_id fnd_flex_value_sets.flex_value_set_id%TYPE;
679 l_proc varchar2(72);
680 l_order_by_start NUMBER;
681 l_from_start NUMBER;
682 l_additional_and_clause VARCHAR2(2000);
683 l_from_where VARCHAR2(2000);
684 l_select_clause VARCHAR2(2000);
685 l_dep_parent_column_name fnd_columns.column_name%TYPE;
686 --
687 begin
688 --
689
690 IF g_debug THEN
691 l_proc:= g_package||'get_validation_info';
692 hr_utility.set_location('Entering:'|| l_proc, 5);
693 END IF;
694
695 --
696 for i in 1..p_segment_info.nsegments loop
697 --
698 -- Copy the retrieved value set information into the hr structure
699 -- containing all the information about the segments.
700 --
701 if (p_segment_info.value_set(i) is not null) then
702 --
703 l_value_set_id := p_segment_info.value_set(i);
704 fnd_vset.get_valueset(l_value_set_id,l_valueset_r,l_valueset_dr);
705 --
706 p_segment_info.format_type(i) := l_valueset_dr.format_type;
707 p_segment_info.alphanumeric_allowed_flag(i)
708 := l_valueset_dr.alphanumeric_allowed_flag;
709 p_segment_info.uppercase_only_flag(i)
710 := l_valueset_dr.uppercase_only_flag;
711 p_segment_info.numeric_mode_flag(i)
712 := l_valueset_dr.numeric_mode_flag;
713 p_segment_info.max_size(i) := l_valueset_dr.max_size;
714 p_segment_info.max_value(i) := l_valueset_dr.max_value;
715 p_segment_info.min_value(i) := l_valueset_dr.min_value;
716 p_segment_info.number_children(i):=0;
717 p_segment_info.longlist_enabled(i)
718 := l_valueset_dr.longlist_enabled;
719 p_segment_info.has_id(i) := l_valueset_dr.has_id;
720 p_segment_info.has_meaning(i) := l_valueset_dr.has_meaning;
721 --
722 -- Initailize the SQL text columns.
723 --
724 p_segment_info.sql_text(i) := '';
725 p_segment_info.sql_txt_descr(i) := '';
726 --
727
728 IF g_debug THEN
729 hr_utility.set_location(l_proc, 10);
730 END IF;
731
732 --
733 --
734 -- Default validation type to None.
735 --
736 p_segment_info.validation_type(i) := 'NONE';
737 --
738 -- Ok next build the SQL text that can be used to build a pop-list
739 -- for this segment, if this is a table validated or independant
740 -- validated value set - i.e. it has an associated list of values.
741 -- We are going to build two versions of the SQL. One can be used
742 -- to define the list of values associated with this segment(SQL_TEXT), the
743 -- other is used to converted a value (ID) stored on the database into a
744 -- a description (VALUE) (SQL_DESCR_TXT).
745 --
746 if l_valueset_r.validation_type = 'F' then
747 --
748 p_segment_info.validation_type(i) := 'TABLE';
749 --
750
751 IF g_debug THEN
752 hr_utility.set_location(l_proc, 20);
753 END IF;
754
755 --
756 select 'SELECT ' ||
757 l_valueset_r.table_info.value_column_name ||
758 decode(l_valueset_r.table_info.meaning_column_name,null,',NULL ',
759 ','||l_valueset_r.table_info.meaning_column_name)||
760 decode(l_valueset_r.table_info.id_column_name,null,',NULL ',
761 ','||l_valueset_r.table_info.id_column_name)||
762 ' FROM ' ||
763 l_valueset_r.table_info.table_name || ' ' ||
764 l_valueset_r.table_info.where_clause
765 into p_segment_info.sql_text(i)
766 from dual;
767 --
768
769 IF g_debug THEN
770 hr_utility.set_location(l_proc, 30);
771 END IF;
772
773 --
774 get_segment_parents(p_segment_info,i,p_segment_info.sql_text(i),
775 p_segment_info.number_parents(i));
776
777
778 IF g_debug THEN
779 hr_utility.set_location(l_proc, 35);
780 END IF;
781
782 --
783 substitute_profiles
784 (p_sql_text => p_segment_info.sql_text(i)
785 );
786 --
787
788 IF g_debug THEN
789 hr_utility.set_location(l_proc, 40);
790 END IF;
791
792 --
793 l_order_by_start := INSTR(upper(p_segment_info.sql_text(i)),'ORDER BY');
794 l_from_start := INSTR(upper(p_segment_info.sql_text(i)),'FROM');
795 --
796 if ((l_valueset_r.table_info.meaning_column_name is not null) or
797 (l_valueset_r.table_info.id_column_name is not null)) then
798
799 if ((l_valueset_r.table_info.meaning_column_name <>
800 l_valueset_r.table_info.value_column_name) or
801 (l_valueset_r.table_info.id_column_name <>
802 l_valueset_r.table_info.value_column_name)) then
803
804 if ((l_valueset_r.table_info.where_clause is null) or
805 (INSTR(upper(p_segment_info.sql_text(i)),'WHERE') = 0)) then
806 if l_valueset_r.table_info.id_column_name is null then
807 --
808
809 IF g_debug THEN
810 hr_utility.set_location(l_proc, 50);
811 END IF;
812
813 --
814 l_additional_and_clause := ' WHERE '||l_valueset_r.table_info.
815 value_column_name||' = '||g_app_col_placeholder||
816 p_segment_info.application_column_name(i)||' ';
817 else
818 --
819
820 IF g_debug THEN
821 hr_utility.set_location(l_proc, 55);
822 END IF;
823
824 --
825 l_additional_and_clause := ' WHERE '||l_valueset_r.table_info.
826 id_column_name||' = '||g_app_col_placeholder||
827 p_segment_info.application_column_name(i)||' ';
828 end if;
829 else
830 --
831
832 IF g_debug THEN
833 hr_utility.set_location(l_proc, 60);
834 END IF;
835
836 --
837 if l_valueset_r.table_info.id_column_name is null then
838 --
839
840 IF g_debug THEN
841 hr_utility.set_location(l_proc, 70);
842 END IF;
843
844 --
845 l_additional_and_clause := ' AND '||l_valueset_r.table_info.value_column_name
846 ||' = '||g_app_col_placeholder||p_segment_info.application_column_name(i)||' ';
847 else
848 --
849
850 IF g_debug THEN
851 hr_utility.set_location(l_proc, 75);
852 END IF;
853
854 --
855 l_additional_and_clause := ' AND '||l_valueset_r.table_info.id_column_name
856 ||' = '||g_app_col_placeholder||p_segment_info.application_column_name(i)||' ';
857 end if;
858 end if;
859 --
860
861 IF g_debug THEN
862 hr_utility.set_location(l_proc, 80);
863 END IF;
864
865 --
866 --
867 -- Build the SQL for the FROM clause
868 --
869 if(l_order_by_start >0) then
870 l_from_where := substr(p_segment_info.sql_text(i),l_from_start,(
871 l_order_by_start-l_from_start));
872 else
873 l_from_where := substr(p_segment_info.sql_text(i),l_from_start);
874 end if;
875 --
876
877 IF g_debug THEN
878 hr_utility.set_location(l_proc, 90);
879 END IF;
880
881 --
882 if(l_valueset_r.table_info.meaning_column_name is not null) then
883 l_select_clause := 'SELECT '||l_valueset_r.table_info.
884 meaning_column_name||' ';
885 else
886 l_select_clause := 'SELECT '||l_valueset_r.table_info.
887 value_column_name||' ';
888 end if;
889 --
890 -- Set the description SQL - i.e. the SQL needed to change the ID or
891 -- value into a description on the WEB page for example.
892 --
893 p_segment_info.sql_txt_descr(i) := l_select_clause||l_from_where||
894 l_additional_and_clause;
895 --
896 else
897 --
898
899 IF g_debug THEN
900 hr_utility.set_location(l_proc, 100);
901 END IF;
902
903 --
904 --
905 -- OK, we don't need a description SQL (for any one of a number of reasons -
906 -- see if clause above)
907 --
908 p_segment_info.sql_txt_descr(i) := '';
909 end if;
910 else
911 --
912
913 IF g_debug THEN
914 hr_utility.set_location(l_proc, 110);
915 END IF;
916
917 --
918 p_segment_info.sql_txt_descr(i) := '';
919 end if;
920 elsif l_valueset_r.validation_type = 'I' then
921 --
922 p_segment_info.validation_type(i) := 'INDEPENDENT';
923 --
924
925 IF g_debug THEN
926 hr_utility.set_location(l_proc, 120);
927 END IF;
928
929 --
930 --
931 -- Now do the same if the segment is independantly validated
932 --
933 p_segment_info.sql_text(i) := 'SELECT FFV.FLEX_VALUE,FFVTL.DESCRIPTION,'||
934 -- 'FFV.FLEX_VALUE_ID FROM ' ||
935 --
936 -- Bizarre fact: AOL require independent values rather than ids
937 -- even in validate in 'ID' mode.
938 --
939 'FFV.FLEX_VALUE FROM ' ||
940 'FND_FLEX_VALUES FFV, FND_FLEX_VALUES_TL FFVTL'||
941 ' WHERE FFV.FLEX_VALUE_ID = FFVTL.FLEX_VALUE_ID' ||
942 ' AND FFV.FLEX_VALUE_SET_ID = ' || L_VALUE_SET_ID ||
943 ' AND FFV.ENABLED_FLAG = ''Y'''||
944 ' AND '''||P_SESSION_DATE||''' BETWEEN'||
945 ' NVL(FFV.START_DATE_ACTIVE,'''||
946 P_SESSION_DATE||''')'||
947 ' AND NVL(FFV.END_DATE_ACTIVE,'''||
948 P_SESSION_DATE||''')'||
949 ' AND FFVTL.LANGUAGE = USERENV(''LANG'')';
950 --
951 -- We can hard code the DESC SQL this time, since we know explicitly
952 -- how independant value sets are built. This should be changed once
953 -- we have the procedure from AOL.
954 --
955 p_segment_info.sql_txt_descr(i) := 'SELECT nvl(DESCRIPTION,FLEX_VALUE)'||
956 ' FROM FND_FLEX_VALUES_VL'||
957 ' WHERE FLEX_VALUE_SET_ID =' || l_value_set_id ||
958 ' AND ENABLED_FLAG = ''Y'''||
959 ' AND '''||P_SESSION_DATE||''' BETWEEN'||
960 ' NVL(START_DATE_ACTIVE,'''||
961 P_SESSION_DATE||''')'||
962 ' AND NVL(END_DATE_ACTIVE,'''||
963 P_SESSION_DATE||''')'||
964 ' AND FLEX_VALUE = '||g_app_col_placeholder||
965 p_segment_info.application_column_name(i);
966
967 p_segment_info.number_parents(i) := 0;
968 --
969 -- Dependent value sets. Again, can hard code some stuff.
970 -- For AOL data model changes must revisit this code.
971 -- Given time should convert to AOL interface
972 --
973 elsif l_valueset_r.validation_type = 'D' then
974 --
975 p_segment_info.validation_type(i) := 'DEPENDENT';
976 --
977
978 IF g_debug THEN
979 hr_utility.set_location(l_proc, 120);
980 END IF;
981
982 --
983 --
984 -- Now do the same if the segment is dependantly validated
985 --
986 --
987 -- Work out what the parent segment is, for replacing later
988 --
989 l_dep_parent_column_name := get_dependent_parent_column
990 (p_segment_info => p_segment_info,
991 p_segment_number =>i);
992
993 if l_dep_parent_column_name <> hr_api.g_varchar2 then
994
995 p_segment_info.sql_text(i) :=
996 ' SELECT FLEX_VALUE,NVL(DESCRIPTION,FLEX_VALUE),FLEX_VALUE'||
997 ' FROM FND_FLEX_VALUES_VL'||
998 ' WHERE FLEX_VALUE_SET_ID = '||l_value_set_id||
999 ' AND ENABLED_FLAG = ''Y'''||
1000 ' AND PARENT_FLEX_VALUE_LOW = '||g_app_col_placeholder||l_dep_parent_column_name||
1001 ' AND '''||P_SESSION_DATE||''' BETWEEN'||
1002 ' NVL(START_DATE_ACTIVE,'''||
1003 P_SESSION_DATE||''')'||
1004 ' AND NVL(END_DATE_ACTIVE,'''||
1005 P_SESSION_DATE||''')';
1006
1007 p_segment_info.sql_txt_descr(i) := 'SELECT nvl(DESCRIPTION,FLEX_VALUE)'||
1008 ' FROM FND_FLEX_VALUES_VL'||
1009 ' WHERE FLEX_VALUE_SET_ID =' || l_value_set_id ||
1010 ' AND ENABLED_FLAG = ''Y'''||
1011 ' AND '''||P_SESSION_DATE||''' BETWEEN'||
1012 ' NVL(START_DATE_ACTIVE,'''||
1013 P_SESSION_DATE||''')'||
1014 ' AND NVL(END_DATE_ACTIVE,'''||
1015 P_SESSION_DATE||''')'||
1016 ' AND FLEX_VALUE = '||g_app_col_placeholder||
1017 p_segment_info.application_column_name(i);
1018 else
1019 --
1020 -- We can't reliably build the SQL list for this column,
1021 -- hence we must pass no dependent information back.
1022 --
1023 p_segment_info.sql_text(i) := '';
1024 p_segment_info.sql_txt_descr(i) := '';
1025
1026 end if;
1027 --
1028 -- We can hard code the DESC SQL this time, since we know explicitly
1029 -- how independant value sets are built. This should be changed once
1030 -- we have the procedure from AOL.
1031 --
1032
1033 p_segment_info.number_parents(i) := 1;
1034
1035 elsif l_valueset_r.validation_type = 'P' then
1036 --
1037
1038 IF g_debug THEN
1039 hr_utility.set_location(l_proc, 130);
1040 END IF;
1041
1042 p_segment_info.validation_type(i) := 'PAIR';
1043 --
1044 -- Can't have parents below this line
1045 --
1046 p_segment_info.number_parents(i) := 0;
1047 p_segment_info.sql_text(i) := '';
1048 p_segment_info.sql_txt_descr(i) := '';
1049 elsif l_valueset_r.validation_type = 'U' then
1050 --
1051
1052 IF g_debug THEN
1053 hr_utility.set_location(l_proc, 130);
1054 END IF;
1055
1056 p_segment_info.validation_type(i) := 'SPECIAL';
1057 --
1058 -- Can't have parents below this line
1059 --
1060 p_segment_info.number_parents(i) := 0;
1061 p_segment_info.sql_text(i) := '';
1062 p_segment_info.sql_txt_descr(i) := '';
1063
1064
1065 end if;
1066 --
1067 else -- have null value set id
1068 --
1069
1070 IF g_debug THEN
1071 hr_utility.set_location(l_proc, 140);
1072 END IF;
1073
1074 --
1075 p_segment_info.number_parents(i) := 0;
1076 p_segment_info.validation_type(i) := null;
1077 p_segment_info.format_type(i) := null;
1078 p_segment_info.alphanumeric_allowed_flag(i) := null;
1079 p_segment_info.uppercase_only_flag(i) := null;
1080 p_segment_info.numeric_mode_flag(i) := null;
1081 --
1082 --set default max length
1083 --
1084 IF p_flex_type = 'KEY' then
1085 IF p_flexfield_name = 'PEA' THEN
1086 p_segment_info.max_size(i) := 150;
1087 ELSE
1088 p_segment_info.max_size(i) := 60;
1089 END IF;
1090 ELSE
1091 p_segment_info.max_size(i) := 150;
1092 END IF;
1093 p_segment_info.max_value(i) := null;
1094 p_segment_info.min_value(i) := null;
1095 p_segment_info.longlist_enabled(i) := false;
1096 p_segment_info.has_id(i) := false;
1097 p_segment_info.has_meaning(i) := false;
1098 --
1099 -- Slight issue with GENERIC address structure and the web application
1100 -- have to return some SQL text, or descr text for the country segment
1101 -- even though this is unvalidated.
1102 --
1103 if(((p_segment_info.application_column_name(i)='COUNTRY') and
1104 (p_context_code = 'GENERIC')) and
1105 (p_flexfield_name = 'Address Structure'))then
1106 p_segment_info.sql_text(i) := 'SELECT TERRITORY_SHORT_NAME,NULL,'||
1107 'TERRITORY_CODE FROM FND_TERRITORIES_VL '||
1108 'ORDER BY TERRITORY_SHORT_NAME';
1109 p_segment_info.sql_txt_descr(i) := 'SELECT TERRITORY_SHORT_NAME '||
1110 'FROM FND_TERRITORIES_VL '||
1111 'WHERE TERRITORY_CODE = '||g_app_col_placeholder||'COUNTRY';
1112 else
1113 p_segment_info.sql_text(i) := '';
1114 p_segment_info.sql_txt_descr(i) := '';
1115 end if;
1116 end if;
1117 --
1118 end loop;
1119
1120 IF g_debug THEN
1121 hr_utility.set_location(' Leaving:'||l_proc, 150);
1122 END IF;
1123
1124 --
1125 end get_validation_info;
1126 --
1127 -- ----------------------------------------------------------------------------
1128 -- |------------------------------< get_segments >----------------------------|
1129 -- ----------------------------------------------------------------------------
1130 --
1131 PROCEDURE get_segments
1132 (p_appl_short_name IN
1133 fnd_application.application_short_name%TYPE,
1134 p_flexfield_name IN
1135 fnd_descriptive_flexs_vl.descriptive_flexfield_name%TYPE,
1136 p_context_code IN VARCHAR2,
1137 p_enabled_only IN BOOLEAN,
1138 p_segments OUT NOCOPY hr_segments_info,
1139 p_session_date IN DATE)
1140 is
1141 --
1142 -- Declare local variables
1143 --
1144 l_flexfield fnd_dflex.dflex_r;
1145 l_flexinfo fnd_dflex.dflex_dr;
1146 l_segments fnd_dflex.segments_dr;
1147 l_hr_segments hr_segments_info;
1148 l_proc varchar2(72);
1149 l_total_parents NUMBER :=0;
1150 --
1151 begin
1152 --
1153 g_debug := hr_utility.debug_enabled;
1154 IF g_debug THEN
1155 l_proc := g_package||'get_segments';
1156 hr_utility.set_location('Entering:'|| l_proc, 10);
1157 END IF;
1158
1159 --
1160 --
1161 -- Start by finding the flexfield
1162 --
1163 fnd_dflex.get_flexfield(p_appl_short_name,
1164 p_flexfield_name,
1165 l_flexfield,
1166 l_flexinfo);
1167 --
1168
1169 IF g_debug THEN
1170 hr_utility.set_location(l_proc, 20);
1171 END IF;
1172
1173 --
1174 -- Now, given the flexfield information returned
1175 -- above, find the segment information
1176 --
1177 fnd_dflex.get_segments(fnd_dflex.make_context(l_flexfield,
1178 p_context_code),
1179 l_segments,
1180 p_enabled_only);
1181 --
1182 l_hr_segments.nsegments := l_segments.nsegments;
1183 --
1184 -- For each of the segments returned, copy the information
1185 -- returned into the HR structure.
1186 --
1187 for i in 1 .. l_hr_segments.nsegments
1188 loop
1189 --
1190
1191 IF g_debug THEN
1192 hr_utility.set_location(l_proc, 30);
1193 END IF;
1194
1195 --
1196 l_hr_segments.application_column_name(i)
1197 := l_segments.application_column_name(i);
1198 l_hr_segments.segment_name(i) := l_segments.segment_name(i);
1199 l_hr_segments.sequence(i) := l_segments.sequence(i);
1200 l_hr_segments.is_displayed(i) := l_segments.is_displayed(i);
1201 l_hr_segments.display_size(i) := l_segments.display_size(i);
1202 l_hr_segments.row_prompt(i) := l_segments.row_prompt(i);
1203 l_hr_segments.column_prompt(i) := l_segments.column_prompt(i);
1204 l_hr_segments.is_enabled(i) := l_segments.is_enabled(i);
1205 l_hr_segments.is_required(i) := l_segments.is_required(i);
1206 l_hr_segments.description(i) := l_segments.description(i);
1207 l_hr_segments.value_set(i) := l_segments.value_set(i);
1208 l_hr_segments.default_type(i) := l_segments.default_type(i);
1209 l_hr_segments.default_value(i) := l_segments.default_value(i);
1210 l_hr_segments.number_children(i) := 0;
1211 l_hr_segments.ak_region_code(i) := get_ak_region_code('DESCRIPTIVE',
1212 p_flexfield_name,
1213 p_context_code,
1214 l_hr_segments,
1215 i);
1216 end loop;
1217 --
1218
1219 IF g_debug THEN
1220 hr_utility.set_location(l_proc, 40);
1221 END IF;
1222
1223 --
1224 get_validation_info(p_segment_info => l_hr_segments,
1225 p_session_date => p_session_date,
1226 p_context_code => p_context_code,
1227 p_flexfield_name => p_flexfield_name,
1228 p_flex_type => 'DESC');
1229 --
1230 p_segments := l_hr_segments;
1231 --
1232
1233 IF g_debug THEN
1234 hr_utility.set_location(l_proc, 50);
1235 END IF;
1236
1237 end get_segments;
1238 --
1239 -- ----------------------------------------------------------------------------
1240 -- |----------------------------< build_sql_text >----------------------------|
1241 -- ----------------------------------------------------------------------------
1242 --
1243 PROCEDURE build_sql_text(
1244 p_sql_text IN OUT NOCOPY long,
1245 p_application_short_name IN fnd_application.application_short_name%TYPE,
1246 p_application_table_name IN fnd_tables.table_name%TYPE,
1247 p_segment_name_value IN segments) IS
1248
1249
1250 BEGIN
1251
1252 build_sql_text
1253 (p_sql_text => p_sql_text,
1254 p_application_short_name => p_application_short_name,
1255 p_application_table_name => p_application_table_name,
1256 p_column_name1 => p_segment_name_value.segment_name(1),
1257 p_column_value1 => p_segment_name_value.segment_value(1),
1258 p_column_name2 => p_segment_name_value.segment_name(2),
1259 p_column_value2 => p_segment_name_value.segment_value(2),
1260 p_column_name3 => p_segment_name_value.segment_name(3),
1261 p_column_value3 => p_segment_name_value.segment_value(3),
1262 p_column_name4 => p_segment_name_value.segment_name(4),
1263 p_column_value4 => p_segment_name_value.segment_value(4),
1264 p_column_name5 => p_segment_name_value.segment_name(5),
1265 p_column_value5 => p_segment_name_value.segment_value(5),
1266 p_column_name6 => p_segment_name_value.segment_name(6),
1267 p_column_value6 => p_segment_name_value.segment_value(6),
1268 p_column_name7 => p_segment_name_value.segment_name(7),
1269 p_column_value7 => p_segment_name_value.segment_value(7),
1270 p_column_name8 => p_segment_name_value.segment_name(8),
1271 p_column_value8 => p_segment_name_value.segment_value(8),
1272 p_column_name9 => p_segment_name_value.segment_name(9),
1273 p_column_value9 => p_segment_name_value.segment_value(9),
1274 p_column_name10 => p_segment_name_value.segment_name(10),
1275 p_column_value10 => p_segment_name_value.segment_value(10),
1276 p_column_name11 => p_segment_name_value.segment_name(11),
1277 p_column_value11 => p_segment_name_value.segment_value(11),
1278 p_column_name12 => p_segment_name_value.segment_name(12),
1279 p_column_value12 => p_segment_name_value.segment_value(12),
1280 p_column_name13 => p_segment_name_value.segment_name(13),
1281 p_column_value13 => p_segment_name_value.segment_value(13),
1282 p_column_name14 => p_segment_name_value.segment_name(14),
1283 p_column_value14 => p_segment_name_value.segment_value(14),
1284 p_column_name15 => p_segment_name_value.segment_name(15),
1285 p_column_value15 => p_segment_name_value.segment_value(15),
1286 p_column_name16 => p_segment_name_value.segment_name(16),
1287 p_column_value16 => p_segment_name_value.segment_value(16),
1288 p_column_name17 => p_segment_name_value.segment_name(17),
1289 p_column_value17 => p_segment_name_value.segment_value(17),
1290 p_column_name18 => p_segment_name_value.segment_name(18),
1291 p_column_value18 => p_segment_name_value.segment_value(18),
1292 p_column_name19 => p_segment_name_value.segment_name(19),
1293 p_column_value19 => p_segment_name_value.segment_value(19),
1294 p_column_name20 => p_segment_name_value.segment_name(20),
1295 p_column_value20 => p_segment_name_value.segment_value(20),
1296 p_column_name21 => p_segment_name_value.segment_name(21),
1297 p_column_value21 => p_segment_name_value.segment_value(21),
1298 p_column_name22 => p_segment_name_value.segment_name(22),
1299 p_column_value22 => p_segment_name_value.segment_value(22),
1300 p_column_name23 => p_segment_name_value.segment_name(23),
1301 p_column_value23 => p_segment_name_value.segment_value(23),
1302 p_column_name24 => p_segment_name_value.segment_name(24),
1303 p_column_value24 => p_segment_name_value.segment_value(24),
1304 p_column_name25 => p_segment_name_value.segment_name(25),
1305 p_column_value25 => p_segment_name_value.segment_value(25),
1306 p_column_name26 => p_segment_name_value.segment_name(26),
1307 p_column_value26 => p_segment_name_value.segment_value(26),
1308 p_column_name27 => p_segment_name_value.segment_name(27),
1309 p_column_value27 => p_segment_name_value.segment_value(27),
1310 p_column_name28 => p_segment_name_value.segment_name(28),
1311 p_column_value28 => p_segment_name_value.segment_value(28),
1312 p_column_name29 => p_segment_name_value.segment_name(29),
1313 p_column_value29 => p_segment_name_value.segment_value(29),
1314 p_column_name30 => p_segment_name_value.segment_name(30),
1315 p_column_value30 => p_segment_name_value.segment_value(30));
1316
1317 END build_sql_text;
1318
1319 PROCEDURE build_sql_text
1320 (p_sql_text IN OUT NOCOPY long,
1321 p_application_short_name in
1322 fnd_application.application_short_name%TYPE,
1323 p_application_table_name in fnd_tables.table_name%TYPE,
1324 p_column_name1 IN VARCHAR2 default null,
1325 p_column_value1 IN VARCHAR2 default null,
1326 p_column_name2 IN VARCHAR2 default null,
1327 p_column_value2 IN VARCHAR2 default null,
1328 p_column_name3 IN VARCHAR2 default null,
1329 p_column_value3 IN VARCHAR2 default null,
1330 p_column_name4 IN VARCHAR2 default null,
1331 p_column_value4 IN VARCHAR2 default null,
1332 p_column_name5 IN VARCHAR2 default null,
1333 p_column_value5 IN VARCHAR2 default null,
1334 p_column_name6 IN VARCHAR2 default null,
1335 p_column_value6 IN VARCHAR2 default null,
1336 p_column_name7 IN VARCHAR2 default null,
1337 p_column_value7 IN VARCHAR2 default null,
1338 p_column_name8 IN VARCHAR2 default null,
1339 p_column_value8 IN VARCHAR2 default null,
1340 p_column_name9 IN VARCHAR2 default null,
1341 p_column_value9 IN VARCHAR2 default null,
1342 p_column_name10 IN VARCHAR2 default null,
1343 p_column_value10 IN VARCHAR2 default null,
1344 p_column_name11 IN VARCHAR2 default null,
1345 p_column_value11 IN VARCHAR2 default null,
1346 p_column_name12 IN VARCHAR2 default null,
1347 p_column_value12 IN VARCHAR2 default null,
1348 p_column_name13 IN VARCHAR2 default null,
1349 p_column_value13 IN VARCHAR2 default null,
1350 p_column_name14 IN VARCHAR2 default null,
1351 p_column_value14 IN VARCHAR2 default null,
1352 p_column_name15 IN VARCHAR2 default null,
1353 p_column_value15 IN VARCHAR2 default null,
1354 p_column_name16 IN VARCHAR2 default null,
1355 p_column_value16 IN VARCHAR2 default null,
1356 p_column_name17 IN VARCHAR2 default null,
1357 p_column_value17 IN VARCHAR2 default null,
1358 p_column_name18 IN VARCHAR2 default null,
1359 p_column_value18 IN VARCHAR2 default null,
1360 p_column_name19 IN VARCHAR2 default null,
1361 p_column_value19 IN VARCHAR2 default null,
1362 p_column_name20 IN VARCHAR2 default null,
1363 p_column_value20 IN VARCHAR2 default null,
1364 p_column_name21 IN VARCHAR2 default null,
1365 p_column_value21 IN VARCHAR2 default null,
1366 p_column_name22 IN VARCHAR2 default null,
1367 p_column_value22 IN VARCHAR2 default null,
1368 p_column_name23 IN VARCHAR2 default null,
1369 p_column_value23 IN VARCHAR2 default null,
1370 p_column_name24 IN VARCHAR2 default null,
1371 p_column_value24 IN VARCHAR2 default null,
1372 p_column_name25 IN VARCHAR2 default null,
1373 p_column_value25 IN VARCHAR2 default null,
1374 p_column_name26 IN VARCHAR2 default null,
1375 p_column_value26 IN VARCHAR2 default null,
1376 p_column_name27 IN VARCHAR2 default null,
1377 p_column_value27 IN VARCHAR2 default null,
1378 p_column_name28 IN VARCHAR2 default null,
1379 p_column_value28 IN VARCHAR2 default null,
1380 p_column_name29 IN VARCHAR2 default null,
1381 p_column_value29 IN VARCHAR2 default null,
1382 p_column_name30 IN VARCHAR2 default null,
1383 p_column_value30 IN VARCHAR2 default null) IS
1384 --
1385 -- Declare local variables
1386 --
1387 l_column_type fnd_columns.column_type%TYPE;
1388 l_placeholder_string_length NUMBER := length(g_app_col_placeholder);
1389 l_placeholder NUMBER;
1390 l_end_placeholder NUMBER;
1391 l_replace_string VARCHAR2(2000);
1392 l_replace_value VARCHAR2(2000);
1393 l_application_column_name VARCHAR2(2000);
1394 l_no_replace_value VARCHAR2(2) :='N';
1395 l_proc varchar2(72);
1396 begin
1397 --
1398 -- Start big loop for replacing the values
1399 -- We shouldn't expect any value to appear only once
1400 --
1401 --
1402 g_debug := hr_utility.debug_enabled;
1403 IF g_debug THEN
1404 l_proc := g_package||'build_sql_text';
1405 hr_utility.set_location('Entering:'|| l_proc, 10);
1406 END IF;
1407
1408 --
1409 if(instr(p_sql_text,g_app_col_placeholder)>0) then
1410
1411 LOOP
1412 --
1413 -- Find the instance of a placeholder in the text
1414 -- Use this to work out which column name needs replacing
1415 --
1416 l_placeholder := INSTR(p_sql_text,g_app_col_placeholder);
1417
1418 EXIT WHEN ((l_placeholder < 1) OR (l_no_replace_value='Y'));
1419 --
1420
1421 IF g_debug THEN
1422 hr_utility.set_location(l_proc, 20);
1423 END IF;
1424
1425 --
1426 -- Need to update this to take account of carriage returns etc.
1427 --
1428 l_end_placeholder := INSTR(p_sql_text,' ',l_placeholder);
1429 if (l_end_placeholder > 0) then
1430 l_replace_string := substr(p_sql_text,l_placeholder,(l_end_placeholder -
1431 l_placeholder));
1432 else
1433 l_replace_string := substr(p_sql_text,l_placeholder);
1434 end if;
1435
1436 l_application_column_name := substr(l_replace_string,(l_placeholder_string_length+1));
1437
1438 --
1439 -- Obtain type of application column name. Do this because in order
1440 -- for the SQL to run correctly, we must change the way we add the
1441 -- value into the string.
1442 -- The default type is taken as varchar2. The package will not generate
1443 -- a when no data found error.
1444 --
1445 BEGIN
1446 select c.column_type into l_column_type
1447 from fnd_application a,
1448 fnd_tables t,
1449 fnd_columns c
1450 where c.column_name = l_application_column_name
1451 and c.table_id = t.table_id
1452 and c.application_id = a.application_id
1453 and t.application_id = a.application_id
1454 and t.table_name = p_application_table_name
1455 and a.application_short_name = p_application_short_name;
1456 EXCEPTION
1457 WHEN NO_DATA_FOUND THEN
1458 hr_utility.trace(' Exception in hr_flexfield_info.build_sql_text ' || SQLERRM );
1459 hr_utility.trace(' Table Name ' || p_application_table_name );
1460 hr_utility.trace(' Column Name ' || l_application_column_name );
1461 --
1462
1463 IF g_debug THEN
1464 hr_utility.set_location(l_proc, 30);
1465 END IF;
1466
1467 --
1468 p_sql_text := 'FAILURE: Column '||l_application_column_name||
1469 ' is not present in FND_COLUMNS. Unknown '||
1470 'column type. Please specify.';
1471
1472 raise;
1473 END;
1474 --
1475 -- Test for, and replace the existing bind variable with
1476 -- value sent to procedure
1477 --
1478 --
1479
1480 IF g_debug THEN
1481 hr_utility.set_location(l_proc, 40);
1482 END IF;
1483
1484 --
1485 if (l_application_column_name = p_column_name1) then
1486 if (l_column_type = 'N') then
1487 l_replace_value := p_column_value1;
1488 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1489 else
1490 l_replace_value := ''''||p_column_value1||'''';
1491 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1492 end if;
1493 elsif (l_application_column_name = p_column_name2) then
1494 if (l_column_type = 'N') then
1495 l_replace_value := p_column_value2;
1496 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1497 else
1498 l_replace_value := ''''||p_column_value2||'''';
1499 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1500 end if;
1501 elsif (l_application_column_name = p_column_name3) then
1502 if (l_column_type = 'N') then
1503 l_replace_value := p_column_value3;
1504 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1505 else
1506 l_replace_value := ''''||p_column_value3||'''';
1507 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1508 end if;
1509 elsif (l_application_column_name = p_column_name4) then
1510 if (l_column_type = 'N') then
1511 l_replace_value := p_column_value4;
1512 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1513 else
1514 l_replace_value := ''''||p_column_value4||'''';
1515 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1516 end if;
1517 elsif (l_application_column_name = p_column_name5) then
1518 if (l_column_type = 'N') then
1519 l_replace_value := p_column_value5;
1520 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1521 else
1522 l_replace_value := ''''||p_column_value5||'''';
1523 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1524 end if;
1525 elsif (l_application_column_name = p_column_name6) then
1526 if (l_column_type = 'N') then
1527 l_replace_value := p_column_value6;
1528 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1529 else
1530 l_replace_value := ''''||p_column_value6||'''';
1531 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1532 end if;
1533 elsif (l_application_column_name = p_column_name7) then
1534 if (l_column_type = 'N') then
1535 l_replace_value := p_column_value7;
1536 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1537 else
1538 l_replace_value := ''''||p_column_value7||'''';
1539 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1540 end if;
1541 elsif (l_application_column_name = p_column_name8) then
1542 if (l_column_type = 'N') then
1543 l_replace_value := p_column_value8;
1544 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1545 else
1546 l_replace_value := ''''||p_column_value8||'''';
1547 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1548 end if;
1549 elsif (l_application_column_name = p_column_name9) then
1550 if (l_column_type = 'N') then
1551 l_replace_value := p_column_value9;
1552 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1553 else
1554 l_replace_value := ''''||p_column_value9||'''';
1555 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1556 end if;
1557 elsif (l_application_column_name = p_column_name10) then
1558 if (l_column_type = 'N') then
1559 l_replace_value := p_column_value10;
1560 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1561 else
1562 l_replace_value := ''''||p_column_value10||'''';
1563 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1564 end if;
1565 elsif (l_application_column_name = p_column_name11) then
1566 if (l_column_type = 'N') then
1567 l_replace_value := p_column_value11;
1568 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1569 else
1570 l_replace_value := ''''||p_column_value11||'''';
1571 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1572 end if;
1573 elsif (l_application_column_name = p_column_name12) then
1574 if (l_column_type = 'N') then
1575 l_replace_value := p_column_value12;
1576 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1577 else
1578 l_replace_value := ''''||p_column_value12||'''';
1579 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1580 end if;
1581 elsif (l_application_column_name = p_column_name13) then
1582 if (l_column_type = 'N') then
1583 l_replace_value := p_column_value13;
1584 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1585 else
1586 l_replace_value := ''''||p_column_value13||'''';
1587 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1588 end if;
1589 elsif (l_application_column_name = p_column_name14) then
1590 if (l_column_type = 'N') then
1591 l_replace_value := p_column_value14;
1592 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1593 else
1594 l_replace_value := ''''||p_column_value14||'''';
1595 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1596 end if;
1597 elsif (l_application_column_name = p_column_name15) then
1598 if (l_column_type = 'N') then
1599 l_replace_value := p_column_value15;
1600 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1601 else
1602 l_replace_value := ''''||p_column_value15||'''';
1603 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1604 end if;
1605 elsif (l_application_column_name = p_column_name16) then
1606 if (l_column_type = 'N') then
1607 l_replace_value := p_column_value16;
1608 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1609 else
1610 l_replace_value := ''''||p_column_value16||'''';
1611 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1612 end if;
1613 elsif (l_application_column_name = p_column_name17) then
1614 if (l_column_type = 'N') then
1615 l_replace_value := p_column_value17;
1616 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1617 else
1618 l_replace_value := ''''||p_column_value17||'''';
1619 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1620 end if;
1621 elsif (l_application_column_name = p_column_name18) then
1622 if (l_column_type = 'N') then
1623 l_replace_value := p_column_value18;
1624 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1625 else
1626 l_replace_value := ''''||p_column_value18||'''';
1627 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1628 end if;
1629 elsif (l_application_column_name = p_column_name19) then
1630 if (l_column_type = 'N') then
1631 l_replace_value := p_column_value19;
1632 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1633 else
1634 l_replace_value := ''''||p_column_value19||'''';
1635 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1636 end if;
1637 elsif (l_application_column_name = p_column_name20) then
1638 if (l_column_type = 'N') then
1639 l_replace_value := p_column_value20;
1640 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1641 else
1642 l_replace_value := ''''||p_column_value20||'''';
1643 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1644 end if;
1645 elsif (l_application_column_name = p_column_name21) then
1646 if (l_column_type = 'N') then
1647 l_replace_value := p_column_value21;
1648 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1649 else
1650 l_replace_value := ''''||p_column_value21||'''';
1651 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1652 end if;
1653 elsif (l_application_column_name = p_column_name22) then
1654 if (l_column_type = 'N') then
1655 l_replace_value := p_column_value22;
1656 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1657 else
1658 l_replace_value := ''''||p_column_value22||'''';
1659 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1660 end if;
1661 elsif (l_application_column_name = p_column_name23) then
1662 if (l_column_type = 'N') then
1663 l_replace_value := p_column_value23;
1664 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1665 else
1666 l_replace_value := ''''||p_column_value23||'''';
1667 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1668 end if;
1669 elsif (l_application_column_name = p_column_name24) then
1670 if (l_column_type = 'N') then
1671 l_replace_value := p_column_value24;
1672 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1673 else
1674 l_replace_value := ''''||p_column_value24||'''';
1675 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1676 end if;
1677 elsif (l_application_column_name = p_column_name25) then
1678 if (l_column_type = 'N') then
1679 l_replace_value := p_column_value25;
1680 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1681 else
1682 l_replace_value := ''''||p_column_value25||'''';
1683 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1684 end if;
1685 elsif (l_application_column_name = p_column_name26) then
1686 if (l_column_type = 'N') then
1687 l_replace_value := p_column_value26;
1688 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1689 else
1690 l_replace_value := ''''||p_column_value26||'''';
1691 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1692 end if;
1693 elsif (l_application_column_name = p_column_name27) then
1694 if (l_column_type = 'N') then
1695 l_replace_value := p_column_value27;
1696 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1697 else
1698 l_replace_value := ''''||p_column_value27||'''';
1699 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1700 end if;
1701 elsif (l_application_column_name = p_column_name28) then
1702 if (l_column_type = 'N') then
1703 l_replace_value := p_column_value28;
1704 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1705 else
1706 l_replace_value := ''''||p_column_value28||'''';
1707 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1708 end if;
1709 elsif (l_application_column_name = p_column_name29) then
1710 if (l_column_type = 'N') then
1711 l_replace_value := p_column_value29;
1712 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1713 else
1714 l_replace_value := ''''||p_column_value29||'''';
1715 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1716 end if;
1717 elsif (l_application_column_name = p_column_name30) then
1718 if (l_column_type = 'N') then
1719 l_replace_value := p_column_value30;
1720 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1721 else
1722 l_replace_value := ''''||p_column_value30||'''';
1723 p_sql_text := replace(p_sql_text,l_replace_string,l_replace_value);
1724 end if;
1725 else
1726 -- There is a missing value - i.e. this procedure can not complete
1727 -- its task. Reset the SQL text with this information, and exit
1728 -- the loop. This is necessary because the loop will continue
1729 -- idenfinitely if this flag is not set.
1730 p_sql_text := 'FAILURE (HR_FLEXFIELD_INFO.BUILD SQL TEXT):'||c_newline||
1731 'There was no supplied value for the column'||
1732 l_application_column_name;
1733 l_no_replace_value:='Y';
1734 end if;
1735
1736 END LOOP;
1737
1738 end if;
1739
1740 --
1741
1742 IF g_debug THEN
1743 hr_utility.set_location('Leaving:'|| l_proc, 50);
1744 END IF;
1745
1746 --
1747 end build_sql_text;
1748 -- ----------------------------------------------------------------------------
1749 -- |-------------------< get_kf_concatenated_structures >---------------------|
1750 -- ----------------------------------------------------------------------------
1751 PROCEDURE get_kf_concatenated_structures
1752 (p_appl_short_name IN fnd_application.application_short_name%TYPE
1753 ,p_id_flex_code IN fnd_id_flex_structures_vl.id_flex_code%TYPE
1754 ,p_enabled_only IN BOOLEAN
1755 ,p_concatenation_chr IN VARCHAR2
1756 ,p_structure_list OUT NOCOPY LONG
1757 ) is
1758 l_flexfield fnd_flex_key_api.flexfield_type;
1759 l_nstructures number;
1760 l_id_flex_num_list fnd_flex_key_api.structure_list;
1761 l_structure_list long;
1762 l_structure fnd_flex_key_api.structure_type;
1763 --
1764 i binary_integer;
1765 l_proc varchar2(72);
1766 begin
1767 g_debug := hr_utility.debug_enabled;
1768 p_structure_list := null;
1769 --
1770 -- Get the flexfield.
1771 --
1772
1773 IF g_debug THEN
1774 l_proc := g_package||'get_kf_concatenated_structures';
1775 hr_utility.set_location('Entering:'|| l_proc, 5);
1776 END IF;
1777
1778 fnd_flex_key_api.set_session_mode(session_mode => 'seed_data');
1779 l_flexfield :=
1780 fnd_flex_key_api.find_flexfield
1781 (appl_short_name => p_appl_short_name
1782 ,flex_code => p_id_flex_code
1783 );
1784 --
1785 -- Get the flexfield structures.
1786 --
1787
1788 IF g_debug THEN
1789 hr_utility.set_location(l_proc, 10);
1790 END IF;
1791
1792 fnd_flex_key_api.get_structures
1793 (flexfield => l_flexfield
1794 ,enabled_only => p_enabled_only
1795 ,nstructures => l_nstructures
1796 ,structures => l_id_flex_num_list
1797 );
1798 --
1799 -- Concatenate the structures.
1800 --
1801 i := l_id_flex_num_list.first;
1802 loop
1803 exit when not l_id_flex_num_list.exists(i);
1804 --
1805 -- fnd_flex_key_api.get_structures returns a table of id_flex_num
1806 -- values. These values must be converted to translated structure
1807 -- names.
1808 --
1809 l_structure :=
1810 fnd_flex_key_api.find_structure
1811 (flexfield => l_flexfield
1812 ,structure_number => l_id_flex_num_list(i)
1813 );
1814 --
1815 -- Add to the the list.
1816 --
1817 l_structure_list :=
1818 l_structure_list || p_concatenation_chr || l_structure.structure_name;
1819 --
1820 i := l_id_flex_num_list.next(i);
1821 end loop;
1822 --
1823 p_structure_list := ltrim(l_structure_list,p_concatenation_chr);
1824 --
1825
1826 IF g_debug THEN
1827 hr_utility.set_location(' Leaving:'||l_proc, 15);
1828 END IF;
1829
1830 end get_kf_concatenated_structures;
1831 --
1832 -- ----------------------------------------------------------------------------
1833 -- |---------------------------< get_kf_segments >----------------------------|
1834 -- ----------------------------------------------------------------------------
1835 --
1836 PROCEDURE get_kf_segments
1837 (p_appl_short_name IN fnd_application.application_short_name%TYPE
1838 ,p_id_flex_code IN fnd_id_flex_structures_vl.id_flex_code%TYPE
1839 ,p_id_flex_structure_name IN
1840 fnd_id_flex_structures_vl.id_flex_structure_name%TYPE
1841 ,p_enabled_only IN BOOLEAN
1842 ,p_segments OUT NOCOPY hr_segments_info
1843 ,p_session_date IN DATE
1844 ) is
1845 --
1846 -- Declare local variables.
1847 --
1848 l_segments hr_segments_info;
1849 l_flexfield fnd_flex_key_api.flexfield_type;
1850 l_segment_list fnd_flex_key_api.segment_list;
1851 l_segment_info fnd_flex_key_api.segment_type;
1852 l_nsegments number;
1853 l_structure fnd_flex_key_api.structure_type;
1854 l_structure_code fnd_id_flex_structures.id_flex_structure_code%TYPE;
1855 l_total_parents number :=0;
1856 --
1857 i number;
1858 l_proc varchar2(72);
1859 begin
1860 --
1861 g_debug := hr_utility.debug_enabled;
1862 IF g_debug THEN
1863 l_proc := g_package||'get_kf_segments';
1864 hr_utility.set_location('Entering:'|| l_proc, 5);
1865 END IF;
1866
1867 --
1868 -- Get the flexfield.
1869 --
1870 fnd_flex_key_api.set_session_mode(session_mode => 'seed_data');
1871 l_flexfield :=
1872 fnd_flex_key_api.find_flexfield
1873 (appl_short_name => p_appl_short_name
1874 ,flex_code => p_id_flex_code
1875 );
1876 --
1877 -- Get the flexfield structure.
1878 --
1879
1880
1881 IF g_debug THEN
1882 hr_utility.set_location(l_proc, 10);
1883 END IF;
1884
1885
1886 /*
1887 l_structure :=
1888 fnd_flex_key_api.find_structure
1889 (flexfield => l_flexfield
1890 --,structure_title => p_id_flex_structure_name
1891 ,structure_code => upper(replace(p_id_flex_structure_name,' ','_'))
1892 );
1893 */
1894 -- fix for bug 1259164
1895 SELECT id_flex_structure_code
1896 INTO l_structure_code
1897 FROM fnd_id_flex_structures_vl
1898 WHERE application_id = l_flexfield.application_id
1899 AND id_flex_code = l_flexfield.flex_code
1900 AND id_flex_structure_name = p_id_flex_structure_name;
1901
1902 l_structure :=
1903 fnd_flex_key_api.find_structure
1904 (flexfield => l_flexfield
1905 ,structure_code => l_structure_code);
1906
1907
1908 --
1909 -- Now, given the flexfield information returned
1910 -- above, find the segment information
1911 --
1912
1913 IF g_debug THEN
1914 hr_utility.set_location(l_proc, 20);
1915 END IF;
1916
1917 fnd_flex_key_api.get_segments
1918 (flexfield => l_flexfield
1919 ,structure => l_structure
1920 ,enabled_only => p_enabled_only
1921 ,nsegments => l_nsegments
1922 ,segments => l_segment_list
1923 );
1924 --
1925 -- For each of the segments returned, copy the information
1926 -- returned into the HR structure.
1927 --
1928 l_segments.nsegments := l_nsegments;
1929 i := l_segment_list.first;
1930 loop
1931
1932 IF g_debug THEN
1933 hr_utility.set_location(l_proc, 20);
1934 END IF;
1935
1936 exit when not l_segment_list.exists(i);
1937 --
1938 -- For each segment get the segment information.
1939 --
1940
1941 IF g_debug THEN
1942 hr_utility.set_location(l_proc, 30);
1943 END IF;
1944
1945 l_segment_info :=
1946 fnd_flex_key_api.find_segment
1947 (flexfield => l_flexfield
1948 ,structure => l_structure
1949 ,segment_name => l_segment_list(i)
1950 );
1951 --
1952 -- Set up the returned segment information.
1953 --
1954
1955 IF g_debug THEN
1956 hr_utility.set_location(l_proc, 40);
1957 END IF;
1958
1959 l_segments.application_column_name(i) := l_segment_info.column_name;
1960 l_segments.segment_name(i) := l_segment_list(i);
1961 l_segments.sequence(i) := l_segment_info.segment_number;
1962 l_segments.is_displayed(i) := upper(l_segment_info.displayed_flag) = 'Y';
1963 l_segments.display_size(i) := l_segment_info.display_size;
1964 l_segments.row_prompt(i) := l_segment_info.window_prompt;
1965 l_segments.column_prompt(i) := l_segment_info.lov_prompt;
1966 l_segments.is_enabled(i) := upper(l_segment_info.enabled_flag) = 'Y';
1967 l_segments.is_required(i) := upper(l_segment_info.required_flag) = 'Y';
1968 l_segments.description(i) := l_segment_info.description;
1969 l_segments.value_set(i) := l_segment_info.value_set_id;
1970 l_segments.default_type(i) := l_segment_info.default_type;
1971 l_segments.default_value(i) := l_segment_info.default_value;
1972 l_segments.number_children(i) := 0;
1973 l_segments.ak_region_code(i) :=
1974 hr_flexfield_info.get_ak_region_code
1975 (p_flex_type => 'KEY'
1976 ,p_flexfield_name => p_id_flex_code
1977 ,p_context_code => to_char(l_structure.structure_number)
1978 ,p_segment_info => l_segments
1979 ,p_segment_number => i
1980 );
1981 --
1982 i := l_segment_list.next(i);
1983 end loop;
1984 --
1985 -- Pass context_code and flexfield_name as null to get_validation_info
1986 -- because they're only required for a descriptive flexfield hack.
1987 --
1988 -- Exception for Key Flex PEA:
1989 -- For bug 2462181 we need to pass flex name as that is the only way
1990 -- to know if it is Personal Analysis flex (PEA)
1991 --
1992
1993 IF g_debug THEN
1994 hr_utility.set_location(l_proc, 50);
1995 END IF;
1996
1997 hr_flexfield_info.get_validation_info
1998 (p_segment_info => l_segments
1999 ,p_session_date => p_session_date
2000 ,p_context_code => null
2001 ,p_flexfield_name => p_id_flex_code
2002 ,p_flex_type => 'KEY'
2003 );
2004 --
2005 -- Pass out the segment information.
2006 --
2007 p_segments := l_segments;
2008
2009 IF g_debug THEN
2010 hr_utility.set_location(l_proc, 60);
2011 END IF;
2012
2013 end get_kf_segments;
2014 --
2015 -- ----------------------------------------------------------------------------
2016 -- |------------------------< gen_ak_web_region_code >------------------------|
2017 -- ----------------------------------------------------------------------------
2018 --
2019 function gen_ak_web_region_code
2020 (p_flex_type in varchar2
2021 ,p_flexfield in varchar2
2022 ,p_context_or_id_flex_num in varchar2
2023 ,p_segment in varchar2
2024 ) return varchar2 is
2025 l_lookup_code fnd_common_lookups.lookup_code%type;
2026 begin
2027 if p_flex_type <> 'KEY' then
2028 return substr(p_flexfield, 1, 5) ||
2029 substr(p_context_or_id_flex_num, 1, 4) ||
2030 substr(p_segment, 1, 20);
2031 else
2032 return p_flexfield||
2033 substr(p_context_or_id_flex_num, 1, 5) ||
2034 substr(p_segment, 1, 20);
2035 end if;
2036 end;
2037 --
2038 end hr_flexfield_info;