[Home] [Help]
Skip to content
PACKAGE BODY: APPS.PER_RI_CONFIG_MAIN
Source
1 PACKAGE BODY per_ri_config_main AS
2 /* $Header: perricmn.pkb 120.4.12010000.8 2009/08/31 08:36:00 psengupt ship $ */
3
4 g_config_effective_date date := TRUNC(TO_DATE('1951/01/01', 'YYYY/MM/DD'));
5 g_config_effective_end_date date := TRUNC(TO_DATE('4712/12/31', 'YYYY/MM/DD'));
6 g_package varchar2(30) := 'per_ri_config_main.';
7
8 PROCEDURE create_technical_summary (p_errbuff out nocopy varchar2
9 ,p_retcode out nocopy number
10 ,p_configuration_code in varchar2
11 ,p_technical_summary_mode in boolean default false
12 ,p_user_clob in out nocopy clob
13 ,p_resp_clob out nocopy clob
14 ,p_location_clob out nocopy clob
15 ,p_bg_clob out nocopy clob
16 ,p_sg_clob out nocopy clob
17 ,p_int_hrms_clob out nocopy clob
18 ,p_key_str_clob out nocopy clob
19 ,p_key_seg_clob out nocopy clob
20 ,p_site_profile_clob out nocopy clob
21 ,p_profile_apps_clob out nocopy clob
22 ,p_profile_resp_clob out nocopy clob
23 ,p_organization_clob out nocopy clob
24 ,p_org_class_clob out nocopy clob
25 ,p_org_class_for_pv_clob out nocopy clob
26 ,p_org_hierarchy_ele_clob out nocopy clob
27 ,p_str_seg_for_pv_clob out nocopy clob
28 ,p_org_hier_ele_for_pv_clob out nocopy clob
29 ,p_org_hierarchy_clob out nocopy clob
30 ,p_post_install_clob out nocopy clob
31 ,p_kf_job_str_clob out nocopy clob
32 ,p_kf_job_rv_str_clob out nocopy clob
33 ,p_kf_job_no_rv_str_clob out nocopy clob
34 ,p_kf_pos_str_clob out nocopy clob
35 ,p_kf_pos_rv_str_clob out nocopy clob
36 ,p_kf_pos_no_rv_str_clob out nocopy clob
37 ,p_kf_grd_str_clob out nocopy clob
38 ,p_kf_cmp_str_clob out nocopy clob
39 ,p_kf_grp_str_clob out nocopy clob
40 ,p_kf_cost_str_clob out nocopy clob
41 ,p_kf_job_seg_clob out nocopy clob
42 ,p_kf_job_rv_seg_clob out nocopy clob
43 ,p_kf_job_no_rv_seg_clob out nocopy clob
44 ,p_kf_pos_seg_clob out nocopy clob
45 ,p_kf_pos_rv_seg_clob out nocopy clob
46 ,p_kf_grd_seg_clob out nocopy clob
47 ,p_kf_grd_no_rv_seg_clob out nocopy clob
48 ,p_kf_grd_rv_seg_clob out nocopy clob
49 ,p_kf_grp_seg_clob out nocopy clob
50 ,p_kf_cmp_seg_clob out nocopy clob
51 ,p_kf_cost_seg_clob out nocopy clob
52 ,p_kf_pos_no_rv_seg_clob out nocopy clob) IS
53
54 cursor csr_config_business_groups(cp_configuration_code in varchar2) IS
55 select distinct configuration_code,
56 per_ri_config_utilities.business_group_decision(configuration_code,country_code)
57 from per_ri_config_country_v
58 where configuration_code = cp_configuration_code;
59
60 l_proc varchar2(72) := g_package || 'create_technical_summary';
61 l_log_message varchar2(360);
62 l_error_message varchar2(360);
63 l_batch_header_id number(15);
64 l_batch_name hr_pump_batch_headers.batch_name%type;
65 l_errbuf varchar2(240);
66 l_retcode number(15);
67 l_job_defined boolean default FALSE;
68 l_pos_defined boolean default FALSE;
69 l_grd_defined boolean default FALSE;
70
71 l_jp_rv_defined boolean default FALSE;
72 l_grd_rv_defined boolean default FALSE;
73 l_country_tab per_ri_config_datapump_entity.country_tab;
74 l_security_profile_tab per_ri_config_fnd_hr_entity.security_profile_tab;
75 l_int_bg_resp_tab per_ri_config_fnd_hr_entity.int_bg_resp_tab;
76
77 l_errbuf_msg varchar2(240);
78 l_retcode_msg number(15);
79 l_fresh_installed boolean;
80 l_data_pump_exception boolean;
81
82 l_enterprise_short_name per_ri_config_information.config_information1%type;
83 not_fresh_install EXCEPTION;
84 data_pump_load_failure EXCEPTION;
85
86 l_user_tab per_ri_config_tech_summary.user_tab;
87 l_resp_tab per_ri_config_tech_summary.resp_tab;
88 l_hrms_resp_tab per_ri_config_tech_summary.hrms_resp_tab;
89 l_hrms_misc_resp_tab per_ri_config_tech_summary.hrms_misc_resp_tab;
90 l_profile_tab per_ri_config_tech_summary.profile_tab;
91 l_profile_apps_tab per_ri_config_tech_summary.profile_apps_tab;
92 l_profile_dpe_ent_tab per_ri_config_tech_summary.profile_dpe_ent_tab;
93
94 l_location_tab per_ri_config_tech_summary.location_tab;
95 l_bg_tab per_ri_config_tech_summary.bg_tab;
96 l_sg_tab per_ri_config_tech_summary.sg_tab;
97 l_post_install_tab per_ri_config_tech_summary.post_install_tab;
98 l_org_ent_tab per_ri_config_tech_summary.org_ent_tab;
99
100 l_org_hierarchy_tab per_ri_config_tech_summary.org_hierarchy_tab;
101 l_org_hierarchy_ele_oc_tab per_ri_config_tech_summary.org_hierarchy_ele_oc_tab;
102 l_org_hierarchy_ele_le_tab per_ri_config_tech_summary.org_hierarchy_ele_le_tab;
103
104 l_org_oc_tab per_ri_config_tech_summary.org_oc_tab;
105 l_org_le_tab per_ri_config_tech_summary.org_le_tab;
106
107 l_org_ent_class_tab per_ri_config_tech_summary.org_ent_class_tab;
108 l_org_oc_class_tab per_ri_config_tech_summary.org_oc_class_tab;
109 l_org_le_class_tab per_ri_config_tech_summary.org_le_class_tab;
110
111 l_kf_job_tab per_ri_config_tech_summary.kf_job_tab;
112 l_kf_pos_tab per_ri_config_tech_summary.kf_pos_tab;
113 l_kf_grd_tab per_ri_config_tech_summary.kf_grd_tab;
114
115 l_kf_job_seg_tab per_ri_config_tech_summary.kf_job_seg_tab;
116 l_kf_pos_seg_tab per_ri_config_tech_summary.kf_pos_seg_tab;
117 l_kf_grd_seg_tab per_ri_config_tech_summary.kf_grd_seg_tab;
118
119 l_kf_grp_tab per_ri_config_tech_summary.kf_grp_tab;
120 l_kf_cmp_tab per_ri_config_tech_summary.kf_cmp_tab;
121 l_kf_cost_tab per_ri_config_tech_summary.kf_cost_tab;
122 l_kf_grp_seg_tab per_ri_config_tech_summary.kf_grp_seg_tab;
123 l_kf_cmp_seg_tab per_ri_config_tech_summary.kf_cmp_seg_tab;
124 l_kf_cost_seg_tab per_ri_config_tech_summary.kf_cost_seg_tab;
125
126 l_kf_job_no_rv_tab per_ri_config_tech_summary.kf_job_no_rv_tab;
127 l_kf_job_no_rv_seg_tab per_ri_config_tech_summary.kf_job_no_rv_seg_tab;
128
129 l_kf_job_rv_tab per_ri_config_tech_summary.kf_job_rv_tab;
130 l_kf_job_rv_seg_tab per_ri_config_tech_summary.kf_job_rv_seg_tab;
131
132 l_kf_pos_no_rv_tab per_ri_config_tech_summary.kf_pos_no_rv_tab;
133 l_kf_pos_no_rv_seg_tab per_ri_config_tech_summary.kf_pos_no_rv_seg_tab;
134
135 l_kf_pos_rv_tab per_ri_config_tech_summary.kf_pos_rv_tab;
136 l_kf_pos_rv_seg_tab per_ri_config_tech_summary.kf_pos_rv_seg_tab;
137
138 l_kf_grd_no_rv_tab per_ri_config_tech_summary.kf_grd_no_rv_tab;
139 l_kf_grd_no_rv_seg_tab per_ri_config_tech_summary.kf_grd_no_rv_seg_tab;
140
141 l_kf_grd_rv_tab per_ri_config_tech_summary.kf_grd_rv_tab;
142 l_kf_grd_rv_seg_tab per_ri_config_tech_summary.kf_grd_rv_seg_tab;
143
144 l_int_hrms_setup_tab per_ri_config_tech_summary.int_hrms_setup_tab;
145
146 l_kf_job_clob clob;
147 l_kf_job_rv_clob clob;
148 l_kf_job_no_rv_clob clob;
149 l_kf_pos_clob clob;
150 l_kf_pos_rv_clob clob;
151 l_kf_pos_no_rv_clob clob;
152 l_kf_grd_clob clob;
153 l_kf_grd_rv_clob clob;
154 l_kf_grd_no_rv_clob clob;
155 l_kf_cmp_clob clob;
156 l_kf_grp_clob clob;
157 l_kf_cost_clob clob;
158 l_kf_job_seg_clob clob;
159 l_kf_job_rv_seg_clob clob;
160 l_kf_job_no_rv_seg_clob clob;
161 l_kf_pos_seg_clob clob;
162 l_kf_pos_rv_seg_clob clob;
163 l_kf_grd_seg_clob clob;
164 l_kf_grd_no_rv_seg_clob clob;
165 l_kf_grd_rv_seg_clob clob;
166 l_kf_grp_seg_clob clob;
167 l_kf_cmp_seg_clob clob;
168 l_kf_cost_seg_clob clob;
169 l_kf_pos_no_rv_seg_clob clob;
170
171 l_more_profile_resp_tab per_ri_config_tech_summary.profile_resp_tab;
172 l_more_int_profile_resp_tab per_ri_config_tech_summary.profile_resp_tab;
173 l_hrms_resp_main_tab per_ri_config_tech_summary.hrms_resp_tab;
174
175 l_multi_tenancy boolean default false;
176 l_system_model varchar2(10) := 'N';
177 l_sec_group_id number(10);
178 l_enterprise_id number(10);
179 l_bg_count number(10);
180 l_configuration_code varchar2(50);
181 l_bg_country_name varchar2(50);
182
183 PRAGMA Exception_Init(not_fresh_install, -20001);
184 PRAGMA Exception_Init(data_pump_load_failure, -20002);
185
186 BEGIN
187
188
189 hr_utility.set_location('Entering:'|| l_proc, 10);
190
191 hr_utility.trace('Starting Time = ' || to_char(sysdate,'DD-Mon-YYYY HH24:MI:SS'));
192
193 l_multi_tenancy := hr_multi_tenancy_pkg.is_multi_tenant_system;
194 l_sec_group_id := fnd_global.security_group_id;
195 if l_multi_tenancy then
196 l_system_model := hr_multi_tenancy_pkg.get_system_model;
197 end if;
198
199
200 BEGIN
201 select enterprise_id into l_enterprise_id from per_ent_security_groups
202 where business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID');
203 EXCEPTION
204 WHEN NO_DATA_FOUND THEN
205 l_enterprise_id := null;
206 END;
207
208 -- Set global variable for loader program mode
209 g_technical_summary_mode := p_technical_summary_mode;
210
211 if g_technical_summary_mode then
212 hr_utility.trace('Technical Summary Mode');
213 end if;
214
215 l_job_defined := per_ri_config_utilities.jpg_defined
216 (p_configuration_code => p_configuration_code
217 ,p_seg_type => 'JOB');
218
219 l_pos_defined := per_ri_config_utilities.jpg_defined
220 (p_configuration_code => p_configuration_code
221 ,p_seg_type => 'POSITION');
222
223 l_grd_defined := per_ri_config_utilities.jpg_defined
224 (p_configuration_code => p_configuration_code
225 ,p_seg_type => 'GRADE');
226
227 l_jp_rv_defined := per_ri_config_utilities.regional_variance_defined
228 (p_configuration_code => p_configuration_code
229 ,p_rv_type => 'JP');
230
231 l_grd_rv_defined := per_ri_config_utilities.regional_variance_defined
232 (p_configuration_code => p_configuration_code
233 ,p_rv_type => 'GRD');
234
235 hr_utility.set_location(l_proc, 20);
236
237 -- populate enterprise short name global variable
238 l_enterprise_short_name := per_ri_config_utilities.get_enterprise_short_name(p_configuration_code);
239 --
240
241 --
242 -- create HRMS_USER super user
243 --
244 per_ri_config_fnd_def_entity.create_user(p_technical_summary_mode => p_technical_summary_mode
245 ,p_user_tab => l_user_tab);
246
247 p_user_clob := per_ri_config_tech_summary.get_user_sql (l_user_tab);
248
249 hr_utility.set_location(l_proc, 30);
250
251 --
252 -- attach default responsibility
253 --
254 per_ri_config_fnd_def_entity.attach_default_responsibility
255 (p_configuration_code => p_configuration_code
256 ,p_technical_summary_mode => p_technical_summary_mode
257 ,p_resp_tab => l_resp_tab
258 );
259
260 hr_utility.set_location(l_proc, 40);
261
262 --
263 -- set site level profile options
264 --
265 per_ri_config_fnd_def_entity.create_site_profile_options
266 (p_configuration_code => p_configuration_code
267 ,p_technical_summary_mode => p_technical_summary_mode
268 ,p_profile_tab => l_profile_tab);
269
270 --p_site_profile_clob := per_ri_config_tech_summary.get_profile_sql (l_profile_tab);
271 -- to be called after enterprise_line
272
273 --
274 -- set aplication level profile options
275 --
276 per_ri_config_fnd_hr_entity.create_application_level_resp
277 (p_configuration_code => p_configuration_code
278 ,p_technical_summary_mode => p_technical_summary_mode
279 ,p_profile_apps_tab => l_profile_apps_tab);
280
281 hr_utility.set_location(l_proc, 50);
282 --
283 -- add hrms responsibilities to created user.
284 --
285 per_ri_config_fnd_hr_entity.create_hrms_responsibility
286 (p_configuration_code => p_configuration_code
287 ,p_security_profile_tab => l_security_profile_tab
288 ,p_technical_summary_mode => p_technical_summary_mode
289 ,p_hrms_resp_tab => l_hrms_resp_tab);
290
291
292 hr_utility.set_location(l_proc, 60);
293
294 --
295 -- add hrms misc responsibilities to created user.
296 --
297 per_ri_config_utilities.assign_misc_responsibility
298 (p_configuration_code => p_configuration_code
299 ,p_technical_summary_mode => p_technical_summary_mode
300 ,p_hrms_misc_resp_tab => l_hrms_misc_resp_tab);
301
302
303 hr_utility.set_location(l_proc, 65);
304
305 --
306 -- Take Decision on the creation of flexfield structures
307 --
308
309 l_bg_count := 0;
310 open csr_config_business_groups(p_configuration_code);
311 loop
312 fetch csr_config_business_groups into
313 l_configuration_code,
314 l_bg_country_name;
315 if csr_config_business_groups%NOTFOUND then
316 --close csr_config_business_groups;
317 exit;
318 end if;
319 l_bg_count := l_bg_count + 1;
320 end loop;
321 close csr_config_business_groups;
322
323 hr_utility.set_location('BG Count is : '|| l_bg_count, 45);
324
325
326 IF l_multi_tenancy AND l_system_model = 'B' AND l_sec_group_id <> '0'
327 AND l_enterprise_id IS NOT NULL AND l_bg_count=1 then
328 hr_utility.set_location('No flex structures required', 11);
329 ELSE
330
331 per_ri_config_fnd_hr_entity.create_global_grp_cmp_cost_kf
332 (p_configuration_code => p_configuration_code
333 ,p_technical_summary_mode => p_technical_summary_mode
334 ,p_kf_grp_tab => l_kf_grp_tab
335 ,p_kf_cmp_tab => l_kf_cmp_tab
336 ,p_kf_cost_tab => l_kf_cost_tab
337 ,p_kf_grp_seg_tab => l_kf_grp_seg_tab
338 ,p_kf_cmp_seg_tab => l_kf_cmp_seg_tab
339 ,p_kf_cost_seg_tab => l_kf_cost_seg_tab
340 );
341 hr_utility.set_location(l_proc, 70);
342
343
344 --
345 -- make decisions about job, positions and grades
346 --
347
348 --jobs/positions not defined
349 if NOT l_job_defined and NOT l_pos_defined then
350 hr_utility.trace('Jobs/Positions NOT defined');
351 --
352 -- create global jobs and positions
353 --
354 per_ri_config_fnd_hr_entity.create_global_job_pos_kf
355 (p_configuration_code => p_configuration_code
356 ,p_technical_summary_mode => p_technical_summary_mode
357 ,p_kf_job_tab => l_kf_job_tab
358 ,p_kf_pos_tab => l_kf_pos_tab
359 ,p_kf_job_seg_tab => l_kf_job_seg_tab
360 ,p_kf_pos_seg_tab => l_kf_pos_seg_tab
361 );
362 hr_utility.set_location(l_proc, 80);
363 end if;
364
365 --when positions not defined - this case comes when
366 -- user has selected only job in RV section.
367
368 if NOT l_pos_defined then
369 hr_utility.trace('Positions NOT defined');
370 --
371 -- create global positions
372 --
373 per_ri_config_fnd_hr_entity.create_global_pos_kf
374 (p_configuration_code => p_configuration_code
375 ,p_technical_summary_mode => p_technical_summary_mode
376 ,p_kf_pos_tab => l_kf_pos_tab
377 ,p_kf_pos_seg_tab => l_kf_pos_seg_tab
378 );
379 hr_utility.set_location(l_proc, 85);
380 end if;
381 --grades are not defined
382 if NOT l_grd_defined then
383 hr_utility.trace('Grades NOT defined');
384 --
385 -- create global grades
386 --
387 per_ri_config_fnd_hr_entity.create_global_grd_kf
388 (p_configuration_code => p_configuration_code
389 ,p_technical_summary_mode => p_technical_summary_mode
390 ,p_kf_grd_tab => l_kf_grd_tab
391 ,p_kf_grd_seg_tab => l_kf_grd_seg_tab
392 );
393 hr_utility.set_location(l_proc, 90);
394 end if;
395
396 -- jobs defined (including RVs)
397 if l_job_defined or l_jp_rv_defined then
398 hr_utility.trace('Jobs defined');
399 --
400 -- create jobs with no rvs
401 --
402 per_ri_config_fnd_hr_entity.create_jobs_no_rv_keyflex
403 (p_configuration_code => p_configuration_code
404 ,p_technical_summary_mode => p_technical_summary_mode
405 ,p_kf_job_no_rv_tab => l_kf_job_no_rv_tab
406 ,p_kf_job_no_rv_seg_tab => l_kf_job_no_rv_seg_tab
407 );
408 hr_utility.set_location(l_proc, 100);
409 -- create jobs with rvs
410 --
411 per_ri_config_fnd_hr_entity.create_jobs_rv_keyflex
412 (p_configuration_code => p_configuration_code
413 ,p_technical_summary_mode => p_technical_summary_mode
414 ,p_kf_job_rv_tab => l_kf_job_rv_tab
415 ,p_kf_job_rv_seg_tab => l_kf_job_rv_seg_tab
416 );
417 hr_utility.set_location(l_proc, 110);
418 end if;
419
420 -- Positions defined (including RVs)
421 if l_pos_defined or (l_pos_defined and l_jp_rv_defined) then -- fix for 4522666
422 hr_utility.trace('Positions IS defined');
423 --
424 -- create positions with no rvs
425 --
426 per_ri_config_fnd_hr_entity.create_positions_no_rv_keyflex
427 (p_configuration_code => p_configuration_code
428 ,p_technical_summary_mode => p_technical_summary_mode
429 ,p_kf_pos_no_rv_tab => l_kf_pos_no_rv_tab
430 ,p_kf_pos_no_rv_seg_tab => l_kf_pos_no_rv_seg_tab
431 );
432 hr_utility.set_location(l_proc, 120);
433 --
434 -- create positions with rvs
435 --
436 per_ri_config_fnd_hr_entity.create_positions_rv_keyflex
437 (p_configuration_code => p_configuration_code
438 ,p_technical_summary_mode => p_technical_summary_mode
439 ,p_kf_pos_rv_tab => l_kf_pos_rv_tab
440 ,p_kf_pos_rv_seg_tab => l_kf_pos_rv_seg_tab
441 );
442 hr_utility.set_location(l_proc, 130);
443 end if;
444
445 -- Grades defined (including RVs)
446 if l_grd_defined or l_grd_rv_defined then
447 hr_utility.trace('Grades IS defined');
448 --
449 -- create grades with no rvs
450 --
451 per_ri_config_fnd_hr_entity.create_grades_no_rv_keyflex
452 (p_configuration_code => p_configuration_code
453 ,p_technical_summary_mode => p_technical_summary_mode
454 ,p_kf_grd_no_rv_tab => l_kf_grd_no_rv_tab
455 ,p_kf_grd_no_rv_seg_tab => l_kf_grd_no_rv_seg_tab
456 );
457 hr_utility.set_location(l_proc, 140);
458 --
459 -- create grades with rvs
460 --
461 per_ri_config_fnd_hr_entity.create_grades_rv_keyflex
462 (p_configuration_code => p_configuration_code
463 ,p_technical_summary_mode => p_technical_summary_mode
464 ,p_kf_grd_rv_tab => l_kf_grd_rv_tab
465 ,p_kf_grd_rv_seg_tab => l_kf_grd_rv_seg_tab
466 );
467 hr_utility.set_location(l_proc, 150);
468 end if;
469
470 -- Get Keyflex and Segments
471 p_key_str_clob := per_ri_config_tech_summary.get_keyflex_structure_sql
472 (p_kf_job_tab => l_kf_job_tab
473 ,p_kf_job_rv_tab => l_kf_job_rv_tab
474 ,p_kf_job_no_rv_tab => l_kf_job_no_rv_tab
475 ,p_kf_pos_tab => l_kf_pos_tab
476 ,p_kf_pos_rv_tab => l_kf_pos_rv_tab
477 ,p_kf_pos_no_rv_tab => l_kf_pos_no_rv_tab
478 ,p_kf_grd_tab => l_kf_grd_tab
479 ,p_kf_grd_rv_tab => l_kf_grd_rv_tab
480 ,p_kf_grd_no_rv_tab => l_kf_grd_no_rv_tab
481 ,p_kf_cmp_tab => l_kf_cmp_tab
482 ,p_kf_grp_tab => l_kf_grp_tab
483 ,p_kf_cost_tab => l_kf_cost_tab
484 ,p_kf_job_str_clob => l_kf_job_clob
485 ,p_kf_job_rv_str_clob => l_kf_job_rv_clob
486 ,p_kf_job_no_rv_str_clob => l_kf_job_no_rv_clob
487 ,p_kf_pos_str_clob => l_kf_pos_clob
488 ,p_kf_pos_rv_str_clob => l_kf_pos_rv_clob
489 ,p_kf_pos_no_rv_str_clob => l_kf_pos_no_rv_clob
490 ,p_kf_cmp_str_clob => l_kf_cmp_clob
491 ,p_kf_grd_str_clob => l_kf_grd_clob
492 ,p_kf_grp_str_clob => l_kf_grp_clob
493 ,p_kf_cost_str_clob => l_kf_cost_clob
494 );
495
496
497 p_key_seg_clob := per_ri_config_tech_summary.get_keyflex_segment_sql
498 (p_kf_job_seg_tab => l_kf_job_seg_tab
499 ,p_kf_job_rv_seg_tab => l_kf_job_rv_seg_tab
500 ,p_kf_job_no_rv_seg_tab => l_kf_job_no_rv_seg_tab
501 ,p_kf_pos_seg_tab => l_kf_pos_seg_tab
502 ,p_kf_pos_rv_seg_tab => l_kf_pos_rv_seg_tab
503 ,p_kf_pos_no_rv_seg_tab => l_kf_pos_no_rv_seg_tab
504 ,p_kf_grd_seg_tab => l_kf_grd_seg_tab
505 ,p_kf_grd_rv_seg_tab => l_kf_grd_rv_seg_tab
506 ,p_kf_grd_no_rv_seg_tab => l_kf_grd_no_rv_seg_tab
507 ,p_kf_grp_seg_tab => l_kf_grp_seg_tab
508 ,p_kf_cmp_seg_tab => l_kf_cmp_seg_tab
509 ,p_kf_cost_seg_tab => l_kf_cost_seg_tab
510 ,p_kf_job_seg_clob => l_kf_job_seg_clob
511 ,p_kf_job_rv_seg_clob => l_kf_job_rv_seg_clob
512 ,p_kf_job_no_rv_seg_clob => l_kf_job_no_rv_seg_clob
513 ,p_kf_pos_seg_clob => l_kf_pos_seg_clob
514 ,p_kf_pos_rv_seg_clob => l_kf_pos_rv_seg_clob
515 ,p_kf_pos_no_rv_seg_clob => l_kf_pos_no_rv_seg_clob
516 ,p_kf_grd_seg_clob => l_kf_grd_seg_clob
517 ,p_kf_grd_rv_seg_clob => l_kf_grd_rv_seg_clob
518 ,p_kf_grd_no_rv_seg_clob => l_kf_grd_no_rv_seg_clob
519 ,p_kf_grp_seg_clob => l_kf_grp_seg_clob
520 ,p_kf_cmp_seg_clob => l_kf_cmp_seg_clob
521 ,p_kf_cost_seg_clob => l_kf_cost_seg_clob
522 );
523
524 p_kf_job_str_clob := l_kf_job_clob;
525 p_kf_pos_str_clob := l_kf_pos_clob;
526 p_kf_pos_rv_str_clob := l_kf_pos_rv_clob;
527 p_kf_pos_no_rv_str_clob := l_kf_pos_no_rv_clob;
528 p_kf_grd_str_clob := l_kf_grd_clob;
529 p_kf_cmp_str_clob := l_kf_cmp_clob;
530 p_kf_grp_str_clob := l_kf_grp_clob;
531 p_kf_cost_str_clob := l_kf_cost_clob;
532 p_kf_job_seg_clob := l_kf_job_seg_clob;
533 p_kf_job_rv_seg_clob := l_kf_job_rv_seg_clob;
534 p_kf_job_no_rv_seg_clob := l_kf_job_no_rv_seg_clob;
535 p_kf_pos_seg_clob := l_kf_pos_seg_clob;
536 p_kf_pos_rv_seg_clob := l_kf_pos_rv_seg_clob;
537 p_kf_pos_no_rv_seg_clob := l_kf_pos_no_rv_seg_clob;
538 p_kf_grd_seg_clob := l_kf_grd_seg_clob;
539 p_kf_grd_no_rv_seg_clob := l_kf_grd_no_rv_seg_clob;
540 p_kf_grd_rv_seg_clob := l_kf_grd_rv_seg_clob;
541 p_kf_grp_seg_clob := l_kf_grp_seg_clob;
542 p_kf_cmp_seg_clob := l_kf_cmp_seg_clob;
543 p_kf_cost_seg_clob := l_kf_cost_seg_clob;
544
545 hr_utility.set_location(l_proc, 170);
546
547 END IF;
548 --
549 -- create locations
550 --
551 per_ri_config_datapump_entity.create_locations_batch_lines
552 (p_configuration_code => p_configuration_code
553 ,p_batch_header_id => l_batch_header_id
554 ,p_technical_summary_mode => p_technical_summary_mode
555 ,p_location_tab => l_location_tab
556 );
557
558 p_location_clob := per_ri_config_tech_summary.get_locations_sql (p_location_tab => l_location_tab) ;
559
560
561 hr_utility.set_location(l_proc, 180);
562
563
564 --
565 -- create business groups
566 --
567 per_ri_config_datapump_entity.create_bg_batch_lines
568 (p_batch_header_id => l_batch_header_id
569 ,p_configuration_code => p_configuration_code
570 ,p_country_tab_out => l_country_tab
571 ,p_technical_summary_mode => p_technical_summary_mode
572 ,p_bg_tab => l_bg_tab
573 ,p_sg_tab => l_sg_tab
574 ,p_post_install_tab => l_post_install_tab
575 ,p_int_bg_resp_tab => l_int_bg_resp_tab);
576
577 p_bg_clob := per_ri_config_tech_summary.get_business_grp_sql (p_business_grp_tab => l_bg_tab);
578
579 p_sg_clob:= per_ri_config_tech_summary.get_security_profile_sql(
580 p_security_profile_tab => l_sg_tab
581 );
582 p_post_install_clob := per_ri_config_tech_summary.get_post_install_sql(
583 p_post_install_tab =>l_post_install_tab
584 );
585 hr_utility.set_location(l_proc, 190);
586 --
587 -- create enterprise orgs and related classifications
588 --
589 per_ri_config_datapump_entity.create_enterprise_batch_lines
590 (p_configuration_code => p_configuration_code
591 ,p_batch_header_id => l_batch_header_id
592 ,p_technical_summary_mode => p_technical_summary_mode
593 ,p_org_ent_tab => l_org_ent_tab
594 ,p_org_ent_class_tab => l_org_ent_class_tab
595 ,p_org_hierarchy_tab => l_org_hierarchy_tab
596 ,p_profile_dpe_ent_tab => l_profile_dpe_ent_tab);
597
598
599 p_org_hierarchy_clob := per_ri_config_tech_summary.get_org_hierarchy_sql(
600 p_org_hierarchy_tab => l_org_hierarchy_tab
601 );
602
603 p_site_profile_clob := per_ri_config_tech_summary.get_profile_sql (
604 p_profile_tab => l_profile_tab
605 ,p_profile_dpe_ent_tab => l_profile_dpe_ent_tab
606 );
607 hr_utility.set_location(l_proc, 200);
608
609 --
610 -- create operating companies orgs and related classifications
611 --
612 per_ri_config_datapump_entity.create_oper_comp_batch_lines
613 (p_configuration_code => p_configuration_code
614 ,p_batch_header_id => l_batch_header_id
615 ,p_technical_summary_mode => p_technical_summary_mode
616 ,p_org_oc_tab => l_org_oc_tab
617 ,p_org_oc_class_tab => l_org_oc_class_tab
618 ,p_org_hierarchy_ele_oc_tab => l_org_hierarchy_ele_oc_tab);
619 hr_utility.set_location(l_proc, 200);
620 hr_utility.set_location(l_proc, 210);
621
622 --
623 -- create legal entities orgs and related classifications
624 --
625 per_ri_config_datapump_entity.create_le_batch_lines
626 (p_configuration_code => p_configuration_code
627 ,p_batch_header_id => l_batch_header_id
628 ,p_technical_summary_mode => p_technical_summary_mode
629 ,p_org_le_tab => l_org_le_tab
630 ,p_org_le_class_tab => l_org_le_class_tab
631 ,p_org_hierarchy_ele_le_tab => l_org_hierarchy_ele_le_tab);
632
633 hr_utility.set_location(l_proc, 220);
634
635 p_organization_clob := per_ri_config_tech_summary.get_org_sql
636 (p_org_ent_tab => l_org_ent_tab
637 ,p_org_oc_tab => l_org_oc_tab
638 ,p_org_le_tab => l_org_le_tab);
639
640 p_org_class_clob := per_ri_config_tech_summary.get_org_class_sql
641 (p_org_ent_class_tab => l_org_ent_class_tab
642 ,p_org_oc_class_tab => l_org_oc_class_tab
643 ,p_org_le_class_tab => l_org_le_class_tab
644 );
645 p_org_class_for_pv_clob := per_ri_config_tech_summary.get_org_class_sql_for_pv
646 (p_org_ent_tab => l_org_ent_tab
647 ,p_org_oc_tab => l_org_oc_tab
648 ,p_org_le_tab => l_org_le_tab
649 ,p_org_ent_class_tab => l_org_ent_class_tab
650 ,p_org_oc_class_tab => l_org_oc_class_tab
651 ,p_org_le_class_tab => l_org_le_class_tab
652 );
653
654 p_org_hierarchy_ele_clob := per_ri_config_tech_summary.get_org_hierarchy_ele_sql (
655 p_org_hierarchy_ele_oc_tab => l_org_hierarchy_ele_oc_tab
656 ,p_org_hierarchy_ele_le_tab => l_org_hierarchy_ele_le_tab
657 );
658
659 p_org_hier_ele_for_pv_clob := per_ri_config_tech_summary.get_org_hier_ele_sql_for_pv (
660 p_org_hierarchy_ele_oc_tab => l_org_hierarchy_ele_oc_tab
661 ,p_org_hierarchy_ele_le_tab => l_org_hierarchy_ele_le_tab
662 );
663
664
665 p_str_seg_for_pv_clob := per_ri_config_tech_summary.get_keyflex_str_seg_sql_for_pv
666 (p_kf_job_tab => l_kf_job_tab
667 ,p_kf_job_rv_tab => l_kf_job_rv_tab
668 ,p_kf_job_no_rv_tab => l_kf_job_no_rv_tab
669 ,p_kf_pos_tab => l_kf_pos_tab
670 ,p_kf_pos_rv_tab => l_kf_pos_rv_tab
671 ,p_kf_pos_no_rv_tab => l_kf_pos_no_rv_tab
672 ,p_kf_grd_tab => l_kf_grd_tab
673 ,p_kf_grd_rv_tab => l_kf_grd_rv_tab
674 ,p_kf_grd_no_rv_tab => l_kf_grd_no_rv_tab
675 ,p_kf_cmp_tab => l_kf_cmp_tab
676 ,p_kf_grp_tab => l_kf_grp_tab
677 ,p_kf_cost_tab => l_kf_cost_tab
678 ,p_kf_job_seg_tab => l_kf_job_seg_tab
679 ,p_kf_job_rv_seg_tab => l_kf_job_rv_seg_tab
680 ,p_kf_job_no_rv_seg_tab => l_kf_job_no_rv_seg_tab
681 ,p_kf_pos_seg_tab => l_kf_pos_seg_tab
682 ,p_kf_pos_rv_seg_tab => l_kf_pos_rv_seg_tab
683 ,p_kf_pos_no_rv_seg_tab => l_kf_pos_no_rv_seg_tab
684 ,p_kf_grd_seg_tab => l_kf_grd_seg_tab
685 ,p_kf_grd_rv_seg_tab => l_kf_grd_rv_seg_tab
686 ,p_kf_grd_no_rv_seg_tab => l_kf_grd_no_rv_seg_tab
687 ,p_kf_grp_seg_tab => l_kf_grp_seg_tab
688 ,p_kf_cmp_seg_tab => l_kf_cmp_seg_tab
689 ,p_kf_cost_seg_tab => l_kf_cost_seg_tab
690 );
691
692 hr_utility.set_location(l_proc, 230);
693
694 per_ri_config_utilities.create_more_hrms_resps
695 (p_configuration_code => p_configuration_code
696 ,p_security_profile_tab => l_security_profile_tab
697 ,p_int_bg_resp_tab => l_int_bg_resp_tab
698 ,p_technical_summary_mode => p_technical_summary_mode
699 ,p_hrms_resp_main_tab => l_hrms_resp_main_tab
700 ,p_more_profile_resp_tab => l_more_profile_resp_tab
701 ,p_more_int_profile_resp_tab => l_more_int_profile_resp_tab);
702
703 if l_more_profile_resp_tab.count > 0 THEN
704 p_profile_resp_clob := per_ri_config_tech_summary.get_profile_resp_sql(l_more_profile_resp_tab);
705 end if;
706
707 p_resp_clob := per_ri_config_tech_summary.get_resp_sql
708 (p_resp_tab =>l_resp_tab
709 ,p_hrms_resp_tab =>l_hrms_resp_main_tab
710 ,p_hrms_misc_resp_tab =>l_hrms_resp_tab);
711
712 -- Security Group Removal Chnages
713 --per_ri_config_utilities.create_security_profile_assign
714 -- (p_security_profile_tab => l_security_profile_tab);
715
716 hr_utility.set_location(' Leaving:'|| l_proc, 250);
717
718 per_ri_config_utilities.submit_int_payroll_request
719 (errbuf => l_errbuf
720 ,retcode => l_retcode
721 ,p_country_tab => l_country_tab
722 ,p_technical_summary_mode => p_technical_summary_mode
723 ,p_int_hrms_setup_tab => l_int_hrms_setup_tab
724 );
725
726 p_int_hrms_clob := per_ri_config_tech_summary.get_int_hrms_setup_sql
727 (p_int_hrms_setup_tab => l_int_hrms_setup_tab);
728
729 hr_utility.set_location(l_proc, 240);
730
731 rollback;
732
733 hr_utility.trace('Ending Time = ' || to_char(sysdate,'DD-Mon-YYYY HH24:MI:SS'));
734 hr_utility.set_location(' Leaving:'|| l_proc, 300);
735
736 EXCEPTION
737 --when not_fresh_install then
738 --per_ri_config_utilities.write_log(p_message => l_log_message);
739 --raise;
740 when others then
741 l_error_message := 'Error in ' || l_proc;
742 hr_utility.trace(l_error_message || '-' || sqlerrm);
743 hr_utility.set_location(' Leaving:'|| l_proc, 500);
744 hr_utility.raise_error;
745
746 END create_technical_summary;
747
748
749 PROCEDURE load_enterprise_configuration (p_errbuff out nocopy varchar2
750 ,p_retcode out nocopy number
751 ,p_configuration_code in varchar2) IS
752
753 cursor csr_bg_ids(c_batch_id in number) IS
754 select unique_key_id from hr_pump_batch_line_user_keys
755 where batch_line_id in (select batch_line_id from hr_pump_batch_lines
756 where batch_id = c_batch_id and api_module_id = (select api_module_id
757 from hr_api_modules where module_name = 'CREATE_BUSINESS_GROUP'));
758
759 cursor csr_config_business_groups(cp_configuration_code in varchar2) IS
760 select distinct configuration_code,
761 per_ri_config_utilities.business_group_decision(configuration_code,country_code)
762 from per_ri_config_country_v
763 where configuration_code = cp_configuration_code;
764
765
766 l_proc varchar2(72) := g_package || 'load_enterprise_configuration';
767 l_log_message varchar2(360);
768 l_error_message varchar2(360);
769 l_batch_header_id number(15);
770 l_batch_name hr_pump_batch_headers.batch_name%type;
771 l_errbuf varchar2(240);
772 l_retcode number(15);
773 l_job_defined boolean default FALSE;
774 l_pos_defined boolean default FALSE;
775 l_grd_defined boolean default FALSE;
776
777 l_jp_rv_defined boolean default FALSE;
778 l_grd_rv_defined boolean default FALSE;
779 l_country_tab per_ri_config_datapump_entity.country_tab;
780 l_security_profile_tab per_ri_config_fnd_hr_entity.security_profile_tab;
781 l_int_bg_resp_tab per_ri_config_fnd_hr_entity.int_bg_resp_tab;
782
783 l_errbuf_msg varchar2(240);
784 l_retcode_msg number(15);
785 l_fresh_installed boolean;
786 l_data_pump_exception boolean;
787
788 l_enterprise_short_name per_ri_config_information.config_information1%type;
789 not_fresh_install EXCEPTION;
790 data_pump_load_failure EXCEPTION;
791
792 l_technical_summary_mode boolean default false;
793 l_user_tab per_ri_config_tech_summary.user_tab;
794 l_resp_tab per_ri_config_tech_summary.resp_tab;
795 l_hrms_resp_tab per_ri_config_tech_summary.hrms_resp_tab;
796 l_hrms_misc_resp_tab per_ri_config_tech_summary.hrms_misc_resp_tab;
797 l_profile_tab per_ri_config_tech_summary.profile_tab;
798 l_profile_apps_tab per_ri_config_tech_summary.profile_apps_tab;
799 l_profile_dpe_ent_tab per_ri_config_tech_summary.profile_dpe_ent_tab;
800
801 l_location_tab per_ri_config_tech_summary.location_tab;
802 l_bg_tab per_ri_config_tech_summary.bg_tab;
803 l_sg_tab per_ri_config_tech_summary.sg_tab;
804 l_post_install_tab per_ri_config_tech_summary.post_install_tab;
805 l_org_ent_tab per_ri_config_tech_summary.org_ent_tab;
806 l_org_hierarchy_tab per_ri_config_tech_summary.org_hierarchy_tab;
807 l_org_hierarchy_ele_oc_tab per_ri_config_tech_summary.org_hierarchy_ele_oc_tab;
808 l_org_hierarchy_ele_le_tab per_ri_config_tech_summary.org_hierarchy_ele_le_tab;
809 l_org_oc_tab per_ri_config_tech_summary.org_oc_tab;
810 l_org_le_tab per_ri_config_tech_summary.org_le_tab;
811 l_org_ent_class_tab per_ri_config_tech_summary.org_ent_class_tab;
812 l_org_oc_class_tab per_ri_config_tech_summary.org_oc_class_tab;
813 l_org_le_class_tab per_ri_config_tech_summary.org_le_class_tab;
814 l_kf_job_tab per_ri_config_tech_summary.kf_job_tab;
815 l_kf_pos_tab per_ri_config_tech_summary.kf_pos_tab;
816 l_kf_grd_tab per_ri_config_tech_summary.kf_grd_tab;
817 l_kf_job_seg_tab per_ri_config_tech_summary.kf_job_seg_tab;
818 l_kf_pos_seg_tab per_ri_config_tech_summary.kf_pos_seg_tab;
819 l_kf_grd_seg_tab per_ri_config_tech_summary.kf_grd_seg_tab;
820 l_kf_grp_tab per_ri_config_tech_summary.kf_grp_tab;
821 l_kf_cmp_tab per_ri_config_tech_summary.kf_cmp_tab;
822 l_kf_cost_tab per_ri_config_tech_summary.kf_cost_tab;
823 l_kf_grp_seg_tab per_ri_config_tech_summary.kf_grp_seg_tab;
824 l_kf_cmp_seg_tab per_ri_config_tech_summary.kf_cmp_seg_tab;
825 l_kf_cost_seg_tab per_ri_config_tech_summary.kf_cost_seg_tab;
826 l_kf_job_no_rv_tab per_ri_config_tech_summary.kf_job_no_rv_tab;
827 l_kf_job_no_rv_seg_tab per_ri_config_tech_summary.kf_job_no_rv_seg_tab;
828 l_kf_job_rv_tab per_ri_config_tech_summary.kf_job_rv_tab;
829 l_kf_job_rv_seg_tab per_ri_config_tech_summary.kf_job_rv_seg_tab;
830 l_kf_pos_no_rv_tab per_ri_config_tech_summary.kf_pos_no_rv_tab;
831 l_kf_pos_no_rv_seg_tab per_ri_config_tech_summary.kf_pos_no_rv_seg_tab;
832 l_kf_pos_rv_tab per_ri_config_tech_summary.kf_pos_rv_tab;
833 l_kf_pos_rv_seg_tab per_ri_config_tech_summary.kf_pos_rv_seg_tab;
834 l_kf_grd_no_rv_tab per_ri_config_tech_summary.kf_grd_no_rv_tab;
835 l_kf_grd_no_rv_seg_tab per_ri_config_tech_summary.kf_grd_no_rv_seg_tab;
836 l_kf_grd_rv_tab per_ri_config_tech_summary.kf_grd_rv_tab;
837 l_kf_grd_rv_seg_tab per_ri_config_tech_summary.kf_grd_rv_seg_tab;
838 l_int_hrms_setup_tab per_ri_config_tech_summary.int_hrms_setup_tab;
839
840 l_more_profile_resp_tab per_ri_config_tech_summary.profile_resp_tab;
841 l_more_int_profile_resp_tab per_ri_config_tech_summary.profile_resp_tab;
842 l_hrms_resp_main_tab per_ri_config_tech_summary.hrms_resp_tab;
843
844 l_multi_tenancy boolean default false;
845 l_system_model varchar2(10) := 'N';
846 l_sec_group_id varchar2(10);
847 l_bg_id number(10);
848 l_label varchar2(50);
849 l_old_sec_group_id number(10);
850 l_enterprise_id number(10);
851 l_err_buf varchar2(4000);
852 l_ret_code number(10);
853 l_configuration_code varchar2(50);
854 l_bg_country_name varchar2(50);
855 l_bg_count number(10);
856
857 PRAGMA Exception_Init(not_fresh_install, -20001);
858 PRAGMA Exception_Init(data_pump_load_failure, -20002);
859
860 BEGIN
861
862
863 hr_utility.set_location('Entering:'|| l_proc, 10);
864
865 hr_utility.trace('Starting Time = ' || to_char(sysdate,'DD-Mon-YYYY HH24:MI:SS'));
866
867 --Check Whether multiTenancy is enabled or not
868 l_multi_tenancy := hr_multi_tenancy_pkg.is_multi_tenant_system;
869 l_sec_group_id := fnd_global.security_group_id;
870 if l_multi_tenancy then
871 l_system_model := hr_multi_tenancy_pkg.get_system_model;
872 end if;
873
874
875 BEGIN
876 select enterprise_id into l_enterprise_id from per_ent_security_groups
877 where business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID');
878 EXCEPTION
879 WHEN NO_DATA_FOUND THEN
880 l_enterprise_id := null;
881 END;
882 hr_utility.set_location('Enterprise Id is ' || l_enterprise_id, 78);
883 hr_utility.set_location('BEFORE SEC GROUP ID IS : '||fnd_global.security_group_id, 78);
884 l_old_sec_group_id := fnd_global.security_group_id;
885 BEGIN
886 select 'C::'||enterprise_label into l_label from per_ent_security_groups
887 where security_group_id = fnd_global.security_group_id;
888 hr_utility.set_location('Sec Group Id is '||fnd_global.security_group_id, 100) ;
889 EXCEPTION
890 WHEN NO_DATA_FOUND THEN
891 l_label := null;
892 END;
893
894
895 -- Check if this database is freshly installed
896 l_fresh_installed := per_ri_config_utilities.check_fresh_installation;
897
898 if NOT l_fresh_installed
899 and fnd_profile.value('PER_RI_LOAD_OVERRIDE') = 'N' then
900
901 l_log_message := '<<----------------------ATTENTION------------------------------>>';
902 per_ri_config_utilities.write_log(p_message => l_log_message);
903
904 l_log_message := 'This database got some entities setup done after it is freshly installed';
905 per_ri_config_utilities.write_log(p_message => l_log_message);
906
907 l_log_message := 'Loader Program Can not Continue';
908 per_ri_config_utilities.write_log(p_message => l_log_message);
909
910 l_log_message := 'Exiting...' ;
911 per_ri_config_utilities.write_log(p_message => l_log_message);
912
913 l_log_message := '<<----------------------ATTENTION------------------------------>>';
914 per_ri_config_utilities.write_log(p_message => l_log_message);
915 raise not_fresh_install;
916 end if;
917
918 -- Check PER_RI_LOAD_OVERRIDE
919 if fnd_profile.value('PER_RI_LOAD_OVERRIDE') = 'Y'
920 and NOT l_fresh_installed then
921 l_log_message := 'This database got some entities setup done after it is freshly installed';
922 l_log_message := l_log_message || '..Some of the entities loading might fail';
923 per_ri_config_utilities.write_log(p_message => l_log_message);
924 end if;
925
926 l_enterprise_short_name := per_ri_config_utilities.get_enterprise_short_name(p_configuration_code);
927
928 l_job_defined := per_ri_config_utilities.jpg_defined
929 (p_configuration_code => p_configuration_code
930 ,p_seg_type => 'JOB');
931
932 l_pos_defined := per_ri_config_utilities.jpg_defined
933 (p_configuration_code => p_configuration_code
934 ,p_seg_type => 'POSITION');
935
936 l_grd_defined := per_ri_config_utilities.jpg_defined
937 (p_configuration_code => p_configuration_code
938 ,p_seg_type => 'GRADE');
939
940 l_jp_rv_defined := per_ri_config_utilities.regional_variance_defined
941 (p_configuration_code => p_configuration_code
942 ,p_rv_type => 'JP');
943
944 l_grd_rv_defined := per_ri_config_utilities.regional_variance_defined
945 (p_configuration_code => p_configuration_code
946 ,p_rv_type => 'GRD');
947
948 hr_utility.set_location(l_proc, 20);
949
950 -- populate enterprise short name global variable
951 l_enterprise_short_name := per_ri_config_utilities.get_enterprise_short_name(p_configuration_code);
952 --
953
954 --
955 -- create HRMS_USER super user
956 --
957
958
959 per_ri_config_fnd_def_entity.create_user(p_technical_summary_mode => l_technical_summary_mode
960 ,p_user_tab => l_user_tab);
961
962 --
963 -- attach default responsibility
964 --
965 per_ri_config_fnd_def_entity.attach_default_responsibility
966 (p_configuration_code => p_configuration_code
967 ,p_technical_summary_mode => l_technical_summary_mode
968 ,p_resp_tab => l_resp_tab
969 );
970 hr_utility.set_location(l_proc, 40);
971
972 --
973 -- set site level profile options
974 --
975 per_ri_config_fnd_def_entity.create_site_profile_options
976 (p_configuration_code => p_configuration_code
977 ,p_technical_summary_mode => l_technical_summary_mode
978 ,p_profile_tab => l_profile_tab);
979
980 --
981 -- set aplication level profile options
982 --
983 per_ri_config_fnd_hr_entity.create_application_level_resp
984 (p_configuration_code => p_configuration_code
985 ,p_technical_summary_mode => l_technical_summary_mode
986 ,p_profile_apps_tab => l_profile_apps_tab);
987 hr_utility.set_location(l_proc, 50);
988 --
989 -- add hrms responsibilities to created user.
990 --
991 per_ri_config_fnd_hr_entity.create_hrms_responsibility
992 (p_configuration_code => p_configuration_code
993 ,p_security_profile_tab => l_security_profile_tab
994 ,p_technical_summary_mode => l_technical_summary_mode
995 ,p_hrms_resp_tab => l_hrms_resp_tab);
996
997 hr_utility.set_location(l_proc, 60);
998
999 --
1000 -- add hrms misc responsibilities to created user.
1001 --
1002 per_ri_config_utilities.assign_misc_responsibility
1003 (p_configuration_code => p_configuration_code
1004 ,p_technical_summary_mode => l_technical_summary_mode
1005 ,p_hrms_misc_resp_tab => l_hrms_misc_resp_tab);
1006
1007
1008 hr_utility.set_location(l_proc, 65);
1009
1010 --
1011 -- Take Decision on the creation of flexfields
1012 --
1013
1014 l_bg_count := 0;
1015 open csr_config_business_groups(p_configuration_code);
1016 loop
1017 fetch csr_config_business_groups into
1018 l_configuration_code,
1019 l_bg_country_name;
1020
1021 exit when csr_config_business_groups%NOTFOUND;
1022 l_bg_count := l_bg_count + 1;
1023 end loop;
1024 close csr_config_business_groups;
1025 hr_utility.set_location('BG Count is : '|| l_bg_count, 45);
1026
1027 IF l_multi_tenancy AND l_system_model = 'B' AND l_sec_group_id <> '0'
1028 AND l_enterprise_id IS NOT NULL AND l_bg_count=1 then
1029 hr_utility.set_location('No flex structures required', 11);
1030 ELSE
1031
1032 per_ri_config_fnd_hr_entity.create_global_grp_cmp_cost_kf
1033 (p_configuration_code => p_configuration_code
1034 ,p_technical_summary_mode => l_technical_summary_mode
1035 ,p_kf_grp_tab => l_kf_grp_tab
1036 ,p_kf_cmp_tab => l_kf_cmp_tab
1037 ,p_kf_cost_tab => l_kf_cost_tab
1038 ,p_kf_grp_seg_tab => l_kf_grp_seg_tab
1039 ,p_kf_cmp_seg_tab => l_kf_cmp_seg_tab
1040 ,p_kf_cost_seg_tab => l_kf_cost_seg_tab
1041 );
1042
1043 hr_utility.set_location(l_proc, 70);
1044
1045
1046 --
1047 -- make decisions about job, positions and grades
1048 --
1049
1050 --jobs/positions not defined
1051 if NOT l_job_defined and NOT l_pos_defined then
1052 hr_utility.trace('Jobs/Positions NOT defined');
1053 --
1054 -- create global jobs and positions
1055 --
1056 per_ri_config_fnd_hr_entity.create_global_job_pos_kf
1057 (p_configuration_code => p_configuration_code
1058 ,p_technical_summary_mode => l_technical_summary_mode
1059 ,p_kf_job_tab => l_kf_job_tab
1060 ,p_kf_pos_tab => l_kf_pos_tab
1061 ,p_kf_job_seg_tab => l_kf_job_seg_tab
1062 ,p_kf_pos_seg_tab => l_kf_pos_seg_tab
1063 );
1064 hr_utility.set_location(l_proc, 80);
1065 end if;
1066
1067 --when positions not defined - this case comes when
1068 --user has selected only job in RV section.
1069
1070 if NOT l_pos_defined then
1071 hr_utility.trace('Positions NOT defined');
1072 --
1073 -- create global positions
1074 --
1075 per_ri_config_fnd_hr_entity.create_global_pos_kf
1076 (p_configuration_code => p_configuration_code
1077 ,p_technical_summary_mode => l_technical_summary_mode
1078 ,p_kf_pos_tab => l_kf_pos_tab
1079 ,p_kf_pos_seg_tab => l_kf_pos_seg_tab
1080 );
1081 hr_utility.set_location(l_proc, 85);
1082 end if;
1083 --grades are not defined
1084 if NOT l_grd_defined then
1085 hr_utility.trace('Grades NOT defined');
1086 --
1087 -- create global grades
1088 --
1089 per_ri_config_fnd_hr_entity.create_global_grd_kf
1090 (p_configuration_code => p_configuration_code
1091 ,p_technical_summary_mode => l_technical_summary_mode
1092 ,p_kf_grd_tab => l_kf_grd_tab
1093 ,p_kf_grd_seg_tab => l_kf_grd_seg_tab
1094 );
1095 hr_utility.set_location(l_proc, 90);
1096 end if;
1097
1098 -- jobs defined (including RVs)
1099 if l_job_defined or l_jp_rv_defined then
1100 hr_utility.trace('Jobs defined');
1101 --
1102 -- create jobs with no rvs
1103 --
1104 per_ri_config_fnd_hr_entity.create_jobs_no_rv_keyflex
1105 (p_configuration_code => p_configuration_code
1106 ,p_technical_summary_mode => l_technical_summary_mode
1107 ,p_kf_job_no_rv_tab => l_kf_job_no_rv_tab
1108 ,p_kf_job_no_rv_seg_tab => l_kf_job_no_rv_seg_tab
1109 );
1110 hr_utility.set_location(l_proc, 100);
1111 -- create jobs with rvs
1112 --
1113 per_ri_config_fnd_hr_entity.create_jobs_rv_keyflex
1114 (p_configuration_code => p_configuration_code
1115 ,p_technical_summary_mode => l_technical_summary_mode
1116 ,p_kf_job_rv_tab => l_kf_job_rv_tab
1117 ,p_kf_job_rv_seg_tab => l_kf_job_rv_seg_tab
1118 );
1119 hr_utility.set_location(l_proc, 110);
1120 end if;
1121
1122 -- Positions defined (including RVs)
1123 if l_pos_defined or (l_pos_defined and l_jp_rv_defined) then -- fix for 4522666
1124 hr_utility.trace('Positions IS defined');
1125 --
1126 -- create positions with no rvs
1127 --
1128 per_ri_config_fnd_hr_entity.create_positions_no_rv_keyflex
1129 (p_configuration_code => p_configuration_code
1130 ,p_technical_summary_mode => l_technical_summary_mode
1131 ,p_kf_pos_no_rv_tab => l_kf_pos_no_rv_tab
1132 ,p_kf_pos_no_rv_seg_tab => l_kf_pos_no_rv_seg_tab
1133 );
1134 hr_utility.set_location(l_proc, 120);
1135 --
1136 -- create positions with rvs
1137 --
1138 per_ri_config_fnd_hr_entity.create_positions_rv_keyflex
1139 (p_configuration_code => p_configuration_code
1140 ,p_technical_summary_mode => l_technical_summary_mode
1141 ,p_kf_pos_rv_tab => l_kf_pos_rv_tab
1142 ,p_kf_pos_rv_seg_tab => l_kf_pos_rv_seg_tab
1143 );
1144 hr_utility.set_location(l_proc, 130);
1145
1146 end if;
1147
1148 -- Grades defined (including RVs)
1149 if l_grd_defined or l_grd_rv_defined then
1150 hr_utility.trace('Grades IS defined');
1151 --
1152 -- create grades with no rvs
1153 --
1154 per_ri_config_fnd_hr_entity.create_grades_no_rv_keyflex
1155 (p_configuration_code => p_configuration_code
1156 ,p_technical_summary_mode => l_technical_summary_mode
1157 ,p_kf_grd_no_rv_tab => l_kf_grd_no_rv_tab
1158 ,p_kf_grd_no_rv_seg_tab => l_kf_grd_no_rv_seg_tab
1159 );
1160 hr_utility.set_location(l_proc, 140);
1161 --
1162 -- create grades with rvs
1163 --
1164 per_ri_config_fnd_hr_entity.create_grades_rv_keyflex
1165 (p_configuration_code => p_configuration_code
1166 ,p_technical_summary_mode => l_technical_summary_mode
1167 ,p_kf_grd_rv_tab => l_kf_grd_rv_tab
1168 ,p_kf_grd_rv_seg_tab => l_kf_grd_rv_seg_tab
1169 );
1170 hr_utility.set_location(l_proc, 150);
1171 end if;
1172 END IF;
1173
1174
1175 --
1176 -- create batch header
1177 l_batch_name := substr(p_configuration_code || ' ' || to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'),1,80);
1178
1179 hr_utility.set_location(l_proc, 160);
1180 l_batch_header_id := hr_pump_utils.create_batch_header
1181 (p_batch_name => l_batch_name);
1182 hr_utility.set_location(l_batch_name, 1990);
1183 hr_utility.set_location(l_batch_header_id, 2000);
1184 hr_utility.set_location(l_proc, 170);
1185 --
1186 -- create locations
1187 --
1188 per_ri_config_datapump_entity.create_locations_batch_lines
1189 (p_configuration_code => p_configuration_code
1190 ,p_batch_header_id => l_batch_header_id
1191 ,p_technical_summary_mode => l_technical_summary_mode
1192 ,p_location_tab => l_location_tab
1193 );
1194 hr_utility.set_location(l_proc, 180);
1195
1196 IF l_multi_tenancy AND l_system_model = 'B' AND l_sec_group_id <> '0'
1197 AND l_enterprise_id IS NOT NULL
1198 THEN
1199 hr_utility.set_location('DP is getting run before hand ', 76);
1200 BEGIN
1201 --
1202 -- run data pump to load hrms entities into schema
1203 --
1204 hr_data_pump.main (errbuf => l_errbuf
1205 ,retcode => l_retcode
1206 ,p_batch_id => l_batch_header_id
1207 ,p_validate => 'N'
1208 ,p_pap_group_id => null);
1209
1210 l_data_pump_exception := per_ri_config_utilities.check_data_pump_exception(p_patch_header_id => l_batch_header_id);
1211 if l_data_pump_exception then
1212 per_ri_config_utilities.write_data_pump_exception_log(p_patch_header_id => l_batch_header_id);
1213 raise data_pump_load_failure;
1214 end if;
1215
1216 EXCEPTION
1217 when data_pump_load_failure then
1218 hr_utility.set_location(l_proc, 221);
1219 hr_utility.raise_error;
1220 commit;
1221 when others then
1222 hr_utility.set_location(l_proc, 222);
1223 p_errbuff := 'Error cooured in loading datapump records';
1224 p_retcode := 2;
1225
1226 l_error_message := 'Error in ' || l_proc;
1227 --hr_utility.trace(l_error_message || '-' || sqlerrm);
1228 hr_utility.set_location(' Leaving:'|| l_proc, 500);
1229 hr_utility.raise_error;
1230 commit;
1231 END;
1232
1233 l_batch_name := substr(p_configuration_code || 'second' || ' ' || to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'),1,80);
1234
1235 hr_utility.set_location(l_proc, 160);
1236 l_batch_header_id := hr_pump_utils.create_batch_header
1237 (p_batch_name => l_batch_name);
1238
1239 END IF;
1240
1241
1242 --
1243 -- create business groups
1244 --
1245
1246 per_ri_config_datapump_entity.create_bg_batch_lines
1247 (p_batch_header_id => l_batch_header_id
1248 ,p_configuration_code => p_configuration_code
1249 ,p_country_tab_out => l_country_tab
1250 ,p_technical_summary_mode => l_technical_summary_mode
1251 ,p_bg_tab => l_bg_tab
1252 ,p_sg_tab => l_sg_tab
1253 ,p_post_install_tab => l_post_install_tab
1254 ,p_int_bg_resp_tab => l_int_bg_resp_tab);
1255
1256 hr_utility.set_location(l_proc, 190);
1257
1258 IF l_multi_tenancy AND l_system_model = 'B' AND l_sec_group_id <> '0'
1259 AND l_enterprise_id IS NOT NULL
1260 THEN
1261 hr_utility.set_location('DP is getting run before hand ', 76);
1262 BEGIN
1263 hr_multi_tenancy_pkg.set_context('ENT');
1264 --
1265 -- run data pump to load hrms entities into schema
1266 --
1267 hr_data_pump.main (errbuf => l_errbuf
1268 ,retcode => l_retcode
1269 ,p_batch_id => l_batch_header_id
1270 ,p_validate => 'N'
1271 ,p_pap_group_id => null);
1272
1273 l_data_pump_exception := per_ri_config_utilities.check_data_pump_exception(p_patch_header_id => l_batch_header_id);
1274 if l_data_pump_exception then
1275 per_ri_config_utilities.write_data_pump_exception_log(p_patch_header_id => l_batch_header_id);
1276 raise data_pump_load_failure;
1277 end if;
1278
1279 EXCEPTION
1280 when data_pump_load_failure then
1281 hr_utility.set_location(l_proc, 221);
1282 hr_utility.raise_error;
1283 commit;
1284 when others then
1285 hr_utility.set_location(l_proc, 222);
1286 p_errbuff := 'Error cooured in loading datapump records';
1287 p_retcode := 2;
1288
1289 l_error_message := 'Error in ' || l_proc;
1290 --hr_utility.trace(l_error_message || '-' || sqlerrm);
1291 hr_utility.set_location(' Leaving:'|| l_proc, 500);
1292 hr_utility.raise_error;
1293 commit;
1294 END;
1295
1296
1297 hr_utility.set_location('Sec Group Id is '||fnd_global.security_group_id, 100);
1298
1299 open csr_bg_ids(l_batch_header_id);
1300 loop
1301 fetch csr_bg_ids into
1302 l_bg_id;
1303 exit when csr_bg_ids%NOTFOUND;
1304
1305
1306 EXECUTE IMMEDIATE 'begin hr_multi_tenant_install.initialize_orgs(errbuf => :1
1307 ,retcode => :2
1308 ,p_enterprise_id => :3
1309 ,p_organization_id => :4); end;'
1310 USING out l_err_buf, out l_ret_code, l_enterprise_id, l_bg_id;
1311
1312
1313 hr_utility.set_location('The label method has been called ', 90);
1314
1315 -- BEGIN
1316 -- hr_utility.set_location('Before the first execute', 10);
1317 -- EXECUTE IMMEDIATE 'UPDATE hr_all_organization_units
1318 -- SET hr_enterprise = char_to_label(''HR_ENTERPRISE_POLICY'', :1)
1319 -- WHERE organization_id = :2'
1320 -- USING l_label, l_bg_id;
1321 -- hr_utility.set_location('After the first execute', 10);
1322 --
1323 -- EXECUTE Immediate 'UPDATE hr_organization_information
1324 -- SET hr_enterprise = char_to_label(''HR_ENTERPRISE_POLICY'',:1 )
1325 -- WHERE organization_id = :2'
1326 -- USING l_label
1327 -- ,l_bg_id;
1328 -- hr_utility.set_location('After the second execute', 10);
1329 -- EXECUTE IMMEDIATE 'UPDATE per_number_generation_controls
1330 -- SET hr_enterprise = char_to_label(''HR_ENTERPRISE_POLICY'', :1)
1331 -- WHERE business_group_id = :2'
1332 -- USING l_label
1333 -- ,l_bg_id;
1334 -- hr_utility.set_location('After the third execute', 10);
1335 -- EXECUTE IMMEDIATE 'UPDATE hr_all_organization_units_tl
1336 -- SET hr_enterprise = char_to_label(''HR_ENTERPRISE_POLICY'', :1)
1337 -- WHERE organization_id = :2'
1338 -- USING l_label, l_bg_id;
1339 -- hr_utility.set_location('After the fourth execute', 10);
1340 -- END;
1341
1342 end loop;
1343 close csr_bg_ids;
1344
1345 /*fnd_global.set_security_group_id_context(security_group_id
1346 => l_old_sec_group_id);*/
1347 hr_multi_tenancy_pkg.set_context(l_label);
1348 hr_utility.set_location('After change the Sec group id is : ' ||
1349 fnd_global.security_group_id, 90);
1350
1351 l_batch_name := substr(p_configuration_code || 'third' || ' ' || to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'),1,80);
1352
1353 hr_utility.set_location(l_proc, 160);
1354 l_batch_header_id := hr_pump_utils.create_batch_header
1355 (p_batch_name => l_batch_name);
1356 hr_utility.set_location(l_batch_name, 1990);
1357 hr_utility.set_location(l_batch_header_id, 2000);
1358 hr_utility.set_location(l_proc, 170);
1359
1360 END IF;
1361 --
1362 -- create enterprise orgs and related classifications
1363 --
1364 per_ri_config_datapump_entity.create_enterprise_batch_lines
1365 (p_configuration_code => p_configuration_code
1366 ,p_batch_header_id => l_batch_header_id
1367 ,p_technical_summary_mode => l_technical_summary_mode
1368 ,p_org_ent_tab => l_org_ent_tab
1369 ,p_org_ent_class_tab => l_org_ent_class_tab
1370 ,p_org_hierarchy_tab => l_org_hierarchy_tab
1371 ,p_profile_dpe_ent_tab => l_profile_dpe_ent_tab);
1372 hr_utility.set_location(l_proc, 200);
1373
1374
1375
1376 --
1377 -- create operating companies orgs and related classifications
1378 --
1379 per_ri_config_datapump_entity.create_oper_comp_batch_lines
1380 (p_configuration_code => p_configuration_code
1381 ,p_batch_header_id => l_batch_header_id
1382 ,p_technical_summary_mode => l_technical_summary_mode
1383 ,p_org_oc_tab => l_org_oc_tab
1384 ,p_org_oc_class_tab => l_org_oc_class_tab
1385 ,p_org_hierarchy_ele_oc_tab => l_org_hierarchy_ele_oc_tab);
1386 hr_utility.set_location(l_proc, 210);
1387
1388 --
1389 -- create legal entities orgs and related classifications
1390 --
1391 per_ri_config_datapump_entity.create_le_batch_lines
1392 (p_configuration_code => p_configuration_code
1393 ,p_batch_header_id => l_batch_header_id
1394 ,p_technical_summary_mode => l_technical_summary_mode
1395 ,p_org_le_tab => l_org_le_tab
1396 ,p_org_le_class_tab => l_org_le_class_tab
1397 ,p_org_hierarchy_ele_le_tab => l_org_hierarchy_ele_le_tab);
1398 hr_utility.set_location(l_proc, 220);
1399
1400
1401
1402
1403
1404 BEGIN
1405 --
1406 -- run data pump to load hrms entities into schema
1407 --
1408 hr_data_pump.main (errbuf => l_errbuf
1409 ,retcode => l_retcode
1410 ,p_batch_id => l_batch_header_id
1411 ,p_validate => 'N'
1412 ,p_pap_group_id => null);
1413
1414 l_data_pump_exception := per_ri_config_utilities.check_data_pump_exception(p_patch_header_id => l_batch_header_id);
1415 if l_data_pump_exception then
1416 per_ri_config_utilities.write_data_pump_exception_log(p_patch_header_id => l_batch_header_id);
1417 raise data_pump_load_failure;
1418 end if;
1419
1420 EXCEPTION
1421 when data_pump_load_failure then
1422 hr_utility.set_location(l_proc, 221);
1423 hr_utility.raise_error;
1424 commit;
1425 when others then
1426 hr_utility.set_location(l_proc, 222);
1427 p_errbuff := 'Error cooured in loading datapump records';
1428 p_retcode := 2;
1429
1430 l_error_message := 'Error in ' || l_proc;
1431 --hr_utility.trace(l_error_message || '-' || sqlerrm);
1432 hr_utility.set_location(' Leaving:'|| l_proc, 500);
1433 hr_utility.raise_error;
1434 commit;
1435 END;
1436
1437 hr_utility.set_location(l_proc, 230);
1438
1439
1440 per_ri_config_utilities.create_more_hrms_resps
1441 (p_configuration_code => p_configuration_code
1442 ,p_security_profile_tab => l_security_profile_tab
1443 ,p_int_bg_resp_tab => l_int_bg_resp_tab
1444 --,p_technical_summary_mode => l_technical_summary_mode
1445 ,p_hrms_resp_main_tab => l_hrms_resp_main_tab
1446 ,p_more_profile_resp_tab => l_more_profile_resp_tab
1447 ,p_more_int_profile_resp_tab => l_more_int_profile_resp_tab);
1448
1449 hr_utility.set_location(' Leaving:'|| l_proc, 250);
1450
1451 -- Security Group Removal Changes
1452 --
1453 --per_ri_config_utilities.create_security_profile_assign
1454 --(p_security_profile_tab => l_security_profile_tab);
1455
1456 hr_utility.set_location(' Leaving:'|| l_proc, 250);
1457
1458 per_ri_config_utilities.submit_int_payroll_request
1459 (errbuf => l_errbuf
1460 ,retcode => l_retcode
1461 ,p_country_tab => l_country_tab
1462 ,p_technical_summary_mode => l_technical_summary_mode
1463 ,p_int_hrms_setup_tab => l_int_hrms_setup_tab
1464 );
1465 hr_utility.set_location(l_proc, 240);
1466
1467 per_ri_config_utilities.update_configuration_status(p_configuration_code);
1468
1469 --
1470 -- Enable Multiple Security Group process
1471 --
1472 -- commented code in this process call.
1473 per_ri_config_utilities.submit_enable_mult_sg_process
1474 (errbuf => l_errbuf_msg
1475 ,retcode => l_retcode_msg);
1476
1477
1478 commit;
1479
1480 hr_utility.trace('Ending Time = ' || to_char(sysdate,'DD-Mon-YYYY HH24:MI:SS'));
1481 hr_utility.set_location(' Leaving:'|| l_proc, 300);
1482
1483
1484
1485 EXCEPTION
1486 --when not_fresh_install then
1487 --per_ri_config_utilities.write_log(p_message => l_log_message);
1488 --raise;
1489 when others then
1490 l_error_message := 'Error in ' || l_proc;
1491 hr_utility.trace(l_error_message || '-' || sqlerrm);
1492 hr_utility.set_location(' Leaving:'|| l_proc, 500);
1493 hr_utility.raise_error;
1494
1495 END load_enterprise_configuration;
1496
1497 END per_ri_config_main;