return to PRS Technologies website


crossover.ksh
################################################################################ # # Module: crossover.ksh # Author: Peter and Jennie Schmidt # Description: Run SQL statement to find crossover invoices # # This script identifies occurrences where dollars were collected by # a working timekeeper from one office who worked on matters from # another office. # # This script was written to work under SQL Server and passes the SQL # to the isql program using the "here-is" technique. Since this script # uses a "trusted" connection to the database server (-E flag), # it can only be run from the database server. # # Change Log # # Date Name Description................. # 01/07/04 Jennifer D. Schmidt Start Program # ################################################################################ OUTFILE=crossover.out echo "Run sql statement to find crossover invoices" echo echo "Enter accounting period (MMYY)" read PERIOD echo echo "Processing ..." isql -d son_db -E -n << EOF > $OUTFILE select mattimhs.mtmatter, mattimhs.mtper, sum(mattimhs.mtdocodc) mtdocodc into #tempclt from mattimhs, matter, timekeep where mattimhs.mtper = '$PERIOD' and mattimhs.mtmatter = matter.mmatter and matter.mloc = 'SFO' and mattimhs.mttk = timekeep.tkinit and timekeep.tkloc = 'CLT' group by mtper, mtmatter having sum(mtdocodc) <> 0 order by mtper, mtmatter; select * from #tempclt; select lmatter matter, linvoice invoice, sum(ledger.lamount) amount, 'CLT' from ledger, #tempclt, ledcode where ledger.lmatter = #tempclt.mtmatter and ledger.lperiod = #tempclt.mtper and ledger.llcode = ledcode.lccode and ledcode.lccollhs = 'Y' group by lmatter, linvoice having sum(lamount) <> 0 order by lmatter, linvoice; select mtmatter, mtper, sum(mtdocodc) mtdocodc into #tempsfo from son_db..mattimhs, son_db..matter, son_db..timekeep where mattimhs.mtper = '$PERIOD' and mattimhs.mtmatter = matter.mmatter and matter.mloc = 'CLT' and mattimhs.mttk = timekeep.tkinit and timekeep.tkloc = 'SFO' group by mtper, mtmatter having sum(mtdocodc) <> 0 order by mtper, mtmatter ; select * from #tempsfo; select lmatter matter, linvoice invoice, sum(lamount) amount, 'SFO' from son_db..ledger, #tempsfo, son_db..ledcode where ledger.lmatter = #tempsfo.mtmatter and ledger.lperiod = #tempsfo.mtper and ledger.llcode = ledcode.lccode and ledcode.lccollhs = 'Y' group by lmatter, linvoice having sum(lamount) <> 0 order by lmatter, linvoice EOF echo echo "Processing completed" echo if [ -s $OUTFILE ]; then cat $OUTFILE | xlp -d//[SERVERNAME]/[PRINTERNAME] -onb fi ################################################################################