|Field description||Field abbreviation||Field version||Field length||Field type|
|Aggregate offshore region of provision||XREGAG01||2.5.1||4||Char|
|XL||Guernsey and Jersey|
|IM||Isle of Man|
|GREU||Geographic region - Other European Union|
|GRAF||Geographic region - Africa|
|GRAS||Geographic region - Asia|
|GRAU||Geographic region - Australasia|
|GRME||Geographic region - Middle East|
|GRNA||Geographic region - North America|
|GROE||Geographic region - Other Europe|
|GRSA||Geographic region - South America|
Depend upon fields
This field groups the country of provision entries from the Aggregate offshore collection (Cyy052) to regions in a manner consistent with the Student collection's domicile derived fields.
The valid entries for this field are created from an overseas lookup table, provided by Data Management, which includes all the valid country entries in the column F_DOMICILE_ISO. (The Aggregate offshore collection country entries are a subset of the Student collection DOMICILE entries.)
Entry XL is the National Statistics County Classification (NSCC) code for Channel Islands not otherwise specified, but is used in this field to label Guernsey and Jersey. Guernsey and Jersey in this context refer to the Bailiwicks of Guernsey and Jersey, which includes their smaller islands. Guernsey and Jersey includes Channel Islands not otherwise specified.
The table T_OVERSEAS_LOOKUP_V9 consists of 3 columns:
Country of domicile (F_DOMICILE, VARCHAR2, 4); Region of domicile (F_REGION, VARCHAR2, 4) and ISO Country of domicile (F_DOMICILE_ISO, VARCHAR2, 2). This table has been supplied in an Oracle table provided by Data Management.
Where the COUNTRY value is found in the F_DOMICILE_ISO column of T_OVERSEAS_LOOKUP_V9, then return F_REGION as XREGAG01.
Where the COUNTRY is not found in the F_DOMICILE_ISO column of T_OVERSEAS_LOOKUP_V9, then return Null as XREGAG01, which will be reproduced in XREGAG01. This is an error, highlighting that a value in the collection's COUNTRY field is not contained in the F_DOMICILE_ISO column of T_OVERSEAS_LOOKUP_V9. (This is designed so the derived function does not fail. Nulls are to be identified at impossible values testing to flag the issue to HESA's Data Management team.)
|Cyy052 Provision.COUNTRY (2 CHAR)||XREGAG01|
|Found in T_OVERSEAS_LOOKUP_V9. F_DOMICILE_ISO||T_OVERSEAS_LOOKUP_V9.F_REGION|
|Not found in T_OVERSEAS_LOOKUP_V9. F_DOMICILE_ISO||Null|
|2016-09-12||2.5.1||Technical specification revised to update references to T_OVERSEAS_LOOKUP_V8, which has been replaced with T_OVERSEAS_LOOKUP_V9 to accomodate Mayotte joining the EU, and consequently deriving to GREU in F_REGION|
|2015-01-13||2.4.2||Removal of guidance relating to Cyy051 valid entry GROO, as this can no longer be derived in Cyy051|
|2013-10-31||2.4.1||Technical specification revised to remove the TEMP processing stage, which is no longer required with the complete move of the source data tables to ISO-3166-1 and the consequent removal of old-style HESA country codes in T_OVERSEAS_LOOKUP_V8 for F_REGION. T_OVERSEAS_LOOKUP_V6 updated to T_OVERSEAS_LOOKUP_V8 to reflect the move to ISO-3166-1 codes in F_REGION and Croatia joining the EU, consequently deriving to GREU in F_REGION|
|2013-02-18||2.3.1||Text added further clarifying the meaning of Guernsey and Jersey to Additional Information. Second row added to the Technical Specification 'TEMP' table: where the COUNTRY value is not found in T_OVERSEAS_LOOKUP_V6.F_DOMICILE_ISO, return Null as XREGAG01 as an error - rather than allowing the function to fail|
|2012-03-06||2.2.1||Updated to use t_overseas_lookup_v6|
|2009-04-30||2.1.1||Removal of valid entry GROO as Cyy052.COUNTRY valid entries cannot derive to Non-European Union unknown|
Contact Liaison by email or on 01242 211144.