Query to get Responsibility and Operating Unit association details
Get Org id from Responsibility Name
--------------------------------------------------------
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
GET RESPONSIBILITY
Name from Org id
--------------------------------------------------------
SELECT frv.responsibility_name,
fpov.profile_option_value org_id,
hou.NAME
FROM apps.fnd_profile_options_vl fpo,
apps.fnd_responsibility_vl frv,
apps.fnd_profile_option_values fpov,
apps.hr_organization_units hou
WHERE hou.NAME = <OU Name>
AND fpov.profile_option_value = TO_CHAR (hou.organization_id)
AND fpo.profile_option_id = fpov.profile_option_id
AND fpo.user_profile_option_name = 'MO: Operating Unit'
AND frv.responsibility_id = fpov.level_value
ORDER BY frv.responsibility_name
--------------------------------------------------------
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
GET RESPONSIBILITY
Name from Org id--------------------------------------------------------
SELECT frv.responsibility_name,
fpov.profile_option_value org_id,
hou.NAME
FROM apps.fnd_profile_options_vl fpo,
apps.fnd_responsibility_vl frv,
apps.fnd_profile_option_values fpov,
apps.hr_organization_units hou
WHERE hou.NAME = <OU Name>
AND fpov.profile_option_value = TO_CHAR (hou.organization_id)
AND fpo.profile_option_id = fpov.profile_option_id
AND fpo.user_profile_option_name = 'MO: Operating Unit'
AND frv.responsibility_id = fpov.level_value
ORDER BY frv.responsibility_name
Very useful query.
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteThank you for the query
ReplyDeleteHi,
ReplyDeleteCould you please share a query to identify the OU by providing the apps username.
My requirement is to get the list of OUs for a particular apps user by providing the responsibility name, also there could be a possibility that I can have security profile set for the responsibility, so in that case there might be chances of associating multiple OUs to the same responsibility.
Please help.
Thanks in Advance!