Results for “as_all_interest_types_v”

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

Overview

The view AS_ALL_INTEREST_TYPES_V belongs to the Sales Foundation (AS) module of Oracle E-Business Suite, available in releases 12.1.1 and 12.2.2. It presents a flattened, denormalized hierarchy of interest types and their associated interest codes, expressed as concatenated key/value pairs. Rather than returning raw foreign-key identifiers, the view emits a composite INT_CODE_ID string and a matching PRODUCT_CATEGORY display string, enabling downstream reports, concurrent programs, and integration interfaces to consume interest-type data in a directly presentable form.

The view is not implemented as a physical database object in the referenced environment, meaning it is defined as a standalone SQL view rather than materialized or seeded through a DBA implementation script. Its primary role is to support LOVs, value sets, and reporting queries where a hierarchical combination of interest type, interest code, and parent/child codes must be presented as a single uniform list. The recurring use of the NLS_UPPER function reflects the view's design intent: language-independent case handling. By applying NLS_UPPER(ENABLED_FLAG) = 'Y', the view filters active records regardless of the session's linguistic sort and comparison behavior, ensuring consistent results across multilingual EBS deployments.

Underlying Base Objects

Although the ETRM metadata documents no referenced base objects, the view text establishes its dependency on two VL (translated) base tables:

• AS_INTEREST_TYPES_VL — the translated interest type definition, supplying INTEREST_TYPE_ID, INTEREST_TYPE, and ENABLED_FLAG.
• AS_INTEREST_CODES_VL (aliased as AIC and AIC2) — the translated interest code definition, supplying INTEREST_CODE_ID, CODE, INTEREST_TYPE_ID, PARENT_INTEREST_CODE_ID, and ENABLED_FLAG.

The view is assembled from five SELECT DISTINCT branches combined by UNION, producing a full permutation of type-only, type/ALL, type/code, type/code/ALL, and type/parent/child combinations.

Key Columns

• INT_CODE_ID — a synthetic key formed by concatenating identifiers with a forward slash, for example INTEREST_TYPE_ID || '/' || INTEREST_CODE_ID. The sentinel value -999 denotes an "ALL" rollup row.
• PRODUCT_CATEGORY — the matching display string, such as INTEREST_TYPE || '/' || AIC.CODE || '/ALL', intended for user-facing presentation.

The AS_INTEREST_TYPES_VL and AS_INTEREST_CODES_VL sources supply the enabled flag and parent-code relationships that govern which combinations appear.

Common Use Cases and Queries

The view is typically queried to populate LOVs or value sets for interest selection. A representative query:

• SELECT INT_CODE_ID, PRODUCT_CATEGORY FROM AS_ALL_INTEREST_TYPES_V ORDER BY PRODUCT_CATEGORY;
• SELECT PRODUCT_CATEGORY FROM AS_ALL_INTEREST_TYPES_V WHERE INT_CODE_ID LIKE '%/ALL';

These support filtering by rollup versus leaf values and provide case-insensitive, multilingual consistency through NLS_UPPER.