Extract Survey Data to ORACLE Tables {HEAMIS-1}

This program selects programme (qualification), course (subject) and student data from the database and puts it into oracle tables. From these tables, the data is taken and reported in XML format - option {HEAMIS-3}

The data extracted is stored in six separate tables. This data will be used to create six different XML-files, i.e.

Student Intake (Table 13)
Student Output (Table 14)
Course Intake (Table 12)
Course Output (Table 16)
Programme Intake (Table 11)
Programme Output (Table 15)

The basic structure of the program is to select student / qualification records that are registered between the reporting year as set-up in {HEAMN-1} and the reporting year minus one, and where the FTEN status for the particular student is not null. Students with no FTEN status are excluded. Students who pass this check will then be checked in terms of whether they are an intake or an output category student. If the student / qualification fits the intake category criteria, then the output category check will be ignored and the record is then evaluated against the last general checks. If the student / qualification record does not meet the intake category criteria, it will be evaluated against the output criteria before it proceeds to the general checks. All of the above logic is discussed in detail, in the discussion that follows.

For every update run, the current records in the table RAUHSD {HEAMIS-2} will be removed and the new records generated inserted. In the summary information at the end of the report a count will indicate the number of records removed and created.

The program’s logic is to take a specific selected student / qualification record and process it through various checks to determine if it is a possible Intake or Output file record. The records to be evaluated as possible Intake or Output file records are selected as follows:

The default values, as set in the control file {HEAMN-1} - and, in particular, the Reporting Year - are used.
Student / qualification registration will be selected where

a) The registration year is between the HEA reporting year as specified in {HEAMN-1} for the field Enter Calendar Year For HEA Reports and this value minus one. For example: if the reporting year is set as 2002, records to be selected will be those where registration year is between 2001 and 2002.
b) The enrolment date must also be before 31-OCT- (Reporting Year -1): for the above example it would be 31-OCT-2001.
c) In addition, no cancellation date must exist for the registration record, or the cancellation date must be after the 31- OCT- (Reporting Year - 1). For the above example, it would be 31-OCT- 2001.
d) In addition, where the offering type of the record is either linked as a full or part-time offering type, Extra Mural offering types are excluded. Only offering types linked to subsidy types A (for Normal) and M (for Mixed) are also included: offering types liked to subsidy types B (for Distant) and C (for None) are excluded. All of the above are linked to the offering type in option {GCS-5}.

For the above selected records the program will determine which are possible Intake or Output file records.
Possible Intake / Output file records are identified as follows:

a) Intake File

i) If the student’s registration year is equal to the reporting year e.g. reporting year 2002 and student’s registration year is also 2002.
ii) The Final Year indicator is equal to “Y”.
iii) The qualification “Result Code” leads to a PASS.

b) Output File

i) If the student’s registration year is equal to the reporting year minus one, e.g. if the reporting year is 2002 and the student’s registration year is 2001.
ii) The student / qualification record must have at least one primary subject that is:

I) Registered for an exam year and month that are between the control file {HEAMN-1} start and end - year and month.
II) The enrolment date is before 31-OCT-(Reporting Year -1), for this particular example it would be 31-OCT-2001.
III) Has no cancellation date in existance for the registration record or the cancellation date is after the 31- OCT-(Reporting Year - 1). For this particular example, it would be 31-OCT- 2001.

For each record in the above possible Intake / Output tables, the following biographical and registration details are selected.

a) Student Number
b) Enrolment Year
c) Qualification - the “Ext. Code” as defined on {SACADO-1} will be reported, if no value exists for the field: the Qualification Code will be concatenated with the Offering Type. The HEA reporting requirement is that different program codes must be reported if different offering types are applicable.
d) Study Period - if the study period of the record is “C” it would be inserted into the table as 99.
e) Qualification Result
f) FTEN Status
g) Final Year Y / N - if there is no value for the record it will default to “N”
h) Qualification Offering Type
i) Table 1.2 Row Code - as defined for the qualification on {SACADO-1}. This row code will only be used if the answer to the question “For Table 1.2 : Is More Than One Row Applicable” is “Y”, otherwise the row code for subjects will be used. Also refer to the logic below to see which subject row code is used.
j) Program Code – the value as entered for the field “Ext. Code” {SACADO-1}. If no program code exists, the Approved Qualification Code as linked to the qualification in {SACAD-1b2}, will be selected.
k) Offering Type Indicator - (F)ull Time / (P)art Time as set-up on {GCS-5}.
l) Post / Undergraduate - indicator for the qualification as set-up on {SCODE3-1b1}.
m) Qualification Type - for the record selected as set-up in {SCODE3-1b1}.
n) Research / Teaching - indicator for the qualification record selected, as set-up in {SCODE3-1b1}.
o)Master / Doctorate - Indicator for the qualification record selected in option {SCODE3-1b1}, if the qualification is not a master or a doctorate.
p) Exam Only Indicator - the block for the qualification record as set-up on {SCODE2-1}.
q) Preparation Time - the value as liked to the qualification ID for the qualification record as set-up on {SACADO-1}. If no value exists for this field a default of “0” will be used.
r) Pass / Fail Indicator - of the qualification’s result code, as linked on {SCODE-23}.
s) Gender of Student

