Version 2.0 :
SET heading OFF

COLUMN "User Name" FORMAT A8
COLUMN "Responsibility" FORMAT A25
COLUMN "Start" FORMAT A10
COLUMN "Application" FORMAT A40 TRUNCATE

BREAK ON "User Name" ON "Start" ON "Application"

ACCEPT v_name PROMPT 'User Name or substring (default "ALL"): '
ACCEPT v_resp PROMPT 'Responsibility or substring (default "ALL"): '

SET linesize 52
SELECT 'You can use this to see "Who does What" in ' || name ||
'. Queried on ' || TO_CHAR( sysdate, 'dd-Mon-yyyy') ||
', looking for User Name like ''%' || UPPER( '&&v_name') ||
'%'', and / or Responsibility like ''%' || UPPER( '&&v_resp') ||
'%.'''
FROM v$database,
dual
/

SET heading ON linesize 86

SELECT SUBSTR( user1.user_name, 1, 20) "User Name"
, SUBSTR( resp.responsibility_name, 1, 25) "Responsibility"
, user_resp.start_date "Start"
, appl.application_name "Application"
FROM applsys.fnd_user user1
, applsys.fnd_responsibility resp
, applsys.fnd_user_responsibility user_resp
, applsys.fnd_application appl
WHERE user1.user_id = user_resp.user_id
AND user_resp.responsibility_id = resp.responsibility_id
AND resp.application_id = appl.application_id
AND user_resp.end_date IS NULL
AND UPPER( user1.user_name) LIKE UPPER( '%&&v_name%')
AND UPPER( resp.responsibility_name) LIKE UPPER( '%&&v_resp%')
ORDER by "User Name", "Responsibility"
/

CLEAR COLUMN

Version 1.0
SELECT substr(user1.user_name,1,20),
       substr(resp.responsibility_name,1,25),
       user_resp.start_date,
       appl.application_name
FROM  fnd_user user1,
      fnd_responsibility resp,
      fnd_user_responsibility user_respr,
      fnd_application a1
WHERE user1.user_id=user_resp.user_id
  AND user_resp.responsibility_id = resp.responsibility_id
  AND resp.application_id=appl.application_id
  AND user_resp.end_date is null
  AND user1.user_name like '&username'
ORDER by user_name

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips