Search Results insert_answer

Overview

IES_ANSWERS_PVT is a private PL/SQL package in the APPS schema that encapsulates the core data manipulation logic for the iLearning (IES) answer dictionary. The iLearning module stores predefined answer values associated with lookup codes; these answers feed questionnaires, surveys, and competency-style assessments presented to learners. Because the package is classified as PVT (private), it is not a published integration API and is not intended for direct invocation by external or customer code. Instead it acts as the workhorse layer beneath the public iLearning APIs, providing the physical insert and update operations against the IES_ANSWERS table.

The package body header indicates a last revision of 115.7, dated June 2003, which places the code in the same release lineage carried forward into Oracle EBS 12.1.1 and 12.2.2. The procedures were written with the standard EBS API conventions of the period, including the p_api_version, p_init_msg_list, p_commit, and p_validation_level parameters, together with the x_return_status, x_msg_count, and x_msg_data OUT parameters used to communicate success or failure back to the calling layer.

Key Procedures and Functions

• INSERT_ANSWER — Creates a new answer record. It acquires a primary key from the IES_ANSWERS_S sequence, then performs a dynamic INSERT of the answer attributes (created_by, creation_date, lookup_id, answer_value, answer_display_value, answer_order, answer_active, and active_status) into the answers table. The RETURNING clause captures the generated answer_id into the x_answer_id OUT parameter. A savepoint named Insert_Answer_PVT is established at entry; on any error the procedure rolls back to that savepoint and returns x_return_status of 'E' with SQLCODE and SQLERRM concatenated into x_msg_data.
• UPDATE_ANSWER — Modifies an existing answer identified by p_answer_id, accepting the last updated by stamp along with the revised answer value, display value, ordering, and status attributes. It follows the same savepoint, exception, and message-handling pattern as INSERT_ANSWER, ensuring partial changes are not committed when an error occurs.

Both procedures are documented procedures only; the metadata records no additional published functions, and the package is referenced by zero other packages in the documented dependency view.

Tables Accessed

The package writes exclusively to the IES_ANSWERS table, resolving the reference through an APPS synonym. IES_ANSWERS holds one row per candidate answer for a given lookup, with columns for the internal answer identifier, the owning lookup, the stored answer value, the label shown to the learner, the display sequence, and two status flags controlling whether the answer is presentation-active and whether the underlying record is active. The IES_ANSWERS_S sequence supplies the answer_id primary key. No other tables are touched by either documented procedure.

Usage Notes

Because IES_ANSWERS_PVT is a private package, normal application flows reach it indirectly through iLearning setup and administration screens that maintain the answer dictionary. Developers who locate this object while tracing an "insert_answer" operation should treat it as an internal implementation detail: the recommended entry point for custom code is the corresponding public iLearning API rather than this private package. Where direct calls are unavoidable, callers must supply a valid p_api_version, honor the x_return_status value before proceeding (checking that it is not 'E'), and inspect x_msg_data for diagnostics, since the package returns raw SQL error text rather than FND_MESSAGE-formatted text. The dynamic SQL construction used inside the procedures should be reviewed carefully by any extension that attempts to reuse or wrap the logic, as string-built insert statements are sensitive to parameter ordering and data type coercion.