Monday, September 10, 2007

Query: Concurrent Programs by User and Responsibility

Sometimes its a tough job putting all the entities together in Oracle Applications. The following query is a combination SQL behind the "Reponsibilities" for a given user on the Users form, and the list of values for the Request Name (Concurrent Program) on the "Submit Requests" form, for version 11.5.10.2 (11i.10.2). This can be useful to provide an "Audit" of what concurrent requests/programs a given Oracle eBusiness Suite user or Responsibility has access to.

select distinct
       fu.user_name
,      r.responsibility_name
,      p.user_concurrent_program_name
,      a.application_name
,      a.application_short_name
,      p.concurrent_program_name
,      p.concurrent_program_id
,      p.application_id program_application_id
from fnd_concurrent_programs_vl p
,    fnd_application_vl a
,    fnd_request_group_units u
,    fnd_responsibility_vl r
,    fnd_user fu
,   (select user_id
     ,      responsibility_id
     ,      responsibility_application_id -- ,start_date,end_date
     from   fnd_user_resp_groups_indirect
     where (responsibility_id,responsibility_application_id)
        in (select responsibility_id, application_id
            from   fnd_responsibility
            where (version = '4' or 
                   version = 'W' or
                   version = 'M' or
                   version = 'H')
           )
     and nvl(start_date,sysdate-1) <= sysdate
     and nvl(end_date,sysdate+1) > sysdate
     union
     select user_id
     ,      responsibility_id
     ,      responsibility_application_id
     from   fnd_user_resp_groups_direct
     where (responsibility_id,responsibility_application_id)
        in (select responsibility_id, application_id
            from   fnd_responsibility
            where (version = '4' or
                   version = 'W' or
                   version = 'M' or
                   version = 'H'))
     and nvl(start_date,sysdate-1) <= sysdate
     and nvl(end_date,sysdate+1) > sysdate
     ) user_resps
where p.srs_flag in ('Y', 'Q')
and   p.enabled_flag = 'Y'
and   request_set_flag = 'N'
and (
      (a.application_id = u.unit_application_id
       and u.application_id = r.group_application_id
       and u.request_group_id = r.request_group_id
       and u.request_unit_type = 'A')
   or (p.application_id = u.unit_application_id
       and p.concurrent_program_id = u.request_unit_id
       and u.application_id = r.group_application_id
       and u.request_group_id = r.request_group_id
       and u.request_unit_type = 'P'))
and p.application_id = a.application_id
and user_resps.user_id = fu.user_id
and user_resps.responsibility_id = r.responsibility_id
and user_resps.responsibility_application_id = r.application_id
order by 1,2,4;
PS. This was my response to a question posted on Oracle Forums, a very handy place for information, and if you're searching for something, worthwhile doing a "site:forums.oracle.com xxxx" on Google!

No comments: