Results for “ams_acct_contact_v”

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

Overview

AMS_ACCT_CONTACT_V is a reporting view in the Oracle E-Business Suite Marketing (AMS) module. Its documented purpose is to return all account contacts and to support list generation within the Marketing application. In EBS 12.1.1 and 12.2.2, the view functions as a denormalized projection that joins customer account role assignments to party and party relationship data, exposing a flat structure suitable for concurrent programs, list-building utilities, and integration extracts that need contact-to-account associations.

The view is read-only in practice. ETRM metadata for 12.2.2 records no documented base objects and no owner, and notes that the view is not implemented in the documented database instance. The authoritative definition therefore resides in the view text itself, typically created under the APPS schema and granted to reporting responsibilities.

Underlying Base Objects

Although the metadata lists no referenced base objects, the view definition joins five tables from the Trading Community Architecture (TCA) model:

• HZ_CUST_ACCOUNT_ROLES (R) — account role assignments; supplies role state, role type, and date range.
• HZ_CUST_ACCOUNTS (A) — the customer account header; restricted to active accounts.
• HZ_PARTIES (P) — the subject party acting as the contact; restricted to active parties.
• HZ_PARTY_RELATIONSHIPS (PR) — links the account role party to the contact party.
• HZ_ORG_CONTACTS (C) — organization contact records keyed by party relationship.

All joins are inner joins, and several filters constrain the result set: PR.CONTENT_SOURCE_TYPE = 'USER_ENTERED', and status equal to 'A' on accounts, parties, relationships, contacts, and current role state. Critically, the view filters R.CUST_ACCT_SITE_ID IS NULL, meaning only account-level (header) roles are returned and site-level roles are excluded.

Key Columns

The column list centers on the account contact relationship and the contact party's identifying attributes. Columns most relevant to the search term acct_id and to general use include:

• ACCT_ID — sourced from R.CUST_ACCOUNT_ID; the customer account identifier, the primary key used to group contacts by account.
• AMS_CONTACT_ID — sourced from PR.PARTY_ID; the account role party identifier used by Marketing.
• CONTACT_PARTY_ID — sourced from PR.SUBJECT_ID; the party acting as the contact person.
• CUST_ACCOUNT_ROLE_ID, CURRENT_ROLE_STATE, ROLE_TYPE, PRIMARY_FLAG — role-level attributes including status and effective dates.
• PARTY_NUMBER, PARTY_NAME, PERSON_FIRST_NAME, PERSON_LAST_NAME, PERSON_TITLE, PERSON_IDENTIFIER — contact identification data.
• AMS_RELATIONSHIP_ID, RELATIONSHIP_PARTY_ID — the party relationship identifiers underpinning the join.

Common Use Cases and Queries

Typical usage includes building marketing lists by account, extracting contacts for campaign targeting, and reconciling account role data. A common query filters by ACCT_ID or by account-level attributes:

SELECT acct_id, contact_party_id, party_name, person_first_name, person_last_name, role_type FROM ams_acct_contact_v WHERE acct_id = :p_acct_id;

Because the view already restricts results to active, user-entered, header-level roles, no additional status filters are normally required. Analysts validating list membership may join back to HZ_CUST_ACCOUNTS using ACCT_ID to retrieve account number and name. When results appear empty, the most frequent causes are the CUST_ACCT_SITE_ID IS NULL filter excluding site-level roles and status filters removing inactive records. Queries should also account for the possibility that the view is not deployed in a given instance, as the ETRM record indicates it was not implemented in the documented database.