Search Results ben_pl_extract_identifier_f




Overview
Explore in AI Chat

Overview

BEN.BEN_PL_EXTRACT_IDENTIFIER_F is a transactional configuration table in the Oracle Advanced Benefits (BEN) module. It supports the ANSI 834 extract delivered by Oracle EBS, which allows customers to transmit enrollment changes and related benefit data to Plan providers. Its specific purpose is to resolve the plan identifier that must be sent for a given plan or option when a provider allocates different identifiers based on the indicative data of the participant — for example, the employee's people group, job, position, grade, payroll, home state, or home zip. Without this mapping table, the ANSI 834 output could not reliably produce the correct third-party plan identifier for each participant segment.

From a Data Vault modeling perspective, the ETRM classification heuristic returns standalone for this object. Although it carries a single foreign key to PAY_PEOPLE_GROUPS, the absence of broader hub-and-link dependencies suggests it is best treated as a standalone mapping or reference entity rather than a true hub, link, or satellite. It is an effective-dated, business-group-scoped rules table.

Key Information Stored

The table carries 22 documented columns. The most significant are:

The documented business key is the unique index BEN_PL_EXTRACT_IDENTIFIER_F_PK, composed of PL_EXTRACT_IDENTIFIER_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE, confirming the table's effective-dated design.

Common Use Cases and Queries

Typical scenarios include auditing which provider identifier will be sent for a participant, validating that no gaps or overlaps exist in effective dates, and troubleshooting ANSI 834 output where the wrong plan code appears.

SELECT PL_EXTRACT_IDENTIFIER_ID, PL_ID, PLIP_ID, OIPL_ID,
       THIRD_PARTY_IDENTIFIER, PEOPLE_GROUP_ID,
       EFFECTIVE_START_DATE, EFFECTIVE_END_DATE
FROM   BEN.BEN_PL_EXTRACT_IDENTIFIER_F
WHERE  PL_ID = :p_plan_id
AND    TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
ORDER BY PEOPLE_GROUP_ID, ORGANIZATION_ID;

A second common pattern checks for overlapping effective ranges per plan, and a third joins to PAY_PEOPLE_GROUPS to resolve the group name for reporting.

Related Objects

  • PAY_PEOPLE_GROUPS — joined on PEOPLE_GROUP_ID; the only documented foreign key.
  • BEN_PL_EXTRACT_IDENTIFIER_F_PK — the composite unique index enforcing the effective-dated business key.
  • BEN_PL_F, BEN_PLIP_F, BEN_OIPL_F — plan, plan-in-program, and option-in-program definitions referenced through PL_ID, PLIP_ID, and OIPL_ID.
  • PER_ALL_PEOPLE_F / PER_ASSIGNMENTS_F — supply the indicative data dimensions matched at extract time.
  • BEN_PL_EXTRACT_* family tables — the broader ANSI 834 extract configuration set consumed by the Benefits extract concurrent programs.
  • HR_ORGANIZATION_UNITS and PER_JOBS / PER_POSITIONS — descriptive lookups for the discriminator columns.