Results for “as_opportunity_contacts_v”

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

Overview

AS_OPPORTUNITY_CONTACTS_V is a VALID database view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It belongs to the AS - Sales Foundation product family (the Oracle Sales/TeleSales application foundation) and is defined as a "view to provide opportunity contact details." Although the object name references "opportunity," the underlying implementation is built on Oracle Sales lead contact data (AS_LEAD_CONTACTS_ALL) joined to the Oracle Trading Community Architecture (TCA) and Oracle Receivables lookup tables. The view therefore surfaces the contact associations attached to a sales lead, including the associated party, relationship, and phone contact information.

The view is primarily consumed for reporting and integration. Its most notable characteristic for developers is that it exposes ROWID-derived pseudo-columns, most importantly CONTACT_ROW_ID, which returns the ROWID of the underlying HZ_PARTIES record for the contact. This makes the view useful both as a descriptive data source and as a mechanism for resolving the TCA party row behind a lead contact. Because it is a read-only view (no INSTEAD OF triggers are documented), it should be treated strictly as a query surface, not as a DML target.

Underlying Base Objects

Per the documented metadata, the view is defined over the following referenced base objects:

• AS_LEAD_CONTACTS_ALL (accessed via synonym) – the driving table holding the lead-to-contact association, rank, primary-contact flag, and standard WHO columns.
• HZ_PARTIES (synonym) – joined twice: once as PARTY_CONTACTS for the contact's name and title attributes, and once as REL_PARTY for the e-mail address.
• HZ_RELATIONSHIPS (synonym) – the TCA relationship records linking the subject and object parties.
• HZ_RELATIONSHIP_TYPES (synonym) – supplies the relationship role and forward relationship code.
• HZ_CONTACT_POINTS (synonym) – provides phone contact point details.
• AR_LOOKUPS (view) – resolves the phone line type code to its displayed meaning.

The join structure attaches each AS_LEAD_CONTACTS_ALL row to its TCA party, the party's relationship record, the party's phone contact points, and lookup-based decode values, effectively denormalizing lead contact data into a single reporting row.

Key Columns

The projection begins with the underlying lead-contact columns: LEAD_CONTACT_ID, LEAD_ID, CONTACT_ID, CONTACT_PARTY_ID, CUSTOMER_ID, ADDRESS_ID, PHONE_ID, RANK, ENABLED_FLAG, PRIMARY_CONTACT_FLAG, and the full set of WHO audit columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) plus ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15.

Derived and joined columns carry significant reporting value:

• ROW_ID – ROWID of the AS_LEAD_CONTACTS_ALL row.
• CONTACT_ROW_ID – ROWID of the HZ_PARTIES contact record; the column most frequently searched for.
• PHONE_ROW_ID – ROWID of the HZ_CONTACT_POINTS phone record.
• FIRST_NAME, LAST_NAME, PERSON_PRE_NAME_ADJUNCT, PERSON_TITLE – contact name and title components.
• EMAIL_ADDRESS – contact e-mail from the related party.
• PHONE_COUNTRY_CODE, AREA_CODE, PHONE_NUMBER, EXTENSION, PHONE_TYPE_CODE, PHONE_TYPE, PRIMARY_FLAG – phone details with decoded type.
• FORWARD_REL_CODE, ROLE, RELATIONSHIP_ID, SUBJECT_PARTY_ID, OBJECT_PARTY_ID – TCA relationship context.

Common Use Cases and Queries

A common integration pattern is resolving the TCA party row behind a lead contact, which is why contact_row_id is a frequent search term. A representative query retrieves contact details for a given lead:

• SELECT lead_contact_id, first_name, last_name, email_address, phone_number, phone_type, contact_row_id FROM as_opportunity_contacts_v WHERE lead_id = :lead_id AND enabled_flag = 'Y';
• SELECT lead_id, contact_party_id, contact_row_id FROM as_opportunity_contacts_v WHERE contact_row_id = :p_rowid;
• SELECT lead_id, first_name, last_name, phone_number FROM as_opportunity_contacts_v WHERE primary_contact_flag = 'Y';

These patterns support lead contact reporting, phone and e-mail enrichment, and cross-referencing into TCA parties via CONTACT_ROW_ID. Because CONTACT_ROW_ID and PHONE_ROW_ID are ROWID values, they are non-portable across database copies and should be used only within the source instance.