Search Results as_lead_contacts_all




Overview
Explore in AI Chat

Overview

The AS_LEAD_CONTACTS_ALL table is a core transactional entity within the Oracle E-Business Suite (EBS) Sales Foundation module (AS). It functions as the central repository for managing the relationship between sales opportunities (leads) and their associated contact persons. This table is essential for maintaining a complete profile of a sales lead, ensuring that all relevant individual contacts are linked, tracked, and accessible throughout the sales process. As a multi-organization enabled table, denoted by the "_ALL" suffix, it stores data across all operating units, with access typically controlled by the MO: Operating Unit profile option. Its primary role is to establish and enforce the many-to-many relationship between leads and contacts, a fundamental aspect of customer relationship management in the EBS architecture.

Key Information Stored

The table's structure is designed to link a lead to a contact while storing context-specific information about that relationship. The primary key is LEAD_CONTACT_ID, a unique system-generated identifier. Two critical foreign key columns form a unique constraint (AS_LEAD_CONTACTS_UK): LEAD_ID, which references the parent opportunity in AS_LEADS_ALL, and CONTACT_ID, which references the specific contact person in HZ_ORG_CONTACTS. Beyond these links, the table stores identifiers to other related entities to provide a complete contact point for the lead, including CONTACT_PARTY_ID (linking to the party in HZ_PARTIES), CUSTOMER_ID (linking to the customer account), ADDRESS_ID (for the contact's site from HZ_PARTY_SITES), and PHONE_ID (for a primary contact point from HZ_CONTACT_POINTS). Additional columns typically track creation and last update dates, the creating and last updating user, and the active status of the link.

Common Use Cases and Queries

This table is central to reporting and operations involving lead contacts. Common use cases include generating contact lists for a specific sales lead, analyzing the number of contacts per opportunity, and validating data integrity between leads and the Trading Community Architecture (TCA) registry. A typical query retrieves all active contacts for a given lead, joining to TCA tables for detailed information:

  • SELECT alc.lead_id, hoc.party_id, hp.party_name, hcp.phone_number FROM as_lead_contacts_all alc, hz_org_contacts hoc, hz_parties hp, hz_contact_points hcp WHERE alc.contact_id = hoc.contact_id AND hoc.party_id = hp.party_id AND alc.phone_id = hcp.contact_point_id(+) AND alc.lead_id = :p_lead_id;

Data is primarily created, updated, and deleted via the Oracle Sales Online (OSO) user interface or through public APIs provided by the AS module, which ensure proper validation against related TCA entities.

Related Objects

AS_LEAD_CONTACTS_ALL has extensive dependencies within the EBS data model, primarily with the Trading Community Architecture (TCA) and the Sales Foundation module. Its key relationships are:

  • AS_LEADS_ALL: The primary parent table for the sales opportunity (LEAD_ID).
  • HZ_ORG_CONTACTS & HZ_PARTIES: The source for the contact person's master record (CONTACT_ID, CONTACT_PARTY_ID).
  • HZ_PARTY_SITES: Provides the contact's address (ADDRESS_ID).
  • HZ_CONTACT_POINTS: Provides phone, email, or web contact details (PHONE_ID).
  • HZ_PARTIES (Customer): Links to the customer account party (CUSTOMER_ID).

Development or reporting involving lead contacts must consistently join to these TCA tables to retrieve meaningful descriptive information, as AS_LEAD_CONTACTS_ALL primarily stores relational identifiers.