Generate SDR Data {RSDR-2} 


1.    INTRODUCTION

1.1    General Information

This is the User Manual for the ITS SDR Subsystem.  It is one of a series of user and technical manuals which are available for the ITS systems.  A complete list of manuals will be found in the General System User Manual.

It is assumed that the reader is already familiar with the general operation of the menus and the keyboard.  These matters are fully discussed in User Manual “Operational Aspects of ITS System”.

The reader is reminded that the copyright of the ITS systems and documentation remains with ITS, and that users thereof are contractually prohibited from providing information thereon to third parties, such as other educational institutions.

1.2    Overview
The SDR/MOE reporting system forms part of the Statutory Reporting Systems. This system is accessed using menu options grouped under the code RSDR.

The SDR subsystem has been written to fulfill the specifications set up by the Ministry of Education of New Zealand (MOE).  Documentation of the reporting requirements for the SDR system is available from the Ministry’s website, www.steo.gov.nz .

The SDR subsystem is made up of 4 menu options:

SDR reporting options should only be used  after the required information in the various ITS subsystems are fully up to date for the particular reporting year and cycle.

Users must please note that this option and {RSDR-4} may require substantial computing power, and may affect system response times for other users.  It is therefore advisable to execute these options overnight.
1.3    Linking to other ITS Systems

This system is fully dependent on other ITS subsystems for its functionality and the completeness and accuracy of the data in those systems is therefore of prime importance. Successful SDR reporting depends upon a well-integrated effort by the users involved in all the subsystems.

Census Dates must be set correctly in option {GOPS-1} as a pre-requisite for SDR Reporting. 
It is important that a record is created for every academic period identified at the institution in menu {SCODE2-1}. 

External Conversion Codes must also be set up on {GOPS-21}, including:
Source of Funding is configured as part of contract setups {FSAC-10} with an override on available year/subject/block/contract combinations under option {SACAD-16}. The source of funding codes are system defined (RBCTAB, row 88010).

The Academic Structure contributes a number of SDR {SMAIN-1}

1.4    The right to use this subsystem

Since the SDR system is the mechanism in the ITS System for the Statutory Reporting of Student Data, any client who purchases the Student Subsystem will also be allowed to use the SDR programs.


2.    USING THE SDR SUBSYSTEM

2.1.    Overview of the Statutory Requirements

For the purposes of reporting in accordance with the SDR requirements, the following ASCII files (“flat files”) are created, with data covering student information that have to be submitted to the Ministry of Education:

1 99990011 Student file STUD
2 99990012 Course enrolment file COUR
3 99900013 Course Register CREG
4 99900014 Qualification completions file GRAD
5 99900015 Subject Completions file COMP

These ASCII files are output to the directory specified in menu option {BATCH-7} using the settings System = 'RPT' and Function = 'HEMIS'

Two different sets of SDR files may be generated. This is indicated in the answers for running menu option {RSDR-2}.

File type 1 - This answer generates data for a single CREG (course register) file that includes a summary of all courses available for study for the SDR year specified in {RSDR-1b1}.

File type 2 - This answer generates all five output files and is based on the current SDR year and cycle specified in {RSDR-1b1}.

The recommended order of execution is given as:
  1. Confirm that the linked ITS systems are configured for SDR (including menu options {GOPS-1}, {SCODE2-1}, {GOPS-21}, {FSAC-10}, {SACAD-16}, {SACADO-1}, {SACADO-10} and others).
  2. Check the output directory is set correctly {BATCH-7}.
  3. Configure the SDR parameters {RSDR-1}. Take specific notice of the Year and SDR run number (found on Tab 1) and the SDR reporting date (found on Tab 2).
  4. Generate the SDR data by running {RSDR-2}. All data written to the database can be accessed from {RSDR-3}. Note that this option also calls {RSDR-4} automatically.
The following extra two steps can be run if manual changes to the generated data are necessary:
  1. Manually query/update data written to the database for a specific SDR year and cycle using option {RSDR-3}.
  2. Generate the ASCII files by running menu option {RSDR-4}. This option can be run independently to recreate output for a previously run SDR year and cycle.
Note that every time {RSDR-2} is run, the generated data will be used to overwrite all pre-existing SDR data stored from a previous data extraction for the same year and SDR run number.  This means that only one set of SDR data is kept on the database for any specific SDR reporting year and SDR run number. 

