Subversion Repositories DevTools

/* User Login Trail */
SELECT DECODE( lt.ENUM_EVENT, 
                           1,  'LIMG_LOGON_SUCCESS',
                           -1, 'LIMG_LOGON_FAIL',
                           0,  'LIMG_LOGOFF',
                           2,  'LIMG_SESSION_EXPIRE') AS event_icon,
           lt.USER_NAME,
           usr.USER_ID,
           usr.FULL_NAME,
           lt.CLIENT_IP,
           app.APP_ID,
           app.APPLICATION_NAME,
           lt.STAMP,
           lt.COMMENTS
  FROM (

                /* Successful Login, Logoff and Session Expire */
                SELECT lt.*
                  FROM LOGIN_TRAIL lt
                 WHERE lt.USER_NAME = :USER_NAME  
                   AND lt.ENUM_EVENT != -1
                
                UNION
                
                /* Login Fail from this user IP */
                SELECT ltf.*
                  FROM LOGIN_TRAIL ltf,
                           (
                                SELECT ltf.CLIENT_IP
                                  FROM LOGIN_TRAIL ltf
                                 WHERE ltf.USER_NAME = :USER_NAME  
                                   AND ltf.CLIENT_IP IS NOT NULL           
                           ) userip
                 WHERE ltf.CLIENT_IP = userip.CLIENT_IP
                   AND ltf.ENUM_EVENT = -1
  
           ) lt,
           APPLICATIONS app,
           USERS usr
 WHERE lt.APP_ID = app.APP_ID (+)
   AND lt.USER_NAME = usr.USER_NAME (+)
ORDER BY lt.STAMP DESC