return to PRS Technologies website
<![CDATA[
################################################################################
--costclear
-- This SQL compares costs in the current period to vouchers in the current
-- period. It is possible that the voucher and cost are in different
-- periods, and report as discrepancies here.
-- Change the period in 2 places.
-- Change the date range in 1 place
-- Change the GL account in 2 places to the appropriate clearing account.
-- Original sql from Elite Users Conference 2000
-- Modified for use with Informix by Mary Ann Tracy
select gpnum, sum(-gpamount) gpamount
from glpost,batch
where gpprimary="01.13205"
and gpbatch=bbatch
and bper="0501"
group by gpnum
into temp temp1 with no log;
select vo_id, sum(amt) amt
from apvodt
where votrdt between "05/01/2001" and "05/31/2001"
and glnum="01.13205"
group by vo_id
into temp temp2 with no log;
select gpnum, gpamount, cvoucher
from temp1, outer costlink
where gpnum=cindex
into temp temp3 with no log;
select cvoucher, sum(gpamount) gpamount
from temp3
group by cvoucher
into temp temp4 with no log;
select cvoucher, gpamount, amt
from temp4, outer temp2
where cvoucher=vo_id
into temp temp5 with no log;
select vo_id, gpamount, amt
from temp2, outer temp4
where cvoucher=vo_id
into temp temp6 with no log;
delete from temp6
where gpamount is not null;
insert into temp5
select * from temp6;
update temp5
set gpamount=0
where gpamount is null;
update temp5
set amt=0
where amt is null;
create table temp7
(cvoucher CHAR(8), cindex INTEGER, camt DECIMAL, vamt DECIMAL, votrdt DATE);
insert into temp7
select cvoucher,gpnum,gpamount,"",""
from temp3
where cvoucher is null and gpamount<>0;
insert into temp7
select cvoucher,"",gpamount,amt,votrdt
from temp5, outer apvo
where cvoucher=vo_id
and temp5.amt<>gpamount and cvoucher is not null;
select * from temp7
order by cvoucher,cindex;
select auindex,auidx,aubidl
from auditt
where aupert="0501"
and aucode="CCRED"
order by auidx;
drop table temp7;
################################################################################
]]>