The final check is to evaluate the data selected: if the record’s data is incomplete according to the checks below, the student / qualification record will be excluded. These records will be printed at the end of the program’s output with the applicable error message. The program will check whether :

a) A gender value exists - if not, it prints No gender code available.
b) A FTEN status value exists - if not, it prints No FTEN Status available.
c) An Offering Type Indicator exists for the record’s offering type {GCS-5}- if not, it prints No offering type available.
d) Either an Ext. Code or Approved ID exists for the qualification {SACADO-1} - if not, it prints No Programme Code Available.
e) A Post / Undergraduate indicator exists for the qualification type linked to the qualification {SCODE3-1b1} - if not, it prints No Post / undergraduate type available.
f) A Qualification Type for the qualification exists {SCODE3-1b1} - if not, it prints No Qualification type available.
g) A Research / Teaching indicator for the qualification type linked to the qualification exists {SCODE3-1b1} - if not, it prints No Research / Teaching indicator available.
h) An Exam Only indicator for the qualification / block registration exists {SCODE2-1} - if not print No Block Exam only indicator available.

The product of Intake / Output records identified up to this point will go through final checks before it is included in the different tables, which will be the source for the files reported to the HEA.
Student Intake Data (TABLE 13)

All possible intake student / qualification records identified thus far will be checked against the following set of rules, before a record is added to the table.

a) Only first year student / qualification records are included. The record must have a study period of one (1).
b) The student / qualification record must be an AB-INITIO student. This will include only records where the preparation time for the qualification is “0”, as defined for Qualification ID linked to the qualification {SACADO-1}.
c) A student / qualification record registered for exam only purposes will be excluded from the table. The block code linked to this registration will determine this: if the block is linked to “Exam Only” on {SCODE2-1} it will cause the record to be excluded.
d) Check for valid ISCED codes on qualification level.

i) If the field Table 1.2 Row Code in option {SACADO-1} has no value and the question on this bock Is more than 1 row applicable” is “N”, then the student will be excluded with the message No ISCED code available on course level.
ii) If the field Table 1.2 Row Code in option {SACADO-1} has an ISCED code value with a length of three characters and the answer to Is more than 1 row applicable” is “N”, then this is regarded as a valid ISCED code, therefore no further checks are required.
iii) If the field Table 1.2 Row Code in option {SACADO-1} has a value with a length not equal to 3, and the answer to the question Is more than 1 row applicable” is “N", then this is an invalid length: therefore the student will be excluded with the message - Illegal length for ISCED code on course level. A valid ISCED code must be 3 characters in length.

e) Checks for ISCED codes on subject level and the ISCED code value to be included on the table, will only be applicable when the answer to the question in {SACADO-1}, "Is more than 1 row applicable” is “Y”.

i) For each subject record selected for the student / qualification registration an ISCED code must exist. If the field Stats 1.2 Row Code in option {SACADO-1} has no ISCED code value, then the student / qualification record will be excluded and the message No ISCED code available on subject level will be printed.
ii) If the ISCED code for the above mentioned field is not a 3 character value the record is excluded with the message Illegal length for ISCED code on subject level.
iii) If no subject registration is found for the student / qualification record, the record will be excluded from the table and the message No Subjects Registered to Identify ISCED code will be provided.
iv) If only one valid ISCED code exists for this student, then this code will be used when the record is included in the table.
v) If more than one valid ISCED code exists then the ISCED code with the most subjects is the value that will be used when the record is included in the table.

Example.

ISCED Code	NR of Subjects
912	3
913	4
914	2

For the above example the value for Stats 1.2 Rowcode to be included on the table will be 913.

vi) If subjects are equally divided between ISCED codes, then the code will be narrowed down to the narrow band or broad band. The band definition is as follows. The ISCED code is divided in 3 parts

Example: ISCED code 917 is split as follows : 9 is the Broad Band 1 is the Narrow Band 7 is the Detail Band

If subjects are equally divided between ISCED codes and only the detail band differs then the detail band will be replaced with a zero (0) -

Example

ISCED Code	NR of Subjects
912	1
913	1
914	1
915	1
916	1

