View: IGI_SIA_DEPT_APPROVER_RG

Product:	IGI - Public Sector Financials International
Description:	Allows selection of users as Department Approvers.
Implementation/DBA Data:	APPS.IGI_SIA_DEPT_APPROVER_RG

View Text

SELECT DISTINCT U.USER_ID
, U.USER_NAME
, HE.FULL_NAME
FROM FND_USER U
, FND_PROFILE_OPTION_VALUES V
, FND_PROFILE_OPTIONS O
, HR_EMPLOYEES HE
, FND_USER_RESP_GROUPS R
WHERE U.USER_ID = R.USER_ID
AND NVL(U.END_DATE
, SYSDATE+1) > SYSDATE
AND NVL(O.END_DATE_ACTIVE
, SYSDATE+1) > SYSDATE
AND R.RESPONSIBILITY_ID = V.LEVEL_VALUE
AND V.PROFILE_OPTION_VALUE = FND_PROFILE.VALUE_WNPS('ORG_ID')
AND V.LEVEL_ID = 10003
AND V.PROFILE_OPTION_ID = O.PROFILE_OPTION_ID
AND O.PROFILE_OPTION_NAME = 'ORG_ID'
AND U.EMPLOYEE_ID = HE.EMPLOYEE_ID (+)
AND EXISTS ( SELECT V1.LEVEL_VALUE
FROM FND_PROFILE_OPTION_VALUES V1
, FND_PROFILE_OPTIONS O1
WHERE V1.PROFILE_OPTION_VALUE = FND_PROFILE.VALUE_WNPS('ORG_ID')
AND V1.LEVEL_ID = 10001
AND V1.PROFILE_OPTION_ID = O1.PROFILE_OPTION_ID
AND O1.PROFILE_OPTION_NAME = 'ORG_ID') UNION SELECT DISTINCT U.USER_ID
, U.USER_NAME
, HE.FULL_NAME
FROM FND_USER U
, FND_PROFILE_OPTION_VALUES V
, FND_PROFILE_OPTIONS O
, HR_EMPLOYEES HE
, FND_USER_RESP_GROUPS R
WHERE U.USER_ID = R.USER_ID
AND NVL(U.END_DATE
, SYSDATE+1) > SYSDATE
AND NVL(O.END_DATE_ACTIVE
, SYSDATE+1) > SYSDATE
AND R.RESPONSIBILITY_ID = V.LEVEL_VALUE
AND V.PROFILE_OPTION_VALUE = FND_PROFILE.VALUE_WNPS('ORG_ID')
AND V.LEVEL_ID = 10003
AND V.PROFILE_OPTION_ID = O.PROFILE_OPTION_ID
AND O.PROFILE_OPTION_NAME = 'ORG_ID'
AND U.EMPLOYEE_ID = HE.EMPLOYEE_ID (+)
AND NOT EXISTS ( SELECT V1.LEVEL_VALUE
FROM FND_PROFILE_OPTION_VALUES V1
, FND_PROFILE_OPTIONS O1
WHERE V1.PROFILE_OPTION_VALUE = FND_PROFILE.VALUE_WNPS('ORG_ID')
AND V1.LEVEL_ID = 10001
AND V1.PROFILE_OPTION_ID = O1.PROFILE_OPTION_ID
AND O1.PROFILE_OPTION_NAME = 'ORG_ID')

Columns