Tuesday, 3 April 2018

Query to get User and Assigned Responsibilities

SELECT c.user_name
      ,a.responsibility_name responsibility
      ,b.start_date
      ,b.end_date
 FROM fnd_user_resp_groups_indirect b
     ,fnd_responsibility_tl a
     ,fnd_user c,
     (SELECT person_id
            ,date_start
            ,actual_termination_date
        FROM per_periods_of_service p1
       WHERE p1.business_group_id = 81
      UNION
      SELECT person_id
            ,date_start
            ,actual_termination_date
        FROM per_periods_of_placement p2
       WHERE p2.business_group_id = 81) d
 WHERE a.responsibility_id = b.responsibility_id
   AND b.user_id = c.user_id
   AND a.language='US'
   AND upper(a.responsibility_name) like 'AP%'
   AND d.person_id = c.employee_id
   AND NVL(d.actual_termination_date,sysdate+1) > sysdate
   AND NVL(b.end_date,sysdate+1) >sysdate  
UNION
SELECT c.user_name
      ,a.responsibility_name ROLE
      ,b.start_date
      ,b.end_date
  FROM fnd_user_resp_groups_direct b
      ,fnd_responsibility_tl a
      ,fnd_user c,
    (SELECT person_id
           ,date_start
           ,actual_termination_date
       FROM per_periods_of_service p1
      WHERE p1.business_group_id = 81
     UNION
     SELECT person_id
           ,date_start
           ,actual_termination_date
       FROM per_periods_of_placement p2
      WHERE p2.business_group_id = 81) d
WHERE a.responsibility_id = b.responsibility_id
  AND b.user_id = c.user_id
  AND a.language='US'
  AND upper(a.responsibility_name) like 'AP%'
  AND d.person_id = c.employee_id
  AND NVL(d.actual_termination_date,sysdate+1) > sysdate
  AND NVL(b.end_date,sysdate+1) >sysdate
ORDER BY start_date

No comments:

Post a Comment