Search Results igs_ad_act_assessments




Overview
Explore in AI Chat

Overview

IGS.IGS_AD_ACT_ASSESSMENTS is a Student System (IGS) staging and reference table within Oracle E-Business Suite 12.1.1 and 12.2.2 that stores ACT assessment records for persons associated with student recruiting and admissions processing. Its documented description is "ACT assessment details of persons with Reporting Year, Test Date and Test Type." The object resides in the IGS schema and holds 33 documented columns in the ETRM 12.1.1 physical schema. It typically captures data imported from external ACT test score files via batch interfaces, providing the admissions office with standardized test results used in prospect and applicant evaluation.

From a heuristic Data Vault modeling perspective, IGS_AD_ACT_ASSESSMENTS is classified as hub-leaning. Its four-part primary key (REPORTING_YEAR, ACT_IDENTIFIER, TEST_TYPE, TEST_DATE_TXT) behaves as a composite business key that uniquely identifies each assessment event, making the table a natural candidate for a hub entity with the descriptive ACT attributes modeled as satellites.

Key Information Stored

The unique index IGS_AD_ACT_ASSESSMENTS_PK on the four columns listed above serves as the business-key candidate; the table itself does not expose a single-column surrogate key in the documented metadata.

Common Use Cases and Queries

Admissions and institutional research teams query this table to load and validate ACT score data, reconcile inbound batches, and feed downstream applicant evaluation. A representative query joins the assessments to profile data by the shared key columns:

SELECT a.REPORTING_YEAR, a.ACT_IDENTIFIER, a.TEST_TYPE,
       a.TEST_DATE_TXT, a.SUM_OF_SCALE_SCORES, a.LOCAL_IDENTIFIER
FROM   IGS.IGS_AD_ACT_ASSESSMENTS a
WHERE  a.REPORTING_YEAR = :p_year
AND    a.TEST_TYPE      = :p_test_type;

Common scenarios include:

  • Batch monitoring: grouping records by ACT_BATCH_ID to confirm successful interface transfer.
  • Score distribution reporting: aggregating SUM_OF_SCALE_SCORES by REPORTING_YEAR or HIGH_SCHOOL_CODE.
  • Duplicate detection: identifying multiple rows per LOCAL_IDENTIFIER across test types.
  • Correction handling: filtering on CORRECTED_REPORT_IND to apply revised scores.

Related Objects

  • IGS_AD_ACT_PROFILES — the primary child table, joined on REPORTING_YEAR, ACT_IDENTIFIER, TEST_TYPE, and TEST_DATE_TXT; profile rows reference the composite key of this table.
  • IGS_AD_INTERFACES / ACT batch staging tables — feed rows into this table via the ACT_BATCH_ID and interface transfer process.
  • IGS_AD_PROSPECTS and the HZ person model — linked indirectly through LOCAL_IDENTIFIER for person resolution.
  • IGS_AD_APPLICATIONS — consumes ACT scores during applicant evaluation and admissions decisions.
  • FND audit and concurrent manager objects — invoked through the standard EBS who-columns and the interface transfer date.