January 2, 2008
ASOC
This new attribute on transfers was added to AAOT and ASOT. I had to modify the functions f_chk_shrtram and f_chk_sordegr to return a 3 digit code instead of a two digit code. The table trigger insert_sgrsatt_from_sgbstdn reads this code and needed to be modified as well. It inserts the attribute. The table triggers insert_sgrsatt_from_shrtram and insert_sgrsatt_from_sordegr were modified as well
Posted by wendlerb at 9:05 AM | Comments (0)
October 4, 2007
Compliance compile
I had to try out a minimal change on smrgevl and thought i should document the compile steps so I could find them easily.
$! If only one of smrgevl,smreval,smrrule,smrjobs,smrsels,smrbcmp or
$! smrcmpl change then only compile the individual change.
$! The lnproc lines must always be included if any of the pieces
$! change.
$!
$ @gen$com:sctproc scroims OBJ "full userid=baninst1/u_pick_it"
$ @gen$com:sctproc scrrfun OBJ "full userid=baninst1/u_pick_it"
$ @gen$com:sctproc scrrims OBJ "full userid=baninst1/u_pick_it"
$ @gen$com:sctproc smrgevl OBJ "full userid=baninst1/u_pick_it"
$ @gen$com:sctproc smreval OBJ "full userid=baninst1/u_pick_it"
$ @gen$com:sctproc smrrule OBJ "full userid=baninst1/u_pick_it"
$ @gen$com:sctproc smrjobs OBJ "full userid=baninst1/u_pick_it"
$ @gen$com:sctproc smrsels OBJ "full userid=baninst1/u_pick_it"
$ @gen$com:sctproc smrbcmp OBJ "full userid=baninst1/u_pick_it"
$ @gen$com:sctproc smrcmpl OBJ "full userid=baninst1/u_pick_it"
$ set def gen$exe
$ lnproc smrcmpl smrcmpl,smreval,smrgevl,smrrule,smrjobs,smrsels,guaorac2,guastdf,guarpfe
$ lnproc smrbcmp smrbcmp,smreval,smrgevl,smrrule,smrjobs,smrsels,guaorac2,guastdf,guarpfe
Posted by wendlerb at 3:22 PM | Comments (0)
April 27, 2007
CAS online
Course Applicability System
This is the development site for Atlas Right now we are overriding the articulation to point to the wou web articulation package. Eventually we need to load the courses into their database. Supposed to be a meeting next week via phone
Posted by wendlerb at 1:46 PM | Comments (0)
March 16, 2007
Smrrule (cont)
SCT was giving out functional capp person a load of crap insisting that his problem was because our database optimizer was set to CHOOSE. So I had the dbas set woudev to RULE and start the capp background processes. Josh found the same thing happening so now maybe SCT will start to look for the real reason. Note that woudev was also refreshed.
Posted by wendlerb at 1:41 PM | Comments (0)
March 8, 2007
Smrrule
Capp compliances have been one of the more buggy of Banner applications that I've seen. Patches come out for it all the time and I'm a little worried about it getting too far ahead of other patches we haven't installed.
Anyway, yet another problem with smrrule/smrcmpl. When you hit the copy button on smasadf it doesn't bring in records correctly. Some SCT tech is telling us it's because our optimizer is not set to Rule with Oracle 9. Since we're on Banner general 7.3, we can't set the enviroment to Rule. I wanted SCT to specify where the problem was in the code but got nowhere. So, I've added the Rule hint to a bunch of cursors in smrrule. We'll see if that changes anything. I wouldn't think so.
Posted by wendlerb at 3:24 PM | Comments (0)
February 26, 2007
shatatr missing connector
Found aprroximately 150 records missing a value in the connector field on multiple equivilencies in shrtatc table. Used following script:
select x.shrtatc_sbgi_code,
x.shrtatc_subj_code_trns,
x.shrtatc_crse_numb_trns
from shrtatc x,
shrtatc y
where x.shrtatc_connector is null
and x.shrtatc_sbgi_code = y.shrtatc_sbgi_code
and x.shrtatc_subj_code_trns = y.shrtatc_subj_code_trns
and x.shrtatc_crse_numb_trns = y.shrtatc_crse_numb_trns
and x.shrtatc_subj_code_inst = y.shrtatc_subj_code_inst
and x.shrtatc_term_code_eff_trns = y.shrtatc_term_code_eff_trns
and x.shrtatc_crse_numb_inst != y.shrtatc_crse_numb_inst
and x.shrtatc_crse_numb_inst like '1XX'
and x.shrtatc_seqno = '2'
and y.shrtatc_seqno = '1'
;
select x.shrtatc_sbgi_code,
x.shrtatc_subj_code_trns,
x.shrtatc_crse_numb_trns
from shrtatc x,
shrtatc y
where x.shrtatc_connector is null
and x.shrtatc_sbgi_code = y.shrtatc_sbgi_code
and x.shrtatc_subj_code_trns = y.shrtatc_subj_code_trns
and x.shrtatc_crse_numb_trns = y.shrtatc_crse_numb_trns
and x.shrtatc_subj_code_inst = y.shrtatc_subj_code_inst
and x.shrtatc_term_code_eff_trns = y.shrtatc_term_code_eff_trns
and x.shrtatc_crse_numb_inst != y.shrtatc_crse_numb_inst
and x.shrtatc_crse_numb_inst like '2XX'
and x.shrtatc_seqno = '2'
and y.shrtatc_seqno = '1'
;
select x.shrtatc_sbgi_code,
x.shrtatc_subj_code_trns,
x.shrtatc_crse_numb_trns
from shrtatc x,
shrtatc y
where x.shrtatc_connector is null
and x.shrtatc_sbgi_code = y.shrtatc_sbgi_code
and x.shrtatc_subj_code_trns = y.shrtatc_subj_code_trns
and x.shrtatc_crse_numb_trns = y.shrtatc_crse_numb_trns
and x.shrtatc_subj_code_inst = y.shrtatc_subj_code_inst
and x.shrtatc_term_code_eff_trns = y.shrtatc_term_code_eff_trns
and x.shrtatc_crse_numb_inst != y.shrtatc_crse_numb_inst
and x.shrtatc_crse_numb_inst like '3XX'
and x.shrtatc_seqno = '2'
and y.shrtatc_seqno = '1'
;
select x.shrtatc_sbgi_code,
x.shrtatc_subj_code_trns,
x.shrtatc_crse_numb_trns
from shrtatc x,
shrtatc y
where x.shrtatc_connector is null
and x.shrtatc_sbgi_code = y.shrtatc_sbgi_code
and x.shrtatc_subj_code_trns = y.shrtatc_subj_code_trns
and x.shrtatc_crse_numb_trns = y.shrtatc_crse_numb_trns
and x.shrtatc_subj_code_inst = y.shrtatc_subj_code_inst
and x.shrtatc_term_code_eff_trns = y.shrtatc_term_code_eff_trns
and x.shrtatc_crse_numb_inst != y.shrtatc_crse_numb_inst
and x.shrtatc_crse_numb_inst like '4XX'
and x.shrtatc_seqno = '2'
and y.shrtatc_seqno = '1'
;
select x.shrtatc_sbgi_code,
x.shrtatc_subj_code_trns,
x.shrtatc_crse_numb_trns
from shrtatc x,
shrtatc y
where x.shrtatc_connector is null
and x.shrtatc_sbgi_code = y.shrtatc_sbgi_code
and x.shrtatc_subj_code_trns = y.shrtatc_subj_code_trns
and x.shrtatc_crse_numb_trns = y.shrtatc_crse_numb_trns
and x.shrtatc_subj_code_inst = y.shrtatc_subj_code_inst
and x.shrtatc_term_code_eff_trns = y.shrtatc_term_code_eff_trns
and x.shrtatc_crse_numb_inst != y.shrtatc_crse_numb_inst
and x.shrtatc_crse_numb_inst not like '4XX'
and x.shrtatc_crse_numb_inst not like '3XX'
and x.shrtatc_crse_numb_inst not like '2XX'
and x.shrtatc_crse_numb_inst not like '1XX'
and x.shrtatc_seqno = '2'
and y.shrtatc_seqno = '1'
;
Posted by wendlerb at 9:20 AM | Comments (0)
February 16, 2007
SZPWART
Must be Capp week this week. Registrar Office asked for a report that would list all records in transfer articulation for a user entered inst (WOU) subj and crse numb. I created this and put it in job submission (szrwart)
So the next request is to be able to change all records with the given subj and crse to a new subj and crse. This proved to be very challenging because you can have multiple course attributes and multiple course equivilances besides the given one for a particular effective term. We also wanted to pull new attributes from the catalog.
For instance:
shbtatc can have the CS 131 transfer record for a sbgi
shrtatc can have CS 160 and CS 1XX as the equivalent
shrtrat can have 2 attributes for each course
Want to change the CS 160 to CS 161
First shot at this put in a new CS 161 record with a new eff term on shrtatc with 2 attributes but lost the CS 1XX. Need to insert both.
Did this with a double select loop and boolean logic to prevent trying to insert records into shbtatc twice when you have multiple equiv courses and shrtatc twice when you have multiple attributes.
Still a question of whether hours are defaulting in correctly on new records.
Posted by wendlerb at 3:11 PM | Comments (0)
February 14, 2007
7.3.1.1 patch
File pcms-dfct75219_stu70301.trz
All wou_sis_mods affected removed so we are at baseline.
Directory WOUP:[000000.GENERAL.C]
SMRCMPL.H;21 13-FEB-2007 12:54:28.65 (RWED,RWED,RE,RE)
Total of 1 file.
Directory WOUP:[000000.GENERAL.EXE]
SMRBCMP.EXE;29 13-FEB-2007 13:34:03.82 (RWED,RWED,RWED,RE)
SMRCMPL.EXE;35 13-FEB-2007 13:33:59.80 (RWED,RWED,RWED,RE)
SMRCMPL.OBJ;27 13-FEB-2007 13:33:54.26 (RWED,RWED,RE,RE)
SMREVAL.OBJ;26 13-FEB-2007 13:28:14.54 (RWED,RWED,RE,RE)
SMRGEVL.OBJ;26 13-FEB-2007 13:26:58.19 (RWED,RWED,RE,RE)
SMRJOBS.OBJ;20 13-FEB-2007 13:31:58.72 (RWED,RWED,RE,RE)
SMRRULE.OBJ;26 13-FEB-2007 13:30:11.84 (RWED,RWED,RE,RE)
SMRSELS.OBJ;19 13-FEB-2007 13:33:31.53 (RWED,RWED,RE,RE)
Total of 8 files.
Directory WOUP:[000000.STUDENT.C]
SMRCMPL.PC;21 13-FEB-2007 12:54:23.36 (RWED,RWED,RE,RE)
SMREVAL.PC;21 13-FEB-2007 12:54:23.38 (RWED,RWED,RE,RE)
SMRGEVL.PC;21 13-FEB-2007 12:54:25.27 (RWED,RWED,RE,RE)
SMRJOBS.PC;17 13-FEB-2007 12:54:26.41 (RWED,RWED,RE,RE)
SMRRULE.PC;22 13-FEB-2007 12:54:27.02 (RWED,RWED,RE,RE)
SMRSELS.PC;15 13-FEB-2007 12:54:28.47 (RWED,RWED,RE,RE)
Total of 6 files.
Posted by wendlerb at 3:18 PM | Comments (0)
January 29, 2007
Attributes
This can be very confusing so I thought I would try and document it. We even had a view transcript package that was looking at the wrong tables before I fixed it.
Institution course records
scrattr is tied to scbcrse Catalog
shrattc is tied to shrtckn Student
ssrattr is tied to ssbsect Section
Transfer
indiv (SHATRNS) shrtatt tied to shrtrcr, shrtrce, shrtrit, and shrtram
inst (SHATATR) shrtrat tied to shrtatc and shbtatc
Student attributes
saraatt tied to saradap (admissions record)
sgrsatt tied to sgbstdn (general student record)
Posted by wendlerb at 10:08 AM | Comments (0)
January 10, 2007
SMRRULE
Registrars was having problems with some compliances and in working directly with SCT asked that some code be added to Smrrule. I noticed an old 6x version of this in the wou_sis_mods directory so it looks like we must have been running an older version. I can see any mods to it so I don't know why it was there. So I grabed the 7.3 baseline and put the code in that. Seems to be working ok since it was compiled.
Posted by wendlerb at 11:41 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)
July 19, 2006
Tran attribute inserted from SGASTDN problem
Ran into a problem with the table trigger insert_sgrsatt_from_sgbstdn. The TRAN attribute was being added to students who had an AAOT or ASOT in a previous term when record added to sgbstdn. Students should not get a TRAN when they already have a previous AAOT or ASOT. The trigger made a call to the functions f_chk_shrtram and f_chk_sordegr to check the degc field on the two tables. It still only checked AAB and AAS so tran insert would happen. I added AAOT and ASOT to the functions and then did a non-standard delete to all tran sgrsatt records that had the previous term AAOT or ASOT codes. Deleted about 640 records.
Posted by wendlerb at 3:13 PM | Comments (0)
June 23, 2006
Pipe process
Thought maybe I ought to document this process since I have to deal with it every once in a while.
Sfrpini is job submission program that kicks it off. It passes the orasid to another process, sfrpips.
Sfrpips has a submit statement at the end of the com file with a lot of database specific hardcode in it. The program builds 3 runtime com jobs based on pipe names in the table SFBPIPE.
These run time com files typically look like this:
$ smrcmpl := $gen$exe:smrcmpl.exe
$! Running SFRPIPE.EXE with user SATURN/
$ set nover
$ @its$util:[oratools]orauser.com 'WOUDEV'
$ sfrpipe := $gen$exe:sfrpipe.exe
$ sfrpipe SATURN/DEV$WOU PIPEWDEV1
$exit
You can see log file of these *pipe* jons in the spool directory. You can see that two other jobs are called in this com file.
If the process executes correctly, you can see the 3 pipes with the command: sho que spruce_posting$batch/all
Posted by wendlerb at 9:07 AM | Comments (0)
June 22, 2006
SZRPINI
This pipe job submission program combines two programs in it's com file:sfrpini and sfrpips. The com files of these have unique statements matchung the database it runs in. So, you can't just copy production com files to one of the dev databases and have it work. I also found out that sfrpini needed to be compiled in woudev to get it to work in woudev. There is also a tablr (SFBPIPE) that has pipe names in it which need to be changed to the database you're in.
Posted by wendlerb at 9:52 AM | Comments (0)
April 27, 2006
GUIEXTR form
This ran slowly. It populated using a view glvextr which need the compiler hint. It used spriden.
Posted by wendlerb at 3:29 PM | Comments (0)
November 16, 2005
Catalog, sect, and histoy HIS subj attributes
We needed to add attributes to HIS subject courses. Li's script worked in a limited fashion for one of the areas but could not handle muliple attributes on one course because of a function he wrote that returned only one attribute from a table he created. I modified that stored procedure and made 2 others based on it to handle the 3 areas of history, section, and course
add_more_ssrattr
add_more_shrattc
add_more_scrattr
I had to change the get_attr function in there to return one record in the case of multiples so this script actually has to be run twice. Run once with select min(attr_code) in code and again with select max(attr_code).
This was run in production 16-NOV-2005
Posted by wendlerb at 1:40 PM | Comments (0)
November 10, 2005
Wouprd stuff
ETS needs us to refresh woutst so we needed to put changes we did to data in woutst to wouprd so they aren't lost. Ran Li's 2 capp insert procedures in production and then ran szpfatt to fix records on several different dates descried in Faye's email plus the day I reran Li's scripts. Ran a script for Faye to list 100Ts that do not have a graduation record and cut it down to about 27 records from several thousand. Script is called shrtatc_tmix if I need it again. I also ran the shatatr and shatrns scripts to update attribures on split courses. These will have an activity date of today in case a problem arises.
Posted by wendlerb at 10:50 AM | Comments (0)
October 28, 2005
SHRTRAT and AHRTATT attribute codes for split course
Both split courses need attributes given to the primary one. In order to do this I had to find transfer courses that had the split where only one had the attributes and then copy them to the other course. Two scripts take care of this, shatatr_insert and shatrns_insert. Ran into a problem with obsolete records of the 100T attribute. The seqno of these records is pretty tricky and the 100T record was fouling it up the first try. Wrote a script, shatatc_tmix, to take care of a bunch of these T records.
Posted by wendlerb at 1:35 PM | Comments (0)
August 10, 2005
CAPP Banner 7
CAPP in woutst is now working like production. There are a number of functions and procedures along with 1 package under the capp schema. Some of these had to be recompiled while logged in as capp. The kicker is 4 table triggers that we had to email Xiaobin to find. They are:
insert_sgrsatt_from_sordegr
insert_sgrsatt_from_shrtram
insert_sgrsatt_from_saradap
insert_sgrsatt_from_sgbstdn
These all had to be recompiled
Posted by wendlerb at 11:49 AM | Comments (0)