[Home] [Help]
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;
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;
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;
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
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);
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
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
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
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
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
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
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;
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;
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;
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;
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