Search This Blog

Wednesday 11 December 2013

List of Form Functions attached to Responsibility

List of Form Functions attached to Responsibility

SELECT   DISTINCT faa.application_name application,
                    rtl.responsibility_name,
                    ffl.user_function_name,
                    ff.function_name,
                    ffl.description,
                    ff.TYPE
    FROM   fnd_compiled_menu_functions cmf,
           fnd_form_functions ff,
           fnd_form_functions_tl ffl,
           fnd_responsibility r,
           fnd_responsibility_vl rtl,
           apps.fnd_application_all_view faa
   WHERE       cmf.function_id = ff.function_id
           AND r.menu_id = cmf.menu_id
           AND rtl.responsibility_id = r.responsibility_id
           AND cmf.grant_flag = 'Y'
           AND ff.function_id = ffl.function_id
           AND faa.application_id(+) = r.application_id
           AND UPPER (rtl.responsibility_name) LIKE '%TEST%'
           AND r.end_date IS NULL
           AND rtl.end_date IS NULL
ORDER BY   rtl.responsibility_name;

Thursday 7 November 2013

Responsibility wise Menu-Function Detail

SELECT lpad(' ',   6 *(LEVEL -1)) || menu_entry.entry_sequence sequence,
    lpad(' ',   6 *(LEVEL -1)) || ft.PROMPT Menu,
    lpad(' ', 6*(level -1)) || menu.user_menu_name Menu_Name,
    lpad(' ',   6 *(LEVEL -1)) || func.user_function_name "Function",
    func.description,
    menu.menu_id,
    func.function_id,
    menu_entry.grant_flag grant_flag,
    decode(menu_entry.sub_menu_id,   NULL,   'FUNCTION',   decode(menu_entry.function_id,   NULL,   'SUBMENU',   'BOTH')) type
FROM fnd_menu_entries menu_entry,
     fnd_menus_tl menu,
     fnd_form_functions_tl func,
     fnd_menu_entries_tl ft
WHERE menu_entry.sub_menu_id = menu.menu_id(+)
 AND menu_entry.function_id = func.function_id(+)
 AND grant_flag = 'Y'
 AND ft.menu_id = menu_entry.menu_id
 AND ft.entry_sequence = menu_entry.entry_sequence
 AND LEVEL < 4 START WITH menu_entry.menu_id = --$Sql_stmt$--67850--79510
 (SELECT b.menu_id
      FROM  fnd_responsibility b,
            fnd_responsibility_tl t
     WHERE b.responsibility_id = t.responsibility_id
     AND t.responsibility_name = '&Responsibility_Name')
CONNECT BY menu_entry.menu_id = PRIOR menu_entry.sub_menu_id
ORDER siblings BY menu_entry.entry_sequence;