SQL

Find All the Responsibilities to which a Concurrent Program is Assigned

SELECT FRV.RESPONSIBILITY_NAME, FRG.REQUEST_GROUP_NAME,
FRGU.REQUEST_UNIT_TYPE,FRGU.REQUEST_UNIT_ID,
FCP.USER_CONCURRENT_PROGRAM_NAME
FROM APPS.FND_RESPONSIBILITY_VL FRV,
APPS.FND_CONCURRENT_PROGRAMS_VL FCP,
APPS.FND_REQUEST_GROUPS FRG,
APPS.FND_REQUEST_GROUP_UNITS FRGU
WHERE FRV.REQUEST_GROUP_ID = FRG.REQUEST_GROUP_ID
AND FRG.REQUEST_GROUP_ID = FRGU.REQUEST_GROUP_ID
AND FRGU.REQUEST_UNIT_ID = FCP.CONCURRENT_PROGRAM_ID
AND FCP.USER_CONCURRENT_PROGRAM_NAME =
‘&CONCURRENT_PROGRAM’;

Find Responsibilities for a User Account

SELECT USR.USER_ID, USR.USER_NAME, RES.RESPONSIBILITY_ID, RES.RESPONSIBILITY_NAME, GRP.START_DATE, GRP.END_DATE
FROM APPS.FND_USER USR,
APPS.FND_RESPONSIBILITY_VL RES,
APPS.FND_USER_RESP_GROUPS GRP
WHERE GRP.RESPONSIBILITY_ID = RES.RESPONSIBILITY_ID
AND GRP.USER_ID = USR.USER_ID
–and usr.user_NAME like ‘%%’
AND UPPER(RES.RESPONSIBILITY_NAME) LIKE UPPER(‘&RESP_NAME’);