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

["R. Ransbottom" <..hidden..>]

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.

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

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

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.

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.

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.  Sales and purchasing need line item FX amounts just
for presentation to the customer or vendor.  Journal lines should
be immutable.

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?  This might allow users to go back to some 
closing or backup, and replay transactions on a fixed version.

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

Thanks again,
Rob

------------------------------------------------------------------------------
_______________________________________________
Ledger-smb-devel mailing list
..hidden..
https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel