Specific civiaccounts question

[jakecivi]

I am trying to update a civi install that uses an older method of associating multiple payments with event registrations (allowing multiple contributions per participant via participant_payment), to 4.5, which allows multiple payments per participant. The upgrade script does not handle the older method correctly, as it's not an official part of Civi, so I need to do some fixes on my own.

I need to know if, when adding a pay-later payment (or any subsequent payment) to a participant/contribution record, the entity_financial_trxn table needs to link the trxn to the financial_item (which does not currently happen, at least in 4.5.3), or if it's enough for that table to link it to the contribution. (see the note in this heading of the civiaccounts data flow page: http://wiki.civicrm.org/confluence/display/CRM/CiviAccounts+Data+Flow#CiviAccountsDataFlow-Pay-latercontributionreceived%28updatePendingPayLaterstatustocompleted%29 )

And.. if that link is necessary, do I add it now even though Civi doesn't add it, or do I not add it and assume that some update script for a later version will take care of it?

[Dave Greenberg]

I'll monitor this post to see what results you get on retesting in 4.5. If you can recreate a situation where the additional entity_financial_trxn record isn't created for the line_item, post the details here and I'll follow your steps. Based on refreshing my memory on this from the wiki, it would be a bug if that linkage was not created.

[jakecivi]

Confirmed that only the civicrm_contribution link is created for a partial payment of a pending pay later contribution in 4.5.3. Now will test with 4.5.4, and if I get the same result, I'll try with a fresh install.

[jakecivi]

Aha! Adding a partial payment (e.g. $50 for a $100 contribution) causes only the contribution to be linked in entity_financial_trxn, while adding the full payment with status Completed and participant status Registered yields both the contribution and financial_item linked to the trxn.

[jakecivi]

Another detail: Setting participant status to Registered in spite of the warning causes two additional transactions created: a $100 transfer from accounts receivable to the bank account, and then another -$50 one from null to the bank account. Both of these have two entries in entity_financial_trxn, one to the contribution, and one to a financial_item. The $-50 one is linked to a new -$50 financial item instead of the original one. The line_item keeps the $100 total_amount, the contribution gets the $50 amount, and the original financial_item keeps the $100 amount.

[jakecivi]

As there's been no reply so far, let me spell out further what I need to know now in order to proceed with a migration project:

1) Do I migrate things the "wrong way," without adding the missing entries, and hope/assume that a future database upgrade will take care of the issue? Will I make things inconsistent later if I do it the "right way" now?

2) What is Civi's approach to this? It seems that going back later and editing already-exported financial data would cause problems; how does anyone (you, the community) want to deal with this? Maybe this is a non-issue as it seems the trxn's themselves are not affected.

3) The bugs described here could be considered fairly major, as a user's ignorance of this particular quirk of Civi could result in confusing, misleading, or inaccurate information being recorded about events, event fees, and payments. What is the strategy for addressing this? Is there a way for a future db upgrade script to parse out what happened and correct for everything?

4) Eileen said that the civiaccounts code seems to do too much thinking and may be better if it were simpler and handled many of the different cases in a more similar way. I'm beginning to agree. Either way, I think there's a ways to go before having a fully functional accounting system in Civi, and I hope it can get some attention soon.

[Dave Greenberg]

From IRC chat with Jake
==================
My ‘best guess’ is that it would be better to migrate the ‘right way’ - as it’s been quite tricky in the past to try an fix missing entries and so is less likely to be addressed in upgrades.
[09:41am] jakew: dgg: how likely that, if it's fixed in upgrades, the upgrades will mess with my having done it the "right" way?
[09:41am] dgg: jakew: as you seem to have definitively identified a bug in creation of financial_item rows for partial payments - you should file a bug report for that.
[09:41am] jakew: dgg: ok, will do
[09:42am] dgg: jakew: don’t know for sure - but best guess is ‘unlikely’. would be important for you to be part of upgrade testing tho.
[09:43am] dgg: jakew: re: item 2 - my understanding of the design is that edits to payments etc after financial export SHOULD result in additional (adjusting) trxns that would be included in subsequent exports. Again, if this is not happening in your testing - a bug report would be appropriate.

[jakecivi]

Bug report: https://issues.civicrm.org/jira/browse/CRM-15848

[SarahG (FountainTribe)]

jakecivi, everyone -  Since you are using the accounting features in CiviCRM, would love your input on the CiviAccounts survey and roadmap at: https://civicrm.org/blogs/pogstonesarahgladstone/civiaccounts-survey-and-roadmap

[jakecivi]

Thank you Sarah, hopefully I can still provide useful information.

Everyone: I have come across another inconsistency that leaves me wondering what to do: the behavior for "change contribution amount" in 4.5.4 is radically different from what is described in the document, and I made a note about it in the intro to that section of the page:

http://wiki.civicrm.org/confluence/display/CRM/CiviAccounts+Data+Flow#CiviAccountsDataFlow-Changecontributionamount%28quickconfig/singleline_item%29

Does anyone have any thoughts? Thanks again.

[jakecivi]

On above post, here an exchange from irc:

