DBA Data[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;