return to PRS Technologies website
<![CDATA[
################################################################################
-- ardebit
-- If ELBS A/R does not equal A/R G/L Account, this sql will find the potential
-- problem ledger index for a debit ledger code (ie. FEES, HCOST, etc.)
-- Run this sql to find potential problem ledger index for specific DEBIT
-- ledger code(s). i.e. FEES, HCOST, etc.
-- Change the ledger code(s) in 2 places, and the corresponding A/R GL
-- account segment(s) in 1 place.
-- Change the period in 3 places.
-- NOTE: if a Gl account is used for both debit and credit ledger codes,
-- this sql will need to be modified accordingly. (i.e. GL account for
-- FEES and UNAFEE (unallocated fees))
-- Insert a line such as
-- 'insert into t1 select lindex,-lamount from ledger
-- where lperiod='XXXX' and llcode='YYYY' and laptoin is null'
-- after the other 'insert into t1' and before 'select gpnum'
-- where 'YYYY' is the credit ledger code.
-- Original sql from Elite Users Conference 2000
-- Modified for use with Informix by Peter Schmidt
select lindex, lamount
from ledger
where lperiod="0501" and llcode in ("FEE","CFEES","FF")
into temp temp1 with no log;
insert into temp1
select l1.lindex, -l1.lamount
from ledger l1, ledger l2
where l1.lperiod="0501" and
l1.laptoin=l2.lindex and
l2.llcode in ("FEE","CFEES","FF");
select gpnum,sum(gpamount) gpa
from glpost, batch
where bper="0501" and
bbatch=gpbatch and
gpprimary="1371000"
group by gpnum
into temp temp2 with no log;
-- This should match the AR Rec current period bills-collections-credits
select sum(lamount) from temp1;
-- This should matches the GL for the current period (argj.sql)
select sum(gpa) from temp2;
select lindex,lamount,gpa
from temp1, outer temp2
where temp1.lindex=temp2.gpnum
into temp temp3 with no log;
select gpnum,lamount,gpa
from temp2, outer temp1
where temp1.lindex=temp2.gpnum
into temp temp4 with no log;
delete from temp4 where lamount is not null;
update temp4 set gpnum=0 where gpnum is null;
insert into temp3 select * from temp4;
update temp3 set
lamount=0
where lamount is null;
update temp3 set
gpa=0
where gpa is null;
-- Following should match the 2 figures reported above, and
-- hopefully the difference is the OOB amount!
select sum(lamount),sum(gpa) from temp3;
select *
from temp3
where lamount<>gpa
into temp temp5 with no log;
select temp5.*, ledger.lmatter, ledger.llcode, ledger.linvoice
from temp5, outer ledger
where temp5.lindex=ledger.lindex
into temp temp6 with no log;
select lmatter,llcode,linvoice,sum(lamount) lamount,sum(gpa) gpa
from temp6
group by lmatter,llcode,linvoice
into temp temp7 with no log;
select *
from temp7 where lamount<>gpa
into temp temp8 with no log;
select *
from temp6
where lmatter in (select lmatter from temp8) or
lmatter is null
order by lmatter,llcode,linvoice,lindex;
################################################################################
]]>