The SPRG ODS Reconciliation process is designed to analyze the SPRG data in CALPADS and provide the submission records necessary to match CALPADS to your local data.  The SPRG ODS Reconciliation is not a required process, but can be used to align CALPADS records with Aeries.

Note:  It is important that the district’s enrollment and program data are correct and up-to-date in Aeries before the process is begun.  The program data in Aeries will not be changed during this process. 

Districts will need to download a Student Program ODS Download file from CALPADS and import it into Aeries.  Aeries will generate three new files to send back to CALPADS with the corrections needed.

Below is a quick summary of the process.  Please read the entire article before starting the SPRG ODS Reconciliation.  Full Details will follow.

• It is recommended to run CALPAD ODS 1.17 and the ODS 5.1 reports for comparison before and after the process
• Log into CALPADS to request and download the full CALPADS Student Program ODS Download- SPRG file
• Log into Aeries to import the CALPADS Student Program ODS Download - SPRG file into Aeries
• Click on the Load, Evaluate and Create New SPRG Files button.  Three new SPRG files will be created. A file with Delete records, another with Add records and a new SPRG file
• Upload the three new files to CALPADS

IMPORTANT NOTES

It is very important that the Delete file is sent up first to CALPADS followed by the Add and then the SPRG file. Each file needs to have a Posted status before uploading the next file.  All three files should be uploaded and Posted in the same day if possible. The program data in CALPADS will not be accurate until the process is fully complete. This is especially critical during the LEA SBAC testing or submission reporting window. 

Per CALPADS, the SPRG ODS file is a snapshot as of 10:00pm the previous day.  Therefore, once the SPRG ODS file has been downloaded from CALPADS, we recommend that no modifications be made to enrollment or program data in Aeries or CALPADS until the SPRG Reconciliation process is complete.  Please keep in mind that since the SPRG ODS file is a snapshot as of 10:00pm the previous day, any enrollment update files (SENR, SINF and SPRG) that have been posted, need to be in complete status the previous day before downloading the SPRG ODS file. 

Once the SPRG ODS process is complete, the enrollment files can be uploaded as usual.

Full Instructions for the Aeries SPRG ODS Reconciliation Process

STEP 1 - CALPADS SPRG ODS DOWNLOAD

• In CALPADS, request the Student Program ODS Download - SPRG file:
  • Extracts > ODS Extracts > Record Type of Student Program ODS Download - SPRG
  • Select "Date Range".  it defaults to "Academic Year", change to "Date Range"
  • Select All Schools
  • DO NOT select the “Active Student” option
  • Enter an Enrollment Start Date of July 1st that is 10 years prior to the current school year (i.e. 07/01/2012)
  • Enter an Enrollment End Date of today

• "Education Program Code" should say "All"
• Enter an Extract File Name
• Click on the Request File button

• Retrieve the Student Program ODS Download file:
  • Extracts >ODS Extracts
  • Download the  Student Program ODS Download file to a location on your hard-drive 

STEP 2 – IMPORT CALPADS SPRG ODS FILE INTO AERIES

• In Aeries:
  • Log into Aeries in the current year
  • Access the CALPADS Extracts page
    • Make sure all options are set correctly and all code value translations are correct
    • Verify the Schools are selected correctly
    • Go to the ODS Reconciliations, SPRG Import tab
      • Click on the SPRG ODS tab
      • Click the Select SPRG ODS Extract File button and browse to the Student Program ODS Download file you downloaded from CALPADS
      • Click on Open

STEP 3 – EVALUATE RECORDS & CREATE NEW SPRG FILES

• Click the Load File, Evaluate Records and Create New SPRG Files button

STEP 4 – REVIEW THE NEW SPRG FILES

• Three new SPRG files will be created and display in the File Download section. 
  • Delete -file includes all records with the transaction code of D for Delete
  • Add - file includes all records that will add a new record or change an existing record.
  • SPRG – a new SPRG file based for all students
• The files will be created with the naming conventions as below.  DEL and ADD will be added to the end of the file name.

File with the Delete records - 2022_03_17_08_55_52_SPRG_DEL.txt

File with the Add/Edit records - 2022_03_17_08_55_52_SPRG_ADD.txt

SPRG File - 2022_03_77_08_55_52_SPRG.txt

• Review the files.  Open the Delete file and review the records.  Delete records will be created for students with program records with different dates in Aeries than in CALPADS.  A Delete record and a new Add record will be created to adjust the dates.  Note:  If there are 0 records in the file, it does not need to be uploaded to CALPADS.

• The SPRG file will only include active program records for the current year.

  
  

PROGRAM DATE NOTES

Participating Programs (Membership Category - 3) – Program dates will be extracted unless the program dates are out of the school enrollment ranges.  If the program dates are out of the school enrollment ranges, the school enrollment dates will be extracted.

Eligibility (Membership Category - 1) – Program dates will be extracted.

• The program start date can be before the enrollment date, but the end date has to be within the enrollment date range.
• If the student’s school enrollment exit date is before the program date, the exit date will be extracted.
• Eligibility programs can have overlapping records if the school School of Attendance is different.

