Results for “ams_org_locations_v”

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

Overview

The AMS_ORG_LOCATIONS_V view is a marketing-module reporting object that exposes organization-party location data drawn from Oracle's Trading Community Architecture (TCA) tables. In Oracle EBS 12.1.1 and 12.2.2 it serves as a denormalized, read-only projection of validated organization addresses, filtered to active parties and active party sites. Its purpose is to give Marketing (AMS) functionality — and any dependent reporting or integration — a convenient, pre-joined source of address records without requiring the caller to reconstruct the HZ_LOCATIONS, HZ_PARTY_SITES, and HZ_PARTIES join logic manually.

The view is significant for address-quality work. It restricts output to user-entered, identifying addresses belonging to organizations that are active. Per the ETRM metadata, the object is not implemented in the extraction database, meaning the definition is documented but no live data exists in the referenced environment. In production instances it resolves against the customer's own TCA data.

Underlying Base Objects

Although the ETRM metadata lists no referenced base objects, the embedded view text makes the dependencies explicit. The view is defined over four sources:

• HZ_LOCATIONS (aliased HL) — the master address store, supplying location identifiers and all address attributes.
• HZ_PARTY_SITES (aliased HPS) — links a party to a location; drives the join and the filtering predicates.
• HZ_PARTIES (aliased HP) — the trading community party record (organization type), providing party status and type.
• FND_TERRITORIES_TL (aliased LANG) — the translated territory table, supplying the localized country name.

The joins and filters are: HL.LOCATION_ID = HPS.LOCATION_ID; HPS.PARTY_ID = HP.PARTY_ID; HPS.STATUS = 'A'; HPS.IDENTIFYING_ADDRESS_FLAG = 'Y'; HPS.ACTUAL_CONTENT_SOURCE = 'USER_ENTERED'; HP.STATUS = 'A'; HP.PARTY_TYPE = 'ORGANIZATION'; LANG.LANGUAGE = USERENV('LANG'); and LANG.TERRITORY_CODE = HL.COUNTRY. Language resolution is therefore session-dependent, returning the country name in the running user's language.

Key Columns

The view exposes the full address attribute set of HZ_LOCATIONS. Notable columns include:

• LOCATION_ID — primary key of the originating HZ_LOCATIONS record.
• COUNTRY / COUNTRY_NAME — country code and its translated short name, useful for localized reporting.
• ADDRESS1–ADDRESS4, CITY, STATE, PROVINCE, COUNTY, POSTAL_CODE, POSTAL_PLUS4_CODE — the standard displayable address lines.
• VALIDATED_FLAG, LIFE_CYCLE_STATUS — indicators of address validation and lifecycle state.
• SALES_TAX_GEOCODE, SALES_TAX_INSIDE_CITY_LIMITS, CLLI_CODE, DODAAC — jurisdiction and telecommunication/military address codes.
• TIME_ZONE, LANGUAGE, ADDRESS_EFFECTIVE_DATE, ADDRESS_EXPIRATION_DATE — temporal and locale context.
• LOC_HIERARCHY_ID, POSITION, DELIVERY_POINT_CODE — location hierarchy and delivery descriptors.

Granular fields such as HOUSE_NUMBER, STREET_NUMBER, BUILDING, FLOOR, SUITE, ROOM, PO_BOX_NUMBER, RURAL_ROUTE_TYPE/NUMBER and OVERSEAS_ADDRESS_FLAG support structured/parseable address formats.

Common Use Cases and Queries

Typical uses include marketing list validation, country-name localized address extracts, and reconciliation of organization addresses to their TCA source. A basic query is:

• SELECT location_id, country_name, city, address1, postal_code FROM ams_org_locations_v WHERE country = 'US';
• SELECT country_name, COUNT(*) FROM ams_org_locations_v GROUP BY country_name; — country distribution of active org addresses.
• SELECT v.location_id, v.city FROM ams_org_locations_v v WHERE v.validated_flag = 'Y'; — validated-address reporting.

Because the view pre-applies the active-status, identifying-address, and USER_ENTERED content-source filters, queries against it return only the "clean" subset of organization addresses, which is precisely the intent of the Marketing location view.