return to PRS Technologies website


arvsrec1.sql
################################################################################ -- arvsrec1 -- This sql statement will calculate total A/R for -- use in troubleshooting A/R Report vs. A/R Rec -- discrepancies -- The period in this sql is the 'next' period -- Change the period in 2 places -- Review the output for null llcode. This indecates ledger -- record(s) with either null ledger codes or invalid laptoins. -- Original sql from Elite Users Conference 2000 -- Modified for use with Informix by Peter Schmidt select llcode, sum(lamount) lamount from ledger where laptoin is null and lperiod<>"0601" group by llcode into temp temp0 with no log; insert into temp0 select 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<>"0601" group by l2.llcode; select llcode, sum(lamount) lamount from temp0 group by llcode into temp temp1 with no log; update temp1 set lamount=-lamount where llcode in (select lccode from ledcode where lcdebcr="C"); select llcode, lcdebcr, lcfco, lamount from temp1, outer ledcode where llcode=lccode order by llcode; select "TOTAL: ", sum(lamount) from temp1; { select llcode, sum(lamount) lamount into #t0 from ledger where laptoin is null and lperiod<>"0601" group by llcode insert into #t0 select l2.llcode, sum(-l1.lamount) from ledger l1 left outer join ledger l2 on l1.laptoin=l2.lindex where l1.laptoin is not null and l1.lperiod<>"0601" group by l2.llcode select llcode, sum(lamount) lamount into #t1 from #t0 group by llcode update #t1 set lamount=-lamount where llcode in (select lccode from ledcode where lcdebcr="C") select llcode, lcdebcr, lcfco, lamount from #t1 left outer join ledcode on llcode=lccode order by llcode }; select "TOTAL: ", sum(lamount) from #t1; ################################################################################