SQL Query to list Active Responsibilities of a Active User

By | August 17, 2011
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
/**************************************************************************
 *PURPOSE: To list out Active Responsibilities assigned to a Active user  *
 *PARAMETERS: User Name                                                   *
 *AUTHOR: Shailender Thallam                                              *
 **************************************************************************/
SELECT fu.user_name,
       frv.responsibility_name,
       frv.responsibility_key,
       TO_CHAR (furgd.start_date, 'DD-MON-YYYY') "START_DATE",
       TO_CHAR (furgd.end_date, 'DD-MON-YYYY') "END_DATE"
FROM fnd_user fu,
  fnd_user_resp_groups_direct furgd,
  fnd_responsibility_vl frv
WHERE fu.user_id                     = furgd.user_id
AND furgd.responsibility_id          = frv.responsibility_id
AND furgd.end_date                  IS NULL
AND fu.user_name                     = '&user_name'
AND furgd.start_date                <= sysdate
AND NVL(furgd.end_date, sysdate + 1) > sysdate
AND fu.start_date                   <= sysdate
AND NVL(fu.end_date, sysdate + 1)    > sysdate
AND frv.start_date                  <= sysdate
AND NVL(frv.end_date, sysdate + 1)   > sysdate;

Points to note:
1) User must be active
2) Responsibility must be active
3) Assignment of Responsibility to a user must be active

WOW! Did you like this post? We'll send more interesting posts like SQL Query to list Active Responsibilities of a Active User to you!
Enter your Email Address:
  • Guest

    Thanks for the query

  • Madhu

    It helped me at right time. thanks