SQL Query to Fetch Oracle Apps User accounts with Responsibility Names in Oracle apps R12 For Specific Operating Unit
SELECT fu.user_name,
(SELECT FULL_NAME FROM PER_PEOPLE_F
WHERE PERSON_ID=FU.EMPLOYEE_ID
AND EFFECTIVE_END_DATE>SYSDATE) EMPLOYEE_NAME
,fr.responsibility_name
FROM apps.fnd_user_resp_groups_all furg
,apps.fnd_user fu
,apps.fnd_responsibility_vl fr
,apps.fnd_profile_option_values fpov
,apps.fnd_profile_options_vl fpo
WHERE fu.user_id= furg.user_id
AND fu.user_name=nvl(:P_USER,fu.user_name)
AND fr.responsibility_name=nvl(:P_RESP,fr.responsibility_name)
AND fr.responsibility_id= furg.responsibility_id
AND (furg.end_date >sysdate OR furg.end_date IS NULL)
AND fpov.level_value=fr.responsibility_id
AND fpov.profile_option_id= fpo.profile_option_id
AND fpov.application_id=fpo.application_id
AND FU.END_DATE IS NULL
AND FR.END_DATE IS NULL
AND fpo.user_profile_option_name='MO: Operating Unit'
AND fpov.profile_option_value=189
ORDER BY 1
SQL Query to Fetch Oracle Apps User accounts with Responsibility Names in Oracle Apps R12 for Specific Operating Unit
