1 PACKAGE BODY hri_oltp_disc_rctmnt AS
2 /* $Header: hriodrec.pkb 120.0 2005/05/29 07:28:35 appldev noship $ */
3
4 /******************************************************************************/
5 /* This function returns the number of applicants who have been hired into a */
6 /* vacancy */
7 /******************************************************************************/
8 FUNCTION get_vacancy_hire_count(p_vacancy IN VARCHAR2,
9 p_business_group IN VARCHAR2,
10 p_requisition IN VARCHAR2,
11 p_applicant_number IN VARCHAR2)
12 RETURN NUMBER IS
13 --
14 l_v_hires NUMBER;
15 --
16 BEGIN
17 --
18 SELECT COUNT(p.person_id)
19 INTO l_v_hires
20 FROM hr_all_organization_units b,
21 per_requisitions r,
22 per_all_vacancies v,
23 per_all_assignments_f a,
24 per_all_people_f p
25 WHERE TRUNC(SYSDATE) BETWEEN p.effective_start_date
26 AND p.effective_end_date
27 /* bug 2033292 */
28 AND TRUNC(SYSDATE) BETWEEN a.effective_start_date
29 AND a.effective_end_date
30 /* bug 2033292 */
31 AND p.employee_number IS NOT NULL
32 AND p.person_id = a.person_id
33 AND a.vacancy_id = v.vacancy_id
34 AND v.name = p_vacancy
35 AND v.requisition_id = r.requisition_id
36 AND r.name = p_requisition
37 AND b.organization_id = v.business_group_id
38 AND b.organization_id = b.business_group_id
39 AND a.assignment_type = 'E'
40 AND b.name = p_business_group;
41 --
42 RETURN l_v_hires;
43 --
44 EXCEPTION
45 --
46 WHEN OTHERS
47 THEN
48 --
49 RETURN 0; -- Default vacancy hire count to 0
50 --
51 END get_vacancy_hire_count;
52
53
54 /******************************************************************************/
55 /* This function returns the number of applicants who have been made an offer */
56 /* for a vacancy */
57 /******************************************************************************/
58 FUNCTION get_vacancy_offer_count(p_vacancy IN VARCHAR2,
59 p_business_group IN VARCHAR2,
60 p_requisition IN VARCHAR2)
61 RETURN NUMBER IS
62 --
63 l_v_offers NUMBER;
64 --
65 BEGIN
66 --
67 SELECT COUNT(p.person_id)
68 INTO l_v_offers
69 FROM hr_all_organization_units b,
70 per_assignment_status_types ast,
71 per_requisitions r,
72 per_all_vacancies v,
73 per_all_assignments_f a,
74 per_all_people_f p
75 WHERE TRUNC(SYSDATE) BETWEEN p.effective_start_date
76 AND p.effective_end_date
77 /* bug 2033292 */
78 AND TRUNC(SYSDATE) BETWEEN a.effective_start_date
79 AND a.effective_end_date
80 /* bug 2033292 */
81 AND p.applicant_number IS NOT NULL
82 AND p.person_id = a.person_id
83 AND a.assignment_type = 'A'
84 AND a.assignment_status_type_id =
85 ast.assignment_status_type_id
86 AND ast.per_system_status = 'OFFER'
87 AND a.vacancy_id = v.vacancy_id
88 AND v.name = p_vacancy
89 AND v.requisition_id = r.requisition_id
90 AND r.name = p_requisition
91 AND b.organization_id = v.business_group_id
92 AND b.organization_id = b.business_group_id
93 AND b.name = p_business_group;
94 --
95 RETURN l_v_offers;
96 --
97 EXCEPTION
98 --
99 WHEN OTHERS
100 THEN
101 --
102 RETURN 0; -- Default vacancy hire count to 0
103 --
104 END get_vacancy_offer_count;
105
106
107 /******************************************************************************/
108 /* This function returns the number of applicants who have been hired via the */
109 /* recruitment activity */
110 /******************************************************************************/
111 FUNCTION get_rec_act_hire_count(p_rec_activity IN VARCHAR2,
112 p_business_group IN VARCHAR2,
113 p_applicant_number IN VARCHAR2)
114 RETURN NUMBER IS
115 --
116 l_ra_hires NUMBER;
117 --
118 BEGIN
119 --
120 SELECT COUNT(p.person_id)
121 INTO l_ra_hires
122 FROM hr_all_organization_units b,
123 per_recruitment_activities r,
124 per_all_assignments_f a,
125 per_all_people_f p
126 WHERE TRUNC(SYSDATE) BETWEEN p.effective_start_date
127 AND p.effective_end_date
128 /* bug 2033292 */
129 AND TRUNC(SYSDATE) BETWEEN a.effective_start_date
130 AND a.effective_end_date
131 /* bug 2033292 */
132 AND p.employee_number IS NOT NULL
133 AND p.person_id = a.person_id
134 AND a.recruitment_activity_id = r.recruitment_activity_id
135 AND r.name = p_rec_activity
136 AND b.organization_id = r.business_group_id
137 AND b.organization_id = b.business_group_id
138 AND a.assignment_type = 'E'
139 AND b.name = p_business_group;
140 --
141 RETURN l_ra_hires;
142 --
143 EXCEPTION
144 --
145 WHEN OTHERS
146 THEN
147 --
148 RETURN 0; -- Default vacancy hire count to 0
149 --
150 END get_rec_act_hire_count;
151
152
153 /******************************************************************************/
154 /* This function returns the number of applicants who have been made offers */
155 /* via the recruitment activity */
156 /******************************************************************************/
157 FUNCTION get_rec_act_offer_count(p_rec_activity IN VARCHAR2,
158 p_business_group IN VARCHAR2)
159 RETURN NUMBER IS
160 --
161 l_ra_offers NUMBER;
162 --
163 BEGIN
164 --
165 SELECT COUNT(p.person_id)
166 INTO l_ra_offers
167 FROM hr_all_organization_units b,
168 per_assignment_status_types ast,
169 per_recruitment_activities r,
170 per_all_assignments_f a,
171 per_all_people_f p
172 WHERE TRUNC(SYSDATE) BETWEEN p.effective_start_date
173 AND p.effective_end_date
174 /* bug 2033292 */
175 AND TRUNC(SYSDATE) BETWEEN a.effective_start_date
176 AND a.effective_end_date
177 /* bug 2033292 */
178 AND p.applicant_number IS NOT NULL
179 AND p.person_id = a.person_id
180 AND a.assignment_type = 'A'
181 AND a.assignment_status_type_id =
182 ast.assignment_status_type_id
183 AND ast.per_system_status = 'OFFER'
184 AND a.recruitment_activity_id = r.recruitment_activity_id
185 AND r.name = p_rec_activity
186 AND b.organization_id = r.business_group_id
187 AND b.organization_id = b.business_group_id
188 AND b.name = p_business_group;
189 --
190 RETURN l_ra_offers;
191 --
192 EXCEPTION
193 --
194 WHEN OTHERS
195 THEN
196 --
197 RETURN 0; -- Default vacancy hire count to 0
198 --
199 END get_rec_act_offer_count;
200
201
202 /******************************************************************************/
203 /* This function returns the number of applicants who have been hired into */
204 /* the vacancy via the recruitment activity */
205 /******************************************************************************/
206 FUNCTION get_rec_act_vac_hire_count(p_rec_activity IN VARCHAR2,
207 p_vacancy IN VARCHAR2,
208 p_business_group IN VARCHAR2,
209 p_applicant_number IN VARCHAR2)
210 RETURN NUMBER IS
211 --
212 l_rav_hires NUMBER;
213 --
214 BEGIN
215 --
216 SELECT COUNT(p.person_id)
217 INTO l_rav_hires
218 FROM hr_all_organization_units b,
219 per_all_vacancies v,
220 per_recruitment_activities r,
221 per_all_assignments_f a,
222 per_all_people_f p
223 WHERE TRUNC(SYSDATE) BETWEEN p.effective_start_date
224 AND p.effective_end_date
225 /* bug 2033292 */
226 AND TRUNC(SYSDATE) BETWEEN a.effective_start_date
227 AND a.effective_end_date
228 /* bug 2033292 */
229 AND p.employee_number IS NOT NULL
230 AND p.person_id = a.person_id
231 AND a.vacancy_id = v.vacancy_id
232 AND a.recruitment_activity_id = r.recruitment_activity_id
233 AND v.name = p_vacancy
234 AND r.name = p_rec_activity
235 AND b.organization_id = v.business_group_id
236 AND b.organization_id = r.business_group_id
237 AND b.organization_id = b.business_group_id
238 AND a.assignment_type = 'E'
239 AND b.name = p_business_group;
240 --
241 RETURN l_rav_hires;
242 --
243 EXCEPTION
244 --
245 WHEN OTHERS
246 THEN
247 --
248 RETURN 0; -- Default vacancy hire count to 0
249 --
250 END get_rec_act_vac_hire_count;
251
252
253 /******************************************************************************/
254 /* This function returns the number of applicants who have been made offers */
255 /* for the vacancy via the recruitment activity */
256 /******************************************************************************/
257 FUNCTION get_rec_act_vac_offer_count(p_rec_activity IN VARCHAR2,
258 p_vacancy IN VARCHAR2,
259 p_business_group IN VARCHAR2)
260 RETURN NUMBER IS
261 --
262 l_rav_offers NUMBER;
263 --
264 BEGIN
265 --
266 SELECT COUNT(p.person_id)
267 INTO l_rav_offers
268 FROM hr_all_organization_units b,
269 per_assignment_status_types ast,
270 per_all_vacancies v,
271 per_recruitment_activities r,
272 per_all_assignments_f a,
273 per_all_people_f p
274 WHERE TRUNC(SYSDATE) BETWEEN p.effective_start_date
275 AND p.effective_end_date
276 /* bug 2033292 */
277 AND TRUNC(SYSDATE) BETWEEN a.effective_start_date
278 AND a.effective_end_date
279 /* bug 2033292 */
280 AND p.applicant_number IS NOT NULL
281 AND p.person_id = a.person_id
282 AND a.assignment_type = 'A'
283 AND a.assignment_status_type_id =
284 ast.assignment_status_type_id
285 AND ast.per_system_status = 'OFFER'
286 AND a.recruitment_activity_id = r.recruitment_activity_id
287 AND a.vacancy_id = v.vacancy_id
288 AND r.name = p_rec_activity
289 AND v.name = p_vacancy
290 AND b.organization_id = r.business_group_id
291 AND b.organization_id = v.business_group_id
292 AND b.organization_id = b.business_group_id
293 AND b.name = p_business_group;
294 --
295 RETURN l_rav_offers;
296 --
297 EXCEPTION
298 --
299 WHEN OTHERS
300 THEN
301 --
302 RETURN 0; -- Default vacancy hire count to 0
303 --
304 END get_rec_act_vac_offer_count;
305
306
307 /******************************************************************************/
308 /* This function returns the hiring cost per head of hiring employees who are */
309 /* still employed */
310 /******************************************************************************/
311 FUNCTION get_hiring_cost_current_emp(p_rec_act_id IN NUMBER,
312 p_actual_cost IN NUMBER)
313 RETURN NUMBER IS
314 --
315 l_cost NUMBER;
316 l_emp_count INTEGER;
317 --
318 BEGIN
319 --
320 -- Count number of current emps hired through the recruitment activity
321 --
322 SELECT COUNT(person_id)
323 INTO l_emp_count
324 FROM per_all_assignments_f a
325 WHERE TRUNC(SYSDATE) BETWEEN a.effective_start_date AND a.effective_end_date
326 AND a.assignment_type = 'E'
327 AND a.recruitment_activity_id = p_rec_act_id;
328 --
329 IF l_emp_count = 0 THEN
330 l_cost := NULL;
331 ELSE
332 l_cost := p_actual_cost / l_emp_count;
333 END IF;
334 --
335 RETURN l_cost;
336 --
337 EXCEPTION
338 --
339 WHEN OTHERS
340 THEN
341 --
342 RETURN 0; -- Default vacancy hire count to 0
343 --
344 END get_hiring_cost_current_emp;
345
346 /******************************************************************************/
347 /* This function returns 'Y' if the vacancy is currently active and 'N' */
348 /* otherwise */
349 /******************************************************************************/
350 FUNCTION check_active_vacancy(p_date_from IN DATE,
351 p_date_to IN DATE)
352 RETURN VARCHAR2 IS
353 --
354 CURSOR check_vacancy IS
355 SELECT 'Y'
356 FROM dual
357 WHERE TRUNC(SYSDATE) BETWEEN p_date_from
358 AND NVL(p_date_to,SYSDATE);
359 --
360 l_active_flag VARCHAR2(1) := 'N';
361 --
362 BEGIN
363 --
364 OPEN check_vacancy;
365 FETCH check_vacancy INTO l_active_flag;
366 CLOSE check_vacancy;
367 --
368 RETURN l_active_flag;
369 --
370 EXCEPTION
371 --
372 WHEN OTHERS
373 THEN
374 --
375 RETURN ''; -- Default vacancy hire count to 0
376 --
377 END check_active_vacancy;
378 --
379 END hri_oltp_disc_rctmnt;
[Home] [Help]