rem fnduser.sql
rem
set linesize 132
rem
ttitle 'Application User Definitions'
rem
col user_name format a15 heading 'USER NAME'
col user_id format 99999 heading 'USER|ID'
col employee_flag format a1 heading 'E'
col description format a35 heading 'DESCRIPTION'
col start_date format a9 heading 'START|DATE'
col end_date format a9 heading 'END|DATE'
col last_logon_date format a9 heading 'LAST|LOGON|DATE'
col last_logon_time format a17 heading 'LAST LOGON|TIMESTAMP'
col last_logon_days format 9999 heading 'LAST|LOGON|DAYS'
col f format a1 heading 'F'
col p format a1 heading 'P'
col password_lifespan_days format 9999 heading 'PW|EXPIRE|DAYS'
col password_date format a9 heading 'PASSWORD|DATE'
col password_change_days format 9999 heading 'PW|CHANGE|DAYS'
rem
break on report
compute count of start_date end_date last_logon_time last_logon_days on report
rem
select user_name,
user_id,
decode( employee_id, null, null, 'E' ) employee_flag,
description,
start_date,
end_date,
decode( end_date, least( end_date, sysdate ), null, '+' ) f,
to_char( last_logon_date, 'mm/dd/yy hh24:mi:ss' ) last_logon_time,
decode( end_date, null, sysdate - last_logon_date ) last_logon_days,
decode( end_date, null, decode( 30, greatest( 30, trunc(sysdate-last_logon_date) ), null, '*' ) ) p,
password_lifespan_days,
password_date,
decode( end_date, null, sysdate - password_date ) password_change_days
from applsys.fnd_user u
where user_name like upper('%&userid%')
order by description, user_name;
rem
set linesize 80