VIEW: APPS.PA_CANDIDATE_REVIEWS_V

Source

View Text - Preformatted

SELECT cand_rew.candidate_review_id ,cand_rew.candidate_id ,cand_rew.status_code ,ps.project_status_name ,cand_rew.reviewer_person_id ,PA_CANDIDATE_UTILS.Get_Resource_Id(cand_rew.reviewer_person_id) ,per.full_name ,cand_rew.review_date ,cand_rew.change_reason_code ,lkp.meaning ,cand_rew.review_comments ,PA_CANDIDATE_UTILS.Get_Candidate_Score(res.resource_id, res.person_id, cand.assignment_id, asgmt.project_id, null, null, null, null, res.resource_job_level, null, null, null, null, null) FROM pa_candidate_reviews cand_rew ,per_people_x per ,pa_project_statuses ps ,pa_lookups lkp ,pa_candidates cand ,pa_resources_denorm res ,pa_project_assignments asgmt WHERE cand_rew.reviewer_person_id = per.person_id AND cand_rew.status_code = ps.project_status_code (+) AND ps.status_type (+) = 'CANDIDATE' AND cand_rew.change_reason_code = lkp.lookup_code (+) AND lkp.lookup_type (+) = 'CANDIDATE_STS_CHANGE_REASON' AND cand.candidate_id = cand_rew.candidate_id AND cand.resource_id = res.resource_id AND cand.assignment_id = asgmt.assignment_id AND asgmt.start_date BETWEEN res.resource_effective_start_date AND NVL(res.resource_effective_end_date, sysdate+1) AND res.schedulable_flag = 'Y'

View Text - HTML Formatted

SELECT CAND_REW.CANDIDATE_REVIEW_ID
, CAND_REW.CANDIDATE_ID
, CAND_REW.STATUS_CODE
, PS.PROJECT_STATUS_NAME
, CAND_REW.REVIEWER_PERSON_ID
, PA_CANDIDATE_UTILS.GET_RESOURCE_ID(CAND_REW.REVIEWER_PERSON_ID)
, PER.FULL_NAME
, CAND_REW.REVIEW_DATE
, CAND_REW.CHANGE_REASON_CODE
, LKP.MEANING
, CAND_REW.REVIEW_COMMENTS
, PA_CANDIDATE_UTILS.GET_CANDIDATE_SCORE(RES.RESOURCE_ID
, RES.PERSON_ID
, CAND.ASSIGNMENT_ID
, ASGMT.PROJECT_ID
, NULL
, NULL
, NULL
, NULL
, RES.RESOURCE_JOB_LEVEL
, NULL
, NULL
, NULL
, NULL
, NULL)
FROM PA_CANDIDATE_REVIEWS CAND_REW
, PER_PEOPLE_X PER
, PA_PROJECT_STATUSES PS
, PA_LOOKUPS LKP
, PA_CANDIDATES CAND
, PA_RESOURCES_DENORM RES
, PA_PROJECT_ASSIGNMENTS ASGMT
WHERE CAND_REW.REVIEWER_PERSON_ID = PER.PERSON_ID
AND CAND_REW.STATUS_CODE = PS.PROJECT_STATUS_CODE (+)
AND PS.STATUS_TYPE (+) = 'CANDIDATE'
AND CAND_REW.CHANGE_REASON_CODE = LKP.LOOKUP_CODE (+)
AND LKP.LOOKUP_TYPE (+) = 'CANDIDATE_STS_CHANGE_REASON'
AND CAND.CANDIDATE_ID = CAND_REW.CANDIDATE_ID
AND CAND.RESOURCE_ID = RES.RESOURCE_ID
AND CAND.ASSIGNMENT_ID = ASGMT.ASSIGNMENT_ID
AND ASGMT.START_DATE BETWEEN RES.RESOURCE_EFFECTIVE_START_DATE
AND NVL(RES.RESOURCE_EFFECTIVE_END_DATE
, SYSDATE+1)
AND RES.SCHEDULABLE_FLAG = 'Y'