Query To Find the Felxfield Segment details

SELECT   fa.application_name, fifst.id_flex_structure_name,
         segment_num "Number", segment_name "Name",
         form_left_prompt "Window Prompt",
         fifs.application_column_name "Column",
         ffvs.flex_value_set_name "Value Set Name",
         ffvs.description "Value Set Description",
         default_type "Default Type", DEFAULT_VALUE "Default Value",
         DECODE (fifs.required_flag,
                 'Y', 'Checked',
                 'N', 'Unchacked'
                ) "Required",
         DECODE (fifs.security_enabled_flag,
                 'Y', 'Checked',
                 'N', 'Unchacked'
                ) "Security Enabled",
         range_code "Range",
         DECODE (display_flag, 'Y', 'Checked', 'N', 'Unchacked') "Display",
         DECODE (enabled_flag, 'Y', 'Checked', 'N', 'Unchacked') "Enabled",
         DECODE (application_column_index_flag,
                 'Y', 'Checked',
                 'N', 'Unchacked'
                ) "Indexed",
         display_size "Display Size",
         maximum_description_len "Descirption Size",
         concatenation_description_len "Concatenated Description Size",
         form_above_prompt "List Of Value Prompt"
    FROM fnd_id_flex_segments fifs,
         fnd_id_flex_segments_tl fifsgt,
         fnd_id_flex_structures_tl fifst,
         fnd_flex_value_sets ffvs,
         fnd_application_tl fa
   WHERE fa.application_id = fifs.application_id
     AND fifs.id_flex_num = fifst.id_flex_num
     AND fifsgt.id_flex_num = fifs.id_flex_num
     AND fifsgt.id_flex_code = fifs.id_flex_code
     AND fifsgt.application_column_name = fifs.application_column_name
     AND fifst.id_flex_code = fifs.id_flex_code
     AND ffvs.flex_value_set_id = fifs.flex_value_set_id
   --AND fifst.id_flex_structure_name LIKE :flex_name
   --and fifst.id_flex_num = 53558
   --AND fifs.id_flex_code = 'GL#'
ORDER BY application_name,id_flex_structure_name;

Query To Find the Key Flexfield Details

SELECT fifs.id_flex_code,fat.application_name "Application", fif.id_flex_name "Flexfield Title",
       fifs.id_flex_structure_code "Structure Code",
       fifst.id_flex_structure_name "Structure Title",
       fifst.description "Structure Description",
       fifs.STRUCTURE_VIEW_NAME "Structure View Name"
       ,decode(fifs.FREEZE_FLEX_DEFINITION_FLAG,'Y','Checked','N','Unchacked') "Freeze Felxfield Definition",
       decode(ENABLED_FLAG,'Y','Checked','N','Unchacked') "Enabled",
       CONCATENATED_SEGMENT_DELIMITER "Segment Separator",
       decode(CROSS_SEGMENT_VALIDATION_FLAG,'Y','Checked','N','Unchacked') "Cross-Validation Segment",
       decode(FREEZE_STRUCTURED_HIER_FLAG,'Y','Checked','N','Unchacked') "Freeze Rollup Group" ,
       decode(DYNAMIC_INSERTS_ALLOWED_FLAG,'Y','Checked','N','Unchacked') "Allow Dyanamic Insert" ,
       fuc.User_name "Created By",
       ful.user_name "Last Updated By"      
  FROM fnd_id_flex_structures fifs,
       fnd_id_flexs fif,
       fnd_application_tl fat,
       fnd_id_flex_structures_tl fifst,
       fnd_user fuc,
       fnd_user ful
 WHERE fif.application_id = fat.application_id
   AND fifs.id_flex_code = fif.id_flex_code
   AND fifst.id_flex_num = fifs.id_flex_num
   AND fifst.id_flex_code = fifs.id_flex_code
   and fuc.user_id(+)=fifs.created_by
   and ful.user_id(+)=fifs.last_updated_by
   AND fifs.id_flex_code = 'GL#'
