Search Results edw_geog_postcode_city_ltc_iv

Overview

The view EDW_GEOG_POSTCODE_CITY_LTC_IV belongs to the BIS (Business Intelligence System) product family within Oracle E-Business Suite, specifically the Applications BIS module. It is an Enterprise Data Warehouse (EDW) style interface view exposing postcode-to-city geography reference data in a denormalized, integration-ready form. The suffix conventions (_LTC for the base staging table, _IV for the interface view) indicate that this object is part of the ETRM geographic data model used to stage and publish location hierarchies for downstream reporting, data warehousing, and third-party data exchange.

The view is documented as not implemented in this database, meaning it is delivered as part of the ETRM metadata but is not physically created in every EBS instance. Where it does exist, its role is to surface postcode and city records together with surrogate keys, user-defined flex attributes, and a packaged row-level OPERATION_CODE column that supports incremental load and change-data-capture patterns typical of EDW extracts.

Underlying Base Objects

The ETRM documentation identifies a single source object: EDW_GEOG_POSTCODE_CITY_LTC. The view is a thin projection over this staging table, selecting every column from the base and augmenting it with two derived expressions:

• ROWID aliased as ROW_ID, providing a physical row locator for the underlying staging record.
• A literal space (' ') aliased as OPERATION_CODE, a placeholder column reserved for the operation type (for example insert, update, or delete) populated by downstream extract processes.

No additional joins, aggregations, or filter predicates are applied. This preserves the staging table's grain — one row per postcode/city relationship — and keeps the interface view suitable for direct bulk extraction without transformation overhead.

Key Columns

• POSTCODE_CITY_PK — Primary key of the postcode-city staging record.
• POSTCODE_CITY_PK_KEY — Surrogate or alternate key representation of the same entity, used by the EDW for dimensional loading.
• POSTCODE_FK — Foreign key reference to the postcode dimension. This is the column most directly associated with the user's search term, postcode_fk, and is central to joining geography facts back to their postcode master.
• POSTCODE_FK_KEY — Keyed (warehouse-ready) form of the postcode foreign key.
• CITY_FK and CITY_FK_KEY — Foreign key to the city dimension and its keyed counterpart.
• NAME — Descriptive name of the postcode/city entity.
• INSTANCE — Identifies the source EBS instance or organization context, enabling multi-instance consolidation.
• POSTCODE_CITY_DP — Data processing or datapoint indicator controlling publication state.
• CREATION_DATE / LAST_UPDATE_DATE — Audit timestamps supporting incremental and delta extraction.
• USER_ATTRIBUTE1 through USER_ATTRIBUTE5 — Descriptive flexfield-style extension columns for customer-specific geography attributes.
• OPERATION_CODE — Placeholder operation indicator appended by the view definition.

Common Use Cases and Queries

Typical consumption patterns include loading a postcode-to-city geography dimension into an external warehouse, validating referential integrity of POSTCODE_FK values, and extracting incremental changes based on LAST_UPDATE_DATE.

Retrieve all postcode-city mappings for a given postcode:

• SELECT POSTCODE_CITY_PK, POSTCODE_FK, CITY_FK, NAME FROM EDW_GEOG_POSTCODE_CITY_LTC_IV WHERE POSTCODE_FK = :p_postcode_fk;

Extract records changed since the last load window:

• SELECT ROW_ID, POSTCODE_FK_KEY, CITY_FK_KEY, OPERATION_CODE FROM EDW_GEOG_POSTCODE_CITY_LTC_IV WHERE LAST_UPDATE_DATE >= :last_run_date;

Validate that every postcode foreign key resolves to a known city key:

• SELECT POSTCODE_FK, COUNT(*) FROM EDW_GEOG_POSTCODE_CITY_LTC_IV GROUP BY POSTCODE_FK HAVING COUNT(DISTINCT CITY_FK_KEY) = 0;

Because the view performs no joins or filtering, it is inexpensive to query and well suited for parallel, full-table EDW extraction. Effective use depends on confirming the object exists in the target instance, since ETRM documents it as not implemented in the reference database.