Search Results get_row_count

Overview

AMW_PURGE_MVIEW_LOG is a PL/SQL package body owned by the APPS schema that supports the Oracle E-Business Suite product family associated with the AMW module prefix, generally linked to the Advanced Product Catalog and related item/opinion materialized view infrastructure. Its principal business role in EBS 12.1.1 and 12.2.2 is twofold: to populate and maintain AMW materialized views through the DBMS_MVIEW refresh API, and to provide a maintenance entry point for purging the materialized view log and for returning row counts from arbitrary schema-qualified tables. The package is classified under the ETRM API classification OTHER, meaning it is not registered as a formal public API and is intended for internal, controlled use.

The package body header shows a version of 120.2 dated 2006, indicating it is a long-standing object carried forward across the 11i to 12.1.1 and 12.2.2 upgrades. The most consequential documented change is the disabling of the PURGE_LOG procedure, driven by Bug 4883995, which concerned limited access privileges to the underlying SYS data dictionary tables. This is a common pattern in EBS where direct queries against sys.dba_mviews were removed in favor of narrower, privilege-safe implementations.

Key Procedures and Functions

• GET_ROW_COUNT — A function that accepts a schema name and table name and returns the number of rows found in that table. It is implemented with dynamic SQL, composing a SELECT COUNT(1) statement against the concatenated schema and table identifiers and returning the scalar result. It is primarily a diagnostic or validation utility used to confirm data presence before or after processing.
• PURGE_LOG — A procedure that was intended to purge the AMW materialized view log. Its body has been reduced to a NULL operation. The comment at line 24 records that the procedure was disabled because of limited access privileges to the sys tables, referencing Bug 4883995. Callers can still invoke it, but it performs no work and returns without side effects.
• REFRESH_ALL — A procedure that refreshes AMW materialized views. When passed a specific materialized view name, it refreshes that view using DBMS_MVIEW.REFRESH with the complete refresh mode ('C'). When no name is supplied, it iterates a hard-coded list consisting of AMW_OPINION_LOG_MV and AMW_OPINION_MV, refreshing each with the same complete refresh mode. The original cursor-based loop over sys.dba_mviews was commented out for the same privilege reason noted in PURGE_LOG. Exceptions are written to the concurrent manager log via FND_FILE and surfaced through the standard errbuf/retcode interface.

Tables Accessed

The documented reference for this package lists DBMS_MVIEW only. DBMS_MVIEW is the Oracle-supplied materialized view management package, and REFRESH_ALL depends on it to perform complete refreshes of AMW_OPINION_MV and AMW_OPINION_LOG_MV. The original implementation additionally queried sys.dba_mviews, but that access path was commented out and is no longer active. GET_ROW_COUNT does not reference a fixed table; it constructs its query dynamically from the schema_name and table_name arguments, so the actual objects read depend entirely on the caller's input.

Usage Notes

AMW_PURGE_MVIEW_LOG is referenced by zero other packages according to the documented metadata, which indicates that it is not part of a larger call graph within the product code and is instead invoked directly by concurrent programs, request sets, or custom extension code. The presence of errbuf and retcode OUT parameters on PURGE_LOG and REFRESH_ALL confirms the standard concurrent program interface and strongly suggests both procedures are registered as concurrent program executables.

Because PURGE_LOG is a no-op, any purge automation that previously relied on it will complete without error but will not truncate or clear the materialized view log. Administrators should therefore verify whether an alternative purge mechanism is required. REFRESH_ALL should be scheduled during maintenance windows, since complete refreshes of the opinion materialized views can be resource intensive. The errbuf and retcode outputs, together with FND_FILE logging, provide the diagnostic information needed to determine whether a refresh completed successfully or failed with an unexpected error.