Results for “ben_bnft_prvdd_ldgr_f”

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

Overview

BEN_BNFT_PRVDD_LDGR_F is a table in the BEN (Advanced Benefits) schema of Oracle E-Business Suite, available in both release 12.1.1 and 12.2.2. It functions as the benefit pool ledger for a participant. In Oracle Advanced Benefits, a benefit pool is a notional account into which an employer credits value — typically flex credits or a defined monetary allowance — that a participant may then spend on eligible benefit offerings during enrollment. The ledger records how much was credited to the participant's pool, how much has been rolled up or forfeited, how much has been used, and how much cash was received, maintaining this information in a date-effective, effective-dated form so that a full historical trail of pool activity is retained.

The table carries a primary key, BEN_BNFT_PRVDD_LDGR_F_PK, defined over BNFT_PRVDD_LDGR_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE. This composite structure is characteristic of Oracle EBS date-tracked (datetrack) tables, where each logical row is versioned across effective date ranges rather than physically deleted or overwritten. Based on the foreign key and unique index structure documented in the ETRM metadata, the table is heuristically classified as satellite-leaning. From a Data Vault modeling perspective, it should be treated as a satellite attached to a participant-level hub or link, with the effective-dated columns serving as the load or change-capture dimension.

Key Information Stored

The table contains 63 documented columns. The most significant are:

• BNFT_PRVDD_LDGR_ID — the surrogate primary key column identifying the ledger entry, combined with the effective dates in BEN_BNFT_PRVDD_LDGR_F_PK.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the date-track boundaries that make each row version uniquely identifiable and support historical and point-in-time reporting.
• PER_IN_LER_ID — foreign key to BEN_PER_IN_LER, linking the ledger to the participant's enrollment result record; this is the primary business relationship anchor.
• BNFT_PRVDR_POOL_ID — identifies the benefit provider pool against which the ledger is kept.
• PRTT_ENRT_RSLT_ID — reference to the participant enrollment result that generated the ledger activity.
• FRFTD_VAL, PRVDD_VAL, RLD_UP_VAL, USED_VAL, CASH_RECD_VAL — the core monetary ledger values: forfeited amount, provided (credited) amount, rolled-up amount, used amount, and cash received amount.
• CMCD_FRFTD_VAL, CMCD_PRVDD_VAL, CMCD_RLD_UP_VAL, CMCD_USED_VAL, CMCD_CASH_RECD_VAL — the corresponding committed values.
• ANN_FRFTD_VAL, ANN_PRVDD_VAL, ANN_RLD_UP_VAL, ANN_USED_VAL, ANN_CASH_RECD_VAL — the annualized equivalents for the same monetary dimensions.
• PRTT_RO_OF_UNUSD_AMT_FLAG — flag governing rollover of unused amounts.
• BUSINESS_GROUP_ID — the enterprise business group context for multi-organization data separation.
• ACTY_BASE_RT_ID, ACTY_REF_PERD_CD, CMCD_REF_PERD_CD — activity rate and reference period identifiers supporting rate-based crediting.
• BPL_ATTRIBUTE_CATEGORY and BPL_ATTRIBUTE1 … BPL_ATTRIBUTE30 — descriptive flexfield (DFF) columns allowing client-specific extensions.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE, OBJECT_VERSION_NUMBER — standard Oracle WHO columns and optimistic locking.

The surrogate key BNFT_PRVDD_LDGR_ID matters for foreign key references, while the unique index combining it with the effective dates is the business-key candidate for lifecycle integrity.

Common Use Cases and Queries

Typical uses include auditing participant pool balances, reconciling credits against expenditures, reporting on forfeited or unused amounts, and diagnosing enrollment-time balance calculations that hand off to payroll or the benefit provider. A common pattern retrieves the currently effective ledger row for a participant enrollment result:

• SELECT bpl.* FROM ben_benft_prvdd_ldgr_f bpl WHERE bpl.prtt_enrt_rslt_id = :p_result_id AND TRUNC(SYSDATE) BETWEEN bpl.effective_start_date AND bpl.effective_end_date;
• Point-in-time balance extraction joins BEN_PER_IN_LER on PER_IN_LER_ID and filters by the desired effective date.
• Aggregated crediting vs. usage reporting sums PRVDD_VAL and USED_VAL grouped by BNFT_PRVDR_POOL_ID.
• Variance analysis compares FRFTD_VAL and CASH_RECD_VAL across annualized (ANN_) and committed (CMCD_) measures.

Because the table is date-effective, queries must always constrain on the effective date range to avoid double counting historical versions.

Related Objects

• BEN_PER_IN_LER — parent participant enrollment result table; joined via BEN_BNFT_PRVDD_LDGR_F.PER_IN_LER_ID = BEN_PER_IN_LER.PER_IN_LER_ID. This is the only documented foreign key relationship.
• BEN_BNFT_PRVDR_POOL_F / BEN_BNFT_PRVDR_POOL — the provider pool definition referenced by BNFT_PRVDR_POOL_ID.
• BEN_PRTT_ENRT_RSLT_F — the participant enrollment result providing PRTT_ENRT_RSLT_ID context.
• BEN_PER_BNFT_POOLS_F — participant-level benefit pool setup.
• BEN_BNFT_POOL_ACTY_RT_F — pool activity rate definitions referenced through ACTY_BASE_RT_ID.
• BEN_BENEFIT_ACTIONS and BEN_PERSON_BENEFIT_ACTIONS — lifecycle tables whose processing can trigger ledger updates.
• BEN_BNFT_PRVDD_LDGR_F_PK — unique index enforcing surrogate plus date-track uniqueness.