Search Results igs_ad_app_req_n1

Overview

The IGS.IGS_AD_APP_REQ table is a core data object within the Oracle EBS Student System (formerly IGS/IGS) schema, specifically designed to manage the financial and application requirements associated with a student's admission applications. It acts as a transactional bridge linking an applicant’s admission application record to their fee-related obligations, statuses, and payment details. In the context of Oracle EBS Releases 12.1.1 and 12.2.2, this table supports the admissions lifecycle by storing per-application fee liabilities, payment methods, and the applicant’s fee status (e.g., domestic or international fee assessment). It is a child table of the primary admission application record and is essential for generating financial reports, fee assessment statements, and compliance audits related to student admissions.

Key Information Stored

The table’s most critical column is APP_REQ_ID (NUMBER, 15), a unique identifier for each applicant’s application requirements record. This primary key is used for indexing and fast data retrieval. Other pivotal columns include:

• PERSON_ID (NUMBER, 15) and ADMISSION_APPL_NUMBER (NUMBER): Together, these form the natural key (ADAR_UK), linking a specific person to a specific admission application.
• SEQUENCE_NUMBER (NUMBER): Identifies the application sequence, useful when an applicant submits multiple applications.
• APPLICANT_FEE_TYPE (NUMBER, 15): Stores a reference to the fee category selected by the applicant (e.g., HECS, GSF, MEDIBANK). This drives fee liability calculations.
• APPLICANT_FEE_STATUS (NUMBER, 15): Indicates the applicant’s fee assessment status (e.g., domestic, international, scholarship-exempt).
• FEE_DATE (DATE): The date on which the fee payment was recorded.
• FEE_PAYMENT_METHOD (NUMBER, 15): Stores the payment method (e.g., credit card, bank transfer, cheque).
• FEE_AMOUNT (NUMBER): The monetary amount associated with the fee requirement.
• REFERENCE_NUM (VARCHAR2, 60): A manually recorded transaction reference number for audit or reconciliation purposes.
• Standard Who Columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN): Standard Oracle EBS audit columns tracking record creation and modification.

Note that NOMINATED_COURSE_CD is documented as OBSOLETE and should not be used in new development.

Common Use Cases and Queries

The table is typically used in admissions, finance, and reporting modules. Common scenarios include:

• Fee Status Reporting: Producing reports of applicant fee statuses (e.g., fee-paying domestic vs. international) by joining with IGS_AD_CODE_CLASSES on APPLICANT_FEE_STATUS.
• Application Fee Reconciliation: Matching records in IGS_AD_APP_REQ with receipt data in financial tables using FEE_DATE and FEE_AMOUNT.
• Per-Application Requirement Tracking: Listing all fee requirements for a given admission application.

Sample SQL Pattern – Fee summary by application:

SELECT
  aar.person_id,
  aar.admission_appl_number,
  aar.applicant_fee_status,
  ac.code_value AS fee_status_display,
  aar.fee_amount
FROM igs.igs_ad_app_req aar
JOIN igs.igs_ad_code_classes ac
  ON aar.applicant_fee_status = ac.code_class_id
WHERE aar.person_id = :p_person_id
ORDER BY aar.creation_date;

Sample SQL Pattern – Applications with pending fee status:

SELECT
  aar.app_req_id,
  aar.person_id,
  aar.admission_appl_number,
  aar.fee_date,
  aar.fee_amount
FROM igs.igs_ad_app_req aar
WHERE aar.applicant_fee_status IS NOT NULL
  AND aar.fee_date IS NULL;

Related Objects

Based on the documented foreign key and index relationships, the following tables are directly linked to IGS_AD_APP_REQ:

• IGS.IGS_AD_APPL_ALL – Primary admission application table. Linked via PERSON_ID and ADMISSION_APPL_NUMBER (foreign key). Each IGS_AD_APP_REQ record belongs to one application.
• IGS.IGS_AD_CODE_CLASSES – Lookup table for code values. Linked via APPLICANT_FEE_STATUS and APPLICANT_FEE_TYPE. Stores display values for fee status and fee type codes.
• IGS.IGS_FI_ACC_ALL – Financial account table. Linked via REV_ACCOUNT_CD and CASH_ACCOUNT_CD. Used for revenue and cash accounting integration.
• Indexes: The table has four non-unique indexes (IGS_AD_APP_REQ_N1 through N4) on PERSON_ID + ADMISSION_APPL_NUMBER, APPLICANT_FEE_STATUS, REV_ACCOUNT_CD, and CASH_ACCOUNT_CD. The primary key index IGS_AD_APP_REQ_PK is on APP_REQ_ID.

From a referencing perspective, this table is also likely consumed by Oracle EBS API packages such as IGS_AD_PKG (for processing application requirements) and reports like IGS_FI_FEE_STATUS_RPT. The column REFERENCE_NUM supports integration with external payment gateways or manual data entry.