[Home] [Help]
PACKAGE BODY: APPS.HXC_CREATE_FLEX_MAPPINGS
Source
1 PACKAGE BODY hxc_create_flex_mappings AS
2 /* $Header: hxcflxdn.pkb 120.6.12010000.2 2008/08/05 12:02:11 ubhat ship $ */
3 --
4 -- Global store for the number of global data elements
5 --
6
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 PROCEDURE undo(
347 p_appl_short_name in FND_APPLICATION.APPLICATION_SHORT_NAME%TYPE
348 ,p_flexfield_name in FND_DESCRIPTIVE_FLEXS.DESCRIPTIVE_FLEXFIELD_NAME%TYPE
349 ,p_element_set_id in PAY_ELEMENT_SETS.ELEMENT_SET_ID%TYPE
350 ,p_effective_date in DATE
351 ,p_include_cost in VARCHAR2
352 ,p_include_group in VARCHAR2
353 ,p_include_job in VARCHAR2
354 ,p_include_pos in VARCHAR2
355 ,p_include_prj in VARCHAR2) is
356 -- 115.24 Change
357 cursor c_elements(p_element_set_id in number, p_effective_date in date) is
358 select pet.element_type_id
359 from pay_element_types_f pet,
360 pay_element_type_rules per
361 where per.element_set_id = p_element_set_id
362 and per.include_or_exclude = 'I'
363 and per.element_type_id = pet.element_type_id
364 and multiple_entries_allowed_flag = 'Y'
365 and p_effective_date between effective_start_date and effective_end_date;
366 -- End 115.24 Change
367 l_bld_blk_info_type_id HXC_BLD_BLK_INFO_TYPES.BLD_BLK_INFO_TYPE_ID%TYPE;
368
369 l_generate BOOLEAN default FALSE;
370 l_loop_var NUMBER default 0;
371 l_key_app VARCHAR2(30) := 'PAY';
372 l_key_flex_code VARCHAR2(30) := 'COST';
373
374 l_flex FND_DFLEX.dflex_r;
375 l_flex_info FND_DFLEX.dflex_dr;
376
377 l_basis_string fnd_descr_flex_contexts.descriptive_flex_context_code%type;
378
379 BEGIN
380
381 --
382 -- The undo procedure will be called when the user wishes to remove
383 -- the mappings.
384 --
385
386 if p_element_set_id is not null then
387 -- 115.24 Change
388 for ele_rec in c_elements(p_element_set_id,p_effective_date) LOOP
389
390 l_basis_string := 'ELEMENT - '||ele_rec.element_type_id;
391
392 l_bld_blk_info_type_id := check_delete_info_type(l_basis_string);
393
394 if l_bld_blk_info_type_id is null then
395 remove_mapping_component(l_basis_string);
396 remove_bld_blk_usage(l_basis_string);
397 remove_flex_context(
398 p_appl_short_name => p_appl_short_name,
399 p_flexfield_name => p_flexfield_name,
400 p_flex_context_basis => l_basis_string);
401 remove_bld_blk_info_type(l_basis_string);
402
403 end if;
404
405 end loop;
406 -- End 115.24 Change
407 end if;
408
409 FOR l_loop_var in 1..4 LOOP
410
411 l_generate := FALSE;
412
413 if ((l_loop_var=1) AND (p_include_cost = 'Y')) then
414 l_key_app := 'PAY';
415 l_key_flex_code := 'COST';
416 l_generate := TRUE;
417 elsif ((l_loop_var=2) AND (p_include_group = 'Y')) then
418 l_key_flex_code := 'GRP';
419 l_generate := TRUE;
420 elsif ((l_loop_var=3) AND (p_include_job = 'Y')) then
421 l_key_app := 'PER';
422 l_key_flex_code := 'JOB';
423 l_generate := TRUE;
424 elsif ((l_loop_var=4) AND (p_include_pos = 'Y')) then
425 l_key_flex_code := 'POS';
426 l_generate := TRUE;
427 end if;
428
429 if l_generate then
430
431 l_bld_blk_info_type_id := check_delete_info_type(l_key_flex_code);
432
433 if l_bld_blk_info_type_id is null then
434 remove_mapping_component(l_key_flex_code);
435 remove_bld_blk_usage(l_key_flex_code);
436 remove_flex_context(
437 p_appl_short_name => p_appl_short_name,
438 p_flexfield_name => p_flexfield_name,
439 p_flex_context_basis => l_key_flex_code);
440 remove_bld_blk_info_type(l_key_flex_code);
441 end if;
442
443 end if; -- are we including this key flex
444
445 if p_include_prj = 'Y' then
446
447 -- l_bld_blk_info_type_id := check_delete_info_type('PAEXPITDFF');
448 l_bld_blk_info_type_id := null;
449
450 if l_bld_blk_info_type_id is null then
451 remove_mapping_component('PAEXPITDFF');
452 remove_bld_blk_usage('PAEXPITDFF');
453 remove_flex_context(
454 p_appl_short_name => p_appl_short_name,
455 p_flexfield_name => p_flexfield_name,
456 p_flex_context_basis => 'PAEXPITDFF');
457 remove_bld_blk_info_type('PAEXPITDFF');
458 else
459 FND_MESSAGE.set_name('HXC','HXC_COMPONENTS_MAPPED');
460 FND_MESSAGE.raise_error;
461 end if;
462
463 end if; -- remove the projects definition?
464
465 END LOOP;
466
467
468 END undo;
469
470 /*
471 Private functions to find value sets, and generate mappings, building
472 block information types etc.
473 */
474
475 FUNCTION find_value_set(l_vid in FND_FLEX_VALUE_SETS.FLEX_VALUE_SET_ID%TYPE)
476 RETURN VARCHAR2 is
477
478 l_value_set_name FND_FLEX_VALUE_SETS.FLEX_VALUE_SET_NAME%type := NULL;
479
480 l_value_set FND_VSET.VALUESET_R;
481 l_value_set_format FND_VSET.VALUESET_DR;
482
483 BEGIN
484
485 if l_vid is not null then
486 FND_VSET.GET_VALUESET(
487 valueset_id => l_vid,
488 valueset => l_value_set,
489 format => l_value_set_format);
490 l_value_set_name := l_value_set.name;
491
492 end if;
493
494 RETURN l_value_set_name;
495
496 END find_value_set;
497
498 function get_name_prefix
499 (p_flex_code in varchar2
500 ,p_flex_num in number) return varchar2 is
501
502 l_prefix varchar2(21);
503
504 begin
505
506 select substr(id_flex_structure_code,1,20) into l_prefix
507 from fnd_id_flex_structures
508 where id_flex_code = p_flex_code
509 and id_flex_num = p_flex_num;
510
511 return l_prefix;
512
513 end get_name_prefix;
514
515 FUNCTION valid_segment_name(
516 p_name in VARCHAR2
517 ,p_flex_code in varchar2
518 ,p_flex_num in varchar2
519 ) return VARCHAR2 is
520
521 cursor c_dup_name
522 (p_seg_name in varchar2
523 ,p_id_flex_code in varchar2
524 ,p_id_flex_num in number
525 ,p_application_id in number
526 ) is
527 select 'Y'
528 from fnd_id_flex_segments
529 where id_flex_code = p_id_flex_code
530 and id_flex_num = p_id_flex_num
531 and segment_name = p_seg_name
532 and application_id = p_application_id;
533
534 l_valid_name FND_DESCR_FLEX_COLUMN_USAGES.END_USER_COLUMN_NAME%TYPE;
535
536 l_dummy varchar2(5);
537
538 BEGIN
539
540 if upper(p_name) = 'GROUP' then
541
542 l_valid_name := 'People Group';
543
544 -- Check for duplicates
545
546 open c_dup_name(l_valid_name,p_flex_code,p_flex_num,801);
547 fetch c_dup_name into l_dummy;
548
549 if c_dup_name%NOTFOUND then
550 close c_dup_name;
551 else
552 close c_dup_name;
553 -- we have to try again with the name.
554 l_valid_name := 'HXC Group';
555 open c_dup_name(l_valid_name,p_flex_code,p_flex_num,801);
556 fetch c_dup_name into l_dummy;
557
558 if c_dup_name%NOTFOUND then
559 close c_dup_name;
560 else
561 close c_dup_name;
562 -- we have to try again with the name.
563 l_valid_name := get_name_prefix(p_flex_code,p_flex_num)||' Group';
564 open c_dup_name(l_valid_name,p_flex_code,p_flex_num,801);
565 fetch c_dup_name into l_dummy;
566
567 if c_dup_name%NOTFOUND then
568 close c_dup_name;
569 else
570 close c_dup_name;
571 -- we can't generate a name - throw an error
572 FND_MESSAGE.SET_NAME('HXC','HXC_UNABLE_TO_NAME_SEGMENT');
573 FND_MESSAGE.SET_TOKEN('SEGMENT_NAME',p_name);
574 FND_MESSAGE.SET_TOKEN('STRUCTURE_NUMBER',p_flex_num);
575 end if;
576 end if;
577 end if;
578
579 else
580 l_valid_name := p_name;
581 end if;
582
583 return l_valid_name;
584
585 END valid_segment_name;
586
587 FUNCTION mapping_missing(
588 p_name in hxc_mapping_components.name%type,
589 p_field_name in hxc_mapping_components.field_name%type,
590 p_bld_blk_info_type_id in hxc_bld_blk_info_types.bld_blk_info_type_id%type,
591 p_segment in hxc_mapping_components.segment%type,
592 p_mp_id IN OUT NOCOPY NUMBER,
593 p_ovn IN OUT NOCOPY NUMBER ) RETURN BOOLEAN IS
594
595
596 BEGIN
597
598 select mapping_component_id,
599 object_version_number
600 into p_mp_id,
601 p_ovn
602 from hxc_mapping_components
603 where field_name = p_field_name
604 and name = p_name
605 and segment = p_segment;
606
607 RETURN FALSE;
608
609 EXCEPTION
610 WHEN NO_DATA_FOUND then
611 RETURN TRUE;
612
613 END;
614
615 FUNCTION update_allowed
616 (p_map_comp_id in HXC_MAPPING_COMPONENTS.MAPPING_COMPONENT_ID%TYPE) RETURN BOOLEAN is
617
618 l_dummy VARCHAR2(2);
619
620 BEGIN
621
622 -- Just check to see if this mapping component is used
623 -- note it can be used more than once, hence the rownum
624 -- in the where clause.
625
626 select 'Y'
627 into l_dummy
628 from hxc_mapping_comp_usages
629 where mapping_component_id = p_map_comp_id
630 and rownum =1;
631
632 return false;
633
634 EXCEPTION
635 when no_data_found then
636 return true;
637
638 END update_allowed;
639
640 PROCEDURE create_mapping(
641 p_name in hxc_mapping_components.name%type,
642 p_field_name in hxc_mapping_components.field_name%type,
643 p_bld_blk_info_type_id in hxc_bld_blk_info_types.bld_blk_info_type_id%type,
644 p_segment in hxc_mapping_components.segment%type) is
645
646 l_mapping_component_id NUMBER;
647 l_ovn NUMBER;
648
649 BEGIN
650
651 if mapping_missing(
652 p_name => p_name,
653 p_field_name => p_field_name,
654 p_bld_blk_info_type_id => p_bld_blk_info_type_id,
655 p_segment => p_segment,
656 p_mp_id => l_mapping_component_id,
657 p_ovn => l_ovn ) then
658 hxc_mapping_component_api.create_mapping_component(
659 p_validate => FALSE,
660 p_mapping_component_id => l_mapping_component_id,
661 p_object_version_number => l_ovn,
662 p_name => p_name,
663 p_field_name => p_field_name,
664 p_bld_blk_info_type_id => p_bld_blk_info_type_id,
665 p_segment => p_segment);
666
667 elsif (update_allowed(l_mapping_component_id)) then
668 hxc_mapping_component_api.update_mapping_component(
669 p_validate => FALSE,
670 p_mapping_component_id => l_mapping_component_id,
671 p_object_version_number => l_ovn,
672 p_name => p_name,
673 p_field_name => p_field_name,
674 p_bld_blk_info_type_id => p_bld_blk_info_type_id,
675 p_segment => p_segment);
676
677 end if;
678
679 END create_mapping;
680
681 FUNCTION create_bld_blk_info_type(
682 p_appl_short_name in VARCHAR2
683 ,p_flexfield_name in VARCHAR2
684 ,p_legislation_code in hxc_bld_blk_info_types.legislation_code%type
685 ,p_bld_blk_info_type in hxc_bld_blk_info_types.bld_blk_info_type%type
686 ,p_category hxc_bld_blk_info_type_usages.building_block_category%type
687 ) RETURN NUMBER is
688
689 cursor c_info_type_id (p_info_type in hxc_bld_blk_info_types.bld_blk_info_type%type) is
690 select bld_blk_info_type_id
691 from HXC_BLD_BLK_INFO_TYPES
692 where bld_blk_info_type = p_info_type;
693
694 l_bld_blk_info_type_id NUMBER;
695
696 BEGIN
697 --
698 -- Try to obtain the building block info type id
699 --
700 OPEN c_info_type_id(p_info_type => p_bld_blk_info_type);
701 FETCH c_info_type_id into l_bld_blk_info_type_id;
702
703
704 --
705 -- Create or Delete the information type record
706 --
707
708 if c_info_type_id%NOTFOUND then
709
710 insert into HXC_BLD_BLK_INFO_TYPES(
711 bld_blk_info_type_id,
712 legislation_code,
713 descriptive_flexfield_name,
714 bld_blk_info_type,
715 multiple_occurences_flag,
716 created_by,
717 creation_date,
718 last_updated_by,
719 last_update_date,
720 last_update_login,
721 object_version_number)
722 select
723 HXC_BLD_BLK_INFO_TYPES_S.NEXTVAL,
724 p_legislation_code,
725 p_flexfield_name,
726 p_bld_blk_info_type,
727 'N',
728 0,
729 sysdate,
730 0,
731 sysdate,
732 0,
733 1
734 from sys.dual;
735
736 end if;
737
738 close c_info_type_id;
739
740 --
741 -- Find the bld_blk_type_id for this information type
742 --
743 OPEN c_info_type_id(p_bld_blk_info_type);
744 FETCH c_info_type_id into l_bld_blk_info_type_id;
745 CLOSE c_info_type_id;
746
747 --
748 -- Create the information type category usage record
749 --
750 insert into HXC_BLD_BLK_INFO_TYPE_USAGES(
751 bld_blk_info_type_usage_id,
752 building_block_category,
753 bld_blk_info_type_id,
754 created_by,
755 creation_date,
756 last_updated_by,
757 last_update_date,
758 last_update_login,
759 object_version_number)
760 select
761 hxc_bld_blk_info_type_usages_s.nextval,
762 p_category,
763 l_bld_blk_info_type_id,
764 0,
765 sysdate,
766 0,
767 sysdate,
768 0,
769 1
770 from sys.dual
771 where not exists(
772 select 'Y'
773 from hxc_bld_blk_info_type_usages
774 where bld_blk_info_type_id = l_bld_blk_info_type_id);
775
776 RETURN l_bld_blk_info_type_id;
777
778 END create_bld_blk_info_type;
779
780 PROCEDURE create_missing_type_usages(
781 p_appl_short_name in FND_APPLICATION.APPLICATION_SHORT_NAME%TYPE
782 ,p_flex_name in FND_DESCRIPTIVE_FLEXS.DESCRIPTIVE_FLEXFIELD_NAME%TYPE
783 ) IS
784
785 cursor c_missing(
786 p_flex_name in FND_DESCRIPTIVE_FLEXS.DESCRIPTIVE_FLEXFIELD_NAME%TYPE
787 )is
788 select fdfc.DESCRIPTIVE_FLEX_CONTEXT_CODE
789 from fnd_descr_flex_contexts fdfc
790 where descriptive_flexfield_name = p_flex_name
791 and fdfc.descriptive_flex_context_code <> 'Global Data Elements'
792 and not exists (
793 select 'Y'
794 from hxc_bld_blk_info_types bbi
795 where bbi.bld_blk_info_type = fdfc.descriptive_flex_context_code
796 );
797 l_dummy_info_type_id NUMBER;
798
799 BEGIN
800
801 for miss_rec in c_missing(p_flex_name) LOOP
802
803 l_dummy_info_type_id := create_bld_blk_info_type(
804 p_appl_short_name => p_appl_short_name
805 ,p_flexfield_name => p_flex_name
806 ,p_legislation_code => NULL
807 ,p_bld_blk_info_type => miss_rec.descriptive_flex_context_code
808 ,p_category => miss_rec.descriptive_flex_context_code);
809
810 END LOOP;
811
812
813 END create_missing_type_usages;
814
815 PROCEDURE create_dummy_context(
816 p_appl_short_name in VARCHAR2,
817 p_flexfield_name in VARCHAR2,
818 p_context_name in VARCHAR2,
819 p_segment_name_prefix in VARCHAR2,
820 p_max_segments in NUMBER
821 ) IS
822
823 l_building_block_info_id NUMBER;
824
825 l_segment_name_prefix VARCHAR2(50);
826 l_sequence_number NUMBER;
827 l_map_comp_name VARCHAR2(50);
828 l_map_comp_field_name VARCHAR2(50);
829
830 BEGIN
831
832 --
833 -- Create the dummy context
834 --
835
836
837 if FND_FLEX_DSC_API.context_exists(
838 P_APPL_SHORT_NAME => p_appl_short_name,
839 P_FLEXFIELD_NAME => p_flexfield_name,
840 P_CONTEXT_CODE => 'Dummy '||initcap(p_context_name)||' Context'
841 ) then
842 FND_FLEX_DSC_API.delete_context(
843 APPL_SHORT_NAME => p_appl_short_name,
844 FLEXFIELD_NAME => p_flexfield_name,
845 CONTEXT => 'Dummy '||initcap(p_context_name)||' Context');
846
847 end if; -- Does this element context exist?
848
849 FND_FLEX_DSC_API.create_context(
850 APPL_SHORT_NAME => p_appl_short_name,
851 FLEXFIELD_NAME => p_flexfield_name,
852 CONTEXT_CODE => 'Dummy '||initcap(p_context_name)||' Context',
853 CONTEXT_NAME => 'Dummy '||initcap(p_context_name)||' Context',
854 DESCRIPTION => 'Auto generated HXC context',
855 ENABLED => 'Y',
856 GLOBAL_FLAG => 'N');
857
858
859
860 --
861 -- Next create the dummy information type
862 --
863 l_building_block_info_id := create_bld_blk_info_type(
864 p_appl_short_name => p_appl_short_name,
865 p_flexfield_name => p_flexfield_name,
866 p_legislation_code => NULL,
867 p_bld_blk_info_type => 'Dummy '||initcap(p_context_name)||' Context',
868 p_category => p_context_name);
869 --
870 -- Now create the dummy mappings
871 --
872 create_mapping(
873 p_name =>'Dummy '||initcap(p_context_name)||' Context',
874 p_field_name => 'Dummy '||initcap(p_context_name)||' Context',
875 p_bld_blk_info_type_id => l_building_block_info_id,
876 p_segment => 'ATTRIBUTE_CATEGORY');
877
878 --
879 -- Now create all the dummy segments
880 --
881
882 for i in 1..p_max_segments LOOP
883
884 FND_FLEX_DSC_API.create_segment(
885 APPL_SHORT_NAME => p_appl_short_name,
886 FLEXFIELD_NAME => p_flexfield_name,
887 CONTEXT_NAME => 'Dummy '||initcap(p_context_name)||' Context',
888 NAME => p_segment_name_prefix||to_char(i),
889 COLUMN => 'ATTRIBUTE'||to_char(i),
890 DESCRIPTION => 'Auto generated HXC context segment',
891 SEQUENCE_NUMBER => i,
892 ENABLED => 'N',
893 DISPLAYED => 'N',
894 VALUE_SET => NULL,
895 DEFAULT_TYPE => NULL,
896 DEFAULT_VALUE => NULL,
897 REQUIRED => 'N',
898 SECURITY_ENABLED => 'N',
899 DISPLAY_SIZE => 30,
900 DESCRIPTION_SIZE => 50,
901 CONCATENATED_DESCRIPTION_SIZE => 10,
902 LIST_OF_VALUES_PROMPT => p_segment_name_prefix||to_char(i),
903 WINDOW_PROMPT => p_segment_name_prefix||to_char(i),
904 RANGE => NULL,
905 SRW_PARAMETER => NULL);
906
907 --
908 -- Now create the dummy mappings
909 --
910 create_mapping(
911 p_name =>p_segment_name_prefix||to_char(i),
912 p_field_name => p_segment_name_prefix||to_char(i),
913 p_bld_blk_info_type_id => l_building_block_info_id,
914 p_segment => 'ATTRIBUTE'||to_char(i));
915
916 END LOOP;
917
918 IF(p_context_name = 'ELEMENT') THEN
919 FOR i in 1..4 LOOP
920
921 IF (i=1) THEN
922 l_segment_name_prefix := 'NAStateName';
923 l_sequence_number := 27;
924 l_map_comp_field_name := 'NA_STATE_NAME';
925 l_map_comp_name := 'NA State Name';
926 ELSIF (i=2) THEN
927 l_segment_name_prefix := 'NACountyName';
928 l_sequence_number := 28;
929 l_map_comp_field_name := 'NA_COUNTY_NAME';
930 l_map_comp_name := 'NA County Name';
931 ELSIF (i=3) THEN
932 l_segment_name_prefix := 'NACityName';
933 l_sequence_number := 29;
934 l_map_comp_field_name := 'NA_CITY_NAME';
935 l_map_comp_name := 'NA City Name';
936 ELSIF (i=4) THEN
937 l_segment_name_prefix := 'NAZipCode';
938 l_sequence_number := 30;
939 l_map_comp_field_name := 'NA_ZIP_CODE';
940 l_map_comp_name := 'NA Zip Code';
941 END IF;
942
943 FND_FLEX_DSC_API.create_segment(
944 APPL_SHORT_NAME => p_appl_short_name,
945 FLEXFIELD_NAME => p_flexfield_name,
946 CONTEXT_NAME => 'Dummy '||initcap(p_context_name)||' Context',
947 NAME => l_segment_name_prefix,
948 COLUMN => 'ATTRIBUTE'||to_char(l_sequence_number),
949 DESCRIPTION => 'Auto generated HXC context segment',
950 SEQUENCE_NUMBER => l_sequence_number,
951 ENABLED => 'N',
952 DISPLAYED => 'N',
953 VALUE_SET => NULL,
954 DEFAULT_TYPE => NULL,
955 DEFAULT_VALUE => NULL,
956 REQUIRED => 'N',
957 SECURITY_ENABLED => 'N',
958 DISPLAY_SIZE => 30,
959 DESCRIPTION_SIZE => 50,
960 CONCATENATED_DESCRIPTION_SIZE => 10,
961 LIST_OF_VALUES_PROMPT => l_segment_name_prefix,
962 WINDOW_PROMPT => l_segment_name_prefix,
963 RANGE => NULL,
964 SRW_PARAMETER => NULL);
965
966 create_mapping(
967 p_name =>l_map_comp_name,
968 p_field_name => l_map_comp_field_name,
969 p_bld_blk_info_type_id => l_building_block_info_id,
970 p_segment => 'ATTRIBUTE'||to_char(l_sequence_number));
971
972 END LOOP;
973 END IF;
974 END create_dummy_context;
975
976
977 PROCEDURE create_segments(
978 p_otc_appl_short_name in FND_APPLICATION.APPLICATION_SHORT_NAME%TYPE
979 ,p_context in FND_DFLEX.context_r
980 ,p_otc_flex_name in FND_DESCRIPTIVE_FLEXS.DESCRIPTIVE_FLEXFIELD_NAME%TYPE
981 ,p_context_code in FND_DESCR_FLEX_CONTEXTS.DESCRIPTIVE_FLEX_CONTEXT_CODE%TYPE
982 ) is
983
984 l_segments FND_DFLEX.segments_dr;
985 l_segment_index NUMBER;
986 l_segment_count NUMBER;
987
988
989 BEGIN
990
991 FND_DFLEX.get_Segments(
992 context => p_context
993 ,segments => l_segments
994 ,enabled_only => TRUE);
995
996 l_segment_index := l_segments.segment_name.first;
997
998 --
999 -- Ok, is this is the global context, then we can set
1000 -- the segment count to zero, otherwise we will already
1001 -- have used a few attributes in creating those global
1002 -- segments, so we'll need to increase the starting segment
1003 -- count to account for this.
1004 --
1005 if p_context.context_code = 'Global Data Elements' then
1006
1007 l_segment_count := 0;
1008 g_global_segment_count := l_segments.segment_name.count;
1009
1010 else
1011
1012 l_segment_count := g_global_segment_count;
1013
1014 end if;
1015
1016 LOOP
1017
1018 EXIT WHEN not l_segments.segment_name.exists(l_segment_index);
1019 l_segment_count := l_segment_count +1;
1020 --
1021 -- Create the segment in the OTC flex as it's
1022 -- defined in the other flexfield
1023 --
1024
1025 FND_FLEX_DSC_API.create_segment(
1026 APPL_SHORT_NAME => p_otc_appl_short_name,
1027 FLEXFIELD_NAME => p_otc_flex_name,
1028 CONTEXT_NAME => p_context_code,
1029 NAME => l_segments.segment_name(l_segment_index),
1030 -- COLUMN => 'ATTRIBUTE'||to_char(l_segment_count),
1031 COLUMN => l_segments.application_column_name(l_segment_index),
1032 DESCRIPTION =>l_segments.description(l_segment_index),
1033 -- SEQUENCE_NUMBER => l_segments.sequence(l_segment_index),
1034 SEQUENCE_NUMBER => l_segment_count,
1035 ENABLED => 'N',
1036 DISPLAYED => 'N',
1037 VALUE_SET => find_value_set(l_segments.value_set(l_segment_index)),
1038 DEFAULT_TYPE => l_segments.default_type(l_segment_index),
1039 DEFAULT_VALUE =>l_segments.default_value(l_segment_index),
1040 REQUIRED => 'N',
1041 SECURITY_ENABLED => 'N',
1042 DISPLAY_SIZE => l_segments.display_size(l_segment_index),
1043 DESCRIPTION_SIZE => l_segments.display_size(l_segment_index),
1044 CONCATENATED_DESCRIPTION_SIZE => l_segments.display_size(l_segment_index),
1045 LIST_OF_VALUES_PROMPT => l_segments.column_prompt(l_segment_index),
1046 WINDOW_PROMPT => l_segments.row_prompt(l_segment_index),
1047 RANGE => NULL,
1048 SRW_PARAMETER => NULL);
1049
1050 l_segment_index := l_segments.segment_name.next(l_segment_index);
1051
1052 END LOOP; -- segments loop
1053
1054 END create_segments;
1055
1056 FUNCTION check_contexts
1057 (p_contexts in FND_DFLEX.contexts_dr)
1058 RETURN BOOLEAN is
1059
1060 -- Here we ascertain whether the flex
1061 -- we are duplicating only has
1062 -- the global context associated with it.
1063 -- Note: all flexs have at least the global context
1064 -- If that's true we have to handle it in a slightly
1065 -- different way to the case when there are context
1066 -- segments.
1067
1068 -- Note, since flex impose a rule that a flexfield
1069 -- must always have global data elements, we can
1070 -- just check the number of contexts, and if only
1071 -- one - we know we only have global data elements!
1072
1073 BEGIN
1074
1075 if (p_contexts.ncontexts = 1) then
1076 return false;
1077 else
1078 return true;
1079 end if;
1080
1081 END check_contexts;
1082
1083 PROCEDURE duplicate_desc_flex
1084 (p_appl_short_name in FND_APPLICATION.APPLICATION_SHORT_NAME%TYPE,
1085 p_flexfield_name in FND_DESCRIPTIVE_FLEXS.DESCRIPTIVE_FLEXFIELD_NAME%TYPE,
1086 p_otc_appl_short_name in FND_APPLICATION.APPLICATION_SHORT_NAME%TYPE,
1087 p_otc_flex_name in FND_DESCRIPTIVE_FLEXS.DESCRIPTIVE_FLEXFIELD_NAME%TYPE,
1088 p_context_prefix in varchar2,
1089 p_preserve in boolean
1090 ) is
1091
1092 -- This cursor retrieves the Sequence Number from the context code like
1093 --PAEXPITDFFC - Number(PAEXPITDFFC - 4252), where Number is the maximum,
1094 --so that the next context like PAEXPITDFFC is created with the code
1095 --PAEXPITDFFC - (Sequence Number +1)
1096
1097 CURSOR get_max_sequence IS
1098 select max(to_number(substrB(DESCRIPTIVE_FLEX_CONTEXT_CODE,
1099 instr(DESCRIPTIVE_FLEX_CONTEXT_CODE,'-')+2)))
1100 FROM fnd_descr_flex_contexts_vl
1101 WHERE descriptive_flexfield_name = 'OTC Information Types'
1102 AND application_id = 809
1103 AND substrB(DESCRIPTIVE_FLEX_CONTEXT_CODE,0,
1104 instr(DESCRIPTIVE_FLEX_CONTEXT_CODE,'-')-2)
1105 =substrB(DESCRIPTIVE_FLEX_CONTEXT_name,0,
1106 instr(DESCRIPTIVE_FLEX_CONTEXT_name,'-')-2)||'C';
1107
1108 l_max_sequence_code fnd_descr_flex_contexts.descriptive_flex_context_code%TYPE;
1109 l_max_sequence_no VARCHAR2(30);
1110 l_flex FND_DFLEX.dflex_r;
1111 l_flex_info FND_DFLEX.dflex_dr;
1112 l_contexts FND_DFLEX.contexts_dr;
1113 l_current_context FND_DFLEX.context_r;
1114 l_global_context FND_DFLEX.context_r;
1115 l_segments FND_DFLEX.segments_dr;
1116 l_context_index NUMBER;
1117 l_segment_index NUMBER;
1118 l_bld_blk_info_type_id NUMBER;
1119 l_segment_count NUMBER;
1120 l_count NUMBER;
1121 l_sequence_code fnd_descr_flex_contexts.descriptive_flex_context_code%TYPE DEFAULT NULL;
1122 l_sequence_no NUMBER;
1123 l_message VARCHAR2(100);
1124 l_preserve BOOLEAN;
1125 BEGIN
1126 --
1127 -- Default is not to preserve the
1128 -- flexfield definitions. By default, we will replace the
1129 -- definition.
1130 --
1131 if(p_preserve is null) then
1132 l_preserve := false;
1133 else
1134 l_preserve := true;
1135 end if;
1136 --
1137 -- Tell the API we're seeding data
1138 --
1139 FND_FLEX_DSC_API.set_session_mode('seed_data');
1140 --
1141 -- First fetch the flexfield
1142 --
1143 FND_DFLEX.get_flexfield
1144 (appl_short_name => p_appl_short_name,
1145 flexfield_name => p_flexfield_name,
1146 flexfield => l_flex,
1147 flexinfo => l_flex_info);
1148 --
1149 -- Next get the contexts
1150 --
1151 FND_DFLEX.get_contexts
1152 (flexfield => l_flex,
1153 contexts => l_contexts
1154 );
1155 --
1156 -- OK, if we have more than just the
1157 -- global context have to do things one
1158 -- way, if only the global context
1159 -- then have to do things slightly differently.
1160 --
1161
1162 if (check_contexts(l_contexts)) then
1163 l_global_context := FND_DFLEX.make_context
1164 (flexfield => l_flex,
1165 context_code =>'Global Data Elements'
1166 );
1167 --
1168 -- For each context, create the information type
1169 -- and the usage, and create a context of the same
1170 -- name against OTC Information types
1171 --
1172 l_context_index := l_contexts.context_code.first;
1173
1174 LOOP
1175 EXIT WHEN not l_contexts.context_code.exists(l_context_index);
1176 --
1177 -- Must avoid create a global data elements context
1178 -- those segments are handled a different way
1179 --
1180 if (
1181 (l_contexts.context_code(l_context_index) <> 'Global Data Elements')
1182 AND
1183 (hxc_otl_info_type_helper.build_otl_contexts
1184 (p_otc_appl_short_name,
1185 p_otc_flex_name,
1186 p_context_prefix,
1187 l_flex,
1188 l_contexts,
1189 l_context_index,
1190 l_global_context,
1191 l_preserve
1192 )
1193 )
1194 )then
1195 -- Create the context against OTC Information types but
1196 -- delete it first to make the process rerunable not as
1197 -- performant as leaving it there, but this way we are
1198 -- ensured to keep the OTC information in step with the
1199 -- descriptive flex information If the length of the
1200 -- Expenditure Items Context Code is less than or equal
1201 -- to 17 then the usual process , i.e the context code
1202 -- will remain like 'PAEXPITDFF''
1203 IF (LENGTH(l_contexts.context_code(l_context_index)) <=
1204 30-((LENGTH(p_context_prefix)+3))) THEN
1205 -- Follow the existing logic
1206
1207 if FND_FLEX_DSC_API.context_exists
1208 (P_APPL_SHORT_NAME => p_otc_appl_short_name,
1209 P_FLEXFIELD_NAME => p_otc_flex_name,
1210 P_CONTEXT_CODE => substr(p_context_prefix||' - '||l_contexts.context_code(l_context_index),1,30)
1211 ) then
1212 FND_FLEX_DSC_API.delete_context
1213 (APPL_SHORT_NAME => p_otc_appl_short_name,
1214 FLEXFIELD_NAME => p_otc_flex_name,
1215 CONTEXT => substr(p_context_prefix||' - '||l_contexts.context_code(l_context_index),1,30));
1216
1217 end if; -- Does this context exist?
1218
1219 FND_FLEX_DSC_API.create_context
1220 (APPL_SHORT_NAME => p_otc_appl_short_name,
1221 FLEXFIELD_NAME => p_otc_flex_name,
1222 CONTEXT_CODE => substr(p_context_prefix||' - '||l_contexts.context_code(l_context_index),1,30),
1223 CONTEXT_NAME => l_contexts.context_name(l_context_index),
1224 DESCRIPTION => l_contexts.context_description(l_context_index),
1225 ENABLED => 'N',
1226 GLOBAL_FLAG => 'N'
1227 );
1228 --
1229 -- Create the Building block information type for this context
1230 --
1231 l_bld_blk_info_type_id := create_bld_blk_info_type
1232 (p_appl_short_name => p_otc_appl_short_name,
1233 p_flexfield_name => p_otc_flex_name,
1234 p_legislation_code => NULL,
1235 p_bld_blk_info_type => substr(p_context_prefix||' - '||l_contexts.context_code(l_context_index),1,30),
1236 p_category => p_flexfield_name
1237 );
1238 --
1239 -- Now, since we're using the OTC information types flexfield
1240 -- as a general flexfield, we can't simply add the global data
1241 -- segments within that context. So, we must add them to
1242 -- each context generated with the flexfield we're currently
1243 -- duplicating. This is irritating, but the only way to do it
1244 -- currently.
1245 --
1246 create_segments
1247 (p_otc_appl_short_name => p_otc_appl_short_name,
1248 p_context => l_global_context,
1249 p_otc_flex_name => p_otc_flex_name,
1250 p_context_code => substr(p_context_prefix||' - '||l_contexts.context_code(l_context_index),1,30)
1251 );
1252 --
1253 -- Next get the segments for this context, and create them against
1254 -- OTC context, AOL require us to "make" the context first
1255 --
1256 l_current_context := FND_DFLEX.make_context
1257 (flexfield => l_flex,
1258 context_code =>l_contexts.context_code(l_context_index)
1259 );
1260
1261 create_segments
1262 (p_otc_appl_short_name => p_otc_appl_short_name,
1263 p_context => l_current_context,
1264 p_otc_flex_name => p_otc_flex_name,
1265 p_context_code => substr(p_context_prefix||' - '||l_contexts.context_code(l_context_index),1,30)
1266 );
1267
1268 ELSE
1269 -- If the length of the Expenditure Item Context Code
1270 -- is greater than 17 then, context should be like
1271 -- PAEXPITDFFC - 14235252 and the Context Name will
1272 -- hold the usual context code like PAEXPITDFF -
1273 -- Painting&Decorating If the context code of
1274 -- Expenditure Items is already greater than 17 and
1275 -- the corresponding context code already exist in
1276 -- the OTL Information Types, then do nothing else
1277 -- create the context code like PAEXPITDFFC -
1278 -- 14235252
1279
1280 IF (not fnd_flex_dsc_api.context_exists
1281 (p_appl_short_name=> p_otc_appl_short_name,
1282 p_flexfield_name=> p_otc_flex_name,
1283 p_context_code=>substr( p_context_prefix || ' - '|| l_contexts.context_code(l_context_index), 1, 30))
1284 )THEN
1285
1286 l_sequence_code := NULL;
1287 -- Check out if the code already exist , say, if
1288 --the context code is 'Painting&Decorating' then
1289 --if PAEXPITDFF - Painting&Decorating exist as
1290 --the context name, then the sequence code will
1291 --be populated with PAEXPITDFFC - 14235252
1292
1293 if not (hxc_deposit_wrapper_utilities.get_dupdff_code
1294 (p_context_prefix||' - '|| l_contexts.context_code(l_context_index))=
1295 p_context_prefix||' - '|| l_contexts.context_code(l_context_index)
1296 ) then
1297
1298 l_sequence_code := hxc_deposit_wrapper_utilities.get_dupdff_code
1299 (p_context_prefix||' - '|| l_contexts.context_code(l_context_index)
1300 );
1301 end if;
1302
1303 if (l_sequence_code is not null) then
1304 -- The context code exist and so it requires
1305 -- to be deleted and once again created
1306 fnd_flex_dsc_api.delete_context
1307 (appl_short_name=> p_otc_appl_short_name,
1308 flexfield_name=> p_otc_flex_name,
1309 CONTEXT=> l_sequence_code
1310 );
1311 else
1312 -- If the context code does not exist for the
1313 --PAEXPITDFF - Painting&Decorating, then obtain
1314 --context code needs to be created like
1315 --'PAEXPITDFFC - Sequence Number, where
1316 --Sequence Number is the maximum
1317 OPEN get_max_sequence;
1318 FETCH get_max_sequence INTO l_sequence_no;
1319
1320 IF l_sequence_no IS NULL THEN
1321 -- If the context like PAEXPITDFFC is
1322 --created for the first time , then
1323 --starting the sequenc from 1
1324 l_sequence_code := p_context_prefix || 'C - 1';
1325 ELSE
1326 -- Sequence Number is populated with the maximum sequence +1
1327 l_sequence_no := l_sequence_no + 1;
1328 l_sequence_code := p_context_prefix || 'C - ' || l_sequence_no;
1329 END IF;
1330 CLOSE get_max_sequence;
1331 end if;
1332 hr_utility.set_message(809,'HXC_DFF_SYSTEM_CONTEXT');
1333
1334
1335 -- Description of the messages needs to be
1336 --'System context, do not modify.Context Name.
1337 --Description'. So keeping in view to avoid
1338 --translation problems, a message is created
1339 --with the above name and the text as 'System
1340 --context, do not modify''
1341 l_message := hr_utility.get_message;
1342 -- Creates the context
1343 fnd_flex_dsc_api.create_context
1344 (appl_short_name=> p_otc_appl_short_name,
1345 flexfield_name=> p_otc_flex_name,
1346 context_code=> l_sequence_code,
1347 context_name=> p_context_prefix
1348 || ' - '
1349 || l_contexts.context_code( l_context_index),
1350 description=> substr(l_message
1351 || l_contexts.context_name(l_context_index)
1352 || '.'
1353 || l_contexts.context_description( l_context_index ),1,240),
1354 enabled=> 'N',
1355 global_flag=> 'N'
1356 );
1357
1358 --Create the building block information
1359 l_bld_blk_info_type_id :=
1360 create_bld_blk_info_type
1361 (p_appl_short_name=> p_otc_appl_short_name,
1362 p_flexfield_name=> p_otc_flex_name,
1363 p_legislation_code=> NULL,
1364 p_bld_blk_info_type=> l_sequence_code,
1365 p_category=> p_flexfield_name
1366 );
1367 -- Create the segments
1368 create_segments
1369 (p_otc_appl_short_name=> p_otc_appl_short_name,
1370 p_context=> l_global_context,
1371 p_otc_flex_name=> p_otc_flex_name,
1372 p_context_code=> l_sequence_code
1373 );
1374
1375 l_current_context :=
1376 fnd_dflex.make_context
1377 (flexfield=> l_flex,
1378 context_code=> l_contexts.context_code(l_context_index)
1379 );
1380
1381 create_segments
1382 (p_otc_appl_short_name=> p_otc_appl_short_name,
1383 p_context=> l_current_context,
1384 p_otc_flex_name=> p_otc_flex_name,
1385 p_context_code=> l_sequence_code
1386 );
1387
1388 end if;
1389 END IF;
1390 end if; -- avoiding the global data elements.
1391 l_context_index := l_contexts.context_code.next(l_context_index);
1392 END LOOP; -- Contexts loop
1393 else
1394 --
1395 -- In this case, we've only got the global contexts
1396 -- just set up a p_context_prefix - global context.
1397 -- No loop needed, since there is only one context.
1398
1399 l_global_context := FND_DFLEX.make_context
1400 (flexfield => l_flex,
1401 context_code =>'Global Data Elements'
1402 );
1403
1404 if FND_FLEX_DSC_API.context_exists
1405 (P_APPL_SHORT_NAME => p_otc_appl_short_name,
1406 P_FLEXFIELD_NAME => p_otc_flex_name,
1407 P_CONTEXT_CODE => substr(p_context_prefix||' - GLOBAL',1,30)
1408 ) then
1409 FND_FLEX_DSC_API.delete_context
1410 (APPL_SHORT_NAME => p_otc_appl_short_name,
1411 FLEXFIELD_NAME => p_otc_flex_name,
1412 CONTEXT => substr(p_context_prefix||' - GLOBAL',1,30)
1413 );
1414
1415 end if; -- Does this context exist?
1416
1417 FND_FLEX_DSC_API.create_context
1418 (APPL_SHORT_NAME => p_otc_appl_short_name,
1419 FLEXFIELD_NAME => p_otc_flex_name,
1420 CONTEXT_CODE => substr(p_context_prefix||' - GLOBAL',1,30),
1421 CONTEXT_NAME => substr(p_context_prefix||' - GLOBAL',1,30),
1422 DESCRIPTION => substr(p_context_prefix||' - GLOBAL',1,30)||' auto generated by the magic process',
1423 ENABLED => 'N',
1424 GLOBAL_FLAG => 'N'
1425 );
1426 --
1427 -- Create the Building block information type for this context
1428 --
1429 l_bld_blk_info_type_id := create_bld_blk_info_type
1430 (p_appl_short_name => p_otc_appl_short_name,
1431 p_flexfield_name => p_otc_flex_name,
1432 p_legislation_code => NULL,
1433 p_bld_blk_info_type => substr(p_context_prefix||' - GLOBAL',1,30),
1434 p_category => p_flexfield_name
1435 );
1436 --
1437 -- Finally create the global segments in the special
1438 -- OTL context created for these globals.
1439 --
1440 create_segments
1441 (p_otc_appl_short_name => p_otc_appl_short_name,
1442 p_context => l_global_context,
1443 p_otc_flex_name => p_otc_flex_name,
1444 p_context_code => substr(p_context_prefix||' - GLOBAL',1,30)
1445 );
1446 end if;
1447
1448 END duplicate_desc_flex;
1449
1450 procedure run_process(
1451 p_errmsg OUT NOCOPY VARCHAR2
1452 ,p_errcode OUT NOCOPY NUMBER
1453 ,p_undo in VARCHAR2 default 'N'
1454 ,p_element_set_id in NUMBER default null
1455 ,p_effective_date in VARCHAR2
1456 ,p_generate_cost in VARCHAR2 default 'Y'
1457 ,p_generate_group in VARCHAR2 default 'Y'
1458 ,p_generate_job in VARCHAR2 default 'Y'
1459 ,p_generate_pos in VARCHAR2 default 'Y'
1460 ,p_generate_prj in VARCHAR2 default 'Y'
1461 ,p_business_group_id in VARCHAR2) is
1462
1463 --Change the parameters to create_alias_definitions.
1464 cursor c_alias_type(p_alias_context_code varchar2) is
1465 select hat.alias_type_id
1466 from hxc_alias_types hat
1467 where reference_object = p_alias_context_code;
1468
1469 cursor c_prompt (p_alias_context_code varchar2) is
1470 select fdfc.descriptive_flex_context_name
1471 from fnd_descr_flex_contexts_vl fdfc
1472 where application_id = 809
1473 and DESCRIPTIVE_FLEXFIELD_NAME = 'OTC Aliases'
1474 and fdfc.descriptive_flex_context_code = p_alias_context_code;
1475
1476 cursor c_elements(p_element_set_id in number, p_effective_date in date) is
1477 select pet.element_name, pet.element_type_id, pet.reporting_name
1478 from pay_element_types_f pet,
1479 pay_element_type_rules per
1480 where per.element_set_id = p_element_set_id
1481 and per.include_or_exclude = 'I'
1482 and per.element_type_id = pet.element_type_id
1483 and multiple_entries_allowed_flag = 'Y'
1484 and p_effective_date between effective_start_date and effective_end_date;
1485
1486 cursor csr_chk_an_exists(p_an_name varchar2,
1487 p_bg_id number) is
1488 select 'Y', alias_definition_id
1489 from hxc_alias_definitions
1490 where alias_definition_name = p_an_name
1491 and business_group_id = p_bg_id;
1492
1493 cursor csr_value_exists(p_ele_type_id number,
1494 p_an_id number) is
1495 -- p_effective_date date) is
1496 select 'Y'
1497 from hxc_alias_values
1498 where alias_definition_id = p_an_id
1499 and attribute1 = to_char(p_ele_type_id);
1500 -- and p_effective_date between date_from and date_to;
1501
1502 cursor c_ipvs(p_element_type_id in number, p_effective_date in date) is
1503 select display_sequence, name, input_value_id, mandatory_flag
1504 from pay_input_values_f
1505 where element_type_id = p_element_type_id
1506 and p_effective_date between effective_start_date and effective_end_date
1507 order by display_sequence, name;
1508 /*
1509 Bug no : 3353252
1510 This cursor csr_chk_repname_exists will check for the duplicate reporting name in an alias_defintion
1511 and return the number of same reporting names available.
1512 */
1513
1514 cursor csr_chk_repname_exists(p_an_id number,
1515 p_ele_rep_name varchar2) is
1516 select count(*)
1517 from hxc_alias_values
1518 where alias_definition_id = p_an_id
1519 AND (alias_value_name like '% ~ '||p_ele_rep_name or alias_value_name = p_ele_rep_name);
1520
1521 l_attr_prompt pay_input_values_f.name%TYPE;
1522 l_temp_segment_choice NUMBER :=0;
1523
1524 l_appl_short_name VARCHAR2(3) := 'HXC';
1525 l_flexfield_name VARCHAR2(30) := 'OTC Information Types';
1526 l_segment_count NUMBER := 0;
1527 l_segment_choice NUMBER :=0;
1528
1529 l_element_count NUMBER := 0;
1530 l_key_flex_structure_count NUMBER :=0;
1531
1532 l_max_input_value_count NUMBER:=0;
1533 l_max_segment_count NUMBER :=0;
1534
1535 l_key_app VARCHAR2(30) := 'PAY';
1536 l_key_flex_code VARCHAR2(30) := 'COST';
1537
1538 l_key_flex FND_FLEX_KEY_API.FLEXFIELD_TYPE;
1539
1540 l_key_structure_list FND_FLEX_KEY_API.STRUCTURE_LIST;
1541 l_key_segment_list FND_FLEX_KEY_API.SEGMENT_LIST;
1542
1543 l_key_structure FND_FLEX_KEY_API.STRUCTURE_TYPE;
1544 l_key_segment FND_FLEX_KEY_API.SEGMENT_TYPE;
1545
1546 l_structure_count NUMBER;
1547
1548 l_structures NUMBER;
1549 l_segments NUMBER;
1550
1551 l_building_block_info_id NUMBER;
1552
1553 i NUMBER;
1554
1555 l_element_set_name VARCHAR2(80);
1556 l_an_context VARCHAR2(30) := 'PAYROLL_ELEMENTS';
1557 l_an_enabled VARCHAR2(80);
1558 l_an_disabled VARCHAR2(80);
1559 l_an_en_exists VARCHAR2(1) := 'N';
1560 l_an_dis_exists VARCHAR2(1) := 'N';
1561 l_an_en_id NUMBER;
1562 l_an_en_ovn NUMBER;
1563 l_an_dis_id NUMBER;
1564 l_an_dis_ovn NUMBER;
1565 l_en_value_exists VARCHAR2(1) := 'N';
1566 l_dis_value_exists VARCHAR2(1) := 'N';
1567 l_av_id NUMBER;
1568 l_av_ovn NUMBER;
1569 l_bg_id NUMBER;
1570 -- Change Parameters to create_alias_definitions
1571 l_alias_type_id NUMBER;
1572 l_prompt varchar2(240);
1573
1574 l_generate BOOLEAN;
1575
1576 l_effective_date DATE;
1577 l_bgp_id FND_PROFILE_OPTION_VALUES.PROFILE_OPTION_VALUE%TYPE;
1578 l_bgp_def BOOLEAN;
1579
1580 l_ret_bg_id VARCHAR2(2000);
1581
1582 l_new_en_repname PAY_ELEMENT_TYPES_F.REPORTING_NAME%TYPE;
1583 l_new_dis_repname PAY_ELEMENT_TYPES_F.REPORTING_NAME%TYPE;
1584
1585 l_alt_name_change VARCHAR2(1) := 'N';
1586 l_name VARCHAR2(1000);
1587
1588 l_warning_string VARCHAR2(1000):='';
1589 l_warning_string1 VARCHAR2(5000):='';
1590 l_temp_msg VARCHAR2(5000):='';
1591 c_warning CONSTANT NUMBER := 1;
1592 l_cnt_repname NUMBER(10):=0;
1593
1594 l_disp_count NUMBER := 0; -- Bug 5919417
1595
1596 BEGIN
1597
1598 /* Bug fix for 3353252
1599 Initialize the string for the warning messages which will be printed if there is any duplication in the alternate names*/
1600 hr_utility.set_message (809, 'HXC_GEN_FLEX_MOD_ALT_WAR_MSG');
1601 l_name:=HR_UTILITY.GET_MESSAGE;
1602 /* end of fix for 3353252*/
1603
1604 --
1605 -- Convert the entered date into real date format
1606 --
1607
1608 l_effective_date := FND_DATE.CANONICAL_TO_DATE(p_effective_date);
1609
1610 --
1611 -- Set the business group id if passed in
1612 --
1613
1614 if (p_business_group_id is not null) then
1615
1616 FND_PROFILE.PUT('PER_BUSINESS_GROUP_ID',p_business_group_id);
1617
1618 else
1619
1620 --
1621 -- Is there a problem with fnd_profile caching?
1622 --
1623 FND_PROFILE.GET_SPECIFIC('PER_BUSINESS_GROUP_ID',null,null,null,l_bgp_id,l_bgp_def);
1624 if (l_bgp_def) then
1625 FND_PROFILE.PUT('PER_BUSINESS_GROUP_ID',l_bg_id);
1626 else
1627 FND_PROFILE.PUT('PER_BUSINESS_GROUP_ID',null);
1628 end if;
1629
1630 end if;
1631
1632 --
1633 -- Check the undo flag, if set, then attempt to undo
1634 -- the flex and mapping component creation
1635 --
1636
1637 if p_undo = 'Y' then
1638 undo(
1639 p_appl_short_name=> l_appl_short_name
1640 ,p_flexfield_name => l_flexfield_name
1641 ,p_element_set_id => p_element_set_id
1642 ,p_effective_date => l_effective_date
1643 ,p_include_cost => p_generate_cost
1644 ,p_include_group => p_generate_group
1645 ,p_include_job => p_generate_job
1646 ,p_include_pos => p_generate_pos
1647 ,p_include_prj => p_generate_prj);
1648
1649 else
1650
1651 --
1652 -- Tell the flex field API we're seeding data
1653 --
1654
1655 FND_FLEX_DSC_API.set_session_mode('seed_data');
1656 -- Create alias definitions (Enabled and Disabled) and for the element set,
1657 -- if it does not already exist.
1658 --
1659 --
1660 -- Create the dummy element context and mappings
1661 --
1662 create_dummy_context(
1663 p_appl_short_name => l_appl_short_name,
1664 p_flexfield_name => l_flexfield_name,
1665 p_context_name => 'ELEMENT',
1666 p_segment_name_prefix=>'InputValue',
1667 p_max_segments => 15
1668 );
1669
1670 IF p_element_set_id IS NOT NULL THEN
1671 --
1672 -- Get the element set name.
1673 --
1674 SELECT element_set_name, business_group_id
1675 INTO l_element_set_name, l_bg_id
1676 FROM pay_element_sets
1677 WHERE element_set_id = p_element_set_id;
1678
1679 --
1680 l_an_enabled := rtrim(substr(l_element_set_name, 1, 70)) || ' - Enabled';
1681 l_an_disabled := rtrim(substr(l_element_set_name, 1, 69)) || ' - Disabled';
1682 /* Bug fix for 3353252
1683 The following code is used to get the alias_definiton_name which will be used in warning message
1684 if there is an alternate name change by the application.*/
1685 FND_MESSAGE.SET_NAME('HXC', 'HXC_GEN_FLEX_MOD_ALS_DEF_NAME');
1686 FND_MESSAGE.SET_TOKEN('ALIAS_DEFINITION_NAME',l_an_enabled);
1687 l_temp_msg:=FND_MESSAGE.GET();
1688 l_warning_string:=l_warning_string || l_temp_msg || '
1689 ';
1690 FND_MESSAGE.SET_NAME ('HXC', 'HXC_GEN_FLEX_MOD_ALS_DEF_NAME');
1691 FND_MESSAGE.SET_TOKEN('ALIAS_DEFINITION_NAME',l_an_disabled);
1692 l_temp_msg:=FND_MESSAGE.GET();
1693 l_warning_string1:=l_warning_string1 || l_temp_msg || '
1694 ';
1695 /* end of fix for 3353252*/
1696 --
1697 -- Check to see whether they already exist.
1698 --
1699 open csr_chk_an_exists(l_an_enabled, l_bg_id);
1700 fetch csr_chk_an_exists into l_an_en_exists, l_an_en_id;
1701 IF csr_chk_an_exists%NOTFOUND THEN
1702 l_an_en_exists := 'N';
1703 END IF;
1704 close csr_chk_an_exists;
1705 --
1706 open csr_chk_an_exists(l_an_disabled, l_bg_id);
1707 fetch csr_chk_an_exists into l_an_dis_exists, l_an_dis_id;
1708 IF csr_chk_an_exists%NOTFOUND THEN
1709 l_an_dis_exists := 'N';
1710 END IF;
1711 close csr_chk_an_exists;
1712 --
1713 -- l_ret_bg_id := FND_PROFILE.value('PER_BUSINESS_GROUP_ID');
1714 -- dbms_output.put_line('Profile business group id is:'||l_ret_bg_id);
1715 --
1716
1717 -- change parameters to create_alias_definitions
1718 open c_alias_type(l_an_context);
1719 fetch c_alias_type into l_alias_type_id;
1720 if (l_alias_type_id is null) then
1721 hr_utility.set_message(809,'HXC_SEED_ALT_NOT_FOUND');
1722 hr_utility.raise_error;
1723 end if;
1724 close c_alias_type;
1725
1726 open c_prompt(l_an_context);
1727 fetch c_prompt into l_prompt;
1728 close c_prompt;
1729
1730
1731 IF l_an_en_exists = 'N' THEN
1732 hxc_alias_definitions_api.create_alias_definition
1733 (p_alias_definition_id => l_an_en_id
1734 ,p_alias_definition_name => l_an_enabled
1735 ,p_business_group_id => l_bg_id
1736 ,p_legislation_code => NULL
1737 ,p_description => 'Created for renaming elements'
1738 ,p_prompt => l_prompt
1739 ,p_timecard_field => 'ElementComponent'
1740 ,p_object_version_number => l_an_en_ovn
1741 ,p_alias_type_id => l_alias_type_id
1742 );
1743 END IF;
1744 --
1745 --
1746 IF l_an_dis_exists = 'N' THEN
1747 hxc_alias_definitions_api.create_alias_definition
1748 (p_alias_definition_id => l_an_dis_id
1749 ,p_alias_definition_name => l_an_disabled
1750 ,p_business_group_id => l_bg_id
1751 ,p_legislation_code => NULL
1752 ,p_description => 'Created for renaming elements'
1753 ,p_prompt => l_prompt
1754 ,p_timecard_field => 'ElementComponent'
1755 ,p_object_version_number => l_an_dis_ovn
1756 ,p_alias_type_id => l_alias_type_id
1757 );
1758 END IF;
1759 --
1760 END IF;
1761 --
1762
1763 /*
1764 Open the element type cursor, and fetch the first element
1765 */
1766
1767
1768 for ele_rec in c_elements(p_element_set_id, l_effective_date) LOOP
1769 -- Check if this element already exists in the values for
1770 -- the two alias definitions.
1771 --
1772 open csr_value_exists(ele_rec.element_type_id, l_an_en_id);
1773 fetch csr_value_exists into l_en_value_exists;
1774 IF csr_value_exists%NOTFOUND THEN
1775 l_en_value_exists := 'N';
1776 END IF;
1777 close csr_value_exists;
1778
1779 --
1780 open csr_value_exists(ele_rec.element_type_id, l_an_dis_id);
1781 fetch csr_value_exists into l_dis_value_exists;
1782 IF csr_value_exists%NOTFOUND THEN
1783 l_dis_value_exists := 'N';
1784 END IF;
1785 close csr_value_exists;
1786
1787 --
1788 --
1789 IF l_en_value_exists = 'N' THEN
1790 l_cnt_repname:=0;
1791 open csr_chk_repname_exists(l_an_en_id,nvl(ele_rec.reporting_name,ele_rec.element_name));
1792 fetch csr_chk_repname_exists into l_cnt_repname;
1793 IF l_cnt_repname=0 THEN
1794 hxc_alias_values_api.create_alias_value
1795 (p_alias_value_id => l_av_id
1796 ,p_alias_value_name => nvl(ele_rec.reporting_name,
1797 ele_rec.element_name)
1798 ,p_date_from => hr_general.start_of_time
1799 ,p_date_to => NULL
1800 ,p_alias_definition_id => l_an_en_id
1801 ,p_enabled_flag => 'Y'
1802 ,p_attribute_category => l_an_context
1803 ,p_attribute1 => ele_rec.element_type_id
1804 ,p_object_version_number => l_av_ovn);
1805 ELSE
1806 /*Bug fix 3353252 Modifying the alternate name to be unique by prefixing the number */
1807 l_new_en_repname:=l_cnt_repname ||' ~ ' || nvl(ele_rec.reporting_name,ele_rec.element_name);
1808 hxc_alias_values_api.create_alias_value
1809 (p_alias_value_id => l_av_id
1810 ,p_alias_value_name => l_new_en_repname
1811 ,p_date_from => hr_general.start_of_time
1812 ,p_date_to => NULL
1813 ,p_alias_definition_id => l_an_en_id
1814 ,p_enabled_flag => 'Y'
1815 ,p_attribute_category => l_an_context
1816 ,p_attribute1 => ele_rec.element_type_id
1817 ,p_object_version_number => l_av_ovn);
1818 FND_MESSAGE.SET_NAME ('HXC', 'HXC_GEN_FLEX_MOD_ALT_NAME');
1819 FND_MESSAGE.SET_TOKEN('REP_NAME',nvl(ele_rec.reporting_name,ele_rec.element_name));
1820 FND_MESSAGE.SET_TOKEN('NEW_REP_NAME',l_new_en_repname);
1821 l_temp_msg:=FND_MESSAGE.GET();
1822 l_warning_string:=l_warning_string || l_temp_msg || '
1823 ';
1824 l_alt_name_change:='Y';
1825 /*end of fix for 3353252*/
1826 END IF;
1827 close csr_chk_repname_exists;
1828 END IF;
1829 --
1830 IF l_dis_value_exists = 'N' THEN
1831 l_cnt_repname:=0;
1832 open csr_chk_repname_exists(l_an_dis_id,nvl(ele_rec.reporting_name,ele_rec.element_name));
1833 fetch csr_chk_repname_exists into l_cnt_repname;
1834 IF l_cnt_repname=0 THEN
1835 hxc_alias_values_api.create_alias_value
1836 (p_alias_value_id => l_av_id
1837 ,p_alias_value_name => nvl(ele_rec.reporting_name,
1838 ele_rec.element_name)
1839 ,p_date_from => hr_general.start_of_time
1840 ,p_date_to => NULL
1841 ,p_alias_definition_id => l_an_dis_id
1842 ,p_enabled_flag => 'N'
1843 ,p_attribute_category => l_an_context
1844 ,p_attribute1 => ele_rec.element_type_id
1845 ,p_object_version_number => l_av_ovn);
1846 ELSE
1847 /*Bug fix 3353252 Modifying the alternate name to be unique by prefixing the number */
1848 l_new_dis_repname:=l_cnt_repname ||' ~ ' || nvl(ele_rec.reporting_name,ele_rec.element_name);
1849 hxc_alias_values_api.create_alias_value
1850 (p_alias_value_id => l_av_id
1851 ,p_alias_value_name => l_new_dis_repname
1852 ,p_date_from => hr_general.start_of_time
1853 ,p_date_to => NULL
1854 ,p_alias_definition_id => l_an_dis_id
1855 ,p_enabled_flag => 'N'
1856 ,p_attribute_category => l_an_context
1857 ,p_attribute1 => ele_rec.element_type_id
1858 ,p_object_version_number => l_av_ovn);
1859 l_alt_name_change:='Y';
1860 FND_MESSAGE.SET_NAME ('HXC', 'HXC_GEN_FLEX_MOD_ALT_NAME');
1861 FND_MESSAGE.SET_TOKEN('REP_NAME',nvl(ele_rec.reporting_name,ele_rec.element_name));
1862 FND_MESSAGE.SET_TOKEN('NEW_REP_NAME',l_new_dis_repname);
1863 l_temp_msg:=FND_MESSAGE.GET();
1864 l_warning_string1:=l_warning_string1 || l_temp_msg || '
1865 ';
1866 /*end of fix for 3353252*/
1867 END IF;
1868 close csr_chk_repname_exists;
1869 END IF;
1870 --
1871 --
1872 if FND_FLEX_DSC_API.context_exists(
1873 P_APPL_SHORT_NAME => l_appl_short_name,
1874 P_FLEXFIELD_NAME => l_flexfield_name,
1875 P_CONTEXT_CODE => 'ELEMENT - '|| ele_rec.element_type_id
1876 ) then
1877 FND_FLEX_DSC_API.delete_context(
1878 APPL_SHORT_NAME => l_appl_short_name,
1879 FLEXFIELD_NAME => l_flexfield_name,
1880 CONTEXT => 'ELEMENT - '|| ele_rec.element_type_id);
1881
1882 end if; -- Does this element context exist?
1883
1884 FND_FLEX_DSC_API.create_context(
1885 APPL_SHORT_NAME => l_appl_short_name,
1886 FLEXFIELD_NAME => l_flexfield_name,
1887 CONTEXT_CODE => 'ELEMENT - '|| ele_rec.element_type_id,
1888 CONTEXT_NAME => ele_rec.element_name,
1889 DESCRIPTION => 'Auto generated HXC element context',
1890 ENABLED => 'Y',
1891 GLOBAL_FLAG => 'N');
1892
1893 l_element_count := l_element_count +1;
1894
1895
1896 --
1897 -- Create the Building block information type for this context
1898 --
1899 l_building_block_info_id := create_bld_blk_info_type(
1900 p_appl_short_name => l_appl_short_name,
1901 p_flexfield_name => l_flexfield_name,
1902 p_legislation_code => NULL,
1903 p_bld_blk_info_type => 'ELEMENT - '|| ele_rec.element_type_id,
1904 p_category => 'ELEMENT');
1905
1906
1907 /* Bug 5919417 Start */
1908
1909 --
1910 -- Find the number of input values with display_sequence 12,13,14 or 15 before generating segments
1911 --
1912
1913 for seq_rec in c_ipvs(ele_rec.element_type_id, l_effective_date)
1914 LOOP
1915 if((seq_rec.display_sequence > 11) AND (seq_rec.display_sequence < 16) AND (seq_rec.mandatory_flag <> 'X')) then
1916 l_disp_count := l_disp_count + 1;
1917 end if;
1918 END LOOP;
1919
1920 /* Bug 5919417 End */
1921
1922
1923
1924
1925 --
1926 -- Now fetch each input value and generate segments, if we are creating
1927 --
1928 l_segment_count := 0;
1929 for ipv_rec in c_ipvs(ele_rec.element_type_id, l_effective_date) LOOP
1930
1931
1932 if((ipv_rec.display_sequence < 12) OR (ipv_rec.display_sequence > 15)) then
1933
1934 l_segment_count := l_segment_count +1;
1935
1936 end if;
1937
1938 if (ipv_rec.mandatory_flag <> 'X') then
1939
1940 if((ipv_rec.display_sequence > 11) AND (ipv_rec.display_sequence < 16)) then
1941
1942 l_segment_choice := ipv_rec.display_sequence;
1943
1944 else
1945
1946 -- l_segment_count := l_segment_count + 1; /* Bug 5919417 */
1947
1948 l_segment_choice := l_segment_count;
1949
1950 end if;
1951
1952
1953 /* Bug 5919417 Start */
1954
1955 if (l_segment_count = 11) then
1956 l_segment_count := l_segment_count + l_disp_count;
1957 end if;
1958
1959 /* Bug 5919417 Start */
1960
1961
1962 FND_FLEX_DSC_API.create_segment(
1963 APPL_SHORT_NAME => l_appl_short_name,
1964 FLEXFIELD_NAME => l_flexfield_name,
1965 CONTEXT_NAME => 'ELEMENT - '||ele_rec.element_type_id,
1966 NAME => ipv_rec.name,
1967 COLUMN => 'ATTRIBUTE'||to_char(l_segment_choice),
1968 DESCRIPTION => 'Auto generated HXC element input value context segment',
1969 SEQUENCE_NUMBER => l_segment_choice,
1970 ENABLED => 'N',
1971 DISPLAYED => 'N',
1972 VALUE_SET => NULL,
1973 DEFAULT_TYPE => NULL,
1974 DEFAULT_VALUE => NULL,
1975 REQUIRED => 'N',
1976 SECURITY_ENABLED => 'N',
1977 DISPLAY_SIZE => 30,
1978 DESCRIPTION_SIZE => 50,
1979 CONCATENATED_DESCRIPTION_SIZE => 10,
1980 LIST_OF_VALUES_PROMPT => ipv_rec.name,
1981 WINDOW_PROMPT => ipv_rec.name,
1982 RANGE => NULL,
1983 SRW_PARAMETER => NULL);
1984
1985 if(ipv_rec.name = 'Jurisdiction') then
1986
1987 FOR i in 1..4 LOOP
1988
1989 if (i=1) then
1990 l_temp_segment_choice := 27;
1991 l_attr_prompt := 'State';
1992 elsif (i=2) then
1993 l_temp_segment_choice := 28;
1994 l_attr_prompt := 'County';
1995 elsif (i=3) then
1996 l_temp_segment_choice := 29;
1997 l_attr_prompt := 'City';
1998 elsif (i=4) then
1999 l_temp_segment_choice := 30;
2000 l_attr_prompt := 'Zipcode';
2001 end if;
2002
2003 FND_FLEX_DSC_API.create_segment(
2004 APPL_SHORT_NAME => l_appl_short_name,
2005 FLEXFIELD_NAME => l_flexfield_name,
2006 CONTEXT_NAME => 'ELEMENT - '||ele_rec.element_type_id,
2007 NAME => l_attr_prompt,
2008 COLUMN => 'ATTRIBUTE'||to_char(l_temp_segment_choice),
2009 DESCRIPTION => 'Auto generated HXC element input value context segment',
2010 SEQUENCE_NUMBER => l_temp_segment_choice,
2011 ENABLED => 'N',
2012 DISPLAYED => 'N',
2013 VALUE_SET => NULL,
2014 DEFAULT_TYPE => NULL,
2015 DEFAULT_VALUE => NULL,
2016 REQUIRED => 'N',
2017 SECURITY_ENABLED => 'N',
2018 DISPLAY_SIZE => 30,
2019 DESCRIPTION_SIZE => 50,
2020 CONCATENATED_DESCRIPTION_SIZE => 10,
2021 LIST_OF_VALUES_PROMPT => l_attr_prompt,
2022 WINDOW_PROMPT => l_attr_prompt,
2023 RANGE => NULL,
2024 SRW_PARAMETER => NULL);
2025 end loop;
2026 end if;
2027 end if; -- is this a user enterable segment
2028
2029 end LOOP; -- Input value loop
2030
2031 if l_max_input_value_count < l_segment_count then
2032 l_max_input_value_count := l_segment_count;
2033 end if;
2034
2035 end LOOP; -- Element loop
2036
2037
2038 /*Bug fix for 3353252, End the concurrent request in warning and Throw a warning message if the alternate names are
2039 modified.Here we are concatenating all the warning messages.*/
2040 IF l_alt_name_change='Y' then
2041 -- Set retcode to 1, indicating a WARNING to the ConcMgr
2042 p_errcode := c_warning;
2043 fnd_file.put_line (fnd_file.LOG, l_name );
2044 fnd_file.put_line (fnd_file.LOG, l_warning_string);
2045 fnd_file.put_line (fnd_file.LOG, l_warning_string1);
2046 -- null;
2047 END IF;
2048 /*end of fix for 3353252*/
2049
2050 /*
2051 Ok next create the key flexfield information
2052
2053 First fetch all the information from the key flex tables
2054
2055 */
2056
2057 fnd_flex_key_api.set_session_mode('seed_data');
2058
2059
2060 FOR i in 1..4 LOOP
2061
2062 l_generate := FALSE;
2063
2064 if ((i=1) AND (p_generate_cost = 'Y')) then
2065 l_key_app := 'PAY';
2066 l_key_flex_code := 'COST';
2067 l_generate := TRUE;
2068 elsif ((i=2) AND (p_generate_group = 'Y')) then
2069 l_key_app := 'PAY';
2070 l_key_flex_code := 'GRP';
2071 l_generate := TRUE;
2072 elsif ((i=3) AND (p_generate_job = 'Y')) then
2073 l_key_app := 'PER';
2074 l_key_flex_code := 'JOB';
2075 l_generate := TRUE;
2076 elsif ((i=4) AND (p_generate_pos = 'Y')) then
2077 l_key_app := 'PER';
2078 l_key_flex_code := 'POS';
2079 l_generate := TRUE;
2080 end if;
2081
2082 if l_generate then
2083
2084 l_max_segment_count := 0;
2085 l_key_flex_structure_count :=0;
2086
2087 --
2088 -- Create the dummy element context and mappings
2089 --
2090 create_dummy_context(
2091 p_appl_short_name => l_appl_short_name,
2092 p_flexfield_name => l_flexfield_name,
2093 p_context_name => l_key_flex_code,
2094 p_segment_name_prefix=>initcap(l_key_flex_code)||'Segment',
2095 p_max_segments => 30
2096 );
2097
2098 l_key_flex := fnd_flex_key_api.find_flexfield(
2099 appl_short_name => l_key_app
2100 ,flex_code => l_key_flex_code);
2101
2102 /*
2103 Next fetch all the stuctures associated with this
2104 flexfield
2105 */
2106
2107
2108 fnd_flex_key_api.get_structures(flexfield => l_key_flex,
2109 enabled_only => TRUE,
2110 nstructures => l_structures,
2111 structures => l_key_structure_list);
2112
2113 l_structure_count := l_key_structure_list.first;
2114
2115 LOOP
2116
2117 EXIT WHEN not l_key_structure_list.exists(l_structure_count);
2118
2119 /*
2120 If the context exists, delete it and recreate otherwise just create it
2121 */
2122
2123 if FND_FLEX_DSC_API.context_exists(
2124 P_APPL_SHORT_NAME => l_appl_short_name,
2125 P_FLEXFIELD_NAME => l_flexfield_name,
2126 P_CONTEXT_CODE => l_key_flex_code||' - '
2127 ||to_char(l_key_structure_list(l_structure_count))
2128 ) then
2129 FND_FLEX_DSC_API.delete_context(
2130 APPL_SHORT_NAME => l_appl_short_name,
2131 FLEXFIELD_NAME => l_flexfield_name,
2132 CONTEXT => l_key_flex_code||' - '
2133 ||to_char(l_key_structure_list(l_structure_count)));
2134
2135 end if; -- Does this context exist?
2136
2137 /*
2138 Get information about the structure
2139 */
2140
2141 l_key_structure := FND_FLEX_KEY_API.find_structure(
2142 flexfield => l_key_flex,
2143 structure_number => l_key_structure_list(l_structure_count));
2144
2145 FND_FLEX_DSC_API.create_context(
2146 APPL_SHORT_NAME => l_appl_short_name,
2147 FLEXFIELD_NAME => l_flexfield_name,
2148 CONTEXT_CODE => l_key_flex_code||' - '
2149 ||to_char(l_key_structure_list(l_structure_count)),
2150 CONTEXT_NAME => l_key_structure.structure_code,
2151 DESCRIPTION => 'Auto generated HXC '||l_key_flex_code||' context',
2152 ENABLED => 'Y',
2153 GLOBAL_FLAG => 'N');
2154
2155 l_key_flex_structure_count := l_key_flex_structure_count +1;
2156
2157 --
2158 -- Create the Building block information type for this context
2159 --
2160 l_building_block_info_id := create_bld_blk_info_type(
2161 p_appl_short_name => l_appl_short_name,
2162 p_flexfield_name => l_flexfield_name,
2163 p_legislation_code => NULL,
2164 p_bld_blk_info_type =>l_key_flex_code||' - '
2165 ||to_char(l_key_structure_list(l_structure_count)),
2166 p_category => l_key_flex_code);
2167
2168 /*
2169 Now, fetch the key flex segment information, and recreate the segments
2170 in the descriptive flexfield case
2171 */
2172
2173 fnd_flex_key_api.get_segments(flexfield => l_key_flex,
2174 structure => l_key_structure,
2175 enabled_only => TRUE,
2176 nsegments => l_segments,
2177 segments => l_key_segment_list);
2178
2179 l_segment_count := l_key_segment_list.first;
2180
2181 LOOP
2182
2183 EXIT WHEN not l_key_segment_list.exists(l_segment_count);
2184 /*
2185 Get information about this segment
2186 */
2187 l_key_segment := FND_FLEX_KEY_API.find_segment(
2188 flexfield => l_key_flex,
2189 structure => l_key_structure,
2190 segment_name => l_key_segment_list(l_segment_count));
2191 /*
2192 Create the descriptive flexfield segment for this corresponding segment
2193 */
2194
2195 FND_FLEX_DSC_API.create_segment(
2196 APPL_SHORT_NAME => l_appl_short_name,
2197 FLEXFIELD_NAME => l_flexfield_name,
2198 CONTEXT_NAME => l_key_flex_code||' - '
2199 ||to_char(l_key_structure_list(l_structure_count)),
2200 NAME => valid_segment_name(l_key_segment.segment_name,l_key_flex_code,l_key_structure_list(l_structure_count)),
2201 COLUMN => 'ATTRIBUTE'||to_char(l_segment_count),
2202 DESCRIPTION => 'Auto generated HXC '||l_key_flex_code||' context segment',
2203 SEQUENCE_NUMBER => l_key_segment.segment_number,
2204 ENABLED => 'N',
2205 DISPLAYED => 'N',
2206 VALUE_SET => find_value_set(l_key_segment.value_set_id),
2207 DEFAULT_TYPE => l_key_segment.default_type,
2208 DEFAULT_VALUE => l_key_segment.default_value,
2209 REQUIRED => l_key_segment.required_flag,
2210 SECURITY_ENABLED => l_key_segment.security_flag,
2211 DISPLAY_SIZE => l_key_segment.display_size,
2212 DESCRIPTION_SIZE => l_key_segment.description_size,
2213 CONCATENATED_DESCRIPTION_SIZE => l_key_segment.concat_size,
2214 LIST_OF_VALUES_PROMPT => l_key_segment.lov_prompt,
2215 WINDOW_PROMPT => l_key_segment.window_prompt,
2216 RANGE => NULL,
2217 SRW_PARAMETER => NULL);
2218
2219 l_segment_count := l_key_segment_list.next(l_segment_count);
2220 END LOOP;
2221
2222 if l_max_segment_count < l_segment_count then
2223 l_max_segment_count := l_segment_count;
2224 end if;
2225
2226 l_structure_count := l_key_structure_list.next(l_structure_count);
2227
2228 END LOOP;
2229
2230 end if; -- should we generate on this pass.
2231
2232 END LOOP;
2233
2234 --
2235 -- Do we need to check for missing types and
2236 -- usages? We are always going to do this now
2237 --
2238
2239 create_missing_type_usages(
2240 p_appl_short_name => 'HXC'
2241 ,p_flex_name => l_flexfield_name);
2242
2243 if p_generate_prj = 'Y' then
2244
2245 create_dummy_context(
2246 p_appl_short_name => l_appl_short_name,
2247 p_flexfield_name => l_flexfield_name,
2248 p_context_name => 'PAEXPITDFF',
2249 p_segment_name_prefix=>'PADFFAttribute',
2250 p_max_segments => 10
2251 );
2252
2253 --
2254 -- Next loop through all the projects contexts,
2255 -- and create the information types and usages
2256 --
2257 duplicate_desc_flex
2258 (p_appl_short_name => 'PA',
2259 p_flexfield_name => 'PA_EXPENDITURE_ITEMS_DESC_FLEX',
2260 p_otc_appl_short_name => 'HXC',
2261 p_otc_flex_name => l_flexfield_name,
2262 p_context_prefix => 'PAEXPITDFF',
2263 p_preserve => true
2264 );
2265
2266 include_mapping_components('PAEXPITDFF');
2267
2268 end if; -- are we generating mappings for the projects flex?
2269
2270 end if; -- are we undoing?
2271
2272 --
2273 -- This next section updates preference definitions from the
2274 -- flex definition of OTC PREFERENCES
2275 --
2276
2277 create_preference_definitions(
2278 p_flex_name => 'OTC PREFERENCES'
2279 ,p_appl_short_name => 'HXC'
2280 );
2281
2282
2283 END run_process;
2284
2285 END hxc_create_flex_mappings;