Results for “employee_contribution”

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

Overview

HR_ADP_OAB_BENEFIT_V is a PL/SQL view owned by the APPS schema and shipped as part of the Oracle Human Resources (PER) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented purpose is to serve as an ADP payroll interface view, consolidating benefits enrollment, rate, and payroll element data into a denormalized projection suitable for extraction by the Automatic Data Processing (ADP) third-party payroll bridge. The view carries a status of VALID and is classified under the PER module rather than under a dedicated benefits schema, reflecting the fact that ADP extraction logic is driven by HR-level packages such as HR_ADP and HR_PAY_INTERFACE_PKG.

Functionally, the view assembles one row per assignment-level benefit participation record, enriching it with company, organization, and person identifiers, benefit plan and program descriptions, coverage type, rate start and end dates, periodicity conversion codes, and payroll element entry values. Because it bridges Oracle Advanced Benefits (OAB) constructs with ADP-ready output, it is typically queried by interface programs and by technical consultants performing reconciliation between Oracle benefit enrollments and the ADP payroll file.

Underlying Base Objects

The view is defined over a documented set of base objects spanning several schemas exposed through APPS synonyms. Benefits-related sources include BEN_ACTY_BASE_RT_F and BEN_ACTY_RT_PTD_LMT_F (activity base rates and period-to-date limits), BEN_PGM_F, BEN_PL_F, and BEN_PL_TYP_F (program, plan, and plan type definitions), BEN_PRTT_ENRT_RSLT_F (participant enrollment results), BEN_PRTT_RT_VAL (participant rate values), and BEN_PTD_LMT_F (period-to-date limits). Payroll and element data is drawn from PAY_ELEMENT_ENTRIES_F and PAY_ELEMENT_ENTRY_VALUES_F. Person and assignment context comes from PER_ALL_PEOPLE_F and PER_ALL_ASSIGNMENTS_F, with PER_TIME_PERIOD_TYPES supplying the fiscal periodicity for the DECODE-based periodicity derivation. Organization names are resolved through HR_ALL_ORGANIZATION_UNITS, and the HR_SOFT_CODING_KEYFLEX synonym provides the segment1 soft-coding value. The view also references the HR_ADP_EMP_REF_V sibling view for company code equivalence and invokes the packages HR_ADP, HR_GENERAL, HR_PAY_INTERFACE_PKG, and HR_SECURITY, the latter used to enforce row-level security on the returned person data.

Key Columns

• RT_STRT_DT / RT_END_DT — Rate effective start and end dates sourced from BPRV1 and BPRV2. The end date is adjusted by a DECODE expression that adds one day unless the record terminates at the 31-12-4712 high date, which is a common user search term (rt_end_dt) because date-range filters against this column are the most frequent cause of unexpectedly missing or duplicated benefit rows.
• ASSIGNMENT_ID, ASSIGNMENT_NUMBER, PRIMARY_FLAG — Assignment identity, with DECODE(PRIMARY_FLAG,'Y',0,ASSIGNMENT_SEQUENCE) ordering primary assignments first.
• PERSON_ID, EMPLOYEE_NUMBER, BUSINESS_GROUP_ID — Person and business group identifiers used for joins and security.
• ORGANIZATION_ID, HOU.NAME — Organization identifier and name.
• BPG.NAME, BPL.NAME, BPT.NAME — Benefit program, plan, and plan type names.
• COVERAGE_TYPE — Coverage classification from BEN participant option data.
• SCREEN_ENTRY_VALUE — Element entry screen value from PAY_ELEMENT_ENTRIES_F/VALUES_F.
• Periodicity code — DECODE of NUMBER_PER_FISCAL_YEAR (52='W', 24='S', 26='B', 12='M').
• MX_VAL — Period-to-date maximum limit value.
• Derived last-update columns — GREATEST expressions over LAST_UPDATE_DATE and EFFECTIVE_START_DATE of person, assignment, and enrollment sources, used by the interface to identify changed rows since the last ADP extract.

Common Use Cases and Queries

The principal use case is generating the ADP benefits extract. A typical reconciliation query filters on the rate end date and restricts to active assignments:

• SELECT person_id, employee_number, assignment_id, rt_strt_dt, rt_end_dt FROM apps.hr_adp_oab_benefit_v WHERE rt_end_dt >= TRUNC(SYSDATE) AND primary_flag = 'Y';
• Comparing enrollment results against BPRV rows where rt_end_dt falls between two payroll period boundaries.
• Extracting coverage and plan names for a specific business group: WHERE business_group_id = :p_bg_id.
• Detecting changed records for incremental feeds using the derived GREATEST timestamp columns.

Because HR_SECURITY is referenced, results are automatically restricted to the operating user's security profile. Queries returning fewer rows than expected are most often caused by the security profile, by the high-date adjustment on RT_END_DT, or by missing BEN_PRTT_ENRT_RSLT_F rows rather than by the view definition itself.