Results for “ota_user_group_elements”

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

Overview

OTA_USER_GROUP_ELEMENTS is a transactional definition table in the OTA (Learning Management) schema of Oracle E-Business Suite, available in both 12.1.1 and 12.2.2. Its purpose is to store the individual membership elements that compose a user group. A user group acts as a reusable audience container within Oracle Learning Management, used to enroll learners, assign offerings or classes, and drive eligibility checks. This table decomposes each group into discrete rows, where each row identifies a person, customer, organization, job, position, organization structure version, party, or nested child user group that belongs to the parent group.

The documented physical schema in ETRM 12.2.2 contains 42 columns, owned by OTA. The metadata's heuristic Data Vault classification for this object is standalone, suggesting that in a dimensional or Data Vault modeling exercise, OTA_USER_GROUP_ELEMENTS may be treated as an independent structure rather than a strongly coupled hub, link, or satellite, though in practice it functions logically as a link between a user group and its member entities. Consumption of the table should be treated as a definitional mapping between a group header and its constituent members, not as transactional or historical event data.

Key Information Stored

Only a subset of the 42 columns is material to typical functional and reporting work. The most important columns are:

• USER_GROUP_ELEMENT_ID — the surrogate primary key identified by unique index OTA_USER_GROUP_ELEMENTS_UK1; uniquely identifies each membership row.
• USER_GROUP_ID — foreign reference to the parent user group; forms part of primary key indexes OTA_USER_GROUP_ELEMENTS_N1 and OTA_USER_GROUP_ELEMENTS_N2.
• PERSON_ID — the person (typically an employee or learner) included in the group as an individual member.
• PARTY_ID, CUSTOMER_ID — references to the TCA party/customer model when the member is an external or CRM-registered entity.
• ORGANIZATION_ID — the organization included as a member of the group.
• JOB_ID, POSITION_ID — job- and position-based membership, enabling the group to be defined by HR assignment attributes rather than named individuals.
• ORG_STRUCTURE_VERSION_ID — the organization structure version used when the group element is defined by an org hierarchy; this column carries the only documented foreign key relationship, referencing PER_ORG_STRUCTURE_VERSIONS.
• CHILD_USER_GROUP_ID — a nested user group, allowing hierarchical composition of groups.
• MATCH_TYPE — controls how the element is interpreted or matched during group resolution.
• BUSINESS_GROUP_ID — the HR business group scoping the membership row.
• ELIG_PRFL_ID, PROCESSING_STATUS#1 — eligibility profile linkage and processing state used by group resolution logic.
• OBJECT_VERSION_NUMBER, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard Oracle EBS concurrency and audit columns.
• ATTRIBUTE_CATEGORY through ATTRIBUTE20 — the mandatory DFF extension columns for customer-specific data.

Common Use Cases and Queries

Typical reporting scenarios include determining who is in a given learning user group, which groups a person belongs to, whether any group is composed of other nested groups, and which groups are defined by organization, job, or position rather than by name.

To list all members of a specific group:

• SELECT user_group_element_id, person_id, party_id, customer_id, organization_id, job_id, position_id FROM ota_user_group_elements WHERE user_group_id = :p_group_id;

To find all groups containing a nested child group:

• SELECT user_group_id, child_user_group_id FROM ota_user_group_elements WHERE child_user_group_id IS NOT NULL;

To count membership by group for capacity or audience-size analysis:

• SELECT user_group_id, COUNT(*) FROM ota_user_group_elements GROUP BY user_group_id;

Because rows may be joined to HR and TCA structures, reports typically join to PER_ALL_PEOPLE_F on PERSON_ID, HR_ALL_ORGANIZATION_UNITS on ORGANIZATION_ID, and HZ_PARTIES on PARTY_ID. Any incremental extract should filter on LAST_UPDATE_DATE.

Related Objects

• PER_ORG_STRUCTURE_VERSIONS — referenced by ORG_STRUCTURE_VERSION_ID; the only documented foreign key dependency.
• OTA_USER_GROUPS — the parent group definition joined on USER_GROUP_ID.
• PER_ALL_PEOPLE_F — joined on PERSON_ID to resolve learner/employee names and assignment details.
• HR_ALL_ORGANIZATION_UNITS — joined on ORGANIZATION_ID for organizational membership.
• PER_JOBS and PER_POSITIONS — joined on JOB_ID and POSITION_ID respectively.
• HZ_PARTIES and HZ_CUST_ACCOUNTS — joined on PARTY_ID and CUSTOMER_ID for external audiences.
• OTA_ELIGIBILITY_PROFILES — referenced through ELIG_PRFL_ID.
• Learning Management enrollment and offering APIs that consume user groups for audience resolution should be checked for their own group-expansion logic, as they ultimately read this table to determine membership.