Skip to main content

SQL Query to find profile option value from Back-end/SQL


Background:
 When we run, the following SQL Query/Script will provide the profile option values set at different levels. That is Site, Application, Responsibility, User.
 So, this script lists ALL Users/Responsibilities/Applications set against a particular Profile option
SQL Script:
 Enter 'Profile Option Name' as need to check
 Eg. FND: Debug Log Enabled
[You can get the exact name from table fnd_profile_options_tl] 
  SELECT fpo.profile_option_name SHORT_NAME,
         fpot.user_profile_option_name NAME,
         DECODE (fpov.level_id,
                 10001, 'Site',
                 10002, 'Application',
                 10003, 'Responsibility',
                 10004, 'User',
                 10005, 'Server',
                 'UnDef')
            LEVEL_SET,
         DECODE (TO_CHAR (fpov.level_id),
                 '10001', '',
                 '10002', fap.application_short_name,
                 '10003', frsp.responsibility_key,
                 '10005', fnod.node_name,
                 '10006', hou.name,
                 '10004', fu.user_name,
                 'UnDef')
            "CONTEXT",
         fpov.profile_option_value VALUE
    FROM fnd_profile_options fpo,
         fnd_profile_option_values fpov,
         fnd_profile_options_tl fpot,
         fnd_user fu,
         fnd_application fap,
         fnd_responsibility frsp,
         fnd_nodes fnod,
         hr_operating_units hou
   WHERE     fpo.profile_option_id = fpov.profile_option_id(+)
         AND fpo.profile_option_name = fpot.profile_option_name
         AND fu.user_id(+) = fpov.level_value
         AND frsp.application_id(+) = fpov.level_value_application_id
         AND frsp.responsibility_id(+) = fpov.level_value
         AND fap.application_id(+) = fpov.level_value
         AND fnod.node_id(+) = fpov.level_value
         AND hou.organization_id(+) = fpov.level_value
         AND fpot.user_profile_option_name IN ('&User_Profile_Option_Name')
ORDER BY short_name;

output put will be like:
SHORT_NAME                     NAME                                     LEVEL_SET      CONTEXT                                  VALUE
------------------------------ ---------------------------------------- -------------- ---------------------------------------- --------------------
AFLOG_ENABLED                  FND: Debug Log Enabled                   Site                                                    N
 

Comments

Popular posts from this blog

EBS R12 “Unable to Authenticate Session”

Oracle E-Business Suite environment with IE8 and higher, When  cloning the EBS envitorment or you are logging out from your DEV Instance and want to login to  PROD-Instance it happens “Unable to authenticate session” Go to all programs, Right click on your IE icon and add -noframemerging to the end of the link in the Target field e.g.”C:\Program Files\Internet Explorer\ iexplore.exe ” -noframemerging (shown below) 1)  Start -> Program Files 2)  Right click on the Internet Explorer icon and select Properties 3)  Add -nomerge to the end of the link in the Target field i.e. “C:\Program Files\Internet Explorer\ iexplore.exe ” -noframemerging 4)  Click the OK button to close the window 5) Stop all Browser sessions. 6) Start a new Browser session and logon to the Instance

EBS Standby Role Tranistion using standby database and standby application Tier

 Role Transitions A database can operate in either a primary or standby role - these roles are mutually exclusive. Oracle Data Guard enables you to change these roles dynamically by issuing SQL commands, and supports the following transitions: Switchover Allows the primary database to switch roles with one of its standby databases. There is no data loss during a switchover. After a switchover, each database continues to participate in the Oracle Data Guard configuration with its new role. Failover Changes a standby database to the primary role in response to a primary database failure. The following role transitions are discussed: 6.1 Performing a Switchover 6.2 Performing a Failover 6.3 Performing a Switchback to the Primary Following A Switchover/Failover Each of these three transitions requires some application configuration to be performed. Most of the application configuration step...

EBS R12.2.4 AutoConfig could not successfully execute the following scripts followed by error "txkGenADOPWrapper.pl INSTE8_APPLY 1"

issue:txkGenADOPWrapper.pl    INSTE8_APPLY       1 WARNING: [AutoConfig Error Report] The following report lists errors AutoConfig encountered during each phase of its execution.  Errors are grouped by directory and phase. The report format is:       <filename>  <phase>  <return code where appropriate>   [APPLY PHASE]   AutoConfig could not successfully execute the following scripts:     Directory: /u01/applprod/fs2/FMW_Home/webtier/perl/bin/perl -I /u01/applprod/fs2/FMW_Home/webtier/perl/lib/5.10.0 -I /u01/applprod/fs2/FMW_Home/webtier/perl/lib/site_perl/5.10.0 -I /u01/applprod/fs2/EBSapps/appl/au/12.0.0/perl -I /u01/applprod/fs2/FMW_Home/webtier/ohs/mod_perl/lib/site_perl/5.10.0/x86_64-linux-thread-multi /u01/applprod/fs2/inst/apps/PRODDB_epc-apps12-node41v/admin/install       txkGenADOPWrapper.pl    ...