Search Results first_rows

Overview

AMS_CONTACT_PRODUCTS_V is an APPS-owned view in the Oracle E-Business Suite Marketing (AMS) module. It is documented under ETRM 12.2.2 with a status of VALID and is described as a view used by Contact Preferences that stores interest types and interest type codes. Functionally, the view presents the hierarchy of interest types (topics) and their associated interest codes (sub-topics) that a contact may register a preference against, which is the data foundation for the Contact Preferences feature in Oracle Marketing.

The view is important in reporting and integration because it provides a denormalized, language-aware, code-and-description payload for preference topics. Downstream consumers — including Contact Preferences setup forms, personalization flows, and external campaign or preference-management integrations — can read a single flat structure rather than joining the underlying interest code base and translation tables themselves. Because it exposes both the internal ID and the human-readable name and code, it is well suited to LOV-style lookups and to extract feeds. The view is defined as a UNION of two branches, which means each row represents either an interest type (at the highest level) or an interest code belonging to a type.

Underlying Base Objects

The documented referenced base objects are AS_INTEREST_TYPES_B, AS_INTEREST_TYPES_TL, AS_INTEREST_CODES_B, and AS_INTEREST_CODES_TL, all accessed through synonyms in the APPS schema. The _B tables hold the base, language-independent definitions; the _TL tables hold the translated name values and are filtered by the user's session language using USERENV('LANG'). This pairing follows the standard Oracle EBS MLS (Multi-Language Support) pattern.

The first UNION branch selects enabled interest types (ENABLED_FLAG = 'Y') that are flagged for expected purchase (EXPECTED_PURCHASE_FLAG = 'Y') and tags them with the literal source marker 'AS_INTEREST_TYPES_B'. The second branch joins interest types to their child interest codes, restricted to those with a null PARENT_INTEREST_CODE_ID (i.e., root-level codes) and enabled codes, and tags them with 'AS_INTEREST_CODES_B'. The view text carries the /*+ FIRST_ROWS USE_NL(BI TI BP TP) */ hint in both branches, instructing the optimizer to favor a first-rows response time and to drive the joins with nested loops.

Key Columns

The metadata lists the exposed columns as PREFERENCE_TOPIC_TYPE_ID, INTEREST_TYPE_ID, INTEREST_CODE_ID, INTEREST_TYPE, PREFERENCE_TOPIC_TYPE_CODE, and PREFERENCE_TOPIC_TYPE. In the view text the projected expressions alias the leading identifiers as ID, the interest type identifier, the interest code identifier, the interest type name, the code value, and a literal source-table marker, which map onto the documented column set.

• ID / PREFERENCE_TOPIC_TYPE_ID — the primary identifier of the row; for a type row this is the interest type ID, and for a code row it is the interest code ID (returned as -1 for the type branch).
• INTEREST_TYPE_ID — the interest type the row belongs to, returned as a character value; present on both branches.
• INTEREST_CODE_ID — the specific interest code identifier, populated only for the second branch and -1 for type-level rows.
• INTEREST_TYPE / PREFERENCE_TOPIC_TYPE — the translated name of the interest type, sourced from AS_INTEREST_TYPES_TL.
• PREFERENCE_TOPIC_TYPE_CODE — the interest code value from AS_INTEREST_CODES_TL, empty for type-level rows.

Common Use Cases and Queries

Typical uses include populating preference topic lists, validating that a chosen interest type or code is enabled for expected purchase, and extracting the preference taxonomy for external systems. Filtering on the marker column distinguishes type-level rows from code-level rows. A representative query is:

• SELECT interest_type_id, interest_code_id, interest_type, preference_topic_type_code FROM apps.ams_contact_products_v ORDER BY interest_type, preference_topic_type_code;
• SELECT * FROM apps.ams_contact_products_v WHERE interest_code_id > 0; — returns only the code-level rows.
• SELECT * FROM apps.ams_contact_products_v WHERE interest_code_id = -1; — returns only the interest type rows.

Performance-sensitive queries benefit from the FIRST_ROWS and USE_NL hints embedded in the view, which favor fast returns of initial rows and are consistent with the "first_rows" search that surfaced this object.