3.    EXRACT STUDENT DATA INTO SDR TABLES {RSDR-2}

This menu option consists of a C program that should be run in background mode (Immediate or Deferred Batch). 
The program rm12ccc.pc which extracts student data will be executed when this option is run.

The SDR reporting year will be taken as the year set in {RSDR-1b1}.
3.1    Executing the option:

User Selection Prompt Text
* an item between square brackets [ ] is the default answer
Type
&
Length
Comments
     Choice of file type to include [1]. N1 This option can be run with one of two answers:

(1) Course registers for the entire year. This will report all subjects available to students in the given year.

(2) All SDR files.
 
NOTE: 
The SDR extraction process involves a large volume of data and will probably take a few hours to complete.

Please see chapter 5 of this manual for a detailed discussion on how each element of the student data files is extracted from the database.


3.2    On Completion of the option:

Output data will be written to table RPT.RAUHSD. This information can be queried in {RSDR-3}.

Menu option {RSDR-4} will be automatically executed to generate the output files. These are written to a directory specified under menu option {BATCH-7} using the settings System = 'RPT' and Function = 'HEMIS'.

An email will be sent to Alan Ward and Richard Twort indicating the SDR run has finished. At present these email addresses are hardcoded in the program. 

The user can also use the “Query Batch” command to check for the completion of this option {BATCH-14}.



4.    CREATION OF THE SDR SUBMISSION FILES {RSDR-4}

This menu option is used to extract the data stored in {RSDR-3} and generate the ASCII submission files as prescribed by the Ministry of Education. 

Normally this option is run automatically whenever {RSDR-2} is run. However, it can be run independantly to regenerate the submission files for different SDR years and cycles. 

The program accepts 2 answers:
1. Do you want (S)tudent, (P)ersonnel or Space(E) files for HEMIS?
    Answer 'S' to get the SDR data;
2. Enter the Submission Number
    Enter the cycle for the desired SDR data.

Note that the program determines the SDR year from {RSDR-1b1}.

The program will provide output to indicate how many records were written from the database to each file.
An example of the output is displayed below:

Total records generated for file STUD6022 is 6641

Total records generated for file COUR6022 is 8244

Total records generated for file CREG6022 is 644

Total records generated for file GRAD6022 is 900

Total records generated for file COMP6022 is 208

END OF REPORT

Please note that the number of “records generated” is the records extracted from the database, and the total is the number of records written to the ASCII files multiplied with the number of columns per file.  The number given in this output report is therefore not the same as the number of lines (records) written to the ASCII files.

The output indicates that five files were created, each with the table name, followed by the institution code.

The files will be created in the directory indicated in the record for System RPT and Function HEMIS in {BATCH-7} - Directories for FTP Transfers.



5.    DATA REFERENCE for the STUD, COUR, CREG, GRAD and COMP files:


Note that in all cases where external conversion codes {GOPS-21} are used, the external body is “SDR”.

Table and Column Ele-
ment
Data obtained from Database reference
 99990011 C1     Institution Code as from {GCS–1}. GAOCODE from GAOINS
99990011
C2
    Student number as from {SREGB-1}, for all students with a qualification enrolment in the SDR year. IADSTNO from IADBIO
99990011
C3
    The Gender of the student as indicated on {SREGB-1}. IADSEX from IADBIO
99990011
C4
    The Birth date of the student as from {SREGB-1}. ADBIRDAT from IADBIO
99990011
C5
    Ethnic Group {SREGB-1}. ADETHN, IADETHN2, IADETHN3 from IADBIO
99990011
C6
  1st 4 letters from surname and 1st letter from names. IADSURN, IADNAMES from IADBIO
99990011
C7
    If student type is D then Previous activity as from {SREGB-1} IADPACT from IADBIO
99990011
C8
  Get Minimum year from previous qualification and qualification enrolments.

Previous Enrollments from other institutions {SREGB-7} or {SREGB-1} Previous Qualifications / Subjects under Functions. Previous enrolments at the Open Polytechnic can be seen at {SSTUDY-21b2}
Min (IDJCYR) from IDJQAL and

