[Home] [Help]
PACKAGE BODY: APPS.PER_AE_MIGRATE_PKG
Source
1 PACKAGE BODY per_ae_migrate_pkg AS
2 /* $Header: peaemigr.pkb 120.0 2006/04/09 22:35:00 abppradh noship $ */
3
4 ------------------------------------------------------------------------
5 ------------------------------------------------------------------------
6 -- Procedure update_scl_from_ddf
7 -- This procedure is used to migrate the data of Accomodation and Transportation provided
8 -- segments from ddf to scl
9 ------------------------------------------------------------------------
10 ------------------------------------------------------------------------
11
12 PROCEDURE update_scl_from_ddf
13 (errbuf OUT NOCOPY VARCHAR2
14 ,retcode OUT NOCOPY VARCHAR2
15 ,p_business_group_id IN NUMBER) IS
16
17 l_acco_provided VARCHAR2(30);
18 l_trans_provided VARCHAR2(30);
19 l_comment_id NUMBER;
20 l_soft_key_flex_id NUMBER;
21 l_effective_start_date DATE;
22 l_effective_end_date DATE;
23 l_concatenated_segments VARCHAR2(1000);
24 l_no_manager_warning BOOLEAN;
25 l_other_manager_warning BOOLEAN;
26 l_business_group_id NUMBER;
27 l_person_id NUMBER;
28 l_prev_person_id NUMBER;
29 l_assignment_id NUMBER;
30 l_object_version_number NUMBER;
31 l_effective_date date;
32 l_segment1 VARCHAR2(30);
33 l_segment2 VARCHAR2(30);
34 l_segment3 VARCHAR2(30);
35 l_segment4 VARCHAR2(30);
36 l_segment5 VARCHAR2(30);
37 l_title per_all_assignments_f.title%TYPE;
38 l_same_person NUMBER;
39 i NUMBER;
40 j NUMBER;
41 k NUMBER;
42 l_ov_update NUMBER;
43 l_datetrack_mode VARCHAR2(30);
44 l_eff_date DATE;
45 l_default_employer VARCHAR2(30);
46 l_migration_processed VARCHAR2(30);
47
48
49 TYPE rec_person IS RECORD
50 (person_id NUMBER
51 ,per_information14 VARCHAR2(240)
52 ,per_information15 VARCHAR2(240)
53 ,effective_start_date DATE
54 ,effective_end_date DATE);
55
56 TYPE t_type_rec_person IS TABLE OF rec_person INDEX BY BINARY_INTEGER;
57
58 tab_rec_person t_type_rec_person;
59
60 /*Cursor for checking if default employer is set*/
61 CURSOR csr_get_bg_det IS
62 SELECT org_information2 def_emp
63 ,NVL(org_information3,'N') mig_indicator
64 FROM hr_organization_information hoi
65 WHERE hoi.organization_id = p_business_group_id
66 AND hoi.org_information_context = 'AE_BG_DETAILS';
67 rec_get_bg_det csr_get_bg_det%ROWTYPE;
68
69
70 /*Cursor for fetching business groups in AE LEGISLATION */
71 CURSOR csr_get_business_group_id IS
72 SELECT business_group_id
73 FROM per_business_groups
74 WHERE legislation_code = 'AE';
75 rec_get_business_group_id csr_get_business_group_id%ROWTYPE;
76
77 /* Cursor for fetching employee details*/
78 CURSOR csr_get_person IS
79 SELECT person_id
80 ,per_information14 acco_provided
81 ,per_information15 trans_provided
82 ,effective_start_date
83 ,effective_end_date
84 FROM per_all_people_f ppf
85 ,per_person_types ppt
86 WHERE ppf.business_group_id = l_business_group_id
87 AND ppt.person_type_id = ppf.person_type_id
88 AND ppt.business_group_id = ppf.business_group_id
89 AND ppt.system_person_type LIKE 'EMP%'
90 ORDER BY person_id, effective_start_date, effective_end_date;
91 rec_get_person csr_get_person%ROWTYPE;
92
93 /*Cursor for fetching the data for assignments of each employee selected by the above cursor.
94 This cursor will be used for selecting the asignments for updating the scl segments. */
95 CURSOR csr_get_assignment_det (p_start_date DATE, p_end_date DATE, p_person_id NUMBER) IS
96 SELECT assignment_id
97 ,paa.object_version_number
98 ,paa.title
99 ,hsc.segment1
100 ,hsc.segment2
101 ,hsc.segment3
102 ,hsc.segment4
103 ,hsc.segment5
104 ,paa.effective_start_date
105 ,paa.effective_end_date
106 ,0 indicator
107 FROM per_all_assignments_f paa
108 ,hr_soft_coding_keyflex hsc
109 WHERE person_id = p_person_id
110 AND paa.primary_flag = 'Y'
111 AND hsc.soft_coding_keyflex_id(+) = paa.soft_coding_keyflex_id
112 AND ((paa.effective_start_date BETWEEN p_start_date AND p_end_date ))
113 UNION
114 SELECT assignment_id
115 ,paa.object_version_number
116 ,paa.title
117 ,hsc.segment1
118 ,hsc.segment2
119 ,hsc.segment3
120 ,hsc.segment4
121 ,hsc.segment5
122 ,paa.effective_start_date
123 ,paa.effective_end_date
124 ,1 indicator
125 FROM per_all_assignments_f paa
126 ,hr_soft_coding_keyflex hsc
127 WHERE person_id = p_person_id
128 AND paa.primary_flag = 'Y'
129 AND hsc.soft_coding_keyflex_id(+) = paa.soft_coding_keyflex_id
130 AND ((paa.effective_start_date < p_start_date AND paa.effective_end_date >= p_end_date))
131 ORDER BY effective_start_date;
132 rec_get_assignment_det csr_get_assignment_det%ROWTYPE;
133 BEGIN
134
135 l_default_employer := NULL;
136 l_migration_processed := NULL;
137
138 OPEN csr_get_bg_det;
139 FETCH csr_get_bg_det INTO rec_get_bg_det;
140 l_default_employer := rec_get_bg_det.def_emp;
141 l_migration_processed := rec_get_bg_det.mig_indicator;
142 CLOSE csr_get_bg_det;
143
144 IF l_default_employer IS NULL THEN
145 -- fnd_file.put_line(fnd_file.log, 'Default Employer is not defined at the business group level');
146 hr_utility.set_message(800, 'HR_377438_AE_DEF_EMP');
147 hr_utility.raise_error;
148 END IF;
149
150 IF l_migration_processed = 'Y' THEN
151 --fnd_file.put_line(fnd_file.log, 'Migration of Accomodation Provided and Transportation Provided segments from Person DDF to Assignment SCL have already been completed');
152 hr_utility.set_message(800, 'HR_377439_AE_MIG_RUN');
153 hr_utility.raise_error;
154 ELSE
155 --OPEN csr_get_business_group_id;
156 --LOOP
157 --FETCH csr_get_business_group_id INTO rec_get_business_group_id;
158 --EXIT WHEN csr_get_business_group_id%NOTFOUND;
159 l_business_group_id := p_business_group_id ; --rec_get_business_group_id.business_group_id;
160 l_prev_person_id := 0;
161 l_same_person := 0;
162 i := 0;
163 OPEN csr_get_person;
164 LOOP
165 FETCH csr_get_person INTO rec_get_person;
166 EXIT WHEN csr_get_person%NOTFOUND;
167 hr_utility.trace('Person ID : '||rec_get_person.person_id);
168
169 /*Populate a table type record with the values and effective dates*/
170 IF l_prev_person_id = rec_get_person.person_id THEN
171 l_same_person := 1;
172 IF (NVL(rec_get_person.acco_provided,'~') = NVL(tab_rec_person(i).per_information14,'~'))
173 AND (NVL(rec_get_person.trans_provided,'~') = NVL(tab_rec_person(i).per_information15,'~')) THEN
174 tab_rec_person(i).effective_end_date := rec_get_person.effective_end_date;
175 ELSE
176 i := i + 1;
177 tab_rec_person(i).person_id := rec_get_person.person_id;
178 tab_rec_person(i).per_information14 := rec_get_person.acco_provided;
179 tab_rec_person(i).per_information15 := rec_get_person.trans_provided;
180 tab_rec_person(i).effective_start_date := rec_get_person.effective_start_date;
181 tab_rec_person(i).effective_end_date := rec_get_person.effective_end_date;
182 END IF;
183 ELSE
184 /*Code for updating assignments */
185 /*All assignments are updated with the details of the current person before the next person is fetched*/
186 ------------------------------------------
187 IF tab_rec_person.COUNT > 0 THEN
188 j := tab_rec_person.COUNT;
189 k := 1;
190 WHILE k <= j LOOP
191 l_ov_update := 0;
192 /*Fetch assignments for the period in which person ddf has changed*/
193 OPEN csr_get_assignment_det(tab_rec_person(k-1).effective_start_date
194 ,tab_rec_person(k-1).effective_end_date
195 ,tab_rec_person(k-1).person_id);
196 LOOP
197 FETCH csr_get_assignment_det INTO rec_get_assignment_det;
198 EXIT WHEN csr_get_assignment_det%NOTFOUND;
199 l_assignment_id := rec_get_assignment_det.assignment_id;
200 --IF l_ov_update = 0 THEN
201 --l_object_version_number := rec_get_assignment_det.object_version_number;
202 --END IF;
203 l_title := rec_get_assignment_det.title;
204 l_segment1 := NVL(rec_get_assignment_det.segment1,l_default_employer);
205 l_segment2 := rec_get_assignment_det.segment2;
206 l_segment3 := rec_get_assignment_det.segment3;
207 l_segment4 := rec_get_assignment_det.segment4;
208 l_segment5 := rec_get_assignment_det.segment5;
209 /*If the start date matches, update the asignment in 'CORRECTION' mode*/
210 IF rec_get_assignment_det.effective_start_date = tab_rec_person(k-1).effective_start_date THEN
211 l_datetrack_mode := 'CORRECTION';
212 l_eff_date := tab_rec_person(k-1).effective_start_date;
213 l_object_version_number := rec_get_assignment_det.object_version_number;
214 ELSE
215 /*If start date do not match, check if any assignment has already been updated for the person within the same period
216 If there has been an UPDATe, then the next record should be updated in 'COREECTION mode*/
217 IF l_ov_update = 1 THEN
218 l_datetrack_mode := 'CORRECTION';
219 l_eff_date := rec_get_assignment_det.effective_start_date;
220 l_object_version_number := rec_get_assignment_det.object_version_number;
221 ELSE
222 /* If there exists future rows in assignment that are date tracked, UPDATE_CHANGE_INSERT mode should be used*/
223 IF (rec_get_assignment_det.effective_end_date < TO_DATE('31-12-4712','DD-MM-YYYY')
224 OR rec_get_assignment_det.effective_start_date > tab_rec_person(k-1).effective_start_date) THEN
225 l_datetrack_mode := 'UPDATE_CHANGE_INSERT';
226 l_eff_date := tab_rec_person(k-1).effective_start_date;
227 ELSE
228 l_datetrack_mode := 'UPDATE';
229 l_eff_date := tab_rec_person(k-1).effective_start_date;
230 END IF;
231 END IF;
232 END IF;
233 l_comment_id := NULL;
234 l_soft_key_flex_id := NULL;
235 l_effective_start_date := NULL;
236 l_effective_end_date := NULL;
237 l_concatenated_segments := NULL;
238 l_no_manager_warning := NULL;
239 l_other_manager_warning := NULL;
240
241 hr_assignment_api.update_emp_asg
242 (p_validate => FALSE
243 ,p_effective_date => l_eff_date --tab_rec_person(k-1).effective_start_date
244 ,p_datetrack_update_mode => l_datetrack_mode
245 ,p_assignment_id => l_assignment_id
246 ,p_object_version_number => l_object_version_number
247 ,p_title => 'AE_STATUTORY_INFO' --l_title
248 ,p_segment1 => l_segment1
249 ,p_segment2 => l_segment2
250 ,p_segment3 => l_segment3
251 ,p_segment4 => l_segment4
252 ,p_segment5 => l_segment5
253 ,p_segment7 => tab_rec_person(k-1).per_information14 --l_acco_provided
254 ,p_segment8 => tab_rec_person(k-1).per_information15 --l_trans_provided
258 ,p_effective_end_date => l_effective_end_date
255 ,p_comment_id => l_comment_id
256 ,p_soft_coding_keyflex_id => l_soft_key_flex_id
257 ,p_effective_start_date => l_effective_start_date
259 ,p_concatenated_segments => l_concatenated_segments
260 ,p_no_managers_warning => l_no_manager_warning
261 ,p_other_manager_warning => l_other_manager_warning);
262
263
264 IF rec_get_assignment_det.indicator = 0 THEN
265 /*If a record was updated with UPDATE_CHANGE_INSERT mode, the next
266 assignments within the same person ddf periods should be updated in CORRECTION mode*/
267 IF l_datetrack_mode = 'UPDATE_CHANGE_INSERT' THEN
268 l_comment_id := NULL;
269 l_soft_key_flex_id := NULL;
270 l_effective_start_date := NULL;
271 l_effective_end_date := NULL;
272 l_concatenated_segments := NULL;
273 l_no_manager_warning := NULL;
274 l_other_manager_warning := NULL;
275 l_object_version_number := rec_get_assignment_det.object_version_number;
276
277 hr_assignment_api.update_emp_asg
278 (p_validate => FALSE
279 ,p_effective_date => rec_get_assignment_det.effective_start_date
280 ,p_datetrack_update_mode => 'CORRECTION'
281 ,p_assignment_id => l_assignment_id
282 ,p_object_version_number => l_object_version_number
283 ,p_title => 'AE_STATUTORY_INFO' --l_title
284 ,p_segment1 => l_segment1
285 ,p_segment2 => l_segment2
286 ,p_segment3 => l_segment3
287 ,p_segment4 => l_segment4
288 ,p_segment5 => l_segment5
289 ,p_segment7 => tab_rec_person(k-1).per_information14 --l_acco_provided
290 ,p_segment8 => tab_rec_person(k-1).per_information15 --l_trans_provided
291 ,p_comment_id => l_comment_id
292 ,p_soft_coding_keyflex_id => l_soft_key_flex_id
293 ,p_effective_start_date => l_effective_start_date
294 ,p_effective_end_date => l_effective_end_date
295 ,p_concatenated_segments => l_concatenated_segments
296 ,p_no_managers_warning => l_no_manager_warning
297 ,p_other_manager_warning => l_other_manager_warning);
298
299 END IF;
300
301 END IF;
302
303 l_ov_update := 1;
304
305 END LOOP;
306 CLOSE csr_get_assignment_det;
307
308 k := k + 1;
309 END LOOP;
310 END IF;
311 ------------------------------------------
312
313 /*Once the assignments of the previous person have been updated continue with the next person*/
314 l_same_person := 0;
315 l_prev_person_id := rec_get_person.person_id;
316 i := 0;
317 tab_rec_person.DELETE;
318 tab_rec_person(i).person_id := rec_get_person.person_id;
319 tab_rec_person(i).per_information14 := rec_get_person.acco_provided;
320 tab_rec_person(i).per_information15 := rec_get_person.trans_provided;
321 tab_rec_person(i).effective_start_date := rec_get_person.effective_start_date;
322 tab_rec_person(i).effective_end_date := rec_get_person.effective_end_date;
323 END IF;
324
325 END LOOP;
326 CLOSE csr_get_person;
327
328 /*Code for updating assignments */
329 ------------------------------------------
330 IF tab_rec_person.COUNT > 0 THEN
331 j := tab_rec_person.COUNT;
332 k := 1;
333 WHILE k <= j LOOP
334 l_ov_update := 0;
335
336 OPEN csr_get_assignment_det(tab_rec_person(k-1).effective_start_date
337 ,tab_rec_person(k-1).effective_end_date
338 ,tab_rec_person(k-1).person_id);
339 LOOP
340 FETCH csr_get_assignment_det INTO rec_get_assignment_det;
341 EXIT WHEN csr_get_assignment_det%NOTFOUND;
342 l_assignment_id := rec_get_assignment_det.assignment_id;
343 --IF l_ov_update = 0 THEN
344 --l_object_version_number := rec_get_assignment_det.object_version_number;
345 --END IF;
346 l_title := rec_get_assignment_det.title;
347 l_segment1 := NVL(rec_get_assignment_det.segment1,l_default_employer);
348 l_segment2 := rec_get_assignment_det.segment2;
349 l_segment3 := rec_get_assignment_det.segment3;
350 l_segment4 := rec_get_assignment_det.segment4;
351 l_segment5 := rec_get_assignment_det.segment5;
352 IF rec_get_assignment_det.effective_start_date = tab_rec_person(k-1).effective_start_date THEN
353 l_datetrack_mode := 'CORRECTION';
354 l_eff_date := tab_rec_person(k-1).effective_start_date;
355 l_object_version_number := rec_get_assignment_det.object_version_number;
356 ELSE
357 IF l_ov_update = 1 THEN
358 l_datetrack_mode := 'CORRECTION';
359 l_eff_date := rec_get_assignment_det.effective_start_date;
360 l_object_version_number := rec_get_assignment_det.object_version_number;
361 ELSE
362 IF (rec_get_assignment_det.effective_end_date < TO_DATE('31-12-4712','DD-MM-YYYY')
363 OR rec_get_assignment_det.effective_start_date > tab_rec_person(k-1).effective_start_date) THEN
364 l_datetrack_mode := 'UPDATE_CHANGE_INSERT';
365 l_eff_date := tab_rec_person(k-1).effective_start_date;
369 END IF;
366 ELSE
367 l_datetrack_mode := 'UPDATE';
368 l_eff_date := tab_rec_person(k-1).effective_start_date;
370 END IF;
371 END IF;
372 l_comment_id := NULL;
373 l_soft_key_flex_id := NULL;
374 l_effective_start_date := NULL;
375 l_effective_end_date := NULL;
376 l_concatenated_segments := NULL;
377 l_no_manager_warning := NULL;
378 l_other_manager_warning := NULL;
379
380 hr_assignment_api.update_emp_asg
381 (p_validate => FALSE
382 ,p_effective_date => l_eff_date --tab_rec_person(k-1).effective_start_date
383 ,p_datetrack_update_mode => l_datetrack_mode
384 ,p_assignment_id => l_assignment_id
385 ,p_object_version_number => l_object_version_number
386 ,p_title => 'AE_STATUTORY_INFO' --l_title
387 ,p_segment1 => l_segment1
388 ,p_segment2 => l_segment2
389 ,p_segment3 => l_segment3
390 ,p_segment4 => l_segment4
391 ,p_segment5 => l_segment5
392 ,p_segment7 => tab_rec_person(k-1).per_information14 --l_acco_provided
393 ,p_segment8 => tab_rec_person(k-1).per_information15 --l_trans_provided
394 ,p_comment_id => l_comment_id
395 ,p_soft_coding_keyflex_id => l_soft_key_flex_id
396 ,p_effective_start_date => l_effective_start_date
397 ,p_effective_end_date => l_effective_end_date
398 ,p_concatenated_segments => l_concatenated_segments
399 ,p_no_managers_warning => l_no_manager_warning
400 ,p_other_manager_warning => l_other_manager_warning);
401
402
403 IF rec_get_assignment_det.indicator = 0 THEN
404
405 IF l_datetrack_mode = 'UPDATE_CHANGE_INSERT' THEN
406 l_comment_id := NULL;
407 l_soft_key_flex_id := NULL;
408 l_effective_start_date := NULL;
409 l_effective_end_date := NULL;
410 l_concatenated_segments := NULL;
411 l_no_manager_warning := NULL;
412 l_other_manager_warning := NULL;
413 l_object_version_number := rec_get_assignment_det.object_version_number;
414
415 hr_assignment_api.update_emp_asg
416 (p_validate => FALSE
417 ,p_effective_date => rec_get_assignment_det.effective_start_date
418 ,p_datetrack_update_mode => 'CORRECTION'
419 ,p_assignment_id => l_assignment_id
420 ,p_object_version_number => l_object_version_number
421 ,p_title => 'AE_STATUTORY_INFO' --l_title
422 ,p_segment1 => l_segment1
423 ,p_segment2 => l_segment2
424 ,p_segment3 => l_segment3
425 ,p_segment4 => l_segment4
426 ,p_segment5 => l_segment5
427 ,p_segment7 => tab_rec_person(k-1).per_information14 --l_acco_provided
428 ,p_segment8 => tab_rec_person(k-1).per_information15 --l_trans_provided
429 ,p_comment_id => l_comment_id
430 ,p_soft_coding_keyflex_id => l_soft_key_flex_id
431 ,p_effective_start_date => l_effective_start_date
432 ,p_effective_end_date => l_effective_end_date
433 ,p_concatenated_segments => l_concatenated_segments
434 ,p_no_managers_warning => l_no_manager_warning
435 ,p_other_manager_warning => l_other_manager_warning);
436
437 END IF;
438
439 END IF;
440 l_ov_update := 1;
441 END LOOP;
442 CLOSE csr_get_assignment_det;
443
444 k := k + 1;
445 END LOOP;
446
447 END IF;
448
449 --END LOOP;
450 --CLOSE csr_get_business_group_id;
451 /*Update the org_information to indicate the migration is complete*/
452 UPDATE hr_organization_information
453 SET org_information3 = 'Y'
454 WHERE organization_id = p_business_group_id
455 AND org_information_context = 'AE_BG_DETAILS';
456 END IF;
457
458 END update_scl_from_ddf;
459
460 ------------------------------------------------------------------------
461 ------------------------------------------------------------------------
462
463 END per_ae_migrate_pkg;