Results for “hr_all_organization_units”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

HR.HR_ALL_ORGANIZATION_UNITS is the foundational organization definition table in Oracle E-Business Suite, owned by the HR schema and delivered under the PER (Human Resources) product family. It stores the master record for every organization unit in the enterprise — business groups, operating units, inventory organizations, legal entities, departments, cost centers, divisions, and other HR-reported organizational constructs. Although the reference to pn_rec_agr_linabat_all typically concerns property manager recurring agreement line attributes, that table reaches this hub exclusively through an ORG_ID foreign key, underscoring how widely HR_ALL_ORGANIZATION_UNITS acts as the system-wide organization registry.

Following the heuristic Data Vault classification supplied in the metadata, this object is best modeled as a hub. Its stable, singular business key (ORGANIZATION_ID) anchors a very large constellation of referencing tables across Financials, Supply Chain, Projects, Payroll, Benefits, and Property Manager, making it the canonical organization dimension.

Key Information Stored

• ORGANIZATION_ID — surrogate primary key (HR_ORGANIZATION_UNITS_PK); the identifier propagated to hundreds of child tables as ORG_ID, BUSINESS_GROUP_ID, or ORGANIZATION_ID.
• NAME and BUSINESS_GROUP_ID — together form the unique business key HR_ORGANIZATION_UNITS_UK2, guaranteeing organization names are unique within a business group.
• BUSINESS_GROUP_ID — self-referencing foreign key to this table, identifying the business group (a special organization type) that owns the unit.
• TYPE — the organization classification (e.g., business group, operating unit, inventory organization, legal entity), the primary filter used by most consumers.
• DATE_FROM / DATE_TO — effective dating for the organization record.
• INTERNAL_EXTERNAL_FLAG — distinguishes internal organizations from external (third-party) organizations.
• LOCATION_ID — foreign key to HR_LOCATIONS_ALL, the physical address of the unit.
• PARTY_ID — links the organization to the Trading Community Architecture (HZ) party model.
• COST_ALLOCATION_KEYFLEX_ID and SOFT_CODING_KEYFLEX_ID — key flexfield references to PAY_COST_ALLOCATION_KEYFLEX and HR_SOFT_CODING_KEYFLEX for default cost allocation and soft coding.
• INTERNAL_ADDRESS_LINE and COMMENTS — internal mail-stop style addressing and free-text notes.
• OBJECT_VERSION_NUMBER, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE — standard WHO/concurrency audit columns.
• ATTRIBUTE1–ATTRIBUTE30 — the DDF (descriptive flexfield) segment columns available for client-specific extensions.

Common Use Cases and Queries

Typical reporting needs include resolving an ORG_ID to a human-readable organization name, listing all organizations of a given type, and traversing organization hierarchies. Because the table is effective-dated and multilingual-aware through related tables, queries should typically constrain by business group.

• Resolving operating unit names:

  SELECT organization_id, name
  FROM   hr_all_organization_units
  WHERE  type = 'OPERATING_UNIT'
  AND    business_group_id = :p_bg_id;

• Joining Property Manager agreements to their owning organization:

  SELECT p.agreement_id, o.name
  FROM   pn_rec_agreements_all p,
         hr_all_organization_units o
  WHERE  p.org_id = o.organization_id;

• Inventory organization lookups (joins to MTL_PARAMETERS via ORGANIZATION_ID).
• Organization hierarchy reporting using PER_ORG_STRUCTURE_ELEMENTS, which stores the parent/child organization relationships referenced to this hub.
• Security and data-access queries joining PER_SECURITY_ORGANIZATIONS to restrict visible organizations.

Related Objects

• PER_ORG_STRUCTURE_ELEMENTS — stores parent (ORGANIZATION_ID_PARENT) and child (ORGANIZATION_ID_CHILD) organization hierarchy relationships.
• HR_LOCATIONS_ALL — physical location referenced by LOCATION_ID.
• HR_ORGANIZATION_INFORMATION — extensible classification data keyed by ORGANIZATION_ID.
• PER_ALL_ASSIGNMENTS_F — employee assignments keyed by ORGANIZATION_ID and BUSINESS_GROUP_ID.
• PAY_ALL_PAYROLLS_F / PAY_ELEMENT_LINKS_F — payroll definitions scoped to an organization or business group.
• BEN_ELIGY_CRITERIA and other Benefits tables — reference BUSINESS_GROUP_ID.
• MTL_PARAMETERS — inventory organization attributes joined on ORGANIZATION_ID.
• PN_REC_AGR_LINABAT_ALL — Property Manager line attribute table referencing ORG_ID, the object that prompted this lookup.