Query To Get Active User Details Along with Associated Employee Details in Oracle Apps r12

 

SELECT user_id,
       user_name,
       creation_date,
       (SELECT user_name
        FROM   fnd_user fu
        WHERE  fu.user_id = created_by)     CREATED_BY,
       A.start_date                         USER_STRT_DATE,
       A.end_date                           USER_END_DATE,
       CASE
         WHEN A.end_date IS NULL
               OR A.end_date > SYSDATE THEN 'ACTIVE'
         ELSE 'INACTIVE'
       END                                  user_status,
       CASE
         WHEN encrypted_user_password = 'INVALID' THEN 'LOCKED'
         ELSE 'OPEN'
       END                                  password_status,
       description,
       A.email_address                      USER_EMAIL,
       password_lifespan_days,
       (SELECT Listagg(full_name, ', ')
                 within GROUP (ORDER BY full_name)
        FROM   per_all_people_f p
        WHERE  p.person_id = a.employee_id) CURRENT_EMPLOYEE,
       a.employee_id                        CURRENT_PERSON_ID,
       b.employee_number,
       b.gender,
       b.title,
       b.first_name,
       b.last_name,
       b.full_name,
       b.date_of_birth,
       b.national_identifier,
       b.email_address                      PERSON_EMAIL,
       b.office_number,
       b.start_date                         PERSON_START_DATE,
       b.end_date                           PERSON_END_DATE,
       b.hire_date                          PERSON_HIRE_DATE,
       b.user_person_type
FROM   apps.fnd_user a,
       (SELECT papf.person_id,
               papf.employee_number,
               (SELECT hrlsex.meaning
                FROM   hr_lookups hrlsex
                WHERE  hrlsex.lookup_type = 'SEX'
                       AND hrlsex.lookup_code = papf.sex)         Gender,
               papf.title,
               papf.first_name,
               papf.last_name,
               papf.full_name,
               papf.date_of_birth,
               papf.national_identifier,
               papf.email_address,
               papf.office_number,
               To_char(papf.effective_start_date, 'DD-MON-RRRR')  Start_Date,
               To_char(papf.effective_end_date, 'DD-MON-RRRR')    End_Date,
               To_char(papf.original_date_of_hire, 'DD-MON-RRRR') Hire_Date,
               ppt.user_person_type
        FROM   per_all_people_f papf,
               per_person_types_tl ppt
        WHERE  ppt.person_type_id (+) = papf.person_type_id
               AND LANGUAGE = Userenv ('LANG')) b
WHERE  1 = 1
       AND a.employee_id = b.person_id(+)
       AND A.end_date IS NULL
       AND A.user_id > 1100 -- Excludes standard users like SYSADMIN,GUEST etc
--and user_name = upper('CS0100417')
--AND a.EMPLOYEE_ID IS NULL ---Condition to get Users's Not Associated with Any Perosn
;