8 Replies Latest reply: Jun 20, 2013 3:12 PM by Michael Armstrong-Smith RSS

    Question about FND_USER table

    529471
      I am a beginner on Discoverer. When I study the topics on Discoverer, I see some people discuss about FND_USER table.

      Could any one help me understand what FND_USER table is used for and where it is stored on the system?

      Or how could I create FND_USER table?

      Thank you.
        • 1. Re: Question about FND_USER table
          RakeshTripathi
          Hi,

          FND_USER table stores details about the Oracle E-Business Suite user-ids (Application Users) and related information (encrypted password, email-ids) etc.

          Rakesh.
          • 2. Re: Question about FND_USER table
            529471
            Could you help me a little bit more? I want to know where I can find this table in system, or how can I create the table with Discoverer?

            Thanks.
            • 3. Re: Question about FND_USER table
              Rod West
              Hi,

              The FND_USER table is only used if you are installation an Apps mode Discoverer EUL to use with Oracle Applications 11i. You will find the table in the APPLSYS schema and it is the table used by Oracle Applications to store details of users and their passwords.

              Rod West
              • 4. Re: Question about FND_USER table
                SK
                Hi,

                APPLSYS.FND_USER : FND_USER stores information about application users. Each row includes the user's username (what a user types in at the sign-on
                screen), password, and information on when the user should change
                the password. Each row also contains information on when the user
                last signed on, start and end dates for when a username is valid
                and a description of the user.
                Oracle Application Object Library uses this information to allow users to sign on to
                an application.
                few columns are
                USER_ID
                USER_NAME
                LAST_UPDATE_DATE
                LAST_UPDATED_BY
                CREATION_DATE

                So if you go to apps schema or applsys schema you can find this table.
                • 5. Re: Question about FND_USER table
                  Yacouba

                  And what about FND_USER_RESP_GROUPS_ALL, if want to retreive all user sign in activities, did I need this table?

                  • 6. Re: Question about FND_USER table
                    Michael Armstrong-Smith

                    Hello

                    You might not need to go after the individual tables, Oracle provides a view that connects everything together.I think the following 2 scripts will help:

                     

                    The following script will show which responsibilities have been granted access to shared workbooks.

                    SELECT
                    USER_NAME || ':' || DOCS.DOC_NAME "Doc Name",
                    RESP.RESPONSIBILITY_NAME           "Resp Name"
                    FROM
                    EUL5_US
                    .EUL5_EUL_USERS    USRS,
                    EUL5_US
                    .EUL5_ACCESS_PRIVS PRIVS,
                    EUL5_US
                    .EUL5_DOCUMENTS    DOCS,
                    EUL5_US
                    .EUL5_EUL_USERS    OWN_USR,
                    APPS.FND_RESPONSIBILITY_VL  RESP,
                    APPS.FND_USER               CRTD_BY
                    WHERE 1=1
                    AND PRIVS.AP_TYPE = 'GD'

                    AND PRIVS.AP_EU_ID = USRS.EU_ID
                    AND PRIVS.GD_DOC_ID = DOCS.DOC_ID
                    AND OWN_USR.EU_ID = DOCS.DOC_EU_ID
                    AND RESP.RESPONSIBILITY_ID =
                      SUBSTR(USRS.EU_USERNAME,2,INSTR(USRS.EU_USERNAME,'#',2)-2)
                    AND CRTD_BY.USER_ID = SUBSTR(DOCS.DOC_CREATED_BY,2)
                    AND OWN_USR.EU_USERNAME NOT IN ('EUL5_US','PUBLIC')
                    ORDER BY 1,2;

                     

                    The following script will show which responsibilities and users have been granted access to business areas.

                    SELECT

                    BAS.BA_NAME,

                    FNDUSRS.USER_NAME USER_OR_RESP_NAME,

                    'EMPLOYEE'        USER_OR_RESP_TYPE

                    FROM

                    EUL5_US.EUL5_BAS          BAS,

                    EUL5_US.EUL5_ACCESS_PRIVS PRIVS,

                    EUL5_US.EUL5_EUL_USERS    USRS,

                    APPLSYS.FND_USER         FNDUSRS

                    WHERE 1=1

                    AND PRIVS.GBA_BA_ID = BAS.BA_ID

                    AND USRS.EU_ID = PRIVS.AP_EU_ID

                    AND USRS.EU_USERNAME NOT IN ('EUL5_US','SYSADMIN')

                    AND FNDUSRS.USER_NAME NOT IN ('SYSADMIN','REPORT_SCHEDULER')

                    AND '#' || FNDUSRS.USER_ID = USRS.EU_USERNAME

                    UNION ALL SELECT

                    BAS.BA_NAME,

                    RESP.RESPONSIBILITY_KEY USER_RESP_NAME,

                    'RESPONSIBILITY'        USER_RESP_TYPE

                    FROM

                    EUL5_US.EUL5_BAS BAS,

                    EUL5_US.EUL5_ACCESS_PRIVS   PRIVS,

                    EUL5_US.EUL5_EUL_USERS      USRS,

                    APPLSYS.FND_RESPONSIBILITY RESP

                    WHERE 1=1

                    AND PRIVS.GBA_BA_ID = BAS.BA_ID

                    AND USRS.EU_ID = PRIVS.AP_EU_ID

                    AND USRS.EU_USERNAME NOT IN ('EUL5_US','SYSADMIN')

                    AND INSTR(USRS.EU_USERNAME,'#',1,2) > 1

                    AND APPLICATION_ID = SUBSTR(USRS.EU_USERNAME,INSTR(USRS.EU_USERNAME,'#',1,2)+1)

                    AND RESPONSIBILITY_ID = SUBSTR(USRS.EU_USERNAME,INSTR(USRS.EU_USERNAME,'#',1,1)+1,INSTR(USRS.EU_USERNAME,'#',1,2)-2)

                    ORDER BY 1, 2;

                     

                    Best wishes

                    Michael

                    • 7. Re: Question about FND_USER table
                      Yacouba

                      Sorry but I don't find this kind of table on my Oracle EUL5_US.EUL5_EUL_USERS.... have got an error..

                      • 8. Re: Question about FND_USER table
                        Michael Armstrong-Smith

                        That's definitely the right table. What error do you have?

                         

                        Is your EUL owned by EUL5_US because if not you will have to change all references to the owner. Some folks use EUL_US without the number 5.

                         

                        Also, you need to either be logged in as that user or with an account that has permission to select from the EUL tables.

                         

                        Best wishes

                        Michael