View: PA_REP_ORG_UTIL_V

Product:	PA - Projects
Description:	PA_REP_ORG_UTIL_V is a view that lists all child organizations for a given application user who has utilization authority
Implementation/DBA Data:	APPS.PA_REP_ORG_UTIL_V

View Text

SELECT POHD.PARENT_ORGANIZATION_ID
, PA_EXPENDITURES_UTILS.GETORGTLNAME(POHD.PARENT_ORGANIZATION_ID)
, POHD.CHILD_ORGANIZATION_ID
, PA_EXPENDITURES_UTILS.GETORGTLNAME(POHD.CHILD_ORGANIZATION_ID)
FROM PA_ORG_HIERARCHY_DENORM POHD
WHERE POHD.PA_ORG_USE_TYPE = 'REPORTING'
AND NVL(POHD.ORG_ID
, NVL(TO_NUMBER( DECODE(SUBSTR(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTR(USERENV('CLIENT_INFO')
, 1
, 10) ))
, -99)) = NVL(TO_NUMBER( DECODE(SUBSTR(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTR(USERENV('CLIENT_INFO')
, 1
, 10) ))
, -99)
AND POHD.PARENT_ORGANIZATION_ID IN (SELECT DISTINCT FG.INSTANCE_PK1_VALUE
FROM FND_GRANTS FG
, FND_OBJECTS FOB
, (SELECT NVL(PA_SECURITY_PVT.GET_MENU_ID('PA_PRM_UTL_AUTH')
, -1) MENU_ID
, NVL(PA_SECURITY_PVT.GET_GRANTEE_KEY
, -1) GRANTEE_KEY FROM DUAL) FCST_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 = FCST_AUTH_MENU.MENU_ID
AND FG.GRANTEE_KEY = FCST_AUTH_MENU.GRANTEE_KEY
AND TRUNC(SYSDATE) BETWEEN TRUNC(FG.START_DATE)
AND TRUNC(NVL(FG.END_DATE
, SYSDATE+1)) ) ORDER BY POHD.PARENT_LEVEL DESC
, PA_EXPENDITURES_UTILS.GETORGTLNAME(POHD.PARENT_ORGANIZATION_ID) ASC

Columns