Results for “as_opportunity_adjs_base_v”

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

Overview

The view AS_OPPORTUNITY_ADJS_BASE_V belongs to the AS – Sales Foundation product family within Oracle E-Business Suite (validated against 12.1.1 and 12.2.2). Its documented purpose is to display the base information of a lead, but in practice it is a denormalized reporting surface that joins the core opportunity/lead record in AS_OPPORTUNITIES to its associated adjustment records in AS_LEAD_ADJS, along with customer, address, lookup, sales-stage, and person reference data. This makes it a convenient single-source view for lead and opportunity analytics, dashboards, and integration extracts that must present a lead together with the original and adjusted values that Telesales/Sales users have applied.

The ETRM metadata explicitly notes that the view is "Not implemented in this database" for the documented environment, meaning it may not be compiled or deployed in every instance. Consumers should confirm existence and grants before relying on it, and it should be treated as a read-only reporting object rather than a transactional interface.

Underlying Base Objects

Although the ETRM "referenced base objects" list is documented as empty, the view text reveals its true FROM clause. The principal table is AS_OPPORTUNITIES (alias LD), which supplies lead/opportunity header information. It is joined to three aliases of AS_LEAD_ADJS — LAB (the original-lead adjustment where ORIGINAL_LEAD_FLAG = 'Y'), LA1 (additional adjustment rows for the same original lead), and LA2 (further adjustment rows, including a REVIEWED_FLAG and ADJUSTED_FOR_SALESGROUP_ID). Supporting lookups and descriptive tables include RA_CUSTOMERS (CU), RA_ADDRESSES (AD), SO_LOOKUPS (SO, for SALES_CHANNEL), AS_LOOKUPS (LU, for LEAD_STATUS), AS_SALES_STAGES_VL (SS), and PER_PEOPLE_X (PF) for the adjusting person's name. The joins are predominantly outer joins on the lookup dimensions, so leads without a matching lookup code are still returned.

Key Columns

• LEAD_ID / LEAD_NUMBER – Primary identifier and user-facing number of the lead.
• ORIGINAL_LEAD_ID – Documented key in AS_LEAD_ADJS that links each adjustment row back to the originating opportunity; central to the user's search and to the LAB/LA1/LA2 join predicates.
• STATUS / STATUS_CODE / CHANNEL / SALES_STAGE – Decoded status meaning (via AS_LOOKUPS), channel meaning (via SO_LOOKUPS), and sales-stage name, plus the raw codes carried from AS_LEAD_ADJS.
• TOTAL_AMOUNT / CURRENCY_CODE / WIN_PROBABILITY / DECISION_DATE – Forecast values on the base opportunity and on each adjustment row, enabling before/after comparison.
• CUSTOMER_NAME / CUSTOMER_NUMBER / CITY – Customer and location descriptors from RA_CUSTOMERS and RA_ADDRESSES.
• LEAD_ADJUSTMENT_ID / ADJUSTED_BY_PERSON_ID – Adjustment identity and the person who made the adjustment.
• REVIEWED_FLAG / ADJUSTED_FOR_SALESGROUP_ID – Review state and sales-group targeting of the adjustment.
• ATTRIBUTE_CATEGORY / ATTRIBUTE1–15 – Descriptive flexfield columns for extension reporting.

Common Use Cases and Queries

Typical scenarios include auditing adjustments against original lead values, reporting win probability changes, and extracting lead data for CRM or data-warehouse loads. The view also serves as a foundation for dashboards comparing base and adjusted forecast amounts.

SELECT lead_number,
       customer_name,
       status,
       total_amount,
       win_probability,
       decision_date
FROM   as_opportunity_adjs_base_v
WHERE  original_lead_id = :p_lead_id;

A second pattern aggregates adjustments per lead:

SELECT lead_number,
       original_lead_id,
       total_amount,
       win_probability
FROM   as_opportunity_adjs_base_v
WHERE  reviewed_flag = 'Y'
  AND  channel = 'DIRECT';

Because the view multiplies rows across three AS_LEAD_ADJS aliases, queries should filter on the specific adjustment alias of interest (for example, REVIEWED_FLAG for the LA2 row) to avoid duplicate lead rows, and should be tested for performance on large opportunity volumes.