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