Results for “ams_lead_contacts_v”

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

Overview

AMS_LEAD_CONTACTS_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, classified under the AMS (Marketing) product family. The view is documented as valid in both EBS 12.1.1 and 12.2.2 and is used by the Discoverer Business Area within the "List generation" folders. Its principal function is to present contact records that are linked to sales opportunities, allowing operational and analytical users to report on the individuals associated with a lead or opportunity without querying the underlying transactional table directly.

Users searching for PRIMARY_CONTACT_FLAG will locate this view because that column is explicitly exposed in its projection. The flag allows reporting logic to distinguish the designated primary contact for a lead from secondary contacts, which is a frequent requirement in list generation, campaign targeting, and opportunity contact analysis.

Underlying Base Objects

The view is defined over a single documented base object, AS_LEAD_CONTACTS_ALL, which is referenced through a synonym. The view text performs a straight projection of that base table with no joins, aggregations, or filters:

• SELECT ... FROM AS_LEAD_CONTACTS_ALL — every column in the view maps one-to-one to a column of the base table.
• Because AS_LEAD_CONTACTS_ALL is an _ALL table, it is partitioned by operating unit and includes the ORG_ID column, which enforces multi-org security at the base layer.
• The view contains no WHERE clause and no DECODE/CASE transformations, so it is effectively a direct read interface onto the lead-contact association table.

Key Columns

The most significant columns exposed by the view include the following:

• LEAD_CONTACT_ID — Primary key of the contact-to-lead association record.
• LEAD_ID — Foreign reference to the lead (opportunity) with which the contact is associated.
• CONTACT_ID — Identifier of the contact party, linkable to the contact master tables.
• PRIMARY_CONTACT_FLAG — Indicates whether the associated contact is the primary contact for the lead; used to filter for the main point of contact.
• ENABLED_FLAG — Indicates whether the association is currently active.
• CUSTOMER_ID, ADDRESS_ID, PHONE_ID — Party, location, and telephone identifiers tied to the contact.
• ROLE — The role played by the contact on the opportunity, supporting segmentation by responsibility.
• RANK — Ordering attributing used to sequence or prioritize contacts within a lead.
• ORG_ID — Operating unit that owns the record, driving multi-org access control.
• Who-columns and descriptive flexfields — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, REQUEST_ID, PROGRAM_ID, ATTRIBUTE_CATEGORY, and ATTRIBUTE1 through ATTRIBUTE15.

Common Use Cases and Queries

The view is typically consumed by Discoverer workbooks, BI Publisher reports, and custom SQL used for list generation and opportunity contact analysis. A common requirement is to retrieve the primary contact for each lead:

• SELECT lead_id, contact_id, primary_contact_flag, role
FROM ams_lead_contacts_v
WHERE primary_contact_flag = 'Y';
• To list all contacts for a specific opportunity irrespective of primary status:
  SELECT contact_id, primary_contact_flag, role, rank
FROM ams_lead_contacts_v
WHERE lead_id = :p_lead_id
AND enabled_flag = 'Y';
• To count contacts by role for a reporting period, filter on creation_date or last_update_date and aggregate on role.

Because the view carries ORG_ID, queries should be executed under an active operating unit context, or explicitly filtered by ORG_ID, to avoid cross-organization data exposure. Where stricter security or additional attributes from related lead or contact entities are required, the view may be joined to lead and contact master views using LEAD_ID and CONTACT_ID respectively.