return to PRS Technologies website
################################################################################
#
# 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
################################################################################