Search Results gl_acct




Overview
Explore in AI Chat

Overview

The APPS.EDW_FACT_SV package body is a component of the Oracle E-Business Suite Enterprise Data Warehouse (EDW) view generation framework. Its primary responsibility is to dynamically generate and manage the SQL views that project source table structures into fact-oriented collections used by the EDW and Daily Business Intelligence (DBI) reporting layers. The package operates against the EDW metadata repository, resolving the naming conventions assigned to facts and mapping their flexfield foreign key relationships into executable view definitions.

Because the package body originates from a 2005 source header ($Header: EDWVFCTB.pls 120.0), it is a long-standing component that carries forward unchanged into EBS 12.1.1 and 12.2.2. It is owned by APPS and classified as OTHER, indicating it is not a public API in the traditional sense but an internal generator utility invoked during EDW implementation and metadata refresh operations.

Key Procedures and Functions

The ETRM documentation identifies two public procedures or functions within this package body:

  • GENERATEVIEWFORFACT — The principal entry point. Given a fact name, this procedure establishes the flex view, generated view, and collection view names for the fact by querying the EDW source view registry, then emits the SQL text that defines the fact's collection view. It drives the orchestration of the generated view text, accumulating statements through the package-level t_view_text_table collection and signalling failure through edw_gen_view.g_success and edw_gen_view.g_error when required view names are not seeded.
  • GETDECODECLAUSEFORFLEXFK — Resolves the decode clause associated with a flexfield foreign key. This is the routine most directly associated with a user_fk lookup, as it translates the flexfield foreign key designation into the CASE/DECODE SQL fragment used to join and label the fact's dimension members within the generated collection view.

The package body also contains internal helper logic such as getViewNameForFact, which fetches the three view names from EDW_SOURCE_VIEWS and raises edw_gen_view.viewgen_exception when any is null. No public parameter lists are documented, and none should be assumed.

Tables Accessed

The following tables are accessed through APPS synonyms:

  • EDW_SOURCE_VIEWS — Holds the flex, generated, and collection view names keyed by object name and version; this is the primary lookup table for GENERATEVIEWFORFACT.
  • EDW_FACT_FLEX_FK_MAPS — Maps fact flexfield foreign keys to their decode clauses; consumed by GETDECODECLAUSEFORFLEXFK.
  • EDW_FLEX_SEG_MAPPINGS — Defines segment-level mappings used when building flexfield decode expressions.
  • EDW_ATTRIBUTE_PROPERTIES — Supplies attribute metadata used during view text generation.
  • DBMS_SQL and PLITBLM — Oracle-supplied packages referenced for dynamic SQL execution and PL/SQL table handling.

Usage Notes

EDW_FACT_SV is an internal generator rather than a user-facing API. It is referenced by one other package in the EDW view generation family and is typically invoked indirectly during EDW setup, metadata synchronization, or view regeneration cycles. Administrators may invoke it from custom PL/SQL during EDW customization when new facts are introduced, but the standard path is through the EDW/DBl concurrent program infrastructure and the parent view generator. Because diagnostic output is directed to /sqlcom/log via l_directory, troubleshooting should begin with that directory. The package is version-sensitive: view names must be seeded in EDW_SOURCE_VIEWS at the current edw_gen_view.g_version before generation proceeds.