--   and fat.application_name  like 'G%'
--   AND id_flex_structure_name LIKE :Flex_name
order by application_name ; 

Query to Find The Structure/Defination of Value Set

SELECT ffvs.flex_value_set_name "Value Set Name", ffvs.description,
       flv.meaning "Validation Type", fls.meaning "Security Type",
              --security_enabled_flag,
       fll.meaning "List Type",
                               --longlist_flag,
                               flf.meaning "Format Type",
                                                         --format_type,
                                                         maximum_size,
       number_precision "Precision",
       DECODE (alphanumeric_allowed_flag,
               'Y', 'Unchacked',
               'N', 'Checked'
              ) "Number Only",
       DECODE (uppercase_only_flag,
               'Y', 'Checked',
               'N', 'Unchecked'
              ) "Upper Case Only",
       DECODE (numeric_mode_enabled_flag,
               'Y', 'Checked',
               'N', 'Unchacked'
              ) "Zero Fill number",
       minimum_value, maximum_value,
       fuc.user_name "Created By User",
       ful.user_name "Last Updated By User"
  FROM fnd_flex_value_sets ffvs,
       fnd_lookups flv,
       fnd_lookups fls,
       fnd_lookups fll,
       fnd_lookups flf,
       fnd_user fuc,
       Fnd_user ful
 WHERE flv.lookup_code = ffvs.validation_type
   AND fls.lookup_code = ffvs.security_enabled_flag
   AND fll.lookup_code = ffvs.longlist_flag
   AND flf.lookup_code = ffvs.format_type
   and fuc.user_id(+)=ffvs.created_by
   and ful.user_id(+)=ffvs.last_updated_by
   AND flf.lookup_type = 'FIELD_TYPE'
   AND fll.lookup_type = 'FLEX_VALUESET_LONGLIST_FLAG'
   AND fls.lookup_type = 'FLEX_VST_SECURITY_ENABLED_FLAG'
   AND flv.lookup_type = 'SEG_VAL_TYPES'
   --AND flex_value_set_name LIKE :values_name
   order by ffvs.last_update_date;

Query to find the Securing attributes for User

SELECT fu.user_name "User", atr.attribute_code "Attribute",
       fa.application_name "Application", number_value "Value",
       fuc.user_name "Added By", ful.user_name "Updated By"
  FROM ak_web_user_sec_attr_values atr,
       fnd_user fu,
       fnd_application_tl fa,
       fnd_user fuc,
       fnd_user ful
 WHERE fu.user_id = atr.web_user_id
   AND atr.attribute_application_id = fa.application_id
   AND atr.created_by = fuc.user_id
   AND atr.last_updated_by = ful.user_id
   AND fu.user_name = UPPER (:user_name);

Query To find Indirect Responsibilities assign to User

SELECT fu.user_name "User", responsibility_name "Responsibility",
       application_name "Application", furgd.description "Description",
       security_group_key "SECURITY GROUP",
       TO_CHAR (furgd.start_date, 'DD-Mon-YYYY') "Start Date",
       TO_CHAR (furgd.end_date, 'DD-Mon-YYYY') "End Date",
       fuc.user_name "Added By", ful.user_name "Updated By"
  FROM fnd_user fu,
       fnd_responsibility_tl fr,
       fnd_user_resp_groups_indirect furgd,
       fnd_application_tl fa,
       fnd_security_groups fsg,
       fnd_user fuc,
       fnd_user ful
 WHERE fr.responsibility_id = furgd.responsibility_id
   AND fu.user_id = furgd.user_id
   AND fa.application_id = furgd.responsibility_application_id
   AND fsg.security_group_id = furgd.security_group_id
   AND furgd.created_by = fuc.user_id
   AND furgd.created_by = ful.user_id
   AND fu.user_name = UPPER (:user_name);

Query to Find the Direct Responsibilities assign to User

SELECT fu.user_name "User", responsibility_name "Responsibility",
       application_name "Application", furgd.description "Description",
       security_group_key "SECURITY GROUP",
       TO_CHAR (furgd.start_date, 'DD-Mon-YYYY') "Start Date",
       TO_CHAR (furgd.end_date, 'DD-Mon-YYYY') "End Date",
       fuc.user_name "Added By", ful.user_name "Updated By"
  FROM fnd_user fu,
       fnd_responsibility_tl fr,
       fnd_user_resp_groups_direct furgd,
       fnd_application_tl fa,
       fnd_security_groups fsg,
       fnd_user fuc,
       fnd_user ful
 WHERE fr.responsibility_id = furgd.responsibility_id
   AND fu.user_id = furgd.user_id
   AND fa.application_id = furgd.responsibility_application_id
   AND fsg.security_group_id = furgd.security_group_id
   AND furgd.created_by = fuc.user_id
   AND furgd.created_by = ful.user_id
   AND fu.user_name = UPPER (:user_name);

Query to find the Concurrent Request Scheduling Details

SELECT   r.request_id,
            p.user_concurrent_program_name
         || CASE
               WHEN p.user_concurrent_program_name = 'Report Set'
                  THEN (SELECT ' - ' || s.user_request_set_name
                          FROM fnd_request_sets_tl s
                         WHERE s.application_id = r.argument1
                           AND s.request_set_id = r.argument2
                           AND LANGUAGE = 'US')
               WHEN p.user_concurrent_program_name = 'Check Periodic Alert'
                  THEN (SELECT ' - ' || a.alert_name
                          FROM alr_alerts a
                         WHERE a.application_id = r.argument1
                           AND a.alert_id = r.argument2
                           AND LANGUAGE = 'US')
            END concurrent_program_name,
         CASE
            WHEN p.user_concurrent_program_name != 'Report Set'
            AND p.user_concurrent_program_name != 'Check Periodic Alert'
               THEN r.argument_text
         END argument_text,
         r.requested_start_date next_run, r.hold_flag on_hold,
         DECODE (c.class_type,
                 'P', 'Periodic',
                 'S', 'On Specific Days',
                 'X', 'Advanced',
                 c.class_type
                ) schedule_type,
         CASE
            WHEN c.class_type = 'P'
               THEN    'Repeat every '
                    || SUBSTR (c.class_info, 1, INSTR (c.class_info, ':') - 1)
                    || DECODE (SUBSTR (c.class_info,
                                       INSTR (c.class_info, ':', 1, 1) + 1,
                                       1
                                      ),
                               'N', ' minutes',
                               'M', ' months',
                               'H', ' hours',
                               'D', ' days'
                              )
                    || DECODE (SUBSTR (c.class_info,
                                       INSTR (c.class_info, ':', 1, 2) + 1,
                                       1
                                      ),
                               'S', ' from the start of the prior run',
                               'C', ' from the completion of the prior run'
                              )
            WHEN c.class_type = 'S'
               THEN    NVL2 (dates.dates,
                             'Dates: ' || dates.dates || '. ',
                             NULL
                            )
                    || DECODE (SUBSTR (c.class_info, 32, 1),
                               '1', 'Last day of month '
                              )
                    || DECODE (SIGN (TO_NUMBER (SUBSTR (c.class_info, 33))),
                               '1', 'Days of week: '
                                || DECODE (SUBSTR (c.class_info, 33, 1),
                                           '1', 'Su '
                                          )
                                || DECODE (SUBSTR (c.class_info, 34, 1),
                                           '1', 'Mo '
                                          )
                                || DECODE (SUBSTR (c.class_info, 35, 1),
                                           '1', 'Tu '
                                          )
                                || DECODE (SUBSTR (c.class_info, 36, 1),
                                           '1', 'We '
                                          )
                                || DECODE (SUBSTR (c.class_info, 37, 1),
                                           '1', 'Th '
                                          )
                                || DECODE (SUBSTR (c.class_info, 38, 1),
                                           '1', 'Fr '
                                          )
                                || DECODE (SUBSTR (c.class_info, 39, 1),
                                           '1', 'Sa '
                                          )
                              )
         END schedule,
         c.date1 start_date, c.date2 end_date, c.class_info
    FROM fnd_concurrent_requests r,
         fnd_conc_release_classes c,
         fnd_concurrent_programs_tl p,
         (SELECT     release_class_id,
                     SUBSTR (MAX (SYS_CONNECT_BY_PATH (s, ' ')), 2) dates
                FROM (SELECT release_class_id,
                             RANK () OVER (PARTITION BY release_class_id ORDER BY s)
                                                                            a,
                             s
                        FROM (SELECT c.class_info, l, c.release_class_id,
                                     DECODE (SUBSTR (c.class_info, l, 1),
                                             '1', TO_CHAR (l)
                                            ) s
                                FROM (SELECT     LEVEL l
                                            FROM DUAL
                                      CONNECT BY LEVEL <= 31),
                                     fnd_conc_release_classes c
                               WHERE c.class_type = 'S')
                       WHERE s IS NOT NULL)
          CONNECT BY PRIOR (a || release_class_id) =
                                                   (a - 1) || release_class_id
          START WITH a = 1
            GROUP BY release_class_id) dates
   WHERE r.phase_code = 'P'
     AND c.application_id = r.release_class_app_id
     AND c.release_class_id = r.release_class_id
     AND NVL (c.date2, SYSDATE + 1) > SYSDATE
     AND c.class_type IS NOT NULL
     AND p.concurrent_program_id = r.concurrent_program_id
     AND p.application_id = r.program_application_id
     AND p.LANGUAGE = 'US'
     AND dates.release_class_id(+) = r.release_class_id
