Query to get All Responsibility profile option

SELECT frt.responsibility_name,
        DECODE(fv.level_id,  10001, 'Site',  10002, 'Application',  10003, 'Responsibility',  10004, 'User',  NULL) profile_level,
        fr.responsibility_key,
        frt.application_id resp_appl_id,
        frt.responsibility_id resp_id,
        b.profile_option_id,
        fv.profile_option_value,
        t.profile_option_name,
        t.user_profile_option_name,
        b.sql_validation
  FROM fnd_profile_options_tl     t,
       fnd_profile_options        b,
       fnd_profile_option_values  fv,
       fnd_responsibility_tl      frt,
       fnd_responsibility         fr
 WHERE     b.profile_option_name = t.profile_option_name
       AND t.language = USERENV('LANG')
       AND frt.language = USERENV('LANG')
       AND b.profile_option_id = fv.profile_option_id
       --AND fv.level_id = 10003                                  --Resp Level
       --AND fv.level_value_application_id = 660                 --resp_appl_id
       --AND fv.level_value = 53387                               --resp_id
       AND frt.application_id = fv.level_value_application_id
       AND frt.responsibility_id = fv.level_value
       AND frt.application_id = fr.application_id
       AND frt.responsibility_id = fr.responsibility_id
       AND t.profile_option_name LIKE 'XX%'
       AND frt.RESPONSIBILITY_NAME like '%Order%' ;

Query to get Value sets Definition

This query helps to extract value set declaration:

SELECT REPLACE(ffv.flex_value_set_name, '&', ';')"Value Set Name",
        REPLACE(ffv.description, '&', ';')        "Value Set Description",
        val_type.meaning                          "Validation Type",
        (SELECT application_name
           FROM apps.fnd_application_tl
          WHERE application_id = ffvt.table_application_id AND language = 'US')
            "Table Application Name",
        ffvt.value_column_name                    "Display Value",
        ffvt.id_column_name                       "ID Value",
        ffvt.application_table_name               "Table Name",
        xx_get_long_text(ffvt.ROWID)           "Additional Information"
  FROM apps.fnd_flex_value_sets ffv, apps.fnd_flex_validation_tables ffvt, apps.fnd_lookups val_type
 WHERE     val_type.lookup_type(+) = 'SEG_VAL_TYPES'
       AND val_type.lookup_code(+) = ffv.validation_type
       AND ffv.flex_value_set_id = ffvt.flex_value_set_id(+)
       AND ffv.flex_value_set_name LIKE 'XX%'

Sample Result:

The function "xx_get_long_text" will convert LONG data type to VARCHAR2:

CREATE OR REPLACE FUNCTION xx_get_long_text(rowid_of_long IN ROWID)
    RETURN VARCHAR2
IS
    long_to_varchar   VARCHAR(32767);
BEGIN
    SELECT  ffvt.additional_where_clause
      INTO long_to_varchar
      FROM  apps.fnd_flex_validation_tables   ffvt
     WHERE ROWID = rowid_of_long;

    RETURN long_to_varchar;
EXCEPTION
    WHEN OTHERS
    THEN
        RETURN 'Error occurred on rowid: ' || rowid_of_long;
END;

Query to get all personalized form

 SELECT DISTINCT ffcr.form_name,
                  ffcr.function_name,
                  func.user_function_name,
                  fat.application_name
    FROM fnd_form_custom_rules ffcr,
         fnd_form_vl          frm,
         fnd_application_tl   fat,
         fnd_form_functions_vl func
   WHERE     1 = 1
         AND frm.form_name = ffcr.form_name
         AND fat.application_id = frm.application_id
         AND fat.language = 'US'
         AND func.form_id = frm.form_id
         AND func.function_name = ffcr.function_name
ORDER BY fat.application_name, ffcr.form_name

