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