Search Results customer_category_code




Overview
Explore in AI Chat

Overview

AS_OPPORTUNITIES_V is a Sales Foundation (AS) view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It presents opportunity and lead records from the sales pipeline together with customer master attributes sourced from Oracle Trading Community Architecture (TCA). Because the Sales Foundation stores both leads and opportunities in a single table (AS_LEADS_ALL), this view supplies the opportunity-oriented projection of that pipeline, enriched with party details that are not physically stored on the lead row itself.

The view is defined by joining AS_LEADS_ALL to HZ_PARTIES on the customer identifier, which allows reporting tools, concurrent programs, and custom integrations to retrieve opportunity data and the associated customer name, number, category, and type in one pass. The inclusion of CUSTOMER_CATEGORY_CODE — aliased from HZ_PARTIES.CATEGORY_CODE — makes the view particularly useful for segmenting opportunities by customer classification without requiring a separate lookup against the TCA registry.

Underlying Base Objects

The ETRM metadata documents two synonym-based base objects:

  • AS_LEADS_ALL — the master table holding both lead and opportunity rows, aliased as OPP in the view definition.
  • HZ_PARTIES — the TCA party registry, aliased as PARTY.

The join condition is OPP.CUSTOMER_ID = PARTY.PARTY_ID. Because this is an inner join, only lead/opportunity records that resolve to a valid party are returned; rows with an orphaned or null customer reference are excluded. The view is a pure projection and join — it introduces no aggregation, filtering on status or deleted flag, or Org ID restriction. Consequently, callers must apply their own predicates for operating unit security and for excluding soft-deleted rows (DELETED_FLAG).

Key Columns

Common Use Cases and Queries

The view is typically consumed by custom reports and extracts that need pipeline data alongside customer attributes. A frequent requirement is grouping open opportunities by customer category, which is where the CUSTOMER_CATEGORY_CODE column is used:

SELECT customer_category_code, COUNT(*) opp_count, SUM(total_amount) pipeline_value FROM apps.as_opportunities_v WHERE deleted_flag = 'N' AND org_id = :p_org_id GROUP BY customer_category_code;

Another common pattern filters opportunities for a specific customer or party number prior to integration with an external CRM:

SELECT lead_id, lead_number, customer_name, sales_stage_id, total_amount, currency_code FROM apps.as_opportunities_v WHERE customer_number = :p_party_number AND deleted_flag = 'N';

Because the view performs no Org ID or deletion filtering, every query should explicitly constrain ORG_ID for operating unit isolation and DELETED_FLAG for active records. For high-volume extracts, joining back to AS_LEADS_ALL directly may be preferable when only native lead columns are required, since the inner join to HZ_PARTIES restricts the result set to party-matched rows.