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