ACT and SAT Test Score Process

An automated process runs twice a week at 9:10 am (Tuesday and Friday) to process test scores through AppWorx.  Test Score requests are generated in three different ways:  Manual Request (SWATSTA), automatically generated using the Admissions Checklist, and unsolicited from student.  All test scores are received from a state repository at Northwest Regional Data Center (NWRDC).  All test scores can be viewed in Banner on the Test Score Information (SWATEST) form.  Test scores are not available outside of Banner. 

 

Technical Details:

ACT and SAT

1. Delete any existing files
1. Relative Path: appworx-wd/tmp
2. Name of file: ADM_ACT_SAT_REQUEST_M.txt
2. Generate ACT and SAT Request File using Admissions Checklist
1. Oracle Directory: NFEDI_TMP
2. File Name: ADM_ACT_SAT_REQUEST_M.txt
3. Generate Request

Select all students with an Admission Request type of SAT or ACT Scores (TSTS) on the Application Checklist on the Admissions Applications (SAAADMS) form where the Entry Term is greater than or equal to the current term and the Received Date is blank (NULL) or a previous request has been unfilled for three days.  A record is inserted into the Generate Test Score Requests Table (SWBTSTA).

4. Build Request File

Select all files from the Generate Test Score Requests Table (SWBTSTA) and create file.  File is saved to the specified path and file name.

5. Clean Request File

Update First Request and Last Request dates on checklist for generated requests.  Insert all records in the Generate Test Score Requests table into the Generate Historical Test Score Requests table.  Delete records from the Generate Test Score Requests table. 

3. Send file to NWRDC
1. Destination Machine Name: nwrdc.fsu.edu
2. Fully Pathed Local File:

/home/norf/nfedi/appworx-wd/tmp/ADM_ACT_SAT_REQUEST_M.txt

3. Fully Pathed Remote Filename: FTP.RQ09841
4. NERDC Job Submission: /home/norf/nfjobs/cntrl/wso2002 

ACT

5. Get ACT file
1. Relative Local path: appworx-wd/tmp
2. Local File Name: none
3. Relative Remote path: Test-Scores/ACT-Scores
4. Remote File Name: ACT*.txt
6. Create backup copy of ACT file
1. Relative Local path: appworx-wd/tmp
2. Local File Name: ACT*.txt
3. Relative Remote path: Test-Scores/ACT-Scores/act_backup_files
4. Remote File Name: none
7. Remove ACT file from server
1. Relative Local path: appworx-wd/tmp
2. Local File Name: none
3. Relative Remote path: Test-Scores/ACT-Scores/
4. Remote File Name: ACT*.txt
8. Remove any empty files
1. Relative path: nfedi/tmp
2. File name: ACT*.txt
9. Send email if file does not exist
1. Recipient: #edi_srvr_mail_id
2. Subject: ACT Files files in : #edi_srvr_mail_id
3. Body: Did not receive ACT Files from Test Server
4. Sender: AppWorx
10. Load ACT test file (uses SQL Loader)
1. Optional Path: none
2. Control File Name: adm_edi_ld_act_files.ctl
3. Data File Name: ACT*.txt
11. Remove test files from local server
1. Relative Path: appworx-wd/tmp
2. Name of File: ACT*.txt

ARP – ACT - automated transaction receive process

12. Clean Up tables used in ARP process

Prepare for new run of the automated transaction receive process by cleaning up temporary values

1. wp_auto_recv_prep_db

Delete records in tables used during the matching process and error logging (gwrfpeo, gwrspeo, gwrsrun, swrpmch, swttrps, swrterr, swbttyp where swbttyp_type in ‘SUSPEND’, ‘NO_SSN’, AP_SUSPEN’)

1. wp_uack_purge

Delete records in the unapplied acknowledgement table that are 180 days old (swruack where swrrule_business_area = ‘UACK PURGE’ and swrrule_rule = ‘PURGE TIME’)

2. wp_process_non_ansi_tsts_db
1. wp_load_multiple_scores_db

Creates new records into the zero tables of SAT and GMAT if additional tests exists

2. wp_del_act_sat_dup_recs_db

Delete any duplicate records for SAT and ACT from SWRSAT0 and SWRACT0

3. wsakpact.process_swract0

ACT data in the SWRACT0 table is inserted into the following tables:

SHBHEAD - EDI Master Header Table

SHRHDR4 – EDI Document Header Table

SHRIDEN – EDI Identification Table

SWRACT1 – ACT Interface Table

If data is successfully inserted, then it is deleted from the SWRACT0 table.  Error messages are logged as necessary.

4. wsakpclt.process_swrclt

