DOYENSYS Knowledge Portal

We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal

Tuesday, October 28, 2014

Oracle Application User(s) List With Age Threshold

The Below script will List the Oracle Application users with the specified age limit

select papf.employee_number,papf.full_name, papf.date_of_birth
from per_all_people_f papf,fnd_user fu
where papf.person_id=fu.employee_id 
and substr(papf.date_of_birth,8) <= 56  --year (2014-58=56)
--Here the user with the birth year less than or equal to 56 will be listed
and fu.END_DATE is NULL
--Specify whether to list end dated user or active users
and trunc(sysdate) between papf.effective_start_date and 
papf.effective_end_date order by DATE_OF_BIRTH

For clear view use the query in Toad