Introduction: This query will fetch you the responsibility details which is missing for a user. 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 which is present in the database. For example there are 200+ entity in a project and responsibility is created for every entity. If a user is assigned with 120 responsibilities and this query will help us find the missing responsibilities for that user
How do we solve:
SELECT FRV.RESPONSIBILITY_ID ,FRV.responsibility_name
FROM FND_RESPONSIBILITY_VL FRV
WHERE FRV.responsibility_name like ‘%<responsibility name>%’
AND NVL(FRV.end_DATE,sysdate+1) >sysdate
AND FRV.RESPONSIBILITY_ID NOT IN
(SELECT FURGD.RESPONSIBILITY_ID –5
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 FRV.responsibility_name like ‘%<responsibility name>%’
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 = ‘<username>’);
Conclusion: this query will help us find the missing responsibilities for that user.
