1 PACKAGE BODY irc_mrs_upgrade AS
2 /* $Header: irmrsupg.pkb 120.0 2005/07/26 15:14:58 mbocutt noship $ */
3
4 -- ----------------------------------------------------------------------------
5 -- |--------------------------< migrateVacancyRecSite >-----------------------|
6 -- ----------------------------------------------------------------------------
7 procedure migrateVacancyRecSite(
8 p_process_ctrl IN varchar2,
9 p_start_pkid IN number,
10 p_end_pkid IN number,
11 p_rows_processed OUT nocopy number)
12 is
13 --
14 l_internal_site_id irc_all_recruiting_sites.recruiting_site_id%type;
15 l_external_site_id irc_all_recruiting_sites.recruiting_site_id%type;
16 l_api_ovn per_recruitment_activities.object_version_number%type;
17 l_int_ext_rec_id per_recruitment_activities.recruitment_activity_id%type;
18 l_third_rec_id per_recruitment_activities.recruitment_activity_id%type;
19 l_rec_activity_for_id per_recruitment_activity_for.recruitment_activity_for_id%type;
20 l_raa_id number;
21 l_vac_name per_all_vacancies.name%type;
22 l_ovn number;
23 l_rows_processed number := 0;
24 l_unique_name per_recruitment_activities.name%type;
25 l_temp_name varchar2(60);
26 l_dummy number;
27 --
28 -- This cursor loops over all recruitment activities for iRecruitment vacancies
29 -- which still have an internal or external flag set
30 --
31 cursor csr_rec_activity is
32 select pra.recruitment_activity_id recruitment_activity_id
33 ,pra.internal_posting internal_posting
34 ,pra.external_posting external_posting
35 ,pra.business_group_id business_group_id
36 ,pra.date_start date_start
37 ,pra.name name
38 ,pra.recruiting_site_id recruiting_site_id
39 ,pra.object_version_number object_version_number
40 ,pra.date_end date_end
41 ,pra.posting_content_id posting_content_id
42 from per_recruitment_activities pra
43 ,irc_posting_contents ipc
44 where ipc.posting_content_id = pra.posting_content_id
45 and ipc.posting_content_id between p_start_pkid and p_end_pkid
46 and (pra.internal_posting is not null or pra.external_posting is not null);
47 --
48 -- This cursor gets all vacancies associated with a recruitment activity
49 --
50 cursor csr_rec_activity_for(p_recruitment_activity_id number) is
51 select pfr.business_group_id business_group_id
52 ,pfr.vacancy_id vacancy_id
53 from per_recruitment_activity_for pfr
54 where pfr.recruitment_activity_id = p_recruitment_activity_id;
55 --
56 -- This cursor gets all assignments in which a person has applied for a recruitment activity
57 -- and looks to see is the person is an emp-apl
58 --
59 cursor csr_asg_rec_activity(p_recruitment_activity_id number) is
60 select asg.assignment_id assignment_id
61 ,asg.object_version_number object_version_number
62 ,ppf.current_employee_flag current_employee_flag
63 from per_all_assignments_f asg
64 ,per_all_people_f ppf
65 where asg.person_id = ppf.person_id
66 and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date
67 and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
68 and asg.recruitment_activity_id = p_recruitment_activity_id;
69 --
70 -- This cursor looks for the internal recruiting_site_id
71 --
72 cursor csr_internal is
73 select irs.recruiting_site_id recruiting_site_id
74 from irc_all_recruiting_sites irs
75 where irs.internal = 'Y'
76 and irs.external = 'N';
77 --
78 -- This cursor looks for the external recruiting_site_id
79 --
80 cursor csr_external is
81 select irs.recruiting_site_id recruiting_site_id
82 from irc_all_recruiting_sites irs
83 where irs.external = 'Y'
84 and irs.internal = 'N';
85 --
86 cursor csr_raa_id is
87 select per_recruitment_activities_s.nextval
88 from sys.dual;
89 --
90 cursor csr_site_exists(p_posting_content_id number,p_recruiting_site_id number) is
91 select 1
92 from per_recruitment_activities pra
93 where posting_content_id=p_posting_content_id
94 and recruiting_site_id=p_recruiting_site_id;
95 --
96 cursor dup_ints is
97 select raa.recruitment_activity_id
98 from per_recruitment_activities raa
99 ,per_recruitment_activity_for raf
100 where raa.posting_content_id is not null
101 and raa.internal_posting='Y'
102 and raa.recruitment_activity_id=raf.recruitment_activity_id
103 and exists (select 1
104 from per_recruitment_activity_for raf2
105 ,per_recruitment_activities raa2
106 where raf2.vacancy_id=raf.vacancy_id
107 and raf2.recruitment_activity_id=raa2.recruitment_activity_id
108 and raa2.internal_posting='Y'
109 and raf2.recruitment_activity_id <> raf.recruitment_activity_id
110 and raf2.creation_date < raf.creation_date)
111 order by 1;
112 --
113 cursor dup_exts is
114 select raa.recruitment_activity_id
115 from per_recruitment_activities raa
116 ,per_recruitment_activity_for raf
117 where raa.posting_content_id is not null
118 and raa.external_posting='Y'
119 and raa.recruitment_activity_id=raf.recruitment_activity_id
120 and exists (select 1
121 from per_recruitment_activity_for raf2
122 ,per_recruitment_activities raa2
123 where raf2.vacancy_id=raf.vacancy_id
124 and raf2.recruitment_activity_id=raa2.recruitment_activity_id
125 and raa2.external_posting='Y'
126 and raf2.recruitment_activity_id <> raf.recruitment_activity_id
127 and raf2.creation_date < raf.creation_date)
128 order by 1;
129 --
130 begin
131 -- Get the internal and external sites IDs
132 --
133 open csr_internal;
134 fetch csr_internal into l_internal_site_id;
135 close csr_internal;
136 --
137 open csr_external;
138 fetch csr_external into l_external_site_id;
139 close csr_external;
140 --
141 if (l_internal_site_id is not null and l_external_site_id is not null) then
142 --
143 -- Check for recruitment modifications
144 --
145 for int_recs in dup_ints loop
146 update per_recruitment_activities
147 set internal_posting=null
148 ,external_posting=null
149 where recruitment_activity_id = int_recs.recruitment_activity_id;
150 end loop;
151 --
152 for ext_recs in dup_exts loop
153 update per_recruitment_activities
154 set internal_posting=null
155 ,external_posting=null
156 where recruitment_activity_id = ext_recs.recruitment_activity_id;
157 end loop;
158 --
159 -- Loop over all iRecruitment recruitment activities
160 --
161 for csr_rec_activity_rec in csr_rec_activity
162 loop
163 if csr_rec_activity_rec.internal_posting = 'Y'
164 then
165 --
166 -- If the recruitment activity is currently for internal, we know it has not
167 -- yet been migrated, so update it to point to the internal site
168 -- clear the existing data so we know not to process the row in future
169 -- and set the external site id
170 --
171 update per_recruitment_activities
172 set internal_posting = null
173 ,external_posting = null
174 ,recruiting_site_id = l_internal_site_id
175 where recruitment_activity_id = csr_rec_activity_rec.recruitment_activity_id;
176 --
177 --
178 -- If this is an external posting too, then create a new external posting
179 if csr_rec_activity_rec.external_posting = 'Y'
180 then
181 --
182 -- Check to see if the external site already exists in case we migrated it
183 -- without clearing the data already;
184 --
185 open csr_site_exists(csr_rec_activity_rec.posting_content_id,l_external_site_id);
186 fetch csr_site_exists into l_dummy;
187 if csr_site_exists%found then
188 close csr_site_exists;
189 else
190 close csr_site_exists;
191 open csr_raa_id;
192 fetch csr_raa_id into l_raa_id;
193 close csr_raa_id;
194 l_vac_name := csr_rec_activity_rec.name;
195 l_temp_name := l_vac_name||l_raa_id;
196 if (lengthb(l_temp_name) > 30) then
197 l_vac_name := substrb(l_temp_name,1,30 - lengthb(to_char(l_raa_id)))
198 ||l_raa_id;
199 else
200 l_vac_name := l_temp_name;
201 end if;
202 per_recruitment_activity_api.create_recruitment_activity
203 (
204 p_business_group_id => csr_rec_activity_rec.business_group_id
205 ,p_date_start => csr_rec_activity_rec.date_start
206 ,p_name => l_vac_name
207 ,p_date_end => csr_rec_activity_rec.date_end
208 ,p_posting_content_id => csr_rec_activity_rec.posting_content_id
209 ,p_recruiting_site_id => l_external_site_id
210 ,p_recruitment_activity_id => l_int_ext_rec_id
211 ,p_object_version_number => l_ovn
212 );
213 --
214 -- add recruitment_activity_for records for the new recruitment activity
215 --
216 for csr_rec_activity_for_rec in csr_rec_activity_for(csr_rec_activity_rec.recruitment_activity_id)
217 loop
218 per_rec_activity_for_api.create_rec_activity_for
219 (
220 p_business_group_id => csr_rec_activity_for_rec.business_group_id
221 ,p_vacancy_id => csr_rec_activity_for_rec.vacancy_id
222 ,p_rec_activity_id => l_int_ext_rec_id
223 ,p_rec_activity_for_id => l_rec_activity_for_id
224 ,p_object_version_number => l_ovn
225 );
226 end loop;
227 --
228 -- update any non-employee assignments which were pointing to this recruitment activity so that
229 -- they point to the new external one
230 --
231 update per_all_assignments_f asg
232 set recruitment_activity_id = l_int_ext_rec_id
233 where recruitment_activity_id = csr_rec_activity_rec.recruitment_activity_id
234 and not exists(select 1
235 from per_all_people_f per
236 where per.person_id=asg.person_id
237 and asg.effective_start_date
238 between per.effective_start_date and per.effective_end_date
239 and per.current_employee_flag <> 'Y'
240 );
241 end if; -- end of csr_site_exists
242 --
243 end if; -- end of external_posting
244 --
245 -- If this has an third party site too, then add a recruitment activity for that
246 --
247 if csr_rec_activity_rec.recruiting_site_id is not null
248 then
249 --
250 -- Check to see if the third party site already exists in case we migrated it
251 -- without clearing the data already;
252 --
253 open csr_site_exists(csr_rec_activity_rec.posting_content_id,csr_rec_activity_rec.recruiting_site_id);
254 fetch csr_site_exists into l_dummy;
255 if csr_site_exists%found then
256 close csr_site_exists;
257 else
258 close csr_site_exists;
259 open csr_raa_id;
260 fetch csr_raa_id into l_raa_id;
261 close csr_raa_id;
262 l_vac_name := csr_rec_activity_rec.name;
263 l_temp_name := l_vac_name||l_raa_id;
264 if (lengthb(l_temp_name) > 30) then
265 l_vac_name := substrb(l_temp_name,1,30 - lengthb(to_char(l_raa_id)))
266 ||l_raa_id;
267 else
268 l_vac_name := l_temp_name;
269 end if;
270 per_recruitment_activity_api.create_recruitment_activity
271 (
272 p_business_group_id => csr_rec_activity_rec.business_group_id
273 ,p_date_start => csr_rec_activity_rec.date_start
274 ,p_name => l_vac_name
275 ,p_date_end => csr_rec_activity_rec.date_end
276 ,p_posting_content_id => csr_rec_activity_rec.posting_content_id
277 ,p_recruiting_site_id => csr_rec_activity_rec.recruiting_site_id
278 ,p_recruitment_activity_id => l_third_rec_id
279 ,p_object_version_number => l_ovn
280 );
281 --
282 -- add recruitment_activity_for records for the new recruitment activity
283 --
284 for csr_rec_activity_for_rec in csr_rec_activity_for(csr_rec_activity_rec.recruitment_activity_id)
285 loop
286 per_rec_activity_for_api.create_rec_activity_for
287 (
288 p_business_group_id => csr_rec_activity_for_rec.business_group_id
289 ,p_vacancy_id => csr_rec_activity_for_rec.vacancy_id
290 ,p_rec_activity_id => l_third_rec_id
291 ,p_rec_activity_for_id => l_rec_activity_for_id
292 ,p_object_version_number => l_ovn
293 );
294 end loop;
295 end if; -- end of csr_site_exists
296 --
297 end if; -- end of recruiting_site_id
298 --
299 elsif csr_rec_activity_rec.external_posting = 'Y'
300 then
301 --
302 -- this is not an internal posting, but it is an external posting, so turn the
303 -- existing recruitment activity in to the external posting
304 -- clear the existing data so we know not to process the row again
305 --
306 update per_recruitment_activities
307 set internal_posting=null
308 ,external_posting=null
309 ,recruiting_site_id=l_external_site_id
310 where recruitment_activity_id = csr_rec_activity_rec.recruitment_activity_id;
311
312 -- if there is a third party recruiting site, then add a line for that
313 --
314 if csr_rec_activity_rec.recruiting_site_id is not null
315 then
316 --
317 -- Check to see if the third party site already exists in case we migrated it
318 -- without clearing the data already;
319 --
320 open csr_site_exists(csr_rec_activity_rec.posting_content_id,csr_rec_activity_rec.recruiting_site_id);
321 fetch csr_site_exists into l_dummy;
322 if csr_site_exists%found
323 then
324 close csr_site_exists;
325 else
326 close csr_site_exists;
327 open csr_raa_id;
328 fetch csr_raa_id into l_raa_id;
329 close csr_raa_id;
330 l_vac_name := csr_rec_activity_rec.name;
331 l_temp_name := l_vac_name||l_raa_id;
332 if (lengthb(l_temp_name) > 30) then
333 l_vac_name := substrb(l_temp_name,1,30 - lengthb(to_char(l_raa_id)))
334 ||l_raa_id;
335 else
336 l_vac_name := l_temp_name;
337 end if;
338 per_recruitment_activity_api.create_recruitment_activity
339 (
340 p_business_group_id => csr_rec_activity_rec.business_group_id
341 ,p_date_start => csr_rec_activity_rec.date_start
342 ,p_name => l_vac_name
343 ,p_date_end => csr_rec_activity_rec.date_end
344 ,p_posting_content_id => csr_rec_activity_rec.posting_content_id
345 ,p_recruiting_site_id => csr_rec_activity_rec.recruiting_site_id
346 ,p_recruitment_activity_id => l_third_rec_id
347 ,p_object_version_number => l_ovn
348 );
349 --
350 -- add recruitment_activity_for records for the new recruitment activity
351 --
352 for csr_rec_activity_for_rec in csr_rec_activity_for(csr_rec_activity_rec.recruitment_activity_id)
353 loop
354 per_rec_activity_for_api.create_rec_activity_for
355 (
356 p_business_group_id => csr_rec_activity_for_rec.business_group_id
357 ,p_vacancy_id => csr_rec_activity_for_rec.vacancy_id
358 ,p_rec_activity_id => l_third_rec_id
359 ,p_rec_activity_for_id => l_rec_activity_for_id
360 ,p_object_version_number => l_ovn
361 );
362 end loop;
363 end if; -- end of csr_site_exists
364 --
365 end if; -- end of recruiting_site_id
366 --
367 elsif csr_rec_activity_rec.recruiting_site_id is not null then
368 --
369 -- this is not for internal or external, only for 3rd party, so just clear the flag data
370 --
371 update per_recruitment_activities
372 set internal_posting=null
373 ,external_posting=null
374 where recruitment_activity_id=csr_rec_activity_rec.recruitment_activity_id;
375 end if; -- end of recruiting_site_id
376 --
377 -- clean the variables for the next pass
378 --
379 l_int_ext_rec_id := null;
380 l_third_rec_id := null;
381 l_rows_processed := l_rows_processed + 1;
382 --
383 end loop;
384 p_rows_processed := l_rows_processed;
385 end if;
386 end migrateVacancyRecSite;
387 --
388 -- ----------------------------------------------------------------------------
389 -- |--------------------------< migrateVacancyRecSiteTL >---------------------|
390 -- ----------------------------------------------------------------------------
391 procedure migrateVacancyRecSiteTL(
392 p_process_ctrl IN varchar2,
393 p_start_pkid IN number,
394 p_end_pkid IN number,
395 p_rows_processed OUT nocopy number)
396 is
397 --
398 l_rows_processed number := 0;
399 --
400 cursor csr_rec_sites is
401 select irs.recruiting_site_id recruiting_site_id
402 ,irs.site_name site_name
403 ,dbms_lob.substr(irs.posting_url) posting_url
404 ,dbms_lob.substr(irs.redirection_url) redirection_url
405 from irc_all_recruiting_sites irs
406 where not exists (select null
407 from irc_all_recruiting_sites_tl itl
408 where itl.recruiting_site_id = irs.recruiting_site_id)
409 and irs.recruiting_site_id between p_start_pkid and p_end_pkid;
410 --
411 cursor csr_lang is
412 select language_code from fnd_languages
413 where installed_flag in ('I', 'B');
414 --
415 begin
416 hr_general.g_data_migrator_mode :='Y';
417 for csr_rec_sites_rec in csr_rec_sites
418 loop
419 for csr_lang_rec in csr_lang
420 loop
421 --
422 irc_irt_ins.ins_tl
423 (p_recruiting_site_id => csr_rec_sites_rec.recruiting_site_id
424 ,p_language_code => csr_lang_rec.language_code
425 ,p_site_name => csr_rec_sites_rec.site_name
426 ,p_redirection_url => csr_rec_sites_rec.redirection_url
427 ,p_posting_url => csr_rec_sites_rec.posting_url
428 );
429 end loop;
430 l_rows_processed := l_rows_processed + 1;
431 end loop;
432 --
433 update irc_all_recruiting_sites irs
434 set internal_name=upper(site_name)
435 where internal_name is null
436 and irs.recruiting_site_id between p_start_pkid and p_end_pkid;
437 --
438 p_rows_processed := l_rows_processed;
439 --
440 end migrateVacancyRecSiteTL;
441 --
442 end irc_mrs_upgrade;
[Home] [Help]