• Query profile options, their values and who set it at different levels.

    SELECT tl.user_profile_option_name      "Name"
          ,decode( to_char(pov.level_id)
                  ,'10001', 'SITE'
                  ,'10002', 'APPLICATION'
                  ,'10003', 'RESPONSIBILITY'
                  ,'10004', 'USER'
                  ,'???'
                 )                         "Level"
          ,decode( to_char(pov.level_id)
                  ,'10001', ''
                  ,'10002', app.application_name
                  ,'10003', rsp.responsibility_name
                  ,'10004', usr.user_name
                  ,'???'
                 )                         "Context"
          ,pov.last_update_date            "Last updated"
          ,usr2.user_name                  "Updated By"
          ,pov.profile_option_value        "Value"
      FROM fnd_profile_options           po
          ,fnd_profile_options_tl        tl
          ,fnd_profile_option_values     pov
          ,fnd_user                      usr
          ,fnd_user                      usr2
          ,(SELECT *
              FROM fnd_application_tl
             WHERE language='US')        app
          ,(SELECT *
              FROM fnd_responsibility_tl
             WHERE language='US')        rsp
     WHERE pov.application_id          = po.application_id
       AND tl.user_profile_option_name LIKE '%Log%'
    --   AND  pov.profile_option_value    LIKE '%###%'
    --   AND  usr.user_name               = 'ARNOLDVK'         -- set for
    --   AND usr2.user_name               IN ('USER1','USER2') -- set by
       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 tl.profile_option_name      = po.profile_option_name
       AND pov.last_updated_by         = usr2.user_id
     ORDER BY "Updated By"
             ,"Name"
             ,pov.level_id
             ,"Context";

     

    Posted by Arnold van Koppen @ 16:37 for EBS R12, Scripts & Queries |

    Leave a Reply

    Your email address will not be published. Required fields are marked *


    You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Ads by Google