Re: Thoughts about data migration when master-mc lands on master

On Thu, Dec 3, 2015 at 9:53 PM, R. Ransbottom <..hidden..> wrote:

On Sun, Nov 29, 2015 at 03:44:15PM +0100, Erik Huelsmann wrote:

> It's my hope that master-mc can land on master soon after we release 1.5.0

> To that extent, I'm thinking about data migration from our current
> transaction registration system to the new one. So far, I have identified 2
> strategies:

> 1. Take the balance in base currency, the currency rates and the
> transaction dates; from those, calculate the correct foreign currency
> amount for the transaction.
> 2. Take the balance's base and foreign currency amounts, use those to set
> up the new transaction records and resolve differences by running a
> revaluation.
>
> While the second approach seems like the correct approach, since it
> replicates best what's in the current balance, this approach may not
> actually be feasible: there was a bug in - at least - 1.3 and 1.4 (which we
> recently fixed in 1.4) in the way payments are handled. The bug means that
> the base currency amount is correct and the foreign currency amount is
> wrong (for the payment line only). What's wrong is that the foreign
> currency and local currency amounts will be equal for the payment
> transaction, independent of the exchange rate. However, in the second
> approach, the revaluation will use the foreign currency amounts and use
> those to calculate the "correct" base currency amount.
> It's easy to see how this approach fails to create a correct migrated
> balance.

> The first approach however, is rather complex to code - with inherent bugs
> of its own - because there are multiple sources for fx transactions, each
> with their own special cases (AR, AP, receipts, payments, GL). Especially
> receipts and payments are complex due to the handling of FX gains and
> losses -- for which the *current* account setting may have changed,
> rendering it hard (if not impossible) to determine which account was/were
> the fx account(s).
> Additionally, recalculating the amounts from the base amount and the rate
> could result in slightly different foreign currency amounts -- if only due
> to rounding.

> If we don't want to go with approach (1), I also see a variant of (2) where
> we "fix" the data coming from the incorrect payments first and run a
> migration using that design after that.
>
>https://mail.google.com/mail/u/0/#search/ransbottom/15153b390621d4be
> Any help determining other options and approaches would be very much
> welcome!

What you say early in your post gives me pause: "resolve differences ...
by revaluation", "inherent bugs", "replicates best", "slightly different".
Those statements imply that a replication of what the transactions should
have been may not be possible; that an audit trail is incomplete.

Well, there are a few problems about the way LedgerSMB (and SQL Ledger, for tthat matter) currently deals with multi-currency transactions. Your mail made me look up the exact handling of fx transactions. Here's how they're being handled:

* the foreign currency amount posted on an account is stored in one record

* then the difference between the foreign currency amount and the local currency is calculated and stored in a separate record, indicated by a field "fx_transaction". No reference to the original transaction is stored.

If the transactions are not reconstructable, it can be expensive in
a tax audit.

I'm not sure what you mean here. Do you mean that the way LedgerSMB currently handles transactions involving foreign currencies, is problematic? Or that it is acceptable, but that my plans to code the migration should be improved? (Either is fine, btw)

Not necessarily any penalty but more work digging into
paper to prove that GL accounts are close to correct.

I think I understand what you mean, but maybe having an example to discuss about could help me assess that I'm correct. Here's my throw at an example, maybe you want to throw in a counter example for the sake of discussion:

Assuming a company with base currency USD and an invoice in foreign currency EUR, we have the following sequence of events:

* Invoice posted on 2014-09-01, 40 EUR @ 1.50 USD / EUR

* Payment posted on 2014-09-15, 40 EUR @ 1.25 USD /EUR

What happens on the invoice creation posting, is that the AP account is posted with 2 line items:

1. with an amount of 40 (fx_transaction == 'false')

2. with an amount of 20 (fx_transaction == 'true')

My proposal #2 is to migrate these two lines as follows:

1. a line with base currency amount 40 and foreign currency amount 40 (currency indicator: EUR)

2. a line with a base currency amount 20 and foreign currency amount 0 (currency indicator: EUR)

This is the closest I have for this situation (I haven't looked at the PNL side of things). An alternative (lets say #2a) option I have for this situation is:

1. a line with base currency amount 40*1.50 and foreign currency amount 40 (currency indicator: EUR), where 1.50 is the registered currency rate for that day for EUR

(and ignore the fx_transaction)

Then we need to migrate the payment transaction. The payment posts just a single line on the AP account:

1. with an amount of 50 (fx_transaction == 'false')

My proposal #2 to migrate this item would result in:

1. a line with base currency amount 50 and foreign currency amount 50 (currency indicator: EUR, because the transaction is part of an AP set and posts to the AP account)

In this case, there is no second line. This results in a problem, because it says that the foreign currency amount is 50, which it isn't. Revaluation would simply assume the foreign currency amount to be the "true" value and would calculate a "new" base value amount, so that revaluation can't be used to clear out any differences caused by migration.

The migration proposal #2a would result in:

1. a line with base currency amount 50*1.25 (=62.5) and a foreign currency amount of 50

This too is incorrect, because in this situation both the base currency and the foreign currency amounts are too high.

Your variant of (2) wins, whether it is an in place correction or a
massaging during migration. If that cannot be verified as correct, then
rewinding and replaying the transactions in a bugless, cloned company is
a strategy.

I think we can't really expect our users to understand our schema and "massage" the figures into the right numbers, especially since we won't be around to tell them the causes of the differences they might be seeing. My assumption is that we need to provide a migration that's going to produce the correct result in 99.9% of all cases. If there has been any fiddling going on before, we can't really service that, of course.

I wanted to add a description here about what my proposal #1 would do to handle this situation. I'm unable to create a write-up without immediately seeing the problems of the various solutions though :-( I'll spend some more time thinking about the various solutions and cases we need to pass in order to have a satisfying solution.

That would allow the construction of correcting entries
against any closed mistakes. If there is not the data to do that then
working from any "paper" trail or head-in-sand look like the last options.

Thanks,
Rob

Thank *you*! This discussion helps me sort my thoughts. I'll come back when I've made a few more steps regarding possible solutions.

Bye,

Erik.

http://efficito.com -- Hosted accounting and ERP.

Robust and Flexible. No vendor lock-in.