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...

adgrants issue with 19c PDB database

 adgrant - system.fnd_oracle_userid ORA-06550: line 1094, column 17 SP2-0625: Error printing variable "result_set" adgrants issue with 19c PDB database  Connect to a multitenant 19c database and follow below steps to resolve ORA error 1- source the cdb env file  2-set the  ORACLE_PDB_SID=<PDB_NAME>   3- connect as  sqlplus / as sysdba 4- go to location of adgrant and execute cd /u01/app/oracle/product/19.0.0/dbhome_1/appsutil/sql    SQL>  @adgrants.sql APPS Note: ignore warnings during adgrant session a d.plsql.ad_zd_sys.GIVE_PRIVILEGE