CBM Cash Receipt AA Journal Issue & Resolution


Couple of days back, a user reported an issue. When she tried to open CBM Cash Receipts window, following message appeared:

Screen Shot 2014-11-25 at 5.48.52 PM

For all other users, when they posted any Cash Receipt, an Analytical Accounting Journal report would open with some entries on it; particularly with 5 cash receipts. User, who reported the issue, confirmed that the records shown on AA journal report were posted by her and only after posting them, the above error message appeared.

Since the report is AA journal, I first checked the AA Sub Ledger tables (AAG20000 series) and confirmed that the entries shown on the AA journal report are present without any issues. If I get rid of the above error message, things would be normal and this particular user can continue with her entries. Task on hand is to find out AA journal report tables and see the stuck/orphaned records and clear them.

Issue: Records got stuck on AAG50000 (AA Sub Ledger Report Header), AAG50001 (AA Sub Ledger Report Distribution) and AAG50002 (AA Sub Ledger Report Assignment) tables.

Resolution: Remove them. Simple. What you MUST note here is that all records (including genuine ones) would be present on these tables. You MUST note down AA Sub Ledger Header ID of the ones that are stuck and use below query to delete records.

DELETE AAG50000 WHERE aaSubLedgerHdrID IN (<aaSubLedgerHdrID>,<aaSubLedgerHdrID>,...) 
DELETE AAG50001 WHERE aaSubLedgerHdrID IN (<aaSubLedgerHdrID>,<aaSubLedgerHdrID>,...) 
DELETE AAG50002 WHERE aaSubLedgerHdrID IN (<aaSubLedgerHdrID>,<aaSubLedgerHdrID>,...)

Replace <aaSubLedgerHdrID> with respective AA Sub Ledger Header ID of the stuck records.

NOTE: Always take a backup of records before you delete.

VAIDY

Cashbook Bank Transfer Error: Date entered is not a current period for checkbook [Checkbook ID]


This was a menace for the past 4 days, to say the least.

I was reported of this issue, where a user when trying to enter a bank transfer for a checkbook, on a date in previous month, he got this error message (in the title of this post) as shown below:

The above message occurred only for one particular checkbook and not for others.

First step was to ensure the fiscal period was not closed. It was not.

Second step was to ensure that they had not run the reconciliation for this checkbook, so genuinely it had closed the period. It was not.

Third step was to run check links for Checkbook Master, to ensure that things are fine with Checkbook ID master record. It was perfect.

Fourth step was to take SQL Profile and see where exactly this message pops up. When I took twice, the results were different. Due to Analytical Accounting. But that I never got to know since, my focus was more on CB tables.

I referred to the ONLY AVAILABLE forum post, Date Entered is not for Current Period for CashBook, which was way back in year 2010. No solution was provided though.

I decided to take the SQL profile again, but this time without Analytical Accounting to avoid extra lines on the trace file. I took the profile twice and both matched line by line.

The last table where the buck stopped was CBINT605 (Reconciled Amounts) on DYNAMICS database. When I queried this table manually, following was the result:

As you see above, there was a record for this checkbook ID, with a date 30-Apr-2012. Due to which, if you enter any date on or before 30-Apr-2012, it fails.

I checked the user again to ensure that he had not run any reconciliation for this checkbook. He assured that he didn’t, but he also mentioned that he was demonstrating this process to a user, but did not complete it. Bad. Demonstrating on a live company is very bad.

I took a backup of this table and then deleted this record.

Now, my checkbook accepts any date for it’s bank transfer entry.

MORAL OF STORY #1: Messages never lie. If there is a message, it will most probably be correct.

MORAL OF STORY #2: Never ever do any R&D on live company. Never ever demonstrate anything on a live company. That’s why test companies are for.

VAIDY

CBM – User [username] is busy with batch [batchname].


As promised in my previous article, here we go.

This is another frequent error message that Users get when they enter CBM Manual Payments in a CBM Batch:

CAUSE: There will be an Activity record inserted when you open a Batch for Payment Entry, in the table CB300006 (Display Name: Batch Entry Records Locked). This does not get cleared at times.

SOLUTION:

I. Follow the Resolution Steps I & II from this article: http://www.vaidy-dyngp.com/2010/04/cbm-checkbook-checkbook-id-is-already.html.

II. Run the following query once above steps are completed.

DELETE CB300006 WHERE USERID = ‘[username]’ AND BACHNUMB = ‘[batch number]’

We are good to go now.

VAIDY

CBM – Checkbook [checkbook id] is already in user by user [username].


I often come across this request from GP Users:

This happens when the User try to reconcile from CBM Reconcile. And once he/she selects the Checkbook which needs to be reconciled.

After some SQL Profiling & Dex Script Log, I found the following:

Cause:

The table CB100006 contains the User Checkbook Activity records, as and when a User reconciles a Checkbook. This table for some reason is not cleared properly. Sometimes:

1. If you open the form, enter the Checkbook ID and just close it without any activity, this record is not cleared.

2. If you open the form, enter the Checkbook ID and just close GP directly without any activity, this record is stuck.

The above scenarios are faced by me and have not heard of this from any other consultants, so the above need not be recreated consistently.

Resolution:

I. Consultants must open SQL Management Studio, log on to the Data Server and connect to relevant company DB.

II. Run the following queries against DYNAMICS and the respective companies:

/*
   This below queries will delete all stranded and unwanted SQL Sessions.
*/
DELETE TEMPDB..DEX_LOCK WHERE SESSION_ID NOT IN (SELECT SQLSESID FROM DYNAMICS..ACTIVITY)

DELETE TEMPDB..DEX_SESSION WHERE SESSION_ID NOT IN (SELECT SQLSESID FROM DYNAMICS..ACTIVITY)

/*
   The below queries will ensure that the respective user, against whom the error message was thrown, would be cleared from GP Application Session(s).
*/
SELECT * FROM DYNAMICS..ACTIVITY WHERE USERID = ‘[username shown in the CBM error message]’

–Ask that user to log off, if this user has logged on for the day.

III. Run the below query to clear the CBM Checkbook Lock:

DELETE CB100006 WHERE USERID = ‘[username shown in the CBM error message]’

DELETE CBEU1020 WHERE CHEKBKID = ‘[stuck checkbook ID]’ AND USERID = ‘[username shown in the CBM error message]’

That’s it. We are good to go with our Checkbook Reconciliation in CBM.

NOTE: I will be posing another article on CBM Payments Batch Lock Error (which is identical to this error message).

VAIDY