/* 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	