1 PACKAGE BODY irc_mds_migration AS
2 /* $Header: irmdsmig.pkb 120.0 2005/07/26 15:14:47 mbocutt noship $ */
3
4 -- ----------------------------------------------------------------------------
5 -- |--------------------------< migrateJobSearchData >------------------------|
6 -- ----------------------------------------------------------------------------
7 procedure migrateJobSearchData(
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 cursor csr_ak_customizations is
15 select SEARCH_CRITERIA_ID, name
16 from ak_customizations_tl ac, irc_search_criteria isc
17 where region_code = 'IRC_APPL_JOB_SEARCH_TBL'
18 and region_application_id = 800
19 and ac.customization_code = isc.search_name
20 and isc.SEARCH_CRITERIA_ID between p_start_pkid
21 and p_end_pkid;
22
23 l_current_name VARCHAR2(80);
24 l_current_id NUMBER(15);
25 l_rows_processed number := 0;
26
27 begin
28
29 /*
30 ** For each customization record which is stored in IRC_SEARCH_CRITERIA
31 ** update the search_name.
32 */
33 for c_cust in csr_ak_customizations loop
34
35 /*
36 ** Set language for iteration....
37 */
38 l_current_id := c_cust.SEARCH_CRITERIA_ID;
39 l_current_name := c_cust.name;
40
41 /*
42 ** Update the Search Criteria
43 */
44 update irc_search_criteria
45 set SEARCH_NAME = l_current_name
46 where SEARCH_CRITERIA_ID = l_current_id;
47
48 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
49
50 end loop;
51
52 p_rows_processed := l_rows_processed;
53
54 Exception
55 --
56 When Others Then
57 --
58 raise;
59 end;
60
61
62
63 -- ----------------------------------------------------------------------------
64 -- |--------------------------< createworkPrefsData >------------------------|
65 -- ----------------------------------------------------------------------------
66 procedure createworkPrefsData(
67 p_process_ctrl IN varchar2,
68 p_start_pkid IN number,
69 p_end_pkid IN number,
70 p_rows_processed OUT nocopy number)
71 is
72 --
73 cursor csr_irc_notifications is
74 select inp.PERSON_ID inp_person_id,
75 pad.town_or_city city,
76 pad.address_type addtype
77 from irc_notification_preferences inp,
78 per_addresses pad
79 where
80 inp.person_id = pad.person_id(+)
81 and pad.ADDRESS_TYPE(+) ='REC'
82 and inp.person_id between p_start_pkid
83 and p_end_pkid
84 order by inp_person_id,addtype ;
85 --
86 cursor csr_work_choices(p_person_id number) is
87 select null
88 from irc_search_criteria isc
89 where isc.object_id = p_person_id
90 and isc.object_type in ('WORK','WPREF');
91 --
92 l_rows_processed number := 0;
93 l_search_criteria_id number := null;
94 l_ovn_number number := null;
95 l_dummy varchar2(1);
96 --
97 begin
98 --
99 /*
100 ** For each personid record in IRC_NOTIFICATION_PREFERENCES ,
101 ** insert a record in IRC_SEARCH_CRITERIA.
102 */
103 --
104 for c_notifs in csr_irc_notifications loop
105 /*
106 **The cursor will return the personid.If there are two addresttype for person
107 ** then first the REC addresstype will be selected and then the null
108 ** addresstype will be selected in cursor.This is achieved by the order by
109 ** clause.So only the first record will be inserted and second record is
110 ** restricted.
111 */
112 open csr_work_choices(c_notifs.inp_person_id);
113 fetch csr_work_choices into l_dummy;
114 if csr_work_choices%notfound then
115 close csr_work_choices;
116 /*
117 ** insert the work preferences in Search Criteria
118 */
119 irc_search_criteria_api.create_work_choices (
120 p_effective_date => trunc(sysdate)
121 ,p_person_id => c_notifs.inp_person_id
122 ,p_employee => 'Y'
123 ,p_contractor => 'Y'
124 ,p_employment_category => 'FULLTIME'
125 ,p_match_competence => 'Y'
126 ,p_match_qualification => 'Y'
127 ,p_salary_period => 'ANNUAL'
128 ,p_work_at_home => null
129 ,p_location => c_notifs.city
130 ,p_object_version_number => l_ovn_number
131 ,p_search_criteria_id => l_search_criteria_id
132 );
133 else
134 close csr_work_choices;
135 end if;
136 --
137 l_rows_processed := l_rows_processed + 1;
138 end loop;
139 p_rows_processed := l_rows_processed;
140 Exception
141 --
142 When Others Then
143 --
144 raise;
145 end;
146 end irc_mds_migration;
[Home] [Help]