Search Results gather_stats

Overview

OPI_COLLECTION_HOOK_P is an Oracle EBS Applications (APPS) PL/SQL package that belongs to the Oracle Projects Intelligence / Enterprise Data Warehouse collection layer, commonly associated with the Operational Performance Intelligence (OPI) family of products. The package is declared with AUTHID CURRENT_USER, meaning that its SQL statements execute under the privilege domain of the invoking user rather than the package owner, a design pattern typical of ETL, staging, and data-collection utilities that must respect the caller's schema and synonym resolution.

Its documented purpose is to serve as a set of "hooks" invoked around the collection and transformation of fact data into the OPI Enterprise Data Warehouse tables. Rather than performing a single monolithic load, the package exposes discrete entry points that are called at defined points in the collection cycle — before and after margin collection, after IPS (Inventory, Purchasing, and Shipping) collection, after revenue collection, and after COGS (Cost of Goods Sold) collection. In addition, it provides two generic utility procedures for statistics gathering and table truncation. The $Header comment (OPICOLLS.pls 115.6, dated 2002/04/29) indicates the package has been stable in the EBS code line for many releases and is shared across the 11i, 12.1.1, and 12.2.2 codelines.

Key Procedures and Functions

The package exposes seven documented procedures:

• POST_IPS_COLL — Accepts a fact name and is invoked after the IPS (Inventory, Purchasing, Shipping) fact collection has completed, allowing post-processing logic keyed to the specific fact.
• POST_MARGIN_COLL — Invoked after margin fact collection; takes a base fact name to identify the affected margin collection.
• PRE_MARGIN_COLL — Invoked before margin fact collection, providing a pre-processing hook (no documented parameters).
• POST_REVENUE_COLL — Post-processing hook executed after revenue fact collection.
• POST_COGS_COLL — Post-processing hook executed after Cost of Goods Sold fact collection.
• GATHER_STATS — Accepts a table name and gathers optimizer statistics for that table; this is the procedure most directly associated with the search term "gather_stats" and is typically used immediately after a bulk load to refresh the data dictionary so the CBO has accurate cardinality information.
• TURNC_TAB — Accepts a table name and truncates the identified table, commonly used to clear staging or collection tables before a fresh load.

The presence of both GATHER_STATS and TURNC_TAB as public procedures is significant: it confirms this package is intended to be called explicitly by the surrounding collection framework rather than hiding these operations internally.

Tables Accessed

The package references a substantial set of EDW fact, log, and materialized-view log tables through APPS synonyms. The dimension and calendar tables EDW_TIME_CAL_PERIOD_LTC and EDW_TIME_M supply time-key context. The primary fact tables include OPI_EDW_COGS_F, OPI_EDW_MARGIN_F, OPI_EDW_INV_DAILY_STAT_F, OPI_EDW_INV_PERD_STAT_F, OPI_EDW_IPS_NET_CHG, and OPI_EDW_IDS_NET_CHG, which store cost, margin, inventory, and net-change measures.

Supporting transactional source tables include FII_AR_TRX_DIST_F (receivables transaction distributions). The materialized view logs MLOG$_FII_AR_TRX_DIST_F, MLOG$_OPI_EDW_COGS_F, MLOG$_OPI_EDW_INV_DAILY_ST, and MLOG$_OPI_EDW_MARGIN_F indicate the package participates in incremental refresh tracking. Log tables OPI_EDW_COGS_LOG, OPI_EDW_IDS_OPICOLLB_LOG, and OPI_EDW_IDS_NET_CHG provide audit and restartability data. Together these tables confirm the package sits at the boundary between operational source data and the OPI analytic model.

Usage Notes

OPI_COLLECTION_HOOK_P is not an end-user API. It is invoked by the OPI collection framework — typically concurrent programs and other PL/SQL packages performing fact loads — at the pre- and post-collection points named in its procedure list. The metadata confirms it is referenced by one other package, consistent with a single orchestrating collector.

Because of AUTHID CURRENT_USER, callers must have appropriate privileges and valid synonyms on the referenced tables. When the search term "gather_stats" is used, the relevant entry point is GATHER_STATS(P_TABLE_NAME); DBAs and developers should note it is a thin wrapper that must be passed a valid table name and is intended for post-load statistics refresh. Similarly, TURNC_TAB performs an unconditional truncate and should be used with caution in any custom or repeatable integration, since it is destructive and bypasses normal row-level delete auditing.