Results for “ast_opportunity_log_v”

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

Overview

AST_OPPORTUNITY_LOG_V is a consolidated reporting view in the Oracle E-Business Suite TeleSales (AST) module, owned by the APPS schema and defined as a VALID database view. It presents a denormalized, human-readable projection of opportunity and lead log records stored in the AS_LEADS_LOG table, joining that transactional spine to party, location, resource, group, and lookup reference data. The view is the principal mechanism by which TeleSales opportunity log entries are surfaced with descriptive attributes rather than raw surrogate identifiers, making it suitable for operational reporting, dashboard extraction, and downstream integration feeds.

The object is directly relevant to the search term as_sales_methodology_tl: AS_SALES_METHODOLOGY_TL is one of the eleven documented base objects underlying this view, joined to expose the sales methodology name (SM.SALES_METHODOLOGY_NAME) alongside each opportunity log row. Users investigating the sales methodology translation table in an AST context will frequently encounter it through this view.

Underlying Base Objects

The view is defined over the following documented objects, all referenced as APPS synonyms except OE_LOOKUPS, which is a view:

• AS_LEADS_LOG — the driving table, aliased OPP, holding opportunity/lead log rows.
• HZ_PARTIES — customer party details, joined on CUSTOMER_ID = PARTY_ID.
• HZ_LOCATIONS and HZ_PARTY_SITES — address elements for the customer site.
• AS_SALES_METHODOLOGY_TL — sales methodology translation table, providing the methodology name.
• AS_SALES_STAGES_ALL_TL — sales stage translations, language-filtered.
• AS_STATUSES_TL — status code descriptions, language-filtered.
• AS_FORECAST_PROB_ALL_TL — forecast probability meanings mapped from WIN_PROBABILITY.
• OE_LOOKUPS — sales channel lookup meanings, outer-joined on CHANNEL_CODE.
• JTF_RS_RESOURCE_EXTNS (twice, aliased RES and OWNRES) and JTF_RS_GROUPS_TL — resource and sales group descriptive names.

Several joins are outer joins (denoted by (+)), so opportunity rows without a matching stage, channel, methodology, or owner resource are still returned with null descriptive columns.

Key Columns

• LOG_ID, LEAD_ID — primary identifiers for the opportunity log and originating lead.
• ORG_ID, SECURITY_GROUP_ID — Multi-Org and security context for row-level access control.
• STATUS_CODE, SALES_STAGE_ID, SALES_METHODOLOGY_ID — pipeline state identifiers, resolved to readable names via the transaction layer views.
• CUSTOMER_ID, ADDRESS_ID — party and location keys, expanded into PARTY_NAME, PARTY_NUMBER, ADDRESS1–POSTAL_CODE.
• CHANNEL_CODE, VEHICLE_RESPONSE_CODE, OFFER_ID, SOURCE_PROMOTION_ID — campaign and channel attribution fields.
• TOTAL_AMOUNT, CURRENCY_CODE, WIN_PROBABILITY, DECISION_DATE — commercial measures, with PROBABILITY_MEANING supplied from the forecast lookup.
• OWNER_SALESFORCE_ID, OWNER_SALES_GROUP_ID — ownership, resolved to OWNER_NAME, GROUP_NAME, and GROUP_DESC.
• LAST_UPDATED_BYNAME — the resource name of the last updater.
• Audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN.

Common Use Cases and Queries

Typical uses include opportunity pipeline reporting by sales methodology, owner workload analysis, and channel effectiveness measurement. Because language-dependent columns are filtered with USERENV('LANG'), results are presented in the session language.

Methodology-based pipeline analysis:

• SELECT SALES_METHODOLOGY_NAME, STATUS_CODE, SUM(TOTAL_AMOUNT) FROM AST_OPPORTUNITY_LOG_V WHERE ORG_ID = :org_id GROUP BY SALES_METHODOLOGY_NAME, STATUS_CODE;

Owner and group performance:

• SELECT OWNER_NAME, GROUP_NAME, COUNT(*) opps, AVG(WIN_PROBABILITY) avg_prob FROM AST_OPPORTUNITY_LOG_V GROUP BY OWNER_NAME, GROUP_NAME;

Customer-level detail with address, restricted by party:

• SELECT LEAD_ID, PARTY_NAME, ADDRESS1, CITY, PROBABILITY_MEANING FROM AST_OPPORTUNITY_LOG_V WHERE PARTY_NUMBER = :party_number AND DECISION_DATE > SYSDATE - 90;

Integration extracts generally select by ORG_ID and LAST_UPDATE_DATE to feed incremental loads, relying on the audit columns to bound the delta window.