Tuesday, 13 October 2015

Operating unit and responsibility name identifying based in query in oracle

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

No comments:

Post a Comment