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