Search Results hz_locations

Overview

The HZ_LOCATIONS table is a core master data table within Oracle E-Business Suite (EBS), specifically under the Receivables (AR) product family. It serves as the central repository for storing normalized physical address data across the entire application. Its primary role is to eliminate data redundancy by providing a single source of truth for address information, which is then referenced by numerous other entities such as customer sites (HZ_PARTY_SITES), bank accounts (IBY_BANKACCT), and service requests (CS_INCIDENTS_ALL_B). This design ensures consistency and integrity in address management throughout modules including Order Management, Receivables, Payables, and Customer Relationship Management.

Key Information Stored

The table stores comprehensive address components. The primary key is LOCATION_ID, a unique system-generated identifier. Key descriptive columns include ADDRESS1 through ADDRESS4 for street-level details, CITY, POSTAL_CODE, and STATE. Critical foreign key columns link to reference data: COUNTRY references FND_TERRITORIES, and LANGUAGE references FND_LANGUAGES. The table also supports globalization and logistics with fields like TIMEZONE_ID (linked to HZ_TIMEZONES), COUNTY, and PROVINCE. Additional columns manage data lifecycle (CREATED_BY, CREATION_DATE, LAST_UPDATE_DATE) and validation status (VALIDATED_FLAG).

Common Use Cases and Queries

Common operational and reporting scenarios involve joining HZ_LOCATIONS to related entities. A frequent use case is retrieving the complete address for a customer's ship-to site. Technical consultants often query this table to diagnose data issues or for custom reporting. Sample SQL patterns include fetching addresses for a specific party or validating address standardization.

• Finding addresses for a specific customer party:
  SELECT loc.address1, loc.city, loc.postal_code, ter.territory_short_name
FROM hz_locations loc, hz_party_sites ps, fnd_territories ter
WHERE loc.location_id = ps.location_id
AND ps.party_id = :p_party_id
AND loc.country = ter.territory_code;
• Identifying locations within a specific postal code range:
  SELECT location_id, address1, city, state
FROM hz_locations
WHERE postal_code BETWEEN '10000' AND '20000'
AND country = 'US';

Related Objects

As indicated by the extensive foreign key relationships in the metadata, HZ_LOCATIONS is a foundational table referenced across the EBS suite. Key dependent objects include:

• HZ_PARTY_SITES: The primary link between a location and a customer, supplier, or partner (HZ_PARTIES).
• HZ_LOCATION_PROFILES: Stores additional profile and classification information for a location.
• IBY_BANKACCT & IBY_CREDITCARD: Reference locations for banking and payment details.
• CS_INCIDENTS_ALL_B: Links service requests to a physical incident location.
• ZX_LINES_DET_FACTORS: Referenced for tax determination using ship-to and ship-from locations.
• Various TCA Extension Tables: Such as HZ_LOCATIONS_EXT_B and HZ_LOCATIONS_EXT_TL for extended attributes and translations.

The table's integrity is maintained through foreign key constraints to reference tables like FND_TERRITORIES and FND_LANGUAGES, ensuring valid country and language codes.