FX differences: correction program to be included in Fixes.sql?

[Erik Huelsmann <..hidden..>]

Hi,


Because I had fractional-cent differences between the AR amount and
the payment amount posted, my invoices kept showing as candidates for
clearing, even though the foreign currency amount added up to 0. The
tip of the 1.3 branch has this issue fixed for general AR/AP
transactions (although I'd very much like verification+confirmation of
the fix).

Even though the 1.3 branch tip has the fix, my database was still off.
So, I worked out the plpgsql function below to fix my ledger. It does
nothing more than select the last AP and fxgain/loss transactions and
use all the characteristics in those postings to create a
fractional-cent posting to make the numbers exact matches.

Remarks? Comments?


BTW: there's one problem running it from Fixes.sql: my ledgersmb user
which I use to run db upgrades isn't a lsmb user. However, when
inserting records, the audittrail trigger is being fired, which tries
to post records in the audit tables. This fails, because the lookup of
the current session user in the users table returns NULL which
violates some key constraint. So we probably need to think where to
include it if not in Fixes.sql.


Bye,


Erik.



CREATE OR REPLACE FUNCTION fixup_ar_ap_fx_differences(link_name text)
RETURNS VOID AS
$$
DECLARE
  ap_acc_id int;
  fxgain_acc_id int;
  fxloss_acc_id int;
  transid int;
  diff numeric;
  trans_date date;
BEGIN
   -- Variable initialization
   SELECT id INTO ap_acc_id
     FROM account acc
       JOIN account_link al
         ON (acc.id = al.account_id)
     WHERE al.description = link_name;
   SELECT value::int INTO fxgain_acc_id
     FROM defaults
     WHERE setting_key = 'fxgain_accno_id';
   SELECT value::int into fxloss_acc_id
     FROM defaults
     WHERE setting_key = 'fxloss_accno_id';

   FOR transid, diff IN
     -- select transactions with rounding differences
     --   which have fx effects. Hopefully there are no
     --   others and we're definitely not out to fix those here.
     SELECT trans_id, sum(amount)
     FROM acc_trans
     WHERE chart_id = ap_acc_id
       AND trans_id IN (SELECT trans_id
                         FROM acc_trans
                        WHERE chart_id = fxgain_acc_id
                           OR chart_id = fxloss_acc_id)
     GROUP BY trans_id
     HAVING abs(sum(amount)) <= 0.005 AND sum(amount) <> 0
   LOOP
     RAISE NOTICE 'trans_id %, diff %', transid, diff;

     DECLARE
        fx_gainloss_line acc_trans%ROWTYPE;
        ap_line acc_trans%ROWTYPE;
        diff_multiplier int;
     BEGIN

       -- select the most recent gain/loss posting
       SELECT * INTO fx_gainloss_line
         FROM acc_trans
        WHERE trans_id = transid
          AND (chart_id = fxgain_acc_id
               OR chart_id = fxloss_acc_id)
        ORDER BY transdate DESC
        LIMIT 1;

        -- select the most recent ar/ap posting
        SELECT * INTO ap_line
          FROM acc_trans
         WHERE trans_id = transid
           AND chart_id = ap_acc_id
         ORDER BY transdate DESC
         LIMIT 1;

        -- Create postings required to make the difference go away
        INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
             source, cleared, fx_transaction, project_id, memo, invoice_id,
             approved, cleared_on, reconciled_on, voucher_id)
         VALUES (fx_gainloss_line.trans_id, fx_gainloss_line.chart_id,
             diff, fx_gainloss_line.transdate, 'correction program',
             fx_gainloss_line.cleared, fx_gainloss_line.fx_transaction,
             fx_gainloss_line.project_id, fx_gainloss_line.memo,
             fx_gainloss_line.invoice_id, fx_gainloss_line.approved,
             fx_gainloss_line.cleared_on, fx_gainloss_line.reconciled_on,
             fx_gainloss_line.voucher_id);

        INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
             source, cleared, fx_transaction, project_id, memo, invoice_id,
             approved, cleared_on, reconciled_on, voucher_id)
         VALUES (ap_line.trans_id, ap_line.chart_id,
             diff * -1, ap_line.transdate, 'correction program',
             ap_line.cleared, ap_line.fx_transaction,
             ap_line.project_id, ap_line.memo,
             ap_line.invoice_id, ap_line.approved,
             ap_line.cleared_on, ap_line.reconciled_on,
             ap_line.voucher_id);
     END;
   END LOOP;

   UPDATE ar
      SET paid = amount - (SELECT sum(amount)
                             FROM acc_trans ac
                             JOIN account acc ON (ac.chart_id = acc.id)
                             JOIN account_link al ON (acc.id = al.account_id)
                            WHERE al.description = 'AR'
                              AND ac.trans_id = ar.id)
   WHERE amount <> paid;

   UPDATE ap
      SET paid = amount - (SELECT sum(amount)
                             FROM acc_trans ac
                             JOIN account acc ON (ac.chart_id = acc.id)
                             JOIN account_link al ON (acc.id = al.account_id)
                            WHERE al.description = 'AP'
                              AND ac.trans_id = ap.id)
   WHERE amount <> paid;

END;
$$ LANGUAGE plpgsql;

SELECT fixup_ar_ap_fx_differences('AR');
SELECT fixup_ar_ap_fx_differences('AP');