Search Results state_dp

Overview

EDW_GEOG_STATE_LCV is an Oracle E-Business Suite (EBS) 12.1.1 / 12.2.2 view owned by the APPS schema and registered under the Purchasing (PO) product family. The suffix "LCV" denotes a "localization" or "list of values" style view, in this case defining a state/province dimension for the Oracle Enterprise Data Warehouse (EDW). The view is designed to present a de-normalized, consolidated list of geographical "state" values drawn from several distinct EBS source entities — supplier sites, HR locations, and customer (HZ) locations — and to normalize them into a single, reporting-friendly structure.

Its principal role is to feed operational data store (ODS) and dimensional schemas (for example the geography or region dimensions used by EDW and Discoverer/OBIA-style reporting). Rather than requiring a report author to reconcile the differing column names used for state-level data across Purchasing, Human Resources, and Receivables, EDW_GEOG_STATE_LCV exposes a single unified set of columns such as STATE_PK, STATE_DP, NAME, and REGION_FK. The user search term "state_dp" corresponds directly to the STATE_DP column, which is the display value for the state dimension element.

Underlying Base Objects

According to the documented view text, EDW_GEOG_STATE_LCV is defined as a UNION ALL of three extraction blocks, each keyed by the instance identifier returned from EDW_LOCAL_INSTANCE:

• PO_VENDOR_SITES_ALL (PVS) — with the comment "SUPPLIER SITE". Supplier site addresses supply the state/province and country values for supplier-based geography.
• HR_LOCATIONS_ALL (HRL) — with the comment "HR_LOCATIONS EXTRACT". Here the state value is sourced from the REGION_2 column, reflecting the HR location model.
• HZ_LOCATIONS (HZL) — with the comment "RA_ADDRESSES (CUSTOMERS, HZ_LOCATIONS) EXTRACT". Customer and party location records provide state and country.
• EDW_LOCAL_INSTANCE (ELI) — joined in each block to supply the INSTANCE_CODE, identifying the source instance for multi-instance/global consolidation.

The documentation lists no referenced base objects in the standard metadata section; the authoritative base object list is therefore inferred from the view text above. The UNION ALL pattern preserves all rows from all three sources without de-duplication, so the same logical state may appear once per contributing entity — a behavior relevant when aggregating.

Key Columns

• STATE_PK — the concatenation of the state (or province, when state is null) with the country, forming a composite primary key string. The DECODE ensures province is used as a fallback.
• STATE_DP — the search term. It is the display value built as state(or province) concatenated with the country in parentheses, e.g. "California(US)". This is the human-readable label used in LOVs and reports.
• NAME — an alias carrying the same expression as STATE_DP, provided for name-based lookups.
• REGION_FK — a synthetic foreign key formed as country plus the literal "CUTY", representing a country/region linkage.
• INSTANCE — the EDW_LOCAL_INSTANCE.INSTANCE_CODE, supporting multi-org/multi-instance reporting.
• CREATION_DATE / LAST_UPDATE_DATE — lineage timestamps inherited from each source table.
• USER_ATTRIBUTE1–5 — placeholder columns, all returned as NULL, provided for extensibility and consistent dimension shape.

Common Use Cases and Queries

The view is typically used to build geography dimensions or to populate a state LOV in custom reports. A straightforward query retrieving the display value and key is:

SELECT STATE_PK, STATE_DP, NAME, REGION_FK, INSTANCE FROM APPS.EDW_GEOG_STATE_LCV ORDER BY STATE_DP;

Because STATE_DP embeds the country, it is useful for filtering by country substring, e.g. WHERE STATE_DP LIKE '%(US)'. To reconcile duplicate states across sources, an aggregation such as SELECT DISTINCT STATE_DP FROM APPS.EDW_GEOG_STATE_LCV is common. Where instance-level reporting is required, WHERE INSTANCE = :instance_code isolates a specific source system. Lastly, joining this view to fact tables on STATE_PK provides the standard dimension join pattern in EDW-style reporting.