Search Results quantity_per_student

Overview

The IGS_AS_ITM_EXAM_MTRL table is a core data object within the Oracle E-Business Suite (EBS) 12.1.1/12.2.2, specifically for the Oracle Student Management (OSM) or iLearning modules. It serves a critical administrative function by defining the materials policy for examinations. The table centrally stores which material types—such as calculators, textbooks, or answer booklets—are allowed, disallowed, or must be supplied by the institution for a specific assessment item (exam). This enables precise control over exam conditions and logistics, ensuring consistency and fairness across assessments.

Key Information Stored

The table's structure captures the relationship between an assessment item and examination materials, along with policy details. Key columns include:

• ASS_ID: A mandatory foreign key linking to the assessment item (IGS_AS_ASSESSMNT_ITM_ALL). It is part of the table's primary key.
• EXAM_MATERIAL_TYPE: A mandatory code (VARCHAR2(10)) identifying the type of material, such as 'CALC' for calculator. It references the lookup table IGS_AS_EXM_MTRL_TYPE and is the second component of the primary key.
• S_MATERIAL_CAT: A VARCHAR2(10) code indicating the system category for the material type: allowable, non-allowable, or supplied. This defines the core policy.
• QUANTITY_PER_STUDENT: A NUMBER field specifying how many units of a material type are to be provided to each student. This is applicable only for materials in the 'supplied' category (e.g., number of answer booklets).
• COMMENTS: A VARCHAR2(2000) field for storing additional descriptive information or special instructions regarding the exam materials.
• Standard WHO Columns: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN for audit tracking.

Common Use Cases and Queries

A primary use case is generating examination instructions and logistics reports for students and invigilators. For instance, a report can list all supplied materials and their quantities for a specific exam to facilitate preparation. Another critical use is validating student-submitted materials against the policy defined in this table during exam registration or entry. A common query pattern retrieves the complete materials policy for an assessment:

SELECT iem.EXAM_MATERIAL_TYPE, iem.S_MATERIAL_CAT, iem.QUANTITY_PER_STUDENT, iem.COMMENTS
FROM IGS_AS_ITM_EXAM_MTRL iem
WHERE iem.ASS_ID = :p_assessment_id
ORDER BY iem.S_MATERIAL_CAT, iem.EXAM_MATERIAL_TYPE;

To find all exams where a specific material, like a calculator, is supplied, one would join with the assessment items table:

SELECT iaia.ASS_ID, iaia.ASSESSMENT_NAME
FROM IGS_AS_ITM_EXAM_MTRL iem
JOIN IGS_AS_ASSESSMNT_ITM_ALL iaia ON iem.ASS_ID = iaia.ASS_ID
WHERE iem.EXAM_MATERIAL_TYPE = 'CALC'
AND iem.S_MATERIAL_CAT = 'SUPPLIED';

Related Objects

The table maintains defined integrity relationships with other key entities in the assessment schema. Its primary key (ASS_ID, EXAM_MATERIAL_TYPE) is enforced by the unique index IGS_AS_ITM_EXAM_MTRL_U1. It has two documented foreign key relationships:

• References IGS_AS_ASSESSMNT_ITM_ALL: The ASS_ID column links to the parent assessment item table, ensuring every material record is associated with a valid exam.
• References IGS_AS_EXM_MTRL_TYPE: The EXAM_MATERIAL_TYPE column references this lookup table, which holds the valid codes and descriptions for material types (e.g., 'BOOK', 'CALC', 'BOOKLET'). This is the key relationship for the user's search context of "exam_material_type".

The non-unique index IGS_AS_ITM_EXAM_MTRL_N1 on EXAM_MATERIAL_TYPE supports efficient queries filtering by material type.