Search Results ben_cmcd_ref_perd




Overview
Explore in AI Chat

Overview

APPS.BEN_BNFT_PRVDD_LDGR_D is a reporting view in the Oracle EBS Advanced Benefits (BEN) module that denormalizes the benefit provider ledger into a human-readable form. The underlying transactional table, BEN_BNFT_PRVDD_LDGR_F, stores the ledger of amounts provided to a benefit provider and the amounts consumed against them, tracked separately for activity-based and communication-based (CMCD) accounting periods. The view exists to resolve the numerous foreign keys and lookup codes on that table into descriptive values, so that reports, extracts, and integrations can present the ledger without re-implementing the same join logic.

The view is exposed under the APPS schema and is intended for query-only use. The name normally carries the suffix "_D" because it is a descriptive (denormalized) view, and it is the natural entry point for any report that needs provider ledger balances with readable period and flag descriptions. Users searching for "ben_cmcd_ref_perd" arrive here because the view resolves the CMCD_REF_PERD_CD lookup column into its meaning via the HR_LOOKUPS join on lookup type 'BEN_CMCD_REF_PERD'.

Underlying Base Objects

The view is defined over five base objects plus lookups:

All secondary joins are outer joins, so a ledger row is returned even when the pool, base rate, user, or a lookup value is null. The ROWID of the base table is surfaced as ROW_ID.

Key Columns

The view exposes the ledger primary key BNFT_PRVDD_LDGR_ID, its effective date range, and two parallel sets of amount columns:

Common Use Cases and Queries

Typical uses include reconciling provider ledger balances, reporting forfeited/rolled-up/utilized amounts per period, and joining ledger data to benefit enrollment extracts. A representative query filtering by communication period and effective date:

  • SELECT bnft_prvdd_ldgr_id, cmcd_ref_perd.meaning cmcd_period, cmcd_prvdd_val, cmcd_used_val, cmcd_cash_recd_val, provider_pool_name FROM apps.ben_bnft_prvdd_ldgr_d WHERE effective_start_date <= sysdate AND effective_end_date > sysdate;
  • Filtering by the resolved CMCD period meaning: ... WHERE cmcd_ref_perd.meaning = 'Pay Period';
  • Auditing updates: join LAST_UPDATED_BY to FND_USER or filter by LAST_UPDATE_DATE range to isolate recent changes to provider ledger balances.

Because the view performs outer joins, results include orphaned ledger rows still pending pool or lookup setup. Consumers should treat null meanings as unresolved configuration rather than missing data.