Results for “competitor_rank”

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

Overview

AS_OPPORTUNITY_COMPETITORS_V is a reporting and integration view in the APPS schema of Oracle E-Business Suite, delivered as part of the AS (Sales Foundation) product family. It exposes competitor information associated with sales leads and opportunities, denormalizing the competitor record held in AS_LEAD_COMPETITORS with the party name held in HZ_PARTIES. In EBS 12.1.1 and 12.2.2 the object is documented as a VALID view owned by APPS, and its structure is identical in both releases because it is a pure SQL view rather than a table-backed entity whose definition changes between the two technology stacks. The view is typically consumed by Oracle Sales, Oracle TeleSales, and Oracle Marketing components that need to display or extract the competitive landscape for a lead, and by customer-specific reports that join opportunity data to trading partners registered in the TCA party model. Because it is a view, it carries no storage of its own and inherits the read consistency and security characteristics of its base objects.

Underlying Base Objects

The documented definition references two objects: AS_LEAD_COMPETITORS (exposed as a synonym for APPS.AS_LEAD_COMPETITORS) and HZ_PARTIES (synonym for APPS.HZ_PARTIES). AS_LEAD_COMPETITORS is the transactional table that stores competitor rows attached to a lead, including rank, win/loss status, products, and descriptive flexfield columns. HZ_PARTIES is the TCA registry of parties, supplying the party name. The join is an inner equijoin on ASLCOMP.COMPETITOR_ID = PARTY.PARTY_ID, so a competitor row is returned only when the competitor identifier resolves to an existing party record. This has a practical consequence: orphaned competitor rows whose party has been merged, purged, or never created will silently drop out of the result set. The view also selects the ROWID of AS_LEAD_COMPETITORS, aliased as ROW_ID, which supports the update-through-view pattern used by Oracle Forms in the Sales Foundation module.

Key Columns

• ROW_ID — the ROWID of the underlying AS_LEAD_COMPETITORS row, used by Forms-based maintenance for optimistic locking.
• LEAD_COMPETITOR_ID — primary key of the competitor assignment; use this for single-row lookups and joins back to the base table.
• LEAD_ID — foreign key to the lead or opportunity to which the competitor is attached; the principal join key for opportunity reporting.
• COMPETITOR_ID — the party identifier of the competing organization; joins to HZ_PARTIES.PARTY_ID.
• CUSTOMER_NAME — PARTY.PARTY_NAME projected under an alias for the competitor organization's name.
• COMPETITOR_RANK — indicates the relative strength or ordering of the competitor against the lead.
• WIN_LOSS_STATUS — records the competitive outcome for the lead, supporting win/loss analytics.
• PRODUCTS — free-text or coded description of the products the competitor is fielding.
• RELATIONSHIP_PARTY_ID (relationship_party_id) — the column matching the user's search term; it identifies a related party in the TCA relationship model, associating the competitor record with a relationship party rather than only the competitor party itself. This is the column used when reports need to resolve the recognized relationship entity, for example the contact or partner through whom the competitive information was captured.
• ATTRIBUTE1 through ATTRIBUTE15, ATTRIBUTE_CATEGORY — descriptive flexfield segments for customer-defined extensions.
• Audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE.

Common Use Cases and Queries

The view is most often queried to list competitors by opportunity, to feed win/loss dashboards, and to resolve competitor names without an explicit join to HZ_PARTIES. The following query lists all competitors for a given lead:

• SELECT lead_competitor_id, lead_id, competitor_id, customer_name, competitor_rank, win_loss_status FROM as_opportunity_competitors_v WHERE lead_id = :p_lead_id ORDER BY competitor_rank;

To report on competitive presence by party relationship, filter on the relationship party identifier:

• SELECT lead_id, customer_name, relationship_party_id FROM as_opportunity_competitors_v WHERE relationship_party_id IS NOT NULL;

Aggregate win/loss analysis by competitor is a further common pattern:

• SELECT customer_name, win_loss_status, COUNT(*) FROM as_opportunity_competitors_v GROUP BY customer_name, win_loss_status;

Because the definition inner-joins HZ_PARTIES, reports that must retain competitor rows with missing party records should query AS_LEAD_COMPETITORS directly and outer-join the party registry. For integration, the view is a convenient, low-privilege source for extracting competitor data into external CRM or analytics platforms, provided the extract account holds SELECT on the APPS synonym and the requisite TCA data grants.