Search Results per_oab_new_benefits_model

Overview

APPS.HR_CERIDIAN_500_EMPLOYEE_V is a reporting view in Oracle E-Business Suite (available in both 12.1.1 and 12.2.2) that consolidates employee demographic, address, assignment, payroll, and period-of-service information into a single flat structure intended for Ceridian payroll and benefits extracts. The name references the Ceridian 500 interface (the "500" series of extract formats), and the view is purpose-built to feed downstream HR/payroll integration processes with a stable, pre-joined recordset rather than requiring integrators to assemble data from the many normalized PER_ and HR_ tables themselves.

Functionally, the view joins the person record (PER_ALL_PEOPLE_F) to assignment data (PER_ALL_ASSIGNMENTS_F), periods of service (PER_PERIODS_OF_SERVICE), address history (PER_ADDRESSES), the business group organization unit (HR_ALL_ORGANIZATION_UNITS), a companion spouse view (HR_CERIDIAN_500_SPOUSE_V), and soft-coding keyflex data. It also invokes the HR_CERIDIAN package to derive the current extract date and the FND_PROFILE package to branch behavior based on the PER_OAB_NEW_BENEFITS_MODEL profile option. This makes the view an integration artifact rather than a purely informational reporting object.

Underlying Base Objects

Per ETRM 12.2.2 metadata, the view is owned by APPS and is defined over the following documented base objects: FND_PROFILE (package), HR_ALL_ORGANIZATION_UNITS (synonym), HR_CERIDIAN (package), HR_CERIDIAN_500_SPOUSE_V (view), HR_SOFT_CODING_KEYFLEX (synonym), PER_ADDRESSES (synonym), PER_ALL_ASSIGNMENTS_F (synonym), PER_ALL_PEOPLE_F (synonym), and PER_PERIODS_OF_SERVICE (synonym).

Key relationships visible in the view text include: pp.person_id = pad.person_id (person to address), pp.person_id = sp.person_id (+) (outer join to spouse data), and a filter pj.assignment_type = 'E' restricting the assignment join to employee-type assignments. The package calls hr_ceridian.get_cer_extract_date and fnd_profile.value supply runtime values that shape date display and derived flags.

Key Columns

• Employee identity: employee_number, last_name, first_name, middle_names, suffix, national_identifier, person_id, business_group_id.
• Demographics: sex, date_of_birth, date_of_death, spouse_date_of_birth (from the spouse view).
• Address: address_line1, address_line2, town_or_city, region_2, add_information17, postal_code, date_from, plus a decoded date_to expression bounded by the Ceridian extract date.
• Assignment/payroll: payroll_id, date_start, leaving_reason, final_process_date, adjusted_svc_date (derived either from periods of service or from attribute3).
• Derived/profile-driven: smoker_flag (decoded from uses_tobacco_flag when the new benefits model profile is 'Y'), adjusted_service_date, and a greatest() column representing the most recent effective date across address, service, and person records.

Common Use Cases and Queries

Typical uses include Ceridian employee extracts, benefits eligibility checks, and reconciliation of employee master data prior to payroll transmission.

• Extract active employees for a Ceridian feed:
  SELECT employee_number, last_name, first_name, national_identifier, payroll_id FROM apps.hr_ceridian_500_employee_v;
• Identify employees with dependents for benefits:
  SELECT employee_number, last_name, spouse_date_of_birth FROM apps.hr_ceridian_500_employee_v WHERE spouse_date_of_birth IS NOT NULL;
• Locate leavers for final processing:
  SELECT employee_number, leaving_reason, final_process_date FROM apps.hr_ceridian_500_employee_v WHERE final_process_date IS NOT NULL;

Because the view depends on profile options and the HR_CERIDIAN package, deployments should verify that PER_OAB_NEW_BENEFITS_MODEL and the extract date are correctly configured, and that the caller's security context permits access to the underlying PER_ objects.