Search Results excld_flag_m




Overview
Explore in AI Chat

Overview

APPS.BENBV_ELIG_MLTRY_STAT_PRTE_V is a read-only Oracle EBS view in the Benefits (BEN) module that exposes eligibility profile rules based on military status. The suffix convention "BV" denotes a business view intended for display and reporting layers, while "PRTE" indicates a participation or eligibility rule component. The view is defined with the WITH READ ONLY clause, meaning it cannot be used as a DML target and is intended strictly for query, reporting, and integration consumption. Its primary function is to present the military status participation rule rows that belong to an eligibility profile, joining surrogate identifiers with their decoded lookup values so downstream users and interfaces do not need to resolve lookup codes manually.

For the search term "mltry_stat_cd", this view is the canonical reporting surface. The column MLTRY_STAT_CD appears directly in the projection and is additionally passed through the HR_BIS.BIS_DECODE_LOOKUP function so that both the raw code and the decoded meaning are available in a single query. This makes the view particularly useful for benefits administrators, extracts, and integration partners that must interpret military status eligibility without accessing the underlying flexfields or lookup APIs separately.

Underlying Base Objects

The view is defined over one base object and one package dependency, as documented in the ETRM metadata:

  • BEN_ELIG_MLTRY_STAT_PRTE_F — referenced in the FROM clause with the alias ELI. The trailing "_F" identifies a dated (date-tracked) entity table in the Benefits schema, which is why the view exposes EFFECTIVE_START_DATE and EFFECTIVE_END_DATE. The "_PRTE" segment confirms the table stores participation/eligibility rule detail rows keyed by ELIG_MLTRY_STAT_PRTE_ID and linked to an eligibility profile via ELIGY_PRFL_ID.
  • HR_BIS — a package (synonym) referenced through the BIS_DECODE_LOOKUP function. This function performs lookup decoding and is invoked twice, once against lookup type 'LOOKUP_NOT_FOUND' for MLTRY_STAT_CD and once against 'YES_NO' for EXCLD_FLAG.

Because the metadata records BEN_ELIG_MLTRY_STAT_PRTE_F as a SYNONYM, the view resolves through the synonym to the actual application table. No other joins, tables, or views are present in the documented projection, so this is a single-table decode view with no aggregation.

Key Columns

  • EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — Date-tracked validity window inherited from the base table, governing when the military status rule row is in force.
  • ORDR_NUM — Ordering sequence controlling the evaluation order of the participation rule relative to other rule components in the eligibility profile.
  • MLTRY_STAT_CD — The military status lookup code that the rule matches. This is the column associated with the term "mltry_stat_cd".
  • HR_BIS.BIS_DECODE_LOOKUP('LOOKUP_NOT_FOUND', MLTRY_STAT_CD) — The decoded, human-readable meaning of the military status code. The 'LOOKUP_NOT_FOUND' token indicates the formatter behavior when a code lacks a lookup entry.
  • EXCLD_FLAG — Flag indicating whether the rule is exclusionary (a match disqualifies) or inclusive.
  • HR_BIS.BIS_DECODE_LOOKUP('YES_NO', EXCLD_FLAG) — Decoded Yes/No text for the exclusion indicator, using the YES_NO lookup type.
  • ELIG_MLTRY_STAT_PRTE_ID — Primary key of the underlying participation rule row, the unique identifier used for joins and tracing back to the base table.
  • ELIGY_PRFL_ID — Foreign key to the eligibility profile header to which this military status rule belongs.

Common Use Cases and Queries

Typical scenarios include auditing the military status criteria behind a given eligibility profile, extracting decoded values for downstream reporting, and verifying exclusion flags before benefits processing. The sample below lists all military status rules for a specific eligibility profile, including decoded text:

SELECT ELIGY_PRFL_ID,
       ELIG_MLTRY_STAT_PRTE_ID,
       MLTRY_STAT_CD,
       HR_BIS.BIS_DECODE_LOOKUP('LOOKUP_NOT_FOUND', MLTRY_STAT_CD) AS MLTRY_STAT_MEANING,
       EXCLD_FLAG,
       HR_BIS.BIS_DECODE_LOOKUP('YES_NO', EXCLD_FLAG) AS EXCLD_MEANING,
       ORDR_NUM,
       EFFECTIVE_START_DATE,
       EFFECTIVE_END_DATE
FROM   APPS.BENBV_ELIG_MLTRY_STAT_PRTE_V
WHERE  ELIGY_PRFL_ID = :p_eligy_prfl_id
ORDER  BY ORDR_NUM;

A second common query filters by the code requested by the user, to identify which eligibility profiles reference a particular military status and whether that reference excludes or includes it:

SELECT ELIGY_PRFL_ID, ELIG_MLTRY_STAT_PRTE_ID, ORDR_NUM, EXCLD_FLAG
FROM   APPS.BENBV_ELIG_MLTRY_STAT_PRTE_V
WHERE  MLTRY_STAT_CD = :p_mltry_stat_cd
AND    TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE AND NVL(EFFECTIVE_END_DATE, TRUNC(SYSDATE))
ORDER  BY ELIGY_PRFL_ID, ORDR_NUM;

Because the view is read-only and performs no joins, queries against it are inexpensive and suitable for concurrent programs, BI Publisher data models, and inbound integration extracts.