Search Results igsfv_program_uk_statistics

Overview

The view IGSFV_PROGRAM_UK_STATISTICS belongs to the IGS — Student System (Obsolete) product family within Oracle E-Business Suite. Its documented purpose is to present a Full View for UK Statistics Program Details, exposing the program-level attributes required for United Kingdom statutory higher-education statistical returns. In practice, this view aggregates program definition data and decodes the associated HESA (Higher Education Statistics Agency) reference codes into human-readable descriptions, functioning as one of the reporting surfaces beneath the UK statistics aggregation layer.

Because the object is annotated as obsolete, it is relevant primarily to legacy EBS 12.1.1 and 12.2.2 installations that still reference the historical IGS UK statistics schema. The ETRM metadata explicitly records Not implemented in this database for the implementation/DBA data, indicating that the view is documented for reference but not necessarily instantiated in the deployment catalogued.

Underlying Base Objects

The ETRM metadata records no documented referenced base objects for this view. The view definition itself, however, is supplied in the documentation and reveals the principal driver: the program table aliased as PRG, joined logically to a program-title source aliased PS. Every coded attribute carried on PRG is decoded by a correlated scalar subquery against IGS_HE_CODE_VALUES.

• PRG — the primary source alias supplying program identifiers, version, return type, funding and fee attributes.
• PS — supplies TITLE, SHORT_TITLE, and ABBREVIATION.
• IGS_HE_CODE_VALUES — the HESA code-description reference table, queried repeatedly via CODE_TYPE and VALUE.
• IGS_LOOKUP_VALUES — referenced through the flexfield-style hint _LA:PRG.RETURN_TYPE:IGS_LOOKUP_VALUES:IGS_HE_RED_RTN:MEANING for the HESA return type description.

Key Columns

The view exposes both raw coded values and their decoded descriptions. Columns include:

• HESA_STATS_PROGRAM_ID (from PRG.HESA_ST_PROG_ID), PROGRAM_CODE, and PROGRAM_VERSION_NUMBER — program identifiers.
• PROGRAM_TITLE, PROGRAM_SHORT_TITLE, PROGRAM_ABBREVIATION — descriptive program names.
• FE_PROGRAM_MARKER with FE_PROGRAM_MARKER_DESC, decoded using CODE_TYPE = 'OSS_FEPRMK'.
• HESA_RETURN_TYPE and the derived HESA_RETURN_TYPE_DESC.
• LOCATION_OF_STUDY with LOCATION_OF_STUDY_DESC (CODE_TYPE = 'OSS_LOCSDY').
• CREDIT_TRANSFER_SCHEME with CREDIT_TRANSFER_SCHEME_DESC (CODE_TYPE = 'OSS_CRDTSCM').
• LEVEL_APP_TO_FUNDING_COUNCIL with LEVEL_APP_FUNDING_COUNCIL_DESC (CODE_TYPE = 'OSS_FUNDLEV').
• FUNDABILITY_CODE with FUNDABILITY_DESC (CODE_TYPE = 'OSS_FUND_CODE').
• FEE_BAND with FEE_BAND_DESC (CODE_TYPE = 'OSS_FEEBAND').
• SOURCE_OF_DEPT_HEALTH_FUNDING with its description (CODE_TYPE = 'OSS_NHS_FUSRC').
• FRANCHISING_ACTIVITY with its description (CODE_TYPE = 'OSS_FRAN_ACT').

Common Use Cases and Queries

The view supports HESA return preparation, funding analysis, and reconciliation of coded program attributes. A typical query retrieves decoded descriptions alongside program identifiers:

SELECT HESA_STATS_PROGRAM_ID,
       PROGRAM_CODE,
       PROGRAM_TITLE,
       FUNDABILITY_CODE,
       FUNDABILITY_DESC,
       FEE_BAND,
       FEE_BAND_DESC
FROM   IGSFV_PROGRAM_UK_STATISTICS
WHERE  HESA_RETURN_TYPE = :p_return_type;

Because the view resolves every code through IGS_HE_CODE_VALUES using distinct CODE_TYPE values, users searching on CODE_TYPE will find that this view encapsulates multiple reference lookups — OSS_FEPRMK, OSS_LOCSDY, OSS_CRDTSCM, OSS_FUNDLEV, OSS_FUND_CODE, OSS_FEEBAND, OSS_NHS_FUSRC, and OSS_FRAN_ACT — within a single query surface. Filtering by any of these attributes enables targeted extracts for reporting, though the obsolete status warrants migration to supported successors in current deployments.