Results for “ben_pgm_info_types_n1”

6 results




Overview
Explore in AI Chat

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

Overview

BEN_PGM_INFO_TYPES is a configuration table in the Oracle Advanced Benefits (BEN) module of Oracle E-Business Suite, holding the definitions of Program Information Types. It resides in the BEN schema and is shipped as a VALID database object in both EBS 12.1.1 and 12.2.2. Information Types are the configurable "attributes" that an administrator attaches to a benefit program to capture supplemental data points at enrollment time — for example, dependent-related questions, coverage-level preferences, or plan-specific declarations. Each row in this table defines one reusable information type, including its legislative context, its behavioral flags, and its auditing information.

Applied as a heuristic Data Vault modeling suggestion, the mined FK structure classifies this object as a standalone table — it does not exhibit the classic hub, link, or satellite pattern driven by foreign-key dependencies on other hub keys, because the metadata does not document inbound/outbound FK relationships to other business entities. In practice it functions as a reference/configuration dimension.

Key Information Stored

The primary key is the index BEN_PGM_TYPE_INFO_TYPES_PK, which uniquely identifies each information type by the column INFORMATION_TYPE. A second unique index, BEN_PGM_INFO_TYPES_N1, is defined over (INFORMATION_TYPE, ZD_EDITION_NAME); this composite index is the primary business-key candidate and supports the multi-edition (ZD_EDITION_NAME) feature introduced in EBS 12.2.

  • INFORMATION_TYPE — surrogate/business key identifying the information type.
  • DESCRIPTION — the user-facing name or description of the information type.
  • ACTIVE_INACTIVE_FLAG — controls whether the information type is currently enabled for selection.
  • MULTIPLE_OCCURENCES_FLAG — indicates whether the attribute may be captured more than once (e.g., multiple dependents).
  • LEGISLATION_CODE — the legislative/regulatory jurisdiction under which the type is valid.
  • PROGRAM_APPLICATION_ID and PROGRAM_ID — references to the owning benefit program and application context.
  • PROGRAM_UPDATE_DATE — timestamp of the last program-level modification.
  • OBJECT_VERSION_NUMBER — optimistic-locking version column used by the OAF/ADF framework.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard WHO audit columns.
  • CREATED_BY, CREATION_DATE — creation audit trail.
  • REQUEST_ID — identifies the concurrent request that created or modified the row.
  • ZD_EDITION_NAME — the edition identifier for the 12.2 Online Patching / Edition-Based Redefinition model.

Common Use Cases and Queries

The most frequent use is administrative verification: confirming which information types are active and available for a given legislation. A typical query pattern:

  • List active information types: SELECT information_type, description FROM ben.ben_pgm_info_types WHERE active_inactive_flag = 'Y' AND legislation_code = :p_leg;
  • Find multi-occurrence attributes: filter on MULTIPLE_OCCURENCES_FLAG = 'Y' to identify types that can be captured repeatedly.
  • Edition-aware lookups (12.2): join or filter on ZD_EDITION_NAME to restrict results to the current edition.
  • Audit reporting: use REQUEST_ID and the WHO columns to trace which concurrent program and user last touched a definition.

Reporting scenarios include validating that every program referenced by PROGRAM_ID has the required information types defined before open enrollment begins.

Related Objects

The metadata classifies this object as standalone, so no FK relationship data was mined. Based on the Advanced Benefits data model, the most significant related objects include:

  • BEN_PGM_TYPES / Program definitions — joined via PROGRAM_ID and PROGRAM_APPLICATION_ID to associate information types with a benefit program.
  • BEN_PGM_TYPE_INFO_USES — the usage/assignment table that connects a program information type to enrollment records (correlating by INFORMATION_TYPE).
  • BEN_LEGAL_ENTITIES / legislation reference — joined via LEGISLATION_CODE.
  • BEN_ENRL_RQD_ITMS / enrollment information responses — capture the per-employee answers tied to each INFORMATION_TYPE.
  • FND_CONCURRENT_REQUESTS — joined via REQUEST_ID for auditing the concurrent process that modified the row.