Search Results program_approval_status

Overview

IGS_AD_APPL_PGMAPPRV_V is an Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 reporting view owned by the APPS schema. It resides in the Student System (IGS) product family and presents program-level approval information recorded against admission applications. Each row represents a single approver assignment and approval outcome for a nominated course or program within a given admission application number. The view surfaces the numeric approval status code alongside its translatable lookup meaning, making it directly consumable by reports, concurrent programs, Oracle Forms LOVs, and integration extracts without requiring the consumer to join the lookup table separately.

The view is a denormalized, read-only projection intended to support queries filtered on program_approval_status, which is the search term most commonly associated with this object. Because it pre-joins the lookup meaning and the approver's person details, it is well suited to operational reporting on the admissions approval workflow.

Underlying Base Objects

The view is defined over three database objects:

• IGS_AD_APPL_PGMAPPRV AP — the primary base table holding the program approval records: approver assignments, approval dates, approval status codes, and notes.
• IGS_LOOKUP_VALUES L — joined on AP.PROGRAM_APPROVAL_STATUS = L.LOOKUP_CODE restricted to L.LOOKUP_TYPE = 'PROGRAM_APPROVAL_STATUS', supplying the decoded status meaning.
• IGS_PE_PERSON_BASE_V P — joined on AP.PGM_APPROVER_ID = P.PERSON_ID, supplying the program approver's full name and person number.

The join to IGS_LOOKUP_VALUES is an inner join, so any approval row carrying a status code with no corresponding lookup entry — or a mismatched lookup type — is silently excluded from the result set. Consumers relying on a complete row count should therefore query the base table directly.

Key Columns

• ROW_ID — the ROWID of the base table row; useful for identifying or updating the underlying record.
• APPL_PGMAPPRV_ID — unique identifier of the program approval record.
• ADMISSION_APPL_NUMBER / PROGRAM_APPLICATION_ID / PROGRAM_ID — the admission application and program context to which the approval belongs.
• PERSON_ID / PGM_APPROVER_ID / FULL_NAME / PERSON_NUMBER — the applicant and the assigned program approver.
• NOMINATED_COURSE_CD / SEQUENCE_NUMBER — the nominated course and ordering sequence of the approval entry.
• ASSIGN_TYPE / ASSIGN_DATE — how and when the approval responsibility was assigned.
• PROGRAM_APPROVAL_DATE — the date the program approval decision was recorded.
• PROGRAM_APPROVAL_STATUS — the coded status value; the primary filter column for approval-state queries.
• MEANING — the decoded, user-facing description of the status (for example Approved, Rejected, Pending).
• APPROVAL_NOTES — free-text comments entered by the approver.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, REQUEST_ID — standard EBS audit and concurrent request columns.
• PROGRAM_UPDATE_DATE — program-specific last-modified timestamp.

Common Use Cases and Queries

The view is typically used to report on the state of program approvals across admission applications, to drive inquiry screens, and to feed downstream integrations that must react to approval decisions.

List all approvals for a given application:

SELECT admission_appl_number, nominated_course_cd, full_name,
       program_approval_status, meaning, program_approval_date
FROM   apps.igs_ad_appl_pgmapprv_v
WHERE  admission_appl_number = :p_appl_number
ORDER  BY sequence_number;

Count approvals by status:

SELECT meaning, COUNT(*)
FROM   apps.igs_ad_appl_pgmapprv_v
GROUP  BY meaning;

Retrieve approvers with pending decisions:

SELECT p.pgm_approver_id, p.full_name, COUNT(*) pending_cnt
FROM   apps.igs_ad_appl_pgmapprv_v p
WHERE  p.program_approval_status = 'PENDING'
GROUP  BY p.pgm_approver_id, p.full_name;

Because the view already supplies the status meaning and approver name, these queries avoid additional lookups and are efficient for ad-hoc and scheduled reporting.