Category Archives: AA

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

Posted on by

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.

VAIDY

Advertisement

CBM Cash Receipt AA Journal Issue & Resolution

Posted on by

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]

Posted on by

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

Analytical Accounting “aagCreateTree” Dimension Creation Error Message & Reason

Posted on by

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.

VAIDY

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

Posted on by

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.

VAIDY

Analytical Accounting – Budget Vs. Actual

Posted on by

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.

VAIDY

Analytical Accounting YE Close – AAG30000 Primary Key Violation Error

Posted on by

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.

VAIDY

Account Segments Vs. Analytical Accounting – My Thoughts

Posted on by

This post from Mohammed R. Daoud sums it all about Account Segments Vs. Analytical Accounting.

I just wanted to share my thoughts:

Account Segments are simply real. You are going to have separate accounts for specific entity and that means you must be setting these accounts up on several other places in GP other than only Posting Accounts setup. For instance, if you are going to have accounts for each employees of your organization and you track expenses on them, then you got to have each account setup against each employee explicitly and setup GP such that it takes accounts from employee directly instead of Posting Accounts.

Analytical Accounting is purely logical. You don’t really have separate account for each entity that you wish to. Setting up AA dimensions and codes are one time.

Actual complexity lies in assigning AA codes on a particular transaction. Worst case, if you have an apportionment across different AA codes, you have manually apportion it in Analytical Accounting Transaction Entry form. Data Entry is very complex at times with AA. Without proper AA setup, your data will logically scattered and any analysis on this data would be a menace for you to understand.

You can minimize the data entry time by using Analytical Accounting ALIAS, but that again depends on case to case basis. You cannot always depend on AA ALIAS for all kinds of transactions.

Simply speaking, AA considerably reduces your COA but let you toil at times entering apportionment within a transaction. You gain on one side, you have to experience some pain on the other side.

VAIDY

Analytical Accounting Menu Master Table (SY07110) Records Multiplying – Bug & Workaround

Posted on by

This one is another SY07110 (syMenuMstr) table issue.

For the past 1 week, users were complaining about GP slow down as soon as they select a company and click OK. The delay was anywere between 30 seconds to 1 minute, which is quite huge, considering the fact that GP is suppose to get initiated within 3-5 seconds maximum.

We have two different launch files; those users who are not suppose access AA and those who access AA. This delay was reported by only AA users. And that eased my debugging task out.

Troubleshooting started by taking a SQL Profile exactly at the time of selecting a company and clicking on OK. After painfully long time, GP got initiated with all Menu and other startup objects. I stopped SQL Profiler and noticed the following piece of SQL query being executed for as many as 143 times, which in total resulted in 4730 records:

The above query is same except some of the fields. But all these SQL SELECTs where targeting the same product; AA (CmdDictID = 3180).
Quite shockingly, I had 4730 records in SY07110 for Dictionary ID 3180, i.e. AA.
So how to solve this? Simple. I took a backup of this table (just for precaution) and executed the following SQL query:
DELETE DYNAMICS..SY07110
WHERE CmdDictID = 3180
It removed all 4730 records for AA module. I then launched GP. Bingo. It’s getting initiated in as long as 3 seconds.
The story does not end here. For the first time SY07110 table gets inserted with 33 records for AA module. As and when a user launch GP, with AA module, it inserts exactly ONE additional row onto SY07110 table.
Now this certainly looks like a bug.
So what’s the solution? Time being, I have to write a scheduled SQL job which will execute the above DELETE query on a weekly or daily basis.
I am not sure who else is currently facing this issue. If any of you people have this issue, then at least you have a workaround.
I am planning to raise a Support Ticket with MSFT team if no one out there gets back to me with a solution and make sure that this is not a bug as such.
VAIDY