VIEW: APPS.PA_REP_ORG_UTIL_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 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

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
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