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 :)