Results for “igs_co_itm_all”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The table IGS_CO_ITM_ALL belongs to the IGS – Student System product family within Oracle E-Business Suite (EBS) and describes correspondence items sent to students. It functions as the transactional record-set for outbound student correspondence generated by the Student System, capturing the correspondence type, a caller-supplied reference number, scheduling metadata, and links to the originating person, course, unit, and calendar instance. The object exists in both EBS 12.1.1 and 12.2.2 schemas under the IGS owner and documents 27 physical columns.

The ETRM documentation states that the object is not implemented in this database. In the context of the reference documentation this indicates that the table is part of the delivered IGS schema definition but is not instantiated in the specific database instance from which the metadata was extracted. Consumers should verify physical existence by querying ALL_TABLES before depending on it. The product itself is marked obsolete, so the table should be treated as legacy.

From a Data Vault modelling perspective, the mined relationship structure suggests a satellite-leaning classification. The primary key is the business key CORI_UK, defined on REFERENCE_NUMBER, and the table carries descriptive attributes plus references to several hub-like entities (HZ_PARTIES, IGS_PS_VER_ALL, IGS_PS_UNIT_VER_ALL, IGS_CA_INST_ALL). This structure is characteristic of a satellite attached to a correspondence hub, with a link to the originator party.

Key Information Stored

The 27 columns fall into three functional groups. The following are the most significant for query and integration purposes:

• REFERENCE_NUMBER — the business key. It is the sole unique index column in IGS_CO_ITM_U2 and forms part of the composite unique index IGS_CO_ITM_U1 together with CORRESPONDENCE_TYPE.
• CORRESPONDENCE_TYPE — the correspondence category. It is a foreign key to IGS_CO_TYPE and also participates in the composite unique index with REFERENCE_NUMBER.
• ORIGINATOR_PERSON_ID — foreign key to HZ_PARTIES, identifying the person or party from whom the correspondence originated.
• COURSE_CD and CV_VERSION_NUMBER — foreign key pair referencing IGS_PS_VER_ALL, identifying the program version associated with the item.
• UNIT_CD and UV_VERSION_NUMBER — foreign key pair referencing IGS_PS_UNIT_VER_ALL, identifying the unit version.
• CAL_TYPE and CI_SEQUENCE_NUMBER — foreign key pair referencing IGS_CA_INST_ALL, identifying the calendar instance context.
• REQUEST_NUM, REQUEST_JOB_ID, JOB_REQUEST_ID, and REQUEST_JOB_RUN_ID — concurrency and job-scheduling identifiers, supporting batch generation of correspondence.
• COMMENTS — free-form descriptive text for the correspondence item.
• The standard WHO audit columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) and concurrent program columns (REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE) are present and should be included in any audit or troubleshooting query.

Common Use Cases and Queries

Reporting on correspondence items typically joins the table to its referenced dimensions to resolve human-readable descriptions. A representative query is:

SELECT i.reference_number,
       ct.description        AS correspondence_type_desc,
       i.course_cd,
       i.unit_cd,
       i.originator_person_id,
       i.creation_date
FROM   igs_co_itm_all i,
       igs_co_type     ct,
       igs_ca_inst_all ca
WHERE  i.correspondence_type = ct.correspondence_type
AND    i.cal_type            = ca.cal_type
AND    i.creation_date      >= :p_from_date;

Operational scenarios include tracing which correspondence was generated for a given student party (ORIGINATOR_PERSON_ID), reconciling batch correspondence runs through the REQUEST_ID or JOB_REQUEST_ID columns, and identifying all items linked to a specific program or unit version. Because the composite unique index IGS_CO_ITM_U1 spans CORRESPONDENCE_TYPE and REFERENCE_NUMBER, queries filtering on both columns are most selective and should be preferred over single-column filters on REFERENCE_NUMBER alone.

Related Objects

• IGS_CO_TYPE — referenced via CORRESPONDENCE_TYPE; provides the correspondence type description.
• IGS_CA_INST_ALL — referenced via CAL_TYPE and CI_SEQUENCE_NUMBER; provides calendar instance context.
• IGS_PS_VER_ALL — referenced via COURSE_CD and CV_VERSION_NUMBER; the program version dimension.
• IGS_PS_UNIT_VER_ALL — referenced via UNIT_CD and UV_VERSION_NUMBER; the unit version dimension.
• HZ_PARTIES — referenced via ORIGINATOR_PERSON_ID; the originating party.
• IGS_CO_OU_CO_ALL — the child table that references back through CORRESPONDENCE_TYPE and REFERENCE_NUMBER, forming the correspondence-to-organization composition.