1.通过User，查找此用户挂的所有职责（Responsibilities）
SELECT FU.USER_ID ,
FU.USER_NAME USER_NAME ,
FR.RESPONSIBILITY_KEY RESPONSIBILITY ,
FRT.Responsibility_Name ,
FA.APPLICATION_NAME APPLICATION
FROM FND_USER FU ,
FND_USER_RESP_GROUPS FG ,
FND_APPLICATION_TL FA ,
FND_RESPONSIBILITY FR ,
FND_RESPONSIBILITY_TL FRT
WHERE FG.USER_ID (+) = FU.USER_ID
AND FG.RESPONSIBILITY_APPLICATION_ID = FA.APPLICATION_ID
AND FA.APPLICATION_ID = FR.APPLICATION_ID
AND FG.RESPONSIBILITY_ID = FR.RESPONSIBILITY_ID
AND FR.APPLICATION_ID = FRT.Application_Id
AND FR.Responsibility_Id = FRT.Responsibility_Id
AND FU.USER_NAME LIKE UPPER ( \'%TIANPAN%\')
AND FRT.Language = \'ZHS\'
AND FA.Language = \'ZHS\'
AND FG.END_DATE IS NULL
ORDER BY FU.USER_ID, FRT.Responsibility_Name ;
2.一个反向脚本，通过Responsibility，反向查找所有绑定此职责的User（及Employee）
SELECT FU.USER_NAME "User Login" ,
FU.DESCRIPTION "Role Description" ,
FU.START_DATE "Login Start Date" ,
FU.END_DATE "Login End Date" ,
FU.EMAIL_ADDRESS "E-Mail Associated" ,
FU.EMPLOYEE_ID "Employee Id" ,
PPF.EMPLOYEE_NUMBER "Employee Number" ,
PPF.FULL_NAME "Full Name" ,
HOU.NAME "Business Group" ,
FR.RESPONSIBILITY_NAME "Responsibility Associated" ,
FUR.START_DATE "Association Start Date" ,
FUR.END_DATE "Association End Date"
FROM APPS.FND_USER FU ,
APPS.PER_ALL_PEOPLE_F PPF ,
APPS.HR_ALL_ORGANIZATION_UNITS HOU ,
APPS.FND_USER_RESP_GROUPS_ALL FUR ,
APPS.FND_RESPONSIBILITY_TL FR
WHERE PPF.PERSON_ID = FU.EMPLOYEE_ID
AND HOU.ORGANIZATION_ID = PPF.BUSINESS_GROUP_ID
AND PPF.EFFECTIVE_END_DATE = TO_DATE ( \'31/12/4712\', \'DD/MM/RRRR\' )
AND FU.USER_ID = FUR.USER_ID
AND NVL (FUR.END_DATE , SYSDATE + 1 ) > SYSDATE
AND FUR.RESPONSIBILITY_ID = FR.RESPONSIBILITY_ID
AND FR.RESPONSIBILITY_NAME LIKE \'%人力资源%\'
AND FR.LANGUAGE = \'ZHS\'
ORDER BY FU.USER_NAME, FR.RESPONSIBILITY_NAME
转载于:http://blog.itpub.net/26687597/viewspace-1207618/
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
