Search Results edw_fact_sv

Overview

APPS.EDW_FACT_SV is a PL/SQL package in the Oracle E-Business Suite (EBS) Applications schema (APPS), classified under the Enterprise Data Warehouse (EDW) family of objects. It is a metadata-driven utility that supports the EBS Enterprise Data Warehouse and business intelligence layer by programmatically generating database views over fact tables. In EBS 12.1.1 and 12.2.2, the EDW schema objects expose pre-built star-schema style views that external reporting tools and Oracle Business Intelligence Applications consume. EDW_FACT_SV serves as the "source view" (SV) generator for fact entities, translating the physical layout of a fact table plus its associated warehouse metadata into a consumable, decoded view definition. Rather than hand-coding each fact view, developers register metadata in EDW control tables and let this package (together with its sibling view generator, EDW_GEN_VIEW) emit the SQL. The object is documented as VALID in APPS and carries an API classification of OTHER, meaning it is an internal technical utility rather than a public, supported business API. It is referenced by only one other package, confirming its role as a narrowly scoped build-time helper in the EDW load/refresh pipeline.

Key Procedures and Functions

Two documented subprograms are exposed by the package specification:

• GENERATEVIEWFORFACT — The primary entry point. It assembles and (typically) creates or refreshes the database view corresponding to a given fact table, using the EDW metadata to resolve dimensions, decode clauses, and source columns. This is the routine invoked when a fact view must be built or regenerated after schema or metadata changes.
• GETDECODECLAUSEFORFLEXFK — A helper function that produces the decode/select clause fragment needed to translate a flexible-key (flexfield) foreign key into its human-readable or warehouse-conformed value. It centralises the logic for interpreting key flexfield segments so that generated fact views present decoded attribute values rather than raw segment codes.

No parameter lists are documented in the available metadata; the procedure names and their functional purpose are the authoritative facts supplied.

Tables Accessed

The package reads EDW metadata via APPS synonyms, driving its generation logic from configuration rather than hard-coded SQL:

• EDW_ATTRIBUTE_PROPERTIES — Attribute-level metadata (names, properties) used to build the projected column list of the fact view.
• EDW_FACT_FLEX_FK_MAPS — Maps fact-table foreign keys to their flexfield sources; consumed by GETDECODECLAUSEFORFLEXFK.
• EDW_FLEX_SEG_MAPPINGS — Segment-to-column mapping for key flexfields, supplying the segment detail required for decoding.
• EDW_SOURCE_VIEWS — Registry of source views, used to link fact definitions to their underlying source objects.

It also references the SYS-supplied DBMS_SQL package, indicating dynamic SQL construction, and PLITBLM, a PL/SQL table/array helper for in-memory collections. No business transaction tables are read or written; access is confined to EDW metadata plus dynamic execution.

Usage Notes

Because EDW_FACT_SV is an internal EDW utility, it is not surfaced through standard EBS forms or concurrent programs as a user-facing API. It is invoked during EDW build, refresh, or upgrade activities—either directly by EDW administration scripts or indirectly through the EDW_GEN_VIEW orchestration that references this package. The dynamic SQL approach (DBMS_SQL) means the generated view text depends entirely on current EDW metadata, so metadata must be correctly populated before invocation. Custom code should avoid calling this package directly; instead, follow the documented EDW generation flow. Its status is VALID across 12.1.1 and 12.2.2, and Oracle's legal notices designate the material proprietary and confidential, so any extension or replacement should be handled cautiously and re-validated after patch or upgrade.