VIEW: APPS.IGI_SIA_INVOICE_CLERK_RG

Source

View Text - Preformatted

SELECT 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 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') union select 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 is null 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 (+)

View Text - HTML Formatted

SELECT 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 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') UNION SELECT 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 IS NULL
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 (+)