[Home] [Help]
PACKAGE BODY: APPS.PER_RI_CONFIG_UTILITIES
Source
1 PACKAGE BODY per_ri_config_utilities AS
2 /* $Header: perriutl.pkb 120.7.12010000.3 2010/05/13 08:50:13 sravikum ship $ */
3 g_package varchar2(30) := 'per_ri_config_utilities.';
4 g_config_effective_date date := TRUNC(TO_DATE('1951/01/01', 'YYYY/MM/DD'));
5 g_default_date_format varchar2(200) := 'RRRR/MM/DD';
6
7 g_config_effective_end_date date := TRUNC(TO_DATE('4712/12/31', 'YYYY/MM/DD'));
8
9 /* --------------------------------------------------------------------------
10 -- Name : jpg_defined
11 -- Purpose : This function returns if Jobs or Positions are defined for
12 -- a given enterprise structures configuration.
13 -- Arguments : p_configuration_code
14 -- p_seg_type
15 -------------------------------------------------------------------------- */
16
17 FUNCTION jpg_defined (p_configuration_code in varchar2
18 ,p_seg_type in varchar2)
19 RETURN boolean IS
20 cursor csr_job_seg_defined (cp_configuration_code in varchar2) IS
21 select count(*)
22 from per_ri_config_job_kf_seg_v
23 where configuration_code = p_configuration_code;
24
25 cursor csr_pos_seg_defined (cp_configuration_code in varchar2) IS
26 select count(*)
27 from per_ri_config_pos_kf_seg_v
28 where configuration_code = p_configuration_code;
29
30 cursor csr_grd_seg_defined (cp_configuration_code in varchar2) IS
31 select count(*)
32 from per_ri_config_grade_kf_seg_v
33 where configuration_code = p_configuration_code;
34
35 l_proc varchar2(72) := g_package || 'jpg_define';
36 l_error_message varchar2(360);
37
38 l_jobs_seg_count number(2) default 0;
39 l_positions_seg_count number(2) default 0;
40 l_grade_seg_count number(2) default 0;
41
42 l_jobs_seg boolean default FALSE;
43 l_positions_seg boolean default FALSE;
44 l_grade_seg boolean default FALSE;
45
46 BEGIN
47 hr_utility.set_location('Entering:'|| l_proc, 10);
48
49 if p_seg_type = 'JOB' then
50 hr_utility.set_location(l_proc, 20);
51 open csr_job_seg_defined(p_configuration_code);
52
53 fetch csr_job_seg_defined into l_jobs_seg_count;
54
55 if l_jobs_seg_count > 0 then
56 l_jobs_seg := TRUE;
57 hr_utility.trace('Jobs Defined');
58 hr_utility.set_location(l_proc, 30);
59 end if;
60 close csr_job_seg_defined;
61 hr_utility.set_location(' Leaving:'|| l_proc, 110);
62 return l_jobs_seg;
63 end if;
64
65 if p_seg_type = 'POSITION' then
66 hr_utility.set_location(l_proc, 40);
67 open csr_pos_seg_defined(p_configuration_code);
68
69 fetch csr_pos_seg_defined into l_positions_seg_count;
70
71 if l_positions_seg_count > 0 then
72 l_positions_seg := TRUE;
73 hr_utility.trace('Positions Defined');
74 hr_utility.set_location(l_proc, 50);
75 end if;
76 close csr_pos_seg_defined;
77 hr_utility.set_location(' Leaving:'|| l_proc, 120);
78 return l_positions_seg;
79 end if;
80
81 if p_seg_type = 'GRADE' then
82 hr_utility.set_location(l_proc, 80);
83 open csr_grd_seg_defined(p_configuration_code);
84
85 fetch csr_grd_seg_defined into l_grade_seg_count;
86
87 if l_grade_seg_count > 0 then
88 l_grade_seg := TRUE;
89 hr_utility.set_location(l_proc, 90);
90 end if;
91 close csr_grd_seg_defined;
92 hr_utility.trace('Grades Defined');
93 hr_utility.set_location(' Leaving:'|| l_proc, 130);
94 return l_grade_seg;
95 end if;
96
97 EXCEPTION
98 when others then
99 l_error_message := 'Error in ' || l_proc;
100 hr_utility.trace(l_error_message || '-' || sqlerrm);
101 hr_utility.set_location(' Leaving:'|| l_proc, 500);
102 hr_utility.raise_error;
103
104 END jpg_defined;
105
106 /* --------------------------------------------------------------------------
107 -- Name : regional_variance_defined
108 -- Purpose : This function returns if regional variance is defined.
109 -- a given enterprise structures configuration.
110 -- Arguments : p_configuration_code
111 -- p_rv_type
112 -------------------------------------------------------------------------- */
113
114
115 FUNCTION regional_variance_defined (p_configuration_code in varchar2
116 ,p_rv_type in varchar2)
117 RETURN boolean IS
118
119 cursor csr_jp_rv_defined (cp_configuration_code in varchar2) IS
120 select count(*)
121 from per_ri_config_jp_rv_v
122 where configuration_code = p_configuration_code;
123
124 cursor csr_grd_rv_defined (cp_configuration_code in varchar2) IS
125 select count(*)
126 from per_ri_config_grd_rv_v
127 where configuration_code = p_configuration_code;
128
129 l_proc varchar2(72) := g_package || 'regional_variance_defined';
130 l_error_message varchar2(360);
131 l_jp_rv_count number(2) default 0;
132 l_grd_rv_count number(2) default 0;
133
134 l_jp_rv boolean default FALSE;
135 l_grd_rv boolean default FALSE;
136
137 BEGIN
138 hr_utility.set_location('Entering:'|| l_proc, 10);
139
140 if p_rv_type = 'JP' then
141 hr_utility.set_location(l_proc, 20);
142 open csr_jp_rv_defined(p_configuration_code);
143
144 fetch csr_jp_rv_defined into l_jp_rv_count;
145
146 if l_jp_rv_count > 0 then
147 l_jp_rv := TRUE;
148 hr_utility.set_location(l_proc, 30);
149 end if;
150 close csr_jp_rv_defined;
151
152 if l_jp_rv then
153 hr_utility.trace('JP Regional Variance Defined');
154 else
155 hr_utility.trace('JP Regional Variance NOT Defined');
156 end if;
157 return l_jp_rv;
158 end if;
159
160 if p_rv_type = 'GRD' then
161 hr_utility.set_location(l_proc, 80);
162 open csr_grd_rv_defined(p_configuration_code);
163
164 fetch csr_grd_rv_defined into l_grd_rv_count;
165
166 if l_grd_rv_count > 0 then
167 l_grd_rv := TRUE;
168 hr_utility.set_location(l_proc, 90);
169 end if;
170 close csr_grd_rv_defined;
171
172 if l_grd_rv then
173 hr_utility.trace('Grades Regional Variance Defined');
174 else
175 hr_utility.trace('Grades Regional Variance NOT Defined');
176 end if;
177
178 return l_grd_rv;
179 end if;
180 hr_utility.set_location(' Leaving:'|| l_proc, 30);
181
182 EXCEPTION
183 when others then
184 l_error_message := 'Error in ' || l_proc;
185 hr_utility.trace(l_error_message || '-' || sqlerrm);
186 hr_utility.set_location(' Leaving:'|| l_proc, 500);
187 hr_utility.raise_error;
188
189 END;
190
191 /* --------------------------------------------------------------------------
192 -- Name : get_bg_job_keyflex_name
193 -- Purpose : This function returns name of JOB KEYFLEX for a business group
194 -- for a given configuration
195 -- Arguments : p_configuration_code
196 -- p_bg_country_code
197 -------------------------------------------------------------------------- */
198
199 FUNCTION get_bg_job_keyflex_name (p_configuration_code in varchar2
200 ,p_bg_country_code in varchar2)
201 RETURN varchar2 IS
202
203
204 cursor csr_job_rv (cp_configuration_code in varchar2
205 ,cp_bg_country_code in varchar2) IS
206 select rv.regional_variance_name
207 from per_ri_config_jp_rv_v rv,
208 per_ri_config_job_rv_seg_v seg
209 where rv.configuration_code = cp_configuration_code
210 and rv.reg_variance_country_code = p_bg_country_code
211 and rv.configuration_code = seg.configuration_code
212 and rv.regional_variance_name = seg.regional_variance_name
213 and seg.global_structure_indicator = 'N';
214
215 l_proc varchar2(72) := g_package || 'get_bg_job_keyflex_name';
216 l_error_message varchar2(360);
217
218 l_regional_variance_name per_ri_config_information.config_information1%type;
219 l_job_key_flex_name varchar2(30);
220
221 BEGIN
222 hr_utility.set_location('Entering:'|| l_proc, 10);
223
224 open csr_job_rv(p_configuration_code
225 ,p_bg_country_code);
226 fetch csr_job_rv into l_regional_variance_name;
227 if csr_job_rv%FOUND then
228 hr_utility.set_location(l_proc, 20);
229 l_job_key_flex_name := per_ri_config_utilities.return_config_entity_name(l_regional_variance_name)
230 || per_ri_config_main.g_job_rv_struct_def_string;
231 else
232 hr_utility.set_location(l_proc, 30);
233 l_job_key_flex_name := per_ri_config_utilities.return_config_entity_name
234 (per_ri_config_main.g_global_job_structure_name);
235 end if;
236 hr_utility.set_location(' Leaving:' || l_proc, 20);
237 return l_job_key_flex_name;
238
239 EXCEPTION
240 when others then
241 l_error_message := 'Error in ' || l_proc;
242 hr_utility.trace(l_error_message || '-' || sqlerrm);
243 hr_utility.set_location(' Leaving:'|| l_proc, 500);
244 hr_utility.raise_error;
245
246 END get_bg_job_keyflex_name;
247
248 /* --------------------------------------------------------------------------
249 -- Name : get_bg_pos_keyflex_name
250 -- Purpose : This function returns name of POS KEYFLEX for a business group
251 -- for a given configuration
252 -- Arguments : p_configuration_code
253 -- p_bg_country_code
254 -------------------------------------------------------------------------- */
255
256 FUNCTION get_bg_pos_keyflex_name (p_configuration_code in varchar2
257 ,p_bg_country_code in varchar2)
258 RETURN varchar2 IS
259
260
261 cursor csr_pos_rv (cp_configuration_code in varchar2
262 ,cp_bg_country_code in varchar2) IS
263 select regional_variance_name
264 from per_ri_config_jp_rv_v
265 where configuration_code = p_configuration_code
266 and reg_variance_country_code = p_bg_country_code
267 and exists (select configuration_code
268 from per_ri_config_pos_rv_seg_v
269 where configuration_code = cp_configuration_code);
270
271 l_proc varchar2(72) := g_package || 'get_bg_pos_keyflex_name';
272 l_error_message varchar2(360);
273
274 l_regional_variance_name per_ri_config_information.config_information1%type;
275 l_pos_key_flex_name varchar2(30);
276
277 BEGIN
278 hr_utility.set_location('Entering:'|| l_proc, 10);
279
280 open csr_pos_rv(p_configuration_code
281 ,p_bg_country_code);
282 fetch csr_pos_rv into l_regional_variance_name;
283 if csr_pos_rv%FOUND then
284 hr_utility.set_location(l_proc, 20);
285 l_pos_key_flex_name := per_ri_config_utilities.return_config_entity_name(l_regional_variance_name)
286 || per_ri_config_main.g_pos_rv_struct_def_string;
287 else
288 hr_utility.set_location(l_proc, 30);
289 l_pos_key_flex_name := per_ri_config_utilities.return_config_entity_name
290 (per_ri_config_main.g_global_pos_structure_name);
291 end if;
292 hr_utility.set_location(' Leaving:' || l_proc, 20);
293 return l_pos_key_flex_name;
294
295 EXCEPTION
296 when others then
297 l_error_message := 'Error in ' || l_proc;
298 hr_utility.trace(l_error_message || '-' || sqlerrm);
299 hr_utility.set_location(' Leaving:'|| l_proc, 500);
300 hr_utility.raise_error;
301
302 END get_bg_pos_keyflex_name;
303
304 /* --------------------------------------------------------------------------
305 -- Name : check_selected_product
306 -- Purpose : This function returns if specified product selection is made
307 -- for a given configuration
308 -- Arguments : p_configuration_code
309 -- p_product_name
310 -------------------------------------------------------------------------- */
311
312 FUNCTION check_selected_product(p_configuration_code in varchar2
313 ,p_product_name in varchar2)
314 RETURN boolean IS
315
316
317 cursor csr_get_product (cp_configuration_code in varchar2
318 ,cp_product_name in varchar2) IS
319 select product_name
320 from per_ri_config_prod_selection_v
321 where configuration_code = cp_configuration_code
322 and product_name = cp_product_name;
323
324 l_proc varchar2(72) := g_package || 'check_selected_product';
325 l_error_message varchar2(360);
326
327 l_product_name per_ri_config_information.config_information1%type;
328 l_product_selected boolean default FALSE;
329
330 BEGIN
331 hr_utility.set_location('Entering:'|| l_proc, 10);
332
333 open csr_get_product(p_configuration_code
334 ,p_product_name);
335 fetch csr_get_product into l_product_name;
336 if csr_get_product%FOUND then
337 hr_utility.set_location(l_proc, 20);
338 l_product_selected := TRUE;
339 else
340 l_product_selected := FALSE;
341 hr_utility.set_location(l_proc, 30);
342 end if;
343 hr_utility.set_location(' Leaving:' || l_proc, 20);
344 close csr_get_product;
345 return l_product_selected;
346
347 EXCEPTION
348 when others then
349 l_error_message := 'Error in ' || l_proc;
350 hr_utility.trace(l_error_message || '-' || sqlerrm);
351 hr_utility.set_location(' Leaving:'|| l_proc, 500);
352 hr_utility.raise_error;
353
354 END check_selected_product;
355
356 /* --------------------------------------------------------------------------
357 -- Name : get_bg_grd_keyflex_name
358 -- Purpose : This function returns name of GRD KEYFLEX for a business group
359 -- for a given configuration
360 -- Arguments : p_configuration_code
361 -- p_bg_country_code
362 -------------------------------------------------------------------------- */
363
364 FUNCTION get_bg_grd_keyflex_name(p_configuration_code in varchar2
365 ,p_bg_country_code in varchar2)
366 RETURN varchar2 IS
367
368
369 cursor csr_grd_rv (cp_configuration_code in varchar2
370 ,cp_bg_country_code in varchar2) IS
371 select per_ri_config_utilities.return_config_entity_name(regional_variance_name)
372 from per_ri_config_grd_rv_v
373 where configuration_code = p_configuration_code
374 and reg_variance_country_code = p_bg_country_code;
375
376 l_proc varchar2(72) := g_package || 'get_bg_grd_keyflex_name';
377 l_error_message varchar2(360);
378 l_regional_variance_name per_ri_config_information.config_information1%type;
379 l_grd_key_flex_name varchar2(30);
380 l_enterprise_primary_industry per_ri_config_information.config_information1%type;
381
382 BEGIN
383 hr_utility.set_location('Entering:'|| l_proc, 10);
384
385 l_enterprise_primary_industry := per_ri_config_utilities.get_ent_primary_industry
386 (p_configuration_code => p_configuration_code);
387 hr_utility.trace('l_enterprise_primary_industry = ' || l_enterprise_primary_industry);
388 if l_enterprise_primary_industry <> 'US_GOVERNMENT' then
389 open csr_grd_rv(p_configuration_code
390 ,p_bg_country_code);
391 fetch csr_grd_rv into l_regional_variance_name;
392 if csr_grd_rv%FOUND then
393 hr_utility.set_location(l_proc, 20);
394 l_grd_key_flex_name := l_regional_variance_name || per_ri_config_main.g_grd_rv_struct_def_string;
395 else
396 hr_utility.set_location(l_proc, 30);
397 l_grd_key_flex_name := per_ri_config_utilities.return_config_entity_name
398 (per_ri_config_main.g_global_grd_structure_name);
399 end if;
400 hr_utility.trace('Grade Key Flex Name : ' || l_grd_key_flex_name);
401 hr_utility.set_location(' Leaving:' || l_proc, 20);
402 else
403 hr_utility.set_location(l_proc, 40);
404 l_grd_key_flex_name := per_ri_config_main.g_global_fed_grd_struct_name;
405 end if;
406
407 hr_utility.trace('l_grd_key_flex_name = ' || l_grd_key_flex_name);
408
409 hr_utility.set_location(' Leaving:'|| l_proc, 60);
410
411 return l_grd_key_flex_name;
412
413 EXCEPTION
414 when others then
415 l_error_message := 'Error in ' || l_proc;
416 hr_utility.trace(l_error_message || '-' || sqlerrm);
417 hr_utility.set_location(' Leaving:'|| l_proc, 500);
418 hr_utility.raise_error;
419
420 END get_bg_grd_keyflex_name;
421
422 /* --------------------------------------------------------------------------
423 -- Name : get_enterprise_short_name
424 -- Purpose : This function returns name of enterpise short name
425 -- for a given configuration
426 -- Arguments : p_configuration_code
427 --
428 -------------------------------------------------------------------------- */
429
430 FUNCTION get_enterprise_short_name (p_configuration_code in varchar2)
431 RETURN varchar2 IS
432
433 cursor csr_get_enterprise_short_name
434 (cp_configuration_code in varchar2) IS
435 select enterprise_short_name
436 from per_ri_config_enterprise_v
437 where configuration_code = p_configuration_code;
438
439 l_enterprise_short_name per_ri_config_information.config_information1%type;
440 l_proc varchar2(72) := g_package || 'get_enterprise_short_name';
441 l_error_message varchar2(360);
442
443 BEGIN
444 hr_utility.set_location('Entering:'|| l_proc, 10);
445
446 open csr_get_enterprise_short_name(p_configuration_code);
447
448 fetch csr_get_enterprise_short_name into
449 l_enterprise_short_name;
450 if csr_get_enterprise_short_name%NOTFOUND then
451 hr_utility.set_location('Entering:'|| l_proc, 20);
452 end if;
453
454 close csr_get_enterprise_short_name;
455
456 hr_utility.set_location(' Leaving:'|| l_proc, 30);
457
458 -- Working on this issue
459 if g_enterprise_short_name is NULL then
460 g_enterprise_short_name := l_enterprise_short_name;
461 end if;
462
463 -- Always set this value
464 g_enterprise_short_name := l_enterprise_short_name;
465 return l_enterprise_short_name;
466
467 EXCEPTION
468 when others then
469 l_error_message := 'Error in ' || l_proc;
470 hr_utility.trace(l_error_message || '-' || sqlerrm);
471 hr_utility.set_location(' Leaving:'|| l_proc, 500);
472 hr_utility.raise_error;
473
474 END;
475
476 /* --------------------------------------------------------------------------
477 -- Name : get_enterprise_name
478 -- Purpose : This function returns enterprise name for a given configuration
479 -- Arguments : p_configuration_code
480 -- p_bg_country_code
481 -------------------------------------------------------------------------- */
482
483 FUNCTION get_enterprise_name(p_configuration_code in varchar2)
484 RETURN varchar2 IS
485
486 cursor csr_get_enterprise_name(cp_configuration_code in varchar2) IS
487 select per_ri_config_utilities.return_config_entity_name(enterprise_name)
488 from per_ri_config_enterprise_v
489 where configuration_code = p_configuration_code;
490
491 l_enterprise_name per_ri_config_information.config_information1%type;
492 l_proc varchar2(72) := g_package || 'get_enterprise_name';
493 l_error_message varchar2(360);
494
495 BEGIN
496 hr_utility.set_location('Entering:'|| l_proc, 10);
497
498 open csr_get_enterprise_name(p_configuration_code);
499
500 fetch csr_get_enterprise_name into
501 l_enterprise_name;
502 if csr_get_enterprise_name%NOTFOUND then
503 hr_utility.set_location('Entering:'|| l_proc, 20);
504 end if;
505
506 close csr_get_enterprise_name;
507
508 hr_utility.trace('l_enterprise_name = ' || l_enterprise_name);
509 hr_utility.set_location(' Leaving:'|| l_proc, 30);
510
511 return l_enterprise_name;
512
513 EXCEPTION
514 when others then
515 l_error_message := 'Error in ' || l_proc;
516 hr_utility.trace(l_error_message || '-' || sqlerrm);
517 hr_utility.set_location(' Leaving:'|| l_proc, 500);
518 hr_utility.raise_error;
519
520 END;
521
522 /* --------------------------------------------------------------------------
523 -- Name : get_ent_primary_industry
524 -- Purpose : This function returns name enterprise primary industry
525 -- for a given configuration
526 -- Arguments : get_ent_primary_industry
527 --
528 -------------------------------------------------------------------------- */
529
530 FUNCTION get_ent_primary_industry(p_configuration_code in varchar2)
531 RETURN varchar2 IS
532
533 cursor csr_get_ent_industry(cp_configuration_code in varchar2) IS
534 select enterprise_primary_industry
535 from per_ri_config_enterprise_v
536 where configuration_code = p_configuration_code;
537
538 l_enterprise_primary_industry per_ri_config_information.config_information1%type;
539
540 l_proc varchar2(72) := g_package || 'get_ent_primary_industry';
541 l_error_message varchar2(360);
542 BEGIN
543 hr_utility.set_location('Entering:'|| l_proc, 10);
544
545 open csr_get_ent_industry(p_configuration_code);
546
547 fetch csr_get_ent_industry into l_enterprise_primary_industry;
548 if csr_get_ent_industry%NOTFOUND then
549 hr_utility.set_location(l_proc, 20);
550 end if;
551
552 close csr_get_ent_industry;
553
554 hr_utility.trace('l_enterprise_primary_industry = ' || l_enterprise_primary_industry);
555 hr_utility.set_location(' Leaving:'|| l_proc, 30);
556
557 return l_enterprise_primary_industry;
558
559 EXCEPTION
560 when others then
561 l_error_message := 'Error in ' || l_proc;
562 hr_utility.trace(l_error_message || '-' || sqlerrm);
563 hr_utility.set_location(' Leaving:'|| l_proc, 500);
564 hr_utility.raise_error;
565
566 END get_ent_primary_industry;
567
568 /* --------------------------------------------------------------------------
569 -- Name : business_group_decision
570 -- Purpose : This function decision about the business group based unon the
571 -- 'Business Group' creation logic for a given configuration
572 -- Arguments : p_configuration_code
573 -- p_country_code
574 -- p_number_of_employees
575 -- p_payroll_to_process_employees
576 -- p_hr_support_for_this_country
577 -------------------------------------------------------------------------- */
578
579 FUNCTION business_group_decision(p_configuration_code in varchar2 default null
580 ,p_country_code in varchar2
581 ,p_number_of_employees in varchar2 default null
582 ,p_payroll_to_process_employees in varchar2 default null
583 ,p_hr_support_for_this_country in varchar2 default null)
584 RETURN varchar2 IS
585
586 cursor csr_config_business_groups(cp_configuration_code in varchar2
587 ,cp_country_code in varchar2) IS
588 select configuration_code,
589 country_code,
590 number_of_employees,
591 payroll_to_process_employees,
592 hr_support_for_this_country
593 from per_ri_config_country_v
594 where country_code = cp_country_code
595 and configuration_code = cp_configuration_code;
596
597 l_configuration_code per_ri_config_information.configuration_code%type;
598 l_country_code per_ri_config_information.config_information1%type;
599 l_number_of_employees per_ri_config_information.config_information1%type;
600 l_payroll_to_process_employees per_ri_config_information.config_information1%type;
601 l_hr_support_for_this_country per_ri_config_information.config_information1%type;
602 l_business_group_decision per_ri_config_information.config_information1%type;
603 l_called_from_ui boolean default FALSE;
604
605 l_proc varchar2(72) := g_package || 'business_group_decision';
606 l_error_message varchar2(360);
607
608 BEGIN
609
610 hr_utility.set_location('Entering: '|| l_proc, 10);
611
612 if (p_number_of_employees is not null)
613 and (p_payroll_to_process_employees is not null)
614 and (p_hr_support_for_this_country is not null) then
615 l_called_from_ui := TRUE;
616 end if;
617
618 if NOT l_called_from_ui then
619 open csr_config_business_groups(p_configuration_code
620 ,p_country_code);
621 fetch csr_config_business_groups into
622 l_configuration_code,
623 l_country_code,
624 l_number_of_employees,
625 l_payroll_to_process_employees,
626 l_hr_support_for_this_country;
627 else
628 l_configuration_code := null;
629 l_country_code := p_country_code;
630 l_number_of_employees := p_number_of_employees;
631 l_payroll_to_process_employees := p_payroll_to_process_employees;
632 l_hr_support_for_this_country := p_hr_support_for_this_country;
633 end if;
634
635 if l_country_code = 'US' then
636 l_business_group_decision := 'US';
637 else
638 if l_payroll_to_process_employees = 'N' then
639 if l_number_of_employees <= 100 then
640 l_business_group_decision := 'INT';
641 else
642 if (per_ri_config_utilities.legislation_support(l_country_code, 'PER')) = TRUE then
643 l_business_group_decision := l_country_code;
644 else
645 if (l_hr_support_for_this_country = 'Y' ) then
646 l_business_group_decision := l_country_code;
647 else
648 l_business_group_decision := 'INT';
649 end if;
650 end if;
651 end if;
652 else
653 if (per_ri_config_utilities.legislation_support(l_country_code, 'PAY')) = TRUE then
654 l_business_group_decision := l_country_code;
655 else
656 -- revisit it
657 -- l_business_group_decision := 'INT';
658 l_business_group_decision := l_country_code;
659 end if;
660 end if;
661 end if;
662
663 hr_utility.trace('l_country_code = ' || l_country_code);
664 hr_utility.trace('l_business_group_decision = ' || l_business_group_decision);
665 if NOT l_called_from_ui then
666 close csr_config_business_groups;
667 end if;
668
669 hr_utility.set_location(' Leaving: '|| l_proc, 20);
670
671 if NOT l_called_from_ui then
672 return l_business_group_decision;
673 else
674 return per_ri_config_utilities.get_country_display_name
675 (p_territory_code => l_business_group_decision);
676 end if;
677
678 return l_business_group_decision;
679
680 EXCEPTION
681 when others then
682 l_error_message := 'Error in ' || l_proc;
683 hr_utility.trace(l_error_message || '-' || sqlerrm);
684 hr_utility.set_location(' Leaving:'|| l_proc, 500);
685 hr_utility.raise_error;
686
687 END business_group_decision;
688
689 /* --------------------------------------------------------------------------
690 -- Name : legislation_support
691 -- Purpose : This function returns if HRMS support that legislation
692 -- Arguments : p_legislation_code
693 -- p_application_short_name
694 -------------------------------------------------------------------------- */
695
696 FUNCTION legislation_support(p_legislation_code in varchar2
697 ,p_application_short_name in varchar2) RETURN BOOLEAN IS
698
699 cursor csr_legislation_support(cp_legislation_code in varchar2
700 ,cp_application_short_name in varchar2) IS
701 select legislation_code
702 from hr_legislation_installations
703 where legislation_code = cp_legislation_code
704 and application_short_name = cp_application_short_name;
705
706 l_legislation_code hr_legislation_installations.legislation_code%type;
707 l_application_short_name hr_legislation_installations.application_short_name%type;
708 l_legislation_support BOOLEAN;
709
710 l_proc varchar2(72) := g_package || 'legislation_support';
711 l_error_message varchar2(360);
712
713 BEGIN
714
715 hr_utility.set_location('Entering:'|| l_proc, 10);
716
717 open csr_legislation_support(p_legislation_code
718 ,p_application_short_name);
719 fetch csr_legislation_support into
720 l_legislation_code;
721 if csr_legislation_support%FOUND then
722 l_legislation_support := TRUE;
723 else
724 l_legislation_support := FALSE;
725 end if;
726
727 close csr_legislation_support;
728 hr_utility.trace('l_legislation_code = ' || l_legislation_code);
729 hr_utility.set_location(' Leaving:'|| l_proc, 30);
730
731 return l_legislation_support;
732
733 EXCEPTION
734 when others then
735 l_error_message := 'Error in ' || l_proc;
736 hr_utility.trace(l_error_message || '-' || sqlerrm);
737 hr_utility.set_location(' Leaving:'|| l_proc, 500);
738 hr_utility.raise_error;
739
740 END legislation_support;
741
742 /* --------------------------------------------------------------------------
743 -- Name : set_profile_option_value
744 -- Purpose : This procedure sets the profile option values for a specified
745 -- level (SITE, APPLICATION, RESPOSIBILITY or USER).
746 -- Arguments : p_level
747 -- p_level_value
748 -- p_profile_name
749 -- p_profile_name
750 -- p_profile_option_value
751 -- p_custom_mode
752 -- p_owner
753 -------------------------------------------------------------------------- */
754
755 PROCEDURE set_profile_option_value(p_level in number
756 ,p_level_value in varchar2
757 ,p_level_value_app in varchar2
758 ,p_profile_name in varchar2
759 ,p_profile_option_value in varchar2
760 ,p_custom_mode in varchar2 DEFAULT 'FORCE'
761 ,p_owner in varchar2 DEFAULT 'CUSTOM') IS
762
763 app_id number := 0;
764 profo_id number := 0;
765 levval_id number := 0;
766 lapp_id number := null;
767 f_luby number; -- entity owner in file
768 f_ludate date := sysdate; -- entity update date in file
769 found varchar2(1);
770
771 l_proc varchar2(72) := g_package || 'set_profile_option_value';
772 l_error_message varchar2(360);
773
774 BEGIN
775 hr_utility.set_location('Entering:'|| l_proc, 10);
776 hr_utility.trace('p_level:'|| p_level);
777 if (p_level = '10001') then
778 levval_id := 0;
779 elsif (p_level = '10002') then
780 select application_id into levval_id
781 from fnd_application
782 where application_short_name = p_level_value;
783 elsif (p_level = '10003') then
784 select application_id into lapp_id
785 from fnd_application
786 where application_short_name = p_level_value_app;
787
788 select responsibility_id into levval_id
789 from fnd_responsibility
790 where application_id = lapp_id
791 and responsibility_key = p_level_value;
792 elsif (p_level = '10005') then
793 select server_id into levval_id
794 from fnd_nodes
795 where node_name = p_level_value;
796 elsif (p_level = '10006') then
797 select organization_id into levval_id
798 from hr_operating_units
799 where name = p_level_value;
800 else
801 select user_id into levval_id
802 from fnd_user
803 where user_name = p_level_value;
804 end if;
805
806 select profile_option_id, application_id
807 into profo_id, app_id
808 from fnd_profile_options
809 where profile_option_name = p_profile_name;
810
811 f_luby := fnd_load_util.owner_id(p_owner);
812
813 begin
814 --
815 -- This section should never perform updates to existing
816 -- data unless CUSTOM_MODE is equal to FORCE
817 --
818
819 if (p_level = '10003') then
820 select 'Y' into found
821 from FND_PROFILE_OPTION_VALUES
822 where PROFILE_OPTION_ID = profo_id
823 and APPLICATION_ID = app_id
824 and LEVEL_ID = 10003
825 and LEVEL_VALUE_APPLICATION_ID = lapp_id
826 and LEVEL_VALUE = levval_id;
827 hr_utility.trace('Found Record..' || lapp_id || ' ' || levval_id);
828 else
829 select 'Y' into found
830 from FND_PROFILE_OPTION_VALUES
831 where PROFILE_OPTION_ID = profo_id
832 and APPLICATION_ID = app_id
833 and LEVEL_ID = to_number(p_level)
834 and LEVEL_VALUE = levval_id;
835 end if;
836
837 if (p_custom_mode = 'FORCE') then
838 update fnd_profile_option_values
839 set profile_option_value = p_profile_option_value,
840 last_update_date = f_ludate,
841 last_updated_by = f_luby,
842 last_update_login = 0
843 where application_id = app_id
844 and profile_option_id = profo_id
845 and level_id = to_number(p_level)
846 and nvl(level_value_application_id, 1) =
847 decode(p_level, '10003', lapp_id, 1)
848 and level_value = levval_id;
849 end if;
850 exception
851 when no_data_found then
852 hr_utility.trace('Creating Profile Option Value..');
853 insert into fnd_profile_option_values (
854 application_id,
855 profile_option_id,
856 level_id,
857 level_value,
858 last_update_date,
859 last_updated_by,
860 creation_date,
861 created_by,
862 last_update_login,
863 profile_option_value,
864 level_value_application_id )
865 values (
866 app_id,
867 profo_id,
868 to_number(p_level),
869 levval_id,
870 f_ludate,
871 f_luby,
872 f_ludate,
873 f_luby,
874 0,
875 p_profile_option_value,
876 decode(p_level, '10003', lapp_id, null));
877 end;
878 end;
879
880 /* --------------------------------------------------------------------------
881 -- Name : create_key_flexfield
882 -- Purpose : This function create a given KEYFLEX structure and returns
883 -- a number
884 -- Arguments : p_appl_short_name
885 -- p_flex_code
886 -- p_structure_code
887 -- p_structure_title
888 -- p_description
889 -- p_view_name
890 -- p_freeze_flag
891 -- p_enabled_flag
892 -- p_cross_val_flag
893 -- p_freeze_rollup_flag
894 -- p_dynamic_insert_flag
895 -- p_shorthand_enabled_flag
896 -- p_shorthand_prompt
897 -- p_shorthand_length
898 -- p_application_short_name
899 -------------------------------------------------------------------------- */
900
901
902 FUNCTION create_key_flexfield
903 (p_appl_short_Name in varchar2
904 ,p_flex_code in varchar2
905 ,p_structure_code in varchar2
906 ,p_structure_title in varchar2
907 ,p_description in varchar2
908 ,p_view_name in varchar2 default null
909 ,p_freeze_flag in varchar2 default 'N'
910 ,p_enabled_flag in varchar2 default 'Y'
911 ,p_cross_val_flag in varchar2 default 'N'
912 ,p_freeze_rollup_flag in varchar2 default 'N'
913 ,p_dynamic_insert_flag in varchar2 default 'Y'
914 ,p_shorthand_enabled_flag in varchar2 default 'N'
915 ,p_shorthand_prompt in varchar2 default null
916 ,p_shorthand_length in number default null)
917 RETURN NUMBER IS
918
919 l_flexfield fnd_flex_key_api.flexfield_type;
920 l_structure fnd_flex_key_api.structure_type;
921 l_application_id number(15);
922 l_proc varchar2(80) := g_package || 'create_key_flexfield';
923 l_log_message varchar2(360);
924
925 BEGIN
926 hr_utility.set_location('Entering:'|| l_proc, 10);
927
928 fnd_flex_key_api.set_session_mode('customer_data');
929
930 l_flexfield := fnd_flex_key_api.find_flexfield
931 (appl_short_name => p_appl_short_name
932 ,flex_code => p_flex_code );
933
934 hr_utility.set_location(l_proc, 20);
935
936 BEGIN
937 l_structure := fnd_flex_key_api.find_structure
938 (flexfield => l_flexfield,
939 structure_code => p_structure_code );
940
941 return l_structure.structure_number;
942 hr_utility.set_location('Entering:'|| l_proc, 30);
943 EXCEPTION
944 WHEN NO_DATA_FOUND THEN
945 hr_utility.set_location(l_proc, 40);
946 l_structure:=fnd_flex_key_api.new_structure
947 (flexfield => l_flexfield,
948 structure_code => p_structure_code,
949 structure_title => p_structure_title,
950 description => p_description,
951 view_name => p_view_name,
952 freeze_flag => p_freeze_flag,
953 enabled_flag => p_enabled_flag,
954 segment_separator => '.',
955 cross_val_flag => p_cross_val_flag,
956 freeze_rollup_flag => p_freeze_rollup_flag,
957 dynamic_insert_flag => p_dynamic_insert_flag,
958 shorthand_enabled_flag => p_shorthand_enabled_flag,
959 shorthand_prompt => p_shorthand_prompt,
960 shorthand_length => p_shorthand_length);
961
962 SELECT application_id
963 INTO l_application_id
964 FROM FND_APPLICATION
965 WHERE application_short_name = p_appl_short_name;
966
967 SELECT NVL(MAX(ifs.id_flex_num),0) + 1
968 INTO l_structure.structure_number
969 FROM fnd_id_flex_structures ifs
970 WHERE ifs.application_id = l_application_id
971 AND ifs.id_flex_code = p_flex_code
972 AND ifs.id_flex_num < 101;
973
974 fnd_flex_key_api.add_structure
975 ( flexfield => l_flexfield,
976 structure => l_structure );
977
978 RETURN l_structure.structure_number;
979 END;
980
981 hr_utility.set_location(' Leaving:'|| l_proc, 100);
982
983 END create_key_flexfield;
984
985 /* --------------------------------------------------------------------------
986 -- Name : create_flex_segments
987 -- Purpose : This procedure create flexfiled returns if HRMS support that legislation
988 -- Arguments : p_appl_short_name
989 -- p_flex_code
990 -- p_structure_code
991 -- p_segment_name
992 -- p_column_name
993 -- p_segment_number
994 -- p_enabled_flag
995 -- p_displayed_flag
996 -- p_indexed_flag
997 -- p_value_set
998 -- p_display_size
999 -- p_description_size
1000 -- p_concat_size
1001 -- p_lov_prompt
1002 -- p_window_prompt
1003 -- p_segment_type
1004 -- p_fed_seg_attribute
1005 -------------------------------------------------------------------------- */
1006
1007 PROCEDURE create_flex_segments
1008 (p_appl_Short_Name in varchar2
1009 ,p_flex_code in varchar2
1010 ,p_structure_code in varchar2
1011 ,p_segment_name in varchar2
1012 ,p_column_name in varchar2
1013 ,p_segment_number in varchar2
1014 ,p_enabled_flag in varchar2 default 'Y'
1015 ,p_displayed_flag in varchar2 default 'Y'
1016 ,p_indexed_flag in varchar2 default 'Y'
1017 ,p_value_set in varchar2
1018 ,p_display_size in number default 60
1019 ,p_description_size in number default 60
1020 ,p_concat_size in number default 60
1021 ,p_lov_prompt in varchar2
1022 ,p_window_prompt in varchar2
1023 ,p_segment_type in varchar2 default 'CHAR'
1024 ,p_fed_seg_attribute in varchar2 default 'N') IS
1025
1026 l_flexfield fnd_flex_key_api.flexfield_type;
1027 l_structure fnd_flex_key_api.structure_type;
1028 l_application_id number(15);
1029 l_flex_num number(15);
1030 l_segment fnd_flex_key_api.segment_type;
1031 l_valueset_seq number(9);
1032 l_valueset_name fnd_flex_value_sets.flex_value_set_name%type;
1033
1034 l_proc varchar2(80) := g_package || 'create_flex_segments';
1035 l_log_message varchar2(360);
1036
1037 BEGIN
1038
1039 hr_utility.set_location('Entering:'|| l_proc, 10);
1040
1041 fnd_flex_key_api.set_session_mode('customer_data');
1042
1043 l_flexfield := fnd_flex_key_api.find_flexfield
1044 (appl_short_name => p_appl_short_name
1045 ,flex_code => p_flex_code );
1046
1047 hr_utility.set_location(l_proc, 20);
1048
1049 l_structure := fnd_flex_key_api.find_structure
1050 (flexfield => l_flexfield
1051 ,structure_code => p_structure_code );
1052 BEGIN
1053 hr_utility.trace(p_segment_name);
1054 l_segment := fnd_flex_key_api.find_segment
1055 (flexfield => l_flexfield
1056 ,structure => l_structure
1057 ,segment_name => p_segment_name);
1058 hr_utility.set_location('Entering:'|| l_proc, 30);
1059 EXCEPTION
1060 when no_data_found then
1061
1062 if p_fed_seg_attribute = 'N' then
1063 --
1064 -- create flex segment value set
1065 --
1066 select per_ri_config_vsets_number_s.nextval into l_valueset_seq from sys.dual;
1067 l_valueset_seq := p_segment_number;
1068 l_valueset_name := p_structure_code || ' ' || p_segment_name
1069 || ' ' || l_valueset_seq;
1070
1071 hr_utility.trace('l_valueset_name = ' || l_valueset_name);
1072 per_ri_config_utilities.create_valueset(p_valueset_name => l_valueset_name
1073 ,p_valueset_type => p_segment_type);
1074 end if;
1075 hr_utility.set_location('Entering:'|| l_proc, 40);
1076 hr_utility.trace('p_segment_number = ' || to_char(p_segment_number));
1077
1078 -- valueset name is always passed when p_fed_seg_attribute = 'Y'
1079 if p_fed_seg_attribute = 'Y' then
1080 l_valueset_name := p_value_set;
1081 end if;
1082
1083 l_segment:= fnd_flex_key_api.new_segment
1084 (flexfield => l_flexfield
1085 ,structure => l_structure
1086 ,segment_name => p_segment_name
1087 ,description => null
1088 ,column_name => p_column_name
1089 ,segment_number => p_segment_number
1090 ,enabled_flag => p_enabled_flag
1091 ,displayed_flag => p_displayed_flag
1092 ,indexed_flag => p_indexed_flag
1093 ,value_set => l_valueset_name
1094 ,default_type => null
1095 ,default_value => null
1096 ,required_flag => 'N'
1097 ,security_flag => 'N'
1098 ,display_size => p_display_size
1099 ,description_size => p_description_size
1100 ,concat_size => p_concat_size
1101 ,lov_prompt => p_lov_prompt
1102 ,window_prompt => p_window_prompt);
1103
1104 hr_utility.set_location(l_proc, 100);
1105
1106 hr_utility.trace(p_segment_name);
1107 BEGIN
1108 hr_utility.set_location(l_proc, 110);
1109 fnd_flex_key_api.add_segment(flexfield => l_flexfield
1110 ,structure => l_structure
1111 ,segment => l_segment);
1112 --
1113 --assign qualifiers for CMP and COST KF.
1114 --
1115
1116 if p_flex_code = 'CMP' and p_appl_short_name = 'PER' then
1117 hr_utility.trace('Assigning Qualifiers 111');
1118 fnd_flex_key_api.assign_qualifier(flexfield => l_flexfield,
1119 structure => l_structure,
1120 segment => l_segment,
1121 flexfield_qualifier => 'Default Attribute',
1122 enable_flag => 'Y');
1123 hr_utility.trace('Assigned Qualifier CMP : Default Attribute');
1124
1125 fnd_flex_key_api.assign_qualifier(flexfield => l_flexfield,
1126 structure => l_structure,
1127 segment => l_segment,
1128 flexfield_qualifier => 'Others',
1129 enable_flag => 'Y');
1130 end if;
1131
1132 if p_flex_code = 'COST' and p_appl_short_name = 'PAY' then
1133 fnd_flex_key_api.assign_qualifier(flexfield => l_flexfield,
1134 structure => l_structure,
1135 segment => l_segment,
1136 flexfield_qualifier => 'ASSIGNMENT',
1137 enable_flag => 'Y');
1138 hr_utility.trace('assigned qualifier COST : ASSIGNMENT');
1139
1140 fnd_flex_key_api.assign_qualifier(flexfield => l_flexfield,
1141 structure => l_structure,
1142 segment => l_segment,
1143 flexfield_qualifier => 'BALANCING',
1144 enable_flag => 'Y');
1145 hr_utility.trace('assigned qualifier COST : BALANCING');
1146
1147 fnd_flex_key_api.assign_qualifier(flexfield => l_flexfield,
1148 structure => l_structure,
1149 segment => l_segment,
1150 flexfield_qualifier => 'ELEMENT',
1151 enable_flag => 'Y');
1152 hr_utility.trace('assigned qualifier COST : ELEMENT');
1153
1154 fnd_flex_key_api.assign_qualifier(flexfield => l_flexfield,
1155 structure => l_structure,
1156 segment => l_segment,
1157 flexfield_qualifier => 'ELEMENT ENTRY',
1158 enable_flag => 'Y');
1159 hr_utility.trace('assigned qualifier COST : ELEMENT ENTRY');
1160
1161 fnd_flex_key_api.assign_qualifier(flexfield => l_flexfield,
1162 structure => l_structure,
1163 segment => l_segment,
1164 flexfield_qualifier => 'ORGANIZATION',
1165 enable_flag => 'Y');
1166 hr_utility.trace('assigned qualifier COST : ORGANIZATION');
1167
1168 fnd_flex_key_api.assign_qualifier(flexfield => l_flexfield,
1169 structure => l_structure,
1170 segment => l_segment,
1171 flexfield_qualifier => 'PAYROLL',
1172 enable_flag => 'Y');
1173 hr_utility.trace('assigned qualifier COST : PAYROLL');
1174 end if;
1175
1176 EXCEPTION
1177 when others then
1178 hr_utility.trace(substr(fnd_flex_key_api.message,1,256));
1179 END;
1180
1181 END;
1182
1183 END create_flex_segments;
1184 /* --------------------------------------------------------------------------
1185 -- Name : write_log
1186 -- Purpose : This function write logfile for enterprise structures configuration
1187 -- loader program.
1188 -- Arguments : p_message
1189 -- p_write_to_log_flag
1190 -------------------------------------------------------------------------- */
1191
1192
1193 PROCEDURE write_log(p_message in varchar2
1194 ,p_write_to_log_flag in boolean default TRUE) IS
1195
1196 l_proc varchar2(72) := g_package || 'write_log';
1197
1198 BEGIN
1199
1200 hr_utility.trace(p_message);
1201 if p_write_to_log_flag then
1202 fnd_file.put_line(fnd_file.log, p_message);
1203 end if;
1204 END write_log;
1205
1206 /* --------------------------------------------------------------------------
1207 -- Name : get_le_bg_name
1208 -- Purpose : This function returns name of LE business group for a given
1209 -- configuration
1210 -- Arguments : p_configuration_code
1211 -- p_legal_entity_name
1212 -------------------------------------------------------------------------- */
1213
1214 FUNCTION get_le_bg_name(p_configuration_code in varchar2
1215 ,p_legal_entity_name in varchar2)
1216 RETURN varchar2 IS
1217
1218 cursor csr_le_bg_name(cp_configuration_code in varchar2
1219 ,cp_legal_entity_name in varchar2) IS
1220 select legal_entity_country
1221 from per_ri_config_legal_entity_v
1222 where configuration_code = cp_configuration_code
1223 and legal_entity_name = cp_legal_entity_name;
1224
1225 l_le_business_group_name per_ri_config_information.config_information1%type;
1226 l_legal_entity_country_name per_ri_config_information.config_information1%type;
1227
1228 l_proc varchar2(72) := g_package || 'get_le_bg_name';
1229 l_error_message varchar2(360);
1230
1231 BEGIN
1232 hr_utility.set_location('Entering:'|| l_proc, 10);
1233
1234 open csr_le_bg_name(p_configuration_code
1235 ,p_legal_entity_name);
1236 loop
1237 fetch csr_le_bg_name into l_legal_entity_country_name;
1238 exit when csr_le_bg_name%NOTFOUND;
1239 l_le_business_group_name := per_ri_config_utilities.business_group_decision
1240 (p_configuration_code
1241 ,l_legal_entity_country_name);
1242
1243 hr_utility.trace('l_legal_entity_country_name = ' || l_legal_entity_country_name);
1244 hr_utility.trace('l_le_business_group_name = ' || l_le_business_group_name);
1245 end loop;
1246 close csr_le_bg_name;
1247 hr_utility.set_location(' Leaving:'|| l_proc, 10);
1248 return l_le_business_group_name;
1249
1250 EXCEPTION
1251 when others then
1252 l_error_message := 'Error in ' || l_proc;
1253 hr_utility.trace(l_error_message || '-' || sqlerrm);
1254 hr_utility.set_location(' Leaving:'|| l_proc, 500);
1255 hr_utility.raise_error;
1256
1257 END get_le_bg_name;
1258
1259
1260 /* --------------------------------------------------------------------------
1261 -- Name : get_oc_bg_name
1262 -- Purpose : This function returns name of OC business group for a given
1263 -- configuration
1264 -- Arguments : p_configuration_code
1265 -- p_legal_entity_name
1266 -------------------------------------------------------------------------- */
1267
1268 FUNCTION get_oc_bg_name(p_configuration_code in varchar2
1269 ,p_operating_company_name in varchar2) RETURN varchar2 IS
1270
1271 cursor csr_oct_bg_name(cp_configuration_code in varchar2
1272 ,cp_operating_company_name in varchar2) IS
1273 select operating_company_hq_country
1274 from per_ri_config_oper_comp_v
1275 where configuration_code = cp_configuration_code
1276 and operating_company_name = cp_operating_company_name;
1277
1278 l_oc_business_group_name per_ri_config_information.config_information1%type;
1279 l_operating_company_hq_country per_ri_config_information.config_information1%type;
1280
1281 l_proc varchar2(72) := g_package || 'get_oc_bg_name';
1282 l_error_message varchar2(360);
1283 BEGIN
1284
1285 hr_utility.set_location('Entering:'|| l_proc, 10);
1286
1287 open csr_oct_bg_name(p_configuration_code
1288 ,p_operating_company_name);
1289 -- if country selected is not there put that in international, if int is not created then put it for hq bg
1290 loop
1291 fetch csr_oct_bg_name into l_operating_company_hq_country;
1292 exit when csr_oct_bg_name%NOTFOUND;
1293 l_oc_business_group_name := per_ri_config_utilities.business_group_decision
1294 (p_configuration_code
1295 ,l_operating_company_hq_country);
1296
1297 hr_utility.trace('l_operating_company_hq_country = ' || l_operating_company_hq_country);
1298 hr_utility.trace('l_oc_business_group_name = ' || l_oc_business_group_name);
1299 end loop;
1300 close csr_oct_bg_name;
1301 hr_utility.set_location(' Leaving:'|| l_proc, 10);
1302 return l_oc_business_group_name;
1303
1304 EXCEPTION
1305 when others then
1306 l_error_message := 'Error in ' || l_proc;
1307 hr_utility.trace(l_error_message || '-' || sqlerrm);
1308 hr_utility.set_location(' Leaving:'|| l_proc, 500);
1309 hr_utility.raise_error;
1310
1311 END get_oc_bg_name;
1312
1313 /* --------------------------------------------------------------------------
1314 -- Name : get_enterprise_bg_name
1315 -- Purpose : This function returns name of enterprise business group for a
1316 -- given configuration.
1317 -- Arguments : p_configuration_code
1318 -- p_enterprise_name
1319 -------------------------------------------------------------------------- */
1320 FUNCTION get_enterprise_bg_name(p_configuration_code in varchar2
1321 ,p_enterprise_name in varchar2) RETURN varchar2 IS
1322
1323 cursor csr_ent_bg_name(cp_configuration_code in varchar2
1324 ,cp_enterprise_name in varchar2) IS
1325 select enterprise_headquarter_country
1326 from per_ri_config_enterprise_v
1327 where configuration_code = cp_configuration_code
1328 and enterprise_name = cp_enterprise_name;
1329
1330 l_business_group_name per_ri_config_information.config_information1%type;
1331 l_enterprise_hq_country per_ri_config_information.config_information1%type;
1332
1333 l_proc varchar2(72) := g_package || 'get_enterprise_bg_name';
1334 l_error_message varchar2(360);
1335
1336 BEGIN
1337
1338 hr_utility.set_location('Entering:'|| l_proc, 10);
1339
1340 open csr_ent_bg_name(p_configuration_code
1341 ,p_enterprise_name);
1342 loop
1343 fetch csr_ent_bg_name into
1344 l_enterprise_hq_country;
1345 exit when csr_ent_bg_name%NOTFOUND;
1346 l_business_group_name := per_ri_config_utilities.business_group_decision
1347 (p_configuration_code
1348 ,l_enterprise_hq_country);
1349
1350 hr_utility.trace('l_enterprise_hq_country = ' || l_enterprise_hq_country);
1351 hr_utility.trace('l_business_group_name = ' || l_business_group_name);
1352 hr_utility.set_location(' Leaving:'|| l_proc, 30);
1353 return l_business_group_name;
1354 end loop;
1355 close csr_ent_bg_name;
1356
1357 EXCEPTION
1358 when others then
1359 l_error_message := 'Error in ' || l_proc;
1360 hr_utility.trace(l_error_message || '-' || sqlerrm);
1361 hr_utility.set_location(' Leaving:'|| l_proc, 500);
1362 hr_utility.raise_error;
1363
1364 END get_enterprise_bg_name;
1365
1366 /* --------------------------------------------------------------------------
1367 -- Name : get_config_location_code
1368 -- Purpose : This function returns location code for a location id
1369 -- Arguments : p_legislation_code
1370 -- p_application_short_name
1371 -------------------------------------------------------------------------- */
1372
1373 FUNCTION get_config_location_code(p_configuration_code in varchar2
1374 ,p_location_id in number)
1375 RETURN varchar2 IS
1376
1377 cursor csr_location(cp_configuration_code in varchar2
1378 ,cp_location_id in number) IS
1379 select location_code
1380 from per_ri_config_locations
1381 where configuration_code = cp_configuration_code
1382 and location_id = cp_location_id;
1383
1384 l_location_code per_ri_config_information.config_information1%type;
1385
1386 l_proc varchar2(72) := g_package || 'get_config_location_code';
1387 l_error_message varchar2(360);
1388
1389 BEGIN
1390
1391 hr_utility.set_location('Entering:'|| l_proc, 10);
1392
1393 open csr_location(p_configuration_code
1394 ,p_location_id);
1395 loop
1396 fetch csr_location into l_location_code;
1397 exit when csr_location%NOTFOUND;
1398 hr_utility.set_location(' Leaving:'|| l_proc, 30);
1399 return per_ri_config_utilities.return_config_entity_name(l_location_code);
1400 end loop;
1401 close csr_location;
1402
1403 EXCEPTION
1404 when others then
1405 l_error_message := 'Error in ' || l_proc;
1406 hr_utility.trace(l_error_message || '-' || sqlerrm);
1407 hr_utility.set_location(' Leaving:'|| l_proc, 500);
1408 hr_utility.raise_error;
1409
1410 END get_config_location_code;
1411
1412 /* --------------------------------------------------------------------------
1413 -- Name : mandatory_org_info_types
1414 -- Purpose : This function returns if any mandatory org info types are
1415 -- are defined for given legilsationa and org class.
1416 -- Arguments : p_legislation_code
1417 -- p_org_classification
1418 -------------------------------------------------------------------------- */
1419
1420 FUNCTION mandatory_org_info_types(p_legislation_code in varchar2
1421 ,p_org_classification in varchar2)
1422 RETURN BOOLEAN IS
1423
1424 cursor csr_mandatory_org_info_types(cp_legislation_code in varchar2
1425 ,cp_org_classification in varchar2) IS
1426 select 'X'
1427 from hr_org_info_types_by_class class,
1428 hr_org_information_types type
1429 where class.org_information_type = type.org_information_type
1430 and class.mandatory_flag = 'Y'
1431 and type.legislation_code = cp_legislation_code
1432 and class.org_classification = cp_org_classification;
1433
1434 l_proc varchar2(72) := g_package || 'mandatory_org_info_types_defined';
1435 l_error_message varchar2(360);
1436 l_defined boolean default TRUE;
1437 l_found varchar2(1);
1438
1439 BEGIN
1440
1441 hr_utility.set_location('Entering:'|| l_proc, 10);
1442
1443 open csr_mandatory_org_info_types(p_legislation_code
1444 ,p_org_classification);
1445 fetch csr_mandatory_org_info_types into l_found;
1446 if csr_mandatory_org_info_types%FOUND then
1447 hr_utility.trace('Mandatory Org Info Types ARE defined for ' || p_legislation_code);
1448 l_defined := TRUE;
1449 else
1450 hr_utility.trace('Mandatory Org Info Types NOT defined for ' || p_legislation_code);
1451 l_defined := FALSE;
1452 end if;
1453 close csr_mandatory_org_info_types;
1454
1455 hr_utility.trace('p_legislation_code = ' || p_legislation_code);
1456 hr_utility.set_location(' Leaving:'|| l_proc, 10);
1457
1458 return l_defined;
1459
1460 EXCEPTION
1461 when others then
1462 l_error_message := 'Error in ' || l_proc;
1463 hr_utility.trace(l_error_message || '-' || sqlerrm);
1464 hr_utility.set_location(' Leaving:'|| l_proc, 500);
1465 hr_utility.raise_error;
1466
1467 END mandatory_org_info_types;
1468
1469 /* --------------------------------------------------------------------------
1470 -- Name : check_currency_enabled
1471 -- Purpose : This function returns if currecny is enabled.
1472 -- Arguments : p_legislation_code
1473 --
1474 -------------------------------------------------------------------------- */
1475
1476 FUNCTION check_currency_enabled(p_legislation_code in varchar2)
1477 RETURN varchar2 IS
1478
1479 cursor csr_country_currency(cp_legislation_code in varchar2) IS
1480 select enabled_flag
1481 from fnd_currencies
1482 where issuing_territory_code = cp_legislation_code;
1483
1484 l_proc varchar2(72) := g_package || 'check_currency_enabled';
1485 l_error_message varchar2(360);
1486 l_enabled_flag varchar2(30);
1487
1488 BEGIN
1489
1490 hr_utility.set_location('Entering:'|| l_proc, 10);
1491
1492 open csr_country_currency(p_legislation_code);
1493 fetch csr_country_currency into l_enabled_flag;
1494 if csr_country_currency%FOUND then
1495 hr_utility.trace('l_enabled_flag = ' || l_enabled_flag);
1496 else
1497 hr_utility.trace('l_enabled_flag NOT DEFIINED ');
1498 end if;
1499 close csr_country_currency;
1500
1501 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1502
1503 return l_enabled_flag;
1504
1505 EXCEPTION
1506 when others then
1507 l_error_message := 'Error in ' || l_proc;
1508 hr_utility.trace(l_error_message || '-' || sqlerrm);
1509 hr_utility.set_location(' Leaving:'|| l_proc, 500);
1510 hr_utility.raise_error;
1511
1512 END check_currency_enabled;
1513
1514
1515 /* --------------------------------------------------------------------------
1516 -- Name : get_country_currency
1517 -- Purpose : This function returns county currency.
1518 -- Arguments : p_legislation_code
1519 --
1520 -------------------------------------------------------------------------- */
1521
1522 FUNCTION get_country_currency(p_legislation_code in varchar2)
1523 RETURN varchar2 IS
1524
1525 cursor csr_country_currency(cp_legislation_code in varchar2) IS
1526 select currency_code
1527 from pay_leg_setup_defaults
1528 where legislation_code = cp_legislation_code;
1529
1530 l_proc varchar2(72) := g_package || 'get_country_currency';
1531 l_error_message varchar2(360);
1532 l_currency_code varchar2(30);
1533
1534 BEGIN
1535
1536 hr_utility.set_location('Entering:'|| l_proc, 10);
1537
1538 open csr_country_currency(p_legislation_code);
1539 fetch csr_country_currency into l_currency_code;
1540 if csr_country_currency%FOUND then
1541 hr_utility.trace('l_currency_code = ' || l_currency_code);
1542 else
1543 hr_utility.trace('l_currency_code NOT DEFIINED ');
1544 end if;
1545 close csr_country_currency;
1546
1547 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1548
1549 return l_currency_code;
1550
1551 EXCEPTION
1552 when others then
1553 l_error_message := 'Error in ' || l_proc;
1554 hr_utility.trace(l_error_message || '-' || sqlerrm);
1555 hr_utility.set_location(' Leaving:'|| l_proc, 500);
1556 hr_utility.raise_error;
1557
1558 END get_country_currency;
1559
1560 /* --------------------------------------------------------------------------
1561 -- Name : enable_country_currency
1562 -- Purpose : This procedure enables currecny of a given legislation.
1563 -- Arguments : p_legislation_code
1564 --
1565 -------------------------------------------------------------------------- */
1566
1567 PROCEDURE enable_country_currency(p_legislation_code in varchar2) IS
1568
1569 cursor csr_country_currency(cp_legislation_code in varchar2) IS
1570 select currency_code
1571 from pay_leg_setup_defaults
1572 where legislation_code = cp_legislation_code;
1573
1574 l_proc varchar2(72) := g_package || 'enable_country_currency';
1575 l_currency_code varchar2(30);
1576 l_error_message varchar2(360);
1577
1578 BEGIN
1579
1580 hr_utility.set_location('Entering:'|| l_proc, 10);
1581 hr_utility.trace('p_legislation_code = '|| p_legislation_code);
1582
1583 open csr_country_currency(p_legislation_code);
1584 fetch csr_country_currency into l_currency_code;
1585 if csr_country_currency%FOUND then
1586 update fnd_currencies set enabled_flag = 'Y' where currency_code = l_currency_code;
1587 hr_utility.trace('Enabled Currecny = ' || l_currency_code);
1588 else
1589 hr_utility.trace('l_currency_code NOT DEFIINED ');
1590 end if;
1591 close csr_country_currency;
1592
1593 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1594
1595 EXCEPTION
1596 when others then
1597 l_error_message := 'Error in ' || l_proc;
1598 hr_utility.trace(l_error_message || '-' || sqlerrm);
1599 hr_utility.set_location(' Leaving:'|| l_proc, 500);
1600 hr_utility.raise_error;
1601
1602 END enable_country_currency;
1603
1604 /* --------------------------------------------------------------------------
1605 -- Name : create_valueset
1606 -- Purpose : This procedure creates a value set
1607 -- Arguments : p_valueset_name
1608 -- p_valueset_type
1609 -------------------------------------------------------------------------- */
1610
1611 PROCEDURE create_valueset(p_valueset_name in varchar2
1612 ,p_valueset_type in varchar2) IS
1613
1614 l_proc varchar2(72) := g_package || 'create_valueset';
1615 l_error_message varchar2(360);
1616 l_log_message varchar2(360);
1617
1618 l_security_available varchar2(1) default 'N';
1619 l_enable_longlist varchar2(1) default 'Y';
1620 l_format_type varchar2(1) default 'C';
1621 l_maximum_size number(9) default 60;
1622 l_precision number(2) default null;
1623 l_numbers_only varchar2(1) default 'N';
1624 l_uppercase_only varchar2(1) default 'N';
1625 l_right_justify_zero_fill varchar2(1) default 'N';
1626 l_min_value varchar2(150) default null;
1627 l_max_value varchar2(150) default null;
1628 l_description varchar2(240);
1629 l_value_set_exists boolean;
1630
1631 l_valueset_seq number(9);
1632 l_valueset_name fnd_flex_value_sets.flex_value_set_name%type;
1633
1634 BEGIN
1635
1636 hr_utility.set_location('Entering:'|| l_proc, 10);
1637
1638 fnd_flex_val_api.set_session_mode('customer_data');
1639
1640 if p_valueset_type = 'CHAR' then
1641 l_format_type := 'C';
1642 l_maximum_size := 60;
1643 elsif p_valueset_type = 'NUMBER' then
1644 l_format_type := 'N';
1645 l_maximum_size := 15;
1646 elsif p_valueset_type = 'CHARLOV' then
1647 l_format_type := 'C';
1648 l_maximum_size := 60;
1649 elsif p_valueset_type = 'NUMLOV' then
1650 l_format_type := 'N';
1651 l_maximum_size := 15;
1652 elsif p_valueset_type = 'DATE' then
1653 l_format_type := 'D';
1654 l_maximum_size := 20;
1655 else
1656 l_format_type := 'C';
1657 l_maximum_size := 60;
1658 end if;
1659
1660 -- Create Character Value Set
1661 l_description := 'This value set is generated by Enterprise Structures Configuration '
1662 || 'This will initially be empty and can '
1663 || 'be populated using the load reference data';
1664
1665 l_valueset_name := p_valueset_name;
1666 l_value_set_exists := fnd_flex_val_api.valueset_exists(value_set => l_valueset_name);
1667
1668 hr_utility.set_location(l_proc, 20);
1669 if not (l_value_set_exists) then
1670 hr_utility.set_location(l_proc, 20);
1671 fnd_flex_val_api.create_valueset_independent
1672 (value_set_name => p_valueset_name
1673 ,description => l_description
1674 ,security_available => l_security_available
1675 ,enable_longlist => l_enable_longlist
1676 ,format_type => l_format_type
1677 ,maximum_size => l_maximum_size
1678 ,precision => l_precision
1679 ,numbers_only => l_numbers_only
1680 ,uppercase_only => l_uppercase_only
1681 ,right_justify_zero_fill => l_right_justify_zero_fill
1682 ,min_value => l_min_value
1683 ,max_value => l_max_value);
1684 l_log_message := 'Created VALUESET ' || p_valueset_name;
1685 per_ri_config_utilities.write_log(p_message => l_log_message);
1686 end if;
1687
1688 hr_utility.set_location(' Leaving:'|| l_proc, 30);
1689
1690 EXCEPTION
1691 when others then
1692 l_error_message := 'Error in ' || l_proc;
1693 hr_utility.trace(l_error_message || '-' || sqlerrm);
1694 hr_utility.set_location(' Leaving:'|| l_proc, 500);
1695 hr_utility.raise_error;
1696
1697 End create_valueset;
1698
1699 /* --------------------------------------------------------------------------
1700 -- Name : check_org_class_lookup_tag
1701 -- Purpose : This function returns if org class lookup tag is enabled.
1702 -- Arguments : p_legislation_code
1703 -- p_lookup_code
1704 -------------------------------------------------------------------------- */
1705
1706 FUNCTION check_org_class_lookup_tag(p_legislation_code in varchar2
1707 ,p_lookup_code in varchar2)
1708 RETURN boolean IS
1709 cursor csr_org_class(cp_lookup_code in varchar2) IS
1710 select tag
1711 from fnd_lookup_values
1712 where lookup_type = 'ORG_CLASS'
1713 and lookup_code = cp_lookup_code
1714 and language = hr_api.userenv_lang;
1715
1716 l_tag fnd_lookup_values.tag%type;
1717 l_enabled boolean default false;
1718 l_plus_tag varchar2(30);
1719
1720 l_proc varchar2(72) := g_package || 'check_org_class_lookup_tag';
1721 l_error_message varchar2(360);
1722
1723 BEGIN
1724 hr_utility.set_location('Entering:'|| l_proc, 10);
1725
1726 if p_lookup_code = 'IN_COMPANY' then
1727 l_enabled := FALSE;
1728 return l_enabled;
1729 end if;
1730
1731 open csr_org_class(p_lookup_code);
1732 fetch csr_org_class into l_tag;
1733 if l_tag is null then
1734 l_enabled := TRUE;
1735 end if;
1736 l_plus_tag := '+' || p_legislation_code;
1737
1738 if l_tag is NOT NULL and instr(l_tag,l_plus_tag)> 0 then
1739 hr_utility.set_location(l_proc, 30);
1740 l_enabled := TRUE;
1741 end if;
1742 close csr_org_class;
1743 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1744 return l_enabled;
1745
1746 EXCEPTION
1747 when others then
1748 l_error_message := 'Error in ' || l_proc;
1749 hr_utility.trace(l_error_message || '-' || sqlerrm);
1750 hr_utility.set_location(' Leaving:'|| l_proc, 500);
1751 hr_utility.raise_error;
1752
1753 END check_org_class_lookup_tag;
1754
1755 /* --------------------------------------------------------------------------
1756 -- Name : get_selected_country_list
1757 -- Purpose : This function returns name of selected country.
1758 -- Arguments : p_configuration_code
1759 -- p_config_info_category
1760 -- p_reg_var_name
1761 -- p_selected_list
1762 -------------------------------------------------------------------------- */
1763
1764 PROCEDURE get_selected_country_list(p_configuration_code varchar2
1765 ,p_config_info_category varchar2
1766 ,p_reg_var_name varchar2
1767 ,p_country_list out nocopy varchar2
1768 ,p_selected_list out nocopy varchar2) IS
1769
1770 cursor get_country_list IS
1771 select distinct per_ri_config_utilities.business_group_decision(configuration_code, config_information1) BusinessGroup
1772 from per_ri_config_information pci
1773 where config_information_category = 'CONFIG COUNTRY'
1774 and configuration_code = p_configuration_code;
1775
1776 cursor get_country_name(p_territory_code varchar2) IS
1777 select territory_short_name
1778 from fnd_territories_vl
1779 where territory_code = p_territory_code;
1780
1781 cursor get_selected_list IS
1782 select distinct config_information2
1783 from per_ri_config_information pci
1784 where configuration_code = p_configuration_code
1785 and config_information_category = p_config_info_category
1786 and config_information1 <> p_reg_var_name;
1787
1788 l_ret_string varchar2(10000);
1789 l_country_name varchar2(100);
1790 l_business_group varchar2(60);
1791
1792 l_proc varchar2(72) := g_package || 'get_selected_country_list';
1793 l_error_message varchar2(360);
1794
1795 BEGIN
1796 hr_utility.set_location('Entering:'|| l_proc, 10);
1797 FOR i in get_country_list LOOP
1798 open get_country_name(i.businessgroup);
1799 fetch get_country_name into l_country_name;
1800 hr_utility.trace('l_country_name = ' || l_country_name);
1801 l_business_group := i.BusinessGroup;
1802 if get_country_name%NOTFOUND then
1803 l_business_group := 'INT';
1804 l_country_name := 'International';
1805 end if;
1806 close get_country_name;
1807
1808 l_ret_string := l_ret_string || '^' || l_business_group ||'-' || l_country_name;
1809 hr_utility.trace('l_ret_string = ' || l_ret_string);
1810 END LOOP;
1811
1812 p_country_list := ltrim(l_ret_string,',');
1813 hr_utility.trace('p_country_list = ' || p_country_list);
1814 FOR i in get_selected_list LOOP
1815 p_selected_list := p_selected_list ||'^' || i.config_information2;
1816 END LOOP;
1817
1818 p_selected_list := ltrim(p_selected_list,',');
1819 hr_utility.trace('p_selected_list = ' || p_selected_list);
1820 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1821
1822 EXCEPTION
1823 when others then
1824 l_error_message := 'Error in ' || l_proc;
1825 hr_utility.trace(l_error_message || '-' || sqlerrm);
1826 hr_utility.set_location(' Leaving:'|| l_proc, 500);
1827 hr_utility.raise_error;
1828
1829 END get_selected_country_list;
1830
1831
1832 /* --------------------------------------------------------------------------
1833 -- Name : get_display_country_list
1834 -- Purpose : This function returns country list to be displayed.
1835 -- Arguments : p_configuration_code
1836 -- p_reg_var_name
1837 -- p_config_info_category
1838 -------------------------------------------------------------------------- */
1839
1840 FUNCTION get_display_country_list(p_configuration_code varchar2
1841 ,p_reg_var_name varchar2
1842 ,p_config_info_category varchar2)
1843 RETURN varchar2 IS
1844 cursor get_selected_country IS
1845 select distinct config_information2 business_group
1846 from per_ri_config_information pci
1847 where configuration_code = p_configuration_code
1848 and config_information_category = p_config_info_category
1849 and config_information1 = p_reg_var_name;
1850
1851 cursor get_bg_display_name(p_territory_code varchar2) IS
1852 select territory_short_name
1853 from fnd_territories_vl
1854 where territory_code = p_territory_code;
1855
1856 l_bg_name varchar2(100);
1857 l_ret_string varchar2(2000);
1858 l_proc varchar2(72) := g_package || 'get_display_country_list';
1859 l_error_message varchar2(360);
1860
1861 BEGIN
1862 hr_utility.set_location('Entering:'|| l_proc, 10);
1863 for i in get_selected_country loop
1864 open get_bg_display_name(i.business_group);
1865 fetch get_bg_display_name into l_bg_name;
1866 hr_utility.trace(i.business_group || '-' || l_bg_name);
1867 if (get_bg_display_name%notfound) then
1868 l_bg_name := 'International';
1869 end if;
1870 l_ret_string := l_ret_string || ',' || l_bg_name;
1871 close get_bg_display_name;
1872 end loop;
1873 hr_utility.trace('l_ret_string = ' || l_ret_string);
1874 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1875 return ltrim(l_ret_string,',');
1876
1877 EXCEPTION
1878 when others then
1879 l_error_message := 'Error in ' || l_proc;
1880 hr_utility.trace(l_error_message || '-' || sqlerrm);
1881 hr_utility.set_location(' Leaving:'|| l_proc, 500);
1882 hr_utility.raise_error;
1883
1884 END get_display_country_list;
1885
1886 /* --------------------------------------------------------------------------
1887 -- Name : get_country_list
1888 -- Purpose : This function returns country list.
1889 -- Arguments : p_configuration_code
1890 -- p_reg_var_name
1891 -- p_config_info_category
1892 -------------------------------------------------------------------------- */
1893
1894 FUNCTION get_country_list(p_configuration_code varchar2
1895 ,p_reg_var_name varchar2
1896 ,p_config_info_category varchar2)
1897 RETURN varchar2 IS
1898
1899 cursor get_selected_list IS
1900 select config_information2 country
1901 from per_ri_config_information pci
1902 where configuration_code = p_configuration_code
1903 and config_information_category = p_config_info_category
1904 and config_information1 = p_reg_var_name;
1905
1906 l_proc varchar2(72) := g_package || 'jpg_define';
1907 l_ret_string varchar2(2000);
1908 l_error_message varchar2(360);
1909
1910 BEGIN
1911 hr_utility.set_location('Entering:'|| l_proc, 10);
1912 FOR i in get_selected_list LOOP
1913 l_ret_string := l_ret_string || ',' || i.country;
1914 end loop;
1915 hr_utility.trace('l_ret_string = ' || l_ret_string);
1916 hr_utility.set_location(' Leaving:'|| l_proc, 10);
1917 return ltrim(l_ret_string,',');
1918 EXCEPTION
1919 when others then
1920 l_error_message := 'Error in ' || l_proc;
1921 hr_utility.trace(l_error_message || '-' || sqlerrm);
1922 hr_utility.set_location(' Leaving:'|| l_proc, 500);
1923 hr_utility.raise_error;
1924
1925 END get_country_list;
1926
1927 /* --------------------------------------------------------------------------
1928 -- Name : freeze_and_compile_flexfield
1929 -- Purpose : This function freeze and compile a given keyflex.
1930 -- Arguments : p_appl_short_Name
1931 -- p_flex_code
1932 -- p_structure_code
1933 -------------------------------------------------------------------------- */
1934
1935 PROCEDURE freeze_and_compile_flexfield
1936 (p_appl_short_Name in varchar2
1937 ,p_flex_code in varchar2
1938 ,p_structure_code in varchar2) IS
1939
1940 cursor flex_num_cursor IS
1941 select fifs.id_flex_num
1942 from fnd_application fa, fnd_id_flex_structures_vl fifs
1943 where fa.application_short_name = p_appl_short_name
1944 and fa.application_id = fifs.application_id
1945 and fifs.id_flex_code = p_flex_code
1946 and fifs.id_flex_structure_code = p_structure_code;
1947
1948 l_proc varchar2(72) := g_package || 'freeze_and_compile_flexfield';
1949 l_error_message varchar2(360);
1950 l_log_message varchar2(360);
1951 l_flexfield fnd_flex_key_api.flexfield_type;
1952 l_structure fnd_flex_key_api.structure_type;
1953 l_new_structure fnd_flex_key_api.structure_type;
1954 l_id_flex_num fnd_id_flex_structures_vl.id_flex_num%type;
1955 l_request_id number(9);
1956
1957 BEGIN
1958 hr_utility.set_location('Entering:'|| l_proc, 10);
1959
1960 open flex_num_cursor;
1961 fetch flex_num_cursor into l_id_flex_num;
1962 close flex_num_cursor;
1963
1964 fnd_flex_key_api.set_session_mode('customer_data');
1965
1966 l_flexfield := fnd_flex_key_api.find_flexfield
1967 (appl_short_name => p_appl_short_name
1968 ,flex_code => p_flex_code );
1969
1970 hr_utility.set_location(l_proc, 20);
1971
1972 l_structure := fnd_flex_key_api.find_structure
1973 (flexfield => l_flexfield
1974 ,structure_code => p_structure_code );
1975
1976 hr_utility.set_location(l_proc, 30);
1977
1978 --
1979 -- freeze flexfield
1980 --
1981 l_new_structure := l_structure;
1982
1983 l_new_structure.freeze_flag := 'Y';
1984
1985 hr_utility.set_location('Entering:'|| l_proc, 30);
1986
1987 fnd_flex_key_api.modify_structure(l_flexfield, l_structure,l_new_structure);
1988
1989 --
1990 -- compile flexfield
1991 --
1992 fnd_global.apps_initialize(user_id => fnd_global.user_id,
1993 resp_id => fnd_global.resp_id,
1994 resp_appl_id => fnd_global.resp_appl_id,
1995 security_group_id => fnd_global.security_group_id);
1996
1997 l_request_id := fnd_request.submit_request(
1998 'FND',
1999 'FDFCMPK',
2000 'Compile Key Flexfield',
2001 NULL, --start_time (varchar2)
2002 FALSE, --sub_request
2003 'K', --
2004 p_appl_short_name,
2005 p_flex_code,
2006 l_id_flex_num,
2007 chr(0), '', '', '', '', '',
2008 '', '', '', '', '', '', '', '', '', '',
2009 '', '', '', '', '', '', '', '', '', '',
2010 '', '', '', '', '', '', '', '', '', '',
2011 '', '', '', '', '', '', '', '', '', '',
2012 '', '', '', '', '', '', '', '', '', '',
2013 '', '', '', '', '', '', '', '', '', '',
2014 '', '', '', '', '', '', '', '', '', '',
2015 '', '', '', '', '', '', '', '', '', '',
2016 '', '', '', '', '', '', '', '', '', '');
2017
2018 l_log_message := 'Submitted Concurrent Request to Compile KEY FLEX ' || p_appl_short_name
2019 || ' ' || p_flex_code || ' ' || p_structure_code;
2020 per_ri_config_utilities.write_log(p_message => l_log_message);
2021
2022 hr_utility.set_location(' Leaving:'|| l_proc, 50);
2023
2024 EXCEPTION
2025 when others then
2026 null;
2027 -- Ignore for now FOR TESTING
2028 --l_error_message := 'Error in ' || l_proc;
2029 --hr_utility.trace(l_error_message || '-' || sqlerrm);
2030 --hr_utility.set_location(' Leaving:'|| l_proc, 500);
2031 --hr_utility.raise_error;
2032
2033 END freeze_and_compile_flexfield;
2034
2035
2036 /* --------------------------------------------------------------------------
2037 -- Name : get_country_display_name
2038 -- Purpose : This function returns country display name.
2039 -- Arguments : p_territory_code
2040 --
2041 -------------------------------------------------------------------------- */
2042
2043 FUNCTION get_country_display_name(p_territory_code in varchar2)
2044 RETURN varchar2 IS
2045
2046 cursor csr_country(cp_territory_code in varchar2) IS
2047 select territory_short_name
2048 from fnd_territories_vl
2049 where territory_code = p_territory_code;
2050
2051 l_territory_short_name fnd_territories_vl.territory_short_name%type;
2052 l_proc varchar2(72) := g_package || 'get_country_display_name';
2053 l_error_message varchar2(360);
2054
2055 BEGIN
2056 hr_utility.set_location('Entering:'|| l_proc, 10);
2057
2058 if p_territory_code = 'INT' then
2059 hr_utility.set_location(' Leaving:'|| l_proc, 30);
2060 return 'International';
2061 else
2062 open csr_country(p_territory_code);
2063 loop
2064 fetch csr_country into l_territory_short_name;
2065 exit when csr_country%NOTFOUND;
2066 hr_utility.set_location(' Leaving:'|| l_proc, 40);
2067 end loop;
2068 return l_territory_short_name;
2069 end if;
2070
2071 EXCEPTION
2072 when others then
2073 l_error_message := 'Error in ' || l_proc;
2074 hr_utility.trace(l_error_message || '-' || sqlerrm);
2075 hr_utility.set_location(' Leaving:'|| l_proc, 500);
2076 hr_utility.raise_error;
2077
2078 END get_country_display_name;
2079
2080
2081 /* --------------------------------------------------------------------------
2082 -- Name : submit_int_payroll_request
2083 -- Purpose : This procedure submit a concurrent request for running International
2084 -- Payroll process for a specified legislation when HRMs does not
2085 -- PER/PAY support.
2086 -- Arguments : errbuf
2087 -- retcode
2088 -- p_country_tab
2089 -------------------------------------------------------------------------- */
2090
2091 PROCEDURE submit_int_payroll_request
2092 (errbuf out nocopy varchar2
2093 ,retcode out nocopy number
2094 ,p_country_tab in per_ri_config_datapump_entity.country_tab
2095 ,p_technical_summary_mode in boolean default FALSE
2096 ,p_int_hrms_setup_tab in out nocopy
2097 per_ri_config_tech_summary.int_hrms_setup_tab) IS
2098
2099 l_proc varchar2(72) := g_package || 'submit_int_payroll_request';
2100 l_log_message varchar2(360);
2101 l_int_hrms_setup_count number(8) := 0;
2102 l_int_hrms_setup_tab per_ri_config_tech_summary.int_hrms_setup_tab;
2103 l_request_id number(9);
2104 l_legislation_code varchar2(30);
2105 l_currency_code varchar2(30);
2106 l_tax_start_date date;
2107 l_error_message varchar2(360);
2108
2109 cursor csr_defaults (cp_legislation_code in varchar2) IS
2110 select legislation_code,
2111 currency_code,
2112 tax_start_date
2113 from pay_leg_setup_defaults
2114 where legislation_code = cp_legislation_code;
2115
2116 BEGIN
2117 hr_utility.set_location('Entering:'|| l_proc, 10);
2118
2119 if p_country_tab.count > 0 THEN
2120 for i in p_country_tab.first ..
2121 p_country_tab.last LOOP
2122 l_legislation_code := p_country_tab(i).territory_code;
2123 hr_utility.trace('p_country_tab.element = ' || l_legislation_code);
2124 if l_legislation_code <> 'INT' then
2125
2126 if NOT (per_ri_config_utilities.legislation_support(l_legislation_code, 'PAY'))
2127 and NOT (per_ri_config_utilities.legislation_support(l_legislation_code, 'PER')) then
2128
2129 open csr_defaults(l_legislation_code);
2130
2131 fetch csr_defaults into l_legislation_code,l_currency_code,l_tax_start_date;
2132 --
2133 -- Submit 'International HRMS Setup' concurernt request
2134 --
2135 if NOT (p_technical_summary_mode) then
2136 l_request_id := fnd_request.submit_request
2137 (application => 'PAY'
2138 ,program => 'PYINTSTU'
2139 ,description => 'Enterprise Structures Configuration'
2140 ,sub_request => FALSE
2141 ,argument1 => l_legislation_code
2142 ,argument2 => l_currency_code
2143 ,argument3 => to_char(l_tax_start_date,'RRRR/MM/DD')
2144 ,argument4 => 'N'
2145 ,argument5 => null);
2146 else
2147 p_int_hrms_setup_tab(l_int_hrms_setup_count).legislation_code := l_legislation_code;
2148 p_int_hrms_setup_tab(l_int_hrms_setup_count).currency_code := l_currency_code;
2149 p_int_hrms_setup_tab(l_int_hrms_setup_count).tax_start_date := l_tax_start_date;
2150 p_int_hrms_setup_tab(l_int_hrms_setup_count).install_tax_unit := 'N';
2151
2152 l_int_hrms_setup_count := l_int_hrms_setup_count + 1 ;
2153 end if;
2154 close csr_defaults;
2155 l_log_message := 'Created International Payroll run CONCURRENT REQUEST for ' || l_legislation_code;
2156 per_ri_config_utilities.write_log(p_message => l_log_message);
2157 end if;
2158 end if;
2159 END LOOP;
2160 end if;
2161
2162 hr_utility.set_location(' Leaving:'|| l_proc, 40);
2163 EXCEPTION
2164 when others then
2165 l_error_message := 'Error in ' || l_proc;
2166 hr_utility.trace(l_error_message || '-' || sqlerrm);
2167 hr_utility.set_location(' Leaving:'|| l_proc, 500);
2168 hr_utility.raise_error;
2169
2170 END submit_int_payroll_request;
2171
2172
2173 /* --------------------------------------------------------------------------
2174 -- Name : create_security_profile_assign
2175 -- Purpose : This procedure create security profile assignments.
2176 -- Arguments : p_security_profile_tab
2177 --
2178 -------------------------------------------------------------------------- */
2179
2180 PROCEDURE create_security_profile_assign(
2181 p_security_profile_tab in per_ri_config_fnd_hr_entity.security_profile_tab) IS
2182
2183 cursor csr_user IS
2184 select user_id
2185 from fnd_user
2186 where user_name = upper(per_ri_config_utilities.return_config_entity_name_pre
2187 (per_ri_config_main.g_configuration_user_name));
2188
2189 cursor csr_responsibility(cp_responsibility_kay in varchar2) IS
2190 select application_id, responsibility_id
2191 from fnd_responsibility
2192 where responsibility_key = cp_responsibility_kay;
2193
2194 cursor csr_business_group(cp_business_group_name in varchar2) IS
2195 select business_group_id,security_group_id
2196 from per_business_groups
2197 where name = cp_business_group_name;
2198
2199 cursor csr_security_profiles (cp_security_profile_name varchar2)IS
2200 select security_profile_id
2201 from per_security_profiles
2202 where security_profile_name = cp_security_profile_name;
2203
2204 l_sec_profile_assignment_id per_sec_profile_assignments.sec_profile_assignment_id%type;
2205 l_user_id per_sec_profile_assignments.user_id%type;
2206 l_security_group_id per_sec_profile_assignments.security_group_id%type;
2207 l_business_group_id per_sec_profile_assignments.business_group_id%type;
2208 l_security_profile_id per_sec_profile_assignments.security_profile_id%type;
2209 l_responsibility_id per_sec_profile_assignments.responsibility_id%type;
2210 l_responsibility_application_i per_sec_profile_assignments.responsibility_application_id%type;
2211
2212 l_security_profile_name per_business_groups.name%type;
2213 l_responsibility_key fnd_responsibility_vl.responsibility_name%type;
2214 l_ovn number(9);
2215 l_proc varchar2(72) := g_package || 'create_security_profile_assign';
2216 l_log_message varchar2(360);
2217 l_error_message varchar2(360);
2218
2219 BEGIN
2220 hr_utility.set_location('Entering:'|| l_proc, 10);
2221
2222 -- open csr_user;
2223 -- fetch csr_user into l_user_id;
2224 -- close csr_user;
2225 --
2226 -- if p_security_profile_tab.count > 0 then
2227 -- for i in p_security_profile_tab.first ..
2228 -- p_security_profile_tab.last LOOP
2229 -- l_security_profile_name := p_security_profile_tab(i).security_profile_name;
2230 --
2231 --
2232 -- l_responsibility_key := p_security_profile_tab(i).responsibility_key;
2233 --
2234 -- open csr_business_group(l_security_profile_name);
2235 -- fetch csr_business_group into l_business_group_id,
2236 -- l_security_group_id;
2237 -- close csr_business_group;
2238 --
2239 -- open csr_security_profiles(l_security_profile_name);
2240 -- fetch csr_security_profiles into l_security_profile_id;
2241 -- close csr_security_profiles;
2242 --
2243 -- open csr_responsibility(l_responsibility_key);
2244 -- fetch csr_responsibility into l_responsibility_application_i,l_responsibility_id;
2245 -- close csr_responsibility;
2246 --
2247 -- hr_utility.trace('11 l_user_id = ' || l_user_id);
2248 -- hr_utility.trace('11 l_business_group_id = ' || l_business_group_id);
2249 -- hr_utility.trace('11 l_responsibility_id = ' || l_responsibility_id);
2250 -- hr_utility.trace('11 p_responsibility_application_i = ' || l_responsibility_application_i);
2251 --
2252 -- per_sec_profile_asg_api.create_security_profile_asg
2253 -- (p_validate => false
2254 -- ,p_sec_profile_assignment_id => l_sec_profile_assignment_id
2255 -- ,p_user_id => l_user_id
2256 -- ,p_security_group_id => l_security_group_id
2257 -- ,p_business_group_id => l_business_group_id
2258 -- --,p_business_group_id => null
2259 -- ,p_security_profile_id => l_security_profile_id
2260 -- ,p_responsibility_id => l_responsibility_id
2261 -- ,p_responsibility_application_i => l_responsibility_application_i
2262 -- ,p_start_date => g_config_effective_date
2263 -- ,p_end_date => null
2264 -- ,p_object_version_number => l_ovn);
2265 -- hr_utility.trace('l_sec_profile_assignment_id = ' || l_sec_profile_assignment_id);
2266 -- hr_utility.trace('l_user_id = ' || l_user_id);
2267 -- hr_utility.trace('l_sec_profile_assignment_id = ' || l_sec_profile_assignment_id);
2268 -- hr_utility.trace('l_business_group_id = ' || l_business_group_id);
2269 -- hr_utility.trace('l_responsibility_id = ' || l_responsibility_id);
2270 -- hr_utility.trace('p_responsibility_application_i = ' || l_responsibility_application_i);
2271 --
2272 -- l_log_message := 'Created Security Profile Assignment ' || l_business_group_id;
2273 -- per_ri_config_utilities.write_log(p_message => l_log_message);
2274 --
2275 -- end loop;
2276 -- end if;
2277 hr_utility.set_location(' Leaving:'|| l_proc, 100);
2278 EXCEPTION
2279 when others then
2280 l_error_message := 'Error in ' || l_proc;
2281 hr_utility.trace(l_error_message || '-' || sqlerrm);
2282 hr_utility.set_location(' Leaving:'|| l_proc, 500);
2283
2284
2285 END create_security_profile_assign;
2286
2287 /* --------------------------------------------------------------------------
2288 -- Name : update_configuration_status
2289 -- Purpose : This procedure to update configuration status to 'LOADED' once
2290 -- LOader Program has loaded all the data without any issues.
2291 -- Arguments : p_configuration_code
2292 --
2293 -------------------------------------------------------------------------- */
2294
2295 PROCEDURE update_configuration_status(p_configuration_code in varchar2) IS
2296
2297 l_proc varchar2(72) := g_package || 'update_configuration_status';
2298 l_log_message varchar2(360);
2299 l_error_message varchar2(360);
2300
2301 l_configuration_code per_ri_configurations_vl.configuration_code%type;
2302 l_configuration_type per_ri_configurations_vl.configuration_type%type;
2303 l_configuration_status per_ri_configurations_vl.configuration_status%type;
2304 l_configuration_name per_ri_configurations_vl.configuration_name%type;
2305 l_configuration_description per_ri_configurations_vl.configuration_description%type;
2306 l_object_version_number per_ri_configurations_vl.object_version_number%type;
2307
2308 cursor csr_configuration(cp_configuration_code in varchar2) IS
2309 select configuration_code,
2310 configuration_type,
2311 configuration_status,
2312 configuration_name,
2313 configuration_description,
2314 object_version_number
2315 from per_ri_configurations_vl
2316 where configuration_code = p_configuration_code;
2317
2318 BEGIN
2319 hr_utility.set_location('Entering:'|| l_proc, 10);
2320
2321 open csr_configuration(p_configuration_code);
2322
2323 fetch csr_configuration into l_configuration_code
2324 ,l_configuration_type
2325 ,l_configuration_status
2326 ,l_configuration_name
2327 ,l_configuration_description
2328 ,l_object_version_number;
2329
2330 hr_utility.trace('l_object_version_number before =' || l_object_version_number);
2331 if csr_configuration%FOUND then
2332 hr_utility.set_location(' Leaving:'|| l_proc, 20);
2333 per_ri_configuration_api.update_configuration
2334 (p_configuration_code => l_configuration_code
2335 ,p_configuration_type => l_configuration_type
2336 ,p_configuration_status => 'LOADED'
2337 ,p_configuration_name => l_configuration_name
2338 ,p_configuration_description => l_configuration_description
2339 ,p_language_code => hr_api.userenv_lang
2340 ,p_effective_date => null
2341 ,p_object_version_number => l_object_version_number);
2342 hr_utility.trace('l_object_version_number after =' || l_object_version_number);
2343 end if;
2344 hr_utility.set_location(' Leaving:'|| l_proc, 20);
2345 EXCEPTION
2346 when others then
2347 l_error_message := 'Error in ' || l_proc;
2348 hr_utility.trace(l_error_message || '-' || sqlerrm);
2349 hr_utility.set_location(' Leaving:'|| l_proc, 500);
2350
2351 END update_configuration_status;
2352
2353
2354 /* --------------------------------------------------------------------------
2355 -- Name : determine_country_resp
2356 -- Purpose : This function determines responsibility
2357 -- Arguments : l_country_code
2358 --
2359 -------------------------------------------------------------------------- */
2360
2361 FUNCTION determine_country_resp(p_country_code in varchar2
2362 ,p_assign_responsibility in varchar2)
2363 RETURN varchar2 IS
2364
2365 l_proc varchar2(72) := g_package || 'determine_country_resp';
2366 l_error_message varchar2(360);
2367 l_country_code per_ri_config_information.config_information1%type;
2368 l_responsibility_application per_ri_config_responsibility.responsibility_application%type;
2369 l_assign_responsibility varchar2(30);
2370 l_shrms boolean;
2371 l_hrms boolean;
2372 l_hr boolean;
2373
2374 BEGIN
2375 hr_utility.set_location('Entering:'|| l_proc, 10);
2376
2377 l_shrms := per_ri_config_utilities.responsibility_exists
2378 (p_country_code => p_country_code
2379 ,p_assign_responsibility => 'SHRMS');
2380 if l_shrms then
2381 return 'SHRMS';
2382 end if;
2383
2384 l_hrms := per_ri_config_utilities.responsibility_exists
2385 (p_country_code => p_country_code
2386 ,p_assign_responsibility => 'HRMS');
2387 if l_hrms then
2388 return 'HRMS';
2389 end if;
2390
2391 l_hr := per_ri_config_utilities.responsibility_exists
2392 (p_country_code => p_country_code
2393 ,p_assign_responsibility => 'PER');
2394 if l_hr then
2395 return 'PER';
2396 end if;
2397
2398 return 'PER'; -- return PER is can not make a decision
2399 hr_utility.set_location(' Leaving:'|| l_proc, 30);
2400
2401 EXCEPTION
2402 when others then
2403 l_error_message := 'Error in ' || l_proc;
2404 hr_utility.trace(l_error_message || '-' || sqlerrm);
2405 hr_utility.set_location(' Leaving:'|| l_proc, 500);
2406 hr_utility.raise_error;
2407
2408 END determine_country_resp;
2409
2410 /* --------------------------------------------------------------------------
2411 -- Name : get_enterprise_short_name
2412 -- Purpose : This function returns if responsibility can be assiged of enterpise short name
2413 -- for a given configuration
2414 -- Arguments : p_configuration_code
2415 --
2416 -------------------------------------------------------------------------- */
2417
2418 FUNCTION responsibility_exists(p_country_code in varchar2
2419 ,p_assign_responsibility in varchar2)
2420 RETURN boolean IS
2421
2422 cursor csr_responsibility
2423 (cp_country_code in varchar2
2424 ,cp_assign_responsibility in varchar2) IS
2425 select responsibility_application
2426 from per_ri_config_responsibility
2427 where territory_code = cp_country_code
2428 and responsibility_application = cp_assign_responsibility;
2429
2430 l_proc varchar2(72) := g_package || 'responsibility_exists';
2431 l_error_message varchar2(360);
2432 l_country_code per_ri_config_information.config_information1%type;
2433 l_responsibility_application per_ri_config_responsibility.responsibility_application%type;
2434 l_assign_responsibility varchar2(30);
2435 l_resp_exists boolean default FALSE;
2436
2437 BEGIN
2438 hr_utility.set_location('Entering:'|| l_proc, 10);
2439
2440 open csr_responsibility(p_country_code
2441 ,p_assign_responsibility);
2442 fetch csr_responsibility into l_responsibility_application;
2443 if csr_responsibility%FOUND then
2444 l_resp_exists := TRUE;
2445 hr_utility.trace('Responsibility exists' || p_country_code );
2446 hr_utility.trace('Responsibility exists' || p_assign_responsibility );
2447 else
2448 l_resp_exists := FALSE;
2449 hr_utility.trace('Responsibility exists' || p_country_code );
2450 hr_utility.trace('Responsibility does not exists' || p_assign_responsibility );
2451 end if;
2452 close csr_responsibility;
2453 return l_resp_exists;
2454
2455 hr_utility.set_location(' Leaving:'|| l_proc, 30);
2456
2457 EXCEPTION
2458 when others then
2459 l_error_message := 'Error in ' || l_proc;
2460 hr_utility.trace(l_error_message || '-' || sqlerrm);
2461 hr_utility.set_location(' Leaving:'|| l_proc, 500);
2462 hr_utility.raise_error;
2463
2464 END;
2465
2466
2467 /* --------------------------------------------------------------------------
2468 -- Name : submit_enable_msg_process
2469 -- Purpose : This procedure submit a concurrent request for running
2470 -- Enable Multiple Security Group process
2471 -- Arguments : errbuf
2472 -- retcode
2473 -------------------------------------------------------------------------- */
2474
2475 PROCEDURE submit_enable_mult_sg_process
2476 (errbuf out nocopy varchar2
2477 ,retcode out nocopy number) IS
2478
2479 l_proc varchar2(72) := g_package || 'submit_enable_mult_sg_process';
2480 l_log_message varchar2(360);
2481 l_request_id number(9);
2482 l_error_message varchar2(360);
2483
2484 l_legislation_code varchar2(30);
2485 l_currency_code varchar2(30);
2486 l_tax_start_date date;
2487
2488 BEGIN
2489 hr_utility.set_location('Entering:'|| l_proc, 10);
2490
2491 -- Remove site level profile option
2492 --PER_SECURITY_PROFILE_ID
2493 per_ri_config_utilities.set_profile_option_value
2494 (p_level => 10001
2495 ,p_level_value => 0
2496 ,p_level_value_app => 'PER'
2497 ,p_profile_name => 'PER_SECURITY_PROFILE_ID'
2498 ,p_profile_option_value => NULL);
2499
2500
2501 --Commented for Multiple Security Group Removal Changes
2502 -- /* DGARG
2503 -- Submit 'submit_enable_msg_process' concurernt request
2504 --
2505 --l_request_id := fnd_request.submit_request
2506 --(application => 'PER'
2507 --,program => 'HRSECGRP'
2508 --,description => 'Enterprise Structures Configuration'
2509 --,sub_request => FALSE);
2510 --hr_utility.trace('l_request_id = ' || to_char(l_request_id));
2511 --l_log_message := 'Created Enable Multiple Security Group process';
2512 --l_log_message := 'Created Enable Multiple Security Group process';
2513 --per_ri_config_utilities.write_log(p_message => l_log_message);
2514
2515 --
2516 hr_utility.set_location(' Leaving:'|| l_proc, 20);
2517 EXCEPTION
2518 when others then
2519 l_error_message := 'Error in ' || l_proc;
2520 hr_utility.trace(l_error_message || '-' || sqlerrm);
2521 hr_utility.set_location(' Leaving:'|| l_proc, 500);
2522 hr_utility.raise_error;
2523
2524 END submit_enable_mult_sg_process;
2525
2526 /* --------------------------------------------------------------------------
2527 -- Name : check_fresh_installation
2528 -- Purpose : This function checks if this database is having some data which
2529 -- can interfere with loader program.
2530 -------------------------------------------------------------------------- */
2531
2532 FUNCTION check_fresh_installation
2533 RETURN boolean IS
2534 cursor csr_bg_data IS
2535 select name
2536 from per_business_groups
2537 where name <> 'Setup Business Group';
2538
2539 l_proc varchar2(72) := g_package || 'check_fresh_installation';
2540 l_log_message varchar2(360);
2541 l_error_message varchar2(360);
2542 l_name per_business_groups.name%type;
2543 l_fresh_installed boolean default FALSE;
2544
2545 BEGIN
2546 hr_utility.set_location('Entering:'|| l_proc, 10);
2547
2548 open csr_bg_data;
2549 fetch csr_bg_data into l_name;
2550 if csr_bg_data%FOUND then
2551 l_fresh_installed := FALSE;
2552 hr_utility.trace('l_fresh_installed = ' || 'FALSE');
2553 else
2554 l_fresh_installed := TRUE;
2555 hr_utility.trace('l_fresh_installed = ' || 'TRUE');
2556 end if;
2557
2558 return l_fresh_installed;
2559
2560 hr_utility.set_location(' Leaving:'|| l_proc, 20);
2561 EXCEPTION
2562 when others then
2563 l_error_message := 'Error in ' || l_proc;
2564 hr_utility.trace(l_error_message || '-' || sqlerrm);
2565 hr_utility.set_location(' Leaving:'|| l_proc, 500);
2566 hr_utility.raise_error;
2567
2568 END check_fresh_installation;
2569
2570
2571 /* --------------------------------------------------------------------------
2572 -- Name : write_data_pump_exception_log
2573 -- Purpose : This procedure write exceptions from datapump if datapump batch
2574 -- is not able to load successfully.
2575 -- Arguments : p_patch_header_id
2576 -------------------------------------------------------------------------- */
2577
2578 PROCEDURE write_data_pump_exception_log
2579 (p_patch_header_id in number) IS
2580
2581 l_proc varchar2(72) := g_package || 'write_data_pump_exception_log';
2582 l_log_message varchar2(360);
2583 l_error_message varchar2(360);
2584
2585 l_exception_text hr_pump_batch_exceptions.exception_text%type;
2586
2587 cursor csr_dp_exception(cp_patch_header_id in number) IS
2588 select exception_text
2589 from hr_pump_batch_exceptions
2590 where source_id in ( select batch_line_id
2591 from hr_pump_batch_lines
2592 where batch_id = cp_patch_header_id);
2593
2594 BEGIN
2595 hr_utility.set_location('Entering:'|| l_proc, 10);
2596
2597 open csr_dp_exception(p_patch_header_id);
2598 fetch csr_dp_exception into l_exception_text;
2599 if csr_dp_exception%FOUND then
2600 l_log_message := 'Error occured in loading datapump records';
2601 per_ri_config_utilities.write_log(p_message => l_log_message);
2602
2603 l_log_message := l_exception_text;
2604 per_ri_config_utilities.write_log(p_message => l_log_message);
2605 hr_utility.raise_error;
2606 end if;
2607 hr_utility.set_location(' Leaving:'|| l_proc, 20);
2608 EXCEPTION
2609 when others then
2610 l_error_message := 'Error in ' || l_proc;
2611 hr_utility.trace(l_error_message || '-' || sqlerrm);
2612 hr_utility.set_location(' Leaving:'|| l_proc, 500);
2613 hr_utility.raise_error;
2614
2615 END write_data_pump_exception_log;
2616
2617 /* --------------------------------------------------------------------------
2618 -- Name : check_data_pump_exception
2619 -- Purpose : This procedure check if datapump load has any exception
2620 -- while processing a batch
2621 -- Arguments : p_patch_header_id
2622 -------------------------------------------------------------------------- */
2623
2624 FUNCTION check_data_pump_exception(p_patch_header_id in number)
2625 return boolean IS
2626
2627 l_proc varchar2(72) := g_package || 'check_data_pump_exception';
2628 l_log_message varchar2(360);
2629 l_error_message varchar2(360);
2630 l_exception boolean default FALSE;
2631 l_exception_text hr_pump_batch_exceptions.exception_text%type;
2632
2633 cursor csr_dp_exception(cp_patch_header_id in number) IS
2634 select exception_text
2635 from hr_pump_batch_exceptions
2636 where source_id in ( select batch_line_id
2637 from hr_pump_batch_lines
2638 where batch_id = cp_patch_header_id);
2639 BEGIN
2640 hr_utility.set_location('Entering:'|| l_proc, 10);
2641
2642 open csr_dp_exception(p_patch_header_id);
2643 fetch csr_dp_exception into l_exception_text;
2644 if csr_dp_exception%FOUND then
2645 l_exception := TRUE;
2646 else
2647 l_exception := FALSE;
2648 end if;
2649 hr_utility.set_location(' Leaving:'|| l_proc, 20);
2650 return l_exception;
2651 EXCEPTION
2652 when others then
2653 l_error_message := 'Error in ' || l_proc;
2654 hr_utility.trace(l_error_message || '-' || sqlerrm);
2655 hr_utility.set_location(' Leaving:'|| l_proc, 500);
2656 hr_utility.raise_error;
2657
2658 END check_data_pump_exception;
2659
2660 /* --------------------------------------------------------------------------
2661 -- Name : assign_misc_responsibility
2662 -- Purpose : This procedure assign misc responsibilities to user.
2663 -- while processing a batch
2664 -- Arguments : p_configuration_code
2665 -------------------------------------------------------------------------- */
2666
2667 PROCEDURE assign_misc_responsibility
2668 (p_configuration_code in varchar2
2669 ,p_technical_summary_mode in boolean default FALSE
2670 ,p_hrms_misc_resp_tab in out nocopy per_ri_config_tech_summary.hrms_misc_resp_tab) IS
2671
2672 l_proc varchar2(72) := g_package || 'assign_misc_responsibility';
2673 l_log_message varchar2(360);
2674 l_error_message varchar2(360);
2675
2676 l_hrms_misc_resp_count number(9) := 0;
2677 l_enterprise_primary_industry per_ri_config_information.config_information1%type;
2678 l_start_date varchar2(240) := to_char(per_ri_config_utilities.g_config_effective_date,'YYYY/MM/DD');
2679 l_end_date varchar2(240) := to_char(per_ri_config_utilities.g_config_effective_end_date,'YYYY/MM/DD');
2680
2681 BEGIN
2682 hr_utility.set_location('Entering:'|| l_proc, 10);
2683 if NOT (p_technical_summary_mode) then
2684 fnd_user_resp_groups_api.load_row(
2685 x_user_name => upper(per_ri_config_utilities.return_config_entity_name_pre
2686 (per_ri_config_main.g_configuration_user_name)),
2687 x_resp_key => 'GLB_SHRMS_MANAGER',
2688 x_app_short_name => 'PER',
2689 x_security_group => 'STANDARD',
2690 x_owner => 'SEED',
2691 x_start_date => l_start_date,
2692 x_end_date => l_end_date,
2693 x_description => per_ri_config_main.g_description_string);
2694 else
2695 p_hrms_misc_resp_tab(l_hrms_misc_resp_count).user_name := upper(per_ri_config_utilities.return_config_entity_name_pre
2696 (per_ri_config_main.g_configuration_user_name));
2697 p_hrms_misc_resp_tab(l_hrms_misc_resp_count).resp_key := 'GLB_SHRMS_MANAGER';
2698 p_hrms_misc_resp_tab(l_hrms_misc_resp_count).app_short_name := 'PER';
2699 p_hrms_misc_resp_tab(l_hrms_misc_resp_count).security_group := 'STANDARD';
2700 p_hrms_misc_resp_tab(l_hrms_misc_resp_count).owner := 'SEED';
2701 p_hrms_misc_resp_tab(l_hrms_misc_resp_count).start_date := l_start_date;
2702 p_hrms_misc_resp_tab(l_hrms_misc_resp_count).end_date := l_end_date;
2703 p_hrms_misc_resp_tab(l_hrms_misc_resp_count).description := per_ri_config_main.g_description_string;
2704
2705 l_hrms_misc_resp_count := l_hrms_misc_resp_count + 1 ;
2706 end if;
2707 hr_utility.trace('Assigned Responsibility: ' || 'GLB_SHRMS_MANAGER');
2708 hr_utility.set_location(l_proc, 20);
2709
2710 l_enterprise_primary_industry := per_ri_config_utilities.get_ent_primary_industry
2711 (p_configuration_code => p_configuration_code);
2712
2713 if l_enterprise_primary_industry = 'US_GOVERNMENT' then
2714 if NOT (p_technical_summary_mode) then
2715 fnd_user_resp_groups_api.load_row(
2716 x_user_name => upper(per_ri_config_utilities.return_config_entity_name_pre
2717 (per_ri_config_main.g_configuration_user_name)),
2718 x_resp_key => 'US_GOV_HR_MANAGER',
2719 x_app_short_name => 'GHR',
2720 x_security_group => 'STANDARD',
2721 x_owner => 'SEED',
2722 x_start_date => l_start_date,
2723 x_end_date => l_end_date,
2724 x_description => per_ri_config_main.g_description_string);
2725 else
2726 p_hrms_misc_resp_tab(l_hrms_misc_resp_count).user_name := upper(per_ri_config_utilities.return_config_entity_name_pre
2727 (per_ri_config_main.g_configuration_user_name));
2728 p_hrms_misc_resp_tab(l_hrms_misc_resp_count).resp_key := 'US_GOV_HR_MANAGER';
2729 p_hrms_misc_resp_tab(l_hrms_misc_resp_count).app_short_name := 'GHR';
2730 p_hrms_misc_resp_tab(l_hrms_misc_resp_count).security_group := 'STANDARD';
2731 p_hrms_misc_resp_tab(l_hrms_misc_resp_count).owner := 'SEED';
2732 p_hrms_misc_resp_tab(l_hrms_misc_resp_count).start_date := l_start_date;
2733 p_hrms_misc_resp_tab(l_hrms_misc_resp_count).end_date := l_end_date;
2734 p_hrms_misc_resp_tab(l_hrms_misc_resp_count).description := per_ri_config_main.g_description_string;
2735
2736 l_hrms_misc_resp_count := l_hrms_misc_resp_count + 1 ;
2737 end if;
2738 hr_utility.trace('Assigned Responsibility: ' || 'US_GOV_HR_MANAGER');
2739 hr_utility.set_location(l_proc, 20);
2740 end if;
2741
2742 hr_utility.set_location(' Leaving:'|| l_proc, 100);
2743 EXCEPTION
2744 when others then
2745 l_error_message := 'Error in ' || l_proc;
2746 hr_utility.trace(l_error_message || '-' || sqlerrm);
2747 hr_utility.set_location(' Leaving:'|| l_proc, 500);
2748 hr_utility.raise_error;
2749
2750 END assign_misc_responsibility;
2751
2752 /* --------------------------------------------------------------------------
2753 -- Name : return_config_entity_name
2754 -- Purpose : This function returns name of entity depanding upon
2755 -- if multiple configuration upload is enabled.
2756 -- Arguments : entity_name
2757 --
2758 -------------------------------------------------------------------------- */
2759
2760 FUNCTION return_config_entity_name(entity_name in varchar2)
2761 RETURN varchar2 IS
2762
2763
2764 l_entity_name per_ri_config_information.config_information1%type;
2765 l_multiple_config_upload varchar2(20);
2766
2767 l_proc varchar2(72) := g_package || 'return_config_entity_name';
2768 l_error_message varchar2(360);
2769
2770 BEGIN
2771 hr_utility.set_location('Entering:'|| l_proc, 10);
2772 hr_utility.trace('per_ri_config_utilities.g_enterprise_short_name = ' || per_ri_config_utilities.g_enterprise_short_name );
2773 l_multiple_config_upload := fnd_profile.value('PER_RI_LOAD_OVERRIDE');
2774
2775 if l_multiple_config_upload = 'Y' then
2776 l_entity_name := entity_name || ' ' || per_ri_config_utilities.g_enterprise_short_name;
2777 else
2778 l_entity_name := entity_name;
2779 end if;
2780
2781 --hr_utility.set_location(' Leaving:'|| l_proc, 30);
2782
2783 return l_entity_name;
2784
2785 EXCEPTION
2786 when others then
2787 l_error_message := 'Error in ' || l_proc;
2788 hr_utility.trace(l_error_message || '-' || sqlerrm);
2789 hr_utility.set_location(' Leaving:'|| l_proc, 500);
2790 hr_utility.raise_error;
2791
2792 END return_config_entity_name;
2793
2794
2795 /* --------------------------------------------------------------------------
2796 -- Name : return_config_entity_name_pre
2797 -- Purpose : This function returns name (prefixed) of entity depanding upon
2798 -- if multiple configuration upload is enabled.
2799 -- Arguments : entity_name
2800 --
2801 -------------------------------------------------------------------------- */
2802
2803 FUNCTION return_config_entity_name_pre(entity_name in varchar2)
2804 RETURN varchar2 IS
2805
2806
2807 l_entity_name per_ri_config_information.config_information1%type;
2808 l_multiple_config_upload varchar2(20);
2809
2810 l_proc varchar2(72) := g_package || 'return_config_entity_name_pre';
2811 l_error_message varchar2(360);
2812
2813 BEGIN
2814 hr_utility.set_location('Entering:'|| l_proc, 10);
2815 hr_utility.trace('per_ri_config_utilities.g_enterprise_short_name = ' || per_ri_config_utilities.g_enterprise_short_name );
2816
2817 l_multiple_config_upload := fnd_profile.value('PER_RI_LOAD_OVERRIDE');
2818
2819 if l_multiple_config_upload = 'Y' then
2820 l_entity_name := per_ri_config_utilities.g_enterprise_short_name || ' ' || entity_name;
2821 else
2822 l_entity_name := entity_name;
2823 end if;
2824
2825 --hr_utility.set_location(' Leaving:'|| l_proc, 30);
2826
2827 return l_entity_name;
2828
2829 EXCEPTION
2830 when others then
2831 l_error_message := 'Error in ' || l_proc;
2832 hr_utility.trace(l_error_message || '-' || sqlerrm);
2833 hr_utility.set_location(' Leaving:'|| l_proc, 500);
2834 hr_utility.raise_error;
2835
2836 END return_config_entity_name_pre;
2837
2838 FUNCTION get_location_prompt(p_style in varchar2
2839 ,p_app_column_name in varchar2)
2840 RETURN varchar2 IS
2841
2842 l_user_column_name varchar2(200);
2843 l_proc varchar2(72) := g_package || 'get_location_prompt';
2844
2845 cursor csr_get_prm(cp_style in varchar2
2846 ,cp_app_col_name in varchar2) IS
2847 select form_left_prompt
2848 from fnd_descr_flex_col_usage_vl
2849 where descriptive_flexfield_name= 'Address Location'
2850 and descriptive_flex_context_code = cp_style
2851 and application_column_name = cp_app_col_name;
2852
2853 BEGIN
2854 hr_utility.set_location(' Entering :'|| l_proc, 30);
2855 hr_utility.set_location(' Style :'|| p_style,40);
2856
2857 open csr_get_prm(p_style,p_app_column_name);
2858
2859 fetch csr_get_prm into l_user_column_name;
2860 if(csr_get_prm%FOUND) then
2861 --hr_utility.set_location(' Leaving :'|| l_proc, 50);
2862 close csr_get_prm;
2863 return l_user_column_name;
2864 else
2865 --hr_utility.set_location(' Leaving :'|| l_proc, 50);
2866 close csr_get_prm;
2867 return null;
2868 end if;
2869
2870
2871 END get_location_prompt;
2872
2873
2874 /* --------------------------------------------------------------------------
2875 -- Name : create_valueset_ts_data
2876 -- Purpose : This procedure creates a value set data for technical summary
2877 -- Arguments : p_valueset_name
2878 -- p_valueset_type
2879 p_fed_seg_attribute
2880 p_valueset_tab
2881 -------------------------------------------------------------------------- */
2882
2883 PROCEDURE create_valueset_ts_data(p_valueset_name in varchar2
2884 ,p_valueset_type in varchar2
2885 ,p_structure_code in varchar2
2886 ,p_segment_name in varchar2
2887 ,p_segment_number in varchar2
2888 ,p_fed_seg_attribute in varchar2 default 'N'
2889 ,p_valueset_tab in out nocopy
2890 per_ri_config_tech_summary.valueset_tab) IS
2891
2892 l_proc varchar2(72) := g_package || 'create_valueset_ts_data';
2893 l_error_message varchar2(360);
2894 l_log_message varchar2(360);
2895
2896 l_security_available varchar2(1) default 'N';
2897 l_enable_longlist varchar2(1) default 'Y';
2898 l_format_type varchar2(1) default 'C';
2899 l_maximum_size number(9) default 60;
2900 l_precision number(2) default null;
2901 l_numbers_only varchar2(1) default 'N';
2902 l_uppercase_only varchar2(1) default 'N';
2903 l_right_justify_zero_fill varchar2(1) default 'N';
2904 l_min_value varchar2(150) default null;
2905 l_max_value varchar2(150) default null;
2906 l_description varchar2(240);
2907 l_value_set_exists boolean;
2908
2909 l_value_set_count number(9) := 1 ;
2910 l_valueset_seq number(9);
2911 l_valueset_name fnd_flex_value_sets.flex_value_set_name%type;
2912
2913 BEGIN
2914
2915 hr_utility.set_location('Entering:'|| l_proc, 10);
2916
2917 if p_fed_seg_attribute = 'N' then
2918 --
2919 -- create flex segment value set
2920 --
2921 --select per_ri_config_vsets_number_s.nextval into l_valueset_seq from sys.dual;
2922 l_valueset_seq := p_segment_number;
2923
2924 l_valueset_name := p_structure_code || ' ' || p_segment_name
2925 || ' ' || l_valueset_seq;
2926
2927 hr_utility.trace('l_valueset_name = ' || l_valueset_name);
2928 end if;
2929
2930 -- valueset name is always passed when p_fed_seg_attribute = 'Y'
2931 if p_fed_seg_attribute = 'Y' then
2932 l_valueset_name := p_valueset_name;
2933 end if;
2934
2935 if p_valueset_type = 'CHAR' then
2936 l_format_type := 'C';
2937 l_maximum_size := 60;
2938 elsif p_valueset_type = 'NUMBER' then
2939 l_format_type := 'N';
2940 l_maximum_size := 15;
2941 elsif p_valueset_type = 'CHARLOV' then
2942 l_format_type := 'C';
2943 l_maximum_size := 60;
2944 elsif p_valueset_type = 'NUMLOV' then
2945 l_format_type := 'N';
2946 l_maximum_size := 15;
2947 elsif p_valueset_type = 'DATE' then
2948 l_format_type := 'D';
2949 l_maximum_size := 20;
2950 else
2951 l_format_type := 'C';
2952 l_maximum_size := 60;
2953 end if;
2954
2955 -- Create Character Value Set
2956 l_description := 'This value set is generated by Enterprise Structures Configuration '
2957 || 'This will initially be empty and can '
2958 || 'be populated using the load reference data';
2959
2960 hr_utility.set_location(l_proc, 20);
2961 p_valueset_tab(l_value_set_count).value_set_name := l_valueset_name;
2962 p_valueset_tab(l_value_set_count).description := l_description;
2963 p_valueset_tab(l_value_set_count).security_available := l_security_available;
2964 p_valueset_tab(l_value_set_count).enable_longlist := l_enable_longlist;
2965 p_valueset_tab(l_value_set_count).format_type := l_format_type;
2966 p_valueset_tab(l_value_set_count).maximum_size := l_maximum_size;
2967 p_valueset_tab(l_value_set_count).precision := l_precision;
2968 p_valueset_tab(l_value_set_count).numbers_only := l_numbers_only;
2969 p_valueset_tab(l_value_set_count).uppercase_only := l_uppercase_only;
2970 p_valueset_tab(l_value_set_count).right_justify_zero_fill := l_right_justify_zero_fill;
2971 p_valueset_tab(l_value_set_count).min_value := l_min_value;
2972 p_valueset_tab(l_value_set_count).max_value := l_max_value;
2973
2974 l_value_set_count := l_value_set_count + 1;
2975
2976 l_log_message := 'Created VALUESET ' || p_valueset_name;
2977
2978 hr_utility.set_location(' Leaving:'|| l_proc, 30);
2979
2980 EXCEPTION
2981 when others then
2982 l_error_message := 'Error in ' || l_proc;
2983 hr_utility.trace(l_error_message || '-' || sqlerrm);
2984 hr_utility.set_location(' Leaving:'|| l_proc, 500);
2985 hr_utility.raise_error;
2986
2987 End create_valueset_ts_data;
2988
2989 /* --------------------------------------------------------------------------
2990 -- Name : create_more_hrms_resps
2991 -- Purpose : This procedure create security profile assignments.
2992 -- Arguments : create_sg_assignments
2993 --
2994 -------------------------------------------------------------------------- */
2995
2996 PROCEDURE create_more_hrms_resps
2997 (p_configuration_code in varchar2
2998 ,p_security_profile_tab in per_ri_config_fnd_hr_entity.security_profile_tab
2999 ,p_int_bg_resp_tab in per_ri_config_fnd_hr_entity.int_bg_resp_tab
3000 ,p_technical_summary_mode in boolean default FALSE
3001 ,p_hrms_resp_main_tab
3002 in out nocopy per_ri_config_tech_summary.hrms_resp_tab
3003 ,p_more_profile_resp_tab
3004 in out nocopy per_ri_config_tech_summary.profile_resp_tab
3005 ,p_more_int_profile_resp_tab
3006 in out nocopy per_ri_config_tech_summary.profile_resp_tab) IS
3007
3008 l_security_profile_name per_business_groups.name%type;
3009 l_responsibility_key fnd_responsibility_vl.responsibility_name%type;
3010 l_ovn number(9);
3011 l_hrms_resp_profile_resp_tab per_ri_config_tech_summary.profile_resp_tab;
3012 l_int_resp_profile_resp_tab per_ri_config_tech_summary.profile_resp_tab;
3013 l_proc varchar2(72) := g_package || 'create_more_hrms_resps';
3014 l_log_message varchar2(360);
3015 l_error_message varchar2(360);
3016 l_main_bgsgut_profile_resp_tab per_ri_config_tech_summary.profile_resp_tab;
3017 l_int_bgsgut_profile_resp_tab per_ri_config_tech_summary.profile_resp_tab;
3018
3019 l_main_bgsgut_profile_resp_ct number(8) := 0;
3020 l_int_bgsgut_profile_resp_ct number(8) := 0;
3021 l_profile_resp_temp_count number(9) := 1;
3022 l_more_profile_resp_tab_count number(9) := 0;
3023 l_hrms_resp_main_count number(9) := 0;
3024
3025 l_hrms_resp_one_tab per_ri_config_tech_summary.hrms_resp_tab;
3026
3027 BEGIN
3028
3029 hr_utility.set_location('Entering:'|| l_proc, 10);
3030
3031 l_main_bgsgut_profile_resp_ct := 0;
3032 if p_security_profile_tab.count > 0 then
3033 for i in p_security_profile_tab.first ..
3034 p_security_profile_tab.last LOOP
3035
3036 l_security_profile_name := p_security_profile_tab(i).security_profile_name;
3037 l_responsibility_key := p_security_profile_tab(i).responsibility_key;
3038
3039 hr_utility.trace('l_security_profile_name := ' || l_security_profile_name);
3040 hr_utility.trace('l_responsibility_key := ' || l_responsibility_key);
3041 hr_utility.trace('before loop l_more_profile_resp_tab_count ' || l_more_profile_resp_tab_count );
3042 per_ri_config_utilities.create_resp_and_profile
3043 (p_configuration_code => p_configuration_code
3044 ,p_security_profile_name => l_security_profile_name
3045 ,p_responsibility_key => l_responsibility_key
3046 ,p_technical_summary_mode => p_technical_summary_mode
3047 ,p_bg_sg_ut_profile_resp_tab => l_main_bgsgut_profile_resp_tab
3048 ,p_hrms_resp_one_tab => l_hrms_resp_one_tab);
3049
3050 -- Populate data for ts resp tables
3051 if l_hrms_resp_one_tab.count > 0 THEN
3052 for l in l_hrms_resp_one_tab.first ..
3053 l_hrms_resp_one_tab.last loop
3054
3055 --new date for responsibility population
3056 p_hrms_resp_main_tab(l_hrms_resp_main_count).user_name := l_hrms_resp_one_tab(l).user_name;
3057 p_hrms_resp_main_tab(l_hrms_resp_main_count).resp_key := l_hrms_resp_one_tab(l).resp_key;
3058 p_hrms_resp_main_tab(l_hrms_resp_main_count).app_short_name := l_hrms_resp_one_tab(l).app_short_name;
3059 p_hrms_resp_main_tab(l_hrms_resp_main_count).security_group := l_hrms_resp_one_tab(l).security_group;
3060 p_hrms_resp_main_tab(l_hrms_resp_main_count).owner := l_hrms_resp_one_tab(l).owner;
3061 p_hrms_resp_main_tab(l_hrms_resp_main_count).start_date := l_hrms_resp_one_tab(l).start_date;
3062 p_hrms_resp_main_tab(l_hrms_resp_main_count).end_date := l_hrms_resp_one_tab(l).end_date;
3063 p_hrms_resp_main_tab(l_hrms_resp_main_count).description := l_hrms_resp_one_tab(l).description;
3064
3065 l_hrms_resp_main_count := 1 + l_hrms_resp_main_count;
3066 end loop;
3067 end if;
3068
3069 if l_main_bgsgut_profile_resp_tab.count > 0 THEN
3070 for j in l_main_bgsgut_profile_resp_tab.first ..
3071 l_main_bgsgut_profile_resp_tab.last loop
3072
3073 hr_utility.trace('l_more_profile_resp_tab_count ' || l_more_profile_resp_tab_count );
3074
3075 if l_main_bgsgut_profile_resp_tab(j).profile_name = 'PER_BUSINESS_GROUP_ID' then
3076 p_more_profile_resp_tab(l_more_profile_resp_tab_count).level
3077 := l_main_bgsgut_profile_resp_tab(j).level;
3078
3079 p_more_profile_resp_tab(l_more_profile_resp_tab_count).level_value
3080 := l_main_bgsgut_profile_resp_tab(j).level_value;
3081
3082 p_more_profile_resp_tab(l_more_profile_resp_tab_count).level_value_app
3083 := l_main_bgsgut_profile_resp_tab(j).level_value_app;
3084
3085 p_more_profile_resp_tab(l_more_profile_resp_tab_count).profile_name
3086 := l_main_bgsgut_profile_resp_tab(j).profile_name;
3087
3088 p_more_profile_resp_tab(l_more_profile_resp_tab_count).profile_option_value
3089 := l_main_bgsgut_profile_resp_tab(j).profile_option_value;
3090
3091 l_more_profile_resp_tab_count := 1 + l_more_profile_resp_tab_count;
3092 end if;
3093 if l_main_bgsgut_profile_resp_tab(j).profile_name = 'PER_SECURITY_PROFILE_ID' then
3094 p_more_profile_resp_tab(l_more_profile_resp_tab_count).level
3095 := l_main_bgsgut_profile_resp_tab(j).level;
3096
3097 p_more_profile_resp_tab(l_more_profile_resp_tab_count).level_value
3098 := l_main_bgsgut_profile_resp_tab(j).level_value;
3099
3100 p_more_profile_resp_tab(l_more_profile_resp_tab_count).level_value_app
3101 := l_main_bgsgut_profile_resp_tab(j).level_value_app;
3102
3103 p_more_profile_resp_tab(l_more_profile_resp_tab_count).profile_name
3104 := l_main_bgsgut_profile_resp_tab(j).profile_name;
3105
3106 p_more_profile_resp_tab(l_more_profile_resp_tab_count).profile_option_value
3107 := l_main_bgsgut_profile_resp_tab(j).profile_option_value;
3108
3109 l_more_profile_resp_tab_count := 1 + l_more_profile_resp_tab_count;
3110 end if;
3111
3112 if l_main_bgsgut_profile_resp_tab(j).profile_name = 'HR_USER_TYPE' then
3113 p_more_profile_resp_tab(l_more_profile_resp_tab_count).level
3114 := l_main_bgsgut_profile_resp_tab(j).level;
3115
3116 p_more_profile_resp_tab(l_more_profile_resp_tab_count).level_value
3117 := l_main_bgsgut_profile_resp_tab(j).level_value;
3118
3119 p_more_profile_resp_tab(l_more_profile_resp_tab_count).level_value_app
3120 := l_main_bgsgut_profile_resp_tab(j).level_value_app;
3121
3122 p_more_profile_resp_tab(l_more_profile_resp_tab_count).profile_name
3123 := l_main_bgsgut_profile_resp_tab(j).profile_name;
3124
3125 p_more_profile_resp_tab(l_more_profile_resp_tab_count).profile_option_value
3126 := l_main_bgsgut_profile_resp_tab(j).profile_option_value;
3127
3128 l_more_profile_resp_tab_count := 1 + l_more_profile_resp_tab_count;
3129 end if;
3130 end loop;
3131 end if;
3132 end loop;
3133 end if;
3134
3135 -- Process International BGs responsibilities
3136 hr_utility.set_location(l_proc, 20);
3137 l_int_bgsgut_profile_resp_ct := 0;
3138 if p_int_bg_resp_tab.count > 0 then
3139 for i in p_int_bg_resp_tab.first ..
3140 p_int_bg_resp_tab.last LOOP
3141
3142 l_security_profile_name := p_int_bg_resp_tab(i).security_profile_name;
3143 -- l_responsibility_key is null at this point
3144 l_responsibility_key := p_int_bg_resp_tab(i).responsibility_key;
3145
3146
3147 hr_utility.trace('l_security_profile_name ' || l_security_profile_name);
3148
3149 if l_security_profile_name like '%INT BG' then
3150
3151 -- all International BG's to get copy of GLB_SHRMS_MANAGER responsibility
3152 l_responsibility_key := 'GLB_SHRMS_MANAGER';
3153
3154 per_ri_config_utilities.create_resp_and_profile
3155 (p_configuration_code => p_configuration_code
3156 ,p_security_profile_name => l_security_profile_name
3157 ,p_responsibility_key => l_responsibility_key
3158 ,p_technical_summary_mode => p_technical_summary_mode
3159 ,p_bg_sg_ut_profile_resp_tab => l_int_bgsgut_profile_resp_tab
3160 ,p_hrms_resp_one_tab => l_hrms_resp_one_tab);
3161
3162 -- Populate data for ts resp tables
3163 if l_hrms_resp_one_tab.count > 0 THEN
3164 for k in l_hrms_resp_one_tab.first ..
3165 l_hrms_resp_one_tab.last loop
3166
3167 --new date for responsibility population
3168 p_hrms_resp_main_tab(l_hrms_resp_main_count).user_name := l_hrms_resp_one_tab(k).user_name;
3169 p_hrms_resp_main_tab(l_hrms_resp_main_count).resp_key := l_hrms_resp_one_tab(k).resp_key;
3170 p_hrms_resp_main_tab(l_hrms_resp_main_count).app_short_name := l_hrms_resp_one_tab(k).app_short_name;
3171 p_hrms_resp_main_tab(l_hrms_resp_main_count).security_group := l_hrms_resp_one_tab(k).security_group;
3172 p_hrms_resp_main_tab(l_hrms_resp_main_count).owner := l_hrms_resp_one_tab(k).owner;
3173 p_hrms_resp_main_tab(l_hrms_resp_main_count).start_date := l_hrms_resp_one_tab(k).start_date;
3174 p_hrms_resp_main_tab(l_hrms_resp_main_count).end_date := l_hrms_resp_one_tab(k).end_date;
3175 p_hrms_resp_main_tab(l_hrms_resp_main_count).description := l_hrms_resp_one_tab(k).description;
3176
3177 l_hrms_resp_main_count := 1 + l_hrms_resp_main_count;
3178 end loop;
3179 end if;
3180
3181 if l_int_bgsgut_profile_resp_tab.count > 0 THEN
3182 for j in l_int_bgsgut_profile_resp_tab.first ..
3183 l_int_bgsgut_profile_resp_tab.last loop
3184
3185
3186 if l_int_bgsgut_profile_resp_tab(j).profile_name = 'PER_BUSINESS_GROUP_ID' then
3187 p_more_profile_resp_tab(l_more_profile_resp_tab_count).level
3188 := l_int_bgsgut_profile_resp_tab(j).level;
3189
3190 p_more_profile_resp_tab(l_more_profile_resp_tab_count).level_value
3191 := l_int_bgsgut_profile_resp_tab(j).level_value;
3192
3193 p_more_profile_resp_tab(l_more_profile_resp_tab_count).level_value_app
3194 := l_int_bgsgut_profile_resp_tab(j).level_value_app;
3195
3196 p_more_profile_resp_tab(l_more_profile_resp_tab_count).profile_name
3197 := l_int_bgsgut_profile_resp_tab(j).profile_name;
3198
3199 p_more_profile_resp_tab(l_more_profile_resp_tab_count).profile_option_value
3200 := l_int_bgsgut_profile_resp_tab(j).profile_option_value;
3201
3202 l_more_profile_resp_tab_count := 1 + l_more_profile_resp_tab_count;
3203 end if;
3204
3205 if l_int_bgsgut_profile_resp_tab(j).profile_name = 'PER_SECURITY_PROFILE_ID' then
3206 p_more_profile_resp_tab(l_more_profile_resp_tab_count).level
3207 := l_int_bgsgut_profile_resp_tab(j).level;
3208
3209 p_more_profile_resp_tab(l_more_profile_resp_tab_count).level_value
3210 := l_int_bgsgut_profile_resp_tab(j).level_value;
3211
3212 p_more_profile_resp_tab(l_more_profile_resp_tab_count).level_value_app
3213 := l_int_bgsgut_profile_resp_tab(j).level_value_app;
3214
3215 p_more_profile_resp_tab(l_more_profile_resp_tab_count).profile_name
3216 := l_int_bgsgut_profile_resp_tab(j).profile_name;
3217
3218 p_more_profile_resp_tab(l_more_profile_resp_tab_count).profile_option_value
3219 := l_int_bgsgut_profile_resp_tab(j).profile_option_value;
3220
3221 l_more_profile_resp_tab_count := 1 + l_more_profile_resp_tab_count;
3222 end if;
3223
3224 if l_int_bgsgut_profile_resp_tab(j).profile_name = 'HR_USER_TYPE' then
3225 p_more_profile_resp_tab(l_more_profile_resp_tab_count).level
3226 := l_int_bgsgut_profile_resp_tab(j).level;
3227
3228 p_more_profile_resp_tab(l_more_profile_resp_tab_count).level_value
3229 := l_int_bgsgut_profile_resp_tab(j).level_value;
3230
3231 p_more_profile_resp_tab(l_more_profile_resp_tab_count).level_value_app
3232 := l_int_bgsgut_profile_resp_tab(j).level_value_app;
3233
3234 p_more_profile_resp_tab(l_more_profile_resp_tab_count).profile_name
3235 := l_int_bgsgut_profile_resp_tab(j).profile_name;
3236
3237 p_more_profile_resp_tab(l_more_profile_resp_tab_count).profile_option_value
3238 := l_int_bgsgut_profile_resp_tab(j).profile_option_value;
3239
3240 l_more_profile_resp_tab_count := 1 + l_more_profile_resp_tab_count;
3241 end if;
3242
3243 end loop;
3244 end if;
3245 end if;
3246 end loop;
3247 end if;
3248
3249 hr_utility.set_location(' Leaving:'|| l_proc, 100);
3250 EXCEPTION
3251 when others then
3252 l_error_message := 'Error in ' || l_proc;
3253 hr_utility.trace(l_error_message || '-' || sqlerrm);
3254 hr_utility.set_location(' Leaving:'|| l_proc, 500);
3255
3256
3257 END create_more_hrms_resps;
3258
3259
3260
3261 /* --------------------------------------------------------------------------
3262 -- Name : create_responsibility
3263 -- Purpose : This procedure creates responsibility
3264 -- Arguments : p_configuration_code
3265 --
3266 -------------------------------------------------------------------------- */
3267
3268 PROCEDURE create_responsibility
3269 (p_app_short_name in fnd_application.application_short_name%type
3270 ,p_resp_key in fnd_responsibility_vl.responsibility_name%type
3271 ,p_responsibility_id in fnd_responsibility.responsibility_id%type
3272 ,p_responsibility_name in fnd_responsibility_tl.responsibility_name%type
3273 ,p_owner in varchar2
3274 ,p_data_group_app_short_name in fnd_application.application_short_name%type
3275 ,p_data_group_name in fnd_data_groups_standard_view.data_group_name%type
3276 ,p_menu_name in fnd_menus.menu_name%type
3277 ,p_start_date in varchar2
3278 ,p_end_date in varchar2
3279 ,p_description in varchar2
3280 ,p_group_app_short_name in fnd_application.application_short_name%type
3281 ,p_request_group_name in fnd_request_groups.request_group_name%type
3282 ,p_version in varchar2
3283 ,p_web_host_name in fnd_responsibility.web_host_name%type
3284 ,p_web_agent_name in fnd_responsibility.web_agent_name%type) IS
3285
3286 l_proc varchar2(72) := g_package || 'create_responsibility';
3287 l_error_message varchar2(360);
3288
3289 BEGIN
3290 hr_utility.set_location('Entering:'|| l_proc, 10);
3291
3292 hr_utility.trace('x_responsibility_name := ' || p_responsibility_name);
3293 hr_utility.trace('x_resp_key := ' || p_resp_key);
3294
3295 -- create responsibility only when if specified resposibility does not exists
3296 -- this case would happen when loader program is running into non-multiple configuration
3297 -- load mode.
3298 if NOT (fnd_function_security.responsibility_exists(responsibility_key => p_resp_key)) then
3299 fnd_responsibility_pkg.load_row
3300 (x_app_short_name => p_app_short_name
3301 ,x_resp_key => p_resp_key
3302 ,x_responsibility_id => null
3303 ,x_responsibility_name => p_responsibility_name
3304 ,x_owner => 'CUSTOM'
3305 ,x_data_group_app_short_name => p_data_group_app_short_name
3306 ,x_data_group_name => p_data_group_name
3307 ,x_menu_name => p_menu_name
3308 ,x_start_date => p_start_date
3309 ,x_end_date => null
3310 ,x_description => p_description
3311 ,x_group_app_short_name => p_group_app_short_name
3312 ,x_request_group_name => p_request_group_name
3313 ,x_version => p_version
3314 ,x_web_host_name => p_web_host_name
3315 ,x_web_agent_name => p_web_agent_name);
3316 end if;
3317
3318 hr_utility.set_location(' Leaving:'|| l_proc, 100);
3319
3320 EXCEPTION
3321 when others then
3322 l_error_message := 'Error in ' || l_proc;
3323 hr_utility.trace(l_error_message || '-' || sqlerrm);
3324 hr_utility.set_location(' Leaving:'|| l_proc, 500);
3325 hr_utility.raise_error;
3326
3327 END create_responsibility;
3328
3329 /* --------------------------------------------------------------------------
3330 -- Name : create_resp_and_profile
3331 -- Purpose : This procedure create responsibility after making copy of the
3332 -- seeded responsibility, assign responsibility to user,
3333 -- assign PER_BUSINESS_GROUP_ID,PER_SECURITY_PROLFILE_ID
3334 -- profile option values.
3335 -- Arguments : p_configuration_code
3336 -- p_security_profile_name
3337 -- p_responsibility_key
3338 -- p_technical_summary_mode
3339 -- p_bg_sg_ut_profile_resp_tab
3340 -- p_hrms_resp_one_tab
3341 --
3342 -------------------------------------------------------------------------- */
3343
3344 PROCEDURE create_resp_and_profile
3345 (p_configuration_code in varchar2
3346 ,p_security_profile_name in varchar2
3347 ,p_responsibility_key in varchar2
3348 ,p_technical_summary_mode in boolean default FALSE
3349 ,p_bg_sg_ut_profile_resp_tab in out nocopy
3350 per_ri_config_tech_summary.profile_resp_tab
3351 ,p_hrms_resp_one_tab in out nocopy
3352 per_ri_config_tech_summary.hrms_resp_tab) IS
3353
3354 cursor csr_user IS
3355 select user_name
3356 from fnd_user
3357 where user_name = upper(per_ri_config_utilities.return_config_entity_name_pre
3358 (per_ri_config_main.g_configuration_user_name));
3359
3360 cursor csr_responsibility(cp_responsibility_kay in varchar2) IS
3361 select application_id, responsibility_id
3362 from fnd_responsibility
3363 where responsibility_key = cp_responsibility_kay;
3364
3365 cursor csr_responsibility_name(cp_responsibility_kay in varchar2) IS
3366 select responsibility_name
3367 from fnd_responsibility_vl
3368 where responsibility_key = cp_responsibility_kay;
3369
3370 cursor csr_application(cp_application_id in number) IS
3371 select application_short_name
3372 from fnd_application
3373 where application_id = cp_application_id;
3374
3375 cursor csr_business_group(cp_business_group_name in varchar2) IS
3376 select security_group_id
3377 from per_business_groups
3378 where name = cp_business_group_name;
3379
3380 cursor csr_security_profiles (cp_security_profile_name varchar2)IS
3381 select security_profile_id
3382 from per_security_profiles
3383 where security_profile_name = cp_security_profile_name;
3384
3385 cursor csr_security_group_name(cp_security_group_id in number) IS
3386 select security_group_name,security_group_key
3387 from fnd_security_groups_vl
3388 where security_group_id = cp_security_group_id;
3389
3390 l_user_name fnd_user.user_name%type;
3391 l_security_group_id fnd_security_groups.security_group_id%type;
3392 l_business_group_id per_business_groups.business_group_id%type;
3393 l_business_group_name per_business_groups.name%type;
3394 l_security_profile_id per_sec_profile_assignments.security_profile_id%type;
3395 l_responsibility_id per_sec_profile_assignments.responsibility_id%type;
3396 l_responsibility_application_i per_sec_profile_assignments.responsibility_application_id%type;
3397
3398 l_security_group_name fnd_security_groups_vl.security_group_name%type;
3399 l_security_group_key fnd_security_groups_vl.security_group_key%type;
3400 l_application_short_name fnd_application.application_short_name%type;
3401
3402 l_security_profile_name per_business_groups.name%type;
3403 l_responsibility_key fnd_responsibility_vl.responsibility_name%type;
3404 l_ts_responsibility_key fnd_responsibility_vl.responsibility_name%type;
3405 l_ts_new_responsibility_name fnd_responsibility_vl.responsibility_name%type;
3406 l_ts_responsibility_name fnd_responsibility_vl.responsibility_name%type;
3407 l_ts_new_resp_key fnd_responsibility_vl.responsibility_name%type;
3408 l_ovn number(9);
3409
3410 l_proc varchar2(72) := g_package || 'create_resp_and_profile';
3411 l_log_message varchar2(360);
3412 l_error_message varchar2(360);
3413
3414 l_bg_sg_ut_profile_resp_tab per_ri_config_tech_summary.profile_resp_tab;
3415 l_new_app_short_name fnd_application.application_short_name%type;
3416 l_new_resp_key fnd_responsibility_vl.responsibility_name%type;
3417 l_new_responsibility_id fnd_responsibility.responsibility_id%type;
3418 l_new_responsibility_name fnd_responsibility_tl.responsibility_name%type;
3419 l_new_owner varchar2(120);
3420 l_new_data_group_app_name fnd_application.application_short_name%type;
3421 l_new_data_group_name fnd_data_groups_standard_view.data_group_name%type;
3422 l_new_data_group_id fnd_data_groups_standard_view.data_group_name%type;
3423 l_new_menu_name fnd_menus.menu_name%type;
3424 l_new_start_date varchar2(240); -- must be varchar
3425 l_new_end_date varchar2(240); -- must be varchar
3426 l_new_description varchar2(240);
3427 l_new_group_app_short_name fnd_application.application_short_name%type;
3428 l_new_request_group_name fnd_request_groups.request_group_name%type;
3429 l_new_request_group_id fnd_request_groups.request_group_name%type;
3430 l_new_version fnd_responsibility.version%type;
3431 l_new_web_host_name fnd_responsibility.web_host_name%type;
3432 l_new_web_agent_name fnd_responsibility.web_agent_name%type;
3433 l_legilsation_code per_business_groups.legislation_code%type;
3434 l_hrms_resp_one_count number(9) := 0;
3435
3436 cursor csr_new_responsibility(cp_new_responsibility_key in varchar) IS
3437 select apps.application_short_name application_short_name,
3438 resp.responsibility_key responsibility_key,
3439 responsibility_id responsibility_id,
3440 responsibility_name responsibility_name,
3441 resp.data_group_id,
3442 menus.menu_name menu_name,
3443 to_char(start_date,'YYYY/MM/DD'),
3444 end_date,
3445 resp.description description,
3446 request_group_id,
3447 resp.version version,
3448 resp.web_host_name web_host_name,
3449 resp.web_agent_name web_agent_name
3450 from fnd_responsibility_vl resp,
3451 fnd_application_vl apps,
3452 fnd_menus menus
3453 where resp.application_id = apps.application_id
3454 and resp.menu_id = menus.menu_id
3455 and resp.responsibility_key = cp_new_responsibility_key;
3456
3457 cursor csr_requests_groups(cp_new_request_group_id in number) IS
3458 select request_group_name, apps.application_short_name
3459 from fnd_request_groups req,
3460 fnd_application apps
3461 where request_group_id = cp_new_request_group_id
3462 and req.application_id = apps.application_id;
3463
3464 cursor csr_data_groups(cp_new_data_group_id in number,
3465 cp_new_app_short_name in varchar2) IS
3466 select dg.data_group_name, a.application_short_name
3467 from fnd_data_group_units dgu,
3468 fnd_data_groups dg,
3469 fnd_application a
3470 where dgu.data_group_id = dg.data_group_id
3471 and dg.data_group_id = cp_new_data_group_id
3472 and dgu.application_id = a.application_id
3473 and a.application_short_name = cp_new_app_short_name;
3474
3475
3476 l_start_date varchar2(240) := to_char(per_ri_config_utilities.g_config_effective_date,'YYYY/MM/DD');
3477 l_end_date varchar2(240) := to_char(per_ri_config_utilities.g_config_effective_end_date,'YYYY/MM/DD');
3478
3479 BEGIN
3480
3481 hr_utility.set_location('Entering:'|| l_proc, 10);
3482
3483 if NOT (p_technical_summary_mode) then
3484 open csr_user;
3485 fetch csr_user into l_user_name;
3486 close csr_user;
3487
3488 l_security_profile_name := p_security_profile_name;
3489 l_responsibility_key := p_responsibility_key;
3490
3491 hr_utility.trace('l_security_profile_name := ' || l_security_profile_name);
3492 hr_utility.trace('l_responsibility_key := ' || l_responsibility_key);
3493
3494 open csr_business_group(l_security_profile_name);
3495 fetch csr_business_group into l_security_group_id;
3496 close csr_business_group;
3497
3498 open csr_security_profiles(l_security_profile_name);
3499 fetch csr_security_profiles into l_security_profile_id;
3500 close csr_security_profiles;
3501
3502 hr_utility.trace('l_security_profile_id := ' || l_security_profile_id);
3503
3504 open csr_responsibility(l_responsibility_key);
3505 fetch csr_responsibility into l_responsibility_application_i,l_responsibility_id;
3506 close csr_responsibility;
3507 hr_utility.trace('l_responsibility_key := ' || l_responsibility_key);
3508
3509 open csr_application(l_responsibility_application_i);
3510 fetch csr_application into l_application_short_name;
3511 close csr_application;
3512 hr_utility.trace('l_application_short_name := ' || l_application_short_name);
3513
3514 open csr_security_group_name(l_security_group_id);
3515 fetch csr_security_group_name into l_security_group_name, l_security_group_key;
3516 close csr_security_group_name;
3517
3518 hr_utility.trace('l_security_group_id := ' || l_security_group_id);
3519 hr_utility.trace('l_security_group_key := ' || l_security_group_key);
3520 hr_utility.trace('l_start_date := ' || l_start_date);
3521 hr_utility.trace('l_end_date := ' || l_end_date);
3522
3523 -- Create a copy of the responsibility and assign it to user.
3524 -- we can not share responsibility level profile options for
3525 -- different user.
3526
3527 open csr_new_responsibility(l_responsibility_key);
3528 fetch csr_new_responsibility into l_new_app_short_name,
3529 l_new_resp_key,
3530 l_new_responsibility_id,
3531 l_new_responsibility_name,
3532 l_new_data_group_id,
3533 l_new_menu_name,
3534 l_new_start_date,
3535 l_new_end_date,
3536 l_new_description,
3537 l_new_request_group_id,
3538 l_new_version,
3539 l_new_web_host_name,
3540 l_new_web_agent_name;
3541 close csr_new_responsibility;
3542
3543 open csr_requests_groups(l_new_request_group_id);
3544 fetch csr_requests_groups into l_new_request_group_name, l_new_group_app_short_name;
3545 close csr_requests_groups;
3546
3547 hr_utility.trace('l_new_request_group_name := ' || l_new_request_group_name);
3548
3549 hr_utility.trace('l_new_app_short_name := ' || l_new_app_short_name);
3550 hr_utility.trace('l_new_data_group_id := ' || l_new_data_group_id);
3551
3552 open csr_data_groups(l_new_data_group_id,l_new_app_short_name);
3553 fetch csr_data_groups into l_new_data_group_name, l_new_data_group_app_name;
3554 close csr_data_groups;
3555
3556 l_new_responsibility_name := per_ri_config_utilities.return_config_entity_name(l_new_responsibility_name);
3557 l_new_resp_key := upper(per_ri_config_utilities.return_config_entity_name(l_new_resp_key)); -- Fix for bug 9706310
3558
3559 hr_utility.trace('l_new_resp_key ' || l_new_resp_key);
3560 hr_utility.trace('l_new_responsibility_name ' || l_new_responsibility_name);
3561
3562 -- Modify GLB key names to country specific keys
3563 if l_new_resp_key like 'GLB_%' then
3564 -- extract legislation code
3565 l_legilsation_code := substr(l_security_profile_name,length(l_security_profile_name)-4,2);
3566 l_new_resp_key := replace(l_new_resp_key,'GLB',l_legilsation_code);
3567
3568 --append legislation code
3569 l_new_responsibility_name := l_legilsation_code || ' ' || l_new_responsibility_name;
3570 end if;
3571
3572 -- this takes care of HR and HRMS resps
3573 if l_new_resp_key like 'GLOBAL%' then
3574 -- extract legislation code
3575 hr_utility.trace('in GLobal Resp');
3576 l_legilsation_code := substr(l_security_profile_name,length(l_security_profile_name)-4,2);
3577 l_new_resp_key := replace(l_new_resp_key,'GLOBAL',l_legilsation_code);
3578
3579 --append legislation code
3580 l_new_responsibility_name := l_legilsation_code || ' ' || l_new_responsibility_name;
3581 end if;
3582
3583 per_ri_config_utilities.create_responsibility
3584 (p_app_short_name => l_new_app_short_name
3585 ,p_resp_key => l_new_resp_key
3586 ,p_responsibility_id => l_new_responsibility_id
3587 ,p_responsibility_name => l_new_responsibility_name
3588 ,p_owner => l_new_owner
3589 ,p_data_group_app_short_name => l_new_data_group_app_name
3590 ,p_data_group_name => l_new_data_group_name
3591 ,p_menu_name => l_new_menu_name
3592 ,p_start_date => l_new_start_date
3593 ,p_end_date => l_new_end_date
3594 ,p_description => l_new_description
3595 ,p_group_app_short_name => 'PER'
3596 ,p_request_group_name => l_new_request_group_name
3597 ,p_version => l_new_version
3598 ,p_web_host_name => l_new_web_host_name
3599 ,p_web_agent_name => l_new_web_agent_name);
3600
3601 -- assign this responsibility to the user
3602 fnd_user_resp_groups_api.load_row(
3603 x_user_name => l_user_name,
3604 x_resp_key => l_new_resp_key,
3605 x_app_short_name => l_new_app_short_name,
3606 x_security_group => l_security_group_key,
3607 x_owner => 'SEED',
3608 x_start_date => l_start_date,
3609 x_end_date => l_end_date,
3610 x_description => 'Created by Enterprise Structure Configuration');
3611
3612 hr_utility.trace('Assigned Responsibility: ' || l_new_resp_key || ' ' || l_security_group_name);
3613 hr_utility.set_location(l_proc, 40);
3614
3615 per_ri_config_fnd_hr_entity.create_bg_id_and_sg_id_profile
3616 (p_configuration_code => p_configuration_code
3617 ,p_responsibility_key => l_new_resp_key
3618 ,p_business_group_name => l_security_profile_name
3619 ,p_technical_summary_mode => p_technical_summary_mode
3620 ,p_bg_sg_ut_profile_resp_tab => l_bg_sg_ut_profile_resp_tab);
3621
3622 else
3623
3624 -- security profile name and business group name is same
3625 -- get the name of new responsibility key and respobnsibility name
3626
3627 l_user_name := upper(per_ri_config_utilities.return_config_entity_name_pre
3628 (per_ri_config_main.g_configuration_user_name));
3629 l_security_profile_name := p_security_profile_name;
3630 l_responsibility_key := p_responsibility_key;
3631
3632 open csr_responsibility_name(l_responsibility_key);
3633 fetch csr_responsibility_name into l_ts_responsibility_name;
3634 close csr_responsibility_name;
3635
3636 l_ts_new_responsibility_name := per_ri_config_utilities.return_config_entity_name(l_ts_responsibility_name);
3637 l_ts_new_resp_key := per_ri_config_utilities.return_config_entity_name(p_responsibility_key);
3638
3639 -- Modify GLB key names to country specific keys
3640 if (l_ts_new_resp_key like 'GLB_%') and (p_security_profile_name not like '%INT BG') then
3641 -- extract legislation code
3642 l_legilsation_code := substr(p_security_profile_name,length(l_security_profile_name)-4,2);
3643 l_ts_new_resp_key := replace(l_ts_new_resp_key,'GLB',l_legilsation_code);
3644
3645 --append legislation code
3646 l_ts_new_responsibility_name := l_legilsation_code || ' ' || l_ts_new_responsibility_name;
3647 end if;
3648
3649 -- this takes care of HR and HRMS resps
3650 -- Modify GLB key names to country specific keys
3651 if (l_ts_new_resp_key like 'GLOBAL%') and (p_security_profile_name not like '%INT BG') then
3652 -- extract legislation code
3653 l_legilsation_code := substr(p_security_profile_name,length(l_security_profile_name)-4,2);
3654 l_ts_new_resp_key := replace(l_ts_new_resp_key,'GLOBAL',l_legilsation_code);
3655
3656 --append legislation code
3657 l_ts_new_responsibility_name := l_legilsation_code || ' ' || l_ts_new_responsibility_name;
3658 end if;
3659
3660 per_ri_config_fnd_hr_entity.create_bg_id_and_sg_id_profile
3661 (p_configuration_code => p_configuration_code
3662 ,p_responsibility_key => l_ts_new_resp_key
3663 ,p_business_group_name => p_security_profile_name
3664 ,p_technical_summary_mode => p_technical_summary_mode
3665 ,p_bg_sg_ut_profile_resp_tab => l_bg_sg_ut_profile_resp_tab);
3666
3667 -- modify the responsibility key to name to be displayed in TS data
3668 l_bg_sg_ut_profile_resp_tab(0).level_value := l_ts_new_responsibility_name;
3669 l_bg_sg_ut_profile_resp_tab(1).level_value := l_ts_new_responsibility_name;
3670 l_bg_sg_ut_profile_resp_tab(2).level_value := l_ts_new_responsibility_name;
3671
3672 p_bg_sg_ut_profile_resp_tab := l_bg_sg_ut_profile_resp_tab; -- three rows
3673
3674 --new date for responsibility population
3675 p_hrms_resp_one_tab(l_hrms_resp_one_count).user_name := l_user_name;
3676 p_hrms_resp_one_tab(l_hrms_resp_one_count).resp_key := l_ts_new_responsibility_name;
3677 p_hrms_resp_one_tab(l_hrms_resp_one_count).app_short_name := 'PER';
3678 p_hrms_resp_one_tab(l_hrms_resp_one_count).security_group := p_security_profile_name;
3679 p_hrms_resp_one_tab(l_hrms_resp_one_count).owner := 'SEED';
3680 p_hrms_resp_one_tab(l_hrms_resp_one_count).start_date := l_start_date;
3681 p_hrms_resp_one_tab(l_hrms_resp_one_count).end_date := l_end_date;
3682 p_hrms_resp_one_tab(l_hrms_resp_one_count).description := per_ri_config_main.g_description_string;
3683 --
3684 hr_utility.set_location(' Leaving:'|| l_proc, 100);
3685 end if;
3686 EXCEPTION
3687 when others then
3688 l_error_message := 'Error in ' || l_proc;
3689 hr_utility.trace(l_error_message || '-' || sqlerrm);
3690 hr_utility.set_location(' Leaving:'|| l_proc, 500);
3691
3692 END create_resp_and_profile;
3693
3694 /* --------------------------------------------------------------------------
3695 -- Name : get_responsibility_name
3696 -- Purpose : This function returns name of responsibility from the key
3697 -- Arguments : p_responsibility_key
3698 --
3699 -------------------------------------------------------------------------- */
3700
3701 FUNCTION get_responsibility_name (p_responsibility_key in varchar2)
3702 RETURN varchar2 IS
3703
3704 cursor csr_responsibility_key
3705 (cp_responsibility_key in varchar2) IS
3706 select responsibility_name
3707 from fnd_responsibility_vl
3708 where responsibility_key = cp_responsibility_key;
3709
3710 l_responsibility_name fnd_responsibility_vl.responsibility_name%type;
3711
3712 l_proc varchar2(72) := g_package || 'get_responsibility_name';
3713 l_error_message varchar2(360);
3714
3715 BEGIN
3716 hr_utility.set_location('Entering:'|| l_proc, 10);
3717
3718 open csr_responsibility_key(p_responsibility_key);
3719
3720 fetch csr_responsibility_key into
3721 l_responsibility_name;
3722 if csr_responsibility_key%NOTFOUND then
3723 hr_utility.trace('Responsibility not found');
3724 l_responsibility_name := p_responsibility_key;
3725 end if;
3726
3727 close csr_responsibility_key;
3728
3729 hr_utility.set_location(' Leaving:'|| l_proc, 30);
3730
3731 return l_responsibility_name;
3732
3733 EXCEPTION
3734 when others then
3735 l_error_message := 'Error in ' || l_proc;
3736 hr_utility.trace(l_error_message || '-' || sqlerrm);
3737 hr_utility.set_location(' Leaving:'|| l_proc, 500);
3738 hr_utility.raise_error;
3739
3740 END get_responsibility_name;
3741
3742 -------------------------------------------------------------------------------
3743 -- This Function returns the business group name through which the user has
3744 -- logged in to the system.
3745 -------------------------------------------------------------------------------
3746 FUNCTION get_business_group_name
3747 RETURN varchar2 IS
3748
3749 cursor bg_name IS
3750 select name from per_business_groups
3751 where business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID');
3752
3753 l_bg_name varchar2(50);
3754
3755 BEGIN
3756
3757 hr_utility.set_location('Entering get_business_group_name ' , 10);
3758
3759 open bg_name;
3760 fetch bg_name into l_bg_name;
3761 close bg_name;
3762
3763 return l_bg_name;
3764
3765 END get_business_group_name;
3766
3767 END per_ri_config_utilities;