Thursday, 15 October 2015

SQL Queries for checking Profile Option Values

The following queries are useful to get the profile option values of a profile option at site, application, responsibility and user level
1) Obtain Profile Option values for Profile Option name like ‘%Ledger%’ and  Responsibility name like ‘%General%Ledger%’
 

/* Formatted on 10/15/2015 12:35:37 PM (QP5 v5.240.12305.39446) */
  SELECT SUBSTR (pro1.user_profile_option_name, 1, 35) Profile,
         DECODE (pov.level_id,
                 10001, 'Site',
                 10002, 'Application',
                 10003, 'Resp',
                 10004, 'User')
            Option_Level,
         DECODE (pov.level_id,
                 10001, 'Site',
                 10002, appl.application_short_name,
                 10003, resp.responsibility_name,
                 10004, u.user_name)
            Level_Value,
         NVL (pov.profile_option_value, 'Is Null') Profile_option_Value
    FROM fnd_profile_option_values pov,
         fnd_responsibility_tl resp,
         fnd_application appl,
         fnd_user u,
         fnd_profile_options pro,
         fnd_profile_options_tl pro1
   WHERE     pro1.user_profile_option_name LIKE ('%Ledger%')
         AND pro.profile_option_name = pro1.profile_option_name
         AND pro.profile_option_id = pov.profile_option_id
         AND resp.responsibility_name LIKE '%General%Ledger%' /* comment this line  if you need to check profiles for all responsibilities */
         AND pov.level_value = resp.responsibility_id(+)
         AND pov.level_value = appl.application_id(+)
         AND pov.level_value = u.user_id(+)
ORDER BY 1, 2;
 
2) Obtain all Profile Option values setup for a particular responsibility. Replace the responsibility name as per your requirement.
 
/* Formatted on 10/15/2015 12:36:49 PM (QP5 v5.240.12305.39446) */
  SELECT SUBSTR (pro1.user_profile_option_name, 1, 35) Profile,
         DECODE (pov.level_id,
                 10001, 'Site',
                 10002, 'Application',
                 10003, 'Resp',
                 10004, 'User')
            Option_Level,
         DECODE (pov.level_id,
                 10001, 'Site',
                 10002, appl.application_short_name,
                 10003, resp.responsibility_name,
                 10004, u.user_name)
            Level_Value,
         NVL (pov.profile_option_value, 'Is Null') Profile_option_Value
    FROM fnd_profile_option_values pov,
         fnd_responsibility_tl resp,
         fnd_application appl,
         fnd_user u,
         fnd_profile_options pro,
         fnd_profile_options_tl pro1
   WHERE     pro.profile_option_name = pro1.profile_option_name
         AND pro.profile_option_id = pov.profile_option_id
         AND resp.responsibility_name LIKE '%General%Ledger%'
         AND pov.level_value = resp.responsibility_id(+)
         AND pov.level_value = appl.application_id(+)
         AND pov.level_value = u.user_id(+)
ORDER BY 1, 2;
 
 
Similarly, you can tweak the above queries to obtain Profile Option Values set for a particular User or a particular application. 

2 comments:

  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
  2. CALFRE is the local search engine where you can find various training institutes that provide training for Oracle Fusion HCM through online in Hyderabad.
    Oracle Fusion Training Institute

    ReplyDelete