Search Results bim_dimv_interest_codes




Overview
Explore in AI Chat

Overview

The BIM_DIMV_INTEREST_CODES view is a read-only dimensional view owned by the APPS schema and delivered as part of the Oracle E-Business Suite BIM – Marketing Intelligence product family. It exposes marketing "interest code" reference data in a denormalized, reporting-friendly form. Interest codes represent categorized customer interests or preferences (for example, product affinities or purchase intentions) that are captured against customers and prospects and subsequently analyzed within Marketing Intelligence. The view joins the base interest code definition tables to their translations and organization-level assignments, and it filters the result set so that only interest types marked with an expected purchase flag of "Y" are surfaced. Because the view encapsulates that join and filter logic, it functions as a stable integration and reporting layer: downstream BI Publisher reports, Discoverer workbooks, OBIEE subject areas, and custom SQL can query a single object rather than reconstructing the multi-table join themselves. The view is defined WITH READ ONLY, so it may be used exclusively for query and extraction, not for data manipulation.

Underlying Base Objects

The view is defined over four documented base objects, all referenced through APPS synonyms:

  • AS_INTEREST_CODES_B – the base table holding the interest code identifier, its associated interest type, and the parent interest code relationship.
  • AS_INTEREST_CODES_TL – the translation table supplying the language-specific code and description, joined on the user session language.
  • AS_INTEREST_CODES_ALL – the organization-enabled intersection table providing the ENABLED_FLAG and ORG_ID.
  • AS_INTEREST_TYPES_ALL – the organization-level interest type assignments, used to qualify which interest types are relevant.

The joins link B to T and to MOC on INTEREST_CODE_ID, and link B to MOT on INTEREST_TYPE_ID and ORG_ID, ensuring interest codes are returned only where both the code and its type are valid for the same operating unit. The language join restricts results to the session language via USERENV('LANG').

Key Columns

  • INTEREST_CODE_ID – primary surrogate key of the interest code; the join column across all base tables.
  • INTEREST_CODE – the translated, displayable code value selected by the user (the object matching the search term "interest_code").
  • DESCRIPTION – the translated descriptive text for the interest code.
  • ENABLED_FLAG – indicates whether the interest code is active (typically "Y"/"N") for the organization.
  • ORG_ID – the operating unit or organization to which the interest code assignment belongs.
  • INTEREST_TYPE_ID – identifies the parent interest type; relevant because the view only returns types flagged for expected purchase.
  • PARENT_INTEREST_CODE_ID – self-referencing key supporting hierarchical interest code structures, enabling parent/child rollups.

Common Use Cases and Queries

Typical scenarios include populating LOVs or report parameters, validating interest codes during data migration or interface loads, and building marketing analysis on expected-purchase interest types.

  • List active interest codes for an organization:
SELECT interest_code, description
FROM   apps.bim_dimv_interest_codes
WHERE  org_id = :p_org_id
AND    enabled_flag = 'Y'
ORDER  BY interest_code;
  • Retrieve a single code by ID:
SELECT interest_code, description, interest_type_id
FROM   apps.bim_dimv_interest_codes
WHERE  interest_code_id = :p_interest_code_id;
  • Resolve hierarchy for rollup reporting:
SELECT child.interest_code, parent.interest_code AS parent_code
FROM   apps.bim_dimv_interest_codes child,
       apps.bim_dimv_interest_codes parent
WHERE  child.parent_interest_code_id = parent.interest_code_id
AND    child.org_id = :p_org_id;

Note the built-in EXPECTED_PURCHASE_FLAG = 'Y' filter: interest codes attached to non-purchasing interest types will not appear. Queries requiring the full universe of interest codes, including disabled or non-purchase types, must go directly against the base tables (AS_INTEREST_CODES_B, AS_INTEREST_CODES_TL, AS_INTEREST_CODES_ALL). Because the view is defined WITH READ ONLY, all usage is limited to SELECT statements.