Search Results ben_icd_transaction




Overview
Explore in AI Chat

Overview

The BEN.BEN_ICD_TRANSACTION table is a core transactional object within the Oracle Advanced Benefits (BEN) module of Oracle E-Business Suite 12.1.1 and 12.2.2. It stores individual transaction records generated by the benefits processing engine when elements, plans, rates, and activities are evaluated for participants. Each row represents a discrete unit of work — such as a rate calculation, element entry creation, or eligibility evaluation — carrying the input values that drive the transaction and the state information required to track it through completion.

The table is owned by the BEN schema and is documented as VALID in ETRM 12.2.2 with 126 columns. A heuristic Data Vault classification mined from the foreign key structure identifies this object as standalone, though its high column count and presence of multiple foreign keys suggest it functions more usefully as a link entity in a Data Vault model — connecting rate definitions, cost allocation structures, and person/assignment contexts — with the many INPUT_VALUE and ATTRIBUTE columns behaving as satellite-style descriptors.

Key Information Stored

The surrogate primary key is ICD_TRANSACTION_ID, enforced by BEN_ICD_TRANSACTION_PK and also exposed as the unique index candidate BEN_ICD_TRANSACTION_U1. Notable foreign keys include ICD_CHC_RATE_ID referencing BEN_ICD_CHC_RATES, and COST_ALLOCATION_KEYFLEX_ID referencing PAY_COST_ALLOCATION_KEYFLEX.

Common Use Cases and Queries

Typical applications include diagnostics of benefits processing failures, reconciliation of calculated rates against payroll element entries, and audit reporting of cost allocation distribution.

  • Failed transaction diagnosis: SELECT ICD_TRANSACTION_ID, PERSON_ID, STATUS, REASON FROM BEN.BEN_ICD_TRANSACTION WHERE STATUS = 'ERROR';
  • Join to rate definitions: SELECT t.ICD_TRANSACTION_ID, r.<rate_column> FROM BEN.BEN_ICD_TRANSACTION t JOIN BEN.BEN_ICD_CHC_RATES r ON t.ICD_CHC_RATE_ID = r.ICD_CHC_RATE_ID;
  • Participant-level activity: Filter by PERSON_ID and EFFECTIVE_START_DATE to reconstruct what the engine evaluated for a person on a given date.
  • Cost allocation audit: Join COST_ALLOCATION_KEYFLEX_ID to PAY_COST_ALLOCATION_KEYFLEX to verify GL distributions.
  • Flexfield reporting: Query ENTRY_INFORMATION1..30 and ATTRIBUTE_CATEGORY for customer-specific extracts.

Related Objects

  • BEN.BEN_ICD_CHC_RATES — Referenced via ICD_CHC_RATE_ID; defines the rate processed by the transaction.
  • PAY.PAY_COST_ALLOCATION_KEYFLEX — Referenced via COST_ALLOCATION_KEYFLEX_ID; supplies cost allocation structure.
  • PER_ALL_PEOPLE_F — Join on PERSON_ID for participant details.
  • PER_ALL_ASSIGNMENTS_F — Join on ASSIGNMENT_ID for assignment context.
  • PAY_ELEMENT_TYPES_F — Join on ELEMENT_TYPE_ID for element definition metadata.
  • PAY_ELEMENT_ENTRIES_F — Join on ELEMENT_ENTRY_ID for resulting payroll entries.
  • BEN_ICD_TRANSACTION_PK / BEN_ICD_TRANSACTION_U1 — Primary key and unique index enforcing identity.