Search Results ota_user_groups_b

Overview

OTA_USER_GROUPS_B is a foundational table within the Oracle E-Business Suite (EBS) Learning Management module (OTA), used across releases 12.1.1 and 12.2.2. It stores the base definition records for user groups, which are logical collections of learners, instructors, or administrators used to drive assignment, enrollment, notification, and reporting behavior throughout the Learning Management application. The "_B" suffix denotes that this is the base table, indicating the presence of a companion translation table (OTA_USER_GROUPS_TL) that holds language-specific descriptive attributes such as names and descriptions. The table is owned by the OTA schema and is documented as VALID in the ETRM 12.2.2 physical schema.

Per the documented Data Vault classification (heuristic, mined from FK structure), this object is suggested as a standalone modeling construct. This implies that, within a Data Vault-style model, the table would most naturally map to a hub or reference structure rather than a relationship (link) satellite, since its foreign key footprint is minimal and it primarily holds descriptive attributes keyed by its own surrogate identifier.

Key Information Stored

The documented physical schema contains 33 columns. The most significant are outlined below.

• USER_GROUP_ID — The surrogate primary key, unique across the table via the OTA_USER_GROUPS_B_UK1 unique index. It is the identifier referenced by dependent child tables.
• BUSINESS_GROUP_ID — The multi-org / business group discriminator that scopes the record to a specific operating unit or business group within the enterprise.
• USER_GROUP_TYPE — Classifies the nature or category of the user group, driving downstream behavior such as membership rules and assignment eligibility.
• USER_GROUP_OPERATOR — Defines the logical operator semantics applied when resolving or combining members within the group (for example, inclusion or exclusion logic).
• START_DATE_ACTIVE and END_DATE_ACTIVE — The date-effective range controlling when the user group is active and usable in the application.
• OBJECT_VERSION_NUMBER — Supports optimistic locking and concurrent update control, standard in Oracle AOL-style tables.
• CREATED_BY, CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — The standard Oracle "who did what when" audit columns.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE20 — The descriptive flexfield (DFF) columns, reserved for client-specific extensions without schema modification.

No secondary unique indexes are documented beyond OTA_USER_GROUPS_B_UK1, so no additional business-key candidate is identified in the metadata.

Common Use Cases and Queries

Typical reporting and integration scenarios include listing active user groups for a given business group, joining to the translation table for a user-facing label, and populating enrollment or assignment logic. A representative query pattern follows:

• Filter active groups: SELECT user_group_id, user_group_type FROM ota_user_groups_b WHERE business_group_id = :p_bg AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE) AND NVL(end_date_active, SYSDATE);
• Resolve display names via the TL table joined on USER_GROUP_ID and the appropriate LANGUAGE.
• Read flexfield attributes for downstream analytics or custom validations.
• Extract incremental changes using LAST_UPDATE_DATE for ETL and data warehouse loads.

Related Objects

• OTA_USER_GROUPS_TL — The translation table, joined on USER_GROUP_ID and LANGUAGE, supplying names and descriptions.
• OTA_USER_GROUP_MEMBERS — The membership table linking learners or persons to a user group via USER_GROUP_ID.
• OTA_USER_GROUPS_VL — The standard views layer combining the base and translated tables for application use.
• OTA_USER_GROUPS_TL_UK1 — Unique index supporting the translation table join.
• Foreign-key dependent child tables resolving members, assignments, and notification audiences through USER_GROUP_ID.