Search Results per_all_organization_units




Overview
Explore in AI Chat

Overview

The APPS.PER_ALL_ORGANIZATION_UNITS view is a reporting and integration construct within the Oracle E-Business Suite Human Resources (PER) module. As documented in the ETRM metadata for releases 12.1.1 and 12.2.2, it is described as the "Unsecured version of PER_ORGANIZATION_UNITS." In Oracle HRMS, the term "unsecured" indicates that the view does not apply row-level security (security profiles) filtering. Consequently, it exposes the complete set of organization unit definitions across all business groups accessible in the database, rather than restricting rows to the organizations a given responsibility or user is permitted to see. This distinguishes it from the secured PER_ORGANIZATION_UNITS view, which applies the HR security model.

The view is owned by the APPS schema and holds a VALID status. Its principal role is to support reporting, data extraction, and integrations where a globally consistent list of internal HR organizations is required without responsibility-level filtering. Because it resolves organizational attributes across the base organization, translation, and information tables, it serves as a convenient denormalized source for downstream queries and interfaces.

Underlying Base Objects

The view is defined over three referenced base objects, each exposed through a synonym in the APPS schema:

  • HR_ALL_ORGANIZATION_UNITS (SYNONYM) — the primary organization definition table, aliased as O. It supplies core attributes such as ORGANIZATION_ID, BUSINESS_GROUP_ID, date ranges, address line, comments, type, location, and key flexfield references.
  • HR_ALL_ORGANIZATION_UNITS_TL (SYNONYM) — the translation table, aliased as OTL, which supplies the language-dependent NAME. The join restricts rows to the session language via OTL.LANGUAGE = USERENV('LANG').
  • HR_ORGANIZATION_INFORMATION (SYNONYM) — the descriptive flexfield / information table, joined twice. Alias O2 retrieves the WORK DAY INFORMATION context columns (ORG_INFORMATION1–ORG_INFORMATION4), while alias O3 enforces the CLASS context filter requiring ORG_INFORMATION1 = 'HR_ORG' and ORG_INFORMATION2 = 'Y', ensuring only organizations classified as internal HR organizations are returned.

The view text joins O.ORGANIZATION_ID to the information and translation tables, with the WORK DAY INFORMATION join expressed as an outer join.

Key Columns

  • ORGANIZATION_ID — Primary key identifying the organization unit.
  • BUSINESS_GROUP_ID — The business group to which the organization belongs.
  • NAME — Language-specific organization name from the translation table.
  • DATE_FROM / DATE_TO — Effective date range of the organization definition.
  • INTERNAL_ADDRESS_LINE and COMMENTS — Descriptive text attributes.
  • ORG_INFORMATION1–4 — Work day information flexfield values.
  • INTERNAL_EXTERNAL_FLAG — Indicates whether the organization is internal or external.
  • TYPE — Organization classification value.
  • LOCATION_ID, COST_ALLOCATION_KEYFLEX_ID, SOFT_CODING_KEYFLEX_ID — Foreign keys to location and key flexfield structures.

Caveat: the documented column list also includes DEFAULT_START_TIME, DEFAULT_END_TIME, WORKING_HOURS, and FREQUENCY. In the available view text these are sourced from the WORK DAY INFORMATION flexfield context; practitioners should verify their presence against the specific patch level in use.

Common Use Cases and Queries

The view is commonly used when an unfiltered inventory of HR organizations is needed, for example in data migration, interface validation, or cross-business-group reporting.

  • Listing active internal HR organizations by business group.
  • Joining to assignments, positions, or payroll to resolve organizational context.
  • Extracting organization details for integration into external systems.

Sample query:

SELECT organization_id,
       business_group_id,
       name,
       date_from,
       date_to,
       internal_external_flag
FROM   apps.per_all_organization_units
WHERE  TRUNC(SYSDATE) BETWEEN date_from AND NVL(date_to, TRUNC(SYSDATE))
ORDER  BY business_group_id, name;

Because the view is unsecured, developers requiring responsibility-based filtering should use the secured PER_ORGANIZATION_UNITS view instead.