[Home] [Help]
PACKAGE BODY: APPS.IRC_GLOBAL_REMAP_PKG
Source
1 package body irc_global_remap_pkg as
2 /* $Header: ircremap.pkb 120.0 2005/07/26 15:00:29 mbocutt noship $ */
3
4 procedure remap_employee (p_person_id in number default null,
5 p_effective_date in date) is
6 --
7 l_add_ovn per_addresses.object_version_number%type;
8 l_phn_ovn per_phones.object_version_number%type;
9 l_prev_emp_ovn per_previous_employers.object_version_number%type;
10 l_qua_ovn per_qualifications.object_version_number%type;
11 l_esa_ovn per_establishment_attendances.object_version_number%type;
12 l_proc varchar2(72) := 'IRC_GLOBAL_REMAP_PKG.remap_employee';
13 --
14 cursor csr_address is
15 select adr.address_id,
16 adr.object_version_number,
17 paf.business_group_id as bg_id
18 from per_addresses adr, per_all_people_f paf
19 where paf.party_id = adr.party_id
20 and paf.person_id = p_person_id
21 and adr.person_id is null
22 and p_effective_date between paf.effective_start_date and paf.effective_end_date;
23
24 cursor csr_phones is
25 select phn.phone_id,
26 phn.object_version_number
27 from per_phones phn, per_all_people_f paf
28 where phn.party_id=paf.party_id
29 and paf.person_id = p_person_id
30 and phn.parent_id is null
31 and p_effective_date between paf.effective_start_date and paf.effective_end_date;
32
33 cursor csr_prev_emp is
34 select pem.previous_employer_id,
35 pem.object_version_number,
36 paf.business_group_id as bg_id
37 from per_previous_employers pem, per_all_people_f paf
38 where pem.party_id=paf.party_id
39 and paf.person_id = p_person_id
40 and pem.person_id is null
41 and p_effective_date between paf.effective_start_date and paf.effective_end_date;
42
43 cursor csr_qual is
44 select qua.qualification_id,
45 qua.object_version_number,
46 paf.business_group_id as bg_id
47 from per_qualifications qua, per_all_people_f paf
48 where qua.party_id=paf.party_id
49 and paf.person_id = p_person_id
50 and qua.person_id is null
51 and p_effective_date between paf.effective_start_date and paf.effective_end_date;
52
53 cursor csr_estab_attend is
54 select pea.attendance_id,
55 pea.object_version_number,
56 paf.business_group_id as bg_id
57 from per_establishment_attendances pea, per_all_people_f paf
58 where pea.party_id=paf.party_id
59 and paf.person_id = p_person_id
60 and pea.person_id is null
61 and p_effective_date between paf.effective_start_date and paf.effective_end_date;
62
63 cursor csr_all_address is
64 select adr.address_id,
65 adr.object_version_number,
66 paf.business_group_id as bg_id,
67 paf.person_id as paf_person_id
68 from per_addresses adr, per_all_people_f paf
69 where paf.party_id = adr.party_id
70 and paf.current_employee_flag = 'Y'
71 and adr.person_id is null
72 and p_effective_date between paf.effective_start_date and paf.effective_end_date
73 and not exists (select 1 from per_all_people_f per2
74 where paf.party_id=per2.party_id
75 and paf.person_id<>per2.person_id
76 and per2.current_employee_flag='Y'
77 and p_effective_date between per2.effective_start_date
78 and per2.effective_end_date
79 and paf.creation_date > per2.creation_date);
80
81 cursor csr_all_phones is
82 select phn.phone_id,
83 phn.object_version_number,
84 paf.person_id as paf_person_id
85 from per_phones phn, per_all_people_f paf
86 where phn.party_id=paf.party_id
87 and paf.current_employee_flag = 'Y'
88 and phn.parent_id is null
89 and p_effective_date between paf.effective_start_date and paf.effective_end_date
90 and not exists (select 1 from per_all_people_f per2
91 where paf.party_id=per2.party_id
92 and paf.person_id<>per2.person_id
93 and per2.current_employee_flag='Y'
94 and p_effective_date between per2.effective_start_date
95 and per2.effective_end_date
96 and paf.creation_date > per2.creation_date);
97
98 cursor csr_all_prev_emp is
99 select pem.previous_employer_id,
100 pem.object_version_number,
101 paf.business_group_id as bg_id,
102 paf.person_id as paf_person_id
103 from per_previous_employers pem, per_all_people_f paf
104 where pem.party_id=paf.party_id
105 and paf.current_employee_flag = 'Y'
106 and pem.person_id is null
107 and p_effective_date between paf.effective_start_date and paf.effective_end_date
108 and not exists (select 1 from per_all_people_f per2
109 where paf.party_id=per2.party_id
110 and paf.person_id<>per2.person_id
111 and per2.current_employee_flag='Y'
112 and p_effective_date between per2.effective_start_date
113 and per2.effective_end_date
114 and paf.creation_date > per2.creation_date);
115
116 cursor csr_all_qual is
117 select qua.qualification_id,
118 qua.object_version_number,
119 paf.business_group_id as bg_id,
120 paf.person_id as paf_person_id
121 from per_qualifications qua, per_all_people_f paf
122 where qua.party_id=paf.party_id
123 and paf.current_employee_flag = 'Y'
124 and qua.person_id is null
125 and qua.attendance_id is null
126 and p_effective_date between paf.effective_start_date and paf.effective_end_date
127 and not exists (select 1 from per_all_people_f per2
128 where paf.party_id=per2.party_id
129 and paf.person_id<>per2.person_id
130 and per2.current_employee_flag='Y'
131 and p_effective_date between per2.effective_start_date
132 and per2.effective_end_date
133 and paf.creation_date > per2.creation_date);
134
135 cursor csr_all_estab_attend is
136 select pea.attendance_id,
137 pea.object_version_number,
138 paf.business_group_id as bg_id,
139 paf.person_id as paf_person_id
140 from per_establishment_attendances pea, per_all_people_f paf
141 where pea.party_id=paf.party_id
142 and paf.current_employee_flag = 'Y'
143 and pea.person_id is null
144 and p_effective_date between paf.effective_start_date and paf.effective_end_date
145 and not exists (select 1 from per_all_people_f per2
146 where paf.party_id=per2.party_id
147 and paf.person_id<>per2.person_id
148 and per2.current_employee_flag='Y'
149 and p_effective_date between per2.effective_start_date
150 and per2.effective_end_date
151 and paf.creation_date > per2.creation_date);
152
153 begin
154 --
155 hr_utility.set_location('Entering Remap employee:'||l_proc, 10);
156 --
157 if (p_person_id is not null) then
158 hr_utility.set_location('Entering Address updation:'||l_proc, 20);
159 --
160 -- Addresses Updation
161 --
162 for c_add_rec in csr_address loop
163 l_add_ovn := c_add_rec.object_version_number;
164 --
165 -- Call to address row handler
166 --
167 hr_utility.set_location('Updating address:'||l_proc, 25);
168 per_add_upd.upd
169 (p_address_id => c_add_rec.address_id
170 ,p_person_id => p_person_id
171 ,p_business_group_id => c_add_rec.bg_id
172 ,p_object_version_number => l_add_ovn
173 ,p_effective_date => p_effective_date
174 );
175 end loop;
176 hr_utility.set_location('Leaving Address updation:'||l_proc, 30);
177 --
178 -- Phones Updation
179 --
180 hr_utility.set_location('Entering Phones updation:'||l_proc, 40);
181 for c_phn_rec in csr_phones loop
182 l_phn_ovn := c_phn_rec.object_version_number;
183 --
184 -- call to phones row handler
185 --
186 hr_utility.set_location('Updating phones:'||l_proc, 45);
187 per_phn_upd.upd
188 (p_phone_id => c_phn_rec.phone_id
189 ,p_parent_id => p_person_id
190 ,p_parent_table => 'PER_ALL_PEOPLE_F'
191 ,p_object_version_number => l_phn_ovn
192 ,p_effective_date => p_effective_date
193 );
194 end loop;
195 hr_utility.set_location('Leaving Phones updation:'||l_proc, 50);
196 --
197 -- Previous Employment Updation
198 --
199 hr_utility.set_location('Entering Previous Employment updation:'||l_proc, 60);
200 for c_pem_rec in csr_prev_emp loop
201 l_prev_emp_ovn := c_pem_rec.object_version_number;
202 --
203 -- call to previous employer row handler
204 --
205 hr_utility.set_location('Updating previous employment:'||l_proc, 65);
206 per_pem_upd.upd
207 (p_effective_date => p_effective_date
208 ,p_person_id => p_person_id
209 ,p_previous_employer_id => c_pem_rec.previous_employer_id
210 ,p_object_version_number => l_prev_emp_ovn
211 ,p_business_group_id => c_pem_rec.bg_id
212 );
213 end loop;
214 hr_utility.set_location('Leaving Previous Employment updation:'||l_proc, 70);
215 --
216 -- Qualifications Updation
217 --
218 hr_utility.set_location('Entering Qualifications updation:'||l_proc, 80);
219 for c_qua_rec in csr_qual loop
220 l_qua_ovn := c_qua_rec.object_version_number;
221 --
222 -- call to qualifications row handler
223 --
224 hr_utility.set_location('Updating qualifications:'||l_proc, 85);
225 per_qua_upd.upd
226 (p_qualification_id => c_qua_rec.qualification_id
227 ,p_object_version_number => l_qua_ovn
228 ,p_person_id => p_person_id
229 ,p_effective_date => p_effective_date
230 ,p_business_group_id => c_qua_rec.bg_id
231 );
232 end loop;
233 hr_utility.set_location('Leaving Qualifications updation:'||l_proc, 90);
234 --
235 -- Establishment Attendances Updation
236 --
237 hr_utility.set_location('Entering Establishment Attendances updation:'||l_proc, 100);
238 for c_esa_rec in csr_estab_attend loop
239 l_esa_ovn := c_esa_rec.object_version_number;
240 --
241 -- call to establishment attendances row handler
242 --
243 hr_utility.set_location('Updating establishment attendances:'||l_proc, 105);
244 per_esa_upd.upd
245 (p_attendance_id => c_esa_rec.attendance_id
246 ,p_business_group_id => c_esa_rec.bg_id
247 ,p_object_version_number => l_esa_ovn
248 ,p_person_id => p_person_id
249 ,p_effective_date => p_effective_date
250 );
251 end loop;
252 hr_utility.set_location('Leaving Establishment Attendances updation:'||l_proc, 110);
253 --
254 else
255 --
256 -- Update all employee records
257 --
258 hr_utility.set_location('Entering All Addresses updation:'||l_proc, 120);
259 --
260 -- Addresses Updation
261 --
262 for c_add_rec in csr_all_address loop
263 l_add_ovn := c_add_rec.object_version_number;
264 hr_utility.set_location('Updating address:'||l_proc, 125);
265 --
266 -- Call to address row handler
267 --
268 per_add_upd.upd
269 (p_address_id => c_add_rec.address_id
270 ,p_person_id => c_add_rec.paf_person_id
271 ,p_business_group_id => c_add_rec.bg_id
272 ,p_object_version_number => l_add_ovn
273 ,p_effective_date => p_effective_date
274 );
275 end loop;
276 hr_utility.set_location('Leaving All Addresses updation:'||l_proc, 130);
277 --
278 -- Phones Updation
279 --
280 hr_utility.set_location('Entering All Phones updation:'||l_proc, 140);
281 for c_phn_rec in csr_all_phones loop
282 l_phn_ovn := c_phn_rec.object_version_number;
283 --
284 -- call to phones row handler
285 --
286 hr_utility.set_location('Updating phones:'||l_proc, 145);
287 per_phn_upd.upd
288 (p_phone_id => c_phn_rec.phone_id
289 ,p_parent_id => c_phn_rec.paf_person_id
290 ,p_parent_table => 'PER_ALL_PEOPLE_F'
291 ,p_object_version_number => l_phn_ovn
292 ,p_effective_date => p_effective_date
293 );
294 end loop;
295 hr_utility.set_location('Leaving All Phones updation:'||l_proc, 150);
296 --
297 -- Previous Employment Updation
298 --
299 hr_utility.set_location('Entering All Previous Employment updation:'||l_proc, 160);
300 for c_pem_rec in csr_all_prev_emp loop
301 l_prev_emp_ovn := c_pem_rec.object_version_number;
302 --
303 -- call to previous employer row handler
304 --
305 hr_utility.set_location('Updating previous employment:'||l_proc, 165);
306 per_pem_upd.upd
307 (p_effective_date => p_effective_date
308 ,p_person_id => c_pem_rec.paf_person_id
309 ,p_previous_employer_id => c_pem_rec.previous_employer_id
310 ,p_object_version_number => l_prev_emp_ovn
311 ,p_business_group_id => c_pem_rec.bg_id
312 );
313 end loop;
314 hr_utility.set_location('Leaving All Previous Employment updation:'||l_proc, 170);
315 --
316 -- Qualifications Updation
317 --
318 hr_utility.set_location('Entering All Qualifications updation:'||l_proc, 180);
319 for c_qua_rec in csr_all_qual loop
320 l_qua_ovn := c_qua_rec.object_version_number;
321 --
322 -- call to qualifications row handler
323 --
324 hr_utility.set_location('Updating qualifications:'||l_proc, 185);
325 per_qua_upd.upd
326 (p_qualification_id => c_qua_rec.qualification_id
327 ,p_object_version_number => l_qua_ovn
328 ,p_person_id => c_qua_rec.paf_person_id
329 ,p_effective_date => p_effective_date
330 ,p_business_group_id => c_qua_rec.bg_id
331 );
332 end loop;
333 hr_utility.set_location('Leaving All Qualifications updation:'||l_proc, 190);
334 --
335 -- Establishment Attendances Updation
336 --
337 hr_utility.set_location('Entering All Establishment Attendances updation:'||l_proc, 200);
338 for c_esa_rec in csr_all_estab_attend loop
339 l_esa_ovn := c_esa_rec.object_version_number;
340 --
341 -- call to establishment attendances row handler
342 --
343 hr_utility.set_location('Updating establishment attendances:'||l_proc, 205);
344 per_esa_upd.upd
345 (p_attendance_id => c_esa_rec.attendance_id
346 ,p_object_version_number => l_esa_ovn
347 ,p_person_id => c_esa_rec.paf_person_id
348 ,p_business_group_id => c_esa_rec.bg_id
349 ,p_effective_date => p_effective_date
350 );
351 end loop;
352 hr_utility.set_location('Leaving All Establishment Attendances updation:'||l_proc, 210);
353 end if;
354 NULL;
355 --
356 end remap_employee;
357 --
358 --
359 -- Procedure remap_employee for the concurrent process
360 --
361 procedure remap_employee (errbuf out nocopy varchar2
362 ,retcode out nocopy varchar2
363 ,p_effective_date in varchar2
364 ,p_person_id in number ) is
365 --
366 l_proc varchar2(72) := 'IRC_GLOBAL_REMAP_PKG.remap_employee';
367 --
368 begin
369 hr_utility.set_location('Entering Remap employee:'||l_proc, 10);
370 remap_employee
371 (p_person_id => p_person_id
372 ,p_effective_date => fnd_date.canonical_to_date(p_effective_date)
373 );
374 commit;
375 retcode := 0;
376 hr_utility.set_location('Leaving Remap employee:'||l_proc, 20);
377 --
378 exception
379 when others then
380 rollback;
381 --
382 -- Set the return parameters to indicate failure
383 --
384 errbuf := sqlerrm;
385 retcode := 2;
386 end remap_employee;
387 --
388 end irc_global_remap_pkg;