Results for “igs_as_grd_schema”

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

Overview

IGS_AS_GRD_SCHEMA is the grading schema definition table within the Oracle E-Business Suite Student System (IGS) product family. It describes a reusable set of available marks, grades, and results that are applied when assessing student unit attempts. Each row represents one versioned grading schema, allowing institutions to maintain historical definitions alongside current ones without overwriting prior configurations. The table is owned by the IGS schema and is marked VALID in the documented ETRM 12.1.1 and 12.2.2 releases.

From a Data Vault modeling perspective, the metadata's heuristic classification is hub-leaning. The table carries a stable business key pair (GRADING_SCHEMA_CD and VERSION_NUMBER) that is referenced by a wide set of dependent tables, which is characteristic of a hub entity. Its descriptive and audit attributes (description text, date ranges, and the ATTRIBUTE1–20 flexfield block) exhibit satellite-like behavior, so a disciplined model would separate the identity hub from its descriptive satellite rather than treating all 33 columns as a single flat dimension.

Key Information Stored

The primary key is IGS_AS_GRD_SCHEMA_PK, defined on the composite of GRADING_SCHEMA_CD and VERSION_NUMBER. A unique index, IGS_AS_GRD_SCHEMA_U1, exists on the same two columns, confirming them as the business-key candidates. The most significant columns include:

• GRADING_SCHEMA_CD — the grading schema code; the primary business identifier.
• VERSION_NUMBER — the version of that schema; combined with the code, forms the composite key.
• DESCRIPTION — descriptive text for the schema.
• GRADING_SCHEMA_TYPE — classifies the schema, distinguishing how marks, grades, and results are resolved.
• START_DT and END_DT — the effective date range during which the schema version is valid.
• COMMENTS — free-form notes attached to the schema.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard WHO audit columns.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE20 — the descriptive flexfield (DFF) block used for institution-specific extensions.

The surrogate primary key is a composite business key rather than a system-generated sequence; there is no single-column synthetic ID in the documented structure.

Common Use Cases and Queries

Typical usage involves validating that a schema version is currently effective, resolving the schema assigned to a unit offering or unit attempt, and reporting on the schemas associated with awards or offers. A common pattern joins the schema to its dependent grade records:

• Lookup of an active schema by code and effective date:

SELECT grading_schema_cd, version_number, description, grading_schema_type
FROM   igs_as_grd_schema
WHERE  grading_schema_cd = :code
AND    TRUNC(SYSDATE) BETWEEN start_dt AND NVL(end_dt, TRUNC(SYSDATE));

• Reporting the grade components of a schema by joining to IGS_AS_GRD_SCH_GRADE on GRADING_SCHEMA_CD and VERSION_NUMBER, useful when producing mark-to-grade mapping documentation.
• Auditing schema assignments to unit attempts through IGS_AS_UNITASS_ITEM_ALL or IGS_PS_UNITASS_ITEM, joined on GRADING_SCHEMA_CD and GS_VERSION_NUMBER.
• Extracting DFF data (ATTRIBUTE1–20) for downstream analytics, filtering on ATTRIBUTE_CATEGORY.

All queries should honor the START_DT/END_DT window to avoid selecting superseded versions of the same schema code.

Related Objects

IGS_AS_GRD_SCHEMA is a hub referenced by numerous dependent tables. The most significant include:

• IGS_AS_GRD_SCH_GRADE — the grade/mark composition of a schema; joined on GRADING_SCHEMA_CD and VERSION_NUMBER.
• IGS_AS_APPR_GRD_SCH — approved grading schemas; joined on GRADING_SCHEMA_CD and GS_VERSION_NUMBER.
• IGS_AS_INC_GRD_CPROF — incomplete grade component profiles; joined on GRADING_SCHEMA_CD and VERSION_NUMBER.
• IGS_AS_UNITASS_ITEM_ALL — unit assessment items; joined on GRADING_SCHEMA_CD and GS_VERSION_NUMBER.
• IGS_PS_UNITASS_ITEM — unit attempt items; joined on GRADING_SCHEMA_CD and GS_VERSION_NUMBER.
• IGS_PS_UNIT_GRD_SCHM — unit grading schemas; joined on GRADING_SCHEMA_CODE and GRD_SCHM_VERSION_NUMBER.
• IGS_PS_USEC_GRD_SCHM — use-case grading schemas; joined on GRADING_SCHEMA_CODE and GRD_SCHM_VERSION_NUMBER.
• IGS_PS_UNIT_OFR_OPT_ALL — unit offering options; joined on GRADING_SCHEMA_CD and GS_VERSION_NUMBER.
• IGS_EN_PLAN_UNITS — plan units; joined on GRADING_SCHEMA_CODE and GS_VERSION_NUMBER.
• IGS_PS_AWD and IGS_PS_OFR_PAT — award and offer patterns referencing the schema via GRADING_SCHEMA_CD and GS_VERSION_NUMBER.

Because the same logical schema is referenced through slightly different column names (GRADING_SCHEMA_CD versus GRADING_SCHEMA_CODE, VERSION_NUMBER versus GS_VERSION_NUMBER), join predicates must be written against the exact column names in each dependent table.