This discussion is archived
8 Replies Latest reply: Jun 20, 2013 1:12 PM by Michael Armstrong-Smith RSS

Question about FND_USER table

529471 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    RodWest Guru
    Currently Being Moderated
    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
    572857 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated

    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 Oracle ACE
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Oracle ACE
    Currently Being Moderated

    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