VIEW: APPS.PA_PROJ_MANAGER_V

Source

View Text - Preformatted

SELECT ppf.full_name ,ppp.resource_source_id FROM per_people_x ppf ,pa_project_parties ppp ,(select NVL(fnd_profile.value('PA_SUPER_PROJECT_VIEW'), 'N') val from dual) prof WHERE prof.val='Y' and ppp.project_role_id = 1 and ppp.resource_type_id = 101 and ppf.person_id = ppp.resource_source_id UNION SELECT ppf.full_name ,ppp.resource_source_id FROM per_people_x ppf ,pa_project_parties ppp ,(select NVL(fnd_profile.value('PA_SUPER_PROJECT_VIEW'), 'N') val from dual) prof WHERE prof.val='N' and ppf.person_id = fnd_global.employee_id and ppp.resource_source_id = ppf.person_id and ppp.project_role_id = 1 and ppp.resource_type_id = 101 UNION SELECT res.resource_name ,res.person_id FROM pa_resources_denorm res ,pa_project_parties ppp ,(select NVL(fnd_profile.value('PA_SUPER_PROJECT_VIEW'), 'N') val from dual) prof ,(SELECT instance_pk1_value FROM fnd_grants fg ,fnd_user fu ,fnd_objects fob ,(select nvl(pa_security_pvt.get_menu_id('PA_PRM_PROJ_AUTH'),-1) menu_id from dual) prj_auth_menu WHERE fg.instance_type = 'INSTANCE' AND fg.grantee_type = 'USER' AND fg.object_id = fob.object_id AND fob.obj_name = 'ORGANIZATION' AND fg.menu_id = prj_auth_menu.menu_id AND fg.grantee_key = 'PER:' || fu.employee_id AND fu.user_id = fnd_global.user_id AND trunc(SYSDATE) BETWEEN trunc(fg.start_date) and trunc(NVL(fg.end_date, SYSDATE+1))) auth_org WHERE prof.val='N' AND ppp.project_role_id = 1 AND ppp.resource_type_id = 101 AND res.person_id = ppp.resource_source_id AND res.resource_organization_id in auth_org.instance_pk1_value

View Text - HTML Formatted

SELECT PPF.FULL_NAME
, PPP.RESOURCE_SOURCE_ID
FROM PER_PEOPLE_X PPF
, PA_PROJECT_PARTIES PPP
, (SELECT NVL(FND_PROFILE.VALUE('PA_SUPER_PROJECT_VIEW')
, 'N') VAL
FROM DUAL) PROF
WHERE PROF.VAL='Y'
AND PPP.PROJECT_ROLE_ID = 1
AND PPP.RESOURCE_TYPE_ID = 101
AND PPF.PERSON_ID = PPP.RESOURCE_SOURCE_ID UNION SELECT PPF.FULL_NAME
, PPP.RESOURCE_SOURCE_ID
FROM PER_PEOPLE_X PPF
, PA_PROJECT_PARTIES PPP
, (SELECT NVL(FND_PROFILE.VALUE('PA_SUPER_PROJECT_VIEW')
, 'N') VAL
FROM DUAL) PROF
WHERE PROF.VAL='N'
AND PPF.PERSON_ID = FND_GLOBAL.EMPLOYEE_ID
AND PPP.RESOURCE_SOURCE_ID = PPF.PERSON_ID
AND PPP.PROJECT_ROLE_ID = 1
AND PPP.RESOURCE_TYPE_ID = 101 UNION SELECT RES.RESOURCE_NAME
, RES.PERSON_ID
FROM PA_RESOURCES_DENORM RES
, PA_PROJECT_PARTIES PPP
, (SELECT NVL(FND_PROFILE.VALUE('PA_SUPER_PROJECT_VIEW')
, 'N') VAL
FROM DUAL) PROF
, (SELECT INSTANCE_PK1_VALUE
FROM FND_GRANTS FG
, FND_USER FU
, FND_OBJECTS FOB
, (SELECT NVL(PA_SECURITY_PVT.GET_MENU_ID('PA_PRM_PROJ_AUTH')
, -1) MENU_ID
FROM DUAL) PRJ_AUTH_MENU
WHERE FG.INSTANCE_TYPE = 'INSTANCE'
AND FG.GRANTEE_TYPE = 'USER'
AND FG.OBJECT_ID = FOB.OBJECT_ID
AND FOB.OBJ_NAME = 'ORGANIZATION'
AND FG.MENU_ID = PRJ_AUTH_MENU.MENU_ID
AND FG.GRANTEE_KEY = 'PER:' || FU.EMPLOYEE_ID
AND FU.USER_ID = FND_GLOBAL.USER_ID
AND TRUNC(SYSDATE) BETWEEN TRUNC(FG.START_DATE)
AND TRUNC(NVL(FG.END_DATE
, SYSDATE+1))) AUTH_ORG
WHERE PROF.VAL='N'
AND PPP.PROJECT_ROLE_ID = 1
AND PPP.RESOURCE_TYPE_ID = 101
AND RES.PERSON_ID = PPP.RESOURCE_SOURCE_ID
AND RES.RESOURCE_ORGANIZATION_ID IN AUTH_ORG.INSTANCE_PK1_VALUE