Query to find Sub menu, Menu, userS attached to a responsibility in Oracle EBS R12.2

 

SELECT

   fu.user_name, frtl.responsibility_name,  FFL.USER_FUNCTION_NAME,FF.FUNCTION_NAME,

   CASE

        WHEN frtl.responsibility_name LIKE '%KBNA%' THEN 'KBNA'

         WHEN frtl.responsibility_name LIKE '%JATM%' THEN 'JATM'

          WHEN frtl.responsibility_name LIKE '%JNA%' THEN 'JNA'

          WHEN frtl.responsibility_name LIKE '%KCI%' THEN 'KCI'

           WHEN frtl.responsibility_name LIKE '%JATX%' THEN 'JATX'

             WHEN frtl.responsibility_name LIKE '%JASC%' THEN 'JASC'

                WHEN frtl.responsibility_name LIKE '%KBCA%' THEN 'KBCA'

             WHEN frtl.responsibility_name LIKE '%JAMX%' THEN 'JAMX'     

                 WHEN frtl.responsibility_name LIKE '%KMX%' THEN 'KMX'     

     WHEN frtl.responsibility_name LIKE '%JANA%' THEN 'JANA'    

          WHEN frtl.responsibility_name LIKE '%JATV%' THEN 'JATV'    

        ELSE 'ALL OU'  

    END AS Converted_String,

    fmvc.menu_name main_menu_name,

    fmv.menu_name  sub_menu_name,

    fr.responsibility_id,

    fmvl.prompt,

    fmvl.description,

    fmvl.grant_flag,

    fmvl.menu_id,

    fmvl.sub_menu_id,

    fmvl.function_id,

    fmvl.last_update_date,

    fmvl.last_updated_by,

    fmvl.created_by,

    fmvl.creation_date,

    fmvl.last_update_login,

    fmvl.row_id,

    cmf.function_id,furg.user_id

FROM

    fnd_menu_entries_vl         fmvl, --PARENT MENU

    fnd_menus_vl                fmv,  --SUBMENU

    fnd_menus_vl                fmvc,

    fnd_responsibility          fr,

    fnd_responsibility_tl       frtl,

    fnd_compiled_menu_functions cmf, fnd_user_resp_groups        furg, fnd_user fu, fnd_form_functions          ff,

       fnd_form_functions_tl       ffl

WHERE

        1 = 1--( fmvl.menu_id = 86773 )

    AND fmv.menu_id (+) = fmvl.sub_menu_id

    AND fmvl.menu_id = fmvc.menu_id

    AND fr.menu_id = fmvl.menu_id

    AND frtl.responsibility_id = fr.responsibility_id

    AND frtl.language = userenv('LANG')

    AND cmf.menu_id = fmv.menu_id

    and furg.responsibility_id = fr.responsibility_id

    AND cmf.function_id = 1282

    and fu.user_id=furg.user_id

     AND cmf.function_id =  ff.function_id

           AND ff.function_id = ffl.function_id

       AND FFL.LANGUAGE=USERENV('LANG')

ORDER BY

 USER_NAME