Obtain responsibility and applicaiton

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 (+)
AND UPPER(fu.user_name) LIKE UPPER('&username%')
ORDER BY START_DATE;

 

Obtain responsibility for a user

SELECT SUBSTR(user_name,1,15) AS username,

b.responsibility_key,

b.responsibility_name name, TO_CHAR(a.responsibility_id) id

FROM fnd_user_resp_groups a,

fnd_responsibility_vl b,

fnd_user u

WHERE a.user_id = u.user_id

AND a.responsibility_id = b.responsibility_id

AND a.responsibility_application_id = b.application_id

AND SYSDATE BETWEEN a.start_date AND NVL(a.end_date,SYSDATE+1)

AND UPPER(u.user_name) LIKE UPPER('&username%')