Results for “ams_org_phone1_v”

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

Overview

AMS_ORG_PHONE1_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It is registered under the AMS (Marketing) product family and is described in the ETRM metadata as presenting "electronic methods of communicating with parties." In practical terms, the view exposes the primary, active telephone contact points recorded for organization-type parties, joined to any applicable call-related contact preferences. Its name reflects its purpose: it returns a single "point" phone record ("PHONE1") for an organization or party relationship, rather than the full set of phone entries a party may hold.

Because the view filters to a single primary phone per party, it serves as a convenient reporting and integration surface. Oracle Marketing, Trade Management, and downstream collateral configurations frequently reference primary phone data when a single canonical number is required per organization. The view is VALID in the documented 12.2.2 environment.

Underlying Base Objects

The view text is defined over three documented base objects, all accessed through public synonyms in the APPS schema:

• HZ_CONTACT_POINTS (aliased HCP) — the source of all phone contact point attributes.
• HZ_PARTIES (aliased HP) — the registered party master providing the owning organization context.
• HZ_CONTACT_PREFERENCES (aliased HCPREF) — joined with outer-join ((+)) syntax to carry call preference data where it exists.

Joins are established on HCP.OWNER_TABLE_ID = HP.PARTY_ID and HCPREF.CONTACT_LEVEL_TABLE_ID (+) = HP.PARTY_ID. The view restricts to HCP.OWNER_TABLE_NAME = 'HZ_PARTIES' and HP.PARTY_TYPE = 'PARTY_RELATIONSHIP', meaning the returned rows represent relationships (including organizations) rather than persons or other party types.

Key Columns

The view exposes attributes from all three base objects. Core phone columns include:

• CONTACT_POINT_ID — unique identifier of the phone contact point.
• OWNER_TABLE_ID and OWNER_TABLE_NAME — the owning party and its originating table (always HZ_PARTIES here).
• PHONE_COUNTRY_CODE, PHONE_AREA_CODE, PHONE_NUMBER, PHONE_EXTENSION, RAW_PHONE_NUMBER — the parsed and unformatted telephony components.
• TRANSPOSED_PHONE_NUMBER — the normalized form used for lookups and matching.
• PRIMARY_FLAG and PRIMARY_BY_PURPOSE — indicators of primary designation; the view is filtered to PRIMARY_FLAG = 'Y'.
• STATUS — record status, filtered to 'A' (active).
• CONTACT_POINT_PURPOSE and CONTACT_POINT_TYPE — purpose classification and type, fixed to 'PHONE'.

Preference columns such as PREFERENCE_CODE, PREFERENCE_START_DATE, PREFERENCE_END_DATE, and MAX_NO_OF_INTERACTIONS originate from HZ_CONTACT_PREFERENCES and are populated only when a matching CALL/DO preference row exists.

Common Use Cases and Queries

The view is commonly used to retrieve a single, authoritative phone number for organizations and party relationships in reports, marketing extracts, and interface programs. Typical scenarios include populating correspondence, dialing lists, or data migration extracts where one primary number per party is required.

A representative query retrieving the primary phone for a specific party:

• SELECT owner_table_id, phone_country_code, phone_area_code, phone_number, phone_extension, preference_code FROM apps.ams_org_phone1_v WHERE owner_table_id = :party_id;

To list all active organization primary phones for reporting:

• SELECT owner_table_id, raw_phone_number, transposed_phone_number, creation_date FROM apps.ams_org_phone1_v ORDER BY creation_date DESC;

Because the view already enforces status, primary, and source filters, consumers should not re-apply those predicates unless refining further. Note that AUTO-sourced, non-primary, or non-relationship phone records will not appear, so callers requiring the complete phone set must query HZ_CONTACT_POINTS directly.