Min (IAGCYR) from IAGENR
99990011
C9
    If student type is D then Select Disability then code is given as 1 otherwise it is a 2.  {SREGH-1} Medical (Y / N). IAGMEDICAL from IAGENR
99990011
C10
    If student type is D then Select School  code {GOPS – 21}.
  • To do the translation correctly, the conversion codes should be entered by the user in the above options as follows:
  • External Body = SDR
  • External Code Type = SCC
  • Internal Code is the school code as linked to the student
External Code is the code as will be reported.
IKXEXTCODE from IKXETR
99990011
C11
    If student type is D then select Matric date {SREGB -1}. IADMATDATE from IADBIO
99990011
C12
    If student type is D then select Matric type IADMATTYPE from IADBIO
99990011
C13
    If alien indicator is P set code to NZP otherwise use External Code on IKXETR
  • External Body = HEA
  • External Code Type = COR
  • Internal Code is the citizenship code as linked to the student
IADINDALIEN from IADBIO
99990011
C14
    If student type is D then IAGASSIST IAGASSIST from IAGENR
99990011
C15
    If student type is D
  • Default code is 1
  • If one or more disability codes are linked to a student in {SREGB-1b6}, the program will report a code of 2.
  • If a certificate code is linked to the student for the certificate codes setup  in the conversion table {GOPS–21}, the program will report a code of 9.
The conversion codes should be entered by the user in the above options as follows:
  • External Body = SDR
  • External Code Type = DCR,DCT
  • Internal Code is the certificate code as linked to the student.
 
99990011
C16
    If student type is D then select qualification enrolment final year. {SREGAR-1b1} IAGFINALYEAR from IAGENR
99990011
C17
    If student type is D then select Iwi Affiliation IADIWI1, IADIWI2, IADIWI3
99990011
C18
  If student type is D then select Internal Revenue Number. IADIRDNUM from IADBIO
99990011
C19
    If student type is D or C then select National Student Number. IADNSNNUMNER from  IADBIO
99990011
C20
    Is Empty. The Foreign fee is returned in the CREG file (99990013 C15). According to the MOE’s manual it can only be submitted in one of the files, not both.  
99990011
C21
    Contains information from student transactions. From IAMLOG
   
99990012
C1
    Institution Code as from {GCS–1} GAOCODE from GAOINS
99990012
C2
    Student number as from {SREGB-1}, for all students with a qualification enrolment in the SDR year. IADSTNO from IADBIO
99990012
C3
    External qualification code (SAPSE) {SACADO-1} External code field}. IAISAPSE from IAIQAL
99990012
C4
    External subject Code {SACADO-10} IALSDRCODE from IALSUB
99990012
C5
    The system finds the first block in which the student was registered for the relevant qualification.  Then the start date of the block, as recorded on the census date information in menu option {GOPS-1} is used. GANSDATE from GANSEN
99990012
C6
   
  • If National Credit (IALNCRED) is less then or equal to 500 then 28 days is added to the block starting date (GANSDATE).
  • If National Credit (IALNCRED) is between 500 and 1080 then 56 days is added to the block starting date (GANSDATE).
  • If National Credit (IALNCRED) is between 1080 and 1670 then 84 days is added to the block starting date (GANSDATE).
  • If National Credit (IALNCRED) is between 1670 and 2170 then 112 days gets added to the block starting date (GANSDATE).
  • If National Credit (IALNCRED) is greater then 2170 then 140 days is added to the block starting date (GANSDATE).
  • If the date the result for the subject  was entered before the block end date, then use the result date.
 
99990012
C7
    The system finds the first block in which the student was registered for the relevant qualification.  Then the end date of the block, as recorded on the census date information in menu option {GOPS-1} is used only if the cancel date is greater then the end date of the block. GANEDATE from GANSEN
99990012
C8
    Not Used  
99990012
C9
    The subsidy type {GCS-5} of the student according to the offering type on the relevant subject enrolment. GASSUB from GASOTP
99990012
C10
    The Campus {GCS-5} of the student according to the offering type on the relevant subject enrolment.  Note that the campus is translated into the SDR codes using the External Conversion code CMP. GASGCAM from GASOTP
99990012
C11
    Source of Funding (FIRSRCFUND) as per table (RBCTAB) RBCROW from RBCTAB
