30-Day Active Users.sql

SELECT DISTINCT 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.LAST_CONNECT > (SYSDATE - 30)
	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)
ORDER BY FS.USER_NAME