Search Results igsbv_ad_act_assessments

Overview

IGSBV_AD_ACT_ASSESSMENTS is a read-only reporting view within the Oracle E-Business Suite IGS - Student System product family. Its purpose is to expose ACT assessment details for persons recorded in the institution's student system, presenting each assessment record together with its Reporting Year, Test Date, and Test Type. The view consolidates assessment data held in the ACT assessments table with a person identifier resolved through the alternate person identifier table, allowing ACT test records to be linked to the institution's internal person records.

The view is documented as obsolete and, per the ETRM implementation notes, "Not implemented in this database." This indicates that the view is a legacy seeded object that may not be present in every environment; it exists primarily for historical reporting and integration against ACT assessment feeds. In Oracle EBS 12.1.1 and 12.2.2, such views are typically consumed by admissions, enrolment, and institutional research reporting rather than for transactional processing. Because the view is defined WITH READ ONLY, consumers cannot perform DML against it.

The view returns one row per ACT assessment record per reporting year and test type, enriched with biographical, address, and high-school attributes supplied by the upstream ACT feed.

Underlying Base Objects

The documented view text identifies two base objects:

• IGS_AD_ACT_ASSESSMENTS (alias ACTAS) — the primary source table holding ACT assessment records, including identifiers, test metadata, scores, demographic fields, and high-school attributes.
• IGS_PE_ALT_PERS_ID (alias PEA) — the alternate person identifier table, joined to resolve the institution's internal person identifier where the person identifier type is 'ACTID'.

The join is an outer join (PEA.API_PERSON_ID (+) and PEA.PERSON_ID_TYPE(+) = 'ACTID'). The ACT identifier is normalised before matching: a leading hyphen in IGS_AD_ACT_ASSESSMENTS.ACT_IDENTIFIER is stripped using DECODE(SUBSTR(...,1,1), '-', SUBSTR(...,2), ...) so that the value compared to PEA.API_PERSON_ID corresponds to the stored alternate identifier. Because the person match is an outer join, assessment rows without a resolved person identifier are still returned, with PERSON_IDENTIFIER null.

No other base objects are documented in the ETRM metadata. Note that documentation of columns is truncated and no explicit owner is recorded.

Key Columns

• PERSON_IDENTIFIER — the institution's internal person identifier resolved from the alternate person identifier table.
• REPORTING_YEAR, TEST_TYPE, TEST_DATE_TXT — the reporting year, test type, and test date (stored as text) associated with the assessment.
• ACT_IDENTIFIER, ACT_BATCH_ID, RELEASE, NORMS_TYPE — the ACT identifier, batch identifier, release code, and norms type from the source feed.
• LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, GENDER, DATE_OF_BIRTH_TXT — biographical attributes of the tested person.
• STREET_ADDRESS, CITY, STATE_CODE, STATE_ABBREVIATION, ZIP_CODE, HOME_PHONE — contact and address details.
• GRADE_LEVEL, HIGH_SCHOOL_CODE, HIGH_SCHOOL_AVERAGE, HIGH_SCHOOL_GRADUATION — high-school attributes and academic indicators.
• CORRECTED_REPORT_INDICATOR, SUM_OF_SCALE_SCORES, COLLEGE_CHOICE, COLLEGE_CODE, LOCAL_IDENTIFIER — corrected-report flag, aggregate score, and college preference/selection data. COLLEGE_CODE is the column most frequently sought by users searching on this view.
• INTERFACE_TRANSFER_DATE and audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE) — integration and row-level audit information.

Common Use Cases and Queries

Typical uses include admissions processing, high-school cohort analysis, verification of ACT college choices, and reconciliation of ACT batch loads. Sample query returning assessment rows for a specific college code:

SELECT person_identifier,
       reporting_year,
       act_identifier,
       test_type,
       test_date_txt,
       last_name,
       first_name,
       college_code,
       sum_of_scale_scores
  FROM igsbv_ad_act_assessments
 WHERE college_code = :p_college_code
   AND reporting_year = :p_reporting_year;

A second query lists unmatched ACT records (no resolved person) for data-cleanup review:

SELECT act_identifier, last_name, first_name, test_type, test_date_txt
  FROM igsbv_ad_act_assessments
 WHERE person_identifier IS NULL
 ORDER BY reporting_year, last_name;

Because the view is read-only and reportedly not implemented in all environments, availability should be confirmed against the target instance before relying on it in custom reports or interfaces.