[Oracle EBS Scripts]通过User找Responsibilities & 通过Responsibility找Users

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/