Fix for MifosX installations using Charges configured with Advanced accounting rules affected by MIFOSX 1557 - openMF/Deprecated-mifosx GitHub Wiki

The supplied SQL scripts help resolve issues with accruals of charges configured to use Advanced accounting rules in loan products using Accrual Accounting.

Details of the issue can be found at https://mifosforge.jira.com/browse/MIFOSX-1557

Delete Journal Entries created for the accruals of Charges configured with Advanced accounting rules

DELETE FROM acc_gl_journal_entry WHEREloan_transaction_id IN ( SELECT lt.id FROM acc_product_mapping pm JOIN m_product_loan lp ON lp.id=pm.product_id AND lp.accounting_type =3 JOIN m_loan loan ON loan.product_id = pm.product_id JOIN m_loan_transaction lt ON lt.loan_id = loan.id AND lt.transaction_type_enum = 10 WHERE pm.charge_id IS NOT NULL AND pm.product_type = 1 GROUP BY lt.id);

Updates Derived Fields in Loan Repayment Schedule to NULL

UPDATE m_loan_repayment_schedule rs JOIN ( SELECT lt.loan_id AS loanId,lt.transaction_date AS dueDate FROM acc_product_mapping pm JOIN m_product_loan lp ON lp.id=pm.product_id AND lp.accounting_type =3 JOIN m_loan loan ON loan.product_id = pm.product_id JOIN m_loan_transaction lt ON lt.loan_id = loan.id AND lt.transaction_type_enum = 10 WHERE pm.charge_id IS NOT NULL AND pm.product_type = 1 GROUP BY lt.id) AS x SET rs.accrual_interest_derived = NULL, rs.accrual_fee_charges_derived = NULL, rs.accrual_penalty_charges_derived = NULL WHERE rs.loan_id = x.loanId AND rs.duedate = x.dueDate;

Reverse loan accrual transactions

UPDATE m_loan_transaction lt JOIN ( SELECT loan.id AS loanId FROM acc_product_mapping pm JOIN m_product_loan lp ON lp.id=pm.product_id AND lp.accounting_type =3 JOIN m_loan loan ON loan.product_id = pm.product_id WHERE pm.charge_id IS NOT NULL AND pm.product_type = 1 GROUP BY loan.id) AS x SET lt.is_reversed = 1 WHERE lt.loan_id = x.loanId AND lt.transaction_type_enum = 10;

finally, run "Add Accrual Transactions" job from scheduler jobs.

⚠️ **GitHub.com Fallback** ⚠️