Search Results ben_pl_extract_identifier_f_f1

Overview

BEN.BEN_PL_EXTRACT_IDENTIFIER_F is a transactional configuration table within the Oracle E-Business Suite Advanced Benefits (BEN) module. It supports the ANSI 834 enrollment extract process by which customers transmit enrollment changes to external plan providers. Because many plan providers assign distinct plan identifiers to the same plan or option according to the indicative data of the enrolled employee, this table stores the mapping of multiple third-party identifiers to a single plan or option. The matching criteria are drawn from indicative data held on the employee's assignment and address, such as organization, job, position, people group, grade, payroll, home state, and ZIP range.

The object resides in the BEN schema and is registered in FND Design Data as BEN.BEN_PL_EXTRACT_IDENTIFIER_F. It is a date-tracked (effective-dated) table, resident in the APPS_TS_TX_DATA tablespace with a PCT Free of 10. From a Data Vault modeling perspective, the heuristic classification of this object is standalone, meaning it does not resolve cleanly into a hub, link, or satellite construct on the basis of its foreign-key structure; it is best treated as an independent reference or configuration entity rather than a core business hub.

Key Information Stored

The table is keyed by PL_EXTRACT_IDENTIFIER_ID, a system-generated surrogate primary key. The documented unique index BEN_PL_EXTRACT_IDENTIFIER_F_PK spans PL_EXTRACT_IDENTIFIER_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE, confirming that the row is effective-dated and that the surrogate key alone does not enforce uniqueness across time — the date range participates in the business key.

• PL_EXTRACT_IDENTIFIER_ID — surrogate primary key; the identifier referenced by downstream extract logic.
• PL_ID, PLIP_ID, OIPL_ID — foreign keys to BEN_PL_F, BEN_PLIP_F, and BEN_OIPL_F respectively, anchoring the record to a specific plan, plan-in-program, and option-in-plan-in-program combination.
• THIRD_PARTY_IDENTIFIER — the external plan identifier transmitted to the provider in the 834 extract; the payload value being resolved.
• ORGANIZATION_ID, JOB_ID, POSITION_ID, PEOPLE_GROUP_ID, GRADE_ID, PAYROLL_ID — indicative-data discriminators drawn from the employee assignment used to select among multiple identifiers.
• HOME_STATE, HOME_ZIP — address-based discriminators, including ZIP range, for providers that segment identifiers geographically.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the date range governing validity of the mapping.
• BUSINESS_GROUP_ID — multi-tenant (legislative data group) scoping column, supported by index BEN_PL_EXTRACT_IDENTIFIER_F_F1.
• OBJECT_VERSION_NUMBER, CREATED_BY, CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard WHO audit and optimistic-locking columns.

Indexes F2, F3, and F4 provide non-unique access paths on PL_ID, PLIP_ID, and OIPL_ID respectively, reflecting the principal lookup patterns.

Common Use Cases and Queries

The primary use case is the resolution of a third-party identifier during generation of the ANSI 834 extract. A reporting query typically joins the identifier table to the plan hierarchy and filters on the effective date and the employee's indicative attributes:

• Retrieve the active identifier for a given plan and date range: SELECT third_party_identifier FROM ben_pl_extract_identifier_f WHERE pl_id = :p_pl_id AND SYSDATE BETWEEN effective_start_date AND effective_end_date AND business_group_id = :p_bg_id;
• Resolve identifiers by indicative data using the F2/F3/F4 indexes and the discriminator columns (organization_id, job_id, position_id, people_group_id, grade_id, payroll_id, home_state, home_zip).
• Audit configuration changes across effective dates for extract troubleshooting or provider reconciliation.
• Validate that every plan/option combination in scope for an extract has at least one mapping row, to prevent missing identifiers in the outbound file.

Related Objects

The following objects are most significant in relation to this table:

• BEN.BEN_PL_F — plan definition; joined on PL_ID.
• BEN.BEN_PLIP_F — plan-in-program; joined on PLIP_ID.
• BEN.BEN_OIPL_F — option-in-plan-in-program; joined on OIPL_ID.
• PAY_PAYROLLS — referenced via PAYROLL_ID (documented foreign key).
• PAY_PEOPLE_GROUPS — referenced via PEOPLE_GROUP_ID (documented foreign key).
• HR_ORGANIZATION_UNITS, PER_JOBS, PER_ALL_POSITIONS, PER_GRADES — source of the remaining indicative-data discriminators.

Index BEN_PL_EXTRACT_IDENTIFIER_F_PK on (PL_EXTRACT_IDENTIFIER_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE) and the WHO columns (CREATED_BY, LAST_UPDATED_BY) complete the documented dependency picture.