Results for “hz_group_wf_user_roles_v”

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

Overview

HZ_GROUP_WF_USER_ROLES_V is a read-only dictionary view owned by the APPS schema in Oracle E-Business Suite releases 12.1.1 and 12.2.2. It belongs to the Receivables (AR) product family and resides within the Oracle Trading Community Architecture (TCA) and Workflow (WF) integration layer of the Oracle Customer Data Management model. The view exposes party relationships in a flattened, workflow-oriented format that presents a person party as a "user" and a group party as a "role." This shape conforms to the Oracle Workflow directory service model, in which users and roles are addressed by a composite name and an originating system identifier. The view is therefore used during the resolution of approvers, notification recipients, and role-based routing in Oracle Workflow and related approval processes, and it also serves as a reporting convenience for administrators who need to enumerate current person-to-group assignments without writing the underlying join logic themselves.

Underlying Base Objects

The view is defined over two documented base objects, both referenced through APPS synonyms: HZ_PARTIES and HZ_RELATIONSHIPS. HZ_PARTIES supplies both the subject and object of each relationship through two aliases, SP (subject party, restricted to PERSON party types) and OP (object party, restricted to GROUP party types). HZ_RELATIONSHIPS supplies the association itself, along with its Start Date, End Date, and relationship type attributes. The view text is structured as an inline view (aliased TEMP) wrapped by an outer projection. The inner query joins SP.PARTY_ID to PR.SUBJECT_ID and OP.PARTY_ID to PR.OBJECT_ID, and applies outer-join syntax on the subject and object table name columns. Multiple filters restrict the result set: both parties and the relationship must have a status of 'A' or 'I' (active or inactive), and both party types must match the corresponding subject and object types on the relationship. The PARTITION_ID literal of 8 and the SECURITY_GROUP_ID of NULL identify the workflow partition context.

Key Columns

• USER_NAME — the workflow username for the person party, formatted as 'HZ_PARTY:' concatenated with the party ID.
• USER_ORIG_SYSTEM — the literal 'HZ_PARTY', identifying the source system of the user record.
• USER_ORIG_SYSTEM_ID — the numeric PARTY_ID of the person party; this is the column most frequently sought by users searching on "user_orig_system_id."
• ROLE_NAME — the workflow role name for the group party, formatted as 'HZ_GROUP:' concatenated with the party ID.
• ROLE_ORIG_SYSTEM — the literal 'HZ_GROUP'.
• ROLE_ORIG_SYSTEM_ID — the numeric PARTY_ID of the group party.
• START_DATE / EXPIRATION_DATE — the relationship start and end dates, mapped from PR.START_DATE and PR.END_DATE.
• SECURITY_GROUP_ID — null in this view.
• PARTITION_ID — constant value 8.

The inner query ranks rows using RANK() OVER (PARTITION BY PR.SUBJECT_ID, PR.OBJECT_ID ORDER BY PR.RELATIONSHIP_ID DESC) and retains only RELRANK = 1, so each user-role pair appears once, reflecting the most recent relationship record.

Common Use Cases and Queries

Typical usage includes verifying whether a given person party is currently assigned to a group role, locating the user identifier behind a workflow notification, and generating reconciliation reports of person-to-group memberships during a TCA data migration. A common query resolving the party behind a workflow user name is:

• SELECT user_name, user_orig_system_id, role_name, start_date, expiration_date FROM hz_group_wf_user_roles_v WHERE user_orig_system_id = :party_id;
• SELECT user_orig_system_id, role_orig_system_id FROM hz_group_wf_user_roles_v WHERE role_name = 'HZ_GROUP:' || TO_CHAR(:group_party_id);
• SELECT COUNT(*) FROM hz_group_wf_user_roles_v WHERE SYSDATE BETWEEN start_date AND NVL(expiration_date, SYSDATE + 1);

Because the view contains only active or inactive parties that are members of groups, it should not be used to retrieve terminated or merged party relationships without further joins to HZ_PARTIES.