Search Results postal_plus4_code




Overview
Explore in AI Chat

Overview

AMS_LT_HZ_LOCATIONS is a data source view belonging to the Oracle Marketing (AMS) module. It is defined as a reporting and integration construct rather than a physical database object, and is primarily consumed by Oracle Marketing's list management and campaign targeting features. The view exposes party site address information by joining the Trading Community Architecture (TCA) locations table, HZ_LOCATIONS, to the party sites table, HZ_PARTY_SITES. Its purpose is to present a flattened, denormalized address record suitable for segmentation, list generation, and outbound marketing activities that require validated postal addresses.

Within the Oracle EBS 12.1.1 and 12.2.2 environments, this view is typically referenced by concurrent programs and BI Publisher data templates that build target lists. Because it carries an identifying address filter, it returns only those party site records flagged as the primary or identifying address for a given party, ensuring that each party contributes a single, authoritative mailing address to downstream processing.

Underlying Base Objects

The view is defined over two TCA tables: HZ_LOCATIONS (aliased HS) and HZ_PARTY_SITES (aliased HPS). The join predicate is HS.LOCATION_ID = HPS.LOCATION_ID, with an additional filter IDENTIFYING_ADDRESS_FLAG = 'Y'. Notably, the ETRM 12.2.2 metadata records the owner as blank and lists no documented referenced base objects, meaning the view is documented as "Not implemented in this database" in some instances. This indicates that AMS_LT_HZ_LOCATIONS may be a seed or template definition that is instantiated only when the corresponding Marketing data source is configured, and may not exist as a compiled object in every environment.

HZ_LOCATIONS stores the address components, while HZ_PARTY_SITES links a party to a location and carries attributes such as MAILSTOP and PARTY_SITE_ID. The identifying address flag resides on HZ_PARTY_SITES and governs which site is treated as the party's default address.

Key Columns

Because the view is a select of individual columns from HZ_LOCATIONS and HZ_PARTY_SITES, its columns map directly to TCA address attributes. The columns most frequently relevant to marketing reporting include:

  • LOCATION_ID — Primary key of the underlying location record; the join key to HZ_PARTY_SITES.
  • PARTY_ID and PARTY_SITE_ID — Identify the owning party and the specific party site, enabling linkage to other AMS objects.
  • ADDRESS1–ADDRESS4 — Free-form address lines.
  • CITY, STATE, PROVINCE, COUNTY, COUNTRY — Geographic descriptors used for segmentation.
  • POSTAL_CODE and POSTAL_PLUS4_CODE — The standard postal code and its four-digit extension, used for postal sorting and geographic targeting.
  • MAILSTOP — Internal routing designation for the site.
  • ADDRESS_EFFECTIVE_DATE / ADDRESS_EXPIRATION_DATE — Validity window for the address.
  • LIFE_CYCLE_STATUS — Indicates whether the address is active or inactive.
  • TIME_ZONE, TIMEZONE_ID — Support time-zone-sensitive campaign scheduling.

Common Use Cases and Queries

The principal use case is generating mailing or campaign target lists. A query retrieving party identifiers alongside postal attributes might be written as:

SELECT PARTY_ID, PARTY_SITE_ID, ADDRESS1, CITY, STATE, POSTAL_CODE, POSTAL_PLUS4_CODE FROM AMS_LT_HZ_LOCATIONS WHERE COUNTRY = 'US';

Because the view already restricts results to identifying addresses, it eliminates the need to repeat the identifying-address join logic. Analysts commonly combine it with AMS list headers or party classification tables to filter by demographic or firmographic criteria. A sample targeting query joining party sites to campaign membership could read:

SELECT L.PARTY_ID, L.POSTAL_CODE, L.POSTAL_PLUS4_CODE FROM AMS_LT_HZ_LOCATIONS L WHERE L.LIFE_CYCLE_STATUS = 'A' AND L.STATE = 'CA';

In practice, the object is administered by Oracle Marketing and should be treated as read-only. Direct DML against the view is not supported, and any address maintenance must be performed on the underlying TCA tables. Where the view is not implemented, equivalent reporting can be achieved by querying HZ_LOCATIONS and HZ_PARTY_SITES directly using the same join and identifying-address filter.