return to PRS Technologies website


fees_mat1.sql

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); ################################################################################