Results for “jtf_mon_ustat”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

JTF_MON_USTAT is a reporting view in the JTF (CRM Foundation) module of Oracle E-Business Suite, present in both 12.1.1 and 12.2.2. It aggregates end-user activity captured in the CRM Foundation monitoring infrastructure, presenting per-user, per-responsibility, and per-application navigation statistics. The view is part of the JTF monitoring framework (the JTF_MON_* object family) used to observe how users traverse EBS functionality, including session counts, page (function) penetration counts, and elapsed activity windows.

The view is not a transactional or integration object; it is a diagnostic and analytical construct. In the ETRM metadata, note the caveat that it is "Not implemented in this database" — meaning the monitoring/audit logging that feeds the underlying log table must be enabled and populated before this view returns meaningful rows. Its role in EBS reporting is to surface summarized usage metrics for performance tuning, adoption analysis, and capacity assessment.

Underlying Base Objects

The view is defined over two principal sources, though the ETRM metadata records "Referenced base objects: none documented." The view text reveals:

• JTF_MON_ACT_LOG — the activity log table that records individual page/function hits, session keys, user, responsibility, application, and start time. The inner query groups this table by day, USER_ID, RESP_ID, and APP_ID.
• JTF_MON_UNAME_LOOKUP — a lookup source that resolves USER_ID to a human-readable USER_NAME.

The core aggregation performs a GROUP BY on the truncated date (TO_DATE(TO_CHAR(START_TIME, 'DD-MON-YYYY'))), USER_ID, RESP_ID, and APP_ID. The resulting derived table (aliased HIT) is joined to the username lookup on USER_ID. This join is the mechanism that enriches numeric user identifiers with display names.

Key Columns

• USER_NAME — the application user name, sourced from JTF_MON_UNAME_LOOKUP.
• RESP_ID — the responsibility identifier under which the activity occurred.
• APP_ID — the application (product) identifier grouping the activity. This is the column most relevant to the user's "app_id" search, as it enables filtering and grouping by EBS application.
• SESSION_CNT — count of distinct sessions (SESKEY) for the user/responsibility/application/day combination.
• PAGE_CNT — total page (function) hits recorded.
• PAGES_PER_SESSION — derived ratio of PAGE_CNT to SESSION_CNT, indicating navigation depth.
• STIME / ETIME — the earliest and latest START_TIME within the group, establishing the activity window. The documentation also lists FIRST_OCCURRENCE and LAST_OCCURRENCE as column variants.

Common Use Cases and Queries

Typical scenarios include identifying the most heavily used applications and responsibilities, measuring user engagement depth, and correlating activity to performance issues.

Filtering by application:

• SELECT user_name, resp_id, app_id, session_cnt, page_cnt, pages_per_session FROM jtf_mon_ustat WHERE app_id = :app_id ORDER BY page_cnt DESC;

Identifying the most active users per day:

• SELECT user_name, app_id, SUM(session_cnt) sessions, SUM(page_cnt) pages FROM jtf_mon_ustat GROUP BY user_name, app_id ORDER BY pages DESC;

Reviewing activity windows:

• SELECT user_name, app_id, MIN(stime) first_hit, MAX(etime) last_hit FROM jtf_mon_ustat GROUP BY user_name, app_id;

Because the view depends on monitoring data being collected, verify that JTF monitoring is enabled and that JTF_MON_ACT_LOG contains records before relying on results. Absence of rows commonly indicates the monitoring feature is inactive rather than an absence of user activity.