[Home] [Help]
PACKAGE BODY: APPS.PA_CANDIDATE_PUB
Source
1 PACKAGE BODY PA_CANDIDATE_PUB AS
2 -- $Header: PARCANPB.pls 120.8.12010000.11 2010/03/31 10:15:40 nisinha ship $
3
4 FUNCTION Get_Person_Id
5 RETURN NUMBER;
6
7 FUNCTION Get_Resource_Name(p_resource_id in NUMBER)
8 RETURN VARCHAR2;
9
10 FUNCTION Get_Number_Of_Candidates(p_project_status_code IN VARCHAR2)
11 RETURN NUMBER
12 IS
13 l_no_of_candidates NUMBER := 0;
14 BEGIN
15 SELECT count(*)
16 into l_no_of_candidates
17 FROM pa_candidates
18 where assignment_id = g_assignment_id
19 AND status_code = p_project_status_code;
20
21 RETURN l_no_of_candidates;
22 EXCEPTION
23 WHEN OTHERS THEN
24 RETURN 0;
25 END;
26
27 FUNCTION Get_Number_Of_Candidates(p_assignment_id IN NUMBER)
28 RETURN NUMBER
29 IS
30 l_no_of_candidates NUMBER := 0;
31 BEGIN
32 SELECT count(*)
33 into l_no_of_candidates
34 FROM pa_candidates
35 where assignment_id = g_assignment_id
36 and status_code in ('UNDER_REVIEW','SUITABLE');
37
38 RETURN l_no_of_candidates;
39 EXCEPTION
40 WHEN OTHERS THEN
41 RETURN 0;
42 END;
43
44 FUNCTION Resource_Is_Candidate(p_resource_id IN NUMBER,
45 p_assignment_id IN NUMBER)
46 RETURN VARCHAR2
47 IS
48 l_exists VARCHAR2(1) := 'N';
49 BEGIN
50 SELECT 'Y'
51 into l_exists
52 FROM pa_candidates
53 WHERE resource_id=p_resource_id
54 AND assignment_id = p_assignment_id;
55
56 RETURN 'Y';
57 EXCEPTION
58 WHEN OTHERS THEN
59 RETURN 'N';
60 END;
61
62 /* --------------------------------------------------------------------
63 FUNCTION: IS_CAND_ON_ANOTHER_ASSIGNMENT
64 PURPOSE: This function is called from the view PA_CANDIDATE_DETAILS_V
65 on which the Candidate list page is based.
66 This page displays the candidates for a given assignment
67 (p_assignment_id). If the candidate is also a candidate on
68 another assignment, or if the candidate is provisionally
69 assigned on another assignment, then an indicator by the
70 candidate number will indicate this.
71 The view has the attribute CAND_ON_ANOTHER_ASSIGNMENT, whose
72 value is based on the value returned by this function.
73 -------------------------------------------------------------------- */
74 FUNCTION IS_CAND_ON_ANOTHER_ASSIGNMENT
75 (p_resource_id IN NUMBER,
76 p_assignment_id IN NUMBER,
77 p_assignment_start_date IN DATE,
78 p_assignment_end_date IN DATE)
79 RETURN VARCHAR2
80 IS
81 l_exists VARCHAR2(1) := 'N';
82 BEGIN
83 BEGIN
84 --Bug 8295734: For active candidate project_system_status_code should not be CANDIDATE_ASSIGNED
85 SELECT 'Y'
86 INTO l_exists
87 FROM pa_candidates pc,
88 pa_project_statuses pps
89 WHERE pc.resource_id = p_resource_id
90 AND pc.assignment_id <> p_assignment_id
91 AND pc.status_code = pps.project_status_code
92 AND pps.project_system_status_code not in ('CANDIDATE_DECLINED','CANDIDATE_WITHDRAWN','CANDIDATE_ASSIGNED')
93 AND ROWNUM = 1;
94
95 EXCEPTION
96 WHEN NO_DATA_FOUND THEN
97 null;
98 END;
99
100 IF l_exists = 'Y' THEN
101 RETURN 'Y';
102 END IF;
103
104 SELECT 'Y'
105 INTO l_exists
106 FROM PA_PROJECT_ASSIGNMENTS
107 WHERE resource_id = p_resource_id
108 AND assignment_id <> p_assignment_id
109 AND nvl(start_date,sysdate) <= nvl(p_assignment_end_date,sysdate)
110 AND nvl(end_date,sysdate) >= nvl(p_assignment_start_date,sysdate)
111 AND ROWNUM = 1;
112
113 RETURN 'Y';
114 EXCEPTION
115 WHEN OTHERS THEN
116 RETURN 'N';
117 END;
118
119 /* --------------------------------------------------------------------
120 FUNCTION: IS_CAND_ON_ASSIGNMENT
121 PURPOSE: This function checks to see if the resource is a candidate
122 on the assigment p_assignment_id. It yes, it returns a 'Y'.
123 This function is called from the client side, by the Resource
124 Requirement Search Page. The Resource Requirement Search
125 Page checks is a page which displays the results for requirements
126 done for a particular resource. If the resource is already a
127 candidate on the requirement, then an indicator by the candidate
128 number will indicate it.
129 Return value = 'Y', means candidate already on assignment, nothing
130 will be done to this candidate.
131 Return value = 'N', means candidate not on assignment so it
132 will be created as a new candidate.
133 Return value = 'U', means candidate on assignment but his/her
134 status will be updated.
135 -------------------------------------------------------------------- */
136 FUNCTION IS_CAND_ON_ASSIGNMENT(p_resource_id IN NUMBER,
137 p_assignment_id IN NUMBER,
138 p_status_code IN VARCHAR2 DEFAULT NULL)
139 RETURN VARCHAR2
140 IS
141 l_exists VARCHAR2(1) := 'Y';
142 l_old_project_system_status VARCHAR2(30);
143 l_new_project_system_status VARCHAR2(30);
144 BEGIN
145 IF p_status_code IS NULL THEN
146 SELECT 'Y'
147 INTO l_exists
148 FROM pa_candidates
149 WHERE resource_id = p_resource_id
150 AND assignment_id = p_assignment_id;
151 ELSE
152 SELECT project_system_status_code
153 INTO l_old_project_system_status
154 FROM pa_candidates, pa_project_statuses
155 WHERE resource_id = p_resource_id
156 AND assignment_id = p_assignment_id
157 AND status_code = project_status_code
158 AND status_type = 'CANDIDATE';
159
160 SELECT project_system_status_code
161 INTO l_new_project_system_status
162 FROM pa_project_statuses
163 WHERE project_status_code = p_status_code
164 AND status_type = 'CANDIDATE';
165
166 IF l_old_project_system_status = 'CANDIDATE_SYSTEM_QUALIFIED' AND
167 l_new_project_system_status <> 'CANDIDATE_SYSTEM_QUALIFIED' THEN
168 RETURN 'U';
169 END IF;
170 END IF;
171
172 RETURN 'Y';
173
174 EXCEPTION
175 WHEN OTHERS THEN
176 RETURN 'N';
177 END IS_CAND_ON_ASSIGNMENT;
178
179
180 /* --------------------------------------------------------------------
181 PROCEDURE: Add_Candidate
182 PURPOSE: This procedure will add p_resource_id as the candidate on
183 p_assigment_id. p_nomination_comments are the comments
184 by the nominator to add this resource on the assignment.
185 This procedure will error out if:
186 1. The resource is already a candidate on the assignment
187 This procedure will give an unexpected error if:
188 1. Resource_Id or Assignment_Id are not valid.
189
190 09-May-2001
191 p_privilege_name and p_project_super_user IN parameters are
192 added, p_privilege name is one key input for checking
193 whether user has resource authority over nominee while
194 p_project_super_user indicates whether user has project
195 super user resp.
196 -------------------------------------------------------------------- */
197 PROCEDURE Add_Candidate
198 (p_assignment_id IN NUMBER,
199 p_resource_name IN VARCHAR2,
200 p_resource_id IN NUMBER DEFAULT NULL,
201 p_status_code IN VARCHAR2 DEFAULT NULL,
202 p_nomination_comments IN VARCHAR2,
203 p_person_id IN NUMBER DEFAULT NULL,
204 p_privilege_name IN VARCHAR2 DEFAULT NULL,
205 p_project_super_user IN VARCHAR2 DEFAULT 'N',
206 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_TRUE, -- Added for Bug 5130421: PJR Enhancements for Public APIs
207 -- Added for bug 9187892
208 p_attribute_category IN pa_candidates.attribute_category%TYPE,
209 p_attribute1 IN pa_candidates.attribute1%TYPE,
210 p_attribute2 IN pa_candidates.attribute2%TYPE,
211 p_attribute3 IN pa_candidates.attribute3%TYPE,
212 p_attribute4 IN pa_candidates.attribute4%TYPE,
213 p_attribute5 IN pa_candidates.attribute5%TYPE,
214 p_attribute6 IN pa_candidates.attribute6%TYPE,
215 p_attribute7 IN pa_candidates.attribute7%TYPE,
216 p_attribute8 IN pa_candidates.attribute8%TYPE,
217 p_attribute9 IN pa_candidates.attribute9%TYPE,
218 p_attribute10 IN pa_candidates.attribute10%TYPE,
219 p_attribute11 IN pa_candidates.attribute11%TYPE,
220 p_attribute12 IN pa_candidates.attribute12%TYPE,
221 p_attribute13 IN pa_candidates.attribute13%TYPE,
222 p_attribute14 IN pa_candidates.attribute14%TYPE,
223 p_attribute15 IN pa_candidates.attribute15%TYPE,
224 x_return_status OUT NOCOPY VARCHAR2, -- 4537865
225 x_msg_count OUT NOCOPY NUMBER, -- 4537865
226 x_msg_data OUT NOCOPY VARCHAR2) -- 4537865
227 IS
228
229 l_exists VARCHAR2(1);
230 l_nominated_by_person_id NUMBER;
231 l_candidate_id NUMBER;
232 l_return_status VARCHAR2(1);
233 l_msg_count NUMBER := 0;
234 l_msg_data VARCHAR2(500);
235 l_msg_index_out NUMBER := 0;
236 l_resource_id NUMBER := 0;
237 l_status_code VARCHAR2(30);
238 l_system_status_code VARCHAR2(30);
239 l_status_name VARCHAR2(80);
240 l_res_id_name_match BOOLEAN := FALSE;
241 l_person_id NUMBER;
242 l_ret_code VARCHAR2(1);
243 l_asmt_start_date DATE;
244 l_resource_type_id NUMBER;
245 l_is_cand_on_asmt VARCHAR2(1) := 'N';
246 l_fnd_user_id NUMBER;
247 l_enable_wf_flag VARCHAR2(1);
248 l_wf_item_type VARCHAR2(30);
249 l_wf_process VARCHAR2(30);
250 l_check_id_flag VARCHAR2(1);
251
252
253 BEGIN
254 -- Initialize the Error Stack
255 PA_DEBUG.init_err_stack('PA_CANDIDATE_PUB.Add_Candidate');
256
257 -- initialize return_status to success
258 x_return_status := FND_API.G_RET_STS_SUCCESS;
259
260 -- Clear the global PL/SQL message table. Added check of p_init_msg_list Bug 5130421: PJR Enhancements for Public APIs
261 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list, FND_API.G_TRUE)) THEN
262 FND_MSG_PUB.initialize;
263 END IF;
264
265
266 -- Check if assignment Id is valid
267 BEGIN
268 SELECT start_date
269 INTO l_asmt_start_date
270 FROM pa_project_assignments
271 WHERE assignment_id=p_assignment_id;
272 EXCEPTION
273 WHEN NO_DATA_FOUND THEN
274 pa_utils.add_message
275 (p_app_short_name => 'PA',
276 p_msg_name => 'PA_XC_RECORD_CHANGED');
277 RAISE FND_API.G_EXC_ERROR;
278 WHEN OTHERS THEN
279 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
280 END;
281
282 IF p_resource_id IS NULL THEN
283 -- Check whether the pass-in person_id and person_name match
284
285
286 /* Bug 2843613 */
287
288 l_check_id_flag := PA_STARTUP.G_Check_ID_Flag;
289 IF PA_STARTUP.G_Calling_Application = 'SELF_SERVICE' THEN
290 PA_STARTUP.G_Check_ID_Flag := 'N';
291 END IF;
292
293 /* Bug 2843613 */
294
295
296 pa_resource_utils.Check_ResourceName_Or_Id
297 (p_resource_id => p_person_id, -- p_resource_id input parameter is actually person_id
298 p_resource_name => p_resource_name,
299 p_date => l_asmt_start_date,
300 p_check_id_flag => PA_STARTUP.G_Check_ID_Flag, /*changed to G_Check_ID_Flag from A*/
301 x_resource_id => l_person_id,
302 x_resource_type_id => l_resource_type_id,
303 x_return_status => l_return_status,
304 x_error_message_code => l_msg_data);
305
306 PA_STARTUP.G_Check_ID_Flag := l_check_id_flag;
307
308 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
309 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
310 ,p_msg_name => l_msg_data );
311 RAISE FND_API.G_EXC_ERROR;
312 END IF;
313
314 PA_R_PROJECT_RESOURCES_PUB.create_resource
315 (p_api_version => 1.0,
316 p_init_msg_list => fnd_api.g_false,
317 --p_commit => p_commit,
318 --p_validate_only => p_validate_only,
319 p_person_id => l_person_id,
320 p_individual => 'Y',
321 p_resource_type => 'EMPLOYEE',
322 p_check_resource => 'Y',
323 x_return_status => l_return_status,
324 x_msg_count => x_msg_count,
325 x_msg_data => x_msg_data,
326 x_resource_id => l_resource_id);
327
328 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
329 RAISE FND_API.G_EXC_ERROR;
330 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
331 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
332 END IF;
333
334 PA_RESOURCE_UTILS.Validate_Person
335 (p_person_id => l_person_id
336 ,p_start_date => l_asmt_start_date
337 ,x_return_status => l_return_status);
338
339 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
340 RAISE FND_API.G_EXC_ERROR;
341 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
342 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
343 END IF;
344
345 -- If calling page is nominate candidate, we need to check whether the user has
346 -- resource authority over nominee
347 IF p_project_super_user = 'N'
348 AND (p_privilege_name = 'PA_NOMINATE_CANDIDATES'
349 OR p_privilege_name = 'PA_NOMINATE_SELF_AS_CANDIDATE') THEN
350
351 pa_security_pvt.check_confirm_asmt(p_project_id => -999,
352 p_resource_id => l_resource_id,
353 p_resource_name => p_resource_name,
354 p_privilege => p_privilege_name,
355 p_start_date => l_asmt_start_date,
356 x_ret_code => l_ret_code,
357 x_return_status => x_return_status,
358 x_msg_count => x_msg_count,
359 x_msg_data => x_msg_data);
360
361 IF l_ret_code = FND_API.G_FALSE THEN
362 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
363 ,p_msg_name => 'PA_NO_RESOURCE_AUTHORITY' );
364 RAISE FND_API.G_EXC_ERROR;
365 END IF;
366 END IF;
367
368 l_nominated_by_person_id := Get_Person_Id;
369 l_fnd_user_id := FND_GLOBAL.USER_ID;
370 ELSE
371 l_resource_id := p_resource_id;
372 l_nominated_by_person_id := null;
373 l_status_code := p_status_code;
374 l_fnd_user_id := -1;
375 END IF;
376
377 -- Check if candidate Status is passed. If not, get the value from
378 -- the profile
379 IF p_status_code is null THEN
380 SELECT fnd_profile.value('PA_DEF_START_CAND_STATUS')
381 INTO l_status_code
382 FROM dual;
383 ELSE
384 l_status_code := p_status_code;
385 END IF;
386
387 -- Return Status 'S' and return the name to the calling page if
388 -- candidate are already in an assignment
389 l_is_cand_on_asmt := IS_CAND_ON_ASSIGNMENT(l_resource_id,p_assignment_id, l_status_code);
390
391 IF l_is_cand_on_asmt = 'Y' THEN
392 x_msg_count := x_msg_count + 1;
393 x_return_status := FND_API.G_RET_STS_SUCCESS;
394 x_msg_data := p_resource_name;
395 RETURN;
396 END IF;
397
398 IF l_status_code is null THEN
399 pa_utils.add_message
400 (p_app_short_name => 'PA',
401 p_msg_name => 'PA_CAND_STATUS_REQD');
402 RAISE FND_API.G_EXC_ERROR;
403 ELSE
404 SELECT project_system_status_code
405 INTO l_system_status_code
406 FROM pa_project_statuses
407 WHERE project_status_code = l_status_code;
408 END IF;
409
410 IF l_is_cand_on_asmt = 'N' THEN
411 -- Insert into the candidate table.
412 INSERT INTO PA_CANDIDATES
413 (CANDIDATE_ID,
414 ASSIGNMENT_ID,
415 RESOURCE_ID,
416 RECORD_VERSION_NUMBER,
417 STATUS_CODE,
418 NOMINATED_BY_PERSON_ID,
419 NOMINATION_DATE,
420 NOMINATION_COMMENTS,
421 CANDIDATE_RANKING,
422 CREATION_DATE,
423 -- Added for bug 9187892
424 ATTRIBUTE_CATEGORY,
425 ATTRIBUTE1,
426 ATTRIBUTE2,
427 ATTRIBUTE3,
428 ATTRIBUTE4,
429 ATTRIBUTE5,
430 ATTRIBUTE6,
431 ATTRIBUTE7,
432 ATTRIBUTE8,
433 ATTRIBUTE9,
434 ATTRIBUTE10,
435 ATTRIBUTE11,
436 ATTRIBUTE12,
437 ATTRIBUTE13,
438 ATTRIBUTE14,
439 ATTRIBUTE15,
440 CREATED_BY,
441 LAST_UPDATE_DATE,
442 LAST_UPDATED_BY)
443 VALUES
444 (PA_CANDIDATES_S.nextval,
445 p_ASSIGNMENT_ID,
446 l_RESOURCE_ID,
447 1,
448 l_status_code,
449 l_nominated_by_person_id,
450 sysdate,
451 p_nomination_comments,
452 null,
453 SYSDATE,
454 -- Added for bug 9187892
455 p_attribute_category,
456 p_attribute1,
457 p_attribute2,
458 p_attribute3,
459 p_attribute4,
460 p_attribute5,
461 p_attribute6,
462 p_attribute7,
463 p_attribute8,
464 p_attribute9,
465 p_attribute10,
466 p_attribute11,
467 p_attribute12,
468 p_attribute13,
469 p_attribute14,
470 p_attribute15,
471 l_fnd_user_id,
472 SYSDATE,
473 l_fnd_user_id)
474 RETURNING
475 CANDIDATE_ID into l_candidate_id;
476 ELSE
477 UPDATE PA_CANDIDATES
478 SET STATUS_CODE = l_status_code,
479 NOMINATION_COMMENTS = p_nomination_comments,
480 RECORD_VERSION_NUMBER = record_version_number + 1,
481 NOMINATED_BY_PERSON_ID = l_nominated_by_person_id,
482 NOMINATION_DATE = SYSDATE,
483 LAST_UPDATE_DATE = SYSDATE,
484 LAST_UPDATED_BY = l_fnd_user_id
485 WHERE assignment_id = p_assignment_id
486 AND resource_id = l_resource_id;
487 END IF;
488
489 SELECT ENABLE_WF_FLAG, WORKFLOW_ITEM_TYPE, WORKFLOW_PROCESS, PROJECT_STATUS_NAME
490 INTO l_enable_wf_flag, l_wf_item_type, l_wf_process, l_status_name
491 FROM PA_PROJECT_STATUSES
492 WHERE status_type = 'CANDIDATE'
493 AND project_status_code = l_status_code;
494
495 --dbms_output.put_line ( 'l_enable_wf_flag ' || l_enable_wf_flag );
496 --dbms_output.put_line ( 'l_system_status_code ' || l_system_status_code);
497
498 IF l_enable_wf_flag = 'Y' AND l_wf_item_type IS NOT NULL AND
499 l_wf_process IS NOT NULL THEN
500
501 Start_Workflow(p_wf_item_type => l_wf_item_type,
502 p_wf_process => l_wf_process,
503 p_assignment_id => p_assignment_id,
504 p_candidate_number => l_candidate_id,
505 p_resource_id => l_resource_id,
506 p_status_name => l_status_name,
507 x_return_status => l_return_status,
508 x_msg_count => l_msg_count,
509 x_msg_data => l_msg_data);
510
511 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
512 RAISE FND_API.G_EXC_ERROR;
513 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
514 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
515 END IF;
516 END IF;
517
518
519 -- Update No_Of_Active_Candidates in PA_PROJECT_ASSIGNMENTS
520 Update_No_Of_Active_Candidates(
521 p_assignment_id => p_assignment_id,
522 p_old_system_status_code => NULL,
523 p_new_system_status_code => l_system_status_code,
524 x_return_status => l_return_status);
525
526 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
527 RAISE FND_API.G_EXC_ERROR;
528 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
529 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
530 END IF;
531
532 EXCEPTION
533 WHEN FND_API.G_EXC_ERROR THEN
534 x_return_status := FND_API.G_RET_STS_ERROR;
535 x_msg_count := FND_MSG_PUB.Count_Msg;
536
537 IF x_msg_count = 1 THEN
538 pa_interface_utils_pub.get_messages
539 (p_encoded => FND_API.G_TRUE,
540 p_msg_index => 1,
541 p_msg_count => 1 ,
542 p_msg_data => l_msg_data ,
543 p_data => x_msg_data,
544 p_msg_index_out => l_msg_index_out );
545 END IF;
546
547 WHEN OTHERS THEN
548 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
549
550 fnd_msg_pub.add_exc_msg
551 (p_pkg_name => 'PA_CANDIDATE_PUB',
552 p_procedure_name => 'Add_Candidate' );
553
554 x_msg_count := FND_MSG_PUB.Count_Msg;
555
556 IF x_msg_count = 1 THEN
557 pa_interface_utils_pub.get_messages
558 (p_encoded => FND_API.G_TRUE,
559 p_msg_index => 1,
560 p_msg_count => 1,
561 p_msg_data => l_msg_data ,
562 p_data => x_msg_data,
563 p_msg_index_out => l_msg_index_out );
564 END IF;
565 RAISE;
566
567 END Add_Candidate;
568
569
570
571 /* --------------------------------------------------------------------
572 PROCEDURE: update_no_of_active_candidates
573 PURPOSE: This procedure will update no_of_active_candidates column of
574 pa_project_assignments by calling from other procedures which
575 update candidate status like Add_Candidate, Add_Candidate_Log,
576 Update_Candidate.
577 -------------------------------------------------------------------- */
578 PROCEDURE Update_No_Of_Active_Candidates(
579 p_assignment_id IN NUMBER,
580 p_old_system_status_code IN VARCHAR2,
581 p_new_system_status_code IN VARCHAR2,
582 x_return_status OUT NOCOPY VARCHAR2 ) -- 4537865
583 IS
584 l_no_of_active_candidates NUMBER;
585 l_record_version_number NUMBER;
586 l_return_status VARCHAR2(1);
587 BEGIN
588 -- initialize return status
589 x_return_status := FND_API.G_RET_STS_SUCCESS;
590
591 -- get original no_of_active_candidates and record_version_number for the passed assignment_id
592 SELECT no_of_active_candidates, record_version_number
593 INTO l_no_of_active_candidates, l_record_version_number
594 FROM pa_project_assignments
595 WHERE assignment_id = p_assignment_id;
596
597 -- if original no_of_active_candidates is null, set to 0 so that we can increase or
598 -- decrease the value with ease.
599 IF (l_no_of_active_candidates is NULL) THEN
600 l_no_of_active_candidates := 0;
601 END IF;
602
603 -- if this has been called from 'Add_Candidate', p_old_system_status_code won't be passed.
604 -- In that case, just check if the new_system_status_code is one of the active status.
605 -- If so, increase the value of no_of_active_candidates in pa_project_assignments table.
606 IF (p_old_system_status_code IS NULL AND is_active_candidate(p_new_system_status_code)='Y') THEN
607 pa_project_assignments_pkg.Update_row(
608 p_assignment_id => p_assignment_id,
609 p_no_of_active_candidates => l_no_of_active_candidates+1,
610 p_record_version_number => l_record_version_number,
611 x_return_status => l_return_status );
612
613 -- if this has been called from either 'Add_Candidate_Log' or 'Update_Candidate', check
614 -- if the new_status_code is differenct as an old one.
615 ELSIF (p_old_system_status_code IS NOT NULL AND p_old_system_status_code <> p_new_system_status_code) THEN
616
617 -- If the status has been changed from active to non-active, decrement no_of_active_candidates
618 IF (is_active_candidate(p_old_system_status_code)='Y'
619 AND is_active_candidate(p_new_system_status_code)='N') THEN
620 pa_project_assignments_pkg.Update_row(
621 p_assignment_id => p_assignment_id,
622 p_no_of_active_candidates => l_no_of_active_candidates-1,
623 p_record_version_number => l_record_version_number,
624 x_return_status => l_return_status );
625
626 -- If the status has been changed from non-active to active, increment no_of_active_candidates
627 ELSIF (is_active_candidate(p_old_system_status_code)='N'
628 AND is_active_candidate(p_new_system_status_code)='Y') THEN
629 pa_project_assignments_pkg.Update_row(
630 p_assignment_id => p_assignment_id,
631 p_no_of_active_candidates => l_no_of_active_candidates+1,
632 p_record_version_number => l_record_version_number,
633 x_return_status => l_return_status );
634 END IF;
635
636 END IF;
637
638 -- 4537865 : Assigning l_return_status to x_return_status was missing
639 x_return_status := l_return_status ;
640
641 EXCEPTION
642 -- catch the exceptins here
643 WHEN OTHERS THEN
644 -- Set the exception Message and the stack
645 FND_MSG_PUB.add_exc_msg(
646 p_pkg_name => 'PA_CANDIDATE_PUB',
647 p_procedure_name => 'Update_No_Of_Active_Candidates');
648
649 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
650 RAISE;
651 END Update_No_Of_Active_Candidates;
652
653
654
655 /* --------------------------------------------------------------------
656 PROCEDURE: Update_Remaining_Candidates
657 PURPOSE: This procedure will update all the candidates (except p_resource_id)
658 on the assignment p_assignment_id to status p_status_code.
659 Currently, the only acceptable status value for p_status_code is DECLINED.
660 This API is called from the assignment page, when an resource is assigned
661 to an assignment. The page has a region "Candidates", where the user
662 can select to update the status of remaing candidates to DECLINED.
663 -------------------------------------------------------------------- */
664 PROCEDURE Update_Remaining_Candidates
665 (p_assignment_id IN NUMBER,
666 p_resource_id IN NUMBER,
667 p_status_code IN VARCHAR2,
668 p_change_reason_code IN VARCHAR2,
669 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
670 -- Added for bug 9187892
671 -- start for bug#9468526 , Added default null values
672 p_attribute_category IN pa_candidates.attribute_category%TYPE :=NULL ,
673 p_attribute1 IN pa_candidates.attribute1%TYPE :=NULL ,
674 p_attribute2 IN pa_candidates.attribute2%TYPE :=NULL ,
675 p_attribute3 IN pa_candidates.attribute3%TYPE :=NULL ,
676 p_attribute4 IN pa_candidates.attribute4%TYPE :=NULL ,
677 p_attribute5 IN pa_candidates.attribute5%TYPE :=NULL ,
678 p_attribute6 IN pa_candidates.attribute6%TYPE :=NULL ,
679 p_attribute7 IN pa_candidates.attribute7%TYPE :=NULL ,
680 p_attribute8 IN pa_candidates.attribute8%TYPE :=NULL ,
681 p_attribute9 IN pa_candidates.attribute9%TYPE :=NULL ,
682 p_attribute10 IN pa_candidates.attribute10%TYPE :=NULL ,
683 p_attribute11 IN pa_candidates.attribute11%TYPE :=NULL ,
684 p_attribute12 IN pa_candidates.attribute12%TYPE :=NULL ,
685 p_attribute13 IN pa_candidates.attribute13%TYPE :=NULL ,
686 p_attribute14 IN pa_candidates.attribute14%TYPE :=NULL ,
687 p_attribute15 IN pa_candidates.attribute15%TYPE :=NULL ,
688 -- start for bug#9468526 , Added default null values
689 x_return_status OUT NOCOPY VARCHAR2, -- 4537865 : Added nocopy hint
690 x_msg_data OUT NOCOPY VARCHAR2, -- 4537865 : Added nocopy hint
691 x_msg_count OUT NOCOPY NUMBER) -- 4537865 : Added nocopy hint
692 IS
693
694 cursor remain_candidates_csr is
695 SELECT candidate_id, resource_id, record_version_number,
696 candidate_ranking, status_code
697 FROM pa_candidates
698 WHERE assignment_id = p_assignment_id
699 and resource_id <> p_resource_id;
700
701 cursor assigned_candidate_csr is
702 SELECT status_code
703 FROM pa_candidates
704 WHERE assignment_id = p_assignment_id
705 and resource_id = p_resource_id;
706
707 l_return_status VARCHAR2(1);
708 l_msg_count NUMBER := 0;
709 l_msg_data VARCHAR2(500);
710 l_msg_index_out NUMBER := 0;
711 l_exists VARCHAR2(1);
712 l_record_version_number NUMBER;
713 l_cand_record_version_number NUMBER;
714 l_asgned_record_version_number NUMBER;
715 l_asgned_candidate_id NUMBER;
716 l_candidate_ranking NUMBER;
717 l_project_status_code VARCHAR2(30);
718 l_project_system_status_code VARCHAR2(30);
719 l_old_system_status_code VARCHAR2(30);
720
721 BEGIN
722 -- Initialize the Error Stack
723 PA_DEBUG.init_err_stack('PA_CANDIDATE_PUB.Update_Remaining_Candidates');
724
725 -- initialize return_status to success
726 x_return_status := FND_API.G_RET_STS_SUCCESS;
727
728 -- Clear the global PL/SQL message table
729 IF FND_API.TO_BOOLEAN( p_init_msg_list ) THEN
730 FND_MSG_PUB.initialize;
731 END IF;
732
733 -- Check if p_status_code is DECLINED only if the passed status_code, which is for the remaining
734 -- candidates, is not null
735 IF p_status_code IS NOT NULL THEN
736 BEGIN
737 SELECT 'Y'
738 INTO l_exists
739 FROM PA_PROJECT_STATUSES
740 WHERE project_status_code = p_status_code
741 AND project_system_status_code = 'CANDIDATE_DECLINED';
742 EXCEPTION
743 WHEN NO_DATA_FOUND THEN
744 -- Message to indicate that only status with Declined sytem status
745 -- is acceptable
746 pa_utils.add_message
747 (p_app_short_name => 'PA',
748 p_msg_name => 'PA_STS_NOT_VALID');
749
750 RAISE FND_API.G_EXC_ERROR;
751 END;
752 END IF;
753
754 BEGIN
755 -- get information of the assigned person from pa_candidates
756 -- if the passed resource is not one of the candidates,
757 -- it won't update anything for the assigned person.
758
759 SELECT candidate_id,
760 record_version_number,
761 candidate_ranking
762 INTO l_asgned_candidate_id,
763 l_asgned_record_version_number,
764 l_candidate_ranking
765 FROM pa_candidates
766 WHERE assignment_id = p_assignment_id
767 AND resource_id = p_resource_id;
768
769 BEGIN
770 -- Get project_status_code for system_status_code CANDIADTE_ASSIGNED.
771
772 SELECT project_status_code
773 INTO l_project_status_code
774 FROM PA_PROJECT_STATUSES
775 WHERE project_system_status_code = 'CANDIDATE_ASSIGNED'
776 AND status_type = 'CANDIDATE'; -- Bug 4773033 CANDIDATE ROUTINES JOIN FOR PROJECT STATUSES
777 -- AND ROWNUM=1;
778
779 -- Update the record for the assigned candidate
780 Update_Candidate
781 (p_candidate_id => l_asgned_candidate_id,
782 p_status_code => l_project_status_code,
783 p_ranking => l_candidate_ranking,
784 p_change_reason_code => null,
785 p_record_version_number => l_asgned_record_version_number,
786 p_init_msg_list => p_init_msg_list,
787 x_record_version_number => l_cand_record_version_number,
788 -- Added for bug 9187892
789 p_attribute_category => p_attribute_category,
790 p_attribute1 => p_attribute1,
791 p_attribute2 => p_attribute2,
792 p_attribute3 => p_attribute3,
793 p_attribute4 => p_attribute4,
794 p_attribute5 => p_attribute5,
795 p_attribute6 => p_attribute6,
796 p_attribute7 => p_attribute7,
797 p_attribute8 => p_attribute8,
798 p_attribute9 => p_attribute9,
799 p_attribute10 => p_attribute10,
800 p_attribute11 => p_attribute11,
801 p_attribute12 => p_attribute12,
802 p_attribute13 => p_attribute13,
803 p_attribute14 => p_attribute14,
804 p_attribute15 => p_attribute15,
805 x_msg_count => l_msg_count,
806 x_msg_data => l_msg_data,
807 x_return_status => l_return_status);
808
809 IF(l_return_status = FND_API.G_RET_STS_ERROR) THEN
810 RAISE FND_API.G_EXC_ERROR;
811 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
812 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
813 END IF;
814
815 EXCEPTION
816 -- if there is no project_status_code for ASSIGN
817 WHEN NO_DATA_FOUND THEN
818 pa_utils.add_message
819 (p_app_short_name => 'PA',
820 p_msg_name => 'PA_NO_ASSIGN_STATUS');
821
822 RAISE FND_API.G_EXC_ERROR;
823 END;
824
825 EXCEPTION
826 -- if the assigned person is not one of the candidates,
827 -- don't do anything for the assigned person.
828 WHEN NO_DATA_FOUND THEN
829 null;
830 END;
831
832
833 -- For the remaing candiates, update the status in pa_candidates
834 -- and create a record in review comments table for the changed status.
835 -- only if the passed status_code, which is for the remaining
836 -- candidates, is not null
837
838 IF p_status_code IS NOT NULL THEN
839
840 FOR c2 in remain_candidates_csr LOOP
841 -- Get the original project_system_status_code for remaining candidates.
842 SELECT project_system_status_code
843 INTO l_project_system_status_code
844 FROM PA_PROJECT_STATUSES
845 WHERE project_status_code = c2.status_code;
846
847 -- Update records only for the remaining active candidate.
848 IF(Is_Active_Candidate(l_project_system_status_code)='Y') THEN
849
850 Update_Candidate
851 (p_candidate_id => c2.candidate_id,
852 p_status_code => p_status_code,
853 p_ranking => c2.candidate_ranking,
854 p_change_reason_code => p_change_reason_code,
855 p_record_version_number => c2.record_version_number,
856 p_init_msg_list => p_init_msg_list,
857 x_record_version_number => l_cand_record_version_number,
858 -- Added for bug 9187892
859 p_attribute_category => p_attribute_category,
860 p_attribute1 => p_attribute1,
861 p_attribute2 => p_attribute2,
862 p_attribute3 => p_attribute3,
863 p_attribute4 => p_attribute4,
864 p_attribute5 => p_attribute5,
865 p_attribute6 => p_attribute6,
866 p_attribute7 => p_attribute7,
867 p_attribute8 => p_attribute8,
868 p_attribute9 => p_attribute9,
869 p_attribute10 => p_attribute10,
870 p_attribute11 => p_attribute11,
871 p_attribute12 => p_attribute12,
872 p_attribute13 => p_attribute13,
873 p_attribute14 => p_attribute14,
874 p_attribute15 => p_attribute15,
875 x_msg_count => l_msg_count,
876 x_msg_data => l_msg_data,
877 x_return_status => l_return_status);
878
879 IF(l_return_status = FND_API.G_RET_STS_ERROR) THEN
880 RAISE FND_API.G_EXC_ERROR;
881 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
882 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
883 END IF;
884 END IF;
885
886 END LOOP;
887
888 -- Get original record_version_number of pa_project_assignments
889 -- for the passed assignment_id
890 SELECT record_version_number
891 INTO l_record_version_number
892 FROM pa_project_assignments
893 WHERE assignment_id = p_assignment_id;
894
895 -- Since there are no more active candidates, update the
896 -- No_Of_Active_Candidate in pa_project_assignments to 0
897 -- for assignment p_assignment_id
898
899 pa_project_assignments_pkg.Update_row(
900 p_assignment_id => p_assignment_id,
901 p_no_of_active_candidates => 0,
902 p_record_version_number => l_record_version_number,
903 x_return_status => l_return_status );
904
905 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
906 RAISE FND_API.G_EXC_ERROR;
907 END IF;
908
909 ELSIF p_status_code IS NULL THEN
910
911 -- Get the original project_system_status_code for the assigned candidate.
912 SELECT ps.project_system_status_code
913 INTO l_old_system_status_code
914 FROM pa_candidates cand, pa_project_statuses ps
915 WHERE cand.assignment_id = p_assignment_id
916 AND cand.resource_id = p_resource_id
917 AND ps.project_status_code = cand.status_code;
918
919 -- Update No_Of_Active_Candidates in PA_PROJECT_ASSIGNMENTS
920 Update_No_Of_Active_Candidates(
921 p_assignment_id => p_assignment_id,
922 p_old_system_status_code => l_old_system_status_code,
923 p_new_system_status_code => 'CANDIDATE_ASSIGNED',
924 x_return_status => l_return_status);
925
926 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
927 RAISE FND_API.G_EXC_ERROR;
928 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
929 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
930 END IF;
931
932 END IF;
933
934
935 EXCEPTION
936 WHEN FND_API.G_EXC_ERROR THEN
937 x_return_status := FND_API.G_RET_STS_ERROR;
938 x_msg_count := FND_MSG_PUB.Count_Msg;
939
940 IF x_msg_count = 1 THEN
941 pa_interface_utils_pub.get_messages(
942 p_encoded => FND_API.G_TRUE,
943 p_msg_index => 1,
944 p_msg_count => 1 ,
945 p_msg_data => l_msg_data ,
946 p_data => x_msg_data,
947 p_msg_index_out => l_msg_index_out );
948 END IF;
949
950 WHEN NO_DATA_FOUND THEN
951 x_return_status := FND_API.G_RET_STS_SUCCESS;
952 x_msg_count := 0; -- 4537865
953 x_msg_data := NULL ; -- 4537865
954
955 WHEN OTHERS THEN
956 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
957
958 fnd_msg_pub.add_exc_msg(
959 p_pkg_name => 'PA_COMPETENCE_PUB',
960 p_procedure_name => 'Update_Remaining_Candidates' );
961 x_msg_count := FND_MSG_PUB.Count_Msg;
962
963 IF x_msg_count = 1 THEN
964
965 pa_interface_utils_pub.get_messages(
966 p_encoded => FND_API.G_TRUE,
967 p_msg_index => 1,
968 p_msg_count => 1,
969 p_msg_data => l_msg_data,
970 p_data => x_msg_data,
971 p_msg_index_out => l_msg_index_out );
972 END IF;
973
974 END Update_Remaining_Candidates;
975
976 /* --------------------------------------------------------------------
977 PROCEDURE: Add_Candidate_Log
978 PURPOSE: This Procedure will add a review comment to for a Candidate
979 in the pa_candidate_reviews table. It will also update
980 the status in pa_candidates table.
981 A change reason can be associated with every status change.
982 A review comment can be associated with every status change.
983 This API will error out if:
984 1. The status change from p_old_status_code to p_new_status_code
985 is not an acceptable change. (e.g: Status cannot change from
986 Declined to Under Review).
987 This API will return an unexpected error if:
988 1. p_candidate_id is not found
989 2. p_old_status_code or p_new_status_code are not valid status codes.
990 PARAMETERS:
991 p_candidate_id : Candidate Id of the candidate for whom
992 a log is being created
993 p_status_code : New Status Code entered for the log.
994 Pass null, if no value is entered in this field.
995 p_review_comments : Review Comments
996 p_change_reason_code : Change Reason for Status change.
997 p_record_version_number : Record Version of the Candidate Record
998 p_cand_rec_version_number : Record Version Number of the Candidate
999 (from pa_candidates). We do not
1000 need the record version number for
1001 the review_comments table, since we only
1002 insert in this table, we do not update it.
1003 -------------------------------------------------------------------- */
1004 PROCEDURE Add_Candidate_Log
1005 (p_candidate_id IN NUMBER,
1006 p_status_code IN VARCHAR2,
1007 p_change_reason_code IN VARCHAR2,
1008 p_review_comments IN VARCHAR2,
1009 p_cand_record_version_number IN NUMBER,
1010 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_TRUE, -- Added for Bug 5130421: PJR Enhancements for Public APIs
1011 x_cand_record_version_number OUT NOCOPY NUMBER, -- 4537865
1012 -- Added for bug 9187892
1013 -- start for bug#9468526 , Added default null values
1014 p_attribute_category IN pa_candidates.attribute_category%TYPE :=NULL ,
1015 p_attribute1 IN pa_candidates.attribute1%TYPE :=NULL ,
1016 p_attribute2 IN pa_candidates.attribute2%TYPE :=NULL ,
1017 p_attribute3 IN pa_candidates.attribute3%TYPE :=NULL ,
1018 p_attribute4 IN pa_candidates.attribute4%TYPE :=NULL ,
1019 p_attribute5 IN pa_candidates.attribute5%TYPE :=NULL ,
1020 p_attribute6 IN pa_candidates.attribute6%TYPE :=NULL ,
1021 p_attribute7 IN pa_candidates.attribute7%TYPE :=NULL ,
1022 p_attribute8 IN pa_candidates.attribute8%TYPE :=NULL ,
1023 p_attribute9 IN pa_candidates.attribute9%TYPE :=NULL ,
1024 p_attribute10 IN pa_candidates.attribute10%TYPE :=NULL ,
1025 p_attribute11 IN pa_candidates.attribute11%TYPE :=NULL ,
1026 p_attribute12 IN pa_candidates.attribute12%TYPE :=NULL ,
1027 p_attribute13 IN pa_candidates.attribute13%TYPE :=NULL ,
1028 p_attribute14 IN pa_candidates.attribute14%TYPE :=NULL ,
1029 p_attribute15 IN pa_candidates.attribute15%TYPE :=NULL ,
1030 -- start for bug#9468526 , Added default null values
1031 x_return_status OUT NOCOPY VARCHAR2, -- 4537865
1032 x_msg_count OUT NOCOPY NUMBER, -- 4537865
1033 x_msg_data OUT NOCOPY VARCHAR2) -- 4537865
1034 IS
1035 l_old_status_code VARCHAR2(30);
1036 l_status_code VARCHAR2(30);
1037 l_status_name VARCHAR2(80);
1038 l_old_system_status_code VARCHAR2(30);
1039 l_system_status_code VARCHAR2(30);
1040 l_reviewer_person_id NUMBER := 0;
1041 l_change_reason_code VARCHAR2(30) := null;
1042 l_review_comments VARCHAR2(2000) := null; /* Fix for Bug 7356131 */
1043 l_assignment_id NUMBER;
1044 l_resource_id NUMBER;
1045 l_return_status VARCHAR2(1);
1046 l_new_cand_record_version_num NUMBER;
1047 l_old_record_version_number NUMBER;
1048 l_msg_count NUMBER := 0;
1049 l_msg_index_out NUMBER;
1050 l_msg_data VARCHAR2(2000);
1051 l_enable_wf_flag VARCHAR2(1);
1052 l_wf_item_type VARCHAR2(30);
1053 l_wf_process VARCHAR2(30);
1054
1055 BEGIN
1056 -- Initialize the Error Stack
1057 PA_DEBUG.init_err_stack('PA_CANDIDATE_PUB.Add_Candidate_Log');
1058
1059 -- initialize return_status to success
1060 x_return_status := FND_API.G_RET_STS_SUCCESS;
1061
1062 -- Clear the global PL/SQL message table. Added check of p_init_msg_list Bug 5130421: PJR Enhancements for Public APIs
1063 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list, FND_API.G_TRUE)) THEN
1064 FND_MSG_PUB.initialize;
1065 END IF;
1066
1067 SELECT record_version_number
1068 INTO l_old_record_version_number
1069 FROM pa_candidates
1070 WHERE candidate_id = p_candidate_id;
1071
1072 IF l_old_record_version_number <> p_cand_record_version_number THEN
1073 pa_utils.add_message
1074 (p_app_short_name => 'PA',
1075 p_msg_name => 'PA_XC_RECORD_CHANGED');
1076 RAISE FND_API.G_EXC_ERROR;
1077 END IF;
1078
1079 SELECT status_code
1080 INTO l_old_status_code
1081 FROM pa_candidates
1082 WHERE candidate_id=p_candidate_id;
1083
1084 l_status_code := p_status_code;
1085 l_review_comments := p_review_comments;
1086 l_change_reason_code := p_change_reason_code;
1087
1088 IF l_status_code is null THEN
1089 l_status_code := l_old_status_code;
1090 END IF;
1091
1092 -- this is for workflow.
1093 SELECT assignment_id,resource_id
1094 INTO l_assignment_id,l_resource_id
1095 FROM pa_candidates
1096 where candidate_id = p_candidate_id;
1097
1098 IF nvl(l_old_status_code,'-1') = nvl(l_status_code,'-1') THEN
1099 -- There is no status change
1100
1101 -- Check to see if change reason is passed.
1102 -- The user should not update Change Reason if status is not
1103 -- updated.
1104
1105 IF p_change_reason_code is not null THEN
1106 pa_utils.add_message
1107 (p_app_short_name => 'PA',
1108 p_msg_name => 'PA_CAND_NO_STATUS_CHANGE');
1109
1110 RAISE FND_API.G_EXC_ERROR;
1111 END IF;
1112
1113 -- check if a comment is passed. If no
1114 -- comment is passed, then return without making an entry in
1115 -- the log file.
1116 IF p_review_comments = FND_API.G_MISS_CHAR or
1117 p_review_comments is null THEN
1118 RETURN;
1119 END IF;
1120
1121 IF l_status_code is null THEN
1122 -- Status is null
1123 l_system_status_code := null;
1124 ELSE
1125 SELECT project_system_status_code
1126 INTO l_system_status_code
1127 FROM pa_project_statuses
1128 WHERE project_status_code = l_status_code
1129 AND status_type = 'CANDIDATE';
1130 END IF;
1131
1132 -- Since user status has not changed, assigning the same
1133 -- value to system status
1134 l_old_system_status_code := l_system_status_code;
1135
1136 -- Since pa_candidates has not been updated, the new record version number should be
1137 -- same as the original passed value.
1138 l_new_cand_record_version_num := p_cand_record_version_number;
1139 ELSE
1140 -- Status has changed.
1141
1142 -- Check if the change of status is allowed
1143 IF Pa_Project_Stus_Utils.Allow_Status_Change
1144 (o_status_code => l_old_status_code,
1145 n_status_code => l_status_code) = 'N'
1146 THEN
1147 -- Status Change is not allowed.
1148 pa_utils.add_message
1149 (p_app_short_name => 'PA',
1150 p_msg_name => 'PA_STATUS_CANT_CHANGE');
1151
1152 RAISE FND_API.G_EXC_ERROR;
1153 END IF;
1154
1155 -- Check if the change of status requires change reason
1156 -- to be specified.
1157 IF (pa_project_utils.Check_prj_stus_action_allowed(l_status_code, 'CANDIDATE_CHANGE_REASON') = 'Y')
1158 AND p_change_reason_code is null THEN
1159 pa_utils.add_message
1160 (p_app_short_name => 'PA',
1161 p_msg_name => 'PA_CAND_CHG_REASON_REQD');
1162 RAISE FND_API.G_EXC_ERROR;
1163 END IF;
1164
1165 -- Update pa_candidates with the new status code
1166 -- Added WHO Column update. Bug 7168412.
1167
1168 -- Added Attribute Columns update for Bug 9187892
1169
1170 UPDATE pa_candidates
1171 SET status_code = l_status_code,
1172 record_version_number = record_version_number + 1,
1173 LAST_UPDATE_DATE = SYSDATE,
1174 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1175 -- start for bug#9468526, Added nvl such that, if the passed value is null, existing one is retained.
1176 attribute_category = nvl(p_attribute_category,attribute_category),
1177 attribute1 = nvl(p_attribute1,attribute1),
1178 attribute2 = nvl(p_attribute2,attribute2),
1179 attribute3 = nvl(p_attribute3,attribute3),
1180 attribute4 = nvl(p_attribute4,attribute4),
1181 attribute5 = nvl(p_attribute5,attribute5),
1182 attribute6 = nvl(p_attribute6,attribute6),
1183 attribute7 = nvl(p_attribute7,attribute7),
1184 attribute8 = nvl(p_attribute8,attribute8),
1185 attribute9 = nvl(p_attribute9,attribute9),
1186 attribute10 = nvl(p_attribute10,attribute10),
1187 attribute11 = nvl(p_attribute11,attribute11),
1188 attribute12 = nvl(p_attribute12,attribute12),
1189 attribute13 = nvl(p_attribute13,attribute13),
1190 attribute14 = nvl(p_attribute14,attribute14),
1191 attribute15 = nvl(p_attribute15,attribute15)
1192 -- end for bug#9468526
1193 WHERE candidate_id = p_candidate_id AND
1194 record_version_number=p_cand_record_version_number;
1195
1196 -- Since pa_candidates has been updated, set the increased record version number to the local
1197 -- variabe to pass back as a out parameter.
1198 l_new_cand_record_version_num := p_cand_record_version_number+1;
1199
1200 IF l_status_code is null THEN
1201 l_system_status_code := null;
1202 ELSE
1203 SELECT project_system_status_code
1204 INTO l_system_status_code
1205 FROM pa_project_statuses
1206 WHERE project_status_code = l_status_code
1207 AND status_type = 'CANDIDATE';
1208 END IF;
1209
1210 IF l_old_status_code is null THEN
1211 l_old_system_status_code := null;
1212 ELSE
1213 SELECT project_system_status_code
1214 INTO l_old_system_status_code
1215 FROM pa_project_statuses
1216 WHERE project_status_code = l_old_status_code
1217 AND status_type = 'CANDIDATE';
1218 END IF;
1219
1220
1221 -- Update No_Of_Active_Candidates in PA_PROJECT_ASSIGNMENTS
1222 Update_No_Of_Active_Candidates(
1223 p_assignment_id => l_assignment_id,
1224 p_old_system_status_code => l_old_system_status_code,
1225 p_new_system_status_code => l_system_status_code,
1226 x_return_status => x_return_status);
1227
1228 -- 4537865 Earlier wrong check was made against l_return_status
1229 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1230 RAISE FND_API.G_EXC_ERROR;
1231 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1232 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1233 END IF;
1234 END IF;
1235
1236 -- set the updated record_version_number to the out parameter.
1237 x_cand_record_version_number := l_new_cand_record_version_num;
1238
1239 l_reviewer_person_id := Get_Person_Id;
1240
1241 INSERT INTO PA_CANDIDATE_REVIEWS
1242 (CANDIDATE_REVIEW_ID,
1243 CANDIDATE_ID,
1244 RECORD_VERSION_NUMBER,
1245 STATUS_CODE,
1246 REVIEWER_PERSON_ID,
1247 REVIEW_DATE,
1248 CHANGE_REASON_CODE,
1249 REVIEW_COMMENTS,
1250 CREATION_DATE,
1251 -- Added for bug 9187892
1252 ATTRIBUTE_CATEGORY,
1253 ATTRIBUTE1,
1254 ATTRIBUTE2,
1255 ATTRIBUTE3,
1256 ATTRIBUTE4,
1257 ATTRIBUTE5,
1258 ATTRIBUTE6,
1259 ATTRIBUTE7,
1260 ATTRIBUTE8,
1261 ATTRIBUTE9,
1262 ATTRIBUTE10,
1263 ATTRIBUTE11,
1264 ATTRIBUTE12,
1265 ATTRIBUTE13,
1266 ATTRIBUTE14,
1267 ATTRIBUTE15,
1268 CREATED_BY,
1269 LAST_UPDATE_DATE,
1270 LAST_UPDATED_BY)
1271 VALUES
1272 (
1273 PA_CANDIDATE_REVIEWS_S.nextval,
1274 p_candidate_id,
1275 1,
1276 p_status_code,
1277 l_reviewer_person_id,
1278 sysdate,
1279 l_change_reason_code,
1280 l_review_comments,
1281 sysdate,
1282 -- Added for bug 9187892
1283 p_attribute_category,
1284 p_attribute1,
1285 p_attribute2,
1286 p_attribute3,
1287 p_attribute4,
1288 p_attribute5,
1289 p_attribute6,
1290 p_attribute7,
1291 p_attribute8,
1292 p_attribute9,
1293 p_attribute10,
1294 p_attribute11,
1295 p_attribute12,
1296 p_attribute13,
1297 p_attribute14,
1298 p_attribute15,
1299 FND_GLOBAL.user_id,
1300 sysdate,
1301 FND_GLOBAL.user_id
1302 );
1303
1304 -- Check is the status change needs a workflow to be started
1305 IF l_system_status_code <> l_old_system_status_code THEN
1306
1307 SELECT ENABLE_WF_FLAG, WORKFLOW_ITEM_TYPE, WORKFLOW_PROCESS, PROJECT_STATUS_NAME
1308 INTO l_enable_wf_flag, l_wf_item_type, l_wf_process, l_status_name
1309 FROM PA_PROJECT_STATUSES
1310 WHERE status_type = 'CANDIDATE'
1311 AND project_status_code = l_status_code;
1312
1313 IF l_enable_wf_flag = 'Y' AND l_wf_item_type IS NOT NULL AND
1314 l_wf_process IS NOT NULL THEN
1315
1316 Start_Workflow(p_wf_item_type => l_wf_item_type,
1317 p_wf_process => l_wf_process,
1318 P_assignment_id => l_assignment_id,
1319 p_candidate_number => p_candidate_id,
1320 p_resource_id => l_resource_id,
1321 p_status_name => l_status_name,
1322 x_return_status => l_return_status,
1323 x_msg_count => l_msg_count,
1324 x_msg_data => l_msg_data);
1325
1326 END IF;
1327
1328 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1329 RAISE FND_API.G_EXC_ERROR;
1330 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1331 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1332 END IF;
1333 END IF;
1334
1335 EXCEPTION
1336 WHEN FND_API.G_EXC_ERROR THEN
1337 x_return_status := FND_API.G_RET_STS_ERROR;
1338 x_msg_count := FND_MSG_PUB.Count_Msg;
1339
1340 -- 4537865 : RESET OUT PARAM
1341 x_cand_record_version_number := NULL ;
1342
1343 IF x_msg_count = 1 THEN
1344 pa_interface_utils_pub.get_messages
1345 (p_encoded => FND_API.G_TRUE
1346 ,p_msg_index => 1
1347 ,p_msg_count => 1
1348 ,p_msg_data => l_msg_data
1349 ,p_data => x_msg_data
1350 ,p_msg_index_out => l_msg_index_out );
1351 END IF;
1352 WHEN OTHERS THEN
1353 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1354
1355 -- 4537865 : RESET OUT PARAM
1356 x_cand_record_version_number := NULL ;
1357
1358 fnd_msg_pub.add_exc_msg
1359 (p_pkg_name => 'PA_COMPETENCE_PUB'
1360 ,p_procedure_name => 'Add_Candidate_Log' );
1361
1362 x_msg_count := FND_MSG_PUB.Count_Msg;
1363
1364 IF x_msg_count = 1 THEN
1365 pa_interface_utils_pub.get_messages
1366 (p_encoded => FND_API.G_TRUE
1367 ,p_msg_index => 1
1368 ,p_msg_count => 1
1369 ,p_msg_data => l_msg_data
1370 ,p_data => x_msg_data
1371 ,p_msg_index_out => l_msg_index_out );
1372 END IF;
1373
1374 END ADD_CANDIDATE_LOG;
1375
1376 /* --------------------------------------------------------------------
1377 PROCEDURE: Update_Candidate
1378 PURPOSE: This Procedure will update candidate p_candidate_id's
1379 status or ranking.
1380 If the status changes, we will create a log entry in the
1381 PA_CANDIDATES_LOG table. we will also update the
1382 NO_OF_ACTIVE_CANDIDATES column in PA_PROJECT_STATUSES, if an
1383 active candidate goes inactive or vice versa
1384 Since no comment is passed, we will create the log with an empty
1385 log message. This API will be called from the Candidate List Page,
1386 where the status,change reason or ranking can be updated.
1387 PARAMETERS: p_candidate_id : Candidate Id of the candidate being
1388 updated
1389 p_status_code : Status Code for the candidate record.
1390 If the status is not changed, this
1391 field will hold the old status value.
1392 p_ranking : Ranking for the candidate
1393 If the ranking is not changed, this
1394 field will hold the old ranking value.
1395 p_change_reason_code : Change Reason for Status change.
1396 p_record_version_number : Record Version of the Candidate Record
1397 -------------------------------------------------------------------- */
1398 PROCEDURE Update_Candidate
1399 (p_candidate_id IN NUMBER,
1400 p_status_code IN VARCHAR2,
1401 p_ranking IN NUMBER,
1402 p_change_reason_code IN VARCHAR2,
1403 p_record_version_number IN NUMBER,
1404 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
1405 p_validate_status IN VARCHAR2 := FND_API.G_TRUE,
1406 -- Added for bug 9187892
1407 -- start for bug#9468526 , Added default null values
1408 p_attribute_category IN pa_candidates.attribute_category%TYPE :=NULL ,
1409 p_attribute1 IN pa_candidates.attribute1%TYPE :=NULL ,
1410 p_attribute2 IN pa_candidates.attribute2%TYPE :=NULL ,
1411 p_attribute3 IN pa_candidates.attribute3%TYPE :=NULL ,
1412 p_attribute4 IN pa_candidates.attribute4%TYPE :=NULL ,
1413 p_attribute5 IN pa_candidates.attribute5%TYPE :=NULL ,
1414 p_attribute6 IN pa_candidates.attribute6%TYPE :=NULL ,
1415 p_attribute7 IN pa_candidates.attribute7%TYPE :=NULL ,
1416 p_attribute8 IN pa_candidates.attribute8%TYPE :=NULL ,
1417 p_attribute9 IN pa_candidates.attribute9%TYPE :=NULL ,
1418 p_attribute10 IN pa_candidates.attribute10%TYPE :=NULL ,
1419 p_attribute11 IN pa_candidates.attribute11%TYPE :=NULL ,
1420 p_attribute12 IN pa_candidates.attribute12%TYPE :=NULL ,
1421 p_attribute13 IN pa_candidates.attribute13%TYPE :=NULL ,
1422 p_attribute14 IN pa_candidates.attribute14%TYPE :=NULL ,
1423 p_attribute15 IN pa_candidates.attribute15%TYPE :=NULL ,
1424 -- End for bug#9468526 , Added default null values
1425 x_record_version_number OUT NOCOPY NUMBER, -- 4537865 Added nocopy hint
1426 x_msg_count OUT NOCOPY NUMBER, -- 4537865 Added nocopy hint
1427 x_msg_data OUT NOCOPY VARCHAR2, -- 4537865 Added nocopy hint
1428 x_return_status OUT NOCOPY VARCHAR2) -- 4537865 Added nocopy hint
1429 IS
1430 l_assignment_id NUMBER;
1431 l_resource_id NUMBER;
1432 l_old_status_code VARCHAR2(30);
1433 l_status_code VARCHAR2(30);
1434 l_status_name VARCHAR2(80);
1435 l_old_system_status_code VARCHAR2(30);
1436 l_system_status_code VARCHAR2(30);
1437 l_old_record_version_number NUMBER;
1438 l_old_candidate_ranking NUMBER;
1439 l_change_reason_code VARCHAR2(30);
1440 l_reviewer_person_id NUMBER := 0;
1441 l_exists VARCHAR2(1);
1442 l_msg_index_out NUMBER ;
1443 l_return_status VARCHAR2(1);
1444 l_msg_data VARCHAR2(2000);
1445 l_msg_count NUMBER := 0;
1446 l_enable_wf_flag VARCHAR2(1);
1447 l_wf_item_type VARCHAR2(30);
1448 l_wf_process VARCHAR2(30);
1449
1450 BEGIN
1451 -- Initialize the Error Stack
1452 PA_DEBUG.init_err_stack('PA_CANDIDATE_PUB.Add_Candidate_Log');
1453
1454 -- initialize return_status to success
1455 x_return_status := FND_API.G_RET_STS_SUCCESS;
1456
1457 -- Clear the global PL/SQL message table
1458 IF FND_API.TO_BOOLEAN( p_init_msg_list ) THEN
1459 FND_MSG_PUB.initialize;
1460 END IF;
1461
1462 l_status_code := p_status_code;
1463 l_change_reason_code := p_change_reason_code;
1464
1465 -- Get Old Value
1466 BEGIN
1467 SELECT assignment_id,
1468 status_code,
1469 record_version_number,
1470 resource_id,
1471 candidate_ranking
1472 INTO l_assignment_id,
1473 l_old_status_code,
1474 l_old_record_version_number,
1475 l_resource_id,
1476 l_old_candidate_ranking
1477 FROM pa_candidates
1478 WHERE candidate_id = p_candidate_id;
1479
1480 EXCEPTION
1481 WHEN NO_DATA_FOUND THEN
1482 pa_utils.add_message
1483 (p_app_short_name => 'PA',
1484 p_msg_name => 'PA_CAND_NOT_FOUND');
1485 RAISE FND_API.G_EXC_ERROR;
1486 WHEN OTHERS THEN
1487 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1488 END;
1489
1490 IF l_old_record_version_number <> p_record_version_number THEN
1491 pa_utils.add_message
1492 (p_app_short_name => 'PA',
1493 p_msg_name => 'PA_XC_RECORD_CHANGED');
1494 RAISE FND_API.G_EXC_ERROR;
1495 END IF;
1496
1497 IF l_status_code is null THEN
1498 l_system_status_code := null;
1499 ELSE
1500 SELECT project_system_status_code
1501 INTO l_system_status_code
1502 FROM pa_project_statuses
1503 WHERE project_status_code = l_status_code
1504 AND status_type = 'CANDIDATE';
1505 END IF;
1506
1507 IF l_old_status_code is null THEN
1508 l_old_system_status_code := null;
1509 ELSE
1510 SELECT project_system_status_code
1511 INTO l_old_system_status_code
1512 FROM pa_project_statuses
1513 WHERE project_status_code = l_old_status_code
1514 AND status_type = 'CANDIDATE';
1515 END IF;
1516
1517 IF l_system_status_code = 'CANDIDATE_SYSTEM_NOMINATED'
1518 AND l_old_system_status_code <> 'CANDIDATE_SYSTEM_NOMINATED' THEN
1519 pa_utils.add_message
1520 (p_app_short_name => 'PA',
1521 p_msg_name => 'PA_CAND_STATUS_NOT_ALLOWED');
1522 RAISE FND_API.G_EXC_ERROR;
1523 END IF;
1524
1525 IF FND_API.TO_BOOLEAN(p_validate_status) THEN
1526 -- Status has not changed. Check to see if change reason is passed.
1527 -- The user should not update Change Reason if status is not
1528 -- updated.
1529 IF nvl(l_old_status_code,'-1') = nvl(l_status_code,'-1') THEN
1530
1531 IF p_change_reason_code is not null THEN
1532 pa_utils.add_message
1533 (p_app_short_name => 'PA',
1534 p_msg_name => 'PA_CAND_NO_STATUS_CHANGE');
1535 RAISE FND_API.G_EXC_ERROR;
1536
1537 -- If nothing has been updated, just return without updating anything.
1538 ELSIF p_change_reason_code is null
1539 AND nvl(l_old_candidate_ranking,'-1') = nvl(p_ranking,'-1') THEN
1540 RETURN;
1541 END IF;
1542
1543 -- Status has changed
1544 -- Check if status can change from l_old_status to p_status.
1545 ELSE
1546 -- if Status Change is not allowed.
1547 IF Pa_Project_Stus_Utils.Allow_Status_Change
1548 (o_status_code => l_old_status_code,
1549 n_status_code => l_status_code) = 'N' THEN
1550 pa_utils.add_message
1551 (p_app_short_name => 'PA',
1552 p_msg_name => 'PA_STATUS_CANT_CHANGE');
1553
1554 RAISE FND_API.G_EXC_ERROR;
1555 END IF;
1556
1557 -- Check if reason is required for the status change
1558 IF (pa_project_utils.Check_prj_stus_action_allowed
1559 (l_status_code, 'CANDIDATE_CHANGE_REASON') = 'Y') AND
1560 p_change_reason_code is null THEN
1561 pa_utils.add_message
1562 (p_app_short_name => 'PA',
1563 p_msg_name => 'PA_CAND_CHG_REASON_REQD');
1564
1565 RAISE FND_API.G_EXC_ERROR;
1566 END IF;
1567 END IF;
1568 END IF;
1569
1570 -- Added WHO Column update. Bug 7168412.
1571 -- Added Attribute Columns update for Bug 9187892
1572 UPDATE pa_candidates
1573 SET
1574 status_code = l_status_code,
1575 candidate_ranking = p_ranking,
1576 record_version_number = p_record_version_number+1,
1577 LAST_UPDATE_DATE = SYSDATE,
1578 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1579 -- start for bug#9468526, Added nvl such that, if the passed value is null, existing one is retained.
1580 attribute_category = nvl(p_attribute_category,attribute_category),
1581 attribute1 = nvl(p_attribute1,attribute1),
1582 attribute2 = nvl(p_attribute2,attribute2),
1583 attribute3 = nvl(p_attribute3,attribute3),
1584 attribute4 = nvl(p_attribute4,attribute4),
1585 attribute5 = nvl(p_attribute5,attribute5),
1586 attribute6 = nvl(p_attribute6,attribute6),
1587 attribute7 = nvl(p_attribute7,attribute7),
1588 attribute8 = nvl(p_attribute8,attribute8),
1589 attribute9 = nvl(p_attribute9,attribute9),
1590 attribute10 = nvl(p_attribute10,attribute10),
1591 attribute11 = nvl(p_attribute11,attribute11),
1592 attribute12 = nvl(p_attribute12,attribute12),
1593 attribute13 = nvl(p_attribute13,attribute13),
1594 attribute14 = nvl(p_attribute14,attribute14),
1595 attribute15 = nvl(p_attribute15,attribute15)
1596 -- end for bug#9468526
1597 WHERE
1598 candidate_id = p_candidate_id AND
1599 record_version_number = p_record_version_number;
1600
1601 -- set the updated record_version_number to the out parameter.
1602 x_record_version_number := p_record_version_number+1;
1603
1604
1605 -- Create an entry on the log table if the status has changed.
1606 IF nvl(l_old_status_code,'-1') <> nvl(l_status_code,'-1') THEN
1607 l_reviewer_person_id := Get_Person_Id;
1608
1609 INSERT INTO PA_CANDIDATE_REVIEWS
1610 (CANDIDATE_REVIEW_ID,
1611 CANDIDATE_ID,
1612 RECORD_VERSION_NUMBER,
1613 STATUS_CODE,
1614 REVIEWER_PERSON_ID,
1615 REVIEW_DATE,
1616 REVIEW_COMMENTS,
1617 CHANGE_REASON_CODE,
1618 CREATION_DATE,
1619 -- Added for bug 9187892
1620 ATTRIBUTE_CATEGORY,
1621 ATTRIBUTE1,
1622 ATTRIBUTE2,
1623 ATTRIBUTE3,
1624 ATTRIBUTE4,
1625 ATTRIBUTE5,
1626 ATTRIBUTE6,
1627 ATTRIBUTE7,
1628 ATTRIBUTE8,
1629 ATTRIBUTE9,
1630 ATTRIBUTE10,
1631 ATTRIBUTE11,
1632 ATTRIBUTE12,
1633 ATTRIBUTE13,
1634 ATTRIBUTE14,
1635 ATTRIBUTE15,
1636 CREATED_BY,
1637 LAST_UPDATE_DATE,
1638 LAST_UPDATED_BY)
1639 VALUES
1640 (
1641 PA_CANDIDATE_REVIEWS_S.nextval,
1642 p_candidate_id,
1643 1,
1644 l_status_code,
1645 l_reviewer_person_id,
1646 sysdate,
1647 null,
1648 l_change_reason_code,
1649 sysdate,
1650 -- Added for bug 9187892
1651 p_attribute_category,
1652 p_attribute1,
1653 p_attribute2,
1654 p_attribute3,
1655 p_attribute4,
1656 p_attribute5,
1657 p_attribute6,
1658 p_attribute7,
1659 p_attribute8,
1660 p_attribute9,
1661 p_attribute10,
1662 p_attribute11,
1663 p_attribute12,
1664 p_attribute13,
1665 p_attribute14,
1666 p_attribute15,
1667 FND_GLOBAL.user_id,
1668 sysdate,
1669 FND_GLOBAL.user_id
1670 );
1671
1672 -- Update No_Of_Active_Candidates in PA_PROJECT_ASSIGNMENTS
1673 Update_No_Of_Active_Candidates(
1674 p_assignment_id => l_assignment_id,
1675 p_old_system_status_code => l_old_system_status_code,
1676 p_new_system_status_code => l_system_status_code,
1677 x_return_status => l_return_status);
1678
1679 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1680 RAISE FND_API.G_EXC_ERROR;
1681 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1682 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1683 END IF;
1684
1685 SELECT ENABLE_WF_FLAG, WORKFLOW_ITEM_TYPE, WORKFLOW_PROCESS, PROJECT_STATUS_NAME
1686 INTO l_enable_wf_flag, l_wf_item_type, l_wf_process, l_status_name
1687 FROM PA_PROJECT_STATUSES
1688 WHERE status_type = 'CANDIDATE'
1689 AND project_status_code = l_status_code;
1690
1691 -- Check is the status change needs a workflow to be started
1692 IF l_enable_wf_flag = 'Y' AND l_wf_item_type IS NOT NULL AND
1693 l_wf_process IS NOT NULL THEN
1694
1695 Start_Workflow(
1696 p_wf_item_type => l_wf_item_type,
1697 p_wf_process => l_wf_process,
1698 P_assignment_id => l_assignment_id,
1699 p_candidate_number => p_candidate_id,
1700 p_resource_id => l_resource_id,
1701 p_status_name => l_status_name,
1702 x_return_status => l_return_status,
1703 x_msg_count => l_msg_count,
1704 x_msg_data => l_msg_data);
1705 END IF;
1706
1707 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1708 RAISE FND_API.G_EXC_ERROR;
1709 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1710 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1711 END IF;
1712 END IF;
1713
1714 EXCEPTION
1715 WHEN FND_API.G_EXC_ERROR THEN
1716 x_return_status := FND_API.G_RET_STS_ERROR;
1717 x_msg_count := FND_MSG_PUB.Count_Msg;
1718
1719 -- 4537865 : RESET OUT PARAM
1720 x_record_version_number := NULL ;
1721
1722 IF x_msg_count = 1 THEN
1723 pa_interface_utils_pub.get_messages
1724 (p_encoded => FND_API.G_TRUE,
1725 p_msg_index => 1,
1726 p_msg_count => 1 ,
1727 p_msg_data => l_msg_data ,
1728 p_data => x_msg_data,
1729 p_msg_index_out => l_msg_index_out );
1730 END IF;
1731
1732 WHEN OTHERS THEN
1733 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1734
1735 -- 4537865 : RESET OUT PARAM
1736 x_record_version_number := NULL ;
1737
1738 fnd_msg_pub.add_exc_msg
1739 (p_pkg_name => 'PA_COMPETENCE_PUB',
1740 p_procedure_name => 'Update_Candidate' );
1741
1742 x_msg_count := FND_MSG_PUB.Count_Msg;
1743
1744
1745 IF x_msg_count = 1 THEN
1746 pa_interface_utils_pub.get_messages
1747 (p_encoded => FND_API.G_TRUE
1748 ,p_msg_index => 1
1749 ,p_msg_count => 1
1750 ,p_msg_data => l_msg_data
1751 ,p_data => x_msg_data
1752 ,p_msg_index_out => l_msg_index_out );
1753 END IF;
1754
1755 END Update_Candidate;
1756
1757 /* --------------------------------------------------------------------
1758 FUNCTION: Get_Competence_Match
1759 PURPOSE: This function will return the competence match for the person
1760 p_person_id for the assigment pa_assignment_id.
1761 -------------------------------------------------------------------- */
1762
1763 FUNCTION Get_Competence_Match
1764 ( p_person_id IN NUMBER
1765 , p_assignment_id IN NUMBER
1766 )
1767 RETURN VARCHAR2
1768 IS
1769 --declare local variables
1770 l_mandatory_competence_count NUMBER:= 0;
1771 l_mandatory_competence_match NUMBER:= 0;
1772 l_optional_competence_count NUMBER:= 0;
1773 l_optional_competence_match NUMBER:= 0;
1774 l_competence_match VARCHAR2(30);
1775
1776 BEGIN
1777
1778 PA_SEARCH_GLOB.Check_Competence_Match
1779 (p_search_mode => 'RESOURCE',
1780 p_person_id => p_person_id,
1781 p_requirement_id => p_assignment_id,
1782 x_mandatory_match => l_mandatory_competence_match,
1783 x_mandatory_count => l_mandatory_competence_count,
1784 x_optional_match => l_optional_competence_match,
1785 x_optional_count => l_optional_competence_count);
1786
1787 -- Angie updated to fix bug 1581223 : COMPETENCE MATCH FOR CANDIDATES PAGE SHOULD INCLUDE
1788 -- ALL COMPETENCIES. Paranthesis here is needed otherwise it will throw exception.
1789 l_competence_match := (l_mandatory_competence_match + l_optional_competence_match) || '/'
1790 || (l_mandatory_competence_count + l_optional_competence_count);
1791
1792 RETURN l_competence_match;
1793 END Get_Competence_Match;
1794
1795
1796 /* --------------------------------------------------------------------
1797 FUNCTION: Check_Availability
1798 PURPOSE: This function will return the availability for the person
1799 p_person_id for the assigment pa_assignment_id.
1800 -------------------------------------------------------------------- */
1801 FUNCTION Check_Availability(p_resource_id IN NUMBER,
1802 p_assignment_id IN NUMBER,
1803 p_project_id IN NUMBER)
1804 RETURN NUMBER
1805 IS
1806 l_availability NUMBER;
1807 BEGIN
1808 l_availability := PA_SEARCH_GLOB.Check_Availability(
1809 p_resource_id => p_resource_id,
1810 p_assignment_id => p_assignment_id,
1811 p_project_id => p_project_id);
1812 RETURN l_availability;
1813 END Check_Availability;
1814
1815 /* --------------------------------------------------------------------
1816 FUNCTION: Check_And_Get_Proj_Customer
1817 PURPOSE:
1818 -------------------------------------------------------------------- */
1819 PROCEDURE Check_And_Get_Proj_Customer ( p_project_id IN NUMBER
1820 ,x_customer_id OUT NOCOPY NUMBER -- 4537865 Added nocopy hint
1821 ,x_customer_name OUT NOCOPY VARCHAR2 ) -- 4537865 Added nocopy hint
1822 IS
1823
1824 -- 4363092 TCA changes, replaced RA views with HZ tables
1825
1826 /*
1827 CURSOR project_customers IS
1828 SELECT ppc.customer_id,rac.customer_name
1829 FROM pa_project_customers ppc,
1830 ra_customers rac
1831 WHERE ppc.project_id = p_project_id
1832 AND rac.customer_id = ppc.customer_id ;
1833 */
1834
1835 CURSOR project_customers IS
1836 SELECT ppc.customer_id, substrb(party.party_name,1,50) customer_name
1837 FROM pa_project_customers ppc,
1838 hz_parties party,
1839 hz_cust_accounts cust_acct
1840 WHERE ppc.project_id = p_project_id
1841 AND cust_acct.cust_account_id = ppc.customer_id
1842 and party.party_id = cust_acct.party_id;
1843
1844 -- 4363092 end
1845
1846 l_count NUMBER := 0;
1847
1848 BEGIN
1849 FOR c1 in project_customers LOOP
1850 l_count := l_count + 1;
1851 IF l_count > 1 THEN
1852 x_customer_name := null;
1853 x_customer_id := null;
1854 EXIT;
1855 END IF;
1856 x_customer_name := c1.customer_name;
1857 x_customer_id := c1.customer_id;
1858 END LOOP;
1859
1860 EXCEPTION
1861 WHEN OTHERS THEN
1862 -- 4537865 RESET OUT PARAM
1863 x_customer_id := NULL ;
1864 x_customer_name := NULL ;
1865 RAISE;
1866
1867 END Check_And_Get_proj_customer;
1868
1869 /* --------------------------------------------------------------------
1870 FUNCTION: Check_Candidacy
1871 PURPOSE: This Procedure accepts a count of resources, and a
1872 list of resource ids (separated by ",", and checks if each
1873 of the resource is a candidate on p_assignment_id.
1874 It returns back a list of 0s and 1s.
1875 For every resource who is not a candidate, it will return
1876 a "1". For every resource who is candidate, it will return
1877 a 0. The client side will display the resources as candidates
1878 based on the value passed for the corresponding resource.
1879 A message will all be passed back which states all the
1880 resources which are candidates. This API is called from
1881 t12a, if that page is called from T10 (where a list of
1882 resources could be selected to create as candidates)
1883 -------------------------------------------------------------------- */
1884 PROCEDURE Check_Candidacy
1885 (p_assignment_id IN NUMBER,
1886 p_resource_count IN NUMBER,
1887 p_resource_list IN VARCHAR2,
1888 x_resource_list OUT NOCOPY VARCHAR2, -- 4537865 Added nocopy hint
1889 x_msg_count OUT NOCOPY NUMBER, -- 4537865 Added nocopy hint
1890 x_invalid_candidates OUT NOCOPY VARCHAR2, -- 4537865 Added nocopy hint
1891 x_return_status OUT NOCOPY VARCHAR2) -- 4537865 Added nocopy hint
1892 IS
1893 l_in_resource_list VARCHAR2(1000);
1894 l_resource_list VARCHAR2(1000);
1895 l_resource_id NUMBER;
1896 l_candidate_list VARCHAR2(4000);
1897 l_delim VARCHAR2(1) := ',';
1898 l_resource_name VARCHAR2(240);
1899 initial NUMBER;
1900 J NUMBER;
1901 nextpos NUMBER;
1902 l_candidate_exists BOOLEAN := FALSE;
1903
1904 l_msg_count NUMBER := 0;
1905 l_data VARCHAR2(500);
1906 l_msg_index_out NUMBER ;
1907 BEGIN
1908 x_return_status := FND_API.G_RET_STS_SUCCESS;
1909
1910 l_in_resource_list := p_resource_list || ',';
1911 j:= 1;
1912 initial := 1;
1913 nextpos := INSTR(l_in_resource_list,l_delim,1,j);
1914
1915 FOR I in 1..p_resource_count LOOP
1916
1917 l_resource_id := to_number(SUBSTR(l_in_resource_list,initial,nextpos-initial));
1918
1919 initial := nextpos + 1.0;
1920
1921 j:= j + 1.0;
1922
1923 nextpos := INSTR(l_in_resource_list,l_delim,1,j);
1924
1925 IF IS_CAND_ON_ASSIGNMENT(l_resource_id,p_assignment_id) = 'Y' THEN
1926
1927 IF l_resource_list is null THEN
1928 l_resource_list := 'Y';
1929 ELSE
1930 l_resource_list := l_resource_list || ',' || 'Y';
1931 END IF;
1932
1933 l_resource_name := Get_Resource_Name(l_resource_id);
1934
1935 IF l_resource_name is not null THEN
1936 IF l_candidate_list is null THEN
1937 l_candidate_list := l_candidate_list || ' ' || l_resource_name;
1938 ELSE
1939 l_candidate_list := l_candidate_list || ',' || ' ' || l_resource_name;
1940 END IF;
1941 l_candidate_exists := TRUE;
1942 END IF;
1943
1944 ELSE
1945
1946 IF l_resource_list is null THEN
1947 l_resource_list := 'N';
1948 ELSE
1949 l_resource_list := l_resource_list || ',' || 'N';
1950 END IF;
1951
1952 END IF;
1953 END LOOP;
1954
1955
1956 IF l_candidate_exists THEN
1957 x_invalid_candidates := l_candidate_list;
1958 x_msg_count := 1;
1959 END IF;
1960
1961 x_return_status := FND_API.G_RET_STS_SUCCESS;
1962 x_resource_list := l_resource_list;
1963
1964 EXCEPTION
1965 WHEN OTHERS THEN
1966 -- 4537865 RESET OUT PARAMS
1967 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
1968 x_msg_count := 1;
1969 x_resource_list := NULL ;
1970 x_invalid_candidates := NULL ;
1971
1972 If Fnd_Msg_Pub.Check_Msg_Level(Fnd_Msg_Pub.G_Msg_Lvl_UnExp_Error) Then
1973 Fnd_Msg_Pub.Add_Exc_Msg
1974 ( P_Pkg_Name => 'PA_CANDIDATE_PUB',
1975 P_Procedure_Name => 'Check_Candidacy');
1976
1977 End If;
1978 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1979
1980 END;
1981
1982
1983
1984 /* --------------------------------------------------------------------
1985 FUNCTION: Start_Workflow
1986 PURPOSE:
1987 -------------------------------------------------------------------- */
1988 Procedure Start_Workflow(p_wf_item_type IN VARCHAR2,
1989 p_wf_process IN VARCHAR2,
1990 p_assignment_id IN NUMBER,
1991 p_candidate_number IN NUMBER,
1992 p_resource_id IN NUMBER,
1993 p_status_name IN VARCHAR2,
1994 x_return_status OUT NOCOPY VARCHAR2, -- 4537865
1995 x_msg_count OUT NOCOPY NUMBER, -- 4537865
1996 x_msg_data OUT NOCOPY VARCHAR2) -- 4537865
1997 IS
1998 CURSOR l_assignments_csr IS
1999 SELECT ppa.assignment_id,
2000 ppa.assignment_name,
2001 ppa.assignment_effort,
2002 ppa.additional_information,
2003 ppa.description,
2004 ppa.note_to_approver,
2005 ppa.project_id,
2006 ppa.resource_id,
2007 ppa.start_date,
2008 ppa.end_date,
2009 ppa.status_code,
2010 ppa.apprvl_status_code,
2011 ppa.pending_approval_flag,
2012 ppa.assignment_type
2013 FROM pa_project_assignments ppa
2014 WHERE assignment_id = p_assignment_id;
2015
2016 CURSOR l_stus_csr (c_status_code IN VARCHAR2) IS
2017 SELECT ps.wf_success_status_code,
2018 ps.wf_failure_status_code,
2019 ps.project_status_name
2020 FROM pa_project_statuses ps
2021 WHERE project_status_code = c_status_code;
2022
2023 CURSOR l_resource_csr(l_resource_id IN NUMBER, p_start_date IN DATE) IS
2024 SELECT res.resource_name,
2025 res.person_id resource_person_id,
2026 res.resource_id,
2027 hou.name resource_organization_name,
2028 res.manager_id
2029 FROM pa_resources_denorm res,
2030 hr_all_organization_units hou
2031 WHERE res.resource_id = l_resource_id
2032 AND hou.organization_id = res.resource_organization_id
2033 AND p_start_date BETWEEN resource_effective_start_date
2034 AND resource_effective_end_date
2035 AND res.schedulable_flag = 'Y';
2036
2037 CURSOR l_projects_csr(l_project_id IN NUMBER) IS
2038 SELECT pap.project_id project_id,
2039 pap.name name,
2040 pap.segment1 segment1,
2041 pap.carrying_out_organization_id carrying_out_organization_id,
2042 pap.location_id,
2043 hr.name organization_name,
2044 NVL(pt.administrative_flag,'N') admin_flag
2045 FROM pa_projects_all pap,
2046 hr_all_organization_units hr,
2047 pa_project_types_all pt
2048 WHERE pap.project_id = l_project_id
2049 AND pap.carrying_out_organization_id =
2050 hr.organization_id
2051 AND pap.org_id = pt.org_id -- Added for Bug 5389093
2052 AND pt.project_type = pap.project_type;
2053
2054 l_assignments_rec l_assignments_csr%ROWTYPE;
2055 l_resource_rec l_resource_csr%ROWTYPE;
2056 l_projects_rec l_projects_csr%ROWTYPE;
2057
2058 l_itemkey VARCHAR2(30);
2059 l_responsibility_id NUMBER;
2060 l_resp_appl_id NUMBER;
2061
2062 l_resource_user_name VARCHAR2(320); /* Modified VARCHAR2(240) for bug 3158966 */
2063 l_resource_display_name VARCHAR2(360); /* Modified VARCHAR2(240) for bug 3158966 */
2064 l_res_manager_id NUMBER;
2065 l_res_manager_name VARCHAR2(240);
2066 l_res_manager_user_name VARCHAR2(320); /* Modified VARCHAR2(240) for bug 3158966 */
2067 l_res_manager_display_name VARCHAR2(360); /* Modified VARCHAR2(240) for bug 3158966 */
2068
2069 l_proj_mgr_person_id NUMBER;
2070 l_proj_mgr_name VARCHAR2(240);
2071 l_proj_mgr_display_name VARCHAR2(240);
2072
2073 l_project_party_id NUMBER;
2074 l_project_role_id NUMBER;
2075 l_project_role_name VARCHAR2(80);
2076
2077 l_asgmt_details_url VARCHAR2(600);
2078 l_resource_details_url VARCHAR2(600);
2079
2080 l_primarycontactid NUMBER := 0;
2081 l_primarycontactname VARCHAR2(240);
2082 l_primarycontact_user_name VARCHAR2(320); /* Modified VARCHAR2(240) for bug 3158966 */
2083 l_primarycontact_display_name VARCHAR2(360); /* Modified VARCHAR2(240) for bug 3158966 */
2084 l_notification_type VARCHAR2(80);
2085
2086 -- 4363092 TCA changes, replaced RA views with HZ tables
2087 /*
2088 l_customer_id ra_customers.customer_id%TYPE;
2089 l_customer_name ra_customers.customer_name%TYPE;
2090 */
2091
2092 l_customer_id hz_cust_accounts.cust_account_id%TYPE;
2093 l_customer_name hz_parties.party_name%TYPE;
2094 -- 4363092 end
2095
2096 l_in_nf_recipients_rec PA_CLIENT_EXTN_CAND_WF.Users_List_Tbltyp;
2097 l_out_nf_recipients_rec PA_CLIENT_EXTN_CAND_WF.Users_List_Tbltyp;
2098 l_number_of_recipients NUMBER;
2099 l_in_recp_rec_index NUMBER := 0;
2100 l_out_recp_rec_index NUMBER;
2101
2102 l_role_name VARCHAR2(320); /* Modified VARCHAR2(240) for bug 3158966 */
2103 l_role_display_name VARCHAR2(360); /* Modified VARCHAR2(240) for bug 3158966 */
2104 l_role_users VARCHAR2(300);
2105 l_resource_person_id NUMBER;
2106
2107 l_staff_owner_person_id_tbl system.pa_num_tbl_type;
2108 l_staff_owner_user_name VARCHAR2(300);
2109 l_staff_owner_display_name VARCHAR2(360);
2110 l_is_recipient VARCHAR2(1);
2111
2112 l_return_status VARCHAR2(1);
2113 l_error_message_code VARCHAR2(30);
2114 l_msg_count NUMBER ;
2115 l_msg_data VARCHAR2(2000);
2116
2117 l_msg_index_out NUMBER ; -- 4537865
2118
2119 l_err_code NUMBER := 0;
2120 l_err_stage VARCHAR2(2000);
2121 l_err_stack VARCHAR2(2000);
2122
2123 l_count_recipients NUMBER := 0; -- Added for Bug 6144224
2124
2125 BEGIN
2126 -- initialize return_status to success
2127 x_return_status := FND_API.G_RET_STS_SUCCESS;
2128 --dbms_output.put_line('begin-- p_wf_item_type ' || p_wf_item_type);
2129 --dbms_output.put_line('begin-- p_wf_process ' || p_wf_process);
2130 --dbms_output.put_line('begin-- p_status_name ' || p_status_name);
2131
2132 -- Create the unique item key to launch WF with
2133 SELECT pa_prm_wf_item_key_s.nextval
2134 INTO l_itemkey
2135 FROM dual;
2136
2137 -- Now start fetching the details
2138 OPEN l_assignments_csr;
2139 FETCH l_assignments_csr INTO l_assignments_rec;
2140 IF l_assignments_csr%NOTFOUND THEN
2141 x_return_status := FND_API.G_RET_STS_ERROR;
2142 pa_utils.add_message (p_app_short_name => 'PA',
2143 p_msg_name => 'PA_INVALID_ASMGT_ID');
2144 CLOSE l_assignments_csr;
2145 ELSE
2146 CLOSE l_assignments_csr;
2147 END IF;
2148
2149 OPEN l_projects_csr(l_assignments_rec.project_id);
2150 FETCH l_projects_csr INTO l_projects_rec;
2151 IF l_projects_csr%NOTFOUND THEN
2152 x_return_status := FND_API.G_RET_STS_ERROR;
2153 pa_utils.add_message (p_app_short_name => 'PA',
2154 p_msg_name => 'PA_INVALID_PROJECT_ID');
2155 CLOSE l_projects_csr;
2156 ELSE
2157 CLOSE l_projects_csr;
2158 END IF;
2159
2160 Check_And_Get_Proj_Customer
2161 (p_project_id => l_assignments_rec.project_id
2162 ,x_customer_id => l_customer_id
2163 ,x_customer_name => l_customer_name );
2164
2165 OPEN l_resource_csr(p_resource_id, l_assignments_rec.start_date);
2166 FETCH l_resource_csr INTO l_resource_rec;
2167 IF l_resource_csr%NOTFOUND THEN
2168 -- bug#9150756 start
2169 CLOSE l_resource_csr;
2170 IF l_assignments_rec.start_date > SYSDATE THEN
2171 OPEN l_resource_csr(p_resource_id, (SYSDATE -1 ));
2172 FETCH l_resource_csr INTO l_resource_rec;
2173 IF l_resource_csr%NOTFOUND THEN
2174 CLOSE l_resource_csr;
2175 RETURN;
2176 ELSE
2177 CLOSE l_resource_csr;
2178 END IF ;
2179 END IF ;
2180 ELSE
2181 CLOSE l_resource_csr;
2182 END IF;
2183 -- bug#9150756 end
2184
2185
2186 -- Get the project manager details
2187 pa_project_parties_utils.get_curr_proj_mgr_details
2188 (p_project_id => l_projects_rec.project_id
2189 ,x_manager_person_id => l_proj_mgr_person_id
2190 ,x_manager_name => l_proj_mgr_name
2191 ,x_project_party_id => l_project_party_id
2192 ,x_project_role_id => l_project_role_id
2193 ,x_project_role_name => l_project_role_name
2194 ,x_return_status => l_return_status
2195 ,x_error_message_code => l_error_message_code );
2196
2197 --dbms_output.put_line('p_project_id: '|| l_projects_rec.project_id);
2198
2199 -- get the candidate person_id
2200 l_resource_person_id := l_resource_rec.resource_person_id;
2201 --dbms_output.put_line('l_resource_person_id :' || l_resource_person_id);
2202
2203 -- get the resource's name
2204 IF l_resource_person_id IS NOT NULL THEN
2205 wf_directory.getusername
2206 (p_orig_system => 'PER',
2207 p_orig_system_id => l_resource_person_id,
2208 p_name => l_resource_user_name,
2209 p_display_name => l_resource_display_name);
2210
2211 IF l_resource_user_name is not NULL THEN
2212 -- increase recipients_record index by one
2213 l_in_recp_rec_index := l_in_recp_rec_index + 1;
2214
2215 --dbms_output.put_line('l_resource_user_name :' || l_resource_user_name);
2216
2217 -- fill in the default recipients list(candidate, resource_manager,
2218 -- primary contact) to send it to the client extension procedure
2219 -- for the candidate workflow notification recipients.
2220
2221 l_in_nf_recipients_rec(l_in_recp_rec_index).User_Name := l_resource_user_name;
2222 l_in_nf_recipients_rec(l_in_recp_rec_index).Person_id := l_resource_person_id;
2223 l_in_nf_recipients_rec(l_in_recp_rec_index).Type := 'RESOURCE';
2224 END IF;
2225 END IF;
2226
2227 -- Call the procedure to get the primary_contact_id(l_primarycontactid)
2228 PA_RESOURCE_UTILS.get_org_primary_contact
2229 (P_ResourceId => p_resource_id
2230 ,p_assignment_id => l_assignments_rec.assignment_id
2231 ,x_PrimaryContactId => l_primarycontactid
2232 ,x_PrimaryContactName => l_primarycontactname
2233 ,x_ManagerId => l_res_manager_id
2234 ,x_ManagerName => l_res_manager_name
2235 ,x_return_Status => l_return_status
2236 ,x_msg_count => l_msg_count
2237 ,x_msg_data => l_msg_data);
2238 --dbms_output.put_line('1... l_primarycontactid :' || l_primarycontactid);
2239 --dbms_output.put_line('l_res_manager_id :' || l_res_manager_id);
2240 --dbms_output.put_line('l_return_status :' || l_return_status);
2241
2242 -- Now get the resource's manager name. -- bug 7623859
2243 IF l_res_manager_id IS NOT NULL THEN
2244 wf_directory.getusername
2245 (p_orig_system => 'PER',
2246 p_orig_system_id => l_res_manager_id,
2247 p_name => l_res_manager_user_name,
2248 p_display_name => l_res_manager_display_name);
2249
2250 IF l_res_manager_user_name is not NULL THEN
2251 -- increase recipients_record index by one
2252 l_in_recp_rec_index := l_in_recp_rec_index + 1;
2253
2254 --dbms_output.put_line('l_resource_rec.manager_id :' || l_resource_rec.manager_id);
2255 --dbms_output.put_line('l_res_manager_user_name :' || l_res_manager_user_name);
2256 --dbms_output.put_line('l_in_recp_rec_index :' || l_in_recp_rec_index);
2257
2258 -- fill in the default recipients list(candidate, resource_manager, primary contact)
2259 -- to send it to the client extension procedure for the candidate workflow notification
2260 -- recipients.
2261 l_in_nf_recipients_rec(l_in_recp_rec_index).User_Name := l_res_manager_user_name;
2262 l_in_nf_recipients_rec(l_in_recp_rec_index).Person_id := l_res_manager_id;
2263 l_in_nf_recipients_rec(l_in_recp_rec_index).Type := 'RESOURCE_MANAGER';
2264 END IF;
2265 END IF;
2266
2267
2268 -- Get the user_name of primary contact if the primary contact is not the same
2269 -- as resource_manager
2270 IF l_primarycontactid IS NOT NULL AND
2271 l_primarycontactid <> l_resource_rec.manager_id AND
2272 l_primarycontactid <> l_resource_person_id THEN --Added for Bug 3959762
2273 wf_directory.getusername
2274 (p_orig_system => 'PER',
2275 p_orig_system_id => l_primarycontactid,
2276 p_name => l_primarycontact_user_name,
2277 p_display_name => l_primarycontact_display_name);
2278
2279 IF l_primarycontact_user_name is not NULL THEN
2280 -- increase recipients_record index by one
2281 l_in_recp_rec_index := l_in_recp_rec_index + 1;
2282
2283 --dbms_output.put_line('l_primarycontactid :' || l_primarycontactid);
2284 --dbms_output.put_line('l_primarycontact_user_name :' || l_primarycontact_user_name);
2285 --dbms_output.put_line('l_in_recp_rec_index :' || l_in_recp_rec_index);
2286
2287 -- fill in the default recipients list(candidate, resource_manager, primary contact)
2288 -- to send it to the client extension procedure for the candidate workflow notification
2289 -- recipients.
2290 l_in_nf_recipients_rec(l_in_recp_rec_index).User_Name := l_primarycontact_user_name;
2291 l_in_nf_recipients_rec(l_in_recp_rec_index).Person_id := l_primarycontactid;
2292 l_in_nf_recipients_rec(l_in_recp_rec_index).Type := 'ORG_PRIMARY_CONTACT';
2293 END IF;
2294 END IF;
2295
2296 -- Call the procedure to get staffing owners
2297 PA_ASSIGNMENT_UTILS.get_all_staffing_owners
2298 ( p_assignment_id => l_assignments_rec.assignment_id
2299 ,p_project_id => l_assignments_rec.project_id
2300 ,x_person_id_tbl => l_staff_owner_person_id_tbl
2301 ,x_return_status => l_return_status
2302 ,x_error_message_code => l_msg_data);
2303
2304 IF l_staff_owner_person_id_tbl.COUNT > 0 THEN
2305
2306 FOR i in 1..l_staff_owner_person_id_tbl.COUNT LOOP
2307
2308 wf_directory.getusername
2309 (p_orig_system => 'PER',
2310 p_orig_system_id => l_staff_owner_person_id_tbl(i),
2311 p_name => l_staff_owner_user_name,
2312 p_display_name => l_staff_owner_display_name);
2313
2314 l_is_recipient := 'F';
2315 -- check if this person is already a recipient
2316 FOR j in 1..l_in_nf_recipients_rec.COUNT LOOP
2317 IF l_in_nf_recipients_rec(j).User_Name = l_staff_owner_user_name THEN
2318 l_is_recipient := 'T';
2319 END IF;
2320 END LOOP;
2321
2322 IF l_staff_owner_user_name is not NULL AND l_is_recipient = 'F' THEN
2323 -- increase recipients_record index by one
2324 l_in_recp_rec_index := l_in_recp_rec_index + 1;
2325 l_in_nf_recipients_rec(l_in_recp_rec_index).User_Name := l_staff_owner_user_name;
2326 l_in_nf_recipients_rec(l_in_recp_rec_index).Person_id := l_staff_owner_person_id_tbl(i);
2327 l_in_nf_recipients_rec(l_in_recp_rec_index).Type := 'STAFFING_OWNER';
2328 END IF;
2329 END LOOP;
2330 END IF;
2331
2332 -- Set the p_notification_type to pass it to client extension procedure for the candidate
2333 -- workflow notification recipients.
2334 IF p_wf_process = 'PRO_CANDIDATE_FYI_NOTIFICATION' THEN
2335 l_notification_type := 'FYI_NOTIFICATION';
2336 END IF;
2337 /*
2338 IF p_wf_process = 'PRO_CANDIDATE_NOMINATED' THEN
2339 l_notification_type := 'PENDING_REVIEW_FYI';
2340 ELSIF p_wf_process = 'PRO_CANDIDATE_DECLINED' THEN
2341 l_notification_type := 'DECLINED_FYI';
2342 END IF;
2343 */
2344
2345 --dbms_output.put_line('l_notification_type :'|| l_notification_type );
2346
2347 -- Call client extension procedure for the candidate workflow notification recipients.
2348 PA_CLIENT_EXTN_CAND_WF.Generate_NF_Recipients
2349 (p_project_id => l_projects_rec.project_id
2350 ,p_assignment_id => p_assignment_id
2351 ,p_candidate_number => p_candidate_number
2352 ,p_notification_type => l_notification_type
2353 ,p_in_list_of_recipients => l_in_nf_recipients_rec
2354 ,x_out_list_of_recipients => l_out_nf_recipients_rec
2355 ,x_number_of_recipients => l_number_of_recipients);
2356
2357 -- If the recipients record doesn't include any record, we don't need to keep processing
2358 -- workflow. Because there is no notification recipient anyway.
2359
2360 IF l_out_nf_recipients_rec.count < 1 THEN
2361 return;
2362 END IF;
2363
2364 -- get first index of l_out_nf_recipients_rec
2365 l_out_recp_rec_index := l_out_nf_recipients_rec.first;
2366 --dbms_output.put_line('l_out_recp_rec_index.first: ' || l_out_recp_rec_index);
2367 --dbms_output.put_line('l_number_of_recipients :' || l_number_of_recipients);
2368
2369 -- loop for l_out_nf_recipients_rec to generate l_role_name with recipients which has
2370 -- been passed by client extension procedure
2371 FOR I in 1..l_out_nf_recipients_rec.count LOOP
2372 IF FND_GLOBAL.USER_NAME <> l_out_nf_recipients_rec(l_out_recp_rec_index).User_Name THEN
2373
2374 l_count_recipients := l_count_recipients + 1 ; -- added for bug 6144224
2375 IF l_role_users is not null THEN
2376 l_role_users := l_role_users || ',' || l_out_nf_recipients_rec(l_out_recp_rec_index).User_Name;
2377 ELSE
2378 l_role_users := l_out_nf_recipients_rec(l_out_recp_rec_index).User_Name;
2379 END IF;
2380 END IF;
2381
2382 -- get next index of l_out_nf_recipients_rec
2383 l_out_recp_rec_index := l_out_nf_recipients_rec.next(l_out_recp_rec_index);
2384 END LOOP;
2385
2386 -- Create an ad hoc role and assign the users, which has been populated with recipients list,
2387 -- to this role.
2388 --dbms_output.put_line('l_role_users: ' || l_role_users);
2389 WF_DIRECTORY.CreateAdHocRole
2390 (role_users => l_role_users
2391 ,role_name => l_role_name
2392 ,role_display_name => l_role_display_name
2393 ,expiration_date => sysdate+1); -- Expiration_date set for bug#5962410
2394
2395 -- dbms_output.put_line('Role Name : ' || l_role_name);
2396 -- dbms_output.put_line('Role Display Name : ' || l_role_display_name);
2397
2398 -- We now have all the values in local variables
2399 -- Create the WF process
2400
2401 --dbms_output.put_line('Process: ' || p_wf_process);
2402 wf_engine.CreateProcess ( ItemType => p_wf_item_type,
2403 ItemKey => l_itemkey,
2404 process => p_wf_process
2405 );
2406
2407 -- Now set the values as appropriate in the WF attributes
2408
2409 /* Commented for Bug 6144224
2410 -- Set Role details attributes
2411 wf_engine.SetItemAttrText
2412 ( itemtype => p_wf_item_type,
2413 itemkey => l_itemkey,
2414 aname => 'ATTR_NOMINATE_ROLE',
2415 avalue => l_role_name
2416 );
2417
2418 wf_engine.SetItemAttrText
2419 ( itemtype => p_wf_item_type,
2420 itemkey => l_itemkey,
2421 aname => 'ATTR_DECLINED_ROLE',
2422 avalue => l_role_name
2423 );
2424
2425 wf_engine.SetItemAttrText
2426 ( itemtype => p_wf_item_type,
2427 itemkey => l_itemkey,
2428 aname => 'ATTR_CANDIDATE_ROLE',
2429 avalue => l_role_name
2430 );
2431 */
2432 --Added for bug 6144224 to set the Adhoc role
2433 wf_engine.SetItemAttrText
2434 ( itemtype => p_wf_item_type,
2435 itemkey => l_itemkey,
2436 aname => 'ATTR_ADHOC_ROLE',
2437 avalue => l_role_name
2438 );
2439
2440 --Added for bug 6144224 to set the final number of recipients
2441 wf_engine.SetItemAttrText
2442 ( itemtype => p_wf_item_type,
2443 itemkey => l_itemkey,
2444 aname => 'NUMBER_OF_RECIPIENTS',
2445 avalue => l_count_recipients
2446 );
2447
2448 --Added for bug 6144224 to set the loop counter
2449 wf_engine.SetItemAttrText
2450 ( itemtype => p_wf_item_type,
2451 itemkey => l_itemkey,
2452 aname => 'NF_LOOP_COUNTER',
2453 avalue => 0
2454 );
2455
2456 -- Set Project details attributes
2457
2458 wf_engine.SetItemAttrText
2459 ( itemtype => p_wf_item_type,
2460 itemkey => l_itemkey,
2461 aname => 'ATTR_PROJ_NUMBER',
2462 avalue => l_projects_rec.segment1
2463 );
2464
2465 wf_engine.SetItemAttrText
2466 ( itemtype => p_wf_item_type,
2467 itemkey => l_itemkey,
2468 aname => 'ATTR_PROJ_NAME',
2469 avalue => l_projects_rec.name
2470 );
2471
2472 wf_engine.SetItemAttrText
2473 ( itemtype => p_wf_item_type,
2474 itemkey => l_itemkey,
2475 aname => 'ATTR_PROJ_ORGANIZATION',
2476 avalue => l_projects_rec.organization_name
2477 );
2478
2479 IF l_customer_name IS NOT NULL THEN
2480 wf_engine.SetItemAttrText
2481 ( itemtype => p_wf_item_type,
2482 itemkey => l_itemkey,
2483 aname => 'ATTR_PROJ_CUSTOMER',
2484 avalue => l_customer_name
2485 );
2486
2487 END IF;
2488
2489
2490 -- Set Assignment related attributes
2491
2492 wf_engine.SetItemAttrText
2493 ( itemtype => p_wf_item_type,
2494 itemkey => l_itemkey,
2495 aname => 'ATTR_ASGMT_NAME',
2496 avalue => l_assignments_rec.assignment_name
2497 );
2498
2499 wf_engine.SetItemAttrText
2500 ( itemtype => p_wf_item_type,
2501 itemkey => l_itemkey,
2502 aname => 'ATTR_ASGMT_NAME',
2503 avalue => l_assignments_rec.assignment_name
2504 );
2505
2506 wf_engine.SetItemAttrText
2507 ( itemtype => p_wf_item_type,
2508 itemkey => l_itemkey,
2509 aname => 'ATTR_ASGMT_DESCRIPTION',
2510 avalue => l_assignments_rec.description
2511 );
2512
2513 wf_engine.SetItemAttrText
2514 ( itemtype => p_wf_item_type,
2515 itemkey => l_itemkey,
2516 aname => 'ATTR_ADDITIONAL_INFORMATION',
2517 avalue => l_assignments_rec.additional_information
2518 );
2519
2520 wf_engine.SetItemAttrNumber
2521 ( itemtype => p_wf_item_type,
2522 itemkey => l_itemkey,
2523 aname => 'ATTR_ASGMT_DURATION',
2524 avalue => (trunc(l_assignments_rec.end_date) -
2525 trunc(l_assignments_rec.start_date)+1)
2526 );
2527
2528 wf_engine.SetItemAttrNumber
2529 ( itemtype => p_wf_item_type,
2530 itemkey => l_itemkey,
2531 aname => 'ATTR_ASGMT_EFFORT',
2532 avalue => l_assignments_rec.assignment_effort
2533 );
2534
2535 -- Set resource related attributes
2536
2537 wf_engine.SetItemAttrText
2538 ( itemtype => p_wf_item_type,
2539 itemkey => l_itemkey,
2540 aname => 'ATTR_CAND_NUMBER',
2541 avalue => p_candidate_number
2542 );
2543
2544 --dbms_output.put_line('p_status_name' || p_status_name);
2545
2546 wf_engine.SetItemAttrText
2547 ( itemtype => p_wf_item_type,
2548 itemkey => l_itemkey,
2549 aname => 'ATTR_CAND_STATUS',
2550 avalue => p_status_name
2551 );
2552
2553 wf_engine.SetItemAttrText
2554 ( itemtype => p_wf_item_type,
2555 itemkey => l_itemkey,
2556 aname => 'ATTR_RESOURCE_NAME',
2557 avalue => l_resource_rec.resource_name
2558 );
2559
2560 wf_engine.SetItemAttrText
2561 ( itemtype => p_wf_item_type,
2562 itemkey => l_itemkey,
2563 aname => 'ATTR_RESOURCE_ORG',
2564 avalue => l_resource_rec.resource_organization_name
2565 );
2566
2567
2568 -- Set project manager attributes
2569
2570 wf_engine.SetItemAttrText
2571 ( itemtype => p_wf_item_type,
2572 itemkey => l_itemkey,
2573 aname => 'ATTR_PROJ_MANAGER',
2574 avalue => l_proj_mgr_name
2575 );
2576
2577 l_asgmt_details_url :=
2578 'JSP:/OA_HTML/OA.jsp?akRegionApplicationId=275&akRegionCode=PA_ASMT_LAYOUT&paCalledPage=OpenAsmt&addBreadCrumb=RP&paAssignmentId='||p_assignment_id;
2579
2580 l_resource_details_url :=
2581 'JSP:/OA_HTML/OA.jsp?akRegionApplicationId=275&akRegionCode=PA_VIEW_RESOURCE_LAYOUT&addBreadCrumb=RP&paResourceId='||p_resource_id;
2582
2583 wf_engine.SetItemAttrText
2584 ( itemtype => p_wf_item_type
2585 , itemkey => l_itemkey
2586 , aname => 'ATTR_ASGMT_DETAILS_URL_INFO'
2587 , avalue => l_asgmt_details_url
2588 );
2589
2590 wf_engine.SetItemAttrText
2591 ( itemtype => p_wf_item_type
2592 , itemkey => l_itemkey
2593 , aname => 'ATTR_RESOURCE_DETAILS_URL'
2594 , avalue => l_resource_details_url
2595 );
2596
2597 -- Now start the WF process
2598 wf_engine.StartProcess
2599 ( itemtype => p_wf_item_type,
2600 itemkey => l_itemkey );
2601
2602 -- Insert to PA tables wf process information.
2603 -- This is required for displaying notifications on PA pages.
2604
2605 PA_WORKFLOW_UTILS.Insert_WF_Processes
2606 (p_wf_type_code => 'CANDIDATE'
2607 ,p_item_type => p_wf_item_type
2608 ,p_item_key => l_itemkey
2609 ,p_entity_key1 => to_char(l_projects_rec.project_id)
2610 ,p_entity_key2 => to_char(p_assignment_id)
2611 ,p_description => NULL
2612 ,p_err_code => l_err_code
2613 ,p_err_stage => l_err_stage
2614 ,p_err_stack => l_err_stack
2615 );
2616
2617
2618 EXCEPTION
2619 WHEN OTHERS THEN
2620 -- 4537865 : RESET OUT PARAMS
2621 X_Return_Status := Fnd_Api.G_Ret_Sts_UnExp_Error;
2622
2623 fnd_msg_pub.add_exc_msg
2624 (p_pkg_name => 'PA_CANDIDATE_PUB',
2625 p_procedure_name => 'Start_Workflow');
2626
2627 -- 4537865 : RESET OUT PARAMS
2628 x_msg_count := FND_MSG_PUB.Count_Msg;
2629
2630 IF x_msg_count = 1 THEN
2631 pa_interface_utils_pub.get_messages
2632 (p_encoded => FND_API.G_TRUE
2633 ,p_msg_index => 1
2634 ,p_msg_count => 1
2635 ,p_msg_data => l_msg_data
2636 ,p_data => x_msg_data
2637 ,p_msg_index_out => l_msg_index_out );
2638 END IF;
2639
2640 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2641
2642 END Start_Workflow;
2643
2644
2645
2646 FUNCTION is_active_candidate(p_system_status_code IN VARCHAR2)
2647 RETURN VARCHAR2
2648 IS
2649 BEGIN
2650 IF (p_system_status_code='CANDIDATE_PENDING_REVIEW' OR
2651 p_system_status_code='CANDIDATE_UNDER_REVIEW' OR
2652 p_system_status_code='CANDIDATE_SYSTEM_NOMINATED' OR
2653 p_system_status_code='CANDIDATE_SUITABLE') THEN
2654 RETURN 'Y';
2655 ELSE
2656 RETURN 'N';
2657 END IF;
2658 END is_active_candidate;
2659
2660
2661
2662 FUNCTION Get_Person_Id
2663 RETURN NUMBER
2664 IS
2665 l_employee_id NUMBER;
2666 BEGIN
2667 SELECT employee_id
2668 INTO l_employee_id
2669 FROM fnd_user
2670 where user_id = FND_GLOBAL.USER_ID;
2671
2672 RETURN l_employee_id;
2673
2674 EXCEPTION
2675 WHEN OTHERS THEN
2676 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2677
2678 END Get_Person_Id;
2679
2680
2681
2682 FUNCTION Get_Resource_Name(p_resource_id in NUMBER)
2683 RETURN VARCHAR2
2684 IS
2685 l_resource_name VARCHAR2(240);
2686 BEGIN
2687 SELECT distinct(resource_name)
2688 INTO l_resource_name
2689 FROM pa_resources_denorm
2690 WHERE resource_id = p_resource_id
2691 AND rownum=1 -- 5345135
2692 ;
2693
2694 RETURN l_resource_name;
2695 EXCEPTION
2696 WHEN OTHERS THEN
2697 l_resource_name := null;
2698 RETURN l_resource_name;
2699 END Get_Resource_Name;
2700
2701 /* --------------------------------------------------------------------
2702 PROCEDURE: Delete_Candidates
2703 PURPOSE: This procedure is called by the Assignment module, once an
2704 assignment is deleted, the candidates in that assignment
2705 should be also deleted accordingly
2706 ---------------------------------------------------------------------*/
2707 PROCEDURE Delete_Candidates(p_assignment_id IN NUMBER,
2708 p_status_code IN VARCHAR2 DEFAULT NULL,
2709 x_return_status OUT NOCOPY VARCHAR2, -- 4537865
2710 x_msg_count OUT NOCOPY NUMBER, -- 4537865
2711 x_msg_data OUT NOCOPY VARCHAR2) -- 4537865
2712 IS
2713 TYPE number_tbl IS TABLE OF NUMBER
2714 INDEX BY BINARY_INTEGER;
2715
2716 -- 4537865
2717 l_msg_data VARCHAR2(2000);
2718 l_msg_index_out NUMBER ;
2719
2720 l_candidates_tbl number_tbl;
2721 BEGIN
2722 -- x_msg_count and x_msg_data are dummy variables, they are
2723 -- reserved for further expansion of this procedure
2724 x_return_status := FND_API.G_RET_STS_SUCCESS;
2725 x_msg_count := 0;
2726 x_msg_data := null;
2727
2728 IF p_status_code IS NULL THEN
2729 SELECT candidate_id
2730 BULK COLLECT INTO l_candidates_tbl
2731 FROM pa_candidates
2732 WHERE assignment_id = p_assignment_id;
2733
2734 DELETE FROM pa_candidates
2735 WHERE assignment_id = p_assignment_id;
2736 ELSE
2737 SELECT candidate_id
2738 BULK COLLECT INTO l_candidates_tbl
2739 FROM pa_candidates
2740 WHERE assignment_id = p_assignment_id
2741 AND status_code = p_status_code;
2742
2743 DELETE FROM pa_candidates
2744 WHERE assignment_id = p_assignment_id
2745 AND status_code = p_status_code;
2746 END IF;
2747
2748 IF l_candidates_tbl.count > 0 THEN
2749 FORALL i IN l_candidates_tbl.FIRST .. l_candidates_tbl.LAST
2750 DELETE FROM pa_candidate_reviews
2751 WHERE candidate_id = l_candidates_tbl(i);
2752 END IF;
2753
2754 EXCEPTION
2755 WHEN OTHERS THEN
2756 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2757 x_msg_data := SUBSTRB(SQLERRM,1,240) ;
2758
2759 fnd_msg_pub.add_exc_msg
2760 (p_pkg_name => 'PA_CANDIDATE_PUB',
2761 p_procedure_name => 'Delete_Candidates',
2762 p_error_text => x_msg_data);
2763
2764 -- 4537865 : RESET OUT PARAMS
2765 x_msg_count := FND_MSG_PUB.Count_Msg;
2766
2767 IF x_msg_count = 1 THEN
2768 pa_interface_utils_pub.get_messages
2769 (p_encoded => FND_API.G_TRUE
2770 ,p_msg_index => 1
2771 ,p_msg_count => 1
2772 ,p_msg_data => l_msg_data
2773 ,p_data => x_msg_data
2774 ,p_msg_index_out => l_msg_index_out );
2775 END IF;
2776
2777 RAISE;
2778 End Delete_Candidates;
2779
2780 /* --------------------------------------------------------------------
2781 PROCEDURE: Withdraw_Candidate
2782 PURPOSE: This procedure removes a candidate from an assignment
2783 ---------------------------------------------------------------------*/
2784
2785 PROCEDURE Withdraw_Candidate (p_candidate_id IN NUMBER,
2786 x_return_status OUT NOCOPY VARCHAR2, -- 4537865 : Added nocopy hint
2787 x_msg_count OUT NOCOPY NUMBER, -- 4537865 : Added nocopy hint
2788 x_msg_data OUT NOCOPY VARCHAR2) -- 4537865 : Added nocopy hint
2789 IS
2790 l_no_of_active_candidates NUMBER;
2791 l_record_version_number NUMBER;
2792 l_system_status_code VARCHAR2(30);
2793 l_assignment_id NUMBER;
2794 l_return_status VARCHAR2(1);
2795
2796 -- 4537865
2797 l_msg_data VARCHAR2(2000);
2798 l_msg_index_out NUMBER;
2799 BEGIN
2800 -- x_msg_count and x_msg_data are dummy variables, they are
2801 -- reserved for further expansion of this procedure
2802
2803 x_return_status := FND_API.G_RET_STS_SUCCESS;
2804 x_msg_count := 0;
2805 x_msg_data := null;
2806
2807 SELECT asmt.no_of_active_candidates,
2808 asmt.record_version_number,
2809 ps.project_system_status_code,
2810 asmt.assignment_id
2811 INTO l_no_of_active_candidates,
2812 l_record_version_number,
2813 l_system_status_code,
2814 l_assignment_id
2815 FROM pa_project_assignments asmt,
2816 pa_candidates cand,
2817 pa_project_statuses ps
2818 WHERE asmt.assignment_id = cand.assignment_id
2819 AND candidate_id = p_candidate_id
2820 AND cand.status_code = ps.project_status_code
2821 AND ps.status_type = 'CANDIDATE';
2822
2823 DELETE FROM pa_candidate_reviews
2824 WHERE candidate_id = p_candidate_id;
2825
2826 DELETE FROM pa_candidates
2827 WHERE candidate_id = p_candidate_id;
2828
2829 IF (is_active_candidate(l_system_status_code)='Y') THEN
2830 pa_project_assignments_pkg.Update_row(
2831 p_assignment_id => l_assignment_id,
2832 p_no_of_active_candidates => l_no_of_active_candidates-1,
2833 p_record_version_number => l_record_version_number,
2834 x_return_status => l_return_status );
2835 END IF;
2836
2837 EXCEPTION
2838
2839 WHEN OTHERS THEN
2840 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2841 -- 4537865 : RESET OUT PARAMS
2842 x_msg_data := SUBSTRB(SQLERRM,1,240) ;
2843
2844 fnd_msg_pub.add_exc_msg
2845 (p_pkg_name => 'PA_CANDIDATE_PUB',
2846 p_procedure_name => 'Withdraw_Candidate',
2847 p_error_text => x_msg_data);
2848
2849 -- 4537865 : RESET OUT PARAMS
2850 x_msg_count := FND_MSG_PUB.Count_Msg;
2851
2852 IF x_msg_count = 1 THEN
2853 pa_interface_utils_pub.get_messages
2854 (p_encoded => FND_API.G_TRUE
2855 ,p_msg_index => 1
2856 ,p_msg_count => 1
2857 ,p_msg_data => l_msg_data
2858 ,p_data => x_msg_data
2859 ,p_msg_index_out => l_msg_index_out );
2860 END IF;
2861 RAISE;
2862 END Withdraw_Candidate;
2863
2864 /* --------------------------------------------------------------------
2865 FUNCTION: Copy_Candidates
2866 PURPOSE: This API will be called when a requirement is partially filled.
2867 A new requirement will be created as a result of the partial
2868 fulfillment of the original requirement. This new requirment
2869 should have all the candidates (of the original requirement) who
2870 are effective as of the start date of the new requirement.
2871 This API is not expected to return any expected errors.
2872 PARAMETERS: p_old_requirement_id : Assignment_id of the old requirement
2873 p_new_requirement_id : Assignment_id of the new requirement
2874 p_new_start_date : Start Date of the new requirement
2875 -------------------------------------------------------------------- */
2876 PROCEDURE Copy_Candidates(p_old_requirement_id IN NUMBER,
2877 p_new_requirement_id IN NUMBER,
2878 p_new_start_date IN DATE,
2879 x_return_status OUT NOCOPY VARCHAR2, -- 4537865 : Added nocopy hint
2880 x_msg_count OUT NOCOPY NUMBER, -- 4537865 : Added nocopy hint
2881 x_msg_data OUT NOCOPY VARCHAR2) -- 4537865 : Added nocopy hint
2882 IS
2883 l_old_candidate_id NUMBER;
2884 l_new_candidate_id NUMBER;
2885 l_no_of_active_candidates NUMBER;
2886 l_record_version_number NUMBER;
2887 l_return_status VARCHAR2(1);
2888
2889 cursor c1 is
2890 SELECT cand.candidate_id,
2891 cand.resource_id,
2892 cand.status_code,
2893 cand.nominated_by_person_id,
2894 cand.nomination_date,
2895 cand.nomination_comments,
2896 cand.candidate_ranking
2897 FROM pa_candidates cand,
2898 pa_resources_denorm res
2899 WHERE assignment_id = p_old_requirement_id
2900 AND p_new_start_date BETWEEN
2901 res.resource_effective_start_date AND
2902 NVL(res.resource_effective_end_date, sysdate+1)
2903 AND res.resource_id = cand.resource_id
2904 AND res.schedulable_flag = 'Y';
2905
2906 cursor c3 is
2907 SELECT status_code,
2908 reviewer_person_id,
2909 review_date,
2910 change_reason_code,
2911 review_comments
2912 FROM pa_candidate_reviews
2913 WHERE candidate_id = l_old_candidate_id;
2914
2915 -- 4537865
2916 l_msg_data VARCHAR2(2000);
2917 l_msg_index_out NUMBER ;
2918
2919 BEGIN
2920
2921 -- 4537865 : INITIALIZE OUT PARAMS
2922
2923 l_return_status := FND_API.G_RET_STS_SUCCESS; -- At the end we will assign l_return_status to x_return_status
2924 x_msg_count := 0;
2925 x_msg_data := null;
2926
2927 FOR c2 in c1 LOOP
2928 l_old_candidate_id := c2.candidate_id;
2929
2930 -- Insert into the candidate table.
2931 INSERT INTO PA_CANDIDATES
2932 (CANDIDATE_ID,
2933 ASSIGNMENT_ID,
2934 RESOURCE_ID,
2935 RECORD_VERSION_NUMBER,
2936 STATUS_CODE,
2937 NOMINATED_BY_PERSON_ID,
2938 NOMINATION_DATE,
2939 NOMINATION_COMMENTS,
2940 CANDIDATE_RANKING,
2941 CREATION_DATE,
2942 CREATED_BY,
2943 LAST_UPDATE_DATE,
2944 LAST_UPDATED_BY)
2945 VALUES
2946 (PA_CANDIDATES_S.nextval,
2947 p_new_requirement_id,
2948 c2.resource_id,
2949 1,
2950 c2.status_code,
2951 c2.nominated_by_person_id,
2952 c2.nomination_date,
2953 c2.nomination_comments,
2954 c2.candidate_ranking,
2955 SYSDATE,
2956 FND_GLOBAL.USER_ID,
2957 SYSDATE,
2958 FND_GLOBAL.USER_ID)
2959 RETURNING
2960 CANDIDATE_ID into l_new_candidate_id;
2961
2962 FOR c4 in c3 LOOP
2963 INSERT INTO PA_CANDIDATE_REVIEWS
2964 (CANDIDATE_REVIEW_ID,
2965 CANDIDATE_ID,
2966 RECORD_VERSION_NUMBER,
2967 STATUS_CODE,
2968 REVIEWER_PERSON_ID,
2969 REVIEW_DATE,
2970 CHANGE_REASON_CODE,
2971 REVIEW_COMMENTS,
2972 CREATION_DATE,
2973 CREATED_BY,
2974 LAST_UPDATE_DATE,
2975 LAST_UPDATED_BY)
2976 VALUES
2977 (
2978 PA_CANDIDATE_REVIEWS_S.nextval,
2979 l_new_candidate_id,
2980 1,
2981 c4.status_code,
2982 c4.reviewer_person_id,
2983 c4.review_date,
2984 c4.change_reason_code,
2985 c4.review_comments,
2986 sysdate,
2987 FND_GLOBAL.user_id,
2988 sysdate,
2989 FND_GLOBAL.user_id
2990 );
2991 END LOOP;
2992 END LOOP;
2993
2994 -- Update No_of_Active_Candidates attribute for the new
2995 -- requirement.
2996
2997 -- get no_of_active_candidates from the previous requirement
2998 SELECT no_of_active_candidates
2999 INTO l_no_of_active_candidates
3000 FROM pa_project_assignments
3001 WHERE assignment_id = p_old_requirement_id;
3002
3003 -- get record_version_number for the passed new requirement id
3004
3005 SELECT record_version_number
3006 INTO l_record_version_number
3007 FROM pa_project_assignments
3008 WHERE assignment_id = p_new_requirement_id;
3009
3010 IF l_no_of_active_candidates is not null AND
3011 l_no_of_active_candidates > 0 THEN
3012
3013 pa_project_assignments_pkg.Update_row(
3014 p_assignment_id => p_new_requirement_id,
3015 p_no_of_active_candidates => l_no_of_active_candidates,
3016 p_record_version_number => l_record_version_number,
3017 x_return_status => l_return_status );
3018
3019 END IF;
3020
3021 -- 4537865
3022 x_return_status := l_return_status ;
3023
3024 EXCEPTION
3025 WHEN OTHERS THEN
3026 -- 4537865 : RESET OUT PARAMS
3027 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3028 x_msg_data := SUBSTRB(SQLERRM,1,240) ;
3029
3030 fnd_msg_pub.add_exc_msg
3031 (p_pkg_name => 'PA_CANDIDATE_PUB',
3032 p_procedure_name => 'Copy_Candidates');
3033
3034 x_msg_count := FND_MSG_PUB.Count_Msg;
3035
3036 IF x_msg_count = 1 THEN
3037 pa_interface_utils_pub.get_messages
3038 (p_encoded => FND_API.G_TRUE
3039 ,p_msg_index => 1
3040 ,p_msg_count => 1
3041 ,p_msg_data => l_msg_data
3042 ,p_data => x_msg_data
3043 ,p_msg_index_out => l_msg_index_out );
3044 END IF;
3045
3046 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3047
3048 END Copy_Candidates;
3049
3050 /*-------------------------------------------------------------------------
3051 PROCEDURE: Decline_Candidates
3052 PURPOSE: When a requirement is cancelled, all ACTIVE candidates attached to
3053 it must be declined. A decline notification should be sent to all
3054 candidates. If p_launch_wf is 'N', then the workflow process will
3055 not be launched, it is used in the upgrade script.
3056 -------------------------------------------------------------------------*/
3057 PROCEDURE Decline_Candidates(p_assignment_id IN NUMBER,
3058 p_launch_wf IN VARCHAR2 DEFAULT 'Y',
3059 x_return_status OUT NOCOPY VARCHAR2, -- 4537865
3060 x_msg_count OUT NOCOPY NUMBER, -- 4537865
3061 x_msg_data OUT NOCOPY VARCHAR2) -- 4537865
3062 IS
3063 TYPE number_tbl IS TABLE OF NUMBER
3064 INDEX BY BINARY_INTEGER;
3065
3066 l_candidate_id_tbl number_tbl;
3067 l_resource_id_tbl number_tbl;
3068 l_record_version_number_tbl number_tbl;
3069 l_asmt_record_version_number NUMBER;
3070 l_reviewer_person_id NUMBER;
3071 l_decline_status_code VARCHAR2(30);
3072 l_decline_status_name VARCHAR2(80);
3073 l_enable_wf_flag VARCHAR2(1);
3074 l_wf_item_type VARCHAR2(30);
3075 l_wf_process VARCHAR2(30);
3076 l_return_status VARCHAR2(1);
3077 l_msg_data VARCHAR2(2000);
3078 l_msg_count NUMBER := 0;
3079 l_save boolean;
3080
3081 -- 4537865
3082 l_msg_index_out NUMBER ;
3083 BEGIN
3084 x_return_status := FND_API.G_RET_STS_SUCCESS;
3085 x_msg_count := 0;
3086 x_msg_data := null;
3087
3088 -- Gather the candidate_ids that needs to be declined
3089 SELECT candidate_id,
3090 resource_id,
3091 record_version_number
3092 BULK COLLECT INTO
3093 l_candidate_id_tbl,
3094 l_resource_id_tbl,
3095 l_record_version_number_tbl
3096 FROM pa_candidates cand,
3097 pa_project_statuses status
3098 WHERE cand.assignment_id = p_assignment_id
3099 AND cand.status_code = status.project_status_code
3100 AND status.project_system_status_code IN ('CANDIDATE_PENDING_REVIEW',
3101 'CANDIDATE_UNDER_REVIEW', 'CANDIDATE_SYSTEM_NOMINATED', 'CANDIDATE_SUITABLE')
3102 AND status.status_type = 'CANDIDATE';
3103
3104 l_decline_status_code := FND_PROFILE.value('PA_CNL_REQ_CAND_STATUS');
3105
3106 --dbms_output.put_line('Log: Candidate Status Code ' || l_decline_status_code);
3107
3108 -- If the status code from the profile option is null,
3109 -- we need to set the default value to '110' - the status code
3110 -- we ship for 'CANDIDATE_DECLINDED' system status
3111
3112 IF l_decline_status_code IS NULL THEN
3113 l_decline_status_code := '110';
3114
3115 --dbms_output.put_line('Save profile option');
3116 l_save := FND_PROFILE.SAVE (X_NAME => 'PA_CNL_REQ_CAND_STATUS'
3117 ,X_VALUE => l_decline_status_code
3118 ,X_LEVEL_NAME => 'SITE');
3119 --IF l_save THEN
3120 -- dbms_output.put_line('l_save = ' || ' TRUE');
3121 --ELSE dbms_output.put_line('l_save = ' || ' FALSE');
3122 --END IF;
3123 END IF;
3124
3125 IF l_candidate_id_tbl.count > 0 THEN
3126
3127 SELECT project_status_name,
3128 enable_wf_flag,
3129 workflow_item_type,
3130 workflow_process
3131 INTO l_decline_status_name,
3132 l_enable_wf_flag,
3133 l_wf_item_type,
3134 l_wf_process
3135 FROM pa_project_statuses
3136 WHERE project_status_code = l_decline_status_code
3137 AND status_type = 'CANDIDATE';
3138
3139 l_reviewer_person_id := Get_Person_Id;
3140
3141 --dbms_output.put_line ('status_code: ' || l_decline_status_code);
3142 --dbms_output.put_line ('Status_name: ' || l_decline_status_name);
3143 --dbms_output.put_line ('wf_item_type ' || l_wf_item_type || ' l_wf_process ' || l_wf_process || ' decline status_name ' || l_decline_status_name);
3144
3145 FOR i in l_candidate_id_tbl.FIRST .. l_candidate_id_tbl.LAST LOOP
3146 --dbms_output.put_line ('candidate id to be declined: ' || l_candidate_id_tbl(i));
3147 --dbms_output.put_line ('resource id to be declined: ' || l_resource_id_tbl(i));
3148
3149 -- Added WHO Column update. Bug 7168412.
3150 UPDATE pa_candidates SET
3151 status_code = l_decline_status_code,
3152 record_version_number = l_record_version_number_tbl(i) + 1,
3153 LAST_UPDATE_DATE = SYSDATE,
3154 LAST_UPDATED_BY = FND_GLOBAL.USER_ID
3155 WHERE candidate_id = l_candidate_id_tbl(i);
3156
3157 -- Change reason code is not supplied in this case
3158
3159 INSERT INTO PA_CANDIDATE_REVIEWS
3160 (CANDIDATE_REVIEW_ID,
3161 CANDIDATE_ID,
3162 RECORD_VERSION_NUMBER,
3163 STATUS_CODE,
3164 REVIEWER_PERSON_ID,
3165 REVIEW_DATE,
3166 REVIEW_COMMENTS,
3167 CHANGE_REASON_CODE,
3168 CREATION_DATE,
3169 CREATED_BY,
3170 LAST_UPDATE_DATE,
3171 LAST_UPDATED_BY)
3172 VALUES
3173 (PA_CANDIDATE_REVIEWS_S.nextval,
3174 l_candidate_id_tbl(i),
3175 1,
3176 l_decline_status_code,
3177 l_reviewer_person_id,
3178 sysdate,
3179 null,
3180 null,
3181 sysdate,
3182 FND_GLOBAL.user_id,
3183 sysdate,
3184 FND_GLOBAL.user_id
3185 );
3186
3187 IF p_launch_wf = 'Y' AND l_enable_wf_flag = 'Y' AND
3188 l_wf_item_type IS NOT NULL AND l_wf_process IS NOT NULL THEN
3189
3190 Start_Workflow(p_wf_item_type => l_wf_item_type,
3191 p_wf_process => l_wf_process,
3192 p_assignment_id => p_assignment_id,
3193 p_candidate_number => l_candidate_id_tbl(i),
3194 p_resource_id => l_resource_id_tbl(i),
3195 p_status_name => l_decline_status_name,
3196 x_return_status => l_return_status,
3197 x_msg_count => l_msg_count,
3198 x_msg_data => l_msg_data);
3199 END IF;
3200
3201 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3202 RAISE FND_API.G_EXC_ERROR;
3203 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3204 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3205 END IF;
3206 END LOOP;
3207
3208 SELECT record_version_number
3209 INTO l_asmt_record_version_number
3210 FROM pa_project_assignments
3211 WHERE assignment_id = p_assignment_id;
3212
3213 pa_project_assignments_pkg.Update_row(p_assignment_id => p_assignment_id,
3214 p_no_of_active_candidates => 0,
3215 p_record_version_number => l_asmt_record_version_number,
3216 x_return_status => l_return_status );
3217
3218 -- 4537865 : The following statement was missing
3219 x_return_status := l_return_status ;
3220 END IF;
3221
3222 EXCEPTION
3223 WHEN OTHERS THEN
3224 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3225
3226 --4537865
3227 x_msg_data := SUBSTRB(SQLERRM,1,240) ;
3228
3229 fnd_msg_pub.add_exc_msg
3230 (p_pkg_name => 'PA_CANDIDATE_PUB',
3231 p_procedure_name => 'Decline_Candidates');
3232
3233 x_msg_count := FND_MSG_PUB.Count_Msg;
3234
3235 IF x_msg_count = 1 THEN
3236 pa_interface_utils_pub.get_messages
3237 (p_encoded => FND_API.G_TRUE
3238 ,p_msg_index => 1
3239 ,p_msg_count => 1
3240 ,p_msg_data => l_msg_data
3241 ,p_data => x_msg_data
3242 ,p_msg_index_out => l_msg_index_out );
3243 END IF;
3244
3245 RAISE;
3246 END Decline_Candidates;
3247
3248
3249 /*-------------------------------------------------------------------------
3250 PROCEDURE: Get_NF_Recipient
3251 PURPOSE: Called from PA Candidate Notification Process workflow (PARCANDD.wft).
3252 This procedure is called in loop to send individual notifications to
3253 all people involved while candidate nomination process.
3254 ( Resource + Res Mgr + Primary Cont + Staffing Owner )
3255 -------------------------------------------------------------------------*/
3256 PROCEDURE Get_NF_Recipient (itemtype IN VARCHAR2
3257 , itemkey IN VARCHAR2
3258 , actid IN NUMBER
3259 , funcmode IN VARCHAR2
3260 , resultout OUT NOCOPY VARCHAR2 )
3261 IS
3262
3263 l_number_of_nf_rects NUMBER := 0;
3264 l_nf_loop_counter NUMBER := 0;
3265 user_table Wf_Directory.UserTable;
3266 l_role_name VARCHAR2(320);
3267 l_role_name_temp VARCHAR2(320);
3268
3269 BEGIN
3270 /*
3271 First , get item attr NF_LOOP_COUNTER and NUMBER_OF_RECIPIENTS
3272 then do NF_LOOP_COUNTER := NF_LOOP_COUNTER + 1;
3273 If NF_LOOP_COUNTER > NUMBER_OF_RECIPIENTS , resultout = 'F'
3274 and return
3275 Else
3276 Get individual PER role, set role attributes for notification,
3277 Resultout = 'S'
3278 end
3279 */
3280
3281 -- Return if WF Not Running
3282 IF (funcmode <> wf_engine.eng_run) THEN
3283 resultout := wf_engine.eng_null;
3284 RETURN;
3285 END IF;
3286
3287 -- Get total number of recipients
3288 l_number_of_nf_rects := wf_engine.GetItemAttrNumber
3289 ( itemtype => itemtype
3290 , itemkey => itemkey
3291 , aname => 'NUMBER_OF_RECIPIENTS'
3292 );
3293
3294 -- Get loop counter value
3295 l_nf_loop_counter := wf_engine.getItemAttrNumber
3296 ( itemtype => itemtype
3297 , itemkey => itemkey
3298 , aname => 'NF_LOOP_COUNTER'
3299 );
3300
3301 -- Get adhoc role created for nitifications
3302 l_role_name := wf_engine.getItemAttrText
3303 ( itemtype => itemtype
3304 , itemkey => itemkey
3305 , aname => 'ATTR_ADHOC_ROLE'
3306 );
3307
3308
3309 l_nf_loop_counter := l_nf_loop_counter + 1;
3310
3311 IF l_nf_loop_counter > l_number_of_nf_rects THEN
3312
3313 resultout := wf_engine.eng_completed||':'||'F';
3314 RETURN;
3315 END IF;
3316
3317 -- Get all users attached to Adhoc role
3318 WF_DIRECTORY.GETROLEUSERS(l_role_name, user_table);
3319
3320 -- Get PER role for the 'nth' user from WF_USER_ROLES
3321 SELECT ROLE_NAME
3322 INTO l_role_name_temp
3323 FROM WF_USER_ROLES
3324 WHERE USER_NAME = user_table(l_nf_loop_counter)
3325 AND USER_ORIG_SYSTEM = 'PER'
3326 AND ROLE_ORIG_SYSTEM = 'PER'
3327 AND ROWNUM = 1;
3328
3329 -- Set the notification roles for the 'nth' user
3330 wf_engine.SetItemAttrText
3331 ( itemtype => itemtype,
3332 itemkey => itemkey,
3333 aname => 'ATTR_NOMINATE_ROLE',
3334 avalue => l_role_name_temp
3335 );
3336
3337 wf_engine.SetItemAttrText
3338 ( itemtype => itemtype,
3339 itemkey => itemkey,
3340 aname => 'ATTR_DECLINED_ROLE',
3341 avalue => l_role_name_temp
3342 );
3343
3344 wf_engine.SetItemAttrText
3345 ( itemtype => itemtype,
3346 itemkey => itemkey,
3347 aname => 'ATTR_CANDIDATE_ROLE',
3348 avalue => l_role_name_temp
3349 );
3350
3351 -- Set the incremented loop counter value
3352 wf_engine.SetItemAttrText
3353 ( itemtype => itemtype,
3354 itemkey => itemkey,
3355 aname => 'NF_LOOP_COUNTER',
3356 avalue => l_nf_loop_counter
3357 );
3358 resultout := wf_engine.eng_completed||':'||'S';
3359
3360
3361 EXCEPTION
3362 WHEN OTHERS THEN
3363 WF_CORE.CONTEXT
3364 ('PA_CANDID_PUB',
3365 'Get_NF_Recipient',
3366 itemtype,
3367 itemkey,
3368 to_char(actid),
3369 funcmode);
3370 RAISE;
3371 END Get_NF_Recipient;
3372
3373 /* --------------------------------------------------------------------
3374 * * FUNCTION: Get_Review_Change_Reason
3375 * * PURPOSE: Get the latest change reason code for a candidate given
3376 * * the candidate_id
3377 * * -------------------------------------------------------------------- */
3378 FUNCTION Get_Review_Change_Reason(p_candidate_id IN NUMBER)
3379 RETURN VARCHAR2
3380 IS
3381 l_change_reason_code VARCHAR2(30);
3382 BEGIN
3383
3384 SELECT change_reason_code
3385 INTO l_change_reason_code
3386 FROM (SELECT change_reason_code
3387 FROM PA_CANDIDATE_REVIEWS
3388 WHERE candidate_id =p_candidate_id
3389 ORDER BY review_date DESC)
3390 WHERE rownum = 1;
3391
3392 RETURN l_change_reason_code;
3393
3394 EXCEPTION
3395 WHEN OTHERS THEN
3396 RETURN NULL;
3397 END Get_Review_Change_Reason;
3398
3399 END PA_CANDIDATE_PUB;