Search Results ota_tsr_shd




Overview
Explore in AI Chat

Overview

OTA_TSR_SHD is the shared ("SHD") helper package body for the Oracle Time and Labor / OTA (Oracle Training Administration) family of PL/SQL APIs that manage suppliable resources. Its principal business function is to provide a common infrastructure layer for the DML-handling APIs operating against the OTA_SUPPLIABLE_RESOURCES entity. This shared package centralises three recurring concerns: tracking whether an API has performed a DML operation, translating Oracle constraint violations into user-facing Oracle HRMS messages, and managing row-level locking during update processing. In Oracle EBS 12.1.1 and 12.2.2 the package resides in the APPS schema (owner APPS) and is classified in ETRM as an "OTHER" API rather than a public business API, meaning it is intended principally for internal consumption by other OTA packages and by the Oracle Forms layer, not as a standalone integration point.

Key Procedures and Functions

  • RETURN_API_DML_STATUS — A function returning a Boolean that reports the status of the package-level g_api_dml flag. It allows callers to determine whether a DML statement has already been issued within the current API invocation, a common pattern used to decide whether further processing or a commit is required.
  • CONSTRAINT_ERROR — A procedure that accepts a constraint name and raises a standardised Oracle HRMS error. It maps specific constraint names (for example OTA_SUPPLIABLE_RESOURCES_FK1 and FK2) to the message HR_6153_ALL_PROCEDURE_FAIL via hr_utility.set_message, sets the PROCEDURE and STEP message tokens, and then calls hr_utility.raise_error. This is the origin of the "hr_6153_all_procedure_fail" error commonly observed during OTA updates.
  • API_UPDATING — A procedure that signals or records that an update operation is in progress, supporting the package's locking and DML-status bookkeeping.
  • LCK — A locking procedure used to acquire/verify a row lock on the suppliable resources record before modification, preventing concurrent update conflicts.
  • CONVERT_ARGS — A utility procedure for converting or conditioning argument values passed into the OTA APIs, ensuring consistent types and defaults before processing.

Tables Accessed

The documented table reference is OTA_SUPPLIABLE_RESOURCES, accessed via its APPS synonym. This table stores the resources that can be supplied against OTA requirements; the package's DML-status flag, lock routine (LCK), and constraint-error handling all relate directly to insert, update, and delete activity on this entity. The constraint names referenced in CONSTRAINT_ERROR (OTA_SUPPLIABLE_RESOURCES_FK1 and FK2) confirm the dependency, as those foreign keys belong to this table.

Usage Notes

OTA_TSR_SHD is invoked indirectly. It is referenced by four other packages, which call its status, locking, and error-translation routines rather than exposing them to end users. In practice, these routines fire during Oracle Forms-based maintenance of suppliable resources and during concurrent or custom processing that manipulates OTA_SUPPLIABLE_RESOURCES. When a foreign-key or integrity constraint is violated, CONSTRAINT_ERROR raises HR_6153_ALL_PROCEDURE_FAIL with the PROCEDURE and STEP tokens populated — the exact signature users encounter when searching "hr_6153_all_procedure_fail". Diagnosing such errors therefore requires identifying the failing constraint (FK1, FK2) reported in the message and correcting the underlying suppliable-resource data, not the package itself. Because the package is a shared internal component, custom code should call the higher-level OTA APIs that wrap it rather than invoking OTA_TSR_SHD routines directly.