Search Results lead_contact




Overview
Explore in AI Chat

Overview

APPS.AST_LM_OPP_EXP_CR_TYPE_V is a reporting view in Oracle E-Business Suite that consolidates lead, opportunity, contact, and sales-credit information into a single denormalized projection. The name follows the ETRM (Enterprise Territory and Resource Management) naming convention, where "OPP" denotes opportunity, "EXP" denotes export, and "CR_TYPE" denotes credit type. It joins the lead and opportunity base tables with party, party-site, location, relationship, sales-stage, status, channel, campaign-source, and sales-credit-type objects to expose a flattened record suitable for extraction, analytics, and integration interfaces.

The view is especially relevant for users searching on "lead_contact", because contact resolution flows through AS_LEAD_CONTACTS_ALL and HZ_RELATIONSHIPS. It exposes both the identified contact party and the contact's first and last names, making it the primary object for reporting on which individual is associated with a given lead or opportunity.

Underlying Base Objects

The view is defined over the following documented objects: AMS_SOURCE_CODES, AS_LEADS_ALL, AS_LEAD_CONTACTS_ALL, AS_SALES_CREDITS, AS_SALES_STAGES_ALL_TL, AS_STATUSES_TL, HZ_LOCATIONS, HZ_PARTIES, HZ_PARTY_SITES, HZ_RELATIONSHIPS, OE_LOOKUPS, and OE_SALES_CREDIT_TYPES. Within APPS these are referenced as synonyms, with OE_LOOKUPS exposed as a view.

The FROM clause anchors on AS_LEADS_ALL (aliased OPP). AS_LEAD_CONTACTS_ALL is outer-joined on lead_id so leads without a contact still return. HZ_PARTIES is joined on customer_id for the account name and type; a second instance (PARTY1) is reached through HZ_RELATIONSHIPS to resolve the contact party. HZ_PARTY_SITES and HZ_LOCATIONS supply address attributes through location_id and party_site_id. AS_SALES_STAGES_ALL_TL and AS_STATUSES_TL provide translated sales-stage and status descriptions, OE_LOOKUPS supplies the channel meaning, and AMS_SOURCE_CODES resolves the campaign source. AS_SALES_CREDITS links the lead to OE_SALES_CREDIT_TYPES, which supplies the credit type name.

Key Columns

Common Use Cases and Queries

Typical uses include lead-to-contact reporting, opportunity pipeline extracts, and territory or credit-type analytics. The outer joins on AS_LEAD_CONTACTS_ALL and HZ_RELATIONSHIPS mean the view returns rows even when no contact exists, so callers must expect NULL contact values. A representative query retrieves leads and their contacts:

  • SELECT lead_number, customer_name, contact_first_name, contact_last_name, status FROM apps.ast_lm_opp_exp_cr_type_v WHERE org_id = :p_org_id;
  • Filter by partial names: WHERE UPPER(contact_last_name) LIKE 'SM%'.
  • Incremental extracts: WHERE last_update_date >= :p_since.
  • Grouping by credit type: SELECT name, COUNT(*) FROM apps.ast_lm_opp_exp_cr_type_v GROUP BY name.

Because the view joins eleven base objects, unconstrained queries can be costly; restricting on ORG_ID, LEAD_ID, or LAST_UPDATE_DATE is recommended for production reporting and integration loads.