Search Results no_merge




Overview
Explore in AI Chat

Overview

APPS.AST_SALESOPP_UWQ_REF_V is a reporting view in the Oracle E-Business Suite Applications (APPS) schema, defined over the sales credit and resource access assignment model. Its name indicates its functional role: it produces a reference mapping between sales opportunity/lead records and the resources associated with them, in a form intended for upstream consumption — typically integrated reporting, opportunity pipeline analysis, or downstream extract processes that populate external sales applications. The view is a single-statement SQL construct with no PL/SQL logic and no package dependency, which makes it lightweight and highly cacheable by the optimizer.

The defining characteristic of this view is the /*+ NO_MERGE */ hint embedded in its text. The hint instructs the Cost-Based Optimizer to prevent view merging — that is, to materialize the view result as a separate row source rather than flattening the query into the outer referencing statement. This is significant because the view applies a DISTINCT to the projected columns. Without no_merge, the optimizer may push predicates and merges into the inner block in ways that change the deduplication semantics or that defeat the intent of the DISTINCT, potentially returning duplicate resource rows. Encapsulating the DISTINCT in a non-merged inline view guarantees that the deduplication occurs at the intended level of the query and delivers a stable set of distinct (lead_id, resource_id) pairs.

Underlying Base Objects

The documented object metadata lists two base objects referenced by this view:

  • AS_ACCESSES_ALL (VIEW) — aliased as ACC. This is the resource access/assignment object that records, for a given user or resource (identified by SALESFORCE_ID), whether the access is currently active (OPEN_FLAG = 'Y') and which lead or opportunity (LEAD_ID) the access pertains to.
  • AS_SALES_CREDITS (SYNONYM) — aliased as SCD. This is the sales credit assignment object that links a SALESFORCE_ID to a LEAD_ID within the sales credit model. Because it is documented as a synonym rather than a table or view, the physical object resolves to the corresponding AOL-registered base object at compile and run time.

The two objects are equijoined on both SALESFORCE_ID and LEAD_ID, meaning that a row is produced only where a resource has both an active access row and a matching sales credit row for the same lead. This effectively intersects the access model with the credit model to produce the authoritative resource attribution for each lead.

Key Columns

  • LEAD_ID — The lead or sales opportunity identifier. This is the driving key of the result set and links the view back to the opportunity/lead entity in the CRM data model.
  • RESOURCE_ID — The resource identifier, supplied here by ACC.SALESFORCE_ID. Although the underlying column is named SALESFORCE_ID, the view exposes it as RESOURCE_ID, indicating the canonical EBS resource reference for the assigned salesperson or team member.
  • OPEN_FLAG — Not projected, but present as a filter. Only rows where the access is currently open ('Y') are returned. Closed or expired access assignments are therefore excluded from the result, which is essential for accurate current-state reporting.

Common Use Cases and Queries

The view is most often used to resolve, for a given lead, the set of active resources credited to it, deduplicated. A typical query retrieves the resource assignments for one or more leads:

  • SELECT lead_id, resource_id FROM apps.ast_salesopp_uwq_ref_v WHERE lead_id = :p_lead_id;
  • SELECT resource_id, COUNT(*) FROM apps.ast_salesopp_uwq_ref_v GROUP BY resource_id; — to summarize opportunity coverage per resource.

Because the view is defined with NO_MERGE, consumers should not rely on predicate pushdown into the inner DISTINCT block. Queries that join this view to other large objects are best written so that the view result is materialized first and then joined, which aligns with the optimizer behavior the hint intentionally enforces. This makes the view well suited to pipeline and coverage reporting in EBS 12.1.1 and 12.2.2, where consistent, non-duplicated resource-to-lead mappings are required.