CLAST data in the SWRCLT0 table is inserted into the following tables:

SHBHEAD - EDI Master Header Table

SHRHDR4 – EDI Document Header Table

SHRIDEN – EDI Identification Table

SWRCLT1 – CLAST Interface Table

If data is successfully inserted, then it is deleted from the SWRCLT0 table.  Error messages are logged as necessary.

5. wsakpgmt.process_swrgmt0

GMAT data in the SWRGMT0 table is inserted into the following tables:

SHBHEAD - EDI Master Header Table

SHRHDR4 – EDI Document Header Table

SHRIDEN – EDI Identification Table

SWRGMT1 – GMAT Interface Table

If data is successfully inserted, then it is deleted from the SWRGMT0 table.  Error messages are logged as necessary.

6. wsakpgre.process_swrgre0

GRE data in the SWRGRE0 table is inserted into the following tables:

SHBHEAD - EDI Master Header Table

SHRHDR4 – EDI Document Header Table

SHRIDEN – EDI Identification Table

SWRGRE1 – GMAT Interface Table

If data is successfully inserted, then it is deleted from the SWRGRE0 table.  Error messages are logged as necessary.

7. wsakpsat.process_swrsat0

SAT data in the SWRSAT0 table is inserted into the following tables:

SHBHEAD - EDI Master Header Table

SHRHDR4 – EDI Document Header Table

SHRIDEN – EDI Identification Table

SWRSAT1 – SAT Interface Table

If data is successfully inserted, then it is deleted from the SWRSAT0 table.  Error messages are logged as necessary.

3. wp_create_recv_workflow_db

Create ‘receive’ transaction and steps for each new electronic transcript. Inserts records into SWBOTRN – Open Transactions from SHBHEAD, SHRHDR4, and SWBOTRN (where transaction number is NULL)

1. wp_required_values_check_db

Selects ‘required values’ step values and inserts error records where the values are not within the desired range for each type of data (test scores, dates, high school codes, post secondary codes, course numbers, etc.)

1. wp_check_translatable_db

For certain types of transactions validates translatable values for each document sequence number as follows:

130, 146 - date of birth, graduation date

1, 2, 3, 4, 5, 130 – state code

130 – session begin date, session end date, class rank date, nation code

130 and Post secondary – student level, honor code for degree, date code

130, 131 – rank date

1 – grade level for ACT

3 – nation code for GMAT

4 – nation code for GRE

5 – grade level and nation code for SAT

147 – follow up action code and reject reason

146 or 130 and post secondary – eligible to return date

1. wp_check_ap_values

Validates translatable values for each document sequence number for each AP test score (date of birth, educational level, test dates, test scores,  recruit term, course term, rules, scores within range, ) in the EDI table (SHBHEAD)

2. wp_check_clast_names

Checks all CLAST component test names are valid

2. wp_match_to_pidm_db

Determines if student already exists in Banner

1. wp_potential_match_db

Insert records that are not an exact match into SWRPMCH table from SWTTRPS and GWRFPEO; update step on SWROTRS to ‘MATCH’

2. wp_main_no_match_db

For records that do not have a match, a person record is created

3. wp_exact_match_db

Update the ‘MATCH’ step in SWROTRS to ‘Y’; updates pidm on SWBOTRN and SHBHEAD for the document sequence number; update biographical information where applicable; if multiple exact matches occur, then change ‘MATCH’ step to ‘N’ for further review as potential match

4. wp_evaluatable_db
1. wp_evaluatable_type_130_db

Update evaluatable step to ‘Y’ for high school transcript

2. wp_evaluatable_in_transreq_db

Determines if transcript request can be processed

3. wp_evaluateable_testscore_db

Determines if test scores can be processed

4. wp_evaluate_db

Call appropriate “evaluate” procedure depending on transaction type and loads data to appropriate tables

1. wp_archive_edi_trans_db

Archive records from the following tables:

SWBOTRN – Open Transactions

SWROTRS – Steps of Open Transactions

SWRO147 – Transcript Request Acknowledgments

SWBOSRQ – Open (not completed) transcript requests (Open Send Requests)

Records are copied to the following tables (in order)

SWBCTRN – Completed Transactions

SWRCTRS – Transaction Steps for completed transactions

SWRC147 - Transcript Request Acknowledgments

SWBCSRQ - Closed Transactions for sent transcript requests

1. wp_purge_edi_tables_db

Purge EDI records for each document sequence number

SAT

