VIEW: APPS.PA_CANDIDATE_LOG_AMG_V

Source

View Text - Preformatted

SELECT pcr.candidate_review_id, cand.candidate_id, res.resource_name, cand.assignment_id, cand.status_code, ps.project_status_name, ps.project_system_status_code, pcr.change_reason_code, pcr.review_comments FROM pa_candidates cand ,pa_resources_denorm res ,pa_project_statuses ps ,pa_candidate_reviews pcr WHERE cand.resource_id = res.resource_id AND res.resource_effective_start_date = ( SELECT max(res2.resource_effective_start_date) FROM pa_resources_denorm res2 WHERE res2.resource_id = res.resource_id) AND cand.status_code = ps.project_status_code AND ps.status_type = 'CANDIDATE' AND cand.candidate_id = pcr.candidate_id

View Text - HTML Formatted

SELECT PCR.CANDIDATE_REVIEW_ID
, CAND.CANDIDATE_ID
, RES.RESOURCE_NAME
, CAND.ASSIGNMENT_ID
, CAND.STATUS_CODE
, PS.PROJECT_STATUS_NAME
, PS.PROJECT_SYSTEM_STATUS_CODE
, PCR.CHANGE_REASON_CODE
, PCR.REVIEW_COMMENTS
FROM PA_CANDIDATES CAND
, PA_RESOURCES_DENORM RES
, PA_PROJECT_STATUSES PS
, PA_CANDIDATE_REVIEWS PCR
WHERE CAND.RESOURCE_ID = RES.RESOURCE_ID
AND RES.RESOURCE_EFFECTIVE_START_DATE = ( SELECT MAX(RES2.RESOURCE_EFFECTIVE_START_DATE)
FROM PA_RESOURCES_DENORM RES2
WHERE RES2.RESOURCE_ID = RES.RESOURCE_ID)
AND CAND.STATUS_CODE = PS.PROJECT_STATUS_CODE
AND PS.STATUS_TYPE = 'CANDIDATE'
AND CAND.CANDIDATE_ID = PCR.CANDIDATE_ID