1 PACKAGE irc_seeker_vac_matching_pkg
2 /* $Header: irjsmtch.pkh 120.3 2007/08/14 16:18:33 vkaduban noship $ */
3 AS
4 -- ----------------------------------------------------------------------------
5 -- RECORD AND TABLE STRUCTURES
6 -- ----------------------------------------------------------------------------
7 TYPE g_seeker_rec_type
8 IS RECORD ( person_id irc_notification_preferences.person_id%TYPE
9 , party_id per_all_people_f.party_id%type
10 , show_jobs_since NUMBER
11 );
12 TYPE g_seeker_criteria_rec_type
13 IS RECORD ( search_criteria_id irc_search_criteria.search_criteria_id%TYPE
14 , person_id irc_search_criteria.object_id%TYPE
15 , party_id per_all_people_f.party_id%type
16 , distance_to_location irc_search_criteria.distance_to_location%TYPE
17 , location irc_search_criteria.location%TYPE
18 , employee irc_search_criteria.employee%TYPE
19 , contractor irc_search_criteria.contractor%TYPE
20 , employment_category irc_search_criteria.employment_category%TYPE
21 , keywords irc_search_criteria.keywords%TYPE
22 , travel_percentage irc_search_criteria.travel_percentage%TYPE
23 , min_salary irc_search_criteria.min_salary%TYPE
24 , salary_currency irc_search_criteria.salary_currency%TYPE
25 , salary_period irc_search_criteria.salary_period%TYPE
26 , match_competence irc_search_criteria.match_competence%TYPE
27 , match_qualification irc_search_criteria.match_qualification%TYPE
28 , job_title irc_search_criteria.job_title%TYPE
29 , department irc_search_criteria.department%TYPE
30 , professional_area irc_search_criteria.professional_area%TYPE
31 , work_at_home irc_search_criteria.work_at_home%TYPE
32 , attribute1 irc_search_criteria.attribute1%TYPE
33 , attribute2 irc_search_criteria.attribute2%TYPE
34 , attribute3 irc_search_criteria.attribute3%TYPE
35 , attribute4 irc_search_criteria.attribute4%TYPE
36 , attribute5 irc_search_criteria.attribute5%TYPE
37 , attribute6 irc_search_criteria.attribute6%TYPE
38 , attribute7 irc_search_criteria.attribute7%TYPE
39 , attribute8 irc_search_criteria.attribute8%TYPE
40 , attribute9 irc_search_criteria.attribute9%TYPE
41 , attribute10 irc_search_criteria.attribute10%TYPE
42 , attribute11 irc_search_criteria.attribute11%TYPE
43 , attribute12 irc_search_criteria.attribute12%TYPE
44 , attribute13 irc_search_criteria.attribute13%TYPE
45 , attribute14 irc_search_criteria.attribute14%TYPE
46 , attribute15 irc_search_criteria.attribute15%TYPE
47 , attribute16 irc_search_criteria.attribute16%TYPE
48 , attribute17 irc_search_criteria.attribute17%TYPE
49 , attribute18 irc_search_criteria.attribute18%TYPE
50 , attribute19 irc_search_criteria.attribute19%TYPE
51 , attribute20 irc_search_criteria.attribute20%TYPE
52 , attribute21 irc_search_criteria.attribute21%TYPE
53 , attribute22 irc_search_criteria.attribute22%TYPE
54 , attribute23 irc_search_criteria.attribute23%TYPE
55 , attribute24 irc_search_criteria.attribute24%TYPE
56 , attribute25 irc_search_criteria.attribute25%TYPE
57 , attribute26 irc_search_criteria.attribute26%TYPE
58 , attribute27 irc_search_criteria.attribute27%TYPE
59 , attribute28 irc_search_criteria.attribute28%TYPE
60 , attribute29 irc_search_criteria.attribute29%TYPE
61 , attribute30 irc_search_criteria.attribute30%TYPE
62 , isc_information1 irc_search_criteria.isc_information1%TYPE
63 , isc_information2 irc_search_criteria.isc_information2%TYPE
64 , isc_information3 irc_search_criteria.isc_information3%TYPE
65 , isc_information4 irc_search_criteria.isc_information4%TYPE
66 , isc_information5 irc_search_criteria.isc_information5%TYPE
67 , isc_information6 irc_search_criteria.isc_information6%TYPE
68 , isc_information7 irc_search_criteria.isc_information7%TYPE
69 , isc_information8 irc_search_criteria.isc_information8%TYPE
70 , isc_information9 irc_search_criteria.isc_information9%TYPE
71 , isc_information10 irc_search_criteria.isc_information10%TYPE
72 , isc_information11 irc_search_criteria.isc_information11%TYPE
73 , isc_information12 irc_search_criteria.isc_information12%TYPE
74 , isc_information13 irc_search_criteria.isc_information13%TYPE
75 , isc_information14 irc_search_criteria.isc_information14%TYPE
76 , isc_information15 irc_search_criteria.isc_information15%TYPE
77 , isc_information16 irc_search_criteria.isc_information16%TYPE
78 , isc_information17 irc_search_criteria.isc_information17%TYPE
79 , isc_information18 irc_search_criteria.isc_information18%TYPE
80 , isc_information19 irc_search_criteria.isc_information19%TYPE
81 , isc_information20 irc_search_criteria.isc_information20%TYPE
82 , isc_information21 irc_search_criteria.isc_information21%TYPE
83 , isc_information22 irc_search_criteria.isc_information22%TYPE
84 , isc_information23 irc_search_criteria.isc_information23%TYPE
85 , isc_information24 irc_search_criteria.isc_information24%TYPE
86 , isc_information25 irc_search_criteria.isc_information25%TYPE
87 , isc_information26 irc_search_criteria.isc_information26%TYPE
88 , isc_information27 irc_search_criteria.isc_information27%TYPE
89 , isc_information28 irc_search_criteria.isc_information28%TYPE
90 , isc_information29 irc_search_criteria.isc_information29%TYPE
91 , isc_information30 irc_search_criteria.isc_information30%TYPE
92 , geometry irc_search_criteria.geometry%TYPE
93 , location_id irc_search_criteria.location_id%TYPE
94 , derived_location irc_search_criteria.derived_location%TYPE
95 , show_jobs_since NUMBER
96 );
97 TYPE g_email_party_rec_type
98 IS RECORD ( search_criteria_id irc_search_criteria.search_criteria_id%TYPE
99 , vacancy_id irc_search_criteria.object_id%TYPE
100 , distance_to_location irc_search_criteria.distance_to_location%TYPE
101 , location irc_search_criteria.location%TYPE
102 , employee irc_search_criteria.employee%TYPE
103 , contractor irc_search_criteria.contractor%TYPE
104 , employment_category irc_search_criteria.employment_category%TYPE
105 , keywords irc_search_criteria.keywords%TYPE
106 , travel_percentage irc_search_criteria.travel_percentage%TYPE
107 , min_salary irc_search_criteria.min_salary%TYPE
108 , salary_currency irc_search_criteria.salary_currency%TYPE
109 , salary_period irc_search_criteria.salary_period%TYPE
110 , match_competence irc_search_criteria.match_competence%TYPE
111 , match_qualification irc_search_criteria.match_qualification%TYPE
112 , min_qual_level irc_search_criteria.min_qual_level%TYPE
113 , max_qual_level irc_search_criteria.max_qual_level%TYPE
114 , job_title irc_search_criteria.job_title%TYPE
115 , department irc_search_criteria.department%TYPE
116 , professional_area irc_search_criteria.professional_area%TYPE
117 , work_at_home irc_search_criteria.work_at_home%TYPE
118 , attribute1 irc_search_criteria.attribute1%TYPE
119 , attribute2 irc_search_criteria.attribute2%TYPE
120 , attribute3 irc_search_criteria.attribute3%TYPE
121 , attribute4 irc_search_criteria.attribute4%TYPE
122 , attribute5 irc_search_criteria.attribute5%TYPE
123 , attribute6 irc_search_criteria.attribute6%TYPE
124 , attribute7 irc_search_criteria.attribute7%TYPE
125 , attribute8 irc_search_criteria.attribute8%TYPE
126 , attribute9 irc_search_criteria.attribute9%TYPE
127 , attribute10 irc_search_criteria.attribute10%TYPE
128 , attribute11 irc_search_criteria.attribute11%TYPE
129 , attribute12 irc_search_criteria.attribute12%TYPE
130 , attribute13 irc_search_criteria.attribute13%TYPE
131 , attribute14 irc_search_criteria.attribute14%TYPE
132 , attribute15 irc_search_criteria.attribute15%TYPE
133 , attribute16 irc_search_criteria.attribute16%TYPE
134 , attribute17 irc_search_criteria.attribute17%TYPE
135 , attribute18 irc_search_criteria.attribute18%TYPE
136 , attribute19 irc_search_criteria.attribute19%TYPE
137 , attribute20 irc_search_criteria.attribute20%TYPE
138 , attribute21 irc_search_criteria.attribute21%TYPE
139 , attribute22 irc_search_criteria.attribute22%TYPE
140 , attribute23 irc_search_criteria.attribute23%TYPE
141 , attribute24 irc_search_criteria.attribute24%TYPE
142 , attribute25 irc_search_criteria.attribute25%TYPE
143 , attribute26 irc_search_criteria.attribute26%TYPE
144 , attribute27 irc_search_criteria.attribute27%TYPE
145 , attribute28 irc_search_criteria.attribute28%TYPE
146 , attribute29 irc_search_criteria.attribute29%TYPE
147 , attribute30 irc_search_criteria.attribute30%TYPE
148 , isc_information1 irc_search_criteria.isc_information1%TYPE
149 , isc_information2 irc_search_criteria.isc_information2%TYPE
150 , isc_information3 irc_search_criteria.isc_information3%TYPE
151 , isc_information4 irc_search_criteria.isc_information4%TYPE
152 , isc_information5 irc_search_criteria.isc_information5%TYPE
153 , isc_information6 irc_search_criteria.isc_information6%TYPE
154 , isc_information7 irc_search_criteria.isc_information7%TYPE
155 , isc_information8 irc_search_criteria.isc_information8%TYPE
156 , isc_information9 irc_search_criteria.isc_information9%TYPE
157 , isc_information10 irc_search_criteria.isc_information10%TYPE
158 , isc_information11 irc_search_criteria.isc_information11%TYPE
159 , isc_information12 irc_search_criteria.isc_information12%TYPE
160 , isc_information13 irc_search_criteria.isc_information13%TYPE
161 , isc_information14 irc_search_criteria.isc_information14%TYPE
162 , isc_information15 irc_search_criteria.isc_information15%TYPE
163 , isc_information16 irc_search_criteria.isc_information16%TYPE
164 , isc_information17 irc_search_criteria.isc_information17%TYPE
165 , isc_information18 irc_search_criteria.isc_information18%TYPE
166 , isc_information19 irc_search_criteria.isc_information19%TYPE
167 , isc_information20 irc_search_criteria.isc_information20%TYPE
168 , isc_information21 irc_search_criteria.isc_information21%TYPE
169 , isc_information22 irc_search_criteria.isc_information22%TYPE
170 , isc_information23 irc_search_criteria.isc_information23%TYPE
171 , isc_information24 irc_search_criteria.isc_information24%TYPE
172 , isc_information25 irc_search_criteria.isc_information25%TYPE
173 , isc_information26 irc_search_criteria.isc_information26%TYPE
174 , isc_information27 irc_search_criteria.isc_information27%TYPE
175 , isc_information28 irc_search_criteria.isc_information28%TYPE
176 , isc_information29 irc_search_criteria.isc_information29%TYPE
177 , isc_information30 irc_search_criteria.isc_information30%TYPE
178 , geometry hr_locations_all.geometry%TYPE
179 , location_id per_all_vacancies.location_id%TYPE
180 , derived_locale hr_locations_all.derived_locale%TYPE
181 , posting_content_id irc_posting_contents_tl.posting_content_id%TYPE
182 , business_group_id per_all_vacancies.business_group_id%TYPE
183 , name per_all_vacancies.name%TYPE
184 , recruiter_id per_all_vacancies.recruiter_id%type
185 );
186
187 TYPE g_posting_details_rec_type
188 IS RECORD ( posting_content_id irc_posting_contents.posting_content_id%TYPE
189 , job_title irc_posting_contents_tl.job_title%TYPE
190 , name irc_posting_contents_tl.name%TYPE
191 , object_id irc_search_criteria.object_id%TYPE
192 );
193 TYPE g_posting_details_tab_type
194 IS TABLE OF
195 g_posting_details_rec_type
196 INDEX BY BINARY_INTEGER;
197
198 TYPE g_seeker_details_rec_type
199 IS RECORD ( full_name per_all_people_f.full_name%TYPE
200 , person_id per_all_people_f.person_id%TYPE
201 );
202 TYPE g_seeker_details_tab_type
203 IS TABLE OF
204 g_seeker_details_rec_type
205 INDEX BY BINARY_INTEGER;
206
207 TYPE g_recruiter_problem_rec_type
208 IS RECORD ( recruiter_id per_all_vacancies.recruiter_id%TYPE
209 , vacancy_id per_all_vacancies.vacancy_id%TYPE
210 , vacancy_name per_all_vacancies.name%TYPE
211 , sqlerrm varchar2(2000)
212 , message varchar2(2000)
213 );
214
215 TYPE g_recruiter_problem_tab_type
216 IS TABLE OF g_recruiter_problem_rec_type
217 INDEX BY BINARY_INTEGER;
218 -- ----------------------------------------------------------------------------
219 -- CURSORS
220 -- ----------------------------------------------------------------------------
221
222
223 -- ----------------------------------------------------------------------------
224 -- FUNCTIONS
225 -- ----------------------------------------------------------------------------
226 FUNCTION get_location_match
227 ( p_location_to_match IN varchar2,
228 p_location_id IN hr_locations_all.LOCATION_ID%TYPE)
229 RETURN number;
230
231 FUNCTION convert_vacancy_amount
232 (p_from_currency IN VARCHAR2
233 ,p_to_currency IN VARCHAR2
234 ,p_amount IN NUMBER
235 ,p_conversion_date IN DATE
236 ,p_business_group_id IN NUMBER
237 ,p_processing_type IN VARCHAR2)
238 RETURN NUMBER;
239
240 FUNCTION get_job_notification_function
241 ( p_is_internal IN varchar2)
242 RETURN VARCHAR2;
243
244 -- ----------------------------------------------------------------------------
245 -- PROCEDURES
246 -- ----------------------------------------------------------------------------
247 PROCEDURE log_message
248 ( p_message IN VARCHAR2
249 , p_type IN VARCHAR2 DEFAULT 'B'
250 );
251 -- ----------------------------------------------------------------------------
252 -- Name :
253 -- email_suitable_vacs_to_seekers
254 -- Description :
255 -- This procedure initially finds all the job seekers who are eligible to
256 -- receive a vacancy notification e-mail by checking how often they want to
257 -- be sent an email notification against when they last received an email.
258 -- For those seekers who are due to receive an email, it searches through
259 -- the available vacancies for those which match their search criteria
260 -- and which are new since the last time they were sent an email.
261 -- Customizations
262 -- Certain parts of the email message sent to the jobs seekers can be
263 -- altered by changing the text in the following messages:
264 -- IRC_EMAIL_SEEKERS_SUBJECT - the text to appear in the msg subject line
265 -- IRC_EMAIL_SEEKERS_INTRODUCTION - html displayed at the top of the email
266 -- IRC_EMAIL_SEEKERS_CONCLUSION - html displayed at the end of the email
267 -- The base URL which all the vacancies have is held in profile option:
268 -- IRC_JOB_NOTIFICATION_URL
269 -- Parameters :
270 -- errbuf Two 'OUT' parameters are needed to pass info back to the
271 -- concurrent manager.errbuf is used to pass an error message back.
272 -- retcode The second parameters used to pass information back to the
273 -- concurrent manager, retcode, passes one of the following codes
274 -- back : '0' or null=success; '1' = a warning; '2' = an error.
275 -- p_ignore_seeker_matching_freq values : 'Y' or 'N'.
276 -- If 'Y', all job seekers who wish to be sent email notifications
277 -- will be matched and sent an email irrespective of whether they
278 -- are due to receive one.
279 -- p_ignore_job_age values : 'Y' or 'N'.
280 -- If 'Y', all vacancies that match will be sent, not just the new
281 -- ones created since the seeker was last sent an email
282 --
283 PROCEDURE email_suitable_vacs_to_seekers
284 ( errbuf OUT NOCOPY VARCHAR2
285 , retcode OUT NOCOPY NUMBER
286 , p_ignore_seeker_matching_freq IN VARCHAR2 DEFAULT 'N'
287 , p_ignore_job_age IN VARCHAR2 DEFAULT 'N'
288 );
289
290 -- p_candidacy_age
291 -- If this is not 0, it will limit the suitable seekers to those
292 -- who have registered within this number of days
293 PROCEDURE get_suitable_seekers_for_vac
294 ( errbuf OUT NOCOPY VARCHAR2
295 , retcode OUT NOCOPY NUMBER
296 , p_candidacy_age IN NUMBER DEFAULT 0
297 );
298 -- ----------------------------------------------------------------------------
299 -- Name
300 -- email_general_notifications
301 -- Description :
302 -- This procedure sends notifications and emails to job seekers who have
303 -- requested to receive them.
304 --
305 -- Customizations
306 -- Certain parts of the email message sent to the jobs seekers can be
307 -- altered by changing the text in the following messages:
308 -- IRC_GENERAL_NOTE_SUBJECT - the text to appear in the msg subject line
309 -- IRC_GENERAL_NOTE_CONTENT_TEXT - Text version of the body of the mail
310 -- IRC_GENERAL_NOTE_CONTENT_HTML - html version of the body of the mail
311 -- Parameters :
312 -- errbuf Two 'OUT' parameters are needed to pass info back to the
313 -- concurrent manager.errbuf is used to pass an error message back.
314 -- retcode The second parameters used to pass information back to the
315 -- concurrent manager, retcode, passes one of the following codes
316 -- back : '0' or null=success; '1' = a warning; '2' = an error.
317 --
318 PROCEDURE email_general_notifications
319 ( errbuf OUT NOCOPY VARCHAR2
320 , retcode OUT NOCOPY NUMBER
321 );
322 --
323 END irc_seeker_vac_matching_pkg;
[Home] [Help]