Query to get Scheduled Concurrent

 SELECT r.request_id,
         p.user_concurrent_program_name || NVL2(r.description, ' (' || r.description || ')', NULL)       Conc_prog,
         s.user_name                                                                                     REQUESTOR,
         r.argument_text                                                                                 arguments,
         r.requested_start_date                                                                          next_run,
         r.last_update_date                                                                              LAST_RUN,
         r.hold_flag                                                                                     on_hold,
         r.increment_dates,
         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
             AS 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,
         fnd_user                  s,
         (WITH date_schedules
               AS (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' AND INSTR(SUBSTR(c.class_info, 1, 31), '1') > 0)
                    WHERE s IS NOT NULL)
              SELECT release_class_id, SUBSTR(MAX(SYS_CONNECT_BY_PATH(s, ' ')), 2) dates
                FROM date_schedules
          START WITH a = 1
          CONNECT BY NOCYCLE PRIOR a = 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.language = 'US'
         AND dates.release_class_id(+) = r.release_class_id
         AND r.requested_by = s.user_id

Query to get All Users Responsibilities

  SELECT fu.user_name "User Name",
         (SELECT full_name
            FROM per_all_people_f
           WHERE     SYSDATE BETWEEN effective_start_date
                                 AND effective_end_date
                 AND employee_id = person_id)
            "Employee Name",
         fu.START_DATE "User Starting Date",
         fu.END_DATE "User End Date",
         frt.responsibility_name "Responsibility Name",
         furg.start_date "Start Date",
         furg.end_date "End Date",
         fr.responsibility_key "Responsibility Key",
         fa.application_short_name "Application Short Name",
         fat.application_name
    FROM fnd_user_resp_groups_direct furg,
         applsys.fnd_user fu,
         applsys.fnd_responsibility_tl frt,
         applsys.fnd_responsibility fr,
         applsys.fnd_application_tl fat,
         applsys.fnd_application fa
   WHERE     furg.user_id = fu.user_id
         AND furg.responsibility_id = frt.responsibility_id
         AND fr.responsibility_id = frt.responsibility_id
         AND fa.application_id = fat.application_id
         AND fr.application_id = fat.application_id
         AND frt.language = USERENV ('LANG')
         AND fat.language = USERENV ('LANG')
ORDER BY 1

Changing oracle user password FND_USER_PKG.CHANGEPASSWORD

DECLARE
   v_user_name      VARCHAR2 (30) := UPPER ('&user_name');
   v_new_password   VARCHAR2 (30) := '&new_password';
   v_status         BOOLEAN;
BEGIN
   v_status :=
      fnd_user_pkg.ChangePassword (username      => v_user_name,
                                   newpassword   => v_new_password);

   IF v_status = TRUE
   THEN
      DBMS_OUTPUT.put_line (
         'The password reset successfully for the User:' || v_user_name);
      COMMIT;
   ELSE
      DBMS_OUTPUT.put_line (
            'Unable to reset password due to'
         || SQLCODE
         || ' '
         || SUBSTR (SQLERRM, 1, 100));
      ROLLBACK;
   END IF;
END;

Query to get all Org attached to responsibility

SELECT responsibility_name,
         (SELECT name
            FROM hr_all_organization_units
           WHERE organization_id = fpov.profile_option_value)
            operating_unit
    FROM applsys.fnd_profile_option_values fpov,
         applsys.fnd_profile_options fpo,
         applsys.fnd_profile_options_tl fpot,
         applsys.fnd_responsibility_tl fr
   WHERE     1 = 1
         AND fpo.profile_option_name = fpot.profile_option_name
         AND fpo.profile_option_id = fpov.profile_option_id
         AND fr.responsibility_id(+) = fpov.level_value
         -- AND fr.responsibility_id = :$PROFILES$.RESP_ID
         AND fpot.profile_option_name = 'ORG_ID'
         -- and fpov.profile_option_value='11'
         --AND fr.responsibility_name LIKE '%Payables%Manager%'
         AND fr.language = 'US'
         AND fpot.language = 'US'
ORDER BY 2