Useful queries in Oracle Apps

1. Query  to get concurrent program name and its parameter

SELECT fcpl.user_concurrent_program_name
     , fcp.concurrent_program_name
     , par.end_user_column_name
     , par.form_left_prompt prompt
     , par.enabled_flag
     , par.required_flag
     , par.display_flag
FROM   fnd_concurrent_programs fcp
     , fnd_concurrent_programs_tl fcpl
     , fnd_descr_flex_col_usage_vl par
WHERE  fcp.concurrent_program_id = fcpl.concurrent_program_id
AND    fcpl.user_concurrent_program_name = &conc_prg_name
AND    fcpl.LANGUAGE = 'US'
AND    par.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name

Profile Value at any Level

Below is another handy query to get profile Values at all levels.
The query prompts for 2 parameters, the profile name and the level name that could be application/responsibility/username. Its a wild search so will retrieve data if any of parameter is passed a blank.

SELECT   distinct pot.user_profile_option_name Profile
     , DECODE (a.profile_option_value
             , '1', '1 (may be "Yes")'
             , '2', '2 (may be "No")'
             , a.profile_option_value
              ) Value
     , DECODE (a.level_id
             , 10001, 'Site'
             , 10002, 'Application'
             , 10003, 'Responsibility'
             , 10004, 'User'
             , '????'
              ) Level_identifier
     , DECODE (a.level_id
             , 10002, e.application_name
             , 10003, c.responsibility_name
             , 10004, d.user_name
             , '-'
              ) Level_Name
FROM     applsys.fnd_application_tl e
     , applsys.fnd_user d
     , applsys.fnd_responsibility_tl c
     , applsys.fnd_profile_option_values a
     , applsys.fnd_profile_options b
     , applsys.fnd_profile_options_tl pot
WHERE    1=1
AND      UPPER (pot.user_profile_option_name) LIKE UPPER ('%&v_profile%')
AND      pot.profile_option_name = b.profile_option_name
AND      b.application_id = a.application_id(+)
AND      b.profile_option_id = a.profile_option_id(+)
AND      a.level_value = c.responsibility_id(+)
AND      a.level_value = d.user_id(+)
AND      a.level_value = e.application_id(+)
AND      (   UPPER (e.application_name) LIKE UPPER ('%&appname_respname_username%')
        OR UPPER (c.responsibility_name) LIKE UPPER ('%&&appname_respname_username%')
        OR UPPER (d.user_name) LIKE UPPER ('%&&appname_respname_username%')
       )
ORDER BY Profile
     , Level_identifier
     , Level_name
     , Value