Results for “ben_bnft_cagr_prtn”

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

Overview

APPS.BENBV_ELIGY_PRFL_V is a business view (the "BV" prefix denotes a business view in the Oracle E-Business Suite Benefits module) that presents benefit eligibility profile definitions in a fully decoded, report-ready form. In Oracle EBS 12.1.1 and 12.2.2, eligibility profiles drive which employees, dependents, and other participants qualify for a given compensation object, plan, or program. The base eligibility profile entities are stored in encoded form in the BEN_ELIGY_PRFL_F table, where columns are held as lookup codes (for example STAT_CD, ASG_TYP_CD, and numerous YES_NO flags). This view resolves those codes into their translatable meanings by invoking the HR_BIS.BIS_DECODE_LOOKUP function for each coded column, producing a denormalized, human-readable result set.

The view is intended for reporting, extracts, and integration interfaces where a consumer needs the eligibility profile definition without joining lookup tables manually. Because each coded attribute is paired with its decode, the view exposes both the raw code and the descriptive value, supporting bilingual and multi-lingual reporting requirements.

Underlying Base Objects

Per the ETRM-documented metadata, the view is owned by APPS and is defined over two referenced objects: BEN_ELIGY_PRFL_F (accessed through a synonym) and the HR_BIS package.

• BEN_ELIGY_PRFL_F — the eligibility profile base table. The view selects directly from this table (aliased ELP) and supplies most of its columns, including NAME, DESCRIPTION, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, and every coded attribute.
• HR_BIS — the Business Intelligence System package whose BIS_DECODE_LOOKUP function performs the code-to-meaning translation. Each coded column is passed to BIS_DECODE_LOOKUP along with and a lookup type, such as 'YES_NO', 'BEN_STAT', 'BEN_ASGN_TYP', 'BEN_ASMT_TO_USE', and 'BEN_BNFT_CAGR_PRTN'.
• Descriptive flexfield — the view includes the pseudo-column '_DF:BEN:BEN_ELIGY_PRFL_F:ELP', which instructs the reporting layer to expand the BEN_ELIGY_PRFL_F descriptive flexfield segments at runtime.

Key Columns

The view returns each eligibility profile with its identity, effective dating, and decoded eligibility criteria attributes.

• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE, NAME, DESCRIPTION — the profile's date-effective identity and descriptive text.
• STAT_CD — the active/inactive status code, with its BEN_STAT decode.
• ASG_TYP_CD — the assignment type (for example employee or applicant), decoded via BEN_ASGN_TYP.
• ASMT_TO_USE_CD — the assessment to use when evaluating the profile, decoded via BEN_ASMT_TO_USE.
• BNFT_CAGR_PRTN_CD — the benefits coverage group partition, decoded via BEN_BNFT_CAGR_PRTN.
• YES_NO flag columns — this is the set most relevant to a "yes_no" search. Each attribute flag is exposed both as a raw code (for example ELIG_ENRLD_PLIP_FLAG) and as its decoded YES_NO value through HR_BIS.BIS_DECODE_LOOKUP('YES_NO', ...). These include ELIG_ENRLD_PLIP_FLAG, ELIG_CBR_QUALD_BNF_FLAG, ELIG_ENRLD_PTIP_FLAG, ELIG_DPNT_CVRD_PLIP_FLAG, ELIG_DPNT_CVRD_PTIP_FLAG, ELIG_DPNT_CVRD_PGM_FLAG, ELIG_JOB_FLAG, ELIG_HRLY_SLRD_FLAG, ELIG_PSTL_CD_FLAG, ELIG_LBR_MMBR_FLAG, ELIG_LGL_ENTY_FLAG, ELIG_BENFTS_GRP_FLAG, ELIG_WK_LOC_FLAG, ELIG_BRGNG_UNIT_FLAG, ELIG_AGE_FLAG, ELIG_LOS_FLAG, ELIG_PER_TYP_FLAG, and ELIG_FL_TM_PT_TM_FLAG. Each flag indicates whether the corresponding eligibility criterion is enforced for the profile.

Common Use Cases and Queries

Typical uses include auditing which criteria a profile enforces, extracting eligibility configurations for a data warehouse, and validating profile setup during implementation.

• Identify profiles that enforce a particular rule, for example age or length of service.
• Produce a readable inventory of all eligibility profiles with their decoded status and assignment type.
• Export profile definitions for comparison across environments.

Sample query listing profiles with decoded YES_NO flags:

SELECT name,
       effective_start_date,
       effective_end_date,
       elig_enrld_plip_flag,
       hr_bis.bis_decode_lookup('YES_NO', elig_enrld_plip_flag) enrld_plip,
       elig_age_flag,
       hr_bis.bis_decode_lookup('YES_NO', elig_age_flag) age_rule
FROM   apps.benvb_eligy_prfl_v
WHERE  SYSDATE BETWEEN effective_start_date AND effective_end_date;

Because the view already performs the decode, integration and reporting code can consume the descriptive values directly, reducing join complexity and ensuring consistent presentation of YES_NO semantics.