Search Results per_all_workforce_v




Overview
Explore in AI Chat

Overview

PER_ALL_WORKFORCE_V is a VALID Oracle E-Business Suite view owned by the APPS schema and shipped as part of the PER (Human Resources) product family. As its name and ETRM description indicate, it presents a unified "workforce" picture: the view returns employees, and returns contingent workers as well when the relevant profile option setup permits. This makes it the canonical single-source query point when a report, interface, or integration needs to resolve a person to a workforce identity without first knowing whether that person is a payroll employee or a non-payroll worker.

The object is a VIEW, not a table, so it carries no storage of its own; it is a read-only projection layered over the core person tables. ETRM records the object as VALID in the APPS schema under product PER, and it is documented identically for the 12.1.1 and 12.2.2 releases. Because it is APPS-owned, it should be queried directly as APPS.PER_ALL_WORKFORCE_V by custom code that has been granted APPS access, rather than by reimplementing the same union logic locally.

Underlying Base Objects

The documented base objects referenced by this view are PER_ALL_PEOPLE_F (accessed through a SYNONYM), the HR_PERSON_NAME package, and the FND_PROFILE package. In practice the view text is a UNION ALL of two branches, each selecting from PER_ALL_PEOPLE_F but filtered differently:

The profile option HR_TREAT_CWK_AS_EMP is therefore the switch that determines whether contingent workers appear at all. HR_PERSON_NAME supplies the formatted name derivation underlying the name columns, while FND_PROFILE is invoked to read the site-level profile value at query time. Because current flags are used, the view reflects the currently effective person records rather than full historical rows.

Key Columns

  • PERSON_ID — the primary person identifier, the correct join key to other PER tables.
  • WORKER_NUMBER — the user's search term and the most important attribute here. It is the polymorphic identifier: EMPLOYEE_NUMBER for employees and NPW_NUMBER for contingent workers, depending on which UNION branch produced the row.
  • FULL_NAME / ORDER_NAME — display and reversed ("last, first") name forms derived via HR_PERSON_NAME.
  • EMPLOYEE_NUMBER, NPW_NUMBER, APPLICANT_NUMBER — the raw underlying identifiers, exposed individually so callers can distinguish worker types when needed.
  • CURRENT_EMPLOYEE_FLAG, CURRENT_NPW_FLAG — flags indicating whether the row is a current employee or current non-payroll worker.
  • EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, BUSINESS_GROUP_ID — date-effectivity bounds and the business group (legislation/HR operating unit) context.

Common Use Cases and Queries

Typical uses include workforce listings, reconciliation of number-to-person mappings, and integrations that must resolve any worker to a single identifier. A basic lookup by the searched attribute is:

SELECT person_id, worker_number, full_name, current_employee_flag, current_npw_flag FROM apps.per_all_workforce_v WHERE worker_number = :p_worker_number;

A simple workforce roster filtered to a business group:

SELECT worker_number, full_name, order_name FROM apps.per_all_workforce_v WHERE business_group_id = :p_bg_id ORDER BY order_name;

Because WORKER_NUMBER is the union-derived alias, note that a lookup returns employees when the profile permits only employees, and employees plus contingent workers when HR_TREAT_CWK_AS_EMP is 'Y'. When code must branch worker type explicitly, select the raw columns and test CURRENT_EMPLOYEE_FLAG or CURRENT_NPW_FLAG rather than relying on WORKER_NUMBER alone.