What is Data Reconciliation?
Data Reconciliation is the process whereby student, program, and cost data are compared/reconciled between the client's SIS and Regent Award. This process is utilized when a school is implementing Regent Award.
The school will provide to Regent an export of student, program, and cost data. For reconciliation purposes, the data must be for the same comparison period, e.g. AY1. Regent can help define what elements are needed for comparison purposes.
The export from the school will be provided as a Quoted CSV File. The CSV file will contain headers matching the field names described in the Detailed Breakdown of Data Fields section.
The primary reconciliation output will be a result set, identifying where there are differences between the source (the school's SIS) and destination (Regent Award Configuration/Student Data)
Detailed Breakdown of Data Fields
The following data points will be compared between Regent Award and the school's systems. This is the detailed field breakdown. The CSV Headers in the delivered file from the school should match the field names below.
1) Academic Structure Data
Scope: This reconciliation will be performed for the Student's Current Active Program against the current list of active programs. This is a configuration-level reconciliation. No student data is compared.
Display Name | Column Name in CSV | Required/Optional |
External Program Id | externalProgramId | required |
Program Name | programName | required |
Program Completion Units | programCompletionUnits | required |
2) Student Record Data
Scope: This reconciliation will be performed for the Student's First Academic Year in Regent Award. Both new and continuing students should be in the export of data delivered by the School.
Display Name | Column Name in CSV | Required/Optional | Notes |
|---|---|---|---|
Student ExternalID1 | externalId1 | required | |
Student Last Name | lastName | optional | |
Program Name | programName | optional | |
Program External Id | programExternalId | required | |
Program Completion Units | programCompletionUnits | optional | |
Program Units Override | programUnitsOverride | optional | This column represents the number of units completed towards the program |
Grade Level | gradeLevel | optional | This column is for the First Enrolled Payment Period |
Enrollment Level Type Code | enrollmentLevelTypeCode | optional | This column is for the First Enrolled Payment Period |
Dependency Status | dependencyStatus | optional | This column is for the First Enrolled Payment Period Possible Data Values: Independent, DependentThis column is for the First Enrolled Payment Period Possible Data Values: FullTime, ThreeQuarterTime, HalfTime, LessThanHalfTime, None |
Academic Year | academicYear | optional | This is the year for AY1. Example: 2020 |
Academic Year Start Date | academicYearStartDate | optional | This is AY1 only |
Academic Year End Date | academicYearEndDate | optional | This is AY1 only |
3) Course Data
Scope: This reconciliation will be performed for courses with the Student's Current Active Program.
Note: Additional analysis will be performed by existing reconciliation scripts Regent has for SBL Course Data.
Display Name | Column Name in CSV | Required/Optional | Notes |
|---|---|---|---|
Student ExternalID1 | externalId1 | required | |
Program External Id | programExternalId | required | |
Term External Id | termExternalId | *required | *Required for term-based courses only |
External Course Identifier | externalCourseIdentifier | required | |
Course Start Date | courseStartDate | required | |
Course End Date | courseEndDate | required | |
Registered Units | registeredUnits | required | |
Completed | completed | optional | |
Counts Toward Progression | countsTowardProgression | required | Possible Data Values: true, false |
Quality Points | qualityPoints | optional | |
Transfer | transfer | optional | Possible Data Values: internal, external, blank |
Enrolled Date | enrolledDate | required | |
Withdrawal Date | withdrawalDate | optional | |
Program Applicable | programApplicable | required | Possible Data Values: true, false |
4) Cost Data
Scope: This reconciliation will be performed for the Student's AY1. You may break this into groupings, by Student-AY-PaymentPeriod or simply Student-AY.
Student External Id 1 | externalId1 | required | |
Student Last Name | studentLastName | optional | |
Dependency Statuses | dependencyStatuses | optional | |
Program External Id | programExternalId | required | |
Program Name | programName | optional | |
Academic Year (AY1) | academicYearAY1 | required | |
Academic Year Start Date | academicYearStartDate | required | |
Academic Year End Date | academicYearEndDate | required | |
Tuition Category | tuitionCategory | required | Examples: 'Tuition Tier 1', 'Standard Tuition' |
Tuition Amount | tuitionAmount | required | |
Room and Board Category | roomAndBoardCategory | optional | |
Room and Board Amount | RoomandBoardAmount | *required | Only required when roomAndBoardCategory is provided |
Personal Expenses Category | personalExpensesCategory | optional | |
Personal Expenses Amount | personalExpensesAmount | *required | Only required when personalExpensesCategory is provided |
Bank Fees | feesAmount | required | |
Other Expenses Category 1 | otherExpensesCategory1 | optional | |
Other Expenses Amount 1 | booksAmount | *required | Only required when otherExpensesCategory1 is provided |
Other Expenses Category 2 | otherExpensesCategory2 | optional | |
Other Expenses Amount 2 | transportationAmount | *required | Only required when otherExpensesCategory2 is provided |
Other Expenses Category 3 | otherExpensesCategory3 | optional | |
Other Expenses Amount 3 | roomandBoardAmountwithParent | *required | Only required when otherExpensesCategory3 is provided |
Other Expenses Category 4 | otherExpensesCategory4 | optional | |
Other Expenses Amount 4 | totalCostAmount | *required | Only required when otherExpensesCategory4 is provided |
Other Expenses Category 5 | otherExpensesCategory5 | optional | |
Other Expenses Amount 5 | otherExpensesAmount | *required | Only required when otherExpensesCategory5 is provided |
5) Export Student Transaction (EST)
Scope: This reconciliation does not require any data from the school. Regent will execute in order to provide a list of negative transactions for analysis by the school. This enables the team to analyze the source of potential refunds after batch packaging runs for students. Examples of the attributes that could result in unanticipated refunds include:
ISIR Grade Level Mismatches
Independent Students near or over aggregate limits
Students over PC Units
Students with loans with LTHT enrollment