Search Results cmcd_ref_perd




Overview
Explore in AI Chat

Overview

BEN_BNFT_PRVDD_LDGR_D is a retrospective (denormalized, "_D" suffixed) view owned by the APPS schema in Oracle E-Business Suite Advanced Benefits (BEN). It exposes benefit-provided ledger data — the accounting-style record of values provided, used, forfeited, and reconciled against flex credit and annual credit balances — in a form that is directly resolvable by end users and reporting tools. The underlying transactional data resides in the dated "_F" table BEN_BNFT_PRVDD_LDGR_F, which stores surrogate identifiers for lookups, pools, and activity base rates. The "_D" view joins those identifiers to their descriptive attributes and decoded lookup meanings, eliminating the need for report authors to write repetitive outer joins to FND_USER and HR_LOOKUPS.

In both 12.1.1 and 12.2.2 the object is registered as a VALID view and is documented as retrofitted, meaning it belongs to the set of views re-engineered during the OAF/forms-to-ADF modernization effort. The ETRM entry records a reference to the HR_API package among the referenced objects, indicating that lookup validation for the view is serviced through the standard HR lookup API rather than a hard-coded set of values.

Underlying Base Objects

The view is defined over the following documented base objects:

  • BEN_BNFT_PRVDD_LDGR_F (synonym) — the driving table, aliased BPL, supplying all business columns and the ROWID used as ROW_ID.
  • BEN_BNFT_PRVDR_POOL_F (synonym) — provides the benefit provider pool NAME.
  • BEN_ACTY_BASE_RT_F (synonym) — provides the activity base rate NAME.
  • FND_USER (synonym) — resolves LAST_UPDATED_BY to a user identity.
  • HR_LOOKUPS (view) — consulted three times for lookup meanings: the partial rollover of unused amount flag, the activity reference period code, and the common (flex) credit reference period code.
  • HR_API (package) — supports lookup value validation and decode behaviour.

All joins to the provider pool, activity base rate, and the three HR_LOOKUPS instances are outer joins (marked with the (+) operator), so ledger rows are never lost when a referenced lookup code or pool is absent. The FND_USER join is likewise outer, guarding against orphaned LAST_UPDATED_BY values.

Key Columns

Common Use Cases and Queries

Typical uses include reconciliation of flex credit consumption by reference period, audit of forfeiture and rollover activity, and feeds into benefits reporting extracts.

  • Audit by reference period:
SELECT BNFT_PRVDD_LDGR_ID, EFFECTIVE_START_DATE,
       CMCD_REF_PERD, CMCD_PRVDD_VAL, CMCD_USED_VAL, CMCD_FRFTD_VAL
FROM   APPS.BEN_BNFT_PRVDD_LDGR_D
WHERE  CMCD_REF_PERD = '&period'
ORDER BY EFFECTIVE_START_DATE;
  • Pool-level utilisation: group by BNFT_PRVDR_POOL_NAME and sum PRVDD_VAL and USED_VAL to compare amounts granted against amounts consumed per pool.
  • Forfeiture analysis: filter where CMCD_FRFTD_VAL > 0 or ANN_FRFTD_VAL > 0, joined back to the participant through BEN_BNFT_PRVDD_LDGR_F, to identify members losing credits at period end.
  • Rollover reporting: compare CMCD_RLD_UP_VAL against PRTT_RO_OF_UNUSD_AMT_MEANING to verify whether partial rollover of unused amounts was applied.
  • Audit trail: join LAST_UPDATED_BY to FND_USER.USER_NAME to attribute manual corrections of ledger records.

Because the view performs the decoding and outer joins internally, reports written against it remain stable across 12.1.1 and 12.2.2 provided the lookup types BEN_CMCD_REF_PERD, BEN_ACTY_REF_PERD, and YES_NO retain their seeded values.