Search Results hz_geography_identifiers_u1

Overview

HZ_GEOGRAPHY_IDENTIFIERS is a transaction data table owned by the AR schema in Oracle E-Business Suite Release 12.1.1 and 12.2.2. It stores the multiple identifiers that may be used to describe a single geography record. A geography, such as a country, state, city, or a subsection of a city, can be known by more than one name or code, and this table provides the repository in which all of those alternate representations are held. Values may originate from external geography data providers or be entered directly by users. Identifiers include names, such as alternate names or vanity names, and codes, such as ISO country codes or UN/LOCODE location codes. The documentation illustrates this concept with the example of a section of Redwood City that carries the vanity name "Redwood Shores."

The ETRM metadata records this object with a status of VALID and an FND Design Data reference of AR.HZ_GEOGRAPHY_IDENTIFIERS. Its physical storage resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10. Based on heuristic analysis of the foreign key structure, the table leans toward a satellite classification in a Data Vault model, suggesting it is best modeled as a descriptive, dependent structure attached to a central geography entity rather than as a standalone hub or a linking table.

Key Information Stored

The table contains 21 documented columns. The most significant are summarized below.

• GEOGRAPHY_ID (NUMBER, 15) — Unique identifier for the geography to which the identifier belongs. This column participates first in the unique index and serves as the principal linkage to the parent geography entity.
• IDENTIFIER_TYPE (VARCHAR2, 30) — Specifies whether the value held in IDENTIFIER_VALUE is a code or a name.
• IDENTIFIER_SUBTYPE (VARCHAR2, 30) — Further qualifies the type of a code or a name.
• IDENTIFIER_VALUE (VARCHAR2, 360) — Stores the actual value of the identifier, such as an alternate name or an ISO code.
• LANGUAGE_CODE — Stores the language in which IDENTIFIER_VALUE is recorded; it carries a foreign key to FND_LANGUAGES.
• PRIMARY_FLAG (VARCHAR2) — Indicates whether the given name or code is the default for the geography.
• GEO_DATA_PROVIDER (VARCHAR2, 30) — Identifies the data source provider that supplied the identifier.
• GEOGRAPHY_USE (VARCHAR2, 30) and GEOGRAPHY_TYPE (VARCHAR2, 30) — Describe the use and type of the geography data respectively.
• OBJECT_VERSION_NUMBER (NUMBER) — Version number of the record, used for optimistic locking.
• CREATED_BY_MODULE (VARCHAR2, 150) — The source application module that created the record.
• Standard WHO columns — LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, and the concurrent program columns APPLICATION_ID, PROGRAM_ID, PROGRAM_LOGIN_ID, PROGRAM_APPLICATION_ID, and REQUEST_ID.

The surrogate primary key is GEOGRAPHY_ID, though this column alone is not unique within this table because a geography may hold several identifiers. The business-key candidate is the unique index HZ_GEOGRAPHY_IDENTIFIERS_U1, defined on the combination of GEOGRAPHY_ID, IDENTIFIER_TYPE, IDENTIFIER_SUBTYPE, IDENTIFIER_VALUE, and LANGUAGE_CODE, stored in APPS_TS_TX_IDX. Two function-based nonunique indexes, HZ_GEOGRAPHY_IDENTIFIERS_N1 and HZ_GEOGRAPHY_IDENTIFIERS_N2, are both defined on UPPER("IDENTIFIER_VALUE") to support case-insensitive identifier lookups.

Common Use Cases and Queries

Typical usage centers on resolving alternate names and codes back to a single geography record, validating that codes supplied by external providers do not collide, and reporting the default identifier for a location. A frequent pattern retrieves all identifiers for a geography:

SELECT identifier_type, identifier_subtype, identifier_value,
       language_code, primary_flag, geo_data_provider
FROM   ar.hz_geography_identifiers
WHERE  geography_id = :p_geography_id;

A case-insensitive search leverages the function-based indexes:

SELECT geography_id, identifier_value
FROM   ar.hz_geography_identifiers
WHERE  UPPER(identifier_value) = UPPER(:p_value);

Reporting use cases include generating lists of ISO country codes or UN/LOCODE values sourced from a given provider, auditing records where PRIMARY_FLAG is not set for a geography, and reconciling duplicate identifier values across languages.

Related Objects

The table is dependent on several core objects, most notably through documented key relationships.

• AR.HZ_GEOGRAPHY — The parent geography entity; joined on GEOGRAPHY_ID, which is the driving column of the unique index.
• FND_LANGUAGES — Referenced by LANGUAGE_CODE through a foreign key relationship.
• AR.HZ_GEOGRAPHY_IDENTIFIERS_U1, _N1, and _N2 — The unique and function-based indexes that support identifier resolution.
• HZ_LOCATIONS and HZ_PARTY_SITES — Location and site entities that consume geography identifiers during address validation.
• AR.HZ_GEO_STRUCTURE_TYPES — Defines the geography type hierarchy referenced by GEOGRAPHY_TYPE values.
• Public HZ Geography APIs — The geography maintenance APIs that insert, update, and query identifier records on behalf of callers.