Search Results push_to_local

Overview

OPI_EDW_OPI_RES_UTIL_F_C is the package body that implements the resource utilization fact collection logic for the Oracle E-Business Suite Operational Intelligence (OPI) data warehouse. Its role is to transform and stage resource utilization records so that the Enterprise Data Warehouse (EDW) collection engine can move them from the operational source into the OPI resource utilization fact (OPI_EDW_RES_UTIL_F). The module name and the _FSTG staging table indicate that this package handles the "fact staging" step of the extraction, transformation, and load (ETL) chain.

The package is catalogued under the APPS schema with an API classification of OTHER. It is closely tied to the OPI star-schema model centered on resource utilization, where each fact row references a resource, a locator, a transaction date, a unit of measure, and an instance, together with numeric measures such as actual resource usage and available resource. The package body carries the standard collector conventions used by OPI: private global variables for the push date range, row count, return code, and error buffer, and a header indicating the version shipped in the 11i/12.x code line (120.1, dated 2005).

Key Procedures and Functions

The documented interface exposes a single function:

• PUSH_TO_LOCAL — Populates the OPI resource utilization fact staging table and sets the COLLECTION_STATUS to 'LOCAL READY'. The metadata comment explains the design intent: when the source and target are the same instance, records that are still "in progress" must be distinguished from those ready to be picked up by the collection engine. Records remain in progress until all child processes have completed successfully, at which point they are marked local ready and become eligible for downstream collection.

The function selects from the staging structure using an ALL_ROWS hint and applies NVL(..., 'NA_EDW') to every key and dimension column, substituting a sentinel value wherever a foreign key or user attribute is null. This guarantees that the fact table never receives an empty key, which would otherwise break the star-schema joins. A single row is emitted per staged record.

Tables Accessed

• OPI_EDW_RES_UTIL_FSTG — the resource utilization fact staging table, accessed via an APPS synonym. This is the only documented table reference and the exclusive target of the INSERT performed by PUSH_TO_LOCAL. The inserted column list covers the fact keys (res_util_pk, locator_fk, res_fk, trx_date_fk, uom_fk, instance_fk), the five user foreign keys and five user measures, the primary measures act_res_usage and avail_res, the department and trx_date dimensions, the fifteen user attributes, the audit columns last_update_date and creation_date, and finally collection_status.

Usage Notes

The package is not intended for direct end-user invocation. It is executed by the OPI/EDW collection framework—typically a concurrent program or a scheduled collection cycle—that calls PUSH_TO_LOCAL with a from-date and to-date range. The private variables g_push_from_date, g_push_to_date, g_row_count, g_errbuf, and g_retcode support standard EBS concurrent manager logging: the return code and error buffer are surfaced to the concurrent program so that the request completes with a recognizable status.

Because the status value 'LOCAL READY' is the hand-off marker, any failure mid-run leaves records in the "in progress" state, which prevents partial data from being collected by a subsequent cycle. Referenced by one other package, it sits in the middle of the OPI resource utilization ETL chain rather than at either end. Developers extending the OPI model should treat the staging table as the integration point and invoke the wrapper collection program rather than calling the function directly, since the date-range parameters and status transitions are coordinated by the collector.