Results for “wf_local_roles”

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

Overview

WF_LOCAL_ROLES is a core Oracle Workflow and Application Object Library (FND) table, owned by the APPLSYS schema, that stores the local resolution of roles known to the Oracle E-Business Suite. Rather than acting purely as a directory of employees, it holds the flattened, workflow-facing definition of every role, user, and group that the Workflow notification engine, the Workflow Directory Services APIs, and related messaging components can address. Each row represents one resolvable role identity within a given originating system and partition, supplying the display attributes (name, e-mail, fax, language, territory) needed to route and render notifications, and the lifecycle attributes (start date, status, expiration date) needed to determine whether the role is currently active.

The ETRM metadata classifies WF_LOCAL_ROLES as standalone under its heuristic Data Vault analysis, meaning it presents as a single hub-like entity rather than a dependent satellite or a pure link. In practice, the table functions as a role/party master from which notification-routing and approval logic derives assignments, so modeling it as a hub keyed on role identity is a reasonable suggestion for analytic or data-vault style reporting.

Key Information Stored

The documented physical schema contains 24 columns. The most operationally significant are:

• NAME — the internal role identifier and the column forming the primary key WF_LOCAL_ROLES_PK.
• DISPLAY_NAME — the human-readable label rendered in notification messages and LOVs.
• DESCRIPTION — free-text annotation of the role's purpose.
• NOTIFICATION_PREFERENCE — the channel (e.g., MAILHTML, MAILTEXT, QUERY) used to deliver notifications to this role.
• LANGUAGE and TERRITORY — determine message translation and date/number formatting.
• EMAIL_ADDRESS and FAX — delivery endpoints for the role.
• ORIG_SYSTEM and ORIG_SYSTEM_ID — identify the source system (with ORIG_SYSTEM_ID referencing HZ_ORIG_SYSTEMS_B via the FK) that owns the underlying party.
• START_DATE, STATUS, and EXPIRATION_DATE — the effective-dating and enablement triad that controls whether a role is active.
• SECURITY_GROUP_ID — FK to FND_SECURITY_GROUPS, governing which data group can see the role.
• USER_FLAG — distinguishes true user roles from group/other role types.
• PARTITION_ID — FK to JTF_FM_PARTITION_X_REQUEST, supporting partitioned multi-tenant deployments.
• PARENT_ORIG_SYSTEM and PARENT_ORIG_SYSTEM_ID — express hierarchical relationships between roles.

While the primary key is NAME, the unique index WF_LOCAL_ROLES_U1 over (NAME, ORIG_SYSTEM, ORIG_SYSTEM_ID, PARTITION_ID) is the true business-key candidate, since the same logical role name can recur across originating systems and partitions.

Common Use Cases and Queries

Typical scenarios include resolving notification recipients for a workflow process, validating that an approver role is still active, and building directory-style reports of users and groups.

Finding active users with their delivery preferences:

SELECT name, display_name, email_address, notification_preference
FROM   applsys.wf_local_roles
WHERE  user_flag = 'Y'
AND    status = 'ACTIVE'
AND    sysdate BETWEEN start_date AND NVL(expiration_date, sysdate+1);

Looking up a role by originating party:

SELECT name, display_name, orig_system, orig_system_id
FROM   applsys.wf_local_roles
WHERE  orig_system = :p_orig_system
AND    orig_system_id = :p_id;

Reporting role counts by security group and partition assists with access reviews, while expiring-role queries support periodic cleanup and de-provisioning checks.

Related Objects

• WF_ROLES — the higher-level role/assignment view that derives many entries from WF_LOCAL_ROLES.
• WF_LOCAL_USER_ROLES — resolves which users belong to which roles.
• WF_USER_ROLES — the combined user-to-role resolution view used by the notification engine.
• HZ_ORIG_SYSTEMS_B — referenced via ORIG_SYSTEM_ID, identifying the originating system.
• FND_SECURITY_GROUPS — referenced via SECURITY_GROUP_ID for data-group scoping.
• JTF_FM_PARTITION_X_REQUEST — referenced via PARTITION_ID for partitioned deployments.
• FND_USER — the application user master whose accounts correspond to USER_FLAG = 'Y' rows.
• WF_LOCAL_ROLES_TL — the translation table supplying language-specific display names.
• Workflow Directory Services APIs (WF_DIRECTORY) — the programmatic interface over this table for role resolution and maintenance.