Accpac ERP 5.3b - Post Reconciliation 1

[j0rg3]

Okay, I'm working with a test copy of our Accpac system which nobody seems to know too much about.

Something gummed up the works so currently I'm trying to do a Post Reconciliation and the error message tells me:

1 - Error - Incorrect procedure. Requested check 000000000001 was cleared previously
2 - Error - Incorrect procedure. Check 1 was not reversed or cleared

Based on some clumsy communication with Accpac done by others, I'm going into the DB (DBO.APPYM) and setting SWCHKCLRD and DATECLRD to 0. Then I try the reconciliation again, and it gives me a new number to try.

This query is what was used previously to clear out a different error.

Can anybody give me some insight on what is happening, why I'm doing it and whether or not it will every yield the result of letting the reconciliation complete.

Being on a test system, I am tempted to try setting SWCHKCLRD to 0 on all fields that don't have a 0 but that won't give me a solution that I can use in production.

Thanks!
j0rg3

 

[Jay Converse]

Set BKCHK.SRCEAPP to "BK" for checks where RECSTATUS = 5 and the equivalent APPYM.SWHKCLRD = 1.

 

[j0rg3]

Is there some kind of foreign key from APPYM? It looks like CHECK.BKCHK is the same as IDRMIT.APPYM (but without the leading zeros).

To give you a little background (very little), I am completely in the dark. I have never seen this software (Accpac) when it was working correctly.

I have several years coding (w/ DB) experience but I don't understand the fields, what is happening vs. what should be happening.

I'm happy to RTFM, if there is one.

Would it be safe to just do all of them? Like this:
UPDATE SET BKCHK.SRCEAPP = "BK" WHERE RECSTATUS = 5;

Thanks so much for the help!! I'm losing my mind with this thing.
j0rg3

 

[Jay Converse]

Yes, unfortunately it's a string in one table and a number in the other. I usually use MSAccess and a make-table query when I have to do this so that I can get the joins right.

If you run this query

UPDATE SET BKCHK.SRCEAPP = "BK" WHERE RECSTATUS = 5;

and some of the checks are in fact not cleared in AP, then they won't ever get cleared in AP. That's your call.

 

[j0rg3]

Okay, I think I have a better idea what question I'm trying to ask.

Currently, the only way I have to determine which checks need cleared is by trying the Reconciliation and waiting for it to give me the error, with the check number. I did this for quite some time yesterday hoping to clear up the mess so I can get a better grip on what is happening. Since it is a test system, I did not keep a record of the checks for which I set SWCHKCLRD.

So -- is there a way, without attempting the reconciliation, for me to figure out which checks are problematic? If I can figure that out then I can figure out how to make the SQL for setting BKCHK.SRCEAPP and SWCHKCLRD.APPYM.

 

[Jay Converse]

Yes, you use MSAccess and a make-table query so that you can join and compare the cleared status flags. If you don't have MSAccess, use a SQL view with a cast.

 

[j0rg3]

Sorry for being so slow on the up-take. I'm probably making this far more difficult than it needs to be.

If I understand what you've been so patiently trying to explain to me, then I should do this:

CREATE TABLE check_problems SELECT dbo.APPYM.IDRMIT FROM dbo.BKCHK, dbo.APPYM WHERE dbo.BKCHK.RECSTATUS = 5 AND dbo.APPYM.SWCHKCLRD = 1 ORDER BY IDRMIT;

Then I have a list of all checks that need manually cleared. Right?

 

[j0rg3]

Sorry. I should have spoken in MS SQL.

SELECT dbo.APPYM.IDRMIT INTO check_problems FROM dbo.BKCHK, dbo.APPYM WHERE dbo.BKCHK.RECSTATUS = 5 AND dbo.APPYM.SWCHKCLRD = 1 ORDER BY IDRMIT;

 

[Jay Converse]

Nope, you need to convert the APPYM check to an int. Like this:

SELECT BKCHK.[CHECK], BKCHK.SERIAL, BKCHK.STATUS, BKCHK.SRCEAPP, BKCHK.RECSTATUS, AP.SWCHKCLRD
FROM dbo.BKCHK INNER JOIN
(SELECT CAST(IDRMIT AS int) AS Checknum, LONGSERIAL, SWCHKCLRD FROM APPYM) AS AP
ON AP.Checknum = BKCHK.[CHECK] AND AP.LONGSERIAL = BKCHK.SERIAL

 

[j0rg3]

Thank you so much, tuba2007!!

In case somebody else needs it, the following code is essentially what I used for this process. I took out the select statements that were only for testing and I committed the transaction instead of rolling it back but I'd rather post harmless code.

BEGIN TRANSACTION;

/* FIND UNCLEARED CHECKS AND RECORD THEIR NUMBERS IN [checks]*/
SELECT BKCHK.[CHECK], BKCHK.SERIAL, BKCHK.STATUS, BKCHK.SRCEAPP, BKCHK.RECSTATUS, AP.SWCHKCLRD
INTO checks
FROM dbo.BKCHK
INNER JOIN
(SELECT CAST(IDRMIT AS int)
AS Checknum, LONGSERIAL, SWCHKCLRD
FROM APPYM)
AS AP
ON AP.Checknum = BKCHK.[CHECK] AND AP.LONGSERIAL = BKCHK.SERIAL;

SELECT * FROM checks ORDER BY [CHECK];

/* CLEAR CHECKS IN [checks] */
UPDATE BKCHK SET SRCEAPP = 'BK'
FROM BKCHK
INNER JOIN
checks
ON
(checks.[CHECK] = BKCHK.[CHECK]);

SELECT TOP 5000 * FROM BKCHK ORDER BY [CHECK];


/* REMOVE THE TABLE */
DROP TABLE checks;

ROLLBACK;

Now, I'm getting "Could not post XXXXXXXX reconciliation because a Bank Check detial has a blank G/L account".

I'm hoping that Accounting will be able to fix that one.

Thanks!
j0rg3