Search Results ota_cert_approval_ss




Overview
Explore in AI Chat

Overview

OTA_CERT_APPROVAL_SS is an Oracle E-Business Suite PL/SQL package owned by the APPS schema that supports the certification enrollment and approval lifecycle within Oracle Learning Management / Oracle Training Administration (OTA). The package declares AUTHID CURRENT_USER, so its SQL executes with the privileges of the invoking user rather than the definer, which is standard for EBS server-side APIs that must respect the caller's security context and MOAC (Multi-Org Access Control) settings.

The suffix _SS indicates a server-side package, and the embedded header comment ($Header: otcrtrev.pkh 120.1 2005/07/11) shows the package is long-established, with an interface that has remained stable across the 11i through 12.1.1 and 12.2.2 releases. Its central business purpose is to persist certification enrollment records, drive the approval (workflow-driven) decision on a certification, and finally create, cancel, or validate the resulting enrollment. In ETRM terms it is classified as API classification OTHER, meaning it is not a formally published public API and is not referenced by any other packaged API (referenced by 0 other packages), but it is used internally by OTA certification forms and workflow processes.

Key Procedures and Functions

  • SAVE_CERT_ENROLL_DETAIL — The principal enrollment persistence routine. It accepts the login person, item type, item key, activity, certification, and person identifiers together with certification status, completion, unenrollment, expiration, and earliest-enroll dates, plus a history flag and the full 20-column DFF attribute set. It writes or updates the certification enrollment detail and returns an error message through an OUT parameter.
  • GET_ADD_ENR_DTL_DATA_FROM_TT — Reads add-enrollment detail data from a PL/SQL table (the "TT" pattern), passing back the assembled enrollment detail structure to its caller. This supports bulk or multi-row form processing.
  • GET_REVIEW_DATA — Retrieves the certification and enrollment information required to present a review or approval page.
  • PROCESS_API — The driver that coordinates the individual steps of the certification approval and enrollment transaction.
  • CREATE_ENROLLMENT — Creates an actual certification enrollment once the approval path permits it.
  • CANCEL_ENROLLMENT — Reverses or cancels an existing certification enrollment.
  • VALIDATE_ENROLLMENT — Performs the business-rule validation of an enrollment before it is committed.
  • APPROVED — Records the approved outcome of a certification approval request and hands off to enrollment creation.
  • GET_APPROVAL_REQ — The routine the user searched for; it returns the approval request associated with a certification item, supplying the item type, item key, and activity context that the workflow-based approval requires.

Tables Accessed

  • OTA_CERT_ENROLLMENTS — the primary certification enrollment entity that is created, validated, cancelled, and updated by this package.
  • OTA_CERTIFICATIONS_B / OTA_CERTIFICATIONS_TL — the certification definition base and translated tables, read to validate the certification and expose its name.
  • WF_ITEMS — the Oracle Workflow item table, written to seed or update the approval workflow item so the certification approval can be routed.
  • HR_API_TRANSACTION_STEPS / HR_API_TRANSACTION_VALUES — the HR API transactional staging tables used to hold pending changes for validation and commit.

Usage Notes

OTA_CERT_APPROVAL_SS is invoked server-side, typically from the Oracle Learning Management certification enrollment and approval forms and from the certification approval workflow. Because the package is not a published API and is not referenced by other packaged APIs, customizations should treat it as an internal server-side routine: callers must supply correctly typed p_item_type, p_item_key, and p_activity_id values matching the workflow item, and must handle the OUT NOCOPY error message parameter. In 12.1.1 and 12.2.2 the Oracle Workflow item-key is the reliable correlation point for GET_APPROVAL_REQ, and the 20 attribute columns allow descriptive flexfield values to travel with the enrollment. Direct custom calls should be avoided where possible; integrate through supported OTA certification APIs and workflow events instead.