This is an automated email from the ASF dual-hosted git repository.
adamsaghy pushed a commit to branch develop
in repository https://gitbox.apache.org/repos/asf/fineract.git
The following commit(s) were added to refs/heads/develop by this push:
new 0ed6b7f94c FINERACT-2421: Fix Trial Balance report
0ed6b7f94c is described below
commit 0ed6b7f94c1bd0297619158d26c095b23cdb08ac
Author: Adam Saghy <[email protected]>
AuthorDate: Thu Jan 29 14:07:23 2026 +0100
FINERACT-2421: Fix Trial Balance report
---
.../db/changelog/tenant/changelog-tenant.xml | 1 +
...h_asset_owner_journal_entry_aggregation_fix.xml | 255 +++++++++++++++++++++
2 files changed, 256 insertions(+)
diff --git
a/fineract-provider/src/main/resources/db/changelog/tenant/changelog-tenant.xml
b/fineract-provider/src/main/resources/db/changelog/tenant/changelog-tenant.xml
index ffab7d77f6..231310cf67 100644
---
a/fineract-provider/src/main/resources/db/changelog/tenant/changelog-tenant.xml
+++
b/fineract-provider/src/main/resources/db/changelog/tenant/changelog-tenant.xml
@@ -226,4 +226,5 @@
<include file="parts/0205_add_read_familymembers_permission.xml"
relativeToChangelogFile="true" />
<include
file="parts/0206_transaction_summary_with_asset_owner_classification_name_bug_fix.xml"
relativeToChangelogFile="true" />
<include file="parts/0207_add_allow_full_term_for_tranche.xml"
relativeToChangelogFile="true" />
+ <include
file="parts/0208_trial_balance_summary_with_asset_owner_journal_entry_aggregation_fix.xml"
relativeToChangelogFile="true" />
</databaseChangeLog>
diff --git
a/fineract-provider/src/main/resources/db/changelog/tenant/parts/0208_trial_balance_summary_with_asset_owner_journal_entry_aggregation_fix.xml
b/fineract-provider/src/main/resources/db/changelog/tenant/parts/0208_trial_balance_summary_with_asset_owner_journal_entry_aggregation_fix.xml
new file mode 100644
index 0000000000..2937925a0a
--- /dev/null
+++
b/fineract-provider/src/main/resources/db/changelog/tenant/parts/0208_trial_balance_summary_with_asset_owner_journal_entry_aggregation_fix.xml
@@ -0,0 +1,255 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+
+ Licensed to the Apache Software Foundation (ASF) under one
+ or more contributor license agreements. See the NOTICE file
+ distributed with this work for additional information
+ regarding copyright ownership. The ASF licenses this file
+ to you under the Apache License, Version 2.0 (the
+ "License"); you may not use this file except in compliance
+ with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing,
+ software distributed under the License is distributed on an
+ "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ KIND, either express or implied. See the License for the
+ specific language governing permissions and limitations
+ under the License.
+
+-->
+<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog";
+ xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";
+
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.3.xsd";>
+
+ <changeSet author="fineract"
id="trial-balance-summary-with-asset-owner-update-4">
+ <update tableName="stretchy_report">
+ <column name="report_sql">
+ <![CDATA[
+WITH retained_earning AS (
+ SELECT DISTINCT
+ '${endDate}' AS postingdate,
+ lp.name AS product,
+ gl_code AS glacct,
+ COALESCE((SELECT name FROM acc_gl_account WHERE gl_code = e.gl_code),
'') AS description,
+ COALESCE(e.owner_external_id, 'self') AS assetowner,
+ SUM(opening_balance_amount) AS beginningbalance,
+ 0 AS debitmovement,
+ 0 AS creditmovement,
+ SUM(opening_balance_amount) AS endingbalance
+ FROM acc_gl_journal_entry_annual_summary e, m_product_loan lp
+ WHERE e.office_id = ${officeId}
+ AND lp.id = product_id
+ AND EXTRACT(YEAR FROM e.year_end_date) < EXTRACT(YEAR FROM
CAST('${endDate}' AS DATE))
+ GROUP BY gl_code, lp.name, office_id, owner_external_id
+),
+aggregated_date AS (
+ SELECT MAX(aggregated_on_date_to) AS latest
+ FROM m_journal_entry_aggregation_tracking
+ WHERE aggregated_on_date_to < '${endDate}'
+),
+summary_snapshot_baseline_data AS (
+ SELECT
+ lp.NAME AS productname,
+ acc_gl_account.gl_code AS glcode,
+ acc_gl_account.NAME AS glname,
+ CASE WHEN ags.external_owner_id IS NULL THEN 0 ELSE
ags.external_owner_id END AS assetowner,
+ SUM(ags.debit_amount) AS debitamount,
+ SUM(ags.credit_amount) AS creditamount
+ FROM acc_gl_account
+ JOIN m_journal_entry_aggregation_summary ags ON acc_gl_account.id =
ags.gl_account_id
+ JOIN m_product_loan lp ON lp.id = ags.product_id
+ WHERE ags.entity_type_enum = 1
+ AND ags.manual_entry = FALSE
+ AND ags.aggregated_on_date <= (SELECT latest FROM aggregated_date)
+ AND (ags.office_id = ${officeId})
+ GROUP BY productname, glcode, glname, assetowner
+),
+post_snapshot_delta_data AS (
+ SELECT
+ lp.NAME AS productname,
+ acc_gl_account.gl_code AS glcode,
+ acc_gl_account.NAME AS glname,
+ CASE WHEN aw.owner_id IS NULL THEN 0 ELSE aw.owner_id END AS
assetowner,
+ SUM(CASE WHEN acc_gl_journal_entry.type_enum = 2 THEN amount ELSE 0
END) AS debitamount,
+ SUM(CASE WHEN acc_gl_journal_entry.type_enum = 1 THEN amount ELSE 0
END) AS creditamount
+ FROM acc_gl_account
+ JOIN acc_gl_journal_entry ON acc_gl_account.id =
acc_gl_journal_entry.account_id
+ JOIN m_loan m ON m.id = acc_gl_journal_entry.entity_id
+ JOIN m_product_loan lp ON lp.id = m.product_id
+ LEFT JOIN m_external_asset_owner_journal_entry_mapping aw ON
aw.journal_entry_id = acc_gl_journal_entry.id
+ WHERE acc_gl_journal_entry.entity_type_enum = 1
+ AND acc_gl_journal_entry.manual_entry = FALSE
+ AND (
+ (SELECT latest FROM aggregated_date) IS NULL
+ OR acc_gl_journal_entry.submitted_on_date > (SELECT latest FROM
aggregated_date)
+ )
+ AND acc_gl_journal_entry.submitted_on_date < '${endDate}'
+ AND (acc_gl_journal_entry.office_id = ${officeId})
+ GROUP BY productname, glcode, glname, assetowner
+),
+
+/*
+ * merged_historical_data:
+ * Replaces FULL OUTER JOIN with:
+ * baseline LEFT JOIN delta
+ * UNION ALL
+ * delta LEFT JOIN baseline (anti-join)
+ */
+merged_historical_data AS (
+ SELECT
+ COALESCE(s.productname, p.productname) AS productname,
+ COALESCE(s.glcode, p.glcode) AS glcode,
+ COALESCE(s.glname, p.glname) AS glname,
+ COALESCE(s.assetowner, p.assetowner, 0) AS assetowner,
+ COALESCE(s.debitamount, 0) + COALESCE(p.debitamount, 0) AS
debitamount,
+ COALESCE(s.creditamount, 0) + COALESCE(p.creditamount, 0) AS
creditamount
+ FROM summary_snapshot_baseline_data s
+ LEFT JOIN post_snapshot_delta_data p
+ ON s.glcode = p.glcode
+ AND s.productname = p.productname
+ AND s.assetowner = p.assetowner
+
+ UNION ALL
+
+ SELECT
+ p.productname AS productname,
+ p.glcode AS glcode,
+ p.glname AS glname,
+ COALESCE(p.assetowner, 0) AS assetowner,
+ COALESCE(p.debitamount, 0) AS debitamount,
+ COALESCE(p.creditamount, 0) AS creditamount
+ FROM post_snapshot_delta_data p
+ LEFT JOIN summary_snapshot_baseline_data s
+ ON s.glcode = p.glcode
+ AND s.productname = p.productname
+ AND s.assetowner = p.assetowner
+ WHERE s.glcode IS NULL
+),
+current_cob_data AS (
+ SELECT
+ lp.name AS productname,
+ account_id,
+ acc_gl_account.gl_code AS glcode,
+ acc_gl_account.name AS glname,
+ CASE WHEN aw.owner_id IS NULL THEN 0 ELSE aw.owner_id END AS
assetowner,
+ SUM(CASE WHEN acc_gl_journal_entry.type_enum = 2 THEN amount ELSE 0
END) AS debitamount,
+ SUM(CASE WHEN acc_gl_journal_entry.type_enum = 1 THEN amount ELSE 0
END) AS creditamount
+ FROM acc_gl_journal_entry
+ JOIN acc_gl_account ON acc_gl_account.id = acc_gl_journal_entry.account_id
+ JOIN m_loan m ON m.id = acc_gl_journal_entry.entity_id
+ JOIN m_product_loan lp ON lp.id = m.product_id
+ LEFT JOIN m_external_asset_owner_journal_entry_mapping aw ON
aw.journal_entry_id = acc_gl_journal_entry.id
+ WHERE acc_gl_journal_entry.entity_type_enum = 1
+ AND acc_gl_journal_entry.manual_entry = FALSE
+ AND acc_gl_journal_entry.submitted_on_date = '${endDate}'
+ AND (acc_gl_journal_entry.office_id = ${officeId})
+ GROUP BY productname, account_id, glcode, glname, assetowner
+)
+
+SELECT *
+FROM (
+ SELECT *
+ FROM retained_earning
+ WHERE glacct = (SELECT gl_code FROM acc_gl_account WHERE name = 'Retained
Earnings Prior Year')
+
+ UNION
+
+ SELECT
+ txnreport.postingdate,
+ txnreport.product,
+ txnreport.glacct,
+ txnreport.description,
+ txnreport.assetowner,
+ (COALESCE(txnreport.beginningbalance, 0) +
COALESCE(summary.beginningbalance, 0)) AS beginningbalance,
+ txnreport.debitmovement AS debitmovement,
+ txnreport.creditmovement AS creditmovement,
+ (COALESCE(txnreport.endingbalance, 0) +
COALESCE(summary.beginningbalance, 0)) AS endingbalance
+ FROM (
+ SELECT *
+ FROM (
+ SELECT DISTINCT
+ '${endDate}' AS postingdate,
+ loan.pname AS product,
+ loan.gl_code AS glacct,
+ loan.glname AS description,
+ COALESCE((SELECT external_id FROM m_external_asset_owner WHERE
id = loan.assetowner), 'self') AS assetowner,
+ loan.openingbalance AS beginningbalance,
+ (loan.debitamount * 1) AS debitmovement,
+ (loan.creditamount * -1) AS creditmovement,
+ (loan.openingbalance + loan.debitamount - loan.creditamount)
AS endingbalance
+ FROM (
+ /*
+ * This replaces:
+ * g LEFT JOIN merged_historical_data
+ * FULL OUTER JOIN current_cob_data
+ * with a UNION ALL of:
+ * (1) g LEFT JOIN mh LEFT JOIN c
+ * (2) c-only rows (anti-join against g)
+ */
+ SELECT DISTINCT
+ g.pname AS pname,
+ g.gl_code AS gl_code,
+ g.glname AS glname,
+ COALESCE(mh.assetowner, c.assetowner, 0) AS assetowner,
+ COALESCE(mh.debitamount, 0) - COALESCE(mh.creditamount, 0)
AS openingbalance,
+ COALESCE(c.debitamount, 0) AS debitamount,
+ COALESCE(c.creditamount, 0) AS creditamount
+ FROM (
+ SELECT DISTINCT ag.gl_code, ag.id, pl.NAME AS pname,
ag.NAME AS glname
+ FROM acc_gl_account ag
+ JOIN acc_product_mapping am ON am.gl_account_id = ag.id
AND am.product_type = 1
+ JOIN m_product_loan pl ON pl.id = am.product_id
+ ) g
+ LEFT JOIN merged_historical_data mh
+ ON g.gl_code = mh.glcode
+ AND mh.productname = g.pname
+ LEFT JOIN current_cob_data c
+ ON g.gl_code = c.glcode
+ AND c.productname = g.pname
+ AND COALESCE(mh.assetowner, 0) = COALESCE(c.assetowner, 0)
+
+ UNION ALL
+
+ SELECT DISTINCT
+ c.productname AS pname,
+ c.glcode AS gl_code,
+ c.glname AS glname,
+ COALESCE(c.assetowner, 0) AS assetowner,
+ COALESCE(mh.debitamount, 0) - COALESCE(mh.creditamount, 0)
AS openingbalance,
+ COALESCE(c.debitamount, 0) AS debitamount,
+ COALESCE(c.creditamount, 0) AS creditamount
+ FROM current_cob_data c
+ LEFT JOIN (
+ SELECT DISTINCT ag.gl_code, pl.NAME AS pname
+ FROM acc_gl_account ag
+ JOIN acc_product_mapping am ON am.gl_account_id = ag.id
AND am.product_type = 1
+ JOIN m_product_loan pl ON pl.id = am.product_id
+ ) g2
+ ON g2.gl_code = c.glcode
+ AND g2.pname = c.productname
+ LEFT JOIN merged_historical_data mh
+ ON mh.glcode = c.glcode
+ AND mh.productname = c.productname
+ AND COALESCE(mh.assetowner, 0) = COALESCE(c.assetowner, 0)
+ WHERE g2.gl_code IS NULL
+ ) loan
+ ) a
+ ) AS txnreport
+ LEFT JOIN retained_earning summary
+ ON txnreport.glacct = summary.glacct
+ AND txnreport.assetowner = summary.assetowner
+ AND summary.product = txnreport.product
+) report
+WHERE report.endingbalance != 0
+ OR report.debitmovement != 0
+ OR report.creditmovement != 0
+ORDER BY glacct
+
+ ]]>
+ </column>
+ <where>report_name='Trial Balance Summary Report with Asset
Owner'</where>
+ </update>
+ </changeSet>
+</databaseChangeLog>
