View: AMS_USER_ORG_V

Product:	AMS - Marketing
Description:	This view is used by Discoverer Business Area:List Generation Folders to show the Organizations which an user has been assigned to.
Implementation/DBA Data:	APPS.AMS_USER_ORG_V

View Text

SELECT DISTINCT USR.USER_ID
, USR.USER_NAME USER_NAME
, OU.ORGANIZATION_ID USER_ORG_ID
, OU.NAME USER_ORG_NAME
FROM FND_USER USR
, FND_USER_RESP_GROUPS RES
, FND_PROFILE_OPTION_VALUES POV
, FND_PROFILE_OPTIONS POP
, HR_OPERATING_UNITS OU
WHERE USR.USER_ID = RES.USER_ID
AND USR.USER_NAME = USER
AND POV.LEVEL_VALUE_APPLICATION_ID = RES.RESPONSIBILITY_APPLICATION_ID
AND POV.LEVEL_VALUE = RES.RESPONSIBILITY_ID
AND POV.LEVEL_ID = 10003
AND POP.APPLICATION_ID > -1
AND POV.LEVEL_VALUE_APPLICATION_ID > -1
AND POV.PROFILE_OPTION_ID = POP.PROFILE_OPTION_ID
AND POP.APPLICATION_ID = POV.APPLICATION_ID
AND POP.PROFILE_OPTION_NAME = 'ORG_ID'
AND POV.PROFILE_OPTION_VALUE = OU.ORGANIZATION_ID
AND SYSDATE BETWEEN RES.START_DATE
AND NVL(RES.END_DATE
, SYSDATE)
AND SYSDATE BETWEEN POP.START_DATE_ACTIVE
AND NVL(POP.END_DATE_ACTIVE
, SYSDATE)
AND SYSDATE BETWEEN OU.DATE_FROM
AND NVL(OU.DATE_TO
, SYSDATE)

Columns