maandag 25 april 2016

Fetch profile options

To find profile options set on different levels you can use the following query. You can filter on level (application, responsibility, user, etc) if necessary.

SELECT AP.Application_Short_Name
,      OP.PROFILE_OPTION_NAME
,      OP_T.USER_PROFILE_OPTION_NAME
,      DECODE (OV.Level_Id,10001,'Site', 10002,'Applicatie',10003, 'Autorisatie', 10004,'Gebruiker') level_Id
,      DECODE (OV.Level_Id,10002,
(SELECT A.Application_Short_Name FROM FND_APPLICATION A WHERE A.Application_Id = OV.Level_Value)
                          ,10003,
(SELECT R.Responsibility_Name FROM FND_RESPONSIBILITY_TL R, FND_RESPONSIBILITY RR WHERE R.Responsibility_Id = OV.Level_Value   AND OV.Level_Value_Application_Id = R.Application_Id AND R.Language = USERENV ('LANG')
AND R.Responsibility_Id = RR.Responsibility_Id AND R.Application_Id = RR.Application_Id  )
                          ,10004,


    (SELECT U.User_Name FROM FND_USER U WHERE U.User_Id = OV.Level_Value)
                              , OV.Level_Value) Level_Value
    ,      OV.Level_Value
    ,      OV.Profile_Option_Value
    FROM FND_PROFILE_OPTION_VALUES    OV
    ,    FND_PROFILE_OPTIONS          OP
    ,    FND_PROFILE_OPTIONS_TL       OP_T
    ,    FND_APPLICATION              AP
    WHERE OV.Profile_Option_id          = OP.Profile_Option_id
    AND   OP.Profile_Option_Name        = OP_T.PROFILE_OPTION_NAME
    AND   OV.Application_Id             = AP.Application_Id
    ;

    Geen opmerkingen:

    Een reactie posten

    Opmerking: Alleen leden van deze blog kunnen een reactie posten.