Results for “hz_locations_ext_b”

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

Overview

HZ_LOCATIONS_EXT_B is a Receivables (AR) module table that stores extensible attribute values for location records in Oracle E-Business Suite 12.1.1 and 12.2.2. It functions as the extension (flexfield) storage component of the Oracle Trading Community Architecture (TCA) party model, allowing customers to capture organization-specific descriptive data about locations without modifying the base HZ_LOCATIONS table structure.

The table is owned by the AR schema, carries STATUS of VALID, and is documented in ETRM with 58 physical columns and a single unique index, HZ_LOCATIONS_EXT_B_U1, on EXTENSION_ID. The primary key constraint is HZ_LOCATIONS_EXT_B_PK, also defined on EXTENSION_ID.

From a Data Vault modeling perspective, the mined relationship structure (a foreign key to HZ_LOCATIONS via LOCATION_ID, together with a surrogate-style PK) suggests a satellite-leaning classification. In this heuristic reading, HZ_LOCATIONS_EXT_B behaves as a descriptive satellite attached to the HZ_LOCATIONS hub/business key, holding non-key, time-variant attribute content rather than defining a new business entity.

Key Information Stored

Only a subset of the 58 documented columns carries routine functional meaning. The most significant are:

• EXTENSION_ID — Surrogate primary key (HZ_LOCATIONS_EXT_B_PK). Serves only as a unique row identifier and is also the column behind the unique index HZ_LOCATIONS_EXT_B_U1.
• LOCATION_ID — Foreign key to HZ_LOCATIONS. This is the effective business-key link tying each extension row back to its parent location; it is the primary join column in virtually every query against this table.
• ATTR_GROUP_ID — Identifies the attribute group (flexfield context) under which the extensible attributes are organized, enabling multiple attribute sets per location.
• C_EXT_ATTR1 … C_EXT_ATTR20 — Twenty character-typed extensible attribute columns, used for text-valued descriptors such as codes, names, and free-form labels.
• N_EXT_ATTR1 … N_EXT_ATTR20 — Twenty numeric-typed extensible attribute columns for quantities, measures, or amounts.
• D_EXT_ATTR1 … D_EXT_ATTR10 — Ten date-typed extensible attribute columns for effective dates, expiry dates, and similar temporal values.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard EBS WHO columns recording insert and update auditing, with LAST_UPDATE_DATE supporting incremental extraction.

The C/N/D prefix convention reflects the datatype family of each attribute; the numbering reflects the seeded descriptive flexfield segments defined in the TCA location DFF.

Common Use Cases and Queries

This table is queried whenever location-level flexfield attributes must be reported, migrated, or consumed by downstream integrations. A typical pattern joins it to the parent location:

SELECT l.location_id, e.attr_group_id,
       e.c_ext_attr1, e.n_ext_attr1, e.d_ext_attr1
FROM   hz_locations l, hz_locations_ext_b e
WHERE  l.location_id = e.location_id
AND    e.c_ext_attr1 = :code;

Common scenarios include:

• Extensibility reporting — Extracting populated C_/N_/D_EXT_ATTR columns to show which custom location attributes have been captured.
• Data migration and conversion — Loading or reconciling DFF values during implementations or upgrades, keyed by LOCATION_ID and ATTR_GROUP_ID.
• Incremental interfaces — Pulling rows changed since the last run using LAST_UPDATE_DATE and the WHO columns.
• Data-quality audits — Detecting orphaned extension rows or locations lacking attributes by outer-joining to HZ_LOCATIONS.

Because the attribute semantics are site-specific, effective queries must always be paired with the DFF segment definitions configured in the environment.

Related Objects

• HZ_LOCATIONS — Parent table; joined on HZ_LOCATIONS_EXT_B.LOCATION_ID = HZ_LOCATIONS.LOCATION_ID. This is the sole documented foreign key relationship.
• HZ_LOCATIONS_EXT_B_PK — Primary key constraint on EXTENSION_ID.
• HZ_LOCATIONS_EXT_B_U1 — Unique index on EXTENSION_ID, the documented business-key candidate.
• HZ_LOCATIONS_V2 / HZ_LOCATIONS_V — TCA location views exposing location data for API and forms consumption.
• HZ_LOCATION_DFF / FND_DESCRIPTIVE_FLEXS — Flexfield metadata that defines the meaning of ATTR_GROUP_ID and the C_/N_/D_EXT_ATTR columns.
• HZ_PARTY_SITES — Links parties to locations; indirectly depends on extension data through its location references.
• HZ_LOCATION_API / HZ_LOCATION_V2PUB — Public TCA APIs that populate location extension rows during location creation and update.