Sunday 29 December 2013

Responsibilities attached to a Function


Return resps with access to a particular function, any level down, not just top level
 enter function name - .e.g AR_ARXCWMAI_QIT

SELECT DISTINCT frt.responsibility_id
              , frt.responsibility_name
              , fr.responsibility_key
              , fa.application_short_name
           FROM applsys.fnd_responsibility fr
              , applsys.fnd_responsibility_tl frt
              , applsys.fnd_application fa
              , applsys.wf_local_user_roles wlur
          WHERE fa.application_id = fr.application_id
            AND fr.application_id = frt.application_id
            AND fr.responsibility_id = frt.responsibility_id
            AND fr.responsibility_id = wlur.role_orig_system_id
            AND wlur.role_orig_system = 'FND_RESP'
            AND SYSDATE BETWEEN fr.start_date AND NVL(fr.end_date
                                                  , SYSDATE + 1)
            AND SYSDATE BETWEEN wlur.start_date AND NVL(wlur.expiration_date, SYSDATE + 1)
            AND fr.menu_id IN(
                   SELECT     menu_id
                         FROM applsys.fnd_menu_entries fme
                   CONNECT BY PRIOR fme.menu_id = fme.sub_menu_id
                   START WITH fme.function_id =
                                     (SELECT function_id
                                        FROM applsys.fnd_form_functions fff
                                       WHERE fff.function_name = :function_name));

No comments:

Post a Comment