99990012
C12
    Not Used  
99990012
C13
  Legal Resident Status. If value of IAGINDALIEN is Q, default to Y  IAGINDALIEN from IAGENR
99990012
C14
    Fund category (IALROW102) RBCALTCONTENTS from RBCTAB
99990012
C15
    Course Classification(IALROW103) RBCALTCONTENTS from RBCTAB
99990012
C16
    NZSCED Field of Study(IALROW209) RBCROW from RBCTAB
99990012
C17
    Course EFTS Factor {SACADO-10} IALNCRED from IALSUB
99990012
C18
    A calculation is done to calculate EFTS by Month  
99990012
C19
    National Student Number {SREGB–1} IADNSNNUMNER from IADBIO
99990012
C20
  The system finds the first block in which the student was registered for the relevant subject.  Then the end date of the block, as recorded on the census date information in menu option {GOPS-1} is used. GANSDATE from GANSEN
99990013
C1
    Institution Code as from {GCS–1} GAOCODE from GAOINS
99990013
C2
    External subject code (SAPSE) {SACADO-10} IALSDRCODE from IALSUB
99990013
C3
    The system select the subject’s full name IALFULLNAME from IALSUB
99990013
C4
    Qualification Code {SACADO-1} External code field IAISAPSE from IAIQAL
99990013
C5
    Course Classification (IALROW103) RBCALTCONTENTS from RBCTAB
99990013
C6
    NZSCED Field of Study (IALROW209) RBCROW from RBCTAB
99990013
C7
    NQF Level (IALNQFLVL) RBCROW from RBCTAB
99990013
C8
    The system select Subject Credit (IALCREDIT), an error is printed if Subject Credit is greater then 240. {SACADO-10} IALCREDIT from
IALSUB
99990013
C9
        RBCALTCONTENTS from RBCTAB
99990013
C10
    EFTS - National Credit (IALNCRED) {SACAD–14b1}. IALNCRED from IALSUB
99990013
C11
    Period of Study {SACADO-17} Field SP for Study Period} IAKPERSTUDY from IAKQAL
99990013
C12
    3.12 Fee  
99990013
C13
    The Level of Internet Access {SACADO-11}. IDCINTERNETACC from IDCSOT
99990013
C14
    Performance Based Research Funding Eligible Course {SACADO-11}. RBCALTCONTENTS from RBCTAB
99990013
C15
    3.15 Fee    
99990013
C16
    3.16 Fee    
99990013
C17
    Course Exemption from FCCM {SACADO-10}. IALEXMAXIMA from IALSUB
99990014
C1
    Institution Code as from {GCS–1}. GAOCODE from GAOINS
99990014
C2
    Student number as from {SREGB-1}, for all students with a qualification enrolment in the SDR year. IADSTNO from IADBIO
99990014
C3
  The Gender of the student as indicated on {SREGB-1} IADSEX from IADBIO
99990014
C4
    The Birth date of the student as from {SREGB-1}. IADBIRDAT from IADBIO
99990014
C5
    Ethnic Group {SREGB -1} IADETHN,IADETHN2,IADETHN3 from IADBIO
99990014
C6
  If alien indicator is P the set code to NZP otherwise use External Code on IKXETR
  • External Body = HEA
  • External Code Type = COR
Internal Code is the citizenship code as linked to the student (IADINDALIEN) {SREGB-1}.
IADINDALIEN from IADBIO
99990014
C7
   
  • School code(IADSCHOOLCODE) {SREGB - 1}.
  • To do the translation correctly, the conversion codes should be entered by the user in the above options as follows:
  • External Body = SDR
  • External Code Type = SCC
  • Internal Code is the school code as linked to the student
External Code is the code as will be reported
IKXEXTCODE from IKXETR
99990014
C8
    Matric Date Year {SREGB-1}.
   
IADMATDATE from IADBIO
99990014
C9
  Highest Secondary School Qualification {SREGB-1} Secondary School Information. IBKCODE from IBKMTP
99990014
C10
    Category of Fees Assessment.
{SDREGA-1} Contract information – SDR Assist.
IAGASSIST from IAGENR
99990014
C11
    External Qualification Code {SACADO-1} External code field. IAISAPSE from IAIQAL
99990014
C12
    Not Used    
