Results for “per_sec_profile_assignments_v”

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

Overview

PER_SEC_PROFILE_ASSIGNMENTS_V is a user-interface support view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It belongs to the PER (Human Resources) product family and is documented with a VALID status in ETRM. The view consolidates security profile assignment data across users, responsibilities, security groups, and employees into a single denormalized result set, which allows Oracle Forms-based maintenance screens and related reporting components to retrieve consolidated security profile information without issuing multiple joins in the UI layer. The description recorded in ETRM states simply that the view is "used to support user-interface," confirming its role as an internal presentation object rather than a documented integration interface.

Because the view joins user-responsibility-security group combinations against security profile assignments, it exposes the effective mapping of which security profile is applied to a given user, responsibility, and responsibility application, together with the employee currently holding that user account.

Underlying Base Objects

The view is defined over a mix of tables, views, and packages. The documented referenced base objects are:

• FND_USER_RESP_GROUPS_DIRECT (aliased F1) — provides the user, responsibility, responsibility application, and security group combination.
• FND_USER (F2) — supplies USER_NAME and EMPLOYEE_ID.
• FND_RESPONSIBILITY_VL (F3) — supplies the translated responsibility name.
• FND_APPLICATION_VL (F4) — supplies the application name associated with the responsibility.
• PER_ALL_PEOPLE_F (P1) — supplies the employee's full name, employee number, current employee flag, and person identifier, restricted by TRUNC(SYSDATE) between the effective start and end dates.
• PER_SEC_PROFILE_ASSIGNMENTS (P3) — the core assignment record containing start and end dates, business group, security group, and the object version number.
• PER_SECURITY_PROFILES (P4) — supplies SECURITY_PROFILE_NAME and SECURITY_PROFILE_ID.

Additional referenced objects include FND_SECURITY_GROUPS, WF_LOCAL_USER_ROLES, FND_APPLICATION, and the FND_DATE and HR_GENERAL packages. The view uses outer joins (denoted by (+)) on the security group, user, security profile, and people joins, so rows persist even when no matching assignment or person record exists.

Key Columns

• USER_ID, USER_NAME — the FND user identity.
• RESPONSIBILITY_ID, RESPONSIBILITY_APPLICATION_ID, RESPONSIBILITY_NAME, APPLICATION_NAME — the responsibility and its owning application.
• SECURITY_GROUP_ID — the security group governing the assignment.
• SECURITY_PROFILE_ID, SECURITY_PROFILE_NAME — the assigned security profile; this is the column a user searching for "security_profile_name" would target.
• START_DATE, END_DATE — the assignment validity window; END_DATE is nulled when the stored end date is the 4712 canonical maximum.
• EMPLOYEE_ID, EMPLOYEE_NUMBER, FULL_NAME, CURRENT_EMPLOYEE_FLAG, CURRENT_EMPLOYEE — employee attributes; CURRENT_EMPLOYEE renders an asterisk when the flag is 'Y'.
• BUSINESS_GROUP_ID, BUSINESS_GROUP_NAME — decoded via HR_GENERAL.DECODE_ORGANIZATION.
• OBJECT_VERSION_NUMBER, SEC_PROFILE_ASSIGNMENT_ID — the assignment identifier and concurrency control value.

Common Use Cases and Queries

The view is typically queried to audit which security profile applies to a user and responsibility combination, or to identify users assigned to a named security profile.

Listing assignments by security profile name:

SELECT user_name, responsibility_name, security_profile_name, start_date, end_date
FROM   apps.per_sec_profile_assignments_v
WHERE  security_profile_name = 'US_HR_MS_ALL'
AND    NVL(end_date, SYSDATE + 1) > SYSDATE;

Finding all profiles assigned to a specific user:

SELECT responsibility_name, application_name, security_profile_name
FROM   apps.per_sec_profile_assignments_v
WHERE  user_name = 'JSMITH';

Reporting current employees with security group context:

SELECT full_name, employee_number, business_group_name,
       responsibility_name, security_profile_name
FROM   apps.per_sec_profile_assignments_v
WHERE  current_employee_flag = 'Y';

Because the view is documented as UI-supporting, it remains valid and stable in both 12.1.1 and 12.2.2, but it should be treated as an Oracle-owned internal object; custom code should not depend on undocumented behavior beyond the columns enumerated in ETRM.