Results for “per_sec_profile_assignments”

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

Overview

PER_SEC_PROFILE_ASSIGNMENTS is a core Human Resources table in the Oracle E-Business Suite, owned by the HR schema and part of the PER (Human Resources) product. It records security profile assignments, binding a security profile to a user, a responsibility, and/or a business group. This makes it the central mapping object that determines which HR security profile governs a given user or responsibility context, and it is foundational to row-level data security in Oracle HRMS.

Within the 12.1.1 and 12.2.2 data models, the table carries both surrogate and business-key identifiers. The primary key is PER_SEC_PROFILE_ASSIGNMENTS_PK, defined on the SEC_PROFILE_ASSIGNMENT_ID column. The unique index PER_SEC_PROFILE_ASSIGNMENTS_UK is defined over USER_ID, RESPONSIBILITY_ID, RESPONSIBILITY_APPLICATION_ID, SECURITY_GROUP_ID, BUSINESS_GROUP_ID, SECURITY_PROFILE_ID, START_DATE, and END_DATE, establishing these as the business-key candidates that govern a distinct valid assignment.

From a Data Vault modeling perspective, the mined foreign-key structure suggests classifying this as a link entity. It resolves relationships among independent hubs — security profile, user, responsibility, security group, and business group — rather than acting as a standalone hub or descriptive satellite.

Key Information Stored

The table exposes 15 documented columns. The most significant are the identifiers and date-range controls:

• SEC_PROFILE_ASSIGNMENT_ID — the surrogate primary key, system-generated to uniquely identify each assignment record.
• SECURITY_PROFILE_ID — the foreign key to PER_SECURITY_PROFILES identifying which security profile is assigned.
• USER_ID — the user to whom the assignment applies; a business-key component.
• RESPONSIBILITY_ID and RESPONSIBILITY_APPLICATION_ID — identify the responsibility (and its application context) governed by the assignment.
• SECURITY_GROUP_ID — the security group scoping the assignment; references FND_SECURITY_GROUPS.
• BUSINESS_GROUP_ID — the business group scope, referencing HR_ALL_ORGANIZATION_UNITS.
• START_DATE and END_DATE — the effective date range of the assignment, both also business-key components.
• OBJECT_VERSION_NUMBER — optimistic locking column supporting concurrent updates.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard WHO audit columns.

Common Use Cases and Queries

Typical use cases center on HR security reporting and troubleshooting: determining which security profile a user inherits, whether a responsibility is correctly scoped, and whether assignments overlap in time. Effective-dating logic (START_DATE/END_DATE) is essential when evaluating current versus historical assignments.

• Current effective assignments for a user:

SELECT a.user_id, a.security_profile_id, a.start_date, a.end_date
FROM   per_sec_profile_assignments a
WHERE  a.user_id = :p_user_id
AND    TRUNC(SYSDATE) BETWEEN NVL(a.start_date, TRUNC(SYSDATE))
                          AND NVL(a.end_date, TRUNC(SYSDATE));

• Assignments joined to their security profile name:

SELECT a.user_id, p.security_profile_name
FROM   per_sec_profile_assignments a,
       per_security_profiles p
WHERE  a.security_profile_id = p.security_profile_id;

• Find the business-key attributes for a specific assignment using the PER_SEC_PROFILE_ASSIGNMENTS_UK columns to detect overlapping or duplicate effective-dated rows.

Related Objects

The table is tightly coupled with the following documented objects through foreign-key relationships:

• PER_SECURITY_PROFILES — joined via PER_SEC_PROFILE_ASSIGNMENTS.SECURITY_PROFILE_ID; supplies the profile definition being assigned.
• HR_ALL_ORGANIZATION_UNITS — joined via BUSINESS_GROUP_ID; supplies the business group context.
• FND_SECURITY_GROUPS — joined via SECURITY_GROUP_ID; provides the security group scope.

Because the assignment references user and responsibility identifiers, related FND objects such as FND_USER and FND_RESPONSIBILITY are logical dependencies for resolving user and responsibility details, while PER_SEC_PROFILE_ASSIGNMENTS_PK and PER_SEC_PROFILE_ASSIGNMENTS_UK enforce record integrity and business-key uniqueness.