Script to find all users with access to a particular function

Dear Readers

Recently came across a requirement where the user wanted to get a listing of all the Oracle EBS users who had access via any assigned responsibility to a particular form function

In this case they were looking for those with "Sales Orders" access to the order entry form.

The below script can be useful to find all the needed details. I am inserting these details into a table because the list can be huge and dbms_output can overflow but if you have a smaller installation then you can simply print these details out as well

CREATE TABLE APPS.XXAS_ACCESS_DETAILS

(

  USER_NAME            VARCHAR2(100),

  USER_ID              NUMBER,

  USER_START_DATE      DATE,

  USER_END_DATE        DATE,

  RESPONSIBILITY_NAME  VARCHAR2(240),

  RESPONSIBILITY_ID    NUMBER,

  RESP_START_DATE      DATE,

  RESP_END_DATE        DATE,

  ASSGN_START_DATE     DATE,

  ASSGN_END_DATE       DATE,

  FUNCTION_ID          NUMBER,

  SUB_MENU_ID          NUMBER,

  MENU_ID              NUMBER,

  FUNCTION_NAME        VARCHAR2(240),

  SUB_MENU_NAME        VARCHAR2(240),

  MENU_NAME            VARCHAR2(240)

);

DECLARE

   CURSOR lcur_user

   IS

      SELECT   *

      FROM     fnd_user;

   CURSOR lcur_resp(p_user_id NUMBER)

   IS

      SELECT   fr.responsibility_name,

               fr.responsibility_id,

               fr.menu_id,

               fr.start_date resp_start_date,

               fr.end_date resp_end_date,

               frgu.start_date assgn_start_date,

               frgu.end_date assgn_end_date

      from     fnd_user_resp_groups frgu,

               fnd_responsibility_vl fr

      where    frgu.user_id = p_user_id

      and      frgu.responsibility_id = fr.responsibility_id;

   CURSOR lcur_menu(p_menu_id NUMBER, p_resp_id NUMBER, p_func_id NUMBER)

   IS

      SELECT   *

      FROM

      (

               SELECT   *

               FROM

               (

                        SELECT   menu_id,

                                 sub_menu_id,

                                 function_id,

                                 prompt menu_prompt,

                                 entry_sequence,

                                 level menu_level,

                                 'N' exclude_flag,

                                 'N' display_flag,

                                 NULL sort_order,

                                 'N' read_only_flag

                        FROM     fnd_menu_entries_vl fme

                        WHERE    prompt IS NOT NULL

                        CONNECT BY PRIOR sub_menu_id = menu_id

                        START WITH menu_id = p_menu_id

                        AND      menu_id = p_menu_id

                        AND      prompt IS NOT NULL

                        AND      grant_flag = 'Y'

                        ORDER BY entry_sequence

               ) a

               CONNECT BY PRIOR sub_menu_id = menu_id

               START WITH menu_id = p_menu_id

               AND   menu_id = p_menu_id

               AND   menu_prompt IS NOT NULL

               and   not exists

               (

                     select   1

                     from     fnd_resp_functions frf

                     where    frf.responsibility_id = p_resp_id

                     and      frf.rule_type = 'M'

                     and (frf.action_id = a.menu_id or frf.action_id = a.sub_menu_id)

                     union

                     select   1

                     from     fnd_resp_functions frf

                     where    frf.responsibility_id = p_resp_id

                     and      frf.rule_type = 'F'

                     and      frf.action_id = a.function_id

               )

      )

      WHERE    function_id = p_func_id;

   lv_function_name  VARCHAR2(240) := 'Sales Orders';

   lv_function_id    NUMBER;

   lv_sub_menu_name  VARCHAR2(240);

   lv_menu_name      VARCHAR2(240);

BEGIN

   SELECT   function_id

   INTO     lv_function_id

   FROM     fnd_form_functions_vl

   WHERE    user_function_name = lv_function_name;

   FOR lrec_user IN lcur_user LOOP

      FOR lrec_resp IN lcur_resp(lrec_user.user_id) LOOP

         FOR lrec_menu IN lcur_menu(lrec_resp.menu_id, lrec_resp.responsibility_id, lv_function_id) LOOP

            IF lrec_menu.sub_menu_id IS NOT NULL THEN

               SELECT   menu_name

               INTO     lv_sub_menu_name

               FROM     fnd_menus_vl

               WHERE    menu_id = lrec_menu.sub_menu_id;

            END IF;

            IF lrec_menu.menu_id IS NOT NULL THEN

               SELECT   menu_name

               INTO     lv_menu_name

               FROM     fnd_menus_vl

               WHERE    menu_id = lrec_menu.menu_id;

            END IF;

            INSERT INTO xxas_access_details

            (

               user_name,

               user_id,

               user_start_date,

               user_end_date,

               responsibility_name,

               responsibility_id,

               resp_start_date,

               resp_end_date,

               assgn_start_date,

               assgn_end_date,

               function_id,

               sub_menu_id,

               menu_id,

               function_name,

               sub_menu_name,

               menu_name

            )

            VALUES

            (

               lrec_user.user_name,

               lrec_user.user_id,

               lrec_user.start_date,

               lrec_user.end_date,

               lrec_resp.responsibility_name,

               lrec_resp.responsibility_id,

               lrec_resp.resp_start_date,

               lrec_resp.resp_end_date,

               lrec_resp.assgn_start_date,

               lrec_resp.assgn_end_date,

               lrec_menu.function_id,

               lrec_menu.sub_menu_id,

               lrec_menu.menu_id,

               lv_function_name,

               lv_sub_menu_name,

               lv_menu_name

            );

         END LOOP;

      END LOOP;

   END LOOP;

END;

Hope this is useful

Cheers
A