eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PA_CANDIDATE_PUB dependencies on PA_CANDIDATES

Line 17: FROM pa_candidates

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;

Line 34: FROM pa_candidates

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;

Line 52: FROM pa_candidates

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';

Line 86: FROM pa_candidates

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:

Line 144: FROM pa_candidates

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

Line 150: FROM pa_candidates, pa_project_statuses

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';

Line 391: INSERT INTO PA_CANDIDATES

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,

Line 406: (PA_CANDIDATES_S.nextval,

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,

Line 422: UPDATE PA_CANDIDATES

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,

Line 623: FROM pa_candidates

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

Line 629: FROM pa_candidates

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);

Line 681: -- get information of the assigned person from pa_candidates

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,

Line 691: FROM pa_candidates

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

Line 742: -- For the remaing candiates, update the status in pa_candidates

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:

Line 806: FROM pa_candidates cand, pa_project_statuses ps

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:

Line 872: the status in pa_candidates table.

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

Line 891: (from pa_candidates). We do not

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: -------------------------------------------------------------------- */

Line 942: FROM pa_candidates

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

Line 954: FROM pa_candidates

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;

Line 968: FROM pa_candidates

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

Line 1009: -- Since pa_candidates has not been updated, the new record version number should be

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.

Line 1038: -- Update pa_candidates with the new status code

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,

Line 1040: UPDATE pa_candidates

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

Line 1048: -- Since pa_candidates has been updated, set the increased record version number to the local

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

Line 1199: PA_CANDIDATES_LOG table. we will also update the

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,

Line 1276: FROM pa_candidates

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

Line 1370: UPDATE pa_candidates

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,

Line 2468: FROM pa_candidates

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;

Line 2471: DELETE FROM pa_candidates

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

Line 2476: 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

Line 2480: DELETE FROM pa_candidates

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:

Line 2553: pa_candidates cand,

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

Line 2563: DELETE FROM pa_candidates

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(

Line 2634: FROM pa_candidates cand,

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

Line 2668: INSERT INTO PA_CANDIDATES

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,

Line 2683: (PA_CANDIDATES_S.nextval,

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,

Line 2833: FROM pa_candidates cand,

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',

Line 2887: UPDATE pa_candidates SET

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