Saturday, 29 September 2012

How many responsibility assign in a User



SELECT  FU.USER_ID,FU.USER_NAME
        ,FAT.APPLICATION_NAME
        ,FA.APPLICATION_SHORT_NAME SHORT_NAME
        ,FRT.RESPONSIBILITY_NAME
        ,FRT.DESCRIPTION
        ,FR.RESPONSIBILITY_KEY
        ,TO_CHAR(FUR.CREATION_DATE,'DD-MON-YYYY HH:MI:SS AM') RESPONS_START_DATE
FROM    FND_RESPONSIBILITY FR
,       FND_RESPONSIBILITY_TL FRT
,       FND_APPLICATION     FA
,       FND_APPLICATION_TL  FAT
,       FND_USER_RESP_GROUPS_DIRECT FUR
,       FND_USER            FU
WHERE   FR.APPLICATION_ID   =   FRT.APPLICATION_ID
AND     FR.RESPONSIBILITY_ID=   FRT.RESPONSIBILITY_ID
AND     FA.APPLICATION_ID   =   FR.APPLICATION_ID
AND     FA.APPLICATION_ID   =   FAT.APPLICATION_ID
AND     FUR.RESPONSIBILITY_ID=  FR.RESPONSIBILITY_ID
AND     FUR.USER_ID         =   FU.USER_ID
AND     FU.USER_NAME        =   UPPER(NVL(:USER_NAME,FU.USER_NAME))  --'PO'
AND     EMPLOYEE_ID  IS NOT NULL