Search Results workers_comp_code

Overview

HR_ADP_WORKERS_COMP_V is a PL/SQL view owned by the APPS schema in Oracle E-Business Suite Release 12.1.1 and 12.2.2. It belongs to the PER - Human Resources product family and serves as an ADP payroll interface view. Its purpose is to expose the workers' compensation attributes associated with employee assignments so that they can be extracted and transmitted to ADP during payroll processing.

The view consolidates data drawn from the person, assignment, job, location, organization, and payroll structures. It filters assignments to employee type only and resolves the latest effective assignment row relative to the ADP extract date returned by HR_ADP.GET_ADP_EXTRACT_DATE. The view is one of a family of HR_ADP_* interface objects that produce a flattened, ADP-consumable representation of Oracle HRMS data. Because it is a view rather than a table, no data is stored; every query executes the underlying joins against the base tables and synonyms.

For implementers, the view is significant because workers' compensation classification frequently maps to the user-defined workers compensation code carried on the job and soft-coding key flexfield rather than to a dedicated column. Users searching for "workers_comp_code" are typically looking for the column in this view that supplies that classification to ADP.

Underlying Base Objects

Per the ETRM metadata, the view is defined over the following documented base objects:

• PER_ALL_PEOPLE_F - supplies person_id, business_group_id, employee_number, effective dates, and last update date.
• PER_ALL_ASSIGNMENTS_F - the central driving entity; supplies assignment_id, assignment_number, primary_flag, assignment_sequence, payroll_id, job_id, location_id, soft_coding_keyflex_id, and effective dates.
• PER_JOBS and PER_JOBS_TL - provide the job definition and its translated name.
• HR_ALL_ORGANIZATION_UNITS - supplies the organization name for the business group or organization unit referenced in the soft coding keyflex.
• HR_SOFT_CODING_KEYFLEX - supplies SEGMENT1 (linked to the organization unit) and SEGMENT8, from which the workers' compensation code is taken.
• HR_LOCATIONS_ALL - supplies REGION_2 for the assignment location.
• PAY_JOB_WC_CODE_USAGES - supplies WC_CODE, used as a fallback when SEGMENT8 is null.
• PAY_PAYROLLS_X and PER_TIME_PERIOD_TYPES - drive the period type and its frequency conversion code.
• HR_ADP_EMP_REF_V - the ADP employee reference view that ties the assignment to the extraction set.
• HR_ADP, HR_GENERAL, and HR_SECURITY - supporting packages referenced by the view definition and its dependent logic.

The joins are inner joins except where NVL, DECODE, or GREATEST expressions are applied, so only employees with a complete assignment, job, location, and soft-coding combination appear in the result set.

Key Columns

• COMPANY_CODE_EQUIVALENT - from HR_ADP_EMP_REF_V; the ADP company identifier.
• NAME (HOU.NAME) - organization unit name sourced through soft-coding SEGMENT1.
• SEGMENT1 - soft-coding keyflex segment linking the assignment to an organization unit.
• ORGANIZATION_ID, PERSON_ID, BUSINESS_GROUP_ID, ASSIGNMENT_ID, ASSIGNMENT_NUMBER - primary identifiers for the worker and assignment.
• EMPLOYEE_NUMBER - the person's employee number; rows with a null value are excluded.
• Frequency code - a DECODE over NUMBER_PER_FISCAL_YEAR returning 'W', 'S', 'B', or 'M' for weekly, semi-monthly, biweekly, or monthly payroll periods.
• PRIMARY_FLAG and sequence - the primary assignment is returned as sequence 0; otherwise the assignment sequence is used.
• PJT.NAME - the translated job name.
• Workers' compensation code - expressed as NVL(HS.SEGMENT8, PJW.WC_CODE), taking the soft-coding segment 8 value and falling back to the job's workers compensation code when segment 8 is null. This is the column users looking for "workers_comp_code" should reference.
• REGION_2 - the location region used in ADP reporting.
• Last update date - a GREATEST expression across person, assignment, job, and job WC usage last update dates, used for incremental extraction.

Common Use Cases and Queries

Typical scenarios include validating workers' compensation classifications before an ADP extract, reconciling extracted rows against HRMS setup, and feeding downstream reporting. Because the view already resolves the latest effective assignment and the ADP extract date, callers do not need to reconstruct effective-dating logic.

• List all workers' compensation codes for active employee assignments.
• Identify assignments where the soft-coding workers' compensation segment is null and the job-level fallback is being used.
• Audit frequency codes and primary assignment indicators for a given payroll.
• Drive incremental extraction using the last update date expression.

Illustrative query:

SELECT employee_number,
      assignment_number,
      name,
      NVL(segment8, NULL) AS workers_comp_code
 FROM apps.hr_adp_workers_comp_v
 WHERE business_group_id = :p_business_group_id
   AND primary_flag = 'Y';

The actual workers' compensation value is obtained by selecting the NVL(HS.SEGMENT8, PJW.WC_CODE) expression directly, since the view does not alias it as WORKERS_COMP_CODE. Joins to HR_ADP_EMP_REF_V and the effective-date subquery are handled internally, so filters should be applied on the exposed columns such as business_group_id, employee_number, or organization_id.