[Home] [Help]
PACKAGE BODY: APPS.HR_H2PI_BG_UPLOAD
Source
1 PACKAGE BODY hr_h2pi_bg_upload AS
2 /* $Header: hrh2pibg.pkb 120.0 2005/05/31 00:38:20 appldev noship $ */
3
4 g_package VARCHAR2(33) := ' hr_h2pi_bg_upload.';
5 MAPPING_ID_INVALID EXCEPTION;
6 MAPPING_ID_MISSING EXCEPTION;
7 PRAGMA EXCEPTION_INIT (MAPPING_ID_MISSING, -20010);
8
9
10 -- --------------------------------------------------------------------------------
11 -- Description: Local function to get the ids corresponding to values for the DFF
12 --
13 -- --------------------------------------------------------------------------------
14 --
15
16 FUNCTION get_id_from_value (p_org_information_id in number,
17 p_org_info_number in number) return varchar2 is
18
19 CURSOR csr_org_info IS
20 SELECT ogi.org_information_context context
21 ,ogi.org_information1 ogi1
22 ,ogi.org_information2 ogi2
23 ,ogi.org_information3 ogi3
24 ,ogi.org_information4 ogi4
25 ,ogi.org_information5 ogi5
26 ,ogi.org_information6 ogi6
27 ,ogi.org_information7 ogi7
28 ,ogi.org_information8 ogi8
29 ,ogi.org_information9 ogi9
30 ,ogi.org_information10 ogi10
31 ,ogi.org_information11 ogi11
32 ,ogi.org_information12 ogi12
33 ,ogi.org_information13 ogi13
34 ,ogi.org_information14 ogi14
35 ,ogi.org_information15 ogi15
36 ,ogi.org_information16 ogi16
37 ,ogi.org_information17 ogi17
38 ,ogi.org_information18 ogi18
39 ,ogi.org_information19 ogi19
40 ,ogi.org_information20 ogi20
41 FROM hr_h2pi_organization_info ogi
42 WHERE ogi.org_information_id = p_org_information_id;
43
44 l_seg_id VARCHAR2(100);
45 l_seg_value VARCHAR2(100);
46 l_seg_desc VARCHAR2(100);
47
48 l_return_status BOOLEAN;
49
50 TYPE t_org_info IS RECORD
51 (column_seq_num number
52 ,column_name varchar2(30)
53 );
54
55 TYPE tab_org_info IS TABLE OF t_org_info
56 INDEX BY BINARY_INTEGER;
57
58 CURSOR csr_flex_cols(p_context VARCHAR2) IS
59 SELECT column_seq_num,
60 application_column_name col_name
61 FROM fnd_descr_flex_column_usages
62 WHERE application_id = 800
63 AND descriptive_flex_context_code = p_context
64 ORDER BY column_seq_num;
65
66 idx NUMBER;
67 i NUMBER;
68 l_proc varchar2(72) := g_package || '.get_id_from_value' ;
69
70 BEGIN
71 hr_utility.set_location('Entering:' || l_proc,10);
72 FOR v_rec IN csr_org_info LOOP
73 hr_utility.trace('Context : ' || v_rec.context);
74 fnd_flex_descval.set_column_value('ORG_INFORMATION_CONTEXT', v_rec.context);
75 fnd_flex_descval.set_column_value('ORG_INFORMATION1', v_rec.ogi1 );
76 fnd_flex_descval.set_column_value('ORG_INFORMATION2', v_rec.ogi2 );
77 fnd_flex_descval.set_column_value('ORG_INFORMATION3', v_rec.ogi3 );
78 fnd_flex_descval.set_column_value('ORG_INFORMATION4', v_rec.ogi4 );
79 fnd_flex_descval.set_column_value('ORG_INFORMATION5', v_rec.ogi5 );
80 fnd_flex_descval.set_column_value('ORG_INFORMATION6', v_rec.ogi6 );
81 fnd_flex_descval.set_column_value('ORG_INFORMATION7', v_rec.ogi7 );
82 fnd_flex_descval.set_column_value('ORG_INFORMATION8', v_rec.ogi8 );
83 fnd_flex_descval.set_column_value('ORG_INFORMATION9', v_rec.ogi9 );
84 fnd_flex_descval.set_column_value('ORG_INFORMATION10', v_rec.ogi10);
85 fnd_flex_descval.set_column_value('ORG_INFORMATION11', v_rec.ogi11);
86 fnd_flex_descval.set_column_value('ORG_INFORMATION12', v_rec.ogi12);
87 fnd_flex_descval.set_column_value('ORG_INFORMATION13', v_rec.ogi13);
88 fnd_flex_descval.set_column_value('ORG_INFORMATION14', v_rec.ogi14);
89 fnd_flex_descval.set_column_value('ORG_INFORMATION15', v_rec.ogi15);
90 fnd_flex_descval.set_column_value('ORG_INFORMATION16', v_rec.ogi16);
91 fnd_flex_descval.set_column_value('ORG_INFORMATION17', v_rec.ogi17);
92 fnd_flex_descval.set_column_value('ORG_INFORMATION18', v_rec.ogi18);
93 fnd_flex_descval.set_column_value('ORG_INFORMATION19', v_rec.ogi19);
94 fnd_flex_descval.set_column_value('ORG_INFORMATION20', v_rec.ogi20);
95
96 hr_utility.set_location(l_proc,10);
97 l_return_status := fnd_flex_descval.VALIDATE_DESCCOLS
98 (
99 appl_short_name => 'PER',
100 desc_flex_name => 'Org Developer DF',
101 values_or_ids => 'V'
102 );
103 hr_utility.set_location(l_proc,30);
104
105 i:=1;
106
107 FOR v_org_info IN csr_flex_cols(v_rec.context) LOOP
108 IF v_org_info.col_name = 'ORG_INFORMATION'||TO_CHAR(p_org_info_number) THEN
109 idx := i;
110 hr_utility.trace('Column Name : ' || v_org_info.col_name);
111 END IF;
112 i:=i+1;
113 END LOOP;
114
115 select fnd_flex_descval.segment_id(idx+1),
116 fnd_flex_descval.segment_value(idx+1),
117 fnd_flex_descval.segment_description(idx+1)
118 into l_seg_id,
119 l_seg_value,
120 l_seg_desc
121 from dual;
122
123 hr_utility.trace(l_seg_id || ':' || l_seg_value);
124 hr_utility.trace(fnd_flex_descval.error_message());
125 return(l_seg_id);
126 END LOOP;
127 hr_utility.set_location('Leaving:' || l_proc,90);
128 END;
129
130 PROCEDURE upload_location (p_from_client_id NUMBER) IS
131
132 CURSOR csr_locations (p_bg_id NUMBER) IS
133 SELECT *
134 FROM hr_h2pi_locations
135 WHERE client_id = p_bg_id
136 AND (status IS NULL OR status <> 'C');
137
138 l_ud_loc hr_h2pi_locations%ROWTYPE;
139 l_location_id NUMBER(15);
140 l_ovn NUMBER(9);
141 l_encoded_message VARCHAR2(200);
142
143 l_proc VARCHAR2(72) := g_package||'upload_location';
144
145
146
147 BEGIN
148 hr_utility.set_location('Entering:'|| l_proc, 10);
149
150 FOR v_ud_loc in csr_locations(p_from_client_id) LOOP
151
152 SAVEPOINT location_start;
153
154 BEGIN
155 hr_utility.set_location(l_proc, 20);
156 l_location_id := hr_h2pi_map.get_to_id
157 (p_table_name => 'HR_LOCATIONS_ALL',
158 p_from_id => v_ud_loc.location_id);
159
160 IF l_location_id = -1 THEN
161 hr_utility.set_location(l_proc, 30);
162 hr_location_api.create_location
163 (p_validate => FALSE
164 ,p_effective_date => SYSDATE
165 ,p_location_code => v_ud_loc.location_code
166 ,p_description => v_ud_loc.description
167 ,p_address_line_1 => v_ud_loc.address_line_1
168 ,p_address_line_2 => v_ud_loc.address_line_2
169 ,p_address_line_3 => v_ud_loc.address_line_3
170 ,p_town_or_city => v_ud_loc.town_or_city
171 ,p_country => v_ud_loc.country
172 ,p_inactive_date => v_ud_loc.inactive_date
173 ,p_postal_code => v_ud_loc.postal_code
174 ,p_region_1 => v_ud_loc.region_1
175 ,p_region_2 => v_ud_loc.region_2
176 ,p_region_3 => v_ud_loc.region_3
177 ,p_style => v_ud_loc.style
178 ,p_telephone_number_1 => v_ud_loc.telephone_number_1
179 ,p_telephone_number_2 => v_ud_loc.telephone_number_2
180 ,p_telephone_number_3 => v_ud_loc.telephone_number_3
181 ,p_loc_information13 => v_ud_loc.loc_information13
182 ,p_loc_information14 => v_ud_loc.loc_information14
183 ,p_loc_information15 => v_ud_loc.loc_information15
184 ,p_loc_information16 => v_ud_loc.loc_information16
185 ,p_loc_information17 => v_ud_loc.loc_information17
186 ,p_loc_information18 => v_ud_loc.loc_information18
187 ,p_loc_information19 => v_ud_loc.loc_information19
188 ,p_loc_information20 => v_ud_loc.loc_information20
189 ,p_attribute_category => v_ud_loc.attribute_category
190 ,p_attribute1 => v_ud_loc.attribute1
191 ,p_attribute2 => v_ud_loc.attribute2
192 ,p_attribute3 => v_ud_loc.attribute3
193 ,p_attribute4 => v_ud_loc.attribute4
194 ,p_attribute5 => v_ud_loc.attribute5
195 ,p_attribute6 => v_ud_loc.attribute6
196 ,p_attribute7 => v_ud_loc.attribute7
197 ,p_attribute8 => v_ud_loc.attribute8
198 ,p_attribute9 => v_ud_loc.attribute9
199 ,p_attribute10 => v_ud_loc.attribute10
200 ,p_attribute11 => v_ud_loc.attribute11
201 ,p_attribute12 => v_ud_loc.attribute12
202 ,p_attribute13 => v_ud_loc.attribute13
203 ,p_attribute14 => v_ud_loc.attribute14
204 ,p_attribute15 => v_ud_loc.attribute15
205 ,p_attribute16 => v_ud_loc.attribute16
206 ,p_attribute17 => v_ud_loc.attribute17
207 ,p_attribute18 => v_ud_loc.attribute18
208 ,p_attribute19 => v_ud_loc.attribute19
209 ,p_attribute20 => v_ud_loc.attribute20
210 ,p_business_group_id => hr_h2pi_upload.g_to_business_group_id
211 ,p_location_id => l_location_id
212 ,p_object_version_number=> l_ovn);
213
214 hr_h2pi_map.create_id_mapping
215 (p_table_name => 'HR_LOCATIONS_ALL',
216 p_from_id => v_ud_loc.location_id,
217 p_to_id => l_location_id);
218 ELSE
219 BEGIN
220 hr_utility.set_location(l_proc, 40);
221 SELECT loc.object_version_number
222 INTO l_ovn
223 FROM hr_locations_all loc
224 WHERE loc.location_id = l_location_id;
225 EXCEPTION
226 WHEN NO_DATA_FOUND THEN
227 hr_utility.set_location(l_proc, 50);
228 RAISE MAPPING_ID_INVALID;
229 END;
230
231 hr_utility.set_location(l_proc, 60);
232 hr_location_api.update_location
233 (p_validate => FALSE
234 ,p_effective_date => SYSDATE
235 ,p_location_id => l_location_id
236 ,p_object_version_number=> l_ovn
237 ,p_location_code => v_ud_loc.location_code
238 ,p_description => v_ud_loc.description
239 ,p_address_line_1 => v_ud_loc.address_line_1
240 ,p_address_line_2 => v_ud_loc.address_line_2
241 ,p_address_line_3 => v_ud_loc.address_line_3
242 ,p_town_or_city => v_ud_loc.town_or_city
243 ,p_country => v_ud_loc.country
244 ,p_inactive_date => v_ud_loc.inactive_date
245 ,p_postal_code => v_ud_loc.postal_code
246 ,p_region_1 => v_ud_loc.region_1
247 ,p_region_2 => v_ud_loc.region_2
248 ,p_region_3 => v_ud_loc.region_3
249 ,p_style => v_ud_loc.style
250 ,p_telephone_number_1 => v_ud_loc.telephone_number_1
251 ,p_telephone_number_2 => v_ud_loc.telephone_number_2
252 ,p_telephone_number_3 => v_ud_loc.telephone_number_3
253 ,p_loc_information13 => v_ud_loc.loc_information13
254 ,p_loc_information14 => v_ud_loc.loc_information14
255 ,p_loc_information15 => v_ud_loc.loc_information15
256 ,p_loc_information16 => v_ud_loc.loc_information16
257 ,p_loc_information17 => v_ud_loc.loc_information17
258 ,p_loc_information18 => v_ud_loc.loc_information18
259 ,p_loc_information19 => v_ud_loc.loc_information19
260 ,p_loc_information20 => v_ud_loc.loc_information20
261 ,p_attribute_category => v_ud_loc.attribute_category
262 ,p_attribute1 => v_ud_loc.attribute1
263 ,p_attribute2 => v_ud_loc.attribute2
264 ,p_attribute3 => v_ud_loc.attribute3
265 ,p_attribute4 => v_ud_loc.attribute4
266 ,p_attribute5 => v_ud_loc.attribute5
267 ,p_attribute6 => v_ud_loc.attribute6
268 ,p_attribute7 => v_ud_loc.attribute7
269 ,p_attribute8 => v_ud_loc.attribute8
270 ,p_attribute9 => v_ud_loc.attribute9
271 ,p_attribute10 => v_ud_loc.attribute10
272 ,p_attribute11 => v_ud_loc.attribute11
273 ,p_attribute12 => v_ud_loc.attribute12
274 ,p_attribute13 => v_ud_loc.attribute13
275 ,p_attribute14 => v_ud_loc.attribute14
276 ,p_attribute15 => v_ud_loc.attribute15
277 ,p_attribute16 => v_ud_loc.attribute16
278 ,p_attribute17 => v_ud_loc.attribute17
279 ,p_attribute18 => v_ud_loc.attribute18
280 ,p_attribute19 => v_ud_loc.attribute19
281 ,p_attribute20 => v_ud_loc.attribute20
282 );
283
284 END IF;
285
286 hr_utility.set_location(l_proc, 70);
287 UPDATE hr_h2pi_locations
288 SET status = 'C'
289 WHERE location_id = v_ud_loc.location_id
290 AND client_id = p_from_client_id;
291
292 COMMIT;
293
294 EXCEPTION
295 WHEN MAPPING_ID_INVALID THEN
296 ROLLBACK;
297 hr_utility.set_location(l_proc, 80);
298 hr_h2pi_error.data_error
299 (p_from_id => l_location_id,
300 p_table_name => 'HR_H2PI_LOCATIONS',
301 p_message_level => 'FATAL',
302 p_message_name => 'HR_289240_MAPPING_ID_INVALID');
303 WHEN APP_EXCEPTIONS.APPLICATION_EXCEPTION THEN
304 ROLLBACK;
305 hr_utility.set_location(l_proc, 90);
306 l_encoded_message := fnd_message.get_encoded;
307 hr_h2pi_error.data_error
308 (p_from_id => v_ud_loc.location_id,
309 p_table_name => 'HR_H2PI_LOCATIONS',
310 p_message_level => 'FATAL',
311 p_message_text => l_encoded_message);
312 END;
313
314 END LOOP;
315
316 hr_utility.set_location('Leaving:'|| l_proc, 100);
317 COMMIT;
318
319 END;
320
321
322
323 PROCEDURE upload_hr_organization (p_from_client_id NUMBER) AS
324
325 CURSOR csr_orgs (p_bg_id NUMBER) IS
326 SELECT DISTINCT organization_id,
327 hr_h2pi_bg_upload.org_exists(p_from_client_id,organization_id, 1) hr_org,
328 hr_h2pi_bg_upload.org_exists(p_from_client_id,organization_id, 2) class,
329 hr_h2pi_bg_upload.org_exists(p_from_client_id,organization_id, 3) info
330 FROM hr_h2pi_hr_organizations
331 WHERE (status IS NULL OR status <> 'C')
332 AND client_id = p_bg_id
333 UNION
334 SELECT DISTINCT organization_id,
335 hr_h2pi_bg_upload.org_exists(p_from_client_id,organization_id, 1) hr_org,
336 hr_h2pi_bg_upload.org_exists(p_from_client_id,organization_id, 2) class,
337 hr_h2pi_bg_upload.org_exists(p_from_client_id,organization_id, 3) info
338 FROM hr_h2pi_organization_class
339 WHERE (status IS NULL OR status <> 'C')
340 AND client_id = p_bg_id
341 UNION
342 SELECT DISTINCT organization_id,
343 hr_h2pi_bg_upload.org_exists(p_from_client_id,organization_id, 1) hr_org,
344 hr_h2pi_bg_upload.org_exists(p_from_client_id,organization_id, 2) class,
345 hr_h2pi_bg_upload.org_exists(p_from_client_id,organization_id, 3) info
346 FROM hr_h2pi_organization_info
347 WHERE (status IS NULL OR status <> 'C')
348 AND client_id = p_bg_id
349 ORDER BY organization_id;
350
351 CURSOR csr_hr_org(p_org_id NUMBER) IS
352 SELECT DISTINCT *
353 FROM hr_h2pi_hr_organizations
354 WHERE organization_id = p_org_id
355 AND (status IS NULL OR status <> 'C')
356 AND client_id = p_from_client_id;
357
358 CURSOR csr_hr_org_class(p_org_id NUMBER) IS
359 SELECT DISTINCT *
360 FROM hr_h2pi_organization_class
361 WHERE organization_id = p_org_id
362 AND (status IS NULL OR status <> 'C')
363 AND client_id = p_from_client_id;
364
365 CURSOR csr_hr_org_info(p_org_id NUMBER) IS
366 SELECT DISTINCT *
367 FROM hr_h2pi_organization_info
368 WHERE organization_id = p_org_id
369 AND (status IS NULL OR status <> 'C')
370 AND client_id = p_from_client_id;
371
372 --
373 CURSOR csr_session_date is
374 SELECT effective_date
375 FROM fnd_sessions
376 WHERE session_id = userenv('sessionid');
377 --
378 v_ud_hr_org hr_h2pi_hr_organizations%ROWTYPE;
379 l_hr_org_id hr_all_organization_units.organization_id%TYPE := NULL;
380 l_hr_org_class_id hr_organization_information.org_information_id%TYPE := NULL;
381 l_hr_org_info_id hr_organization_information.org_information_id%TYPE := NULL;
382 l_location_id hr_all_organization_units.location_id%TYPE;
383 l_encoded_message VARCHAR2(200);
384 l_org_info2 VARCHAR2(150);
385 l_ovn NUMBER(9);
386 l_session_date DATE;
387
388 l_org_info1 hr_h2pi_organization_info.org_information1%TYPE;
389 l_org_info8 hr_h2pi_organization_info.org_information8%TYPE;
390 l_org_info9 hr_h2pi_organization_info.org_information9%TYPE;
391 l_org_info10 hr_h2pi_organization_info.org_information10%TYPE;
392 l_org_info12 hr_h2pi_organization_info.org_information12%TYPE;
393 l_org_info13 hr_h2pi_organization_info.org_information13%TYPE;
394
395 l_proc VARCHAR2(72) := g_package||'upload_hr_organization';
396
397 BEGIN
398 hr_utility.set_location('Entering:'|| l_proc, 10);
399
400 -- Insert fnd_sessions row
401 OPEN csr_session_date;
402 hr_utility.set_location(l_proc, 11);
403 FETCH csr_session_date into l_session_date;
404 hr_utility.set_location(l_proc, 12);
405 IF csr_session_date%notfound then
406 hr_utility.set_location(l_proc, 13);
407 insert into fnd_sessions (SESSION_ID, EFFECTIVE_DATE)
408 values(userenv('sessionid'), trunc(SYSDATE));
409 hr_utility.set_location(l_proc, 14);
410 hr_utility.set_location(l_proc || ': ' || userenv('sessionid'), 15);
411 END IF;
412 CLOSE csr_session_date;
413 --
414
415 FOR v_ud_org IN csr_orgs(p_from_client_id) LOOP
416
417 hr_utility.set_location(l_proc, 20);
418 SAVEPOINT hr_org_start;
419
420 BEGIN
421 IF v_ud_org.hr_org = 1 THEN
422
423 l_hr_org_id := NULL;
424 hr_utility.set_location(l_proc, 30);
425 OPEN csr_hr_org(v_ud_org.organization_id);
426 FETCH csr_hr_org INTO v_ud_hr_org;
427 CLOSE csr_hr_org;
428
429 hr_utility.set_location(l_proc, 40);
430 l_location_id := hr_h2pi_map.get_to_id
431 (p_table_name => 'HR_LOCATIONS_ALL',
432 p_from_id => v_ud_hr_org.location_id,
433 p_report_error => TRUE);
434
435 l_hr_org_id := hr_h2pi_map.get_to_id
436 (p_table_name => 'HR_ALL_ORGANIZATION_UNITS',
437 p_from_id => v_ud_hr_org.organization_id);
438
439 hr_utility.set_location(l_proc, 50);
440 IF l_hr_org_id = -1 THEN
441 hr_utility.set_location(l_proc, 60);
442 hr_organization_api.create_organization
443 (p_effective_date => SYSDATE
444 ,p_business_group_id => hr_h2pi_upload.g_to_business_group_id
445 ,p_date_from => v_ud_hr_org.date_from
446 ,p_date_to => v_ud_hr_org.date_to
447 ,p_name => v_ud_hr_org.name
448 ,p_location_id => l_location_id
449 ,p_internal_external_flag => v_ud_hr_org.internal_external_flag
450 ,p_attribute_category => v_ud_hr_org.attribute_category
451 ,p_attribute1 => v_ud_hr_org.attribute1
452 ,p_attribute2 => v_ud_hr_org.attribute2
453 ,p_attribute3 => v_ud_hr_org.attribute3
454 ,p_attribute4 => v_ud_hr_org.attribute4
455 ,p_attribute5 => v_ud_hr_org.attribute5
456 ,p_attribute6 => v_ud_hr_org.attribute6
457 ,p_attribute7 => v_ud_hr_org.attribute7
458 ,p_attribute8 => v_ud_hr_org.attribute8
459 ,p_attribute9 => v_ud_hr_org.attribute9
460 ,p_attribute10 => v_ud_hr_org.attribute10
461 ,p_attribute11 => v_ud_hr_org.attribute11
462 ,p_attribute12 => v_ud_hr_org.attribute12
463 ,p_attribute13 => v_ud_hr_org.attribute13
464 ,p_attribute14 => v_ud_hr_org.attribute14
465 ,p_attribute15 => v_ud_hr_org.attribute15
466 ,p_attribute16 => v_ud_hr_org.attribute16
467 ,p_attribute17 => v_ud_hr_org.attribute17
468 ,p_attribute18 => v_ud_hr_org.attribute18
469 ,p_attribute19 => v_ud_hr_org.attribute19
470 ,p_attribute20 => v_ud_hr_org.attribute20
471 ,p_organization_id => l_hr_org_id
472 ,p_object_version_number => l_ovn
473 );
474
475 hr_h2pi_map.create_id_mapping
476 (p_table_name => 'HR_ALL_ORGANIZATION_UNITS',
477 p_from_id => v_ud_hr_org.organization_id,
478 p_to_id => l_hr_org_id);
479 ELSE
480 hr_utility.set_location(l_proc, 70);
481 BEGIN
482 SELECT org.object_version_number
483 INTO l_ovn
484 FROM hr_all_organization_units org
485 WHERE org.organization_id = l_hr_org_id;
486 EXCEPTION
487 WHEN NO_DATA_FOUND THEN
488 hr_utility.set_location(l_proc, 75);
489 ROLLBACK;
490 hr_h2pi_error.data_error
491 (p_from_id => l_hr_org_class_id,
492 p_table_name => 'HR_H2PI_ORGANIZATION_CLASS',
493 p_message_level => 'FATAL',
494 p_message_name => 'HR_289240_MAPPING_ID_INVALID');
495 RAISE MAPPING_ID_INVALID;
496 END;
497
498 hr_utility.set_location(l_proc, 80);
499 hr_organization_api.update_organization
500 (p_effective_date => SYSDATE
501 ,p_organization_id => l_hr_org_id
502 ,p_date_from => v_ud_hr_org.date_from
503 ,p_date_to => v_ud_hr_org.date_to
504 ,p_location_id => l_location_id
505 ,p_attribute_category => v_ud_hr_org.attribute_category
506 ,p_attribute1 => v_ud_hr_org.attribute1
507 ,p_attribute2 => v_ud_hr_org.attribute2
508 ,p_attribute3 => v_ud_hr_org.attribute3
509 ,p_attribute4 => v_ud_hr_org.attribute4
510 ,p_attribute5 => v_ud_hr_org.attribute5
511 ,p_attribute6 => v_ud_hr_org.attribute6
512 ,p_attribute7 => v_ud_hr_org.attribute7
513 ,p_attribute8 => v_ud_hr_org.attribute8
514 ,p_attribute9 => v_ud_hr_org.attribute9
515 ,p_attribute10 => v_ud_hr_org.attribute10
516 ,p_attribute11 => v_ud_hr_org.attribute11
517 ,p_attribute12 => v_ud_hr_org.attribute12
518 ,p_attribute13 => v_ud_hr_org.attribute13
519 ,p_attribute14 => v_ud_hr_org.attribute14
520 ,p_attribute15 => v_ud_hr_org.attribute15
521 ,p_attribute16 => v_ud_hr_org.attribute16
522 ,p_attribute17 => v_ud_hr_org.attribute17
523 ,p_attribute18 => v_ud_hr_org.attribute18
524 ,p_attribute19 => v_ud_hr_org.attribute19
525 ,p_attribute20 => v_ud_hr_org.attribute20
526 ,p_object_version_number=> l_ovn
527 );
528
529 END IF;
530
531 hr_utility.set_location(l_proc, 90);
532 UPDATE hr_h2pi_hr_organizations
533 SET status = 'C'
534 WHERE organization_id = v_ud_hr_org.organization_id
535 AND client_id = p_from_client_id;
536
537 ELSE
538
539 hr_utility.set_location(l_proc, 94);
540 l_hr_org_id := hr_h2pi_map.get_to_id
541 (p_table_name => 'HR_ALL_ORGANIZATION_UNITS',
542 p_from_id => v_ud_org.organization_id,
543 p_report_error => TRUE);
544
545 END IF;
546
547 IF v_ud_org.class = 1 THEN
548 FOR v_ud_hr_org_class IN csr_hr_org_class(v_ud_org.organization_id)
549 LOOP
550
551 BEGIN
552 hr_utility.set_location(l_proc, 100);
553 l_hr_org_class_id := hr_h2pi_map.get_to_id
554 (p_table_name => 'HR_ORGANIZATION_INFORMATION',
555 p_from_id => v_ud_hr_org_class.org_information_id);
556
557 hr_utility.set_location(l_proc, 110);
558 IF l_hr_org_class_id = -1 THEN
559 hr_utility.set_location(l_proc, 120);
560 hr_organization_api.create_org_classification
561 (p_effective_date => SYSDATE
562 ,p_organization_id => l_hr_org_id
563 ,p_org_classif_code => v_ud_hr_org_class.org_information1
564 ,p_org_information_id => l_hr_org_class_id
565 ,p_object_version_number=> l_ovn
566 );
567
568 hr_h2pi_map.create_id_mapping
569 (p_table_name => 'HR_ORGANIZATION_INFORMATION',
570 p_from_id => v_ud_hr_org_class.org_information_id,
571 p_to_id => l_hr_org_class_id);
572 END IF;
573
574 hr_utility.set_location(l_proc, 130);
575 hr_utility.set_location(l_proc, l_hr_org_class_id);
576 BEGIN
577 SELECT org_information2,
578 object_version_number
579 INTO l_org_info2,
580 l_ovn
581 FROM hr_organization_information
582 WHERE org_information_id = l_hr_org_class_id;
583 EXCEPTION
584 WHEN NO_DATA_FOUND THEN
585 hr_utility.set_location(l_proc, 135);
586 ROLLBACK;
587 hr_h2pi_error.data_error
588 (p_from_id => l_hr_org_class_id,
589 p_table_name => 'HR_H2PI_ORGANIZATION_CLASS',
590 p_message_level => 'FATAL',
591 p_message_name => 'HR_289240_MAPPING_ID_INVALID');
592 RAISE MAPPING_ID_INVALID;
593 END;
594
595 hr_utility.set_location(l_proc, 140);
596 IF v_ud_hr_org_class.org_information2 = 'N' AND
597 l_org_info2 = 'Y' THEN
598 hr_utility.set_location(l_proc, 150);
599 hr_organization_api.disable_org_classification
600 (p_effective_date => SYSDATE
601 ,p_org_information_id => l_hr_org_class_id
602 ,p_org_info_type_code => 'CLASS'
603 ,p_object_version_number => l_ovn
604 );
605 ELSIF v_ud_hr_org_class.org_information2 = 'Y' AND
606 l_org_info2 = 'N' THEN
607 hr_utility.set_location(l_proc, 160);
608 hr_organization_api.enable_org_classification
609 (p_effective_date => SYSDATE
610 ,p_org_information_id => l_hr_org_class_id
611 ,p_org_info_type_code => 'CLASS'
612 ,p_object_version_number => l_ovn
613 );
614 END IF;
615
616 UPDATE hr_h2pi_organization_class
617 SET status = 'C'
618 WHERE org_information_id = v_ud_hr_org_class.org_information_id
619 AND client_id = p_from_client_id;
620 hr_utility.set_location(l_proc, 170);
621
622 EXCEPTION
623 WHEN APP_EXCEPTIONS.APPLICATION_EXCEPTION THEN
624 hr_utility.set_location(l_proc, 180);
625 l_encoded_message := fnd_message.get_encoded;
626 hr_h2pi_error.data_error
627 (p_from_id => v_ud_hr_org_class.org_information_id,
628 p_table_name => 'HR_H2PI_ORGANIZATION_CLASS',
629 p_message_level => 'FATAL',
630 p_message_text => l_encoded_message);
631 END;
632
633 END LOOP;
634
635 END IF;
636
637 IF v_ud_org.info = 1 THEN
638
639 FOR v_ud_hr_org_info in csr_hr_org_info(v_ud_org.organization_id)LOOP
640
641 BEGIN
642 hr_utility.set_location(l_proc, 190);
643 l_hr_org_info_id := hr_h2pi_map.get_to_id
644 (p_table_name => 'HR_ORGANIZATION_INFORMATION',
645 p_from_id => v_ud_hr_org_info.org_information_id);
646
647 hr_utility.set_location(l_proc, 200);
648
649 -- set values from uploaded org info table
650 l_org_info1 := v_ud_hr_org_info.org_information1;
651 l_org_info8 := v_ud_hr_org_info.org_information8;
652 l_org_info9 := v_ud_hr_org_info.org_information9;
653 l_org_info10 := v_ud_hr_org_info.org_information10;
654 l_org_info12 := v_ud_hr_org_info.org_information12;
655 l_org_info13 := v_ud_hr_org_info.org_information13;
656
657 hr_utility.set_location(l_proc, 210);
658
659 /* Added to take care for value set IDs in org additional info */
660 IF v_ud_hr_org_info.org_information_context = 'SQWL GN Transmitter Rules' THEN
661 -- for MMREF Contact Name
662 hr_utility.trace('Found : ' || v_ud_hr_org_info.org_information_context);
663 IF v_ud_hr_org_info.org_information12 IS NOT NULL THEN
664 hr_utility.set_location(l_proc, 220);
665 l_org_info12 := hr_h2pi_map.get_to_id
666 (p_table_name => 'PER_ALL_PEOPLE_F',
667 p_from_id => v_ud_hr_org_info.org_information12);
668 hr_utility.set_location(l_proc, 220);
669 IF l_org_info12 = -1 THEN
670 hr_utility.trace(v_ud_hr_org_info.org_information_context || ':' ||
671 'ORG_INFORMATION12'|| ':' ||
672 v_ud_hr_org_info.org_information12 || ':' ||
673 'is unsupported');
674
675 l_org_info12 := NULL;
676 END IF;
677 ELSE
678 l_org_info12 := v_ud_hr_org_info.org_information12;
679 END IF;
680 -- for Company Name
681 IF v_ud_hr_org_info.org_information13 IS NOT NULL THEN
682 hr_utility.set_location(l_proc, 260);
683 l_org_info13 := hr_h2pi_map.get_to_id
684 (p_table_name => 'HR_ALL_ORGANIZATION_UNITS',
685 p_from_id =>v_ud_hr_org_info.org_information13);
686 ELSE
687 l_org_info13 := v_ud_hr_org_info.org_information13;
688 END IF;
689 ElSIF v_ud_hr_org_info.org_information_context = 'State Tax Rules' THEN
690 -- for WC Carrier Name
691 hr_utility.trace('Found : ' || v_ud_hr_org_info.org_information_context);
692 IF v_ud_hr_org_info.org_information8 IS NOT NULL THEN
693 l_org_info8 := hr_h2pi_map.get_to_id
694 (p_table_name => 'HR_ALL_ORGANIZATION_UNITS',
695 p_from_id => v_ud_hr_org_info.org_information8);
696 ELSE
697 l_org_info8 := v_ud_hr_org_info.org_information8;
698 END IF;
699 ELSIF v_ud_hr_org_info.org_information_context = 'W2 Reporting Rules' THEN
700 -- for Company Name
701 hr_utility.trace('Found : ' || v_ud_hr_org_info.org_information_context);
702 IF v_ud_hr_org_info.org_information9 IS NOT NULL THEN
703 hr_utility.set_location(l_proc, 270);
704 l_org_info9 := hr_h2pi_map.get_to_id
705 (p_table_name => 'HR_ALL_ORGANIZATION_UNITS',
706 p_from_id =>v_ud_hr_org_info.org_information9);
707 ELSE
708 l_org_info9 := v_ud_hr_org_info.org_information9;
709 END IF;
710 -- for Contact Name
711 IF v_ud_hr_org_info.org_information10 IS NOT NULL THEN
712 l_org_info10 := hr_h2pi_map.get_to_id
713 (p_table_name => 'PER_ALL_PEOPLE_F',
714 p_from_id => v_ud_hr_org_info.org_information10);
715 IF l_org_info10 = -1 THEN
716 hr_utility.trace(v_ud_hr_org_info.org_information_context || ':' ||
717 'ORG_INFORMATION10' || ':' ||
718 v_ud_hr_org_info.org_information10 || ':' ||
719 'is unsupported');
720 l_org_info10 := NULL;
721 END IF;
722 ELSE
723 l_org_info10 := v_ud_hr_org_info.org_information10;
724 END IF;
725 ELSIF v_ud_hr_org_info.org_information_context = 'Work Schedule' THEN
726 -- for Schedule Table
727 hr_utility.trace('Found : ' || v_ud_hr_org_info.org_information_context);
728 IF v_ud_hr_org_info.org_information1 IS NOT NULL THEN
729 hr_utility.set_location(l_proc, 210);
730 l_org_info1 := hr_h2pi_map.get_to_id
731 (p_table_name => 'COMPANY_WORK_SCHEDULE',
732 p_from_id => v_ud_hr_org_info.org_information1);
733 IF l_org_info10 = -1 THEN
734 hr_utility.trace(v_ud_hr_org_info.org_information_context || ':' ||
735 'ORG_INFORMATION1' || ':' ||
736 v_ud_hr_org_info.org_information1 || ':' ||
737 'is unsupported');
738 l_org_info1 := NULL;
739 END IF;
740 ELSE
741 l_org_info1 := v_ud_hr_org_info.org_information1;
742 END IF;
743 ELSE
744 Null;
745 END IF;
746
747 IF l_hr_org_info_id = -1 THEN
748 hr_utility.set_location(l_proc, 210);
749
750 hr_organization_api.create_org_information
751 (p_effective_date => SYSDATE
752 ,p_organization_id => l_hr_org_id
753 ,p_org_info_type_code
754 => v_ud_hr_org_info.org_information_context
755 ,p_org_information1 => l_org_info1
756 ,p_org_information2 => v_ud_hr_org_info.org_information2
757 ,p_org_information3 => v_ud_hr_org_info.org_information3
758 ,p_org_information4 => v_ud_hr_org_info.org_information4
759 ,p_org_information5 => v_ud_hr_org_info.org_information5
760 ,p_org_information6 => v_ud_hr_org_info.org_information6
761 ,p_org_information7 => v_ud_hr_org_info.org_information7
762 ,p_org_information8 => l_org_info8
763 ,p_org_information9 => l_org_info9
764 ,p_org_information10 => l_org_info10
765 ,p_org_information11 => v_ud_hr_org_info.org_information11
766 ,p_org_information12 => l_org_info12
767 ,p_org_information13 => l_org_info13
768 ,p_org_information14 => v_ud_hr_org_info.org_information14
769 ,p_org_information15 => v_ud_hr_org_info.org_information15
770 ,p_org_information16 => v_ud_hr_org_info.org_information16
771 ,p_org_information17 => v_ud_hr_org_info.org_information17
772 ,p_org_information18 => v_ud_hr_org_info.org_information18
773 ,p_org_information19 => v_ud_hr_org_info.org_information19
774 ,p_org_information20 => v_ud_hr_org_info.org_information20
775 ,p_org_information_id => l_hr_org_info_id
776 ,p_object_version_number=> l_ovn
777 );
778
779 hr_h2pi_map.create_id_mapping
780 (p_table_name => 'HR_ORGANIZATION_INFORMATION',
781 p_from_id => v_ud_hr_org_info.org_information_id,
782 p_to_id => l_hr_org_info_id);
783 ELSE
784
785 hr_utility.set_location(l_proc, 220);
786 hr_utility.set_location(l_proc, l_hr_org_info_id);
787 BEGIN
788 SELECT org_information2,
789 object_version_number
790 INTO l_org_info2,
791 l_ovn
792 FROM hr_organization_information
793 WHERE org_information_id = l_hr_org_info_id;
794 EXCEPTION
795 WHEN NO_DATA_FOUND THEN
796 hr_utility.set_location(l_proc, 50);
797 ROLLBACK;
798 hr_h2pi_error.data_error
799 (p_from_id => l_hr_org_info_id,
800 p_table_name => 'HR_H2PI_ORGANIZATION_INFO',
801 p_message_level => 'FATAL',
802 p_message_name => 'HR_289240_MAPPING_ID_INVALID');
803 RAISE MAPPING_ID_INVALID;
804 END;
805
806 hr_utility.set_location(l_proc, 230);
807 hr_organization_api.update_org_information
808 (p_effective_date => SYSDATE
809 ,p_org_info_type_code
810 => v_ud_hr_org_info.org_information_context
811 ,p_org_information1 => l_org_info1
812 ,p_org_information2 => v_ud_hr_org_info.org_information2
813 ,p_org_information3 => v_ud_hr_org_info.org_information3
814 ,p_org_information4 => v_ud_hr_org_info.org_information4
815 ,p_org_information5 => v_ud_hr_org_info.org_information5
816 ,p_org_information6 => v_ud_hr_org_info.org_information6
817 ,p_org_information7 => v_ud_hr_org_info.org_information7
818 ,p_org_information8 => l_org_info8
819 ,p_org_information9 => l_org_info9
820 ,p_org_information10 => l_org_info10
821 ,p_org_information11 => v_ud_hr_org_info.org_information11
822 ,p_org_information12 => l_org_info12
823 ,p_org_information13 => l_org_info13
824 ,p_org_information14 => v_ud_hr_org_info.org_information14
825 ,p_org_information15 => v_ud_hr_org_info.org_information15
826 ,p_org_information16 => v_ud_hr_org_info.org_information16
827 ,p_org_information17 => v_ud_hr_org_info.org_information17
828 ,p_org_information18 => v_ud_hr_org_info.org_information18
829 ,p_org_information19 => v_ud_hr_org_info.org_information19
830 ,p_org_information20 => v_ud_hr_org_info.org_information20
831 ,p_org_information_id => l_hr_org_info_id
832 ,p_object_version_number=> l_ovn
833 );
834 END IF;
835
836
837 UPDATE hr_h2pi_organization_info
838 SET status = 'C'
839 WHERE org_information_id = v_ud_hr_org_info.org_information_id
840 AND client_id = p_from_client_id;
841 hr_utility.set_location(l_proc, 240);
842
843 EXCEPTION
844 WHEN APP_EXCEPTIONS.APPLICATION_EXCEPTION THEN
845 hr_utility.set_location(l_proc, 250);
846 l_encoded_message := fnd_message.get_encoded;
847 hr_h2pi_error.data_error
848 (p_from_id => v_ud_hr_org_info.org_information_id,
849 p_table_name => 'HR_H2PI_ORGANIZATION_INFO',
850 p_message_level => 'FATAL',
851 p_message_text => l_encoded_message);
852 END;
853
854 END LOOP;
855
856 END IF;
857
858 hr_utility.set_location(l_proc, 260);
859 COMMIT;
860
861 EXCEPTION
862 WHEN MAPPING_ID_INVALID THEN
863 hr_utility.set_location(l_proc, 270);
864 WHEN MAPPING_ID_MISSING THEN
865 hr_utility.set_location(l_proc, 280);
866 WHEN APP_EXCEPTIONS.APPLICATION_EXCEPTION THEN
867 ROLLBACK;
868 hr_utility.set_location(l_proc, 290);
869 l_encoded_message := fnd_message.get_encoded;
870 hr_h2pi_error.data_error
871 (p_from_id => v_ud_hr_org.organization_id,
872 p_table_name => 'HR_H2PI_HR_ORGANIZATIONS',
873 p_message_level => 'FATAL',
874 p_message_text => l_encoded_message);
875 END;
876
877 END LOOP;
878
879 hr_utility.set_location('Leaving:'|| l_proc, 10);
880 COMMIT;
881
882
883 END;
884
885
886 PROCEDURE upload_element_type (p_from_client_id NUMBER) AS
887
888 CURSOR csr_ele_names (p_bg_id NUMBER) IS
889 SELECT *
890 FROM hr_h2pi_element_names
891 WHERE client_id = p_bg_id
892 AND (status IS NULL OR status <> 'C');
893
894 l_ud_ele_name hr_h2pi_element_names%ROWTYPE;
895 l_ele_type_id pay_element_types_f.element_type_id%TYPE;
896 l_element_name pay_element_types_f.element_name%TYPE;
897 l_encoded_message VARCHAR2(200);
898 l_ovn NUMBER(9);
899
900 l_proc VARCHAR2(72) := g_package||'upload_element_name';
901
902 BEGIN
903 hr_utility.set_location('Entering:'|| l_proc, 10);
904 FOR v_ud_ele_name in csr_ele_names(p_from_client_id) LOOP
905
906 hr_utility.set_location(l_proc, 20);
907 SAVEPOINT hr_ele_name_start;
908
909 BEGIN
910 hr_utility.set_location(l_proc, 30);
911 l_ele_type_id := hr_h2pi_map.get_to_id
912 (p_table_name => 'PAY_ELEMENT_TYPES_F',
913 p_from_id => v_ud_ele_name.element_type_id,
914 p_report_error => TRUE);
915
916 hr_utility.set_location(l_proc, 50);
917 SELECT elt.element_name
918 INTO l_element_name
919 FROM pay_element_types_f elt
920 WHERE elt.element_type_id = l_ele_type_id
921 AND SYSDATE BETWEEN elt.effective_start_date
922 AND elt.effective_end_date;
923
924 hr_utility.set_location(l_proc, 60);
925 /*
926 pay_element_types_pkg.translate_row
927 (x_e_element_name => l_element_name
928 ,x_e_legislation_code => 'US'--hr_h2pi_upload.g_legislation_code
929 ,x_e_effective_start_date => SYSDATE
930 ,x_e_effective_end_date => SYSDATE
931 ,x_element_name => v_ud_ele_name.element_name
932 ,x_reporting_name => v_ud_ele_name.reporting_name
933 ,x_description => NULL
934 ,x_owner => 'OWNER'
935 ,x_business_group_id => hr_h2pi_upload.g_to_business_group_id);
936 */
937
938 UPDATE hr_h2pi_element_names
939 SET status = 'C'
940 WHERE element_type_id = v_ud_ele_name.element_type_id
941 AND client_id = p_from_client_id;
942
943 hr_utility.set_location(l_proc, 70);
944 COMMIT;
945
946 EXCEPTION
947 WHEN MAPPING_ID_MISSING THEN
948 hr_utility.set_location(l_proc, 80);
949 WHEN APP_EXCEPTIONS.APPLICATION_EXCEPTION THEN
950 ROLLBACK;
951 hr_utility.set_location(l_proc, 90);
952 l_encoded_message := fnd_message.get_encoded;
953 hr_h2pi_error.data_error
954 (p_from_id => v_ud_ele_name.element_type_id,
955 p_table_name => 'HR_H2PI_ELEMENT_NAME',
956 p_message_level => 'FATAL',
957 p_message_text => l_encoded_message);
958 END;
959
960 END LOOP;
961
962 hr_utility.set_location('Leaving:'|| l_proc, 100);
963 COMMIT;
964
965 END;
966
967 FUNCTION org_exists (p_from_client_id NUMBER,
968 p_org_id NUMBER,
969 p_table NUMBER) RETURN NUMBER IS
970
971 l_proc VARCHAR2(72) := g_package||'org_exists';
972
973 CURSOR csr_hr_org (p_id NUMBER) IS
974 SELECT 1
975 FROM hr_h2pi_hr_organizations
976 WHERE organization_id = p_id
977 AND (status IS NULL OR status <> 'C')
978 AND client_id = p_from_client_id;
979 CURSOR csr_class (p_id NUMBER) IS
980 SELECT 1
981 FROM hr_h2pi_organization_class
982 WHERE organization_id = p_id
983 AND (status IS NULL OR status <> 'C')
984 AND client_id = p_from_client_id;
985 CURSOR csr_info (p_id NUMBER) IS
986 SELECT 1
987 FROM hr_h2pi_organization_info
988 WHERE organization_id = p_id
989 AND (status IS NULL OR status <> 'C')
990 AND client_id = p_from_client_id;
991
992 l_dummy NUMBER;
993 l_retval NUMBER := 0;
994
995 BEGIN
996
997 IF p_table = 1 THEN
998 OPEN csr_hr_org(p_org_id);
999 FETCH csr_hr_org INTO l_dummy;
1000 IF csr_hr_org%FOUND THEN
1001 l_retval := 1;
1002 END IF;
1003 CLOSE csr_hr_org;
1004 ELSIF p_table = 2 THEN
1005 OPEN csr_class(p_org_id);
1006 FETCH csr_class INTO l_dummy;
1007 IF csr_class%FOUND THEN
1008 l_retval := 1;
1009 END IF;
1010 CLOSE csr_class;
1011 ELSIF p_table = 3 THEN
1012 OPEN csr_info(p_org_id);
1013 FETCH csr_info INTO l_dummy;
1014 IF csr_info%FOUND THEN
1015 l_retval := 1;
1016 END IF;
1017 CLOSE csr_info;
1018 END IF;
1019
1020 RETURN l_retval;
1021
1022 END;
1023
1024 END hr_h2pi_bg_upload;