How to get the list of users assigned with responsibilities as per Operating Unit
Query :
SELECT hou.NAME,fpov.profile_option_value org_id,frv.responsibility_name, d.full_name,a.USER_NAME, b.START_DATE,b.end_date
FROM apps.hr_organization_units hou,
apps.fnd_profile_options_vl fpo,
apps.fnd_profile_option_values fpov,
apps.fnd_responsibility_vl frv,apps.FND_USER a,apps.FND_USER_RESP_GROUPS_all b,apps. per_all_people_f d,apps.FND_RESPONSIBILITY_TL res
WHERE
fpov.level_value = frv.responsibility_id and b.responsibility_id = res.responsibility_id
and a.USER_ID=b.USER_ID and b.RESPONSIBILITY_ID=frv.RESPONSIBILITY_ID and a.employee_id=d.person_id
AND fpo.profile_option_id = fpov.profile_option_id
AND fpo.user_profile_option_name = ‘MO: Operating Unit’
AND fpov.profile_option_id = fpo.profile_option_id and res.language = ‘US’
AND hou.organization_id = TO_NUMBER (fpov.profile_option_value)
and sysdate between d.effective_start_date and d.effective_end_Date
and sysdate between nvl(a.start_date,sysdate) and nvl(a.end_date,sysdate) and sysdate between nvl(frv.start_date,sysdate) and nvl(frv.end_date,sysdate)
group by a.USER_NAME,frv.responsibility_name, fpov.profile_option_value , hou.NAME,d.full_name,b.START_DATE,b.end_date
ORDER BY frv.responsibility_name
DBA 