Critical Financial Report Issues After Updating to WHMCS 9.0.0

[bluesteam]

30 minutes ago, wtools said:

@bluesteam Regarding the below section,  I am lil bit confused
 

They are saying the difference is because you made some changes, but have you made any change to the income display? 

 

I never made any changes.  You are confusing the statement the support staff made to the simple query I performed to see my actual income from the reported income in the dashboard.

The query was a direct query to return the income results for the month.  There is no changes I made from my side. I saw a discrepency from the annual income reports compared to actual money received and that's when I directly ran a query on the tblinvoices table and saw the difference. The support staff member is referring to that query that I performed.
I don't have any custom displays.

I need to fix the invoices that have incorrect credit notes and support is not telling me how to do that.

Edited June 22 by bluesteam

[wtools]

@bluesteam Okay, now I understand what you mean.  WHMCS is using the tblaccounts table for income calculations, as it has transactions based on each day's conversion rate. You have to use SQL quires on the tblaccounts table.  Do you have multiple currencies or just one currency? 

Edited June 22 by wtools

[bluesteam]

@wtools 

We use a single currency.

I have gone deeper in to the database to see where these credit notes are stored and found 3 tables

tblaccounts (The actual credit transaction)
tblbillingnotes (The summarised credit note lines shown on the invoice under Ledger)
tblbillingnoteitems (The detailed ledger item in question)

NOTE: I COMPLETELY understand that directly editing a database is NOT the way to go but WHMCS is leaving me no choice here. I can't raise NEW invoices for invoices that have already gone out to clients AND receipted.

I suspect that deleting the transaction record and the related billing notes will remove these credit notes but I am unsure of the system impact it will have from a sequential id ordering perspective on the tables.

What do you think will happen if I remove these records? Will it break the system?

Edited June 22 by bluesteam

[wtools]

@bluesteam Don't remove those records now, I'll have a look to see what will happen if we remove these records.
But do you have a table tblcredit, does it have some credit data as well? 

Edited June 22 by wtools

[bluesteam]

Hi, yes I have a tblcredit table and that table has a billing_note_id column that relates to this new system.

The data in the tblcredit table has 3 records on it that relates to the new credit notes under the billing_note_id so we should also include this table.

in my case it's only 3 records and it's because these clients had actual credit which is why the tblcredit table has actual records there

[wtools]

I am still in discussion with @bluesteam regarding the deletion of the credit notes without causing any issue. We will share an update once everything is working as expected.

Edited June 22 by wtools

[Chris Swart]

I finally decided to take a deep dive into people having similar issues, when i reported the list of issues to WHMCS support i was basically brushed off and told the issues are not with V9 but rather my WHMCS instance.

The amount of time I've had to spend to correct clients accounts to make things balance correctly since the Accrual system implementation is crazy, in fact it lead me to now creating my own CRM that caters for every use case i had with WHMCS.

Best of luck to you all , I hope WHMCS is able to resolve all the issues and learn from this lesson for future feature releases.

[bluesteam]

Here is an update to my situation and a fix for when the "Mark Paid" button was used after the 9.x upgrade. this comes directly from Support.

I hope this helps someone

My situation was that when a client pays money for an invoice via normal EFT, I would always mark the invoice as paid using the "Mark Paid" button on the invoices page.

After the 9.x upgrade I continued to follow my normal process but quickly became aware that my income reports were misaligned with my actual income.

It was determined that when using the "Mark Paid" button on invoices, the system applies a credit note to the invoice instead of marking it as paid with a normal invoice payment transaction and this it was seen as credit applied.

After much back and forth with support, the below is the steps to rectify any invoices that are now incorrect.

IMPORTANT! This is only applicable where the "Mark Paid" button was used to mark an invoice as paid for an eft payment after the 9.x upgrade as the upgrade changes the way we need to add payments.

Quote

 

To address the situation where the Mark Paid option was used, I recommend the following steps:

• Go to System Settings -> General Settings -> Mail and enable the option "Disable Email Sending".  This is to disable invoice adjustment emails being sent out a second time to the clients. NOTE: Ignore this step if you are fine with your customers receiving a corrected email. Also note that while you are fixing invoices, your system will not be sending out any emails. So I simply disabled emails right before I added a transaction and then enabled it again right after it was done while I processed the next invoice.
• Go to the Transactions tab for the relevant client
• Click Add Transaction and enter the payment details, ensuring the invoice number is included in the Invoice ID field. This will apply the transaction to the correct invoice
• WHMCS will now treat this transaction as an overpayment because there is actually no outstanding balance and will now create a debit note.
• IMPORTANT! Since it is considered an overpayment on the invoice, real credit will also be added to the client’s account which we have to remove.
• Navigate to Manage Credits under the Summary tab of the client profile and delete the credit created in the previous step (VERY VITAL STEP!!)
• To enable emails on your system again, go back to System Settings -> General Settings -> Mail and disable the option "Disable Email Sending" again to make sure your system is sending out mails again.