Search Results methodology_code

Overview

The AST_OPPORTUNITY_DET_V view is a reporting and integration object owned by the APPS schema within the Oracle E-Business Suite AST – TeleSales module. It exposes opportunity (lead) detail records drawn from the TeleSales lead management tables, joined to customer party information and enriched with decoded lookup values. Its documented status is VALID in releases 12.1.1 and 12.2.2, and it is registered as a VIEW in the ETRM repository.

The view presents a denormalised, read-friendly projection of opportunity data. Rather than requiring consumers to join the transactional lead table to multiple party, lookup, and stage tables, the view resolves those relationships in advance and returns descriptions in place of raw codes. This makes it a natural source for operational reporting, custom BI extracts, and interface programs that need opportunity context without reconstructing the underlying join logic.

Underlying Base Objects

The 12.2.2 metadata documents the following referenced base objects:

• AS_LEADS_ALL (synonym) — the primary driving table, aliased OPP in the view text, supplying lead and opportunity columns.
• HZ_PARTIES (synonym, aliased PARTY) — the Trading Community Architecture party record supplying customer/party names and identifiers.
• AS_STATUSES_TL (synonym) — the status lookup, joined to return the translated status meaning.
• AS_SALES_STAGES_ALL_TL (synonym) — the sales stage lookup.
• OE_LOOKUPS (view) — the common lookup view used to decode meaning values referenced through the ASO channel and other lookup aliases.

The view text confirms that OPP and PARTY are the two joined sources in the extracted statement, with additional lookup joins (for example ASOCHANNELS, ASSTATUSES, and STGTL) resolving codes into descriptive names.

p>The relationship to the lead table is one row per lead/opportunity record, keyed by LEAD_ID, with customer identification provided through CUSTOMER_ID and party enrichment through HZ_PARTIES.

Key Columns

• LEAD_ID / ORIGINAL_LEAD_ID — the opportunity primary key and its original lineage reference.
• LEAD_NUMBER, STATUS, RANK — business identifiers and funnel attributes.
• CUSTOMER_ID, ADDRESS_ID — the owning customer and address on the opportunity.
• END_USER_CUSTOMER_ID, END_USER_ADDRESS_ID, END_USER_CUSTOMER_NAME — the end-user (as opposed to the transacting account) party, address, and name. These columns are the typical targets of searches for end_user_address_id.
• PARTY_NAME, PERSON_FIRST_NAME, PERSON_LAST_NAME, PARTY_TYPE, CATEGORY_CODE, PARTY_NUMBER — party-level attributes from HZ_PARTIES.
• MEANING columns (ASOCHANNELS.MEANING, ASSTATUSES.MEANING) and STGTL.NAME — decoded channel, status, and sales-stage text.
• TOTAL_AMOUNT, CURRENCY_CODE, WIN_PROBABILITY, TOTAL_REVENUE_OPP_FORECAST_AMT — revenue and forecast measures.
• DELETE_ALLOWED_FLAG, FREEZE_FLAG, NO_OPP_ALLOWED_FLAG — behavioural control flags.
• Standard EBS who-columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE) and the ATTRIBUTE1–15 descriptive flexfield columns.

Common Use Cases and Queries

Typical scenarios include opportunity pipeline extracts, end-user address resolution, channel and status reporting, and reconciliation between the opportunity and the TCA party model. The view is commonly queried with a party join on the customer or end-user identifier.

Example — locate opportunities by end-user address:

SELECT lead_id, lead_number, end_user_customer_id,
       end_user_address_id, end_user_customer_name, status
  FROM apps.ast_opportunity_det_v
 WHERE end_user_address_id = :p_address_id
   AND deleted_flag = 'N';

Example — active pipeline by channel and stage:

SELECT lead_number, party_name, meaning AS channel,
       name AS sales_stage, total_amount, currency_code,
       win_probability
  FROM apps.ast_opportunity_det_v
 WHERE status = 'OPEN'
   AND org_id = :p_org_id
 ORDER BY total_amount DESC;

Because the view surfaces decoded meaning columns, report authors avoid re-implementing lookup joins. All queries should be schema-qualified with APPS. and, in multi-org environments, filtered by ORG_ID where appropriate.