[IBP] "Link to invoice" feature adjustments

[bohte]

Hi,

Some time ago we have introduced a feature in IBP that allows user to create payments for AR/AP invoices based on imported bank transactions. After testing we have discovered that our business logic does not cover SQL-Ledger which may lead to inconsistency in data since some of SQL-Ledger forms are still in use.

Explanation scenario: 1) User imports bank data (transactions) via IBP import feature 2) User links bank transaction to existing related AR/AP invoice via IBP feature --> invoice payment is created 3) User goes to affected AR/AP invoice edit SQL-Ledger form Actual result: created payment is editable Expected result: created payment is not editable

As shown in the explanation scenario, possibility of editing payments that were created based on bank data may lead to inconsistency. For example if user changes payment amount - link between bank transaction and payment is no longer valid since amounts are different.

In order to prevent such cases we want to request following changes in SQL-Ledger invoice editing: payment, linked to bank transaction, must not be editable anymore: it can be checked by verifying that "imported_transaction_to_booking" table contains record with

• 'booking_id' being equal to "ar"/"ap" table 'id' value (invoice id)
• 'booking_part_id' being equal to "acc_trans" table 'id' value (invoice payment id)

In addition to changes, requested above, we would also need some adjustments in regard to payments handling. During testing I've noticed interesting behavior with 'id' column values in "acc_trans" table when it comes to payments identification. As far as I could understand - 'id' value is set based on payments list index which is calculated based on ordering of payments in AR/AP invoice form that is being submitted. Meanwhile, order of payments is not something that is only defined by user, since each time when edit form is opened and prefilled with data - payments get sorted by date field value.

Explanation scenario: 1) User creates AR/AP invoice via SQL-Ledger form 2) User opens created invoice and enters payments in random order (not sorted by date) 3) User saves invoice with entered payments 4) User opens the same invoice --> payments are sorted by date value 5) User saves invoice without putting any changes Actual result: 'id' values for "acc_trans" payment related records are changed according to the order on the form Expected result: 'id' values for "acc_trans" payment related records remain unchanged

In order to prevent such situation, following changes may be introduced: 1) 'id' value for "acc_trans" payment related records are constant after they are set: when the payment row is created - it is assigned with particular 'id' value (as it works now), but after that, regardless of the payments sorting inside edit form, 'id' never gets reassigned and remains the same even after invoice is saved. Even if some rows get deleted - other rows keep the same 'id' values that they had before the update. 2) When new payment row is added

• IF there are no payment rows - 'id' column may be assigned with value 1 (as it works now)
• ELSE - 'id' column value is calculated based on maximum 'id' value of other payment rows as MAX(payment row id) + 1

Please, consider this ticket as proposal for solution and open for discussion. If any blockers or difficulties come up due to specificity of existing implementation - alternatives can be discussed.

Thanks.

[ledger123]

Hi @bohte ,

Got your points. Will update code after previous ticket which I am hoping to complete tomorrow morning.

Regards

[bohte]

Request is cancelled after re-discussing topic again