VIEW: APPS.PA_REP_ALL_RES_MGR_V

Source

View Text - Preformatted

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'

View Text - HTML Formatted

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'