Search Results country_fk




Overview
Explore in AI Chat

Overview

APPS.EDW_GEOG_REGION_LCV is a reporting-layer view in the Oracle E-Business Suite (12.1.1 and 12.2.2) Enterprise Data Warehouse (EDW) schema. The suffix "LCV" denotes a "Local Country View," a family of EDW objects designed to expose region and country reference data for dimensional reporting and extract-transform-load pipelines. The view collapses geographic information to the country level and presents a single, source-neutral representation of a country as a reporting "region."

Rather than describing geographic hierarchies, EDW_GEOG_REGION_LCV treats the country code itself as the region key. This makes it a lightweight dimension source for downstream EDW fact loads, geographic roll-up reporting, and cross-application integration where a common country key is required across Procurement, Human Resources, and Customer/Receivables data. Because the region identity is derived directly from the country code, no separate geography maintenance is needed to produce the region-level dimension.

Underlying Base Objects

The view is defined as a three-branch UNION ALL, each branch drawing from a different application source and cross-joining to EDW_LOCAL_INSTANCE. The documented base objects are:

  • PO_VENDOR_SITES_ALL — supplier site addresses (Procurement), contributing the "Supplier Site" branch.
  • HR_LOCATIONS_ALL — HR location records, contributing the "HR_LOCATIONS EXTRACT" branch.
  • HZ_LOCATIONS — the Trading Community Architecture (TCA) location table behind RA_ADDRESSES and customer addresses, contributing the "RA_ADDRESSES (customers, HZ_LOCATIONS) EXTRACT" branch.
  • EDW_LOCAL_INSTANCE — cross-joined in every branch to supply the INSTANCE_CODE value that identifies the source EBS instance.

In each branch, the COUNTRY column of the respective base table is the driving attribute. No documented foreign keys or lookup joins are declared; the union is composed purely of these base tables. The "CUTY" literal appended to the country value indicates the country-level ("Country") grain of the region key.

Key Columns

  • REGION_PK — concatenation of COUNTRY, a hyphen, and the literal 'CUTY'; the surrogate primary key for the region dimension.
  • COUNTRY_FK — the country code, exposed as the foreign key. This is the column returned when users search for "country_fk" and is the join key to country or geography dimensions.
  • REGION_DP — the descriptive/display value, set equal to COUNTRY.
  • NAME — the country name, also set to COUNTRY in this view.
  • INSTANCE — ELI.INSTANCE_CODE from EDW_LOCAL_INSTANCE, identifying the originating EBS instance.
  • CREATION_DATE / LAST_UPDATE_DATE — audit timestamps carried from the source base table of each branch.
  • USER_ATTRIBUTE1 through USER_ATTRIBUTE5 — reserved placeholders, all NULL, retained to satisfy the standard EDW dimension interface.

Common Use Cases and Queries

The principal use is populating or validating a country/region dimension and resolving country-level foreign keys. A typical query retrieving distinct countries with their region keys:

  • SELECT DISTINCT region_pk, country_fk, name, instance FROM apps.edw_geog_region_lcv;

To join EDW fact or staging data to the region dimension on the country foreign key:

  • SELECT f.*, g.region_pk FROM apps.edw_fact_table f JOIN apps.edw_geog_region_lcv g ON f.country_fk = g.country_fk;

Because the same country may appear from supplier sites, HR locations, and HZ locations, analysts frequently filter by source to avoid duplicate country rows, or aggregate with DISTINCT. The view is also used during EDW loads to reconcile country codes present in Procurement, HR, and TCA against a single instance-scoped list. Note that the view carries no zone or territory detail; all geographic reporting is intentionally flattened to the country grain.