Results for “elig_apls”

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

Overview

APPS.BEN_PGM_D is a denormalized reporting view over the Oracle Advanced Benefits (OAB) program configuration model. It exposes a single row per benefit program definition, keyed by PGM_ID and bounded by EFFECTIVE_START_DATE and EFFECTIVE_END_DATE, drawn from the BEN_PGM_F base table. The view's principal role is to resolve coded foreign-key columns on the base table into human-readable meanings, so that downstream reports, extracts, and integration interfaces do not have to reproduce the lookup logic themselves.

Rather than storing the decode logic per consumer, the view joins HR_LOOKUPS multiple times — once per lookup type — and calls HR_GENERAL.DECODE_LOOKUP and HR_API for additional flag and code translations. This makes BEN_PGM_D the canonical read-only surface for benefit program metadata in Oracle EBS 12.1.1 and 12.2.2, and it is the object that BI Publisher, Discoverer, and custom concurrent programs typically query when program-level attributes are required.

Underlying Base Objects

The view is owned by APPS and is defined over the following documented objects:

• BEN_PGM_F (synonym) — the driving base table holding benefit program definitions, effective-dated rows, and the coded columns being decoded.
• FND_USER (synonym) — outer-joined (USER_ID (+) = PGM.LAST_UPDATED_BY) to supply USER_NAME for the last updater.
• HR_LOOKUPS (view) — aliased ten times (e.g., DPNT_DOB_RQD, DPNT_LEGV_ID_RQD, DPNT_DSGN_LVL, PGM_STAT, PGM_TYP, DPNT_DSGN, ACTY_REF_PERD, DPNT_ADRS_RQD, PGM_PRVDS_NO_AUTO_ENRT, PGM_PRVDS_NO_DFLT_ENRT, ELIG_APLS), each outer-joined on a specific lookup type such as 'YES_NO', 'BEN_DPNT_DSGN_LVL', 'BEN_STAT', and 'BEN_PGM_TYP', to translate coded flags.
• HR_GENERAL (package) — provides DECODE_LOOKUP, used for DFLT_PGM_FLAG, USE_PROG_POINTS_FLAG, Dflt_step_cd, UPDATE_SALARY_CD, Use_multi_pay_rates_flag, USE_SCORES_CD, and SCORES_CALC_MTHD_CD.
• HR_API (package) — referenced as supporting PL/SQL logic for the view's decode-dependent columns.

All lookups are outer-joined, so a program row is preserved even when a coded value has no matching lookup entry.

Key Columns

• ROW_ID, PGM_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE — surrogate row identifier, primary key, and the effective-dating boundaries.
• NAME, SHORT_NAME, PGM_DESC, URL_REF_NAME — descriptive identity of the program.
• DPNT_DOB_RQD.MEANING — resolves DPNT_DOB_RQD_FLAG against the 'YES_NO' lookup: indicates whether a dependent's date of birth is required. This is the column most frequently associated with the "dpnt_dob_rqd" search term.
• DPNT_LEGV_ID_RQD.MEANING, DPNT_ADRS_RQD.MEANING — dependent legal-ID and address requirement flags, both decoded against 'YES_NO'.
• PGM_PRVDS_NO_AUTO_ENRT.MEANING, PGM_PRVDS_NO_DFLT_ENRT.MEANING — auto-enrollment and default-enrollment provision flags.
• DPNT_DSGN.MEANING, DPNT_DSGN_LVL.MEANING — dependent designation code and designation level (lookup type 'BEN_DPNT_DSGN_LVL').
• PGM_STAT.MEANING, PGM_TYP.MEANING, ACTY_REF_PERD.MEANING, ELIG_APLS.MEANING — program status ('BEN_STAT'), program type ('BEN_PGM_TYP'), activity reference period, and eligibility applicability.
• PGM.MX_DPNT_PCT_PRTT_LF_AMT, PGM.MX_SPS_PCT_PRTT_LF_AMT — maximum dependent and spouse percentages of participant life amount.
• DFLT_PGM_FLAG, USE_PROG_POINTS_FLAG, DFLT_STEP_CD, DFLT_STEP_RL, UPDATE_SALARY_CD, USE_MULTI_PAY_RATES_FLAG, USE_SCORES_CD, SCORES_CALC_MTHD_CD, SCORES_CALC_RL — control flags and step/score configuration, decoded via HR_GENERAL.DECODE_LOOKUP.
• LAST_UPDATE_DATE, USER_NAME — audit columns.

Common Use Cases and Queries

Typical scenarios include: auditing which benefit programs require dependent date of birth or legal ID; driving enrollment-configuration extracts; and feeding dependent-eligibility validation routines. Because all coded columns are already decoded, no additional lookup joins are needed.

Example — programs requiring dependent DOB within a date range:

SELECT pgm_id, name, short_name,
       dpr.meaning AS dpnt_dob_required,
       effective_start_date, effective_end_date
  FROM apps.ben_pgm_d dpr
 WHERE dpr.dpnt_dob_rqd_meaning = 'Yes'
   AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;

Example — active program inventory with status and type:

SELECT name, pgm_stat_meaning, pgm_typ_meaning,
       dflt_pgm_flag, user_name, last_update_date
  FROM apps.ben_pgm_d
 WHERE TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date
   AND pgm_stat_meaning = 'Active'
 ORDER BY name;

Because the view is effective-dated, queries should always restrict on EFFECTIVE_START_DATE and EFFECTIVE_END_DATE to avoid returning historical row versions.