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

Return to : Oracle Database, SQL, Application, Programming Tips