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
;
