Search Results igsbv_ad_act_profiles_ooca

Overview

The view APPS.IGSBV_AD_ACT_PROFILES_OOCA is a read-only reporting object within the Oracle E-Business Suite Student System (IGS) product family. Its documented purpose is to expose "ACT profile components and category for type as Out of Class Accomplishments." In practical terms, it presents Admissions Test (ACT) profile records that have been classified under the OOCA profile type, together with the associated person identifier, reporting year, test attributes, and free-form code segments.

The "BV" naming convention indicates a business view intended for downstream consumption by reporting tools, extracts, and integration layers rather than for direct transactional maintenance. Because the view is declared WITH READ ONLY, it cannot be used as the target of DML; its role is strictly to select and present data. This makes it suitable for OBIEE/BI Publisher reports, ETRM-based data extraction, and interfaces that need a flattened representation of ACT out-of-class accomplishment profiles.

Underlying Base Objects

The view is defined over two base objects in the IGS schema:

• IGS_AD_ACT_PROFILES (aliased ACTPR) — the primary source of ACT profile data, including reporting year, ACT identifier, test type, test date text, profile category, and the CODE1 through CODE7 attributes.
• IGS_PE_ALT_PERS_ID (aliased PEA) — the alternate person identifier table, joined to resolve the ACT identifier to a person identifier.

The join between the two tables is an outer join (PEA.API_PERSON_ID (+)) constrained by PEA.PERSON_ID_TYPE (+) = 'ACTID', so ACT profile rows are retained even where no matching alternate person identifier exists. The ACT identifier is normalized before joining: a leading hyphen is stripped using DECODE(SUBSTR(...)) so that values such as -123456 resolve to 123456 for the person-identifier match. Only rows where ACTPR.PROFILE_TYPE = 'OOCA' qualify, which is the defining filter that restricts the view to Out of Class Accomplishments.

Key Columns

• PERSON_IDENTIFIER — the resolved person identifier from PEA.PE_PERSON_ID, linking the ACT profile to an individual.
• REPORTING_YEAR — the year the ACT profile result applies to.
• ACT_IDENTIFIER — the ACT identifier (typically the student's ACT ID).
• TEST_TYPE — the classification of the test record.
• TEST_DATE_TXT — the test date held as text.
• PROFILE_CATEGORY_CODE — the profile category, sourced from ACTPR.PROFILE_CATEGORY. This is the column most relevant to a profile_category_code search.
• _LA:PROFILE_CATEGORY and _LA:PROFILE_CATEGORY_DESC — translation placeholders resolved against IGS_LOOKUP_VALUES using the lookup type ACT_PROFILE_CATEGORY, returning the meaning and description for the category.
• CODE1 … CODE7 — flexible attribute columns carried from the profile record.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE — standard Oracle who-columns for audit tracking.

Common Use Cases and Queries

A typical use is to list out-of-class ACT accomplishments for a set of students in a given reporting year, resolving the profile category to its lookup meaning:

SELECT person_identifier, reporting_year, act_identifier,
       profile_category_code, "_LA:PROFILE_CATEGORY"
  FROM apps.igsbv_ad_act_profiles_ooca
 WHERE reporting_year = 2024
   AND profile_category_code = :p_category;

Another frequent scenario filters by person to assemble a complete profile picture for an applicant or enrolled student, using the resolved PERSON_IDENTIFIER as the predicate. Integration extracts may select the CODE1–CODE7 segments together with the translation columns to populate an external student information system, while audit or reconciliation reports use the who-columns to trace record changes. Because the view is read-only and already joins the alternate person identifier, it spares consumers from reproducing the identifier-normalization and lookup logic in ad hoc SQL, though performance-sensitive extracts should confirm indexing on the base objects when filtering by reporting year or profile category.