Search Results unit_seq_num




Overview
Explore in AI Chat

Overview

The IGS_DA_OUT_UNT_CAT table is a data storage object within the Oracle E-Business Suite (EBS) 12.1.1/12.2.2, owned by the IGS (iGrants) schema. Its primary function is to store outgoing unit categories associated with student academic units identified during a specific data request or batch process. The table's documented purpose is to categorize units found for students selected in a request, linking them to either advanced standing (AS) or enrollment (EN) data. Notably, the ETRM documentation explicitly marks this table as "Obsolete," indicating it is part of a legacy or deprecated data model within the application. It resides in the APPS_TS_INTERFACE tablespace, which is typically used for transient or interface data.

Key Information Stored

The table's structure is designed to uniquely categorize units per student per batch request. The key columns that define its business logic and relationships are:

  • BATCH_ID: A mandatory NUMBER column that identifies the parent data request, grouping all related records.
  • PERSON_ID: A mandatory NUMBER(15) column that uniquely identifies the student within the request.
  • UNIT_SEQ_NUM: A mandatory NUMBER column that serves as a critical foreign key reference. It points to the sequence number of the specific unit record in either the IGS_DA_OUT_UNT_AS (Advanced Standing) or IGS_DA_OUT_UNT_EN (Enrollment) tables, as determined by the OSS_UNIT_LOCATOR.
  • OSS_UNIT_LOCATOR: A mandatory VARCHAR2 column that specifies the subsystem origin of the linked unit data. It accepts two values: 'AS' for Advanced Standing or 'EN' for Enrollment.
  • UNIT_CATEGORY: A mandatory VARCHAR2(10) column that stores the user-defined category code assigned to the unit for the student in this context.
The combination of BATCH_ID, PERSON_ID, UNIT_SEQ_NUM, OSS_UNIT_LOCATOR, and UNIT_CATEGORY forms the table's primary key (IGS_DA_OUT_UNT_CAT_PK), ensuring uniqueness. Standard EBS "Who" columns (CREATION_DATE, CREATED_BY, etc.) are also present for auditing.

Common Use Cases and Queries

Given its obsolete status, primary use cases involve historical data reporting, data migration validation, or supporting legacy interfaces. A common query would retrieve all unit category assignments for a specific batch process to audit or reconcile data. The UNIT_SEQ_NUM is central for joining to the detailed unit records.

Sample Query: Retrieve all categorized units for a specific batch:
SELECT cat.BATCH_ID, cat.PERSON_ID, cat.UNIT_SEQ_NUM, cat.OSS_UNIT_LOCATOR, cat.UNIT_CATEGORY
FROM IGS.IGS_DA_OUT_UNT_CAT cat
WHERE cat.BATCH_ID = :p_batch_id
ORDER BY cat.PERSON_ID, cat.UNIT_SEQ_NUM;

Sample Query: Join to hypothetical unit details using UNIT_SEQ_NUM and OSS_UNIT_LOCATOR:
SELECT cat.*,
CASE cat.OSS_UNIT_LOCATOR
WHEN 'AS' THEN as_tbl.UNIT_CODE
WHEN 'EN' THEN en_tbl.UNIT_CODE
END AS UNIT_CODE
FROM IGS.IGS_DA_OUT_UNT_CAT cat
LEFT JOIN IGS.IGS_DA_OUT_UNT_AS as_tbl ON cat.UNIT_SEQ_NUM = as_tbl.UNIT_SEQ_NUM AND cat.OSS_UNIT_LOCATOR = 'AS'
LEFT JOIN IGS.IGS_DA_OUT_UNT_EN en_tbl ON cat.UNIT_SEQ_NUM = en_tbl.UNIT_SEQ_NUM AND cat.OSS_UNIT_LOCATOR = 'EN'
WHERE cat.BATCH_ID = :p_batch_id;

Related Objects

Based on the provided metadata, the table's relationships are defined by its foreign key dependencies, which are implicit in the column definitions rather than explicitly declared constraints. The UNIT_SEQ_NUM and OSS_UNIT_LOCATOR columns form a logical foreign key to one of two tables:

  • IGS_DA_OUT_UNT_AS: When OSS_UNIT_LOCATOR = 'AS', the UNIT_SEQ_NUM references a sequence number in this Advanced Standing table.
  • IGS_DA_OUT_UNT_EN: When OSS_UNIT_LOCATOR = 'EN', the UNIT_SEQ_NUM references a sequence number in this Enrollment table.
The table itself is referenced by an APPS synonym named IGS_DA_OUT_UNT_CAT, which facilitates access from other EBS schemas. No other referencing objects are listed in the provided dependency information.