Search Results get_program_completion_dt

Overview

IGS_AD_OUTCOME_LETTERS_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the Student Systems / Admissions (IGS) product family. It presents a denormalized, read-only projection that combines trading community party and address attributes with applicant admission and outcome data, so that outcome letters, admission decisions, and program completion communications can be produced without joining the underlying HZ and IGS tables directly. The view is intended for reporting, letter generation, and integration extracts rather than for transactional maintenance. Its name and column set indicate that it is scoped to admissions outcome processing, including expected completion information relevant to the "get_program_completion_dt" search that commonly leads users to this object. Because it is a view, no data is stored within it; all values are derived at runtime from the referenced base objects.

Underlying Base Objects

The ETRM documentation for release 12.2.2 records the owner and view text but documents no base objects explicitly. The view text itself, however, shows that the projection is defined over at least three source aliases: hz, representing the trading community party/address layer (columns such as party_id, party_name, party_number, person_first_name, address1, postal_code, and country are all drawn from this alias); ps, supplying program and course attributes including course_cd, title, responsible_org_unit_cd, responsible_ou_start_dt, and course_type; and acaiv, supplying applicant admission and outcome attributes including preference_number, location_cd, attendance_mode, attendance_type, adm_outcome_status, entry_status, entry_level, sch_apl_to_id, school_applying_to, expected_completion_yr, expected_completion_perd, and edu_goal_prior_enroll. Additional aliases ou and loc and am contribute descriptive lookups for organizational unit, location, and attendance mode. The literal TO_DATE(null) wh_update_date indicates a placeholder column retained for warehouse or interface compatibility rather than a genuine stored value.

Key Columns

The view exposes three logical groups of columns. Party and contact columns include party_id, party_name, party_number, party_type, person_first_name, person_last_name, person_identifier, email_address, and the full address block (address1 through address4, city, state, province, postal_code, country). Communication and demographic columns include salutation, known_as fields, language_name, do_not_mail_flag, and status. Admissions outcome columns include adm_outcome_status, entry_status, entry_status_name, entry_level, entry_level_name, school_applying_to and its description, preference_number, and the expected completion pair expected_completion_yr and expected_completion_perd. Program columns include course_cd, title, course_type, responsible_org_unit_cd, and responsible_ou_start_dt. Because the view carries expected completion year and period but no directly named completion date column, consumers seeking a program completion date typically derive it from expected_completion_yr and expected_completion_perd, or resolve it through the underlying admission application and program completion entities referenced by sch_apl_to_id.

Common Use Cases and Queries

Typical usage includes generating admissions outcome letters, extracting applicant cohorts for a given school or program, and reporting expected completion timing for enrolled applicants. A representative query selecting completion-related attributes is shown below.

• SELECT party_id, party_name, course_cd, title, adm_outcome_status, entry_status, expected_completion_yr, expected_completion_perd FROM apps.igs_ad_outcome_letters_v WHERE school_applying_to = :p_school AND adm_outcome_status = :p_status;
• SELECT party_number, person_first_name, person_last_name, email_address, postal_code, preference_number FROM apps.igs_ad_outcome_letters_v WHERE do_not_mail_flag = 'N' AND course_cd = :p_course;
• SELECT course_cd, expected_completion_yr, COUNT(*) FROM apps.igs_ad_outcome_letters_v GROUP BY course_cd, expected_completion_yr ORDER BY course_cd;

Because the view performs multiple joins across party, program, and admission tables, queries should filter on indexed identifiers such as party_id, party_number, course_cd, or sch_apl_to_id to avoid full scans. Access is read-only and should be granted through the APPS schema or a custom synonym rather than against the base tables directly.