SQL Query to identify Users and Responsibilities

The following query will list active FND users and their active responsibility assignments:

SELECT fu.user_name
,      fr.responsibility_name
FROM   fnd_user fu
,      fnd_user_resp_groups_direct furgd
,      fnd_responsibility_vl fr
WHERE  TRUNC(SYSDATE) BETWEEN TRUNC(fu.start_date) AND NVL(TRUNC(fu.end_date),SYSDATE + 1)
AND    fu.user_id = furgd.user_id
AND    TRUNC(SYSDATE) BETWEEN TRUNC(furgd.start_date) AND NVL(TRUNC(furgd.end_date),SYSDATE + 1)
AND    fr.responsibility_id = furgd.responsibility_id
AND    TRUNC(SYSDATE) BETWEEN TRUNC(fr.start_date) AND NVL(TRUNC(fr.end_date),SYSDATE + 1)
ORDER BY 1,2
;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s