Search Results ota_certifications_b




Overview
Explore in AI Chat

Overview

OTA_CERTIFICATIONS_B is the base (non-translated) table in the Oracle E-Business Suite Learning Management module (OTA) that defines certification objects. A certification is a structured learning outcome that belongs to a category and may consist of one or more courses. Certifications can be configured as one-time achievements or as renewable credentials, and they typically must be completed by a learner within a specific period. As the "_B" suffixed table, it stores language-independent certification attributes, while translated, user-facing text such as names and descriptions is held in OTA_CERTIFICATIONS_TL. The table is owned by the OTA schema and is present in both 12.1.1 and 12.2.2 with a documented physical schema of 43 columns in ETRM 12.2.2.

From a modeling perspective, the Data Vault classification derived heuristically from the foreign-key structure is hub-leaning. This is supported by the single-column surrogate primary key and the fact that three dependent tables reference this table by CERTIFICATION_ID rather than the reverse, indicating that OTA_CERTIFICATIONS_B functions as a central business entity from which related descriptive and transactional data are keyed.

Key Information Stored

The primary key is CERTIFICATION_ID, enforced through the unique index OTA_CERTIFICATIONS_B_PK. This column is the sole documented business-key candidate and serves as the surrogate identifier referenced by all dependent tables. Among the most significant attributes are:

Common Use Cases and Queries

Typical reporting and integration scenarios include listing active certifications for a business group, identifying qualifications approaching expiry for notification processing, and joining base rows to translated names for user-facing output. A representative query joining the base and translation tables is shown below.

SELECT c.certification_id, t.name, c.renewable_flag, c.validity_duration, c.notify_days_before_expire
FROM   ota_certifications_b c,
       ota_certifications_tl t
WHERE  c.certification_id = t.certification_id
AND    t.language = USERENV('LANG')
AND    c.business_group_id = :p_bg_id
AND    TRUNC(SYSDATE) BETWEEN NVL(c.start_date_active, SYSDATE)
                          AND NVL(c.end_date_active, SYSDATE);

Enrollment rosters for a certification can be obtained by joining OTA_CERT_ENROLLMENTS on CERTIFICATION_ID, while the composition of a certification (its member courses) is retrieved through OTA_CERTIFICATION_MEMBERS. Expiration-focused extracts filter on VALIDITY_DURATION, RENEWAL_DURATION, and NOTIFY_DAYS_BEFORE_EXPIRE to feed notification and compliance dashboards.

Related Objects

The following objects reference or depend on OTA_CERTIFICATIONS_B through CERTIFICATION_ID:

  • OTA_CERTIFICATIONS_TL — the translation (TL) table holding language-specific certification names and descriptions; joined on CERTIFICATION_ID.
  • OTA_CERTIFICATION_MEMBERS — defines the courses or objects that make up a certification; joined on CERTIFICATION_ID.
  • OTA_CERT_ENROLLMENTS — records learner enrollments in certifications; joined on CERTIFICATION_ID.

Beyond the documented foreign keys, related OTA entities such as certification categories and competency definitions are commonly accessed in the same reporting joins, since a certification belongs to a category and completion may update competency records. All foreign-key relationships above are documented as referencing CERTIFICATION_ID on this table, confirming OTA_CERTIFICATIONS_B as the hub entity for certification data in Oracle Learning Management.