Results for “as_interest_types_vl”

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

Overview

AS_INTEREST_TYPES_VL is a seeded, customer-facing view owned by the APPS schema in Oracle E-Business Suite, belonging to the AS (Sales Foundation) product family. As its description states, it presents interest types within a single organization and single language context. In the EBS multi-org and multi-lingual architecture, the "VL" suffix denotes a view that joins the base table (_B) and translation table (_TL) while resolving the current session's organization and language. This view is the principal reporting and integration surface for the Interest Type lookup used by Oracle Sales, TeleSales, and Oracle Marketing (TCA/AS foundation) to classify contacts, leads, and opportunities according to expressed customer interests, such as product preferences or hobbies.

The object is reported as VALID in the ETRM metadata for 12.1.1 and 12.2.2, and its definition is consistent across both releases.

Underlying Base Objects

The view is defined over three base objects, all synonyms resolved to APPS tables in the AS schema:

• AS_INTEREST_TYPES_B — the base (non-translated) table holding interest type IDs, descriptive flags, seeding attributes, and audit columns.
• AS_INTEREST_TYPES_TL — the translation table storing the language-dependent INTEREST_TYPE name and DESCRIPTION, keyed by LANGUAGE = USERENV('LANG').
• AS_INTEREST_TYPES_ALL — the multi-org (operating unit) extension table carrying ORG_ID and an override of ENABLED_FLAG. The join to this table is an outer join (MOT), meaning interest types with no org-specific row are still returned.

The organization filter uses USERENV('CLIENT_INFO') to derive the current operating unit, comparing it to MOT.ORG_ID with an NVL fallback to -99, which is the standard EBS multi-org security predicate applied to org-striped views.

Key Columns

• INTEREST_TYPE_ID — primary key linking all three base tables.
• INTEREST_TYPE and DESCRIPTION — the translated, user-facing name and text, sourced from _TL.
• ENABLED_FLAG — the effective enablement status, computed as NVL(MOT.ENABLED_FLAG, 'Y'), so an org-level override governs, otherwise the value defaults to enabled.
• ORG_ID — the operating unit from the _ALL table, or NULL for global interest types.
• MASTER_ENABLED_FLAG — the base-table ENABLED_FLAG, aliased to distinguish it from the effective flag.
• COMPANY_CLASSIFICATION_FLAG, CONTACT_INTEREST_FLAG, LEAD_CLASSIFICATION_FLAG, EXPECTED_PURCHASE_FLAG, CURRENT_ENVIRONMENT_FLAG — usage indicators that determine which business processes (company classification, contact interest, lead classification, expected purchase, current environment) may consume the interest type.
• PRODUCT_CATEGORY_ID and PRODUCT_CAT_SET_ID — optional linkage to product categories and the category set, supporting interest types tied to specific products.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard WHO audit columns propagated from the base table.

Common Use Cases and Queries

Typical applications include populating interest-type LOVs on contact and lead forms, filtering interest types for a specific classification process, and extracting the translated list for data migration or integration.

• List all enabled interest types for the current session's org and language:

  SELECT interest_type_id, interest_type, description
  FROM   as_interest_types_vl
  WHERE  enabled_flag = 'Y'
  ORDER BY interest_type;

• Retrieve interest types eligible for lead classification:

  SELECT interest_type_id, interest_type
  FROM   as_interest_types_vl
  WHERE  lead_classification_flag = 'Y'
  AND    enabled_flag = 'Y';

• Identify interest types linked to a product category:

  SELECT interest_type_id, interest_type, product_category_id
  FROM   as_interest_types_vl
  WHERE  product_category_id IS NOT NULL;

• Reconcile effective versus master enablement:

  SELECT interest_type, org_id, enabled_flag, master_enabled_flag
  FROM   as_interest_types_vl
  WHERE  enabled_flag <> master_enabled_flag;

Because the view applies language and org predicates automatically, reports and integrations can query it directly without reproducing multi-org or translation joins, ensuring consistent, session-aware results across Oracle EBS 12.1.1 and 12.2.2.