VIEW: APPS.AMS_USER_ORG_V

Source

View Text - Preformatted

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)

View Text - HTML Formatted

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)