Search Results ben_icd_transaction_u1

Overview

BEN.BEN_ICD_TRANSACTION is a transactional staging table in the Oracle E-Business Suite Advanced Benefits (BEN) schema. It holds unapproved Individual Compensation Distribution (ICD) records — that is, compensation entries (such as spot bonuses, awards, or other individual payments) that have been captured through the ICD self-service flow or via API, but which have not yet been processed and approved by the benefits administrative workflow. Once approved, these rows are typically consumed by the benefits element entry engine and written to the underlying element entry structures.

The table resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10, reflecting its role as an active transactional store rather than a reference table. The ETRM metadata assigns a heuristic Data Vault classification of standalone, meaning the model does not suggest a classic hub/link/satellite decomposition. From a modeling perspective, this reflects the object's nature as a self-contained transaction staging entity whose rows are short-lived and whose lifecycle is bounded by the approval process.

Key Information Stored

Each row represents a single unapproved ICD distribution line. The columns of greatest analytical and functional importance are:

• ICD_TRANSACTION_ID — the system-generated surrogate primary key (BEN_ICD_TRANSACTION_PK), drawn from a sequence. This is also the single business-key candidate identified by the unique index BEN_ICD_TRANSACTION_U1.
• PERSON_ID — foreign key to PER_ALL_PEOPLE_F, identifying the recipient of the compensation.
• ASSIGNMENT_ID — foreign key to PER_ALL_ASSIGNMENTS_F, tying the distribution to a specific assignment.
• TRANSACTION_ID and TRANSACTION_STEP_ID — foreign keys to HR_API_TRANSACTIONS and HR_API_TRANSACTION_STEPS, linking the row to the originating API transaction flow.
• ICD_CHC_RATE_ID — foreign key to BEN_ICD_CHC_RATES, pointing to the check/rate definition used for the distribution.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE / EFFECTIVE_DATE — date-track and effective dating controls.
• DML_OPERATION and DATETRACK_MODE — record the operation type (INSERT, UPDATE, DELETE) and the date-track mode applied.
• ELEMENT_TYPE_ID, ELEMENT_LINK_ID, ELEMENT_ENTRY_ID — target element definition and any existing entry linkage.
• INPUT_VALUE_ID1 through INPUT_VALUE_ID15 and INPUT_VALUE1 through INPUT_VALUE15 — the flexfield-style capture of up to fifteen input values fed into the element entry.
• PL_TYP_ID, PL_ID, OIPL_ID, OPT_ID — references to plan type, plan, option-in-plan, and option definitions.
• STATUS, ITEM_KEY, ITEM_TYPE — workflow/status tracking fields used by the approval process.
• COST_ALLOCATION_KEYFLEX_ID — foreign key to PAY_COST_ALLOCATION_KEYFLEX, pointing to the cost allocation structure applied.
• OBJECT_VERSION_NUMBER / E_OBJECT_VERSION_NUMBER — optimistic locking controls consistent with the OAF framework.

The table carries 126 documented columns in total, including the standard WHO columns (LAST_UPDATE_DATE, CREATED_BY, etc.) and 20+ descriptive attribute columns (ATTRIBUTE1–ATTRIBUTE20, ENTRY_INFORMATION1–ENTRY_INFORMATION30) available for customer extensions.

Common Use Cases and Queries

The primary operational use case is monitoring and troubleshooting of pending ICD approvals. Administrators commonly query the table to identify stalled or rejected distributions:

• List all unapproved ICD records for a person: SELECT icd_transaction_id, person_id, element_type_id, status FROM ben.ben_icd_transaction WHERE person_id = :p_person_id;
• Count pending distributions by plan type: SELECT pl_typ_id, COUNT(*) FROM ben.ben_icd_transaction WHERE status IS NULL GROUP BY pl_typ_id;
• Reconcile ICD transactions against element entries to detect orphaned or failed writes.
• Audit DML activity by filtering on DML_OPERATION and DATETRACK_MODE.
• Reporting of ICD spend by element type or plan using ELEMENT_TYPE_ID and the INPUT_VALUE columns.

Because the table is short-lived by design, high row counts are not unusual during peak enrollment periods, and ITEM_KEY is frequently used to correlate rows with the concurrent workflow process.

Related Objects

• BEN.BEN_ICD_CHC_RATES — joined on ICD_CHC_RATE_ID to resolve rate/check details.
• HR_OPERATING_UNITS / HR_ALL_ORGANIZATION_UNITS — related via BUSINESS_GROUP_ID.
• PER_ALL_PEOPLE_F — joined on PERSON_ID for recipient details.
• PER_ALL_ASSIGNMENTS_F — joined on ASSIGNMENT_ID.
• HR_API_TRANSACTIONS and HR_API_TRANSACTION_STEPS — the API transaction framework that generates rows.
• PAY_COST_ALLOCATION_KEYFLEX — joined on COST_ALLOCATION_KEYFLEX_ID.
• BEN_PL_TYP_F, BEN_PL_F, BEN_OIPL_F, BEN_OPT_F — plan configuration definitions linked through PL_TYP_ID, PL_ID, OIPL_ID, and OPT_ID.
• PAY_ELEMENT_ENTRIES_F / element entry APIs — the downstream consumers of approved ICD records.

Together these relationships position BEN_ICD_TRANSACTION as the junction between the benefits plan configuration, the person/assignment model, and the payroll element entry engine.