return to PRS Technologies website
################################################################################
--arvsrec2
-- This sql will help troubleshoot discrepency
-- between A/R Report and A/R Rec
-- Prior to running this sql, run the A/R Report
-- with matter level detail, to a data warehouse.
-- The period in this sql is the 'next' period
-- Change the period in 2 places
-- Replace dw_TABLE with your data warehouse table name in 2 places
-- also review the output for null llcode. This indicates
-- ledger record(s) with either null ledger codes or invalid laptoins.
-- Original sql from Elite Users Conference 2000
-- Modified for use with Informix by Mary Ann Tracy
select lmatter,llcode,sum(lamount) lamount
from ledger
where laptoin is null and lperiod<>"0801"
group by lmatter,llcode
into temp temp0 with no log;
insert into temp0
select l1.lmatter,l2.llcode,sum(-l1.lamount)
from ledger l1, outer ledger l2
where l1.laptoin=l2.lindex
and l1.laptoin is not null and l1.lperiod<>"0801"
group by l1.lmatter,l2.llcode;
select lmatter,llcode,sum(lamount) lamount
from temp0
group by lmatter,llcode
into temp temp1 with no log;
update temp1 set
lamount=-lamount
where llcode in
(select lccode from ledcode where lcdebcr="C");
select lmatter,sum(lamount) lamount
from temp1
group by lmatter
into temp temp2 with no log;
select lmatter,lamount,artotal
from temp2, outer dw_TABLE
where lmatter=mmatter
into temp temp3 with no log;
select mmatter,lamount,artotal
from dw_TABLE, outer temp2
where lmatter=mmatter
into temp temp4 with no log;
delete from temp4 where lamount is not null;
insert into temp3 select * from temp4;
update temp3 set lamount=0 where lamount is null;
update temp3 set artotal=0 where artotal is null;
select * from temp1 where llcode is null order by lmatter;
select sum(lamount),sum(artotal) from temp3;
select * from temp3 where lamount<>artotal order by lmatter;
################################################################################