View: PA_REP_FORECAST_ORG_V

Product:	PA - Projects
Description:	PA_REP_FORECAST_ORG_V is a view that lists all child organizations for a given application user who has forecast authority
Implementation/DBA Data:	APPS.PA_REP_FORECAST_ORG_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
, PA_IMPLEMENTATIONS IMP
, (SELECT NVL(FND_PROFILE.VALUE('PA_SUPER_PROJECT_VIEW')
, 'N') VAL
FROM DUAL) PROF
, (SELECT FG.INSTANCE_PK1_VALUE
FROM FND_GRANTS FG
, FND_USER FU
, FND_OBJECTS FOB
, (SELECT NVL(PA_SECURITY_PVT.GET_MENU_ID('PA_PRM_FCST_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 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 POHD.PA_ORG_USE_TYPE = 'REPORTING'
AND IMP.ORG_ID = POHD.ORG_ID
AND POHD.PARENT_ORGANIZATION_ID = AUTH_ORG.INSTANCE_PK1_VALUE UNION 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
, PA_IMPLEMENTATIONS IMP
, (SELECT NVL(FND_PROFILE.VALUE('PA_SUPER_PROJECT_VIEW')
, 'N') VAL
FROM DUAL) PROF
WHERE PROF.VAL='Y'
AND POHD.PA_ORG_USE_TYPE = 'REPORTING'
AND IMP.ORG_ID = POHD.ORG_ID

Columns