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