VIEW: APPS.PA_REP_FORECAST_ORG_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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