Search Results pgm_approver_id

Overview

IGS.IGS_AD_APPL_PGMAPPRV is a transactional table within the Oracle E-Business Suite Student Systems (IGS) schema that stores the program representatives assigned to evaluate applications for program review. In the admissions lifecycle, an applicant's program application may require approval by one or more authorized evaluators, and this table records each such assignment along with its evaluation outcome. The table is registered under FND Design Data as IGS.IGS_AD_APPL_PGMAPPRV, is VALID in release 12.1.1 and 12.2.2, and resides in the APPS_TS_TX_DATA tablespace with 10 percent PCTFREE.

From a data warehouse or Data Vault modeling perspective, the object leans toward a link classification. Each row associates (links) an applicant person, a program application context (admission application number, nominated course code, and sequence number), and an evaluator identified by PGM_APPROVER_ID, while carrying descriptive status and date attributes that could be split into a satellite around that link. The presence of many-to-one foreign keys to both the applicant and the approver supports this interpretation.

Key Information Stored

The surrogate primary key is APPL_PGMAPPRV_ID, a system-generated number that uniquely identifies each program-representative assignment record (enforced by IGS_AD_APPL_PGMAPPRV_PK). Business-key candidates appear through the non-unique indexes rather than a single composite unique constraint: IGS_AD_APPL_PGMAPPRV_U2 covers ADMISSION_APPL_NUMBER, NOMINATED_COURSE_CD, PERSON_ID, PGM_APPROVER_ID, and SEQUENCE_NUMBER. Because this index is NONUNIQUE, the combination should be treated as a logical access path rather than a strict uniqueness guarantee.

The most important columns are:

• PGM_APPROVER_ID — stores the evaluator person ID; the search key frequently used to find all assignments for a given approver. Indexed by IGS_AD_APPL_PGMAPPRV_N1 and part of the U2 composite index; foreign key to HZ_PARTIES.
• PERSON_ID — the institution-defined number uniquely identifying the applicant; foreign key to IGS_AD_PS_APPL_INST_ALL.
• ADMISSION_APPL_NUMBER and SEQUENCE_NUMBER — the application number and its sequence, tying the row to a specific admission application instance.
• NOMINATED_COURSE_CD — the program code the applicant is seeking admission to.
• ASSIGN_TYPE — whether the evaluator was assigned manually (M) or automatically (A).
• ASSIGN_DATE — date the application was assigned to the evaluator.
• PROGRAM_APPROVAL_STATUS — Approved, Pending, or Rejected, validated against IGS_LOOKUPS where LOOKUP_TYPE = 'PROGRAM_APPROVAL_STATUS'.
• PROGRAM_APPROVAL_DATE — the evaluation date.
• APPROVAL_NOTES — up to 240 characters of representative comments.
• Standard Who columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, REQUEST_ID) and additional application context (PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE).

Common Use Cases and Queries

Typical reporting needs include identifying evaluators with pending reviews, tracking approval turnaround, and auditing manual versus automatic assignments. A query for a specific approver's outstanding work is common:

• SELECT a.ADMISSION_APPL_NUMBER, a.NOMINATED_COURSE_CD, a.PERSON_ID, a.PROGRAM_APPROVAL_STATUS FROM IGS.IGS_AD_APPL_PGMAPPRV a WHERE a.PGM_APPROVER_ID = :approver_id AND a.PROGRAM_APPROVAL_STATUS = 'PENDING';
• Aggregate approval volumes by status and date range: SELECT PROGRAM_APPROVAL_STATUS, COUNT(*) FROM IGS.IGS_AD_APPL_PGMAPPRV WHERE ASSIGN_DATE BETWEEN :from AND :to GROUP BY PROGRAM_APPROVAL_STATUS;
• Join to HZ_PARTIES on PGM_APPROVER_ID to resolve evaluator names, and to IGS_AD_PS_APPL_INST_ALL on PERSON_ID for applicant detail.

Related Objects

The table participates in two documented foreign-key relationships and several indexed joins:

• IGS.IGS_AD_PS_APPL_INST_ALL — referenced on PERSON_ID; supplies applicant institution data.
• HZ_PARTIES — referenced on PGM_APPROVER_ID; supplies the evaluator party record.
• IGS_AD_APPL_PGMAPPRV_PK / _U1 / _U2 / _N1 — indexes supporting primary-key lookups, application-number joins, and approver queries.
• IGS_LOOKUPS — lookup source for PROGRAM_APPROVAL_STATUS values.

Because the surrogate key and approver identifier drive nearly all access, queries should lead with APPL_PGMAPPRV_ID or PGM_APPROVER_ID to exploit the available indexes.