Search Results calculate_cme_status

Overview

APPS.OTA_CME_UTIL is a utility PL/SQL package within the Oracle E-Business Suite OTA (Oracle Training Administration) product family. Its functional domain centers on Certification Member Enrollment (CME) and Certification Program Enrollment (CPE) tracking, which allow organizations to record and maintain the enrollment of employees and external delegates into certification programs and their constituent member activities. The package abstracts the underlying certification enrollment data model so that higher-level APIs, forms, and concurrent processes can query enrollment state, evaluate whether an active certification enrollment exists, compute or refresh a certification's current status, and retrieve surrogate keys for member and program enrollment records. It is classified as a UTIL package in the ETRM registry, reflecting its role as a shared internal service layer rather than a published business API. In the 12.1.1 and 12.2.2 releases the object resides in the APPS schema and is reported as VALID.

Key Procedures and Functions

The documented package exposes eleven procedures and functions. Those confirmed by name include:

• GET_ENRL_STATUS_ON_UPDATE — Derives the appropriate enrollment status to be applied when an enrollment record is updated, ensuring status transitions remain consistent with certification rules.
• CALCULATE_CME_STATUS — Computes the certification member enrollment status from associated activity, booking, and certification data.
• UPDATE_CME_STATUS — Persists a recalculated member enrollment status onto the relevant enrollment record.
• UPDATE_CPE_STATUS — Applies status changes to certification program enrollment records, mirroring member-level processing at the program level.
• CHK_IF_CME_EXISTS — Boolean-style existence check determining whether a certification member enrollment already exists for a given member/activity combination.
• REFRESH_CME — Re-evaluates and synchronizes member enrollment state after upstream changes to certifications, offerings, or events.
• CHK_ACTIVE_CME_ENRL — Determines whether an active member enrollment is present, typically used before permitting further status modification.
• GET_CERT_MBR_ENROLL_ID — Returns the primary key identifier of a certification member enrollment record.
• GET_CERT_PRD_ENROLL_ID — Returns the primary key identifier of a certification program enrollment record.

All nine named routines operate against the certification enrollment tables and are invoked internally by higher-level APIs; none are documented as public integration entry points.

Tables Accessed

Through APPS synonyms the package reads and writes a defined set of OTA tables. OTA_CERT_ENROLLMENTS and OTA_CERTIFICATION_MEMBERS hold the certification and member master records that anchor enrollment logic. OTA_CERT_MBR_ENROLLMENTS and OTA_CERT_PRD_ENROLLMENTS store the member-level and program-level enrollment rows that the package creates, inspects, and updates. OTA_ACTIVITY_VERSIONS and OTA_EVENTS supply activity version and event context used in status calculation. OTA_BOOKING_STATUS_TYPES provides the valid booking status codes referenced when deriving enrollment status. OTA_DELEGATE_BOOKINGS links third-party delegate bookings to enrollment records. Additional referenced objects include OTA_CATEGORY_USAGES, OTA_CERTIFICATIONS_B, OTA_EVENTS_TL, and OTA_OFFERINGS.

Usage Notes

OTA_CME_UTIL is an internal utility and is not intended for direct customer invocation. ETRM records show it is referenced by seven dependent packages, including OTA_CERT_ENROLLMENT_API, OTA_CERT_MBR_ENROLLMENT_API, OTA_CPE_UTIL, OTA_DELEGATE_BOOKING_API, OTA_LO_UTILITY, OTA_TDB_DEL, and OTA_TRAINING_RECORD, and it references itself. These callers invoke it during enrollment creation, status recalculation, and refresh processing triggered from Training Administration forms and concurrent programs. Customizations should call the published OTA enrollment APIs rather than this package, since its interface is unsupported and may change between releases.