Query to find Org ID attached to responsibility

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

SELECT   frv.responsibility_name,
         fpov.profile_option_value org_id,
         hou.NAME
    FROM apps.hr_organization_units hou,
         apps.fnd_profile_options_vl fpo,
         apps.fnd_profile_option_values fpov,
         apps.fnd_responsibility_vl frv
   WHERE frv.responsibility_name = <Responsibility Name>
     AND fpov.level_value = frv.responsibility_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 hou.organization_id = TO_NUMBER (fpov.profile_option_value)
ORDER BY frv.responsibility_name