SCRIPT - APPS lookup USER by responsibility

PURPOSE:
      SCRIPT - APPS lookup USER by responsibility
      
      tested on 1158
      

EXAMPLE OUTPUT:

17:11:12 APPS@XYZZ:sisgene> @a_uresp_1158.sql

Session altered.

Enter value for resp_name_like: IBE_ADMIN%
old  16: and   frv.RESPONSIBILITY_name like '&resp_name_like'
new  16: and   frv.RESPONSIBILITY_name like 'IBE_ADMIN%'

USER_NAME  RESPONSIBILITY_KEY                  RESPONSIBILITY_NAME                      START_DATE  END_DATE    CREATION_DA
---------- ----------------------------------- ---------------------------------------- ----------- ----------- -----------
ALEFOXA    IBE_ADMINSTRATOR                    IBE_ADMINSTRATOR (XYZZ)                  2003-OCT-03             2003-OCT-03
LIGUTUA    IBE_ADMINSTRATOR                    IBE_ADMINSTRATOR (XYZZ)                  2003-FEB-07             2003-FEB-07
JOHEGOND   IBE_ADMINSTRATOR                    IBE_ADMINSTRATOR (XYZZ)                  2001-NOV-27             2001-NOV-27
IBE_ADMIN  IBE_ADMINSTRATOR                    IBE_ADMINSTRATOR (XYZZ)                  2001-NOV-25             2001-NOV-25
PRACEADJ   IBE_ADMINSTRATOR                    IBE_ADMINSTRATOR (XYZZ)                  2001-NOV-25 2002-FEB-13 2001-NOV-25
CHAETERA   IBE_ADMINSTRATOR                    IBE_ADMINSTRATOR (XYZZ)                  2002-SEP-20 2003-APR-11 2002-SEP-20
KARUPETS   IBE_ADMINSTRATOR                    IBE_ADMINSTRATOR (XYZZ)                  2003-JUL-22 2003-AUG-20 2003-JUL-23
MURTANE    IBE_ADMINSTRATOR                    IBE_ADMINSTRATOR (XYZZ)                  2003-DEC-29             2003-DEC-29

9 rows selected.

17:11:24 APPS@XYZZ:sisgene> l
  1  SELECT
  2      fu.user_name
  3  ,   frv.RESPONSIBILITY_KEY
  4  ,   frv.responsibility_name
  5  ,  furg.START_DATE
  6  ,   furg.END_DATE
  7  ,   furg.CREATION_DATE
  8  FROM FND_USER_RESP_GROUPS furg
  9  ,    fnd_user fu
 10  ,    fnd_responsibility_vl frv
 11  ,    fnd_responsibility fr
 12  WHERE furg.RESPONSIBILITY_ID = fr.RESPONSIBILITY_ID
 13  and   furg.RESPONSIBILITY_APPLICATION_ID = fr.APPLICATION_ID
 14  and   (fr.VERSION = '4' OR fr.VERSION = 'W' OR fr.VERSION = 'M')
 15  AND   furg.RESPONSIBILITY_ID = frv.RESPONSIBILITY_ID
 16  and   frv.RESPONSIBILITY_name like '&resp_name_like'
 17  and   furg.USER_ID=fu.user_id
 18  order by user_name,furg.responsibility_application_id,
 19* furg.responsibility_id, furg.security_group_id
17:11:37 APPS@XYZZ:sisgene>


----------------------------------- a_uresp_1158.sql -----------------------------------

alter session set nls_date_format='RRRR-MON-DD';

col USER_NAME           format a10
col RESPONSIBILITY_NAME format a40
col RESPONSIBILITY_KEY  format a35


set lines 132
set trims on


SELECT
    fu.user_name
,   frv.RESPONSIBILITY_KEY
,   frv.responsibility_name
,   furg.START_DATE
,   furg.END_DATE
,   furg.CREATION_DATE
FROM FND_USER_RESP_GROUPS furg
,    fnd_user fu
,    fnd_responsibility_vl frv
,    fnd_responsibility fr
WHERE furg.RESPONSIBILITY_ID = fr.RESPONSIBILITY_ID
and   furg.RESPONSIBILITY_APPLICATION_ID = fr.APPLICATION_ID
and   (fr.VERSION = '4' OR fr.VERSION = 'W' OR fr.VERSION = 'M')
AND   furg.RESPONSIBILITY_ID = frv.RESPONSIBILITY_ID
and   frv.RESPONSIBILITY_name like '&resp_name_like'
and   furg.USER_ID=fu.user_id
order by user_name,furg.responsibility_application_id,
furg.responsibility_id, furg.security_group_id
/

----------------------------------- a_uresp_1158.sql -----------------------------------