return to PRS Technologies website
This sql statement will identify any discrepancy between fees collected in the ledger table and fees posted to the fee income account
################################################################################
-- This sql statement will identify any discrepancy
-- between fees collected in the ledger table
-- and fees posted to the fee income account
-- This sql attempts to total the value of fees
-- paid in a credits batch vs. the value posted
-- to the fee income account in the corresponding
-- gl batch
-- Note: before running this SQL statement:
-- update the payment period and the g/l date range
-- update the fee income account
-- update the fee ledger codes
-- Author: Mary Ann Tracy
drop table temp1;
drop table temp2;
create temp table temp1 (
lbatch char(10),
lamount decimal
) with no log;
insert into temp1
select
pay.lbatch,sum(pay.lamount) lamount
from ledger pay, ledger bills, ledcode paycodes
where
pay.lperiod = "0302"
and pay.ldocumnt IS NOT NULL
and pay.llcode = paycodes.lccode
and paycodes.lccollhs = "Y"
and pay.laptoin = bills.lindex
and bills.llcode in ("FEES","CFEES")
group by 1;
create temp table temp2 (
gjbatch char(8),
glnum char(4),
gjdebit decimal,
gjcredit decimal
) with no log;
insert into temp2
select
gjbtid,glnum,sum(dbamt),sum(cramt)
from gj
where glnum="5015"
and gjtrdt between "03/01/2002" and "03/31/2002"
group by 1,2;
update temp2
set gjdebit=0
where gjdebit is null;
update temp2
set gjcredit=0
where gjcredit is null;
select * from temp1,temp2
where lbatch=gjbatch[3,8]
and lamount<>(gjcredit-gjdebit);
################################################################################