Way to success...

--"Running Away From Any PROBLEM Only Increases The DISTANCE From The SOLUTION"--.....--"Your Thoughts Create Your FUTURE"--.....--"EXCELLENCE is not ACT but a HABIT"--.....--"EXPECT nothing and APPRECIATE everything"--.....

Tuesday, December 22, 2015

Query to get menu function hierarchy for the given responsibility 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(+);

1 comment:

  1. nice query...
    expecting above query including user and responsibility ...

    ReplyDelete