Refund Rate Setup

This article was imported from Kenobi 6/17/2022

We have an automated way to add the refund rates for Banner.  Without this, it is a several hour job that is extremely tedious.

The process that runs it is tzpfees.shl which can be run in jobsub.

Before it can be run, the data must be set in the SOATERM form and the Parts of Term subform under it.

For several years, the following table has been used to decide how the various Registration Status (RSTS) codes will be processed.  See below for descriptions of the Refund_type, Begin_RFTP_code, and End_RFTP_Coce:

(This table is actually SZBRFDT.  See below for more tables.)

The RSTS_CODE column is from STVRSTS and SFRSTCR.

The Refund Type column has these values:

• MANUAL - no refunds will be made automatically
• 50PCT - 50% refund will be made (for audit courses)
• 100PCT - 100% refund will be made
• POLICY - Refund policy will be used such that multiple refund date ranges and percentages may be used.  The detail for this by PTRM lengths is in SZBREFD
• NONE - No refund

The Begin and End RFTP (Refund Type) code columns have these values:

• PTRM_START - The start date of the POT from the SOATERM subform
• PTRM_END - The end date of the POT from the SOATERM subform
• WR_START - The first day to web register based on the data in SZBREFD for the PTRM code and number of weeks (this comes from SORRTRM and is in the bottom section of the SOATERM Part of Term and Web Registration Controls page)
• WR_END - The last day to web register based on the data in SZBREFD for the PTRM code and number of weeks (this will be restricted by the web registration end date in SORRTRM (bottom of SOATERM Part of Term and Web Registration Controls page)
• LAST_ADD - The last day to add based on the data in SZBREFD for the PTRM code and number of weeks
• LAST_DROP - The last day to drop based on the data in SZBREFD for the PTRM code and number of weeks
• LAST_ADD+1 - The day after the last day to drop based on the data in SZBREFD for the PTRM code and number of weeks

Technical Information

Underneath, there are three tables that control what is setup.  They are:

• SZBEFDT which has settings for each of the codes in STVESTS, the enrollment status
• SZBRFDT which has settings for the STVRSTS codes, the registration status codes
• SZBREFD which has the number of days for each type of refund for each part-of-term length (in weeks).  All of the data in SZBREFD is based on the "Day Of Term" where the first day of the POT as found in SOATERM is designated as 1.  0 is the day before, -1 is the second day before and so on.

TZPFEES1.SQL calculates the dates and other data and inserts it into temporary tables

• SZTESTS
• SZTRSTS
• SZTRFCR.

TZPFEES2.SQL takes that data and inserts it into:

• SFBESTS - the enrollment statuses that are valid for a term (SFAESTS)
• SFRRSTS - the registration statuses valid for a term (SFARSTS)
• SFRRFCR - the refund schedule for a term. (SFARSTS Course Registration Status Refund Rules)

In turn, this data is accessible through SFAESTS and SFARSTS.

Troubleshooting

If it does not work, there are a few things to check.

10/27/2008 - No data was inserted. In the TZPFEES log file, all of the insert statements said 0 records inserted. In looking, there was no data in the temporary tables SZTESTS, SZTRSTS and SZTRFCR. In looking further, the data in SOBTERM and SOBPTRM seemed correct, but there was no data for the term in SORRTRM. This is the table that contains the web registration dates for each part of term. After the data was inserted, the process works.

3/16/2010 - There were some missing dates in POT 1 for 201040.  That was because POT 1 was set up as a 12 week session and SZBREFD did not have an entry for a 12 week session so had nothing to use to calculate the various things that depend on the number weeks.  POT 1 for Summer has traditionally been an 11 week session.  But this year they wanted 12 (I guess because of the 2 week gap between Intersession and Summer 1).  So I added a row to SZBREFD for a 12 week session.