Search Results igs_ad_api_int_s

Overview

The APPS.IGS_AD_ACT_ASSESSMENTS_PKG package body implements the Import ACT Assessment Details Process within Oracle E-Business Suite. Its purpose is to load external ACT (American College Testing) assessment data — test scores, statistics, and related person identifiers — into the EBS admissions interface tables so that the data can be validated and subsequently transferred into the institution's student records. The package is part of the Oracle Student System (formerly Oracle Admissions/Recruitment) data migration and third-party test score integration framework.

The header identifies several historical defect corrections, including support for non-US country records (bug 4085289), correction of the planned completion date (bug 4252413), and handling of unexpected characters found in the National/Local Norm columns of IGS_AD_ACT_STATISTICS (bug 4693325). These corrections indicate the package processes ACT statistics with locale-sensitive and free-form text columns that require defensive parsing. The package is classified as OTHER (not a public API) in the ETRM metadata for 12.1.1, and is not referenced by any other package, confirming it is a standalone loader invoked directly rather than a shared library routine.

Key Procedures and Functions

Three documented program units make up the package:

• GET_BATCH_ID — A function that returns a NUMBER batch identifier. On first invocation it selects the next value from the sequence IGS_AD_ACT_ASSESSMENTS_S and caches it in the package-level variable batch_id; subsequent calls within the same session return the cached value. This mechanism stamps every row loaded by the SQL*Loader process with a common batch identifier, enabling grouped processing and rollback of a single import run. The function is explicitly referenced in the source as supporting the SQL*Loader process writing to the assessment table.
• CHECK_SETUPS — A validation routine that verifies required system setup before the import proceeds. Based on the package's global variables, it confirms the presence of the SSN and ACT person identifier types in IGS_PE_PERSON_ID_TYP and resolves the code identifiers for score source, transcript source, grading scale, and unit difficulty from IGS_AD_CODE_CLASSES. Failure of these checks indicates missing lookup or code class configuration in the target environment.
• INSERT_ACT_TO_INTERFACE — The principal load routine. It maps incoming ACT assessment and statistics records into the admissions interface tables, applying code lookups and performing the data conversions required by the known bug fixes noted in the header. It is the entry point for the actual data transformation and insert activity.

The package also contains a private helper, GET_LOOKUP_MEANING, which resolves a lookup code to its meaning from IGS_LOOKUP_VALUES, and a set of global variables (G_SSN_Person_Id_Type, G_ACT_Person_Id_Type, G_Score_Source_id, G_Transcript_Source, G_Grading_Scale, G_Unit_Difficulty) used to cache setup lookups for the duration of the session.

Tables Accessed

The package reads and writes admissions interface tables through APPS synonyms. The interface tables — IGS_AD_ACT_ASSESSMENTS and its sequence IGS_AD_ACT_ASSESSMENTS_S, IGS_AD_ACT_STATISTICS, IGS_AD_ADDRUSAGE_INT_ALL, IGS_AD_ACADHIS_INT_ALL and IGS_AD_ACADHIS_INT_S, IGS_AD_ADDR_INT_ALL and IGS_AD_ADDR_INT_S, IGS_AD_API_INT_ALL and IGS_AD_API_INT_S, and IGS_AD_CONTACTS_INT_ALL and IGS_AD_CONTACTS_INT_S — receive the staged assessment, address usage, academic history, address, applicant, and contact data. The setup and reference tables IGS_AD_CODE_CLASSES and IGS_PE_PERSON_ID_TYP are read to resolve code identifiers and person identifier types needed to translate the incoming ACT values into valid EBS codes.

Usage Notes

This package is not a general-purpose public API and is not called by other database packages. It is designed to be invoked in the context of the Import ACT Assessment Details concurrent program, working in conjunction with an external SQL*Loader control file that populates a staging area. GET_BATCH_ID is called by the loader to tag rows with a consistent batch identifier, while CHECK_SETUPS and INSERT_ACT_TO_INTERFACE perform setup validation and the interface insert respectively.

The code_classes search term maps directly to the package's dependency on IGS_AD_CODE_CLASSES, which supplies the code identifiers for score source, transcript source, grading scale, and unit difficulty. Administrators must ensure the relevant code classes and lookup values are configured before running the import; otherwise CHECK_SETUPS will report missing setup. Because the package was last updated in release 12.0.x-era source (header revision 120.8, 2006), it remains compatible with the 12.1.1 and 12.2.2 file systems, but any customization should be applied through a copy or wrapper rather than direct modification of the seeded package body.