Search Results igs_ad_appl_pgmapprv_pk

Overview

IGS_AD_APPL_PGMAPPRV is a transactional table in the IGS (Student System) product schema of Oracle E-Business Suite, valid in both 12.1.1 and 12.2.2. It holds the details of program representatives who are assigned to individual admission applications for the purpose of program review. In institutional workflows where an academic program must endorse or evaluate an applicant before a decision is finalized, this table records which approver was nominated against a specific application, how the assignment was created, and the outcome of the review activity.

The documented physical schema for 12.1.1 lists twenty columns, with a single-column primary key, IGS_AD_APPL_PGMAPPRV_PK, defined on APPL_PGMAPPRV_ID. Two foreign key paths are documented: PERSON_ID, ADMISSION_APPL_NUMBER, NOMINATED_COURSE_CD and SEQUENCE_NUMBER collectively point back to IGS_AD_PS_APPL_INST_ALL, and PGM_APPROVER_ID references HZ_PARTIES. Based on the mined foreign key structure, the heuristic Data Vault classification suggested for this object is a link: it resolves a many-to-many association between an admission application and the party acting as program approver, carrying descriptive review attributes alongside the relationship. This classification is a modeling suggestion rather than a delivered EBS construct.

Key Information Stored

The surrogate primary key APPL_PGMAPPRV_ID uniquely identifies each approval assignment row and is the only documented unique key. The business identity of a row is instead formed by the combination of the application reference and the approver: PERSON_ID together with ADMISSION_APPL_NUMBER, NOMINATED_COURSE_CD, and SEQUENCE_NUMBER identifies the application instance, while PGM_APPROVER_ID identifies the reviewing party. Together these are the practical business-key candidates for a natural-key lookup.

• APPL_PGMAPPRV_ID — surrogate primary key for the approval assignment.
• PERSON_ID, ADMISSION_APPL_NUMBER, NOMINATED_COURSE_CD, SEQUENCE_NUMBER — the composite reference to the admission application instance in IGS_AD_PS_APPL_INST_ALL.
• PGM_APPROVER_ID — the program representative (party) assigned to review the application; FK to HZ_PARTIES.
• ASSIGN_TYPE and ASSIGN_DATE — how and when the approver was assigned to the application.
• PROGRAM_APPROVAL_STATUS and PROGRAM_APPROVAL_DATE — the current disposition of the review and when it was reached.
• APPROVAL_NOTES — free-text commentary recorded by or about the approver.
• PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — linkage to the program application context and the program itself, with the last program-side update stamp.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, REQUEST_ID — standard EBS WHO-column audit and concurrent request attribution.

Common Use Cases and Queries

Typical reporting scenarios include identifying approvers with outstanding reviews, measuring turnaround between ASSIGN_DATE and PROGRAM_APPROVAL_DATE, and auditing submissions by program or nominated course. A representative query joins the approver party to the application:

• Pending approvals by program: SELECT a.ADMISSION_APPL_NUMBER, a.PGM_APPROVER_ID, a.ASSIGN_DATE FROM IGS_AD_APPL_PGMAPPRV a WHERE a.PROGRAM_APPROVAL_STATUS = 'PENDING' ORDER BY a.ASSIGN_DATE;
• Approver workload: SELECT PGM_APPROVER_ID, COUNT(*) FROM IGS_AD_APPL_PGMAPPRV GROUP BY PGM_APPROVER_ID;
• Cycle-time analysis: SELECT APPL_PGMAPPRV_ID, PROGRAM_APPROVAL_DATE - ASSIGN_DATE AS DAYS FROM IGS_AD_APPL_PGMAPPRV WHERE PROGRAM_APPROVAL_DATE IS NOT NULL;

Related Objects

• IGS_AD_PS_APPL_INST_ALL — parent application instance; joined via PERSON_ID, ADMISSION_APPL_NUMBER, NOMINATED_COURSE_CD, and SEQUENCE_NUMBER.
• HZ_PARTIES — the trading community party record for the program approver; joined via PGM_APPROVER_ID.
• IGS_AD_APPL_PGMAPPRV_PK — the primary key constraint enforcing uniqueness of APPL_PGMAPPRV_ID.
• Program-side base tables referenced by PROGRAM_ID and PROGRAM_APPLICATION_ID provide the academic program context used in approval reporting.