June 19, 2008
SZPOFAX
Grades for 200703 were loaded today and brought up a problem. I needed to change the logic for the term code used away from using sysdate and instead using the term start date in ryrotmp. The delete was also erroring out when no data existed yet in ryrofax for the term being loaded. I had to check to see if there is data in ryrofax for the term before doing the delete command.
So 200703 data coming in deletes 200703 data now instead of 200800 and the first 200800 data that comes in will skip the delete part until after it's been loaded once.
Posted by wendlerb at 3:31 PM | Comments (0)
February 6, 2008
OFAX Process
Here are the steps
1) Run szrofax with term code and save output to file from database. This pulls dual enrolled students (student type D) registered at WOU for the term
2) Log into state ofax site/
3) Upload the file into the state ofax database. Be sure and pick download return file instead of email.
4) Save return zip file and extract it with financial aid's password. Save file into J:\Financial_Aid-Business\OFAX. The file is a combination of WOU and Chemekata data which matches on SSN and birthday. Usually if there is a problem with the load, it's because a record in this file is missing the last character of a date.
5) The file is ftp'd to wou_sis$disk:[wouprd.ftp.fafsa] on spruce
6) Szpofax runs on a cron. It first sqlloads the file into a holding table, ryrotmp. Then the the proC program converts the format and loads the table ryrofax which is what is behind the Banner form.
Notes:
There is history on our table ryrofax but no history from the state ofax database. You get what was last put in. Szpofax deletes data from the current term before reinserting the new load but leaves previous terms alone. Data from Chemekata's last load and WOU's last load must be the same term or you end up with screwy data. There are a lot of mods in szpofax to deal with bad data coming from the state ofax site. Stuff like school codes coming in as 003209 and 3209.
Posted by wendlerb at 3:02 PM | Comments (0)
January 3, 2008
Direct Loan load - SWBRDLL
Because we've had issues with this cron running multiple times due to a glitch on spruce, I decided to add he same logic that tgrfeed uses to prevent it from executing when another instance of swbrdll is already running. That means that if swbrdll aborts somewhere in the middle of the process, a programmer will have to do the following before it correctly runs again:
DELETE FROM GJBPRUN
WHERE GJBPRUN_JOB = 'SWBRDLL'
AND GJBPRUN_ONE_UP_NO = -1;
This should prevent multiple loads in the future.
Posted by wendlerb at 8:39 AM | Comments (0)
December 31, 2007
Finaid duplicate runs
The cron jobs ran more than once 3 times last week including swbrdll, the direct loan load. ETS confirmed and fixed the problem last Friday. Apparently, jobs were kicking off a couple of seconds too soon for some reason and then kicking off again at the correct time. Anyway, direct loans loaded multiple times for 7 people on the Dec 28 run. While the Business Office took care of tbrmemo manually and put a hold on the students, direct_pre_memo was still scewed up. This in turn caused financial aid adjustments to not load because multiple rows were being returned in a net amount select.
Posted by wendlerb at 2:35 PM | Comments (0)
July 23, 2007
Ofax stuff
The ofax stuff has been giving me fits. I was having problems with inserting mutiple reocrds in the table ryrofax. At first I just deleted all records for a parameter entered term code and then reloaded the term with each szpofax run. This caused problems when doing a back term. You can't pull matching data from Chemekada for the prior term unless they first load it in ofax.
So I tried looking for an existing record and inserting if it didn't exist or just updating if it does. Had a heck of a time matching the temp table course and number to the ryrofax course and number. Turned out blank characters causing problem so rtrim seems to have solved. The 00 in 003209 is still sometimes there and sometimes not.
Posted by wendlerb at 9:41 AM | Comments (0)
June 4, 2007
Szrsoil
The CLM database is on a Microsoft SQL Server 2003 box. This makes it a huge pain to generate a soil file. I have to start query analyzer to run a script to grab the data, save it, and move it over to an Oracle table under wouprd. Szrsoil is the program to generate a soil file sent to the state. The logic in it deletes the records off this szasoil table for a particular ssn when a delete record case happens. This was a pain because I can't rerun the program to pick up the deletes more than once. So I created another table called szasoil_deletes so at least I can put them back if need be.
Posted by wendlerb at 2:04 PM | Comments (0)
April 24, 2007
Grad Plus Loans
A new loan type has sprung up and the first time it went through the direct loan load process it went on every load giving multiple records. I investigated and found the detail code field on the direct_pre_memo table wasn't getting populated when swbrdll runs. Therefore, it wasn't matching to the record already on memo and would load again. It turms out this new loan has a new loan type G. There is no crosswalk for it on direct_type_xref table so I added it. Type G now crosswalks to a new detail code FDLG.
Posted by wendlerb at 2:09 PM | Comments (0)
April 4, 2007
Ryaofax
I've changed the load to include multiple terms and have tested it out ok. Not sure yet if our community college is inputing multiple terms on their end.
Posted by wendlerb at 11:26 AM | Comments (0)
April 24, 2006
CLM Soil
Ok, time for documentation. I had a number of issues with interest using the invoice table. The table is not current and caused a bunch of outer join headaches so I got the code CLM uses to calculate interest and used that instead.
I have a query soil4.sql the folder clm on my h drive. Run this from sql analyzer on winchester in sql server and save output to file called soil.csv This is important. You must sequence the sequence field yourself manually. Ftp the file to spruce and load it into the szasoil table using the control file soil.ctl From here you can run szrsoil to create a file szrsoil.lis which is in oregom dept of revenue format.
Posted by wendlerb at 10:04 AM | Comments (0)
April 18, 2006
Web aid release error report - Szraidd
I tested new code in wou_fa package and it worked correctly. I released aid hit the back button and tried again. The error popup came up telling me to see cashiers and it exited out to the menu. In addition I wrote a report, Szraidd, which checks aid released onto tbraccd. It looks for duplicate aid type detail code with identical descriotion fields for the term entered.
Posted by wendlerb at 8:57 AM | Comments (0)
April 17, 2006
Financial aid web release problem
Financial aid and the business office discovered 2 dtudents last term who released their aid online with problems. Some of the aid was loaded twice into tbraccd. This has never happened before. Their aiddecision shows multiple records on each of the codes but it's difficult to tell exactly what happened. The decision of the student is passed to a processing procedure as a list and it appears that the entire process is happening twice for some reason. I've added code to prevent double clicks on the submit button and to check if detail and detail description already exist for the aid released in the given term. Hopefully this will stop this from happening again in the future. I've emailed the students hoping to get a description of their session to get some clues on why this happened in the first place. So far one of them saod there were no duplicate check boxes but the pop up box didn't return right away after she clicked submit. I suspect a double click on the submit or hitingthe back button and resubmitting.
Posted by wendlerb at 10:28 AM | Comments (0)
March 2, 2006
CLM Soil file
This has been a huge pain. Problem I'm having is dealing with all the outer joins in T-SQL. I got a report from Dona that listed 478 students as owing money past payment. After comparisons I got a query to return 525 rows of data as opposed to 225 I had before. This is just first adds. Haven't delt with changes or deletes yet.
Posted by wendlerb at 2:51 PM | Comments (0)
January 3, 2006
SOIL and Perkins Loans
We have not been getting a SOIL file to the state with Perkins loan info ever since WOU switched to CLM. I fixed some V#s on this system so I was at least a little familiar with it. It's set up in a SQL Server environment. I have the format of the data transfer file but did not know the corresponding fields in CLM so I had to spend some time learning the table structure and finding them. Currently working on this and have come up with T-SQL query to get data. Will likely do a feed into an oracle table to make it easier to work with the data and format output.
Posted by wendlerb at 2:41 PM | Comments (0)
September 29, 2005
Powerfaids problem
Discovered that when two records are loaded into the direct_pre_memo table for the same pidm with identical detail codes and a record already exists in tbrmemo with the same detail that's been zeroed out, the load proocess did one of two things. I believe it updated the zeroed out record with the new total and inserted a new tbrmemo record with the new total at the same time. The net effect is the person has 2 records in tbrmemo with aid instead of one. This is a load problem and unrelated to the online aid release. I looked at the code and selected a sum(amount) in the pre_memo select in SWBRPFD. This caused a problem the next day load because there's an update direct_pre_memo statement in there which matches each record in pre_memo with amount. I commented that out so hopefully both records will get updated and program won't bomb.
Posted by wendlerb at 3:25 PM | Comments (0)
June 20, 2005
Disbursement goes live
Only 9 records created over the weekend. All released aid correctly but check process didn't cut check for two grad students. Found bug in swpbchk grad check routine looking for a Y instead of a T. Fixed now so grad students will get check cut. Only 2 new ones since this early morning. Not a lot of aid out there for summer.
Posted by wendlerb at 11:16 AM | Comments (0)
June 17, 2005
Disbursement
This goes live over the weekend. Powerfaids turns off disbursement page on the menu when loading aid into memo and then turns it on when finished. This is causing headaches because sometimes you want to leave it off and the process turns it on every morning that it runs.
Posted by wendlerb at 1:46 PM | Comments (0)
June 14, 2005
Disbursement on web for student
The front end of the financial aid disbursement page on web for student is actually fairly simple. The student selects the term and then gets to see the aid he has loaded into Banner. He can either release it, decline it, or leave it undecided for now. The system will remember what has been declined, what has been accepted, and what is still undecided. Currently, we are only allowing one term to be available at a time. In the past when a student declined aid, he filled out a form and the aid was zeroed out in tbrmemo later by staff. Now, the aid gets zeroed out immediately so it can’t get reinstated without a trip to financial aid. If the student is owed money, a record is created in a collector table. The job submission program, szpbchk, is run which populates these records into the normal student check printing process.
The real work of the form is on the back end making sure aid is not released to the student when it shouldn’t be according to WOU business policies. There’s error checking against credit hours taken. This takes into account part time students on the financial aid list as well as part of term 3 credit hour scenarios. There’s a check to make sure the student is set up with a revolving charge account. There’s a check against several holds which stop release of financial aid. One hold prevents money being paid out to the student which is earmarked for being returned to their parents. The actual amount which can be cut on a check to the student has to have this amount removed. The program must also determine if a third of tuition was paid and if money is owed from previous terms. Recent additions check if Visa payments were paid tying up funds. A record of the released aid goes on tbraccd just like it would if you released it from Banner.
Posted by wendlerb at 9:27 AM | Comments (0)
June 8, 2005
Financial Aid/Banner Feeds
Powerfaids and part time student lists are done every morning in a cron job. The part time student list is done one term only (instead of all terms) and transferred from financial aid’s server to a directory on spruce every morning at 4:15 am. This info is used in the financial aid disbursement package on Banner Web for Student. The FAIDS file transfer runs shortly after. The files controlling these two transfer process are on the shared drive on maverick. The Banner process on spruce which takes those files and feeds Banner,SWBRPFD, runs at 5:30 am. There is logic in SWBRPFD.COM that only runs it if the date of the faids.dat file is the same as the system date. This was set up to help prevent it from double loading data into Banner when no new faids file was transferred into spruce. Note that this only checks against day so you will double load Banner if you run from job submission manually after the load ran that morning via the cron job. The latest part time student file is always loaded regardless. Financial aid uses POE instead of term code. The table to crosswalk this is faids_term.
The direct loan feed is also a cron job on spruce which runs nightly on spruce at 8 pm. It is called SWBRDLL and is different than powerfaids in that it can be run more than once without hurting anything. The file transfer from financial aid (anticipated.txt and loan.txt) to a directory on spruce takes place at 7 pm. This process is on wolfman. There is also another cron job on spruce which runs at 8:20 pm that emails the financial aid accountant telling her if the direct loan feed was successful or not.
Both direct loans and powerfaids load financial aid into Banner's memo table and must be released manually before getting applied to student's account. The new financial aid disbursement process on web for student will let the student do this himself.
Posted by wendlerb at 11:22 AM | Comments (0)