[Home] [Help]
PACKAGE BODY: APPS.PER_RI_CONFIG_DATAPUMP_ENTITY
Source
1 PACKAGE BODY per_ri_config_datapump_entity AS
2 /* $Header: perridpe.pkb 120.8.12010000.4 2008/12/16 10:29:43 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_datapump_entity.';
7
8
9 /* --------------------------------------------------------------------------
10 -- Name : create_enterprise_batch_lines
11 -- Purpose : This procedure creates enterprise batch lines records in the
12 -- data pump tables which would be loaded by the enterprise
13 -- structures configuration loader program for a specified
14 -- configuration.
15 -- Arguments : p_configuration_code
16 -- p_batch_header_id
17 -------------------------------------------------------------------------- */
18
19 PROCEDURE create_enterprise_batch_lines(p_configuration_code in varchar2
20 ,p_batch_header_id in number
21 ,p_multiple_config_upload in boolean default FALSE
22 ,p_technical_summary_mode in boolean default FALSE
23 ,p_org_ent_tab in out nocopy
24 per_ri_config_tech_summary.org_ent_tab
25 ,p_org_ent_class_tab in out nocopy
26 per_ri_config_tech_summary.org_ent_class_tab
27 ,p_org_hierarchy_tab in out nocopy
28 per_ri_config_tech_summary.org_hierarchy_tab
29 ,p_profile_dpe_ent_tab in out nocopy
30 per_ri_config_tech_summary.profile_dpe_ent_tab) IS
31
32 cursor csr_config_enterprise (cp_configuration_code in varchar2) IS
33 select configuration_code,
34 config_information_id,
35 config_sequence,
36 config_information_category,
37 per_ri_config_utilities.return_config_entity_name(enterprise_name),
38 enterprise_name, -- for other processing
39 enterprise_short_name,
40 enterprise_headquarter_country,
41 enterprise_primary_industry,
42 enterprise_location_id
43 from per_ri_config_enterprise_v
44 where configuration_code = cp_configuration_code;
45
46 l_configuration_code per_ri_config_information.configuration_code%type;
47 l_config_information_id per_ri_config_information.config_information_id%type;
48 l_config_sequence per_ri_config_information.config_sequence%type;
49 l_config_information_category per_ri_config_information.config_information_category%type;
50 l_enterprise_name per_ri_config_information.config_information1%type;
51 l_enterprise_name_original per_ri_config_information.config_information1%type;
52 l_enterprise_short_name per_ri_config_information.config_information1%type;
53 l_enterprise_hq_country per_ri_config_information.config_information1%type;
54 l_hr_enterprise_primary_indust per_ri_config_information.config_information1%type;
55 l_enterprise_location_id per_ri_config_information.config_information1%type;
56
57 l_business_group_country_code per_business_groups.name%type;
58 l_business_group_name per_business_groups.name%type;
59 l_ent_location_code per_ri_config_locations.description%type; -- fix for 4457389
60
61
62 l_hr_legal_mand_org_info_types boolean;
63 l_ent_count number(10) := 0;
64 l_profile_dpe_ent_count number(10) := 0;
65 l_ent_class_count number(10) := 0;
66 l_org_hierarchy_count number(10) := 0;
67 l_proc varchar2(72) := g_package ||'create_enterprise_batch_lines';
68 l_log_message varchar2(360);
69 l_error_message varchar2(360);
70 l_multi_tenancy boolean := false;
71 l_system_model varchar2(10) := 'N';
72 l_sec_group_id varchar2(10);
73 l_country_selected varchar2(50);
74 l_selected_country_code varchar2(10);
75 l_selected_country_name varchar2(50);
76 l_enterprise_id number(9);
77
78 BEGIN
79
80 hr_utility.set_location('Entering:'|| l_proc, 10);
81
82 l_multi_tenancy := hr_multi_tenancy_pkg.is_multi_tenant_system;
83 l_sec_group_id := fnd_global.security_group_id;
84 if l_multi_tenancy then
85 l_system_model := hr_multi_tenancy_pkg.get_system_model;
86 end if;
87 BEGIN
88 select enterprise_id into l_enterprise_id from per_ent_security_groups
89 where business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID');
90 EXCEPTION
91 WHEN NO_DATA_FOUND THEN
92 l_enterprise_id := null;
93 END;
94
95 select legislation_code into l_selected_country_code from
96 per_business_groups where business_group_id =
97 fnd_profile.value('PER_BUSINESS_GROUP_ID');
98
99 select name into l_selected_country_name from per_business_groups
100 where business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID');
101 open csr_config_enterprise(p_configuration_code);
102
103 fetch csr_config_enterprise into
104 l_configuration_code,
105 l_config_information_id,
106 l_config_sequence,
107 l_config_information_category,
108 l_enterprise_name,
109 l_enterprise_name_original,
110 l_enterprise_short_name,
111 l_enterprise_hq_country,
112 l_hr_enterprise_primary_indust,
113 l_enterprise_location_id;
114
115 if csr_config_enterprise%FOUND then
116 hr_utility.set_location(l_proc, 20);
117
118 -- decide on business group for enterprise
119 l_business_group_country_code := per_ri_config_utilities.get_enterprise_bg_name
120 (p_configuration_code => p_configuration_code
121 ,p_enterprise_name => l_enterprise_name_original);
122
123 hr_utility.trace('l_business_group_country_code = ' || l_business_group_country_code);
124 hr_utility.set_location(l_business_group_country_code, 93000);
125 hr_utility.set_location(l_selected_country_code, 94000);
126 IF l_business_group_country_code = l_selected_country_code AND
127 l_multi_tenancy AND l_system_model = 'B' AND l_sec_group_id <> '0'
128 AND l_enterprise_id IS NOT NULL
129 THEN
130 hr_utility.set_location('BG Matches', 91000);
131 l_business_group_name := l_selected_country_name;
132 ELSE
133 l_business_group_name := per_ri_config_utilities.get_enterprise_short_name
134 (p_configuration_code => p_configuration_code)
135 || ' ' || l_business_group_country_code || per_ri_config_main.g_bg_name_suffix_string;
136 END IF;
137 hr_utility.trace('l_business_group_name = ' || l_business_group_name);
138 hr_utility.set_location(l_business_group_name, 92000);
139 l_ent_location_code := per_ri_config_utilities.get_config_location_code
140 (p_configuration_code => p_configuration_code
141 ,p_location_id => l_enterprise_location_id);
142 -- get entity name for multiple configuration
143 --
144 hr_utility.trace('l_ent_location_code = ' || l_ent_location_code);
145 hr_utility.set_location(l_proc, 30);
146 if NOT (p_technical_summary_mode) then
147 -- if l_multi_tenancy AND l_system_model = 'B' AND l_sec_group_id <> '0' then
148 -- l_business_group_name := per_ri_config_utilities.get_business_group_name;
149 -- end if;
150 hrdpp_create_organization.insert_batch_lines
151 (p_batch_id => p_batch_header_id
152 ,p_link_value => per_ri_config_main.g_global_dp_link_value
153 ,p_user_sequence => 30
154 ,p_effective_date => g_config_effective_date
155 ,p_date_from => g_config_effective_date
156 ,p_data_pump_business_grp_name => l_business_group_name
157 ,p_name => l_enterprise_name
158 ,p_location_code => l_ent_location_code
159 ,p_internal_external_flag => 'INT'
160 ,p_org_user_key => l_enterprise_name || SYSDATE);
161
162 hr_utility.set_location(l_proc, 40);
163
164 hrdpp_update_organization.insert_batch_lines
165 (p_batch_id => p_batch_header_id
166 ,p_link_value => per_ri_config_main.g_global_dp_link_value
167 ,p_user_sequence => 40
168 ,p_effective_date => g_config_effective_date
169 ,p_date_from => g_config_effective_date
170 ,p_data_pump_business_grp_name => l_business_group_name
171 ,p_language_code => hr_api.userenv_lang
172 ,p_organization_name => l_enterprise_name
173 ,p_location_code => l_ent_location_code);
174 else
175 p_org_ent_tab(l_ent_count).effective_date := g_config_effective_date;
176 p_org_ent_tab(l_ent_count).date_from := g_config_effective_date;
177 p_org_ent_tab(l_ent_count).business_grp_name := l_business_group_name;
178 p_org_ent_tab(l_ent_count).name := l_enterprise_name;
179 p_org_ent_tab(l_ent_count).location_code := l_ent_location_code;
180 p_org_ent_tab(l_ent_count).internal_external_flag := 'INT';
181
182 l_ent_count := l_ent_count + 1;
183
184 end if;
185
186 l_log_message := 'Created ENTERPRISE ' || l_enterprise_name;
187 per_ri_config_utilities.write_log(p_message => l_log_message);
188 hr_utility.set_location(l_proc, 50);
189
190 --
191 -- Create HR_LEGAL classification for corporate LE
192 -- For all Localizations where no mandatory org info is defined.
193 -- and this classification is visible.
194 --
195 if per_ri_config_utilities.check_org_class_lookup_tag
196 (p_legislation_code => l_business_group_country_code
197 ,p_lookup_code => 'HR_LEGAL')
198 and NOT per_ri_config_utilities.mandatory_org_info_types
199 (p_legislation_code => l_business_group_country_code
200 ,p_org_classification => 'HR_LEGAL') then
201 hr_utility.trace('l_mandatory_org_info_types = ' || 'TRUE');
202 if NOT (p_technical_summary_mode) then
203 hrdpp_create_org_classificatio.insert_batch_lines
204 (p_batch_id => p_batch_header_id
205 ,p_link_value => per_ri_config_main.g_global_dp_link_value
206 ,p_user_sequence => 50
207 ,p_effective_date => g_config_effective_date
208 ,p_data_pump_business_grp_name => l_business_group_name
209 ,p_org_classif_code => 'HR_LEGAL'
210 ,p_organization_name => l_enterprise_name
211 ,p_language_code => hr_api.userenv_lang);
212 else
213 p_org_ent_class_tab(l_ent_class_count).effective_date := g_config_effective_date;
214 p_org_ent_class_tab(l_ent_class_count).date_from := g_config_effective_date;
215 p_org_ent_class_tab(l_ent_class_count).business_grp_name := l_business_group_name;
216 p_org_ent_class_tab(l_ent_class_count).org_classif_code := 'HR_LEGAL';
217 p_org_ent_class_tab(l_ent_class_count).organization_name := l_enterprise_name;
218
219 l_ent_class_count := l_ent_class_count + 1;
220
221 end if;
222 hr_utility.set_location(l_proc, 60);
223 l_log_message := 'Created CLASSIFICATION HR_LEGAL ' || l_enterprise_name;
224 per_ri_config_utilities.write_log(p_message => l_log_message);
225 end if;
226
227 hr_utility.set_location(l_proc, 70);
228
229 --also create organization structure hierarchy batch line
230 if NOT (p_technical_summary_mode) then
231 hrdpp_create_organization_stru.insert_batch_lines
232 (p_batch_id => p_batch_header_id
233 --,p_data_pump_business_grp_name => l_business_group_name
234 ,p_data_pump_business_grp_name => null
235 ,p_link_value => per_ri_config_main.g_global_dp_link_value
236 ,p_user_sequence => 60
237 ,p_effective_date => g_config_effective_date
238 ,p_name => substr(l_enterprise_name,1,30)-- name of the hierarchy same
239 ,p_primary_structure_flag => 'N'
240 ,p_position_control_structure_f => 'N'
241 ,p_org_structure_user_key => p_configuration_code || ' ' || l_enterprise_name
242 || ' Structure');
243 else
244 p_org_hierarchy_tab(l_org_hierarchy_count).name := substr(l_enterprise_name,1,30);
245 end if;
246
247 l_log_message := 'Created HIERARCHY STRUCTURE ' || l_enterprise_name;
248 per_ri_config_utilities.write_log(p_message => l_log_message);
249
250 if NOT (p_technical_summary_mode) then
251 hrdpp_create_org_structure_ver.insert_batch_lines
252 (p_batch_id => p_batch_header_id
253 --,p_data_pump_business_grp_name => l_business_group_name
254 ,p_data_pump_business_grp_name => null
255 ,p_link_value => per_ri_config_main.g_global_dp_link_value
256 ,p_user_sequence => 70
257 ,p_effective_date => g_config_effective_date
258 ,p_date_from => g_config_effective_date
259 ,p_version_number => 1
260 ,p_topnode_pos_ctrl_enabled_fla => 'N'
261 ,p_org_str_version_user_key => p_configuration_code || ' ' || l_enterprise_name
262 || ' Version'
263 ,p_org_structure_user_key => p_configuration_code || ' ' || l_enterprise_name
264 || ' Structure');
265 else
266 -- just populate varsion
267 p_org_hierarchy_tab(l_org_hierarchy_count).org_structure_version_id := 1;
268 end if;
269
270 l_log_message := 'Created HIERARCHY VERSION ' || to_char(1);
271 per_ri_config_utilities.write_log(p_message => l_log_message);
272 hr_utility.set_location(l_proc, 80);
273 else
274 null; -- report error
275 end if;
276
277 close csr_config_enterprise;
278
279 -- Create BIS profile option
280 --BIS_PRIMARY_RATE_TYPE
281 if NOT (p_technical_summary_mode) then
282 per_ri_config_utilities.set_profile_option_value
283 (p_level => 10001
284 ,p_level_value => 0
285 ,p_level_value_app => 'BIS'
286 ,p_profile_name => 'BIS_PRIMARY_CURRENCY_CODE'
287 ,p_profile_option_value => 'USD'); -- change it to enter bg currency
288 else
289 p_profile_dpe_ent_tab(l_profile_dpe_ent_count).level := 1001;
290 p_profile_dpe_ent_tab(l_profile_dpe_ent_count).level_value := 0;
291 p_profile_dpe_ent_tab(l_profile_dpe_ent_count).level_value_app := 'BIS';
292 p_profile_dpe_ent_tab(l_profile_dpe_ent_count).profile_name := 'BIS_PRIMARY_CURRENCY_CODE';
296
293 p_profile_dpe_ent_tab(l_profile_dpe_ent_count).profile_option_value := 'USD';
294
295 l_profile_dpe_ent_count := l_profile_dpe_ent_count + 1;
297 end if;
298 l_log_message := 'Created PROFILE_OPTION ' || 'BIS_PRIMARY_RATE_TYPE';
299 per_ri_config_utilities.write_log(p_message => l_log_message);
300 hr_utility.set_location(l_proc, 60);
301 hr_utility.set_location(' Leaving:'|| l_proc, 100);
302
303 EXCEPTION
304 when others then
305 l_error_message := 'Error in ' || l_proc;
306 hr_utility.trace(l_error_message || '-' || sqlerrm);
307 hr_utility.set_location(' Leaving:'|| l_proc, 500);
308 hr_utility.raise_error;
309
310 END create_enterprise_batch_lines;
311
312
313 /* --------------------------------------------------------------------------
314 -- Name : create_oper_comp_batch_lines
315 -- Purpose : This procedure creates oper comp batch lines records in the
316 -- data pump tables which would be loaded by the enterprise
317 -- structures configuration loader program for a specified
318 -- configuration.
319 -- Arguments : p_configuration_code
320 -- p_batch_header_id
321 -------------------------------------------------------------------------- */
322
323 PROCEDURE create_oper_comp_batch_lines(p_configuration_code in varchar2
324 ,p_batch_header_id in number
325 ,p_multiple_config_upload in boolean default FALSE
326 ,p_technical_summary_mode in boolean default FALSE
327 ,p_org_oc_tab in out nocopy
328 per_ri_config_tech_summary.org_oc_tab
329 ,p_org_oc_class_tab in out nocopy
330 per_ri_config_tech_summary.org_oc_class_tab
331 ,p_org_hierarchy_ele_oc_tab in out nocopy
332 per_ri_config_tech_summary.org_hierarchy_ele_oc_tab) IS
333
334 cursor csr_config_oper_comp (cp_configuration_code in varchar2) IS
335 select configuration_code,
336 config_information_id,
337 config_sequence,
338 config_information_category,
339 per_ri_config_utilities.return_config_entity_name(operating_company_name),
340 operating_company_name,
341 operating_company_short_name,
342 operating_company_hq_country,
343 operating_company_location_id
344 from per_ri_config_oper_comp_v
345 where configuration_code = cp_configuration_code;
346
347 l_configuration_code per_ri_config_information.configuration_code%type;
348 l_config_information_id per_ri_config_information.config_information_id%type;
349 l_config_sequence per_ri_config_information.config_sequence%type;
350 l_config_information_category per_ri_config_information.config_information_category%type;
351 l_operating_company_name per_ri_config_information.config_information1%type;
352 l_operating_company_name_orig per_ri_config_information.config_information1%type;
353 l_operating_company_short_name per_ri_config_information.config_information1%type;
354 l_operating_company_hq_country per_ri_config_information.config_information1%type;
355 l_operating_comp_location_id per_ri_config_information.config_information1%type;
356
357 l_business_group_country_code per_business_groups.name%type;
358 l_business_group_name per_business_groups.name%type;
359 l_oc_location_code per_ri_config_locations.description%type; -- fix for 4457389
360 l_enterprise_name per_ri_config_information.config_information1%type;
361
362 l_oc_count number(10) := 0;
363 l_oc_class_count number(10) := 0;
364 l_org_hierarchy_ele_oc_count number(10) := 0;
365 l_proc varchar2(72) := g_package ||'create_oper_comp_batch_lines';
366 l_log_message varchar2(360);
367 l_error_message varchar2(360);
368 l_multi_tenancy boolean := false;
369 l_system_model varchar2(10) := 'N';
370 l_sec_group_id varchar2(10);
371 l_selected_country_code varchar2(10);
372 l_selected_country_name varchar2(50);
373 l_enterprise_id number(9);
374
375 BEGIN
376
377 hr_utility.set_location('Entering:'|| l_proc, 10);
378
379 l_multi_tenancy := hr_multi_tenancy_pkg.is_multi_tenant_system;
380 l_sec_group_id := fnd_global.security_group_id;
381 if l_multi_tenancy then
382 l_system_model := hr_multi_tenancy_pkg.get_system_model;
383 end if;
384
385 BEGIN
386 select enterprise_id into l_enterprise_id from per_ent_security_groups
387 where business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID');
388 EXCEPTION
389 WHEN NO_DATA_FOUND THEN
390 l_enterprise_id := null;
391 END;
392
393 select legislation_code into l_selected_country_code from
394 per_business_groups where business_group_id =
395 fnd_profile.value('PER_BUSINESS_GROUP_ID');
396
397 select name into l_selected_country_name from per_business_groups
398 where business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID');
399
400 open csr_config_oper_comp(p_configuration_code);
401
402 loop
403 fetch csr_config_oper_comp into
407 l_config_information_category,
404 l_configuration_code,
405 l_config_information_id,
406 l_config_sequence,
408 l_operating_company_name,
409 l_operating_company_name_orig,
410 l_operating_company_short_name,
411 l_operating_company_hq_country,
412 l_operating_comp_location_id;
413
414 exit when csr_config_oper_comp%NOTFOUND;
415 if csr_config_oper_comp%FOUND then
416 hr_utility.set_location(l_proc, 20);
417
418 hr_utility.trace('l_operating_company_name = ' || l_operating_company_name);
419 l_business_group_country_code := per_ri_config_utilities.get_oc_bg_name
420 (p_configuration_code => p_configuration_code
421 ,p_operating_company_name => l_operating_company_name_orig);
422 hr_utility.set_location(l_business_group_country_code, 93000);
423 hr_utility.set_location(l_selected_country_code, 94000);
424 IF l_business_group_country_code = l_selected_country_code AND
425 l_multi_tenancy AND l_system_model = 'B' AND l_sec_group_id <> '0'
426 AND l_enterprise_id IS NOT NULL
427 THEN
428 hr_utility.set_location('BG Matches', 91000);
429 l_business_group_name := l_selected_country_name;
430 ELSE
431 l_business_group_name := per_ri_config_utilities.get_enterprise_short_name
432 (p_configuration_code => p_configuration_code)
433 || ' ' || l_business_group_country_code || per_ri_config_main.g_bg_name_suffix_string;
434 END IF;
435 hr_utility.trace('l_business_group_name = ' || l_business_group_name);
436
437 l_oc_location_code := per_ri_config_utilities.get_config_location_code
438 (p_configuration_code => p_configuration_code
439 ,p_location_id => l_operating_comp_location_id);
440
441 hr_utility.trace('l_business_group_country_code = ' || l_business_group_country_code);
442 hr_utility.trace('l_business_group_name = ' || l_business_group_name);
443 hr_utility.trace('l_oc_location_code = ' || l_oc_location_code);
444
445 if NOT (p_technical_summary_mode) then
446 -- if l_multi_tenancy AND l_system_model = 'B' AND l_sec_group_id <> '0' then
447 -- l_business_group_name := per_ri_config_utilities.get_business_group_name;
448 -- end if;
449 hrdpp_create_organization.insert_batch_lines
450 (p_batch_id => p_batch_header_id
451 ,p_link_value => per_ri_config_main.g_global_dp_link_value
452 ,p_user_sequence => 30
453 ,p_effective_date => g_config_effective_date
454 ,p_date_from => g_config_effective_date
455 ,p_data_pump_business_grp_name => l_business_group_name
456 ,p_language_code => hr_api.userenv_lang
457 ,p_name => l_operating_company_name
458 ,p_internal_external_flag => 'INT'
459 ,p_location_code => l_oc_location_code
460 ,p_org_user_key => l_operating_company_name || SYSDATE);
461
462
463 hr_utility.set_location(l_proc, 30);
464
465 hrdpp_update_organization.insert_batch_lines
466 (p_batch_id => p_batch_header_id
467 ,p_link_value => per_ri_config_main.g_global_dp_link_value
468 ,p_user_sequence => 40
469 ,p_effective_date => g_config_effective_date
470 ,p_date_from => g_config_effective_date
471 ,p_data_pump_business_grp_name => l_business_group_name
472 ,p_language_code => hr_api.userenv_lang
473 ,p_organization_name => l_operating_company_name
474 ,p_location_code => l_oc_location_code);
475 else
476 p_org_oc_tab(l_oc_count).effective_date := g_config_effective_date;
477 p_org_oc_tab(l_oc_count).date_from := g_config_effective_date;
478 p_org_oc_tab(l_oc_count).business_grp_name := l_business_group_name;
479 p_org_oc_tab(l_oc_count).name := l_operating_company_name;
480 p_org_oc_tab(l_oc_count).location_code := l_oc_location_code;
481 p_org_oc_tab(l_oc_count).internal_external_flag := 'INT';
482
483 l_oc_count := l_oc_count + 1;
484 end if;
485
486
487 l_log_message := 'Created OPERATING COMPANY ' || l_operating_company_name;
488 per_ri_config_utilities.write_log(p_message => l_log_message);
489 hr_utility.set_location(l_proc, 40);
490
491 -- create org hierarchy elements
492 --
493 l_enterprise_name := per_ri_config_utilities.get_enterprise_name
494 (p_configuration_code => p_configuration_code);
495 if NOT (p_technical_summary_mode) then
499 ,p_user_sequence => 80
496 hrdpp_create_hierarchy_element.insert_batch_lines
497 (p_batch_id => p_batch_header_id
498 ,p_link_value => per_ri_config_main.g_global_dp_link_value
500 ,p_effective_date => g_config_effective_date
501 ,p_date_from => g_config_effective_date
502 ,p_data_pump_business_grp_name => null
503 --,p_data_pump_business_grp_name => l_business_group_name
504 ,p_view_all_orgs => 'Y'
505 ,p_end_of_time => null
506 ,p_hr_installed => null
507 ,p_pa_installed => null
508 ,p_pos_control_enabled_flag => null
509 ,p_warning_raised => null
510 ,p_parent_organization_name => l_enterprise_name
511 ,p_child_organization_name => l_operating_company_name
512 ,p_language_code => hr_api.userenv_lang
513 ,p_org_str_version_user_key => p_configuration_code || ' ' || l_enterprise_name
514 || ' Version'
515 -- ,p_security_profile_name => l_business_group_name);
516 ,p_security_profile_name => null);
517
518 else
519 p_org_hierarchy_ele_oc_tab(l_org_hierarchy_ele_oc_count).org_structure_version_id := 1;
520 p_org_hierarchy_ele_oc_tab(l_org_hierarchy_ele_oc_count).parent_organization_name := l_enterprise_name;
521 p_org_hierarchy_ele_oc_tab(l_org_hierarchy_ele_oc_count).child_organization_name := l_operating_company_name;
522
523 l_org_hierarchy_ele_oc_count := l_org_hierarchy_ele_oc_count + 1 ;
524 end if;
525
526 l_log_message := 'Created HIERARCHY ELEMENT ENTERPRISE ' || l_enterprise_name || ' ' || l_operating_company_name;
527 per_ri_config_utilities.write_log(p_message => l_log_message);
528 --
529 -- Create HR_LEGAL classification
530 -- For all Localizations where no mandatory org info is defined.
531 -- and this classification is visible.
532 --
533 if per_ri_config_utilities.check_org_class_lookup_tag
534 (p_legislation_code => l_business_group_country_code
535 ,p_lookup_code => 'HR_OPC')
536 and NOT per_ri_config_utilities.mandatory_org_info_types
537 (p_legislation_code => l_business_group_country_code
538 ,p_org_classification => 'HR_OPC') then
539 if NOT (p_technical_summary_mode) then
540 hrdpp_create_org_classificatio.insert_batch_lines
541 (p_batch_id => p_batch_header_id
542 ,p_link_value => per_ri_config_main.g_global_dp_link_value
543 ,p_user_sequence => 50
544 ,p_effective_date => g_config_effective_date
545 ,p_data_pump_business_grp_name => l_business_group_name
546 ,p_org_classif_code => 'HR_OPC'
547 ,p_organization_name => l_operating_company_name
548 ,p_language_code => hr_api.userenv_lang);
549
550 else
551 p_org_oc_class_tab(l_oc_class_count).effective_date := g_config_effective_date;
552 p_org_oc_class_tab(l_oc_class_count).date_from := g_config_effective_date;
553 p_org_oc_class_tab(l_oc_class_count).business_grp_name := l_business_group_name;
554 p_org_oc_class_tab(l_oc_class_count).org_classif_code := 'HR_OPC';
555 p_org_oc_class_tab(l_oc_class_count).organization_name := l_operating_company_name;
556
557 l_oc_class_count := l_oc_class_count + 1;
558
559 end if;
560 hr_utility.set_location(l_proc, 50);
561 l_log_message := 'Created CLASSIFICATION HR_OPC ' || l_operating_company_name;
562 per_ri_config_utilities.write_log(p_message => l_log_message);
563 end if;
564 end if;
565 end loop;
566 close csr_config_oper_comp;
567 hr_utility.set_location(' Leaving:'|| l_proc, 100);
568
569 EXCEPTION
570 when others then
571 l_error_message := 'Error in ' || l_proc;
572 hr_utility.trace(l_error_message || '-' || sqlerrm);
573 hr_utility.set_location(' Leaving:'|| l_proc, 500);
574 hr_utility.raise_error;
575
576 END create_oper_comp_batch_lines;
577
578 /* --------------------------------------------------------------------------
579 -- Name : create_le_batch_lines
580 -- Purpose : This procedure creates leagl entity batch lines records in the
581 -- data pump tables which would be loaded by the enterprise
582 -- structures configuration loader program for a specified
583 -- configuration.
584 -- Arguments : p_configuration_code
585 -- p_batch_header_id
586 -------------------------------------------------------------------------- */
587
588 PROCEDURE create_le_batch_lines (p_configuration_code in varchar2
592 ,p_org_le_tab in out nocopy
589 ,p_batch_header_id in number
590 ,p_multiple_config_upload in boolean default FALSE
591 ,p_technical_summary_mode in boolean default FALSE
593 per_ri_config_tech_summary.org_le_tab
594 ,p_org_le_class_tab in out nocopy
595 per_ri_config_tech_summary.org_le_class_tab
596 ,p_org_hierarchy_ele_le_tab in out nocopy
597 per_ri_config_tech_summary.org_hierarchy_ele_le_tab) IS
598
599 cursor csr_config_legal_entity (cp_configuration_code in varchar2) IS
600 select configuration_code,
601 config_information_id,
602 config_sequence,
603 config_information_category,
604 per_ri_config_utilities.return_config_entity_name(legal_entity_name),
605 legal_entity_name,
606 legal_entity_short_name,
607 legal_entity_country,
608 legal_entity_parent_oper_comp,
609 legal_entity_location_id
610 from per_ri_config_legal_entity_v
611 where configuration_code = cp_configuration_code;
612
613 l_configuration_code per_ri_config_information.configuration_code%type;
614 l_config_information_id per_ri_config_information.config_information_id%type;
615 l_config_sequence per_ri_config_information.config_sequence%type;
616 l_config_information_category per_ri_config_information.config_information_category%type;
617 l_enterprise_name per_ri_config_information.config_information1%type;
618 l_legal_entity_name per_ri_config_information.config_information1%type;
619 l_legal_entity_name_original per_ri_config_information.config_information1%type;
620 l_legal_entity_short_name per_ri_config_information.config_information1%type;
621 l_legal_entity_country per_ri_config_information.config_information1%type;
622 l_le_parent_oper_comp per_ri_config_information.config_information1%type;
623 l_legal_entity_location_id per_ri_config_information.config_information1%type;
624
625
626 l_le_count number(10) := 0;
627 l_le_class_count number(10) := 0;
628 l_org_hierarchy_ele_le_count number(10) := 0;
629 l_proc varchar2(72) := g_package ||'create_le_batch_lines';
630 l_log_message varchar2(360);
631 l_error_message varchar2(360);
632
633 l_business_group_country_code per_business_groups.name%type;
634 l_business_group_name per_business_groups.name%type;
635 l_le_location_code per_ri_config_locations.description%type; -- fix for 4457389
636 l_mandatory_org_info_types boolean default true;
637 l_multi_tenancy boolean := false;
638 l_system_model varchar2(10) := 'N';
639 l_sec_group_id varchar2(10);
640 l_selected_country_code varchar2(10);
641 l_selected_country_name varchar2(50);
642 l_enterprise_id number(9);
643
644 BEGIN
645
646 hr_utility.set_location('Entering:'|| l_proc, 10);
647
648 l_multi_tenancy := hr_multi_tenancy_pkg.is_multi_tenant_system;
649 l_sec_group_id := fnd_global.security_group_id;
650 if l_multi_tenancy then
651 l_system_model := hr_multi_tenancy_pkg.get_system_model;
652 end if;
653
654 BEGIN
655 select enterprise_id into l_enterprise_id from per_ent_security_groups
656 where business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID');
657 EXCEPTION
658 WHEN NO_DATA_FOUND THEN
659 l_enterprise_id := null;
660 END;
661
662 select legislation_code into l_selected_country_code from
663 per_business_groups where business_group_id =
664 fnd_profile.value('PER_BUSINESS_GROUP_ID');
665
666 select name into l_selected_country_name from per_business_groups
667 where business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID');
668
669 open csr_config_legal_entity(p_configuration_code);
670
671 l_enterprise_name := per_ri_config_utilities.get_enterprise_name
672 (p_configuration_code => p_configuration_code);
673 loop
674 fetch csr_config_legal_entity into
675 l_configuration_code,
676 l_config_information_id,
677 l_config_sequence,
678 l_config_information_category,
679 l_legal_entity_name,
680 l_legal_entity_name_original,
681 l_legal_entity_short_name,
682 l_legal_entity_country,
683 l_le_parent_oper_comp,
684 l_legal_entity_location_id;
685
686 exit when csr_config_legal_entity%NOTFOUND;
687 hr_utility.set_location(l_proc, 20);
688
689 hr_utility.trace('l_legal_entity_name = ' || l_legal_entity_name);
690 hr_utility.trace('l_legal_entity_country = ' || l_legal_entity_country);
691
692 l_business_group_country_code := per_ri_config_utilities.get_le_bg_name
693 (p_configuration_code => p_configuration_code
694 ,p_legal_entity_name => l_legal_entity_name_original);
695 hr_utility.set_location(l_business_group_country_code, 93000);
699 AND l_enterprise_id IS NOT NULL
696 hr_utility.set_location(l_selected_country_code, 94000);
697 IF l_business_group_country_code = l_selected_country_code AND
698 l_multi_tenancy AND l_system_model = 'B' AND l_sec_group_id <> '0'
700 THEN
701 hr_utility.set_location('BG Matches', 91000);
702 l_business_group_name := l_selected_country_name;
703 ELSE
704 l_business_group_name := per_ri_config_utilities.get_enterprise_short_name
705 (p_configuration_code => p_configuration_code)
706 || ' ' || l_business_group_country_code || per_ri_config_main.g_bg_name_suffix_string;
707 END IF;
708 hr_utility.trace('l_business_group_name = ' || l_business_group_name);
709
710 l_le_location_code := per_ri_config_utilities.get_config_location_code
711 (p_configuration_code => p_configuration_code
712 ,p_location_id => l_legal_entity_location_id);
713 hr_utility.trace('l_legal_entity_location_id = ' || l_legal_entity_location_id);
714 hr_utility.set_location(l_proc, 30);
715
716 if NOT (p_technical_summary_mode) then
717 -- if l_multi_tenancy AND l_system_model = 'B' AND l_sec_group_id <> '0' then
718 -- l_business_group_name := per_ri_config_utilities.get_business_group_name;
719 -- end if;
720 hrdpp_create_organization.insert_batch_lines
721 (p_batch_id => p_batch_header_id
722 ,p_link_value => per_ri_config_main.g_global_dp_link_value
723 ,p_user_sequence => 30
724 ,p_effective_date => g_config_effective_date
725 ,p_date_from => g_config_effective_date
726 ,p_data_pump_business_grp_name => l_business_group_name
727 ,p_name => l_legal_entity_name
728 ,p_location_code => l_le_location_code
729 ,p_internal_external_flag => 'INT'
730 ,p_org_user_key => l_legal_entity_name || SYSDATE);
731
732
733 hrdpp_update_organization.insert_batch_lines
734 (p_batch_id => p_batch_header_id
735 ,p_link_value => per_ri_config_main.g_global_dp_link_value
736 ,p_user_sequence => 40
737 ,p_effective_date => g_config_effective_date
738 ,p_date_from => g_config_effective_date
739 ,p_data_pump_business_grp_name => l_business_group_name
740 ,p_language_code => hr_api.userenv_lang
741 ,p_organization_name => l_legal_entity_name
742 ,p_location_code => l_le_location_code);
743 hr_utility.set_location(l_proc, 40);
744 else
745 p_org_le_tab(l_le_count).effective_date := g_config_effective_date;
746 p_org_le_tab(l_le_count).date_from := g_config_effective_date;
747 p_org_le_tab(l_le_count).business_grp_name := l_business_group_name;
748 p_org_le_tab(l_le_count).name := l_legal_entity_name;
749 p_org_le_tab(l_le_count).location_code := l_le_location_code;
750 p_org_le_tab(l_le_count).internal_external_flag := 'INT';
751
752 l_le_count := l_le_count + 1;
753 end if;
754
755 l_log_message := 'Created LEGAL ENTITY ' || l_legal_entity_name;
756 per_ri_config_utilities.write_log(p_message => l_log_message);
757
758 --
759 -- Create CC classification
760 -- For all localization if no mandatory org info is defined.
761 -- and this classification is visible.
762 --
763 if per_ri_config_utilities.check_org_class_lookup_tag
764 (p_legislation_code => l_business_group_country_code
765 ,p_lookup_code => 'CC')
766 and NOT per_ri_config_utilities.mandatory_org_info_types
767 (p_legislation_code => l_business_group_country_code
768 ,p_org_classification => 'CC') then
769 if NOT (p_technical_summary_mode) then
770 hrdpp_create_org_classificatio.insert_batch_lines
771 (p_batch_id => p_batch_header_id
772 ,p_link_value => per_ri_config_main.g_global_dp_link_value
773 ,p_user_sequence => 50
774 ,p_effective_date => g_config_effective_date
775 ,p_data_pump_business_grp_name => l_business_group_name
776 ,p_org_classif_code => 'CC'
777 ,p_organization_name => l_legal_entity_name
778 ,p_language_code => hr_api.userenv_lang);
779
780 else
781 p_org_le_class_tab(l_le_class_count).effective_date := g_config_effective_date;
782 p_org_le_class_tab(l_le_class_count).date_from := g_config_effective_date;
783 p_org_le_class_tab(l_le_class_count).business_grp_name := l_business_group_name;
784 p_org_le_class_tab(l_le_class_count).org_classif_code := 'CC';
788
785 p_org_le_class_tab(l_le_class_count).organization_name := l_legal_entity_name;
786
787 l_le_class_count := l_le_class_count + 1;
789 end if;
790 hr_utility.set_location(l_proc, 50);
791 l_log_message := 'Created CLASSIFICATION CC ' || l_legal_entity_name;
792 per_ri_config_utilities.write_log(p_message => l_log_message);
793 end if;
794
795 --
796 -- Create HR_LEGAL_EMPLOYER classification
797 -- For all localization if no mandatory org info is defined.
798 -- and this classification is visible.
799 --
800 if per_ri_config_utilities.check_org_class_lookup_tag
801 (p_legislation_code => l_business_group_country_code
802 ,p_lookup_code => 'HR_LEGAL_EMPLOYER')
803 and NOT per_ri_config_utilities.mandatory_org_info_types
804 (p_legislation_code => l_business_group_country_code
805 ,p_org_classification => 'HR_LEGAL_EMPLOYER') then
806 if NOT (p_technical_summary_mode) then
807 hrdpp_create_org_classificatio.insert_batch_lines
808 (p_batch_id => p_batch_header_id
809 ,p_link_value => per_ri_config_main.g_global_dp_link_value
810 ,p_user_sequence => 50
811 ,p_effective_date => g_config_effective_date
812 ,p_data_pump_business_grp_name => l_business_group_name
813 ,p_org_classif_code => 'HR_LEGAL_EMPLOYER'
814 ,p_organization_name => l_legal_entity_name
815 ,p_language_code => hr_api.userenv_lang);
816 else
817 p_org_le_class_tab(l_le_class_count).effective_date := g_config_effective_date;
818 p_org_le_class_tab(l_le_class_count).date_from := g_config_effective_date;
819 p_org_le_class_tab(l_le_class_count).business_grp_name := l_business_group_name;
820 p_org_le_class_tab(l_le_class_count).org_classif_code := 'HR_LEGAL_EMPLOYER';
821 p_org_le_class_tab(l_le_class_count).organization_name := l_legal_entity_name;
822
823 l_le_class_count := l_le_class_count + 1;
824
825 end if;
826
827 hr_utility.set_location(l_proc, 60);
828 l_log_message := 'Created CLASSIFICATION HR_LEGAL_EMPLOYER ' || l_legal_entity_name;
829 per_ri_config_utilities.write_log(p_message => l_log_message);
830 end if;
831
832 --
833 -- Create FR_SOCIETE classification
834 -- For FR localization if no mandatory org info is defined.
835 -- and this classification is visible.
836 --
837 if l_business_group_country_code = 'FR' and
838 per_ri_config_utilities.check_org_class_lookup_tag
839 (p_legislation_code => l_business_group_country_code
840 ,p_lookup_code => 'FR_SOCIETE')
841 and NOT per_ri_config_utilities.mandatory_org_info_types
842 (p_legislation_code => l_business_group_country_code
843 ,p_org_classification => 'FR_SOCIETE') then
844 if NOT (p_technical_summary_mode) then
845 hrdpp_create_org_classificatio.insert_batch_lines
846 (p_batch_id => p_batch_header_id
847 ,p_link_value => per_ri_config_main.g_global_dp_link_value
848 ,p_user_sequence => 50
849 ,p_effective_date => g_config_effective_date
850 ,p_data_pump_business_grp_name => l_business_group_name
851 ,p_org_classif_code => 'FR_SOCIETE'
852 ,p_organization_name => l_legal_entity_name
853 ,p_language_code => hr_api.userenv_lang);
854
855 else
856 p_org_le_class_tab(l_le_class_count).effective_date := g_config_effective_date;
857 p_org_le_class_tab(l_le_class_count).date_from := g_config_effective_date;
858 p_org_le_class_tab(l_le_class_count).business_grp_name := l_business_group_name;
859 p_org_le_class_tab(l_le_class_count).org_classif_code := 'FR_SOCIETE';
860 p_org_le_class_tab(l_le_class_count).organization_name := l_legal_entity_name;
861
862 l_le_class_count := l_le_class_count + 1;
863
864 end if;
865 hr_utility.set_location(l_proc, 70);
866 l_log_message := 'Created CLASSIFICATION FR_SOCIETE ' || l_legal_entity_name;
867 per_ri_config_utilities.write_log(p_message => l_log_message);
868 end if;
869
870 --
871 -- Create IN_COMPANY classification
872 -- For IN localization if no mandatory org info is defined.
873 -- and this classification is visible.
874 --
875
876 if l_business_group_country_code = 'IN' and
877 per_ri_config_utilities.check_org_class_lookup_tag
878 (p_legislation_code => l_business_group_country_code
879 ,p_lookup_code => 'IN_COMPANY')
880 and NOT per_ri_config_utilities.mandatory_org_info_types
884 hrdpp_create_org_classificatio.insert_batch_lines
881 (p_legislation_code => l_business_group_country_code
882 ,p_org_classification => 'IN_COMPANY')then
883 if NOT (p_technical_summary_mode) then
885 (p_batch_id => p_batch_header_id
886 ,p_link_value => per_ri_config_main.g_global_dp_link_value
887 ,p_user_sequence => 50
888 ,p_effective_date => g_config_effective_date
889 ,p_data_pump_business_grp_name => l_business_group_name
890 ,p_org_classif_code => 'IN_COMPANY'
891 ,p_organization_name => l_legal_entity_name
892 ,p_language_code => hr_api.userenv_lang);
893
894 else
895 p_org_le_class_tab(l_le_class_count).effective_date := g_config_effective_date;
896 p_org_le_class_tab(l_le_class_count).date_from := g_config_effective_date;
897 p_org_le_class_tab(l_le_class_count).business_grp_name := l_business_group_name;
898 p_org_le_class_tab(l_le_class_count).org_classif_code := 'IN_COMPANY';
899 p_org_le_class_tab(l_le_class_count).organization_name := l_legal_entity_name;
900
901 l_le_class_count := l_le_class_count + 1;
902
903 end if;
904 hr_utility.set_location(l_proc, 80);
905 l_log_message := 'Created CLASSIFICATION IN_COMPANY ' || l_legal_entity_name;
906 per_ri_config_utilities.write_log(p_message => l_log_message);
907 end if;
908
909 --
910 -- Create HR_LEGAL classification
911 -- For all Localizations where no mandatory org info is defined.
912 -- and this classification is visible.
913 --
914 if per_ri_config_utilities.check_org_class_lookup_tag
915 (p_legislation_code => l_business_group_country_code
916 ,p_lookup_code => 'HR_LEGAL')
917 and NOT per_ri_config_utilities.mandatory_org_info_types
918 (p_legislation_code => l_business_group_country_code
919 ,p_org_classification => 'HR_LEGAL') then
920 if NOT (p_technical_summary_mode) then
921 hrdpp_create_org_classificatio.insert_batch_lines
922 (p_batch_id => p_batch_header_id
923 ,p_link_value => per_ri_config_main.g_global_dp_link_value
924 ,p_user_sequence => 50
925 ,p_effective_date => g_config_effective_date
926 ,p_data_pump_business_grp_name => l_business_group_name
927 ,p_org_classif_code => 'HR_LEGAL'
928 ,p_organization_name => l_legal_entity_name
929 ,p_language_code => hr_api.userenv_lang);
930 else
931 p_org_le_class_tab(l_le_class_count).effective_date := g_config_effective_date;
932 p_org_le_class_tab(l_le_class_count).date_from := g_config_effective_date;
933 p_org_le_class_tab(l_le_class_count).business_grp_name := l_business_group_name;
934 p_org_le_class_tab(l_le_class_count).org_classif_code := 'HR_LEGAL';
935 p_org_le_class_tab(l_le_class_count).organization_name := l_legal_entity_name;
936
937 l_le_class_count := l_le_class_count + 1;
938
939 end if;
940
941 hr_utility.set_location(l_proc, 90);
942 l_log_message := 'Created CLASSIFICATION HR_LEGAL ' || l_legal_entity_name;
943 per_ri_config_utilities.write_log(p_message => l_log_message);
944 end if;
945
946 --
947 -- create org hierarchy elements
948 --
949 if NOT (p_technical_summary_mode) then
950 hrdpp_create_hierarchy_element.insert_batch_lines
951 (p_batch_id => p_batch_header_id
952 ,p_link_value => per_ri_config_main.g_global_dp_link_value
953 ,p_user_sequence => 90
954 ,p_effective_date => g_config_effective_date
955 ,p_date_from => g_config_effective_date
956 ,p_data_pump_business_grp_name => null
957 --,p_data_pump_business_grp_name => l_business_group_name
958 ,p_view_all_orgs => 'Y'
959 ,p_end_of_time => null
960 ,p_hr_installed => null
961 ,p_pa_installed => null
962 ,p_pos_control_enabled_flag => null
963 ,p_warning_raised => null
964 ,p_parent_organization_name => per_ri_config_utilities.return_config_entity_name
965 (l_le_parent_oper_comp)
966 ,p_child_organization_name => l_legal_entity_name
967 ,p_language_code => hr_api.userenv_lang
968 ,p_org_str_version_user_key => p_configuration_code || ' ' || l_enterprise_name
969 || ' Version'
973 p_org_hierarchy_ele_le_tab(l_org_hierarchy_ele_le_count).org_structure_version_id := 1;
970 --,p_security_profile_name => l_business_group_name);
971 ,p_security_profile_name => null);
972 else
974 p_org_hierarchy_ele_le_tab(l_org_hierarchy_ele_le_count).parent_organization_name := per_ri_config_utilities.return_config_entity_name
975 (l_le_parent_oper_comp);
976 p_org_hierarchy_ele_le_tab(l_org_hierarchy_ele_le_count).child_organization_name := l_legal_entity_name;
977
978 l_org_hierarchy_ele_le_count := l_org_hierarchy_ele_le_count + 1 ;
979 end if;
980
981 l_log_message := 'Created HIERARCHY ELEMENT OPERATING_COMPANY ' || l_le_parent_oper_comp || ' ' || l_legal_entity_name;
982 per_ri_config_utilities.write_log(p_message => l_log_message);
983
984 hr_utility.set_location(l_proc, 100);
985 end loop;
986 close csr_config_legal_entity;
987
988 hr_utility.set_location(' Leaving:'|| l_proc, 110);
989
990 EXCEPTION
991 when others then
992 l_error_message := 'Error in ' || l_proc;
993 hr_utility.trace(l_error_message || '-' || sqlerrm);
994 hr_utility.set_location(' Leaving:'|| l_proc, 500);
995 hr_utility.raise_error;
996
997 END create_le_batch_lines;
998
999 /* --------------------------------------------------------------------------
1000 -- Name : create_locations_batch_lines
1001 -- Purpose : This procedure creates locations batch lines records in the
1002 -- data pump tables which would be loaded by the enterprise
1003 -- structures configuration loader program for a specified
1004 -- configuration.
1005 -- Arguments : p_configuration_code
1006 -- p_batch_header_id
1007 -------------------------------------------------------------------------- */
1008 PROCEDURE create_locations_batch_lines(p_configuration_code in varchar2
1009 ,p_batch_header_id in number
1010 ,p_multiple_config_upload in boolean default FALSE
1011 ,p_technical_summary_mode in boolean default FALSE
1012 ,p_location_tab in out nocopy
1013 per_ri_config_tech_summary.location_tab) IS
1014
1015 cursor csr_config_location(cp_configuration_code in varchar2) IS
1016 select configuration_code,
1017 configuration_context,
1018 location_id,
1019 location_code,
1020 description,
1021 style,
1022 address_line_1,
1023 address_line_2,
1024 address_line_3,
1025 town_or_city,
1026 country,
1027 postal_code,
1028 region_1,
1029 region_2,
1030 region_3,
1031 telephone_number_1,
1032 telephone_number_2,
1033 telephone_number_3,
1034 loc_information13,
1035 loc_information14,
1036 loc_information15,
1037 loc_information16,
1038 loc_information17,
1039 loc_information18,
1040 loc_information19,
1041 loc_information20,
1042 object_version_number
1043 from per_ri_config_locations
1044 where configuration_code = cp_configuration_code;
1045
1046 -- Get the Country Code when style is generic
1047 CURSOR csr_get_country_code(cp_country_name in varchar2) IS
1048 SELECT territory_code
1049 FROM fnd_territories_vl
1050 WHERE territory_short_name = cp_country_name;
1051
1052 l_configuration_code per_ri_config_locations.configuration_code%type;
1053 l_configuration_context per_ri_config_locations.configuration_context%type;
1054 l_location_id per_ri_config_locations.location_id%type;
1055 l_location_code per_ri_config_locations.description%type; -- fix for 4457389
1056 l_description per_ri_config_locations.description %type;
1057 l_style per_ri_config_locations.style%type;
1058 l_address_line_1 per_ri_config_locations.address_line_1%type;
1059 l_address_line_2 per_ri_config_locations.address_line_2%type;
1060 l_address_line_3 per_ri_config_locations.address_line_3%type;
1061 l_town_or_city per_ri_config_locations.town_or_city%type;
1062 l_country per_ri_config_locations.country%type;
1063 l_postal_code per_ri_config_locations.postal_code%type;
1064 l_region_1 per_ri_config_locations.region_1%type;
1065 l_region_2 per_ri_config_locations.region_2%type;
1066 l_region_3 per_ri_config_locations.region_3%type;
1067 l_telephone_number_1 per_ri_config_locations.telephone_number_1%type;
1068 l_telephone_number_2 per_ri_config_locations.telephone_number_2%type;
1069 l_telephone_number_3 per_ri_config_locations.telephone_number_3%type;
1070 l_loc_information13 per_ri_config_locations.loc_information13%type;
1071 l_loc_information14 per_ri_config_locations.loc_information14%type;
1072 l_loc_information15 per_ri_config_locations.loc_information15%type;
1073 l_loc_information16 per_ri_config_locations.loc_information16%type;
1074 l_loc_information17 per_ri_config_locations.loc_information17%type;
1075 l_loc_information18 per_ri_config_locations.loc_information18%type;
1079
1076 l_loc_information19 per_ri_config_locations.loc_information19%type;
1077 l_loc_information20 per_ri_config_locations.loc_information20%type;
1078 l_object_version_number per_ri_config_locations.object_version_number%type;
1080 l_locations_count number(10) := 0;
1081 l_proc varchar2(72) := g_package || 'create_locations_batch_lines';
1082 l_log_message varchar2(360);
1083 l_error_message varchar2(360);
1084
1085 BEGIN
1086 hr_utility.set_location('Entering:'|| l_proc, 10);
1087
1088 open csr_config_location(p_configuration_code);
1089 LOOP
1090 fetch csr_config_location into
1091 l_configuration_code,
1092 l_configuration_context,
1093 l_location_id,
1094 l_location_code,
1095 l_description,
1096 l_style,
1097 l_address_line_1,
1098 l_address_line_2,
1099 l_address_line_3,
1100 l_town_or_city,
1101 l_country,
1102 l_postal_code,
1103 l_region_1,
1104 l_region_2,
1105 l_region_3,
1106 l_telephone_number_1,
1107 l_telephone_number_2,
1108 l_telephone_number_3,
1109 l_loc_information13,
1110 l_loc_information14,
1111 l_loc_information15,
1112 l_loc_information16,
1113 l_loc_information17,
1114 l_loc_information18,
1115 l_loc_information19,
1116 l_loc_information20,
1117 l_object_version_number;
1118
1119 exit when csr_config_location%NOTFOUND;
1120 l_location_code := per_ri_config_utilities.return_config_entity_name(l_location_code);
1121
1122 if l_style = 'GENERIC' then
1123
1124 OPEN csr_get_country_code(l_country);
1125 FETCH csr_get_country_code into l_country;
1126 CLOSE csr_get_country_code;
1127
1128 end if;
1129
1130 hr_utility.trace( 'l_location_code = ' || l_location_code);
1131 hr_utility.trace( 'l_style = ' || l_style);
1132 if NOT (p_technical_summary_mode) then
1133 hrdpp_create_location.insert_batch_lines
1134 (p_batch_id => p_batch_header_id
1135 ,p_effective_date => g_config_effective_date
1136 ,p_link_value => per_ri_config_main.g_global_dp_link_value
1137 ,p_user_sequence => 10
1138 ,p_location_code => l_location_code
1139 ,p_description => l_description
1140 ,p_address_line_1 => l_address_line_1
1141 ,p_address_line_2 => l_address_line_2
1142 ,p_address_line_3 => l_address_line_3
1143 ,p_bill_to_site_flag => 'Y'
1144 ,p_country => l_country
1145 ,p_in_organization_flag => 'Y'
1146 ,p_postal_code => l_postal_code
1147 ,p_receiving_site_flag => 'Y'
1148 ,p_region_1 => l_region_1
1149 ,p_region_2 => l_region_2
1150 ,p_region_3 => l_region_3
1151 ,p_ship_to_site_flag => 'Y'
1152 ,p_style => l_style
1153 ,p_telephone_number_1 => l_telephone_number_1
1154 ,p_telephone_number_2 => l_telephone_number_2
1155 ,p_telephone_number_3 => l_telephone_number_3
1156 ,p_town_or_city => l_town_or_city
1157 ,p_loc_information13 => l_loc_information13
1158 ,p_loc_information14 => l_loc_information14
1159 ,p_loc_information15 => l_loc_information15
1160 ,p_loc_information16 => l_loc_information16
1161 ,p_loc_information17 => l_loc_information17
1162 ,p_loc_information18 => l_loc_information18
1163 ,p_loc_information19 => l_loc_information19
1164 ,p_loc_information20 => l_loc_information20
1165 ,p_location_user_key => l_location_code
1166 ,p_ship_to_location_code => l_location_code);
1167 else
1168 p_location_tab(l_locations_count).location_code := l_location_code;
1169 p_location_tab(l_locations_count).description := l_description;
1170 p_location_tab(l_locations_count).address_line_1 := l_address_line_1;
1171 p_location_tab(l_locations_count).address_line_2 := l_address_line_2;
1172 p_location_tab(l_locations_count).address_line_3 := l_address_line_3;
1173 p_location_tab(l_locations_count).bill_to_site_flag := 'Y';
1177 p_location_tab(l_locations_count).receiving_site_flag := 'Y';
1174 p_location_tab(l_locations_count).country := l_country;
1175 p_location_tab(l_locations_count).in_organization_flag := 'Y';
1176 p_location_tab(l_locations_count).postal_code := l_postal_code;
1178 p_location_tab(l_locations_count).region_1 := l_region_1;
1179 p_location_tab(l_locations_count).region_2 := l_region_2;
1180 p_location_tab(l_locations_count).region_3 := l_region_3;
1181 p_location_tab(l_locations_count).ship_to_site_flag := 'Y';
1182 p_location_tab(l_locations_count).style := l_style;
1183 p_location_tab(l_locations_count).telephone_number_1 := l_telephone_number_1;
1184 p_location_tab(l_locations_count).telephone_number_2 := l_telephone_number_2;
1185 p_location_tab(l_locations_count).telephone_number_3 := l_telephone_number_3;
1186 p_location_tab(l_locations_count).town_or_city := l_town_or_city;
1187 p_location_tab(l_locations_count).loc_information13 := l_loc_information13;
1188 p_location_tab(l_locations_count).loc_information14 := l_loc_information14;
1189 p_location_tab(l_locations_count).loc_information15 := l_loc_information15;
1190 p_location_tab(l_locations_count).loc_information16 := l_loc_information16;
1191 p_location_tab(l_locations_count).loc_information17 := l_loc_information17;
1192 p_location_tab(l_locations_count).loc_information18 := l_loc_information18;
1193 p_location_tab(l_locations_count).loc_information19 := l_loc_information19;
1194 p_location_tab(l_locations_count).loc_information20 := l_loc_information20;
1195 p_location_tab(l_locations_count).ship_to_location_code := l_location_code;
1196
1197 l_locations_count := l_locations_count + 1;
1198 end if;
1199
1200 l_log_message := 'Created LOCATION ' || l_location_code;
1201 per_ri_config_utilities.write_log(p_message => l_log_message);
1202 hr_utility.set_location(l_proc, 40);
1203 END LOOP;
1204 close csr_config_location;
1205 hr_utility.set_location(' Leaving :' || l_proc, 50);
1206
1207 EXCEPTION
1208 when others then
1209 l_error_message := 'Error in ' || l_proc;
1210 hr_utility.trace(l_error_message || '-' || sqlerrm);
1211 hr_utility.set_location(' Leaving:'|| l_proc, 500);
1212 hr_utility.raise_error;
1213 END create_locations_batch_lines;
1214
1215
1216 /* --------------------------------------------------------------------------
1217 -- Name : create_bg_batch_lines
1218 -- Purpose : This procedure creates business groups batch lines records in
1219 -- data pump tables which would be loaded by the enterprise
1220 -- structures configuration loader program for a specified
1221 -- configuration.
1222 -- Arguments : p_configuration_code
1223 -- p_batch_header_id
1224 -- p_country_tab_out
1225 -------------------------------------------------------------------------- */
1226
1227 PROCEDURE create_bg_batch_lines(p_batch_header_id in number
1228 ,p_configuration_code in varchar2
1229 ,p_country_tab_out in out nocopy country_tab
1230 ,p_multiple_config_upload in boolean default FALSE
1231 ,p_technical_summary_mode in boolean default FALSE
1232 ,p_bg_tab in out nocopy
1233 per_ri_config_tech_summary.bg_tab
1234 ,p_sg_tab in out nocopy
1235 per_ri_config_tech_summary.sg_tab
1236 ,p_post_install_tab in out nocopy
1237 per_ri_config_tech_summary.post_install_tab
1238 ,p_int_bg_resp_tab in out nocopy
1239 per_ri_config_fnd_hr_entity.int_bg_resp_tab) IS
1240
1241 cursor csr_config_business_groups(cp_configuration_code in varchar2) IS
1242 select distinct configuration_code,
1243 per_ri_config_utilities.business_group_decision(configuration_code,country_code)
1244 from per_ri_config_country_v
1245 where configuration_code = cp_configuration_code;
1246
1247 -- Bugfix 4133935
1248 -- Need the enterprise BG country
1249 cursor csr_config_information(cp_configuration_code in varchar2) IS
1250 select config_information3 country_code
1251 from per_ri_config_information
1252 where configuration_code = cp_configuration_code
1253 and config_information_category = 'CONFIG ENTERPRISE';
1254
1255 l_configuration_code per_ri_config_information.configuration_code%type;
1256 l_bg_country_name per_ri_config_information.config_information1%type;
1257 l_enterprise_bg_country_code per_ri_config_information.config_information2%type;
1258 l_business_group_name hr_all_organization_units.name%type;
1259 l_batch_header_id number(15);
1260 l_legislation_code per_business_groups.legislation_code%type;
1261 l_job_flex_stru_code varchar2(30);
1262 l_position_flex_stru_code varchar2(30);
1263 l_grade_flex_stru_code varchar2(30);
1264 l_group_flex_stru_code varchar2(30);
1265 l_cost_flex_stru_code varchar2(30);
1266 l_security_group_name varchar2(30) default 'STANDARD';
1267 l_competence_flex_stru_code varchar2(30);
1268 l_currency_code varchar2(30);
1272 l_bg_count number(8) := 0;
1269 l_currecny_flag varchar2(30);
1270 l_country_tab country_tab;
1271
1273 l_sg_count number(8) := 0;
1274 l_int_bg_resp_count number(8) := 0;
1275 l_post_install_count number(8) := 0;
1276 l_proc varchar2(72) := g_package || 'create_bg_batch_lines';
1277 l_log_message varchar2(360);
1278 l_error_message varchar2(360);
1279
1280 l_per_post_install boolean;
1281 l_pay_post_install boolean;
1282
1283 l_multi_tenancy boolean default false;
1284 l_system_model varchar2(10) := 'N';
1285 l_sec_group_id varchar2(10);
1286 l_country_selected PER_BUSINESS_GROUPS.LEGISLATION_CODE%TYPe;
1287 l_enterprise_id number(9);
1288
1289 BEGIN
1290 hr_utility.set_location('Entering:'|| l_proc, 10);
1291
1292 select legislation_code into l_country_selected from per_business_groups
1293 where business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID');
1294
1295 l_multi_tenancy := hr_multi_tenancy_pkg.is_multi_tenant_system;
1296 l_sec_group_id := fnd_global.security_group_id;
1297 if l_multi_tenancy then
1298 l_system_model := hr_multi_tenancy_pkg.get_system_model;
1299 end if;
1300
1301 hr_utility.set_location('Sec Group Id Is '||fnd_global.security_group_id, 95000);
1302 IF l_multi_tenancy AND l_system_model = 'B' AND l_sec_group_id <> '0'
1303 THEN
1304 hr_utility.set_location('Inside the if ', 96000);
1305 select security_group_name into l_security_group_name
1306 from fnd_security_groups_tl
1307 where security_group_id = fnd_global.security_group_id
1308 and language = userenv('LANG');
1309 hr_utility.set_location('Sec Group Name Is '|| l_security_group_name, 97000);
1310 END IF;
1311
1312 BEGIN
1313 select enterprise_id into l_enterprise_id from per_ent_security_groups
1314 where business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID');
1315 EXCEPTION
1316 WHEN NO_DATA_FOUND THEN
1317 l_enterprise_id := null;
1318 END;
1319 hr_utility.set_location('The Legislation Code is '|| l_country_selected, 43);
1320
1321
1322 open csr_config_business_groups(p_configuration_code);
1323
1324 -- bugfix 4133935
1325 open csr_config_information(p_configuration_code);
1326 fetch csr_config_information into
1327 l_enterprise_bg_country_code;
1328 --
1329
1330 loop
1331 fetch csr_config_business_groups into
1332 l_configuration_code,
1333 l_bg_country_name;
1334
1335 exit when csr_config_business_groups%NOTFOUND;
1336
1337 l_business_group_name := per_ri_config_utilities.get_enterprise_short_name
1338 (p_configuration_code => p_configuration_code)
1339 || ' ' || l_bg_country_name || per_ri_config_main.g_bg_name_suffix_string;
1340
1341 hr_utility.set_location('The name selected is '|| l_bg_country_name, 44) ;
1342
1346 l_legislation_code := l_bg_country_name;
1343 if l_bg_country_name = 'INT' then
1344 l_legislation_code := 'BF'; -- For Burkina Faso
1345 else
1347 end if;
1348
1349 if l_bg_country_name = 'INT' then
1350 l_currency_code := 'USD'; -- chnage it out corporate LE bg currency.
1351 else
1352 l_currency_code := per_ri_config_utilities.get_country_currency
1353 (p_legislation_code => l_bg_country_name);
1354 end if;
1355
1356 -- For those BG legislations that do not have a currency code defined
1357 -- i. Default the currency code for the legislation defined
1358 -- at the Enterprise
1359 -- ii. If a currency code does not exist for the BG legislation at the
1360 -- Enterprise, default to USD
1361 if l_currency_code is null then
1362 l_currency_code := per_ri_config_utilities.get_country_currency
1363 (p_legislation_code => l_enterprise_bg_country_code);
1364 end if;
1365
1366 if l_currency_code is null then
1367 l_currency_code := 'USD';
1368 end if;
1369
1370 l_job_flex_stru_code := per_ri_config_utilities.get_bg_job_keyflex_name
1371 (p_configuration_code => p_configuration_code
1372 ,p_bg_country_code => l_bg_country_name);
1373 hr_utility.trace('Job Name ' || l_job_flex_stru_code);
1374
1375 l_position_flex_stru_code := per_ri_config_utilities.get_bg_pos_keyflex_name
1376 (p_configuration_code => p_configuration_code
1377 ,p_bg_country_code => l_bg_country_name);
1378
1379 hr_utility.trace('Position Name ' || l_position_flex_stru_code);
1380 l_grade_flex_stru_code := per_ri_config_utilities.get_bg_grd_keyflex_name
1381 (p_configuration_code => p_configuration_code
1382 ,p_bg_country_code => l_bg_country_name);
1383
1384 hr_utility.trace('Grade Name ' || l_grade_flex_stru_code);
1385
1386 hr_utility.trace('l_legislation_code = ' || l_legislation_code);
1387
1388 l_currecny_flag := per_ri_config_utilities.check_currency_enabled (p_legislation_code => l_legislation_code);
1389 hr_utility.trace('l_currecny_flag = ' || l_currecny_flag);
1390 if l_currecny_flag = 'X' then
1391 per_ri_config_utilities.enable_country_currency (p_legislation_code => l_legislation_code);
1392 hr_utility.trace('l_currecny_flag = ' || l_currecny_flag);
1393 end if;
1394
1395 IF l_bg_country_name = l_country_selected AND
1396 l_multi_tenancy AND l_system_model = 'B' AND l_sec_group_id <> '0'
1397 AND l_enterprise_id IS NOT NULL
1398 THEN
1399 hr_utility.set_location('The BG has to be skipped', 90000);
1400 ELSE
1401 if NOT (p_technical_summary_mode) then
1402
1403 hrdpp_create_business_group.insert_batch_lines
1404 (p_batch_id => p_batch_header_id
1405 ,p_link_value => per_ri_config_main.g_global_dp_link_value
1406 ,p_user_sequence => 20
1407 ,p_effective_date => g_config_effective_date
1408 ,p_language_code => hr_api.userenv_lang
1409 ,p_date_from => g_config_effective_date
1410 ,p_name => l_business_group_name
1411 ,p_type => null
1412 ,p_internal_external_flag => 'INT'
1413 ,p_short_name => l_business_group_name
1414 ,p_emp_gen_method => 'A'
1415 ,p_app_gen_method => 'A'
1416 ,p_cwk_gen_method => 'A'
1417 ,p_legislation_code => l_legislation_code
1418 ,p_currency_code => l_currency_code
1419 ,p_fiscal_year_start => null
1420 ,p_min_work_age => null
1421 ,p_max_work_age => null
1422 ,p_location_code => null
1423 ,p_org_user_key => l_business_group_name || sysdate
1424 ,p_grade_flex_stru_code => l_grade_flex_stru_code
1425 ,p_group_flex_stru_code => per_ri_config_main.g_global_pea_structure_name
1426 ,p_job_flex_stru_code => l_job_flex_stru_code
1427 ,p_cost_flex_stru_code => per_ri_config_main.g_global_cst_structure_name
1428 ,p_position_flex_stru_code => l_position_flex_stru_code
1429 ,p_security_group_name => l_security_group_name
1430 ,p_competence_flex_stru_code => per_ri_config_main.g_global_cmp_structure_name);
1431 else
1432 hr_utility.set_location('Entering into the other tab as well ', 89);
1433 p_bg_tab(l_bg_count).effective_date := g_config_effective_date;
1434 p_bg_tab(l_bg_count).language_code := hr_api.userenv_lang;
1435 p_bg_tab(l_bg_count).date_from := g_config_effective_date;
1436 p_bg_tab(l_bg_count).name := l_business_group_name;
1437 p_bg_tab(l_bg_count).type := null;
1438 p_bg_tab(l_bg_count).internal_external_flag := 'INT';
1439 p_bg_tab(l_bg_count).short_name := l_business_group_name;
1440 p_bg_tab(l_bg_count).emp_gen_method := 'A';
1441 p_bg_tab(l_bg_count).app_gen_method := 'A';
1442 p_bg_tab(l_bg_count).cwk_gen_method := 'A';
1443 p_bg_tab(l_bg_count).legislation_code := l_legislation_code;
1444 p_bg_tab(l_bg_count).currency_code := l_currency_code;
1445 p_bg_tab(l_bg_count).fiscal_year_start := null;
1446 p_bg_tab(l_bg_count).min_work_age := null;
1447 p_bg_tab(l_bg_count).max_work_age := null ;
1448 p_bg_tab(l_bg_count).location_code := null;
1449 p_bg_tab(l_bg_count).grade_flex_stru_code := l_grade_flex_stru_code;
1450 p_bg_tab(l_bg_count).group_flex_stru_code := per_ri_config_main.g_global_pea_structure_name;
1451 p_bg_tab(l_bg_count).job_flex_stru_code := l_job_flex_stru_code;
1452 p_bg_tab(l_bg_count).cost_flex_stru_code := per_ri_config_main.g_global_cst_structure_name;
1453 p_bg_tab(l_bg_count).position_flex_stru_code:= l_position_flex_stru_code;
1454 p_bg_tab(l_bg_count).security_group_name := l_security_group_name;
1455 p_bg_tab(l_bg_count).competence_flex_stru_code := per_ri_config_main.g_global_cmp_structure_name;
1456
1457 l_bg_count := l_bg_count +1 ;
1458
1459 -- also populate secuity profiles table
1460 p_sg_tab(l_sg_count).security_group_name := l_business_group_name;
1461 p_sg_tab(l_sg_count).business_group_name := l_business_group_name;
1462
1463 l_sg_count := l_sg_count +1 ;
1464
1465 -- determine post install steps for PER and PAY
1466 if per_ri_config_utilities.check_selected_product
1467 (p_configuration_code => p_configuration_code
1468 ,p_product_name => 'PER') then
1469 l_per_post_install := per_ri_config_utilities.legislation_support
1470 (p_legislation_code => l_legislation_code
1471 ,p_application_short_name => 'PER');
1472 if l_per_post_install or l_legislation_code = 'BF' then
1473 p_post_install_tab(l_post_install_count).legislation_code := l_legislation_code;
1474 p_post_install_tab(l_post_install_count).applicaton_short_name := 'PER';
1475
1476 l_post_install_count := l_post_install_count + 1 ;
1477 end if;
1478 end if;
1479
1480 if per_ri_config_utilities.check_selected_product
1481 (p_configuration_code => p_configuration_code
1482 ,p_product_name => 'PAY') then
1483 l_per_post_install := per_ri_config_utilities.legislation_support
1484 (p_legislation_code => l_legislation_code
1485 ,p_application_short_name => 'PAY');
1486 if l_per_post_install then
1487 p_post_install_tab(l_post_install_count).legislation_code := l_legislation_code;
1488 p_post_install_tab(l_post_install_count).applicaton_short_name := 'PAY';
1489
1490 l_post_install_count := l_post_install_count + 1 ;
1491 end if;
1492 end if;
1493 end if;
1494 END IF;
1495
1496 l_log_message := 'Created BUSINESS GROUP ' || l_business_group_name;
1497 per_ri_config_utilities.write_log(p_message => l_log_message);
1498 hr_utility.set_location(l_proc, 40);
1499
1500 p_country_tab_out(csr_config_business_groups%ROWCOUNT).territory_code := l_legislation_code;
1501
1502 p_int_bg_resp_tab(csr_config_business_groups%ROWCOUNT).security_profile_name := l_business_group_name;
1503
1504 end loop;
1505 close csr_config_business_groups;
1506 close csr_config_information;
1507
1508 hr_utility.set_location(' Leaving:'|| l_proc, 50);
1509
1510 EXCEPTION
1511 when others then
1512 l_error_message := 'Error in ' || l_proc;
1513 hr_utility.trace(l_error_message || '-' || sqlerrm);
1514 hr_utility.set_location(' Leaving:'|| l_proc, 500);
1515 hr_utility.raise_error;
1516
1517 END create_bg_batch_lines;
1518
1519
1520
1521 END per_ri_config_datapump_entity;