[Home] [Help]
975:
976: /* --------------------------------------------------------------------
977: PROCEDURE: Add_Candidate_Log
978: PURPOSE: This Procedure will add a review comment to for a Candidate
979: in the pa_candidate_reviews table. It will also update
980: the status in pa_candidates table.
981: A change reason can be associated with every status change.
982: A review comment can be associated with every status change.
983: This API will error out if:
1237: x_cand_record_version_number := l_new_cand_record_version_num;
1238:
1239: l_reviewer_person_id := Get_Person_Id;
1240:
1241: INSERT INTO PA_CANDIDATE_REVIEWS
1242: (CANDIDATE_REVIEW_ID,
1243: CANDIDATE_ID,
1244: RECORD_VERSION_NUMBER,
1245: STATUS_CODE,
1269: LAST_UPDATE_DATE,
1270: LAST_UPDATED_BY)
1271: VALUES
1272: (
1273: PA_CANDIDATE_REVIEWS_S.nextval,
1274: p_candidate_id,
1275: 1,
1276: p_status_code,
1277: l_reviewer_person_id,
1605: -- Create an entry on the log table if the status has changed.
1606: IF nvl(l_old_status_code,'-1') <> nvl(l_status_code,'-1') THEN
1607: l_reviewer_person_id := Get_Person_Id;
1608:
1609: INSERT INTO PA_CANDIDATE_REVIEWS
1610: (CANDIDATE_REVIEW_ID,
1611: CANDIDATE_ID,
1612: RECORD_VERSION_NUMBER,
1613: STATUS_CODE,
1637: LAST_UPDATE_DATE,
1638: LAST_UPDATED_BY)
1639: VALUES
1640: (
1641: PA_CANDIDATE_REVIEWS_S.nextval,
1642: p_candidate_id,
1643: 1,
1644: l_status_code,
1645: l_reviewer_person_id,
2746: END IF;
2747:
2748: IF l_candidates_tbl.count > 0 THEN
2749: FORALL i IN l_candidates_tbl.FIRST .. l_candidates_tbl.LAST
2750: DELETE FROM pa_candidate_reviews
2751: WHERE candidate_id = l_candidates_tbl(i);
2752: END IF;
2753:
2754: EXCEPTION
2819: AND candidate_id = p_candidate_id
2820: AND cand.status_code = ps.project_status_code
2821: AND ps.status_type = 'CANDIDATE';
2822:
2823: DELETE FROM pa_candidate_reviews
2824: WHERE candidate_id = p_candidate_id;
2825:
2826: DELETE FROM pa_candidates
2827: WHERE candidate_id = p_candidate_id;
2908: reviewer_person_id,
2909: review_date,
2910: change_reason_code,
2911: review_comments
2912: FROM pa_candidate_reviews
2913: WHERE candidate_id = l_old_candidate_id;
2914:
2915: -- 4537865
2916: l_msg_data VARCHAR2(2000);
2959: RETURNING
2960: CANDIDATE_ID into l_new_candidate_id;
2961:
2962: FOR c4 in c3 LOOP
2963: INSERT INTO PA_CANDIDATE_REVIEWS
2964: (CANDIDATE_REVIEW_ID,
2965: CANDIDATE_ID,
2966: RECORD_VERSION_NUMBER,
2967: STATUS_CODE,
2974: LAST_UPDATE_DATE,
2975: LAST_UPDATED_BY)
2976: VALUES
2977: (
2978: PA_CANDIDATE_REVIEWS_S.nextval,
2979: l_new_candidate_id,
2980: 1,
2981: c4.status_code,
2982: c4.reviewer_person_id,
3155: WHERE candidate_id = l_candidate_id_tbl(i);
3156:
3157: -- Change reason code is not supplied in this case
3158:
3159: INSERT INTO PA_CANDIDATE_REVIEWS
3160: (CANDIDATE_REVIEW_ID,
3161: CANDIDATE_ID,
3162: RECORD_VERSION_NUMBER,
3163: STATUS_CODE,
3169: CREATED_BY,
3170: LAST_UPDATE_DATE,
3171: LAST_UPDATED_BY)
3172: VALUES
3173: (PA_CANDIDATE_REVIEWS_S.nextval,
3174: l_candidate_id_tbl(i),
3175: 1,
3176: l_decline_status_code,
3177: l_reviewer_person_id,
3383:
3384: SELECT change_reason_code
3385: INTO l_change_reason_code
3386: FROM (SELECT change_reason_code
3387: FROM PA_CANDIDATE_REVIEWS
3388: WHERE candidate_id =p_candidate_id
3389: ORDER BY review_date DESC)
3390: WHERE rownum = 1;
3391: