return to PRS Technologies website


arvsrec2.sql
################################################################################ --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; ################################################################################