[Home] [Help]
PACKAGE BODY: APPS.BEN_ICD_FLEX_FIELD_SETUP
Source
1 PACKAGE BODY BEN_ICD_FLEX_FIELD_SETUP AS
2 /* $Header: beicdflexsetup.pkb 120.4.12010000.2 2009/01/21 11:43:23 vkodedal ship $ */
3 g_package varchar2(30) :='BEN_ICD_FLEX_FIELD_SETUP.';
4 ICD_ELE_CTXT_CD_PREFIX varchar2(30) := 'ICD_ELE_TYPE_';
5 FUNCTION lowest_seq_input_value(
6 P_ELEMENT_TYPE_ID IN VARCHAR2,
7 P_EFFECTIVE_DATE IN DATE
8 ) return NUMBER is
9 cursor c_input_value is
10 select input_value_id
11 from pay_input_values_f
12 where element_type_id = p_element_type_id
13 and p_effective_date between effective_start_date and effective_end_date
14 order by display_sequence asc;
15
16 l_proc varchar2(100) := g_package||'lowest_seq_input_value';
17 l_lowest_seq_input_value_id NUMBER;
18
19 begin
20 hr_utility.set_location('Entering: '||l_proc,10);
21
22 for l_input_value in c_input_value loop
23 l_lowest_seq_input_value_id := l_input_value.input_value_id;
24 exit;
25 end loop;
26
27 hr_utility.set_location('Leaving: '||l_proc,20);
28
29 return l_lowest_seq_input_value_id;
30 end lowest_seq_input_value;
31
32 FUNCTION create_lookup_valueset(
33 P_LOOKUP_TYPE IN VARCHAR2,
34 P_VIEW_APPLICATION_ID IN NUMBER,
35 P_FORMAT_TYPE IN VARCHAR2
36 ) return varchar2 is
37
38 l_proc varchar2(100) := g_package||'create_lookup_valueset';
39 l_description varchar2(150);
40 l_value_set_name varchar2(40);
41 l_where_clause1 varchar2(250);
42 l_where_clause2 varchar2(250);
43 l_where_clause varchar2(500);
44
45 begin
46 hr_utility.set_location('Entering: '||l_proc,10);
47
48 fnd_flex_val_api.set_session_mode('customer_data');
49 l_value_set_name := 'ICD_'||p_lookup_type;
50 l_description := 'ICD Value set for lookup type '||p_lookup_type;
51
52 /* When creating a value set for a Lookup, always enable "POPLIST" option for Valueset
53 This makes the corresponding segment appear as a POPLIST on the details page */
54
55 -- This splitting and joining the where clause is to avoid the GSCC chksql 6 error
56 l_where_clause1 := 'where lookup_type = '''||P_LOOKUP_TYPE||''' and language = USERENV(''LANG'') AND view_application_id = '|| P_VIEW_APPLICATION_ID ||' AND security_group_id = ';
57 l_where_clause2 := ' fnd_global.lookup_security_group('''|| P_LOOKUP_TYPE ||''','||P_VIEW_APPLICATION_ID||') ORDER BY meaning';
58 l_where_clause := l_where_clause1 || l_where_clause2;
59
60 if(NOT fnd_flex_val_api.valueset_exists(l_value_set_name)) then
61 hr_utility.set_location('Creating valueset: '||l_value_set_name,20);
62 fnd_flex_val_api.create_valueset_table(
63 /* basic parameters */
64 value_set_name => l_value_set_name,
65 description => l_description,
66 security_available => 'N', -- FLEX_VST_SECURITY_ENABLED_FLAG
67 enable_longlist => 'X', -- POPLIST
68 format_type => P_FORMAT_TYPE,
69 maximum_size => 60,
70 numbers_only => 'N',
71 uppercase_only => 'N',
72 right_justify_zero_fill => 'N',
73 min_value => NULL,
74 max_value => NULL,
75 /* Table validation parameters */
76 table_application => 'Application Object Library',
77 table_appl_short_name => 'FND',
78 table_name => 'FND_LOOKUP_VALUES',
79 allow_parent_values => 'N',
80 value_column_name => 'MEANING',
81 value_column_type => 'V',
82 value_column_size => 80,
83 id_column_name => 'LOOKUP_CODE',
84 id_column_type => 'V',
85 id_column_size => 30,
86 where_order_by => l_where_clause
87 );
88 end if;
89
90 hr_utility.set_location('Leaving: '||l_proc,30);
91
92 return l_value_set_name;
93
94 end create_lookup_valueset;
95
96
97 FUNCTION create_minmax_valueset(
98 P_VIEW_APPLICATION_ID IN NUMBER,
99 P_FORMAT_TYPE IN VARCHAR2,
100 P_MIN_VALUE IN VARCHAR2,
101 P_MAX_VALUE IN VARCHAR2
102 ) return varchar2 is
103
104 l_proc varchar2(100) := g_package||'create_minmax_valueset';
105 l_description varchar2(150);
106 l_value_set_name varchar2(40);
107
108 begin
109 hr_utility.set_location('Entering: '||l_proc,10);
110
111 fnd_flex_val_api.set_session_mode('customer_data');
112 l_value_set_name := 'ICD_'||P_MIN_VALUE||P_MAX_VALUE;
113 l_description := 'ICD Value set for Min Max Input Value: '||l_value_set_name;
114
115 if(NOT fnd_flex_val_api.valueset_exists(l_value_set_name)) then
116 hr_utility.set_location('Creating valueset: '||l_value_set_name,20);
117 fnd_flex_val_api.create_valueset_none(
118 /* basic parameters */
119 value_set_name => l_value_set_name,
120 description => l_description,
121 security_available => 'N',
122 enable_longlist => 'N',
123 format_type => P_FORMAT_TYPE,
124 maximum_size => 30,
125 -- precision IN NUMBER DEFAULT NULL,
126 numbers_only => 'N',
127 uppercase_only => 'N',
128 right_justify_zero_fill => 'N',
129 min_value => P_MIN_VALUE,
130 max_value => P_MAX_VALUE
131 );
132 end if;
133 hr_utility.set_location('Leaving: '||l_proc,30);
134
135 return l_value_set_name;
136
137 end create_minmax_valueset;
138
139 FUNCTION create_data_format_valueset(
140 p_FORMAT_TYPE IN VARCHAR2
141 ,P_maximum_size IN NUMBER
142 ,P_NUMBER_PRECISION IN NUMBER
143 ,P_INPUT_VALUE_UOM IN VARCHAR2
144 ) return varchar2 is
145
146 l_proc varchar2(100) := g_package||'create_data_format_valueset';
147 l_description varchar2(150);
148 l_value_set_name varchar2(40);
149 l_format_type_name varchar2(100);
150 l_format_type varchar2(10);
151 begin
152 hr_utility.set_location('Entering: '||l_proc,10);
153 fnd_flex_val_api.set_session_mode('customer_data');
154 l_value_set_name := 'ICD_DFF_'||P_INPUT_VALUE_UOM;
155 l_description := 'Only for internal use within ICD Developer Descriptive Flex Field';
156
157 if(NOT fnd_flex_val_api.valueset_exists(l_value_set_name)) then
158 hr_utility.set_location('Creating Valueset: '||l_value_set_name,20);
159 fnd_flex_val_api.create_valueset_none(
160 /* basic parameters */
161 value_set_name => l_value_set_name,
162 description => l_description,
163 security_available => 'N',
164 enable_longlist => 'N',
165 format_type => p_FORMAT_TYPE,
166 maximum_size => P_maximum_size,
167 precision => P_NUMBER_PRECISION,
168 numbers_only => 'N',
169 uppercase_only => 'N',
170 right_justify_zero_fill => 'N',
171 min_value => NULL,
172 max_value => NULL
173 );
174 end if;
175 hr_utility.set_location('Leaving: '||l_proc,30);
176
177 return l_value_set_name;
178
179 end create_data_format_valueset;
180
181 procedure CREATE_ICD_CONFIG
182 (
183 P_ELEMENT_TYPE_ID IN NUMBER,
184 P_EFFECTIVE_DATE IN DATE
185 ) is
186
187 /* Always order the Input Values by Id in asc */
188 cursor c_input_values(p_element_type_id number) is
189 select * from
190 pay_input_values_f
191 where element_type_id = p_element_type_id
192 and p_effective_date between effective_start_date and effective_end_date
193 order by input_value_id asc;
194
195 cursor c_element_type(p_element_type_id number) is
196 select * from
197 pay_element_types_f
198 where element_type_id = p_element_type_id
199 and p_effective_date between effective_start_date and effective_end_date;
200
201 i Number;
202 j Number;
203 n Number;
204 l_proc varchar2(100) := g_package||'create_icd_config';
205 l_element_type_id pay_element_types_f.element_type_id%TYPE;
206 l_element_entry_id number;
207 l_context_code FND_DESCR_FLEX_CONTEXTS.DESCRIPTIVE_FLEX_CONTEXT_CODE%TYPE;
208 l_element_type pay_element_types_f%ROWTYPE;
209 l_value_set_name fnd_flex_value_sets.flex_value_set_name%TYPE;
210 l_lookup_value_set_name fnd_flex_value_sets.flex_value_set_name%TYPE;
211 l_minmax_value_set_name fnd_flex_value_sets.flex_value_set_name%TYPE;
212 l_dataformat_value_set_name fnd_flex_value_sets.flex_value_set_name%TYPE;
213 l_segment_display_size fnd_descr_flex_column_usages.display_size%TYPE;
214 l_value_set_format_type fnd_flex_value_sets.format_type%TYPE;
215 l_value_set_maximum_size fnd_flex_value_sets.maximum_size%TYPE;
216 l_value_set_number_precision fnd_flex_value_sets.number_precision%TYPE;
217 l_user_enterable_flag ben_icd_ss_config.USER_ENTERABLE_FLAG%TYPE;
218 l_show_on_overview_flag ben_icd_ss_config.SHOW_ON_OVERVIEW_flag%TYPE;
219 l_context_exists_flag boolean;
220 l_ss_config_exists_flag boolean;
221 l_lowest_seq_input_value_id number;
222 l_required varchar2(1);
223 l_input_value_id pay_input_values_f.input_value_id%TYPE;
224
225 begin
226 hr_utility.set_location('Entering: '||l_proc,10);
227
228 l_context_exists_flag := true;
229
230 -- fetch Element Type Id
231
232 /* Un comment this if you have to create context based on Element_Link_Id
233 select element_type_id into l_element_type_id
234 from PAY_ELEMENT_LINKS_F
235 where element_link_id = P_ELEMENT_LINK_ID
236 and p_effective_date between effective_start_date and effective_end_date; */
237
238 -- Comment the below line if you have to create context based on Element_Link_Id
239 l_element_type_id := p_element_type_id;
240
241 -- Copy the element entry row if it already exists
242 -- Create a new transaction row if it is new
243
244 -- Create a new context for the Element Type
245
246
247 open c_element_type(l_element_type_id);
248 fetch c_element_type into l_element_type;
249 close c_element_type;
250
251 fnd_flex_dsc_api.set_session_mode('customer_data');
252
253 begin
254 select DESCRIPTIVE_FLEX_CONTEXT_CODE into l_context_code
255 from FND_DESCR_FLEX_CONTEXTS
256 where application_id = 805
257 and DESCRIPTIVE_FLEXFIELD_NAME = 'Ben ICD Developer DF'
258 and DESCRIPTIVE_FLEX_CONTEXT_CODE = to_char(l_element_type.element_type_id);
259 EXCEPTION
260 when no_data_found then
261 l_context_exists_flag := false;
262 end;
263
264 if(NOT l_context_exists_flag) then
265 begin
266 hr_utility.set_location('Creating Context: '||to_char(l_element_type.element_type_id),20);
267 fnd_flex_dsc_api.create_context(
268 /* identification */
269 appl_short_name => 'BEN',
270 flexfield_name => 'Ben ICD Developer DF',
271 /* data */
272 context_code => to_char(l_element_type.element_type_id),
273 context_name => l_element_type.element_name,
274 description => l_element_type.reporting_name,
275 enabled => 'Y',
276 global_flag => 'N'
277 );
278
279 i := 1;
280 l_lowest_seq_input_value_id := lowest_seq_input_value(p_element_type_id,p_effective_date);
281 for l_input_value in c_input_values(l_element_type_id) loop
282 hr_utility.set_location('Beginning processing InputValue: '||to_char(l_input_value.input_value_id),30);
283 l_ss_config_exists_flag := true;
284
285 /* Converting Input Value Data formats to Value Set Data format types */
286 if('C'=l_input_value.UOM) then
287 l_value_set_format_type :='C';
288 l_value_set_maximum_size:= 60;
289 l_value_set_number_precision:=null;
290 l_segment_display_size := 30;
291 elsif('D'=l_input_value.UOM) then
292 l_value_set_format_type := 'X' ;
293 l_value_set_maximum_size:= 11;
294 l_value_set_number_precision:=null;
295 l_segment_display_size := 11;
296 elsif('H_DECIMAL1'=l_input_value.UOM) then
297 l_value_set_format_type := 'N';
298 l_value_set_maximum_size:= 16;
299 l_value_set_number_precision:= 1;
300 l_segment_display_size := 17;
301 elsif('H_DECIMAL2'=l_input_value.UOM) then
302 l_value_set_format_type := 'N' ;
303 l_value_set_maximum_size:= 17;
304 l_value_set_number_precision:= 2;
305 l_segment_display_size := 18;
306 elsif('H_DECIMAL3'=l_input_value.UOM) then
307 l_value_set_format_type := 'N';
308 l_value_set_maximum_size:= 18;
309 l_value_set_number_precision:= 3;
310 l_segment_display_size := 19;
311 elsif('H_HH'=l_input_value.UOM) then
312 l_value_set_format_type := 'N';
313 l_value_set_maximum_size:= 38 ;
314 l_value_set_number_precision:= 0;
315 l_segment_display_size := 30;
316 elsif('H_HHMM'=l_input_value.UOM) then
317 l_value_set_format_type := 'C';
318 l_value_set_maximum_size:= 40;
319 l_value_set_number_precision:=0;
320 l_segment_display_size := 30;
321 elsif('H_HHMMSS'=l_input_value.UOM) then
322 l_value_set_format_type := 'C';
323 l_value_set_maximum_size:= 40;
324 l_value_set_number_precision:=null;
325 l_segment_display_size := 30;
326 elsif('I'=l_input_value.UOM) then
327 l_value_set_format_type := 'N';
328 l_value_set_maximum_size:= 20;
329 l_value_set_number_precision:= 0;
330 l_segment_display_size := 20;
331 elsif('M'=l_input_value.UOM) then
332 l_value_set_format_type := 'N';
333 l_value_set_maximum_size:= 38;
334 l_value_set_number_precision:= null;
335 l_segment_display_size := 30;
336 elsif('N'=l_input_value.UOM) then
337 l_value_set_format_type := 'N';
338 l_value_set_maximum_size:= 38;
339 l_value_set_number_precision:= null;
340 l_segment_display_size := 30;
341 elsif('ND' =l_input_value.UOM) then
342 l_value_set_format_type := 'N';
343 l_value_set_maximum_size:= 38;
347 l_value_set_format_type := 'I';
344 l_value_set_number_precision:= null;
345 l_segment_display_size := 30;
346 elsif('T'=l_input_value.UOM) then
348 l_value_set_maximum_size:= 5;
349 l_value_set_number_precision:= null;
350 l_segment_display_size := 5;
351 end if;
352 /*
353 if( 'C' = l_input_value.UOM OR 'D' = l_input_value.UOM OR 'N' = l_input_value.UOM) then
354 l_value_set_format_type := l_input_value.UOM;
355 elsif ('M' = l_input_value.UOM) then
356 l_value_set_format_type := 'N';
357 elsif ('I' = l_input_value.UOM) then
358 l_value_set_format_type := 'N'; -- For now, converting Integer to Number
359 else
360 l_value_set_format_type := 't'; -- For now, converting all other Date / Time formats in Input Value to Time format of ValueSet
361 end if;
362 */
363 if(l_input_value.mandatory_flag = 'Y') then
364 l_required := 'Y';
365 else
366 l_required := 'N';
367 end if;
368
369
370 if(l_input_value.value_set_id is not null) then
371 begin
372 begin
373 SELECT
374 flex_value_set_name,maximum_size into l_value_set_name,l_value_set_maximum_size
375 FROM fnd_flex_value_sets
376 WHERE flex_value_set_id =l_input_value.value_set_id;
377 Exception
378 When No_Data_Found Then
379 RAISE;
380 When others then
381 RAISE;
382 end;
383
384 if(l_segment_display_size <= l_value_set_maximum_size) then
385 l_segment_display_size:=l_value_set_maximum_size;
386 end if ;
387 hr_utility.set_location('Fetched valueset: '||l_value_set_name,31);
388 hr_utility.set_location('Creating Segment for InputValue: '||to_char(l_input_value.input_value_id),32);
389 fnd_flex_dsc_api.create_segment(
390 /* identification */
391 appl_short_name => 'BEN',
392 flexfield_name => 'Ben ICD Developer DF',
393 context_name => to_char(l_element_type.element_type_id),
394 /* data */
395 --vkodedal 7827903 - append string to input value id
396 name => 'ICD_'||l_input_value.input_value_id,
397 column => 'INPUT_VALUE'||i,
398 description => 'INPUT_VALUE'||i,
399 sequence_number => l_input_value.display_sequence,
400 enabled => 'Y',
401 displayed => 'Y',
402 /* validation */
403 value_set => l_value_set_name,
404 default_type => '',
405 default_value => '',
406 required => l_required,
407 security_enabled=> 'N',
408 /* sizes */
409 display_size => l_segment_display_size,
410 description_size=> 50,
411 concatenated_description_size => 25,
412 list_of_values_prompt => l_input_value.name,
413 window_prompt => l_input_value.name
414 -- range => NULL,
415 -- srw_parameter => NULL
416 );
417 end;
418 elsif(l_input_value.LOOKUP_TYPE is not null) then
419 begin
420 l_lookup_value_set_name := create_lookup_valueset(l_input_value.LOOKUP_TYPE,3,l_value_set_format_type);
421 hr_utility.set_location('Creating Segment for InputValue: '||to_char(l_input_value.input_value_id),33);
422 fnd_flex_dsc_api.create_segment(
423 /* identification */
424 appl_short_name => 'BEN',
425 flexfield_name => 'Ben ICD Developer DF',
426 context_name => to_char(l_element_type.element_type_id),
427 /* data */
428 --vkodedal 7827903 - append string to input value id
429 name => 'ICD_'||l_input_value.input_value_id,
430 column => 'INPUT_VALUE'||i,
431 description => 'INPUT_VALUE'||i,
432 sequence_number => l_input_value.display_sequence,
433 enabled => 'Y',
434 displayed => 'Y',
435 /* validation */
436 value_set => l_lookup_value_set_name,
437 default_type => '',
438 default_value => '',
439 required => l_required,
440 security_enabled => 'N',
441 /* sizes */
442 display_size => 30,
443 description_size => 50,
444 concatenated_description_size => 25,
445 list_of_values_prompt => l_input_value.name,
446 window_prompt => l_input_value.name
447 -- range => NULL,
448 -- srw_parameter => NULL
449 );
450
451 end;
452 /*this can not be created on element because links may have a different min max and defaults therefore leaving
453 this validation for the payroll apis. Still we need to create the proper format for the data type.
454
455 elsif(l_input_value.MIN_VALUE is not null or l_input_value.MAX_VALUE is not null) then
456 begin
457 l_minmax_value_set_name := create_minmax_valueset(3,l_value_set_format_type,l_input_value.MIN_VALUE,l_input_value.MAX_VALUE);
458 hr_utility.set_location('Creating Segment for InputValue: '||to_char(l_input_value.input_value_id),34);
459 fnd_flex_dsc_api.create_segment(
460 appl_short_name => 'BEN',
461 flexfield_name => 'Ben ICD Developer DF',
462 context_name => to_char(l_element_type.element_type_id),
463 name => to_char(l_input_value.input_value_id),
464 column => 'INPUT_VALUE'||i,
465 description => 'INPUT_VALUE'||i,
466 sequence_number => l_input_value.display_sequence,
470 default_type => '',
467 enabled => 'Y',
468 displayed => 'Y',
469 value_set => l_minmax_value_set_name,
471 default_value => '',
472 required => l_required,
473 security_enabled => 'N',
474 display_size => 50,
475 description_size => 50,
476 concatenated_description_size => 25,
477 list_of_values_prompt => l_input_value.name,
478 window_prompt => l_input_value.name
479 -- range => NULL,
480 -- srw_parameter => NULL
481 );
482 end;
483 */
484
485
486 else
487 begin
488 l_dataformat_value_set_name :=
489 create_data_format_valueset(l_value_set_format_type,l_value_set_maximum_size,l_value_set_number_precision,l_input_value.UOM);
490
491 hr_utility.set_location('Creating Segment for InputValue: '||to_char(l_input_value.input_value_id),35);
492 fnd_flex_dsc_api.create_segment(
493 /* identification */
494 appl_short_name => 'BEN',
495 flexfield_name => 'Ben ICD Developer DF',
496 context_name => to_char(l_element_type.element_type_id),
497 /* data */
498 --vkodedal 7827903 - append string to input value id
499 name => 'ICD_'||l_input_value.input_value_id,
500 column => 'INPUT_VALUE'||i,
501 description => 'INPUT_VALUE'||i,
502 sequence_number => l_input_value.display_sequence,
503 enabled => 'Y',
504 displayed => 'Y',
505 /* validation */
506 value_set => l_dataformat_value_set_name,
507 default_type => '',
508 default_value => '',
509 required => l_required,
510 security_enabled => 'N',
511 /* sizes */
512 display_size => l_segment_display_size,
513 description_size => 50,
514 concatenated_description_size => 25,
515 list_of_values_prompt => l_input_value.name,
516 window_prompt => l_input_value.name
517 -- range => NULL,
518 -- srw_parameter => NULL
519 );
520 end;
521 end if;
522
523 -- Create a row in SS_CONFIG
524 if('Pay Value' = l_input_value.name) then
525 l_show_on_overview_flag := 'Y';
526 else
527 l_show_on_overview_flag := 'N';
528 end if;
529
530 if(l_input_value.mandatory_flag = 'Y' or l_input_value.mandatory_flag = 'N') then
531 l_user_enterable_flag := 'Y';
532 else
533 l_user_enterable_flag := 'N';
534 end if;
535
536 -- Create the SS CONFIG information for this Input Value
537
538 begin
539 select input_value_id into l_input_value_id
540 from ben_icd_ss_config
541 where input_value_id = l_input_value.input_value_id;
542 EXCEPTION
543 when no_data_found then
544 l_ss_config_exists_flag := false;
545 end;
546
547 if(NOT l_ss_config_exists_flag) then
548 hr_utility.set_location('Creating Config Information',36);
549 insert into ben_icd_ss_config(element_type_id,input_value_id,uom,show_on_overview_flag,user_enterable_flag,input_value_id_char,order_num)
550 values(l_element_type_id,l_input_value.input_value_id,l_input_value.uom,l_show_on_overview_flag,l_user_enterable_flag,to_char(l_input_value.input_value_id),i);
551 else
552 hr_utility.set_location('Updating Config Information',37);
553 update ben_icd_ss_config
554 set
555 uom = l_input_value.uom
556 ,show_on_overview_flag = l_show_on_overview_flag
557 ,user_enterable_flag = l_user_enterable_flag
558 where input_value_id = l_input_value.input_value_id;
559 end if;
560
561 i := i + 1;
562
563 end loop;
564 --flag an input value as show on overview if none is flagged by now.
565 update ben_icd_ss_config
566 set show_on_overview_flag = 'Y'
567 where input_value_id = l_lowest_seq_input_value_id
568 and not exists (select 'Y' from ben_icd_ss_config
569 where element_type_id = p_element_type_id
570 and show_on_overview_flag = 'Y');
571 end;
572 end if;
573 hr_utility.set_location('Leaving: '||l_proc,40);
574 end CREATE_ICD_CONFIG;
575
576 procedure UPDATE_ICD_CONFIG(
577 P_ELEMENT_TYPE_ID IN NUMBER,
578 P_INPUT_VALUE_ID IN NUMBER,
579 P_COLUMN_SEQ_NUM IN NUMBER,
580 P_SELF_SERVICE_DISPLAY_PROMPT IN VARCHAR2,
581 P_HIDDEN_IN_SELFSERVICE IN VARCHAR2,
582 P_USER_ENTERABLE_FLAG IN VARCHAR2,
583 P_SHOW_ON_OVERVIEW_FLAG IN VARCHAR2
584 ) is
585
586 cursor c_input_value_segment is
587 select *
588 from fnd_descr_flex_column_usages
589 where application_id = 805
590 and descriptive_flexfield_name = 'Ben ICD Developer DF'
591 and descriptive_flex_context_code = to_char(p_element_type_id)
592 --vkodedal 7827903 - append string to input value id
593 and end_user_column_name = 'ICD_'||p_input_value_id;
594
595 cursor c_value_set_name(p_value_set_id number) is
596 select flex_value_set_name
597 from fnd_flex_value_sets
598 where flex_value_set_id = p_value_set_id;
599
600 cursor c_check_show_on_overview(P_ELEMENT_TYPE_ID NUMBER,P_INPUT_VALUE_ID number) is
601 select input_value_id from ben_icd_ss_config
602 where element_type_id = p_element_type_id
603 and show_on_overview_flag = 'Y'
604 and input_value_id <> P_INPUT_VALUE_ID;
605
606 l_proc varchar2(100) := g_package||'update_icd_config';
607 l_display fnd_descr_flex_column_usages.display_flag%TYPE;
608 l_value_set_name fnd_flex_value_sets.flex_value_set_name%TYPE;
609 l_input_value_segment c_input_value_segment%ROWTYPE;
610 l_input_value_id number;
611 begin
612 hr_utility.set_location('Entering: '||l_proc,10);
613
614 -- test that any other input value has check_on_overview flag checked
615
616 if('Y' = P_SHOW_ON_OVERVIEW_FLAG) then
617 open c_check_show_on_overview(P_ELEMENT_TYPE_ID,P_INPUT_VALUE_ID);
618 fetch c_check_show_on_overview into l_input_value_id;
619 if( c_check_show_on_overview%found) then
620 update ben_icd_ss_config
621 set show_on_overview_flag = 'N'
622 where input_value_id = l_input_value_id;
623 end if;
624 close c_check_show_on_overview;
625 end if;
626
627 fnd_flex_dsc_api.set_session_mode('customer_data');
628
629 open c_input_value_segment;
630 fetch c_input_value_segment into l_input_value_segment;
631 close c_input_value_segment;
632
633 if(P_HIDDEN_IN_SELFSERVICE = 'Y') then
634 l_display := 'N';
635 else l_display := 'Y';
636 end if;
637
638 open c_value_set_name(l_input_value_segment.flex_value_set_id);
639 fetch c_value_set_name into l_value_set_name;
640 close c_value_set_name;
641
642 hr_utility.set_location('modifying the segment afresh',30);
643 -- Create the segment afresh with the new configuration options
644
645
646 fnd_flex_dsc_api.modify_segment(
647 p_appl_short_name => 'BEN',
648 p_flexfield_name => 'Ben ICD Developer DF',
649 p_context_code => to_char(p_element_type_id),
650 --vkodedal 7827903 - append string to input value id
651 p_segment_name => 'ICD_'||p_input_value_id,
652 -- p_column_name => l_input_value_segment.application_column_name,
653 p_sequence_number => P_COLUMN_SEQ_NUM,
654 p_displayed => l_display,
655 p_lov_prompt => P_SELF_SERVICE_DISPLAY_PROMPT ,
656 p_window_prompt => P_SELF_SERVICE_DISPLAY_PROMPT
657 );
658
659
660 hr_utility.set_location('Updating SS Config info',40);
661 -- if( 'Y' = p_show_on_overview_flag) then
662 /*check if there is any other checked already then error*/
663
664 update ben_icd_ss_config
665 set user_enterable_flag = p_user_enterable_flag,
666 show_on_overview_flag = p_show_on_overview_flag
667 where input_value_id = p_input_value_id;
668
669 hr_utility.set_location('Leaving: '||l_proc,10);
670 exception
671 when others then
672 raise;
673 end UPDATE_ICD_CONFIG;
674
675
676 procedure REFRESH_ICD_CONFIG (
677 P_ELEMENT_TYPE_ID IN NUMBER,
678 P_EFFECTIVE_DATE IN DATE
679 ) is
680 l_proc varchar2(100) := g_package||'refresh_icd_config';
681 begin
682 hr_utility.set_location('Entering: '||l_proc,10);
683 -- Refresh is invoked when user has made some changes
684 -- to the Element Type definition and wants to
685 -- see those changes in ICD too. So, first
686 -- delete all the previous ICD specific data
687 -- created for Element Type and create them again
688 hr_utility.set_location('Deleting the existing flex setup of '||to_char(p_element_type_id),20);
689 delete_icd_config(p_element_type_id,p_effective_date);
690
691 hr_utility.set_location('Creating the flex setup afresh for '||to_char(p_element_type_id),30);
692 -- Create Icd Config data afresh for the element type
693 CREATE_ICD_CONFIG (
694 P_ELEMENT_TYPE_ID => p_element_type_id
695 ,P_EFFECTIVE_DATE => p_effective_date
696 );
697 hr_utility.set_location('Leaving: '||l_proc,40);
698 end REFRESH_ICD_CONFIG;
699
700 procedure DELETE_ICD_CONFIG (
701 P_ELEMENT_TYPE_ID IN NUMBER,
702 P_EFFECTIVE_DATE IN DATE
703 ) is
704 l_proc varchar2(100) := g_package||'delete_icd_config';
705 begin
706 hr_utility.set_location('Entering: '||l_proc,10);
707
708 -- Deleting the existing Descriptive Flex information of this Element Type
709 hr_utility.set_location('Deleting flex context of: '||to_char(p_element_type_id),20);
710 fnd_flex_dsc_api.delete_context(
711 appl_short_name => 'BEN',
712 flexfield_name => 'Ben ICD Developer DF',
713 context => to_char(p_element_type_id));
714 hr_utility.set_location('Deleting ss config info of: '||to_char(p_element_type_id),30);
715 -- Delete the existing SS Config information
716 delete from ben_icd_ss_config
717 where element_type_id = p_element_type_id;
718
719 hr_utility.set_location('Leaving: '||l_proc,40);
720 end DELETE_ICD_CONFIG;
721
722 END BEN_ICD_FLEX_FIELD_SETUP;