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

Hi Rob,

Thanks for your response. I had planned to come up with a new description of my approach, but have been working on resolving immediate complaints with 1.4; I'm going to do the write-up next week, probably. In the mean time, I like our discussion: it helps me fine-tune my reasoning about the migration.

On Sat, Dec 12, 2015 at 4:41 PM, R. Ransbottom <..hidden..> wrote:

On Sun, Dec 06, 2015 at 05:37:18PM +0100, Erik Huelsmann wrote:
> 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:

> * 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

I mean, from a user's perspective, having a govermental tax agency
audit find some little discrepancies in FX could easily result in
lots of extra work trying to justify the general ledger FX accounts.

Ah. Right. My plan isn't about the rate being applied. So, I think that should ease your mind. I think the new schema actually helps address the above worry by allowing a rate per transaction(line) instead of a rate per day: the rate per day could be a problem because you could have 2 bank accounts with different banks, which use a different rate on the same day (the current schema doesn't support that; the new schema does).

> Assuming a company with base currency USD and an invoice in foreign

A sidenote: What you call "base currency" and I like to call
"native currency"; I have learned the accountants' term is
"functional currency". They use the term "presentation currency" or
"reporting currency" for the currency used in the financial reporting.

Ok. I think that using the term "functional currency" is ok, but I've used the shorthand "bc" for base currency in the code, because "fc" looks too much like "foreign currency".

> 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')

That is bizarre and cumbersome in its storage of the currencies.
A simple: 60.00 ( EUR 40.00) per line item should suffice.
There is no advantage to splitting the amount payable nor to
expressing the ratio indirectly.

It is. Which is why I desperately want to get away from the current schema and onto the new schema, which doesn't record the two lines, but just a single line, even for foreign currency transactions. Well, so far, there have only been draw-backs, but the one major advantage - I guess - for the current scheme is that there was no need to do a full rewrite of SL when they came up with this idea. Nice low impact: just a single column with a default value (fx_transaction). Note that this isn't *my* position, I'm just imagining what went on in the original author's head when deciding for this schema.

I don't think I'll be any direct help on your problem. I think you
are trying to work within the table definition of acc_trans, but
I am not sure.

I'm trying to move away from acc_trans and describing what's currently in acc_trans that I have to work with if I want to move to something very much like what you describe as what should be required.

That payments are applied to line items is a complete waste of
time. It seems the fundamental problem is that FX should be
handled in another document.

I'm not completely on board here. My position is that an accounting document is a grouping of all the accounting effects of one real-world transaction. This means that all fx effects of a single payment should be accounted for in the same document as where the actual payment is recorded. If things don't work that way, it'll be very hard to determine which fx consequences belong to which payment transactions.

For accounting, the FX rate, currency, and amount need only be in
the journal entry header. The journal lines don't need any info
about FX.

I'm not sure I agree with you here. In general I do, but in particular, I wonder about revaluation transactions (revaluation of financial assets / liabilities): they require a zero-amount foreign currency but a non-zero amount base currency, because the amount being posted is a consequence of the exchange rate changing.

Additionally, if you consider - like me - that the foreign currency gains/losses should be posted as part of the payment transaction, multiple rates will need to be used in a single transaction: the rate of the payment date and the rate of the date of the sales invoice.

Sales and purchasing need line item FX amounts just
for presentation to the customer or vendor. Journal lines should
be immutable.

Agreed and they are: Even currently with the two-line-per-line schema, the data in the schema is immutable. It's the migration that is posing me problems, not the schema itself.

(I'm not sure what you mean by "fx amounts just for presentation" in the sense that the fx amounts are an integral part of the accounting, as far as I'm concerned - and I would imagine every auditor too)

This bug and migration issue draws me in another direction. I know
postgresql has logging but not much more. Could LSMB be set up to
log all transactions?

There's a table to which audit information is written. Please have a look at it and tell me if that's what you mean. Other than that: LedgerSMB only ever accumulates data (never deletes it) [apart from migrations]. Myself, I regularly create a database dump so I have a restore point (once per quarter, maybe).

This might allow users to go back to some
closing or backup, and replay transactions on a fixed version.

There's the option to configure postgresql to use WAL archiving. When you use that, the logs can be replayed to a specific point in time. From that point, you can continue accounting as if nothing happened.

That would allow a user to replay as much history as wanted. Make
an adjusting entry to "open" the books at some date, leaving earlier
periods as informational only. The previous install would be maintained
to support reporting.

> 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

> 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

Sorry, I was unclear. I meant that the errors might be fixed (massaged)
in place versus being corrected when migrated.

Ah! I should think to what extent the problems that we're looking at are problems due to the design of the schema and which ones are problems just because I want data to be migrated.

I'll come with a new migration design document in a week or so. I'd very much appreciate it if you were going to read and discuss like in this thread!

Like you I don't expect
users to know sql. I would not expect any fiddling, that would tend to
fall outside the FXness of a transaction.

Ok. That's the intent of the migration indeed.

Bye,

Erik.

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

Robust and Flexible. No vendor lock-in.