Search Results region_2




Overview
Explore in AI Chat

Overview

The view APPS.EDW_GEOG_POSTCODE_CITY_LCV is an Oracle E-Business Suite 12.1.1 / 12.2.2 data-warehouse staging object that consolidates geographic location data, specifically the combination of city, postal code, and region/state information, into a single denormalized set. It is a "LCV" (Load/Collection View) style object typically consumed by the E-Business Tax (EBTax) and Enterprise Data Warehouse (EDW) geography extraction processes. The view presents one unified "postcode-city" key derived from multiple transactional source contexts — supplier sites, HR locations, and customer/party addresses — so that downstream geography loaders can resolve a single canonical geographic identifier regardless of originating module. It plays no direct role in OLTP transaction processing; its purpose is to feed ETL/reporting layers that map transaction addresses to geography reference data.

Underlying Base Objects

The view is defined as a UNION ALL of three address extracts, each joined to EDW_LOCAL_INSTANCE (aliased eli) to stamp the instance code:

  • Supplier Site extract — sourced from PO_VENDOR_SITES_ALL pvs, using city, zip, state, province, and country.
  • HR_LOCATIONS extract — sourced from HR_LOCATIONS_ALL hrl, using town_or_city, postal_code, region_2, and country.
  • RA_ADDRESSES / HZ_LOCATIONS extract — sourced from the Receivables/customer address tables, using city, truncated postal_code (substrb(postal_code,1,57)), state, province, and country.

Because the object is a view over these three unions, the documented metadata lists no independent base objects beyond those named in the view text. The region_2 column appearing in the HR_LOCATIONS branch is the HR representation of the state/region attribute; in the supplier and customer branches the equivalent role is served by state with a province fallback via decode(state, null, province, state).

Key Columns

  • postcode_city_pk — composite surrogate key: city-'zip'-region-country. In the HR branch, the region element is region_2.
  • city_fk — foreign key form city-'region'-country.
  • postcode_fk — foreign key form zip/postal_code-'region'-country.
  • postcode_city_dp / name — display form city(zip,region).
  • instance — eli.instance_code, the local instance identifier.
  • creation_date / last_update_date — audit columns from the source record.
  • user_attribute1..5 — reserved, currently NULL.

Common Use Cases and Queries

Typical scenarios include geography reference loading, address de-duplication, and joining transactional addresses to a normalized postcode/city dimension. A representative query filtering on the HR region attribute sought by the user would be:

SELECT postcode_city_pk, city_fk, postcode_fk, name, instance FROM apps.edw_geog_postcode_city_lcv;

Because the union exposes region_2 only through the HR_LOCATIONS branch (encoded inside the composite keys), callers searching for a specific region should generally resolve it through city_fk or postcode_fk rather than a standalone region_2 column, which is not projected in the outer SELECT list.