Search Results postcode_code_fk

Overview

APPS.EDW_GEOG_LOCATION_LCV is a consolidated "list of values" style view used within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 environment to present a unified geography/location dimension for reporting, data warehousing, and integration purposes. The "EDW" prefix indicates its role in the Enterprise Data Warehouse design pattern for EBS, where such views flatten and unify location information drawn from multiple functional schemas (Purchasing, Human Resources, and Trading Community/Receivables) into a common structure. The "LCV" suffix denotes a list-of-values collection view.

The view's primary objective is to expose a single, normalized set of location attributes — including a primary key, a composite postcode/city foreign key, address lines, a display descriptor, and an instance indicator — so that downstream extract processes and reports can reference any physical or party location through one consistent interface rather than resolving each source transaction individually.

Underlying Base Objects

The view is defined as a UNION ALL over three distinct source extracts:

• Supplier sites — joined across edw_local_instance (eli), po_vendors (pov), and po_vendor_sites_all (pvs), filtered on pvs.vendor_id = pov.vendor_id. Records are tagged with location type SUPPLIER_SITE.
• HR locations — joined across edw_local_instance and hr_locations_all (hrl), producing records tagged HR_LOCATION.
• Party sites — drawn from hz_party_sites (hzps) and its associated location table (hzl), tagged PARTY_SITE.

The edw_local_instance table is common to all three branches and supplies the instance_code that distinguishes the source EBS instance. The ETRM metadata does not enumerate these dependencies as formal base objects, but they are explicit in the view text.

Key Columns

• location_pk — the synthetic primary key, built as a concatenation of the source identifier (vendor_site_id, location_id, or party_site_id), the instance code, and the location type.
• postcode_city_fk / postcode_code_fk — the composite geography foreign key. For supplier sites the alias is postcode_city_fk (city-zip-state/province-country); for HR locations the documentation explicitly names it postcode_code_fk (town_or_city-postal_code-region_2-country). This is the column most relevant to users searching on "postcode_code_fk", as it provides the standardized postal-code-driven join key to a geography or postal-code reference dimension.
• address_line_1 through address_line_3 — free-form address components; address_line_4 is exposed as NULL for uniformity.
• location_dp / name — the display descriptor and display name, typically the site code plus vendor name or the HR location code.
• instance — the instance code from edw_local_instance.
• location_id — the source-specific identifier.
• location_type — one of SUPPLIER_SITE, HR_LOCATION, or PARTY_SITE.
• org_id — populated for supplier sites; NULL for HR and party sites.
• creation_date / last_update_date — audit columns, with supplier site dates using the greatest of the vendor and site last-update timestamps.

Common Use Cases and Queries

The view is typically used to resolve a canonical location record for reporting, to validate that a postal code exists across all source systems, or to feed an EDW staging table. Representatives may use postcode_code_fk to correlate HR location records with a postal-code dimension. A sample query filtering by location type and postal key:

• SELECT location_pk, postcode_code_fk, name, instance FROM apps.edw_geog_location_lcv WHERE location_type = 'HR_LOCATION';
• SELECT location_pk, name, org_id FROM apps.edw_geog_location_lcv WHERE location_type = 'SUPPLIER_SITE' AND org_id = :p_org_id;
• SELECT location_pk, postcode_city_fk, address_line_1 FROM apps.edw_geog_location_lcv WHERE postcode_city_fk LIKE 'SW1A%';

Because the view spans three source schemas, queries should filter on location_type or instance to control performance and avoid duplicate conceptual matches across suppliers, employees, and parties.