Basic SQL Queries used in Oracle Apps

Below query will fetch the data for Concurrent Program attached to which responsibility -

  SELECT DISTINCT fcpt.user_concurrent_program_name,
                             frt.responsibility_name,
                             frg.request_group_name,
                             frgu.request_unit_type,
                             frgu.request_unit_id
    FROM apps.fnd_Responsibility fr,
             apps.fnd_responsibility_tl frt,
             apps.fnd_request_groups frg,
             apps.fnd_request_group_units frgu,
             apps.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 frt.language = USERENV ('LANG')
         AND fcpt.language = USERENV ('LANG')
         AND fcpt.user_concurrent_program_name LIKE  '%<Concurrent Program Name>%'
ORDER BY 2;

Below query will fetch the data from which responsibility request was submitted:-

SELECT   c.request_id, 
                 frv.responsibility_name
  FROM    fnd_responsibility_vl frv, 
                 fnd_concurrent_requests fcr
 WHERE  fcr.request_id = <Request ID>
       AND  fcr.responsibility_application_id = frv.application_id
       AND  fcr.responsibility_id = frv.responsibility_id;

Below query will fetch the data which function is attached to which responsibilities:-

SELECT  fr.responsibility_name, 

                fm.menu_name, 

                ff.user_function_name

  FROM   apps.fnd_responsibility_vl fr,

                apps.fnd_menus fm,

                apps.fnd_compiled_menu_functions fmf,

                apps.fnd_Form_functions_vl ff

 WHERE  fr.menu_id = fm.menu_id

       AND  ff.function_id = fmf.function_id

       AND  fmf.menu_id = fm.menu_id

       AND  ff.user_function_name LIKE '%<Function Name>%';

Below query will fetch the data of Request group and it's concurrent programs:-

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  apps.fnd_request_groups rg,

               apps.fnd_request_group_units rgu,

               apps.fnd_concurrent_programs cp,

               apps.fnd_concurrent_programs_tl cpt,

               apps.fnd_application fa,

               apps.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 rg.request_group_name LIKE '%<Request Group Name>%';

Below Query for Extracting the data for Alert in Oracle Apps:-

SELECT  fav.application_short_name,
                aa.alert_name,
                aa.description alert_description,
                aa.start_date_active,
                aa.enabled_flag,
                aa.sql_statement_text,
                aav.description alert_action_type,
                aav.to_recipients,
                aav.cc_recipients,
                aav.bcc_recipients
  FROM   apps.alr_actions_v aav, 
               apps.alr_alerts aa, 
               apps.fnd_application_vl fav
 WHERE aav.alert_id = aa.alert_id
       AND aav.application_id = aa.application_id
       AND fav.application_id  = aa.application_id
       AND (fav.application_short_name LIKE '%<Application Short Name>%' 
               OR  aa.alert_name LIKE '%<Alert Name>%');


Below Query will Find all the fields in a given Descriptive Flex Field(DFF):-

  SELECT   ffv.descriptive_flexfield_name DFF_Name,
                    ffv.application_table_name Table_Name,
                    ffv.title Title,
                    ap.application_name Application,
                    att.column_seq_num SegmentNumber,
                    att.form_left_prompt SegmentName,
                    att.application_column_name,
                    fvs.flex_value_set_name ValueSet,
                    att.required_flag
    FROM     apps.fnd_descriptive_flexs_vl ffv,
                    apps.fnd_descr_flex_contexts_vl ffc,
                    apps.fnd_descr_flex_col_usage_vl att,
                    apps.fnd_flex_value_sets fvs,
                    apps.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  ffv.title IN ('<Descriptive FF Title>')
ORDER BY ffv.title;


Below Query will find User and it's attached responsibilities:-

SELECT   fu.user_name,
                 fr.responsibility_name,
                 furg.start_date,
                 furg.end_date
  FROM   apps.fnd_user_resp_groups_direct furg,
                apps.fnd_user fu,
                apps.fnd_responsibility_vl fr
 WHERE fu.user_name = UPPER ('<User Name>')
       AND furg.user_id = fu.user_id
       AND furg.responsibility_id = fr.responsibility_id;


Below Query will find Form Personalization and it's assigned responsibilities:-

SELECT rmcr.description Personalization,
              (CASE
                    WHEN fmcs.level_value =
                          (SELECT responsibility_id
                              FROM apps.fnd_responsibility_vl
                           WHERE responsibility_id = fmcs.level_value)
                    THEN
                          (SELECT responsibility_name
                              FROM apps.fnd_responsibility_vl
                           WHERE responsibility_id = fmcs.level_value)
              END)
               "Responsibility Name"
  FROM    apps.fnd_form_custom_scopes fmcs,
                apps.fnd_responsibility_vl frv,
                apps.fnd_form_custom_rules rmcr
 WHERE  level_id = 30
       AND frv.responsibility_id = fmcs.level_value
       AND rmcr.id = fmcs.rule_id

       AND rmcr.description LIKE '%<Personalization Name>%';


Below Query will find Concurrent Program's parameter:-

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  apps.fnd_concurrent_programs fcp,
               apps.fnd_concurrent_programs_tl fcpl,
               apps.fnd_descr_flex_col_usage_vl fdfcuv,
               apps.fnd_application_vl fav
 WHERE fcp.concurrent_program_id = fcpl.concurrent_program_id
       AND fcpl.user_concurrent_program_name = '<User Concurrent Program Name>'
       AND fav.application_id = fcp.application_id
       AND fcpl.language = 'US'
       AND fdfcuv.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name;


Below Query will find Responsibility level Profile Options:-

  SELECT NVL (g.responsibility_name, c.level_value) "Responsibility",
               b.user_profile_option_name "Profile",
               c.profile_option_value "Profile Value",
               (SELECT application_name
                  FROM apps.fnd_application_vl
                WHERE application_id = g.application_id)
                "Application"
    FROM  apps.fnd_profile_options a,
                 apps.fnd_profile_options_vl b,
                 apps.fnd_profile_option_values c,
                 apps.fnd_user d,
                 apps.fnd_user e,
                 apps.fnd_responsibility_vl g,
                 apps.fnd_application h
   WHERE 1 = 1
         AND a.profile_option_name = b.profile_option_name
         AND a.profile_option_id = c.profile_option_id
         AND a.application_id = c.application_id
         AND c.last_updated_by = d.user_id(+)
         AND c.level_value = e.user_id(+)
         AND c.level_value = g.responsibility_id(+)
         AND c.level_value = h.application_id(+)
         AND c.level_id = 10003
         AND g.responsibility_name LIKE '%<Responsibility Name>%'
ORDER BY 1;