[Home] [Help]
PACKAGE BODY: APPS.HRI_OPL_RECRUITMENT
Source
1 PACKAGE BODY hri_opl_recruitment AS
2 /* $Header: hriprec.pkb 120.1 2006/02/02 06:13:39 cbridge noship $ */
3
4 g_assignment_id NUMBER;
5 g_hire_asg_id NUMBER;
6
7 g_interview1_date DATE;
8 g_interview2_date DATE;
9 g_offer_date DATE;
10 g_accepted_date DATE;
11 g_current_date DATE;
12 g_hire_date DATE;
13 g_application_reason VARCHAR2(30);
14 g_interview1_reason VARCHAR2(30);
15 g_interview2_reason VARCHAR2(30);
16 g_offer_reason VARCHAR2(30);
17 g_accepted_reason VARCHAR2(30);
18 g_hire_reason VARCHAR2(30);
19 g_current_status VARCHAR2(30);
20 g_success_flag VARCHAR2(30);
21
22 /* Variables for vacancy cache */
23 g_vacancy_id NUMBER := -1;
24 g_vacancy_days_to_hire NUMBER;
25 g_vacancy_fill_to_hire NUMBER;
26 g_vacancy_days_to_fill NUMBER;
27 g_vacancy_apl_count NUMBER;
28
29 PROCEDURE calculate_stages( p_assignment_id IN NUMBER,
30 p_person_id IN NUMBER,
31 p_term_reason IN VARCHAR2,
32 p_end_date IN DATE) IS
33
34 /*
35 CURSOR irec_stage_csr IS
36 SELECT
37 ias.status_change_date
38 ,ast.per_system_status
39 ,ias.status_change_reason
40 FROM
41 irc_assignment_statuses ias
42 ,per_assignment_status_types ast
43 WHERE ias.assignment_status_type_id = ast.assignment_status_type_id
44 AND ias.assignment_id = p_assignment_id;
45 */
46
47 CURSOR irec_stage_csr IS
48 SELECT
49 asg.effective_start_date status_change_date
50 ,ast.per_system_status
51 ,asg.change_reason status_change_reason
52 FROM
53 per_all_assignments_f asg
54 ,per_assignment_status_types ast
55 WHERE asg.assignment_status_type_id = ast.assignment_status_type_id
56 AND asg.assignment_id = p_assignment_id;
57
58 CURSOR success_csr(cp_person_id NUMBER
59 ,cp_assignment_id NUMBER
60 ,cp_end_date DATE) IS
61 SELECT 'Y', asg.change_reason, asg.assignment_id
62 FROM per_all_assignments_f asg
63 WHERE asg.person_id = cp_person_id
64 AND asg.effective_start_date = cp_end_date + 1
65 AND (asg.assignment_id = cp_assignment_id
66 OR asg.primary_flag = 'Y')
67 AND asg.assignment_type = 'E'
68 ORDER BY DECODE(asg.assignment_id, cp_assignment_id, 1, 2);
69
70 BEGIN
71
72 g_assignment_id := p_assignment_id;
73
74 g_interview1_date := to_date(null);
75 g_interview2_date := to_date(null);
76 g_offer_date := to_date(null);
77 g_accepted_date := to_date(null);
78 g_current_date := to_date(null);
79 g_hire_date := to_date(null);
80 g_interview1_reason := null;
81 g_interview2_reason := null;
82 g_offer_reason := null;
83 g_accepted_reason := null;
84 g_hire_reason := null;
85 g_success_flag := null;
86 g_current_status := null;
87
88 FOR stage_rec IN irec_stage_csr LOOP
89
90 IF (stage_rec.per_system_status = 'ACTIVE_APL') THEN
91 g_application_reason := stage_rec.status_change_reason;
92 ELSIF (stage_rec.per_system_status = 'INTERVIEW1' AND
93 g_interview1_date IS NULL) THEN
94 g_interview1_date := stage_rec.status_change_date;
95 g_interview1_reason := stage_rec.status_change_reason;
96 ELSIF (stage_rec.per_system_status = 'INTERVIEW2' AND
97 g_interview2_date IS NULL) THEN
98 g_interview2_date := stage_rec.status_change_date;
99 g_interview2_reason := stage_rec.status_change_reason;
100 ELSIF (stage_rec.per_system_status = 'OFFER' AND
101 g_offer_date IS NULL) THEN
102 g_offer_date := stage_rec.status_change_date;
103 g_offer_reason := stage_rec.status_change_reason;
104 ELSIF (stage_rec.per_system_status = 'ACCEPTED' AND
105 g_accepted_date IS NULL) THEN
106 g_accepted_date := stage_rec.status_change_date;
107 g_accepted_reason := stage_rec.status_change_reason;
108 ELSIF (stage_rec.per_system_status = 'ACTIVE_ASSIGN' AND
109 g_hire_date IS NULL) THEN
110 g_hire_date := stage_rec.status_change_date;
111 END IF;
112
113 g_current_status := stage_rec.per_system_status || '_PEND';
114 g_current_date := stage_rec.status_change_date;
115
116 END LOOP;
117
118 OPEN success_csr(p_person_id, p_assignment_id, g_hire_date-1);
119 FETCH success_csr INTO g_success_flag, g_hire_reason, g_hire_asg_id;
120 CLOSE success_csr;
121
122 IF (g_success_flag = 'Y') THEN
123 g_current_status := 'END_SCCSS';
124 g_current_date := g_hire_date ;
125 ELSE
126 g_success_flag := 'N';
127 g_current_status := 'END_FAIL';
128 g_current_date := g_current_date;
129 END IF;
130
131 END calculate_stages;
132
133 PROCEDURE refresh_globals(p_assignment_id IN NUMBER,
134 p_person_id IN NUMBER,
135 p_term_reason IN VARCHAR2,
136 p_end_date IN DATE) IS
137
138 BEGIN
139
140 IF (p_assignment_id = g_assignment_id) THEN
141 null;
142 ELSE
143 calculate_stages(p_assignment_id => p_assignment_id,
144 p_person_id => p_person_id,
145 p_term_reason => p_term_reason,
146 p_end_date => p_end_date);
147 END IF;
148
149 END refresh_globals;
150
151 FUNCTION get_stage_status(p_assignment_id IN NUMBER,
152 p_person_id IN NUMBER,
153 p_term_reason IN VARCHAR2,
154 p_end_date IN DATE,
155 p_system_status IN VARCHAR2)
156 RETURN VARCHAR2 IS
157
158 l_return_status VARCHAR2(30);
159
160 BEGIN
161
162 refresh_globals(p_assignment_id => p_assignment_id,
163 p_person_id => p_person_id,
164 p_term_reason => p_term_reason,
165 p_end_date => p_end_date);
166
167 IF (p_system_status = 'ACTIVE_APL') THEN
168 IF (g_interview1_date IS NOT NULL OR
169 g_interview2_date IS NOT NULL OR
170 g_offer_date IS NOT NULL OR
171 g_accepted_date IS NOT NULL) THEN
172 l_return_status := p_system_status || '_ACC';
173 ELSIF (p_end_date IS NULL) THEN
174 l_return_status := p_system_status || '_PEND';
175 ELSE
176 l_return_status := p_system_status || '_REJ';
177 END IF;
178 ELSIF (p_system_status = 'INTERVIEW1' AND g_interview1_date IS NOT NULL) THEN
179 IF (g_interview2_date IS NOT NULL OR
180 g_offer_date IS NOT NULL OR
181 g_accepted_date IS NOT NULL) THEN
182 l_return_status := p_system_status || '_ACC';
183 ELSIF (p_end_date IS NULL) THEN
184 l_return_status := p_system_status || '_PEND';
185 ELSE
186 l_return_status := p_system_status || '_REJ';
187 END IF;
188 ELSIF (p_system_status = 'INTERVIEW2' AND g_interview2_date IS NOT NULL) THEN
189 IF (g_offer_date IS NOT NULL OR g_accepted_date IS NOT NULL) THEN
190 l_return_status := p_system_status || '_ACC';
191 ELSIF (p_end_date IS NULL) THEN
192 l_return_status := p_system_status || '_PEND';
193 ELSE
194 l_return_status := p_system_status || '_REJ';
195 END IF;
196 ELSIF (p_system_status = 'OFFER' AND g_offer_date IS NOT NULL) THEN
197 IF (g_accepted_date IS NOT NULL) THEN
198 l_return_status := p_system_status || '_ACC';
199 ELSIF (p_end_date IS NULL) THEN
200 l_return_status := p_system_status || '_PEND';
201 ELSE
202 l_return_status := p_system_status || '_REJ';
203 END IF;
204 ELSIF (p_system_status = 'ACCEPTED' AND g_accepted_date IS NOT NULL) THEN
205 IF (g_success_flag = 'Y') THEN
206 l_return_status := p_system_status || '_ACC';
207 ELSIF (g_success_flag = 'N') THEN
208 l_return_status := p_system_status || '_REJ';
209 ELSE
210 l_return_status := p_system_status || '_PEND';
211 END IF;
212 ELSIF (p_system_status = 'END' AND p_end_date IS NOT NULL) THEN
213 IF (g_success_flag = 'Y') THEN
214 l_return_status := p_system_status || '_SCCSS';
215 ELSIF (g_success_flag = 'N') THEN
216 l_return_status := p_system_status || '_FAIL';
217 END IF;
218 ELSIF (p_system_status = 'CURRENT') THEN
219 l_return_status := g_current_status;
220 END IF;
221
222 RETURN l_return_status;
223
224 END get_stage_status;
225
226 FUNCTION get_stage_reason(p_assignment_id IN NUMBER,
227 p_person_id IN NUMBER,
228 p_term_reason IN VARCHAR2,
229 p_end_date IN DATE,
230 p_system_status IN VARCHAR2)
231 RETURN VARCHAR2 IS
232
233 BEGIN
234
235 refresh_globals(p_assignment_id => p_assignment_id,
236 p_person_id => p_person_id,
237 p_term_reason => p_term_reason,
238 p_end_date => p_end_date);
239
240 IF (p_system_status = 'ACTIVE_APL') THEN
241 RETURN g_application_reason;
242 ELSIF (p_system_status = 'INTERVIEW1') THEN
243 RETURN g_interview1_reason;
244 ELSIF (p_system_status = 'INTERVIEW2') THEN
245 RETURN g_interview2_reason;
246 ELSIF (p_system_status = 'OFFER') THEN
247 RETURN g_offer_reason;
248 ELSIF (p_system_status = 'ACCEPTED') THEN
249 RETURN g_accepted_reason;
250 ELSIF (p_system_status = 'HIRE') THEN
251 RETURN g_hire_reason;
252 END IF;
253
254 RETURN null;
255
256 END get_stage_reason;
257
258 FUNCTION get_stage_date (p_assignment_id IN NUMBER,
259 p_person_id IN NUMBER,
260 p_term_reason IN VARCHAR2,
261 p_end_date IN DATE,
262 p_system_status IN VARCHAR2)
263 RETURN DATE IS
264
265 BEGIN
266
267 refresh_globals(p_assignment_id => p_assignment_id,
268 p_person_id => p_person_id,
269 p_term_reason => p_term_reason,
270 p_end_date => p_end_date);
271
272 IF (p_system_status = 'INTERVIEW1') THEN
273 RETURN g_interview1_date;
274 ELSIF (p_system_status = 'INTERVIEW2') THEN
275 RETURN g_interview2_date;
276 ELSIF (p_system_status = 'OFFER') THEN
277 RETURN g_offer_date;
278 ELSIF (p_system_status = 'ACCEPTED') THEN
279 RETURN g_accepted_date;
280 ELSIF (p_system_status = 'CURRENT') THEN
281 RETURN g_current_date;
282 ELSIF (p_system_status = 'HIRE') THEN
283 RETURN g_hire_date;
284 END IF;
285
286 RETURN to_date(null);
287
288 END get_stage_date;
289
290 FUNCTION get_hire_assignment(p_assignment_id IN NUMBER,
291 p_person_id IN NUMBER,
292 p_term_reason IN VARCHAR2,
293 p_end_date IN DATE)
294 RETURN NUMBER IS
295
296 BEGIN
297
298 refresh_globals(p_assignment_id => p_assignment_id,
299 p_person_id => p_person_id,
300 p_term_reason => p_term_reason,
301 p_end_date => p_end_date);
302
303 RETURN g_hire_asg_id;
304
305 END get_hire_assignment;
306
307 FUNCTION is_pursued_apl(p_person_id IN NUMBER,
308 p_vacancy_id IN NUMBER,
309 p_effective_date IN DATE)
310 RETURN NUMBER IS
311
312 l_pursued_indicator NUMBER;
313
314 CURSOR pursued_apl_csr IS
315 SELECT 1
316 FROM
317 irc_vacancy_considerations ivc
318 ,per_all_people_f peo
319 WHERE ivc.party_id = peo.party_id
320 AND peo.person_id = p_person_id
321 AND p_effective_date
322 BETWEEN peo.effective_start_date AND peo.effective_end_date;
323
324 BEGIN
325
326 OPEN pursued_apl_csr;
327 FETCH pursued_apl_csr INTO l_pursued_indicator;
328 CLOSE pursued_apl_csr;
329
330 RETURN NVL(l_pursued_indicator,0);
331
332 END is_pursued_apl;
333
334 /* Refreshes vacancy cache if necessary */
335 PROCEDURE check_vacancy_cache(p_vacancy_id IN NUMBER,
336 p_date_from IN DATE) IS
337
338 CURSOR vacancy_apl_details_csr IS
339 SELECT
340 AVG(DECODE(aac.current_status_code,
341 'END_SCCSS', aac.hire_date - p_date_from,
342 to_number(null))) days_to_recruit
343 ,AVG(DECODE(aac.current_status_code,
344 'END_SCCSS', aac.hire_date - aac.accepted_date,
345 to_number(null))) days_accept_to_hire
346 ,AVG(aac.accepted_date - p_date_from) days_to_accept
347 ,COUNT(aac.assignment_id) vacancy_apl_count
348 FROM hri_mb_apl_activity_v aac
349 WHERE fk_vacancy_id = p_vacancy_id; -- bug 4992287 performance enh.
350
351 BEGIN
352
353 IF (p_vacancy_id <> g_vacancy_id) THEN
354 OPEN vacancy_apl_details_csr;
355 FETCH vacancy_apl_details_csr INTO g_vacancy_days_to_hire,
356 g_vacancy_fill_to_hire,
357 g_vacancy_days_to_fill,
358 g_vacancy_apl_count;
359 CLOSE vacancy_apl_details_csr;
360 g_vacancy_id := p_vacancy_id;
361 END IF;
362
363 END check_vacancy_cache;
364
365 FUNCTION calc_avg_days_to_hire(p_vacancy_id IN NUMBER,
366 p_date_from IN DATE)
367 RETURN NUMBER IS
368
369 BEGIN
370
371 check_vacancy_cache(p_vacancy_id => p_vacancy_id,
372 p_date_from => p_date_from);
373
374 RETURN g_vacancy_days_to_hire;
375
376 END calc_avg_days_to_hire;
377
378 FUNCTION calc_avg_days_to_fill(p_vacancy_id IN NUMBER,
379 p_date_from IN DATE)
380 RETURN NUMBER IS
381
382 BEGIN
383
384 check_vacancy_cache(p_vacancy_id => p_vacancy_id,
385 p_date_from => p_date_from);
386
387 RETURN g_vacancy_days_to_fill;
388
389 END calc_avg_days_to_fill;
390
391 FUNCTION calc_avg_fill_to_hire(p_vacancy_id IN NUMBER,
392 p_date_from IN DATE)
393 RETURN NUMBER IS
394
395 BEGIN
396
397 check_vacancy_cache(p_vacancy_id => p_vacancy_id,
398 p_date_from => p_date_from);
399
400 RETURN g_vacancy_fill_to_hire;
401
402 END calc_avg_fill_to_hire;
403
404 FUNCTION calc_no_apls(p_vacancy_id IN NUMBER,
405 p_date_from IN DATE)
406 RETURN NUMBER IS
407
408 BEGIN
409
410 check_vacancy_cache(p_vacancy_id => p_vacancy_id,
411 p_date_from => p_date_from);
412
413 RETURN g_vacancy_apl_count;
414
415 END calc_no_apls;
416
417 END hri_opl_recruitment;