Search Results igs_in_applml_pkgitm_n1

Overview

The IGS_IN_APPLML_PKGITM table is a core data structure within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2, specifically for the IGS (iGrants) module. It functions as a transactional table designed to store detailed records of items included within an enquiry package. An enquiry package is a collection of informational materials or items sent to a prospective applicant in response to their initial enquiry. The table's primary role is to track which specific items (ENQUIRY_PACKAGE_ITEM) were associated with a particular applicant's enquiry (identified by PERSON_ID and ENQUIRY_APPL_NUMBER), including the mailing status and date. Notably, the provided documentation explicitly marks this table as "Obsolete," indicating it is part of a legacy process that may have been superseded in later application flows, though it remains present and valid in the schema.

Key Information Stored

The table's columns capture the core business data and standard auditing information. The primary business key is a composite of PERSON_ID, ENQUIRY_APPL_NUMBER, and ENQUIRY_PACKAGE_ITEM, which uniquely identifies a single package item for an applicant's enquiry. The ENQUIRY_PACKAGE_ITEM column (VARCHAR2(10)) holds the code for the mailed item. Critical status columns include MAILED_DT, which records the date the item was physically dispatched, and DONOT_MAIL_IND, a flag that can be set to prevent mailing for a specific item. The table is fully audited with standard "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).

Common Use Cases and Queries

This table supports operational reporting and audit trails related to applicant communication and fulfillment processes. Common use cases include generating reports of all items mailed to applicants within a specific date range, identifying items flagged to not be mailed, and reconciling package fulfillment. A typical query to retrieve package details for a specific applicant would leverage the primary key columns. For reporting purposes, this table is often joined to person and enquiry header tables.

• Sample Query - Retrieve Mailed Package Items for an Applicant:
  SELECT enquiry_package_item, mailed_dt, donot_mail_ind
  FROM igs.igs_in_applml_pkgitm
  WHERE person_id = :p_person_id
  AND enquiry_appl_number = :p_enquiry_number
  ORDER BY mailed_dt DESC;
• Sample Query - Audit Items Mailed in a Period:
  SELECT person_id, enquiry_appl_number, enquiry_package_item, mailed_dt, created_by
  FROM igs.igs_in_applml_pkgitm
  WHERE mailed_dt BETWEEN :p_start_date AND :p_end_date
  AND donot_mail_ind IS NULL;

Related Objects

The table's integrity is enforced through its primary key constraint (IGS_IN_APPLML_PKGITM_PK) on the composite of PERSON_ID, ENQUIRY_APPL_NUMBER, and ENQUIRY_PACKAGE_ITEM. It maintains referential integrity through foreign key relationships. Based on the provided metadata, the ENQUIRY_PACKAGE_ITEM column references the IGS_IN_ENQ_PKG_ITEM table. This relationship validates that the item code stored in IGS_IN_APPLML_PKGITM exists as a valid, predefined item in the IGS_IN_ENQ_PKG_ITEM reference table. The unique index (IGS_IN_APPLML_PKGITM_U1) supports the primary key, and a non-unique index (IGS_IN_APPLML_PKGITM_N1) on ENQUIRY_PACKAGE_ITEM facilitates efficient joins to its parent reference table.