VIEW: APPS.PA_RES_OVC_HOURS_STAFF_V

Source

View Text - Preformatted

SELECT res.resource_id, res.person_id, res.resource_name, res.resource_job_level, res.manager_id, res.manager_name, pfi.expenditure_organization_id, org.name, jobs.name, pfi.item_date, pa_resource_pvt.get_resource_ovc_to_date( res.resource_id, pfi.item_date), fg.grantee_key, res.resource_country_code, res.resource_country from pa_forecast_items pfi, per_jobs jobs, hr_all_organization_units org, fnd_grants fg, fnd_objects fob, pa_resources_denorm res, (select pa_security_pvt.get_menu_id('PA_PRM_RES_AUTH') menu_id from dual) temp WHERE pfi.resource_id = res.resource_id and pfi.forecast_item_type = 'U' and pfi.delete_flag = 'N' and pfi.overcommitment_flag = 'Y' and pfi.availability_flag = 'N' and pfi.error_flag in ('Y', 'N') and pfi.item_date >= resource_effective_start_date and pfi.item_date <= resource_effective_end_date and jobs.job_id = res.job_id and fg.instance_pk1_value = pfi.expenditure_organization_id and org.organization_id = pfi.expenditure_organization_id and sysdate <= res.resource_effective_end_date and fg.instance_type = 'INSTANCE' and fg.object_id = fob.object_id and fob.obj_name = 'ORGANIZATION' and fg.menu_id = temp.menu_id and fg.grantee_type = 'USER' and trunc(SYSDATE) between trunc(fg.start_date) and trunc(NVL(fg.end_date, SYSDATE+1))

View Text - HTML Formatted

SELECT RES.RESOURCE_ID
, RES.PERSON_ID
, RES.RESOURCE_NAME
, RES.RESOURCE_JOB_LEVEL
, RES.MANAGER_ID
, RES.MANAGER_NAME
, PFI.EXPENDITURE_ORGANIZATION_ID
, ORG.NAME
, JOBS.NAME
, PFI.ITEM_DATE
, PA_RESOURCE_PVT.GET_RESOURCE_OVC_TO_DATE( RES.RESOURCE_ID
, PFI.ITEM_DATE)
, FG.GRANTEE_KEY
, RES.RESOURCE_COUNTRY_CODE
, RES.RESOURCE_COUNTRY
FROM PA_FORECAST_ITEMS PFI
, PER_JOBS JOBS
, HR_ALL_ORGANIZATION_UNITS ORG
, FND_GRANTS FG
, FND_OBJECTS FOB
, PA_RESOURCES_DENORM RES
, (SELECT PA_SECURITY_PVT.GET_MENU_ID('PA_PRM_RES_AUTH') MENU_ID
FROM DUAL) TEMP
WHERE PFI.RESOURCE_ID = RES.RESOURCE_ID
AND PFI.FORECAST_ITEM_TYPE = 'U'
AND PFI.DELETE_FLAG = 'N'
AND PFI.OVERCOMMITMENT_FLAG = 'Y'
AND PFI.AVAILABILITY_FLAG = 'N'
AND PFI.ERROR_FLAG IN ('Y'
, 'N')
AND PFI.ITEM_DATE >= RESOURCE_EFFECTIVE_START_DATE
AND PFI.ITEM_DATE <= RESOURCE_EFFECTIVE_END_DATE
AND JOBS.JOB_ID = RES.JOB_ID
AND FG.INSTANCE_PK1_VALUE = PFI.EXPENDITURE_ORGANIZATION_ID
AND ORG.ORGANIZATION_ID = PFI.EXPENDITURE_ORGANIZATION_ID
AND SYSDATE <= RES.RESOURCE_EFFECTIVE_END_DATE
AND FG.INSTANCE_TYPE = 'INSTANCE'
AND FG.OBJECT_ID = FOB.OBJECT_ID
AND FOB.OBJ_NAME = 'ORGANIZATION'
AND FG.MENU_ID = TEMP.MENU_ID
AND FG.GRANTEE_TYPE = 'USER'
AND TRUNC(SYSDATE) BETWEEN TRUNC(FG.START_DATE)
AND TRUNC(NVL(FG.END_DATE
, SYSDATE+1))