Re: Plans for financial rewrite in 1.5

On Thu, Jan 23, 2014 at 2:37 PM, Erik Huelsmann <..hidden..> wrote:

Hi Chris,

On Thu, Jan 23, 2014 at 3:48 AM, Chris Travers <..hidden..> wrote:

Hi all;

I would like to submit my plans for financial rewrite and suggest we discuss any expectations. Existing areas of testing and what we can do to ensure they are still met will be added below.

Thanks for writing this up. My current thinking it's a good start to list the requirements. I'd like to add any requirements that we know the current system already meets, so we can be sure that a new design will not drop any of the important ones.

The financial rewrite will be done in such a way as to allow 1.5 to be released even if it is not complete. However, we need to try hard to avoid making it a moving target, so hopefully this will provide some guidance there as well.

I have basically scoped out the work required, and come up with the following requirements:

1. We need to be able to handle arbitrary two-currency or even three currency transactions. For example, if one has one's books in USD and transferring money from a GBP to an EUR account, this must be supported.

Right. What's more, I'd like the design (although possibly not the immediate implementation) to allow for assets to be held in non-base currencies. E.g. to hold a USD current account in a company which is EUR based. Part of this requirement is the need to be able to revalue the asset -- report it as having a different current value than the value that it had when the posting was originally created.

Sure. I am thinking of keeping the base + foreign system. Revaluing can then be done by querying foreign values and posting an adjustment against base. That seems the simplest and most transparent way to do it.

2. We need to be able to handle per-transaction exchangerates.

Right. I'd say that we need a per-date default exchange-rate as well -- one that's picked up if no other exchange rate is specified.

Yep. I didn't mention this but per currency you'd have an allowed variance from the default rate that could be set. This would default to 15% (meaning the per transaction report must be within 15% of the set rate). This is high enough I think to cover most variances but low enough to catch most cases of entering the rate in reverse or order of magnitude issues,

To me, this requirement solves the following use-cases:

- different banks (or bank and credit card) with different exchange rates for the same currencies on the same date
- FX transaction reversal of a transaction in a closed period with a currency different from the current default

3. For reporting and referential integrity purposes, we need a more unified database structure. Some reports have test cases, notably the PNL reports.

I must say that it's not clear to me what you mean with this one. Could you try to explain what it is that you consider to be required here?

Instead of ar/ap/gl/transactions/acc_trans, I would like to have something which avoids a need to union ar, ap, and gl tables in reporting. We don't need a separate table per journal.

4. For COGS tracking, we need to be able to audit how this is done, so as bugs are reported, we can track full changes of state across time. I have COGS tests btw.

What does "audit how it's done" mean here? Do you plan to record all movements and their calculated COGS prices?

At least enough information to track it. The reason is that if someone says their COGS calculation seems off right now we have no way to say it is or is not, much less what went wrong. I don't expect this to be an issue in 1.4 but it has been an issue in 1.2.

Do you plan to write routines which produce intermediate results for specific transactions so transactions can be audited? Or do you mean anything else?

5. We need to get rid of the silly restriction that ensures you can't have accounts in different places of the ar/ap transaction screen.

I'm not following here. Do you have a bug report or feature request to point to? Are you talking customer accounts (eca's) or gl accounts?

The issue I am thinking about is something like reserves for credit card processors. Some of the money gets put into a payable reserve account and becomes payable at a later date. On the AP transaction screen this basically means you have to have a liability account on the expense side and a different liability account on the AP summary side, and move money between them as the reserves expire. It would be much cleaner to allow an account to appear multiple places on the screen. We can't do that because there is no way of tagging the line item as to which part it goes on, and so if you have an AP_amount and AP_paid account, fun things happen when you enter stuff against it.

Other requirements I'd like to keep in place:

* Period closing
* Period-end/closed period balance snapshots (for performance)
* Recurring transactions of some sort
* automatic fx result calculation based on the fx rates of the invoice and the payment

Absolutely. I haven't gotten into rewriting recurring transactions but that will likely have to be done in some way too.

