Search Results state_pk

Overview

EDW_GEOG_STATE_LCV is a read-only view owned by the APPS schema within the Oracle E-Business Suite (EBS) Enterprise Data Warehouse (EDW) layer. Its purpose is to consolidate geographic state, province, and region data drawn from multiple transactional source tables into a single, normalized list of values (LCV). The suffix "LCV" denotes List of Values, indicating the view is designed for reference and lookup consumption in reporting, extraction, and integration contexts rather than transactional DML. The view presents a derived concatenated key (STATE_PK) and an aggregated region foreign key (REGION_FK) that standardizes how state and country combinations are identified across heterogeneous EBS modules. In the configuration described for EBS 12.1.1 and 12.2.2, this object is typically consumed by data warehouse extractions, BI Publisher reports, and ETL processes requiring a unified geographic dimension. The view is instantiated through the EDW_LOCAL_INSTANCE table's INSTANCE_CODE column, which stamps each row with the originating EBS instance identifier, supporting multi-instance or multi-organization consolidation.

Underlying Base Objects

The view is defined as a UNION ALL across three distinct source extracts, each joined to EDW_LOCAL_INSTANCE to attach the instance code. The documented base objects are:

• PO_VENDOR_SITES_ALL (alias PVS): Supplies supplier site address geography. The view extracts STATE and PROVINCE for key derivation.
• HR_LOCATIONS_ALL (alias HRL): Supplies HR location geography, using REGION_2 to populate the state key and description columns.
• HZ_LOCATIONS (alias HZL): Supplies customer and address geography via RA_ADDRESSES and TCA party site data, using STATE and PROVINCE.
• EDW_LOCAL_INSTANCE (alias ELI): Contributes INSTANCE_CODE, identifying the source EBS instance for each unioned row.

Although the documented referenced base object list is empty, the view text explicitly names the four tables above, and each branch is labeled with an embedded comment identifying its extract family: "Supplier Site," "HR_LOCATIONS EXTRACT," and "RA_ADDRESSES (customers, HZ_LOCATIONS) EXTRACT."

Key Columns

• STATE_PK: A concatenated primary key composed of the decoded state (falling back to province when state is null) joined with the country by a hyphen. This provides a unique surrogate identifier for the geography.
• REGION_FK: A derived foreign key built as COUNTRY concatenated with '-CUTY' (country-unitity), providing a coarse geographic grouping. This is the column most relevant to the user's "region_fk" search, as it links state records upward to a country-level region dimension.
• STATE_DP: The display value, formatted as state(province) concatenated with the country in parentheses.
• NAME: Mirrors STATE_DP, used as the human-readable label in list-of-values pickers.
• INSTANCE: The source instance code from EDW_LOCAL_INSTANCE.
• CREATION_DATE / LAST_UPDATE_DATE: Timestamps carried from the respective source table for each union branch.
• USER_ATTRIBUTE1 through USER_ATTRIBUTE5: Null placeholders reserved for extensibility.

Common Use Cases and Queries

Typical uses include populating geography list-of-values, building state-to-region mappings for dashboards, and reconciling address geography across supplier, HR, and customer domains. A representative query retrieving distinct country-level regions is:

• SELECT DISTINCT region_fk, name FROM apps.edw_geog_state_lcv ORDER BY region_fk;

A query returning state keys grouped by region:

• SELECT region_fk, state_pk, name FROM apps.edw_geog_state_lcv WHERE region_fk = 'US-CUTY' ORDER BY state_pk;

Filtering by instance for multi-instance consolidation:

• SELECT instance, state_pk, region_fk FROM apps.edw_geog_state_lcv WHERE instance = :instance_code;

These patterns support ETL population of geographic dimensions and cross-module geography validation.