Archive

Archive for April 27th, 2014

Delete voided check and reset the next check number in AP

April 27th, 2014 No comments

Sometimes a user will accidentally type in a wrong number in the starting check number in A/P check printing and posting. If that check number is higher than the last check number used, the system will prompt to void all the check numbers in between. If that prompt is answered "ok", this will result in a large number of voided checks in the Bank Reconciliation and the system will not allow those numbers to be used.

This problem can be fixed in one of two ways.

The first is to go into the bank reconciliation and delete those voided checks one by one until they get back to the correct starting check number.

The second way is to use SQL Query to programmatically delete those voided checks.

 

/* This script is used to delete a range of check numbers from the glbank table */

declare @commit int, @rows int, @check_num GlCheckNumType

——————————————————————-

/* INSTRUCTIONS: CHANGE THE @commit VARIABLE TO VIEW OR COMMIT CHANGES

AND ENTER A MAX CHECK NUMBER. */

SET @commit = 0 — 0=View; 1=Commit the update changes

SET @check_num = 1055 — Enter highest check number wanted in SL

——————————————————————-

— VIEW CHECK RECORDS THAT WILL BE DELETED

select * from glbank

where check_num > @check_num and type = ‘C’

set @rows=@@rowcount

if @commit=0

begin

if @rows > 0 select [Results]=’Viewing record(s) to be deleted.’

else select [Results]=’No matching records found.’

end

else

begin

— DELETE CHECK RECORDS

delete from glbank

where check_num > @check_num and type = ‘C’

select [Results]= dbo.cstr(@@rowcount) + ‘ Glbank record(s) deleted.’

end

 

Progress version

for each (database name).glbank where check-num > xxxxxxxxx and type = "C":

display glbank with 2 col.

Run the query. If the checks that display are the ones that need to be deleted, then run the following:

for each (database name).glbank where check-num > xxxxxxxx and type = "C":

delete glbank.

This will reset the starting check number back to the original check.