Search Results region_pk




Overview
Explore in AI Chat

Overview

EDW_GEOG_REGION_LCV is a reporting view shipped within the Oracle E-Business Suite Purchasing (PO) module, oriented toward the Enterprise Data Warehouse (EDW) extract layer. Its name follows the EDW naming conventions: the "_LCV" suffix denotes a "Local Code View," an extract-ready object intended to feed downstream warehouse or integration processes rather than to support transactional forms. In Oracle EBS 12.1.1 and 12.2.2, this view presents a consolidated, de-duplicated list of geographic regions expressed at the country level, drawn from multiple operational source tables that each independently store country information.

The view carries no application-level business logic beyond normalization. It converts each source row into a single synthetic region key by concatenating the COUNTRY value with a fixed literal string ('CUTY'), producing a derived key such as "US-CUTY". This is a common EDW technique for generating deterministic surrogate keys without sequence-based dependencies. Notably, the ETRM metadata records "Not implemented in this database" for this object, indicating the view is deployed conditionally, typically only when the EDW extract package is installed.

Underlying Base Objects

The view text is a UNION ALL over three distinct source queries, each joined to the EDW_LOCAL_INSTANCE table to stamp the instance code:

Each branch selects the same projection: country, surrogate key, instance code, creation and last-update audit dates, and five null user attribute placeholders. EDW_LOCAL_INSTANCE is an EDW infrastructure table, not an application base table, and supplies INSTANCE_CODE so that multi-organization or multi-instance extracts remain uniquely identifiable. The view performs no joins between the three address sources; consolidation occurs purely through UNION ALL, so a country appearing in all three sources yields three rows per source instance unless deduplicated downstream.

Key Columns

  • REGION_PK — The derived surrogate key, constructed as COUNTRY || '-' || 'CUTY'. This is the value the search term "user_attribute5" is returned alongside, as it is the nearest available key when no natural region identifier exists.
  • COUNTRY_FK and REGION_DP — Both carry the raw country code, serving as the foreign key to a country dimension and as the descriptive attribute respectively.
  • NAME — Also mapped to country, providing the display value for the region.
  • INSTANCE — The instance code from EDW_LOCAL_INSTANCE, identifying the originating EBS instance.
  • CREATION_DATE / LAST_UPDATE_DATE — Audit timestamps inherited from each source row, used for incremental extract watermarking.
  • USER_ATTRIBUTE1 through USER_ATTRIBUTE5 — Exposed as NULL in every branch of the UNION. These are descriptive-flexfield placeholders retained for schema compatibility with the EDW target table; they carry no data in this view.

Common Use Cases and Queries

The primary use case is populating a country-level geographic region dimension within the EDW, typically followed by a de-duplication step. A typical extract query is:

  • SELECT DISTINCT REGION_PK, COUNTRY_FK, NAME, INSTANCE FROM EDW_GEOG_REGION_LCV; — resolves the UNION ALL duplication into one row per country per instance.
  • SELECT * FROM EDW_GEOG_REGION_LCV WHERE COUNTRY_FK = 'US'; — retrieves all source-origin regions for a single country.
  • SELECT INSTANCE, COUNT(DISTINCT COUNTRY_FK) FROM EDW_GEOG_REGION_LCV GROUP BY INSTANCE; — reports country coverage per instance.
  • SELECT REGION_PK, USER_ATTRIBUTE5 FROM EDW_GEOG_REGION_LCV; — relevant to the search for "user_attribute5"; this column is always NULL and should not be relied upon for filtering or reporting.

Because the user attribute columns are hard-coded to NULL, any downstream logic expecting meaningful values in USER_ATTRIBUTE1–5 must be redirected to the actual source tables (PO_VENDOR_SITES_ALL, HR_LOCATIONS_ALL, HZ_LOCATIONS), where those flexfield columns hold real data.