Data Reconciliation

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 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

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 Possible Data Values: FullTime, ThreeQuarterTime, HalfTime, LessThanHalfTime, None
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
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

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