SELECT RES_MGR.MANAGER_ID , RES_MGR.MANAGER_NAME FROM PA_REP_RES_MGR_V RES_MGR , PA_RESOURCES_DENORM RES , (SELECT NVL(FND_PROFILE.VALUE('PA_SUPER_RESOURCE') , 'N') VAL FROM DUAL) PROF WHERE PROF.VAL='N' UNION SELECT RES.MANAGER_ID , RES.MANAGER_NAME FROM (SELECT DISTINCT MANAGER_ID , MANAGER_NAME FROM PA_RESOURCES_DENORM WHERE SYSDATE BETWEEN RESOURCE_EFFECTIVE_START_DATE AND RESOURCE_EFFECTIVE_END_DATE AND MANAGER_NAME IS NOT NULL) RES , PA_RESOURCES_DENORM RES2 , (SELECT INSTANCE_PK1_VALUE FROM FND_GRANTS FG , FND_USER FU , FND_OBJECTS FOB , (SELECT NVL(PA_SECURITY_PVT.GET_MENU_ID('PA_PRM_RES_AUTH') , -1) MENU_ID FROM DUAL) RES_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 = RES_AUTH_MENU.MENU_ID AND FG.GRANTEE_KEY = 'HZ_PARTY:' || FU.PERSON_PARTY_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)) ) RES_AUTH , (SELECT NVL(FND_PROFILE.VALUE('PA_SUPER_RESOURCE') , 'N') VAL FROM DUAL) PROF WHERE PROF.VAL='N' AND RES.MANAGER_ID = RES2.PERSON_ID AND RES2.RESOURCE_ORGANIZATION_ID IN RES_AUTH.INSTANCE_PK1_VALUE UNION ALL SELECT RES.MANAGER_ID , RES.MANAGER_NAME FROM (SELECT DISTINCT MANAGER_ID , MANAGER_NAME FROM PA_RESOURCES_DENORM WHERE SYSDATE BETWEEN RESOURCE_EFFECTIVE_START_DATE AND RESOURCE_EFFECTIVE_END_DATE AND MANAGER_NAME IS NOT NULL) RES , (SELECT NVL(FND_PROFILE.VALUE('PA_SUPER_RESOURCE') , 'N') VAL FROM DUAL) PROF WHERE PROF.VAL='Y'