Results for “ivr_ident”

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

Overview

The BEN_PGM_D view is a denormalized, user-facing representation of program definitions within the Oracle Advanced Benefits (BEN) module. It is owned by the APPS schema and resides in the BEN product family. The view draws its primary data from the BEN_PGM_F table, which stores the base program configuration records, and enriches those records by joining to a series of HR_LOOKUPS definitions and the FND_USER table. The suffix "_D" and the "Retrofitted" designation in the ETRM metadata indicate that this object was created to replace an earlier, obsolete database object and to present translated / decoded values in place of raw code columns.

In the Oracle EBS 12.1.1 and 12.2.2 environments, BEN_PGM_D serves as a reporting and integration layer. Report developers, integrators, and technical consultants query it rather than the underlying BEN_PGM_F table when they need human-readable meanings for lookup-coded flags and statuses. Because the view performs the lookup decoding at the database level, it reduces the amount of work required in downstream reports and interfaces.

Underlying Base Objects

The ETRM 12.2.2 metadata documents the following base objects referenced by the view definition:

• BEN_PGM_F (SYNONYM) — the primary program definition table, aliased as PGM in the view text. This table supplies all core columns such as PGM_ID, NAME, dates, and amounts.
• FND_USER (SYNONYM) — joined via an outer join on USER_ID = LAST_UPDATED_BY to resolve the last-updating user name.
• HR_LOOKUPS (VIEW) — joined multiple times (aliases DPNT_LEGV_ID_RQD, DPNT_DSGN_LVL, PGM_STAT, PGM_TYP, DPNT_DSGN, ACTY_REF_PERD, DPNT_ADRS_RQD, PGM_PRVDS_NO_AUTO_ENRT, DPNT_DOB_RQD, PGM_PRVDS_NO_DFLT_ENRT, ELIG_APLS) to decode lookup codes into meanings.
• HR_GENERAL (PACKAGE) — supplies the DECODE_LOOKUP function used to translate YES_NO and other coded flags inline.
• HR_API (PACKAGE) — a package referenced by the view definition per ETRM metadata.

All joins to the lookup views and FND_USER are outer joins, ensuring that program records are returned even when a lookup translation or matching user is missing.

Key Columns

The view exposes a broad column set. Noteworthy columns include:

• PGM_ID, NAME, SHORT_NAME, SHORT_CODE — program identification attributes.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — date-tracked validity range from BEN_PGM_F.
• MX_DPNT_PCT_PRTT_LF_AMT — maximum dependent percentage of participant life amount, a program-level financial parameter (the search term of interest).
• MX_SPS_PCT_PRTT_LF_AMT — maximum spouse percentage of participant life amount.
• PGM_STAT.MEANING, PGM_TYP.MEANING, DPNT_DSGN.MEANING, DPNT_DSGN_LVL.MEANING, ELIG_APLS.MEANING, ACTY_REF_PERD.MEANING — decoded lookup meanings for status, type, designation, eligibility, and activity reference period.
• DFLT_PGM_FLAG, USE_PROG_POINTS_FLAG, USE_MULTI_PAY_RATES_FLAG — YES_NO decoded flags.
• LAST_UPDATE_DATE, USER_NAME — audit trail information.

Common Use Cases and Queries

Typical usage includes program configuration reporting, dependency-amount auditing, and integration extracts that feed external benefit administration systems.

SELECT pgm_id, name, mx_dpnt_pct_prtt_lf_amt, mx_sps_pct_prtt_lf_amt
FROM   apps.ben_pgm_d
WHERE  TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;

To identify the program that defines a specific dependent percentage cap:

SELECT pgm_id, name, short_name
FROM   apps.ben_pgm_d
WHERE  mx_dpnt_pct_prtt_lf_amt IS NOT NULL;

Because the view already decodes status and type, a report may filter directly on the translated meaning columns rather than re-joining HR_LOOKUPS, simplifying the SQL and improving consistency with the application's own display logic.