« October 2006 | Main | December 2006 »
November 30, 2006
SFARQST
This form was hanging sessions taking about 15 to 20 minutes to page down into the form. The form had several spriden selects in it which needed a compiler hint so I changed those and put the mod into production. It didn't help
I ran the sql to find the slow cursor and found a query referencing the table sfrenrl and sfvenrl. Never could find that query anywhere in database, form, or associated libraries but from past experience knew the problem had to be with the view. Sure enough, the view has about 4 unions in it all with spriden joins. First tried the driving site hint but that didn't work. Used all rows on it and it seems to be working ok now.
So there's a form mod and a view mod
Posted by wendlerb at 10:29 AM | Comments (0)
November 28, 2006
Compliance via SMARQCM
When you select the option to run the compliance via this form it calls a com file: smrcmpl.com which actually runs two jobs. They are smrcmpl and smrcrlt. Smrcrlt handles the format of the printout and has a wou mod on it to print 80 lines instead of 55.
Posted by wendlerb at 11:32 AM | Comments (0)
November 20, 2006
OFAX
WOU OFAX
INBOUND File
Run Banner job submission: SZROFAX which looks for student type ‘D’ and checks for attribute “HOME� present or not.
Print to DATABASE and save output with .txt extension
Upload inbound file through ofax web page
OUTBOUND File
Save outbound file from ofax web page.
Extract ofaxout.csv with password.
FTP tp spruce wou_sis$disk:[wouprd.ftp.fafsa]
Run Banner job submission: SZPOFAX
Posted by wendlerb at 2:11 PM | Comments (0)
November 16, 2006
Checks not fed
Last month when the system glitch caused the feed to run multiple times, when ended up with checks that had their accounting fed but not the check. A couple of people have already cashed the checks. Jeremey ran into this situation once before so already had a scipt to handle it. It creates a stand alone fgp.lis file. See README.FIS_CHECKLOAD
Posted by wendlerb at 9:51 AM | Comments (0)
November 15, 2006
Removing Advisors
It's been requested that we create a process to remove the advisor from an inactive student record. The form SGAADVR is a little deceptive. When you hit the maintainance button and close he advisor, the fiels on the form shows updates the end term field. There is no field on the table behind the form that corresponds to this field. In reality, a new record is inserted into the table with a new effective term and nulls in the advisor field. I wrote Szpeadv as a job submission program which does this. It looks for IG or IS sgbstdn records.
Posted by wendlerb at 2:46 PM | Comments (0)
November 13, 2006
Spriden in Banner SIS
Wops and wouprd are two seperate databases connected via spriden and via pidm generation. The spriden table in wouprd has actually been removed. The spriden that you see is a view created via a link to the spriden table in wops. This was done to ensure that the spriden data on the two systems were always in sync. So when you create a student in SPAIDEN you are really creating a record in wops. The pidm that gets assigned to the student comes from a sequence in wops and there is a crosswalk table, swbpidm.
The fact that the spriden in SIS is a view instead of an indexed table creates a whole host of problems that we hope will go away once we merge into one database. This is why we need compiler hints like /*+ All rows */ or /*+ Driving site spriden */ in spriden selects that involve spriden and other joined tables. Without these hints, simple selects can take ridiculously long times. Also, our environmental variables for the databases are set different than other schools which causes the optimizer base code to not always work well on complicated selects. This is what gave us all the time constraint headaches with the feeds. We finally figured out we had to use a rule base on the problem cursors.
The biggest problem is with the APIs involving person or corporation creation. The baseline APIs return a rowid which is not on a view. We had to code around all these every upgrade.
Posted by wendlerb at 3:55 PM | Comments (0)
November 10, 2006
Dual Enrollment/OFAX
I need to determine if FAFSA is on file for stuent in Banner generated uoload file for the state OFAX site. This information is on financial aids Microsoft SQL server database. Financial aid generates a file: fafsafilers.csv that gets ftp'd to spruce. I wrote a com file: swfafsa.com to sqlload this into a Banner temp table using fafsa.ctl to do the load. We decided to do this as a seperate process instead of tagging it onto the power faids load.
Posted by wendlerb at 10:15 AM | Comments (0)
November 8, 2006
Dual Enrollment
WOU has signed an agreement Dual Enrollment Anouncement and left us to figure out how to set this up in Banner. This involves number of steps. The amount of students in the program is minimal right now but we expect more. You have to be able to identify these students in Banner. You have to determine which instituation is their home institution. The home institution hands out the financial aid. You have to upload date to the ofax database on the Oregon financial aid page OFAX You have to be able to see how many houts a student is taking at both colleges.
Step 1 is setting up ofax data exchange. I wrote a job submission program, szrofax to generate an upload file. Difficult piece is a field FAFSA ON FILE which is on financial aid server. Right now I'm getting a file of SSNs that gets loaded into a temp table.
Posted by wendlerb at 3:37 PM | Comments (0)
November 3, 2006
Inactivating Student Records
Registrars and admissions have decided to change the status of students who do not meet the difinition of active student to an appropriate inactive status in Banner. They have requested a two step procedure. The first is to clean up the status of students who have graduated and have not registered since graduating. The second is to clean up currently active records of students who don't meet the definition of active anymore.
Step 1 Add_new_sgbstdn
I wrote this procedure for one time execution. It inserts a new sgbstdn record on students with a degree code of UA or GA and who haven't registered since shrdgmr_term_code_grad. It inserts this new record with a sgbstdn_term_code_eff one term higher than either the last sgbstdn record or shrdgmr_term_code_grad (depending on which is greater). The status for these records is 'IG' with a user id of BWENDLER.
Step2 Job submission program SZPSTDN
I wrote this job submission program that takes active student sgbstdn records and inserts a new record where the student has not been registered the current term and 3 terms back. It inserts a status of 'IR', a user selected sgbstdn_term_code_eff, and has a user id of SZPSTDN. It only inserts records and will not update a record if one exists with the same sgbstdn_term_code_eff as the inactivate term parameter selected by the user. Need to check for dual enrolled students
Posted by wendlerb at 2:41 PM | Comments (0)
November 1, 2006
SZRSCAN
Service Learning & Career Development have been making a lot of request lately but have really gone through all the hoops to get access to student info. One of the things they've done is buy a scanner to scan student id cards at certain events. They then want information on the students who attended these events based on an excel file generated by the scanner. I wrote the program, szrscan, which is wrapped in a com file. The problem is getting the scanner data into banner so I can match it.
I created a temp table in wouprd called swbscan and sqlload a csv file in the front of the com file. Swbscan.csv must be located in wou_sis$disk:[wouprd.ftp.szrscan] and the user has to ftp the file there.
Posted by wendlerb at 11:50 AM | Comments (0)