Results for “ben_unsspnd_enrt”

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

Overview

APPS.BENBV_LER_BNFT_RSTRN_V is a read-only Oracle EBS view that exposes benefit restriction rules defined at the Life Event Reason (LER) level within Oracle Advanced Benefits (OAB). The "BV" prefix denotes that the object is a business view intended for reporting and integration consumption rather than for direct transactional maintenance. The view surfaces the restriction attributes that govern how a participant's enrollment options are constrained when a specific life event reason triggers a benefits re-enrollment, including coverage minimums, coverage maximums, increment rules, and the treatment of pending certifications.

The view is defined with WITH READ ONLY, preventing any DML through the view, and it applies security-profile-based row filtering so that only records belonging to the session's permitted business group are returned. This makes it suitable for concurrent programs, BI Publisher reports, Oracle Discoverer workbooks, and outbound integration extracts that must respect EBS security profiles.

Underlying Base Objects

Per the documented ETRM metadata, the view is defined over two referenced objects: the synonym BEN_LER_BNFT_RSTRN_F (resolved to the underlying APPS base table) and the HR_BIS package.

• BEN_LER_BNFT_RSTRN_F — the primary base table holding life event reason benefit restriction rows. The view aliases it as LBR and selects effective-dated rows from it.
• HR_BIS — the HR business intelligence server package, invoked through HR_BIS.BIS_DECODE_LOOKUP to translate stored lookup codes into their user-facing meanings, and through HR_BIS.GET_SEC_PROFILE_BG_ID to enforce business group security in the WHERE clause.

The relationship is a straightforward one-to-many projection: each qualifying row in the base table produces one row in the view, enriched with decoded lookup values and filtered by the security profile business group.

Key Columns

• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the date range for which the restriction rule is valid.
• DFLT_TO_ASN_PNDG_CTFN_RL / DFLT_TO_ASN_PNDG_CTFN_CD — the rule and decoded value controlling whether pending certifications default to assumed (the column referenced by the search term ben_dflt_to_asn_pndg_ctfn). The lookup type is BEN_DFLT_TO_ASN_PNDG_CTFN.
• MX_CVG_ALWD_AMT, MN_CVG_AMT, MX_CVG_RL, MN_CVG_RL — maximum and minimum coverage amounts and their associated rules, defining the permitted coverage range.
• MX_CVG_INCR_ALWD_AMT, MX_CVG_INCR_WCF_ALWD_AMT, MX_CVG_MLT_INCR_NUM, MX_CVG_MLT_INCR_WCF_NUM — the maximum coverage increment amounts and multipliers, with and without coverage factor ("WCF") variants.
• MX_CVG_WCFN_AMT, MX_CVG_WCFN_MLT_NUM — maximum coverage amounts and multipliers where no coverage factor applies.
• CVG_INCR_R_DECR_ONLY_CD — decoded via BEN_CVG_INCR_R_DECR_ONLY, indicating whether coverage may only increase or decrease.
• UNSSPND_ENRT_CD — decoded via BEN_UNSSPND_ENRT, controlling unsuspension behavior on enrollment.
• NO_MN_CVG_INCR_APLS_FLAG, NO_MX_CVG_AMT_APLS_FLAG, NO_MX_CVG_INCR_APLS_FLAG — Yes/No flags (lookup YES_NO) that suppress application of minimum coverage increments, maximum coverage amounts, and maximum coverage increments respectively.
• LBR.LER_BNFT_RSTRN_ID, LBR.LER_ID, LBR.BUSINESS_GROUP_ID, LBR.PL_ID, LBR.PLIP_ID — identity columns linking the restriction to the LER, business group, plan, and plan-in-program context.
• WHO columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE for audit traceability.
• Descriptive flexfield token — the literal '_DF:BEN:BEN_LER_BNFT_RSTRN_F:LBR' signals the DFF context for the underlying table.

Common Use Cases and Queries

This view is typically used to audit or report on the restriction rules applied when a life event reason drives re-enrollment. A common pattern combines it with the LER and plan views to produce a rule inventory.

• Pending certification defaults — locate LERs where pending certifications are assumed or denied by default. This is directly relevant to the searched column.
• Coverage boundary review — identify LERs whose minimum or maximum coverage amounts or increments differ from corporate standards.
• Migration and reconciliation — compare restriction rules across business groups or between environments.
• Effective-dating analysis — list currently active rules using SYSDATE against the effective date columns.

Example query targeting the searched attribute:

SELECT ler_id,
       effective_start_date,
       effective_end_date,
       dflt_to_asn_pndg_ctfn_cd,
       dflt_to_asn_pndg_ctfn_rl
FROM   apps.benbv_ler_bnft_rstrn_v
WHERE  TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date
ORDER  BY ler_id;

Example query examining coverage limits together with suppression flags:

SELECT ler_id,
       mn_cvg_amt,
       mx_cvg_alwd_amt,
       mx_cvg_mlt_incr_num,
       no_mx_cvg_amt_apls_flag,
       no_mx_cvg_incr_apls_flag
FROM   apps.benbv_ler_bnft_rstrn_v
WHERE  no_mx_cvg_amt_apls_flag = 'N';

Because the view is secured and read-only, it can be safely queried from custom reports, extracts, and integration layers without risk to the underlying benefits configuration data.