Comment 7 for bug 1174498

Revision history for this message
Blake GH (bmagic) wrote :

When trying to load this new table with existing data I get many unique constraint errors. I cannot seem to come up with a way of finding those unique constraint breakers. I think it has something to do with transactions containing refunds. I did have success inserting rows into the table by narrowing the query a bit:

CREATE OR REPLACE FUNCTION tmp_populate_p_b_bt () RETURNS BOOL AS $$
DECLARE
    p RECORD;
BEGIN
    FOR p IN
        SELECT DISTINCT xact
          FROM money.payment
          WHERE NOT voided
                AND amount > 0.0
    and xact in(select id from money.billable_xact where xact_start>'2014-01-01')
    and
    xact not in
    (
    select id from money.billable_xact where id in
(
select xact from (
select xact,count(*) from money.billing where amount>0 group by xact having count(*)>1
) as a
)
and id in
(
select xact from (
select xact,count(*) from money.payment where amount>0 group by xact having count(*)>1
) as b
) and xact_start> '2014-01-01'
)
    LOOP
...
...
..

This will only populate transactions since the beginning of the year. Of those transactions, only ones that didn't have multiple billing lines AND multiple payments.

In case anyone finds it useful.