What I think we want to get into place:

* Period-end balance snapshots for AR/AP on a customer/eca level
This would solve the issue that we've talked about where the "POS customer" would cumulate thousands of AR items and the POS would get slower due to that (trying to calculate the outstanding limit)

[ Another solution would be to allow a POS module *not* to use a customer ]

One thing I added for 1.4 is a setting to stop calculating credit limits. But I agree we need period end snapshots on customer level. I would say we should have them on a per invoice level too because that would speed up payment selection. But these can be added after the rewrite easier than before.

* Accrual/deferral transactions

What I mean here is that we discussed how e.g. an interest-paid-in-arrears accrues interest until the customer is really invoiced. In my current business, I have yearly payments which I distribute over the year using monthly postings. We discussed that it's possible to have accrual/deferral be part of reporting, if we would create a specific transaction type. It's definitely something I'd like to help investigate viability.

Agreed here too.

* A functional interface (before a webservice interface), so we get per posting/transaction integrity checks in place

That's something I am working on in all new code :-)

* Separation of duties based on the employee: I'd like to be able to support separation of duties where two employees would be able to post to the books, but only one can post the transactions of the other. (Really: the one who last edited the transaction can't post him/her self)

Sure. Not sure how to do this based on settings. Currently that's add-on territory and a very simple one for batches at least. Will be making that easier to do for drafts too.

* A different workflow for handling pre-/overpayments
Entry of overpayments should be separated from handling normal payments
Use of overpayments should be part of the normal receipt-handling workflow

Agreed.

What I think we want to drop:

* The current recurring transaction/invoices way of working

To think about when creating our new implementation

Agreed.

* A design (no implementation yet, I presume) for handling reconciliation (differently) in combination with automatic transaction import; one of the things to combine would be the experience in the US and the structure currently being implemented as part of the EU SEPA efforts

Sure.

So my plan is basically as follows:

1. Rewrite currency handling, and provide an opportunity to set per-currency allowed exchangerate variances. This option would be initially hidden by css. Initially I will just propagate the changes to the defaults table to avoid messing with the old code. This will be done in trunk and affect the full release both new and old code.

2. Write a journal entry module. Basic test cases would be written including on the db-side posting and reversing a balanced transaction, and on the Perl side, attempting to post an unbalanced transaction (and having it fail). I am not quite sure how to handle this in terms of save points, but this can be figured out. Extending our current test framework to add this will not be problematic. After this point, I would really like new reports to include logic against this db model instead of the old acc_trans tables.

3. Port the invoice module I wrote using PGObject::Simple::Role from wxpos to Moose and move this area over. We'd remove payment handling since that would be separate. I don't expect this to be too hard.

4. We'd write a new payment object model following this. Once this is done, wxpos can take over some part of the role of a data insert tool for reports development etc. I expect that the end result will be some of our current sql will be portable, and a lot of stuff will be rewritten. This may be one of the harder aspects.

5. The reconciliation module would need to be ported. This is not likely to be too hard. Existing reconciliation tests would be expected to pass.

6. The financial reports would be gone through and ported over.

7. At that point we'll branch, switch-over, make sure tests work, and pull the version with the removed code back into 1.5.

With the advancements that have taken place in 1.4, I expect 1-3 to happen fairly quickly. The real unknowns are 4 and 6.

Then I think we need to spend a bit of time to flash out those. However, can we simply go through the other steps and address the items that I have taken up above?

Any feedback?

--
Bye,

Erik.

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

Robust and Flexible. No vendor lock-in.

------------------------------------------------------------------------------
CenturyLink Cloud: The Leader in Enterprise Cloud Services.
Learn Why More Businesses Are Choosing CenturyLink Cloud For
Critical Workloads, Development Environments & Everything In Between.
Get a Quote or Start a Free Trial Today.
http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg.clktrk
_______________________________________________
Ledger-smb-devel mailing list
..hidden..
https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel

Best Wishes,

Chris Travers

Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in.

http://www.efficito.com/learn_more.shtml