VIEW: APPS.PA_CANDIDATE_DETAILS_V

Source

View Text - Preformatted

SELECT cand.candidate_id ,res.resource_name ,res.resource_id ,res.person_id ,asgmt.project_id ,cand.assignment_id ,asgmt.start_date ,asgmt.end_date ,asgmt.record_version_number ,pj.name ,res.resource_job_level ,org.name ,res.resource_organization_id ,cand.status_code ,ps.project_status_name ,ps.project_system_status_code ,cand.nominated_by_person_id ,PA_CANDIDATE_UTILS.Get_Resource_Id(cand.nominated_by_person_id) ,PA_CANDIDATE_UTILS.Get_Nominator_Name(cand.nominated_by_person_id) ,cand.nomination_date ,cand.nomination_comments ,cand.candidate_ranking ,cand.record_version_number ,PA_CANDIDATE_PUB.Is_Cand_On_Another_Assignment(res.resource_id,cand.assignment_id,asgmt.start_date,asgmt.end_date) ,PA_CANDIDATE_PUB.Get_Competence_Match(res.person_id,cand.assignment_id) ,PA_CANDIDATE_PUB.Check_Availability(res.resource_id,cand.assignment_id,asgmt.project_id) ,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_candidates cand ,pa_resources_denorm res ,pa_project_assignments asgmt ,pa_project_statuses ps ,per_jobs pj ,hr_all_organization_units org WHERE cand.resource_id = res.resource_id 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, sysdate+1) AND ps.status_type = 'CANDIDATE' AND ps.project_system_status_code <> 'CANDIDATE_SYSTEM_QUALIFIED' AND res.resource_organization_id = org.organization_id (+) AND res.job_id = pj.job_id AND res.schedulable_flag = 'Y'

View Text - HTML Formatted

SELECT CAND.CANDIDATE_ID
, RES.RESOURCE_NAME
, RES.RESOURCE_ID
, RES.PERSON_ID
, ASGMT.PROJECT_ID
, CAND.ASSIGNMENT_ID
, ASGMT.START_DATE
, ASGMT.END_DATE
, ASGMT.RECORD_VERSION_NUMBER
, PJ.NAME
, RES.RESOURCE_JOB_LEVEL
, ORG.NAME
, RES.RESOURCE_ORGANIZATION_ID
, CAND.STATUS_CODE
, PS.PROJECT_STATUS_NAME
, PS.PROJECT_SYSTEM_STATUS_CODE
, CAND.NOMINATED_BY_PERSON_ID
, PA_CANDIDATE_UTILS.GET_RESOURCE_ID(CAND.NOMINATED_BY_PERSON_ID)
, PA_CANDIDATE_UTILS.GET_NOMINATOR_NAME(CAND.NOMINATED_BY_PERSON_ID)
, CAND.NOMINATION_DATE
, CAND.NOMINATION_COMMENTS
, CAND.CANDIDATE_RANKING
, CAND.RECORD_VERSION_NUMBER
, PA_CANDIDATE_PUB.IS_CAND_ON_ANOTHER_ASSIGNMENT(RES.RESOURCE_ID
, CAND.ASSIGNMENT_ID
, ASGMT.START_DATE
, ASGMT.END_DATE)
, PA_CANDIDATE_PUB.GET_COMPETENCE_MATCH(RES.PERSON_ID
, CAND.ASSIGNMENT_ID)
, PA_CANDIDATE_PUB.CHECK_AVAILABILITY(RES.RESOURCE_ID
, CAND.ASSIGNMENT_ID
, ASGMT.PROJECT_ID)
, 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_CANDIDATES CAND
, PA_RESOURCES_DENORM RES
, PA_PROJECT_ASSIGNMENTS ASGMT
, PA_PROJECT_STATUSES PS
, PER_JOBS PJ
, HR_ALL_ORGANIZATION_UNITS ORG
WHERE CAND.RESOURCE_ID = RES.RESOURCE_ID
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
, SYSDATE+1)
AND PS.STATUS_TYPE = 'CANDIDATE'
AND PS.PROJECT_SYSTEM_STATUS_CODE <> 'CANDIDATE_SYSTEM_QUALIFIED'
AND RES.RESOURCE_ORGANIZATION_ID = ORG.ORGANIZATION_ID (+)
AND RES.JOB_ID = PJ.JOB_ID
AND RES.SCHEDULABLE_FLAG = 'Y'