Results for “per_organization_list”

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

Overview

The PER_ORGANIZATION_LIST table, owned by the HR schema, is a core security construct within the Oracle E-Business Suite Human Resources (PER) module. It stores the list of organizations that a secure user is authorized to access. In Oracle EBS, security profiles govern which organizations, positions, payrolls, and other HR entities a user may view or transact against; PER_ORGANIZATION_LIST is the materialized bridge that resolves a given security profile into the concrete set of organization units it grants. As such, it functions as the operational backbone of Organization Security Profile enforcement across HR and downstream modules such as Payroll, OAB, and self-service applications.

The table carries a heuristic Data Vault classification of link. This is a modeling suggestion: the table resolves a many-to-many relationship between security profiles and organization units, and its natural key structure (SECURITY_PROFILE_ID plus ORGANIZATION_ID, optionally USER_ID) reflects an associative/link role rather than a descriptive hub or satellite.

Key Information Stored

The documented schema comprises seven columns. The most significant are:

• SECURITY_PROFILE_ID — Identifies the security profile that confers access. Part of the composite primary key.
• ORGANIZATION_ID — Identifies the organization unit (from HR_ALL_ORGANIZATION_UNITS) that the profile grants access to. Part of the composite primary key.
• USER_ID — Optionally identifies a specific user associated with the row. Included in the unique index PER_ORGANIZATION_LIST_UK1.
• REQUEST_ID — Concurrent request identifier that created or last processed the row; supports traceability to the originating batch job.
• PROGRAM_APPLICATION_ID — Application owning the concurrent program that generated the row.
• PROGRAM_ID — Concurrent program identifier responsible for populating the row.
• PROGRAM_UPDATE_DATE — Timestamp of the concurrent program update that last touched the row.

The surrogate primary key, PER_ORGANIZATION_LIST_PK, is defined on (SECURITY_PROFILE_ID, ORGANIZATION_ID). A business-key candidate is captured by the unique index PER_ORGANIZATION_LIST_UK1 on (SECURITY_PROFILE_ID, ORGANIZATION_ID, USER_ID), which extends the PK with the user dimension. These two constraints are the anchors referenced when a developer searches for "per_organization_list_pk".

Common Use Cases and Queries

Typical scenarios include determining which organizations a user can see, validating that a security profile resolves to the expected organization set, and diagnosing access anomalies after profile changes. Because the table is populated and refreshed by the security profile list concurrent programs, the PROGRAM_ID, REQUEST_ID, and PROGRAM_UPDATE_DATE columns are frequently used to audit population runs.

• Resolve organizations for a profile: SELECT organization_id FROM per_organization_list WHERE security_profile_id = :profile_id;
• Joining to organization names: SELECT p.organization_id, o.name FROM per_organization_list p JOIN hr_all_organization_units o ON o.organization_id = p.organization_id WHERE p.security_profile_id = :profile_id;
• Auditing population runs: SELECT request_id, program_id, program_update_date FROM per_organization_list WHERE security_profile_id = :profile_id ORDER BY program_update_date DESC;

Reporting use cases include security profile access matrices and organization-level access audits for SOX/ITGC compliance.

Related Objects

The foreign keys defined on this table establish its principal relationships:

• HR_ALL_ORGANIZATION_UNITS — Joined via ORGANIZATION_ID; the authoritative source of organization unit definitions.
• PER_SECURITY_PROFILES — Joined via SECURITY_PROFILE_ID; defines the security profile that owns the granted access.

Dependent objects that reference this table include the Organization Security Profile validation and list-generation logic within the HR module, and the organization security views and PL/SQL APIs (such as HR_SECURITY) that consume the resolved list during authorization checks. Because PER_ORGANIZATION_LIST underlies organization-level data security throughout EBS, any direct modification of its rows can materially affect user access.