Search Results first_rows




Overview
Explore in AI Chat

Overview

APPS.AMS_CONTACT_PRODUCTS_V is a reporting and integration view in the Oracle E-Business Suite 12.1.1 and 12.2.2 environments, owned by the APPS schema. Despite its name, the view does not expose contact records directly; it presents a normalized, flattened list of enabled interest types and their associated interest codes as they relate to products available for expectation/purchase tracking in Oracle Marketing and related modules. The view consolidates header-level interest type definitions with their child interest codes into a single result set using a UNION of two component queries, allowing applications and interfaces to treat types and codes uniformly through a shared set of column aliases.

Underlying Base Objects

The documented base objects referenced by the view are:

The first branch of the UNION joins AS_INTEREST_TYPES_B to AS_INTEREST_TYPES_TL, filtering on enabled_flag = 'Y' and expected_purchase_flag = 'Y', and restricts language via USERENV('LANG'). The second branch extends the join to AS_INTEREST_CODES_B and AS_INTEREST_CODES_TL, additionally requiring bp.parent_interest_code_id IS NULL and bp.enabled_flag = 'Y'. Both branches carry the hint /*+ FIRST_ROWS USE_NL(bi ti bp tp) */, indicating a design optimized for interactive, row-by-row retrieval rather than bulk aggregation. Because all four base objects are referenced through SYNONYM entries, the view remains portable across database links and schema configurations.

Key Columns

  • ID — a surrogate identifier concatenating the source context; sourced from bi.interest_type_id in the first branch and bp.interest_code_id in the second, enabling a unified key across both record types.
  • INTEREST_TYPE_ID — character-converted interest type identifier, consistent across both UNION branches as to_char(bi.interest_type_id).
  • INTEREST_CODE_ID — populated with -1 for type-only rows and the actual code identifier for code-level rows.
  • INTEREST_TYPE — the descriptive interest type name from the translation table (TI.INTEREST_TYPE).
  • Code — blank for type-level rows, and the translated code value (TP.CODE) for code-level rows.
  • 'as_interest_types_b' / 'as_interest_codes_b' — literal discriminator columns identifying the originating base table, useful for downstream branching logic.

Common Use Cases and Queries

Applications and custom reports use this view to populate interest/expectation LOVs, product qualifier lists, and integration payloads that require both parent types and their child codes. A typical query filtering type-level rows is:

  • SELECT id, interest_type_id, interest_type FROM apps.ams_contact_products_v WHERE interest_code_id = -1; — returns only enabled interest types.
  • SELECT id, interest_type, code FROM apps.ams_contact_products_v WHERE interest_code_id > 0; — returns enabled, top-level interest codes.
  • SELECT interest_type, code FROM apps.ams_contact_products_v WHERE interest_type = :p_type; — drives dependent LOVs where codes are filtered by a selected type.

Because both branches honor USERENV('LANG'), consumers automatically receive language-appropriate descriptions when the session language is set. The view is read-only and intended for inquiry; any lookup by the user term "carbage bi" is not reflected in the documented metadata or in the view definition, and the underlying content pertains solely to interest type and interest code structures.