Query responsibilities given the OU name
/* Formatted on 10/13/2015 5:19:16 PM (QP5 v5.240.12305.39446) */
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 = :p_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
Query OU given the responsibilities name
/* Formatted on 10/13/2015 5:19:05 PM (QP5 v5.240.12305.39446) */
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 = :p_resp_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
/* Formatted on 10/13/2015 5:19:16 PM (QP5 v5.240.12305.39446) */
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 = :p_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
Query OU given the responsibilities name
/* Formatted on 10/13/2015 5:19:05 PM (QP5 v5.240.12305.39446) */
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 = :p_resp_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
No comments:
Post a Comment