1 Reply Latest reply on Nov 26, 2019 4:30 PM by hal9000akilroy

    Query to find list of resp, menu, sub menu attached to user

    ORCL_novice

      Hi all,

       

      Can anyone please share a query to find list of responsibilities, Menus, Sub Menus and form functions attached to FND users?

       

      I want to get list of form functions attached to sub menus also iteratively. I tried with Connect by clause...somehow I fail to put it in right way.

       

      Thanks in advance.

        • 1. Re: Query to find list of resp, menu, sub menu attached to user
          hal9000akilroy

          here some query, have fun

           

          ######################################

          /*

          begin

            apps.FND_GLOBAL.set_nls_context (p_nls_language => 'CANADIAN FRENCH', p_nls_date_format => null, p_nls_date_language => null, p_nls_numeric_characters => '.,', p_nls_sort => null, p_nls_territory=> null);

          end;

          */

           

          SELECT distinct

            a.user_name,

            b.responsibility_name,

            papf.last_name || ', ' || papf.first_name employe,

            papf.employee_number ,

            (SELECT NAME FROM hr_all_organization_units haou WHERE haou.organization_id = paaf.organization_id) CR ,

            (SELECT town_or_city FROM hr_locations_all WHERE location_id = paaf.location_id) LOCATION

          FROM fnd_user a,

            fnd_responsibility_tl b,

            fnd_responsibility fr,

            fnd_user_resp_groups_direct c,

            fnd_application_tl fat,

            per_all_people_f papf,

            per_all_assignments_f paaf

          WHERE a.user_id    = c.user_id

          AND a.employee_id  = papf.person_id

          AND papf.person_id = paaf.person_id

          AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date

          AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date

          AND fat.application_id   = c.responsibility_application_id

          AND fr.responsibility_id = b.responsibility_id

          AND fr.end_date         IS NULL

          AND (c.end_date         IS NULL

          OR c.end_date            > SYSDATE) -- Active User Responsibilities

          AND (a.end_date         IS NULL

          OR a.end_date            > SYSDATE) -- Active Users

          AND b.responsibility_id = c.responsibility_id

          ORDER BY a.user_name, b.responsibility_name;

           

          SELECT connect_by_root responsibility_name resp

               , user_menu_name

               , prompt

               , Sub_Menu_Name

               , user_function_name

               , function_type

               , form_name

               , SYS_CONNECT_BY_PATH( decode( prompt,null,

                                                decode(sub_menu_name,null,'(Hidden User Function ->'|| user_function_name|| ')'

                                                                         ,'(Hidden Sub Menu ->'     || sub_menu_name     || ')'

                                                      ),

                                              prompt

                                            )

                                    , ' --> '

                                    ) Navigation_Path

               , ROWNUM seq

               , menu.menu_id

               , LEVEL lvl

               , entry_sequence

            FROM (SELECT  fm.menu_id

                       , fme.sub_menu_id

                       , (SELECT user_menu_name

                            FROM apps.fnd_menus_vl

                           WHERE menu_id = fme.sub_menu_id

                         ) Sub_Menu_Name

                       , fme.entry_sequence

                       , fm.user_menu_name

                       , fme.function_id

                       , fme.prompt

                       , func.user_function_name

                       , func.type function_type

                       , DECODE(func.type, 'FORM', frm.form_name

                                         , 'JSP' , WEB_HTML_CALL

                                         , 'WWW' , WEB_HTML_CALL

                                         , NULL

                               ) Form_name

                    FROM apps.fnd_menu_entries_vl   fme   

                       , apps.fnd_menus_vl          fm

                       , apps.fnd_form_functions_vl func

                       , applsys.fnd_form           frm

           

           

                   WHERE fm.menu_id      = fme.menu_id

                     AND fme.menu_id     <> NVL(fme.sub_menu_id,-1)

                     AND fme.function_id = func.function_id

                     AND func.form_id    = frm.form_id     

                 ) menu

                , (select * from apps.fnd_responsibility_vl where end_date is null) resp

          WHERE resp.menu_id(+) =  menu.menu_id

             START WITH responsibility_name like '%'

          CONNECT BY PRIOR sub_menu_id = menu.menu_id

             ORDER SIBLINGS BY entry_sequence;

            

          select resp.responsibility_name,

          grp.REQUEST_GROUP_NAME,

          grp.application_name,

          grp.REQUEST_GROUP_CODE,

          grp.DESCRIPTION,

          grp.TYPE_REQUETE,

          grp.user_concurrent_program_name,

          grp.conc_application_name,

          grp.Type

          from (select r.* from apps.fnd_responsibility_vl r where r.end_date is null) resp,

          (SELECT  

            RG.REQUEST_GROUP_NAME,

            APPL_RG.application_name,

            RG.REQUEST_GROUP_CODE,

            RG.DESCRIPTION,

            DECODE(D.REQUEST_UNIT_TYPE,'P','Programme',D.REQUEST_UNIT_TYPE) TYPE_REQUETE,

            p.user_concurrent_program_name,

            APPL_P.application_name conc_application_name,

            decode(substr(p.user_concurrent_program_name,1,2),'RQ','Non standard','Standard') Type

            ,rg.application_id,rg.request_group_id 

          FROM FND_REQUEST_GROUP_UNITS D,

               FND_CONCURRENT_PROGRAMS_TL p,

               FND_REQUEST_GROUPS RG,

               FND_APPLICATION_TL APPL_RG,

               FND_APPLICATION_TL APPL_P

          WHERE D.request_unit_id    = p.concurrent_program_id

          AND D.REQUEST_GROUP_ID   = RG.REQUEST_GROUP_ID

          AND p.language           = 'FRC'

          AND REQUEST_GROUP_NAME  LIKE '%'

          and request_group_name not in ('RQ_TEST_RAPPORT')

          and RG.APPLICATION_ID = APPL_RG.APPLICATION_ID

          and APPL_RG.SOURCE_LANG = 'FRC'

          and P.APPLICATION_ID = APPL_P.APPLICATION_ID

          and P.SOURCE_LANG = 'FRC'

          union

          SELECT

            RG.REQUEST_GROUP_NAME,

            APPL.application_name,

            RG.REQUEST_GROUP_CODE,

            RG.DESCRIPTION,

            DECODE(DET.REQUEST_UNIT_TYPE,'S','Jeu',DET.REQUEST_UNIT_TYPE) TYPE_REQUETE,

            s.USER_REQUEST_SET_NAME,

            APPL_DET.application_name conc_application_name,

            decode(substr(s.USER_REQUEST_SET_NAME,1,2),'RQ','Non standard','Standard') Type

            ,rg.application_id,rg.request_group_id

          FROM FND_REQUEST_GROUP_UNITS DET,

               FND_APPLICATION_TL APPL_DET,

               FND_APPLICATION_TL APPL,

               FND_REQUEST_GROUPS RG,

               FND_REQUEST_SETS_VL S

          WHERE (DET.REQUEST_UNIT_TYPE='S')

          and  APPL_DET.APPLICATION_ID = DET.UNIT_APPLICATION_ID

          and  APPL_DET.SOURCE_LANG    = 'FRC'

          and  APPL.APPLICATION_ID     = DET.APPLICATION_ID

          and  APPL.SOURCE_LANG        = 'FRC'

          and  RG.APPLICATION_ID       = DET.APPLICATION_ID

          and  RG.REQUEST_GROUP_ID     = DET.REQUEST_GROUP_ID

          and  S.REQUEST_SET_ID        = DET.REQUEST_UNIT_ID

          and  s.APPLICATION_ID        = DET.UNIT_APPLICATION_ID

          and  request_group_name like '%') grp

          where resp.responsibility_name like '%'

          and   resp.group_application_id = grp.application_id

          and   resp.request_group_id = grp.request_group_id

          order by resp.responsibility_name, grp.user_concurrent_program_name;

           

          ######################################