[Home] [Help]
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:
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,
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,
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,
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,
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
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;
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);
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,
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,
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,
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,
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: