1 PACKAGE BODY PA_CANDIDATE_UTILS AS
2 -- $Header: PARCANUB.pls 120.5 2006/05/29 08:55:47 avaithia ship $
3
4 /* --------------------------------------------------------------------
5 FUNCTION: Get_Active_Candidates_Number
6 PURPOSE:
7 -------------------------------------------------------------------- */
8 FUNCTION Get_Active_Candidates_Number(p_assignment_id IN NUMBER)
9 RETURN NUMBER
10 IS
11 l_no_of_active_candidates NUMBER := 0;
12 BEGIN
13 SELECT no_of_active_candidates
14 INTO l_no_of_active_candidates
15 FROM pa_project_assignments
16 WHERE assignment_id = p_assignment_id;
17
18 IF l_no_of_active_candidates is null THEN
19 RETURN 0;
20 ELSE
21 RETURN l_no_of_active_candidates;
22 END IF;
23 EXCEPTION
24 WHEN OTHERS THEN
25 RETURN 0;
26 END Get_Active_Candidates_Number;
27
28 /* --------------------------------------------------------------------
29 FUNCTION: Get_Requirements_Of_Candidate
30 PURPOSE:
31 -------------------------------------------------------------------- */
32 FUNCTION Get_Requirements_Of_Candidate(p_resource_id IN NUMBER)
33 RETURN requirements_tbl
34 IS
35 i NUMBER := 0;
36 l_candidate_req_table requirements_tbl;
37
38 BEGIN
39 SELECT
40 assignment_id
41 BULK COLLECT INTO l_candidate_req_table
42 FROM pa_candidates
43 WHERE resource_id = p_resource_id;
44
45 RETURN l_candidate_req_table;
46 EXCEPTION
47 WHEN OTHERS THEN RAISE;
48
49 END Get_Requirements_Of_Candidate;
50
51
52 /* --------------------------------------------------------------------
53 FUNCTION: Get_Resource_Id
54 PURPOSE:
55 -------------------------------------------------------------------- */
56 FUNCTION Get_Resource_Id(p_person_id IN NUMBER)
57 RETURN NUMBER
58 IS
59 l_resource_id NUMBER := null;
60 BEGIN
61 SELECT resource_id
62 INTO l_resource_id
63 FROM pa_resources_denorm
64 WHERE person_id = p_person_id and rownum=1;
65
66 RETURN l_resource_id;
67 EXCEPTION
68 WHEN OTHERS THEN
69 RETURN null;
70 END Get_Resource_Id;
71
72 /* --------------------------------------------------------------------
73 FUNCTION: Check_Resource_Is_Candidate
74 PURPOSE: This API checks to see if the resource p_resource_id is a
75 candidate on the assignment p_assignment_id.
76 It returns 'Y', if the resource is a candidate.
77 It returns 'N', if the resource is not a candidate.
78 -------------------------------------------------------------------- */
79 FUNCTION Check_Resource_Is_Candidate(p_resource_id IN NUMBER,
80 p_assignment_id IN NUMBER)
81 RETURN VARCHAR2
82 IS
83 l_exists VARCHAR2(1) := 'N';
84 BEGIN
85 SELECT 'Y'
86 into l_exists
87 from pa_candidates
88 where assignment_id = p_assignment_id
89 and resource_id = p_resource_id;
90
91 RETURN 'Y';
92
93 EXCEPTION
94 WHEN OTHERS THEN
95 RETURN 'N';
96 END Check_Resource_Is_Candidate;
97
98 /* --------------------------------------------------------------------
99 PROCEDURE: Reverse_Candidate_Status
100 PURPOSE: This procedure will restore given candidate's status
101 to PENDING_REVIEW. It is called when when the cancel button on
102 page PA_SUBMIT_ASMT_APR_LAYOUT is clicked.
103 -------------------------------------------------------------------- */
104
105 PROCEDURE Reverse_Candidate_Status
106 (p_assignment_id IN NUMBER,
107 p_resource_id IN NUMBER,
108 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
109 x_error_message_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
110 IS
111 l_candidate_id NUMBER;
112 l_candidate_ranking NUMBER;
113 l_record_version_number NUMBER;
114 l_msg_count NUMBER := 0;
115 l_project_status_code VARCHAR2(30);
116 l_return_status VARCHAR2(1);
117 l_msg_data VARCHAR2(2000);
118
119 -- 4537865
120 l_new_record_version_number NUMBER;
121 BEGIN
122
123 -- Initialize the Error Stack
124 PA_DEBUG.init_err_stack('PA_CANDIDATE_PUB.Reverse_Candidate_Status');
125
126 -- initialize return_status to success
127 x_return_status := FND_API.G_RET_STS_SUCCESS;
128 x_error_message_code := NULL;
129
130 BEGIN
131 SELECT candidate_id,
132 candidate_ranking,
133 record_version_number
134 INTO l_candidate_id,
135 l_candidate_ranking,
136 l_record_version_number
137 FROM pa_candidates
138 WHERE assignment_id = p_assignment_id
139 AND resource_id = p_resource_id;
140 EXCEPTION
141 WHEN NO_DATA_FOUND THEN
142 -- No candiddate record exist for given resource_id
143 RETURN;
144 END;
145
146 SELECT project_status_code
147 INTO l_project_status_code
148 FROM PA_PROJECT_STATUSES
149 WHERE project_system_status_code = 'CANDIDATE_PENDING_REVIEW'
150 AND PREDEFINED_FLAG = 'Y' -- Added for bug 5222893
151 AND status_type = 'CANDIDATE'; -- Bug 4773033 CANDIDATE ROUTINES JOIN FOR PROJECT STATUSES
152
153 PA_CANDIDATE_PUB.Update_Candidate
154 (p_candidate_id => l_candidate_id,
155 p_status_code => l_project_status_code,
156 p_ranking => l_candidate_ranking,
157 p_change_reason_code => null,
158 p_record_version_number => l_record_version_number,
159 x_record_version_number => l_new_record_version_number, -- 4537865 : Changed from l_record_version_number to new variable
160 x_msg_count => l_msg_count,
161 x_msg_data => l_msg_data,
162 x_return_status => l_return_status);
163
164 -- 4537865 : Start
165 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
166 l_record_version_number := l_new_record_version_number ;
167 END IF;
168 -- End : 4537865
169 IF(l_return_status = FND_API.G_RET_STS_ERROR) THEN
170 RAISE FND_API.G_EXC_ERROR;
171 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
172 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
173 END IF;
174
175 EXCEPTION
176 WHEN FND_API.G_EXC_ERROR THEN
177 x_return_status := FND_API.G_RET_STS_ERROR;
178 x_error_message_code := l_msg_data;
179
180 WHEN OTHERS THEN
181 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
182 x_error_message_code := SQLCODE; -- 4537865
183 RAISE;
184
185 END Reverse_Candidate_Status;
186
187
188 /* --------------------------------------------------------------------
189 FUNCTION: Get_Candidate_Score
190 PURPOSE: This API calculates the candidate score of a resource.
191 Expects p_resource_id, p_person_id, p_assignment_id
192 and p_resource_job_level to be always passed in.
193 It returns the Candidate Score.
194 -------------------------------------------------------------------- */
195 FUNCTION Get_Candidate_Score(p_resource_id IN NUMBER,
196 p_person_id IN NUMBER,
197 p_assignment_id IN NUMBER,
198 p_project_id IN NUMBER,
199 p_competence_match_count IN VARCHAR2,
200 p_competence_match IN NUMBER,
201 p_competence_count IN NUMBER,
202 p_availability IN NUMBER,
203 p_resource_job_level IN NUMBER,
204 p_min_job_level IN NUMBER,
205 p_max_job_level IN NUMBER,
206 p_comp_match_weighting IN NUMBER,
207 p_avail_match_weighting IN NUMBER,
208 p_job_level_match_weighting IN NUMBER)
209 RETURN NUMBER
210 IS
211
212 l_candidate_score NUMBER := null;
213 l_competence_count NUMBER := null;
214 l_competence_match NUMBER := null;
215 l_availability NUMBER := null;
216 l_min_job_level NUMBER := null;
217 l_max_job_level NUMBER := null;
218 l_comp_match_weighting NUMBER := null;
219 l_avail_match_weighting NUMBER := null;
220 l_job_level_match_weighting NUMBER := null;
221 l_comp_match_count VARCHAR2(20) := null;
222 l_target_job_level NUMBER := null;
223 l_job_level_match_denorm NUMBER := null;
224 l_job_level_match NUMBER := null;
225 l_total_weightings NUMBER := 0;
226
227 CURSOR get_requirement_details IS
228 SELECT min_resource_job_level, max_resource_job_level, competence_match_weighting, availability_match_weighting, job_level_match_weighting
229 FROM pa_project_assignments
230 WHERE assignment_id = p_assignment_id;
231
232 BEGIN
233
234 --get max min job levels and match weightings from the requirement
235 --if any of these are not passed in
236 IF (p_min_job_level IS NULL OR p_max_job_level IS NULL OR
237 p_comp_match_weighting IS NULL OR p_avail_match_weighting IS NULL OR
238 p_job_level_match_weighting IS NULL) THEN
239
240 OPEN get_requirement_details;
241 FETCH get_requirement_details into l_min_job_level, l_max_job_level, l_comp_match_weighting, l_avail_match_weighting, l_job_level_match_weighting;
242 CLOSE get_requirement_details;
243
244 END IF;
245
246 --use the parameter values for job levels if they are passed in
247 IF p_min_job_level IS NOT NULL AND p_max_job_level IS NOT NULL THEN
248 l_min_job_level := p_min_job_level;
249 l_max_job_level := p_max_job_level;
250 END IF;
251
252 --use the parameter values for match weightings if they are passed in
253 IF p_comp_match_weighting IS NOT NULL AND
254 p_avail_match_weighting IS NOT NULL AND
255 p_job_level_match_weighting IS NOT NULL THEN
256 l_comp_match_weighting := p_comp_match_weighting;
257 l_avail_match_weighting := p_avail_match_weighting;
258 l_job_level_match_weighting := p_job_level_match_weighting;
259 END IF;
260
261 -- return candidate score = 0 if the weightings add up to zero
262 l_total_weightings := l_comp_match_weighting + l_avail_match_weighting + l_job_level_match_weighting;
263
264 IF l_total_weightings = 0 THEN
265 l_candidate_score := 0;
266 RETURN l_candidate_score;
267 END IF;
268
269 --obtain competence match and count
270 IF p_competence_count IS NULL OR p_competence_match IS NULL THEN
271
272 IF p_competence_match_count IS NULL THEN
273 l_comp_match_count := PA_CANDIDATE_PUB.Get_Competence_Match(p_person_id,
274 p_assignment_id);
275 ELSE
276 l_comp_match_count := p_competence_match_count;
277 END IF;
278
279 l_competence_match := TO_NUMBER(SUBSTR(l_comp_match_count, 1, INSTR(l_comp_match_count, '/') -1));
280 l_competence_count := TO_NUMBER(SUBSTR(l_comp_match_count, INSTR(l_comp_match_count, '/')+1, LENGTH(l_comp_match_count)));
281
282 ELSE
283 l_competence_match := p_competence_match;
284 l_competence_count := p_competence_count;
285 END IF;
286
287 IF l_competence_count = 0 THEN
288 l_competence_count := 1;
289 END IF;
290
291 --obtain availability
292 IF p_availability IS NULL THEN
293 l_availability := nvl(PA_CANDIDATE_PUB.Check_Availability(p_resource_id,
294 p_assignment_id,
295 p_project_id),
296 0);
297
298 ELSE
299 l_availability := p_availability;
300 END IF;
301
302 -- Job Level Match is zero if resource does not have a job level
303 IF p_resource_job_level IS NOT NULL THEN
304
305 --calculate target Job Level
306 l_target_job_level := (l_max_job_level + l_min_job_level)/2;
307
308 --calculate the job level match value
309 IF p_resource_job_level > l_max_job_level OR
310 p_resource_job_level < l_min_job_level THEN
311 l_job_level_match := 0;
312 ELSE
313 l_job_level_match := 100;
314 END IF;
315
316 /*
317 -- Temporarily give either 0 or 100 as job level match
318 -- instead of calculating job level match using a formula
319 -- due to open issue about HR job level ranges
320
321 ELSIF p_resource_job_level = l_target_job_level THEN
322 l_job_level_match := 100;
323 ELSE
324 --calculate the denominator of the job level match formula
325 l_job_level_match_denorm := (l_max_job_level - l_target_job_level) * 1.01;
326
327 l_job_level_match := (1- ABS(p_resource_job_level-l_target_job_level)/l_job_level_match_denorm) * 100;
328 END IF;
329 */
330
331 ELSE
332 l_job_level_match := 0;
333 END IF;
334
335
336 --calculate candidate score
337 l_candidate_score := TRUNC( 100 * (
338 (l_competence_match/l_competence_count * 100 * l_comp_match_weighting +
339 l_availability * l_avail_match_weighting +
340 l_job_level_match * l_job_level_match_weighting) /
341 ((l_comp_match_weighting +
342 l_avail_match_weighting +
343 l_job_level_match_weighting) * 100)));
344
345 RETURN l_candidate_score;
346 EXCEPTION
347 WHEN OTHERS THEN
348 RAISE;
349
350 END Get_Candidate_Score;
351
352 /* --------------------------------------------------------------------
353 FUNCTION: Get_Nominator_Name
354 PURPOSE: This API returns nominator of the candidate
355 Expects nominator's person Id
356 It returns the name of the nominator.
357 -------------------------------------------------------------------- */
358
359 FUNCTION Get_Nominator_Name(p_nominated_by_person_id IN NUMBER)
360 RETURN VARCHAR2
361 IS
362 l_nominator_name VARCHAR2(100) := null;
363 BEGIN
364
365 IF p_nominated_by_person_id IS NULL THEN
366 l_nominator_name := FND_MESSAGE.GET_STRING('PA', 'PA_AUTOMATED_SEARCH_PROCESS');
367 /*
368 SELECT message_text
369 INTO l_nominator_name
370 FROM fnd_new_messages
371 WHERE message_name = 'PA_AUTOMATED_SEARCH_PROCESS';
372 */
373 ELSE
374 SELECT full_name
375 INTO l_nominator_name
376 FROM per_people_x
377 WHERE person_id = p_nominated_by_person_id;
378 END IF;
379
380 RETURN l_nominator_name;
381 EXCEPTION
382 WHEN OTHERS THEN
383 RETURN null;
384 END Get_Nominator_Name;
385
386 /* --------------------------------------------------------------------
387 FUNCTION: Get_Candidate_Nominations
388 PURPOSE: This API returns how many times he/she has been
389 nominated among all REQUIREMENTS.
390 -------------------------------------------------------------------- */
391 FUNCTION Get_Candidate_Nominations (p_resource_id IN NUMBER)
392 RETURN NUMBER
393 IS
394 l_num_nomination NUMBER := 0;
395 BEGIN
396
397 SELECT count(*)
398 INTO l_num_nomination
399 FROM pa_candidates can,
400 pa_project_assignments asmt,
401 pa_project_statuses ps
402 WHERE can.assignment_id = asmt.assignment_id
403 AND can.status_code = ps.project_status_code
404 AND can.resource_id = p_resource_id
405 AND asmt.assignment_type = 'OPEN_ASSIGNMENT'
406 AND ps.status_type = 'CANDIDATE'
407 AND ps.project_system_status_code IN ('CANDIDATE_PENDING_REVIEW',
408 'CANDIDATE_UNDER_REVIEW',
409 'CANDIDATE_SUITABLE',
410 'CANDIDATE_SYSTEM_NOMINATED');
411
412 RETURN l_num_nomination;
413 EXCEPTION
414 WHEN OTHERS THEN
415 RETURN 0;
416 END Get_Candidate_Nominations;
417
418 /* --------------------------------------------------------------------
422 -------------------------------------------------------------------- */
419 FUNCTION: Get_Candidate_Qualifieds
420 PURPOSE: This API returns how many times he/she has been
421 nominated as qualified assignment for REQUIREMENTS.
423 FUNCTION Get_Candidate_Qualifieds (p_resource_id IN NUMBER)
424 RETURN NUMBER
425 IS
426 l_num_qualifieds NUMBER := 0;
427 BEGIN
428
429 SELECT count(*)
430 INTO l_num_qualifieds
431 FROM pa_candidates can,
432 pa_project_assignments asmt,
433 pa_project_statuses ps
434 WHERE can.assignment_id = asmt.assignment_id
435 AND can.status_code = ps.project_status_code
436 AND asmt.assignment_type = 'OPEN_ASSIGNMENT'
437 AND can.resource_id = p_resource_id
438 AND ps.status_type = 'CANDIDATE'
439 AND ps.project_system_status_code = 'CANDIDATE_SYSTEM_QUALIFIED';
440
441 RETURN l_num_qualifieds;
442 EXCEPTION
443 WHEN OTHERS THEN
444 RETURN 0;
445 END Get_Candidate_Qualifieds;
446
447 /* --------------------------------------------------------------------
448 FUNCTION: Update_No_Of_Active_Candidates
449 PURPOSE: This API updates the no_of_active_candidates column in the
450 pa_project_assignments table. It will be called when the user
451 changes the duration for the requirements.
452 -------------------------------------------------------------------- */
453 PROCEDURE Update_No_Of_Active_Candidates (p_assignment_id IN NUMBER,
454 x_return_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
455 IS
456 l_no_of_active_candidates NUMBER := 0;
457 l_record_version_number NUMBER := 0;
458 l_return_status VARCHAR2(1);
459 BEGIN
460
461 PA_DEBUG.init_err_stack('PA_CANDIDATE_UTIL.Update_No_Of_Active_Candidates');
462
463 x_return_status := FND_API.G_RET_STS_SUCCESS;
464
465 SELECT count(*)
466 INTO l_no_of_active_candidates
467 FROM pa_candidates cand,
468 pa_project_assignments asmt,
469 pa_resources_denorm res,
470 pa_project_statuses ps
471 WHERE cand.assignment_id = p_assignment_id
472 AND cand.assignment_id = asmt.assignment_id
473 AND cand.resource_id = res.resource_id
474 AND asmt.start_date BETWEEN res.resource_effective_start_date AND
475 NVL(res.resource_effective_end_date, asmt.start_date+1)
476 AND cand.status_code = ps.project_status_code
477 AND ps.status_type = 'CANDIDATE'
478 AND ps.project_system_status_code in ('CANDIDATE_PENDING_REVIEW',
479 'CANDIDATE_UNDER_REVIEW',
480 'CANDIDATE_SYSTEM_NOMINATED',
481 'CANDIDATE_SUITABLE')
482 AND res.schedulable_flag = 'Y';
483
484 SELECT record_version_number
485 INTO l_record_version_number
486 FROM pa_project_assignments
487 WHERE assignment_id = p_assignment_id;
488
489 pa_project_assignments_pkg.Update_row(
490 p_assignment_id => p_assignment_id,
491 p_no_of_active_candidates => l_no_of_active_candidates,
492 p_record_version_number => l_record_version_number,
493 x_return_status => l_return_status );
494
495 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
496 RAISE FND_API.G_EXC_ERROR;
497 END IF;
498
499 EXCEPTION
500 WHEN OTHERS THEN
501 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
502
503 fnd_msg_pub.add_exc_msg
504 (p_pkg_name => 'PA_CANDIDATE_UTIL',
505 p_procedure_name => 'Update_No_Of_Active_Candidates' );
506
507 RAISE;
508 END Update_No_Of_Active_Candidates;
509
510 END PA_CANDIDATE_UTILS ;
511
[Home] [Help]