13. Get SAT file
1. Relative Local path: appworx-wd/tmp
2. Local File Name: none
3. Relative Remote path: Test-Scores/SAT-Scores
4. Remote File Name: SAT*.txt
14. Create backup copy of SAT file
1. Relative Local path: appworx-wd/tmp
2. Local File Name: SAT*.txt
3. Relative Remote path: Test-Scores/SAT-Scores/sat_backup_files
4. Remote File Name: none
15. Remove SAT file from server
1. Relative Local path: appworx-wd/tmp
2. Local File Name: none
3. Relative Remote path: Test-Scores/SAT-Scores/
4. Remote File Name: SAT*.txt
16. Remove any empty files
1. Relative path: nfedi/tmp
2. File name: SAT*.txt
17. Send email if file does not exist
1. Recipient: #edi_srvr_mail_id
2. Subject: SAT Files files in : #edi_srvr_mail_id
3. Body: Did not receive SAT Files from Test Server
4. Sender: AppWorx
18. Load SAT test file  (uses SQL Loader)
1. Optional Path: none
2. Control File Name: adm_edi_ld_act_files.ctl
3. Data File Name: SAT*.txt
19. Remove test files from local server
1. Relative Path: appworx-wd/tmp
2. Name of File: SAT*.txt

ARP – SAT - automated transaction receive process

20. Same steps as for ARP – ACT (number 12 above)
21. Fix Test Scores Names and Addresses

For any records (SPRIDEN) created since the last processing date, fix the last name, first name, and middle name to proper case.  In addition, fix the street line 1, street line 2, city, and zip code.  (wksgdem.p_fix_case)

22. CLAS Exempt

Process all students to determine if they can receive a CLAS Exemption.  This includes both SAT and ACT scores.  In addition, some courses may be used to exempt parts of the CLAS.  See the University Requirements College Level Academic Skills (CLAS) Requirement Exemption process for detailed information. (wp_clast_exemption_upd_db)

Chain Details

 

NSTU_ACT_SAT_TESTSCORE_PROCESS

 

WSO2001

      REQUEST-BUILD

            ADM_EDI_REQ_ACT_SAT_SCORE_C

                  ADM_DEL_OLD_REQ_FILE_M

                        Alias - COM_DELETE_FILES_IN_AGENT_M

                              /home/norf/nfedi/appworx-wd/shells/com_delete_files_in_agent_m.shl

                  ADM_ACT_SAT_REQUEST_M

                              adm_act_sat_request_m

                                    WP_BUILD_SWBTSTA_RQST_DB

                                    WP_BUILD_SWBTSTA_FILE_DB

                                    WP_CLEAN_SWBTSTA_FILE_DB

FTP-OUT-REQUEST

      UNFFTPP (File Transfer – Passive FTP)

            /home/norf/nfjobs/exec/unfftpa

SUBMIT

      UNFSUBM (NERDC Job Submission)

            /home/norf/nfjobs/exec/unfsubm

 

WSO2002

      LOAD-ACT         

            ADM_EDI_LD_ACT_FILES_C

                  ADM_EDI_GET_TEST_FILES

                        /home/norf/nfedi/appworx-wd/shells/com_ftp_m

                  ADM_EDI_BKP_TEST_FILE

                        /home/norf/nfedi/appworx-wd/shells/com_ftp_m

                  ADM_EDI_RMV_TEST_FILE_FRM_SRVR

                        /home/norf/nfedi/appworx-wd/shells/com_remv_zerosize_file_m.shl

                  ADM_EDI_RMV_ZERO_TEST_FILE

                        /home/norf/nfedi/appworx-wd/shells/com_remv_zerosize_file_m.shl

                  ADM_EDI_SEND_NOTESTFILE_MAIL

                        /home/norf/nfedi/appworx-wd/sql/com_send_mail_m

                           wsakemal.wp_send_email_db

                  ADM_EDI_LOAD_TEST_SCORE

                        /home/norf/nfedi/appworx-wd/shells/adm_edi_multi_sql_loader_m.shl

                  ADM_EDI-RM_TEST_FILE_FRM_LCL

                        /home/norf/nfedi/appworx-wd/shells/com_delete_files_in_agent_m.shl

      ARP-ACT

            ADM_EDI_EXEC_ARP_M

                  /home/norf/nfedi/appworx-wd/sql/adm_edi_exec_arp_m

                        wp_auto_recv_prep_db

                              wp_uack_purge_db

                              wp_process_non_ansi_tsts_db

                              wp_create_recv_workflow_db

                              wp_archive_edi_trans_db

      LOAD-SAT

            ADM_EDI_LD_SAT_FILES_C

                  ADM_EDI_GET_TEST_FILES

                        /home/norf/nfedi/appworx-wd/shells/com_ftp_m

                  ADM_EDI_BKP_TEST_FILE

                        /home/norf/nfedi/appworx-wd/shells/com_ftp_m

                  ADM_EDI_RMV_TEST_FILE_FRM_SRVR

                        /home/norf/nfedi/appworx-wd/shells/com_remv_zerosize_file_m.shl

                  ADM_EDI_RMV_ZERO_TEST_FILE

                        /home/norf/nfedi/appworx-wd/shells/com_remv_zerosize_file_m.shl

                  ADM_EDI_SEND_NOTESTFILE_MAIL

                        /home/norf/nfedi/appworx-wd/sql/com_send_mail_m

                              wsakemal.wp_send_email_db

                  ADM_EDI_LOAD_TEST_SCORE

                        /home/norf/nfedi/appworx-wd/shells/adm_edi_multi_sql_loader_m.shl

                  ADM_EDI-RM_TEST_FILE_FRM_LCL

                        /home/norf/nfedi/appworx-wd/shells/com_delete_files_in_agent_m.shl

      ARP-SAT

            ADM_EDI_EXEC_ARP_M

                  /home/norf/nfedi/appworx-wd/sql/adm_edi_exec_arp_m

                        wp_auto_recv_prep_db

                              wp_uack_purge_db

                              wp_process_non_ansi_tsts_db

                              wp_create_recv_workflow_db

                              wp_archive_edi_trans_db

 

