[Home] [Help]
PACKAGE BODY: APPS.HR_KFLEX_UTILITY
Source
1 Package Body hr_kflex_utility as
2 /* $Header: hrkfutil.pkb 120.2.12020000.4 2012/11/21 10:19:01 lbodired ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := 'hr_kflex_utility';
7 g_kfcode_structure l_ignore_kfcode_varray := l_ignore_kfcode_varray();
8 g_delimiter_escape varchar2(1) := '\';
9 g_debug boolean := hr_utility.debug_enabled;
10 --
11 -- ----------------------------------------------------------------------------
12 -- |---------------------------- get_application_id ---------------------------|
13 -- ----------------------------------------------------------------------------
14 --
15 -- {Start Of Comments}
16 -- Bug fix 2148839
17 -- Description:
18 -- This private function returns the application id for the application short
19 -- name passed.
20 --
21 -- Prerequisites:
22 -- A valid application short name.
23 --
24 -- In Parameters:
25 -- Name Reqd Type Description
26 -- ==== ==== ==== ===========
27 -- p_appl_short_name Yes Varchar Application short name.
28 --
29 -- Post Success:
30 -- Application Is will be retunred.
31 --
32 -- Post Failure:
33 -- No error.
34 --
35 -- Access Status:
36 -- Private.
37 --
38 -- {End Of Comments}
39 --
40 --
41 Function get_application_id(
42 p_appl_short_name in fnd_application.application_short_name%TYPE)
43 return number is
44 cursor get_appln_id is
45 select application_id
46 from fnd_application
47 where application_short_name = p_appl_short_name;
48 l_application_id fnd_application.application_id%type;
49
50 begin
51 open get_appln_id;
52 fetch get_appln_id into l_application_id;
53 close get_appln_id;
54 return l_application_id;
55 end get_application_id;
56 --
57 -- ----------------------------------------------------------------------------
58 -- |---------------------------- get_no_segments -----------------------------|
59 -- ----------------------------------------------------------------------------
60 --
61 -- {Start Of Comments}
62 --
63 -- Description:
64 -- This private function returns the number of enabled segments within a
65 -- given flexfield structure.
66 --
67 -- Prerequisites:
68 -- A valid id_flex_code (flex structure code)
69 -- A valid id_flex_num (flex structure number)
70 --
71 -- In Parameters:
72 -- Name Reqd Type Description
73 -- ==== ==== ==== ===========
74 -- p_flex_num Yes number The structure number for the Bank
75 -- Details key flexfield.
76 -- p_flex_code Yes varchar2 The id_flex_code of the relevant key
77 -- flexfield structure
78 --
79 -- Post Success:
80 -- The number of valid segments for a given flex structure is returned.
81 --
82 -- Post Failure:
83 -- The process raises an error and stops execution.
84 --
85 -- Access Status:
86 -- Private.
87 --
88 -- {End Of Comments}
89 --
90 --
91 FUNCTION get_no_segments
92 (p_flex_num number
93 ,p_flex_code fnd_id_flex_segments.id_flex_code%TYPE
94 -- Bug fix 2148839.
95 ,p_application_id IN number
96 )
97 RETURN NUMBER
98 is
99 --
100 l_no_segments number;
101 l_proc varchar2(72);
102 --
103 -- Bug fix 2148839.
104 -- Application id added to the cursor to improve
105 -- performance.
106
107 cursor number_of_segments is
108 select count(*)
109 from fnd_id_flex_segments
110 where id_flex_code = p_flex_code
111 and id_flex_num = p_flex_num
112 and application_id = p_application_id
113 and enabled_flag = 'Y';
114 --
115 begin
116 if g_debug then
117 l_proc := 'get_no_segments';
118 hr_utility.set_location('Entering:'|| l_proc, 10);
119 end if;
120 --
121 -- Fetch number of segments in KF structure
122 --
123 open number_of_segments;
124 fetch number_of_segments into l_no_segments;
125 -- bug fix 2148839.
126 -- If condition below is commented. Since the
127 -- count will always return a row.
128 /* if number_of_segments%NOTFOUND then
129 close number_of_segments;
130 --
131 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
132 hr_utility.set_message_token('PROCEDURE', l_proc);
133 hr_utility.set_message_token('STEP','20');
134 hr_utility.raise_error;
135 --
136 end if;*/
137 close number_of_segments;
138 RETURN l_no_segments;
139 --
140 if g_debug then
141 hr_utility.set_location('Leaving:'|| l_proc, 30);
142 end if;
143 --
144 end get_no_segments;
145 --
146 -- ----------------------------------------------------------------------------
147 -- |------------------------< find_error_segment >-------------------|
148 -- ----------------------------------------------------------------------------
149 --
150 procedure find_error_segment(p_appl_short_name IN varchar2,
151 p_flex_code IN varchar2,
152 p_flex_num IN number,
153 p_error_seg_num IN number,
154 p_application_col_name OUT NOCOPY varchar2,
155 p_form_left_prompt OUT NOCOPY varchar2,
156 p_table_name OUT NOCOPY varchar2
157 )is
158 l_proc varchar2(72);
159 l_flexfield fnd_flex_key_api.flexfield_type;
160 l_structure fnd_flex_key_api.structure_type;
161 l_segment fnd_flex_key_api.segment_type;
162 l_segment_list fnd_flex_key_api.segment_list;
163 l_nsegments number;
164 l_error_segment_name fnd_id_flex_segments.segment_name%TYPE;
165
166 begin
167
168 -- Obtain the application column name corresponding to the error segment
169 -- in an AOL supported way. It would be much faster to perform a direct
170 -- access on the table FND_DESCR_FLEX_COLUMN_USAGES but this SQL would
171 -- not be supported by AOL.
172 --
173 -- First get the flexfield information
174 --
175 if g_debug then
176 l_proc := 'hr_kflex_utility.find_error_segment';
177 hr_utility.set_location('Entering:'|| l_proc, 10);
178 end if;
179 l_flexfield := fnd_flex_key_api.find_flexfield(
180 appl_short_name => p_appl_short_name,
181 flex_code => p_flex_code);
182
183 --
184 -- Use l_flexfield in calls that follow to identify the flexfield.
185 -- Next check that the context is valid, otherwise return the context
186 -- column name and prompt
187 --
188 if g_debug then
189 hr_utility.set_location(l_proc, 20);
190 end if;
191 --
192 if (p_error_seg_num = 0) then
193 --
194 -- The context is in error. We should return the context column
195 -- and prompt.
196 --
197 if g_debug then
198 hr_utility.set_location(l_proc, 30);
199 end if;
200 --
201 p_application_col_name := l_flexfield.structure_column;
202 p_form_left_prompt := p_flex_code||' - '||to_char(p_flex_num);
203 p_table_name := l_flexfield.table_name;
204 --
205 else
206 --
207 -- We have a valid context, so get the context information
208 --
209 if g_debug then
210 hr_utility.set_location(l_proc, 40);
211 end if;
212 --
213 l_structure := fnd_flex_key_api.find_structure(
214 flexfield => l_flexfield,
215 structure_number => p_flex_num);
216 --
217 -- Finally retrieve the segment information for this context
218 --
219 if g_debug then
220 hr_utility.set_location(l_proc, 50);
221 end if;
222 --
223 -- Get the list of segments for this structure
224 --
225 fnd_flex_key_api.get_segments(
226 flexfield => l_flexfield,
227 structure => l_structure,
228 enabled_only => TRUE,
229 nsegments => l_nsegments,
230 segments => l_segment_list);
231 --
232 if g_debug then
233 hr_utility.set_location(l_proc, 55);
234 end if;
235 --
236 -- Use the error segment number passed in, to work out
237 -- which segment is in error.
238 --
239 l_error_segment_name := l_segment_list(p_error_seg_num);
240 --
241 -- Use that name to find out information about the segment
242 --
243 l_segment:= fnd_flex_key_api.find_segment(
244 flexfield => l_flexfield,
245 structure => l_structure,
246 segment_name => l_error_segment_name);
247 --
248 -- Next retrieve the application column name corresponding to the segment
249 -- in error. We know where this is in the table because the sequence is
250 -- used as the order key. This should be the same sequence as is used
251 -- inside the FND_FLEX_DESCVAL procedures.
252 --
253 p_application_col_name := l_segment.column_name;
254 p_form_left_prompt := l_segment.window_prompt;
255 p_table_name := l_flexfield.table_name;
256 --
257 end if;
258 --
259 if g_debug then
260 hr_utility.set_location('Leaving: '||l_proc, 60);
261 end if;
262 end find_error_segment;
263 --
264 -- ----------------------------------------------------------------------------
265 -- |------------------------ Check_segment_combination -----------------------|
266 -- ----------------------------------------------------------------------------
267 --
268 -- {Start Of Comments}
269 --
270 -- Description:
271 -- Concatenated string interface
272 --
273 -- This private procedure ensures that either a new flex combination is
274 -- created for the key flexfield selected or the relevant code combination
275 -- is found if the combination already exists.
276 --
277 -- Prerequisites:
278 -- A valid application short name
279 -- A valid id_flex_code (flex structure code)
280 -- A valid id_flex_num (flex structure number)
281 --
282 -- In Parameters:
283 -- Name Reqd Type Description
284 -- ==== ==== ==== ===========
285 -- p_appl_short_name Yes varchar2 The application shortname for the
286 -- application a key flexfield belongs
287 -- to (i.e. PER/PAY).
288 -- p_flex_num Yes number The id_flex_num of the relevant key
289 -- flexfield structure
290 -- p_flex_code Yes varchar2 The id_flex_code of the relevant key
291 -- flexfield structure
292 -- p_concat_segments Yes varchar2 Contains concatenated string of key
293 -- flexfield segment values in segment
294 -- display order
295 --
296 -- Post Success:
297 -- The procedure create a new code combination and passes back the new
298 -- CCID or finds an existing code combination and passes back the CCID.
299 --
300 -- Post Failure:
301 -- The process raises an AOL error and stops execution.
302 --
303 -- Access Status:
304 -- Private.
305 --
306 -- {End Of Comments}
307 --
308 procedure check_segment_combination
309 (p_appl_short_name in fnd_application.application_short_name%TYPE
310 ,p_flex_num in number
311 ,p_flex_code in fnd_id_flex_segments.id_flex_code%TYPE
312 ,p_concat_segments_in in varchar2
313 ,p_ccid out nocopy number
314 ,p_concat_segments_out out nocopy varchar2
315 ) is
316 --
317 l_proc varchar2(72);
318 error_seg number;
319 app_col_name fnd_columns.column_name%TYPE;
320 l_flex_seg_error_prompt fnd_id_flex_segments_vl.form_left_prompt%type;
321 value VARCHAR2(2000); -- Note: PL/SQL value errors may occur if
322 -- we increase the db size of key flex
323 -- segments.
324 l_table_name VARCHAR2(60);
325 -- start for fix of 3171225
326 l_effective_date date;
327
328 cursor date_set is
329 select EFFECTIVE_DATE from fnd_sessions
330 where session_id=userenv('sessionid');
331 -- end for fix of 3171225
332 --
333 begin
334 if g_debug then
335 l_proc := 'check_segment_combination';
336 hr_utility.set_location('Entering:'|| l_proc, 11);
337 end if;
338 --
339 --3171225 start
340 l_effective_date:=to_date(null);
341 open date_set;
342 fetch date_set into l_effective_date;
343 if date_set%notfound then
344 if g_debug then
345 hr_utility.set_location(l_proc,13);
346 end if;
347 l_effective_date := trunc(sysdate);
348 close date_set;
349 else
350 if g_debug then
351 hr_utility.set_location(l_proc,15);
352 end if;
353 close date_set;
354 end if;
355 --3171225 end
356 if fnd_flex_keyval.validate_segs
357 (operation => 'CREATE_COMBINATION'
358 ,appl_short_name => p_appl_short_name
359 ,key_flex_code => p_flex_code
360 ,structure_number => p_flex_num
361 ,concat_segments => p_concat_segments_in
362 ,values_or_ids => 'V'
363 ,validation_date => nvl(l_effective_date,trunc(sysdate)) --3171225
364 )
365 then
366 if g_debug then
367 hr_utility.set_location(l_proc, 20);
368 end if;
369 --
370 -- Flex routine succeeds - fetch relevant ccid
371 -- and concatenated segment string
372 --
373 p_ccid := fnd_flex_keyval.combination_id;
374 p_concat_segments_out := fnd_flex_keyval.concatenated_values;
375 --
376
377 if g_debug then
378 hr_utility.set_location(l_proc, 30);
379 end if;
380 else
381 if g_debug then
382 hr_utility.set_location(l_proc, 40);
383 end if;
384 --
385 -- Raise or trap the AOL error
386 --
387 if g_debug then
388 hr_utility.set_location(l_proc, 41);
389 end if;
390 --
391 -- The FND FLEX KEYVAL package makes a call to FND MESSAGE
392 -- get encoded
393 hr_message.parse_encoded(p_encoded_error =>
394 FND_FLEX_KEYVAL.encoded_error_message);
395 if g_debug then
396 hr_utility.set_location(l_proc, 42);
397 end if;
398 --
399 if ((hr_message.last_message_app = 'FND') and
400 (hr_message.last_message_name = 'FLEX-VALUE NOT FOUND')) then
401 --
402 if g_debug then
403 hr_utility.set_location(l_proc, 44);
404 end if;
405 --
406 -- In this case, there was an invalid segment, or context.
407 -- We must allow for the possibility that the context value
408 -- was not valid.
409 -- Should trap this error
410 -- and replace with a more user friendly message
411 -- First, work out which segment is in error, and change the
412 -- error message to indicate which of these segments is
413 -- is incorrect.
414 --
415 error_seg := FND_FLEX_KEYVAL.error_segment;
416 find_error_segment(p_appl_short_name => p_appl_short_name,
417 p_flex_code => p_flex_code,
418 p_flex_num => p_flex_num,
419 p_error_seg_num => error_seg,
420 p_application_col_name => app_col_name,
421 p_form_left_prompt => l_flex_seg_error_prompt,
422 p_table_name => l_table_name
423 );
424 if g_debug then
425 hr_utility.set_location(l_proc, 46);
426 end if;
427 value := hr_message.get_token_value(p_token_name => 'VALUE');
428 --
429 -- Now have the additional error information, raise a different error
430 -- using this information.
431 --
432 fnd_message.set_name('PER','HR_FLEX_VALUE_INVALID');
433 fnd_message.set_token('COLUMN',app_col_name);
434 fnd_message.set_token('VALUE',value);
435 fnd_message.set_token('PROMPT',l_flex_seg_error_prompt);
436 hr_multi_message.add
437 (p_associated_column1 => l_table_name || '.' || app_col_name);
438 elsif hr_message.last_message_app = 'FND' and
439 hr_message.last_message_name = 'FLEX-NULL SEGMENT' then
440 if g_debug then
441 hr_utility.set_location(l_proc, 50);
442 end if;
443 --
444 -- Work out which segment is in error. Replace the error message
445 -- by a more useful one.
446 --
447 error_seg := FND_FLEX_KEYVAL.error_segment;
448 find_error_segment
449 (p_appl_short_name => p_appl_short_name
450 ,p_flex_code => p_flex_code
451 ,p_flex_num => p_flex_num
452 ,p_error_seg_num => error_seg
453 ,p_application_col_name => app_col_name
454 ,p_form_left_prompt => l_flex_seg_error_prompt
455 ,p_table_name => l_table_name
456 );
457 --
458 -- Raise the alternative error.
459 --
460 if g_debug then
461 hr_utility.set_location(l_proc, 55);
462 end if;
463 fnd_message.set_name('PER','HR_FLEX_VALUE_MISSING');
464 fnd_message.set_token('COLUMN',app_col_name);
465 fnd_message.set_token('PROMPT',l_flex_seg_error_prompt);
466 hr_multi_message.add
467 (p_associated_column1 => l_table_name || '.' || app_col_name);
468 else
469 --
470 -- Some other non-expected error, simply raise the error in the
471 -- previous fashion.
472 --
473 if g_debug then
474 hr_utility.set_location(l_proc, 60);
475 end if;
476 --
477 -- Raise the AOL error
478 --
479 fnd_message.set_name('FND',hr_message.last_message_name);
480 fnd_message.raise_error;
481 --
482 end if;
483 end if;
484 --
485 if g_debug then
486 hr_utility.set_location(' Leaving '||l_proc, 100);
487 end if;
488 --
489 end check_segment_combination;
490 --
491 -- ----------------------------------------------------------------------------
492 -- |------------------------ Check_segment_combination -----------------------|
493 -- ----------------------------------------------------------------------------
494 --
495 -- {Start Of Comments}
496 --
497 -- Description:
498 -- Parameter interface Version
499 --
500 -- This private procedure ensures that either a new flex combination is
501 -- created for the key flexfield selected or the relevant code combination
502 -- is found if the combination already exists.
503 --
504 -- Prerequisites:
505 -- A valid application short name
506 -- A valid id_flex_code (flex structure code)
507 -- A valid id_flex_num (flex structure number)
508 --
509 -- In Parameters:
510 -- Name Reqd Type Description
511 -- ==== ==== ==== ===========
512 -- p_appl_short_name Yes varchar2 The application shortname for the
513 -- application a key flexfield belongs
514 -- to (i.e. PER/PAY).
515 -- p_flex_num Yes number The id_flex_num of the relevant key
516 -- flexfield structure
517 -- p_flex_code Yes varchar2 The id_flex_code of the relevant key
518 -- flexfield structure
519 -- p_parameter Yes PL*SQL The PLSQL table which stores the
520 -- table segment array for the flex segment
521 -- values
522 -- p_too_many_segs Yes boolean Used to verify whether too many segments
523 -- have been passed in through the
524 -- parameter interface
525 --
526 --
527 -- Post Success:
528 -- The procedure create a new code combination and passes back the new
529 -- CCID or finds an existing code combination and passes back the CCID.
530 --
531 -- Post Failure:
532 -- The process raises an AOL error and stops execution.
533 --
534 -- Access Status:
535 -- Private.
536 --
537 -- {End Of Comments}
538 --
539 procedure check_segment_combination
540 (p_appl_short_name in fnd_application.application_short_name%TYPE
541 ,p_flex_num in number
542 ,p_flex_code in fnd_id_flex_segments.id_flex_code%TYPE
543 ,p_parameter in fnd_flex_ext.SegmentArray
544 ,p_too_many_segs in boolean
545 ,p_ccid out nocopy number
546 ,p_concat_segments_out out nocopy varchar2
547 )
548 is
549 --
550 -- Declare cursors and local variables
551 --
552 l_proc varchar2(72);
553 --
554 l_seg_plsql_row_num binary_integer;
555 l_parameter_temp fnd_flex_ext.SegmentArray;
556 l_delimiter varchar2(1);
557 l_concat_string varchar2(2000);
558 l_segment_value varchar2(2000);
559 l_first_seg boolean;
560 error_seg number;
561 app_col_name fnd_columns.column_name%TYPE;
562 l_flex_seg_error_prompt fnd_id_flex_segments_vl.form_left_prompt%type;
563 value VARCHAR2(2000); -- Note: PL/SQL value errors may occur if
564 -- we increase the db size of key flex
565 -- segments.
566 l_table_name VARCHAR2(60);
567 -- start for fix of 3171225
568 l_effective_date date;
569
570 cursor date_set is
571 select EFFECTIVE_DATE from fnd_sessions
572 where session_id=userenv('sessionid');
573 -- end for fix of 3171225
574 --
575 -- Bug 3897294 Starts
576 l_no_segments number;
577 l_application_id number;
578 -- Bug 3897294 Ends
579 begin
580 if g_debug then
581 l_proc := 'check_segment_combination';
582 hr_utility.set_location('Entering:'|| l_proc, 10);
583 end if;
584 --
585 -- Calls function to return delimeter for relevant flexfield
586 -- structure
587 --
588 l_delimiter := fnd_flex_ext.get_delimiter
589 (p_appl_short_name
590 ,p_flex_code
591 ,p_flex_num
592 );
593 --
594 -- Bug 3897294 Starts
595 l_application_id := get_application_id( p_appl_short_name => p_appl_short_name );
596 --
597 l_no_segments := get_no_segments
598 (p_flex_num
599 ,p_flex_code
600 ,l_application_id
601 );
602 -- Bug 3897294 Ends
603 --
604 if g_debug then
605 hr_utility.set_location(l_proc, 20);
606 end if;
607 --
608 -- Convert segment array structure into concatenated string
609 --
610 -- Check if any segment values are populated
611 --
612 if p_parameter.count > 0 then
613 if g_debug then
614 hr_utility.set_location(l_proc, 30);
615 end if;
616
617 -- Initialize the concatenated sring
618 --
619 l_concat_string := null;
620 l_first_seg := TRUE;
621 --
622 -- Loop through all segment values
623 --
624 for l_seg_plsql_row_num in p_parameter.first .. p_parameter.last loop
625 --
626 -- Assign the segment value to a local
627 --
628 l_segment_value := p_parameter(l_seg_plsql_row_num);
629 --
630 if g_debug then
631 hr_utility.set_location('This is the seg val'||length(l_segment_value),100);
632 end if;
633 if l_segment_value <> hr_api.g_varchar2
634 or l_segment_value is null
635 then
636 --
637 -- Check if the segment value is not set
638 --
639 if l_segment_value is null then
640
641 -- Set segment value to null indicator
642 --
643 l_segment_value := '';
644 --
645 end if;
646
647 /* Fix For Bug # 8357649 Starts*/
648
649 l_segment_value := replace(l_segment_value,g_delimiter_escape,g_delimiter_escape||g_delimiter_escape);
650
651 /* Fix For Bug # 8357649 Ends*/
652
653 --
654 -- Check for the first segment value
655 --
656 -- 3897294 Starts
657 --
658 -- Check for the first segment value
659 --
660 if l_first_seg then
661 If l_no_segments = 1 then
662 l_concat_string := l_segment_value;
663 else
664 l_concat_string := replace(l_segment_value,l_delimiter,
665 g_delimiter_escape||l_delimiter);
666 end if;
667 l_first_seg := FALSE;
668 --
669 else
670 l_concat_string := l_concat_string||l_delimiter||
671 replace(l_segment_value,l_delimiter,
672 g_delimiter_escape||l_delimiter);
673 end if;
674 -- 3897294 Ends
675 --
676 end if;
677 --
678 end loop;
679 --
680 end if;
681 --
682 if g_debug then
683 hr_utility.set_location(l_proc, 45);
684 end if;
685 --
686 -- Check for too many segments in segment structure.
687 -- If too many exist, force AOL to produce appropriate
688 -- error message
689 --
690 if p_too_many_segs then
691
692 l_concat_string := l_concat_string||l_delimiter;
693 end if;
694 --
695 -- Stop overflow of hr_utility by restricting using substr.
696 -- WWBUG 1812106.
697 --
698 if g_debug then
699 hr_utility.set_location('=>'||substr(l_concat_string,1,80),48);
700 end if;
701
702 if g_debug then
703 hr_utility.set_location(l_proc,50);
704 end if;
705 --
706 --3171225 start
707 l_effective_date:=to_date(null);
708 open date_set;
709 fetch date_set into l_effective_date;
710 if date_set%found then
711 if g_debug then
712 hr_utility.set_location(l_proc,53);
713 end if;
714 close date_set;
715 else
716 if g_debug then
717 hr_utility.set_location(l_proc,55);
718 end if;
719 close date_set;
720 l_effective_date := trunc(sysdate);
721 end if;
722 --3171225 end
723 -- Call AOL flex validation routine
724 --;
725
726 if fnd_flex_keyval.validate_segs
727 (operation => 'CREATE_COMBINATION'
728 ,appl_short_name => p_appl_short_name
729 ,key_flex_code => p_flex_code
730 ,structure_number => p_flex_num
731 ,concat_segments => l_concat_string
732 ,values_or_ids => 'I'
733 ,validation_date => nvl(l_effective_date,trunc(sysdate)) --3171225
734 )
735 then
736 if g_debug then
737 hr_utility.set_location(l_proc, 60);
738 end if;
739 --
740 -- Flex routine succeeds - fetch relevant ccid
741 -- and concatenated segment string
742 --
743 p_ccid := fnd_flex_keyval.combination_id;
744 p_concat_segments_out := fnd_flex_keyval.concatenated_values;
745 --
746 if g_debug then
747 hr_utility.set_location(l_proc, 70);
748 end if;
749 --
750 else
751 if g_debug then
752 hr_utility.set_location(l_proc, 80);
753 end if;
754 --
755 -- Raise or trap the AOL error
756 --
757 if g_debug then
758 hr_utility.set_location(l_proc, 81);
759 end if;
760 --
761 -- The FND FLEX KEYVAL package makes a call to FND MESSAGE
762 -- get encoded
763 hr_message.parse_encoded(p_encoded_error =>
764 FND_FLEX_KEYVAL.encoded_error_message);
765 if g_debug then
766 hr_utility.set_location(l_proc, 82);
767 end if;
768 --
769 if ((hr_message.last_message_app = 'FND') and
770 (hr_message.last_message_name = 'FLEX-VALUE NOT FOUND')) then
771 --
772 if g_debug then
773 hr_utility.set_location(l_proc, 84);
774 end if;
775 --
776 -- In this case, there was an invalid segment, or context.
777 -- We must allow for the possibility that the context value
778 -- was not valid.
779 -- Should trap this error
780 -- and replace with a more user friendly message
781 -- First, work out which segment is in error, and change the
782 -- error message to indicate which of these segments is
783 -- is incorrect.
784 --
785 error_seg := FND_FLEX_KEYVAL.error_segment;
786 find_error_segment(p_appl_short_name => p_appl_short_name,
787 p_flex_code => p_flex_code,
788 p_flex_num => p_flex_num,
789 p_error_seg_num => error_seg,
790 p_application_col_name => app_col_name,
791 p_form_left_prompt => l_flex_seg_error_prompt,
792 p_table_name => l_table_name
793 );
794 if g_debug then
795 hr_utility.set_location(l_proc, 86);
796 end if;
797 --
798 value := hr_message.get_token_value(p_token_name => 'VALUE');
799 --
800 -- Now have the additional error information, raise a different error
801 -- using this information.
802 --
803 fnd_message.set_name('PER','HR_FLEX_VALUE_INVALID');
804 fnd_message.set_token('COLUMN',app_col_name);
805 fnd_message.set_token('VALUE',value);
806 fnd_message.set_token('PROMPT',l_flex_seg_error_prompt);
807 hr_multi_message.add
808 (p_associated_column1 => l_table_name || '.' || app_col_name);
809 elsif hr_message.last_message_app = 'FND' and
810 hr_message.last_message_name = 'FLEX-NULL SEGMENT' then
811 if g_debug then
812 hr_utility.set_location(l_proc, 90);
813 end if;
814 --
815 -- Work out which segment is in error. Replace the error message
816 -- by a more useful one.
817 --
818 error_seg := FND_FLEX_KEYVAL.error_segment;
819 find_error_segment
820 (p_appl_short_name => p_appl_short_name
821 ,p_flex_code => p_flex_code
822 ,p_flex_num => p_flex_num
823 ,p_error_seg_num => error_seg
824 ,p_application_col_name => app_col_name
825 ,p_form_left_prompt => l_flex_seg_error_prompt
826 ,p_table_name => l_table_name
827 );
828 --
829 -- Raise the alternative error.
830 --
831 if g_debug then
832 hr_utility.set_location(l_proc, 95);
833 end if;
834 fnd_message.set_name('PER','HR_FLEX_VALUE_MISSING');
835 fnd_message.set_token('COLUMN',app_col_name);
836 fnd_message.set_token('PROMPT',l_flex_seg_error_prompt);
837 hr_multi_message.add
838 (p_associated_column1 => l_table_name || '.' || app_col_name);
839 else
840 --
841 -- Some other non-expected error, simply raise the error in the
842 -- previous fashion.
843 --
844
845 -- Raise the AOL error
846 --
847 if g_debug then
848 hr_utility.set_location(l_proc, 100);
849 end if;
850 -- fnd_message.set_name('FND',hr_message.last_message_name); Fix for the bug 15845677
851 fnd_message.set_encoded(fnd_flex_keyval.encoded_error_message);
852 fnd_message.raise_error;
853 --
854 end if;
855 end if;
856 --
857 if g_debug then
858 hr_utility.set_location(' Leaving:'||l_proc, 200);
859 end if;
860 --
861 end check_segment_combination;
862 --
863 -- ----------------------------------------------------------------------------
864 -- |---------------------------- sort_segment_order --------------------------|
865 -- ----------------------------------------------------------------------------
866 --
867 -- {Start Of Comments}
868 --
869 -- Description:
870 -- This private procedure sorts the flexfield segments into DISPLAY order
871 -- for the relevant flexfield structure definition.
872 --
873 -- Prerequisites:
874 -- A valid id_flex_code (flex structure code)
875 -- A valid id_flex_num (flex structure number)
876 --
877 -- In Parameters:
878 -- Name Reqd Type Description
879 -- ==== ==== ==== ===========
880 -- p_segment1 - 30 Yes varchar2 Flex segments for 'Bank Details'
881 -- in SEGMENT order
882 -- p_flex_code Yes varchar2 The id_flex_code of the relevant key
883 -- flexfield structure
884 -- p_flex_num Yes number The id_flex_num of the 'Bank Details'
885 -- flexfield structure
886 --
887 -- Post Success:
888 -- The procedure creates a PLSQL table containing a segment array of
889 -- flex segment values in DISPLAY order.
890 --
891 -- Post Failure:
892 -- The process raises an AOL error and stops execution.
893 --
894 -- Access Status:
895 -- Private.
896 --
897 -- {End Of Comments}
898 --
899 procedure sort_segment_order
900 (p_segment1 in varchar2 default null
901 ,p_segment2 in varchar2 default null
902 ,p_segment3 in varchar2 default null
903 ,p_segment4 in varchar2 default null
904 ,p_segment5 in varchar2 default null
905 ,p_segment6 in varchar2 default null
906 ,p_segment7 in varchar2 default null
907 ,p_segment8 in varchar2 default null
908 ,p_segment9 in varchar2 default null
909 ,p_segment10 in varchar2 default null
910 ,p_segment11 in varchar2 default null
911 ,p_segment12 in varchar2 default null
912 ,p_segment13 in varchar2 default null
913 ,p_segment14 in varchar2 default null
914 ,p_segment15 in varchar2 default null
915 ,p_segment16 in varchar2 default null
916 ,p_segment17 in varchar2 default null
917 ,p_segment18 in varchar2 default null
918 ,p_segment19 in varchar2 default null
919 ,p_segment20 in varchar2 default null
920 ,p_segment21 in varchar2 default null
921 ,p_segment22 in varchar2 default null
922 ,p_segment23 in varchar2 default null
923 ,p_segment24 in varchar2 default null
924 ,p_segment25 in varchar2 default null
925 ,p_segment26 in varchar2 default null
926 ,p_segment27 in varchar2 default null
927 ,p_segment28 in varchar2 default null
928 ,p_segment29 in varchar2 default null
929 ,p_segment30 in varchar2 default null
930 ,p_flex_code in fnd_id_flex_segments.id_flex_code%TYPE
931 ,p_flex_num in number
932 -- Bug fix 2148839. Application id passed.
933 ,p_application_id in number
934 ,p_parameter out nocopy fnd_flex_ext.SegmentArray
935 )
936 is
937 --
938 -- Declare cursors and local variables
939 --
940 l_proc varchar2(72);
941 --
942 l_parameter_temp fnd_flex_ext.SegmentArray;
943 l_parameter_sorted fnd_flex_ext.SegmentArray;
944 l_disp_no number;
945 l_seg_no number;
946 l_seg_count number := 0;
947
948 -- Bug fix 2148839.
949 Type NumberArray is table of number index by binary_integer;
950 l_seg_no_array NumberArray;
951 l_disp_no_array NumberArray;
952 --
953 -- Bug fix 2148339.
954 -- Application id condition added to cursor to increase the
955 -- performance.
956
957 cursor get_pop_segs is
958 select substr(application_column_name,8,2)
959 from fnd_id_flex_segments_vl
960 where id_flex_code = p_flex_code
961 and id_flex_num = p_flex_num
962 and enabled_flag='Y'
963 and application_id = p_application_id
964 order by to_number(substr(application_column_name,8,2));
965 --
966 cursor get_seg_order is
967 select substr(application_column_name,8,2)
968 from fnd_id_flex_segments_vl
969 where id_flex_code = p_flex_code
970 and id_flex_num = p_flex_num
971 and enabled_flag = 'Y'
972 and application_id = p_application_id
973 order by segment_num;
974 --
975 begin
976
977 if g_debug then
978 l_proc := 'sort_segment_order';
979 hr_utility.set_location('Entering:'|| l_proc, 10);
980 end if;
981 --
982 -- Assign all segments values to a temporary PLSQL
983 -- table or Segment Array
984 --
985 l_parameter_temp(1) := p_segment1;
986 l_parameter_temp(2) := p_segment2;
987 l_parameter_temp(3) := p_segment3;
988 l_parameter_temp(4) := p_segment4;
989 l_parameter_temp(5) := p_segment5;
990 l_parameter_temp(6) := p_segment6;
991 l_parameter_temp(7) := p_segment7;
992 l_parameter_temp(8) := p_segment8;
993 l_parameter_temp(9) := p_segment9;
994 l_parameter_temp(10) := p_segment10;
995 l_parameter_temp(11) := p_segment11;
996 l_parameter_temp(12) := p_segment12;
997 l_parameter_temp(13) := p_segment13;
998 l_parameter_temp(14) := p_segment14;
999 l_parameter_temp(15) := p_segment15;
1000 l_parameter_temp(16) := p_segment16;
1001 l_parameter_temp(17) := p_segment17;
1002 l_parameter_temp(18) := p_segment18;
1003 l_parameter_temp(19) := p_segment19;
1004 l_parameter_temp(20) := p_segment20;
1005 l_parameter_temp(21) := p_segment21;
1006 l_parameter_temp(22) := p_segment22;
1007 l_parameter_temp(23) := p_segment23;
1008 l_parameter_temp(24) := p_segment24;
1009 l_parameter_temp(25) := p_segment25;
1010 l_parameter_temp(26) := p_segment26;
1011 l_parameter_temp(27) := p_segment27;
1012 l_parameter_temp(28) := p_segment28;
1013 l_parameter_temp(29) := p_segment29;
1014 l_parameter_temp(30) := p_segment30;
1015 --
1016 if g_debug then
1017 hr_utility.set_location(l_proc, 20);
1018 end if;
1019 --
1020 -- Check if any segments exist
1021 --
1022 if l_parameter_temp.count > 0 then
1023 --
1024 -- Create sorted segment array (l_parameter_sorted)
1025 --
1026 -- Initailize l_parameter_sorted to hr_api.g_varchar2 to
1027 -- ensure that no empty cells exist in the segment array
1028 --
1029 for i in 1..30
1030 loop
1031 l_parameter_sorted(i) := hr_api.g_varchar2;
1032 end loop;
1033 if g_debug then
1034 hr_utility.set_location(l_proc, 30);
1035 end if;
1036 --
1037 -- Populate SORTED segment array
1038 -- Bug fix 2148839
1039 -- Bulk collect used to improve performance.
1040 open get_pop_segs;
1041 fetch get_pop_segs BULK COLLECT into l_seg_no_array;
1042
1043 open get_seg_order;
1044 fetch get_seg_order BULK COLLECT into l_disp_no_array;
1045
1046 for j in 1..l_seg_no_array.count
1047 loop
1048 -- fetch get_seg_order into l_disp_no; -- The list of column order required by AOL
1049 -- fetch get_pop_segs into l_seg_no; -- A list of segments in column order
1050 -- exit when get_pop_segs%NOTFOUND;
1051 --
1052 -- Pass user defined segment values into sorted
1053 -- segment array
1054 --
1055 l_parameter_sorted(l_seg_no_array(j)) := l_parameter_temp(l_disp_no_array(j));
1056 l_seg_count := l_seg_count + 1;
1057 --
1058 end loop;
1059 close get_seg_order;
1060 close get_pop_segs;
1061 if g_debug then
1062 hr_utility.set_location(l_proc, 40);
1063 end if;
1064 --
1065 -- Verify whether sorted array has been created
1066 --
1067 if l_seg_count = 0 then
1068 --
1069 -- Error in creation
1070 --
1071 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1072 hr_utility.set_message_token('PROCEDURE', l_proc);
1073 hr_utility.set_message_token('STEP','40');
1074 hr_utility.raise_error;
1075 --
1076 end if;
1077 if g_debug then
1078 hr_utility.set_location(l_proc, 50);
1079 end if;
1080 --
1081 -- Pass back sorted segment array to calling procedure
1082 --
1083 p_parameter := l_parameter_sorted;
1084 --
1085 end if;
1086 --
1087 if g_debug then
1088 hr_utility.set_location('Leaving:'|| l_proc, 60);
1089 end if;
1090 end sort_segment_order;
1091 --
1092 -- ----------------------------------------------------------------------------
1093 -- |---------------------------- check_pop_segments --------------------------|
1094 -- ----------------------------------------------------------------------------
1095 --
1096 -- {Start Of Comments}
1097 --
1098 -- Description:
1099 -- This private function checks that the user does not pass in values for
1100 -- segments that do not exist in the relevant flexfield structure. If too
1101 -- many segments have been passed via the parameter interface the function
1102 -- sets too_many_segments to TRUE otherwise FALSE is returned.
1103 --
1104 -- This function is only called when using the parameter interface.
1105 --
1106 -- Prerequisites:
1107 -- A valid id_flex_code (flex structure code)
1108 -- A valid id_flex_num (flex structure number)
1109 --
1110 -- In Parameters:
1111 -- Name Reqd Type Description
1112 -- ==== ==== ==== ===========
1113 -- p_parameter Yes PL*SQL Flex segments for the key flexfield
1114 -- table stored in a PL*SQL table structure
1115 -- p_flex_num Yes number The structure number for the relevant
1116 -- key flexfield.
1117 -- p_flex_code Yes varchar2 The id_flex_code of the relevant key
1118 -- flexfield structure
1119 --
1120 -- Post Success:
1121 -- N/A
1122 --
1123 -- Post Failure:
1124 -- N/A
1125 --
1126 -- Access Status:
1127 -- Private.
1128 --
1129 -- {End Of Comments}
1130 --
1131 --
1132 function check_pop_segments
1133 (p_parameter in fnd_flex_ext.SegmentArray
1134 ,p_flex_code in fnd_id_flex_segments.id_flex_code%TYPE
1135 ,p_flex_num in number
1136 -- Bug fix 2148839.parameter p_application_id added.
1137 ,p_application_id in number
1138 ) RETURN boolean
1139 is
1140 --
1141 l_proc varchar2(72);
1142 --
1143 l_parameter_temp fnd_flex_ext.SegmentArray;
1144 l_no_segments number;
1145 l_no_pop_segments number := 0;
1146 --
1147 begin
1148 if g_debug then
1149 l_proc := 'check_pop_segments';
1150 hr_utility.set_location('Entering:'|| l_proc, 10);
1151 end if;
1152 --
1153 -- Fetch number of enabled segments in flex structure
1154 --
1155 l_no_segments := get_no_segments
1156 (p_flex_num
1157 ,p_flex_code
1158 ,p_application_id
1159 );
1160 if g_debug then
1161 hr_utility.set_location(l_proc, 20);
1162 end if;
1163 --
1164 -- Put segment values into a PL*SQL table
1165 --
1166 l_parameter_temp := p_parameter;
1167 --
1168 -- Loop for number of segments on flex combination table
1169 --
1170 for i in 1..30
1171 loop
1172 if l_parameter_temp(i) <> hr_api.g_varchar2 then
1173 --
1174 -- Increase count of populated segments
1175 --
1176 l_no_pop_segments := l_no_pop_segments + 1;
1177 --
1178 end if;
1179 end loop;
1180 --
1181 if g_debug then
1182 hr_utility.set_location(l_proc, 30);
1183 end if;
1184 --
1185 if l_no_pop_segments > l_no_segments then
1186 --
1187 -- Number of populated segments exceeds
1188 -- number of segments in relevant flexfield structure
1189 --
1190 return TRUE;
1191 else
1192 --
1193 -- Correct number of segments in structure
1194 --
1195 return FALSE;
1196 end if;
1197 --
1198 if g_debug then
1199 hr_utility.set_location('Leaving:'|| l_proc, 40);
1200 end if;
1201 end check_pop_segments;
1202 -----------------------------------------------------------------------------
1203 -- ignore validation procedures using varry of flexcodes to ignore.
1204 -------------------------------------------------------------------------------
1205 -- create it
1206 -------------------------------------------------------------------------------
1207 --
1208 procedure create_ignore_kf_validation(p_rec in l_ignore_kfcode_varray) is
1209 l_proc varchar2(72);
1210 begin
1211 g_debug := hr_utility.debug_enabled;
1212 if g_debug then
1213 l_proc := 'create_ignore_kf_validation';
1214 hr_utility.set_location('Entering:'|| l_proc, 10);
1215 end if;
1216 if p_rec.count <> 0
1217 then
1218 g_kfcode_structure := p_rec;
1219 end if;
1220 if g_debug then
1221 hr_utility.set_location('Leaving:'|| l_proc, 20);
1222 end if;
1223 End create_ignore_kf_validation;
1224 --
1225 -- user calls a population script to populate this array
1226 ----------------------------------------------------------------------------
1227 -- check it
1228 ----------------------------------------------------------------------------
1229 function check_ignore_varray(p_structure in varchar2) return boolean is
1230 l_proc varchar2(72);
1231 begin
1232 g_debug := hr_utility.debug_enabled;
1233 if g_debug then
1234 l_proc := 'check_ignore_varray';
1235 hr_utility.set_location('Entering:'|| l_proc, 10);
1236 end if;
1237 if g_kfcode_structure.count <> 0
1238 then
1239 for l_count in g_kfcode_structure.first..g_kfcode_structure.last
1240 loop
1241 if g_kfcode_structure(l_count) = p_structure
1242 then
1243 return true;
1244 end if;
1245 end loop;
1246 end if;
1247 return false;
1248 if g_debug then
1249 hr_utility.set_location('Leaving:'|| l_proc, 20);
1250 end if;
1251 end;
1252 --
1253 ----------------------------------------------------------------------------
1254 -- delete it
1255 ----------------------------------------------------------------------------
1256 procedure remove_ignore_kf_validation is
1257 l_proc varchar2(72);
1258 begin
1259 g_debug := hr_utility.debug_enabled;
1260 if g_debug then
1261 l_proc := 'remove_ignore_kf_validation';
1262 hr_utility.set_location('Entering:'|| l_proc, 10);
1263 end if;
1264 g_kfcode_structure.delete;
1265 if g_debug then
1266 hr_utility.set_location('Leaving:'|| l_proc, 20);
1267 end if;
1268 end;
1269 --
1270 -- ----------------------------------------------------------------------------
1271 -- |------------------------ ins_or_sel_keyflex_comb -------------------------|
1272 -- ----------------------------------------------------------------------------
1273 --
1274 procedure ins_or_sel_keyflex_comb
1275 (p_appl_short_name in fnd_application.application_short_name%TYPE
1276 ,p_flex_code in fnd_id_flex_segments.id_flex_code%TYPE
1277 ,p_flex_num in number
1278 ,p_segment1 in varchar2 default null
1279 ,p_segment2 in varchar2 default null
1280 ,p_segment3 in varchar2 default null
1281 ,p_segment4 in varchar2 default null
1282 ,p_segment5 in varchar2 default null
1283 ,p_segment6 in varchar2 default null
1284 ,p_segment7 in varchar2 default null
1285 ,p_segment8 in varchar2 default null
1286 ,p_segment9 in varchar2 default null
1287 ,p_segment10 in varchar2 default null
1288 ,p_segment11 in varchar2 default null
1289 ,p_segment12 in varchar2 default null
1290 ,p_segment13 in varchar2 default null
1291 ,p_segment14 in varchar2 default null
1292 ,p_segment15 in varchar2 default null
1293 ,p_segment16 in varchar2 default null
1294 ,p_segment17 in varchar2 default null
1295 ,p_segment18 in varchar2 default null
1296 ,p_segment19 in varchar2 default null
1297 ,p_segment20 in varchar2 default null
1298 ,p_segment21 in varchar2 default null
1299 ,p_segment22 in varchar2 default null
1300 ,p_segment23 in varchar2 default null
1301 ,p_segment24 in varchar2 default null
1302 ,p_segment25 in varchar2 default null
1303 ,p_segment26 in varchar2 default null
1304 ,p_segment27 in varchar2 default null
1305 ,p_segment28 in varchar2 default null
1306 ,p_segment29 in varchar2 default null
1307 ,p_segment30 in varchar2 default null
1308 ,p_concat_segments_in in varchar2 default null
1309 ,p_ccid out nocopy number
1310 ,p_concat_segments_out out nocopy varchar2
1311 )
1312 is
1313 --
1314 -- Declare cursors and local variables
1315 --
1316 l_proc varchar2(72) := 'ins_or_sel_keyflex_comb';
1317 --
1318 l_parameter_sorted fnd_flex_ext.SegmentArray;
1319 l_parameter_temp fnd_flex_ext.SegmentArray;
1320 l_too_many_segs boolean default FALSE;
1321 --
1322 l_flex_code varchar2(30) := NULL;
1323 -- Bug fix 2148839.
1324 l_application_id number;
1325 --
1326 begin
1327 g_debug := hr_utility.debug_enabled;
1328 if g_debug then
1329 hr_utility.set_location('Entering:'|| l_proc, 10);
1330 end if;
1331 --
1332 -- Set up KF session mode
1333 --
1334 fnd_flex_key_api.set_session_mode('customer_data');
1335 --
1336 -- Check mandatory parameters have been set
1337 --
1338 hr_api.mandatory_arg_error
1339 (p_api_name => l_proc
1340 ,p_argument => 'appl_short_name'
1341 ,p_argument_value => p_appl_short_name
1342 );
1343 --
1344 hr_api.mandatory_arg_error
1345 (p_api_name => l_proc
1346 ,p_argument => 'flex_code'
1347 ,p_argument_value => p_flex_code
1348 );
1349 --
1350 hr_api.mandatory_arg_error
1351 (p_api_name => l_proc
1352 ,p_argument => 'flex_num'
1353 ,p_argument_value => p_flex_num
1354 );
1355 if g_debug then
1356 hr_utility.set_location(l_proc, 20);
1357 end if;
1358 --
1359 -- 2252580 ignore key flex validation if flexcode exists in varray:
1360 --
1361 l_flex_code := p_flex_code;
1362 --
1363 if check_ignore_varray(l_flex_code)
1364 then
1365 if g_debug then
1366 hr_utility.set_location(l_proc, 25);
1367 end if;
1368 -- 2252580 no validation required, exit procedure
1369 return;
1370 else
1371 -- 2252580 go ahead and validate if flex code is not in varray.
1372 --
1373 -- Check to see whether key flex segments as individual parameters
1374 -- have been passed in or a concatenated string of segments
1375 --
1376 if p_concat_segments_in is null then
1377 if g_debug then
1378 hr_utility.set_location(l_proc, 30);
1379 end if;
1380 -- Bug fix 2148839.
1381 -- function call to get application id.
1382 l_application_id := get_application_id( p_appl_short_name => p_appl_short_name );
1383 --
1384 -- Call sort_segment_order to format segment interface
1385 -- into segment DISPLAY order
1386 --
1387 sort_segment_order
1388 (p_segment1 => p_segment1
1389 ,p_segment2 => p_segment2
1390 ,p_segment3 => p_segment3
1391 ,p_segment4 => p_segment4
1392 ,p_segment5 => p_segment5
1393 ,p_segment6 => p_segment6
1394 ,p_segment7 => p_segment7
1395 ,p_segment8 => p_segment8
1396 ,p_segment9 => p_segment9
1397 ,p_segment10 => p_segment10
1398 ,p_segment11 => p_segment11
1399 ,p_segment12 => p_segment12
1400 ,p_segment13 => p_segment13
1401 ,p_segment14 => p_segment14
1402 ,p_segment15 => p_segment15
1403 ,p_segment16 => p_segment16
1404 ,p_segment17 => p_segment17
1405 ,p_segment18 => p_segment18
1406 ,p_segment19 => p_segment19
1407 ,p_segment20 => p_segment20
1408 ,p_segment21 => p_segment21
1409 ,p_segment22 => p_segment22
1410 ,p_segment23 => p_segment23
1411 ,p_segment24 => p_segment24
1412 ,p_segment25 => p_segment25
1413 ,p_segment26 => p_segment26
1414 ,p_segment27 => p_segment27
1415 ,p_segment28 => p_segment28
1416 ,p_segment29 => p_segment29
1417 ,p_segment30 => p_segment30
1418 ,p_flex_num => p_flex_num
1419 ,p_flex_code => p_flex_code
1420 -- bug fix 2148839.
1421 -- Application id is paased to improve performance.
1422 ,p_application_id => l_application_id
1423 ,p_parameter => l_parameter_sorted
1424 );
1425 if g_debug then
1426 hr_utility.set_location(l_proc, 40);
1427 end if;
1428 --
1429 -- Verify that number of populated segments
1430 -- passed in does exceed number of segments in
1431 -- flexfield structure
1432 --
1433 l_parameter_temp(1) := p_segment1;
1434 l_parameter_temp(2) := p_segment2;
1435 l_parameter_temp(3) := p_segment3;
1436 l_parameter_temp(4) := p_segment4;
1437 l_parameter_temp(5) := p_segment5;
1438 l_parameter_temp(6) := p_segment6;
1439 l_parameter_temp(7) := p_segment7;
1440 l_parameter_temp(8) := p_segment8;
1441 l_parameter_temp(9) := p_segment9;
1442 l_parameter_temp(10) := p_segment10;
1443 l_parameter_temp(11) := p_segment11;
1444 l_parameter_temp(12) := p_segment12;
1445 l_parameter_temp(13) := p_segment13;
1446 l_parameter_temp(14) := p_segment14;
1447 l_parameter_temp(15) := p_segment15;
1448 l_parameter_temp(16) := p_segment16;
1449 l_parameter_temp(17) := p_segment17;
1450 l_parameter_temp(18) := p_segment18;
1451 l_parameter_temp(19) := p_segment19;
1452 l_parameter_temp(20) := p_segment20;
1453 l_parameter_temp(21) := p_segment21;
1454 l_parameter_temp(22) := p_segment22;
1455 l_parameter_temp(23) := p_segment23;
1456 l_parameter_temp(24) := p_segment24;
1457 l_parameter_temp(25) := p_segment25;
1458 l_parameter_temp(26) := p_segment26;
1459 l_parameter_temp(27) := p_segment27;
1460 l_parameter_temp(28) := p_segment28;
1461 l_parameter_temp(29) := p_segment29;
1462 l_parameter_temp(30) := p_segment30;
1463 --
1464 if g_debug then
1465 hr_utility.set_location(l_proc, 50);
1466 end if;
1467 --
1468 l_too_many_segs := check_pop_segments
1469 (p_parameter => l_parameter_temp
1470 ,p_flex_code => p_flex_code
1471 ,p_flex_num => p_flex_num
1472 -- Bug fix 2148839.
1473 ,p_application_id => l_application_id
1474 );
1475 --
1476 if g_debug then
1477 hr_utility.set_location(l_proc, 60);
1478 end if;
1479 --
1480 -- Call Wrapper flex handler to bring back CCID value
1481 --
1482 check_segment_combination
1483 (p_flex_num => p_flex_num
1484 ,p_flex_code => p_flex_code
1485 ,p_appl_short_name => p_appl_short_name
1486 ,p_parameter => l_parameter_sorted
1487 ,p_too_many_segs => l_too_many_segs
1488 ,p_ccid => p_ccid
1489 ,p_concat_segments_out => p_concat_segments_out
1490 );
1491 --
1492 if g_debug then
1493 hr_utility.set_location(l_proc, 70);
1494 end if;
1495 --
1496 else
1497 --
1498 if g_debug then
1499 hr_utility.set_location(l_proc, 80);
1500 end if;
1501 --
1502 check_segment_combination
1503 (p_flex_num => p_flex_num
1504 ,p_flex_code => p_flex_code
1505 ,p_appl_short_name => p_appl_short_name
1506 ,p_concat_segments_in => p_concat_segments_in
1507 ,p_ccid => p_ccid
1508 ,p_concat_segments_out => p_concat_segments_out
1509 );
1510 --
1511 if g_debug then
1512 hr_utility.set_location(l_proc, 90);
1513 end if;
1514 --
1515 end if;
1516 --
1517 end if; -- end if keyflex code exists in ignore varray
1518 if g_debug then
1519 hr_utility.set_location('Leaving '|| l_proc, 100);
1520 end if;
1521 exception
1522 when app_exception.application_exception then
1523 if hr_multi_message.exception_add then
1524 if g_debug then
1525 hr_utility.set_location(l_proc, 100);
1526 end if;
1527 raise;
1528 end if;
1529 if g_debug then
1530 hr_utility.set_location(l_proc, 110);
1531 end if;
1532 end ins_or_sel_keyflex_comb;
1533 --
1534 -- ----------------------------------------------------------------------------
1535 -- |------------------------- upd_or_sel_keyflex_comb ------------------------|
1536 -- ----------------------------------------------------------------------------
1537 --
1538 procedure upd_or_sel_keyflex_comb
1539 (p_appl_short_name in fnd_application.application_short_name%TYPE
1540 ,p_flex_code in fnd_id_flex_segments.id_flex_code%TYPE
1541 ,p_flex_num in number
1542 ,p_segment1 in varchar2 default hr_api.g_varchar2
1543 ,p_segment2 in varchar2 default hr_api.g_varchar2
1544 ,p_segment3 in varchar2 default hr_api.g_varchar2
1545 ,p_segment4 in varchar2 default hr_api.g_varchar2
1546 ,p_segment5 in varchar2 default hr_api.g_varchar2
1547 ,p_segment6 in varchar2 default hr_api.g_varchar2
1548 ,p_segment7 in varchar2 default hr_api.g_varchar2
1549 ,p_segment8 in varchar2 default hr_api.g_varchar2
1550 ,p_segment9 in varchar2 default hr_api.g_varchar2
1551 ,p_segment10 in varchar2 default hr_api.g_varchar2
1552 ,p_segment11 in varchar2 default hr_api.g_varchar2
1553 ,p_segment12 in varchar2 default hr_api.g_varchar2
1554 ,p_segment13 in varchar2 default hr_api.g_varchar2
1555 ,p_segment14 in varchar2 default hr_api.g_varchar2
1556 ,p_segment15 in varchar2 default hr_api.g_varchar2
1557 ,p_segment16 in varchar2 default hr_api.g_varchar2
1558 ,p_segment17 in varchar2 default hr_api.g_varchar2
1559 ,p_segment18 in varchar2 default hr_api.g_varchar2
1560 ,p_segment19 in varchar2 default hr_api.g_varchar2
1561 ,p_segment20 in varchar2 default hr_api.g_varchar2
1562 ,p_segment21 in varchar2 default hr_api.g_varchar2
1563 ,p_segment22 in varchar2 default hr_api.g_varchar2
1564 ,p_segment23 in varchar2 default hr_api.g_varchar2
1565 ,p_segment24 in varchar2 default hr_api.g_varchar2
1566 ,p_segment25 in varchar2 default hr_api.g_varchar2
1567 ,p_segment26 in varchar2 default hr_api.g_varchar2
1568 ,p_segment27 in varchar2 default hr_api.g_varchar2
1569 ,p_segment28 in varchar2 default hr_api.g_varchar2
1570 ,p_segment29 in varchar2 default hr_api.g_varchar2
1571 ,p_segment30 in varchar2 default hr_api.g_varchar2
1572 ,p_concat_segments_in in varchar2 default null
1573 ,p_ccid in out nocopy number
1574 ,p_concat_segments_out out nocopy varchar2
1575 )
1576 is
1577 -- Local Variables for the upd_or_sel_kflex_comb
1578 -- when calling ins_or_sel_kflex_comb, This allows
1579 -- this call to be made and changes the default values to null values
1580 l_segment1 varchar2(150);
1581 l_segment2 varchar2(150);
1582 l_segment3 varchar2(150);
1583 l_segment4 varchar2(150);
1584 l_segment5 varchar2(150);
1585 l_segment6 varchar2(150);
1586 l_segment7 varchar2(150);
1587 l_segment8 varchar2(150);
1588 l_segment9 varchar2(150);
1589 l_segment10 varchar2(150);
1590 l_segment11 varchar2(150);
1591 l_segment12 varchar2(150);
1592 l_segment13 varchar2(150);
1593 l_segment14 varchar2(150);
1594 l_segment15 varchar2(150);
1595 l_segment16 varchar2(150);
1596 l_segment17 varchar2(150);
1597 l_segment18 varchar2(150);
1598 l_segment19 varchar2(150);
1599 l_segment20 varchar2(150);
1600 l_segment21 varchar2(150);
1601 l_segment22 varchar2(150);
1602 l_segment23 varchar2(150);
1603 l_segment24 varchar2(150);
1604 l_segment25 varchar2(150);
1605 l_segment26 varchar2(150);
1606 l_segment27 varchar2(150);
1607 l_segment28 varchar2(150);
1608 l_segment29 varchar2(150);
1609 l_segment30 varchar2(150);
1610 l_concat_segments_in varchar2(700);
1611 --
1612 -- Declare cursors and local variables
1613 --
1614 l_proc varchar2(72) := 'upd_or_sel_keyflex_comb';
1615 --
1616 l_flex_code varchar2(30) := NULL; -- 2252580
1617 --
1618 array_counter number;
1619 l_no_segments number;
1620 l_seg_num number;
1621 l_too_many_segs boolean default FALSE;
1622 l_concat_string varchar2(2000);
1623 l_delimiter varchar2(1);
1624 --
1625 -- l_parameter_temp
1626 --
1627 -- Holds complete seg array containing all new
1628 -- values passed in by the user plus the values
1629 -- retrieved from the exisiting combination
1630 -- details stored on the database. This array is
1631 -- different to l_parameter_updated in that it
1632 -- will store ALL the values passed in by the user,
1633 -- even if they are linked to segments which are not
1634 -- set for the KF structure
1635 --
1636 l_parameter_temp fnd_flex_ext.SegmentArray;
1637 --
1638 -- Holds original inserted seg array
1639 --
1640 l_parameter_original fnd_flex_ext.SegmentArray;
1641 --
1642 -- holds new seg array containing updated
1643 -- segment details
1644 --
1645 l_parameter_new fnd_flex_ext.SegmentArray;
1646 --
1647 -- holds complete seg array ready to send
1648 -- to AOL Validation
1649 --
1650 l_parameter_updated fnd_flex_ext.SegmentArray;
1651 -- Bug fix 2148839.
1652 l_application_id number;
1653 --
1654 -- 1141260 : Corrected order by of following cursor.
1655 cursor get_seg_num is
1656 select substr(application_column_name,8,2)
1657 from fnd_id_flex_segments_vl
1658 where id_flex_code = p_flex_code
1659 and id_flex_num = p_flex_num
1660 and enabled_flag='Y'
1661 and application_id = l_application_id --3456567 to avoid FTS
1662 order by to_number(substr(application_column_name,8,2));
1663 --
1664 begin
1665 g_debug := hr_utility.debug_enabled;
1666 if g_debug then
1667 hr_utility.set_location('Entering:'|| l_proc, 10);
1668 end if;
1669 --
1670 -- Set up KF session mode
1671 --
1672 fnd_flex_key_api.set_session_mode('customer_data');
1673 --
1674 -- Check mandatory parameters have been set
1675 --
1676 hr_api.mandatory_arg_error
1677 (p_api_name => l_proc
1678 ,p_argument => 'appl_short_name'
1679 ,p_argument_value => p_appl_short_name
1680 );
1681 --
1682 hr_api.mandatory_arg_error
1683 (p_api_name => l_proc
1684 ,p_argument => 'flex_code'
1685 ,p_argument_value => p_flex_code
1686 );
1687 --
1688 hr_api.mandatory_arg_error
1689 (p_api_name => l_proc
1690 ,p_argument => 'flex_num'
1691 ,p_argument_value => p_flex_num
1692 );
1693 --
1694 if g_debug then
1695 hr_utility.set_location(l_proc, 20);
1696 end if;
1697 --
1698 --
1699 -- 2252580 ignore key flex validation if flexcode exists in varray:
1700 --
1701 l_flex_code := p_flex_code;
1702 --
1703 if check_ignore_varray(l_flex_code)
1704 then
1705 if g_debug then
1706 hr_utility.set_location(l_proc, 25);
1707 end if;
1708 -- no validation required, exit procedure
1709 return;
1710 else
1711 -- flex code was not in varray so go ahead and validate procedure
1712 --
1713 -- Check to see whether key flex segments as individual parameters
1714 -- have been passed in or a concatenated string of segments
1715 --
1716 if p_ccid is not null then
1717 if (p_concat_segments_in = hr_api.g_varchar2
1718 or p_concat_segments_in is null)
1719 then
1720 --
1721 -- Initialize l_parameter_temp for use within call to
1722 -- check_pop_segs procedure. Segment array is populated
1723 -- with updated segment values prior to call.
1724 --
1725 l_parameter_temp(1) := p_segment1;
1726 l_parameter_temp(2) := p_segment2;
1727 l_parameter_temp(3) := p_segment3;
1728 l_parameter_temp(4) := p_segment4;
1729 l_parameter_temp(5) := p_segment5;
1730 l_parameter_temp(6) := p_segment6;
1731 l_parameter_temp(7) := p_segment7;
1732 l_parameter_temp(8) := p_segment8;
1733 l_parameter_temp(9) := p_segment9;
1734 l_parameter_temp(10) := p_segment10;
1735 l_parameter_temp(11) := p_segment11;
1736 l_parameter_temp(12) := p_segment12;
1737 l_parameter_temp(13) := p_segment13;
1738 l_parameter_temp(14) := p_segment14;
1739 l_parameter_temp(15) := p_segment15;
1740 l_parameter_temp(16) := p_segment16;
1741 l_parameter_temp(17) := p_segment17;
1742 l_parameter_temp(18) := p_segment18;
1743 l_parameter_temp(19) := p_segment19;
1744 l_parameter_temp(20) := p_segment20;
1745 l_parameter_temp(21) := p_segment21;
1746 l_parameter_temp(22) := p_segment22;
1747 l_parameter_temp(23) := p_segment23;
1748 l_parameter_temp(24) := p_segment24;
1749 l_parameter_temp(25) := p_segment25;
1750 l_parameter_temp(26) := p_segment26;
1751 l_parameter_temp(27) := p_segment27;
1752 l_parameter_temp(28) := p_segment28;
1753 l_parameter_temp(29) := p_segment29;
1754 l_parameter_temp(30) := p_segment30;
1755 --
1756 if g_debug then
1757 hr_utility.set_location(l_proc, 30);
1758 end if;
1759 -- Bug fix 2148839.
1760 -- function call to get application id.
1761 l_application_id := get_application_id( p_appl_short_name => p_appl_short_name );
1762 --
1763 -- Call sort_segment_order to format segment interface
1764 -- into segment DISPLAY order
1765 --
1766 sort_segment_order
1767 (p_segment1 => p_segment1
1768 ,p_segment2 => p_segment2
1769 ,p_segment3 => p_segment3
1770 ,p_segment4 => p_segment4
1771 ,p_segment5 => p_segment5
1772 ,p_segment6 => p_segment6
1773 ,p_segment7 => p_segment7
1774 ,p_segment8 => p_segment8
1775 ,p_segment9 => p_segment9
1776 ,p_segment10 => p_segment10
1777 ,p_segment11 => p_segment11
1778 ,p_segment12 => p_segment12
1779 ,p_segment13 => p_segment13
1780 ,p_segment14 => p_segment14
1781 ,p_segment15 => p_segment15
1782 ,p_segment16 => p_segment16
1783 ,p_segment17 => p_segment17
1784 ,p_segment18 => p_segment18
1785 ,p_segment19 => p_segment19
1786 ,p_segment20 => p_segment20
1787 ,p_segment21 => p_segment21
1788 ,p_segment22 => p_segment22
1789 ,p_segment23 => p_segment23
1790 ,p_segment24 => p_segment24
1791 ,p_segment25 => p_segment25
1792 ,p_segment26 => p_segment26
1793 ,p_segment27 => p_segment27
1794 ,p_segment28 => p_segment28
1795 ,p_segment29 => p_segment29
1796 ,p_segment30 => p_segment30
1797 ,p_flex_code => p_flex_code
1798 ,p_flex_num => p_flex_num
1799 -- Bug fix 2148839. Application id is passed.
1800 ,p_application_id => l_application_id
1801 ,p_parameter => l_parameter_new
1802 );
1803 --
1804 if g_debug then
1805 hr_utility.set_location(l_proc, 40);
1806 end if;
1807 --
1808 -- Initialize l_parameter_updated so that each cell in
1809 -- the segment array contains the hr_api.g_varchar2 default
1810 --
1811 for i in 1..30
1812 loop
1813 l_parameter_updated(i) := hr_api.g_varchar2;
1814 end loop;
1815 --
1816 if g_debug then
1817 hr_utility.set_location(l_proc, 50);
1818 end if;
1819 --
1820 -- Fetch the existing segment array for the CCID passed in
1821 --
1822 if fnd_flex_keyval.validate_ccid
1823 (appl_short_name => p_appl_short_name
1824 ,key_flex_code => p_flex_code
1825 ,structure_number => p_flex_num
1826 ,combination_id => p_ccid
1827 )
1828 then
1829 if g_debug then
1830 hr_utility.set_location(l_proc, 60);
1831 end if;
1832
1833 -- Retrieve concatenated id segment string
1834 --
1835 l_concat_string := fnd_flex_keyval.concatenated_ids;
1836
1837 -- Retrieve delimiter used in segment structure
1838 --
1839 l_delimiter := fnd_flex_keyval.segment_delimiter;
1840
1841 -- Convert concatenated string returned into a PL*SQL
1842 -- table segment array
1843 --
1844 l_no_segments := fnd_flex_ext.breakup_segments
1845 (concatenated_segs => l_concat_string
1846 ,delimiter => l_delimiter
1847 ,segments => l_parameter_original
1848 );
1849 --
1850 -- The flexfield has some segments but breakup_segments has not
1851 -- counted them properly nor has it initialised the
1852 -- l_parameter_original array
1853 -- in the situation where the flexfield has one segment defined and
1854 -- the record currently references the null combination, so get the
1855 -- correct segment count and also initialise the array to null.
1856 --
1857 l_no_segments:=fnd_flex_keyval.segment_count;
1858 if g_debug then
1859 hr_utility.set_location(l_proc, 70);
1860 end if;
1861 --
1862 if l_no_segments > 0 and l_parameter_original.count = 0 then
1863 for i in 1..l_no_segments loop
1864 l_parameter_original(i) := null;
1865 end loop;
1866 end if;
1867 --
1868 -- Create PL*SQL table l_parameter_updated with ORIGINAL and UPDATED
1869 -- key flexfield segment values.
1870 --
1871 open get_seg_num;
1872 array_counter:=1;
1873 --
1874 for i in 1..l_no_segments loop
1875 fetch get_seg_num into l_seg_num;
1876 exit when get_seg_num%NOTFOUND;
1877 --
1878 if l_parameter_new(l_seg_num) = hr_api.g_varchar2 then
1879 --
1880 -- Segment value has not changed - reset to old value
1881 --
1882 l_parameter_updated(l_seg_num) :=
1883 l_parameter_original(array_counter);
1884 l_parameter_temp(l_seg_num) := l_parameter_original(array_counter);
1885 --
1886 -- Otherwise set segment to new value
1887 --
1888 else
1889 --
1890 l_parameter_updated(l_seg_num) := nvl(l_parameter_new(l_seg_num), NULL);
1891 l_parameter_temp(l_seg_num) := nvl(l_parameter_new(l_seg_num), NULL);
1892 --
1893 end if;
1894 --
1895 array_counter:=array_counter+1;
1896
1897 end loop;
1898 close get_seg_num;
1899 else
1900 fnd_message.raise_error;
1901 end if;
1902 --
1903 if g_debug then
1904 hr_utility.set_location(l_proc, 80);
1905 end if;
1906 --
1907 -- Verify that number of populated segments passed in does exceed
1908 -- number of segments in flexfield structure.
1909 --
1910 -- Note : This check cannot be performed until the Update segment
1911 -- array is populated with both existing and updated values
1912 -- as only then do we have a complete update interface.
1913 --
1914 l_too_many_segs := check_pop_segments
1915 (p_parameter => l_parameter_temp
1916 ,p_flex_code => p_flex_code
1917 ,p_flex_num => p_flex_num
1918 -- Bug fix 2148839.
1919 ,p_application_id => l_application_id
1920 );
1921 --
1922 if g_debug then
1923 hr_utility.set_location(l_proc, 90);
1924 end if;
1925 --
1926 -- Call Wrapper flex handler to bring back new CCID value
1927
1928 check_segment_combination
1929 (p_appl_short_name => p_appl_short_name
1930 ,p_flex_code => p_flex_code
1931 ,p_flex_num => p_flex_num
1932 ,p_parameter => l_parameter_updated
1933 ,p_too_many_segs => l_too_many_segs
1934 ,p_ccid => p_ccid
1935 ,p_concat_segments_out => p_concat_segments_out
1936 );
1937 if g_debug then
1938 hr_utility.set_location(l_proc, 100);
1939 end if;
1940 --
1941 else
1942 if g_debug then
1943 hr_utility.set_location(l_proc, 110);
1944 end if;
1945 --
1946 check_segment_combination
1947 (p_appl_short_name => p_appl_short_name
1948 ,p_flex_code => p_flex_code
1949 ,p_flex_num => p_flex_num
1950 ,p_concat_segments_in => p_concat_segments_in
1951 ,p_ccid => p_ccid
1952 ,p_concat_segments_out => p_concat_segments_out
1953 );
1954 if g_debug then
1955 hr_utility.set_location(l_proc, 120);
1956 end if;
1957 --
1958 end if;
1959 else
1960 --
1961 -- No combination ID on update so nothing saved when record as inserted
1962 -- so although an update to the assignment we are inserting the keyflex
1963 -- so just call ins_or_sel_keyflex_comb.
1964 --
1965 if g_debug then
1966 hr_utility.set_location(' Seg3 '||p_segment3,101);
1967 end if;
1968 --
1969 if p_segment1 = hr_api.g_varchar2 then
1970 l_segment1 := null;
1971 else
1972 l_segment1 := p_segment1;
1973 end if;
1974
1975 if p_segment2 = hr_api.g_varchar2 then
1976 l_segment2 := null;
1977 else
1978 l_segment2 := p_segment2;
1979 end if;
1980
1981 if p_segment3 = hr_api.g_varchar2 then
1982 l_segment3 := null;
1983 else
1984 l_segment3 := p_segment3;
1985 end if;
1986
1987 if p_segment4 = hr_api.g_varchar2 then
1988 l_segment4 := null;
1989 else
1990 l_segment4 := p_segment4;
1991 end if;
1992
1993 if p_segment5 = hr_api.g_varchar2 then
1994 l_segment5 := null;
1995 else
1996 l_segment5 := p_segment5;
1997 end if;
1998
1999 if p_segment6 = hr_api.g_varchar2 then
2000 l_segment6 := null;
2001 else
2002 l_segment6 := p_segment6;
2003 end if;
2004
2005 if p_segment7 = hr_api.g_varchar2 then
2006 l_segment7 := null;
2007 else
2008 l_segment7 := p_segment7;
2009 end if;
2010
2011 if p_segment8 = hr_api.g_varchar2 then
2012 l_segment8 := null;
2013 else
2014 l_segment8 := p_segment8;
2015 end if;
2016
2017 if p_segment9 = hr_api.g_varchar2 then
2018 l_segment9 := null;
2019 else
2020 l_segment9 := p_segment9;
2021 end if;
2022
2023 if p_segment10 = hr_api.g_varchar2 then
2024 l_segment10 := null;
2025 else
2026 l_segment10 := p_segment10;
2027 end if;
2028
2029 if p_segment11 = hr_api.g_varchar2 then
2030 l_segment11 := null;
2031 else
2032 l_segment11 := p_segment11;
2033 end if;
2034
2035 if p_segment12 = hr_api.g_varchar2 then
2036 l_segment12 := null;
2037 else
2038 l_segment12 := p_segment12;
2039 end if;
2040
2041 if p_segment13 = hr_api.g_varchar2 then
2042 l_segment13 := null;
2043 else
2044 l_segment13 := p_segment13;
2045 end if;
2046
2047 if p_segment14 = hr_api.g_varchar2 then
2048 l_segment14 := null;
2049 else
2050 l_segment14 := p_segment14;
2051 end if;
2052
2053 if p_segment15 = hr_api.g_varchar2 then
2054 l_segment15 := null;
2055 else
2056 l_segment15 := p_segment15;
2057 end if;
2058
2059 if p_segment16 = hr_api.g_varchar2 then
2060 l_segment16 := null;
2061 else
2062 l_segment16 := p_segment16;
2063 end if;
2064
2065 if p_segment17 = hr_api.g_varchar2 then
2066 l_segment17 := null;
2067 else
2068 l_segment17 := p_segment17;
2069 end if;
2070
2071 if p_segment18 = hr_api.g_varchar2 then
2072 l_segment18 := null;
2073 else
2074 l_segment18 := p_segment18;
2075 end if;
2076
2077 if p_segment19 = hr_api.g_varchar2 then
2078 l_segment19 := null;
2079 else
2080 l_segment19 := p_segment19;
2081 end if;
2082
2083 if p_segment20 = hr_api.g_varchar2 then
2084 l_segment20:= null;
2085 else
2086 l_segment20 := p_segment20;
2087 end if;
2088
2089 if p_segment21 = hr_api.g_varchar2 then
2090 l_segment21 := null;
2091 else
2092 l_segment21 := p_segment21;
2093 end if;
2094
2095 if p_segment22 = hr_api.g_varchar2 then
2096 l_segment22 := null;
2097 else
2098 l_segment22 := p_segment22;
2099 end if;
2100
2101 if p_segment23 = hr_api.g_varchar2 then
2102 l_segment23 := null;
2103 else
2104 l_segment23 := p_segment23;
2105 end if;
2106
2107 if p_segment24 = hr_api.g_varchar2 then
2108 l_segment24 := null;
2109 else
2110 l_segment24 := p_segment24;
2111 end if;
2112
2113 if p_segment25 = hr_api.g_varchar2 then
2114 l_segment25 := null;
2115 else
2116 l_segment25 := p_segment25;
2117 end if;
2118
2119 if p_segment26 = hr_api.g_varchar2 then
2120 l_segment26 := null;
2121 else
2122 l_segment26 := p_segment26;
2123 end if;
2124
2125 if p_segment27 = hr_api.g_varchar2 then
2126 l_segment27 := null;
2127 else
2128 l_segment27 := p_segment27;
2129 end if;
2130
2131 if p_segment28 = hr_api.g_varchar2 then
2132 l_segment28 := null;
2133 else
2134 l_segment28 := p_segment28;
2135 end if;
2136
2137 if p_segment29 = hr_api.g_varchar2 then
2138 l_segment29 := null;
2139 else
2140 l_segment29 := p_segment29;
2141 end if;
2142
2143 if p_segment30 = hr_api.g_varchar2 then
2144 l_segment30 := null;
2145 else
2146 l_segment30 := p_segment30;
2147 end if;
2148
2149 if p_concat_segments_in = hr_api.g_varchar2 then
2150 l_concat_segments_in := null;
2151 else
2152 l_concat_segments_in := p_concat_segments_in;
2153 end if;
2154 --
2155 --
2156 ins_or_sel_keyflex_comb
2157 (p_appl_short_name => p_appl_short_name
2158 ,p_flex_code => p_flex_code
2159 ,p_flex_num => p_flex_num
2160 ,p_segment1 => l_segment1
2161 ,p_segment2 => l_segment2
2162 ,p_segment3 => l_segment3
2163 ,p_segment4 => l_segment4
2164 ,p_segment5 => l_segment5
2165 ,p_segment6 => l_segment6
2166 ,p_segment7 => l_segment7
2167 ,p_segment8 => l_segment8
2168 ,p_segment9 => l_segment9
2169 ,p_segment10 => l_segment10
2170 ,p_segment11 => l_segment11
2171 ,p_segment12 => l_segment12
2172 ,p_segment13 => l_segment13
2173 ,p_segment14 => l_segment14
2174 ,p_segment15 => l_segment15
2175 ,p_segment16 => l_segment16
2176 ,p_segment17 => l_segment17
2177 ,p_segment18 => l_segment18
2178 ,p_segment19 => l_segment19
2179 ,p_segment20 => l_segment20
2180 ,p_segment21 => l_segment21
2181 ,p_segment22 => l_segment22
2182 ,p_segment23 => l_segment23
2183 ,p_segment24 => l_segment24
2184 ,p_segment25 => l_segment25
2185 ,p_segment26 => l_segment26
2186 ,p_segment27 => l_segment27
2187 ,p_segment28 => l_segment28
2188 ,p_segment29 => l_segment29
2189 ,p_segment30 => l_segment30
2190 ,p_concat_segments_in => l_concat_segments_in
2191 ,p_ccid => p_ccid
2192 ,p_concat_segments_out => p_concat_segments_out
2193 );
2194 end if;
2195 --
2196 if g_debug then
2197 hr_utility.set_location('Leaving '|| l_proc, 130);
2198 end if;
2199 end if; -- if flex code in varray
2200 exception
2201 when app_exception.application_exception then
2202 if hr_multi_message.exception_add then
2203 if g_debug then
2204 hr_utility.set_location('Leaving '|| l_proc, 140);
2205 end if;
2206 raise;
2207 end if;
2208 if g_debug then
2209 hr_utility.set_location('Leaving '|| l_proc, 150);
2210 end if;
2211 when others then
2212 if hr_multi_message.exception_add then
2213 if g_debug then
2214 hr_utility.set_location('Leaving '|| l_proc, 160);
2215 end if;
2216 fnd_message.raise_error;
2217 end if;
2218 if g_debug then
2219 hr_utility.set_location('Leaving '|| l_proc, 170);
2220 end if;
2221 --reset out vaiables
2222 -- added for NOCOPY compliance
2223 p_ccid:=null;
2224 p_concat_segments_out:=null;
2225 end upd_or_sel_keyflex_comb;
2226 --
2227 --
2228 -- ----------------------------------------------------------------------------
2229 -- |------------------------------- set_profiles -----------------------------|
2230 -- ----------------------------------------------------------------------------
2231 --
2232 procedure set_profiles
2233 (p_business_group_id in per_all_assignments_f.business_group_id%type default hr_api.g_number
2234 ,p_assignment_id in per_all_assignments_f.assignment_id%type default null
2235 ,p_organization_id in per_all_assignments_f.organization_id%type default null
2236 ,p_location_id in per_all_assignments_f.location_id%type default null
2237 ,p_person_id in per_all_assignments_f.person_id%type default null
2238 ) is
2239 --
2240 l_proc varchar2(72):=' hr_kflex_utility.set_profiles ';
2241 --
2242 begin
2243 g_debug := hr_utility.debug_enabled;
2244 --
2245 if g_debug then
2246 l_proc := g_package||' set_profiles';
2247 hr_utility.set_location('Entering '||l_proc,5);
2248 end if;
2249 --
2250 fnd_profile.put('PER_ASSIGNMENT_ID',p_assignment_id);
2251 if g_debug then
2252 hr_utility.set_location(l_proc,10);
2253 end if;
2254 --
2255 if nvl(p_business_group_id,-1) <> hr_api.g_number then
2256 fnd_profile.put('PER_BUSINESS_GROUP_ID',p_business_group_id);
2257 if g_debug then
2258 hr_utility.set_location(l_proc,20);
2259 end if;
2260 end if;
2261 --
2262 fnd_profile.put('PER_ORGANIZATION_ID',p_organization_id);
2263 if g_debug then
2264 hr_utility.set_location(l_proc,30);
2265 end if;
2266 --
2267 fnd_profile.put('PER_LOCATION_ID',p_location_id);
2268 if g_debug then
2269 hr_utility.set_location(l_proc,40);
2270 end if;
2271 --
2272 fnd_profile.put('PER_PERSON_ID',P_PERSON_ID);
2273 if g_debug then
2274 hr_utility.set_location(l_proc,50);
2275 end if;
2276
2277 if g_debug then
2278 hr_utility.set_location('Leaving '||l_proc,100);
2279 end if;
2280 --
2281 end set_profiles;
2282 --
2283 --
2284 -- ----------------------------------------------------------------------------
2285 -- |----------------------------- set_session_date ---------------------------|
2286 -- ----------------------------------------------------------------------------
2287 --
2288 procedure set_session_date
2289 (p_effective_date in date
2290 ,p_session_id out nocopy number
2291 ) is
2292 l_proc varchar2(72);
2293 --
2294 cursor date_set is
2295 select 1 from fnd_sessions
2296 where session_id=userenv('sessionid');
2297 --
2298 l_dummy number;
2299 begin
2300 g_debug := hr_utility.debug_enabled;
2301 --
2302 if g_debug then
2303 l_proc := g_package||' set_session_date';
2304 hr_utility.set_location('Entering '||l_proc,5);
2305 end if;
2306 --
2307 open date_set;
2308 fetch date_set into l_dummy;
2309 if date_set%found then
2310 if g_debug then
2311 hr_utility.set_location(l_proc,20);
2312 end if;
2313 p_session_id:=-1;
2314 else
2315 if g_debug then
2316 hr_utility.set_location(l_proc,30);
2317 end if;
2318 dt_fndate.set_effective_date(trunc(p_effective_Date));
2319 -- insert into fnd_sessions
2320 -- values
2321 -- (userenv('sessionid')
2322 -- ,trunc(p_effective_date));
2323 p_session_id:=userenv('sessionid');
2324 end if;
2325 --
2326 if g_debug then
2327 hr_utility.set_location('Leaving '||l_proc,100);
2328 end if;
2329 --
2330 end set_session_date;
2331 --
2332 -- ----------------------------------------------------------------------------
2333 -- |------------------------- unset_session_date -----------------------------|
2334 -- ----------------------------------------------------------------------------
2335 --
2336 procedure unset_session_date
2337 (p_session_id in number
2338 ) is
2339 l_proc varchar2(72);
2340 begin
2341 g_debug := hr_utility.debug_enabled;
2342 if g_debug then
2343 l_proc := g_package||' unset_session_date';
2344 hr_utility.set_location('Entering '||l_proc,5);
2345 end if;
2346 --
2347 if nvl(p_session_id,-1)<>-1 then
2348 if g_debug then
2349 hr_utility.set_location(l_proc,10);
2350 end if;
2351 delete from fnd_sessions
2352 where session_id=p_session_id;
2353 end if;
2354 --
2355 if g_debug then
2356 hr_utility.set_location('Leaving '||l_proc,100);
2357 end if;
2358 --
2359 end unset_session_date;
2360 --
2361 -- ----------------------------------------------------------------------------
2362 -- |---------------------------- set_session_language_code--------------------|
2363 -- ----------------------------------------------------------------------------
2364 --
2365 -- {Start Of Comments}
2366 --
2367 -- Description:
2368 -- Set the session language from the language code and clears the
2369 -- key flex cache
2370 --
2371 -- Prerequisites:
2372 --
2373 -- In Parameters:
2374 -- Name Reqd Type Description
2375 -- ==== ==== ==== ===========
2376 -- p_language_code Yes varchar2 the Two digit language code
2377 --
2378 -- Post Success:
2379 -- userenv('LANG') is set to language code
2380 --
2381 -- Post Failure:
2382 -- user session language is not changed
2383 --
2384 --
2385 -- Access Status:
2386 -- Public - For Internal Development Use Only
2387 --
2388 -- {End Of Comments}
2389 --
2390 procedure set_session_language_code
2391 ( p_language_code in fnd_languages.language_code%TYPE
2392 ) IS
2393 l_proc varchar2(72) := g_package||' set_session_language_code';
2394 begin
2395 set_session_nls_language( pay_core_mls.get_nls_language( p_language_code ) );
2396 end;
2397 --
2398 -- ----------------------------------------------------------------------------
2399 -- |---------------------------- set_session_nls_language --------------------|
2400 -- ----------------------------------------------------------------------------
2401 --
2402 -- {Start Of Comments}
2403 --
2404 -- Description:
2405 -- Set the session language from the nls language and clears the
2406 -- key flex cache
2407 --
2408 -- Prerequisites:
2409 --
2410 -- In Parameters:
2411 -- Name Reqd Type Description
2412 -- ==== ==== ==== ===========
2413 -- p_nls_language Yes varchar2 The nls language (NOT the 2 letter language code)
2414 --
2415 -- Post Success:
2416 -- userev('LANG') is set to language code derived from nls language
2417 --
2418 -- Post Failure:
2419 -- user session language is not changed
2420 --
2421 --
2422 -- Access Status:
2423 -- Public - For Internal Development Use Only
2424 --
2425 -- {End Of Comments}
2426 --
2427 procedure set_session_nls_language
2428 ( p_nls_language in fnd_languages.nls_language%TYPE
2429 ) IS
2430 l_proc varchar2(72) := g_package||' set_session_nls_language';
2431 begin
2432 -- dbms_session will raise an error is nls_language is invalid
2433 dbms_session.set_nls('NLS_LANGUAGE', ''''||p_nls_language||''''); -- Bug #2958520
2434 fnd_flex_ext.clear_ccid_cache;
2435 exception
2436 when others then
2437 hr_utility.set_location('Error in '||l_proc, 99);
2438 raise;
2439 end;
2440 --
2441 --
2442 end hr_kflex_utility;