Results for “ota_utest_responses”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

OTA_UTEST_RESPONSES is a transactional table in the OTA (Learning Management) schema of Oracle E-Business Suite, documented as VALID in both 12.1.1 and 12.2.2. It stores the individual answer records generated when an online test is assembled and delivered to a learner. When a test is instantiated for a user, one row is inserted for every question in that test instance. As the learner works through the test, the row is updated to reflect the selected response, whether the question has been answered, and whether the answer is correct. The table therefore functions as the persistence layer for in-progress and completed test attempts.

Its role in EBS is operational rather than setup-oriented: it is written by the test-taking runtime and read by scoring, review, and results-reporting processes. The table carries WHO-style audit columns and an OBJECT_VERSION_NUMBER for optimistic locking, consistent with OAF-based self-service pages. Under the heuristic Data Vault classification mined from its foreign-key structure, this object is best modeled as a link, since it resolves the many-to-many relationship between a user's test questions and the predefined response values and response types.

Key Information Stored

• USER_TEST_RESPONSE_ID — surrogate primary key, enforced by unique index OTA_UTEST_RESPONSES_PK; the only documented business-key candidate.
• USER_TEST_QUESTION_ID — foreign key to OTA_UTEST_QUESTIONS, tying the response to the specific question instance presented to the learner.
• QUESTION_ID — foreign key to OTA_QUESTIONS, identifying the underlying question definition independent of the test instance.
• RESPONSE_VALUE_ID — foreign key to OTA_RESPONSE_VALUES, the actual answer option selected.
• RESPONSE_TYPE_ID — foreign key to OTA_RESPONSE_TYPES, classifying the kind of response recorded.
• ANSWERED_FLAG — indicates whether the learner has supplied an answer for the question.
• CORRECT_FLAG — indicates whether the recorded answer is judged correct for scoring purposes.
• TEXT — free-text or descriptive content associated with the response, used where the answer is not a coded value.
• BUSINESS_GROUP_ID — multi-tenant partition key isolating data by operating unit/business group.
• OBJECT_VERSION_NUMBER, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard audit and concurrency-control columns.

Common Use Cases and Queries

Typical scenarios include retrieving a learner's completed answers for score review, auditing unanswered questions, and reproducing test results for compliance reporting. All queries should filter on BUSINESS_GROUP_ID and restrict to the relevant test question set.

• Retrieve all responses for a test instance:
  SELECT r.USER_TEST_RESPONSE_ID, r.QUESTION_ID, r.RESPONSE_VALUE_ID, r.CORRECT_FLAG, r.ANSWERED_FLAG FROM OTA.OTA_UTEST_RESPONSES r, OTA.OTA_UTEST_QUESTIONS q WHERE r.USER_TEST_QUESTION_ID = q.USER_TEST_QUESTION_ID AND q.USER_TEST_ID = :user_test_id;
• Count correct answers per attempt by joining to OTA_RESPONSE_VALUES for display text.
• List unanswered questions (ANSWERED_FLAG = 'N') to detect incomplete attempts.
• Reconcile stored responses against their question definitions via OTA_QUESTIONS for content reporting.

Related Objects

• OTA_UTEST_QUESTIONS — parent of USER_TEST_QUESTION_ID; holds the presented question instance.
• OTA_QUESTIONS — joined on QUESTION_ID; source question definition.
• OTA_RESPONSE_VALUES — joined on RESPONSE_VALUE_ID; the answer options.
• OTA_RESPONSE_TYPES — joined on RESPONSE_TYPE_ID; classifies response behavior.
• OTA_UTEST_RESPONSES_PK — primary key index on USER_TEST_RESPONSE_ID.
• Test-scoring and results-reporting processes within the OTA Learning Management module consume this table alongside OTA_UTEST_QUESTIONS and OTA_QUESTIONS.

Because this is a high-volume transactional object, DBA maintenance should monitor index statistics on the primary key and the four foreign-key columns to maintain join performance.