Search Results postcode_city_fk

Overview

The view EDW_GEOG_LOCATION_LCV is an Oracle E-Business Suite data extraction object belonging to the Purchasing (PO) product family. It is designed to present a consolidated, denormalized list of geographic locations drawn from multiple source systems within EBS, exposing them in a common column layout suitable for loading into an Enterprise Data Warehouse (EDW). The "LCV" suffix conventionally denotes a "List of Values" or canonical extraction view, and here it serves as a union of supplier sites, HR locations, and party sites. Each source contributes rows that are normalized into a shared set of columns such as LOCATION_PK, POSTCODE_CITY_FK, LOCATION_DP, LOCATION_ID, LOCATION_TYPE, and ORG_ID.

Because the view is documented as "Not implemented in this database," it exists as a reference definition rather than a deployed object in the ETRM environment. Its primary role is to feed downstream extraction programs, ETL mappings, and reporting layers with a single, consistent geographic location dimension spanning procurement and human resources data.

Underlying Base Objects

The view text defines a UNION ALL over three principal sources, each joined to the local instance reference table:

• EDW_LOCAL_INSTANCE (ELI) — joined in every branch to supply INSTANCE_CODE, identifying the source EBS instance.
• PO_VENDORS (POV) and PO_VENDOR_SITES_ALL (PVS) — the supplier site branch, linked by VENDOR_ID to retrieve vendor names alongside site address data.
• HR_LOCATIONS_ALL (HRL) — the HR location branch, which supplies internal organization location records.
• HZ_PARTY_SITES (HZPS) and related party location tables (e.g., HZ_LOCATIONS) — the party site branch, capturing trading community and customer site addresses.

Despite the metadata noting "Referenced base objects: none documented," the embedded view text clearly shows dependencies on the tables above. The union aligns differing source semantics into a single schema by hard-coding LOCATION_TYPE values of SUPPLIER_SITE, HR_LOCATION, and PARTY_SITE.

Key Columns

• LOCATION_PK — the synthetic primary key, constructed by concatenating the source identifier, ORG_ID or instance code, and location type.
• POSTCODE_CITY_FK — a composite geographic key concatenating city, postal code, region/state, and country. This is the column most relevant to searches such as "postcode_city_fk," and it enables joins on geographic grouping. Note the HR branch aliases it as POSTCODE_CODE_FK, a naming inconsistency worth handling in ETL.
• LOCATION_DP / NAME — descriptive labels; supplier branches concatenate the vendor site code with the vendor name.
• LOCATION_ID — the native source identifier (vendor site ID, location ID, or party site ID).
• LOCATION_TYPE — discriminates the origin of each row.
• ORG_ID — populated only for supplier sites; NULL for HR and party site rows.
• CREATION_DATE / LAST_UPDATE_DATE — audit timestamps, with the supplier branch using GREATEST of vendor site and vendor dates.

Common Use Cases and Queries

Typical use is geographic de-duplication and reporting across procurement, HR, and customer data.

SELECT LOCATION_ID, LOCATION_TYPE, POSTCODE_CITY_FK, NAME
FROM   EDW_GEOG_LOCATION_LCV
WHERE  POSTCODE_CITY_FK LIKE '%UK%';

SELECT LOCATION_TYPE, COUNT(*)
FROM   EDW_GEOG_LOCATION_LCV
GROUP  BY LOCATION_TYPE;

These queries support address cleansing, warehouse dimension building, and cross-module geographic analysis.