[Home] [Help]
PACKAGE BODY: APPS.HXC_CREATE_FLEX_MAPPINGS
Source
1 PACKAGE BODY hxc_create_flex_mappings AS
2 /* $Header: hxcflxdn.pkb 120.13.12020000.2 2012/09/26 16:02:33 bbayragi ship $ */
3 --
4 -- Global store for the number of global data elements
5 --
6 g_debug boolean := hr_utility.debug_enabled;
7 g_global_segment_count NUMBER := 0;
8
9 FUNCTION check_delete_info_type(p_info_type_basis VARCHAR2)
10 return NUMBER is
11
12 cursor c_delete(p_info_type_basis in VARCHAR2) is
13 select bbi.bld_blk_info_type_id
14 from hxc_mapping_comp_usages mcu,
15 hxc_mapping_components mc,
16 hxc_bld_blk_info_types bbi
17 where mcu.mapping_component_id = mc.mapping_component_id
18 and mc.bld_blk_info_type_id = bbi.bld_blk_info_type_id
19 and upper(bld_blk_info_type) like '%'||p_info_type_basis||'%';
20
21 l_bld_blk_info_type_id HXC_BLD_BLK_INFO_TYPES.BLD_BLK_INFO_TYPE_ID%TYPE;
22
23 BEGIN
24
25 open c_delete(p_info_type_basis);
26 fetch c_delete into l_bld_blk_info_type_id;
27
28 if c_delete%NOTFOUND then
29 l_bld_blk_info_type_id := NULL;
30 end if;
31
32 close c_delete;
33
34 RETURN l_bld_blk_info_type_id;
35
36 END check_delete_info_type;
37
38 FUNCTION valid_to_add_map
39 (p_map_id in HXC_MAPPING_COMPONENTS.MAPPING_COMPONENT_ID%TYPE
40 ,p_process_map in HXC_DEPOSIT_PROCESSES.NAME%TYPE) RETURN BOOLEAN is
41
42 cursor field_already_mapped(p_id in number,p_name in varchar2) is
43 SELECT 'Y'
44 from hxc_mapping_comp_usages u,
45 hxc_mappings m
46 where m.name = p_name
47 and u.mapping_id = m.mapping_id
48 and u.mapping_component_id in
49 (select c2.mapping_component_id
50 from hxc_mapping_components c1,
51 hxc_mapping_components c2
52 where c1.mapping_component_id = p_id
53 and c2.segment = c1.segment
54 and c2.field_name = c1.field_name
55 and c2.bld_blk_info_type_id = c1.bld_blk_info_type_id
56 and c2.mapping_component_id <> c1.mapping_component_id);
57
58 cursor field_name_already
59 (p_id in number
60 ,p_name in varchar2) is
61 select 'Y'
62 from hxc_mappings m, hxc_mapping_comp_usages u, hxc_mapping_components c
63 where m.mapping_id = u.mapping_id
64 and m.name = p_name
65 and u.mapping_component_id = c.mapping_component_id
66 and c.field_name =
67 (select field_name
68 from hxc_mapping_components c1
69 where c1.mapping_component_id = p_id);
70
71 l_valid BOOLEAN := FALSE;
72 l_dummy VARCHAR2(2);
73
74 BEGIN
75
76 open field_name_already(p_map_id, p_process_map);
77 fetch field_name_already into l_dummy;
78
79 IF (field_name_already%FOUND) then
80 l_valid := FALSE;
81 else
82 l_valid := TRUE;
83 end if;
84
85 close field_name_already;
86
87 if(l_valid) then
88
89 open field_already_mapped(p_map_id, p_process_map);
90 fetch field_already_mapped into l_dummy;
91
92 if (field_already_mapped%FOUND) then
93 l_valid := FALSE;
94 else
95 l_valid := TRUE;
96 end if;
97
98 close field_already_mapped;
99
100 end if;
101
102 return l_valid;
103
104 END valid_to_add_map;
105
106 PROCEDURE add_comp_to_proc
107 (p_map_id in NUMBER
108 ,p_process_name in VARCHAR2) is
109
110 cursor c_mapping_id(p_mapping_process_name in VARCHAR2) is
111 select mapping_id
112 from hxc_mappings m
113 where m.name = p_mapping_process_name;
114
115 l_mapping_id HXC_MAPPINGS.MAPPING_ID%TYPE;
116 l_map_comp_usage_id NUMBER;
117 l_map_comp_ovn NUMBER;
118
119 BEGIN
120
121 -- Obtain the mapping id and call the
122 -- mapping component usage API to
123 -- insert the record.
124
125 open c_mapping_id(p_process_name);
126 fetch c_mapping_id into l_mapping_id;
127
128 if (c_mapping_id%FOUND) then
129
130 hxc_mapping_comp_usage_api.create_mapping_comp_usage
131 (FALSE
132 ,l_map_comp_usage_id
133 ,l_map_comp_ovn
134 ,l_mapping_id
135 ,p_map_id
136 );
137
138 end if;
139
140 close c_mapping_id;
141
142 END add_comp_to_proc;
143
144 PROCEDURE include_mapping_components
145 (p_info_type_basis in HXC_BLD_BLK_INFO_TYPES.BLD_BLK_INFO_TYPE%TYPE)
146 IS
147
148
149 cursor c_map_comp(
150 p_info_type_basis in HXC_BLD_BLK_INFO_TYPES.BLD_BLK_INFO_TYPE%TYPE
151 ) is
152 select mc.mapping_component_id
153 from hxc_mapping_components mc,
154
155 hxc_bld_blk_info_types bbit
156 where upper(bbit.bld_blk_info_type) like '%'||upper(p_info_type_basis)||'%'
157 and bbit.bld_blk_info_type_id = mc.bld_blk_info_type_id;
158
159
160 BEGIN
161
162 for map_rec in c_map_comp(p_info_type_basis) LOOP
163
164 -- Check to see if the mapping exists in the OTL Deposit Process
165
166 if(valid_to_add_map(map_rec.mapping_component_id
167 ,'Projects Retrieval Process Mapping')) then
168
169 --Ok, we can add it to the process mapping.
170
171 add_comp_to_proc
172 (map_rec.mapping_component_id
173 ,'Projects Retrieval Process Mapping'
174 );
175
176 end if;
177
178 if(valid_to_add_map(map_rec.mapping_component_id
179 ,'OTL Deposit Process Mapping')) then
180
181 add_comp_to_proc
182 (map_rec.mapping_component_id
183 ,'OTL Deposit Process Mapping'
184 );
185
186 end if;
187
188 END LOOP;
189
190 END include_mapping_components;
191
192 PROCEDURE remove_mapping_component(
193 p_info_type_basis in HXC_BLD_BLK_INFO_TYPES.BLD_BLK_INFO_TYPE%TYPE)
194 IS
195
196 cursor c_map_comp(
197 p_info_type_basis in HXC_BLD_BLK_INFO_TYPES.BLD_BLK_INFO_TYPE%TYPE
198 ) is
199 select mc.mapping_component_id
200 from hxc_mapping_components mc,
201 hxc_bld_blk_info_types bbit
202 where upper(bbit.bld_blk_info_type) like '%'||upper(p_info_type_basis)||'%'
203 and bbit.bld_blk_info_type_id = mc.bld_blk_info_type_id;
204
205 BEGIN
206
207 if(p_info_type_basis = 'PAEXPITDFF') then
208
209 --
210 -- for PA only remove the DFF based columns from the mapping
211 -- component usages.
212 --
213
214 for map_rec in c_map_comp(p_info_type_basis) LOOP
215
216 DELETE from hxc_mapping_comp_usages
217 where mapping_component_id = map_rec.mapping_component_id;
218
219 end LOOP;
220
221 end if;
222
223 for map_rec in c_map_comp(p_info_type_basis) LOOP
224
225 DELETE from HXC_MAPPING_COMPONENTS
226 where mapping_component_id = map_rec.mapping_component_id;
227
228 END LOOP;
229
230 END remove_mapping_component;
231
232 PROCEDURE remove_bld_blk_usage(
233 p_info_type_basis in HXC_BLD_BLK_INFO_TYPES.BLD_BLK_INFO_TYPE%TYPE)
234 IS
235
236 cursor c_bld_blk_usage(
237 p_info_type_basis in HXC_BLD_BLK_INFO_TYPES.BLD_BLK_INFO_TYPE%TYPE
238 ) is
239 select bbu.bld_blk_info_type_usage_id
240 from hxc_bld_blk_info_type_usages bbu,
241 hxc_bld_blk_info_types bbit
242 where upper(bbit.bld_blk_info_type) like '%'||upper(p_info_type_basis)||'%'
243 and bbit.bld_blk_info_type_id = bbu.bld_blk_info_type_id;
244
245 BEGIN
246
247 for usage_rec in c_bld_blk_usage(p_info_type_basis) LOOP
248
249 DELETE from HXC_BLD_BLK_INFO_TYPE_USAGES
250 where bld_blk_info_type_usage_id = usage_rec.bld_blk_info_type_usage_id;
251
252 END LOOP;
253
254 END remove_bld_blk_usage;
255
256 PROCEDURE remove_flex_context(
257 p_appl_short_name in FND_APPLICATION.APPLICATION_SHORT_NAME%TYPE,
258 p_flexfield_name in FND_DESCRIPTIVE_FLEXS.DESCRIPTIVE_FLEXFIELD_NAME%TYPE,
259 p_flex_context_basis in VARCHAR2) IS
260
261 cursor c_flex(
262 p_flex_context_basis in HXC_BLD_BLK_INFO_TYPES.BLD_BLK_INFO_TYPE%TYPE
263 ) is
264 select bld_blk_info_type
265 from hxc_bld_blk_info_types
266 where upper(bld_blk_info_Type) like '%'||upper(p_flex_context_basis)||'%';
267
268 BEGIN
269
270 for context_rec in c_flex(p_flex_context_basis) LOOP
271
272 if FND_FLEX_DSC_API.context_exists(
273 P_APPL_SHORT_NAME => p_appl_short_name,
274 P_FLEXFIELD_NAME => p_flexfield_name,
275 P_CONTEXT_CODE => context_rec.bld_blk_info_type
276 ) then
277
278
279 FND_FLEX_DSC_API.delete_context(
280 APPL_SHORT_NAME => p_appl_short_name,
281 FLEXFIELD_NAME => p_flexfield_name,
282 CONTEXT => context_rec.bld_blk_info_type);
283
284 end if; -- Does this element context exist?
285
286 END LOOP;
287
288 END remove_flex_context;
289
290 PROCEDURE remove_bld_blk_info_type(
291 p_bld_blk_info_type_basis in HXC_BLD_BLK_INFO_TYPES.BLD_BLK_INFO_TYPE%TYPE
292 ) is
293
294 BEGIN
295
296 DELETE from HXC_BLD_BLK_INFO_TYPES
297 where upper(bld_blk_info_type) like '%'||upper(p_bld_blk_info_type_basis)||'%';
298
299 END remove_bld_blk_info_type;
300
301 PROCEDURE create_preference_definitions(
302 p_flex_name in FND_DESCRIPTIVE_FLEXS.DESCRIPTIVE_FLEXFIELD_NAME%TYPE
303 ,p_appl_short_name in FND_APPLICATION.APPLICATION_SHORT_NAME%TYPE) is
304
305
306 cursor c_flex_context_name(
307 p_flex_name in FND_DESCRIPTIVE_FLEXS.DESCRIPTIVE_FLEXFIELD_NAME%TYPE
308 ,p_appl_short_name in FND_APPLICATION.APPLICATION_SHORT_NAME%TYPE
309 ) is
310 select c.descriptive_flex_context_code, c.descriptive_flex_context_name
311 from fnd_descr_flex_contexts_vl c,
312 fnd_application a
313 where c.descriptive_flexfield_name = p_flex_name
314 and a.application_short_name = p_appl_short_name
315 and a.application_id = c.application_id
316 and c.descriptive_flex_context_code <> 'Global Data Elements';
317
318 BEGIN
319
320 --
321 -- Check the preference definition for each context
322 --
323
324 for context_rec in c_flex_context_name(p_flex_name, p_appl_short_name) LOOP
325
326 insert into hxc_pref_definitions
327 (PREF_DEFINITION_ID,
328 CODE,
329 DESCRIPTIVE_FLEXFIELD_NAME)
330 select
331 hxc_pref_definitions_s.nextval,
332 context_rec.descriptive_flex_context_code,
333 p_flex_name
334 from dual
335 where not exists(
336 select 'Y'
337 from hxc_pref_definitions
338 where code = context_rec.descriptive_flex_context_code
339 and descriptive_flexfield_name = p_flex_name);
340
341 END LOOP;
342
343
344 END create_preference_definitions;
345
346 /*
347 Added for 8645021 HR OTL Absence Integration
348
349 This procedure is called in undo part of GFMP, in which all absence info is removed
350 from hxc_absence_type_elements table
351 */
352
353 --Change start
354 PROCEDURE REMOVE_HXC_ABS_ELEM_INFO(p_error_msg OUT NOCOPY VARCHAR2,
355 p_element_type_id IN hxc_absence_type_elements.element_type_id%type)
356 IS
357
358 CURSOR chk_abs_elem_exists(p_element_type_id IN NUMBER)
359
360 IS
361 SELECT
362 1
363 FROM
364 hxc_absence_type_elements
365 WHERE
366 element_type_id = p_element_type_id;
367
368
369 x_var NUMBER(1):=0;
370
371 BEGIN
372
373 /*
374 The logic would be to first delete the records with the
375 present element type ids and then insert it.
376 */
377
378 if g_debug then
379 hr_utility.trace('Entered REMOVE_HXC_ABS_ELEM_INFO');
380 end if;
381
382
383 IF p_element_type_id is not null and g_abs_incl_flag = 'Y' THEN
384
385 OPEN chk_abs_elem_exists(p_element_type_id);
386
387 FETCH chk_abs_elem_exists into x_var;
388
389 CLOSE chk_abs_elem_exists;
390
391 IF x_var = 1 THEN
392
393 delete from hxc_absence_type_elements -- hxc_absence_type_elements
394 where element_type_id=p_element_type_id;
395
396 if g_debug then
397
398 hr_utility.trace('REMOVE_HXC_ABS_ELEM_INFO for element ='||p_element_type_id);
399
400 end if;
401
402 --commit;
403
404 END IF;
405
406 END IF; --p_element_type_id and g_abs_incl_flag
407
408 p_error_msg := null;
409
410 if g_debug then
411 hr_utility.trace('Leaving REMOVE_HXC_ABS_ELEM_INFO');
412 end if;
413
414
415
416 END; -- REMOVE_HXC_ABS_ELEM_INFO
417
418
419 --Change end
420
421
422
423
424
425 PROCEDURE undo(
426 p_appl_short_name in FND_APPLICATION.APPLICATION_SHORT_NAME%TYPE
427 ,p_flexfield_name in FND_DESCRIPTIVE_FLEXS.DESCRIPTIVE_FLEXFIELD_NAME%TYPE
428 ,p_element_set_id in PAY_ELEMENT_SETS.ELEMENT_SET_ID%TYPE
429 ,p_effective_date in DATE
430 ,p_include_cost in VARCHAR2
431 ,p_include_group in VARCHAR2
432 ,p_include_job in VARCHAR2
433 ,p_include_pos in VARCHAR2
434 ,p_include_prj in VARCHAR2) is
435 -- 115.24 Change
436 cursor c_elements(p_element_set_id in number, p_effective_date in date) is
437 select pet.element_type_id
438 from pay_element_types_f pet,
439 pay_element_type_rules per
440 where per.element_set_id = p_element_set_id
441 and per.include_or_exclude = 'I'
442 and per.element_type_id = pet.element_type_id
443 and multiple_entries_allowed_flag = 'Y'
444 and p_effective_date between effective_start_date and effective_end_date;
445 -- End 115.24 Change
446 l_bld_blk_info_type_id HXC_BLD_BLK_INFO_TYPES.BLD_BLK_INFO_TYPE_ID%TYPE;
447
448 l_generate BOOLEAN default FALSE;
449 l_loop_var NUMBER default 0;
450 l_key_app VARCHAR2(30) := 'PAY';
451 l_key_flex_code VARCHAR2(30) := 'COST';
452
453 l_flex FND_DFLEX.dflex_r;
454 l_flex_info FND_DFLEX.dflex_dr;
455
456 l_basis_string fnd_descr_flex_contexts.descriptive_flex_context_code%type;
457
458 /*
459 Added for 8645021 HR OTL Absence Integration
460 */
461
462 --change start
463 p_err_msg VARCHAR2(1000);
464 --change end
465
466 BEGIN
467
468 --
469 -- The undo procedure will be called when the user wishes to remove
470 -- the mappings.
471 --
472
473 if p_element_set_id is not null then
474 -- 115.24 Change
475 for ele_rec in c_elements(p_element_set_id,p_effective_date) LOOP
476
477 l_basis_string := 'ELEMENT - '||ele_rec.element_type_id;
478
479 l_bld_blk_info_type_id := check_delete_info_type(l_basis_string);
480
481 if l_bld_blk_info_type_id is null then
482 remove_mapping_component(l_basis_string);
483 remove_bld_blk_usage(l_basis_string);
484 remove_flex_context(
485 p_appl_short_name => p_appl_short_name,
486 p_flexfield_name => p_flexfield_name,
487 p_flex_context_basis => l_basis_string);
488 remove_bld_blk_info_type(l_basis_string);
489
490 /*
491 Added for 8645021 HR OTL Absence Integration
492
493 Call to delete any existing absence info in hxc_absence_type_elements
494 */
495 -- change start
496
497 remove_hxc_abs_elem_info(p_err_msg,ele_rec.element_type_id);
498
499 --change end
500
501
502 end if;
503
504 end loop;
505 -- End 115.24 Change
506 end if;
507
508 FOR l_loop_var in 1..4 LOOP
509
510 l_generate := FALSE;
511
512 if ((l_loop_var=1) AND (p_include_cost = 'Y')) then
513 l_key_app := 'PAY';
514 l_key_flex_code := 'COST';
515 l_generate := TRUE;
516 elsif ((l_loop_var=2) AND (p_include_group = 'Y')) then
517 l_key_flex_code := 'GRP';
518 l_generate := TRUE;
519 elsif ((l_loop_var=3) AND (p_include_job = 'Y')) then
520 l_key_app := 'PER';
521 l_key_flex_code := 'JOB';
522 l_generate := TRUE;
523 elsif ((l_loop_var=4) AND (p_include_pos = 'Y')) then
524 l_key_flex_code := 'POS';
525 l_generate := TRUE;
526 end if;
527
528 if l_generate then
529
530 l_bld_blk_info_type_id := check_delete_info_type(l_key_flex_code);
531
532 if l_bld_blk_info_type_id is null then
533 remove_mapping_component(l_key_flex_code);
534 remove_bld_blk_usage(l_key_flex_code);
535 remove_flex_context(
536 p_appl_short_name => p_appl_short_name,
537 p_flexfield_name => p_flexfield_name,
538 p_flex_context_basis => l_key_flex_code);
539 remove_bld_blk_info_type(l_key_flex_code);
540 end if;
541
542 end if; -- are we including this key flex
543
544 if p_include_prj = 'Y' then
545
546 -- l_bld_blk_info_type_id := check_delete_info_type('PAEXPITDFF');
547 l_bld_blk_info_type_id := null;
548
549 if l_bld_blk_info_type_id is null then
550 remove_mapping_component('PAEXPITDFF');
551 remove_bld_blk_usage('PAEXPITDFF');
552 remove_flex_context(
553 p_appl_short_name => p_appl_short_name,
554 p_flexfield_name => p_flexfield_name,
555 p_flex_context_basis => 'PAEXPITDFF');
556 remove_bld_blk_info_type('PAEXPITDFF');
557 else
558 FND_MESSAGE.set_name('HXC','HXC_COMPONENTS_MAPPED');
559 FND_MESSAGE.raise_error;
560 end if;
561
562 end if; -- remove the projects definition?
563
564 END LOOP;
565
566
567 END undo;
568
569 /*
570 Private functions to find value sets, and generate mappings, building
571 block information types etc.
572 */
573
574 FUNCTION find_value_set(l_vid in FND_FLEX_VALUE_SETS.FLEX_VALUE_SET_ID%TYPE)
575 RETURN VARCHAR2 is
576
577 l_value_set_name FND_FLEX_VALUE_SETS.FLEX_VALUE_SET_NAME%type := NULL;
578
579 l_value_set FND_VSET.VALUESET_R;
580 l_value_set_format FND_VSET.VALUESET_DR;
581
582 BEGIN
583
584 if l_vid is not null then
585 FND_VSET.GET_VALUESET(
586 valueset_id => l_vid,
587 valueset => l_value_set,
588 format => l_value_set_format);
589 l_value_set_name := l_value_set.name;
590
591 end if;
592
593 RETURN l_value_set_name;
594
595 END find_value_set;
596
597 function get_name_prefix
598 (p_flex_code in varchar2
599 ,p_flex_num in number) return varchar2 is
600
601 l_prefix varchar2(21);
602
603 begin
604
605 select substr(id_flex_structure_code,1,20) into l_prefix
606 from fnd_id_flex_structures
607 where id_flex_code = p_flex_code
608 and id_flex_num = p_flex_num;
609
610 return l_prefix;
611
612 end get_name_prefix;
613
614 FUNCTION valid_segment_name(
615 p_name in VARCHAR2
616 ,p_flex_code in varchar2
617 ,p_flex_num in varchar2
618 ) return VARCHAR2 is
619
620 cursor c_dup_name
621 (p_seg_name in varchar2
622 ,p_id_flex_code in varchar2
623 ,p_id_flex_num in number
624 ,p_application_id in number
625 ) is
626 select 'Y'
627 from fnd_id_flex_segments
628 where id_flex_code = p_id_flex_code
629 and id_flex_num = p_id_flex_num
630 and segment_name = p_seg_name
631 and application_id = p_application_id;
632
633 l_valid_name FND_DESCR_FLEX_COLUMN_USAGES.END_USER_COLUMN_NAME%TYPE;
634
635 l_dummy varchar2(5);
636
637 BEGIN
638
639 if upper(p_name) = 'GROUP' then
640
641 l_valid_name := 'People Group';
642
643 -- Check for duplicates
644
645 open c_dup_name(l_valid_name,p_flex_code,p_flex_num,801);
646 fetch c_dup_name into l_dummy;
647
648 if c_dup_name%NOTFOUND then
649 close c_dup_name;
650 else
651 close c_dup_name;
652 -- we have to try again with the name.
653 l_valid_name := 'HXC Group';
654 open c_dup_name(l_valid_name,p_flex_code,p_flex_num,801);
655 fetch c_dup_name into l_dummy;
656
657 if c_dup_name%NOTFOUND then
658 close c_dup_name;
659 else
660 close c_dup_name;
661 -- we have to try again with the name.
662 l_valid_name := get_name_prefix(p_flex_code,p_flex_num)||' Group';
663 open c_dup_name(l_valid_name,p_flex_code,p_flex_num,801);
664 fetch c_dup_name into l_dummy;
665
666 if c_dup_name%NOTFOUND then
667 close c_dup_name;
668 else
669 close c_dup_name;
670 -- we can't generate a name - throw an error
671 FND_MESSAGE.SET_NAME('HXC','HXC_UNABLE_TO_NAME_SEGMENT');
672 FND_MESSAGE.SET_TOKEN('SEGMENT_NAME',p_name);
673 FND_MESSAGE.SET_TOKEN('STRUCTURE_NUMBER',p_flex_num);
674 end if;
675 end if;
676 end if;
677
678 else
679 l_valid_name := p_name;
680 end if;
681
682 return l_valid_name;
683
684 END valid_segment_name;
685
686 FUNCTION mapping_missing(
687 p_name in hxc_mapping_components.name%type,
688 p_field_name in hxc_mapping_components.field_name%type,
689 p_bld_blk_info_type_id in hxc_bld_blk_info_types.bld_blk_info_type_id%type,
690 p_segment in hxc_mapping_components.segment%type,
691 p_mp_id IN OUT NOCOPY NUMBER,
692 p_ovn IN OUT NOCOPY NUMBER ) RETURN BOOLEAN IS
693
694
695 BEGIN
696
697 select mapping_component_id,
698 object_version_number
699 into p_mp_id,
700 p_ovn
701 from hxc_mapping_components
702 where field_name = p_field_name
703 and name = p_name
704 and segment = p_segment;
705
706 RETURN FALSE;
707
708 EXCEPTION
709 WHEN NO_DATA_FOUND then
710 RETURN TRUE;
711
712 END;
713
714 FUNCTION update_allowed
715 (p_map_comp_id in HXC_MAPPING_COMPONENTS.MAPPING_COMPONENT_ID%TYPE) RETURN BOOLEAN is
716
717 l_dummy VARCHAR2(2);
718
719 BEGIN
720
721 -- Just check to see if this mapping component is used
722 -- note it can be used more than once, hence the rownum
723 -- in the where clause.
724
725 select 'Y'
726 into l_dummy
727 from hxc_mapping_comp_usages
728 where mapping_component_id = p_map_comp_id
729 and rownum =1;
730
731 return false;
732
733 EXCEPTION
734 when no_data_found then
735 return true;
736
737 END update_allowed;
738
739 PROCEDURE create_mapping(
740 p_name in hxc_mapping_components.name%type,
741 p_field_name in hxc_mapping_components.field_name%type,
742 p_bld_blk_info_type_id in hxc_bld_blk_info_types.bld_blk_info_type_id%type,
743 p_segment in hxc_mapping_components.segment%type) is
744
745 l_mapping_component_id NUMBER;
746 l_ovn NUMBER;
747
748 BEGIN
749
750 if mapping_missing(
751 p_name => p_name,
752 p_field_name => p_field_name,
753 p_bld_blk_info_type_id => p_bld_blk_info_type_id,
754 p_segment => p_segment,
755 p_mp_id => l_mapping_component_id,
756 p_ovn => l_ovn ) then
757 hxc_mapping_component_api.create_mapping_component(
758 p_validate => FALSE,
759 p_mapping_component_id => l_mapping_component_id,
760 p_object_version_number => l_ovn,
761 p_name => p_name,
762 p_field_name => p_field_name,
763 p_bld_blk_info_type_id => p_bld_blk_info_type_id,
764 p_segment => p_segment);
765
766 elsif (update_allowed(l_mapping_component_id)) then
767 hxc_mapping_component_api.update_mapping_component(
768 p_validate => FALSE,
769 p_mapping_component_id => l_mapping_component_id,
770 p_object_version_number => l_ovn,
771 p_name => p_name,
772 p_field_name => p_field_name,
773 p_bld_blk_info_type_id => p_bld_blk_info_type_id,
774 p_segment => p_segment);
775
776 end if;
777
778 END create_mapping;
779
780 FUNCTION create_bld_blk_info_type(
781 p_appl_short_name in VARCHAR2
782 ,p_flexfield_name in VARCHAR2
783 ,p_legislation_code in hxc_bld_blk_info_types.legislation_code%type
784 ,p_bld_blk_info_type in hxc_bld_blk_info_types.bld_blk_info_type%type
785 ,p_category hxc_bld_blk_info_type_usages.building_block_category%type
786 ) RETURN NUMBER is
787
788 cursor c_info_type_id (p_info_type in hxc_bld_blk_info_types.bld_blk_info_type%type) is
789 select bld_blk_info_type_id
790 from HXC_BLD_BLK_INFO_TYPES
791 where bld_blk_info_type = p_info_type;
792
793 l_bld_blk_info_type_id NUMBER;
794
795 BEGIN
796 --
797 -- Try to obtain the building block info type id
798 --
799 OPEN c_info_type_id(p_info_type => p_bld_blk_info_type);
800 FETCH c_info_type_id into l_bld_blk_info_type_id;
801
802
803 --
804 -- Create or Delete the information type record
805 --
806
807 if c_info_type_id%NOTFOUND then
808
809 insert into HXC_BLD_BLK_INFO_TYPES(
810 bld_blk_info_type_id,
811 legislation_code,
812 descriptive_flexfield_name,
813 bld_blk_info_type,
814 multiple_occurences_flag,
815 created_by,
816 creation_date,
817 last_updated_by,
818 last_update_date,
819 last_update_login,
820 object_version_number)
821 select
822 HXC_BLD_BLK_INFO_TYPES_S.NEXTVAL,
823 p_legislation_code,
824 p_flexfield_name,
825 p_bld_blk_info_type,
826 'N',
827 0,
828 sysdate,
829 0,
830 sysdate,
831 0,
832 1
833 from sys.dual;
834
835 end if;
836
837 close c_info_type_id;
838
839 --
840 -- Find the bld_blk_type_id for this information type
841 --
842 OPEN c_info_type_id(p_bld_blk_info_type);
843 FETCH c_info_type_id into l_bld_blk_info_type_id;
844 CLOSE c_info_type_id;
845
846 --
847 -- Create the information type category usage record
848 --
849 insert into HXC_BLD_BLK_INFO_TYPE_USAGES(
850 bld_blk_info_type_usage_id,
851 building_block_category,
852 bld_blk_info_type_id,
853 created_by,
854 creation_date,
855 last_updated_by,
856 last_update_date,
857 last_update_login,
858 object_version_number)
859 select
860 hxc_bld_blk_info_type_usages_s.nextval,
861 p_category,
862 l_bld_blk_info_type_id,
863 0,
864 sysdate,
865 0,
866 sysdate,
867 0,
868 1
869 from sys.dual
870 where not exists(
871 select 'Y'
872 from hxc_bld_blk_info_type_usages
873 where bld_blk_info_type_id = l_bld_blk_info_type_id);
874
875 RETURN l_bld_blk_info_type_id;
876
877 END create_bld_blk_info_type;
878
879 PROCEDURE create_missing_type_usages(
880 p_appl_short_name in FND_APPLICATION.APPLICATION_SHORT_NAME%TYPE
881 ,p_flex_name in FND_DESCRIPTIVE_FLEXS.DESCRIPTIVE_FLEXFIELD_NAME%TYPE
882 ) IS
883
884 cursor c_missing(
885 p_flex_name in FND_DESCRIPTIVE_FLEXS.DESCRIPTIVE_FLEXFIELD_NAME%TYPE
886 )is
887 select fdfc.DESCRIPTIVE_FLEX_CONTEXT_CODE
888 from fnd_descr_flex_contexts fdfc
889 where descriptive_flexfield_name = p_flex_name
890 and fdfc.descriptive_flex_context_code <> 'Global Data Elements'
891 and not exists (
892 select 'Y'
893 from hxc_bld_blk_info_types bbi
894 where bbi.bld_blk_info_type = fdfc.descriptive_flex_context_code
895 );
896 l_dummy_info_type_id NUMBER;
897
898 BEGIN
899
900 for miss_rec in c_missing(p_flex_name) LOOP
901
902 l_dummy_info_type_id := create_bld_blk_info_type(
903 p_appl_short_name => p_appl_short_name
904 ,p_flexfield_name => p_flex_name
905 ,p_legislation_code => NULL
906 ,p_bld_blk_info_type => miss_rec.descriptive_flex_context_code
907 ,p_category => miss_rec.descriptive_flex_context_code);
908
909 END LOOP;
910
911
912 END create_missing_type_usages;
913
914 PROCEDURE create_dummy_context(
915 p_appl_short_name in VARCHAR2,
916 p_flexfield_name in VARCHAR2,
917 p_context_name in VARCHAR2,
918 p_segment_name_prefix in VARCHAR2,
919 p_max_segments in NUMBER
920 ) IS
921
922 l_building_block_info_id NUMBER;
923
924 l_segment_name_prefix VARCHAR2(50);
925 l_sequence_number NUMBER;
926 l_map_comp_name VARCHAR2(50);
927 l_map_comp_field_name VARCHAR2(50);
928
929 BEGIN
930
931 --
932 -- Create the dummy context
933 --
934
935
936 if FND_FLEX_DSC_API.context_exists(
937 P_APPL_SHORT_NAME => p_appl_short_name,
938 P_FLEXFIELD_NAME => p_flexfield_name,
939 P_CONTEXT_CODE => 'Dummy '||initcap(p_context_name)||' Context'
940 ) then
941 FND_FLEX_DSC_API.delete_context(
942 APPL_SHORT_NAME => p_appl_short_name,
943 FLEXFIELD_NAME => p_flexfield_name,
944 CONTEXT => 'Dummy '||initcap(p_context_name)||' Context');
945
946 end if; -- Does this element context exist?
947
948 FND_FLEX_DSC_API.create_context(
949 APPL_SHORT_NAME => p_appl_short_name,
950 FLEXFIELD_NAME => p_flexfield_name,
951 CONTEXT_CODE => 'Dummy '||initcap(p_context_name)||' Context',
952 CONTEXT_NAME => 'Dummy '||initcap(p_context_name)||' Context',
953 DESCRIPTION => 'Auto generated HXC context',
954 ENABLED => 'Y',
955 GLOBAL_FLAG => 'N');
956
957
958
959 --
960 -- Next create the dummy information type
961 --
962 l_building_block_info_id := create_bld_blk_info_type(
963 p_appl_short_name => p_appl_short_name,
964 p_flexfield_name => p_flexfield_name,
965 p_legislation_code => NULL,
966 p_bld_blk_info_type => 'Dummy '||initcap(p_context_name)||' Context',
967 p_category => p_context_name);
968 --
969 -- Now create the dummy mappings
970 --
971 create_mapping(
972 p_name =>'Dummy '||initcap(p_context_name)||' Context',
973 p_field_name => 'Dummy '||initcap(p_context_name)||' Context',
974 p_bld_blk_info_type_id => l_building_block_info_id,
975 p_segment => 'ATTRIBUTE_CATEGORY');
976
977 --
978 -- Now create all the dummy segments
979 --
980
981 for i in 1..p_max_segments LOOP
982
983 FND_FLEX_DSC_API.create_segment(
984 APPL_SHORT_NAME => p_appl_short_name,
985 FLEXFIELD_NAME => p_flexfield_name,
986 CONTEXT_NAME => 'Dummy '||initcap(p_context_name)||' Context',
987 NAME => p_segment_name_prefix||to_char(i),
988 COLUMN => 'ATTRIBUTE'||to_char(i),
989 DESCRIPTION => 'Auto generated HXC context segment',
990 SEQUENCE_NUMBER => i,
991 ENABLED => 'N',
992 DISPLAYED => 'N',
993 VALUE_SET => NULL,
994 DEFAULT_TYPE => NULL,
995 DEFAULT_VALUE => NULL,
996 REQUIRED => 'N',
997 SECURITY_ENABLED => 'N',
998 DISPLAY_SIZE => 30,
999 DESCRIPTION_SIZE => 50,
1000 CONCATENATED_DESCRIPTION_SIZE => 10,
1001 LIST_OF_VALUES_PROMPT => p_segment_name_prefix||to_char(i),
1002 WINDOW_PROMPT => p_segment_name_prefix||to_char(i),
1003 RANGE => NULL,
1004 SRW_PARAMETER => NULL);
1005
1006 --
1007 -- Now create the dummy mappings
1008 --
1009 create_mapping(
1010 p_name =>p_segment_name_prefix||to_char(i),
1011 p_field_name => p_segment_name_prefix||to_char(i),
1012 p_bld_blk_info_type_id => l_building_block_info_id,
1013 p_segment => 'ATTRIBUTE'||to_char(i));
1014
1015 END LOOP;
1016
1017 IF(p_context_name = 'ELEMENT') THEN
1018 FOR i in 1..4 LOOP
1019
1020 IF (i=1) THEN
1021 l_segment_name_prefix := 'NAStateName';
1022 l_sequence_number := 27;
1023 l_map_comp_field_name := 'NA_STATE_NAME';
1024 l_map_comp_name := 'NA State Name';
1025 ELSIF (i=2) THEN
1026 l_segment_name_prefix := 'NACountyName';
1027 l_sequence_number := 28;
1028 l_map_comp_field_name := 'NA_COUNTY_NAME';
1029 l_map_comp_name := 'NA County Name';
1030 ELSIF (i=3) THEN
1031 l_segment_name_prefix := 'NACityName';
1032 l_sequence_number := 29;
1033 l_map_comp_field_name := 'NA_CITY_NAME';
1034 l_map_comp_name := 'NA City Name';
1035 ELSIF (i=4) THEN
1036 l_segment_name_prefix := 'NAZipCode';
1037 l_sequence_number := 30;
1038 l_map_comp_field_name := 'NA_ZIP_CODE';
1039 l_map_comp_name := 'NA Zip Code';
1040 END IF;
1041
1042 FND_FLEX_DSC_API.create_segment(
1043 APPL_SHORT_NAME => p_appl_short_name,
1044 FLEXFIELD_NAME => p_flexfield_name,
1045 CONTEXT_NAME => 'Dummy '||initcap(p_context_name)||' Context',
1046 NAME => l_segment_name_prefix,
1047 COLUMN => 'ATTRIBUTE'||to_char(l_sequence_number),
1048 DESCRIPTION => 'Auto generated HXC context segment',
1049 SEQUENCE_NUMBER => l_sequence_number,
1050 ENABLED => 'N',
1051 DISPLAYED => 'N',
1052 VALUE_SET => NULL,
1053 DEFAULT_TYPE => NULL,
1054 DEFAULT_VALUE => NULL,
1055 REQUIRED => 'N',
1056 SECURITY_ENABLED => 'N',
1057 DISPLAY_SIZE => 30,
1058 DESCRIPTION_SIZE => 50,
1059 CONCATENATED_DESCRIPTION_SIZE => 10,
1060 LIST_OF_VALUES_PROMPT => l_segment_name_prefix,
1061 WINDOW_PROMPT => l_segment_name_prefix,
1062 RANGE => NULL,
1063 SRW_PARAMETER => NULL);
1064
1065 create_mapping(
1066 p_name =>l_map_comp_name,
1067 p_field_name => l_map_comp_field_name,
1068 p_bld_blk_info_type_id => l_building_block_info_id,
1069 p_segment => 'ATTRIBUTE'||to_char(l_sequence_number));
1070
1071 END LOOP;
1072 END IF;
1073 END create_dummy_context;
1074
1075
1076 PROCEDURE create_segments(
1077 p_otc_appl_short_name in FND_APPLICATION.APPLICATION_SHORT_NAME%TYPE
1078 ,p_context in FND_DFLEX.context_r
1079 ,p_otc_flex_name in FND_DESCRIPTIVE_FLEXS.DESCRIPTIVE_FLEXFIELD_NAME%TYPE
1080 ,p_context_code in FND_DESCR_FLEX_CONTEXTS.DESCRIPTIVE_FLEX_CONTEXT_CODE%TYPE
1081 ) is
1082
1083 l_segments FND_DFLEX.segments_dr;
1084 l_segment_index NUMBER;
1085 l_segment_count NUMBER;
1086
1087
1088 BEGIN
1089
1090 FND_DFLEX.get_Segments(
1091 context => p_context
1092 ,segments => l_segments
1093 ,enabled_only => TRUE);
1094
1095 l_segment_index := l_segments.segment_name.first;
1096
1097 --
1098 -- Ok, is this is the global context, then we can set
1099 -- the segment count to zero, otherwise we will already
1100 -- have used a few attributes in creating those global
1101 -- segments, so we'll need to increase the starting segment
1102 -- count to account for this.
1103 --
1104 if p_context.context_code = 'Global Data Elements' then
1105
1106 l_segment_count := 0;
1107 g_global_segment_count := l_segments.segment_name.count;
1108
1109 else
1110
1111 l_segment_count := g_global_segment_count;
1112
1113 end if;
1114
1115 LOOP
1116
1117 EXIT WHEN not l_segments.segment_name.exists(l_segment_index);
1118 l_segment_count := l_segment_count +1;
1119 --
1120 -- Create the segment in the OTC flex as it's
1121 -- defined in the other flexfield
1122 --
1123
1124 FND_FLEX_DSC_API.create_segment(
1125 APPL_SHORT_NAME => p_otc_appl_short_name,
1126 FLEXFIELD_NAME => p_otc_flex_name,
1127 CONTEXT_NAME => p_context_code,
1128 NAME => l_segments.segment_name(l_segment_index),
1129 -- COLUMN => 'ATTRIBUTE'||to_char(l_segment_count),
1130 COLUMN => l_segments.application_column_name(l_segment_index),
1131 DESCRIPTION =>l_segments.description(l_segment_index),
1132 -- SEQUENCE_NUMBER => l_segments.sequence(l_segment_index),
1133 SEQUENCE_NUMBER => l_segment_count,
1134 ENABLED => 'N',
1135 DISPLAYED => 'N',
1136 VALUE_SET => find_value_set(l_segments.value_set(l_segment_index)),
1137 DEFAULT_TYPE => l_segments.default_type(l_segment_index),
1138 DEFAULT_VALUE =>l_segments.default_value(l_segment_index),
1139 REQUIRED => 'N',
1140 SECURITY_ENABLED => 'N',
1141 DISPLAY_SIZE => l_segments.display_size(l_segment_index),
1142 DESCRIPTION_SIZE => l_segments.display_size(l_segment_index),
1143 CONCATENATED_DESCRIPTION_SIZE => l_segments.display_size(l_segment_index),
1144 LIST_OF_VALUES_PROMPT => l_segments.column_prompt(l_segment_index),
1145 WINDOW_PROMPT => l_segments.row_prompt(l_segment_index),
1146 RANGE => NULL,
1147 SRW_PARAMETER => NULL);
1148
1149 l_segment_index := l_segments.segment_name.next(l_segment_index);
1150
1151 END LOOP; -- segments loop
1152
1153 END create_segments;
1154
1155 FUNCTION check_contexts
1156 (p_contexts in FND_DFLEX.contexts_dr)
1157 RETURN BOOLEAN is
1158
1159 -- Here we ascertain whether the flex
1160 -- we are duplicating only has
1161 -- the global context associated with it.
1162 -- Note: all flexs have at least the global context
1163 -- If that's true we have to handle it in a slightly
1164 -- different way to the case when there are context
1165 -- segments.
1166
1167 -- Note, since flex impose a rule that a flexfield
1168 -- must always have global data elements, we can
1169 -- just check the number of contexts, and if only
1170 -- one - we know we only have global data elements!
1171
1172 BEGIN
1173
1174 if (p_contexts.ncontexts = 1) then
1175 return false;
1176 else
1177 return true;
1178 end if;
1179
1180 END check_contexts;
1181
1182 PROCEDURE duplicate_desc_flex
1183 (p_appl_short_name in FND_APPLICATION.APPLICATION_SHORT_NAME%TYPE,
1184 p_flexfield_name in FND_DESCRIPTIVE_FLEXS.DESCRIPTIVE_FLEXFIELD_NAME%TYPE,
1185 p_otc_appl_short_name in FND_APPLICATION.APPLICATION_SHORT_NAME%TYPE,
1186 p_otc_flex_name in FND_DESCRIPTIVE_FLEXS.DESCRIPTIVE_FLEXFIELD_NAME%TYPE,
1187 p_context_prefix in varchar2,
1188 p_preserve in boolean
1189 ) is
1190
1191 -- This cursor retrieves the Sequence Number from the context code like
1192 --PAEXPITDFFC - Number(PAEXPITDFFC - 4252), where Number is the maximum,
1193 --so that the next context like PAEXPITDFFC is created with the code
1194 --PAEXPITDFFC - (Sequence Number +1)
1195
1196 CURSOR get_max_sequence IS
1197 select max(to_number(substrB(DESCRIPTIVE_FLEX_CONTEXT_CODE,
1198 instr(DESCRIPTIVE_FLEX_CONTEXT_CODE,'-')+2)))
1199 FROM fnd_descr_flex_contexts_vl
1200 WHERE descriptive_flexfield_name = 'OTC Information Types'
1201 AND application_id = 809
1202 AND substrB(DESCRIPTIVE_FLEX_CONTEXT_CODE,0,
1203 instr(DESCRIPTIVE_FLEX_CONTEXT_CODE,'-')-2)
1204 =substrB(DESCRIPTIVE_FLEX_CONTEXT_name,0,
1205 instr(DESCRIPTIVE_FLEX_CONTEXT_name,'-')-2)||'C';
1206
1207 l_max_sequence_code fnd_descr_flex_contexts.descriptive_flex_context_code%TYPE;
1208 l_max_sequence_no VARCHAR2(30);
1209 l_flex FND_DFLEX.dflex_r;
1210 l_flex_info FND_DFLEX.dflex_dr;
1211 l_contexts FND_DFLEX.contexts_dr;
1212 l_current_context FND_DFLEX.context_r;
1213 l_global_context FND_DFLEX.context_r;
1214 l_segments FND_DFLEX.segments_dr;
1215 l_context_index NUMBER;
1216 l_segment_index NUMBER;
1217 l_bld_blk_info_type_id NUMBER;
1218 l_segment_count NUMBER;
1219 l_count NUMBER;
1220 l_sequence_code fnd_descr_flex_contexts.descriptive_flex_context_code%TYPE DEFAULT NULL;
1221 l_sequence_no NUMBER;
1222 l_message VARCHAR2(100);
1223 l_preserve BOOLEAN;
1224 BEGIN
1225 --
1226 -- Default is not to preserve the
1227 -- flexfield definitions. By default, we will replace the
1228 -- definition.
1229 --
1230 if(p_preserve is null) then
1231 l_preserve := false;
1232 else
1233 l_preserve := true;
1234 end if;
1235 --
1236 -- Tell the API we're seeding data
1237 --
1238 FND_FLEX_DSC_API.set_session_mode('seed_data');
1239 --
1240 -- First fetch the flexfield
1241 --
1242 FND_DFLEX.get_flexfield
1243 (appl_short_name => p_appl_short_name,
1244 flexfield_name => p_flexfield_name,
1245 flexfield => l_flex,
1246 flexinfo => l_flex_info);
1247 --
1248 -- Next get the contexts
1249 --
1250 FND_DFLEX.get_contexts
1251 (flexfield => l_flex,
1252 contexts => l_contexts
1253 );
1254 --
1255 -- OK, if we have more than just the
1256 -- global context have to do things one
1257 -- way, if only the global context
1258 -- then have to do things slightly differently.
1259 --
1260
1261 if (check_contexts(l_contexts)) then
1262 l_global_context := FND_DFLEX.make_context
1263 (flexfield => l_flex,
1264 context_code =>'Global Data Elements'
1265 );
1266 --
1267 -- For each context, create the information type
1268 -- and the usage, and create a context of the same
1269 -- name against OTC Information types
1270 --
1271 l_context_index := l_contexts.context_code.first;
1272
1273 LOOP
1274 EXIT WHEN not l_contexts.context_code.exists(l_context_index);
1275 --
1276 -- Must avoid create a global data elements context
1277 -- those segments are handled a different way
1278 --
1279 if (
1280 (l_contexts.context_code(l_context_index) <> 'Global Data Elements')
1281 AND
1282 (hxc_otl_info_type_helper.build_otl_contexts
1283 (p_otc_appl_short_name,
1284 p_otc_flex_name,
1285 p_context_prefix,
1286 l_flex,
1287 l_contexts,
1288 l_context_index,
1289 l_global_context,
1290 l_preserve
1291 )
1292 )
1293 )then
1294 -- Create the context against OTC Information types but
1295 -- delete it first to make the process rerunable not as
1296 -- performant as leaving it there, but this way we are
1297 -- ensured to keep the OTC information in step with the
1298 -- descriptive flex information If the length of the
1299 -- Expenditure Items Context Code is less than or equal
1300 -- to 17 then the usual process , i.e the context code
1301 -- will remain like 'PAEXPITDFF''
1302 IF (LENGTH(l_contexts.context_code(l_context_index)) <=
1303 30-((LENGTH(p_context_prefix)+3))) THEN
1304 -- Follow the existing logic
1305
1306 if FND_FLEX_DSC_API.context_exists
1307 (P_APPL_SHORT_NAME => p_otc_appl_short_name,
1308 P_FLEXFIELD_NAME => p_otc_flex_name,
1309 P_CONTEXT_CODE => substr(p_context_prefix||' - '||l_contexts.context_code(l_context_index),1,30)
1310 ) then
1311 FND_FLEX_DSC_API.delete_context
1312 (APPL_SHORT_NAME => p_otc_appl_short_name,
1313 FLEXFIELD_NAME => p_otc_flex_name,
1314 CONTEXT => substr(p_context_prefix||' - '||l_contexts.context_code(l_context_index),1,30));
1315
1316 end if; -- Does this context exist?
1317
1318 FND_FLEX_DSC_API.create_context
1319 (APPL_SHORT_NAME => p_otc_appl_short_name,
1320 FLEXFIELD_NAME => p_otc_flex_name,
1321 CONTEXT_CODE => substr(p_context_prefix||' - '||l_contexts.context_code(l_context_index),1,30),
1322 CONTEXT_NAME => l_contexts.context_name(l_context_index),
1323 DESCRIPTION => l_contexts.context_description(l_context_index),
1324 ENABLED => 'N',
1325 GLOBAL_FLAG => 'N'
1326 );
1327 --
1328 -- Create the Building block information type for this context
1329 --
1330 l_bld_blk_info_type_id := create_bld_blk_info_type
1331 (p_appl_short_name => p_otc_appl_short_name,
1332 p_flexfield_name => p_otc_flex_name,
1333 p_legislation_code => NULL,
1334 p_bld_blk_info_type => substr(p_context_prefix||' - '||l_contexts.context_code(l_context_index),1,30),
1335 p_category => p_flexfield_name
1336 );
1337 --
1338 -- Now, since we're using the OTC information types flexfield
1339 -- as a general flexfield, we can't simply add the global data
1340 -- segments within that context. So, we must add them to
1341 -- each context generated with the flexfield we're currently
1342 -- duplicating. This is irritating, but the only way to do it
1343 -- currently.
1344 --
1345 create_segments
1346 (p_otc_appl_short_name => p_otc_appl_short_name,
1347 p_context => l_global_context,
1348 p_otc_flex_name => p_otc_flex_name,
1349 p_context_code => substr(p_context_prefix||' - '||l_contexts.context_code(l_context_index),1,30)
1350 );
1351 --
1352 -- Next get the segments for this context, and create them against
1353 -- OTC context, AOL require us to "make" the context first
1354 --
1355 l_current_context := FND_DFLEX.make_context
1356 (flexfield => l_flex,
1357 context_code =>l_contexts.context_code(l_context_index)
1358 );
1359
1360 create_segments
1361 (p_otc_appl_short_name => p_otc_appl_short_name,
1362 p_context => l_current_context,
1363 p_otc_flex_name => p_otc_flex_name,
1364 p_context_code => substr(p_context_prefix||' - '||l_contexts.context_code(l_context_index),1,30)
1365 );
1366
1367 ELSE
1368 -- If the length of the Expenditure Item Context Code
1369 -- is greater than 17 then, context should be like
1370 -- PAEXPITDFFC - 14235252 and the Context Name will
1371 -- hold the usual context code like PAEXPITDFF -
1372 -- Painting&Decorating If the context code of
1373 -- Expenditure Items is already greater than 17 and
1374 -- the corresponding context code already exist in
1375 -- the OTL Information Types, then do nothing else
1376 -- create the context code like PAEXPITDFFC -
1377 -- 14235252
1378
1379 IF (not fnd_flex_dsc_api.context_exists
1380 (p_appl_short_name=> p_otc_appl_short_name,
1381 p_flexfield_name=> p_otc_flex_name,
1382 p_context_code=>substr( p_context_prefix || ' - '|| l_contexts.context_code(l_context_index), 1, 30))
1383 )THEN
1384
1385 l_sequence_code := NULL;
1386 -- Check out if the code already exist , say, if
1387 --the context code is 'Painting&Decorating' then
1388 --if PAEXPITDFF - Painting&Decorating exist as
1389 --the context name, then the sequence code will
1390 --be populated with PAEXPITDFFC - 14235252
1391
1392 if not (hxc_deposit_wrapper_utilities.get_dupdff_code
1393 (p_context_prefix||' - '|| l_contexts.context_code(l_context_index))=
1394 p_context_prefix||' - '|| l_contexts.context_code(l_context_index)
1395 ) then
1396
1397 l_sequence_code := hxc_deposit_wrapper_utilities.get_dupdff_code
1398 (p_context_prefix||' - '|| l_contexts.context_code(l_context_index)
1399 );
1400 end if;
1401
1402 if (l_sequence_code is not null) then
1403 -- The context code exist and so it requires
1404 -- to be deleted and once again created
1405 fnd_flex_dsc_api.delete_context
1406 (appl_short_name=> p_otc_appl_short_name,
1407 flexfield_name=> p_otc_flex_name,
1408 CONTEXT=> l_sequence_code
1409 );
1410 else
1411 -- If the context code does not exist for the
1412 --PAEXPITDFF - Painting&Decorating, then obtain
1413 --context code needs to be created like
1414 --'PAEXPITDFFC - Sequence Number, where
1415 --Sequence Number is the maximum
1416 OPEN get_max_sequence;
1417 FETCH get_max_sequence INTO l_sequence_no;
1418
1419 IF l_sequence_no IS NULL THEN
1420 -- If the context like PAEXPITDFFC is
1421 --created for the first time , then
1422 --starting the sequenc from 1
1423 l_sequence_code := p_context_prefix || 'C - 1';
1424 ELSE
1425 -- Sequence Number is populated with the maximum sequence +1
1426 l_sequence_no := l_sequence_no + 1;
1427 l_sequence_code := p_context_prefix || 'C - ' || l_sequence_no;
1428 END IF;
1429 CLOSE get_max_sequence;
1430 end if;
1431 hr_utility.set_message(809,'HXC_DFF_SYSTEM_CONTEXT');
1432
1433
1434 -- Description of the messages needs to be
1435 --'System context, do not modify.Context Name.
1436 --Description'. So keeping in view to avoid
1437 --translation problems, a message is created
1438 --with the above name and the text as 'System
1439 --context, do not modify''
1440 l_message := hr_utility.get_message;
1441 -- Creates the context
1442 fnd_flex_dsc_api.create_context
1443 (appl_short_name=> p_otc_appl_short_name,
1444 flexfield_name=> p_otc_flex_name,
1445 context_code=> l_sequence_code,
1446 context_name=> p_context_prefix
1447 || ' - '
1448 || l_contexts.context_code( l_context_index),
1449 description=> substr(l_message
1450 || l_contexts.context_name(l_context_index)
1451 || '.'
1452 || l_contexts.context_description( l_context_index ),1,240),
1453 enabled=> 'N',
1454 global_flag=> 'N'
1455 );
1456
1457 --Create the building block information
1458 l_bld_blk_info_type_id :=
1459 create_bld_blk_info_type
1460 (p_appl_short_name=> p_otc_appl_short_name,
1461 p_flexfield_name=> p_otc_flex_name,
1462 p_legislation_code=> NULL,
1463 p_bld_blk_info_type=> l_sequence_code,
1464 p_category=> p_flexfield_name
1465 );
1466 -- Create the segments
1467 create_segments
1468 (p_otc_appl_short_name=> p_otc_appl_short_name,
1469 p_context=> l_global_context,
1470 p_otc_flex_name=> p_otc_flex_name,
1471 p_context_code=> l_sequence_code
1472 );
1473
1474 l_current_context :=
1475 fnd_dflex.make_context
1476 (flexfield=> l_flex,
1477 context_code=> l_contexts.context_code(l_context_index)
1478 );
1479
1480 create_segments
1481 (p_otc_appl_short_name=> p_otc_appl_short_name,
1482 p_context=> l_current_context,
1483 p_otc_flex_name=> p_otc_flex_name,
1484 p_context_code=> l_sequence_code
1485 );
1486
1487 end if;
1488 END IF;
1489 end if; -- avoiding the global data elements.
1490 l_context_index := l_contexts.context_code.next(l_context_index);
1491 END LOOP; -- Contexts loop
1492 else
1493 --
1494 -- In this case, we've only got the global contexts
1495 -- just set up a p_context_prefix - global context.
1496 -- No loop needed, since there is only one context.
1497
1498 l_global_context := FND_DFLEX.make_context
1499 (flexfield => l_flex,
1500 context_code =>'Global Data Elements'
1501 );
1502
1503 if FND_FLEX_DSC_API.context_exists
1504 (P_APPL_SHORT_NAME => p_otc_appl_short_name,
1505 P_FLEXFIELD_NAME => p_otc_flex_name,
1506 P_CONTEXT_CODE => substr(p_context_prefix||' - GLOBAL',1,30)
1507 ) then
1508 FND_FLEX_DSC_API.delete_context
1509 (APPL_SHORT_NAME => p_otc_appl_short_name,
1510 FLEXFIELD_NAME => p_otc_flex_name,
1511 CONTEXT => substr(p_context_prefix||' - GLOBAL',1,30)
1512 );
1513
1514 end if; -- Does this context exist?
1515
1516 FND_FLEX_DSC_API.create_context
1517 (APPL_SHORT_NAME => p_otc_appl_short_name,
1518 FLEXFIELD_NAME => p_otc_flex_name,
1519 CONTEXT_CODE => substr(p_context_prefix||' - GLOBAL',1,30),
1520 CONTEXT_NAME => substr(p_context_prefix||' - GLOBAL',1,30),
1521 DESCRIPTION => substr(p_context_prefix||' - GLOBAL',1,30)||' auto generated by the magic process',
1522 ENABLED => 'N',
1523 GLOBAL_FLAG => 'N'
1524 );
1525 --
1526 -- Create the Building block information type for this context
1527 --
1528 l_bld_blk_info_type_id := create_bld_blk_info_type
1529 (p_appl_short_name => p_otc_appl_short_name,
1530 p_flexfield_name => p_otc_flex_name,
1531 p_legislation_code => NULL,
1532 p_bld_blk_info_type => substr(p_context_prefix||' - GLOBAL',1,30),
1533 p_category => p_flexfield_name
1534 );
1535 --
1536 -- Finally create the global segments in the special
1537 -- OTL context created for these globals.
1538 --
1539 create_segments
1540 (p_otc_appl_short_name => p_otc_appl_short_name,
1541 p_context => l_global_context,
1542 p_otc_flex_name => p_otc_flex_name,
1543 p_context_code => substr(p_context_prefix||' - GLOBAL',1,30)
1544 );
1545 end if;
1546
1547 END duplicate_desc_flex;
1548
1549 /*
1550 Added for 8645021 HR OTL Absence Integration
1551
1552 Call to insert absence info from the element set to hxc_absence_type_elements
1553 */
1554
1555 --change start
1556 PROCEDURE INSERT_INTO_HXC_ABSENCES(p_error_msg OUT NOCOPY VARCHAR2,
1557 p_abs_info IN hxc_create_flex_mappings.hxc_abs_tab_type)
1558 IS
1559
1560 CURSOR chk_abs_elem_exists(p_absence_attendance_type_id IN NUMBER,
1561 p_element_type_id IN NUMBER)
1562 --p_uom IN VARCHAR2,
1563 --p_absence_category IN VARCHAR2)
1564 IS
1565 SELECT
1566 1
1567 FROM
1568 hxc_absence_type_elements
1569 WHERE
1570 absence_attendance_type_id = p_absence_attendance_type_id AND
1571 element_type_id = p_element_type_id;
1572
1573
1574 x_var NUMBER(1);
1575
1576 BEGIN
1577
1578 /*
1579 The logic would be to first delete the records with the
1580 present element type ids and den insert it.
1581 */
1582
1583 if g_debug then
1584 hr_utility.trace('Entered INSERT_INTO_HXC_ABSENCES');
1585 end if;
1586
1587 IF p_abs_info.COUNT > 0 THEN
1588
1589 FOR tab_count in p_abs_info.FIRST .. p_abs_info.LAST
1590 LOOP
1591
1592 OPEN chk_abs_elem_exists(
1593 p_abs_info(tab_count).ABSENCE_ATTENDANCE_TYPE_ID,
1594 p_abs_info(tab_count).ELEMENT_TYPE_ID
1595 );
1596
1597 FETCH chk_abs_elem_exists into x_var;
1598
1599 IF (chk_abs_elem_exists%NOTFOUND) THEN
1600
1601 if g_debug then
1602
1603 hr_utility.trace('Inserting');
1604 hr_utility.trace('ABSENCE_ATTENDANCE_TYPE_ID = '||p_abs_info(tab_count).ABSENCE_ATTENDANCE_TYPE_ID);
1605 hr_utility.trace('ELEMENT_TYPE_ID = '||p_abs_info(tab_count).ELEMENT_TYPE_ID);
1606 hr_utility.trace('EDIT_FLAG = '||p_abs_info(tab_count).EDIT_FLAG);
1607 hr_utility.trace('UOM = '||p_abs_info(tab_count).UOM);
1608 hr_utility.trace('ABSENCE_CATEGORY = '||p_abs_info(tab_count).ABSENCE_CATEGORY);
1609
1610
1611 end if;
1612
1613 insert into hxc_absence_type_elements -- hxc_absence_type_elements
1614 (
1615 ABSENCE_ATTENDANCE_TYPE_ID,
1616 ELEMENT_TYPE_ID,
1617 EDIT_FLAG,
1618 UOM,
1619 ABSENCE_CATEGORY
1620 )
1621
1622 VALUES
1623 (
1624 p_abs_info(tab_count).ABSENCE_ATTENDANCE_TYPE_ID,
1625 p_abs_info(tab_count).ELEMENT_TYPE_ID,
1626 p_abs_info(tab_count).EDIT_FLAG,
1627 p_abs_info(tab_count).UOM,
1628 p_abs_info(tab_count).ABSENCE_CATEGORY
1629 );
1630
1631 ELSE
1632 if g_debug then
1633 hr_utility.trace('Updating');
1634 hr_utility.trace('ABSENCE_ATTENDANCE_TYPE_ID = '||p_abs_info(tab_count).ABSENCE_ATTENDANCE_TYPE_ID);
1635 hr_utility.trace('ELEMENT_TYPE_ID = '||p_abs_info(tab_count).ELEMENT_TYPE_ID);
1636 hr_utility.trace('EDIT_FLAG NEW = '||p_abs_info(tab_count).EDIT_FLAG);
1637 hr_utility.trace('UOM NEW= '||p_abs_info(tab_count).UOM);
1638 hr_utility.trace('ABSENCE_CATEGORY NEW = '||p_abs_info(tab_count).ABSENCE_CATEGORY);
1639 end if;
1640
1641
1642 UPDATE hxc_absence_type_elements
1643 SET UOM = p_abs_info(tab_count).UOM,
1644 ABSENCE_CATEGORY = p_abs_info(tab_count).ABSENCE_CATEGORY
1645 WHERE absence_attendance_type_id = p_abs_info(tab_count).ABSENCE_ATTENDANCE_TYPE_ID
1646 AND element_type_id = p_abs_info(tab_count).ELEMENT_TYPE_ID;
1647
1648
1649 END IF;
1650
1651 CLOSE chk_abs_elem_exists;
1652
1653 END LOOP;
1654
1655 --commit;
1656
1657 END IF;
1658
1659 p_error_msg := null;
1660
1661 /*
1662 EXCEPTION
1663
1664 WHEN OTHERS THEN
1665
1666 p_error_msg:= 'ERROR';
1667
1668 */
1669
1670 END; -- insert_into_hxc_absence
1671
1672 -- change end
1673
1674 procedure run_process(
1675 p_errmsg OUT NOCOPY VARCHAR2
1676 ,p_errcode OUT NOCOPY NUMBER
1677 ,p_undo in VARCHAR2 default 'N'
1678 ,p_element_set_id in NUMBER default null
1679 ,p_effective_date in VARCHAR2
1680 ,p_generate_cost in VARCHAR2 default 'Y'
1681 ,p_generate_group in VARCHAR2 default 'Y'
1682 ,p_generate_job in VARCHAR2 default 'Y'
1683 ,p_generate_pos in VARCHAR2 default 'Y'
1684 ,p_generate_prj in VARCHAR2 default 'Y'
1685 ,p_business_group_id in VARCHAR2
1686 ,p_incl_abs_flg in VARCHAR2 default 'N') is -- Added for 8645021 HR Absence intg
1687 /*
1688 p_incl_abs_flg indicates Include Absence Information Param.
1689 */
1690 --Change the parameters to create_alias_definitions.
1691 cursor c_alias_type(p_alias_context_code varchar2) is
1692 select hat.alias_type_id
1693 from hxc_alias_types hat
1694 where reference_object = p_alias_context_code;
1695
1696 cursor c_prompt (p_alias_context_code varchar2) is
1697 select fdfc.descriptive_flex_context_name
1698 from fnd_descr_flex_contexts_vl fdfc
1699 where application_id = 809
1700 and DESCRIPTIVE_FLEXFIELD_NAME = 'OTC Aliases'
1701 and fdfc.descriptive_flex_context_code = p_alias_context_code;
1702
1703 cursor c_elements(p_element_set_id in number, p_effective_date in date) is
1704 select pet.element_name, pet.element_type_id, pet.reporting_name
1705 from pay_element_types_f pet,
1706 pay_element_type_rules per
1707 where per.element_set_id = p_element_set_id
1708 and per.include_or_exclude = 'I'
1709 and per.element_type_id = pet.element_type_id
1710 and multiple_entries_allowed_flag = 'Y'
1711 and p_effective_date between effective_start_date and effective_end_date;
1712
1713 cursor csr_chk_an_exists(p_an_name varchar2,
1714 p_bg_id number) is
1715 select 'Y', alias_definition_id
1716 from hxc_alias_definitions
1717 where alias_definition_name = p_an_name
1718 and business_group_id = p_bg_id;
1719
1720 cursor csr_value_exists(p_ele_type_id number,
1721 p_an_id number) is
1722 -- p_effective_date date) is
1723 select 'Y'
1724 from hxc_alias_values
1725 where alias_definition_id = p_an_id
1726 and attribute1 = to_char(p_ele_type_id);
1727 -- and p_effective_date between date_from and date_to;
1728
1729 cursor c_ipvs(p_element_type_id in number, p_effective_date in date) is
1730 select display_sequence, name, input_value_id, mandatory_flag
1731 from pay_input_values_f
1732 where element_type_id = p_element_type_id
1733 and p_effective_date between effective_start_date and effective_end_date
1734 order by display_sequence, name;
1735 /*
1736 Bug no : 3353252
1737 This cursor csr_chk_repname_exists will check for the duplicate reporting name in an alias_defintion
1738 and return the number of same reporting names available.
1739 */
1740
1741 cursor csr_chk_repname_exists(p_an_id number,
1742 p_ele_rep_name varchar2) is
1743 select count(*)
1744 from hxc_alias_values
1745 where alias_definition_id = p_an_id
1746 AND (alias_value_name like '% ~ '||p_ele_rep_name or alias_value_name = p_ele_rep_name);
1747
1748
1749 /*
1750 Added for 8645021 HR OTL Absence Integration
1751
1752 Cursor added to pick up all absence type elements in the element set
1753 */
1754
1755 --change start
1756 CURSOR abs_elements (p_effective_date IN DATE ,
1757 p_element_set_id IN NUMBER
1758 )
1759 IS
1760 select
1761 pat.absence_attendance_type_id,
1762 pet.element_type_id,
1763 'N' EDIT_FLAG,
1764 decode(piv.UOM, 'ND', 'DAYS'
1765 , 'H_H','HOURS'
1766 , 'H_DECIMAL1','HOURS'
1767 , 'H_DECIMAL2','HOURS'
1768 , 'H_DECIMAL3','HOURS'
1769 , 'H_HHMM','HOURS'
1770 , NULL) UOM,
1771 pat.absence_category
1772 from
1773 pay_element_types_f pet ,
1774 pay_input_values_f piv,
1775 per_absence_attendance_types pat,
1776 pay_element_type_rules per
1777 where
1778 per.element_set_id= p_element_set_id AND
1779 per.include_or_exclude = 'I' AND
1780 per.element_type_id=pet.element_type_id AND
1781 pet.multiple_entries_allowed_flag='Y' AND
1782 p_effective_date between
1783 nvl(pet.effective_start_date,hr_general.start_of_time)
1784 and
1785 nvl(pet.effective_end_date,hr_general.end_of_time) AND
1786 piv.element_type_id=pet.element_type_id AND
1787 p_effective_date between
1788 nvl(piv.effective_start_date,hr_general.start_of_time)
1789 and
1790 nvl(piv.effective_end_date ,hr_general.end_of_time) AND
1791 pat.input_value_id=piv.input_value_id AND
1792 p_effective_date between
1793 nvl(pat.date_effective,hr_general.start_of_time)
1794 and
1795 nvl(pat.date_end ,hr_general.end_of_time);
1796
1797
1798 -- change end
1799
1800
1801 l_attr_prompt pay_input_values_f.name%TYPE;
1802 l_temp_segment_choice NUMBER :=0;
1803
1804 l_appl_short_name VARCHAR2(3) := 'HXC';
1805 l_flexfield_name VARCHAR2(30) := 'OTC Information Types';
1806 l_segment_count NUMBER := 0;
1807 l_segment_choice NUMBER :=0;
1808
1809 l_element_count NUMBER := 0;
1810 l_key_flex_structure_count NUMBER :=0;
1811
1812 l_max_input_value_count NUMBER:=0;
1813 l_max_segment_count NUMBER :=0;
1814
1815 l_key_app VARCHAR2(30) := 'PAY';
1816 l_key_flex_code VARCHAR2(30) := 'COST';
1817
1818 l_key_flex FND_FLEX_KEY_API.FLEXFIELD_TYPE;
1819
1820 l_key_structure_list FND_FLEX_KEY_API.STRUCTURE_LIST;
1821 l_key_segment_list FND_FLEX_KEY_API.SEGMENT_LIST;
1822
1823 l_key_structure FND_FLEX_KEY_API.STRUCTURE_TYPE;
1824 l_key_segment FND_FLEX_KEY_API.SEGMENT_TYPE;
1825
1826 l_structure_count NUMBER;
1827
1828 l_structures NUMBER;
1829 l_segments NUMBER;
1830
1831 l_building_block_info_id NUMBER;
1832
1833 i NUMBER;
1834
1835 l_element_set_name VARCHAR2(80);
1836 l_an_context VARCHAR2(30) := 'PAYROLL_ELEMENTS';
1837 l_an_enabled VARCHAR2(80);
1838 l_an_disabled VARCHAR2(80);
1839 l_an_en_exists VARCHAR2(1) := 'N';
1840 l_an_dis_exists VARCHAR2(1) := 'N';
1841 l_an_en_id NUMBER;
1842 l_an_en_ovn NUMBER;
1843 l_an_dis_id NUMBER;
1844 l_an_dis_ovn NUMBER;
1845 l_en_value_exists VARCHAR2(1) := 'N';
1846 l_dis_value_exists VARCHAR2(1) := 'N';
1847 l_av_id NUMBER;
1848 l_av_ovn NUMBER;
1849 l_bg_id NUMBER;
1850 -- Change Parameters to create_alias_definitions
1851 l_alias_type_id NUMBER;
1852 l_prompt varchar2(240);
1853
1854 l_generate BOOLEAN;
1855
1856 l_effective_date DATE;
1857 l_bgp_id FND_PROFILE_OPTION_VALUES.PROFILE_OPTION_VALUE%TYPE;
1858 l_bgp_def BOOLEAN;
1859
1860 l_ret_bg_id VARCHAR2(2000);
1861
1862 l_new_en_repname PAY_ELEMENT_TYPES_F.REPORTING_NAME%TYPE;
1863 l_new_dis_repname PAY_ELEMENT_TYPES_F.REPORTING_NAME%TYPE;
1864
1865 l_alt_name_change VARCHAR2(1) := 'N';
1866 l_name VARCHAR2(1000);
1867
1868 l_warning_string VARCHAR2(1000):='';
1869 l_warning_string1 VARCHAR2(5000):='';
1870 l_temp_msg VARCHAR2(5000):='';
1871 c_warning CONSTANT NUMBER := 1;
1872 l_cnt_repname NUMBER(10):=0;
1873
1874 l_disp_flag VARCHAR2(2) := 'Y'; -- Added for Bug 14570475
1875
1876 /*
1877 Added for 8645021 HR OTL Absence Integration
1878
1879 Variables added for Abs_Intg
1880 */
1881
1882 --change start
1883 hxc_abs_tab hxc_abs_tab_type;
1884 abs_elem_exists abs_elem_exists_type;
1885 l_tab_counter NUMBER:=1;
1886 l_non_abs_elem_exists VARCHAR2(1):='Y';
1887 l_abs_elem_flg VARCHAR2(1):='N';
1888 l_err_msg VARCHAR2(1000);
1889
1890 PROFILE_NOT_SET EXCEPTION;
1891 --change end
1892
1893
1894
1895 BEGIN
1896
1897 IF g_debug THEN
1898 hr_utility.trace('In hxc_create_flex_mappings.run process ..');
1899 hr_utility.trace('undo ::'||p_undo );
1900 hr_utility.trace('element_set_id ::'||p_element_set_id );
1901 hr_utility.trace('effective_date ::'||p_effective_date );
1902 hr_utility.trace('generate_cost ::'||p_generate_cost );
1903 hr_utility.trace('generate_group ::'||p_generate_group );
1904 hr_utility.trace('generate_job ::'||p_generate_job );
1905 hr_utility.trace('generate_pos ::'||p_generate_pos );
1906 hr_utility.trace('generate_prj ::'||p_generate_prj );
1907 hr_utility.trace('business_group_id ::'||p_business_group_id );
1908 hr_utility.trace('incl_abs_flg ::'||p_incl_abs_flg );
1909 END IF;
1910 /*
1911 Added for 8645021 HR OTL Absence Integration
1912
1913 Setting up the global variable with the absence inclusion parameter
1914 */
1915
1916 -- change start
1917 g_abs_incl_flag:= p_incl_abs_flg;
1918 -- change end
1919
1920
1921 /* Bug fix for 3353252
1922 Initialize the string for the warning messages which will be printed if there is any duplication in the alternate names*/
1923 hr_utility.set_message (809, 'HXC_GEN_FLEX_MOD_ALT_WAR_MSG');
1924 l_name:=HR_UTILITY.GET_MESSAGE;
1925 /* end of fix for 3353252*/
1926
1927 --
1928 -- Convert the entered date into real date format
1929 --
1930
1931 l_effective_date := FND_DATE.CANONICAL_TO_DATE(p_effective_date);
1932
1933 --
1934 -- Set the business group id if passed in
1935 --
1936
1937 if (p_business_group_id is not null) then
1938
1939 FND_PROFILE.PUT('PER_BUSINESS_GROUP_ID',p_business_group_id);
1940
1941 else
1942
1943 --
1944 -- Is there a problem with fnd_profile caching?
1945 --
1946 FND_PROFILE.GET_SPECIFIC('PER_BUSINESS_GROUP_ID',null,null,null,l_bgp_id,l_bgp_def);
1947 if (l_bgp_def) then
1948 FND_PROFILE.PUT('PER_BUSINESS_GROUP_ID',l_bg_id);
1949 else
1950 FND_PROFILE.PUT('PER_BUSINESS_GROUP_ID',null);
1951 end if;
1952
1953 end if;
1954
1955 /*
1956 Added for 8645021 HR OTL Absence Integration
1957
1958 Check for Profile Parameter mismatch
1959 */
1960
1961 -- change start
1962 if (fnd_profile.value('HR_ABS_OTL_INTEGRATION') is null or
1963 fnd_profile.value('HR_ABS_OTL_INTEGRATION') <> 'Y') then
1964
1965 if g_abs_incl_flag='Y' then
1966
1967 g_abs_incl_flag:= 'N';
1968 --RAISE PROFILE_NOT_SET;
1969
1970 FND_MESSAGE.set_name('HXC','HXC_ABS_PROF_PARAM_MISMATCH');
1971 FND_MESSAGE.raise_error;
1972
1973 end if;
1974
1975 end if;
1976 --change end
1977
1978 --
1979 -- Check the undo flag, if set, then attempt to undo
1980 -- the flex and mapping component creation
1981 --
1982
1983 if p_undo = 'Y' then
1984 undo(
1985 p_appl_short_name=> l_appl_short_name
1986 ,p_flexfield_name => l_flexfield_name
1987 ,p_element_set_id => p_element_set_id
1988 ,p_effective_date => l_effective_date
1989 ,p_include_cost => p_generate_cost
1990 ,p_include_group => p_generate_group
1991 ,p_include_job => p_generate_job
1992 ,p_include_pos => p_generate_pos
1993 ,p_include_prj => p_generate_prj);
1994
1995 else
1996
1997 --
1998 -- Tell the flex field API we're seeding data
1999 --
2000
2001 FND_FLEX_DSC_API.set_session_mode('seed_data');
2002 -- Create alias definitions (Enabled and Disabled) and for the element set,
2003 -- if it does not already exist.
2004 --
2005 --
2006 -- Create the dummy element context and mappings
2007 --
2008 create_dummy_context(
2009 p_appl_short_name => l_appl_short_name,
2010 p_flexfield_name => l_flexfield_name,
2011 p_context_name => 'ELEMENT',
2012 p_segment_name_prefix=>'InputValue',
2013 p_max_segments => 15
2014 );
2015
2016 IF p_element_set_id IS NOT NULL THEN
2017
2018 /*
2019 Added for 8645021 HR OTL Absence Integration
2020
2021 Need to populate hxc_abs_tab and abs_elem_exists plsql tables
2022 */
2023
2024 -- change start
2025 IF p_incl_abs_flg='Y' THEN
2026
2027 l_tab_counter:=0;
2028
2029 FOR abs_info IN abs_elements (l_effective_date,
2030 p_element_set_id
2031 )
2032 LOOP
2033 l_tab_counter := l_tab_counter + 1;
2034
2035 hxc_abs_tab(l_tab_counter).ABSENCE_ATTENDANCE_TYPE_ID :=
2036 abs_info.ABSENCE_ATTENDANCE_TYPE_ID;
2037 hxc_abs_tab(l_tab_counter).ELEMENT_TYPE_ID :=
2038 abs_info.ELEMENT_TYPE_ID;
2039 hxc_abs_tab(l_tab_counter).EDIT_FLAG :=
2040 abs_info.EDIT_FLAG;
2041 hxc_abs_tab(l_tab_counter).UOM :=
2042 abs_info.UOM;
2043 hxc_abs_tab(l_tab_counter).ABSENCE_CATEGORY :=
2044 abs_info.ABSENCE_CATEGORY;
2045
2046 END LOOP; -- abs_info
2047
2048 if g_debug then
2049
2050 hr_utility.trace('hxc_abs_tab.COUNT = '||hxc_abs_tab.COUNT);
2051
2052
2053
2054 if hxc_abs_tab.count>0 then
2055
2056 FOR i in hxc_abs_tab.FIRST .. hxc_abs_tab.LAST
2057 LOOP
2058
2059 if hxc_abs_tab.EXISTS(i) then
2060
2061 hr_utility.trace('hxc_abs_tab(i).ABSENCE_ATTENDANCE_TYPE_ID = '||hxc_abs_tab(i).ABSENCE_ATTENDANCE_TYPE_ID);
2062 hr_utility.trace('hxc_abs_tab(i).ELEMENT_TYPE_ID = '||hxc_abs_tab(i).ELEMENT_TYPE_ID);
2063 hr_utility.trace('hxc_abs_tab(i).EDIT_FLAG = '||hxc_abs_tab(i).EDIT_FLAG);
2064 hr_utility.trace('hxc_abs_tab(i).UOM = '||hxc_abs_tab(i).UOM);
2065 hr_utility.trace('hxc_abs_tab(i).ABSENCE_CATEGORY = '||hxc_abs_tab(i).ABSENCE_CATEGORY);
2066
2067 end if;
2068
2069 END LOOP;
2070
2071 end if;
2072
2073 end if; -- g_debug
2074
2075 IF hxc_abs_tab.COUNT>0 THEN
2076
2077 FOR i in hxc_abs_tab.first .. hxc_abs_tab.last
2078 LOOP
2079
2080 abs_elem_exists(hxc_abs_tab(i).element_type_id):= 1;
2081
2082 END LOOP; -- hxc_abs_tab plsql table loop
2083
2084 if g_debug then
2085 hr_utility.trace('abs_elem_exists.COUNT = '||abs_elem_exists.COUNT);
2086 end if;
2087
2088
2089 END IF; -- hxc_abs_tab.count
2090
2091 -- now to check for non_absence_elements in the element set at all
2092
2093 l_non_abs_elem_exists := 'N';
2094
2095 FOR ele_rec in c_elements(p_element_set_id, l_effective_date) LOOP
2096
2097 IF NOT(abs_elem_exists.EXISTS(ele_rec.element_type_id)) THEN
2098
2099 l_non_abs_elem_exists := 'Y';
2100
2101 END IF;
2102
2103 END LOOP;
2104
2105
2106 END IF; -- p_incl_abs_flg
2107 -- change end
2108
2109 if g_debug then
2110 hr_utility.trace('l_non_abs_elem_exists = '||l_non_abs_elem_exists);
2111 end if;
2112 --
2113 -- Get the element set name.
2114 --
2115 SELECT element_set_name, business_group_id
2116 INTO l_element_set_name, l_bg_id
2117 FROM pay_element_sets
2118 WHERE element_set_id = p_element_set_id;
2119
2120 --
2121 l_an_enabled := rtrim(substr(l_element_set_name, 1, 70)) || ' - Enabled';
2122 l_an_disabled := rtrim(substr(l_element_set_name, 1, 69)) || ' - Disabled';
2123 /* Bug fix for 3353252
2124 The following code is used to get the alias_definiton_name which will be used in warning message
2125 if there is an alternate name change by the application.*/
2126 FND_MESSAGE.SET_NAME('HXC', 'HXC_GEN_FLEX_MOD_ALS_DEF_NAME');
2127 FND_MESSAGE.SET_TOKEN('ALIAS_DEFINITION_NAME',l_an_enabled);
2128 l_temp_msg:=FND_MESSAGE.GET();
2129 l_warning_string:=l_warning_string || l_temp_msg || '
2130 ';
2131 FND_MESSAGE.SET_NAME ('HXC', 'HXC_GEN_FLEX_MOD_ALS_DEF_NAME');
2132 FND_MESSAGE.SET_TOKEN('ALIAS_DEFINITION_NAME',l_an_disabled);
2133 l_temp_msg:=FND_MESSAGE.GET();
2134 l_warning_string1:=l_warning_string1 || l_temp_msg || '
2135 ';
2136 /* end of fix for 3353252*/
2137 --
2138 -- Check to see whether they already exist.
2139 --
2140 open csr_chk_an_exists(l_an_enabled, l_bg_id);
2141 fetch csr_chk_an_exists into l_an_en_exists, l_an_en_id;
2142 IF csr_chk_an_exists%NOTFOUND THEN
2143 l_an_en_exists := 'N';
2144 END IF;
2145 close csr_chk_an_exists;
2146 --
2147 open csr_chk_an_exists(l_an_disabled, l_bg_id);
2148 fetch csr_chk_an_exists into l_an_dis_exists, l_an_dis_id;
2149 IF csr_chk_an_exists%NOTFOUND THEN
2150 l_an_dis_exists := 'N';
2151 END IF;
2152 close csr_chk_an_exists;
2153 --
2154 -- l_ret_bg_id := FND_PROFILE.value('PER_BUSINESS_GROUP_ID');
2155 -- dbms_output.put_line('Profile business group id is:'||l_ret_bg_id);
2156 --
2157
2158 -- change parameters to create_alias_definitions
2159 open c_alias_type(l_an_context);
2160 fetch c_alias_type into l_alias_type_id;
2161 if (l_alias_type_id is null) then
2162 hr_utility.set_message(809,'HXC_SEED_ALT_NOT_FOUND');
2163 hr_utility.raise_error;
2164 end if;
2165 close c_alias_type;
2166
2167 open c_prompt(l_an_context);
2168 fetch c_prompt into l_prompt;
2169 close c_prompt;
2170
2171
2172 /*
2173 Added for 8645021 HR OTL Absence Integration
2174
2175 Check for, if at all any non absence elements exist
2176 */
2177
2178 -- change start
2179 IF l_non_abs_elem_exists = 'Y' then --
2180 -- change end
2181
2182 if g_debug then
2183 hr_utility.trace('Creating Alias Definitions');
2184 end if;
2185
2186 IF l_an_en_exists = 'N' THEN
2187 hxc_alias_definitions_api.create_alias_definition
2188 (p_alias_definition_id => l_an_en_id
2189 ,p_alias_definition_name => l_an_enabled
2190 ,p_business_group_id => l_bg_id
2191 ,p_legislation_code => NULL
2192 ,p_description => 'Created for renaming elements'
2193 ,p_prompt => l_prompt
2194 ,p_timecard_field => 'ElementComponent'
2195 ,p_object_version_number => l_an_en_ovn
2196 ,p_alias_type_id => l_alias_type_id
2197 );
2198 END IF;
2199 --
2200 --
2201 IF l_an_dis_exists = 'N' THEN
2202 hxc_alias_definitions_api.create_alias_definition
2203 (p_alias_definition_id => l_an_dis_id
2204 ,p_alias_definition_name => l_an_disabled
2205 ,p_business_group_id => l_bg_id
2206 ,p_legislation_code => NULL
2207 ,p_description => 'Created for renaming elements'
2208 ,p_prompt => l_prompt
2209 ,p_timecard_field => 'ElementComponent'
2210 ,p_object_version_number => l_an_dis_ovn
2211 ,p_alias_type_id => l_alias_type_id
2212 );
2213 END IF;
2214
2215 END IF; --l_non_abs_elem_exists
2216
2217 --
2218 END IF; -- p_element_set_id
2219 --
2220
2221 /*
2222 Open the element type cursor, and fetch the first element
2223 */
2224
2225 IF g_debug THEN
2226 hr_utility.trace('Open the element set cursor, and fetch the elements..');
2227 END IF;
2228
2229 for ele_rec in c_elements(p_element_set_id, l_effective_date) LOOP
2230 -- Check if this element already exists in the values for
2231 -- the two alias definitions.
2232 --
2233 open csr_value_exists(ele_rec.element_type_id, l_an_en_id);
2234 fetch csr_value_exists into l_en_value_exists;
2235 IF csr_value_exists%NOTFOUND THEN
2236 l_en_value_exists := 'N';
2237 END IF;
2238 close csr_value_exists;
2239
2240 --
2241 open csr_value_exists(ele_rec.element_type_id, l_an_dis_id);
2242 fetch csr_value_exists into l_dis_value_exists;
2243 IF csr_value_exists%NOTFOUND THEN
2244 l_dis_value_exists := 'N';
2245 END IF;
2246 close csr_value_exists;
2247
2248 --
2249 /*
2250 Added for 8645021 HR OTL Absence Integration
2251
2252 Checking whether the element is attached to an absence type
2253 */
2254 --change start
2255 l_abs_elem_flg:='N';
2256
2257 IF p_incl_abs_flg = 'Y' then -- abs_intg
2258
2259 IF abs_elem_exists.EXISTS(ele_rec.element_type_id) then
2260 l_abs_elem_flg:='Y';
2261 ELSE
2262 l_abs_elem_flg:='N';
2263 END IF; -- abs_elem_exists
2264
2265 END IF; -- p_incl_abs_flg
2266
2267
2268
2269 IF l_abs_elem_flg ='N' then -- svg abs_intg
2270 -- change end
2271
2272 if g_debug then
2273 hr_utility.trace('Creating Alias Values for '||ele_rec.element_type_id);
2274 end if;
2275
2276
2277 IF l_en_value_exists = 'N' THEN
2278 l_cnt_repname:=0;
2279 open csr_chk_repname_exists(l_an_en_id,nvl(ele_rec.reporting_name,ele_rec.element_name));
2280 fetch csr_chk_repname_exists into l_cnt_repname;
2281 IF l_cnt_repname=0 THEN
2282 hxc_alias_values_api.create_alias_value
2283 (p_alias_value_id => l_av_id
2284 ,p_alias_value_name => nvl(ele_rec.reporting_name,
2285 ele_rec.element_name)
2286 ,p_date_from => hr_general.start_of_time
2287 ,p_date_to => NULL
2288 ,p_alias_definition_id => l_an_en_id
2289 ,p_enabled_flag => 'Y'
2290 ,p_attribute_category => l_an_context
2291 ,p_attribute1 => ele_rec.element_type_id
2292 ,p_object_version_number => l_av_ovn);
2293 ELSE
2294 /*Bug fix 3353252 Modifying the alternate name to be unique by prefixing the number */
2295 l_new_en_repname:=l_cnt_repname ||' ~ ' || nvl(ele_rec.reporting_name,ele_rec.element_name);
2296 hxc_alias_values_api.create_alias_value
2297 (p_alias_value_id => l_av_id
2298 ,p_alias_value_name => l_new_en_repname
2299 ,p_date_from => hr_general.start_of_time
2300 ,p_date_to => NULL
2301 ,p_alias_definition_id => l_an_en_id
2302 ,p_enabled_flag => 'Y'
2303 ,p_attribute_category => l_an_context
2304 ,p_attribute1 => ele_rec.element_type_id
2305 ,p_object_version_number => l_av_ovn);
2306 FND_MESSAGE.SET_NAME ('HXC', 'HXC_GEN_FLEX_MOD_ALT_NAME');
2307 FND_MESSAGE.SET_TOKEN('REP_NAME',nvl(ele_rec.reporting_name,ele_rec.element_name));
2308 FND_MESSAGE.SET_TOKEN('NEW_REP_NAME',l_new_en_repname);
2309 l_temp_msg:=FND_MESSAGE.GET();
2310 l_warning_string:=l_warning_string || l_temp_msg || '
2311 ';
2312 l_alt_name_change:='Y';
2313 /*end of fix for 3353252*/
2314 END IF;
2315 close csr_chk_repname_exists;
2316 END IF;
2317 --
2318 IF l_dis_value_exists = 'N' THEN
2319 l_cnt_repname:=0;
2320 open csr_chk_repname_exists(l_an_dis_id,nvl(ele_rec.reporting_name,ele_rec.element_name));
2321 fetch csr_chk_repname_exists into l_cnt_repname;
2322
2323 IF l_cnt_repname=0 THEN
2324 hxc_alias_values_api.create_alias_value
2325 (p_alias_value_id => l_av_id
2326 ,p_alias_value_name => nvl(ele_rec.reporting_name,
2327 ele_rec.element_name)
2328 ,p_date_from => hr_general.start_of_time
2329 ,p_date_to => NULL
2330 ,p_alias_definition_id => l_an_dis_id
2331 ,p_enabled_flag => 'N'
2332 ,p_attribute_category => l_an_context
2333 ,p_attribute1 => ele_rec.element_type_id
2334 ,p_object_version_number => l_av_ovn);
2335 ELSE
2336 /*Bug fix 3353252 Modifying the alternate name to be unique by prefixing the number */
2337 l_new_dis_repname:=l_cnt_repname ||' ~ ' || nvl(ele_rec.reporting_name,ele_rec.element_name);
2338 hxc_alias_values_api.create_alias_value
2339 (p_alias_value_id => l_av_id
2340 ,p_alias_value_name => l_new_dis_repname
2341 ,p_date_from => hr_general.start_of_time
2342 ,p_date_to => NULL
2343 ,p_alias_definition_id => l_an_dis_id
2344 ,p_enabled_flag => 'N'
2345 ,p_attribute_category => l_an_context
2346 ,p_attribute1 => ele_rec.element_type_id
2347 ,p_object_version_number => l_av_ovn);
2348 l_alt_name_change:='Y';
2349 FND_MESSAGE.SET_NAME ('HXC', 'HXC_GEN_FLEX_MOD_ALT_NAME');
2350 FND_MESSAGE.SET_TOKEN('REP_NAME',nvl(ele_rec.reporting_name,ele_rec.element_name));
2351 FND_MESSAGE.SET_TOKEN('NEW_REP_NAME',l_new_dis_repname);
2352 l_temp_msg:=FND_MESSAGE.GET();
2353 l_warning_string1:=l_warning_string1 || l_temp_msg || '
2354 ';
2355 /*end of fix for 3353252*/
2356 END IF;
2357 close csr_chk_repname_exists;
2358 END IF;
2359 --
2360 --
2361 END IF; --l_abs_elem_flg
2362
2363
2364 if FND_FLEX_DSC_API.context_exists(
2365 P_APPL_SHORT_NAME => l_appl_short_name,
2366 P_FLEXFIELD_NAME => l_flexfield_name,
2367 P_CONTEXT_CODE => 'ELEMENT - '|| ele_rec.element_type_id
2368 ) then
2369 FND_FLEX_DSC_API.delete_context(
2370 APPL_SHORT_NAME => l_appl_short_name,
2371 FLEXFIELD_NAME => l_flexfield_name,
2372 CONTEXT => 'ELEMENT - '|| ele_rec.element_type_id);
2373
2374 end if; -- Does this element context exist?
2375
2376 FND_FLEX_DSC_API.create_context(
2377 APPL_SHORT_NAME => l_appl_short_name,
2378 FLEXFIELD_NAME => l_flexfield_name,
2379 CONTEXT_CODE => 'ELEMENT - '|| ele_rec.element_type_id,
2380 CONTEXT_NAME => ele_rec.element_name,
2381 DESCRIPTION => 'Auto generated HXC element context',
2382 ENABLED => 'Y',
2383 GLOBAL_FLAG => 'N');
2384
2385 l_element_count := l_element_count +1;
2386
2387 IF g_debug THEN
2388 hr_utility.trace('l_element_count ::'||l_element_count);
2389 hr_utility.trace('call to Create the Building block information type for this context ::'||ele_rec.element_type_id);
2390 END IF;
2391 --
2392 -- Create the Building block information type for this context
2393 --
2394 l_building_block_info_id := create_bld_blk_info_type(
2395 p_appl_short_name => l_appl_short_name,
2396 p_flexfield_name => l_flexfield_name,
2397 p_legislation_code => NULL,
2398 p_bld_blk_info_type => 'ELEMENT - '|| ele_rec.element_type_id,
2399 p_category => 'ELEMENT');
2400
2401
2402 IF g_debug THEN
2403 hr_utility.trace('intial value of l_disp_flag ::'||l_disp_flag);
2404 END IF;
2405
2406 -- Modified for Bug 12741569
2407
2408 /* Bug 5919417 Start */
2409
2410
2411 --
2412 -- Find the number of input values with display_sequence 12,13,14 or 15 before generating segments
2413
2414 -- Modified for Bug 14570475
2415 l_disp_flag := 'N';
2416 for seq_rec in c_ipvs(ele_rec.element_type_id, l_effective_date)
2417 LOOP
2418 if((seq_rec.display_sequence > 11) AND (seq_rec.display_sequence < 16) AND (seq_rec.mandatory_flag <> 'X')) then
2419 l_disp_flag := 'Y';
2420 exit;
2421 end if;
2422 END LOOP;
2423
2424 /* Bug 5919417 End */
2425
2426 IF g_debug THEN
2427 hr_utility.trace('l_disp_flag for element -'||ele_rec.element_type_id||'- ::'||l_disp_flag);
2428 hr_utility.trace('initial segment count ::'||l_segment_count);
2429 END IF;
2430
2431 --
2432 -- Now fetch each input value and generate segments, if we are creating
2433 --
2434
2435
2436 l_segment_count := 0;
2437 for ipv_rec in c_ipvs(ele_rec.element_type_id, l_effective_date) LOOP
2438
2439 if((ipv_rec.display_sequence < 12) OR (ipv_rec.display_sequence > 15)) then
2440
2441 l_segment_count := l_segment_count +1;
2442
2443 end if;
2444
2445 if (ipv_rec.mandatory_flag <> 'X') then
2446
2447 if((ipv_rec.display_sequence > 11) AND (ipv_rec.display_sequence < 16)) then
2448
2449 l_segment_choice := ipv_rec.display_sequence;
2450
2451 else
2452
2453 -- l_segment_count := l_segment_count + 1; /* Bug 5919417 */
2454
2455 l_segment_choice := l_segment_count;
2456
2457 end if;
2458
2459
2460 /* Bug 5919417 Start */
2461 -- Modified for Bug 14570475
2462 if l_segment_count = 11 and l_disp_flag = 'Y' then
2463 l_segment_count := l_segment_count + 4;
2464 IF g_debug THEN
2465 hr_utility.trace('In diplay flag check -- segment count ::'||l_segment_count);
2466 END IF;
2467 end if;
2468
2469 /* Bug 5919417 Start */
2470
2471
2472 FND_FLEX_DSC_API.create_segment(
2473 APPL_SHORT_NAME => l_appl_short_name,
2474 FLEXFIELD_NAME => l_flexfield_name,
2475 CONTEXT_NAME => 'ELEMENT - '||ele_rec.element_type_id,
2476 NAME => ipv_rec.name,
2477 COLUMN => 'ATTRIBUTE'||to_char(l_segment_choice),
2478 DESCRIPTION => 'Auto generated HXC element input value context segment',
2479 SEQUENCE_NUMBER => l_segment_choice,
2480 ENABLED => 'N',
2481 DISPLAYED => 'N',
2482 VALUE_SET => NULL,
2483 DEFAULT_TYPE => NULL,
2484 DEFAULT_VALUE => NULL,
2485 REQUIRED => 'N',
2486 SECURITY_ENABLED => 'N',
2487 DISPLAY_SIZE => 30,
2488 DESCRIPTION_SIZE => 50,
2489 CONCATENATED_DESCRIPTION_SIZE => 10,
2490 LIST_OF_VALUES_PROMPT => ipv_rec.name,
2491 WINDOW_PROMPT => ipv_rec.name,
2492 RANGE => NULL,
2493 SRW_PARAMETER => NULL);
2494
2495 if(ipv_rec.name = 'Jurisdiction') then
2496
2497 FOR i in 1..4 LOOP
2498
2499 if (i=1) then
2500 l_temp_segment_choice := 27;
2501 l_attr_prompt := 'State';
2502 elsif (i=2) then
2503 l_temp_segment_choice := 28;
2504 l_attr_prompt := 'County';
2505 elsif (i=3) then
2506 l_temp_segment_choice := 29;
2507 l_attr_prompt := 'City';
2508 elsif (i=4) then
2509 l_temp_segment_choice := 30;
2510 l_attr_prompt := 'Zipcode';
2511 end if;
2512
2513 FND_FLEX_DSC_API.create_segment(
2514 APPL_SHORT_NAME => l_appl_short_name,
2515 FLEXFIELD_NAME => l_flexfield_name,
2516 CONTEXT_NAME => 'ELEMENT - '||ele_rec.element_type_id,
2517 NAME => l_attr_prompt,
2518 COLUMN => 'ATTRIBUTE'||to_char(l_temp_segment_choice),
2519 DESCRIPTION => 'Auto generated HXC element input value context segment',
2520 SEQUENCE_NUMBER => l_temp_segment_choice,
2521 ENABLED => 'N',
2522 DISPLAYED => 'N',
2523 VALUE_SET => NULL,
2524 DEFAULT_TYPE => NULL,
2525 DEFAULT_VALUE => NULL,
2526 REQUIRED => 'N',
2527 SECURITY_ENABLED => 'N',
2528 DISPLAY_SIZE => 30,
2529 DESCRIPTION_SIZE => 50,
2530 CONCATENATED_DESCRIPTION_SIZE => 10,
2531 LIST_OF_VALUES_PROMPT => l_attr_prompt,
2532 WINDOW_PROMPT => l_attr_prompt,
2533 RANGE => NULL,
2534 SRW_PARAMETER => NULL);
2535 end loop;
2536 end if;
2537 end if; -- is this a user enterable segment
2538
2539 end LOOP; -- Input value loop
2540
2541 if l_max_input_value_count < l_segment_count then
2542 l_max_input_value_count := l_segment_count;
2543 end if;
2544
2545 end LOOP; -- Element loop
2546
2547 /*
2548 Added for 8645021 HR OTL Absence Integration
2549
2550 Insertion into hxc_absence_type_elements
2551 */
2552 -- change start
2553 IF (p_incl_abs_flg = 'Y' and hxc_abs_tab.COUNT>0) THEN
2554
2555 insert_into_hxc_absences (p_error_msg => l_err_msg,
2556 p_abs_info => hxc_abs_tab);
2557
2558 END IF;
2559 -- change end
2560
2561 /*Bug fix for 3353252, End the concurrent request in warning and Throw a warning message if the alternate names are
2562 modified.Here we are concatenating all the warning messages.*/
2563 IF l_alt_name_change='Y' then
2564 -- Set retcode to 1, indicating a WARNING to the ConcMgr
2565 p_errcode := c_warning;
2566 fnd_file.put_line (fnd_file.LOG, l_name );
2567 fnd_file.put_line (fnd_file.LOG, l_warning_string);
2568 fnd_file.put_line (fnd_file.LOG, l_warning_string1);
2569 -- null;
2570 END IF;
2571 /*end of fix for 3353252*/
2572
2573 /*
2574 Ok next create the key flexfield information
2575
2576 First fetch all the information from the key flex tables
2577
2578 */
2579
2580 fnd_flex_key_api.set_session_mode('seed_data');
2581
2582
2583 FOR i in 1..4 LOOP
2584
2585 l_generate := FALSE;
2586
2587 if ((i=1) AND (p_generate_cost = 'Y')) then
2588 l_key_app := 'PAY';
2589 l_key_flex_code := 'COST';
2590 l_generate := TRUE;
2591 elsif ((i=2) AND (p_generate_group = 'Y')) then
2592 l_key_app := 'PAY';
2593 l_key_flex_code := 'GRP';
2594 l_generate := TRUE;
2595 elsif ((i=3) AND (p_generate_job = 'Y')) then
2596 l_key_app := 'PER';
2597 l_key_flex_code := 'JOB';
2598 l_generate := TRUE;
2599 elsif ((i=4) AND (p_generate_pos = 'Y')) then
2600 l_key_app := 'PER';
2601 l_key_flex_code := 'POS';
2602 l_generate := TRUE;
2603 end if;
2604
2605 if l_generate then
2606
2607 l_max_segment_count := 0;
2608 l_key_flex_structure_count :=0;
2609
2610 --
2611 -- Create the dummy element context and mappings
2612 --
2613 create_dummy_context(
2614 p_appl_short_name => l_appl_short_name,
2615 p_flexfield_name => l_flexfield_name,
2616 p_context_name => l_key_flex_code,
2617 p_segment_name_prefix=>initcap(l_key_flex_code)||'Segment',
2618 p_max_segments => 30
2619 );
2620
2621 l_key_flex := fnd_flex_key_api.find_flexfield(
2622 appl_short_name => l_key_app
2623 ,flex_code => l_key_flex_code);
2624
2625 /*
2626 Next fetch all the stuctures associated with this
2627 flexfield
2628 */
2629
2630
2631 fnd_flex_key_api.get_structures(flexfield => l_key_flex,
2632 enabled_only => TRUE,
2633 nstructures => l_structures,
2634 structures => l_key_structure_list);
2635
2636 l_structure_count := l_key_structure_list.first;
2637
2638 LOOP
2639
2640 EXIT WHEN not l_key_structure_list.exists(l_structure_count);
2641
2642 /*
2643 If the context exists, delete it and recreate otherwise just create it
2644 */
2645
2646 if FND_FLEX_DSC_API.context_exists(
2647 P_APPL_SHORT_NAME => l_appl_short_name,
2648 P_FLEXFIELD_NAME => l_flexfield_name,
2649 P_CONTEXT_CODE => l_key_flex_code||' - '
2650 ||to_char(l_key_structure_list(l_structure_count))
2651 ) then
2652 FND_FLEX_DSC_API.delete_context(
2653 APPL_SHORT_NAME => l_appl_short_name,
2654 FLEXFIELD_NAME => l_flexfield_name,
2655 CONTEXT => l_key_flex_code||' - '
2656 ||to_char(l_key_structure_list(l_structure_count)));
2657
2658 end if; -- Does this context exist?
2659
2660 /*
2661 Get information about the structure
2662 */
2663
2664 l_key_structure := FND_FLEX_KEY_API.find_structure(
2665 flexfield => l_key_flex,
2666 structure_number => l_key_structure_list(l_structure_count));
2667
2668 FND_FLEX_DSC_API.create_context(
2669 APPL_SHORT_NAME => l_appl_short_name,
2670 FLEXFIELD_NAME => l_flexfield_name,
2671 CONTEXT_CODE => l_key_flex_code||' - '
2672 ||to_char(l_key_structure_list(l_structure_count)),
2673 CONTEXT_NAME => l_key_structure.structure_code,
2674 DESCRIPTION => 'Auto generated HXC '||l_key_flex_code||' context',
2675 ENABLED => 'Y',
2676 GLOBAL_FLAG => 'N');
2677
2678 l_key_flex_structure_count := l_key_flex_structure_count +1;
2679
2680 --
2681 -- Create the Building block information type for this context
2682 --
2683 l_building_block_info_id := create_bld_blk_info_type(
2684 p_appl_short_name => l_appl_short_name,
2685 p_flexfield_name => l_flexfield_name,
2686 p_legislation_code => NULL,
2687 p_bld_blk_info_type =>l_key_flex_code||' - '
2688 ||to_char(l_key_structure_list(l_structure_count)),
2689 p_category => l_key_flex_code);
2690
2691 /*
2692 Now, fetch the key flex segment information, and recreate the segments
2693 in the descriptive flexfield case
2694 */
2695
2696 fnd_flex_key_api.get_segments(flexfield => l_key_flex,
2697 structure => l_key_structure,
2698 enabled_only => TRUE,
2699 nsegments => l_segments,
2700 segments => l_key_segment_list);
2701
2702 l_segment_count := l_key_segment_list.first;
2703
2704 LOOP
2705
2706 EXIT WHEN not l_key_segment_list.exists(l_segment_count);
2707 /*
2708 Get information about this segment
2709 */
2710 l_key_segment := FND_FLEX_KEY_API.find_segment(
2711 flexfield => l_key_flex,
2712 structure => l_key_structure,
2713 segment_name => l_key_segment_list(l_segment_count));
2714 /*
2715 Create the descriptive flexfield segment for this corresponding segment
2716 */
2717
2718 FND_FLEX_DSC_API.create_segment(
2719 APPL_SHORT_NAME => l_appl_short_name,
2720 FLEXFIELD_NAME => l_flexfield_name,
2721 CONTEXT_NAME => l_key_flex_code||' - '
2722 ||to_char(l_key_structure_list(l_structure_count)),
2723 NAME => valid_segment_name(l_key_segment.segment_name,l_key_flex_code,l_key_structure_list(l_structure_count)),
2724 COLUMN => 'ATTRIBUTE'||to_char(l_segment_count),
2725 DESCRIPTION => 'Auto generated HXC '||l_key_flex_code||' context segment',
2726 SEQUENCE_NUMBER => l_key_segment.segment_number,
2727 ENABLED => 'N',
2728 DISPLAYED => 'N',
2729 VALUE_SET => find_value_set(l_key_segment.value_set_id),
2730 DEFAULT_TYPE => l_key_segment.default_type,
2731 DEFAULT_VALUE => l_key_segment.default_value,
2732 REQUIRED => l_key_segment.required_flag,
2733 SECURITY_ENABLED => l_key_segment.security_flag,
2734 DISPLAY_SIZE => l_key_segment.display_size,
2735 DESCRIPTION_SIZE => l_key_segment.description_size,
2736 CONCATENATED_DESCRIPTION_SIZE => l_key_segment.concat_size,
2737 LIST_OF_VALUES_PROMPT => l_key_segment.lov_prompt,
2738 WINDOW_PROMPT => l_key_segment.window_prompt,
2739 RANGE => NULL,
2740 SRW_PARAMETER => NULL);
2741
2742 l_segment_count := l_key_segment_list.next(l_segment_count);
2743 END LOOP;
2744
2745 if l_max_segment_count < l_segment_count then
2746 l_max_segment_count := l_segment_count;
2747 end if;
2748
2749 l_structure_count := l_key_structure_list.next(l_structure_count);
2750
2751 END LOOP;
2752
2753 end if; -- should we generate on this pass.
2754
2755 END LOOP;
2756
2757 --
2758 -- Do we need to check for missing types and
2759 -- usages? We are always going to do this now
2760 --
2761
2762 create_missing_type_usages(
2763 p_appl_short_name => 'HXC'
2764 ,p_flex_name => l_flexfield_name);
2765
2766 if p_generate_prj = 'Y' then
2767
2768 create_dummy_context(
2769 p_appl_short_name => l_appl_short_name,
2770 p_flexfield_name => l_flexfield_name,
2771 p_context_name => 'PAEXPITDFF',
2772 p_segment_name_prefix=>'PADFFAttribute',
2773 p_max_segments => 10
2774 );
2775
2776 --
2777 -- Next loop through all the projects contexts,
2778 -- and create the information types and usages
2779 --
2780 duplicate_desc_flex
2781 (p_appl_short_name => 'PA',
2782 p_flexfield_name => 'PA_EXPENDITURE_ITEMS_DESC_FLEX',
2783 p_otc_appl_short_name => 'HXC',
2784 p_otc_flex_name => l_flexfield_name,
2785 p_context_prefix => 'PAEXPITDFF',
2786 p_preserve => true
2787 );
2788
2789 include_mapping_components('PAEXPITDFF');
2790
2791 end if; -- are we generating mappings for the projects flex?
2792
2793 end if; -- are we undoing?
2794
2795 --
2796 -- This next section updates preference definitions from the
2797 -- flex definition of OTC PREFERENCES
2798 --
2799
2800 create_preference_definitions(
2801 p_flex_name => 'OTC PREFERENCES'
2802 ,p_appl_short_name => 'HXC'
2803 );
2804
2805
2806 commit;
2807
2808 /*
2809 Added for 8645021 HR OTL Absence Integration
2810
2811 Calling the Error msg
2812
2813 EXCEPTION
2814 WHEN PROFILE_NOT_SET THEN
2815 FND_MESSAGE.SET_NAME ('HXC', 'HXC_ABS_PROF_PARAM_MISMATCH');
2816 l_temp_msg:=FND_MESSAGE.GET();
2817 fnd_file.put_line(fnd_file.log,l_temp_msg);
2818 RAISE;
2819 */
2820
2821 END run_process;
2822
2823 END hxc_create_flex_mappings;