Results for “hz_locations”

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

Overview

HZ_LOCATIONS is the master address repository within the Oracle E-Business Suite Trading Community Architecture (TCA). Owned by the AR (Receivables) schema, it stores physical addresses referenced across virtually every Oracle EBS module, including Receivables, Payables, Order Management, Shipping, Human Resources, and Marketing. In Release 12.1.1 and 12.2.2, the table is documented as VALID with 116 columns. Its declared description — "Physical addresses" — understates its architectural reach: HZ_LOCATIONS is the single source of truth for street-level address data associated with parties, party sites, bank accounts, credit cards, incidents, venues, and tax determination factors.

From a Data Vault modeling perspective, ETRM's heuristic classification places HZ_LOCATIONS as a hub. This is consistent with its role: it holds a stable surrogate key (LOCATION_ID) and descriptive business attributes, while relationships to parties, sites, and transactional entities are resolved through separate linking tables such as HZ_PARTY_SITES and HZ_LOC_ASSIGNMENTS. Treating it as a hub in an analytical or integration model preserves address identity independently of any single consuming transaction.

Key Information Stored

The primary key is LOCATION_ID, enforced by HZ_LOCATIONS_PK and by the unique index HZ_LOCATIONS_U1. This surrogate identifier is the value propagated to every dependent table and should always be used as the join key rather than any address fragment.

The most operationally significant columns include:

• LOCATION_ID — surrogate primary key and universal foreign key for address references.
• COUNTRY — foreign key to FND_TERRITORIES; drives address style and validation rules.
• ADDRESS1–ADDRESS4 — free-form address lines used when structured parsing is unavailable or incomplete.
• ADDRESS_KEY, ADDRESS_STYLE, VALIDATED_FLAG, VALIDATION_STATUS_CODE, DATE_VALIDATED, DO_NOT_VALIDATE_FLAG — control and outcome of address validation processing.
• ADDRESS_EFFECTIVE_DATE, ADDRESS_EXPIRATION_DATE — temporal validity of the location record.
• CITY, STATE, PROVINCE, COUNTY, POSTAL_CODE, POSTAL_PLUS4_CODE — geographic components used in tax, shipping, and reporting.
• STREET, STREET_NUMBER, HOUSE_NUMBER, BUILDING, FLOOR, SUITE, ROOM, APARTMENT_NUMBER — parsed address elements for structured geocoding.
• TIMEZONE_ID — foreign key to HZ_TIMEZONES; LANGUAGE references FND_LANGUAGES.
• LIFE_CYCLE_STATUS — active/inactive state of the address.
• SALES_TAX_GEOCODE, SALES_TAX_INSIDE_CITY_LIMITS — jurisdiction indicators consumed by E-Business Tax.
• FA_LOCATION_ID — linkage to Fixed Assets location definitions.
• OBJECT_VERSION_NUMBER, CREATED_BY_MODULE, APPLICATION_ID — concurrency control, ownership, and origin metadata.
• The fifteen ATTRIBUTE columns and twenty GLOBAL_ATTRIBUTE columns — extensibility slots used by Oracle and customers for DFF-context storage.

Common Use Cases and Queries

Typical usage centers on resolving addresses for a party site, validating or de-duplicating address records, and reporting geographic distribution of customers or suppliers. A common join pattern retrieves addresses for a party site:

SELECT l.location_id, l.address1, l.city, l.state, l.postal_code, l.country
FROM hz_locations l, hz_party_sites ps
WHERE ps.location_id = l.location_id
AND ps.party_site_id = :p_party_site_id;

A second frequent pattern identifies unvalidated addresses for a data-quality initiative:

SELECT location_id, address1, city, country
FROM hz_locations
WHERE nvl(validated_flag,'N') = 'N'
AND nvl(do_not_validate_flag,'N') = 'N';

Reporting scenarios include geographic revenue analysis (join through ZX_LINES_DET_FACTORS on SHIP_TO_LOCATION_ID or BILL_TO_LOCATION_ID), supplier remittance address extraction (join through IBY_BANKACCT on ADDRESSID), and marketing venue listings (join through AMS_VENUES_B on LOCATION_ID). All such queries should filter on LIFE_CYCLE_STATUS and respect the effective/expiration date range where historical accuracy matters.

Related Objects

HZ_LOCATIONS participates in a dense foreign-key network. The most significant relationships include:

• HZ_PARTY_SITES.LOCATION_ID → HZ_LOCATIONS.LOCATION_ID — associates a site with its physical address; the primary consumption path for TCA.
• HZ_LOC_ASSIGNMENTS.LOCATION_ID → HZ_LOCATIONS.LOCATION_ID — assigns locations to parties or sites with usage context.
• HZ_LOCATION_PROFILES.LOCATION_ID → HZ_LOCATIONS.LOCATION_ID — holds address-style and formatting profiles.
• HZ_LOCATIONS_EXT_B / HZ_LOCATIONS_EXT_TL — extension and translated-description tables keyed by LOCATION_ID.
• IBY_BANKACCT.ADDRESSID → HZ_LOCATIONS.LOCATION_ID — bank account and remittance addresses.
• IBY_CREDITCARD.UPGRADE_ADDRESSID — cardholder billing address reference.
• CS_INCIDENTS_ALL_B.INCIDENT_LOCATION_ID — incident location for service and support.
• AMS_VENUES_B, AMS_EVENT_OFFERS_ALL_B, AMS_LIST_ENTRIES, AMS_IMP_SOURCE_LINES — marketing venue and campaign address references.
• ZX_LINES_DET_FACTORS — ship-to, ship-from, bill-to, and bill-from location IDs used by E-Business Tax.
• FND_TERRITORIES, FND_LANGUAGES, HZ_TIMEZONES — lookup tables referenced by COUNTRY, LANGUAGE, and TIMEZONE_ID respectively.

Together these relationships confirm HZ_LOCATIONS as a central hub whose surrogate key is propagated broadly across the EBS data model.