VIEW: APPS.PA_RES_APRVL_ROLES_V

Source

View Text - Preformatted

SELECT res.resource_id , res.resource_name, res.resource_job_level, org.name, res.resource_organization_id, res.resource_city, res.resource_country_code, res.resource_region, asgn.assignment_id, asgn.assignment_name, asgn.assignment_type, asgn.start_date, asgn.end_date, asgn.apprvl_status_code, res.manager_name, res.manager_id, res.manager_id, ntf.group_id, nvl(asgn.no_of_active_candidates, 0) FROM pa_resources_denorm res, pa_project_assignments asgn, pa_wf_ntf_performers ntf, hr_all_organization_units org WHERE ntf.routing_order = 1 AND asgn.assignment_id = ntf.object_id1 AND res.resource_id = asgn.resource_id AND org.organization_id = res.resource_organization_id AND asgn.start_date BETWEEN res.resource_effective_start_date AND res.resource_effective_end_date AND res.schedulable_flag = 'Y' UNION ALL SELECT res.resource_id, res.resource_name, res.resource_job_level, org.name, res.resource_organization_id, res.resource_city, res.resource_country_code, res.resource_region, asgn.assignment_id, asgn.assignment_name, asgn.assignment_type, asgn.start_date, asgn.end_date, asgn.apprvl_status_code, res.manager_name, res.manager_id, per.person_id, ntf.group_id, nvl(asgn.no_of_active_candidates, 0) FROM fnd_grants fg, per_all_people_f per, wf_roles wfr, fnd_objects fob, pa_resources_denorm res, (select pa_security_pvt.get_menu_id('PA_PRM_RES_PRMRY_CONTACT') menu_id from dual) temp, pa_project_assignments asgn, pa_wf_ntf_performers ntf, hr_all_organization_units org WHERE ntf.routing_order = 1 AND asgn.assignment_id = ntf.object_id1 AND res.resource_id = asgn.resource_id AND fg.instance_pk1_value = res.resource_organization_id 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 fg.grantee_key = wfr.name and wfr.orig_system = 'HZ_PARTY' and per.party_id = wfr.orig_system_id and sysdate between per.effective_start_date and per.effective_end_date AND org.organization_id = res.resource_organization_id AND asgn.start_date BETWEEN res.resource_effective_start_date AND res.resource_effective_end_date AND res.schedulable_flag = 'Y'

View Text - HTML Formatted

SELECT RES.RESOURCE_ID
, RES.RESOURCE_NAME
, RES.RESOURCE_JOB_LEVEL
, ORG.NAME
, RES.RESOURCE_ORGANIZATION_ID
, RES.RESOURCE_CITY
, RES.RESOURCE_COUNTRY_CODE
, RES.RESOURCE_REGION
, ASGN.ASSIGNMENT_ID
, ASGN.ASSIGNMENT_NAME
, ASGN.ASSIGNMENT_TYPE
, ASGN.START_DATE
, ASGN.END_DATE
, ASGN.APPRVL_STATUS_CODE
, RES.MANAGER_NAME
, RES.MANAGER_ID
, RES.MANAGER_ID
, NTF.GROUP_ID
, NVL(ASGN.NO_OF_ACTIVE_CANDIDATES
, 0)
FROM PA_RESOURCES_DENORM RES
, PA_PROJECT_ASSIGNMENTS ASGN
, PA_WF_NTF_PERFORMERS NTF
, HR_ALL_ORGANIZATION_UNITS ORG
WHERE NTF.ROUTING_ORDER = 1
AND ASGN.ASSIGNMENT_ID = NTF.OBJECT_ID1
AND RES.RESOURCE_ID = ASGN.RESOURCE_ID
AND ORG.ORGANIZATION_ID = RES.RESOURCE_ORGANIZATION_ID
AND ASGN.START_DATE BETWEEN RES.RESOURCE_EFFECTIVE_START_DATE
AND RES.RESOURCE_EFFECTIVE_END_DATE
AND RES.SCHEDULABLE_FLAG = 'Y' UNION ALL SELECT RES.RESOURCE_ID
, RES.RESOURCE_NAME
, RES.RESOURCE_JOB_LEVEL
, ORG.NAME
, RES.RESOURCE_ORGANIZATION_ID
, RES.RESOURCE_CITY
, RES.RESOURCE_COUNTRY_CODE
, RES.RESOURCE_REGION
, ASGN.ASSIGNMENT_ID
, ASGN.ASSIGNMENT_NAME
, ASGN.ASSIGNMENT_TYPE
, ASGN.START_DATE
, ASGN.END_DATE
, ASGN.APPRVL_STATUS_CODE
, RES.MANAGER_NAME
, RES.MANAGER_ID
, PER.PERSON_ID
, NTF.GROUP_ID
, NVL(ASGN.NO_OF_ACTIVE_CANDIDATES
, 0)
FROM FND_GRANTS FG
, PER_ALL_PEOPLE_F PER
, WF_ROLES WFR
, FND_OBJECTS FOB
, PA_RESOURCES_DENORM RES
, (SELECT PA_SECURITY_PVT.GET_MENU_ID('PA_PRM_RES_PRMRY_CONTACT') MENU_ID
FROM DUAL) TEMP
, PA_PROJECT_ASSIGNMENTS ASGN
, PA_WF_NTF_PERFORMERS NTF
, HR_ALL_ORGANIZATION_UNITS ORG
WHERE NTF.ROUTING_ORDER = 1
AND ASGN.ASSIGNMENT_ID = NTF.OBJECT_ID1
AND RES.RESOURCE_ID = ASGN.RESOURCE_ID
AND FG.INSTANCE_PK1_VALUE = RES.RESOURCE_ORGANIZATION_ID
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 FG.GRANTEE_KEY = WFR.NAME
AND WFR.ORIG_SYSTEM = 'HZ_PARTY'
AND PER.PARTY_ID = WFR.ORIG_SYSTEM_ID
AND SYSDATE BETWEEN PER.EFFECTIVE_START_DATE
AND PER.EFFECTIVE_END_DATE
AND ORG.ORGANIZATION_ID = RES.RESOURCE_ORGANIZATION_ID
AND ASGN.START_DATE BETWEEN RES.RESOURCE_EFFECTIVE_START_DATE
AND RES.RESOURCE_EFFECTIVE_END_DATE
AND RES.SCHEDULABLE_FLAG = 'Y'