Sysadmin

1.
System Administrator
====================

col USER_NAME for a15
col RESPONSIBILITY_NAME for a25
select a.user_name,c.RESPONSIBILITY_NAME,b.START_DATE,b.END_DATE
from fnd_user a,fnd_user_resp_groups b ,fnd_responsibility_vl c
where a.user_id=b.user_id and b.RESPONSIBILITY_ID=c.RESPONSIBILITY_ID and c.RESPONSIBILITY_NAME='System Administrator' and a.user_name!='SYSADMIN';

Application Administrator
=========================

col USER_NAME for a15
col RESPONSIBILITY_NAME for a25
select a.user_name,c.RESPONSIBILITY_NAME,b.START_DATE,b.END_DATE
from fnd_user a,fnd_user_resp_groups b ,fnd_responsibility_vl c
where a.user_id=b.user_id and b.RESPONSIBILITY_ID=c.RESPONSIBILITY_ID and c.RESPONSIBILITY_NAME='Application Administrator' and a.user_name!='SYSADMIN';

Application Developer
=====================

col USER_NAME for a15
col RESPONSIBILITY_NAME for a25
select a.user_name,c.RESPONSIBILITY_NAME,b.START_DATE,b.END_DATE
from fnd_user a,fnd_user_resp_groups b ,fnd_responsibility_vl c
where a.user_id=b.user_id and b.RESPONSIBILITY_ID=c.RESPONSIBILITY_ID and c.RESPONSIBILITY_NAME='Application Devolper' and a.user_name!='SYSADMIN';

2.To View Added Responsibilty

1. Run the request ‘Sync responsibility role data into the WF table’.
2. Run the request “Synchronize WF LOCAL tables”.
3. Run the request “Workflow Directory Services User/Role Validation” (with parameters 10000, yes,yes, yes).
4. Have the user log off and back on and verify that the responsibility appears.

3.Script to find who changed sysadmin password

SELECT substr(d.user_name,1,30) || '~'||substr(f.user_name,1,10)||'~'||substr(d.last_update_date,1,10) key,count(*) value
from apps.fnd_user d, apps.fnd_user f
WHERE
trunc(d.last_update_date) = trunc(sysdate)
and d.user_name='SYSADMIN'
and d.last_updated_by=f.user_id GROUP BY substr(d.user_name,1,30) || '~'||
substr(f.user_name,1,10)||'~'||substr(d.last_update_date,1,10);

4.Query to Check Particular Responsibility of a user:

SELECT fu.user_id, fu.user_name, fur.responsibility_id,fr.responsibility_name FROM fnd_user fu, fnd_user_resp_groups fur, fnd_responsibility_vl fr
WHERE fu.user_id = fur.user_id AND fr.application_id = fur.responsibility_application_id AND fr.responsibility_id = fur.responsibility_id
AND TRUNC (SYSDATE) BETWEEN TRUNC (fr.start_date) AND TRUNC (NVL ((fr.end_date - 1), SYSDATE)) AND TRUNC (SYSDATE) BETWEEN TRUNC (fur.start_date)
AND TRUNC (NVL ((fur.end_date - 1), SYSDATE)) and user_name like 'GUEST';

 --- for all user or for perticular user
-- AND fur.responsibility_application_id = 275 -- to check users for perticular responsibility
order by user_name

SELECT FURGA.USER_ID
, FU.USER_NAME
, FURGA.RESPONSIBILITY_ID
, FRTL.RESPONSIBILITY_NAME
, FURGA.RESPONSIBILITY_APPLICATION_ID
, FA.APPLICATION_SHORT_NAME
, FURGA.SECURITY_GROUP_ID
, FSG.SECURITY_GROUP_KEY
, FURGA.START_DATE
, FURGA.END_DATE
, FURGA.CREATED_BY
, FUCB.USER_NAME
, FURGA.CREATION_DATE
, FURGA.LAST_UPDATED_BY
, FULUB.USER_NAME
, FURGA.LAST_UPDATE_DATE
, FURGA.LAST_UPDATE_LOGIN
, FULUL.USER_NAME
FROM
FND_USER_RESP_GROUPS_ALL FURGA,
FND_USER FU,
FND_USER FUCB,
FND_USER FULUB,
FND_USER FULUL,
FND_APPLICATION FA,
FND_RESPONSIBILITY_TL FRTL,
FND_SECURITY_GROUPS FSG
WHERE
FURGA.USER_ID = FU.USER_ID (+)
AND FURGA.CREATED_BY = FUCB.USER_ID (+)
AND FURGA.LAST_UPDATED_BY = FULUB.USER_ID (+)
AND FURGA.LAST_UPDATE_LOGIN = FULUL.USER_ID (+)
AND FURGA.RESPONSIBILITY_APPLICATION_ID = FA.APPLICATION_ID (+)
AND FURGA.RESPONSIBILITY_ID = FRTL.RESPONSIBILITY_ID (+)
AND FRTL.LANGUAGE = ‘US’
AND FURGA.SECURITY_GROUP_ID = FSG.SECURITY_GROUP_ID (+)
ORDER BY START_DATE;

— ADD CONDITION ON START_DATE FOR RESPONSIBILITIES ADDED AFTER A SPECIFIC DATE LIKE “WHERE FURGA.START_DATE > TO_DATE(’22-JUL-2008')”


SELECT frt.RESPONSIBILITY_NAME, furg.end_date FROM  fnd_user_resp_groups furg,FND_RESPONSIBILITY fr,fnd_responsibility_tl frt,
fnd_user fu WHERE fu.user_name = ‘&&username’ AND   fu.user_id = furg.user_id AND   furg.responsibility_id = fr.RESPONSIBILITY_ID
AND  frt.responsibility_id = fr.RESPONSIBILITY_ID ORDER BY 1;

select a.user_name,c.RESPONSIBILITY_NAME,b.START_DATE,b.END_DATE from fnd_user a,fnd_user_resp_groups b ,fnd_responsibility_vl c
where a.user_id=b.user_id and b.RESPONSIBILITY_ID=c.RESPONSIBILITY_ID and a.user_name='&username';