Search Results hz_geography_identifiers




Overview
Explore in AI Chat

Overview

HZ_GEOGRAPHY_IDENTIFIERS is a Receivables (AR) schema table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores the multiple identifiers used to describe the same geography. A single geography record—such as a country, state, province, city, or postal code—can be known by more than one external code or naming convention, depending on the source system, data provider, or regional standard. This table captures those alternate representations so that geographic reference data can be reconciled across feeds, integrations, and Oracle applications without duplicating the underlying geography itself.

The table resides in the AR (Receivables) schema and holds a documented status of VALID. It carries a single documented foreign key: LANGUAGE_CODE references FND_LANGUAGES, indicating that identifiers can be language-sensitive. From a heuristic Data Vault modeling perspective, the mined foreign key structure classifies this object as satellite-leaning, meaning it functions primarily as a descriptive, attribute-bearing table attached to a parent geography entity rather than as a standalone hub or an associative link between independent hubs. This classification is a suggestion derived from key structure and should be validated against the actual referential model before being used for data-warehouse design.

Key Information Stored

The table contains 21 documented columns. The most significant are:

  • GEOGRAPHY_ID — The identifier linking each row to its parent geography. It is the first column of the documented unique index and the primary join key to the geography master.
  • IDENTIFIER_TYPE — The category of identifier being recorded (for example, a standard code versus a provider-specific code).
  • IDENTIFIER_SUBTYPE — A finer classification within the identifier type.
  • IDENTIFIER_VALUE — The actual code or textual identifier value used to describe the geography.
  • LANGUAGE_CODE — The language in which the identifier applies; foreign key to FND_LANGUAGES.
  • PRIMARY_FLAG — Indicates whether this identifier is the primary representation for the geography.
  • GEO_DATA_PROVIDER — The source or provider of the geographic identifier data.
  • GEOGRAPHY_USE and GEOGRAPHY_TYPE — Contextual attributes describing how and what type of geography the identifier belongs to.

The documented unique index, HZ_GEOGRAPHY_IDENTIFIERS_U1, covers GEOGRAPHY_ID, IDENTIFIER_TYPE, IDENTIFIER_SUBTYPE, IDENTIFIER_VALUE, and LANGUAGE_CODE. This composite constitutes the business-key candidate, ensuring that a given identifier value for a given geography, type, subtype, and language is unique. The surrogate primary key is the row-level system identifier, distinct from these business-key columns. Standard audit columns are present—CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER—along with concurrent-program context columns such as APPLICATION_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, PROGRAM_LOGIN_ID, and REQUEST_ID.

Common Use Cases and Queries

Typical uses include reconciling external geographic feeds, resolving an inbound identifier to the correct geography, and reporting all known identifiers for a geography. A common query pattern resolves a code to its geography:

  • SELECT geography_id, identifier_type, identifier_value FROM hz_geography_identifiers WHERE identifier_value = :code AND language_code = USERENV('LANG');
  • SELECT identifier_value FROM hz_geography_identifiers WHERE geography_id = :geo_id AND primary_flag = 'Y';
  • Joining to FND_LANGUAGES to retrieve the language description: SELECT g.identifier_value, l.description FROM hz_geography_identifiers g, fnd_languages l WHERE g.language_code = l.language_code;

Reporting scenarios include validating provider-mapping completeness and detecting geographies that lack a designated primary identifier.

Related Objects

The most significant related objects include:

These joins enable complete resolution and reporting of geographic identifiers within the Receivables geography model.