99990014
C13
    External subject Code {SACADO-10} IALSDRCODE from IALSUB
99990014
C14
    External subject Code {SACAD-14b1} IALSDRCODE from IALSUB
99990014
C15
    External subject Code {SACADO-10} IALSDRCODE from IALSUB
99990014
C16
  Iwi Affiliation {SREGB-1} IADIWI1, IADIWI2, IADIWI3 from IADBIO
99990014
C17
    National Student Number {SREGB – 1} IADNSNNUMNER from IADBIO
99990015
C1
    Institution Code as from {GCS–1} GAOCODE from GAOINS
99990015
C2
    Student number as from {SREGB-1}, for all students with a qualification enrolment in the SDR year. IADSTNO from IADBIO
99990015
C3
    External subject Code {SACAD-14b1} IALSDRCODE from IALSUB
99990015
C4
    Result: If the subject result (IAHERES) = the required result (ICQCOMPULRESULT) this becomes a 4 otherwise the pass / fail indicator (IBQPASSFAIL) is returned. See below for detail:

The various codes are derived as follows:

0:  Still to complete course – valid extension or grade not yet available (Register Level 1-8):

When there is no result in IAHERES or in IBQPASSFAIL where IBQCODE = IAHERES and the system cycle start date GANSDATE of the subjects block is greater than the IAHSSDATE.

1:  Still to complete course – course end date not yet reached (Register Level 1-8):

When there is no result in IAHERES or in IBQPASSFAIL where IBQCODE=IAHERES and the system cycle start date GANSDATE of the subjects block is not greater than the  subjects start date IAHSSDATE.

2:  Completed course successfully:

IBQPASSFAIL for the subjects IAHERES is a P.

3:  Completed course unsuccessfully:

IBQPASSFAIL for the subjects result IAHERES is an F.

4:  Did not complete course:

When the ICQCOMPULRESULT is not equal to the subjects result code IAHERES.

5:  Practicum to complete - on job training (Register Levels 1-8):

When IBQPASSFAIL is null and IALINSERV=’Y’

6:  Yet to complete – Register Levels 9 & 10:

When IBQPASSFAIL is null and IALNQFLEV = (9 or 0) and the system cycle start date GANSDATE of the subjects block is greater than the IAHSSDATE

7:  Extension granted or under moderation – Register Levels 9 & 10:

When IBQPASSFAIL is null and IALNQFLEV = (9 or 0) and the system cycle start date GANSDATE of the subjects block is not greater than the IAHSSDATE.

   
99990015
C5
    The system finds the first block in which the student was registered for the relevant subject.  The the start date of the block, as recorded on the census date information in menu option {GOPS-1} is used. GANSDATE from GANSEN
99990015
C6
    National Student Number {SREGB–1} IADNSNNUMNER from IADBIO
99990015
C7
   
  • If National Credit (IALNCRED) is less then or equal to 500 then 28 days is added to the block starting date (GANSDATE).
  • If National Credit (IALNCRED) is between 500 and 1080 then 56 days is added to the block starting date (GANSDATE).
  • If National Credit (IALNCRED) is between 1080 and 1670 then 84 days is added to the block starting date (GANSDATE).
  • If National Credit (IALNCRED) is between 1670 and 2170 then 112 days gets added to the block starting date (GANSDATE).
  • If National Credit (IALNCRED) is greater then 2170 then 140 days is added to the block starting date (GANSDATE).
  • If the date the result for the subject  was entered is before the block end date, then use the result date.
 
99990015
C8
    Source of Funding {SDREGA-1} or {SREGB-1} Qualification / Subject Registration. IAHSRCFUND from IAHSUB


 




History of Changes

Date System Version By Whom Job Description
17-Jul-2009 v01.0.0.0 Magda van der Westhuizen
t160160 New manual format.
5-Sep-2009 v01.0.0.1 Melanie Zeelie
t161683 Add info excluded from statutory reporting
30-Jun-2011 v01.0.0.2 Hermien Hartman t166882 Updated according to Peter Rombel's input.
21-JUL-2015 v03.0.0.0 Bea Swart t204758 Add Legal Residence Status value Q to COUR field 13
26-Jun-2019
v04.1.0.0
Frans Pelser
t237245
Update Academic Structure references