ORDER BY on_hold, next_run;

Query to List Profile options details

SELECT   b.user_profile_option_name "Long Name",
         a.profile_option_name "Short Name",
         DECODE (TO_CHAR (c.level_id),
                 '10001', 'Site',
                 '10002', 'Application',
                 '10003', 'Responsibility',
                 '10004', 'User',
                 'Unknown'
                ) "Level",
         DECODE (TO_CHAR (c.level_id),
                 '10001', 'Site',
                 '10002', NVL (h.application_short_name,
                               TO_CHAR (c.level_value)
                              ),
                 '10003', NVL (g.responsibility_name, TO_CHAR (c.level_value)),
                 '10004', NVL (e.user_name, TO_CHAR (c.level_value)),
                 'Unknown'
                ) "Level Value",
         c.profile_option_value "Profile Value",
         c.profile_option_id "Profile ID",
         TO_CHAR (c.last_update_date, 'DD-MON-YYYY HH24:MI') "Updated Date",
         NVL (d.user_name, TO_CHAR (c.last_updated_by)) "Updated By"
    FROM apps_fnd.fnd_profile_options a,
         apps_fnd.fnd_profile_options_vl b,
         apps_fnd.fnd_profile_option_values c,
         apps_fnd.fnd_user d,
         apps_fnd.fnd_user e,
         apps_fnd.fnd_responsibility_vl g,
         apps_fnd.fnd_application h
   WHERE
--a.application_id = nvl(401, a.application_id)
--and a.profile_option_name = nvl('INV', a.profile_option_name)
         b.user_profile_option_name LIKE 'EGO%'
     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(+)
ORDER BY b.user_profile_option_name,
         c.level_id,
         DECODE (TO_CHAR (c.level_id),
                 '10001', 'Site',
                 '10002', NVL (h.application_short_name,
                               TO_CHAR (c.level_value)
                              ),
                 '10003', NVL (g.responsibility_name, TO_CHAR (c.level_value)),
                 '10004', NVL (e.user_name, TO_CHAR (c.level_value)),
                 'Unknown'
                );