Search Results igsbv_ad_act_profiles_sps

Overview

The view IGSBV_AD_ACT_PROFILES_SPS is a read-only reporting object within the Oracle E-Business Suite IGS – Student System module. It exposes ACT profile components and category details for records whose profile type is restricted to 'SPSQ', denoting the SPS Questionnaire classification of ACT assessment data. The view is documented as obsolete in current releases and is explicitly noted as not implemented in this database in the 12.2.2 ETRM metadata, meaning the underlying view definition exists in the reference repository but is not deployed in every environment.

Its role is to flatten the parent ACT profile table together with alternate person identifier data into a single denormalized result set suitable for reporting engines, concurrent program output, and integration extracts. The naming convention prefix IGSBV identifies it as a business view belonging to the Student System, while the _SPS suffix distinguishes the SPS Questionnaire slice from other ACT profile views. The view is declared WITH READ ONLY, preventing DML through the view and reinforcing its reporting orientation.

Underlying Base Objects

The view text references two base tables:

• IGS_AD_ACT_PROFILES (aliased ACTPR) — the primary ACT profile storage table holding assessment identifiers, test type, test dates, profile categories, code columns, educational major, and vocational choices.
• IGS_PE_ALT_PERS_ID (aliased PEA) — the alternate person identifier table, joined to resolve the ACT identifier to a person record.

The join is an outer join (PEA.API_PERSON_ID(+)) constrained to PEA.PERSON_ID_TYPE(+) = 'ACTID'. A notable transformation occurs in the join predicate: when the first character of ACTPR.ACT_IDENTIFIER is a hyphen, the hyphen is stripped via nested SUBSTR before matching against the alternate identifier. Records are filtered to ACTPR.PROFILE_TYPE = 'SPSQ', isolating SPS Questionnaire profiles from other ACT profile types.

Key Columns

• PERSON_IDENTIFIER — the resolved person identifier sourced from PEA.PE_PERSON_ID; may be null where no matching ACTID alternate identifier exists.
• ACT_IDENTIFIER — the ACT assessment identifier; the value used in the join logic, with leading hyphens handled explicitly.
• REPORTING_YEAR / TEST_TYPE / TEST_DATE_TXT — reporting period, ACT test classification, and textual test date.
• PROFILE_CATEGORY_CODE / PROFILE_CATEGORY / PROFILE_CATEGORY_DESC — the category code plus two label columns resolved through IGS_LOOKUP_VALUES for the ACT_PROFILE_CATEGORY lookup, exposing MEANING and DESCRIPTION.
• CODE1 through CODE21 — generic profile component code slots holding graded ACT component detail.
• EDUC_MAJOR — the educational major declared on the ACT profile; this is the column matched by the search term educ_major.
• VOC_CHOICE1 / VOC_CHOICE2 and CHOICE1 through CHOICE5 — vocational and general preference selections.
• Audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE.

Common Use Cases and Queries

Typical reporting queries filter on reporting year and profile category or extract declared majors for cohort analysis. Because EDUC_MAJOR is a free-text descriptor, category-based aggregation is usually combined with a lookup join.

• Identify declared majors for a reporting year: SELECT person_identifier, act_identifier, educ_major FROM igsbv_ad_act_profiles_sps WHERE reporting_year = :p_year;
• Summarize SPS Questionnaire profiles by category: SELECT profile_category, COUNT(*) FROM igsbv_ad_act_profiles_sps GROUP BY profile_category;
• Detect unmatched person records where the ACTID join did not resolve: SELECT act_identifier FROM igsbv_ad_act_profiles_sps WHERE person_identifier IS NULL;
• Extract component codes for integration feeds: SELECT person_identifier, code1, code2, educ_major, voc_choice1 FROM igsbv_ad_act_profiles_sps WHERE test_type IS NOT NULL;

Because the object is obsolete and not implemented universally, confirm its presence in the target instance before relying on it; equivalent data is generally retrievable directly from IGS_AD_ACT_PROFILES where the view is unavailable.