Results for “benbv_prtt_rt_val_v”

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

Overview

The view APPS.BENBV_PRTT_RT_VAL_V is a business-facing reporting object within the Oracle E-Business Suite Advanced Benefits (BEN) module. It exposes the calculated financial outcomes associated with a participant's enrollment in a plan, plan option, or activity. Specifically, the view identifies information about standard rates, total flex credits, and imputed income calculated for a person enrolled in a plan or option in plan. As a "BV" (Business View) object, it is designed to present the underlying transactional data from BEN_PRTT_RT_VAL together with decoded lookup values, making it suitable for concurrent program reports, Discoverer workbooks, and integration extracts without requiring the caller to join reference tables manually. The view is defined with a WITH READ ONLY clause and enforces business group security through the session-level function HR_BIS.GET_SEC_PROFILE_BG_ID, ensuring that consumers see only rows for their currently selected business group. This is consistent with the standard Oracle EBS multi-tenant security model in release 12.1.1 and 12.2.2.

Underlying Base Objects

The view is defined over a single base object, the synonym BEN_PRTT_RT_VAL, which points to the participant rate value table owned by the APPS schema. The source table stores one row per calculated rate or value line associated with a participant enrollment result. In addition to the base synonym, the view text references the package HR_BIS, using its BIS_DECODE_LOOKUP function to translate stored code values into their human-readable meanings at query time. The HR_BIS.GET_SEC_PROFILE_BG_ID function is similarly invoked in the WHERE clause to constrain rows to the caller's business group. No other base tables are joined; all descriptive decode work is performed through lookup function calls rather than physical joins, which keeps the view self-contained. The documented columns include RT_STRT_DT, RT_END_DT, RT_OVRIDN_THRU_DT, ELCTNS_MADE_DT, RT_VAL, ANN_RT_VAL, CMCD_RT_VAL, and PP_IN_YR_USED_NUM.

Key Columns

Every base column from BEN_PRTT_RT_VAL is carried through, along with a paired decoded column for each coded attribute. The most significant columns for reporting are:

• RT_STRT_DT / RT_END_DT — Effective start and end dates of the rate.
• RT_OVRIDN_THRU_DT — Date through which an override rate applies, if any.
• ELCTNS_MADE_DT — The date the participant's elections were made.
• RT_VAL / ANN_RT_VAL / CMCD_RT_VAL — The rate value, its annualized equivalent, and the computed value used in flex credit or imputed income calculations.
• PP_IN_YR_USED_NUM — The number of pay periods in the year used when deriving per-period amounts.
• RT_TYP_CD and its decode — Identifies the rate type, for example standard rate, flex credit, or imputed income.
• CMCD_REF_PERD_CD and its decode — The communication frequency reference period. This decode is driven by the lookup type BEN_ENRT_INFO_RT_FREQ, which is the identifier frequently searched as ben_enrt_info_rt_freq. It determines the frequency context (such as per pay period, monthly, or annual) used to express the computed rate.
• PRTT_RT_VAL_STAT_CD, TX_TYP_CD, ACTY_TYP_CD, MLT_CD, and ACTY_REF_PERD_CD — Status, taxation, activity type, multiple, and activity reference period codes with matching decodes.
• BUSINESS_GROUP_ID, PRTT_ENRT_RSLT_ID, PRTT_RT_VAL_ID, and related ID columns — Keys linking back to enrollment results, element entries, coverage calculations, and payroll-related records.

Common Use Cases and Queries

This view is typically used to report participant-level rates, flex credits, and imputed income for a benefits period, and to drive downstream payroll or carrier files. Because the decode of CMCD_REF_PERD_CD depends on the BEN_ENRT_INFO_RT_FREQ lookup, the view is the standard source when validating enrollment information rate frequency values.

• List all rates for a participant enrollment result:

SELECT prtt_rt_val_id, rt_typ_cd, rt_val, ann_rt_val, cmcd_ref_perd_cd, rt_strt_dt, rt_end_dt
FROM   apps.benbv_prtt_rt_val_v
WHERE  prtt_enrt_rslt_id = :p_enrt_rslt_id
ORDER  BY rt_strt_dt, order_num;

• Report flex credits and imputed income by rate type, using the decoded meanings for readability.
• Validate that the rate frequency lookup BEN_ENRT_INFO_RT_FREQ is correctly assigned by comparing CMCD_REF_PERD_CD against PAY_PP_IN_YR or enrollment periodicity.
• Feed carrier or payroll extracts, selecting BUSINESS_GROUP_ID to respect the session security profile already applied by the view.