Results for “hr_pay_interface_oab_option_v”

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

Overview

The HR_PAY_INTERFACE_OAB_OPTION_V view is a Payroll interface toolkit view owned by the APPS schema within the PER (Human Resources) product family. It is documented as a VALID database object in ETRM for Oracle EBS 12.1.1 and 12.2.2. Its purpose is to expose the current, effective option-level configuration associated with Oracle Advanced Benefits (OAB) plan offerings so that payroll interface processes and third-party payroll integrations can extract the correct option records for a given payroll period.

The view acts as a controlled, de-normalized read layer over two datetrack-enabled tables, BEN_OPT_F and BEN_OIPL_F. Rather than forcing integration code to reconcile effective dates against the population of historic rows, the view resolves each record to the single row whose effective start date is closest to, but not later than, the extract date returned by HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE. This makes it a deterministic source for point-in-time benefit option data used in payroll costing, deduction, and reporting flows.

Underlying Base Objects

The view is defined over the following documented objects:

• BEN_OPT_F (SYNONYM) — the datetracked option definition table, aliased OPT.
• BEN_OIPL_F (SYNONYM) — the datetracked table linking options to plan offerings and plan identifiers, aliased COP.
• HR_PAY_INTERFACE_PKG (PACKAGE) — supplies the GET_EXTRACT_DATE function that anchors the effective-date resolution.

The two benefit tables are joined on OPT.OPT_ID = COP.OPT_ID. Two correlated subqueries, each selecting MAX(EFFECTIVE_START_DATE) filtered by GET_EXTRACT_DATE, independently pin the correct historical row from BEN_OIPL_F and BEN_OPT_F. The join therefore returns only the current-effective option and option-in-plan-offering rows, avoiding duplication that would otherwise arise from multiple datetracked versions.

Key Columns

• COVERAGE_TYPE — exposes OPT.NAME, the option name (for example, coverage tier or enrollment option label) as defined on the option record.
• PL_ID — the plan identifier from BEN_OIPL_F, linking the option to its parent plan.
• OIPL_ID — the option-in-plan-offering identifier, the primary key of the BEN_OIPL_F record.
• OPT_LAST_UPDATE_DATE — the greatest of COP.LAST_UPDATE_DATE and OPT.EFFECTIVE_START_DATE, with NVL defaulting to 1900/01/01. This provides a reliable high-water mark for incremental extraction.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the datetrack validity window of the resolved BEN_OIPL_F row.

Common Use Cases and Queries

This view is typically consumed by payroll interface extracts, benefits-to-payroll reconciliation, and custom OAB reporting where the caller needs the option data effective as of the payroll extract date.

• List all currently effective options for payroll extraction.
• Drive incremental loads using OPT_LAST_UPDATE_DATE.
• Join to BEN_PL_F or payroll element linkages by PL_ID.

Sample query:

SELECT coverage_type, pl_id, oipl_id, opt_last_update_date, effective_start_date, effective_end_date FROM apps.hr_pay_interface_oab_option_v;

Incremental variant:

SELECT pl_id, oipl_id, coverage_type FROM apps.hr_pay_interface_oab_option_v WHERE opt_last_update_date > :last_run_date;