Federal Work Study (FWS) Reporting Document Creation & Submission

Context

This article documents the process for creating and submitting the Federal Work Study (FWS) reporting document. It explains how to isolate student IDs, summarize earnings, lookup ISIR transaction numbers, run the file parser, and generate the outbound XML file for COD transmission.

Steps

1. In the past, the systems analyst has worked this process in August/September. Typically the compliance manager or operations manager provides an Excel file detailing FWS disbursements by student by pay period for the calendar year. Part of the challenge of reporting is that the calendar year report encompasses two aid years. e.g. Calendar year 2025 covers the spring term (2253 term/2025 aid year) and the fall term (2268 term/2026 aid year).

2. Open the dataset/Excel file. The ID column in the original data is often a concatenation of the student's ID, a hyphen, and their last name. Use Text to Columns with spaces as the delimiter to isolate the numerical Student ID.

3. Insert a pivot table using the isolated Student ID as the rows and Expenses as the values (set to Sum). This derives the student's total expense for the calendar year with no duplicate IDs.

4. I prefer to copy the pivot table results and Paste as Values into a separate Excel tab because the data remains fixed and isn't linked to formulas or pivot tables that can shift.

5. On a new Excel sheet prepare the layout for your primary import tab. The final import file requires seven columns in this order (do not include a header row):

   • Column A: Student ID

   • Column B: Calendar Year (e.g., 2025)

   • Column C: (Leave Blank)

   • Column D: Institution (UOD01)

   • Column E: Aid Year (populated in step 9)

   • Column F: ISIR Transaction Number (populated in step 9)

   • Column G: Calendar Year Expense Amount

6. From the tab created in step 4, copy the static list of student IDs and expense amounts into Column A & Column G of the import sheet respectively. Populate Column B (Aid Year) and Column D (UOD01) for each student ID.

7. To determine Columns E and F, log into the PeopleSoft QA environment (SAQA) and navigate to Query Manager. Search for the query BPS_FWS_MAX_TRAN_NUMBER.

8. Edit the criteria tab of BPS_FWS_MAX_TRAN_NUMBER to ensure both aid years that fall within the reporting calendar year are included. Save and run the query, then download the results to Excel.

9. In the query export file, sort the data so that the most recent aid year appears first for each student. To play it safe, build a concatenated helper column (Student ID + Aid Year + Transaction Number) and sort descending. Return to your working import file and use a VLOOKUP to match the Student ID against the query export:

   • Column E: Pull the Aid Year.

   • Column F: Pull the ISIR Transaction Number.

10. Review the dataset for any #N/A or error values (typically fewer than three students). To resolve these, navigate to Process ISIR Corrections in PeopleSoft (Menu > Financial Aid > File Management > Process ISIR Corrections). Search for the student ID starting with the highest aid year to locate their processed ISIR transaction number. If no record is found in the highest aid year, check the prior aid year. Update the missing values manually in your working sheet so no errors remain.

11. Save the finalized import file as a CSV file. Pick an easy to remember file name (using all lowercase for the .csv extension), as we will need it in later steps.

12. In PeopleSoft SAQA, navigate to Menu > UDel Customizations > UD File Load Utility. Select your CSV file and click Upload, then hit Save.

13. Navigate to Menu > Set Up SACR > System Administration > Utilities > File Parser > Run File Parser. Use the run control FWS_REPORT_PARSER. Set the File Mapping ID to FWS reporting to COD and enter the server file path along with your exact CSV file name (ensuring .csv remains lowercase). Select Run and monitor progress in the Process Monitor.
    
    

14. Verify that the data loaded correctly by navigating to Menu > Financial Aid > File Management > COD Full Participant > Federal Work Study Data. Look up a few student IDs for the calendar year and confirm that the imported expense amount, aid year, and ISIR transaction number match your working file.

15. Navigate to Menu > Financial Aid > File Management > COD Full Participant > Generate Federal Work Study. Use the run control FWS_REPORT_PARSER. Fill in the Institution, Calendar Year, Attending School Routing ID, Schema Version, and File Path with intended name of the file (ending in .xml in lowercase).
    
    

    Note: If the Attending School Routing ID shows an "Invalid Value" error, update your User Defaults under SACR to include a default Aid Year.

16. Check the FWS Outbound and Population Select boxes. Under Selection Tool, select PS Query and enter the population selection query name. Edit the query's criteria to ensure the Calendar Year matches the current reporting year so extra or unintended years are not transmitted. Preview the selection results, then select Run.

17. Email IT Processing to request assistance transmitting the generated XML file to the COD mailbox. In the email you should let them this know this is a CRCBMYIN message class and provide the file path location (or an encrypted copy of the xml file).

    

    Note: In a prior cycle a system bug inadvertently updated the year in the XML header. The header year should match the accepted schema version year rather than the calendar year being submitted. If COD returns an invalid file error, we manually corrected the header before resubmitting.

18. Request a copy of the response file from IT once received, and notify the operations manager that the FWS report submission is complete.