1 PACKAGE BODY PA_CANDIDATE_UTILS AS
2 -- $Header: PARCANUB.pls 120.5.12010000.2 2010/03/23 12:45:32 vgovvala 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
122 l_candidate_in_rec PA_RES_MANAGEMENT_AMG_PUB.CANDIDATE_IN_REC_TYPE; -- Added for bug 8339510
123
124
125 BEGIN
126
127 -- Initialize the Error Stack
128 PA_DEBUG.init_err_stack('PA_CANDIDATE_PUB.Reverse_Candidate_Status');
129
130 -- initialize return_status to success
131 x_return_status := FND_API.G_RET_STS_SUCCESS;
132 x_error_message_code := NULL;
133
134 BEGIN
135 SELECT candidate_id,
136 candidate_ranking,
137 record_version_number
138 INTO l_candidate_id,
139 l_candidate_ranking,
140 l_record_version_number
141 FROM pa_candidates
142 WHERE assignment_id = p_assignment_id
143 AND resource_id = p_resource_id;
144 EXCEPTION
145 WHEN NO_DATA_FOUND THEN
146 -- No candiddate record exist for given resource_id
147 RETURN;
148 END;
149
150 SELECT project_status_code
151 INTO l_project_status_code
152 FROM PA_PROJECT_STATUSES
153 WHERE project_system_status_code = 'CANDIDATE_PENDING_REVIEW'
154 AND PREDEFINED_FLAG = 'Y' -- Added for bug 5222893
155 AND status_type = 'CANDIDATE'; -- Bug 4773033 CANDIDATE ROUTINES JOIN FOR PROJECT STATUSES
156
157 PA_CANDIDATE_PUB.Update_Candidate
158 (p_candidate_id => l_candidate_id,
159 p_status_code => l_project_status_code,
160 p_ranking => l_candidate_ranking,
161 p_change_reason_code => null,
162 p_record_version_number => l_record_version_number,
163 -- Added for bug 8339510
164 p_attribute_category => l_candidate_in_rec.attribute_category,
165 p_attribute1 => l_candidate_in_rec.attribute1,
166 p_attribute2 => l_candidate_in_rec.attribute2,
167 p_attribute3 => l_candidate_in_rec.attribute3,
168 p_attribute4 => l_candidate_in_rec.attribute4,
169 p_attribute5 => l_candidate_in_rec.attribute5,
170 p_attribute6 => l_candidate_in_rec.attribute6,
171 p_attribute7 => l_candidate_in_rec.attribute7,
172 p_attribute8 => l_candidate_in_rec.attribute8,
173 p_attribute9 => l_candidate_in_rec.attribute9,
174 p_attribute10 => l_candidate_in_rec.attribute10,
175 p_attribute11 => l_candidate_in_rec.attribute11,
176 p_attribute12 => l_candidate_in_rec.attribute12,
177 p_attribute13 => l_candidate_in_rec.attribute13,
178 p_attribute14 => l_candidate_in_rec.attribute14,
179 p_attribute15 => l_candidate_in_rec.attribute15,
180 x_record_version_number => l_new_record_version_number, -- 4537865 : Changed from l_record_version_number to new variable
181 x_msg_count => l_msg_count,
182 x_msg_data => l_msg_data,
183 x_return_status => l_return_status);
184
185 -- 4537865 : Start
186 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
187 l_record_version_number := l_new_record_version_number ;
188 END IF;
189 -- End : 4537865
190 IF(l_return_status = FND_API.G_RET_STS_ERROR) THEN
191 RAISE FND_API.G_EXC_ERROR;
192 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
193 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
194 END IF;
195
196 EXCEPTION
197 WHEN FND_API.G_EXC_ERROR THEN
198 x_return_status := FND_API.G_RET_STS_ERROR;
199 x_error_message_code := l_msg_data;
200
201 WHEN OTHERS THEN
202 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
203 x_error_message_code := SQLCODE; -- 4537865
204 RAISE;
205
206 END Reverse_Candidate_Status;
207
208
209 /* --------------------------------------------------------------------
210 FUNCTION: Get_Candidate_Score
211 PURPOSE: This API calculates the candidate score of a resource.
212 Expects p_resource_id, p_person_id, p_assignment_id
213 and p_resource_job_level to be always passed in.
214 It returns the Candidate Score.
215 -------------------------------------------------------------------- */
216 FUNCTION Get_Candidate_Score(p_resource_id IN NUMBER,
217 p_person_id IN NUMBER,
218 p_assignment_id IN NUMBER,
219 p_project_id IN NUMBER,
220 p_competence_match_count IN VARCHAR2,
221 p_competence_match IN NUMBER,
222 p_competence_count IN NUMBER,
223 p_availability IN NUMBER,
224 p_resource_job_level IN NUMBER,
225 p_min_job_level IN NUMBER,
226 p_max_job_level IN NUMBER,
227 p_comp_match_weighting IN NUMBER,
228 p_avail_match_weighting IN NUMBER,
229 p_job_level_match_weighting IN NUMBER)
230 RETURN NUMBER
231 IS
232
233 l_candidate_score NUMBER := null;
234 l_competence_count NUMBER := null;
235 l_competence_match NUMBER := null;
236 l_availability NUMBER := null;
237 l_min_job_level NUMBER := null;
238 l_max_job_level NUMBER := null;
239 l_comp_match_weighting NUMBER := null;
240 l_avail_match_weighting NUMBER := null;
241 l_job_level_match_weighting NUMBER := null;
242 l_comp_match_count VARCHAR2(20) := null;
243 l_target_job_level NUMBER := null;
244 l_job_level_match_denorm NUMBER := null;
245 l_job_level_match NUMBER := null;
246 l_total_weightings NUMBER := 0;
247
248 CURSOR get_requirement_details IS
249 SELECT min_resource_job_level, max_resource_job_level, competence_match_weighting, availability_match_weighting, job_level_match_weighting
250 FROM pa_project_assignments
251 WHERE assignment_id = p_assignment_id;
252
253 BEGIN
254
255 --get max min job levels and match weightings from the requirement
256 --if any of these are not passed in
257 IF (p_min_job_level IS NULL OR p_max_job_level IS NULL OR
258 p_comp_match_weighting IS NULL OR p_avail_match_weighting IS NULL OR
259 p_job_level_match_weighting IS NULL) THEN
260
261 OPEN get_requirement_details;
262 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;
263 CLOSE get_requirement_details;
264
265 END IF;
266
267 --use the parameter values for job levels if they are passed in
268 IF p_min_job_level IS NOT NULL AND p_max_job_level IS NOT NULL THEN
269 l_min_job_level := p_min_job_level;
270 l_max_job_level := p_max_job_level;
271 END IF;
272
273 --use the parameter values for match weightings if they are passed in
274 IF p_comp_match_weighting IS NOT NULL AND
275 p_avail_match_weighting IS NOT NULL AND
276 p_job_level_match_weighting IS NOT NULL THEN
277 l_comp_match_weighting := p_comp_match_weighting;
278 l_avail_match_weighting := p_avail_match_weighting;
279 l_job_level_match_weighting := p_job_level_match_weighting;
280 END IF;
281
282 -- return candidate score = 0 if the weightings add up to zero
283 l_total_weightings := l_comp_match_weighting + l_avail_match_weighting + l_job_level_match_weighting;
284
285 IF l_total_weightings = 0 THEN
286 l_candidate_score := 0;
287 RETURN l_candidate_score;
288 END IF;
289
290 --obtain competence match and count
291 IF p_competence_count IS NULL OR p_competence_match IS NULL THEN
292
293 IF p_competence_match_count IS NULL THEN
294 l_comp_match_count := PA_CANDIDATE_PUB.Get_Competence_Match(p_person_id,
295 p_assignment_id);
296 ELSE
297 l_comp_match_count := p_competence_match_count;
298 END IF;
299
300 l_competence_match := TO_NUMBER(SUBSTR(l_comp_match_count, 1, INSTR(l_comp_match_count, '/') -1));
301 l_competence_count := TO_NUMBER(SUBSTR(l_comp_match_count, INSTR(l_comp_match_count, '/')+1, LENGTH(l_comp_match_count)));
302
303 ELSE
304 l_competence_match := p_competence_match;
305 l_competence_count := p_competence_count;
306 END IF;
307
308 IF l_competence_count = 0 THEN
309 l_competence_count := 1;
310 END IF;
311
312 --obtain availability
313 IF p_availability IS NULL THEN
314 l_availability := nvl(PA_CANDIDATE_PUB.Check_Availability(p_resource_id,
315 p_assignment_id,
316 p_project_id),
317 0);
318
319 ELSE
320 l_availability := p_availability;
321 END IF;
322
323 -- Job Level Match is zero if resource does not have a job level
324 IF p_resource_job_level IS NOT NULL THEN
325
326 --calculate target Job Level
327 l_target_job_level := (l_max_job_level + l_min_job_level)/2;
328
329 --calculate the job level match value
330 IF p_resource_job_level > l_max_job_level OR
331 p_resource_job_level < l_min_job_level THEN
332 l_job_level_match := 0;
333 ELSE
334 l_job_level_match := 100;
335 END IF;
336
337 /*
338 -- Temporarily give either 0 or 100 as job level match
339 -- instead of calculating job level match using a formula
340 -- due to open issue about HR job level ranges
341
342 ELSIF p_resource_job_level = l_target_job_level THEN
343 l_job_level_match := 100;
344 ELSE
345 --calculate the denominator of the job level match formula
346 l_job_level_match_denorm := (l_max_job_level - l_target_job_level) * 1.01;
347
348 l_job_level_match := (1- ABS(p_resource_job_level-l_target_job_level)/l_job_level_match_denorm) * 100;
349 END IF;
350 */
351
352 ELSE
353 l_job_level_match := 0;
354 END IF;
355
356
357 --calculate candidate score
358 l_candidate_score := TRUNC( 100 * (
359 (l_competence_match/l_competence_count * 100 * l_comp_match_weighting +
360 l_availability * l_avail_match_weighting +
361 l_job_level_match * l_job_level_match_weighting) /
362 ((l_comp_match_weighting +
363 l_avail_match_weighting +
364 l_job_level_match_weighting) * 100)));
365
366 RETURN l_candidate_score;
367 EXCEPTION
368 WHEN OTHERS THEN
369 RAISE;
370
371 END Get_Candidate_Score;
372
373 /* --------------------------------------------------------------------
374 FUNCTION: Get_Nominator_Name
375 PURPOSE: This API returns nominator of the candidate
376 Expects nominator's person Id
377 It returns the name of the nominator.
378 -------------------------------------------------------------------- */
379
380 FUNCTION Get_Nominator_Name(p_nominated_by_person_id IN NUMBER)
381 RETURN VARCHAR2
382 IS
383 l_nominator_name VARCHAR2(100) := null;
384 BEGIN
385
386 IF p_nominated_by_person_id IS NULL THEN
387 l_nominator_name := FND_MESSAGE.GET_STRING('PA', 'PA_AUTOMATED_SEARCH_PROCESS');
388 /*
389 SELECT message_text
390 INTO l_nominator_name
391 FROM fnd_new_messages
392 WHERE message_name = 'PA_AUTOMATED_SEARCH_PROCESS';
393 */
394 ELSE
395 SELECT full_name
396 INTO l_nominator_name
397 FROM per_people_x
398 WHERE person_id = p_nominated_by_person_id;
399 END IF;
400
401 RETURN l_nominator_name;
402 EXCEPTION
403 WHEN OTHERS THEN
404 RETURN null;
405 END Get_Nominator_Name;
406
407 /* --------------------------------------------------------------------
408 FUNCTION: Get_Candidate_Nominations
409 PURPOSE: This API returns how many times he/she has been
410 nominated among all REQUIREMENTS.
411 -------------------------------------------------------------------- */
412 FUNCTION Get_Candidate_Nominations (p_resource_id IN NUMBER)
413 RETURN NUMBER
414 IS
415 l_num_nomination NUMBER := 0;
416 BEGIN
417
418 SELECT count(*)
419 INTO l_num_nomination
420 FROM pa_candidates can,
421 pa_project_assignments asmt,
422 pa_project_statuses ps
423 WHERE can.assignment_id = asmt.assignment_id
424 AND can.status_code = ps.project_status_code
425 AND can.resource_id = p_resource_id
426 AND asmt.assignment_type = 'OPEN_ASSIGNMENT'
427 AND ps.status_type = 'CANDIDATE'
428 AND ps.project_system_status_code IN ('CANDIDATE_PENDING_REVIEW',
429 'CANDIDATE_UNDER_REVIEW',
430 'CANDIDATE_SUITABLE',
431 'CANDIDATE_SYSTEM_NOMINATED');
432
433 RETURN l_num_nomination;
434 EXCEPTION
435 WHEN OTHERS THEN
436 RETURN 0;
437 END Get_Candidate_Nominations;
438
439 /* --------------------------------------------------------------------
440 FUNCTION: Get_Candidate_Qualifieds
441 PURPOSE: This API returns how many times he/she has been
442 nominated as qualified assignment for REQUIREMENTS.
443 -------------------------------------------------------------------- */
444 FUNCTION Get_Candidate_Qualifieds (p_resource_id IN NUMBER)
445 RETURN NUMBER
446 IS
447 l_num_qualifieds NUMBER := 0;
448 BEGIN
449
450 SELECT count(*)
451 INTO l_num_qualifieds
452 FROM pa_candidates can,
453 pa_project_assignments asmt,
454 pa_project_statuses ps
455 WHERE can.assignment_id = asmt.assignment_id
456 AND can.status_code = ps.project_status_code
457 AND asmt.assignment_type = 'OPEN_ASSIGNMENT'
458 AND can.resource_id = p_resource_id
459 AND ps.status_type = 'CANDIDATE'
460 AND ps.project_system_status_code = 'CANDIDATE_SYSTEM_QUALIFIED';
461
462 RETURN l_num_qualifieds;
463 EXCEPTION
464 WHEN OTHERS THEN
465 RETURN 0;
466 END Get_Candidate_Qualifieds;
467
468 /* --------------------------------------------------------------------
469 FUNCTION: Update_No_Of_Active_Candidates
470 PURPOSE: This API updates the no_of_active_candidates column in the
471 pa_project_assignments table. It will be called when the user
472 changes the duration for the requirements.
473 -------------------------------------------------------------------- */
474 PROCEDURE Update_No_Of_Active_Candidates (p_assignment_id IN NUMBER,
475 x_return_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
476 IS
477 l_no_of_active_candidates NUMBER := 0;
478 l_record_version_number NUMBER := 0;
479 l_return_status VARCHAR2(1);
480 BEGIN
481
482 PA_DEBUG.init_err_stack('PA_CANDIDATE_UTIL.Update_No_Of_Active_Candidates');
483
484 x_return_status := FND_API.G_RET_STS_SUCCESS;
485
486 SELECT count(*)
487 INTO l_no_of_active_candidates
488 FROM pa_candidates cand,
489 pa_project_assignments asmt,
490 pa_resources_denorm res,
491 pa_project_statuses ps
492 WHERE cand.assignment_id = p_assignment_id
493 AND cand.assignment_id = asmt.assignment_id
494 AND cand.resource_id = res.resource_id
495 AND asmt.start_date BETWEEN res.resource_effective_start_date AND
496 NVL(res.resource_effective_end_date, asmt.start_date+1)
497 AND cand.status_code = ps.project_status_code
498 AND ps.status_type = 'CANDIDATE'
499 AND ps.project_system_status_code in ('CANDIDATE_PENDING_REVIEW',
500 'CANDIDATE_UNDER_REVIEW',
501 'CANDIDATE_SYSTEM_NOMINATED',
502 'CANDIDATE_SUITABLE')
503 AND res.schedulable_flag = 'Y';
504
505 SELECT record_version_number
506 INTO l_record_version_number
507 FROM pa_project_assignments
508 WHERE assignment_id = p_assignment_id;
509
510 pa_project_assignments_pkg.Update_row(
511 p_assignment_id => p_assignment_id,
512 p_no_of_active_candidates => l_no_of_active_candidates,
513 p_record_version_number => l_record_version_number,
514 x_return_status => l_return_status );
515
516 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
517 RAISE FND_API.G_EXC_ERROR;
518 END IF;
519
520 EXCEPTION
521 WHEN OTHERS THEN
522 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
523
524 fnd_msg_pub.add_exc_msg
525 (p_pkg_name => 'PA_CANDIDATE_UTIL',
526 p_procedure_name => 'Update_No_Of_Active_Candidates' );
527
528 RAISE;
529 END Update_No_Of_Active_Candidates;
530
531 END PA_CANDIDATE_UTILS ;
532
[Home] [Help]