How to get User last login information from Oracle HCM Cloud environment

There are two ways to get the User's last login information from Oracle HCM Cloud environment.

1> From FND_SESSIONS table
2> From ASE_USER_LOGIN_INFO table

We can directly query FND_SESSIONS table and it always stores data.

ASE_USER_LOGIN_INFO table needs to be updated. We have to run a ESS Job named "Import User Login History" to get ASE_USER_LOGIN_INFO table to be populated.

Sample SQL Query (You can modify as required):

-----------------------------------

Procedure 1 :

SELECT DISTINCT
   USER_NAME
  ,CREATED_BY
  ,CREATION_DATE
  ,LAST_UPDATED_BY
  ,LAST_UPDATE_DATE
  ,LAST_UPDATE_LOGIN
  ,FIRST_CONNECT
  ,LAST_CONNECT
  ,TERRITORY
FROM FND_SESSIONS
WHERE LAST_UPDATE_DATE > (SYSDATE-30)

Procedure 2 :

a> First navigate to Home --> Tools --> Scheduled Processes

b> Schedule the process "Import User Login History"

c> Then run the below query in OTBI

SELECT DISTINCT
   PU.USERNAME
FROM ASE_USER_LOGIN_INFO AULI, PER_USERS PU
WHERE AULI.USER_GUID = PU.USER_GUID

AND   AULI.LAST_LOGIN_DATE > (SYSDATE-30)