Search Results unique_constraint_violated

Overview

CSD_RT_TRAN_RESPS_PVT is a private PL/SQL API package in the Oracle E-Business Suite APPS schema that manages the transition responsibility records used by Oracle's return-to-vendor and field service return flows. The package encapsulates the create, update, delete, and lock operations against the RT_TRAN_RESP entity, which associates a transition definition with a specific responsibility during Return Material Authorization (RMA) or return-to-supplier processing. It belongs to the CSD (Service) module family, and its header comment identifies the source file as csdvrtrb.pls (version 120.1). As a PVT (private) classification package, it is intended for internal consumption by other CSD APIs and public wrappers rather than direct customer invocation.

All four documented procedures are exposed through a single private package body that adheres to the standard Oracle Application Object Library API programming conventions: a savepoint is set at the start of each transactional procedure, FND_API.Compatible_API_Call validates the API version, and FND_MSG_PUB manages the message stack. Return status is communicated through the standard x_return_status, x_msg_count, and x_msg_data OUT parameters, allowing callers to react to FND_API.G_RET_STS_SUCCESS, G_RET_STS_ERROR, or G_RET_STS_UNEXP_ERROR in a consistent manner.

Key Procedures and Functions

• CREATE_RT_TRAN_RESP — Inserts a new transition responsibility record. It accepts a record of type RT_TRAN_RESP_REC_TYPE and returns the generated x_rt_tran_resp_id. The procedure declares a user-defined exception, UNIQUE_CONSTRAINT_VIOLATED, bound via PRAGMA EXCEPTION_INIT to SQLCODE -00001, so that duplicate-key errors on the underlying table can be trapped, logged through FND_MSG_PUB, and converted into a controlled API failure rather than an unhandled ORA-00001.
• UPDATE_RT_TRAN_RESP — Modifies an existing transition responsibility row, typically to change the responsibility assignment or associated transition attributes. It follows the same savepoint, version-check, and message-stack pattern as the create procedure.
• DELETE_RT_TRAN_RESP — Removes a transition responsibility record. It is the inverse of CREATE and is normally preceded by LOCK_RT_TRAN_RESP to guarantee that the record has not been modified by another session.
• LOCK_RT_TRAN_RESP — Performs a SELECT ... FOR UPDATE against the target row to serialize concurrent modifications before an update or delete. It returns a success/failure status without altering data.

Tables Accessed

The package operates against a single base table (accessed through the APPS synonym) that stores transition responsibility assignments. Column-level details are not exposed in the ETRM metadata, but the x_rt_tran_resp_id OUT parameter confirms the table is keyed by a generated numeric identifier, and the UNIQUE_CONSTRAINT_VIOLATED handler confirms at least one unique index exists—most likely on the (transition_id, responsibility_id) combination. No additional tables are documented as referenced by this package, and the ETRM report shows zero packages referencing it, indicating it is called from higher-level CSD APIs rather than directly by other PL/SQL units.

Usage Notes

Because this is a private API, it is invoked indirectly: typically by public CSD setup or return-processing packages, by Oracle Forms save logic within Service/Returns responsibility setup, or by concurrent programs that bulk-load return transition configurations. Customizations that need to call it must use the APPS credentials (or a synonym) and should pass p_api_version = 1.0, honour the standard FND_API message protocol, and issue their own COMMIT or ROLLBACK according to the value supplied in p_commit. The inclusion of the UNIQUE_CONSTRAINT_VIOLATED exception is significant for developers who encounter ORA-00001 when inserting transition responsibilities: the package is designed to translate that condition into a structured API error retrievable through FND_MSG_PUB.Get, so debug sessions should inspect the FND message stack rather than relying on the raw SQL error alone.