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

1 comment:

  1. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, BI Publisher, OAF, ADF, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete