Search Results first_update_date




Overview
Explore in AI Chat

Overview

OPI_COLLECTION_HOOK_P is a PL/SQL package body owned by the APPS schema that supports the Oracle E-Business Suite Operational Intelligence (OPI) and Enterprise Data Warehouse (EDW) collection framework. Its central responsibility is to manage the pre- and post-collection processing steps that occur when Operational Intelligence fact tables are populated from source Online Transaction Processing (OLTP) data. In ETRM terminology this package is classified as an "OTHER" API rather than a formal public interface, indicating that it is an internal implementation utility rather than a supported integration point.

The package orchestrates the flow of change-tracking data from materialized view logs and staging fact tables into the EDW snapshot and collection log tables. It gathers optimizer statistics on the working tables, truncates and repopulates intermediate change capture tables, and coordinates the movement of non-attribute columns into temporary staging structures. This work is essential to the incremental refresh strategy used by OPI collections, ensuring that only changed rows flow through the extract-transform-load pipeline.

The package resides in APPS and references objects through APPS synonyms. The header comment in the source confirms active ownership around 2005, at which point the package was marked "noship." However, the same body is deployed and functional in Oracle EBS 12.1.1 and 12.2.2 environments, where it remains part of the OPI collection infrastructure.

Key Procedures and Functions

Seven documented procedures and functions make up the package. Their purposes are as follows:

  • POST_IPS_COLL — Executes post-collection processing for the Inventory Planning and Sales (IPS) fact. It resolves session context through fnd_installation.get_app_info, truncates the intermediate OPI_EDW_IDS_OPICOLLB_LOG, OPI_EDW_IDS_NET_CHG, and OPI_EDW_IPS_NET_CHG tables, gathers statistics, and loads distinct row identifiers from the materialized view log or daily status fact depending on the fact name supplied.
  • POST_MARGIN_COLL — Performs post-collection handling for the Margin fact, moving or reconciling changed records into margin collector log tables after the collection run.
  • PRE_MARGIN_COLL — Executes the preparation step for the Margin fact before collection begins, setting up the working data set and change identifiers used by the post step.
  • POST_REVENUE_COLL — Conducts post-collection processing for the Revenue fact.
  • POST_COGS_COLL — Conducts post-collection processing for the Cost of Goods Sold fact.
  • GATHER_STATS — Collects optimizer statistics for a named table. It is invoked internally (for example against OPI_EDW_INV_DAILY_STAT_F and OPI_EDW_IDS_OPICOLLB_LOG) to keep execution plans efficient during joins across staging and log tables.
  • TURNC_TAB — A utility that truncates a specified table, used to reset intermediate change-capture or log structures between collection runs.

Tables Accessed

The package reads and writes a set of OPI/EDW fact, log, and staging tables referenced through APPS synonyms. The primary fact tables include OPI_EDW_COGS_F, OPI_EDW_MARGIN_F, OPI_EDW_INV_DAILY_STAT_F, and OPI_EDW_INV_PERD_STAT_F, which hold the business measures being collected. Log and staging tables include OPI_EDW_COGS_LOG, OPI_EDW_IDS_NET_CHG, OPI_EDW_IPS_NET_CHG, and OPI_EDW_IDS_OPICOLLB_LOG, which carry row identifiers and net-change records through the pipeline. The package also reads 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 — to identify changed rows, and consults calendar tables EDW_TIME_CAL_PERIOD_LTC and EDW_TIME_M for period and time dimension mapping. The source receivable distribution table FII_AR_TRX_DIST_F supplies transaction-level revenue and receivable data.

Usage Notes

OPI_COLLECTION_HOOK_P is invoked automatically within the OPI/EDW collection cycle rather than being called directly by end users. The hooks are triggered by the collection framework before and after each fact's collection, using the procedures above. It is suitable for use by concurrent program infrastructure and internal EDW streams. Because it manipulates shared staging and log tables using TRUNCATE and statistic gathering, invocations must be serialized with respect to the relevant collection run. Custom code should not depend on this package's internals, as it is not a published API. The package is referenced by one other package within the same OPI collection family.

Regarding the search term "first_update_date": this package itself does not expose a documented first_update_date attribute. In the OPI/EDW collection context, first_update_date is typically a column found on source fact and dimension tables or audit columns used by the collection framework to track record currency. Users tracing first_update_date behavior should inspect the underlying OPI_EDW fact tables and their views rather than this hook package, which operates on row identifiers and change logs.