Cleaning out empty invoices

For reason I do not quite understand, we accumulate quite a number of $0 In-progress invoices in the system.  Rather than deleting them manually I wrote some SQL to do so.  It is given below.  The first query shows what will go, the second does the actual delete. Note that I am selecting only invoices with no line items.  Note also that as a result of the foreign key constraints, the delete operation will also remove those records in the participations and financial_acts tables that reference the acts being deleted.  You will of course need a Tomcat restart to clear the caches so that the deleted $0 invoices disappear from the Work In Progress list.

A practice which actively manages the Reporting|Work In Progress list should not have this problem - but we let things go until we had some 18 pages of $0 stuff.

Regards, Tim G

select * from acts a
join financial_acts f on f.financial_act_id = a.act_id
left join act_relationships ar on a.act_id = ar.source_id
where a.arch_short_name = 'act.customerAccountChargesInvoice'
and a.status = 'IN_PROGRESS'
and f.total = 0.0
and isnull(ar.target_id)
and a.activity_start_time < '2014-09-01';

delete a from acts a
join financial_acts f on f.financial_act_id = a.act_id
left join act_relationships ar on a.act_id = ar.source_id
where a.arch_short_name = 'act.customerAccountChargesInvoice'
and a.status = 'IN_PROGRESS'
and f.total = 0.0
and a.activity_start_time < '2014-09-01'
and isnull(ar.target_id);

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Re: Cleaning out empty invoices

Yep, I have noticed this as a potential issue as well.  It seems to occur because the typical workflow creates an empty invoice as part of its process.  I think occassionally we might accidentaly add an item to an invoice and then remove it because the client didnt proceed to sale, so the invoice remains at 0.  

Because its a 0 balance the client never "CHECKS OUT" and the invoice is never finalized either.

Another interesting search is looking for 0 balance (no line item) finalized invoices.

 

Regards
 
Ben 
OpenVPMS Installer and Helper 
Ph: +61423044823 
Email: info[at]charltonit.com[dot]au

Re: Cleaning out empty invoices

Hi,

We too have this issue and have many $0 invoices that are finalised (to make them just "go away") and our admin assistant has as part of her daily tasks to delete $0 invoices. Not exactly a productive use of her time.

I don't think that what Ben is suggesting though is entirely why it happens. Most of ours have a normal invoice where they have been charged that has been closed and then another $0 invoice on top.

I thought they were sometimes being generated when people go back into a completed visit to finish writing notes, creating letters, etc? But not able to make this happen. It is frustrating for sure.

We had a different thread about this problem here but reached no consensus/resolution.

http://www.openvpms.org/forum/curse-0-invoice

Adrian

Re: Cleaning out empty invoices

Adrian -I must admit that I did little in the way of a forensic examination of the $0 in-progress invoices.

I did look at a couple and found a) real invoice on the same date; b) nothing on the same date, but a medical entry for a re-check visit (which I don't think they charge for).

I have asked the appropriate staff to see if they can discern a pattern.

I would be interested in knowing whether, in your case, people are explicitly finalising $0 invoices.

Regards, Tim G

Re: Cleaning out empty invoices

Hi,

did you look at your system or ours?

I did work out one way it is happening and it is staff error.... People are finalising invoices via the Customer|Charges screen. The patient is then left in the worklist. Then when people go to check out properly a new $0 invoice is created.

Staff training but.... should there be a question "Do you want to check this patient out of the worklist" if an invoice is finalised and paid via this method...?

Re: Cleaning out empty invoices

Adrian - I was looking at the system in Hong Kong. Regards, Tim G

Re: Cleaning out empty invoices

Maybe we could place a listener on invoice finalization that reflects back and marks as complete any related appointments/workflows

This would also resolve the multiple pet 1 invoice scenario where only 1 appointment ever gets checkout completed the others have to be manually changed to completed from billed.

Regards
 
Ben 
OpenVPMS Installer and Helper 
Ph: +61423044823 
Email: info[at]charltonit.com[dot]au

Re: Cleaning out empty invoices

Hi Everyone,

I just come across this issue - we have 15 pages of $0 invoices - most are in progress. I have tried running the above script via Workbench but it seems to fail. When I run it line by line, it seems the second line fails.

The line that fails for me is 

join financial_acts f on f.financial_act_id = a.act_id

and the error I get returned is;

22:11:28 join financial_acts f on f.financial_act_id = a.act_id Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'join financial_acts f on f.financial_act_id = a.act_id' at line 1 0.015 sec
 
Not sure what to do next?

Re: Cleaning out empty invoices

The safest way to remove empty invoices is to generate a listing of them from Reporting - Charges - Search, and then remove them individually via Customer - Charges.

Running SQL on the OpenVPMS database can lead to permanent data loss or data corruption if there is an error in the SQL statement.

Make sure you have a backup before proceeding.

Warnings aside, can you show the complete statement you are trying to run?

Its likely that the above SQL will no longer work in OpenVPMS 2.x as charges also include rows in the act_details table which need to be deleted first. It will generate a constraint error however, rather than the one you are seeing.

The following SQL will show the act_details rows that need to be deleted, assuming you want to remove zero invoices prior to 1/1/2020.

select a.act_id, a.arch_short_name, d.*
from acts a
join financial_acts f
    on f.financial_act_id = a.act_id
left join act_relationships ar
    on a.act_id = ar.source_id
left join act_details d
    on a.act_id = d.act_id
where a.arch_short_name = 'act.customerAccountChargesInvoice'
    and a.status = 'IN_PROGRESS'
    and f.total = 0.0
    and ar.target_id is null
    and a.activity_start_time < '2020-01-01';

This SQL will show the acts rows that need to be deleted.

select a.act_id, a.arch_short_name, a.activity_start_time
from acts a
join financial_acts f
    on f.financial_act_id = a.act_id
left join act_relationships ar
    on a.act_id = ar.source_id
where a.arch_short_name = 'act.customerAccountChargesInvoice'
    and a.status = 'IN_PROGRESS'
    and f.total = 0.0
    and ar.target_id is null
    and a.activity_start_time < '2020-01-01';

Run each statement individually, and make a note of the number of rows returned.

The following will delete both. The begin statement starts a transaction for sanity checking purposes; if the numbers returned by the delete statements don't match those returned by the select statements above, you can issue a rollback.

 

begin;
delete d.*
from acts a
join financial_acts f
    on f.financial_act_id = a.act_id
left join act_relationships ar
    on a.act_id = ar.source_id
left join act_details d
    on a.act_id = d.act_id
where a.arch_short_name = 'act.customerAccountChargesInvoice'
    and a.status = 'IN_PROGRESS'
    and f.total = 0.0
    and ar.target_id is null
    and a.activity_start_time < '2020-01-01';

delete a
from acts a
join financial_acts f
    on f.financial_act_id = a.act_id
left join act_relationships ar
    on a.act_id = ar.source_id
left join act_details d
    on a.act_id = d.act_id
where a.arch_short_name = 'act.customerAccountChargesInvoice'
    and a.status = 'IN_PROGRESS'
    and f.total = 0.0
    and ar.target_id is null
    and a.activity_start_time < '2020-01-01';

If the two delete statements return the same numbers as the select statements above, enter:

commit;

to make the changes permanent otherwise enter:

rollback;

to revert them.

 

Syndicate content