Results for “edw_geog_postcode_lstg”

22 results




AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

EDW_GEOG_POSTCODE_LSTG is a staging table within the Oracle EBS Business Intelligence System (BIS) product family, owned by the POA schema. Its naming convention—the "_LSTG" suffix—identifies it as a landing or staging object used to receive inbound postal code (postcode) records from external or legacy sources before they are validated, transformed, and promoted into the enterprise geography warehouse structures. The table is documented as containing 18 columns in ETRM 12.1.1 and is explicitly flagged as "Not implemented in this database," indicating that in the reference system the object exists as a template definition rather than an instantiated table.

Because it functions purely as an intake buffer, the table carries control and audit columns typical of EBS staging interfaces (collection status, operation code, error code, request ID) alongside the core geography attributes. The heuristic Data Vault classification derived from the FK structure is standalone; from a modeling perspective, this object would most naturally be treated as a satellite-like staging artifact rather than a hub or link, since it holds descriptive postcode payload with transient load metadata rather than durable relationships between business keys.

Key Information Stored

The table's semantics are defined by the following documented columns:

  • POSTCODE_PK — the surrogate primary key for the postcode record; the primary identifier used to resolve and reference the postcode row.
  • POSTCODE_DP — the display (business) representation of the postcode string, the human-readable business-key candidate.
  • NAME — the descriptive name associated with the postcode geography.
  • STATE_REGION_FK / STATE_REGION_FK_KEY — foreign key reference and its associated key identifier linking the postcode to its parent state or region.
  • ROW_ID — a row-level identifier participating in a documented foreign key to CS_SYSTEMS_ALL_B_TEMP.
  • COLLECTION_STATUS — controls the state of the staging row within the collection/load cycle.
  • OPERATION_CODE — indicates the intended DML operation (insert, update, delete) to apply on promotion.
  • ERROR_CODE — captures validation or transformation failures for rejected rows.
  • REQUEST_ID — the concurrent program request that loaded the row, enabling traceability.
  • INSTANCE — identifies the source instance or collection instance from which the record originated.
  • CREATION_DATE / LAST_UPDATE_DATE — standard audit timestamps for record creation and modification.
  • USER_ATTRIBUTE1 … USER_ATTRIBUTE5 — five descriptive flexfield (DFF) columns reserved for client-specific extensions.

Common Use Cases and Queries

The primary use case is the bulk load and validation of reference postcode data, where operators monitor collection status and clear validation errors before promotion. Reporting and reconciliation queries typically target rows that failed validation or remain unprocessed:

  • Identify rejected rows: SELECT POSTCODE_PK, POSTCODE_DP, ERROR_CODE FROM EDW_GEOG_POSTCODE_LSTG WHERE ERROR_CODE IS NOT NULL;
  • Track a specific load batch: SELECT POSTCODE_DP, OPERATION_CODE, COLLECTION_STATUS FROM EDW_GEOG_POSTCODE_LSTG WHERE REQUEST_ID = :request_id;
  • Reconcile postcodes to their region: SELECT l.POSTCODE_DP, l.STATE_REGION_FK FROM EDW_GEOG_POSTCODE_LSTG l WHERE l.COLLECTION_STATUS = 'PENDING';

These patterns support data-quality dashboards, interface restart/recovery, and downstream geography dimension loads.

Related Objects

The documented FK relationship ties the staging table to a systems reference object, while the geography semantics imply dependencies on the standard geography tables. The most significant related objects are:

  • CS_SYSTEMS_ALL_B_TEMP — referenced by EDW_GEOG_POSTCODE_LSTG.ROW_ID, supplying the source system context for the load.
  • HZ_GEOGRAPHIES / HZ_LOCATIONS — standard EBS geography and location entities that consume validated postcode data.
  • HZ_GEOGRAPHY_IDENTIFIERS — stores the postcode identifier types assigned to geographic entities.
  • FND_CONCURRENT_REQUESTS — joins REQUEST_ID to the concurrent program execution that loaded the staging rows.
  • EDW_GEOG_STATE_REGION (or equivalent region staging) — resolves STATE_REGION_FK to the parent territorial hierarchy.

Because the object is documented as not implemented in the reference database, all related-object dependencies should be confirmed against the target instance's POA schema before use.