Search Results benbv_bnft_prvdd_ldgr_v




Overview
Explore in AI Chat

Overview

The view BENBV_BNFT_PRVDD_LDGR_V is a business-facing database view owned by the APPS schema within the Oracle Advanced Benefits (BEN) product module. Its documented purpose is to identify the benefit pool ledger details for a participant and a benefit provider pool. In the Oracle EBS 12.1.1 and 12.2.2 environments, this view serves as a simplified, secure reporting and integration layer over the underlying benefit provider pool ledger data.

The view presents a flattened, denormalized representation of ledger activity, exposing amount and quantity values alongside their period-based contexts. It encapsulates several technical details that would otherwise require joins and lookups to resolve, including descriptive flexfield references, lookup code translation, and security profile filtering. Because it is defined WITH READ ONLY, it is intended strictly for query and reporting purposes and cannot be used to insert, update, or delete benefit pool ledger records.

The view plays a significant role in operational reporting, data extraction, and integration scenarios where downstream systems or concurrent programs require benefit pool ledger balances without navigating the full normalized table structure of Advanced Benefits.

Underlying Base Objects

According to the documented ETRM metadata, BENBV_BNFT_PRVDD_LDGR_V is defined over two referenced base objects: the synonym BEN_BNFT_PRVDD_LDGR_F and the package HR_BIS. The primary data source is BEN_BNFT_PRVDD_LDGR_F, the benefit provider pool ledger base table from which all ledger rows are drawn. The view aliases this table as BPL in its defining query.

The HR_BIS package is referenced through the function HR_BIS.BIS_DECODE_LOOKUP, which translates stored lookup codes into their display meanings, and through HR_BIS.GET_SEC_PROFILE_BG_ID, which applies business group security. The view's defining query joins these elements by selecting from BEN_BNFT_PRVDD_LDGR_F and applying a WHERE clause restricting rows to the session's secured business group via NVL(HR_BIS.GET_SEC_PROFILE_BG_ID, BPL.BUSINESS_GROUP_ID). This construction enforces row-level security so that users see only the ledger data for their authorized business group.

Key Columns

The view exposes a comprehensive set of ledger measurement and control columns. The date range columns EFFECTIVE_START_DATE and EFFECTIVE_END_DATE define the validity period of each ledger record. The core financial values include FRFTD_VAL (forfeited value), PRVDD_VAL (provided value), RLD_UP_VAL (rolled-up value), USED_VAL, and CASH_RECD_VAL (cash received). Parallel sets of these amounts are provided for cumulative (CMCD_ prefix) and annual (ANN_ prefix) reference periods.

Code columns ACTY_REF_PERD_CD and CMCD_REF_PERD_CD indicate the activity reference period, and each is accompanied by a decoded meaning column (ACTY_REF_PERD_CD_M, CMCD_REF_PERD_CD_M) resolved through the BEN_ACTY_REF_PERD lookup. The flag PRTT_RO_OF_UNUSD_AMT_FLAG controls whether unused amounts are rolled over, with its decoded value in PRTT_RO_OF_UNUSD_AMT_FLAG_M using the YES_NO lookup. The descriptive flexfield reference is exposed as _DF:BPL.

Identifier columns include BNFT_PRVDD_LDGR_ID (primary key), PER_IN_LER_ID, ACTY_BASE_RT_ID, BUSINESS_GROUP_ID, BNFT_PRVDR_POOL_ID, and PRTT_ENRT_RSLT_ID. Standard WHO columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY) complete the column list.

Common Use Cases and Queries

Typical use cases include reporting participant benefit pool balances, reconciling provided versus used and forfeited amounts, and integrating ledger balances into external systems. A representative query retrieving active ledger rows for a given pool follows:

  • SELECT bnft_prvdd_ldgr_id, per_in_ler_id, bnft_prvdr_pool_id, pr vdd_val, used_val, effective_start_date
  • FROM apps.benbv_bnft_prvdd_ldgr_v
  • WHERE bnft_prvdr_pool_id = :p_pool_id
  • AND SYSDATE BETWEEN effective_start_date AND effective_end_date;

Another common scenario examines rollover behavior by filtering on prtt_ro_of_unusd_amt_flag and comparing frftd_val against rl_d_up_val. Because the view is read only and secured by business group, it is well suited for extraction programs and BI queries that must respect security profiles while presenting decoded, business-friendly ledger information.