Search Results eml_pk




Overview
Explore in AI Chat

Overview

BEN.BEN_ELIG_MLTRY_STAT_PRTE_F is a date-effective eligibility criteria table within the Oracle E-Business Suite Benefits (BEN) schema. It identifies which person military statuses are included in, or excluded from, an eligibility profile. The criteria recorded here must be satisfied for a person to qualify to participate in the associated compensation object, such as a benefits plan or program. The table resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10, and its indexes are stored in APPS_TS_TX_IDX. The object is documented as VALID in ETRM 12.2.2 and follows the standard Oracle EBS "_F" (date-tracked) convention, carrying EFFECTIVE_START_DATE and EFFECTIVE_END_DATE for temporal versioning. From a Data Vault modeling perspective, the mined heuristic classification is standalone; rather than a pure hub, link, or satellite, the metadata indicates no outbound foreign keys declared at the database level, so the table functions as a self-contained eligibility criteria record keyed by eligibility profile.

Key Information Stored

  • ELIG_MLTRY_STAT_PRTE_ID (NUMBER(15), mandatory): System-generated surrogate primary key for the criteria row.
  • EFFECTIVE_START_DATE (DATE, mandatory): Start of the date-effective range for the criterion.
  • EFFECTIVE_END_DATE (DATE, mandatory): End of the date-effective range.
  • ELIGY_PRFL_ID (NUMBER(15)): Foreign key to BEN_ELIGY_PRFL_F identifying the parent eligibility profile.
  • MLTRY_STAT_CD (VARCHAR2(30)): Military status code being evaluated (include or exclude).
  • EXCLD_FLAG (VARCHAR2(30)): Y or N, indicating whether the military status is excluded (Y) or included (N).
  • ORDR_NUM (NUMBER): Display or evaluation order of the criterion.

The surrogate key is ELIG_MLTRY_STAT_PRTE_ID, while the unique index EML_PK — searched by the user as "eml_pk" — constitutes the business-key candidate and spans (ELIG_MLTRY_STAT_PRTE_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE). A secondary nonunique index, BEN_ELIG_MLTRY_STAT_PRTE_F_N1, covers ELIGY_PRFL_ID to support profile-level lookups. The physical primary key is documented as BEN_ELIG_MLTRY_STAT_PRTE_F_PK on the same three columns.

Common Use Cases and Queries

Typical use cases include benefits eligibility diagnostics, profile configuration reporting, and data migration or integration checks. A common pattern retrieves all military status criteria for a given profile as of a specific date:

SELECT a.ELIG_MLTRY_STAT_PRTE_ID, a.MLTRY_STAT_CD, a.EXCLD_FLAG, a.ORDR_NUM
FROM BEN.BEN_ELIG_MLTRY_STAT_PRTE_F a
WHERE a.ELIGY_PRFL_ID = :p_profile_id
AND TRUNC(SYSDATE) BETWEEN a.EFFECTIVE_START_DATE AND a.EFFECTIVE_END_DATE
ORDER BY a.ORDR_NUM;

Another scenario validates whether a person is excluded due to military status by joining eligibility profiles to this criteria table and filtering on EXCLD_FLAG = 'Y'. Reporting extracts frequently denormalize MLTRY_STAT_CD into readable lookups, and audits compare effective-dated versions to detect configuration drift. Because the EML_PK index leads with ELIG_MLTRY_STAT_PRTE_ID, direct lookups by criteria ID and date are index-driven and efficient.

Related Objects

  • BEN.BEN_ELIGY_PRFL_F: Parent eligibility profile table; joined via ELIGY_PRFL_ID.
  • BEN.BEN_ELIG_MLTRY_STAT_PRTE_F#: The underlying table object referenced by this table's documentation.
  • BEN_ELIG_MLTRY_STAT_PRTE_F_PK: Primary key constraint on (ELIG_MLTRY_STAT_PRTE_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE).
  • BEN_ELIG_MLTRY_STAT_PRTE_F_N1: Nonunique index facilitating ELIGY_PRFL_ID access paths.
  • Military status reference (MLTRY_STAT_CD) values drawn from person military status setup, used for decode and validation.

These relationships make BEN_ELIG_MLTRY_STAT_PRTE_F a criteria-level extension of the eligibility profile framework, central to determining compensation object participation.