Search Results address_effective_date




Overview
Explore in AI Chat

Overview

AST_LOCATIONS_V is an APPS-owned database view in the Oracle E-Business Suite, belonging to the AST — TeleSales product family. It presents a consolidated, reporting-friendly projection of party site and location address data by joining Oracle Trading Community Architecture (TCA) entities with territory and timezone reference data. The view is registered as VALID in the EBS data dictionary and is available in both 12.1.1 and 12.2.2 environments.

Its principal role is to expose a single flattened row for each active or inactive party site, combining the site-level record from HZ_PARTY_SITES with its postal address from HZ_LOCATIONS. This design spares downstream reports, concurrent programs, and integrations from having to reassemble the same multi-table join themselves. The most frequently searched column, ADDRESS_EFFECTIVE_DATE, is sourced directly from HZ_LOCATIONS and indicates the date from which the stored address is considered effective.

Underlying Base Objects

The documented view metadata identifies the following referenced base objects, all accessed through APPS synonyms:

  • HZ_PARTY_SITES — supplies the site record, status, party identifiers, mailstop, attributes, and site-level audit columns.
  • HZ_LOCATIONS — supplies the postal address components, address effective and expiration dates, address style, and location-level audit columns.
  • FND_TERRITORIES_TL — supplies the translated territory short name used to derive the country name; joined with an outer (+) operator.
  • FND_TIMEZONES_B and FND_TIMEZONES_VL — supply timezone name and upgrade timezone identifier, joined with an outer (+) operator.

The join predicate ties SITE.LOCATION_ID to LOC.LOCATION_ID, resolves the country code against FND_TERRITORIES_TL with the session language (USERENV('LANG')), and links the timezone through TM.UPGRADE_TZ_ID = LOC.TIMEZONE_ID. Only party sites whose STATUS is 'A' (active) or 'I' (inactive) qualify for inclusion.

Key Columns

The view exposes a broad column set. The following are the most significant for reporting and integration:

Common Use Cases and Queries

Typical uses include TeleSales address verification, customer-facing correspondence, data migration reconciliation, and ad-hoc address extracts. A query filtered on the effective date is the most common pattern:

SELECT party_site_number, address1, city, state, postal_code, address_effective_date, address_expiration_date FROM apps.ast_locations_v WHERE address_effective_date >= :p_from_date AND status = 'A';

Because the view already resolves the country name and timezone, it is frequently used to produce mailing labels or export files without additional lookups:

SELECT party_id, party_site_id, address1, address2, city, country_name, timezone FROM apps.ast_locations_v WHERE country_code = 'US' AND status = 'A' ORDER BY postal_code;

Integrations that synchronize address data to external systems generally key on LOCATION_ID and compare ADDRESS_EFFECTIVE_DATE against a stored watermark to identify changed records. When translating the view into a report, note that the outer joins to territory and timezone can return nulls for unrecognized country codes, and that inactive sites are returned unless the query explicitly restricts STATUS.