Search Results hr_adp_emp_ref_v




Overview
Explore in AI Chat

Overview

HR_ADP_EMP_REF_V is a VALID database view owned by the APPS schema within the PER (Human Resources) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented description is simply "ADP payroll interface view," indicating that it functions as an extract layer that flattens core HR person, assignment, and payroll data into the denormalized structure expected by an external ADP payroll interface. Rather than exposing raw transactional tables to third-party processing, the view consolidates the employee reference information needed to key ADP records back to EBS identities and assignments. It is registered in the ETRM as a standard, non-custom object, so it is supported and subject to patch-level change. Because it is a view rather than a table, it stores no data of its own; every query re-derives results live from the HR and Payroll base objects, which means output always reflects the current effective-dated state of the underlying records at the moment of execution.

Underlying Base Objects

The documented referenced base objects are: HR_ADP (PACKAGE), HR_GENERAL (PACKAGE), HR_SECURITY (PACKAGE), HR_SOFT_CODING_KEYFLEX (SYNONYM), PAY_PAYROLLS_F (VIEW), PER_ALL_ASSIGNMENTS_F (SYNONYM), PER_ALL_PEOPLE_F (SYNONYM), and PER_PERIODS_OF_SERVICE (SYNONYM). The core join structure links PER_ALL_ASSIGNMENTS_F (aliased PASG), PER_ALL_PEOPLE_F (PP), HR_SOFT_CODING_KEYFLEX (HS), PER_PERIODS_OF_SERVICE (PPS), and PAY_PAYROLLS_F (PPR). The HR_ADP.GET_ADP_EXTRACT_DATE function is used repeatedly to drive a consistent extract cut-off date. The view exploits the _F tables' effective-dating by selecting only the single latest row for each assignment and each period of service whose start date is on or before the extract date. The HR_SOFT_CODING_KEYFLEX synonym supplies key flexfield segment values (used twice as SEGMENT1), while PAY_PAYROLLS_F supplies the period type. HR_SECURITY and HR_GENERAL support security and general date handling within the HR_ADP package logic.

Key Columns

Common Use Cases and Queries

Typical scenarios include feeding an outbound ADP file, reconciling the employees that will be extracted on a given date, and diagnosing why an employee is missing from the ADP feed. Because the extract date is supplied by HR_ADP.GET_ADP_EXTRACT_DATE, all validation queries should be run against the same effective date to reproduce exactly what the interface would return.

  • Extract active employees for the pending run: SELECT employee_number, first_name, last_name, company_code_equivalent, period_type FROM hr_adp_emp_ref_v;
  • Verify a specific employee is included: SELECT employee_number, assignment_id, primary_flag FROM hr_adp_emp_ref_v WHERE employee_number = :p_emp_num;
  • Count employees by company for file reconciliation: SELECT company_code_equivalent, COUNT(*) FROM hr_adp_emp_ref_v GROUP BY company_code_equivalent;
  • Join back to source rows using the exposed ROWIDs: SELECT a.assignment_id FROM per_all_assignments_f a, hr_adp_emp_ref_v v WHERE a.rowid = v.asg_rowid;

Note that the view filters on assignment_type = 'E' (employee assignments only), enforces the latest effective assignment and period-of-service rows, and constrains the period of service to the maximum DATE_START not exceeding the extract date. These predicates explain most exclusions during troubleshooting.