i.e. the ISCED that will be used is 910, which is the first 2 digits plus a zero.

vii) If the subjects are equally divided and the narrow band differs, then the narrow and detail bands are replaced with zero's -

Example:

ISCED Code	NR of Subjects
912	1
913	1
914	1
920	1
919	1

for example: the ISCED code that will be used is 900, which is the first digit plus two zeros.

viii) If the number of subjects is split equally between '2 Humanities and Arts' and '3 Social Sciences and Law' subjects then the ISCED code is set to 910.
ix) If the subjects are equally divided and the broad band differs, then the ISCED code will be set to 900.

Example

ISCED Code	NR of Subjects
912	1
713	1
814	1
314	1

f) A student / qualification record qualifies as a successful Intake table record when all the above checks have been passed. Each record is inserted individually with the following information into specific columns that determine the data type that is inserted into the table. The following columns are included for table 13.

i)	Column	50	=	Institution Code
ii)	Column	60	=	Academic Year
iii)	Column	130	=	Student Number
iv)	Column	70	=	Scrambled Student Number
v)	Column	100	=	Programme Code concatenated with offering type. [As indicated in above discussion if no Programme code was available the Approved Qualification ID concatenated with the offering is used].
vi)	Column	110	=	Study Period
vii)	Column	170	=	Gender Tag (Male=1 & Female =2)
viii)	Column	280	=	Student Type Tag

The value for the student tag is determined as follows:

I) If the study period of the student / qualification record is equal to 1 and the FTEN status is not an “F” then the student type is set to '1' (not a new entrant). If the FTEN status is a “F” then the student type is set to '2' (new entrant).
II) If the student / qualification record’s final year indicator is “Y” and the Result for this record leads to a PASS and is equal to the either of the result codes specified in the initial selection criteria, the student type is set to '4' (first class or 2.1 award recipient). If the record’s result is not what was specified with the selection criteria then the student type is set to '3' (normal graduand).

ix)

Column

290

ISCED Code - as determined by {SACADO-1} or by subject registrations.

Student Output Data (Table 14)

All possible output student / qualification records identified thus far will be checked against the following set of rules before a record will be added to the table.

a) Only students with preparation time for the Qualification ID linked to the qualification in option {SACADO-1} equal to zero(0) or one (1) will be included. If a student has any other value, the student is excluded.
b) Only graduate student / qualification records are included in this table: if the record’s final year indicator is not “Y’, the record will be excluded.
c) Only student / qualifications with a PASS related result code will be included. If the result leads to a FAIL result the record is excluded.
d) Checks for valid ISCED codes on qualification or subject level. The logic for the ISCED code to be used is the same as that for the Student Intake table as discussed above.
e) A student / qualification record qualifies as a successful Output table record when all the above checks have been passed. Each record is inserted individually with the following information into specific columns that determine the data type that is inserted into the table. The following columns are included for table 14.

i)	Column	50	=	Institution Code
ii)	Column	60	=	Academic Year
iii)	Column	130	=	Student Number
iv)	Column	70	=	Scrambled Student Number
v)	Column	100	=	Programme Code concatenated with offering type. [As indicated in above discussion if no Programme code was available the Approved Qualification ID concatenated with the offering is used].
vi)	Column	110	=	Study Period
vii)	Column	170	=	Gender Tag (Male=1 & Female =2)
viii)	Column	280	=	Student Type Tag

The value for the student tag is determined as follows:

I) If the study period of the student / qualification record is equal to 1, and the FTEN status is not an “F”, then the student type is set to '1' (not a new entrant). If the FTEN status is an “F”, then the student type is set to '2' (new entrant).
II) If the student / qualification record’s final year indicator is “Y” and the result for this record leads to a PASS and is equal to the either of the result codes specified in the initial selection criteria, the student type is set to '4' (first class or 2.1 award recipient). If the record’s result is not what was specified with the selection criteria then the student type is set to '3' (normal graduand).

ix)

Column

290

ISCED Code - as determined by {SACADO-1} or by subject registrations.

From the data that is inserted into the student intake / output tables the data for the Programme (Approved Qualification) intake / output and course (Qualiffication) intake / output is determined.
Course Intake Data (Table 12)

a) From the student intake table (Table 13) distinct records are selected for the

i) Intake year
ii) Programme Code concatenated with Offering Type or Approved Qualification name with Offering Type and;
iii) Approved qualifications ID concatenated with offering type.

b) Each record found will be individually inserted into the table, with the following set of information attached to it. This information is inserted into specific defined columns that determine the data type that is inserted into the table.
c) Table 12 consists of the following columns:

