Friday, May 4, 2012

Profile Option at Responsibility level using SQL

Profile Option at Responsibility level using SQL


I have been asked to extract data. User need to know the Set Of Books name against every responsibility defined in System Profile Value.

I tried to do it myself more than two hours I did well but wasn't able to get exact results so I google then and found a query by Jiltin (http://www.notesbit.com/index.php/oracle-applications/11i-scripts/how-do-you-check-the-profile-option-values-using-sql-oracle-applications/)  over there which help a lot. Following is my query and at the end is the detail I found by googling.


SELECT PO.USER_PROFILE_OPTION_NAME PROFILE_OPTION,
       (SELECT APPLICATION_NAME
          FROM FND_APPLICATION_VL FA
         WHERE FA.APPLICATION_ID = POV.LEVEL_VALUE_APPLICATION_ID)APPLICATION,
               RESPONSIBILITY_NAME RESPONSIBILITY,
               PROFILE_OPTION_VALUE PROFILE_VALUE
  FROM FND_PROFILE_OPTIONS_VL PO,
       FND_PROFILE_OPTION_VALUES POV,
       FND_RESPONSIBILITY RSP,
       FND_RESPONSIBILITY_TL RSP_T
 WHERE PO.PROFILE_OPTION_NAME LIKE 'GL_SET_OF_BKS_NAME'
   AND POV.APPLICATION_ID = PO.APPLICATION_ID
   AND POV.PROFILE_OPTION_ID = PO.PROFILE_OPTION_ID
   AND RSP.APPLICATION_ID  = POV.LEVEL_VALUE_APPLICATION_ID
   AND RSP.RESPONSIBILITY_ID  = POV.LEVEL_VALUE
   AND RSP.RESPONSIBILITY_ID = RSP_T.RESPONSIBILITY_ID
   AND RSP.END_DATE IS NULL
ORDER BY PROFILE_OPTION,APPLICATION,RESPONSIBILITY,PROFILE_VALUE


Following is the query I found on Internet

SELECT po.profile_option_name "NAME",
po.USER_PROFILE_OPTION_NAME,
        decode
(to_char(pov.level_id),
               
‘10001′, ‘SITE’,
               
‘10002′, ‘APP’,
               
‘10003′, ‘RESP’,
               
‘10005′, ‘SERVER’,
               
‘10006′, ‘ORG’,
               
‘10004′, ‘USER’, ‘???’) "LEV",
        decode
(to_char(pov.level_id),
               
‘10001′, ,
               
‘10002′, app.application_short_name,
               
‘10003′, rsp.responsibility_key,
               
‘10005′, svr.node_name,
               
‘10006′, org.name,
               
‘10004′, usr.user_name,
               
‘???’) "CONTEXT",
        pov.profile_option_value 
"VALUE"
 
FROM   FND_PROFILE_OPTIONS_VL po,
        FND_PROFILE_OPTION_VALUES pov,
        fnd_user usr,
        fnd_application app,
        fnd_responsibility rsp,
        fnd_nodes svr,
        hr_operating_units org
 
WHERE  po.profile_option_name LIKE ‘%&&profile%’
 
AND    pov.application_id = po.application_id
 
AND    pov.profile_option_id = po.profile_option_id
 
AND    usr.user_id (+) = pov.level_value
 
AND    rsp.application_id (+) = pov.level_value_application_id
 
AND    rsp.responsibility_id (+) = pov.level_value
 
AND    app.application_id (+) = pov.level_value
 
AND    svr.node_id (+) = pov.level_value
 
AND    org.organization_id (+) = pov.level_value
 
AND decode(to_char(pov.level_id),
               
‘10001′, ,
               
‘10002′, app.application_short_name,
               
‘10003′, rsp.responsibility_key,
               
‘10005′, svr.node_name,
               
‘10006′, org.name,
               
‘10004′, usr.user_name,
               
‘???’) LIKE ‘%&&username%’
 
ORDER BY "NAME", pov.level_id, "VALUE";







Column
Description
Profile Level
Level1 =Site(level_id=10001)
Level 2 = Application ( level_id=10002)
Level 3 = Responsibility ( level_id=10003)
Level 4 = User ( level_id=10004)
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



Ref:http://www.notesbit.com/index.php/oracle-applications/11i-scripts/how-do-you-check-the-profile-option-values-using-sql-oracle-applications/


Your feedback/comment is quite important for improving this blog so feel free to leave your comments & suggestions

0 comments:

Post a Comment