Search Results edw_gen_view




Overview
Explore in AI Chat

Overview

EDW_GEN_VIEW is an Oracle E-Business Suite PL/SQL package owned by the APPS schema that supports the Enterprise Data Warehouse (EDW) generator framework. Its principal business function is the dynamic construction and creation of database views that materialize the flexfield-aware column projections used by EDW's dimension and fact staging structures. Rather than requiring a developer to hand-author a view for every generated star-schema component, EDW_GEN_VIEW builds a view statement programmatically from metadata stored in the EDW mapping tables, translating key flexfield and descriptive flexfield segments into their properly qualified columns and decode expressions.

The package is classified as OTHER within the ETRM API classification, meaning it is an internal infrastructure utility rather than a public, published API. It is a component of the EDW warehouse generation toolchain and is invoked by the surrounding EDW service packages (EDW_DIM_SV, EDW_FACT_SV) and by EDW_UPDATE_ATTRIBUTES during the generation and refresh lifecycle. The package body is marked VALID, and the package specification exposes twenty-two documented procedures and functions.

Key Procedures and Functions

  • GENERATE — The top-level orchestrator that drives view generation for a supplied target, coordinating the lower-level builder procedures.
  • BUILDVIEWSTMT — Assembles the complete SQL text of the CREATE VIEW statement from the mapped columns and flex expressions.
  • CREATEVIEW and CREATELONGVIEW — Execute the generated DDL against the database; the "LONG" variant handles large statement text that exceeds conventional string limits.
  • GETCOLUMNMAPS — Retrieves the mapping definitions that determine how source columns are projected into the generated view.
  • FORMSEGMENTNAME and GETCONTEXTCOLFORFLEX — Derive the runtime column names for flexfield segments, including the context-sensitive column for descriptive flexfields.
  • GETDECODECLAUSEFORFLEXCOL and GETNVLCLAUSE — Produce the decode and NVL expressions required to resolve flexfield values and substitute defaults.
  • GETFLEXPREFIX — Returns the naming prefix convention used to qualify flexfield columns.
  • GETCOLUMNCOUNTFORVIEW — Determines the number of columns the generated view will expose.
  • GETAPPSSCHEMA, GETAPPLSYSSCHEMA, and GETAPPSVERSION — Return environment context (schema names and application version) used to build qualified references and version-appropriate SQL.
  • GETUTLFILEDIR — Resolves the UTL_FILE directory used for generated output.
  • WRITELOG, WRITEOUT, and WRITEOUTLINE — Logging and file-output utilities for diagnostics and generated script capture.
  • INDENTBEGIN and INDENTEND — Formatting helpers that manage indentation of the emitted SQL text.

Tables Accessed

The package reads its generation metadata primarily from EDW_ATTRIBUTE_MAPPINGS, EDW_FACT_FLEX_FK_MAPS, EDW_FLEX_ATTRIBUTE_MAPPINGS, and EDW_FLEX_SEG_MAPPINGS, which together define the attribute, flex, and foreign-key mappings that drive column construction. EDW_SV_FLEX_ASSIGNMENTS supplies flex assignment definitions. USER_VIEWS is queried to detect existing views, and V$PARAMETER is read to determine database configuration relevant to generated SQL. DUAL supports scalar lookups. The package relies on the DBMS_SQL and UTL_FILE built-in packages for dynamic SQL execution and file output, and references STANDARD. All EDW tables are reached through APPS synonyms.

Usage Notes

EDW_GEN_VIEW is not exposed to end users through Oracle Forms. It is invoked programmatically during EDW warehouse generation and refresh, principally from EDW_DIM_SV and EDW_FACT_SV, which call it to create the views backing generated dimensions and facts. EDW_UPDATE_ATTRIBUTES also references the package to regenerate views when attribute mappings change. Custom code may call the package directly, but because it issues DDL and depends on the EDW mapping tables, it should be invoked only within the controlled generation process. The API classification is OTHER, so it carries no supported public interface guarantee.