VIEW: APPS.PA_REQMNT_CANDIDATES_AMG_V

Source

View Text - Preformatted

SELECT cand.candidate_id, res.resource_name, res.resource_id, res.person_id, asgmt.project_id, proj.name, proj.segment1, cand.assignment_id, asgmt.assignment_name, asgmt.start_date, asgmt.end_date, cand.status_code, ps.project_status_name, ps.project_system_status_code, PA_CANDIDATE_UTILS.Get_Nominator_Name( cand.nominated_by_person_id), cand.nominated_by_person_id, cand.nomination_date, cand.nomination_comments, cand.candidate_ranking, cand.record_version_number FROM pa_candidates cand ,pa_resources_denorm res ,pa_project_assignments asgmt ,pa_projects_all proj ,pa_project_statuses ps WHERE cand.resource_id = res.resource_id AND res.schedulable_flag = 'Y' AND cand.status_code = ps.project_status_code AND cand.assignment_id = asgmt.assignment_id AND asgmt.start_date BETWEEN res.resource_effective_start_date and NVL(res.resource_effective_end_date, asgmt.start_date+1) AND asgmt.project_id = proj.project_id AND ps.status_type = 'CANDIDATE'

View Text - HTML Formatted

SELECT CAND.CANDIDATE_ID
, RES.RESOURCE_NAME
, RES.RESOURCE_ID
, RES.PERSON_ID
, ASGMT.PROJECT_ID
, PROJ.NAME
, PROJ.SEGMENT1
, CAND.ASSIGNMENT_ID
, ASGMT.ASSIGNMENT_NAME
, ASGMT.START_DATE
, ASGMT.END_DATE
, CAND.STATUS_CODE
, PS.PROJECT_STATUS_NAME
, PS.PROJECT_SYSTEM_STATUS_CODE
, PA_CANDIDATE_UTILS.GET_NOMINATOR_NAME( CAND.NOMINATED_BY_PERSON_ID)
, CAND.NOMINATED_BY_PERSON_ID
, CAND.NOMINATION_DATE
, CAND.NOMINATION_COMMENTS
, CAND.CANDIDATE_RANKING
, CAND.RECORD_VERSION_NUMBER
FROM PA_CANDIDATES CAND
, PA_RESOURCES_DENORM RES
, PA_PROJECT_ASSIGNMENTS ASGMT
, PA_PROJECTS_ALL PROJ
, PA_PROJECT_STATUSES PS
WHERE CAND.RESOURCE_ID = RES.RESOURCE_ID
AND RES.SCHEDULABLE_FLAG = 'Y'
AND CAND.STATUS_CODE = PS.PROJECT_STATUS_CODE
AND CAND.ASSIGNMENT_ID = ASGMT.ASSIGNMENT_ID
AND ASGMT.START_DATE BETWEEN RES.RESOURCE_EFFECTIVE_START_DATE
AND NVL(RES.RESOURCE_EFFECTIVE_END_DATE
, ASGMT.START_DATE+1)
AND ASGMT.PROJECT_ID = PROJ.PROJECT_ID
AND PS.STATUS_TYPE = 'CANDIDATE'