VIEW: APPS.PA_RESOURCE_AVAIL_STAFF_V

Source

View Text - Preformatted

SELECT res.rowid, per.person_id resource_analyst_id, res.resource_id, res.person_id, res.resource_name, res.resource_organization_id, org.name organization_name, res.manager_id, res.manager_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, per1.email_address from pa_resources_denorm res, per_all_people_f per, per_all_people_f per1, wf_roles wfr, fnd_grants fg, fnd_objects fob, fnd_menus temp, hr_all_organization_units_tl org WHERE to_number(fg.instance_pk1_value) = res.resource_organization_id AND fg.instance_set_id is null AND fg.instance_type = 'INSTANCE' AND fg.object_id = fob.object_id AND fob.obj_name = 'ORGANIZATION' AND temp.menu_name = 'PA_PRM_RES_AUTH' AND fg.menu_id = temp.menu_id AND res.resource_organization_id = org.organization_id AND org.language = USERENV('LANG') AND fg.grantee_type = 'USER' AND fg.grantee_key = wfr.name AND wfr.orig_system = 'HZ_PARTY' AND per.party_id = wfr.orig_system_id AND trunc(SYSDATE) BETWEEN per.effective_start_date AND per.effective_end_date AND trunc(SYSDATE) BETWEEN trunc(fg.start_date) AND trunc(NVL(fg.end_date, SYSDATE+1)) AND res.schedulable_flag = 'Y' AND 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.person_id = res.person_id AND res1.resource_effective_end_date >= trunc(sysdate)) AND res.person_id = per1.person_id AND trunc(SYSDATE) BETWEEN per1.effective_start_date AND per1.effective_end_date

View Text - HTML Formatted

SELECT RES.ROWID
, PER.PERSON_ID RESOURCE_ANALYST_ID
, RES.RESOURCE_ID
, RES.PERSON_ID
, RES.RESOURCE_NAME
, RES.RESOURCE_ORGANIZATION_ID
, ORG.NAME ORGANIZATION_NAME
, RES.MANAGER_ID
, RES.MANAGER_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
, PER1.EMAIL_ADDRESS
FROM PA_RESOURCES_DENORM RES
, PER_ALL_PEOPLE_F PER
, PER_ALL_PEOPLE_F PER1
, WF_ROLES WFR
, FND_GRANTS FG
, FND_OBJECTS FOB
, FND_MENUS TEMP
, HR_ALL_ORGANIZATION_UNITS_TL ORG
WHERE TO_NUMBER(FG.INSTANCE_PK1_VALUE) = RES.RESOURCE_ORGANIZATION_ID
AND FG.INSTANCE_SET_ID IS NULL
AND FG.INSTANCE_TYPE = 'INSTANCE'
AND FG.OBJECT_ID = FOB.OBJECT_ID
AND FOB.OBJ_NAME = 'ORGANIZATION'
AND TEMP.MENU_NAME = 'PA_PRM_RES_AUTH'
AND FG.MENU_ID = TEMP.MENU_ID
AND RES.RESOURCE_ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND ORG.LANGUAGE = USERENV('LANG')
AND FG.GRANTEE_TYPE = 'USER'
AND FG.GRANTEE_KEY = WFR.NAME
AND WFR.ORIG_SYSTEM = 'HZ_PARTY'
AND PER.PARTY_ID = WFR.ORIG_SYSTEM_ID
AND TRUNC(SYSDATE) BETWEEN PER.EFFECTIVE_START_DATE
AND PER.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN TRUNC(FG.START_DATE)
AND TRUNC(NVL(FG.END_DATE
, SYSDATE+1))
AND RES.SCHEDULABLE_FLAG = 'Y'
AND 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.PERSON_ID = RES.PERSON_ID
AND RES1.RESOURCE_EFFECTIVE_END_DATE >= TRUNC(SYSDATE))
AND RES.PERSON_ID = PER1.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN PER1.EFFECTIVE_START_DATE
AND PER1.EFFECTIVE_END_DATE