Results for “edw_geog_area1_lcv”

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

Overview

EDW_GEOG_AREA1_LCV is a reporting view owned by the APPS schema in Oracle E-Business Suite, documented under the Purchasing (PO) product family. It forms part of the Oracle Enterprise Data Warehouse (EDW) geography and local-instance abstraction layer used by ETRM (Enterprise Territory and Resource Management) and related analytics. The "_LCV" suffix denotes a "list of values" or localized code view, and the view exists to expose a single synthetic "ALL AREA1" record rather than a true hierarchical geography. This makes it suitable for reporting contexts that require the presence of a valid Area1 value even when no specific geography has been selected, effectively acting as a catch-all default dimension member.

Users searching for "area1_dp" typically encounter this view because AREA1_DP is the display column presented to users in LOV popups and reporting pickers. The view is marked VALID and is intended for read-only consumption by reporting, BI Publisher, and Discoverer-style workloads in EBS 12.1.1 and 12.2.2.

Underlying Base Objects

The view text selects exclusively from EDW_LOCAL_INSTANCE (aliased ELI), itself an EDW helper that identifies the local instance's code, creation date, and related metadata. The documented metadata lists no other base objects. Key derivations include:

• The primary key column AREA1_PK is a concatenation of the literal 'ALL' with '-ARE1', producing a constant identifier.
• The foreign key column ALL_FK is the literal string 'ALL'.
• The display column AREA1_DP and NAME both resolve to the literal 'ALL AREA1'.
• INSTANCE is sourced from ELI.INSTANCE_CODE.
• CREATION_DATE is taken from ELI.CREATION_DATE with an NVL fallback to SYSDATE.
• LAST_UPDATE_DATE and USER_ATTRIBUTE1 through USER_ATTRIBUTE5 are returned as NULL, deliberately placeholder columns to satisfy a uniform reporting interface contract.

The view therefore has no true join to a geography table; its "data" is a projection of the local instance's identity combined with hard-coded constants.

Key Columns

The following columns are exposed and carry specific meaning within the EDW reporting layer:

• AREA1_PK — Synthetic primary key equal to 'ALL-ARE1'; used as the join key when reporting against Area1 dimensions.
• ALL_FK — Constant foreign key 'ALL' used by aggregation or rollup queries.
• AREA1_DP — Display value shown in LOVs and report output; the column referenced by the user search term "area1_dp".
• NAME — Descriptive name, identical to AREA1_DP for this record.
• INSTANCE — Instance code identifying the EBS environment from EDW_LOCAL_INSTANCE.
• CREATION_DATE — Instance creation date, defaulting to SYSDATE if NULL.
• LAST_UPDATE_DATE, USER_ATTRIBUTE1-5 — Nullable placeholders retained for interface compatibility; they carry no data in this view.

Common Use Cases and Queries

Typical use cases include populating an Area1 LOV as a fallback entry, supplying a default dimension row in EDW fact queries, and validating instance context in reporting extracts.

Sample query to fetch the display value and key:

• SELECT area1_pk, area1_dp, instance FROM apps.edw_geog_area1_lcv;

Sample query to confirm environment context:

• SELECT instance, creation_date FROM apps.edw_geog_area1_lcv WHERE area1_dp = 'ALL AREA1';

Sample join usage treating the view as the Area1 dimension:

• SELECT f.*, g.area1_dp FROM my_fact f LEFT JOIN apps.edw_geog_area1_lcv g ON f.area1_fk = g.area1_pk;

Because it returns exactly one row, the view is inexpensive and safe to include in reporting joins without affecting cardinality. Query users should confirm the object is VALID in their environment and reference it under the APPS schema.