Search Results dbms_mview




Overview
Explore in AI Chat

Overview

The APPS.HRI_OLTP_CONC_MV package body is a database utility within the Oracle E-Business Suite Applications schema (APPS) that supports the refresh cycle of materialized views used by the HRI (Human Resources Intelligence) OLTP reporting layer. Its principal business function is to provide a controlled, concurrent-safe wrapper around Oracle's native materialized view refresh infrastructure so that HRI OLTP summary objects can be rebuilt on demand or on a scheduled basis without requiring administrators to construct ad hoc DBMS_MVIEW.REFRESH calls manually.

Materialized views in the HRI OLTP schema underpin cross-functional HR and workforce analytics — headcount, assignment, and related transactional aggregates. Because these views are sourced from high-volume transactional tables, their refreshes are expensive, long-running, and must be coordinated so that only one refresh executes at a time and that failures are surfaced to the requesting session. This package encapsulates that coordination logic, tooling it for invocation from the Concurrent Manager rather than from interactive sessions.

Key Procedures and Functions

The ETRM metadata documents two entry points in this package body:

  • REFRESH_MV — The primary driver routine. It performs the orchestrated refresh of the target materialized view(s) associated with the HRI OLTP concurrent refresh process. Internally it relies on DBMS_MVIEW to execute the refresh and uses FND_FILE to emit diagnostic and progress output to the concurrent program log and output file. Concurrency control and session context are obtained through FND_GLOBAL.
  • REFRESH_MV_SQL — A companion routine that constructs and/or executes the dynamic SQL statement used to invoke the materialized view refresh. This separation allows the refresh command text to be assembled independently of its execution, which is useful for logging the exact DDL/DML issued and for runtime validation before submission.

Neither routine is documented with a parameter list in the ETRM metadata; callers should therefore consult the shipped package specification (HRI_OLTP_CONC_MV) for the authoritative signature of each entry point.

Tables Accessed

The package operates primarily against dictionary and infrastructure objects rather than business tables. The documented references are:

  • DBMS_MVIEW — Oracle's supplied package for materialized view refresh. This is the core dependency and the reason this object surfaces in searches for dbms_mview. HRI_OLTP_CONC_MV does not reimplement refresh logic; it invokes the supported API.
  • FND_ORACLE_USERID — Referenced via an APPS synonym. This is used to resolve the Oracle database username and credentials context in which the refresh executes, ensuring the materialized view is rebuilt under the correct schema identity.

Additional dependencies shown in the ETRM metadata include FND_FILE (log/output handling), FND_GLOBAL (session and user context), FND_INSTALLATION and FND_STATS (installation state and statistics gathering), HR_UTILITY (shared HR utility logic), and SYS.STANDARD. The package is not referenced by any other database object, confirming it sits at the top of its own call stack.

Usage Notes

Because the package writes to FND_FILE and reads FND_GLOBAL, it is designed to run inside the Concurrent Manager environment, where a concurrent request ID and log/output file handles are available. It is typically invoked by an HRI OLTP concurrent program — commonly scheduled during low-activity windows given the cost of full materialized view refreshes — or called directly from PL/SQL by administrators performing a targeted refresh during implementation or troubleshooting.

Key operational considerations: the package is valid and supported at both Oracle EBS 12.1.1 and 12.2.2, but because it is classified as OTHER rather than a public API, customers should treat it as internal. Custom code should not call it directly where a supported concurrent program exists. When refreshing HRI OLTP materialized views manually, prefer the documented concurrent program entry point so that logging, privileges, and concurrency locking behave as Oracle intended.