GP2016 – Feature of the Day -Export/Import in SLD, AA Access & More

I was busy with various activities that I missed some interesting/exciting FsOTD on Inside Microsoft Dynamics GP blog.

I am particularly excited about the last two in the above list; AA Access and Smartlist Designer Export/Import.

The gap between Smartlist Designer and Smartlist Builder is slowly (yet steadily) getting filled. While Smartlist Builder is one of its kind, Smartlist Designer now gives customers a lean version of Smartlist Builder that would satisfy most of the requirements. I am happy as a consultant that I now have an improved Smartlist Designer to talk about with customers/prospects.

Analytical Accounting User Access Settings – Steve Endow perfectly expressed his (and mine as well) thoughts on this tweet. Can’t agree more.


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.


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.


Analytical Accounting “aagCreateTree” Dimension Creation Error Message & Reason

I received an email from one of the users with an attachment that displayed an error message while trying to create a new Analytical Accounting Dimension. Below is the screenshot that I received:

Upon debugging the stored procedure aagCreateTree, I realized that another stored procedure named aagGetCompanyStatus returned this error number 22004.

This stored procedure validates the version of Analytical Account and the status of particular company, from where you are trying to create a dimension. In my case, there was no record of this company at all.

I finally realized that Analytical Accounting company information was missing on table AAG00104 in DYNAMICS database. I am not sure how and why this could be missing, unless I forgot to run the AA company setup routines (which I did, to my best knowledge).

Solution: I had to rerun the AA company setup options again and recreate the records.

Just in case, someone faces this issue, make sure that you check AA company setup records.


Check Links for Analytical Accounting in Microsoft Dynamics GP 10 & 2010

Inside Microsoft Dynamics GP blog has got a post that cautions us about two important things:

1. Don’t run Check Links process for AA at this time on versions GP10 and GP2010.

2 Apply latest Service Packs or Hotfixes for Microsoft Dynamics GP (versions 10 and 2010) to get AA related latest fixes.

More on this here: Information about Check Links for Analytical Accounting in Microsoft Dynamics GP.


Analytical Accounting – Budget Vs. Actual

Siva has added one more to his awesome list of AA posts. Check out his SQL View to analyze AA Budget Vs. Actuals.

I am sure this is going to delight people across world who use AA and are dying to see some kind of ways to enhance their AA analysis capabilities.


Analytical Accounting YE Close – AAG30000 Primary Key Violation Error

UPDATE (06/14/2011): A comment from Kumar clears this entire thing. There is a KB article which explains how to update Intercompany ID on a Test Company once we restore Live Data. Link to that KB article: Set up a test company that has a copy of live company data.

Our Finance Team and I decided to first do perform YE Close on our test company first before we make it to Live, to anticipate any possible issues and also learn this entire process as this our first YE close in GP2010.

You must by now anticipated what I had done to simulate Live Company. YES, I created a Test Company on same SQL Server (Mark, trust me, your post came after this exercise) and restored live company data on to Test Company.

This we normally do for any transaction level exercise that users would want to perform for their own sake.

The process started and after a good enough time of about 5 hours, it threw an error message, which said roughly Primary Key violation of key PKAAG30000 on table AAG30000.

Upon digging more and keeping a SQL Server profiler, I met this silly yet critical reason: AA was trying to create an entry on table AAG30000 in Live Company database. Why? I have no clue why it would ever happen.

My only guess is that every Journal Entry in GL table will have Live Company Database ID (such as TWO that denotes sample company). AA might have thought that this one is an Intercompany transaction and it requires to create an AA entry in Live Company for that portion of JRN that was processed in Test Company. The only field in AAG30000 that’s considered as Primary Key is aaGLHdrID. My live company would have gone past the next aaGLHdrID which is in Test Company (a back dated restoration) by the time I ran YE close on my Test Company.

I am yet to prove my own theory above, but I strongly doubt so.

Upon suspicion, I decided to create a TEST SERVER (Mark, your post had come exactly at that point of time). I just believe Mark Polino’s Weekly Dynamics article was so timely and I couldn’t agree for more.

After configuring a Test Server environment, I restored DYNAMICS and Company DB, pointed my GP to this test server and ran YE close as if I was running it on my production server.

Good god, YE Close got over without any AA error. I then had no other option than to believe that Test Company with Live Data do not augur well for exercises such as YE close and is in fact quite risky.

Addendum: With GP 12 Multi-Tenant architecture, we don’t have to worry about this, as we can easily setup a TEST SERVER along with LIVE SERVER in the same physical server.