Search Results postcode_city_pk

Overview

EDW_GEOG_POSTCODE_CITY_LCV is a reporting view within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 environment, catalogued under the Purchasing (PO) product module. Its name follows the Enterprise Data Warehouse (EDW) naming convention commonly used in Oracle EBS analytics and data extraction layers, where the "_LCV" suffix denotes a "local" or lightweight consolidated view used for extract, transformation, and reporting purposes.

The view consolidates geographic location information — specifically normalized combinations of city, postal code (ZIP), state, province, and country — drawn from disparate source tables across multiple Oracle EBS modules. Its purpose is to present a single, unioned result set of postcode/city combinations that can be consumed by downstream reporting, data warehousing, or integration processes. The ETRM metadata records that this object is not implemented in the current database, indicating it is a documentation-only artifact or a view that exists in the EDW layer rather than the transactional EBS schema.

Underlying Base Objects

The view definition is a UNION ALL of three distinct extract segments, each sourced from base tables. Although the documented metadata lists "none documented" for referenced base objects, the view text reveals the following relationships:

• PO_VENDOR_SITES_ALL (PVS) — the supplier site address segment ("SUPPLIER SITE"). Supplies CITY, ZIP, STATE, PROVINCE, and COUNTRY for vendor locations.
• HR_LOCATIONS_ALL (HRL) — the HR location extract segment. Supplies TOWN_OR_CITY, POSTAL_CODE, REGION_2, and COUNTRY for internal organization locations.
• RA_ADDRESSES — the customer address segment, associated with HZ_LOCATIONS, supplying CITY, POSTAL_CODE, STATE, PROVINCE, and COUNTRY for customer and party addresses.
• EDW_LOCAL_INSTANCE (ELI) — joined in every segment to provide the INSTANCE_CODE, identifying the source EBS instance.

Each segment projects a consistent set of derived columns, allowing the union to function as a unified geography dimension source.

Key Columns

The view exposes a defined set of derived columns:

• POSTCODE_CITY_PK — a concatenated surrogate key combining city, ZIP, state/province, and country, used to uniquely identify each geography record.
• CITY_FK — a foreign-key style concatenation of city, state/province, and country.
• POSTCODE_FK — a foreign-key style concatenation of ZIP/postal code, state/province, and country.
• POSTCODE_CITY_DP — a display-formatted string presenting city with postal code and region in parentheses.
• NAME — mirrors the display format of POSTCODE_CITY_DP.
• INSTANCE — the sourced instance code from EDW_LOCAL_INSTANCE.
• CREATION_DATE / LAST_UPDATE_DATE — audit timestamps inherited from the source record.
• USER_ATTRIBUTE1–5 — reserved placeholder columns returned as NULL for extensibility.

A DECODE function ensures that when STATE is null, PROVINCE is substituted, normalizing regional data across geographies.

Common Use Cases and Queries

This view supports geography normalization, supplier/customer address analysis, and data warehouse dimension loading. A typical query might retrieve distinct city/postcode combinations for a given country:

• SELECT DISTINCT POSTCODE_CITY_PK, NAME FROM EDW_GEOG_POSTCODE_CITY_LCV WHERE POSTCODE_CITY_PK LIKE '%-US';
• Joining the view to supplier site facts using CITY_FK or POSTCODE_FK to enrich address reporting.
• Populating a geography dimension in an EDW or BI repository, using INSTANCE to segregate multi-instance data.

Because the object is not implemented in the transactional database, usage is confined to environments where the EDW extraction layer has been deployed and the documented view has been instantiated.