Why it pays to read old quality reports

So, every once in a while, you stumble across an old quality report referenced in a knowledge base article. Usually I look at it, see it was resolved with a service pack to version 6, or 5.5, and I think "oh, well, that's not the problem". But today taught me that sometimes old quality reports will shed some light on a current situation.

So, here is the scoop. A client emails me asking why they see duplicate records in the RM30501 (Commission History table). They are paying commissions when invoice is posted (not when invoice paid). They see something like this in the table:

STDINV2599 with commission amount $100
STDINV2599 with commission amount $100
STDINV2599 with commission amount ($100)

Each entry has its own sequence number. So I do some testing. I enter an invoice, post it, void it, and see if I can come up with the same entries in the RM30501. No luck. So I told the client I needed to take a look on their system. We poke around for a bit, chasing one wild hair after another with no luck.

And then I start searching randomly in the knowledge base and I find an old article referencing an issue on version 6 that was resolved with a service pack. In the scenario they gave, if a payment was voided that was greater than the invoice amount, it would lead to negative commissions appearing on the commission report even if commissions where supposed to be paid when the invoice was posted (not when invoice was paid). Not quite the issue that started this, but promising enough. So here is what I tested:

1. Enter invoice in SOP and post
2. Pay invoice with payment greater than invoice amount (so a portion of the payment is unapplied) and post
3. Move invoice to history using Paid Transaction Removal (this step will move the commision record from the RM10501 to the RM30501)
4. Transfer commissions to mark the commission as paid
5. Void the payment recorded (this step will move the commission record back from the RM30501 to the RM10501, but more importantly this is where two additional records are created for the commission in the RM10501- one positive, one negative, so the net is correct but the detail is not)
6. Transfer commissions again, this time a negative commission will appear (even though we are paying commissions when posted not paid, so the voiding of an invoice should not matter)
7. Print Reports>>Sales>>Commission>>Commission Dist by Salesperson and you will see all three transactions for commissions: the original plus the two erroneous entries created by the void

Strange, huh? The commission distribution report is not a huge issue since it nets correctly, although it is troublesome that the detail is incorrect. A bigger issue is the transfer commissions journal, since that incorrectly shows a negative commission transferred and is often used as an entry list for paying commissions from payroll or payables.

Anyway, Microsoft is able to reproduce the issue, so we'll see what resolution they provide. I am a bit curious if the quality report came back after being resolved, which makes me wish I had a version 8 install to test on as well. I will definitely update this blog with any additional news. In the meantime, you may want to monitor closely if you use the transfer commissions journal to pay commissions. And definitely share any stories you have of long lost quality reports rearing their ugly heads :) I will share any posts I get.

Have a great holiday weekend!

Project Accounting Cost Category and Fee ID issue

Well, I stumbled across a known issue last week that I thought I would share. A client called last week with a strange issue. Generally, this client solves most of their own issues, so when they call, I know it is going to be an interesting and complex issue.

So here is the scoop..in Project Accounting, actual billings equaled payments/receipts received. Great. And the detail supported this summary. However, they ran reconcile on Cash Apply (Tools>>Routines>>Project>>PA Reconcile) and the payments/receipts were recalculated to be less than the actual billings. The difference was the same as the FREIGHT fee billed on the project. This was most apparent on a simple project that had one invoice, one payment. The client had noticed that it was happening on all projects with the fee called FREIGHT.

We started poking around in the database, looking at the underlying setup of the FREIGHT fee and comparing it to other fees, trying to determine what might be different. We could not see anything different. We ran a dexsql log during the PA reconcile process, to see what stored procedures/tables were being referenced. We looked through all the tables again, still no luck. So then we started to wonder what was unique about the fee ID of FREIGHT. At that point, the client said something like "you know what, this fee is named the same as a cost category". Bingo!

So we made some quick changes in the tables (test environment, of course) so that that fee ID was FREIGHT2 instead of FREIGHT. We re-ran the PA reconcile, and the payments/receipts received were now correct! What an odd little issue.

It is actually a recently documented quality report (#49611). I thought I would share the wisdom that, for now, don't name your fees and cost categories the same. For those that already have the issue, there are some scripts available from MBS Professional Services that allow you to change Cost Category IDs (and therefore correct the problem for projects that already have activity).

Co-author credit on this one has to go to Dave (the client), for the teamwork to determine the root cause :)