For FRE (NSLP/Free and Reduced Meals) - Only FRE records with current year dates will be extracted.  FRE records without any Eligibility dates will not be extracted. 

SPECIAL NOTES

Records with the following inactive Education Program Codes will be included in the CALPADS ODS SPRG file.  No action will be taken with these records during the SPRG Reconciliation and they will not be included in the files that are created.

Education Program Code 101 - 504 Accommodation Plan:  Refer to the following documents for more information on 504 Plans in Aeries:  504 Plans - How does Aeries extract 504 Plan records?

Education Program Code 113 – California Partnership Academy:

• CALPADS will only accept valid California Partnership ID. If there are invalid codes or blanks in the CALPADS SPRG ODS file, Aeries will extract the student’s code from PGM.CPA if available.

Education Program Code 135 – Title 1 Part C Migrant:  For the Migrant Student ID, CALPADS will only accept the Migrant Student ID from CDE that is exactly 11 characters with the first two characters beginning with “06”. 

If the alphanumeric Migrant Student Directory number (MSD) is included in the CALPADS SPRG ODS file, Aeries will look for the 06 Migrant Student ID and extract it in place of the alphanumeric number. 

Both fields are in Aeries on the Secondary Student Data (SSD) tab on the Students form.

The State Migrant ID (SSD.MID) should be exactly 11 characters with the first two characters beginning with “06”.  The COE Migrant ID (SSD.MIG) is an alphanumeric number.

If the State Migrant ID (SSD.MID) is not available, Aeries will extract the COE Migrant ID but format the existing number as an 11 character number starting with “06”

STEP 5 – UPLOAD FILE TO CALPADS 

Use the CALPADS system to upload the resulting SPRG files:

1. Upload the Delete file first – this file includes all records with the transaction code of D for Delete.
2. When the Delete file has a status of Posted upload the Add file.
3. Review errors. See below for a list of errors and problem solving information.
4. When the Add file has a status of Posted, upload the SPRG file.  

NOTE:  It is very important that the Delete file is sent up first to CALPADS followed by the Add and then the SPRG file. Each file needs to have a Posted status before uploading the next file.

IMPORTANT - All three files should be uploaded and completed in the same day.  

Check the File Status in CALPADS to verify the record status.

KNOWN ERRORS

Import Message of 0 Records or Many Errors after the Add file:

• Verify that the original CALPADS SPRG ODS .txt file is imported
• Do not import the CALPADS SPRG ODS file after it has been opened and saved in excel.  This could result in leading zeroes being dropped from data in the file.

GERR0002 – Value not found in Reference Set

Verify the Rejected Records file for the invalid value.  Correct the value.

GERR0005 - LEA does not have ownership of student enrollment to make this update.  

1. This error could be a result of districts posting enrollment update files after the SPRG ODS file was downloaded.  Or the districts did not wait until the next day to download the SPRG ODS file.  New students, students who have changed schools or new program information for existing students may not yet be posted to CALPADS, but included in the SPRG Reconciliation files. 

If this error is generated - Verify the students enrollment in Aeries and in CALPADS

2.  If districts receive many GERR0005 errors, verify the CALPADS Manage Code Table.  Verify the Reference Code for Membership Category.  The local codes of 1 or 3 should not be translated to other state codes.  Local code of 1 and 3 for Membership Category should match the State code of 1 and 3. 

SPRG0070 - Overlapping Program Records - This error is caused by a student with an Education Program Code (Excluding NCLB Title 1 Part A Basic Targeted - code 122) where a student has an existing OVERLAPPING program record with the SAME Education Program code and SAME school in CALPADS.  

Verify the student’s enrollment in Aeries

Verify the program start and end dates.  The program record may have invalid or missing dates.

A student should not have 2 open records in Aeries.  Here is an example of 504 Plan records that will generate the SPRG0070 error:

SPRG0076 – Education Program Membership Start Date Less Than Birth Date – Verify the student’s birthdate and Education Program Start Date.  Correct the date in Aeries.

SPRG0149 – Invalid California Partnership Academy ID – Student could have multiple open program records for code 113 with different California Partnership Academy IDs.

SPRG9999 – Record(s) Will Pass this Validation Once All Other Fatal Errors Are Corrected For Student - When a batch SPRG file containing program update records is submitted, the system groups the submitted program update records for an SSID into a cluster. If this cluster of program update records contains one or more records which produced Fatal CALPADS validations, all of the records contained in the cluster are rejected.

Once all the other Fatal Errors are fixed for the students, the SPRG9999 errors will be resolved.

PROBLEM SOLVING

If data has been changed in Aeries but not in CALPADS due to error corrections, and the Add file was not posted to Complete Status in CALPADS, new files can be generated from Aeries and reposted to CALPADS . 

If data has been changed in CALPADS due to error corrections, a new SPRG ODS file needs to be downloaded the next day after the changes had been made and the process can be run again.  If the new Delete file has 0 records, it is not necessary to upload to CALPADS.

The Aeries SPRG ODS Reconciliation process can be run multiple times.  Each time the process will analyze the SPRG data in CALPADS and generate the submission records necessary to match CALPADS to your local data.  Subsequent files will have less and less records to post back to CALPADS.