2,746 total views
Most of the customers want to know the list of users who logged in to Fusion Applications and want to encourage other employees too to use the Fusion system. For this, the customer needs to generate a report with the list of users who have logged in within a period of time like last 30days or on specific date or in last 6 months, etc.
In order to report on the logged in user history, we need to schedule “Importing User Login History” process to be run daily which updates the security tables in Fusion (ASE_USER_LOGIN_INFO) with the User login details from “FND_SESSIONS” table which stores the user information for 7 days before it gets purged automatically.
Steps to Schedule or run ad-hoc basis:
- Open the Scheduled Processes work area from Navigator > Tools > Scheduled Processes
- In the Search Results section of the Overview page, click Schedule New Process.
- In the Schedule New Process dialog box, search for and select the “Import User Login History“. Click OK.
- In the Process Details dialog box, click Advanced.
- On the Schedule tab, set Run to Using a schedule.
- Set Frequency to Daily and Every to 1. Enter start and end dates and times.
- Click Submit. Click OK to close the Confirmation message.
Once the User History Import is done, we can create a BI Report to report the user data.
SQL Query to get list of users who logged in last 30 days:
SELECT U.USERNAME, to_char(U.CREATION_DATE, 'dd-mm-yyyy') CREATION_DATE, to_char(A.LAST_LOGIN_DATE, 'dd-mm-yyyy') LAST_LOGIN_DATE FROM ASE_USER_LOGIN_INFO A,PER_USERS U WHERE A.USER_GUID = U.USER_GUID AND U.USERNAME NOT LIKE 'FUSION_APPS_%' AND A.LAST_LOGIN_DATE IS NOT NULL AND A.LAST_LOGIN_DATE > sysdate - 30
If you aren’t using this process, then you can use the FND_SESSIONS table query to get the required information, however this table might get purged and it is not suggested to go this way.
SELECT DISTINCT user_name "LOGIN_USERNAME" ,to_char(last_connect, 'dd-mm-yyyy') "LAST_LOGIN_DATE" FROM fnd_sessions WHERE user_name NOT LIKE 'FUSION_APPS%' ORDER BY to_char(last_connect, 'dd-mm-yyyy')
If you have any questions, please feel free to reach out to me by posting in comments section.
If you are interested in learning all Fusion Technical tools go through this article.
If you liked the article, please share it with your friends/ colleagues/ teammates or anyone who might also benefit from it.
Latest posts by Sricharan Monigari (see all)
- Renaming HCM Extracts is a possibility from 20D - November 24, 2020
- Sangam20 – Oracle Users Group Conference - November 23, 2020
- Passing Parameter values from Flow Instance to HDL Transformation Formula - November 23, 2020
- Solve manual FTP server update issue for HCM Extracts post every P2T refresh - November 16, 2020