Monday, October 8, 2012

Query to get the Profile option values at all levels


Execute the following query with valid value for any of the following columns.
profile_option_id
profile_option_name
user_profile_option_name


SELECT *
FROM
  (SELECT po.profile_option_name "NAME",
    po.user_profile_option_name "FRIENDLYNAME",
    po.description "DESCRIPTION",
    DECODE(TO_CHAR(pov.level_id), '10001', 'SITE', '10002', 'APPLICATION', '10003', 'RESPONSIBILITY', '10004', 'USER', '???') "LEV",
    DECODE(TO_CHAR(pov.level_id), '10001', '', '10002', app.application_id, '10003', rsp.responsibility_id, '10004', usr.user_id, '???') "CONTEXTID",
    DECODE(TO_CHAR(pov.level_id), '10001', '', '10002', app.application_name, '10003', rsp.responsibility_name, '10004', usr.user_name, '???') "CONTEXT",
    DECODE(TO_CHAR(pov.level_id), '10001', '', '10002', '', '10003', rsp.application_id, '10004', to_number(NULL), '???') "RESPAPPID",
    pov.profile_option_value "VALUE",
    fpc.description category_name,
    po.sql_validation
  FROM fnd_profile_options_vl po,
    fnd_profile_option_values pov,
    fnd_user usr,
    fnd_application_tl app,
    fnd_responsibility_tl rsp,
    fnd_profile_cats_tl fpc,
    fnd_profile_cat_options fpco
  WHERE 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 fpco.category_name                     = fpc.name (+)
  AND fpco.profile_option_id (+)             = po.profile_option_id
  AND fpco.profile_option_application_id (+) = po.application_id
    --AND po.profile_option_id = <>
    --AND po.profile_option_name = '<>'
  AND po.user_profile_option_name = <>
    --It is language dependent and gets stored in table  FND_PROFILE_OPTIONS_TL
  AND app.LANGUAGE (+) = USERENV('LANG')
  AND rsp.LANGUAGE (+) = USERENV('LANG')
  ) QRSLT
ORDER BY FRIENDLYNAME ASC

No comments:

Post a Comment