Search Results contact_reference

Overview

The AMS_CONTACT_INTEREST_V view is a Marketing (AMS) module database object in Oracle E-Business Suite 12.1.1 and 12.2.2. It is designed to support the Discoverer Business Area known as List Generation Folders, providing a denormalized, reporting-friendly representation of contact records held in the Trading Community Architecture (TCA) model. The view consolidates every contact on the system with their associated address, party identifiers, and primary and secondary marketing interests. Its principal role is to serve as a data source for list generation and campaign targeting within Oracle Marketing, allowing users to select contacts based on interest codes and status. Because it joins directly against TCA party and contact tables, it reflects the current master data for individuals flagged as contacts of organizations or other parties. Note that in the documented environment this view is reported as not implemented in the database, meaning it may not exist in every instance and its availability should be verified before use in custom queries.

Underlying Base Objects

The view is defined over a set of TCA and Marketing base tables. The core join is between HZ_PARTIES (aliased twice, once for the contact person and once for the organization) and HZ_PARTY_RELATIONSHIPS, which links the person to the organization through the relationship types CONTACT or CONTACT_OF. Contact-specific attributes are drawn from HZ_ORG_CONTACTS, keyed by PARTY_RELATIONSHIP_ID. Interest data is sourced from AS_INTERESTS_ALL, AS_INTEREST_TYPES_VL, and AS_INTEREST_CODES_VL (joined twice for primary and secondary codes). Contact e-mail addresses are obtained from AMS_P_CONTACT_EMAIL_V. All interest and e-mail joins are outer joins, so contacts without interests or e-mail addresses are still returned. Although no base objects are documented in the ETRM metadata, the view text confirms these eight source objects.

Key Columns

• CONTACT_ID — the organization contact identifier (ORG_CONTACT_ID); CON_TYPE is the constant literal AMS_CONTACT_LO.
• ADDRESS_ID — the party site identifier associated with the contact.
• CUSTOMER_ID and PARTY_ID — party identifiers for the organization and the person respectively.
• LAST_NAME, FIRST_NAME, SUFFIX, SALUTATION, TITLE — personal name and salutation attributes.
• EMAIL_ADDRESS — the contact's e-mail address from the email view.
• CONTACT_ACTIVE — a decoded value derived from HZ_ORG_CONTACTS.STATUS, returning ACTIVE when the status is 'A' and INACTIVE otherwise. This is the column most relevant to a search for "contact_active".
• CONTACT_INTEREST_TYPE, CONTACT_PRIMARY_INT_CODE, CONTACT_SECONDARY_INT_CODE — concatenated interest type and code values describing the contact's primary and secondary marketing interests.
• CONTACT_DECISION_MAKER and CONTACT_REFERENCE — flags (defaulted to 'N') indicating decision-maker and reference-use status.
• CON_LAST_UPDATED_BY, CON_LAST_UPDATED_DATE, CON_LAST_UPDATED_NAME — audit columns resolving the last updater's user name via FND_USER_AP_PKG.GET_USER_NAME.

Common Use Cases and Queries

The primary use case is building marketing lists of active contacts filtered by interest. The following query returns active contacts with their primary interest:

SELECT contact_id, last_name, first_name, email_address,
       contact_primary_int_code
FROM   ams_contact_interest_v
WHERE  contact_active = 'ACTIVE'
ORDER  BY last_name;

To target contacts by a specific interest type, filter on the concatenated code columns. Because interest data is outer-joined, use IS NOT NULL or NVL logic when interests are mandatory. Auditing recently maintained contacts is also feasible via CON_LAST_UPDATED_DATE. Report authors using Discoverer should note the view is bound to the List Generation Business Area and, per ETRM metadata, may be absent from certain databases.