September 9, 2008
CLM Overview
Campus Loan Manager
The business office manages Perkin's Loan information with this SCT application. The database behind it is a Microsoft SQL database named clm on the winchester server locally. You will need access to winchester, access to the database, and access to the web page front end. The financial aid accountant (DV) in the business office is the clm administrator and access to winchester is given by computing services. The reports she generates from it for the most part use Crystal Reports.
There are two pieces you have to deal with as a programmer: The creation of a soil report which goes to the state treasury and the Banner link that creates automatic holds. The soil stuff is in another blog entry.
The web page is https://winchester/clm/. Use the database CLM Production. Go to the Office Mgmt tab and select system configuration parameters from the dropdown. Then select Student System Interface Configuration. You will see a screen with the link info to wouprd along with info on the automatic hold process. You need a userid and link of someone's Banner account to get the connect string to work. They need maintenance Banner access for the link to connect. It uses a Microsoft Data Access Client to do the link which is a little different than Oracle's. The hold buttons are setup by the business office. If everything is correct, you will see a count of holds on the bottom.
Posted by wendlerb at 10:17 AM | Comments (0)
Perkin's Soil Overview
his file comes from CLM (perkins loan data) Microsoft SQL database which is located on winchester
First thing you do is make a backup of the table szasoil
Remote into the server winchester
Start Enterprise manager
Choose the CLM database
Under tools, start SQL Query Analyzer
Open the file soil_current which is in a folder clm in the programmers folder.
The file is a union query. You need to increment the seq no on both sides of the union. It is currently on 7. Save the file with the new seq no.
Run the query and then save the result in a file called soil.csv
close Enterprise Manager, SQL Query Analyzer, and exit winchester
FTP the soil.csv file over to apollo sqlload it into wouprd using the control file soil.ctl
run szrsoil.exe This creates the soil file for the business office that they send to revenue
Posted by wendlerb at 10:13 AM | Comments (0)
Ofax overview
Where to start?
Ofax uses the temp table ryrotmp to load the flat file in wouprd and the table ryrofax which is behind the form RYAOFAX.
The Banner program, szrofax, creates a file which gets uploaded to the ofax web site https://secure2.osac.state.or.us/fao/ You run szrofax from Banner job submission and print to database so you can grab the file. You can get login info to the state web page from financial aid. You upload the file and then download a return file. This file,ofaxout.csv, contains info from all institutions in the the dual enrolled student's program. It gets ftp'd to wou_sis$disk:[wouprd.ftp.fafsa].
This file often has the last character of a date missing on one record which will foul you up. The leading 0's on SSNs also often get dropped.
The program szpofax converts data in ryrotmp and inserts into ryrofax.
The terms of all institutions in the ofaxout.csv file have to be the same.
At the start of a term, I usually make delete and recreate a backup table of ryrofax. There is a delete statement in the program szpofax that will error out if no data exists in the new term. I left this in to prevent the cron fron processing the first entry in a new term. You save yourself a lot of headaches if you check the file to make sure all the terms are the same and run szpofax
while commenting out the delete statement for the first run. Financial aid runs it at the end of the term to get grades and often the next run has mixed terms
Posted by wendlerb at 9:47 AM | Comments (0)
September 8, 2008
Fac/staff directory creation
The pictures that telecom take need to be renamed to the appropriate V number for each person.
Install Adobe photoshop 7.0 om your computer. In the programmers folder under ucs you'll see a folder called Adobe Photoshop 7.0 Settings. Copy this folder to c:\documents and settings/userid/application data/Adobe/Photoshop/7.0 This is where batch settings for photoshop get stored.
Open up the jpg pictures from telecom. Click on the upper left "Rectangular Marquee Tool" on the tool bar palette. Up above put Feather : 0 px Style: Fixed Aspect Ratio Width: 225 Height: 225
Frame the person's head and then click on image and then crop. Save the cropped image as a tiff file with no image compression.
Place these new tiff files in a seperate directory. When you have all the tiff files done, you're ready for the next step.
Click on file,automate, and batch. In the popup window, select the action directory_photo from the dropdown choices. Select the folder you put all the tiffs in as your source folder and whatever destination folder you want. When you click ok, all the tiffs will be processed one at a time. This creates the photos.
There is a Banner job pzrcdir that creates the directory info for fac/staff in a file. This file and the photos get used by a perl program Mike S runs that creates the directory.
Posted by wendlerb at 1:13 PM | Comments (0)
Feed
TGRCLOS runs nightly before the feed kicks off. It closes and finalizes the cashiers sessions. A cashiers session will not be fed until it is finalized. The feed itself runs nightly in the cron job SWBUGLF. This is a com file that goes through a series of steps to complete a feed. This takes records form A/R and student and feeds them into the FIS system on Banner. This includes the accounting for checks done in SIS.
The first part of the feed is unapplication of payments and application of payments. TGRAPPL has only aborted once in about 10 years when a detail code from housing was fed into Banner from a third party software incorrectly. We had to fix a bunch of
tbraccd records after manually unapplying them.
TGRFEED is the main program that feeds stuff across. It puts records into tables gurfeed and gurapay that gets picked up by the FIS part of the process. If the feed bombs in the middle of tgrfeed, your best bet is to reset the feed and start over. There is a script, feed_reset.sql, that shows you how to do this. It is the usual spot where a feed bombs
Furfeed list the fdocs generated, tgrrcon is a A/R report, tgrcshr is the cashier report. The generated index report shows the SIS checks that fed across. These are all just reports
The FIS side starts up around step 16. This sets up posting and accounting for the records coming across. It's important to note you can run these FIS steps manually one at a time without hurting anything. So if you run into a problem in this section, you
don't have to back out all the stuff in the first half of the feed.
A common thing that happens is the printer runs out of paper and then queue stalls overnight. When you restart the queue, you get an error message for the lis file. Just go to the ban_spool directory and reprint the files that are missing.
Posted by wendlerb at 12:43 PM | Comments (0)
July 29, 2008
Table trigger summary
Admissions/Tuition Promise
st_saradap_update_row
st_saradap_insert_row
These set the rate code for incoming students
sarappd_insert_row
This sets the cohort code on students when the admit decision is positive. The endterm field gets populated from a cron job... szpecht.
Capp
insert_sgrsatt_from saradap
insert_sgrsatt_from sgbstdn
insert_sgrsatt_from_shrtram
insert_sgrsatt_from_sordegr
These deal with transfer students and populates the student attribute table with the appropriate attribute for Capp (AAOT,ASOT,ASOC)
Direct Deposit/AP
st_spraddr_insert_row
This syncs the 4 tables necessary for ap direct deposit to work on a person who updates an address. It syncs the atyp and seqno if it is a ap direct deposit vendor.
ID creation
st_spriden_update_row
st_spriden_insert_row
The big daddy. The insert one is a recursive trigger. It populates the ssn field on spbpers if the insert is coming from a student form. It checks against swbpidm to tell if it is an insert or an update. If it's a new record, a swbpidm entry gets inserted A new V number is generated from a sequence and gets inserted into spriden on the second half of the recursion.
Posted by wendlerb at 2:45 PM | Comments (0)
April 13, 2007
Shrediy prob
Shrediy hadn't been working as a cron job for quite a while. Found that out this week. Tried several things to get it to work. The problem is using sysdate in the address date parameter when running from command line like a cron job does. Running out of job submission directly works fine. I had to modify the source to handle sysdate as input.
Posted by wendlerb at 9:59 AM | Comments (0)
January 11, 2007
SOATEST
We got a request to have the test date also get picked up when you hit duplicate record to enter a new test code. Baseline sets it null. The mod is on key_dupl trigger on sortest block. Add another form to wou mods.
Posted by wendlerb at 10:38 AM | Comments (0)
December 28, 2006
Cron and Sleep/Wake
Look - Cron job that moves shrediy output out of jobs directory and into approriate locations on spruce.
FTP - Corvallis job to handle ftp
Wouwrlss - Cron job that notifies students and telecom when their wireless service expires.
ABHolds - Cron job that places Account Balance holds on student accounts.
Gwrclen - Cron job that cleans inactive records out of various tables.
Sfrpini - Cron job that launches 3 pipes for Capp
Sgrsprt - Cron job that list athletes with less that 12 hours
Shredir - Cron job that loads acknowledgement of EDI transcript into Banner.
Shrediy - Cron job that outputs EDI transcript files
Smpcprg - Cron job to to purge students' records from the CAPP tables of a degree audit.
Swbrdll - Cron job to load direct loans
Swbrdll_notify Cron job that notifies direct loan resultd
Swbrpfd - Cron job to load powerfaids
Swbuglf - Cron job for nightly FIS feed
Swirbut - Cron job for budget report
Swiurch - Cron job that creates research table
Swrardf - Cron job that list web address changes
Swrrep2 - Cron job that creates m report
Swrrtrl - Cron job that creates nightly transcript log
Swsprdl - Cron job to track spriden deletes
Swwxweb - Cron job for web section tally tables
Szasftp - Cron job that ftp files from SmartEdi to spruce
Szfafsa - Cron job to load fafsa on file table for Ryaofax
Szpsftp - Cron job to ftp Shrediy files to SmartEDI
Szpofax - Cron jon that loads ofax data into banner
Szpvpad - Cron job to update HR VP address records from SIS
Szrbads - Cron job that lists SSN records with null spriden change ind
Tgrclos - Cron job to close cashier sessions
Pinmail - Sleep/wake that emails password for change in student web pin.
Swbrchk - Sleep/wake student check
Swrrsc2 - Sleep/wake student schdule pintout
Posted by wendlerb at 10:18 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 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 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)
September 27, 2006
Query Tunning
Oracle used a CBO or Cost Based Optimizer but still supports RBO or Rule Based Optimizer for select statements. RBO will be discontinued with Oracle 10i. CBO relies on statistics which need to be kept up to date. Since wouprd is set up a little differently becase of spriden, CBO does not always give the best performance. This is true especially with the table tbraccd. Sometimes as in the feed processes, the RBO works a lot better. I cut the feed down by almost 3 hours using the RBO compiler hint. This will supposedly not be an issue once we go to one database.
Posted by wendlerb at 10:25 AM | Comments (0)
July 26, 2006
Banner front menu
You can adjust from form GUTGMNU
It is *MENU
Posted by wendlerb at 3:52 PM | Comments (0)
July 25, 2006
BackupFiles updated
All Capp functions, procedures, triggers, ans packages are stored in a capp folder under the banner folder in my home direcory (H drive)
FIS/HR material is stored under FISHRIS folders
SIS material is stored under SIS folders. In addition, the woup:[wou_sis_mods.dbprocs] directory on spruce has also been updated with production modifications.
Posted by wendlerb at 2:53 PM | Comments (0)
June 5, 2006
EDI Cron
SZASFTP - 10:15 am SFTP transfer of sedirec.dat from EDI server to com directory on wouprd.
SHREDIR - 10:30 am Banner job submission to populate ack on request
SHREDIY - 4:00 pm Banner job submission generates EDI transcript files from request.
Look - 4:15 pm Banner job submission transfers shrediy files to ftp location on spruce.
SZPSFTP - 4:25 pm SFTP transfer of sedixxx.dat files from spruce to EDI server
Posted by wendlerb at 9:44 AM | Comments (1)
January 30, 2006
SZRGRAD
Job submission
parameters : term code
List student, degree, and honors that graduated in given term.
Posted by wendlerb at 1:22 PM | Comments (0)
January 24, 2006
SZRPCOL
This is a report that lists student and colleges attended prior to admission to WOU. It has two parameters:Start and End Terms to give a term range to select against. It also outputs total credit hours for the college.
Posted by wendlerb at 2:38 PM | Comments (0)
December 29, 2005
SZRIMAJ
Invalid Major/Monor/Conc Report
This report compares student sgbstdn records against valid indicators saved in the szvmajr snapshot taken before we activated all majors.
Posted by wendlerb at 10:55 AM | Comments (1)
December 28, 2005
Standardized request
In an effort to try and gain some control over SIS request, we have decided to use some of the same standards in FIS/HRIS. People requesting new job submission programs to be created will need to fill out request just like FIS/HRIS. A non standard request will work the same way. We have a binder above Mike's desk.
Posted by wendlerb at 9:10 AM | Comments (0)
September 9, 2005
WOU Mods
I've set up a folder under the usc/programmers directory for placing wou mods to Banner forms, packages, triggers, and views. I hope this makes it easier for us to track changes we've made the next time an upgrade comes along. It is, however, only as good as we make it.
Posted by wendlerb at 8:29 AM | Comments (0)
June 8, 2005
SIS report file
I'm up to about 180 active Banner jobs and processes used at WOU. I'm guessing that's about half.
Posted by wendlerb at 9:19 AM | Comments (0)
June 2, 2005
Taming Chaos
I have begun to try and get a handle on all the sis programs we have out there by starting out focusing on job submission c programs. There are also sql scripts, stored procedures, pearl scripts, and God knows what else. It's obvious we are going to have to create some type of user request form similar to FIS/HR to keep track of these. I'm finding that I've written student reports myself that I can't remember who they were for.
Posted by wendlerb at 8:52 AM | Comments (0)