VIEW: APPS.PA_RESOURCE_AVAILABILITY_V

Source

View Text - Preformatted

SELECT res.rowid, res.resource_id, res.person_id, res.resource_name full_name, res.resource_organization_id, org.name organization_name, res.manager_id, per1.full_name, res.resource_job_level, res.resource_effective_start_date, res.resource_effective_end_date, res.resource_city, res.resource_region, res.resource_country_code, res.resource_country, pa_resource_utils.Get_Current_Project_NameNumber(res.resource_id), pa_resource_utils.Get_Current_Project_Id(res.resource_id), res.resource_person_type, per.email_address from pa_resources_denorm res, per_all_people_f per, per_all_people_f per1, hr_all_organization_units_tl org WHERE res.resource_effective_start_date = (SELECT min (res1.resource_effective_start_date) FROM pa_resources_denorm res1 WHERE res1.resource_id = res.resource_id AND res1.resource_effective_end_date >= trunc(sysdate)) AND res.schedulable_flag = 'Y' AND res.resource_organization_id = org.organization_id AND org.language = USERENV('LANG') AND per.person_id = res.person_id AND (res.resource_effective_start_date between per.effective_start_date and per.effective_end_date) and ( res.manager_id is null or ( per1.person_id = res.manager_id and per1.EFFECTIVE_START_DATE= (select min (per2.effective_start_date) from per_all_people_f per2 where per2.person_id = per1.person_id and per2.effective_end_date >= trunc(sysdate)) ) ) and res.manager_id = per1.person_id(+)

View Text - HTML Formatted

SELECT RES.ROWID
, RES.RESOURCE_ID
, RES.PERSON_ID
, RES.RESOURCE_NAME FULL_NAME
, RES.RESOURCE_ORGANIZATION_ID
, ORG.NAME ORGANIZATION_NAME
, RES.MANAGER_ID
, PER1.FULL_NAME
, RES.RESOURCE_JOB_LEVEL
, RES.RESOURCE_EFFECTIVE_START_DATE
, RES.RESOURCE_EFFECTIVE_END_DATE
, RES.RESOURCE_CITY
, RES.RESOURCE_REGION
, RES.RESOURCE_COUNTRY_CODE
, RES.RESOURCE_COUNTRY
, PA_RESOURCE_UTILS.GET_CURRENT_PROJECT_NAMENUMBER(RES.RESOURCE_ID)
, PA_RESOURCE_UTILS.GET_CURRENT_PROJECT_ID(RES.RESOURCE_ID)
, RES.RESOURCE_PERSON_TYPE
, PER.EMAIL_ADDRESS
FROM PA_RESOURCES_DENORM RES
, PER_ALL_PEOPLE_F PER
, PER_ALL_PEOPLE_F PER1
, HR_ALL_ORGANIZATION_UNITS_TL ORG
WHERE RES.RESOURCE_EFFECTIVE_START_DATE = (SELECT MIN (RES1.RESOURCE_EFFECTIVE_START_DATE)
FROM PA_RESOURCES_DENORM RES1
WHERE RES1.RESOURCE_ID = RES.RESOURCE_ID
AND RES1.RESOURCE_EFFECTIVE_END_DATE >= TRUNC(SYSDATE))
AND RES.SCHEDULABLE_FLAG = 'Y'
AND RES.RESOURCE_ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND ORG.LANGUAGE = USERENV('LANG')
AND PER.PERSON_ID = RES.PERSON_ID
AND (RES.RESOURCE_EFFECTIVE_START_DATE BETWEEN PER.EFFECTIVE_START_DATE
AND PER.EFFECTIVE_END_DATE)
AND ( RES.MANAGER_ID IS NULL OR ( PER1.PERSON_ID = RES.MANAGER_ID
AND PER1.EFFECTIVE_START_DATE= (SELECT MIN (PER2.EFFECTIVE_START_DATE)
FROM PER_ALL_PEOPLE_F PER2
WHERE PER2.PERSON_ID = PER1.PERSON_ID
AND PER2.EFFECTIVE_END_DATE >= TRUNC(SYSDATE)) ) )
AND RES.MANAGER_ID = PER1.PERSON_ID(+)