Financial issueshttps://lab.civicrm.org/dev/financial/-/issues2023-11-23T07:28:45Zhttps://lab.civicrm.org/dev/financial/-/issues/217Proposal: Remove Transaction ID from message templates2023-11-23T07:28:45ZlarsssandergreenProposal: Remove Transaction ID from message templatesSix message templates (event online and offline, contribution online and offline, membership and payment/refund) include Transaction #. I don't think this is useful information for the recipient, as it is just a long string of characters...Six message templates (event online and offline, contribution online and offline, membership and payment/refund) include Transaction #. I don't think this is useful information for the recipient, as it is just a long string of characters. So my proposal is to remove it, to simplify the templates and only send pertinent information to the recipient.
The only potential use I can think of would be in some edge case where perhaps you can't find a contribution for some reason and you could in that case search by transaction ID, but that seems rare and easily covered by having the exact time, amount, etc. If we did want to have something for that purpose, contribution ID would make a lot more sense, but I don't think we need either. Has anyone ever used the transaction ID from a receipt for any purpose?https://lab.civicrm.org/dev/financial/-/issues/207Currency codes for São Tome and Principe Dobra, Zambian Kwacha and Zimbabwe D...2022-09-09T10:17:04ZBradley TaylorCurrency codes for São Tome and Principe Dobra, Zambian Kwacha and Zimbabwe Dollar incorrectEsentially the same issue as dev/financial#184.
- São Tome and Principe Dobra: STN since 1 January 2018
- Zambian Kwacha: ZMW since December 31, 2012
- Zimbabwe Dollar: ZWL since June 2009
I think we need to copy what we did for https:...Esentially the same issue as dev/financial#184.
- São Tome and Principe Dobra: STN since 1 January 2018
- Zambian Kwacha: ZMW since December 31, 2012
- Zimbabwe Dollar: ZWL since June 2009
I think we need to copy what we did for https://github.com/civicrm/civicrm-core/pull/21751/files, but for these 3 additional currencies:
- Update the SQL files for new installs
- Add a migration to update any historical payments.
These is possibly an argument for not migrating the data for old transactions (as in all cases the ISO code changed to reflect some change to the currency, for example a redenomination). Doing the migration fits with what happened last time, and probably saves headaches with needing to support old currency codes, which will no longer exist in libraries like Brick\Money. However, as the data _was_ migrated for Ghana and Belarus I think it probably makes sense to continue with that pattern this time around.https://lab.civicrm.org/dev/financial/-/issues/205CiviFinancial Blue Sky Dreaming2022-08-25T21:31:40ZJoeMurrayCiviFinancial Blue Sky DreamingThere has been talk over the years by different people about a LExIM shift to a new paradigm/implementation for CiviAccount. I have been approached by someone who could rustle up a tiny starting stake for such a huge effort (I'm not conf...There has been talk over the years by different people about a LExIM shift to a new paradigm/implementation for CiviAccount. I have been approached by someone who could rustle up a tiny starting stake for such a huge effort (I'm not confident there would be an ability to get to 50% needed to launch an MIH that will likely cost in the six figures at CT billable rates). Here is a blue sky dreaming of how a CiviFinancials LExIM might succeed.
1. We find funding for integrating payments into Form Builder - this seems likely to be possible and occur, yet is still a big lift. Imagine all the financial aspects of webform_civicrm.
2. When implementing the new interface for contribution pages and event reg pages, etc., we have some 'dependency injection' that allows calls to be made either to the existing financial APIs or to a new set.
3. We work to implement the guts of CiviAccounting in a new way. Discussion is needed here, but I would be in favour of some significant breaking changes like simplifying the Financial Type/Financial Account model into much more standard accounting, preferrably with simple, sensible defaults.
1. We could design our own CiviAccounts v2 from scratch.
2. We could aim to implement an interface with an existing open source accounting system. The aim is to store all the CiviCRM financial transactions in a standard, validated way without having the development and support burden of making it ourselves. We'd only need to keep the integration going strong. Two candidates I have found that might be feasible, and there are many others, are Front Accounting and GnuCash. https://sourceforge.net/projects/frontaccounting/files/FrontAccounting%202.4/stats/timeline?dates=2019-08-01+to+2022-08-01 has the same technical stack as CiviCRM (PHP and MySQL) but seems to be trending down in installs. https://sourceforge.net/projects/gnucash/files/gnucash%20%28stable%29/stats/timeline?dates=2019-08-01+to+2022-08-01 is much more popular and losing momentum more slowly. It is built in C and C++, so it would be more complex to integrate.
3. If there is a fully open source implementation via CiviAccounts v2 or integrating an open source accounting package, then I would be okay with there being integrations with closed source accounting systems that are popular like Xero and QuickBooks Online. I suppose we could allow the existing implementation of CiviAccounts to be the open source option. While more financially realistic, it might be bad for our reputation.https://lab.civicrm.org/dev/financial/-/issues/188Financial Items incorrectly recorded when using Payment API2021-10-27T16:53:49ZhaystackFinancial Items incorrectly recorded when using Payment APIFinally found the time to return to the Order & Payment API and test the changes that @eileen and @KarinG have worked on. So much better now that I can update a contribution without the tax amounts being recalculated!
Warning, this is g...Finally found the time to return to the Order & Payment API and test the changes that @eileen and @KarinG have worked on. So much better now that I can update a contribution without the tax amounts being recalculated!
Warning, this is going to be a long issue because it seems to involve two (maybe overlapping) issues which I haven't been able to separate. So... the scenario that I'm testing is where:
* I create multiple Line Items in an Order that are a mix of taxable and non-taxable.
* They are also a mix of Participant and Membership Line Items.
* The Line Items can be in any sequence when the Order is created.
To tease out what's going on, I tested the following:
1. Taxable Participant ($25) followed by Non-taxable Membership ($50)
2. Non-taxable Membership ($50) followed by Taxable Participant ($25)
3. Taxable Participant ($50) followed by Non-taxable Membership ($50)
4. Non-taxable Membership ($50) followed by Taxable Participant ($50)
These happen to be for Events ("Summer Solstice Festival Day Concert" and "Fall Fundraiser Dinner") and Membership Types (Student) in the CiviCRM Sample Data - though if you want to use those to replicate then it should be noted that "Summer Solstice Festival Day Concert" is incorrectly set to the "Member Dues" Financial Type. @kcristiano was invaluable in setting up CiviCRM's Sample Data with correct Financial Types and I assume there's nothing amiss in that regard.
## Taxable Participant ($25) followed by Non-taxable Membership ($50)
The procedure is commented upon in full for this first test. I'll just post the annotated logs for the subsequent tests since the procedure is identical.
![civicrm-E25-M50-order](/uploads/74c5bdb91fb8ee46d73a0f525a7552ad/civicrm-E25-M50-order.png)
The params for Order.create are as follows:
```
[params] => Array
(
[contact_id] => 210
[financial_type_id] => 1 <-- Non-taxable Financial Type
[payment_instrument_id] => 4
[trxn_id] => WooCommerce Order - 2247
[invoice_id] => 2247_woocommerce
[receive_date] => 2021-10-11 11:28:21
[contribution_status_id] => Pending
[is_pay_later] => 1
[total_amount] => 79.84 <-- Note: Total Amount has 2 decimal places
[source] => Shop
[campaign_id] => 3
[note] => Solstice Ticket x 1, Student Membership x 1
[line_items] => Array
(
[17] => Array
(
[params] => Array
(
[event_id] => 2
[contact_id] => 210
[role_id] => 1
[source] => Shop: Solstice Ticket
[status_id] => Pending from pay later
)
[line_item] => Array
(
[0] => Array
(
[price_field_id] => 1
[unit_price] => 25.00
[qty] => 1
[line_total] => 25.00
[tax_amount] => 4.84 <-- Note: Tax Amount has 2 decimal places
[label] => Solstice Ticket
[entity_table] => civicrm_participant
[financial_type_id] => 5 <-- Taxable Financial Type
)
)
)
[18] => Array
(
[params] => Array
(
[membership_type_id] => 2
[source] => Shop
[contact_id] => 210
[skipStatusCal] => 1
[status_id] => Pending
)
[line_item] => Array
(
[0] => Array
(
[price_field_id] => 1
[unit_price] => 50.00
[qty] => 1
[line_total] => 50.00
[tax_amount] => 0.00
[label] => Student Membership
[entity_table] => civicrm_membership
[financial_type_id] => 2 <-- Non-taxable Financial Type
[membership_type_id] => 2
)
)
)
)
)
```
The CiviCRM API (yeah, I know I should convert to API4 but that's another story) returns:
```
[result] => Array
(
[is_error] => 0
[version] => 3
[count] => 1
[id] => 103
[values] => Array
(
[103] => Array
(
[id] => 103
[contact_id] => 210
[financial_type_id] => 1
[contribution_page_id] =>
[payment_instrument_id] => 4
[receive_date] => 20211011112821
[non_deductible_amount] =>
[total_amount] => 79.844775 <-- Note: Total has 6 decimal places
[fee_amount] => 0
[net_amount] => 79.844775 <-- Note: Net Amount has 6 decimal places
[trxn_id] => WooCommerce Order - 2247
[invoice_id] => 2247_woocommerce
[invoice_number] => INV_103
[currency] => USD
[cancel_date] =>
[cancel_reason] =>
[receipt_date] =>
[thankyou_date] =>
[source] => Shop
[amount_level] =>
[contribution_recur_id] =>
[is_test] =>
[is_pay_later] => 1
[contribution_status_id] => 2
[address_id] =>
[check_number] =>
[campaign_id] => 3
[creditnote_id] =>
[tax_amount] => 4.84 <-- Note: Tax Amount has 2 decimal places
[revenue_recognition_date] =>
[is_template] =>
[contribution_type_id] => 1
[line_item] => Array
(
[0] => Array
(
[qty] => 1
[price_field_id] => 1
[price_field_value_id] => 1
[entity_table] => civicrm_participant
[unit_price] => 25.00
[label] => Solstice Ticket
[line_total] => 25.00
[tax_amount] => 4.844775 <-- Note: Tax Amount has 6 decimal places
[financial_type_id] => 5
[entity_id] => 56
)
[1] => Array
(
[qty] => 1
[price_field_id] => 1
[price_field_value_id] => 1
[entity_table] => civicrm_membership
[unit_price] => 50.00
[label] => Student Membership
[line_total] => 50.00
[tax_amount] => 0
[financial_type_id] => 2
[membership_type_id] => 2
[entity_id] => 35
)
)
)
)
)
```
So far so good - though it seems odd that the Total Amount, Net Amount and Tax Amount are reported as having 6 decimal places given that a pre-calculated Tax Amount to 2 decimal places was passed in.
Let's have a look at the Bookkeeping Report at this stage:
![civicrm-E25-M50-pre](/uploads/0fa2ceec4ca463ce50a38bb0ff8b96ae/civicrm-E25-M50-pre.png)
Looks good. The database looks good too:
```
SELECT id, total_amount, fee_amount, net_amount, tax_amount FROM `civicrm_contribution` WHERE id = '103';
+-----+--------------+------------+------------+------------+
| id | total_amount | fee_amount | net_amount | tax_amount |
+-----+--------------+------------+------------+------------+
| 103 | 79.84 | 0.00 | 79.84 | 4.84 |
+-----+--------------+------------+------------+------------+
SELECT id, entity_table, qty, unit_price, line_total, financial_type_id, tax_amount FROM `civicrm_line_item` WHERE contribution_id = '103';
+-----+---------------------+------+------------+------------+-------------------+------------+
| id | entity_table | qty | unit_price | line_total | financial_type_id | tax_amount |
+-----+---------------------+------+------------+------------+-------------------+------------+
| 107 | civicrm_participant | 1.00 | 25.00 | 25.00 | 5 | 4.84 |
| 108 | civicrm_membership | 1.00 | 50.00 | 50.00 | 2 | 0.00 |
+-----+---------------------+------+------------+------------+-------------------+------------+
SELECT * FROM `civicrm_financial_item` WHERE contact_id = '210';
+-----+---------------------+---------------------+------------+--------------------+--------+----------+----------------------+-----------+-------------------+-----------+
| id | created_date | transaction_date | contact_id | description | amount | currency | financial_account_id | status_id | entity_table | entity_id |
+-----+---------------------+---------------------+------------+--------------------+--------+----------+----------------------+-----------+-------------------+-----------+
| 104 | 2021-10-11 11:28:23 | 2021-10-11 11:28:21 | 210 | Solstice Ticket | 25.00 | USD | 15 | 3 | civicrm_line_item | 107 |
| 105 | 2021-10-11 11:28:23 | 2021-10-11 11:28:21 | 210 | Sales Tax | 4.84 | USD | 17 | 3 | civicrm_line_item | 107 |
| 106 | 2021-10-11 11:28:23 | 2021-10-11 11:28:21 | 210 | Student Membership | 50.00 | USD | 2 | 3 | civicrm_line_item | 108 |
+-----+---------------------+---------------------+------------+--------------------+--------+----------+----------------------+-----------+-------------------+-----------+
SELECT id, from_financial_account_id, to_financial_account_id, trxn_date, total_amount, net_amount, is_payment, status_id, payment_instrument_id FROM `civicrm_financial_trxn` WHERE id > '100';
+-----+---------------------------+-------------------------+---------------------+--------------+------------+------------+-----------+-----------------------+
| id | from_financial_account_id | to_financial_account_id | trxn_date | total_amount | net_amount | is_payment | status_id | payment_instrument_id |
+-----+---------------------------+-------------------------+---------------------+--------------+------------+------------+-----------+-----------------------+
| 101 | NULL | 7 | 2021-10-11 11:28:21 | 79.84 | 79.84 | 0 | 2 | 4 |
+-----+---------------------------+-------------------------+---------------------+--------------+------------+------------+-----------+-----------------------+
SELECT * FROM `civicrm_entity_financial_trxn` WHERE financial_trxn_id > '100';
+-----+------------------------+-----------+-------------------+--------+
| id | entity_table | entity_id | financial_trxn_id | amount |
+-----+------------------------+-----------+-------------------+--------+
| 203 | civicrm_contribution | 103 | 101 | 79.84 | <-- Correct Entity ID.
| 204 | civicrm_financial_item | 104 | 101 | 25.00 | <-- Correct Entity ID.
| 205 | civicrm_financial_item | 105 | 101 | 4.84 | <-- Correct Entity ID.
| 206 | civicrm_financial_item | 106 | 101 | 50.00 | <-- Correct Entity ID.
+-----+------------------------+-----------+-------------------+--------+
```
Submit the `Payment.create` with:
```
[params] => Array
(
[contribution_id] => 103
[total_amount] => 79.84
[trxn_date] => 2021-10-11 11:30:58
[trxn_id] => WooCommerce Order - 2247
[payment_instrument_id] => 4
)
```
And the logs show:
```
PHP Notice: Undefined variable: CRM16923AnUnreliableMethodHasBeenUserToDeterminePaymentProcessorFromEvent in /Users/interactivist/Sites/civicrm/civicrm.events.tec.latest/httpdocs/wp-content/plugins/civicrm/civicrm/CRM/Contribute/BAO/Contribution.php on line 2724
PHP Notice: Undefined variable: CRM16923AnUnreliableMethodHasBeenUserToDeterminePaymentProcessorFromEvent in /Users/interactivist/Sites/civicrm/civicrm.events.tec.latest/httpdocs/wp-content/plugins/civicrm/civicrm/CRM/Contribute/BAO/Contribution.php on line 2724
```
The API result is:
```
[result] => Array
(
[is_error] => 0
[version] => 3
[count] => 1
[id] => 102
[values] => Array
(
[102] => Array
(
[id] => 102
[from_financial_account_id] => 7
[to_financial_account_id] => 6
[trxn_date] => 2021-10-11 11:30:58
[total_amount] => 79.84
[fee_amount] => 0.00
[net_amount] => 79.84
[currency] => USD
[is_payment] => 1
[trxn_id] => WooCommerce Order - 2247
[trxn_result_code] =>
[status_id] => 1
[payment_processor_id] =>
)
)
)
```
Nice. So what's in the database?
```
SELECT id, total_amount, fee_amount, net_amount, tax_amount FROM `civicrm_contribution` WHERE id = '103';
+-----+--------------+------------+------------+------------+
| id | total_amount | fee_amount | net_amount | tax_amount |
+-----+--------------+------------+------------+------------+
| 103 | 79.84 | 0.00 | 79.84 | 4.84 |
+-----+--------------+------------+------------+------------+
SELECT id, entity_table, qty, unit_price, line_total, financial_type_id, tax_amount FROM `civicrm_line_item` WHERE contribution_id = '103';
+-----+---------------------+------+------------+------------+-------------------+------------+
| id | entity_table | qty | unit_price | line_total | financial_type_id | tax_amount |
+-----+---------------------+------+------------+------------+-------------------+------------+
| 107 | civicrm_participant | 1.00 | 25.00 | 25.00 | 5 | 4.84 |
| 108 | civicrm_membership | 1.00 | 50.00 | 50.00 | 2 | 0.00 |
+-----+---------------------+------+------------+------------+-------------------+------------+
SELECT * FROM `civicrm_financial_item` WHERE contact_id = '210';
+-----+---------------------+---------------------+------------+--------------------+--------+----------+----------------------+-----------+-------------------+-----------+
| id | created_date | transaction_date | contact_id | description | amount | currency | financial_account_id | status_id | entity_table | entity_id |
+-----+---------------------+---------------------+------------+--------------------+--------+----------+----------------------+-----------+-------------------+-----------+
| 104 | 2021-10-11 11:28:23 | 2021-10-11 11:28:21 | 210 | Solstice Ticket | 25.00 | USD | 15 | 1 | civicrm_line_item | 107 |
| 105 | 2021-10-11 11:28:23 | 2021-10-11 11:28:21 | 210 | Sales Tax | 4.84 | USD | 17 | 3 | civicrm_line_item | 107 |
| 106 | 2021-10-11 11:28:23 | 2021-10-11 11:28:21 | 210 | Student Membership | 50.00 | USD | 2 | 3 | civicrm_line_item | 108 |
+-----+---------------------+---------------------+------------+--------------------+--------+----------+----------------------+-----------+-------------------+-----------+
SELECT id, from_financial_account_id, to_financial_account_id, trxn_date, total_amount, net_amount, is_payment, status_id, payment_instrument_id FROM `civicrm_financial_trxn` WHERE id > '100';
+-----+---------------------------+-------------------------+---------------------+--------------+------------+------------+-----------+-----------------------+
| id | from_financial_account_id | to_financial_account_id | trxn_date | total_amount | net_amount | is_payment | status_id | payment_instrument_id |
+-----+---------------------------+-------------------------+---------------------+--------------+------------+------------+-----------+-----------------------+
| 101 | NULL | 7 | 2021-10-11 11:28:21 | 79.84 | 79.84 | 0 | 2 | 4 |
| 102 | 7 | 6 | 2021-10-11 11:30:58 | 79.84 | 79.84 | 1 | 1 | 4 |
+-----+---------------------------+-------------------------+---------------------+--------------+------------+------------+-----------+-----------------------+
SELECT * FROM `civicrm_entity_financial_trxn` WHERE financial_trxn_id > '100';
+-----+------------------------+-----------+-------------------+--------+
| id | entity_table | entity_id | financial_trxn_id | amount |
+-----+------------------------+-----------+-------------------+--------+
| 203 | civicrm_contribution | 103 | 101 | 79.84 |
| 204 | civicrm_financial_item | 104 | 101 | 25.00 |
| 205 | civicrm_financial_item | 105 | 101 | 4.84 |
| 206 | civicrm_financial_item | 106 | 101 | 50.00 |
| 207 | civicrm_contribution | 103 | 102 | 79.84 | <-- Correct Entity ID.
| 208 | civicrm_financial_item | 104 | 102 | 25.00 | <-- Correct Entity ID.
| 209 | civicrm_financial_item | 105 | 102 | 4.84 | <-- Duplicated in 211?
| 210 | civicrm_financial_item | 104 | 102 | 50.00 | <-- Wrong Entity ID.
| 211 | civicrm_financial_item | 105 | 102 | 4.84 | <-- Duplicate of 209?
+-----+------------------------+-----------+-------------------+--------+
```
The Bookkeeping Report seems to reflect the odd assignment of Financial Items:
![civicrm-E25-M50-post](/uploads/2e550b6a4893a9d8960ec4b0a2bcff14/civicrm-E25-M50-post.png)
As you can see, it lists all of the `1100` entries as "Event Fee Taxable". The "Member Dues" item seems to have been switched to "Event Fee Taxable". There also seems to be a duplicate Tax Amount entry. This may be important below when the Financial Item amounts are identical.
## Non-taxable Membership ($50) followed by Taxable Participant ($25)
Sequence of Line Items is reversed:
![civicrm-M50-E25-order](/uploads/3fb49ce43318c51ba2da2f6cd8be3470/civicrm-M50-E25-order.png)
Order API params:
```
[params] => Array
(
[contact_id] => 210
[financial_type_id] => 1 <-- Non-taxable Financial Type
[payment_instrument_id] => 4
[trxn_id] => WooCommerce Order - 2247
[invoice_id] => 2247_woocommerce
[receive_date] => 2021-10-11 11:36:49
[contribution_status_id] => Pending
[is_pay_later] => 1
[total_amount] => 79.84 <-- Note: Total Amount has 2 decimal places
[source] => Shop
[campaign_id] => 3
[note] => Student Membership x 1, Solstice Ticket x 1
[line_items] => Array
(
[17] => Array
(
[params] => Array
(
[membership_type_id] => 2
[source] => Shop
[contact_id] => 210
[skipStatusCal] => 1
[status_id] => Pending
)
[line_item] => Array
(
[0] => Array
(
[price_field_id] => 1
[unit_price] => 50.00
[qty] => 1
[line_total] => 50.00
[tax_amount] => 0.00
[label] => Student Membership
[entity_table] => civicrm_membership
[financial_type_id] => 2 <-- Non-taxable Financial Type
[membership_type_id] => 2
)
)
)
[18] => Array
(
[params] => Array
(
[event_id] => 2
[contact_id] => 210
[role_id] => 1
[source] => Shop: Solstice Ticket
[status_id] => Pending from pay later
)
[line_item] => Array
(
[0] => Array
(
[price_field_id] => 1
[unit_price] => 25.00
[qty] => 1
[line_total] => 25.00
[tax_amount] => 4.84 <-- Note: Tax Amount has 2 decimal places
[label] => Solstice Ticket
[entity_table] => civicrm_participant
[financial_type_id] => 5 <-- Taxable Financial Type
)
)
)
)
)
```
API returns:
```
[result] => Array
(
[is_error] => 0
[version] => 3
[count] => 1
[id] => 103
[values] => Array
(
[103] => Array
(
[id] => 103
[contact_id] => 210
[financial_type_id] => 1
[contribution_page_id] =>
[payment_instrument_id] => 4
[receive_date] => 20211011113649
[non_deductible_amount] =>
[total_amount] => 79.844775 <-- Note: Total has 6 decimal places
[fee_amount] => 0
[net_amount] => 79.844775 <-- Note: Net Amount has 6 decimal places
[trxn_id] => WooCommerce Order - 2247
[invoice_id] => 2247_woocommerce
[invoice_number] => INV_103
[currency] => USD
[cancel_date] =>
[cancel_reason] =>
[receipt_date] =>
[thankyou_date] =>
[source] => Shop
[amount_level] =>
[contribution_recur_id] =>
[is_test] =>
[is_pay_later] => 1
[contribution_status_id] => 2
[address_id] =>
[check_number] =>
[campaign_id] => 3
[creditnote_id] =>
[tax_amount] => 4.84 <-- Note: Tax Amount has 2 decimal places
[revenue_recognition_date] =>
[is_template] =>
[contribution_type_id] => 1
[line_item] => Array
(
[0] => Array
(
[qty] => 1
[price_field_id] => 1
[price_field_value_id] => 1
[entity_table] => civicrm_membership
[unit_price] => 50.00
[label] => Student Membership
[line_total] => 50.00
[tax_amount] => 0
[financial_type_id] => 2
[membership_type_id] => 2
[entity_id] => 35
)
[1] => Array
(
[qty] => 1
[price_field_id] => 1
[price_field_value_id] => 1
[entity_table] => civicrm_participant
[unit_price] => 25.00
[label] => Solstice Ticket
[line_total] => 25.00
[tax_amount] => 4.844775 <-- Note: Tax Amount has 6 decimal places
[financial_type_id] => 5
[entity_id] => 56
)
)
)
)
)
```
Database looks good:
```
SELECT id, total_amount, fee_amount, net_amount, tax_amount FROM `civicrm_contribution` WHERE id = '103';
+-----+--------------+------------+------------+------------+
| id | total_amount | fee_amount | net_amount | tax_amount |
+-----+--------------+------------+------------+------------+
| 103 | 79.84 | 0.00 | 79.84 | 4.84 |
+-----+--------------+------------+------------+------------+
SELECT id, entity_table, qty, unit_price, line_total, financial_type_id, tax_amount FROM `civicrm_line_item` WHERE contribution_id = '103';
+-----+---------------------+------+------------+------------+-------------------+------------+
| id | entity_table | qty | unit_price | line_total | financial_type_id | tax_amount |
+-----+---------------------+------+------------+------------+-------------------+------------+
| 107 | civicrm_membership | 1.00 | 50.00 | 50.00 | 2 | 0.00 |
| 108 | civicrm_participant | 1.00 | 25.00 | 25.00 | 5 | 4.84 |
+-----+---------------------+------+------------+------------+-------------------+------------+
SELECT * FROM `civicrm_financial_item` WHERE contact_id = '210';
+-----+---------------------+---------------------+------------+--------------------+--------+----------+----------------------+-----------+-------------------+-----------+
| id | created_date | transaction_date | contact_id | description | amount | currency | financial_account_id | status_id | entity_table | entity_id |
+-----+---------------------+---------------------+------------+--------------------+--------+----------+----------------------+-----------+-------------------+-----------+
| 104 | 2021-10-11 11:36:51 | 2021-10-11 11:36:49 | 210 | Student Membership | 50.00 | USD | 2 | 3 | civicrm_line_item | 107 |
| 105 | 2021-10-11 11:36:51 | 2021-10-11 11:36:49 | 210 | Solstice Ticket | 25.00 | USD | 15 | 3 | civicrm_line_item | 108 |
| 106 | 2021-10-11 11:36:51 | 2021-10-11 11:36:49 | 210 | Sales Tax | 4.84 | USD | 17 | 3 | civicrm_line_item | 108 |
+-----+---------------------+---------------------+------------+--------------------+--------+----------+----------------------+-----------+-------------------+-----------+
SELECT id, from_financial_account_id, to_financial_account_id, trxn_date, total_amount, net_amount, is_payment, status_id, payment_instrument_id FROM `civicrm_financial_trxn` WHERE id > '100';
+-----+---------------------------+-------------------------+---------------------+--------------+------------+------------+-----------+-----------------------+
| id | from_financial_account_id | to_financial_account_id | trxn_date | total_amount | net_amount | is_payment | status_id | payment_instrument_id |
+-----+---------------------------+-------------------------+---------------------+--------------+------------+------------+-----------+-----------------------+
| 101 | NULL | 7 | 2021-10-11 11:36:49 | 79.84 | 79.84 | 0 | 2 | 4 |
+-----+---------------------------+-------------------------+---------------------+--------------+------------+------------+-----------+-----------------------+
SELECT * FROM `civicrm_entity_financial_trxn` WHERE financial_trxn_id > '100';
+-----+------------------------+-----------+-------------------+--------+
| id | entity_table | entity_id | financial_trxn_id | amount |
+-----+------------------------+-----------+-------------------+--------+
| 203 | civicrm_contribution | 103 | 101 | 79.84 | <-- Correct Entity ID.
| 204 | civicrm_financial_item | 104 | 101 | 50.00 | <-- Correct Entity ID.
| 205 | civicrm_financial_item | 105 | 101 | 25.00 | <-- Correct Entity ID.
| 206 | civicrm_financial_item | 106 | 101 | 4.84 | <-- Correct Entity ID.
+-----+------------------------+-----------+-------------------+--------+
```
Bookkeeping confirms this:
![civicrm-M50-E25-pre](/uploads/87857b96e6fb0b8b2379c29e832da746/civicrm-M50-E25-pre.png)
Submit the `Payment.create` with:
```
[params] => Array
(
[contribution_id] => 103
[total_amount] => 79.84
[trxn_date] => 2021-10-11 11:38:59
[trxn_id] => WooCommerce Order - 2247
[payment_instrument_id] => 4
)
```
Logs again show:
```
[11-Oct-2021 10:39:00 UTC] PHP Notice: Undefined variable: CRM16923AnUnreliableMethodHasBeenUserToDeterminePaymentProcessorFromEvent in /Users/interactivist/Sites/civicrm/civicrm.events.tec.latest/httpdocs/wp-content/plugins/civicrm/civicrm/CRM/Contribute/BAO/Contribution.php on line 2724
[11-Oct-2021 10:39:01 UTC] PHP Notice: Undefined variable: CRM16923AnUnreliableMethodHasBeenUserToDeterminePaymentProcessorFromEvent in /Users/interactivist/Sites/civicrm/civicrm.events.tec.latest/httpdocs/wp-content/plugins/civicrm/civicrm/CRM/Contribute/BAO/Contribution.php on line 2724
```
API result:
```
[result] => Array
(
[is_error] => 0
[version] => 3
[count] => 1
[id] => 102
[values] => Array
(
[102] => Array
(
[id] => 102
[from_financial_account_id] => 7
[to_financial_account_id] => 6
[trxn_date] => 2021-10-11 11:38:59
[total_amount] => 79.84
[fee_amount] => 0.00
[net_amount] => 79.84
[currency] => USD
[is_payment] => 1
[trxn_id] => WooCommerce Order - 2247
[trxn_result_code] =>
[status_id] => 1
[payment_processor_id] =>
)
)
)
```
Database again shows the wrongly assigned Financial Items:
```
SELECT id, total_amount, fee_amount, net_amount, tax_amount FROM `civicrm_contribution` WHERE id = '103';
+-----+--------------+------------+------------+------------+
| id | total_amount | fee_amount | net_amount | tax_amount |
+-----+--------------+------------+------------+------------+
| 103 | 79.84 | 0.00 | 79.84 | 4.84 |
+-----+--------------+------------+------------+------------+
SELECT id, entity_table, qty, unit_price, line_total, financial_type_id, tax_amount FROM `civicrm_line_item` WHERE contribution_id = '103';
+-----+---------------------+------+------------+------------+-------------------+------------+
| id | entity_table | qty | unit_price | line_total | financial_type_id | tax_amount |
+-----+---------------------+------+------------+------------+-------------------+------------+
| 107 | civicrm_membership | 1.00 | 50.00 | 50.00 | 2 | 0.00 |
| 108 | civicrm_participant | 1.00 | 25.00 | 25.00 | 5 | 4.84 |
+-----+---------------------+------+------------+------------+-------------------+------------+
SELECT * FROM `civicrm_financial_item` WHERE contact_id = '210';
+-----+---------------------+---------------------+------------+--------------------+--------+----------+----------------------+-----------+-------------------+-----------+
| id | created_date | transaction_date | contact_id | description | amount | currency | financial_account_id | status_id | entity_table | entity_id |
+-----+---------------------+---------------------+------------+--------------------+--------+----------+----------------------+-----------+-------------------+-----------+
| 104 | 2021-10-11 11:36:51 | 2021-10-11 11:36:49 | 210 | Student Membership | 50.00 | USD | 2 | 1 | civicrm_line_item | 107 |
| 105 | 2021-10-11 11:36:51 | 2021-10-11 11:36:49 | 210 | Solstice Ticket | 25.00 | USD | 15 | 3 | civicrm_line_item | 108 |
| 106 | 2021-10-11 11:36:51 | 2021-10-11 11:36:49 | 210 | Sales Tax | 4.84 | USD | 17 | 3 | civicrm_line_item | 108 |
+-----+---------------------+---------------------+------------+--------------------+--------+----------+----------------------+-----------+-------------------+-----------+
SELECT id, from_financial_account_id, to_financial_account_id, trxn_date, total_amount, net_amount, is_payment, status_id, payment_instrument_id FROM `civicrm_financial_trxn` WHERE id > '100';
+-----+---------------------------+-------------------------+---------------------+--------------+------------+------------+-----------+-----------------------+
| id | from_financial_account_id | to_financial_account_id | trxn_date | total_amount | net_amount | is_payment | status_id | payment_instrument_id |
+-----+---------------------------+-------------------------+---------------------+--------------+------------+------------+-----------+-----------------------+
| 101 | NULL | 7 | 2021-10-11 11:36:49 | 79.84 | 79.84 | 0 | 2 | 4 |
| 102 | 7 | 6 | 2021-10-11 11:38:59 | 79.84 | 79.84 | 1 | 1 | 4 |
+-----+---------------------------+-------------------------+---------------------+--------------+------------+------------+-----------+-----------------------+
SELECT * FROM `civicrm_entity_financial_trxn` WHERE financial_trxn_id > '100';
+-----+------------------------+-----------+-------------------+--------+
| id | entity_table | entity_id | financial_trxn_id | amount |
+-----+------------------------+-----------+-------------------+--------+
| 203 | civicrm_contribution | 103 | 101 | 79.84 |
| 204 | civicrm_financial_item | 104 | 101 | 50.00 |
| 205 | civicrm_financial_item | 105 | 101 | 25.00 |
| 206 | civicrm_financial_item | 106 | 101 | 4.84 |
| 207 | civicrm_contribution | 103 | 102 | 79.84 | <-- Correct Entity ID.
| 208 | civicrm_financial_item | 104 | 102 | 50.00 | <-- Correct Entity ID.
| 209 | civicrm_financial_item | 106 | 102 | 4.84 | <-- Duplicated in 211?
| 210 | civicrm_financial_item | 104 | 102 | 25.00 | <-- Wrong Entity ID.
| 211 | civicrm_financial_item | 106 | 102 | 4.84 | <-- Duplicate of 209?
+-----+------------------------+-----------+-------------------+--------+
```
Again, there also seems to be a duplicate Tax Amount entry.
Bookkeeping Report confirmation of the odd assignment:
![civicrm-M50-E25-post](/uploads/7124d730e050f3f288de87ed29bfeb1b/civicrm-M50-E25-post.png)
The "Event Fee Taxable" item seems to have been switched to "Member Dues".
## Taxable Participant ($50) followed by Non-taxable Membership ($50)
Sequence as per first test:
![civicrm-E50-M50-order](/uploads/3cadc61cf6ecceeb570c7298ae0aafb5/civicrm-E50-M50-order.png)
Order API params:
```
[params] => Array
(
[contact_id] => 210
[financial_type_id] => 1 <-- Non-taxable Financial Type
[payment_instrument_id] => 4
[trxn_id] => WooCommerce Order - 2247
[invoice_id] => 2247_woocommerce
[receive_date] => 2021-10-11 12:01:02
[contribution_status_id] => Pending
[is_pay_later] => 1
[total_amount] => 109.69 <-- Note: Total Amount has 2 decimal places
[source] => Shop
[campaign_id] => 3
[note] => Fundraiser Dinner Ticket x 1, Student Membership x 1
[line_items] => Array
(
[17] => Array
(
[params] => Array
(
[event_id] => 1
[contact_id] => 210
[role_id] => 1
[source] => Shop: Fundraiser Dinner Ticket
[status_id] => Pending from pay later
)
[line_item] => Array
(
[0] => Array
(
[price_field_id] => 1
[unit_price] => 50.00
[qty] => 1
[line_total] => 50.00
[tax_amount] => 9.69 <-- Note: Tax Amount has 2 decimal places
[label] => Fundraiser Dinner Ticket
[entity_table] => civicrm_participant
[financial_type_id] => 5 <-- Taxable Financial Type
)
)
)
[18] => Array
(
[params] => Array
(
[membership_type_id] => 2
[source] => Shop
[contact_id] => 210
[skipStatusCal] => 1
[status_id] => Pending
)
[line_item] => Array
(
[0] => Array
(
[price_field_id] => 1
[unit_price] => 50.00
[qty] => 1
[line_total] => 50.00
[tax_amount] => 0.00
[label] => Student Membership
[entity_table] => civicrm_membership
[financial_type_id] => 2 <-- Non-taxable Financial Type
[membership_type_id] => 2
)
)
)
)
)
```
API return:
```
[result] => Array
(
[is_error] => 0
[version] => 3
[count] => 1
[id] => 103
[values] => Array
(
[103] => Array
(
[id] => 103
[contact_id] => 210
[financial_type_id] => 1
[contribution_page_id] =>
[payment_instrument_id] => 4
[receive_date] => 20211011120102
[non_deductible_amount] =>
[total_amount] => 109.68955 <-- Note: Total has 6 decimal places
[fee_amount] => 0
[net_amount] => 109.68955 <-- Note: Net Amount has 6 decimal places
[trxn_id] => WooCommerce Order - 2247
[invoice_id] => 2247_woocommerce
[invoice_number] => INV_103
[currency] => USD
[cancel_date] =>
[cancel_reason] =>
[receipt_date] =>
[thankyou_date] =>
[source] => Shop
[amount_level] =>
[contribution_recur_id] =>
[is_test] =>
[is_pay_later] => 1
[contribution_status_id] => 2
[address_id] =>
[check_number] =>
[campaign_id] => 3
[creditnote_id] =>
[tax_amount] => 9.69 <-- Note: Tax Amount has 2 decimal places
[revenue_recognition_date] =>
[is_template] =>
[contribution_type_id] => 1
[line_item] => Array
(
[0] => Array
(
[qty] => 1
[price_field_id] => 1
[price_field_value_id] => 1
[entity_table] => civicrm_participant
[unit_price] => 50.00
[label] => Fundraiser Dinner Ticket
[line_total] => 50.00
[tax_amount] => 9.68955 <-- Note: Tax Amount has 6 decimal places
[financial_type_id] => 5
[entity_id] => 56
)
[1] => Array
(
[qty] => 1
[price_field_id] => 1
[price_field_value_id] => 1
[entity_table] => civicrm_membership
[unit_price] => 50.00
[label] => Student Membership
[line_total] => 50.00
[tax_amount] => 0
[financial_type_id] => 2
[membership_type_id] => 2
[entity_id] => 35
)
)
)
)
)
```
Again, database looks good:
```
SELECT id, total_amount, fee_amount, net_amount, tax_amount FROM `civicrm_contribution` WHERE id = '103';
+-----+--------------+------------+------------+------------+
| id | total_amount | fee_amount | net_amount | tax_amount |
+-----+--------------+------------+------------+------------+
| 103 | 109.69 | 0.00 | 109.69 | 9.69 |
+-----+--------------+------------+------------+------------+
SELECT id, entity_table, qty, unit_price, line_total, financial_type_id, tax_amount FROM `civicrm_line_item` WHERE contribution_id = '103';
+-----+---------------------+------+------------+------------+-------------------+------------+
| id | entity_table | qty | unit_price | line_total | financial_type_id | tax_amount |
+-----+---------------------+------+------------+------------+-------------------+------------+
| 107 | civicrm_participant | 1.00 | 50.00 | 50.00 | 5 | 9.69 |
| 108 | civicrm_membership | 1.00 | 50.00 | 50.00 | 2 | 0.00 |
+-----+---------------------+------+------------+------------+-------------------+------------+
SELECT * FROM `civicrm_financial_item` WHERE contact_id = '210';
+-----+---------------------+---------------------+------------+--------------------------+--------+----------+----------------------+-----------+-------------------+-----------+
| id | created_date | transaction_date | contact_id | description | amount | currency | financial_account_id | status_id | entity_table | entity_id |
+-----+---------------------+---------------------+------------+--------------------------+--------+----------+----------------------+-----------+-------------------+-----------+
| 104 | 2021-10-11 12:01:04 | 2021-10-11 12:01:02 | 210 | Fundraiser Dinner Ticket | 50.00 | USD | 15 | 3 | civicrm_line_item | 107 |
| 105 | 2021-10-11 12:01:04 | 2021-10-11 12:01:02 | 210 | Sales Tax | 9.69 | USD | 17 | 3 | civicrm_line_item | 107 |
| 106 | 2021-10-11 12:01:04 | 2021-10-11 12:01:02 | 210 | Student Membership | 50.00 | USD | 2 | 3 | civicrm_line_item | 108 |
+-----+---------------------+---------------------+------------+--------------------------+--------+----------+----------------------+-----------+-------------------+-----------+
SELECT id, from_financial_account_id, to_financial_account_id, trxn_date, total_amount, net_amount, is_payment, status_id, payment_instrument_id FROM `civicrm_financial_trxn` WHERE id > '100';
+-----+---------------------------+-------------------------+---------------------+--------------+------------+------------+-----------+-----------------------+
| id | from_financial_account_id | to_financial_account_id | trxn_date | total_amount | net_amount | is_payment | status_id | payment_instrument_id |
+-----+---------------------------+-------------------------+---------------------+--------------+------------+------------+-----------+-----------------------+
| 101 | NULL | 7 | 2021-10-11 12:01:02 | 109.69 | 109.69 | 0 | 2 | 4 |
+-----+---------------------------+-------------------------+---------------------+--------------+------------+------------+-----------+-----------------------+
SELECT * FROM `civicrm_entity_financial_trxn` WHERE financial_trxn_id > '100';
+-----+------------------------+-----------+-------------------+--------+
| id | entity_table | entity_id | financial_trxn_id | amount |
+-----+------------------------+-----------+-------------------+--------+
| 203 | civicrm_contribution | 103 | 101 | 109.69 | <-- Correct Entity ID.
| 204 | civicrm_financial_item | 104 | 101 | 50.00 | <-- Correct Entity ID.
| 205 | civicrm_financial_item | 105 | 101 | 9.69 | <-- Correct Entity ID.
| 206 | civicrm_financial_item | 106 | 101 | 50.00 | <-- Correct Entity ID.
+-----+------------------------+-----------+-------------------+--------+
```
Bookkeeping Report confirms:
![civicrm-E50-M50-pre](/uploads/728d5845348f0f8bf289f5a7fbcb84f6/civicrm-E50-M50-pre.png)
This is where things get a bit weird(er).
Call `Payment.create` with:
```
[params] => Array
(
[contribution_id] => 103
[total_amount] => 109.69
[trxn_date] => 2021-10-11 12:04:49
[trxn_id] => WooCommerce Order - 2247
[payment_instrument_id] => 4
)
```
Logs (as usual) show:
```
[11-Oct-2021 11:04:50 UTC] PHP Notice: Undefined variable: CRM16923AnUnreliableMethodHasBeenUserToDeterminePaymentProcessorFromEvent in /Users/interactivist/Sites/civicrm/civicrm.events.tec.latest/httpdocs/wp-content/plugins/civicrm/civicrm/CRM/Contribute/BAO/Contribution.php on line 2724
[11-Oct-2021 11:04:51 UTC] PHP Notice: Undefined variable: CRM16923AnUnreliableMethodHasBeenUserToDeterminePaymentProcessorFromEvent in /Users/interactivist/Sites/civicrm/civicrm.events.tec.latest/httpdocs/wp-content/plugins/civicrm/civicrm/CRM/Contribute/BAO/Contribution.php on line 2724
```
API result is:
```
[result] => Array
(
[is_error] => 0
[version] => 3
[count] => 1
[id] => 102
[values] => Array
(
[102] => Array
(
[id] => 102
[from_financial_account_id] => 7
[to_financial_account_id] => 6
[trxn_date] => 2021-10-11 12:04:49
[total_amount] => 109.69
[fee_amount] => 0.00
[net_amount] => 109.69
[currency] => USD
[is_payment] => 1
[trxn_id] => WooCommerce Order - 2247
[trxn_result_code] =>
[status_id] => 1
[payment_processor_id] =>
)
)
)
```
Database has the same pattern of mis-assignment:
```
SELECT id, total_amount, fee_amount, net_amount, tax_amount FROM `civicrm_contribution` WHERE id = '103';
+-----+--------------+------------+------------+------------+
| id | total_amount | fee_amount | net_amount | tax_amount |
+-----+--------------+------------+------------+------------+
| 103 | 109.69 | 0.00 | 109.69 | 9.69 |
+-----+--------------+------------+------------+------------+
SELECT id, entity_table, qty, unit_price, line_total, financial_type_id, tax_amount FROM `civicrm_line_item` WHERE contribution_id = '103';
+-----+---------------------+------+------------+------------+-------------------+------------+
| id | entity_table | qty | unit_price | line_total | financial_type_id | tax_amount |
+-----+---------------------+------+------------+------------+-------------------+------------+
| 107 | civicrm_participant | 1.00 | 50.00 | 50.00 | 5 | 9.69 |
| 108 | civicrm_membership | 1.00 | 50.00 | 50.00 | 2 | 0.00 |
+-----+---------------------+------+------------+------------+-------------------+------------+
SELECT * FROM `civicrm_financial_item` WHERE contact_id = '210';
+-----+---------------------+---------------------+------------+--------------------------+--------+----------+----------------------+-----------+-------------------+-----------+
| id | created_date | transaction_date | contact_id | description | amount | currency | financial_account_id | status_id | entity_table | entity_id |
+-----+---------------------+---------------------+------------+--------------------------+--------+----------+----------------------+-----------+-------------------+-----------+
| 104 | 2021-10-11 12:01:04 | 2021-10-11 12:01:02 | 210 | Fundraiser Dinner Ticket | 50.00 | USD | 15 | 1 | civicrm_line_item | 107 |
| 105 | 2021-10-11 12:01:04 | 2021-10-11 12:01:02 | 210 | Sales Tax | 9.69 | USD | 17 | 3 | civicrm_line_item | 107 |
| 106 | 2021-10-11 12:01:04 | 2021-10-11 12:01:02 | 210 | Student Membership | 50.00 | USD | 2 | 3 | civicrm_line_item | 108 |
+-----+---------------------+---------------------+------------+--------------------------+--------+----------+----------------------+-----------+-------------------+-----------+
SELECT id, from_financial_account_id, to_financial_account_id, trxn_date, total_amount, net_amount, is_payment, status_id, payment_instrument_id FROM `civicrm_financial_trxn` WHERE id > '100';
+-----+---------------------------+-------------------------+---------------------+--------------+------------+------------+-----------+-----------------------+
| id | from_financial_account_id | to_financial_account_id | trxn_date | total_amount | net_amount | is_payment | status_id | payment_instrument_id |
+-----+---------------------------+-------------------------+---------------------+--------------+------------+------------+-----------+-----------------------+
| 101 | NULL | 7 | 2021-10-11 12:01:02 | 109.69 | 109.69 | 0 | 2 | 4 |
| 102 | 7 | 6 | 2021-10-11 12:04:49 | 109.69 | 109.69 | 1 | 1 | 4 |
+-----+---------------------------+-------------------------+---------------------+--------------+------------+------------+-----------+-----------------------+
SELECT * FROM `civicrm_entity_financial_trxn` WHERE financial_trxn_id > '100';
+-----+------------------------+-----------+-------------------+--------+
| id | entity_table | entity_id | financial_trxn_id | amount |
+-----+------------------------+-----------+-------------------+--------+
| 203 | civicrm_contribution | 103 | 101 | 109.69 |
| 204 | civicrm_financial_item | 104 | 101 | 50.00 |
| 205 | civicrm_financial_item | 105 | 101 | 9.69 |
| 206 | civicrm_financial_item | 106 | 101 | 50.00 |
| 207 | civicrm_contribution | 103 | 102 | 109.69 | <-- Correct Entity ID.
| 208 | civicrm_financial_item | 104 | 102 | 50.00 | <-- Correct Entity ID.
| 209 | civicrm_financial_item | 105 | 102 | 9.69 | <-- Duplicated in 211?
| 210 | civicrm_financial_item | 104 | 102 | 50.00 | <-- Wrong Entity ID.
| 211 | civicrm_financial_item | 105 | 102 | 9.69 | <-- Duplicate of 209?
+-----+------------------------+-----------+-------------------+--------+
```
However, this time the consequences of this combination of mismatch and duplicate entry gives a different result in the Bookkeeping Report:
![civicrm-E50-M50-post](/uploads/afb612896bb51e3db2afd7b67b176c9d/civicrm-E50-M50-post.png)
This time, it seems, there are only 5 Financial Items visible.
## Non-taxable Membership ($50) followed by Taxable Participant ($50)
![civicrm-M50-E50-order](/uploads/0f34a882ab9bfb12322254183951b89a/civicrm-M50-E50-order.png)
I'll skip the API calls for this one - if you want them, I'm happy to provide.
The `Order.create` process goes fine, but we're left with the following in the database:
```
+-----+--------------+------------+------------+------------+
| id | total_amount | fee_amount | net_amount | tax_amount |
+-----+--------------+------------+------------+------------+
| 103 | 109.69 | 0.00 | 109.69 | 9.69 |
+-----+--------------+------------+------------+------------+
SELECT id, entity_table, qty, unit_price, line_total, financial_type_id, tax_amount FROM `civicrm_line_item` WHERE contribution_id = '103';
+-----+---------------------+------+------------+------------+-------------------+------------+
| id | entity_table | qty | unit_price | line_total | financial_type_id | tax_amount |
+-----+---------------------+------+------------+------------+-------------------+------------+
| 107 | civicrm_membership | 1.00 | 50.00 | 50.00 | 2 | 0.00 |
| 108 | civicrm_participant | 1.00 | 50.00 | 50.00 | 5 | 9.69 |
+-----+---------------------+------+------------+------------+-------------------+------------+
SELECT * FROM `civicrm_financial_item` WHERE contact_id = '210';
+-----+---------------------+---------------------+------------+--------------------------+--------+----------+----------------------+-----------+-------------------+-----------+
| id | created_date | transaction_date | contact_id | description | amount | currency | financial_account_id | status_id | entity_table | entity_id |
+-----+---------------------+---------------------+------------+--------------------------+--------+----------+----------------------+-----------+-------------------+-----------+
| 104 | 2021-10-11 12:21:33 | 2021-10-11 12:21:31 | 210 | Student Membership | 50.00 | USD | 2 | 1 | civicrm_line_item | 107 |
| 105 | 2021-10-11 12:21:33 | 2021-10-11 12:21:31 | 210 | Fundraiser Dinner Ticket | 50.00 | USD | 15 | 3 | civicrm_line_item | 108 |
| 106 | 2021-10-11 12:21:33 | 2021-10-11 12:21:31 | 210 | Sales Tax | 9.69 | USD | 17 | 3 | civicrm_line_item | 108 |
+-----+---------------------+---------------------+------------+--------------------------+--------+----------+----------------------+-----------+-------------------+-----------+
SELECT id, from_financial_account_id, to_financial_account_id, trxn_date, total_amount, net_amount, is_payment, status_id, payment_instrument_id FROM `civicrm_financial_trxn` WHERE id > '100';
+-----+---------------------------+-------------------------+---------------------+--------------+------------+------------+-----------+-----------------------+
| id | from_financial_account_id | to_financial_account_id | trxn_date | total_amount | net_amount | is_payment | status_id | payment_instrument_id |
+-----+---------------------------+-------------------------+---------------------+--------------+------------+------------+-----------+-----------------------+
| 101 | NULL | 7 | 2021-10-11 12:21:31 | 109.69 | 109.69 | 0 | 2 | 4 |
| 102 | 7 | 6 | 2021-10-11 12:25:19 | 109.69 | 109.69 | 1 | 1 | 4 |
+-----+---------------------------+-------------------------+---------------------+--------------+------------+------------+-----------+-----------------------+
SELECT * FROM `civicrm_entity_financial_trxn` WHERE financial_trxn_id > '100';
+-----+------------------------+-----------+-------------------+--------+
| id | entity_table | entity_id | financial_trxn_id | amount |
+-----+------------------------+-----------+-------------------+--------+
| 203 | civicrm_contribution | 103 | 101 | 109.69 |
| 204 | civicrm_financial_item | 104 | 101 | 50.00 |
| 205 | civicrm_financial_item | 105 | 101 | 50.00 |
| 206 | civicrm_financial_item | 106 | 101 | 9.69 |
| 207 | civicrm_contribution | 103 | 102 | 109.69 | <-- Correct Entity ID.
| 208 | civicrm_financial_item | 104 | 102 | 50.00 | <-- Correct Entity ID.
| 209 | civicrm_financial_item | 106 | 102 | 9.69 | <-- Duplicated in 211?
| 210 | civicrm_financial_item | 104 | 102 | 50.00 | <-- Wrong Entity ID.
| 211 | civicrm_financial_item | 106 | 102 | 9.69 | <-- Duplicate of 209?
+-----+------------------------+-----------+-------------------+--------+
```
The Bookkeeping Report shows the end result:
![civicrm-M50-E50-post](/uploads/4cefa4c34c1b02956fdc9ac3e62c8d4c/civicrm-M50-E50-post.png)
## Final thought
My best guess at the moment is that the combination of the mis-assigned `entity_id`s for the Financial Items _plus_ the effect of the extra entry in the `civicrm_entity_financial_trxn` table leads to the mix of 5 or 6 Items visible in the Bookkeeping Report.
If you've got to here, thanks for reading! Hope this helps fix what seemed to be mind-bending symptoms.https://lab.civicrm.org/dev/financial/-/issues/142Order api proposal - require less line item parameters2020-12-03T10:37:27ZeileenOrder api proposal - require less line item parametersI'd like to propose that we add sensible defaults for line items when using the order api
1) price_field_id - if NONE of the rows have a price_field_id then use the default pricefield id for all of them (I don't want to open up technica...I'd like to propose that we add sensible defaults for line items when using the order api
1) price_field_id - if NONE of the rows have a price_field_id then use the default pricefield id for all of them (I don't want to open up technical analysis of dealing with multiple price sets, more complex config) - so this is just for that common use case
2) qty - if not set, default to 1
This would make the minimum required params for a 'quick config' (I hate that usage but I think we know what it means) order
```
$this->callAPISuccess('Order', 'create', [
'financial_type_id' => 'Donation',
'contact_id' => $contact1,
'line_items' => [
['line_item' => [
[
'financial_type_id' => CRM_Core_PseudoConstant::getKey('CRM_Contribute_BAO_Contribution', 'financial_type_id', 'Donation'),
'line_total' => 40,
],
[
'financial_type_id' => CRM_Core_PseudoConstant::getKey('CRM_Contribute_BAO_Contribution', 'financial_type_id', 'Member Dues'),
'line_total' => 50,
],
]],
]
]);
```
Ideally we would also accept
```
'financial_type_id' =>'Donation',
```https://lab.civicrm.org/dev/financial/-/issues/138Broken bookkeeping records: no financial trxn from Accounts Receivable to rev...2020-06-29T04:05:27ZRichBroken bookkeeping records: no financial trxn from Accounts Receivable to revenue account.According to the [docs](https://docs.civicrm.org/dev/en/latest/financial/financialentities/), when a Pending Order is created it's supposed to create a financial trxn transferring the total sum to accounts receivable. I'm not seeing this...According to the [docs](https://docs.civicrm.org/dev/en/latest/financial/financialentities/), when a Pending Order is created it's supposed to create a financial trxn transferring the total sum to accounts receivable. I'm not seeing this happening.
@JoeMurray [pointed out](https://chat.civicrm.org/civicrm/pl/zs556c7xqjdtjciry8qnipw7rc) that *There's a setting in CiviContribute Compnent Settings now which iirc disables this by default.*
And @eileen suggested that the default should be the other way around.
But even with this turned ON, the trxn is not created.
```bash
# Set this to a contact that exists:
CID=202
cv api Order.create financial_type_id=Donation contact_id=$CID contribution_status_id=Pending \
total_amount=30 receive_date=2020-05-29\ 15:30:00
```
At this point we would expect (according to my understanding) to see:
- ✔ 1 contribution record.
- ✔ 1 line item: directly linked to the contribution.
- ✔ 1 financial item, linked to the line item, which specifies the CREDIT financial account (*Donation*) and status 3 (pending).
- ✖ 1 financial trxn:
- `from_account` (the CREDIT): The revenue (income) account for the *Donation* financial type.
- `to_account` (the DEBIT): Accounts Receiveable
- ✖ related entity financial trxn records
Without this we end up with bookkeeping problems:
- all financial trxns come *from* Accounts Recievable, but there's nothing going *to* Accounts Receivable.
- there are no bookkeeping transactions for the revenue accounts.
- So you get info like "some money went into your bank" but not "a donation went into your bank".JoeMurrayJoeMurrayhttps://lab.civicrm.org/dev/financial/-/issues/114Support use of 'Chargeback Account is'2020-01-14T05:39:52ZJoeMurraySupport use of 'Chargeback Account is'Currently, trying to configure a Account Relationship for 'Chargeback Account is' leads to an error, "This financial account cannot have 'Chargeback Account is' relationship.' It comes from https://github.com/civicrm/civicrm-core/blob/ma...Currently, trying to configure a Account Relationship for 'Chargeback Account is' leads to an error, "This financial account cannot have 'Chargeback Account is' relationship.' It comes from https://github.com/civicrm/civicrm-core/blob/master/CRM/Financial/BAO/FinancialTypeAccount.php#L264. The problem is that https://github.com/civicrm/civicrm-core/edit/master/CRM/Financial/BAO/FinancialAccount.php#L282 does not define this account relationship. The reason for that was core does not support the bookkeeping around that.
Currently, when the Contribution status is changed to 'Chargeback', eg from completed, the bookkeeping transaction that is created uses a reversal transaction with the 'Revenue Account is' financial account for the financial type. The proper way to handle chargebacks would be at the Payment level rather than the Contribution level. The contribution status should only change to Chargeback when all unfailed and unrefunded payments, including partial ones, are in status chargeback. But that is too big a change to work on immediately, and should be left to when we refactor and maybe eliminate the contribution status field.
The current proposal is to better support a financial type with a 'Chargeback Account is' relationship defined as follows:
1) Don't throw an error when trying to define this relationship in the browser. Instead, add the following as L292:
'Chargeback Account is' => 'Revenue', // this is like a contra-revenue account and assumes the chargeback is for a donation rather than creating a bad debt bookkeeping entry for a sale of goods or services that had a chargeback
2) When the status is being changed to 'Chargeback' on a payment or contribution, use the chargeback account rather than a reversal transaction on the financial account with a 'Revenue Account is' relationship. Note that this will be at a line item level, which is okay.
So currently, creating a completed contribution and then changing its status to Chargeback creates the following two accounting entries:
```
Debit Account Credit Account Amount
Deposit Bank Account Donation 100.00
Deposit Bank Account Donation -100.00
```
After the change, the second transaction would be
```
Deposit Bank Account Chargeback -100.00
```Monish DebMonish Debhttps://lab.civicrm.org/dev/financial/-/issues/108Disabling a price option or price field causes to be zeroed out and financial...2019-11-19T09:22:41ZseamusleeDisabling a price option or price field causes to be zeroed out and financial integrity issues when changing fee selectionsTo reproduce this error:
1. Create a Price set with > 1 price fields
2. Register and pay for someone using just 1 of those price fields
3. Now disable the price field that was used in step 2
4. Navigate to change fee selections to now s...To reproduce this error:
1. Create a Price set with > 1 price fields
2. Register and pay for someone using just 1 of those price fields
3. Now disable the price field that was used in step 2
4. Navigate to change fee selections to now select an option from the 2nd price field
5. Notice that the qty for the first price field gets zeroed out and no new financial trxn is created sensibly.
I believe @KarinG replicated this in chat https://chat.civicrm.org/civicrm/pl/ap5bif4gsifeuc57yftbxnmynh a work around is to rather than disabling the price option is to set the visibility to be admin only. The situation we were having is we were offering a catered option and an uncatered option for an event and wanted to stop people registering for the catered option at a point in time.https://lab.civicrm.org/dev/financial/-/issues/106on Order API test, pass parameters required to create line items, not line items2020-05-28T06:51:36ZJoeMurrayon Order API test, pass parameters required to create line items, not line itemsThe idea of the order api is to provide a wrapper around creating a contribution and all of the line item objects, eg the membership created when buying a membership, or the event registration when buying a ticket.
In the specification ...The idea of the order api is to provide a wrapper around creating a contribution and all of the line item objects, eg the membership created when buying a membership, or the event registration when buying a ticket.
In the specification (https://wiki.civicrm.org/confluence/display/CRM/Order+API), the idea was that there would be array of of the parameters required to create line items. Note that although the entity_table is specified, the entity_id is set to null. Moreover, there is no contribution_id value. Calling the Order API is NOT supposed to require prior calls to create other objects.https://lab.civicrm.org/dev/financial/-/issues/95Update Payment.create api handling of payment instrument2019-11-01T12:04:03ZeileenUpdate Payment.create api handling of payment instrumentAt the moment the passed in payment_instrument_id will take precedent over the one associated with the relevant processor. I don't think that is how the design was intended and in other places we use the paymnet_processor_id.
Note that...At the moment the passed in payment_instrument_id will take precedent over the one associated with the relevant processor. I don't think that is how the design was intended and in other places we use the paymnet_processor_id.
Note that I think that the Payment.create api should require either the payment_instrument_id or the payment_processor_id.
Logically this is something 'known' when making a payment & not something we should 'guess'. Transitionally we would deprecate NOT passing in one of those values rathe than requiring it.
I do think we should add some handling to Order.create so that specifically when chaining Payment.create it automatically sets some values - this being one.
@JoeMurray @monish.deb @artfulrobothttps://lab.civicrm.org/dev/financial/-/issues/93Agree / implement handling of failure for doPayment/ doRefund / params for do...2019-11-01T11:58:47ZeileenAgree / implement handling of failure for doPayment/ doRefund / params for doRefundCurrently both doPayment and doRefund throw a PaymentProcessorException if there is a failure either in terms of config or in terms of processing. The flow we agreed back when we implemented this in 4.6 is we should be working towards
`...Currently both doPayment and doRefund throw a PaymentProcessorException if there is a failure either in terms of config or in terms of processing. The flow we agreed back when we implemented this in 4.6 is we should be working towards
```
Order.create.... status=pending
try {
$result = $paymentObject->doPayment();
if ($result['payment_status_id') === 1) {
Payment.create.....
}
}
else {
\\ Add code here to add failed payment
}
```
Currently the CRM_Core_Payment doPayment converts failed results for old processors to exceptions and throws them and new processors have been expected to throw exceptions.
Some of the forms implement things similar to the above although in other cases the exception is caught much further from the call to doPayment and in some cases we are still not creating the order before processing the payment.
Note that in this flow a payment that did not fail but was not successful would not result in a payment being created.
@mattwire is proposing that we change this for doRefund and stop throwing exceptions for failed payments (or any types) and instead return a status for failed for user-related reasons and an exception for system related reasons.
The options as I see them are
1) implement the same pattern for doRefund as doPayment
2) implement and extend the pattern - ie add a couple of things we probably would have done if implementing from scratch such as
- a PaymentProcessorException_PaymentFailed Exception which extends the existing PaymentProcessorException & can be throwing when the payment relates to card failure rather than a config error. Calling code can choose to catch these separately if it chooses. (we could use this for doPayment too)
- add a getter for the outcome ie. start a transition from returning 1 / the value that maps to contributionStatus = Completed (which is not actually a payment status) to ```$paymentObject->isCompleted()``` or ``` $paymentObject->isSuccessful()```. (Note Omnipay uses isSuccessful()). We could transition doPayment to this over time.
- add a property / getter for getRefundTrxnResult
- add a property / getter for 'processor_result' / 'passthrough_params' which is something that @mattwire has suggested although I have no specifics / examples.
- encourage the use of getters & setters rather than passing in params - ie ```$paymentObject->setAmount(20.40);``` Notte we have just added getters & setters to the class in general https://lab.civicrm.org/dev/financial/issues/82 - implement this in the api call & new core interactions with the class
3) switch to the pattern matt proposes - ie. for doRefund
```
return [
// refund_status_id would normally return the contribution_status_id Completed or Failed
'refund_status_id' => CRM_Core_PseudoConstant::getKey('CRM_Contribute_BAO_Contribution', 'contribution_status_id', 'Completed'),
// The refund trxn_id may be different from the trxn_id of the original payment.
// If it is the same then trxn_id should be copied to refund_trxn_id
'refund_trxn_id' => NULL,
// Array of params returned by the payment processor. The contents of this will vary by processor and should not be relied on.
// However, they can be very useful for logging or providing specific feedback.
'processor_result' => [],
];
```https://lab.civicrm.org/dev/financial/-/issues/90Add new api Order functionality to update line items2019-10-25T08:12:07ZeileenAdd new api Order functionality to update line itemsI'm pretty sure that despite @JoeMurray's optimism the current apiv3 Order.create doesn't support this. @monish.deb & I discussed adding it back here
https://github.com/civicrm/civicrm-core/pull/11380
I don't think we should add it o a...I'm pretty sure that despite @JoeMurray's optimism the current apiv3 Order.create doesn't support this. @monish.deb & I discussed adding it back here
https://github.com/civicrm/civicrm-core/pull/11380
I don't think we should add it o apiv3 now - I think we should write a v4 api that does it. I think the object oriented approach will allow us to come up with cleaner methods. I'm kinda keen to try to build those up on a ORDER pseudoBAO class & play a bit before we expose in the api so we can learn about about what works & doesn't before locking it inhttps://lab.civicrm.org/dev/financial/-/issues/86Make 'Record Payment' & 'Record Refund' visible regardless of whether the bal...2020-09-16T00:27:09ZeileenMake 'Record Payment' & 'Record Refund' visible regardless of whether the balance 'requires' onePer https://lab.civicrm.org/dev/financial/issues/85
@JoeMurray agreed that it is OK to ALWAYS show 'record payment' & 'record refund' as opposed to currently it only shows 'add payment' if there it a balance to pay & add 'record refund'...Per https://lab.civicrm.org/dev/financial/issues/85
@JoeMurray agreed that it is OK to ALWAYS show 'record payment' & 'record refund' as opposed to currently it only shows 'add payment' if there it a balance to pay & add 'record refund' if there is a balance to refund. However, we need to be able to overpay & over refund - I'm going to work on this issue & seeing the UI afterwards (just having the links more often visible) might affect what people think 'makes sense'
I did start on this but discovered a regression when I started writing my preliminary test so it's on hold while that gets merged through to masterhttps://lab.civicrm.org/dev/financial/-/issues/83Barcelona sprint financials2019-11-01T12:05:38ZeileenBarcelona sprint financialsI'm creating this issue to document & track the discussions we had at the Barcelona sprint.
We had a financial team of @mattwire @JoeMurray @ayduns @artfulrobot @BjoernE @andrei @ejegg present
Main issues
1. Ensuring there were ...I'm creating this issue to document & track the discussions we had at the Barcelona sprint.
We had a financial team of @mattwire @JoeMurray @ayduns @artfulrobot @BjoernE @andrei @ejegg present
Main issues
1. Ensuring there were no blockers to people using the Order.create->PaymentProcessor.pay->Payment.create flow https://lab.civicrm.org/dev/financial/issues/76
1. Making the poor performance associated with the creditnote_id field opt in rather than opt out https://lab.civicrm.org/dev/financial/issues/84
1. Addressing Stripe's need to store some Stripe generated information for which there is no suitable field. We resolved this at the data level by adding the field civicrm_financial_trxn.result_code. However docs pending! https://lab.civicrm.org/dev/financial/issues/57
1. Having a plan to get off our broken contribution.template model https://lab.civicrm.org/dev/financial/issues/6
1. UI for permitting refunds processing for payments. https://lab.civicrm.org/dev/financial/issues/85
Not actually from the sprint but topical from discussions - add getters & setters to payment processor base class https://lab.civicrm.org/dev/financial/issues/82