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.

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.