application user definition

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