Query to get DFF and Segment Values

SELECT ffv.descriptive_flexfield_name,
  ffv.application_table_name,
  ffv.title,
  ap.application_name ,
  ffc.descriptive_flex_context_code ,
  ffc.descriptive_flex_context_name,
  ffc.description ,
  ffc.enabled_flag ,
  att.column_seq_num ,
  att.form_left_prompt ,
  att.application_column_name ,
  fvs.flex_value_set_name ,
  att.display_flag ,
  att.enabled_flag ,
  att.required_flag
FROM fnd_descriptive_flexs_vl ffv,
  fnd_descr_flex_contexts_vl ffc,
  fnd_descr_flex_col_usage_vl att,
  fnd_flex_value_sets fvs,
  fnd_application_vl ap
WHERE ffv.descriptive_flexfield_name = att.descriptive_flexfield_name
AND ap.application_id                =ffv.application_id
AND ffv.descriptive_flexfield_name   = ffc.descriptive_flexfield_name
AND ffv.application_id               = ffc.application_id
AND ffc.descriptive_flex_context_code=att.descriptive_flex_context_code
AND fvs.flex_value_set_id            =att.flex_value_set_id
AND ap.application_name              = 'Order Management'
AND ffv.application_table_name       ='OE_ORDER_HEADERS_ALL';

Query to find out concurrent program details and its parameters

SELECT fcpl.user_concurrent_program_name,
  fcp.concurrent_program_name,
  fcp.concurrent_program_id,
  fav.application_short_name,
  fav.application_name,
  fav.application_id,
  fdfcuv.end_user_column_name,
  fdfcuv.form_left_prompt prompt ,
  fdfcuv.enabled_flag,
  fdfcuv.required_flag,
  fdfcuv.display_flag
FROM fnd_concurrent_programs fcp ,
  fnd_concurrent_programs_tl fcpl ,
  fnd_descr_flex_col_usage_vl fdfcuv ,
  fnd_application_vl fav
WHERE fcp.concurrent_program_id       = fcpl.concurrent_program_id
AND fcpl.user_concurrent_program_name = :conc_prg_name
AND fav.application_id                =fcp.application_id
AND fcpl.language                     = 'US'
AND fdfcuv.descriptive_flexfield_name = '$SRS$.'|| fcp.concurrent_program_name;

Query To Find Concurrent Program Attached To a Responsibility

SELECT fcpt.user_concurrent_program_name,
  frt.responsibility_name,
  frg.request_group_name
FROM fnd_Responsibility fr,
  fnd_responsibility_tl frt,
  fnd_request_groups frg,
  fnd_request_group_units frgu,
  fnd_concurrent_programs_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.concurrent_program_id        = frgu.request_unit_id
AND fcpt.user_concurrent_program_name = '&conc_program_name';

Query to list all the responsibilities attached to a user

SELECT fu.user_name,
  fr.responsibility_name,
  furg.start_date,
  furg.end_date
FROM fnd_user_resp_groups_direct furg,
  fnd_user fu,
  fnd_responsibility_tl fr
WHERE upper(fu.user_name)  =upper(:user_name)
AND furg.user_id           = fu.user_id
AND furg.responsibility_id = fr.responsibility_id
AND fr.language            = userenv('lang');

Query to find request group and application name for a concurrent program

SELECT cpt.user_concurrent_program_name "Concurrent Program Name",
  DECODE(rgu.request_unit_type, 'P', 'Program', 'S', 'Set', rgu.request_unit_type) "Unit Type",
  cp.concurrent_program_name "Concurrent Program Short Name",
  rg.application_id "Application ID",
  rg.request_group_name "Request Group Name",
  fat.application_name "Application Name",
  fa.application_short_name "Application Short Name",
  fa. basepath "Basepath"
FROM fnd_request_groups rg,
  fnd_request_group_units rgu,
  fnd_concurrent_programs cp,
  fnd_concurrent_programs_tl cpt,
  fnd_application fa,
  fnd_application_tl fat
WHERE rg.request_group_id    = rgu.request_group_id
AND rgu.request_unit_id      = cp.concurrent_program_id
AND cp.concurrent_program_id = cpt.concurrent_program_id
AND rg.application_id        = fat.application_id
AND fa.application_id        = fat.application_id
AND cpt.language             = USERENV('LANG')
AND fat.language             = USERENV('LANG')
AND cpt.user_concurrent_program_name LIKE 'Reservations Interface Manager';