i)	Column	40	=	Institution Code
ii)	Column	50	=	Academic Year
iii)	Column	70	=	Student Number
iv)	Column	70	=	Programme Code - Approved Qualification ID concatenated with Offering Type.
v)	Column	90	=	Course Code - the Qualification Code and Offering Type concatenated. [As indicated in above discussion if no Programme code was available the Approved Qualification ID concatenated with the offering is used].
vi)	Column	100	=	Qualification Description
vii)	Column	120	=	Study Period - this value will include the total number of years to complete the qualification. If the record’s study period is “C” the column value is set to “99”.

Course Output Data (Table 16)

a) From the student output table (Table 14} distinct records are selected for the:

i) Output year
ii) Programme Code concatenated with Offering Type or Approved Qualification name with Offering Type and;
iii) Approved Qualifications ID concatenated with Offering Type.

c) Table 16 consists of the following columns:

i)	Column	40	=	Institution Code
ii)	Column	50	=	Academic Year
iii)	Column	70	=	Programme Code - Approved Qualification ID concatenated with Offering Type.
iv)	Column	90	=	Course Code - the Qualification Code and Offering Type concatenated. [As indicated in the above discussion if no Programme code was available the Approved Qualification ID concatenated with the offering is used].
v)	Column	100	=	Qualification Description
vi)	Column	120	=	Study Period - this value will include the total number of years to complete the qualification. If the record’s study period is “C” the column value is set to “99”.

Programme Intake Data (Table 11)

a) From the course intake table (Table 12} distinct records are selected for the intake year, Programme Code as defined in Course Intake (i.e. Approved Qualification ID concatenated with Offering Type) and Programme type. The definition of programme type is as follows. The programme type consists of three digits determined as follows:

i) First digit -
If Offering Type Indicator - (F)ull Time / (P)art Time as set-up on {GCS-5}; if it is 'F'.
then set the first digit to '1'. If it is 'P' then set it to '2', otherwise default it to
ii) Second digit –
If Post / Undergraduate - indicator for the qualification, as set-up on {SCODE3-1b1}; if it is 'U' then set the second digit to '1'. If it is 'P' then set it to '2',
otherwise default it to '0'.
iii) Third digit -
If Master / Doctorate - Indicator for a master or doctorat,e as set-up on {SCODE3-1b1}; if it is 'D', then set the third digit to '7'.
If Master / Doctorate indicator is 'M', then the indicator is either set to '5' or '6'. This is determined by the Research / Teaching indicator for the qualification record selected, as set-up in {SCODE3-1b1}. If it is 'R', then set the third digit to '6', otherwise set the third digit to '5'.

If Master / Doctorate indicator is not 'D' or 'M' then we decide the third digit as follows:
If Qualification Type - for the record selected as set-up in {SCODE3-1b1} is 'O' then set the third digit to '4'.
If Qualification Type indicator is 'C', then set third digit to '3'.
If Qualification Type indicator is 'P', then set third digit to '2'.
If Qualification Type indicator is 'D', then set third digit to '1'.
If Qualification Type is none of the above then the third digit is set to zero.

i)	Column	40	=	Institution tag
ii)	Column	50	=	Academic Year
iii)	Column	70	=	Programme Code - Approved Qualification concatenated with Offering Type.
iv)	Column	80	=	Programme Name description
v)	Column	90	=	Programme Type

Programme Output Data (Table 15)

a) From the course output table (Table 16) distinct records are selected for the output year, Programme code as defined in Course Output (i.e. Approved Qualification ID concatenated with Offering Type) and Programme Type. Programme type consists of three digits, the same as outlined in Programme Intake Data section.
b) Each record found will be individually inserted into the table, with the following set of information attached to it. This information is inserted into specific defined columns that determine the data type that is inserted into the table.
c) Table 15 consists of the following columns:

i)	Column	40	=	Institution tag
ii)	Column	50	=	Academic Year
iii)	Column	70	=	Programme Code - Approved Qualification concatenated with Offering Type.
iv)	Column	80	=	Programme Name description
v)	Column	90	=	Programme Type

User Selection	Prompt Text ^{* an item between square brackets [ ] is the default answer}	Type & Length	Comments
	Is this the Preliminary (FIRST) Hemis Submission - (Y)es or (N)o ?	A1

Sort Order	Per	Comments
	Error messages are reported first and summary of report is given at the end.

System Select
	As per above discussion.

Example:

Processing Rules
	No special processing rules.

Date	System Version	By Whom	Job	Description
29-May-2008	v01.0.0.0	Charlene van der Schyff	t12345	New manual format.
22-Sep-2008	v01.0.0.0	Charlene van der Schyff	t151627	Edit language obtained from proof read language Juliet Gillies.
10-Apr-2015	v04.0.0.0	Magda van der Westhuizen	t205922	Convert to INT4.0.