Results for “ben_cvg_mlt”

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

Overview

APPS.BENBV_ENRT_BNFT_V is a Business Intelligence/benefits extract view shipped with the Oracle E-Business Suite Advanced Benefits (Oracle Benefits) module. It exposes enrollment benefit rows from the underlying transaction table in a reporting-friendly, already-decoded form. The view is defined as a read-only projection of BEN_ENRT_BNFT, filtered at query time by the current security profile's business group, and is intended for use by Benefits extract processes, concurrent programs, and custom reporting rather than for direct DML. It is available in both 12.1.1 and 12.2.2, though only 12.2.2 metadata is formally documented here.

The "BV" naming convention identifies this as a benefits view generated for extract/BI consumption. Rather than returning raw lookup codes, the view resolves them to their display meanings via HR_BIS.BIS_DECODE_LOOKUP, and it emits a descriptive flexfield marker so that downstream extract routines can identify the source context ('_DF:BEN:BEN_ENRT_BNFT:ENB'). This design makes the view particularly useful when reporting benefit offerings and their coverage-multiple parameters.

Underlying Base Objects

The view is defined over the synonym BEN_ENRT_BNFT, which resolves to the base enrollment benefit (enrollment option/plan eligibility) table in the Benefits schema. Only the columns of this single base table are projected; no joins to plan or program tables are performed inside the view.

The second referenced object is the PL/SQL package HR_BIS, used exclusively through two functions:

• HR_BIS.BIS_DECODE_LOOKUP — called repeatedly to translate each stored lookup code into its meaningful description.
• HR_BIS.GET_SEC_PROFILE_BG_ID — used in the WHERE clause to restrict rows to the business group of the current security profile, falling back to all business groups when no profile value is set.

Because the view text ends with WITH READ ONLY, the object cannot be used as a target of INSERT, UPDATE, or DELETE statements.

Key Columns

• VAL / MN_VAL / MX_VAL / INCRMT_VAL / DFLT_VAL / MX_WOUT_CTFN_VAL — value, minimum, maximum, increment, default, and maximum-without-certification thresholds for the enrollment benefit.
• NNMNTRY_UOM and its decoded description — the non-monetary unit of measure (for example, multiples of salary or a flat amount).
• CVG_MLT_CD and 'BEN_CVG_MLT' decoded value — the coverage multiple code, which is the lookup the user searched for ("ben_cvg_mlt"). This drives whether coverage is calculated as a multiple of salary, of a benefit amount, and so on.
• BNFT_TYP_CD, RT_TYP_CD, BNDRY_PERD_CD — benefit type, rate type, and boundary period codes, each paired with a decoded description.
• DFLT_FLAG, CTFN_RQD_FLAG, CRNTLY_ENRLD_FLAG, ENTR_VAL_AT_ENRT_FLAG, MX_WO_CTFN_FLAG, VAL_HAS_BN_PRORTD_FLAG — YES/NO flags controlling default behavior, certification requirements, current enrollment, value entry at enrollment, and proration.
• ENRT_BNFT_ID, BUSINESS_GROUP_ID, ELIG_PER_ELCTBL_CHC_ID, COMP_LVL_FCTR_ID, PRTT_ENRT_RSLT_ID — key identifiers linking the row to its eligibility/electable choice, compensation level factor, and prorated enrollment result.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — concurrent program context columns.
• WHO columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE) — standard audit columns.

Common Use Cases and Queries

The view is typically used to report coverage multiple settings and enrollment value thresholds for benefit options, or as a source for custom extracts that feed downstream payroll or carrier interfaces. A representative query retrieving coverage multiples for all benefits in the current business group:

• SELECT enrt_bnft_id, cvg_mlt_cd, bnft_typ_cd, val, mn_val, mx_val FROM apps.benbv_enrt_bnft_v WHERE cvg_mlt_cd IS NOT NULL;

To list offerings requiring certification and their decoded coverage multiple meaning (note that the decoded column is not aliased in the view, so refer to it by position or create a wrapper view):

• SELECT enrt_bnft_id, cvg_mlt_cd, ctfn_rqd_flag, dflt_val FROM apps.benbv_enrt_bnft_v WHERE ctfn_rqd_flag = 'Y';

Because business-group filtering is applied automatically through GET_SEC_PROFILE_BG_ID, no explicit BG predicate is required when querying as a secured user. Cross-business-group reporting requires a database session whose security profile returns NULL, otherwise only the caller's business group rows are visible.