Useful queries in Oracle Apps

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

Find concurrent program & Request set attached to a responsibility

Query to find concurrent program

SELECT fna1.application_short_name "Application" ,

frg.request_group_name "Group" ,

frg.request_group_code "Code" ,frg.description "Description" ,

DECODE(frgu.request_unit_type, 'P', 'Program', 'A', 'Application', 'S', 'Set', 'F', 'Stage Function') "Request Type" ,

fcpl.user_concurrent_program_name "Request Name" ,

fna2.application_short_name "Request Application"

FROM fnd_request_groups frg,

 fnd_request_group_units frgu,

 fnd_concurrent_programs_tl fcpl,

 fnd_application fna1,

  fnd_application fna2

 WHERE frg.request_group_id = frgu.request_group_id

 AND frg.application_id = fna1.application_id

 AND frgu.application_id = fna2.application_id

 AND frg.application_id = frgu.application_id

 AND fcpl.concurrent_program_id = frgu.request_unit_id

 AND UPPER(fcpl.user_concurrent_program_name) LIKE '%' || UPPER(:program) || '%'

Query to find Request Set

select  frt.responsibility_name, frg.request_group_name,
          frgu.request_unit_type,frgu.request_unit_id,
          fcpt.user_request_set_name
From  apps.fnd_Responsibility fr, apps.fnd_responsibility_tl frt,
         apps.fnd_request_groups frg, apps.fnd_request_group_units frgu,
         apps.fnd_request_Sets_tl fcpt
where frt.responsibility_id = fr.responsibility_id
and    frg.request_group_id = fr.request_group_id
and    frgu.request_group_id = frg.request_group_id
and    fcpt.request_set_id = frgu.request_unit_id
and    fcpt.user_request_set_name = '&request_set_name'
order  by 1,2,3,4

Concurrent Program Attached to a Responsibility

Below query can be a handy one to find the list of responsibilities where the concurret program is attached.

select frg.request_group_name
      ,fcpt.user_concurrent_program_name CONC_PGM_NAME
  from apps.fnd_concurrent_programs_tl fcpt
      ,apps.fnd_request_group_units    frgu
      ,apps.fnd_request_groups         frg
      ,apps.fnd_responsibility         fr
      ,apps.fnd_responsibility_tl      frt
 where 1 = 1
   and frgu.request_unit_id = concurrent_program_id
   and frg.request_group_id = frgu.request_group_id
   and fr.request_group_id = frg.request_group_id
   and frt.responsibility_id = fr.responsibility_id
   and frt.responsibility_name = '&RESPONSIBILITY_NAME'
 order by fcpt.user_concurrent_program_name