Introduction:This query will fetch you the user responsibility details such as responsibility name, application name, description, security group, start date and end date. The input parameter passed is ‘user name’ which will get processed and provide you with the mentioned information in the combination of active users with active responsibility.
How do we solve:
SELECT FU.USER_NAME,
FRV.RESPONSIBILITY_NAME,
fav.application_name ,
FURGD.Description ,
NULL Security_Group ,
TO_CHAR (FURGD.START_DATE, ‘DD-MON-YYYY’) “START_DATE”,
TO_CHAR (FURGD.END_DATE, ‘DD-MON-YYYY’) “END_DATE”
FROM FND_USER FU,
FND_USER_RESP_GROUPS_DIRECT FURGD,
FND_RESPONSIBILITY_VL FRV,
FND_APPLICATION_VL fav
WHERE FU.USER_ID = FURGD.USER_ID
AND FURGD.RESPONSIBILITY_ID = FRV.RESPONSIBILITY_ID
AND FURGD.responsibility_application_id =fav.application_id
AND NVL(FURGD.end_DATE,sysdate+1) >sysdate —this will give all active responsibilities
–AND NVL(FU.end_DATE,sysdate +1) >sysdate —- this will give all active users
AND FU.USER_NAME IN (
‘USER_NAME’
)
ORDER BY 1;
Conclusion: This query will fetch you the required user responsibility details.
