1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Query to extract user status (active or inactive) of fnd_user

Discussion in 'SQL PL/SQL' started by kartheekbk, Aug 15, 2014.

  1. kartheekbk

    kartheekbk Active Member

    Messages:
    37
    Likes Received:
    0
    Trophy Points:
    80
    Hi Forum Team,

    In Oracle Apps, Can you provide me the query to extract user status (active or inactive) of fnd_user.

    See the attached image where you can find the user status from user form (sysem administrator->security: User ->define).


    Thanks,
    Kartheek B
     

    Attached Files:

  2. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    Have you tried opening the form definition to look at the query source? The value may actually be in FND_USER...but may be a number and decoded via a domain to what you see on the screen.

    HTH

    CJ
     
  3. vijayreddy82

    vijayreddy82 Active Member

    Messages:
    2
    Likes Received:
    1
    Trophy Points:
    65
    Try this to find the status for all users.

    Code (SQL):
     SELECT 'Active', valid.*
      FROM fnd_user valid
     WHERE valid.user_id IN (SELECT user_id
                               FROM fnd_user
                              WHERE NVL (end_date, SYSDATE) >= SYSDATE)
    UNION ALL
    SELECT 'In Active', invalid.*
      FROM fnd_user invalid
     WHERE invalid.user_id IN (SELECT user_id
                                 FROM fnd_user
                                WHERE NVL (end_date, SYSDATE) < SYSDATE)
    ORDER BY 2;
     
    kartheekbk likes this.
  4. vijayreddy82

    vijayreddy82 Active Member

    Messages:
    2
    Likes Received:
    1
    Trophy Points:
    65
    Try this to input a particular user id or user name.

    Code (SQL):
    SELECT * FROM (SELECT 'Active' "Status", valid.*
      FROM fnd_user valid
     WHERE valid.user_id IN (SELECT user_id
                               FROM fnd_user
                              WHERE NVL (end_date, SYSDATE) >= SYSDATE)
    UNION ALL
    SELECT 'In Active' "Status", invalid.*
      FROM fnd_user invalid
     WHERE invalid.user_id IN (SELECT user_id
                                 FROM fnd_user
                                WHERE NVL (end_date, SYSDATE) < SYSDATE)
    )
    WHERE user_id =:user_id
    OR user_name = :user_name
     
  5. kartheekbk

    kartheekbk Active Member

    Messages:
    37
    Likes Received:
    0
    Trophy Points:
    80
    It seems You are right Viyaj.

    but I tried with the DECODE function, can you correct the below query. I'm trying to get the same result with DECODE function.

    Select employee_number, Decode((end_date > sysdate), 'ACTIVE', 'INACTIVE') USER_STATUS
    from apps.fnd_user
     
  6. kartheekbk

    kartheekbk Active Member

    Messages:
    37
    Likes Received:
    0
    Trophy Points:
    80
    I tried to find it from user definition form to get the query source, But no query exists there.
    Can you correct the below query ..the condition is if the end_date > sysdate , then it is active..else Inactive.

    Select employee_number, Decode((end_date > sysdate), 'ACTIVE', 'INACTIVE') USER_STATUS
    from apps.fnd_use
     
  7. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Kartheek,

    When you use the form to get the Query (Help -> Diagnostics -> Examine , Block: SYSTEM, FIELD: LAST_QUERY), you do get something, but the last query executed by the form is for the responsibilities assigned to the User; so this won't help you.

    Vijay's idea is fine and I suppose that's the way to get the status, although I think there are other cases apart from 'Active', 'Inactive' like 'Locked', 'Pending', ... - see fnd_lookup_values table for lookup_type='FND_USRDEF_STATUS' to get the complete list.

    Coming to your SELECT:
    1) employee_number does not exist in fnd_user table, it's employee_id
    2) The corrected query is as follows (I've added a few more fields just to show the details - you can amend as you wish) - basically, you can't put such an operator in the DECODE; a more elegant and flexible way is to use CASE:

    Code (SQL):
    SELECT user_name,
      employee_id,
      start_date,
      end_date,
      CASE
        WHEN end_date IS NULL  OR end_date > sysdate
        THEN 'ACTIVE'
        ELSE 'INACTIVE'
      END user_status
    FROM apps.fnd_user
    ;
     
  8. kartheekbk

    kartheekbk Active Member

    Messages:
    37
    Likes Received:
    0
    Trophy Points:
    80
    Thanks Rajen, You made me better understand to get the things.

    Do you have any idea how the user status(Active/Inactive/Locked etc.,) are populating in front end User definition screen ?
    is this status depending on any tables or sessions ?
     
  9. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi,

    In addition to above logic (to get status Active/Inactive), there is definitely additional pieces of code in the Form which retrieve the other statuses - most probably from FND_USER table columns.

    To get these details, you'll have to open the Form in Forms Developer and extract the logic.
    The Form Name is FNDSCAUS.fmb (Help -> About Oracle Applications).
     
    kartheekbk likes this.
  10. kartheekbk

    kartheekbk Active Member

    Messages:
    37
    Likes Received:
    0
    Trophy Points:
    80
    Rajen, Appreciated your prompt reply. I will check the logic.