return to PRS Technologies website
<![CDATA[
################################################################################
-- balprob
-- This sql looks for NB time or cost on billable matters,
-- PB time or cost with non-zero bill amount.
-- This sql also compares the time and cost tables to the ledger table
-- and looks for time/cost records modified or billed in an earlier
-- period than worked.
-- Change the period in 10 places (7 current period, 3 next period)
-- Change the NB practice code if different
-- Chnage the time and cost ledger codes if different
-- Look for NB time/cost on billable matter
select tindex, tmatter, tstatus, tbilldol from timecard,matter
where tmatter=mmatter and mprac<>"9000" and tstatus like "NB*"
order by tindex;
select cindex, cmatter, cstatus, cbillamt from cost, matter
where cmatter=mmatter and mprac<>"9000" and cstatus like "NB*"
order by cindex;
-- Look for PB time/cost where billable amount <> 0
select tindex from timecard where tstatus = "PB" and tbilldol<>0
order by tindex;
select cindex from cost where cstatus="PB" and cbillamt<>0
order by cindex;
--Compare Timecard Table to Ledger Table
select tmatter,tinvoice,sum(tbilldol) tbilldol into #t1
from timecard where tbiper="1000" and tstatus<>"NBP"
group by tmatter,tinvoice;
select lmatter,linvoice,sum(lamount) lamount into #t2
from ledger where lperiod="1000" and llcode="FEES"
group by lmatter,linvoice;
select tmatter,tinvoice,tbilldol,lamount into #t3
from #t1 left outer join #t2 on tmatter=lmatter and tinvoice=linvoice;
select lmatter,linvoice,tbilldol,lamount into #t4
from #t2 left outer join #t1 on tmatter=lmatter and tinvoice=linvoice;
delete from #t4 where tbilldol is not null;
insert into #t3 select * from #t4;
update #t3 set tbilldol=0 where tbilldol is null;
update #t3 set lamount=0 where lamount is null;
select * from #t3 where lamount<>tbilldol order by tmatter,tinvoice
-- Compare Cost Table to Ledger Table
select cmatter,cinvoice,cledger,sum(cbillamt) cbillamt into #t1a
from cost where cbiper="1000" and cstatus<>"NBP";
group by cmatter,cinvoice,cledger;
select lmatter,linvoice,llcode,sum(lamount) lamount into #t2a
from ledger where lperiod="1000" and llcode in ("HCOST","SCOST")
group by lmatter,linvoice,llcode;
select cmatter,cinvoice,cledger,cbillamt,lamount into #t3a
from #t1a left outer join #t2a on cmatter=lmatter and
cinvoice=linvoice
and cledger=llcode;
select lmatter,linvoice,llcode,cbillamt,lamount into #t4a
from #t2a left outer join #t1a on cmatter=lmatter and
cinvoice=linvoice
and cledger=llcode;
delete from #t4a where cbillamt is not null;
insert into #t3a select * from #t4a;
update #t3a set cbillamt=0 where cbillamt is null;
update #t3a set lamount=0 where lamount is null;
select * from #t3a where lamount<>cbillamt order by cmatter,cinvoice
-- Look for time/cost worked in the current period or future period and
-- modified in a period earlier than the work period.
-- For the current work, enter the current period in 1 place.
select auindex,auidx from auditt, periodt p1, periodt p2
where auper= "1000" and auper=p1.pe and aupert=p2.pe and
p1.pebedt>p2.pebedt
order by auindex;
-- For the future (next) period, enter the next period in 1 place.
select auindex,auidx from auditt, periodt p1, periodt p2
where auper= "1100" and auper=p1.pe and aupert=p2.pe and
p1.pebedt>p2.pebedt
order by auindex;
-- Look for time worked in the current period or future period and
-- billed in a period earlier than the work period.
-- For the current work, enter the current period in 1 place.
select tindex,twoper,tbiper,tbilldol from timecard, periodt p1, periodt p2
where twoper= "1000" and p1.pe=twoper and p2.pe=tbiper and p1.pebedt>
p2.pebedt
order by tindex;
-- For the future (next) period, enter the next period in 1 place.
select tindex,twoper,tbiper,tbilldol from timecard, periodt p1,
periodt p2
where twoper= "1100" and p1.pe=twoper and p2.pe=tbiper and p1.pebedt>
p2.pebedt
order by tindex;
-- Look for cost worked in the current period or future period and
-- billed in a period earlier than the work period.
-- For the current work, enter the current period in 1 place.
select cindex,cdiper,cbiper,cbillamt from cost, periodt p1, periodt p2
where cdiper= "1000" and p1.pe=cdiper and p2.pe=cbiper and p1.pebedt>
p2.pebedt
order by cindex;
-- For the future (next) period, enter the next period in 1 place.
select cindex,cdiper,cbiper,cbillamt from cost, periodt p1, periodt p2
where cdiper= "1100" and p1.pe=cdiper and p2.pe=cbiper and p1.pebedt>
p2.pebedt
order by cindex;
################################################################################
]]>