1 PACKAGE BODY irc_primary_posting_change AS
2 /* $Header: irppiupg.pkb 120.5 2006/10/18 01:48:34 gjaggava noship $*/
3
4 -- ----------------------------------------------------------------------------
5 -- |--------------------------< update_primary_posting_data >-----------------|
6 -- ----------------------------------------------------------------------------
7 procedure update_primary_posting_data(
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 -- This cursor gets all of the vacancy records to be processed
15 --
16 cursor get_vacs is
17 select per_vac.vacancy_id from per_all_vacancies per_vac
18 where per_vac.vacancy_id between p_start_pkid and p_end_pkid
19 and per_vac.primary_posting_id is null;
20 --
21 -- this cursor gets all of the dummy recruitment activity records
22 --
23 cursor get_dummy_posting(p_vacancy_id number) is
24 select per_rac.posting_content_id
25 , per_rac.recruitment_activity_id
26 , per_for.recruitment_activity_for_id
27 from per_recruitment_activities per_rac
28 , per_recruitment_activity_for per_for
29 where per_for.vacancy_id=p_vacancy_id
30 and per_for.recruitment_activity_id = per_rac.recruitment_activity_id
31 and per_rac.posting_content_id is not null
32 and per_rac.recruiting_site_id is null
33 and not exists (select null
34 from per_recruitment_activity_for per_for2
35 where per_for2.recruitment_activity_id = per_for.recruitment_activity_id
36 and per_for2.vacancy_id <> per_for.vacancy_id)
37 order by per_rac.posting_content_id;
38 --
39 l_rows_processed number := 0;
40 --
41 begin
42 for vac_rec in get_vacs loop
43 for posting_rec in get_dummy_posting(vac_rec.vacancy_id) loop
44 update per_all_vacancies
45 set primary_posting_id=posting_rec.posting_content_id
46 where vacancy_id=vac_rec.vacancy_id;
47
48 update per_all_assignments_f
49 set recruitment_activity_id = null
50 where recruitment_activity_id=posting_rec.recruitment_activity_id;
51
52 delete from per_recruitment_activity_for
53 where recruitment_activity_for_id=posting_rec.recruitment_activity_for_id;
54 delete from per_recruitment_activities
55 where recruitment_activity_id=posting_rec.recruitment_activity_id;
56
57 end loop;
58 l_rows_processed := l_rows_processed + 1;
59
60 end loop;
61
62 p_rows_processed := l_rows_processed;
63 end update_primary_posting_data;
64 --
65 end irc_primary_posting_change;
[Home] [Help]