Search Results role_start_date

Overview

FND_RESP_SEC_UR is an APPS-owned database view in the Oracle E-Business Suite environment, classified under the FND — Application Object Library product family. Its name reflects its purpose: it unifies user, responsibility, and security group context into a single relational projection (RESP = responsibility, SEC = security group, U = user, R = responsibility). In Oracle EBS 12.1.1 and 12.2.2 the view presents a flattened, reporting-friendly image of which users hold which responsibilities, under which security group, with the effective date ranges of each assignment.

The view is commonly used in reporting, integration, and identity-management scenarios — for example, populating downstream provisioning tools, feeding user access certification reports, or extracting role assignments for audit purposes — because it joins several FND base entities that would otherwise require multi-table joins on every query.

Underlying Base Objects

The documented view metadata identifies five referenced base objects, all exposed to APPS as synonyms:

• FND_USER — the user master record, supplying user identity and user-level start/end dates.
• FND_USER_RESP_GROUPS_OLD — the historical assignment table that links users to responsibilities and security groups.
• FND_RESPONSIBILITY — the responsibility (role) definition, including application and responsibility key.
• FND_APPLICATION — the owning application of each responsibility.
• FND_SECURITY_GROUPS — the security group under which the assignment is scoped.

The view is defined as a UNION of two branches of essentially the same join, differing in how the ROLE_NAME and ROLE_ORIG_SYSTEM values are derived — one branch uses a pipe-delimited short-name format, the other uses a numeric application/responsibility concatenation. Both branches join FND_USER to FND_USER_RESP_GROUPS_OLD on USER_ID, to FND_RESPONSIBILITY on RESPONSIBILITY_ID and RESPONSIBILITY_APPLICATION_ID, to FND_APPLICATION on APPLICATION_ID, and to FND_SECURITY_GROUPS on SECURITY_GROUP_ID.

Key Columns

• USER_NAME — the FND user login name.
• USER_ORIG_SYSTEM / USER_ORIG_SYSTEM_ID — origin system of the user ('FND_USR' or 'PER') and the associated employee identifier.
• ROLE_NAME / ROLE_ORIG_SYSTEM / ROLE_ORIG_SYSTEM_ID — composite role identity, built from the responsibility and security group keys.
• START_DATE / EXPIRATION_DATE — the effective range of the user-to-responsibility assignment (mapped from UR.START_DATE and UR.END_DATE).
• USER_START_DATE / USER_END_DATE — the user record's own start and end dates; USER_END_DATE is the key column many users search for when checking whether an account has been end-dated.
• ROLE_START_DATE / ROLE_END_DATE — the responsibility definition's own start and end dates.
• SECURITY_GROUP_ID, PARTITION_ID, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — audit and scoping columns.

Common Use Cases and Queries

A frequent requirement is identifying users whose account has been end-dated while still holding active responsibilities:

SELECT user_name, role_name, user_end_date, expiration_date
FROM   fnd_resp_sec_ur
WHERE  user_end_date IS NOT NULL
AND    (expiration_date IS NULL OR expiration_date > SYSDATE);

Another common query returns all current responsibility assignments for a specific user:

SELECT user_name, role_name, start_date, expiration_date
FROM   fnd_resp_sec_ur
WHERE  user_name = :p_user
AND    (expiration_date IS NULL OR expiration_date > SYSDATE);

Because the view already resolves application, responsibility, and security group context, it is also well suited to reconciliation reports that must confirm the role landscape delivered to access-management or GRC systems.