WSU5010 - Fix test score name and addresses

 

WSO2002

CLAST-EXEMPT

      ADM_CLAST_EXMPT_TSCORE_M

AP Test Score Process

The following nightly processes are run to load Advanced Placement (AP) scores into the Banner system:

1. WSO2015 – Retrieve all available AP files from the ETS server via SFTP using a Java program.  Then delete files that have been previously stored in the backup and copy all others to a temporary location for processing.
2. WSO2010 – Decrypt all received files.
3. WSO2020 – Combine all received files into one file for processing.
4. ADM_LOAD_AP_SCORES_M – Load into Banner (ARP process must be run in order to view them in Banner).
5. WSO2021 – Remove the file used for processing from the temporary directory and move the original files to a final back up directory.

Technical Notes:

	AppWorx Chain		NSTU_TEST_SCORES, NSTU_AP_TESTSCORE_LOAD
	Job		WSO2015, WSO2010, WSO2020, WSO2021
	Package		n/a
	Procedure		n/a

GMAT Test Score Process

GRE Test Score Process

Math and Writing Placement Test Scores

Students take the Math Placement Test and the three Writing Placement Tests (WPC – Concepts, WPG – Grammar, and WPS – Clarity) in Blackboard.  The scores are moved from Blackboard into a temporary table.  This nightly process selects all math and writing placement test scores from the temporary table and inserts or updates them into the Test Score Information (SWATEST) form in Banner.  Once the test scores are updated or inserted they are deleted from the temporary table.

 

Technical Notes: 

Technical Notes

	AppWorx Chain		NSTU_TEST_SCORES
	Job		WSO5035
	Package		wksotst
	Procedure		p_bb_wtbtmat

Set Test Score Purpose Codes

The University of North Florida must report which test was used for admission.  On the Test Score Information (SWATEST) form there is a Purpose field that UNF is using to identify which test was used for admission.  The user must enter a Test identify of TIUG (Test Indicator Undergraduate) or TIGR (Test Indicator Graduate) with a Score indicating the test to be used.  The codes are as follows:

 

                     A for ACT

                     S for SAT

                     G for GRE

                     H for Unofficial GRE

                     R for Revised GRE

                     P for Revised Unofficial GRE

                     B for GMAT 

                     C for Unofficial GMAT

                     M for Miller

 

This nightly process selects students with activity on the Test Score Information form since the last time the process ran.  The process then updates the Purpose code based on the best score for the test indicated by the Test identifier of TIUG and TIGR.

 

Technical Notes: 

Technical Notes

	AppWorx Chain		NSTU_TEST_SCORES
	Job		WSO5010
	Package		wksotst
	Procedure		p_adm_tst_check

Update ACT/SAT Test Dates

This nightly process updates test dates for ACT and SAT scores.  The process selects all students whose ACT and SAT test dates are the same.  The ACT date is updated to the 15^th of the month and the SAT date is updated to the 1^st of the month.  A file is created and sent to the Y drive of users on the distribution list.  An email is sent to the distribution list (sortest-chg-file) to alert them that the file is available.

 

Technical Notes: 

Technical Notes

	AppWorx Chain		NSTU_TEST_SCORES
	Job		WSO5020
	Package		wksotst
	Procedure		p_update_test_date