Search Results wf_local_roles_old

Overview

WF_LOCAL_ROLES_OLD is a table owned by the APPLSYS schema within the Oracle E-Business Suite, catalogued under the FND — Application Object Library product. It is documented as VALID in ETRM for both release 12.1.1 and 12.2.2, and its physical schema comprises 24 columns. The table stores role and user resolution data used by the Oracle Workflow directory services layer, which resolves role names, e-mail addresses, notification preferences, and hierarchy relationships for use by the Workflow Notification System and related routing engines.

The "_OLD" suffix indicates that this object functions as a historical or pre-transformation staging counterpart to WF_LOCAL_ROLES. In upgrade and data-migration contexts (notably the transition to the 12.2 Online Patching and Multi-Org Access Control model, and the earlier 11i-to-12.1.1 upgrade), rows are frequently archived here before the active directory tables are rebuilt. Applications should not treat it as a live transactional table; it is retained for recovery, reconciliation, and post-upgrade auditing.

From a data-modeling perspective, the metadata's heuristic Data Vault classification reports this object as standalone. This is a suggestion rather than a normative statement: because the mined foreign keys point outward to reference and partition tables rather than to a tightly coupled parent, the object behaves more like a satellite of the Workflow directory hub (WF_LOCAL_ROLES) than a hub or link in its own right.

Key Information Stored

The table's identity and descriptive attributes are carried principally by the following columns: NAME, DISPLAY_NAME, DESCRIPTION, STATUS, EXPIRATION_DATE, START_DATE, LANGUAGE, TERRITORY, EMAIL_ADDRESS, FAX, NOTIFICATION_PREFERENCE, ORIG_SYSTEM, ORIG_SYSTEM_ID, PARENT_ORIG_SYSTEM, PARENT_ORIG_SYSTEM_ID, SECURITY_GROUP_ID, USER_FLAG, PARTITION_ID, and OWNER_TAG.

NAME is the natural business key inherited from the originating directory source, qualified by ORIG_SYSTEM and ORIG_SYSTEM_ID, which identify the source system that published the role. The parent columns (PARENT_ORIG_SYSTEM, PARENT_ORIG_SYSTEM_ID) encode the reporting hierarchy used to expand role membership during notification routing. USER_FLAG distinguishes true user accounts from group or role entries. SECURITY_GROUP_ID scopes the row to a security group, and PARTITION_ID scopes it to a multi-tenant partition. Auditing columns CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN follow the standard FND Who columns.

The ETRM metadata does not document an explicit single-column surrogate primary key for this table; where one exists in the physical database it is typically a system-generated identifier not exposed in the documented column list. Business-key uniqueness is therefore expected to derive from the combination of NAME, ORIG_SYSTEM, ORIG_SYSTEM_ID, and SECURITY_GROUP_ID.

Common Use Cases and Queries

Typical usage centres on reconciliation and troubleshooting after a Workflow directory synchronisation or an EBS upgrade. A common pattern compares the archived rows against the live directory:

• Row-count and key-parity checks between WF_LOCAL_ROLES_OLD and WF_LOCAL_ROLES for a given ORIG_SYSTEM.
• Locating roles or users present in the _OLD table but absent from the active directory, indicating a failed Workflow Directory Services synchronisation.
• Reconstructing a historical notification recipient list using NAME, EMAIL_ADDRESS, and NOTIFICATION_PREFERENCE as of a prior point in time.
• Auditing SECURITY_GROUP_ID or PARTITION_ID assignments that changed during an upgrade.

A representative query selects NAME, ORIG_SYSTEM, ORIG_SYSTEM_ID, EMAIL_ADDRESS, STATUS, and LAST_UPDATE_DATE from WF_LOCAL_ROLES_OLD where ORIG_SYSTEM = :p_orig_system and STATUS = 'ACTIVE', ordered by NAME. Analysts should treat the table as read-only.

Related Objects

The documented foreign keys define three outward relationships, supplementing the implicit link to the live directory:

• WF_LOCAL_ROLES_OLD.ORIG_SYSTEM_ID references HZ_ORIG_SYSTEMS_B, identifying the registered source system.
• WF_LOCAL_ROLES_OLD.SECURITY_GROUP_ID references FND_SECURITY_GROUPS, controlling access scoping.
• WF_LOCAL_ROLES_OLD.PARTITION_ID references JTF_FM_PARTITION_X_REQUEST, tying the row to a partition request.
• WF_LOCAL_ROLES — the active counterpart, joined on NAME, ORIG_SYSTEM, and ORIG_SYSTEM_ID.
• WF_ROLES and WF_USER_ROLES — the runtime role-assignment tables that consume directory data.
• FND_USER — the EBS user registry linking USER_FLAG entries to application accounts.

Workflow Directory Services APIs maintain these structures; direct DML against WF_LOCAL_ROLES_OLD is not supported.