Recent Users.sql

SELECT DISTINCT TO_CHAR(FS.LAST_CONNECT, 'YYYY/MM/DD') "LastConnect"
	, FS.USER_NAME "UserName"
	, PLU.EMAIL "Email"
	, PAPF.PERSON_NUMBER "PersonNumber"
FROM FND_SESSIONS FS
	, PER_USERS PU
		LEFT JOIN PER_ALL_PEOPLE_F PAPF
		ON PAPF.PERSON_ID = PU.PERSON_ID
		AND PAPF.LAST_UPDATE_DATE = (SELECT MAX(PAPF2.LAST_UPDATE_DATE)
								FROM PER_ALL_PEOPLE_F PAPF2
								WHERE 1 = 1
									AND PAPF2.PERSON_ID = PAPF.PERSON_ID)
	, PER_LDAP_USERS PLU
WHERE 1 = 1
	AND FS.USER_NAME = PU.USERNAME
	AND FS.USER_NAME = PLU.USERNAME
	AND PLU.LAST_UPDATE_DATE = (SELECT MAX(PLU2.LAST_UPDATE_DATE)
								FROM PER_LDAP_USERS PLU2
								WHERE 1 = 1
									AND PLU2.LDAP_USER_ID = PLU.LDAP_USER_ID)
	AND PU.LAST_UPDATE_DATE = (SELECT MAX(PU2.LAST_UPDATE_DATE)
								FROM PER_USERS PU2
								WHERE 1 = 1
									AND PU2.USER_ID = PU.USER_ID)
	AND USER_NAME LIKE nvl(:User_Name, FS.USER_NAME)
	AND EMAIL LIKE nvl(:Email, PLU.EMAIL)
	AND PERSON_NUMBER LIKE nvl(:PersonNumber, PAPF.PERSON_NUMBER)
ORDER BY TO_CHAR(FS.LAST_CONNECT, 'YYYY/MM/DD') DESC