Results for “comp_typ_meaning”

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

Overview

BEN_PL_TYP_D is a reporting view in the APPS schema within the Oracle Advanced Benefits (BEN) module. It exposes plan-type definition data from the base table BEN_PL_TYP_F, denormalizing foreign-key lookups into human-readable meaning columns so that consumers avoid additional joins. The view is titled and documented as "Retrofitted," indicating it was created to retrofit legacy or earlier-release reporting interfaces to the current 12.1.1 / 12.2.2 data model. In Oracle EBS, such _D views are datetrack-enabled reporting projections: the trailing "D" denotes a date-tracked view, meaning it surfaces the effective-dated rows and enables consumers to query by effective date semantics. The view carries no business logic of its own beyond lookup resolution and provides the IVR_IDENT column, which the user search term "ivr_ident" references. IVR_IDENT is the Interactive Voice Response identifier assigned to a plan type so a telephony or self-service interface can map a plan type to its configured voice-menu key. Reports, interfaces, and conversion programs therefore use BEN_PL_TYP_D as a stable read-only layer over the plan-type definition, isolating callers from changes to underlying lookup codes.

Underlying Base Objects

The documented ETRM metadata lists the following referenced base objects: BEN_PL_TYP_F (SYNONYM), FND_USER (SYNONYM), HR_API (PACKAGE), and HR_LOOKUPS (VIEW). The view text joins BEN_PL_TYP_F (aliased PTP) to five instances of HR_LOOKUPS and to FND_USER:

• HR_LOOKUPS aliased PL_TYP_STAT resolves PTP.PL_TYP_STAT_CD with LOOKUP_TYPE 'BEN_STAT'.
• HR_LOOKUPS aliased OPT_TYP resolves PTP.OPT_TYP_CD with LOOKUP_TYPE 'BEN_OPT_TYP'.
• HR_LOOKUPS aliased NO_MX_ENRL_NUM_DFND and NO_MN_ENRL_NUM_DFND resolve the corresponding flags with LOOKUP_TYPE 'YES_NO'.
• HR_LOOKUPS aliased OPT_DSPLY_FMT resolves PTP.OPT_DSPLY_FMT_CD with LOOKUP_TYPE 'BEN_OPT_DSPLY_FMT'.
• HR_LOOKUPS aliased COMP_TYP resolves PTP.COMP_TYP_CD with LOOKUP_TYPE 'BEN_COMP_TYP'.
• FND_USER resolves LAST_UPDATED_BY to identify the user who last modified the row.

Every lookup join is an outer join (+), so rows are retained even when a lookup value is null or unmapped. The ROW_ID column is derived from PTP.ROWID, supporting the datetrack/update use pattern.

Key Columns

• ROW_ID, PL_TYP_ID — row identifier and primary key of the plan type.
• EFFECTIVE_START_DATE, EFFECTIVE_END_DATE — datetrack validity window for the plan type definition.
• NAME, SHORT_NAME, SHORT_CODE — descriptive identifiers for the plan type.
• MX_ENRL_ALWD_NUM, MN_ENRL_RQD_NUM — maximum allowed and minimum required enrollment counts.
• PL_TYP_STAT_MEANING — status description decoded from 'BEN_STAT'.
• IVR_IDENT — Interactive Voice Response identifier for the plan type.
• NO_MX_ENRL_NUM_DFND_MEANING, NO_MN_ENRL_NUM_DFND_MEANING — decoded YES/NO flags indicating whether the max/min enrollment numbers are undefined.
• OPT_TYP_MEANING, OPT_DSPLY_FMT_MEANING, COMP_TYP_MEANING — decoded option type, option display format, and compensation type.
• LAST_UPDATE_DATE, LAST_UPDATED_BY — audit columns; LAST_UPDATED_BY returns the user identifier resolved through FND_USER.
• LEGISLATION_CODE, LEGISLATION_SUBGROUP — legislative grouping that scopes the plan type.

Common Use Cases and Queries

The view serves self-service and IVR configuration reporting, plan-type validation, and implementation/conversion audits. A representative query retrieving IVR-identified active plan types is:

• SELECT PL_TYP_ID, NAME, SHORT_NAME, IVR_IDENT, PL_TYP_STAT_MEANING FROM APPS.BEN_PL_TYP_D WHERE IVR_IDENT IS NOT NULL AND TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
• SELECT PL_TYP_ID, NAME, OPT_TYP_MEANING, COMP_TYP_MEANING FROM APPS.BEN_PL_TYP_D WHERE LEGISLATION_CODE = :p_legislation ORDER BY NAME;
• SELECT PL_TYP_ID, NAME, LAST_UPDATED_BY, LAST_UPDATE_DATE FROM APPS.BEN_PL_TYP_D WHERE LAST_UPDATE_DATE >= :p_since ORDER BY LAST_UPDATE_DATE DESC;
• SELECT NAME, MX_ENRL_ALWD_NUM, MN_ENRL_RQD_NUM, NO_MX_ENRL_NUM_DFND_MEANING, NO_MN_ENRL_NUM_DFND_MEANING FROM APPS.BEN_PL_TYP_D WHERE NO_MX_ENRL_NUM_DFND_MEANING IS NULL;

Queries should filter on effective dates where historical or future-dated definitions matter, and should be executed against the APPS schema to benefit from the documented synonyms and grants.