 Profile Option values are extremely important ... and difficult to extract from the system. As far as I am aware, the only standard report is the 'User Profile Option Values' report.
So a while ago I created a SQL script to extract them from the database. I usually export the output to Excel to do the actual analysis.
The script has the following optional (which means all may be left blank) parameters:
| Parameter |
Description
|
Default value
|
Example |
| LANG |
Profile options translated in (installed!) language |
US |
NL |
| SEARCH |
Search argument for profile options |
% |
GL%ooks% |
| MODULE |
Applications Module |
% |
Pay% |
| LAST_UPDATE |
Include only the updates on or after this date, format is DD-MON-RRRR |
01-JAN-1000 |
21-DEC-2007 |
| PROF_UPD_BY |
Username who updated this profile |
% |
OPERATIONS |
The script has the following columns:
| Column |
Description
|
| Profile Level |
Level 1 = Site
Level 2 = Application
Level 3 = Responsibility
Level 4 = User
|
| Site |
In case of site level value, it has the value 'SITE' |
| Application Level |
In case of application level value, it has the name of the application |
| Responsibility Level |
In case of responsibility level value, it has the name of the responsibility |
| User Level |
In case of user level value, it has the name of the user |
| Profile Name |
Profile Option name |
| Profile Option Value |
Value of the Profile Option |
| Source Module |
Related source module. E.g. ADI profile options belong to General Ledger module |
| Last Update Date |
Last update date |
| Update By |
User name who performed the last update |
The file can be executed with SQL*Plus (it includes some formatting and a spool file, c:\temp\profiles.txt).
Profile Option Values (7.04 kB)
Find below the SQL, which can be copied/pasted in SQL Developer or TOAD.
SELECT FPOV.LEVEL_ID-10000 PROFILE_LEVEL, -- SITE level
' ' SITE_LEVEL,
APP.APPLICATION_NAME APPLICATION_LEVEL,
' ' RESPONSIBILITY_LEVEL,
' ' USER_LEVEL,
FPOT.USER_PROFILE_OPTION_NAME PROFILE_NAME,
FPOV.PROFILE_OPTION_VALUE,
FAT.APPLICATION_NAME SOURCE_MODULE,
FPOV.LAST_UPDATE_DATE,
FU.USER_NAME UPDATE_BY
FROM APPS.FND_PROFILE_OPTION_VALUES FPOV,
APPS.FND_APPLICATION_TL APP,
APPS.FND_PROFILE_OPTIONS_TL FPOT,
APPS.FND_PROFILE_OPTIONS FPO,
APPS.FND_APPLICATION_TL FAT,
APPS.FND_USER FU
WHERE FPOV.LEVEL_ID = 10002
AND APP.LANGUAGE = nvl('&LANG','US')
AND APP.LANGUAGE = FPOT.LANGUAGE
AND FPO.PROFILE_OPTION_ID = FPOV.PROFILE_OPTION_ID
AND FPO.PROFILE_OPTION_NAME = FPOT.PROFILE_OPTION_NAME
AND FPOV.LEVEL_VALUE = APP.APPLICATION_ID
AND FPOV.APPLICATION_ID = FPO.APPLICATION_ID
AND FPOV.APPLICATION_ID = FAT.APPLICATION_ID
AND FPOT.USER_PROFILE_OPTION_NAME LIKE '&SEARCH%'
AND FAT.APPLICATION_NAME LIKE '&MODULE%'
AND FAT.LANGUAGE = FPOT.LANGUAGE
AND FPOV.LAST_UPDATE_DATE >= to_date(nvl('&LAST_UPDATE','01-JAN-1000'),'DD-MON-YYYY')
AND FPOV.LAST_UPDATED_BY = FU.USER_ID
AND FU.USER_NAME LIKE UPPER('&PROF_UPD_BY%')
UNION
SELECT LEVEL_ID-10000 PROFILE_LEVEL, -- USER level
' ',
' ',
' ',
USERS.USER_NAME,
FPOT.USER_PROFILE_OPTION_NAME PROFILE_NAME,
FPOV.PROFILE_OPTION_VALUE,
FAT.APPLICATION_NAME BRON_APPLICATIE,
FPOV.LAST_UPDATE_DATE,
FU.USER_NAME
FROM APPS.FND_PROFILE_OPTION_VALUES FPOV,
APPS.FND_USER USERS,
APPS.FND_PROFILE_OPTIONS_TL FPOT,
APPS.FND_PROFILE_OPTIONS FPO,
APPS.FND_APPLICATION_TL FAT,
APPS.FND_USER FU
WHERE LEVEL_ID = 10004
AND FPO.PROFILE_OPTION_ID = FPOV.PROFILE_OPTION_ID
AND FPO.PROFILE_OPTION_NAME = FPOT.PROFILE_OPTION_NAME
AND FPOV.LEVEL_VALUE = USERS.USER_ID
AND FPOV.APPLICATION_ID = FPO.APPLICATION_ID
AND FPOT.LANGUAGE = nvl('&LANG','US')
AND FPOV.APPLICATION_ID = FAT.APPLICATION_ID
AND FAT.LANGUAGE = FPOT.LANGUAGE
AND FPOT.USER_PROFILE_OPTION_NAME LIKE '&SEARCH%'
AND FAT.APPLICATION_NAME LIKE '&MODULE%'
AND FPOV.LAST_UPDATE_DATE >= to_date(nvl('&LAST_UPDATE','01-JAN-1000'),'DD-MON-YYYY')
AND FPOV.LAST_UPDATED_BY = FU.USER_ID
AND FU.USER_NAME LIKE UPPER('&PROF_UPD_BY%')
UNION
SELECT LEVEL_ID-10000 PROFILE_LEVEL, -- APPLICATION level
'SITE' SITE,
' ',
' ',
' ',
FPOT.USER_PROFILE_OPTION_NAME PROFILE_NAME,
FPOV.PROFILE_OPTION_VALUE,
FAT.APPLICATION_NAME BRON_APPLICATIE,
FPOV.LAST_UPDATE_DATE,
FU.USER_NAME
FROM APPS.FND_PROFILE_OPTION_VALUES FPOV,
APPS.FND_PROFILE_OPTIONS_TL FPOT,
APPS.FND_PROFILE_OPTIONS FPO,
APPS.FND_APPLICATION_TL FAT,
APPS.FND_USER FU
WHERE LEVEL_ID = 10001
AND FPO.PROFILE_OPTION_ID = FPOV.PROFILE_OPTION_ID
AND FPO.PROFILE_OPTION_NAME = FPOT.PROFILE_OPTION_NAME
AND FPOV.APPLICATION_ID = FPO.APPLICATION_ID
AND FPOT.LANGUAGE = nvl('&LANG','US')
AND FPOV.APPLICATION_ID = FAT.APPLICATION_ID
AND FAT.LANGUAGE = FPOT.LANGUAGE
AND FPOT.USER_PROFILE_OPTION_NAME LIKE '&SEARCH%'
AND FAT.APPLICATION_NAME LIKE '&MODULE%'
AND FPOV.LAST_UPDATE_DATE >= to_date(nvl('&LAST_UPDATE','01-JAN-1000'),'DD-MON-YYYY')
AND FPOV.LAST_UPDATED_BY = FU.USER_ID
AND FU.USER_NAME LIKE UPPER('&PROF_UPD_BY%')
UNION
SELECT LEVEL_ID-10000 PROFILE_LEVEL, -- RESPONSIBILITY level
' ',
' ',
FRT.RESPONSIBILITY_NAME,
' ',
FPOT.USER_PROFILE_OPTION_NAME PROFILE_NAME,
FPOV.PROFILE_OPTION_VALUE,
FAT.APPLICATION_NAME BRON_APPLICATIE,
FPOV.LAST_UPDATE_DATE,
FU.USER_NAME
FROM APPS.FND_PROFILE_OPTION_VALUES FPOV,
APPS.FND_PROFILE_OPTIONS_TL FPOT,
APPS.FND_PROFILE_OPTIONS FPO,
APPS.FND_RESPONSIBILITY_TL FRT,
APPS.FND_APPLICATION_TL FAT,
APPS.FND_USER FU
WHERE LEVEL_ID = 10003
AND FRT.RESPONSIBILITY_ID = FPOV.LEVEL_VALUE
AND FRT.LANGUAGE = FPOT.LANGUAGE
AND FPO.PROFILE_OPTION_ID = FPOV.PROFILE_OPTION_ID
AND FPO.PROFILE_OPTION_NAME = FPOT.PROFILE_OPTION_NAME
AND FPOV.APPLICATION_ID = FPO.APPLICATION_ID
AND FPOT.LANGUAGE = nvl('&LANG','US')
AND FPOV.APPLICATION_ID = FAT.APPLICATION_ID
AND FAT.LANGUAGE = FPOT.LANGUAGE
AND FPOT.USER_PROFILE_OPTION_NAME LIKE '&SEARCH%'
AND FAT.APPLICATION_NAME LIKE '&MODULE%'
AND FPOV.LAST_UPDATE_DATE >= to_date(nvl('&LAST_UPDATE','01-JAN-1000'),'DD-MON-YYYY')
AND FPOV.LAST_UPDATED_BY = FU.USER_ID
AND FU.USER_NAME LIKE UPPER('&PROF_UPD_BY%')
ORDER BY PROFILE_NAME,PROFILE_LEVEL;
|
Hi sven ,
I hope you are doing good. Is there any way to throw the same form...
Comments