Results for “edw_fact”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

EDW_COLLECTION_UTIL is a utility package in the APPS schema that forms part of the Oracle Enterprise Data Warehouse (EDW) collection and staging framework shipped with Oracle E-Business Suite. It provides the shared infrastructure used by the EDW collection programs to gather, transport and stage data extracted from EBS source tables before it is loaded into the warehouse or an external reporting repository. The package is classified as a utility (UTIL) rather than a business API, meaning it supplies generic plumbing — instance checks, staging table management, push scheduling, logging and lookups — that other EDW objects call rather than exposing end-user functionality itself. The source header identifies it internally as EDWSRCTS.pls, with a version constant that is populated from the RCS header string, and it is declared AUTHID CURRENT_USER so that privileged operations run under the invoking user's schema context. A large number of public procedures and functions are declared (33 documented entries), and the package is referenced by 87 other packages, which confirms its role as a foundational dependency of the EDW collection subsystem. The user search term "edw_fact" corresponds directly to a public constant declared in this package: edw_fact CONSTANT VARCHAR2 (10) := 'FACT';, which is one of two object-type markers (alongside edw_dim set to 'DIMENSION') used throughout the EDW code to distinguish fact tables from dimension tables during collection and staging.

Key Procedures and Functions

The package exposes setup routines and runtime helpers. The SETUP function is overloaded in several forms, accepting an object name and various combinations of primary-key view, missing-key view, transport flag, local and remote staging table names, and an exception message output, to initialise the collection context for a given EDW object. WRAPUP performs the corresponding completion and cleanup steps. IS_INSTANCE_ENABLED and SOURCE_SAME_AS_TARGET determine whether the current instance participates in collection and whether the source and target are the same database. GET_PUSH_GLOBALS retrieves the global parameters used during a push operation, while SET_PUSH_END_DATES, GET_LAST_PUSH_DATE and GET_LAST_PUSH_DATE_LOGICAL maintain and return the watermark dates that control incremental extraction. SET_WH_LANGUAGE, GET_WH_LANGUAGE, GET_WH_LOOKUP_VALUE, GET_LOOKUP_VALUE and GET_OBJECT_TYPE supply warehouse language, lookup and object-type resolution. STAGING_LOG writes staging activity records, SET_STATUS_READY marks objects as ready for processing, and TRUNCATE_STG clears staging tables. GET_STG_TABLE_NAMES and GET_DBLINK_NAMES resolve staging and database link names, CLEAN_UP removes residual state, and PUSH_TO_TARGET performs the actual transport of staged data to the target instance.

Tables Accessed

The package reads and writes a mix of EDW control tables and Oracle data dictionary views through APPS synonyms. EDW tables include EDW_LOCAL_INSTANCE and EDW_LOCAL_SYSTEM_PARAMETERS for instance and parameter configuration, and EDW_MAPPING_SEQ for mapping sequence control. Dictionary views include USER_TABLES, DBA_TABLES, ALL_TAB_COLUMNS, USER_USERS, USER_SEGMENTS, USER_TABLESPACES, GLOBAL_NAME and DUAL, used to discover table structure, ownership, sizing and database identity. Reference and profile data come from FND_LANGUAGES and FND_PROFILE_OPTION_VALUES, while DBMS_SQL and PLITBLM provide dynamic SQL and PL/SQL table support respectively.

Usage Notes

EDW_COLLECTION_UTIL is not invoked directly by end users. It is called by the EDW collection concurrent programs and by the 87 dependent packages that make up the collection framework, typically as the first step of an extract run (to resolve instance, language, watermark and staging context) and as the last step (to log, push and clean up). Because it is declared AUTHID CURRENT_USER, custom code that calls it must run with sufficient privileges on the dictionary and EDW tables. The edw_fact and edw_dim constants are used by callers to tag objects as fact or dimension tables when invoking SETUP and related routines, which is the reference most commonly encountered when searching for "edw_fact" within the EBS codebase.