dgg: jakew: on quick read, the ‘actual’ behavior seems like it might be preferable since it looks like it might help ensure that additional bookkeeping transactions are exportable (i.e. assuming the initial contribution and financial records were exported, THEN the amount was changed, THEN another export)
dgg: jakew: would be good to test that theory AND discuss w/ Joe since I am really an accounting idiot.
dgg: jakew: if my theory is correct, then i guess the ‘action item’ is to update the wiki. Else, verify same behavior in 4.6 / file bug report.
jakew: dgg: yep. one thing I don't understand about the 'actual' flow - there is no new financial_trxn created, so how would an accounting system keep track of a receivable amount (I would think that there would be an accounts-receivable trxn in the amount of the difference)?
dgg: jakew: good question, seems like it’s needed - but definitely discuss w/ joe.
jakew: dgg: I also wonder if the 'described' flow wouldn't also allow for 'new' activity to be exported in the order you described; it's only changing the contribution and line_item; and it does add the financial_trxn

[jakecivi]

josephmurray: dgg: any possibility we could slip this fix into 4.6?
jakew: josephmurray: to clarify, the above is a new issue (i also updated the jira bug for the previous issue dealing with partial payments); do you have a sense of whether the 'actual' or 'described' flow for the new issue (changing contribution amount) is intended?
josephmurray: reviewing spec and the actual behaviour you observed, everything about the spec is appropriate, and little about the observed behaviour is correct
dgg: josephmurray: re CRM-15848 in 4.6 - code change to insert missing entity_financial_trxn going forward is probalby fine as long as test coverage is good. I’d be nervous about ‘fixing’ existing trxns in upgrade at this point since we’re so close to stable.
josephmurray: the system needs to retain without changing the existing financial_item and financial_trxn records since they provide the history for auditors and may help to match what is happening in various accounts at bank, payment processor, etc
josephmurray: the system then needs to put in new records for change in those tables
josephmurray: the line_item amount is like the invoice amount shown to user, and we are not tracking all changes on an invoice, only the current state, so that’s why the original should be updated, rather than a new one added.
josephmurray: dgg: agreed
jakew: josephmurray: it seems that in both expected and observed versions, the existing financial_items and financial_trxn's are left as is an new ones are added.
jakew: josephmurray: here's an extra little bit: Civi seems to keep track of changes to the contribution amount, and seems (?) to use the line_items to do it; check out this screenshot (coming in a second)
josephmurray: wrt to financial_item: spec has 1 inserted with difference amount, observed has two, one backing out original and another inserting new amount. This is equivalent in bookkeeping terms
josephmurray: There may have been some refactoring when accommodating changes to financial account being done at same time as change in amount, but as I recall we deliberately decided to make bookkeeping entries for each change separately rather than trying to code all combinations of several changing at same time.
josephmurray: re: financial_trxn, you wrote 6) No new financial transaction is created.
jakew: josephmurray: does each change separately refer to the observed behavior?
josephmurray: whereas spec creates one in order to have both sides of the accounting equation represented
jakew: josephmurray: yes, the spec on 6 seems to make more sense
josephmurray: 4) A new entity_financial_trxn is created relating the first new financial item to the initial accounts-receivable financial transaction, with the negative initial amount.
josephmurray: This is a problem, since on export we draw the from account from the financial item in general, and never from another transaction in financial_trxn so far as I recall
josephmurray: same with 5 in observed behavour
jakew: josephmurray: re financial_items: do you mean that a backing out financial item and then a new amount added is a result of doing "each change separately"?
josephmurray: behaviour
josephmurray: sort of.
josephmurray: In the spec for update contribution, there are many fields that can be changed and there is a spec for how to handle each one
josephmurray: eg change contribution financial type
josephmurray: change payment instrument
josephmurray: change amount
josephmurray: change premium
josephmurray: rather than trying to coalesce all of the bookkeeping for these changes into minimal number of transactions if all are changed at same time
josephmurray: we reuse the code for handling each independently
josephmurray: this results in more verbose bookkeeping transactions
josephmurray: but they are easier to understand
josephmurray: and the code is much more maintainable
josephmurray: make sense?
josephmurray: The reason I say ‘sort of’ is that often we create what I call a difference transaction
josephmurray: rather than a formal backing out transaction followed by a new one
josephmurray: The accountants consulted weren’t really concerned about this difference
jakew: josephmurray: yes - but i'm wondering if you're saying that, although the 'spec' does not create a difference transaction, the 'observed' behavior of creating one is fine.
jakew: josephmurray: sorry - i miswrote.
josephmurray: the observed changes to financial_item aren’t a big issue ie 2 and 3
jakew: josephmurray: what i mean is, although the 'spec' creates a difference one, the observed behavior does not need to be changed
jakew: josephmurray: for 2 and 3, that is.
josephmurray: are equivalent to spec behaviour for inserting a financial_item
jakew: josephmurray: ok.
josephmurray: i’m in a call now
josephmurray: ttyl
jakew: josephmurray: thank you.
josephmurray: np
jakew: dgg: josephmurray: added above conversation to forum post. attaching screenshot showing what I think are results of a "backing out" transaction and then a new transaction.

[jakecivi]

Question: how is the above different from this spec? http://wiki.civicrm.org/confluence/display/CRM/CiviAccounts+Data+Flow#CiviAccountsDataFlow-Changefeeamount/addfeetoexistingcontribution

[JoeMurray]

Umm, well, your link is for fees, rather than what is discussed above. I believe the Financial Type and thus Financial Account will be different for fees.

[jakecivi]

Oh - I see - the word fee is used in two contexts in Civi: an event fee, and a processor fee.