FND User Login History – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™. Oracle EBS module: Application Object Library · all Application Object Library reports

Overview

Audit history of application user logins.

EBS user logon audit is controlled by profile ‘Sign-On:Audit Level’. The most detailed audit level setting is ‘FORM’. Unfortunately, this audit tracks access to individual forms only, but not to different JSPs (HTML / OAF / ADF Pages). As a workaround, the report SQL also joins to icx_sessions, which contains a record for each login (in fact, it also stores a record for each access to the login page before login. These records are marked with guest=’Y’). The function retrieved from icx_session however, just shows the latest OAF function accessed by the user, not all individual JSP functions accessed within that session.

Run this report in Excel in seconds: download Blitz Report™ free (your first 30 reports are free) or try it in the online demo.

Report Parameters

Responsibility Name, Form Name, User Name, Logged in within Days, Date From, Date To, Restrict to incremental data, Incremental Alert Mode, Exclude User Name, Include Geolocation

Oracle EBS Tables Used

per_security_profiles, hr_operating_units, per_organization_list, hr_all_organization_units_vl, per_all_assignments_f, fnd_profile_option_values, fnd_logins, fnd_login_responsibilities, fnd_login_resp_forms, fnd_form_vl, fnd_appl_sessions, icx_sessions, fnd_nodes, fnd_form_functions_vl, fnd_user, fnd_responsibility_vl, gv$session

Report Categories

Enginatics

FND Responsibility Access, FND Responsibilities, AP Suppliers, GL Account Analysis, INV Item Upload, GL Account Distribution Analysis, INV Items, AR Customer Upload

Running This SQL Without Blitz Report

The SQL source can be run in any SQL client. Some Oracle EBS SQL reports in this library use functions from the utility package xxen_util – install it first. Blitz Report™ adds parameters, Excel output with pivots, scheduling and security on top.

Download & Import Options

Resource Link
Excel Example Output FND User Login History 24-Jul-2021 233519.xlsx
Blitz Report™ XML Import FND_User_Login_History.xml
SQL Source FND User Login History.sql
Full Report on Enginatics www.enginatics.com/reports/fnd-user-login-history/

FND User Login History - Case Study

Executive Summary

The FND User Login History report is an essential security and auditing tool for Oracle E-Business Suite. It provides a detailed historical record of user access to the system, including login times, logout times, and the specific responsibilities accessed. This report is vital for IT security teams, auditors, and system administrators to ensure compliance with regulatory standards (such as SOX or GDPR), monitor user adoption, and investigate potential security breaches.

Business Challenge

Tracking “who did what and when” is a fundamental requirement for enterprise systems. However, Oracle EBS login data is often scattered across multiple tables depending on the access method (Forms vs. Self-Service Web Applications).

Solution

The FND User Login History report bridges the gap between Forms-based and Web-based login auditing. It consolidates data from the underlying FND (Foundation) and ICX (Inter-Cartridge Exchange) tables to provide a unified view of user sessions.

Key Features:

Technical Architecture

The report’s logic handles the complexity of Oracle’s dual-layer architecture (Forms vs. Web).

Primary Tables:

Key Logic:

Frequently Asked Questions

Q: Why are some logout times missing? A: If a user closes their browser without clicking “Logout,” or if the session crashes, the logout time may not be recorded properly. The system eventually marks these as timed out, but the timestamp might be null or delayed.

Q: Does this report show which specific records a user modified? A: No. This report tracks access (logins and responsibilities). To track record-level changes (inserts, updates), you need to use FND Audit Trail or Database Auditing features.

Q: Why do I see records for the ‘GUEST’ user? A: The ‘GUEST’ user is used internally by Oracle EBS for anonymous access (like the initial login page). The report typically filters these out or identifies them separately, but they are a normal part of icx_sessions.

Q: How far back does the history go? A: It depends on your system’s purge policies. The “Purge Signon Audit Data” concurrent program is usually scheduled to remove old records to save space. The report can only show data that hasn’t been purged.

© Enginatics GmbH