Results for “ben_elig_ee_stat_prte_d”

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

Overview

The view APPS.BEN_ELIG_EE_STAT_PRTE_D is a denormalized reporting object within the Oracle Advanced Benefits (BEN) product family. It presents eligibility participation criteria that are driven by employee assignment status, resolving the underlying foreign-key identifiers into human-readable meanings and user names. In Oracle EBS 12.1.1 and 12.2.2 the object carries a VALID status and is owned by the APPS schema. The trailing _D suffix conventionally denotes a "descriptive" or resolved view, distinguishing it from the base table BEN_ELIG_EE_STAT_PRTE_F (the _F suffix denoting the base/fact table). The ETRM documentation describes the object only as "Retrofitted," indicating that the view was regenerated or re-created to conform to a later release's column and join conventions. Its principal role is to support reporting, data extraction, and integration scenarios where eligibility rules must be evaluated or audited in terms that business users can interpret, rather than as opaque numeric identifiers. Because it joins status-amendment logic, it is directly relevant to the per_ass_status_type_amends search term that surfaced it, since PER_ASS_STATUS_TYPE_AMENDS participates in the view definition.

Underlying Base Objects

The documented referenced base objects for this view are:

• BEN_ELIG_EE_STAT_PRTE_F (SYNONYM) — the driving eligibility participation criterion table.
• PER_ASSIGNMENT_STATUS_TYPES (SYNONYM) — assignment status type definitions.
• PER_ASS_STATUS_TYPE_AMENDS (SYNONYM) — assignment status type amendments, which override the base status definition.
• HR_LOOKUPS (VIEW) — lookup values, here used to resolve the EXCLD_FLAG against the YES_NO lookup type.
• FND_USER (SYNONYM) — the application user who last updated the row.
• HR_API (PACKAGE) — a documented referenced object, typically providing security or descriptive resolution.

The view text shows the core join path: the base criterion row is joined to PER_ASSIGNMENT_STATUS_TYPES on ASSIGNMENT_STATUS_TYPE_ID, and that status type is outer-joined to PER_ASS_STATUS_TYPE_AMENDS on the same identifier (the (+) outer-join token). The resolved status text is produced by NVL(ASA.USER_STATUS, AST.USER_STATUS), so an amended user status takes precedence over the base status when present. FND_USER is outer-joined on LAST_UPDATED_BY, and HR_LOOKUPS is outer-joined for the exclusion flag.

Key Columns

• ELIG_EE_STAT_PRTE_ID — primary identifier of the eligibility participation criterion row.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — date-tracked validity window of the criterion.
• EXCLD_FLAG — inclusion/exclusion indicator, resolved through HR_LOOKUPS (YES_NO) into a MEANING.
• ORDR_NUM — evaluation order of the criterion.
• ASSIGNMENT_STATUS_TYPE_ID — the assignment status type driving the criterion.
• The NVL(ASA.USER_STATUS, AST.USER_STATUS) expression — the resolved, human-readable status label.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, and FUSER.USER_NAME — audit/traceability information.

Note that the view exposes MEANING (from HR_LOOKUPS) and the resolved user status without a stable alias in the documented column list; consumers should reference them positionally or via the underlying expressions.

Common Use Cases and Queries

Typical scenarios include auditing which assignment statuses included or excluded employees from a program, tracing status amendments that override base statuses, and feeding eligibility rules into downstream extracts. A representative query:

SELECT e.ELIG_EE_STAT_PRTE_ID,
       e.EFFECTIVE_START_DATE,
       e.EFFECTIVE_END_DATE,
       e.MEANING            AS EXCLD_MEANING,
       e.ORDR_NUM,
       NVL(asa.USER_STATUS, ast.USER_STATUS) AS STATUS_TEXT,
       e.LAST_UPDATE_DATE,
       f.USER_NAME
FROM   APPS.BEN_ELIG_EE_STAT_PRTE_D e,
       APPS.PER_ASSIGNMENT_STATUS_TYPES ast,
       APPS.PER_ASS_STATUS_TYPE_AMENDS asa,
       APPS.FND_USER f
WHERE  ast.ASSIGNMENT_STATUS_TYPE_ID = e.ASSIGNMENT_STATUS_TYPE_ID
AND    asa.ASSIGNMENT_STATUS_TYPE_ID (+) = ast.ASSIGNMENT_STATUS_TYPE_ID
AND    f.USER_ID (+) = e.LAST_UPDATED_BY
AND    e.EFFECTIVE_START_DATE <= SYSDATE
AND    NVL(e.EFFECTIVE_END_DATE, SYSDATE+1) > SYSDATE;

Because the view already encapsulates the status-amendment resolution, it removes the need for consumers to reproduce the NVL and outer-join logic themselves, making it the preferred access point for reporting against assignment-status-based eligibility criteria.