Search Results ams_p_email_v

Overview

AMS_P_EMAIL_V is a public, VALID view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It belongs to the AMS (Marketing) product family and, per its ETRM definition, "returns primary email for a party" and is principally "used for list generation." In practice, the view provides a marketing-oriented, denormalized projection of the electronic-mail contact points that Oracle's Trading Community Architecture (TCA) stores in HZ_CONTACT_POINTS. Rather than exposing every contact point for every entity type, AMS_P_EMAIL_V filters down to a single, well-defined slice: active, user-entered, primary e-mail addresses belonging to parties.

Its role in reporting and integration is to simplify the construction of campaign target lists, audience segments, and outbound e-mail extraction routines. Because the view encapsulates the filtering logic (status, primary flag, owner table, contact point type, content source), downstream reports, Discoverer workbooks, concurrent programs, and integrations can select a stable set of columns without re-implementing the TCA predicate logic each time.

Underlying Base Objects

The view is defined over a single documented base object, HZ_CONTACT_POINTS, referenced through a SYNONYM. The defining query selects from HZ_CONTACT_POINTS (aliased A) and applies five restrictive predicates that together define its grain:

• PRIMARY_FLAG = 'Y' — only the contact point flagged as primary for the party is returned.
• OWNER_TABLE_NAME = 'HZ_PARTIES' — restricts the result to contact points owned by a party record, excluding other owner types such as organizations or locations.
• CONTACT_POINT_TYPE = 'EMAIL' — limits results to the e-mail contact point type.
• CONTENT_SOURCE_TYPE = 'USER_ENTERED' — excludes system-generated or imported addresses, so only manually entered e-mail addresses qualify.
• STATUS = 'A' — returns only active contact points; inactive or obsolete addresses are filtered out.

Therefore the view's cardinality is effectively "at most one active, primary, user-entered e-mail per party." A number of TCA infrastructure columns are passed through unchanged, including the WHO/WHEN audit columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN), the WH_UPDATE_DATE and request/program concurrency columns (REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE), the twenty descriptive ATTRIBUTE columns, the twenty GLOBAL_ATTRIBUTE columns, and the GLOBAL_ATTRIBUTE_CATEGORY.

Key Columns

Several columns are central to using this view correctly:

• AMS_EMAIL_ID — aliased from CONTACT_POINT_ID; the unique identifier of the underlying e-mail contact point.
• PARTY_ID — aliased from OWNER_TABLE_ID; the party (HZ_PARTIES) identifier to which the e-mail belongs.
• EMAIL_ADDRESS — the actual e-mail address value, the field most commonly extracted for list generation.
• PRIMARY_FLAG, STATUS, CONTACT_POINT_TYPE, OWNER_TABLE_NAME, CONTENT_SOURCE_TYPE — the filter columns; their values are effectively constant per the view's predicates (Y, A, EMAIL, HZ_PARTIES, USER_ENTERED).
• PRIORITY_OF_USE_CODE — a code indicating the relative preference or ranking assigned to the contact point for use, relevant where multiple contact mechanisms exist.
• EMAIL_FORMAT, WEB_TYPE, URL — format and web-related attributes carried through for categorization and routing.
• DO_NOT_USE_FLAG, DO_NOT_USE_REASON — suppression indicators that consumers must honor for compliance.
• BEST_TIME_TO_CONTACT_START, BEST_TIME_TO_CONTACT_END, TIME_ZONE, LAST_CONTACT_DT_TIME, CONTACT_ATTEMPTS, CONTACTS — contact-history and scheduling attributes supporting campaign timing and frequency analysis.
• ORIG_SYSTEM_REFERENCE — the source-system key, useful for reconciliation and data lineage.

Common Use Cases and Queries

Typical scenarios include extracting the primary e-mail for a campaign list, validating that a party has a usable address, and joining the view to party or marketing list tables. Because the view already applies the primary/active/user-entered predicates, callers usually only need to constrain by party or attributes.

Selecting the primary e-mail for a specific party:

• SELECT party_id, ams_email_id, email_address, priority_of_use_code FROM apps.ams_p_email_v WHERE party_id = :p_party_id;

Building a bulk extraction for list generation, honoring suppression:

• SELECT v.party_id, v.email_address FROM apps.ams_p_email_v v WHERE v.do_not_use_flag = 'N' AND v.party_id IN (SELECT party_id FROM apps.ams_list_entries);

Joining to the party master for names and ranking use by priority:

• SELECT p.party_name, v.email_address, v.priority_of_use_code FROM apps.ams_p_email_v v, apps.hz_parties p WHERE p.party_id = v.party_id ORDER BY v.priority_of_use_code;

Consumers should remember that the view returns at most one row per party; where multiple addresses are needed, query HZ_CONTACT_POINTS directly.