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