Results for “refresh_summary_mv”

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

Overview

ONT_OS_MV_REFRESH is an Oracle E-Business Suite Order Management (ONT) database package owned by the APPS schema. Its name reflects its purpose: it is the materialized view (MV) refresh controller for the Order Management Order Summary and order-line materialized view set (the "ONT_OS" family). In EBS 12.1.1 and 12.2.2, Order Management uses a collection of Materialized Views for shipped orders, booked orders, and aggregated order summary reporting. These MVs are non-transactional read models: they pre-join and pre-aggregate order headers, lines, and fulfillment data so that operational reports, dashboards, and Order Management inquiries can query them cheaply rather than scanning base order tables. The package is created with AUTHID CURRENT_USER, meaning its SQL executes with the privileges of the calling user rather than the APPS owner, which is the standard convention for EBS concurrent-program wrapper packages that must resolve APPS synonyms at runtime.

Key Procedures and Functions

The package exposes three public procedures, all of which follow the standard EBS concurrent-program interface: each returns an OUT NOCOPY errbuf for error reporting and an OUT NOCOPY retcode for completion status (0 for success, 1 for warning, 2 for error). This signature is what makes the package callable directly from a Concurrent Program definition rather than through a wrapper.

• refresh_shipped_orders — Refreshes the materialized view(s) that store shipped and fulfilled order-line data. This is the data set read by shipped-order reports and delivery confirmation inquiries.
• refresh_booked_orders — Refreshes the materialized view(s) holding booked order information, i.e. order lines that have been entered and booked but not necessarily shipped. This supports backlog and open-order reporting.
• refresh_summary_mv — Refreshes the aggregated order summary materialized view. This is the procedure matching the user's search term "refresh_summary_mv". It rebuilds the pre-aggregated summary structure that compresses large volumes of order data into reporting-friendly summaries. In practice this procedure accepts no business arguments beyond errbuf and retcode; it uses fixed, internally defined refresh logic.

No functions, and no other public procedures, are documented in the package specification. The stored header version is 120.1 (2006), indicating the package has been stable since the early R12 codebase.

Tables Accessed

The ETRM metadata records no directly referenced tables via APPS synonyms. This is expected: materialized view refresh is executed through DBMS_MVIEW / DBMS_SNAPSHOT refresh calls that reference the MV names (which behave as tables) rather than the underlying base tables. The base tables underlying the ONT_OS MV family are the standard Order Management entities — OE_ORDER_HEADERS_ALL, OE_ORDER_LINES_ALL, and related fulfillment and shipping tables — but these are read indirectly by the MV refresh engine, not directly by this package's SQL. Because the package is AUTHID CURRENT_USER, any synonym resolution occurs in the caller's schema, so the caller must have execute privilege on the package and the appropriate MV refresh privileges.

Usage Notes

ONT_OS_MV_REFRESH is designed to be driven from Concurrent Manager. Each of the three procedures maps to a concurrent program (typically grouped under an Order Management "Refresh" or "Maintain" request group), allowing the system administrator to schedule MV refreshes, run them on demand, or chain them into a request set that refreshes shipped, booked, and summary MVs in sequence. The errbuf/retcode pair written to FND_CONCURRENT_REQUESTS determines whether the concurrent request reports Normal, Warning, or Error. Typical invocation patterns include a nightly batch window that refreshes the MVs before order reports and analytics are produced, and manual execution when users need current summary data sooner than the scheduled run. Custom code may also call the procedures directly, provided it supplies the two OUT parameters and observes the convention that the same database session context (APPS or a user with the necessary synonyms) is used.

The package is referenced by zero other packages per the ETRM metadata, confirming it is a top-level refresh entry point rather than an internal utility called by other ONT APIs. Its small, fixed interface and unchanged 120.1 versioning make it safe to invoke from third-party or extension code, though refresh of the underlying MVs should be coordinated with the Order Management reporting cycle to avoid contention with concurrent order entry and shipping transactions.