Search Results igs_he_red_rtn

Overview

IGS_HE_ST_PROG_DTLS_V is a denormalized reporting view owned by the APPS schema within the Oracle E-Business Suite Higher Education (HE) module. It presents detailed attributes of higher education student programme records, exposing the underlying data held in the IGS_HE_ST_PROG entity alongside descriptive values resolved from the higher education code value lookups and the program version definition. The view is used primarily for statutory returns, funding analysis, and integration extracts where programme-level attributes such as teacher training designation, fundability, fee band, franchise arrangements, and student support markers must be reported in a single, readily consumable structure.

The view is particularly relevant to users searching for "oss_fundlev," since it surfaces funding level attributes (LEVEL_APPLICABLE_TO_FUNDING), fundability (FUNDABILITY), fee band (FEE_BAND), implied funding rate (IMPLIED_FUND_RATE), and disadvantage uplift eligibility (DISADV_UPLIFT_ELIG_CD) in one place. These are the fields required for funding-level derivations and returns such as HESA and OFS submissions, making the view a convenient foundation for funding-level (fundlev) logic.

Underlying Base Objects

The view is defined over the base table IGS_HE_ST_PROG (aliased A), which stores higher education student programme records, joined to IGS_PS_VER (aliased B) for the program version and its short title. Lookup descriptions are resolved through repeated joins to IGS_HE_CODE_VALUES, aliased C through X, and to IGS_HE_CODE_VALUES-derived and FND_LOOKUPS-style sources for certain descriptors such as RETURN_TYPE (S) and OTHER_INST_PROV_TEACHING (K, L, which resolve to NAME rather than VALUE_DESCRIPTION).

The joins follow a consistent pattern: each coded column on IGS_HE_ST_PROG is matched to a code value row, and the corresponding VALUE_DESCRIPTION is returned as a *_DESC column. ETRM documentation does not enumerate the underlying tables individually, but the view text confirms that IGS_HE_ST_PROG, IGS_PS_VER, and IGS_HE_CODE_VALUES are the documented base objects.

• IGS_HE_ST_PROG (A) — primary table supplying programme, funding, franchise, and teaching attributes.
• IGS_PS_VER (B) — program version supplying SHORT_TITLE.
• IGS_HE_CODE_VALUES (C–X) — code lookups supplying descriptive text for each coded column.

Key Columns

• HESA_ST_PROG_ID / COURSE_CD / VERSION_NUMBER — primary identifiers linking the record to its programme and version.
• TEACHER_TRAIN_PROG_ID / ITT_PHASE / BILINGUAL_ITT_MARKER — initial teacher training designation and phase, with descriptions.
• TEACHING_QUAL_SOUGHT_SECTOR / SUBJ1–SUBJ3 — teaching qualification sector and subject codes with descriptions.
• LOCATION_OF_STUDY / OTHER_INST_PROV_TEACHING1–2 — study location and partner institution providers.
• PROP_TEACHING_IN_WELSH / PROP_NOT_TAUGHT — proportions of teaching delivered in Welsh and not taught.
• CREDIT_TRANSFER_SCHEME / RETURN_TYPE — credit transfer scheme and return type indicators.
• DEFAULT_AWARD / PROGRAM_CALC / LEVEL_APPLICABLE_TO_FUNDING — award and funding level determination.
• FRANCHISING_ACTIVITY / FRANCH_PARTNER_CD / FRANCH_OUT_ARR_CD — franchise activity and partner arrangements.
• NHS_FUNDING_SOURCE / FE_PROGRAM_MARKER / FEE_BAND / FUNDABILITY — funding source, fee band, and fundability flags.
• FTE_INTENSITY / TEACH_PERIOD_START_DT / TEACH_PERIOD_END_DT — teaching period and intensity.
• IMPLIED_FUND_RATE / GOV_INITIATIVES_CD / DISADV_UPLIFT_ELIG_CD — funding rate and eligibility markers.
• UNITS_FOR_QUAL / EXCLUDE_FLAG — qualification units and exclusion indicator.
• ROW_ID — ROWID of the base IGS_HE_ST_PROG row, useful for updates.

Common Use Cases and Queries

Typical uses include statutory funding returns, funding-level (fundlev) derivations, franchise reporting, and data extracts into external HE systems. The view allows filtering by fundability, fee band, or level applicable to funding without additional lookup joins.

Example query listing funding-level attributes for a programme:

SELECT course_cd
     , version_number
     , short_title
     , fundability_desc
     , fee_band_desc
     , level_appl_to_funding_desc
     , implied_fund_rate
     , teach_period_start_dt
     , teach_period_end_dt
  FROM apps.igs_he_st_prg_dtls_v
 WHERE fundability = '1'
   AND teach_period_start_dt >= TRUNC(SYSDATE)
 ORDER BY course_cd, version_number;

Example query identifying franchise and disadvantage uplift eligible programmes:

SELECT course_cd
     , franch_partner_desc
     , franch_out_arr_desc
     , disadv_uplift_elig_desc
     , exclude_flag
  FROM apps.igs_he_st_prg_dtls_v
 WHERE franchising_activity IS NOT NULL
   AND nvl(exclude_flag,'N') = 'N';

Because descriptions are resolved inside the view, these queries avoid repeated joins to IGS_HE_CODE_VALUES and are suitable for concurrent-program extracts and BI Publisher reports in both 12.1.1 and 12.2.2.