Search Results igr_i_a_pkgitm_n1

Overview

The IGS.IGR_I_A_PKGITM table is a core data structure within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2, specifically in the IGS (iGrants) product family, which is often associated with student systems and grants management. Its primary function is to store transactional details related to enquiry packages. An enquiry package typically represents a collection of information items or materials sent to or intended for a prospective applicant or student in response to an enquiry. This table acts as a junction or intersection table, linking a specific person and their enquiry application to individual package items, thereby tracking the distribution and mailing status of each component within a package.

Key Information Stored

The table's columns can be categorized into key identifiers, business process data, and standard EBS audit columns. The composite primary key (PERSON_ID, ENQUIRY_APPL_NUMBER, PACKAGE_ITEM_ID) uniquely identifies a single package item for a specific person's enquiry. The PACKAGE_ITEM_ID is a foreign key linking to the master definition of the package item. Critical business columns include MAILED_DT, which records the actual date an item was dispatched, and DONOT_MAIL_IND, a VARCHAR2 flag central to controlling the mailing process. When set, this indicator prevents the associated package item from being mailed to the person, a crucial rule for compliance and communication preferences. The table also includes standard EBS "Who" columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) and concurrent program tracking columns (REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) for integration and audit purposes.

Common Use Cases and Queries

A primary use case is managing communication workflows for enquiries. The DONOT_MAIL_IND column is pivotal for generating mailing lists; items where this indicator is set (e.g., to 'Y') are excluded from batch mail runs. Reporting on package fulfillment status is another common scenario. Sample queries include identifying items that have not been mailed for a specific enquiry, checking for "Do Not Mail" overrides, and auditing update history. For example, to find all package items for an enquiry that are eligible for mailing (i.e., not mailed yet and not flagged to be withheld), one might use:

• SELECT * FROM igs.igr_i_a_pkgitm WHERE enquiry_appl_number = :p_appl_num AND mailed_dt IS NULL AND (donot_mail_ind IS NULL OR donot_mail_ind != 'Y');

Another critical query pattern involves joining to the referenced package item master table (via PACKAGE_ITEM_ID) to report on item descriptions alongside their mailing status.

Related Objects

Based on the provided metadata, IGR_I_A_PKGITM has defined relationships with other tables, though the specific target tables are indicated generically. The foreign key relationships are:

• The PACKAGE_ITEM_ID column references a primary key in a separate Package Item master table (likely named IGR_PACKAGE_ITEM or similar within the IGS schema), which holds descriptive information about the item being mailed.
• The PERSON_ID column references a primary key in a Person table (such as IGS_PE_PERSON or a similar base person table), identifying the individual associated with the enquiry.

The table itself is referenced by an APPS synonym (IGR_I_A_PKGITM), which is the standard access point for EBS applications and custom code. Its primary key index is IGR_I_A_PKGITM_PK, and it has a non-unique index (IGR_I_A_PKGITM_N1) on PACKAGE_ITEM_ID to optimize joins to the master item table.