Search Results get_time

Overview

EDW_METADATA_REFRESH is an Oracle E-Business Suite PL/SQL package body owned by the APPS schema and classified under the ETRM metadata as OTHER. Its principal responsibility is to refresh the metadata tables that support the Enterprise Data Warehouse (EDW) and Oracle Warehouse Builder (OWB) integration layer within EBS. The package extracts metadata from the OWB repository and synchronizes it into the operational tables used by the EDW reporting and dimensional modeling components.

The package body carries the header identifier EDWMDRFB.pls, version 115.17, dated 2004/04/06. The code explicitly detects whether the underlying repository is OWB 9i or OWB 211; when the repository is OWB 211, the refresh logic is bypassed entirely. Only in the OWB 9i environment does the package proceed with metadata synchronization. This version-gating behavior is central to understanding when the package performs work.

Key Procedures and Functions

• REFRESH_METADATA_TABLES — The main entry point. It accepts Errbuf and Retcode out parameters, opens the log file, resolves the OWB schema (defaulting to EDWREP when the profile value is null), resolves the BIS owner, and orchestrates the downstream refresh steps.
• GET_DB_USER — Resolves the database user associated with a given schema or logical owner, used here to identify the BIS owner. This is the routine related to the user's "get_time" search context.
• GET_TIME — Returns a formatted timestamp value used throughout the refresh cycle for logging and audit purposes. It complements the direct calls to FND_DATE.DATE_TO_DISPLAYDT seen in the source.
• LOG — Writes diagnostic and progress messages to the log file opened by OPEN_LOG_FILE.
• OPEN_LOG_FILE — Initializes the output log destination for the refresh run.
• DROP_TABLE — Removes a target table prior to repopulation.
• POPULATE_PVT_TABLES — Populates the private (staging) tables that hold refreshed metadata before final publication.
• ANALYZE_ALL — Gathers statistics on the refreshed tables to support the optimizer.
• TRUNCATE_ALL — Empties target metadata tables as a precursor to reload.
• REFRESH_OWB_MV — Refreshes the OWB materialized views that expose repository metadata.

Tables Accessed

The package references two documented objects through APPS synonyms. USER_OBJECTS is queried to determine whether ALL_IV_DIMENSIONS exists, which serves as the OWB 9i versus OWB 211 discriminator; if the count is zero, the refresh exits without action. DBMS_SNAPSHOT is invoked to refresh the OWB materialized views, consistent with the REFRESH_OWB_MV routine. The package also reads FND profile options such as EDW_OP_TABLE_SPACE and calls FND_ORACLE_SCHEMA.GETOUVALUE and AD_TSPACE_UTIL routines to resolve the operational tablespace under the new tablespace mode.

Usage Notes

EDW_METADATA_REFRESH is referenced by one other package, indicating it is invoked programmatically rather than directly by end users. In practice it is driven as part of the EDW/OWB metadata synchronization cycle, typically scheduled through a concurrent program or a wrapper that supplies Errbuf and Retcode. The Errbuf NULL assignment and Retcode of 0 signal successful completion to the calling concurrent manager. Administrators should confirm the OWB version before expecting any refresh activity, since OWB 211 environments cause the procedure to return immediately. Because the package manipulates tablespace assignments and drops or truncates tables, it should be run only during a controlled maintenance window and with the OWB and BIS schemas properly configured.