5 Replies Latest reply on Apr 7, 2015 1:34 PM by Kj - Kiran Jadhav

    Oracle EBS hierarchy query

    user599292

      Is there an easy way to create via SQL for R12 of EBS a hierarchy of all the various forms and menus attached to a responsibility. The MOS note 1334930.1 "sensitive administrative pages in Oracle EBS" can list which users have access to the so called forms, but claims such forms are only accessible from sysadmin type responsibilities and menus, so I wanted to see which forms are tied to which responsibilities via a query. The form names on their own are quite pointless.

        • 1. Re: Oracle EBS hierarchy query
          Kj - Kiran Jadhav

          Please find below query which will list Menu/Function Hierarchy by entering Reponsibility name

           

          SELECT /*+ALL_ROWS */

                 srno, b.entry_sequence, padding,

                 RPAD (' ', 4 * (padding - 1)) || prompt menuprompt, a.description,

                 b.menu_id, b.sub_menu_id, b.function_id, c.user_function_name

            FROM apps.fnd_menu_entries_tl a,

                 (SELECT     ROWNUM srno, LEVEL padding, menu_id, entry_sequence,

                             sub_menu_id, function_id

                        FROM apps.fnd_menu_entries

                  CONNECT BY menu_id = PRIOR sub_menu_id

                  START WITH menu_id IN (

                                SELECT /*+ALL_ROWS */

                                       menu_id

                                  FROM apps.fnd_responsibility_vl

                                 WHERE NVL (end_date, SYSDATE) > = SYSDATE

                                   AND responsibility_name = 'Application Developer')) b, --Enter Responsibility Name Here

                 fnd_form_functions_tl c

          WHERE a.menu_id = b.menu_id

             AND a.entry_sequence = b.entry_sequence

             AND a.LANGUAGE = USERENV ('LANG')

             --AND a.menu_id IN ('80825')

             AND b.function_id = c.function_id(+)

           

          -Kj

          • 2. Re: Oracle EBS hierarchy query
            Kj - Kiran Jadhav

            Also find below query which will list functions, responsibilities attached to the user's by entering the Function Name.

             

            SELECT DISTINCT u.user_name, rtl.responsibility_name, ff.function_name,

                            ffl.user_function_name

                       FROM fnd_compiled_menu_functions cmf,

                            fnd_form_functions ff,

                            fnd_form_functions_tl ffl,

                            fnd_responsibility r,

                            fnd_responsibility_tl rtl,

                            fnd_user_resp_groups urg,

                            fnd_user u

                      WHERE cmf.function_id = ff.function_id

                        AND r.menu_id = cmf.menu_id

                        AND urg.responsibility_id = r.responsibility_id

                        AND rtl.responsibility_id = r.responsibility_id

                        AND cmf.grant_flag = 'Y'

                        AND r.application_id = urg.responsibility_application_id

                        AND u.user_id = urg.user_id

            --and ff.function_id=19438

                        AND UPPER (ffl.user_function_name) LIKE

                                                          UPPER ('Receiving Transactions') -- Enter Function Name Here

                        AND ff.function_id = ffl.function_id

                        AND rtl.responsibility_name NOT IN (

                               SELECT fnd_responsibility_vl.responsibility_name

                                 FROM fnd_resp_functions, fnd_responsibility_vl

                                WHERE action_id = 1678

                                  AND fnd_responsibility_vl.responsibility_id =

                                                      fnd_resp_functions.responsibility_id)

                   ORDER BY u.user_name

             

            -Kj

            • 3. Re: Oracle EBS hierarchy query
              user599292

              Thanks - is there anyway to tweak the aforementioned queries to list the same information but for all responsibilities, as opposed to having to manually enter the responsibility or function name, i.e. a master report for all functions or responsibilities?

              • 4. Re: Oracle EBS hierarchy query
                user599292

                I was hoping the query would draw out "form names" as discussed in MOS note 1334930.1 (sensitive admin pages in Oracle EBS).

                • 5. Re: Oracle EBS hierarchy query
                  Kj - Kiran Jadhav

                  Yes, You can add the function code listed in Note 1334930.1 in the below query:

                   

                  SELECT DISTINCT u.user_name, rtl.responsibility_name, ff.function_name,

                                  ffl.user_function_name

                             FROM fnd_compiled_menu_functions cmf,

                                  fnd_form_functions ff,

                                  fnd_form_functions_tl ffl,

                                  fnd_responsibility r,

                                  fnd_responsibility_tl rtl,

                                  fnd_user_resp_groups urg,

                                  fnd_user u

                            WHERE cmf.function_id = ff.function_id

                              AND r.menu_id = cmf.menu_id

                              AND urg.responsibility_id = r.responsibility_id

                              AND rtl.responsibility_id = r.responsibility_id

                              AND cmf.grant_flag = 'Y'

                              AND r.application_id = urg.responsibility_application_id

                              AND u.user_id = urg.user_id

                  --and ff.function_id=19438

                             AND ff.function_name IN ('ALR_ALRALERT', 'ASO_DEFAULT_RULES') -- Add the required functions here

                              AND ff.function_id = ffl.function_id

                              AND rtl.responsibility_name NOT IN (

                                     SELECT fnd_responsibility_vl.responsibility_name

                                       FROM fnd_resp_functions, fnd_responsibility_vl

                                      WHERE action_id = 1678

                                        AND fnd_responsibility_vl.responsibility_id =

                                                            fnd_resp_functions.responsibility_id)

                         ORDER BY u.user_name

                   

                  -Kj