Results for “primary_interest_code”

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

Overview

BIL_DIMV_PRIM_INT_CODES is a read-only dimensional view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the BIL – Sales Intelligence product family. It presents the set of "primary interest codes" — that is, interest codes that serve as top-level or parent-level entries in Oracle's interest code hierarchy — together with a special sentinel row used to represent an "unknown" or "no selection" condition. The view is intended primarily for use as a reporting and integration dimension, supplying a clean, denormalized list of interest code identifiers and their display values for downstream ETL, Discoverer workbooks, Oracle Business Intelligence (OBIEE) repositories, and custom concurrent programs.

Underlying Base Objects

The view is defined over three documented base tables in the Oracle Advanced Sales (AS) schema: AS_INTEREST_CODES_B (the base table holding interest code definitions and their hierarchical PARENT_INTEREST_CODE_ID relationships), AS_INTEREST_CODES_TL (the translated table providing the language-specific CODE and DESCRIPTION for each interest code), and AS_INTEREST_TYPES_B (the interest type definitions, which carry the EXPECTED_PURCHASE_FLAG). A fourth source, FND_LOOKUPS, contributes a synthetic row when the lookup type BIL_VALUE_TYPE contains the lookup code '-999'. The join between AS_INTEREST_CODES_B and AS_INTEREST_CODES_TL is on INTEREST_CODE_ID and is filtered to the session language via USERENV('LANG').

Key Columns

• PRIMARY_INTEREST_CODE_ID — The numeric identifier of the primary interest code, sourced from AS_INTEREST_CODES_B.INTEREST_CODE_ID. For the sentinel row it is TO_NUMBER('-999').
• PRIMARY_INTEREST_CODE — The interest code mnemonic from AS_INTEREST_CODES_TL.CODE, or FL.MEANING for the sentinel row.
• ID — A character representation of the identifier (either TO_CHAR of the interest code ID or SUBSTR of the lookup code), suitable for use as a dimension key in reporting tools that prefer string keys.
• VALUE — The display label, truncated to 80 characters. It defaults to the translated DESCRIPTION when available, falling back to the CODE mnemonic; for the sentinel row it is the lookup meaning.

Common Use Cases and Queries

Typical uses include populating drop-down value sets, driving LOVs in concurrent programs, and joining sales pipeline facts to a primary interest code dimension. The union with the '-999' FND_LOOKUPS row ensures dashboards can display a placeholder when no interest code has been captured.

SELECT primary_interest_code_id,
       primary_interest_code,
       id,
       value
  FROM apps.bil_dimv_prim_int_codes
 ORDER BY value;

To resolve only genuine primary codes and exclude the sentinel:

SELECT primary_interest_code_id, value
  FROM apps.bil_dimv_prim_int_codes
 WHERE primary_interest_code_id != -999;

As with all APPS-owned BIL dimensional views, querying through the APPS schema inherits the appropriate row-level security and MOAC context, so the view should not be queried directly against the base tables in reporting logic.