Results for “relationship_id”

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

Overview

WF_ROLE_HIERARCHIES is an Oracle E-Business Suite table owned by the APPLSYS schema and registered under the FND – Application Object Library product. It stores the parent-child relationships that define the Oracle Workflow role hierarchy, allowing the workflow engine and related products to resolve which roles are superior or subordinate to one another. Each row captures a directed relationship between a "super" role and a "sub" role, and this structure underpins routing, notification, and approval behavior wherever role-based resolution is required.

In Data Vault modeling terms, the ETRM metadata classifies this object heuristically as standalone. That classification is a modeling suggestion only: the table is not a pure hub, link, or satellite in the Data Vault sense. Functionally it behaves most like a relationship (link) table, joining two role identifiers (SUPER_NAME and SUB_NAME) and carrying descriptive and audit attributes alongside that association. Its two foreign keys — to FND_SECURITY_GROUPS and JTF_FM_PARTITION_X_REQUEST — make it queryable as a standalone dimension-like structure for reporting.

Key Information Stored

The table exposes 13 documented columns. The most operationally significant are:

• RELATIONSHIP_ID — surrogate primary key for the row; enforced by unique index WF_ROLE_HIERARCHIES_U1.
• SUPER_NAME — the superior (parent) role in the hierarchy.
• SUB_NAME — the subordinate (child) role in the hierarchy. Together with SUPER_NAME it forms a second business-key candidate enforced by unique index WF_ROLE_HIERARCHIES_U2.
• ENABLED_FLAG — indicates whether the relationship is active and should be honored by role resolution.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, scoping the relationship to a security group.
• PARTITION_ID — foreign key to JTF_FM_PARTITION_X_REQUEST, associating the row with a partition context.
• SUPERIOR_PARTITION_ID — partition identifier for the superior side of the relationship.
• PROPAGATE_DATE — timestamp recording when the hierarchy change was propagated.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — the standard WHO audit columns tracking creation and modification.

The presence of two unique indexes means the surrogate key (RELATIONSHIP_ID) is the technical identifier, while (SUPER_NAME, SUB_NAME) is the natural business key used to prevent duplicate relationships.

Common Use Cases and Queries

Typical uses include validating role hierarchy configuration, generating approval routing reports, and troubleshooting workflow notifications that fail to escalate. A common pattern resolves all direct subordinates of a given role:

SELECT SUB_NAME FROM APPLSYS.WF_ROLE_HIERARCHIES WHERE SUPER_NAME = :role AND ENABLED_FLAG = 'Y';

To walk upward, the inverse query returns all superior roles for a subordinate. Reports frequently join SECURITY_GROUP_ID to FND_SECURITY_GROUPS to resolve the security context, and filter on ENABLED_FLAG to exclude disabled relationships. Because the hierarchy is recursive, DBAs and developers often use CONNECT BY or recursive WITH clauses rooted on SUPER_NAME/SUB_NAME to expand full reporting trees. Auditing queries use PROPAGATE_DATE and LAST_UPDATE_DATE to detect recently changed hierarchies.

Related Objects

• FND_SECURITY_GROUPS — joined on SECURITY_GROUP_ID to resolve the security grouping for a relationship.
• JTF_FM_PARTITION_X_REQUEST — joined on PARTITION_ID to associate the row with a partition request context.
• WF_ROLES — the role definition table that supplies the names referenced by SUPER_NAME and SUB_NAME.
• WF_LOCAL_ROLES and its hierarchy columns — the runtime view used for role resolution and notification addressing.
• WF_ROLE_HIERARCHIES_U1 / U2 — the two unique indexes that enforce the surrogate key and the business-key candidate.
• WF_NOTIFICATIONS — depends on role hierarchy resolution when determining notification recipients.
• PER_ROLES / HR_ALL_ORGANIZATION_UNITS — commonly referenced when roles map to HR positions and organizations in approval workflows.