{

sqlplus -s / AS SYSDBA <<ENDOFSQL

SET HEADING OFF;

SET FEEDBACK OFF;

SET LINESIZE 300;

SELECT '\\"' || USERNAME || '\\"' ||

       ' \\"' || REPLACE( ACCOUNT_STATUS, ' ', '\\ ' ) || '\\"' ||

       ' \\"' || NVL( to_char(lock_date,'DD-MON-YYYY\\ HH24:MI:SS'), '..-...-....\\ ..:..:..' ) || '\\"' ||

       ' \\"' || NVL( to_char(expiry_date,'DD-MON-YYYY\\ HH24:MI:SS'), '..-...-....\\ ..:..:..' ) || '\\"' ||

       ' \\"' || NVL( to_char(last_login,'DD-MON-YYYY\\ HH24:MI:SS'), '..-...-....\\ ..:..:..' ) || '\\"'

FROM dba_users

;

ENDOFSQL

}|while read us st lo ex la

do

if [ "$us" ] # Line not NULL

then

USERNAME="$us"

ACCOUNT_STATUS="$st"

LOCK_DATE="$lo"

EXPIRY_DATE="$ex"

LAST_LOGIN="$la"

echo usr=$USERNAME   stat=$ACCOUNT_STATUS   loc=$LOCK_DATE    exp=$EXPIRY_DATE   log=$LAST_LOGIN

fi

done

SELECT APPLICATION_NAME||':'||DBNAME||':'||USERNAME||':'||

       ACCOUNT_STATUS||':'||LOCK_DATE||':'||EXPIRY_DATE||':'||LAST_LOGIN||':'||

       USER_EMAIL||':'||MANAGERNAME||':'||MANAGER_MAIL||':'||STAT_COLLECT_DATE 

  FROM ( SELECT replace(trim(d.APPLICATION),' ','_') APPLICATION_NAME

              , trim(a.DBNAME) DBNAME

              , trim(a.USERNAME) USERNAME

              , trim(ACCOUNT_STATUS) ACCOUNT_STATUS

              , trim(to_char(lock_date,'DD-MON-YYYY_HH24_MI_SS')) LOCK_DATE

              , trim(to_char(expiry_date,'DD-MON-YYYY_HH24_MI_SS')) EXPIRY_DATE

              , trim(to_char(last_login,'DD-MON-YYYY_HH24_MI_SS')) LAST_LOGIN

              , trim(b.EMAIL) USER_EMAIL

              , trim(MANAGERNAME) MANAGERNAME

              , trim(MANAGER_MAIL) MANAGER_MAIL

              , trim(STAT_COLLECT_DATE) STAT_COLLECT_DATE

           from GAPMASTER.VS_STALE_USERS a

              , VS_AD_USERS b

              , (select USERNAME, email MANAGER_MAIL

                   from vs_ad_users) c

              , VS_AD_CONTACTS d 

           where profile in ('END_USER_PRFL','READ_ONLY_PRFL')

             and upper(a.username) = upper(b.username)

             and upper(b.managername) = upper(c.username)

             and a.dbname = d.dbname

             and a.dbname = 'IWP1'

          order by 2,1

       );

SELECT x.appl || ':' || x.dbn || ':' || x.usrn || ':' ||

       x.stat || ':' || x.lockd || ':' || x.expid || ':' ||

       x.logd || ':' || x.mail || ':' || x.mgrn || ':' ||

       x.mgrm || ':' || c.cold

  FROM ( SELECT REPLACE( TRIM( d.application ), ' ', '_' ) appl

              , TRIM( a.dbname ) dbn

              , TRIM( a.username ) usrn

              , TRIM( a.account_status ) stat

              , TO_CHAR( a.lock_date, 'DD-MON-YYYY_HH24_MI_SS' ) lockd

              , TO_CHAR( a.expiry_date, 'DD-MON-YYYY HH24_MI_SS' ) expid

              , TO_CHAR( a.last_login, 'DD-MON-YYYY HH24_MI_SS' ) logd

              , ?.user_email mail

              , b.manager_name mgrn

              , c.manager_mail mgrm

              , TRIM( ?.stat_collect_date ) cold

           FROM gapmaster.vs_stale_users a

           INNER JOIN vs_ad_users b

             ON UPPER( a.username ) = UPPER( b.username )

           INNER JOIN ( SELECT vau.username, vau.manager_mail

                          FROM vs_ad_users vau ) c

             ON UPPER( b.manager_name ) = UPPER( v.manager_name )

           INNER JOIN vs_ad_contacts d

             ON a.dbname = d.dbname

           WHERE ?.profile IN ( 'END_USER_PRFL', 'READ_ONLY_PRFL' )

             AND a.dbname = 'IWP1'

        )

  ORDER BY 2, 1

;