Search Results correspondence_cat




Overview
Explore in AI Chat

Overview

The IGS_CO_CAT_ITM table is a core data structure within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2, specifically for the Student Management (Campus Solutions) product family under the IGS (iGrants) schema. This table functions as a mapping or junction table, defining the relationship between correspondence categories and correspondence types. Its primary role is to administratively control which specific correspondence items (types) are included in or excluded from a broader correspondence category. This configuration is essential for managing the bulk generation and distribution of both system-generated and manual correspondence to students, ensuring recipients receive appropriate and relevant communications based on institutional rules.

Key Information Stored

The table's columns store the configuration and lifecycle data for the category-to-item relationships. The mandatory composite key (CORRESPONDENCE_CAT, CORRESPONDENCE_TYPE, CREATE_DT) uniquely identifies a specific item's inclusion in a category. The CORRESPONDENCE_CAT and CORRESPONDENCE_TYPE columns hold institution-defined codes linking to their respective master definitions. Critical operational columns include CREATE_DT, which records the inclusion date, and LOGICAL_DELETE_DT, which supports soft deletion by marking when an item was removed from the category. The BLOCK_WHEN_ENCUMBERED_IND flag is a significant business rule controller, determining if correspondence generation should be halted if the recipient student has specific system holds. Standard EBS "Who" columns (CREATED_BY, CREATION_DATE, etc.) track audit information.

Common Use Cases and Queries

This table is central to correspondence management setups and audits. A common use case is generating a report of all active correspondence items within a category for a given date, which is vital for troubleshooting communication workflows or validating setup. Administrators frequently query the table to understand the history of changes to a category's composition. Sample SQL patterns include fetching all active items for a category and identifying items blocked by student encumbrances.

  • Active Items in a Category: SELECT correspondence_type, create_dt FROM igs.igs_co_cat_itm WHERE correspondence_cat = 'ADM_LETTER' AND logical_delete_dt IS NULL ORDER BY create_dt;
  • Audit History for an Item: SELECT * FROM igs.igs_co_cat_itm WHERE correspondence_cat = 'FEE_INV' AND correspondence_type = 'INV_MAIN' ORDER BY create_dt;
  • Items with Encumbrance Blocks: SELECT correspondence_cat, correspondence_type FROM igs.igs_co_cat_itm WHERE block_when_encumbered_ind = 'Y' AND logical_delete_dt IS NULL;

Related Objects

The IGS_CO_CAT_ITM table does not exist in isolation; it is a child table in a hierarchy of correspondence configuration objects. It directly references master definitions via its key columns. The unique index IGS_CO_CAT_ITM_U1 enforces data integrity for the relationship. This table is almost certainly referenced by core correspondence generation engines and APIs within the IGS module to determine the list of items to process for a given category. Related objects would include the master tables for CORRESPONDENCE_CAT and CORRESPONDENCE_TYPE (e.g., IGS_CO_CATEGORY, IGS_CO_TYPE), and likely parent tables defining the category's broader rules, as well as student correspondence history or queue tables that consume this setup data.