You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@fineract.apache.org by ad...@apache.org on 2024/09/25 14:42:51 UTC

(fineract) branch develop updated: FINERACT-2081: Improve transaction summary with asset owner report

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 837dc6da2 FINERACT-2081: Improve transaction summary with asset owner report
837dc6da2 is described below

commit 837dc6da26349b14588bda66a2a4156266bf8ce2
Author: Oleksii Novikov <al...@onix-systems.com>
AuthorDate: Wed Sep 18 13:45:57 2024 +0300

    FINERACT-2081: Improve transaction summary with asset owner report
---
 .../db/changelog/tenant/changelog-tenant.xml       |    1 +
 ..._asset_owner_report_performance_improvement.xml | 1168 ++++++++++++++++++++
 .../InitiateExternalAssetOwnerTransferTest.java    |  256 ++++-
 3 files changed, 1420 insertions(+), 5 deletions(-)

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 6d9c8aaf1..8f7b9c640 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
@@ -166,4 +166,5 @@
     <include file="parts/0144_transaction_summary_with_asset_owner_report_unc_allocation_fix.xml" relativeToChangelogFile="true" />
     <include file="parts/0145_job_short_name.xml" relativeToChangelogFile="true" />
     <include file="parts/0147_enable_payment_hub_integration.xml" relativeToChangelogFile="true" />
+    <include file="parts/0148_transaction_summary_with_asset_owner_report_performance_improvement.xml" relativeToChangelogFile="true" />
 </databaseChangeLog>
diff --git a/fineract-provider/src/main/resources/db/changelog/tenant/parts/0148_transaction_summary_with_asset_owner_report_performance_improvement.xml b/fineract-provider/src/main/resources/db/changelog/tenant/parts/0148_transaction_summary_with_asset_owner_report_performance_improvement.xml
new file mode 100644
index 000000000..1972a1275
--- /dev/null
+++ b/fineract-provider/src/main/resources/db/changelog/tenant/parts/0148_transaction_summary_with_asset_owner_report_performance_improvement.xml
@@ -0,0 +1,1168 @@
+<?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="1">
+        <update tableName="stretchy_report">
+            <column name="report_sql"
+                    value="WITH
+
+slt_except_charge_adj_and_accrual AS (
+    SELECT '${endDate}' AS transactiondate,
+        t.id,
+        l.name,
+        d.payment_type_id,
+        t.transaction_type_enum,
+        t.amount,
+        t.overpayment_portion_derived,
+        t.principal_portion_derived,
+        t.interest_portion_derived,
+        t.fee_charges_portion_derived,
+        t.penalty_charges_portion_derived,
+        e.status,
+        e.settlement_date,
+        e.owner_id,
+        m.charged_off_on_date,
+        t.transaction_date,
+        m.charge_off_reason_cv_id
+    FROM m_loan_transaction t
+        JOIN m_loan m ON m.id = t.loan_id
+        JOIN m_product_loan l ON l.id = m.product_id
+        LEFT JOIN m_payment_detail d ON d.id = t.payment_detail_id
+        LEFT JOIN m_external_asset_owner_transfer e ON e.loan_id = t.loan_id
+        AND e.settlement_date &lt; '${endDate}'
+        AND e.effective_date_to &gt;= '${endDate}'
+    WHERE t.submitted_on_date = '${endDate}'
+        AND t.transaction_type_enum not in (10, 26)
+        AND (t.office_id = ${officeId})
+),
+
+slt_charge_adj AS (
+    SELECT '${endDate}' AS transactiondate,
+        t.id,
+        l.name,
+        t.transaction_type_enum,
+        d.payment_type_id,
+        t.overpayment_portion_derived,
+        t.principal_portion_derived,
+        t.interest_portion_derived,
+        t.fee_charges_portion_derived,
+        t.penalty_charges_portion_derived,
+        t.amount,
+        e.status,
+        e.settlement_date,
+        e.owner_id,
+        m.charged_off_on_date,
+        t.transaction_date,
+        m.charge_off_reason_cv_id
+    FROM m_loan_transaction t
+        JOIN m_loan m ON m.id = t.loan_id
+        JOIN m_product_loan l ON l.id = m.product_id
+        LEFT JOIN m_payment_detail d ON d.id = t.payment_detail_id
+        LEFT JOIN m_external_asset_owner_transfer e ON e.loan_id = t.loan_id
+        AND e.settlement_date &lt; '${endDate}'
+        AND e.effective_date_to &gt;= '${endDate}'
+    WHERE t.submitted_on_date = '${endDate}'
+        AND t.transaction_type_enum = 26
+        AND (t.office_id = ${officeId})
+),
+
+rlt_except_charge_adj_and_accrual AS (
+    SELECT '${endDate}' AS transactiondate,
+        t.id,
+        l.name,
+        t.transaction_type_enum,
+        d.payment_type_id,
+        t.overpayment_portion_derived,
+        t.principal_portion_derived,
+        t.interest_portion_derived,
+        t.fee_charges_portion_derived,
+        t.penalty_charges_portion_derived,
+        t.amount,
+        e.status,
+        e.settlement_date,
+        e.owner_id,
+        m.charged_off_on_date,
+        t.transaction_date,
+        m.charge_off_reason_cv_id
+    FROM m_loan_transaction t
+        JOIN m_loan m ON m.id = t.loan_id
+        JOIN m_product_loan l ON l.id = m.product_id
+        LEFT JOIN m_payment_detail d ON d.id = t.payment_detail_id
+        LEFT JOIN m_external_asset_owner_transfer e ON e.loan_id = t.loan_id
+        AND e.settlement_date &lt; '${endDate}'
+        AND e.effective_date_to &gt;= '${endDate}'
+    WHERE t.reversed_on_date = '${endDate}'
+        AND t.transaction_type_enum not in (10, 26)
+        AND (t.office_id = ${officeId})
+),
+
+rlt_charge_adj AS (
+    SELECT '${endDate}' AS transactiondate,
+        t.id,
+        l.name,
+        t.transaction_type_enum,
+        d.payment_type_id,
+        t.overpayment_portion_derived,
+        t.principal_portion_derived,
+        t.interest_portion_derived,
+        t.fee_charges_portion_derived,
+        t.penalty_charges_portion_derived,
+        t.amount,
+        e.status,
+        e.settlement_date,
+        e.owner_id,
+        m.charged_off_on_date,
+        t.transaction_date,
+        m.charge_off_reason_cv_id
+    FROM m_loan_transaction t
+        JOIN m_loan m ON m.id = t.loan_id
+        JOIN m_product_loan l ON l.id = m.product_id
+        LEFT JOIN m_payment_detail d ON d.id = t.payment_detail_id
+        LEFT JOIN m_external_asset_owner_transfer e ON e.loan_id = t.loan_id
+        AND e.settlement_date &lt; '${endDate}'
+        AND e.effective_date_to &gt;= '${endDate}'
+    WHERE t.reversed_on_date = '${endDate}'
+        AND t.transaction_type_enum = 26
+        AND (t.office_id = ${officeId})
+
+),
+
+active_external_asset_owner_transfers AS (
+    SELECT '${endDate}' AS transactiondate,
+        t.id,
+        p.name,
+        t.owner_id,
+        dt.principal_outstanding_derived,
+        dt.interest_outstanding_derived,
+        dt.fee_charges_outstanding_derived,
+        dt.penalty_charges_outstanding_derived,
+        dt.total_overpaid_derived
+    FROM m_external_asset_owner_transfer t
+        JOIN m_loan l ON l.id = t.loan_id
+        JOIN m_client c ON c.id = l.client_id
+        JOIN m_product_loan p ON p.id = l.product_id
+        JOIN m_external_asset_owner_transfer_details dt ON dt.asset_owner_transfer_id = t.id
+    WHERE t.status = 'ACTIVE'
+        AND c.office_id = ${officeId}
+        AND t.settlement_date = '${endDate}'
+),
+
+buyback_external_asset_owner_transfers AS (
+    SELECT '${endDate}' AS transactiondate,
+        t.id,
+        p.name,
+        dt.principal_outstanding_derived,
+        dt.interest_outstanding_derived,
+        dt.fee_charges_outstanding_derived,
+        dt.penalty_charges_outstanding_derived,
+        dt.total_overpaid_derived,
+        l.charged_off_on_date,
+        t.settlement_date,
+        l.charge_off_reason_cv_id
+    FROM m_external_asset_owner_transfer t
+        JOIN m_loan l ON l.id = t.loan_id
+        JOIN m_client c ON c.id = l.client_id
+        JOIN m_product_loan p ON p.id = l.product_id
+        JOIN m_external_asset_owner_transfer_details dt ON dt.asset_owner_transfer_id = t.id
+    WHERE t.status = 'BUYBACK'
+        AND c.office_id = ${officeId}
+        AND t.settlement_date = '${endDate}'
+)
+
+SELECT '${endDate}' AS TransactionDate,
+    a.product AS Product,
+    CASE
+        WHEN a.transaction_type = 9999 THEN 'Asset Transfer'
+        WHEN a.transaction_type = 99999 THEN 'Asset Buyback'
+        ELSE (
+            SELECT enum_message_property
+            FROM r_enum_value
+            WHERE enum_name = 'transaction_type_enum'
+                AND enum_id = a.transaction_type
+        )
+    END AS TransactionType_Name,
+    (SELECT value FROM m_payment_type WHERE id = a.payment_type_id) AS PaymentType_Name,
+    a.chargetype AS chargetype,
+    a.reversal_indicator AS Reversed,
+    a.Allocation_Type AS Allocation_Type,
+    (SELECT code_value FROM m_code_value WHERE id = a.charge_off_reason_id) AS Chargeoff_ReasonCode,
+    CASE
+        WHEN a.transaction_type = 9999 THEN sum(a.amount) * + 1
+        WHEN a.transaction_type = 99999 THEN sum(a.amount) * - 1
+        WHEN a.transaction_type IN (2, 23, 21, 22, 24, 4, 5, 8, 6, 27, 9, 26, 28)
+        AND a.reversal_indicator = false THEN sum(a.amount) * -1
+        WHEN a.transaction_type IN (2, 23, 21, 22, 24, 4, 5, 8, 6, 27, 9, 26, 28)
+        AND a.reversal_indicator = true THEN sum(a.amount) * + 1
+        WHEN a.transaction_type IN (1, 10, 25, 20)
+        AND a.reversal_indicator = false THEN sum(a.amount) * + 1
+        WHEN a.transaction_type IN (1, 10, 25, 20)
+        AND a.reversal_indicator = true THEN sum(a.amount) * -1
+    END AS Transaction_Amount,
+    (SELECT external_id FROM m_external_asset_owner WHERE id = a.asset_owner_id) AS Asset_owner_id
+FROM (
+        SELECT t.transactiondate,
+            t.id,
+            t.name AS product,
+            t.transaction_type_enum AS transaction_type,
+            t.payment_type_id,
+            '' AS chargetype,
+            false AS reversal_indicator,
+            'Principal' AS Allocation_Type,
+            CASE
+                WHEN t.transaction_type_enum in (1) THEN (
+                    CASE
+                        WHEN t.amount is null THEN 0
+                        WHEN t.overpayment_portion_derived is null THEN t.amount
+                        WHEN t.overpayment_portion_derived is not null THEN t.amount - t.overpayment_portion_derived
+                        ELSE t.amount
+                    END
+                )
+                ELSE (
+                    CASE
+                        WHEN t.principal_portion_derived is null THEN 0
+                        ELSE t.principal_portion_derived
+                    end
+                )
+            END amount,
+            CASE
+                WHEN t.status = 'ACTIVE'
+                AND t.settlement_date &lt; '${endDate}' THEN t.owner_id
+            END AS asset_owner_id,
+            CASE
+                WHEN t.transaction_type_enum = 27
+                OR (t.charged_off_on_date &lt;= t.transaction_date) THEN t.charge_off_reason_cv_id
+            END AS charge_off_reason_id
+        FROM slt_except_charge_adj_and_accrual AS t
+
+        UNION ALL
+
+        SELECT t.transactiondate,
+            t.id,
+            t.name AS product,
+            t.transaction_type_enum AS transaction_type,
+            t.payment_type_id,
+            '' AS chargetype,
+            false AS reversal_indicator,
+            'Interest' AS Allocation_Type,
+            CASE
+                WHEN t.interest_portion_derived is null THEN 0
+                ELSE t.interest_portion_derived
+            END AS amount,
+            CASE
+                WHEN t.status = 'ACTIVE'
+                AND t.settlement_date &lt; '${endDate}' THEN t.owner_id
+            END AS asset_owner_id,
+            CASE
+                WHEN t.transaction_type_enum = 27
+                OR (t.charged_off_on_date &lt;= t.transaction_date) THEN t.charge_off_reason_cv_id
+            END AS charge_off_reason_id
+        FROM slt_except_charge_adj_and_accrual AS t
+
+        UNION ALL
+
+        SELECT t.transactiondate,
+            t.id,
+            t.name AS product,
+            t.transaction_type_enum AS transaction_type,
+            t.payment_type_id,
+            '' AS chargetype,
+            false AS reversal_indicator,
+            'Fees' AS Allocation_Type,
+            CASE
+                WHEN t.fee_charges_portion_derived is null THEN 0
+                ELSE t.fee_charges_portion_derived
+            END AS amount,
+            CASE
+                WHEN t.status = 'ACTIVE'
+                AND t.settlement_date &lt; '${endDate}' THEN t.owner_id
+            END AS asset_owner_id,
+            CASE
+                WHEN t.transaction_type_enum = 27
+                OR (t.charged_off_on_date &lt;= t.transaction_date) THEN t.charge_off_reason_cv_id
+            END AS charge_off_reason_id
+        FROM slt_except_charge_adj_and_accrual AS t
+
+        UNION ALL
+
+        SELECT t.transactiondate,
+            t.id,
+            t.name AS product,
+            t.transaction_type_enum AS transaction_type,
+            t.payment_type_id,
+            '' AS chargetype,
+            false AS reversal_indicator,
+            'Penalty' AS Allocation_Type,
+            CASE
+                WHEN t.penalty_charges_portion_derived is null THEN 0
+                ELSE t.penalty_charges_portion_derived
+            END AS amount,
+            CASE
+                WHEN t.status = 'ACTIVE'
+                AND t.settlement_date &lt; '${endDate}' THEN t.owner_id
+            END AS asset_owner_id,
+            CASE
+                WHEN t.transaction_type_enum = 27
+                OR (t.charged_off_on_date &lt;= t.transaction_date) THEN t.charge_off_reason_cv_id
+            END AS charge_off_reason_id
+        FROM slt_except_charge_adj_and_accrual AS t
+
+        UNION ALL
+
+        SELECT t.transactiondate,
+            t.id,
+            t.name AS product,
+            t.transaction_type_enum AS transaction_type,
+            t.payment_type_id,
+            '' AS chargetype,
+            false AS reversal_indicator,
+            'Unallocated Credit (UNC)' AS Allocation_Type,
+            CASE
+                WHEN t.overpayment_portion_derived is null THEN 0
+                ELSE t.overpayment_portion_derived
+            END AS amount,
+            CASE
+                WHEN t.status = 'ACTIVE'
+                AND t.settlement_date &lt; '${endDate}' THEN t.owner_id
+            END AS asset_owner_id,
+            CASE
+                WHEN t.transaction_type_enum = 27
+                OR (t.charged_off_on_date &lt;= t.transaction_date) THEN t.charge_off_reason_cv_id
+            END AS charge_off_reason_id
+        FROM slt_except_charge_adj_and_accrual AS t
+
+        UNION ALL
+
+        SELECT '${endDate}' AS transactiondate,
+            t.id,
+            l.name AS product,
+            t.transaction_type_enum AS transaction_type,
+            null AS payment_type_id,
+            mc.name AS chargetype,
+            false AS reversal_indicator,
+            'Fees' AS Allocation_Type,
+            CASE
+                WHEN pd.amount is null THEN 0
+                ELSE pd.amount
+            END AS amount,
+            CASE
+                WHEN e.status = 'ACTIVE'
+                AND e.settlement_date &lt; '${endDate}' THEN e.owner_id
+            END AS asset_owner_id,
+            CASE
+                WHEN t.transaction_type_enum = 27
+                OR (m.charged_off_on_date &lt;= t.transaction_date) THEN m.charge_off_reason_cv_id
+            END AS charge_off_reason_id
+        FROM m_loan_transaction t
+            JOIN m_loan m ON m.id = t.loan_id
+            JOIN m_product_loan l ON l.id = m.product_id
+            JOIN m_loan_charge_paid_by pd ON pd.loan_transaction_id = t.id
+            JOIN m_loan_charge c ON c.id = pd.loan_charge_id
+            JOIN m_charge mc ON mc.id = c.charge_id AND mc.is_penalty = false
+            LEFT JOIN m_external_asset_owner_transfer e ON e.loan_id = t.loan_id
+            AND e.settlement_date &lt; '${endDate}'
+            AND e.effective_date_to &gt;= '${endDate}'
+        WHERE t.submitted_on_date = '${endDate}'
+            AND t.transaction_type_enum = 10
+            AND t.is_reversed = false
+            AND (t.office_id = ${officeId})
+
+        UNION ALL
+
+        SELECT '${endDate}' AS transactiondate,
+            t.id,
+            l.name AS product,
+            t.transaction_type_enum AS transaction_type,
+            null AS payment_type_id,
+            mc.name AS chargetype,
+            false AS reversal_indicator,
+            'Penalty' AS Allocation_Type,
+            CASE
+                WHEN pd.amount is null THEN 0
+                ELSE pd.amount
+            END AS amount,
+            CASE
+                WHEN e.status = 'ACTIVE'
+                AND e.settlement_date &lt; '${endDate}' THEN e.owner_id
+            END AS asset_owner_id,
+            CASE
+                WHEN t.transaction_type_enum = 27
+                OR (m.charged_off_on_date &lt;= t.transaction_date) THEN m.charge_off_reason_cv_id
+            END AS charge_off_reason_id
+        FROM m_loan_transaction t
+            JOIN m_loan m ON m.id = t.loan_id
+            JOIN m_product_loan l ON l.id = m.product_id
+            JOIN m_loan_charge_paid_by pd ON pd.loan_transaction_id = t.id
+            JOIN m_loan_charge c ON c.id = pd.loan_charge_id
+            JOIN m_charge mc ON mc.id = c.charge_id AND mc.is_penalty = true
+            LEFT JOIN m_external_asset_owner_transfer e ON e.loan_id = t.loan_id
+            AND e.settlement_date &lt; '${endDate}'
+            AND e.effective_date_to &gt;= '${endDate}'
+        WHERE t.submitted_on_date = '${endDate}'
+            AND t.transaction_type_enum = 10
+            AND t.is_reversed = false
+            AND (t.office_id = ${officeId})
+
+        UNION ALL
+
+        SELECT '${endDate}' AS transactiondate,
+            t.id,
+            l.name AS product,
+            t.transaction_type_enum AS transaction_type,
+            null AS payment_type_id,
+            '' AS chargetype,
+            false AS reversal_indicator,
+            'Interest' AS Allocation_Type,
+            CASE
+                WHEN t.interest_portion_derived is null THEN 0
+                ELSE t.interest_portion_derived
+            END AS amount,
+            CASE
+                WHEN e.status = 'ACTIVE'
+                AND e.settlement_date &lt; '${endDate}' THEN e.owner_id
+            END AS asset_owner_id,
+            CASE
+                WHEN t.transaction_type_enum = 27
+                OR (m.charged_off_on_date &lt;= t.transaction_date) THEN m.charge_off_reason_cv_id
+            END AS charge_off_reason_id
+        FROM m_loan_transaction t
+            JOIN m_loan m ON m.id = t.loan_id
+            JOIN m_product_loan l ON l.id = m.product_id
+            LEFT JOIN m_external_asset_owner_transfer e ON e.loan_id = t.loan_id
+            AND e.settlement_date &lt; '${endDate}'
+            AND e.effective_date_to &gt;= '${endDate}'
+        WHERE t.submitted_on_date = '${endDate}'
+            AND t.transaction_type_enum = 10
+            AND t.is_reversed = false
+            AND (t.office_id = ${officeId})
+
+        UNION ALL
+
+        SELECT '${endDate}' AS transactiondate,
+            t.id,
+            l.name AS product,
+            t.transaction_type_enum AS transaction_type,
+            d.payment_type_id,
+            mc.name AS chargetype,
+            false AS reversal_indicator,
+            'Fees' AS Allocation_Type,
+            CASE
+                WHEN pd.amount is null THEN 0
+                ELSE pd.amount
+            END AS amount,
+            CASE
+                WHEN e.status = 'ACTIVE'
+                AND e.settlement_date &lt; '${endDate}' THEN e.owner_id
+            END AS asset_owner_id,
+            CASE
+                WHEN t.transaction_type_enum = 27
+                OR (m.charged_off_on_date &lt;= t.transaction_date) THEN m.charge_off_reason_cv_id
+            END AS charge_off_reason_id
+        FROM m_loan_transaction t
+            JOIN m_loan m ON m.id = t.loan_id
+            JOIN m_product_loan l ON l.id = m.product_id
+            JOIN m_loan_charge_paid_by pd ON pd.loan_transaction_id = t.id
+            JOIN m_loan_charge c ON c.id = pd.loan_charge_id
+            JOIN m_charge mc ON mc.id = c.charge_id AND mc.is_penalty = false
+            LEFT JOIN m_payment_detail d ON d.id = t.payment_detail_id
+            LEFT JOIN m_external_asset_owner_transfer e ON e.loan_id = t.loan_id
+            AND e.settlement_date &lt; '${endDate}'
+            AND e.effective_date_to &gt;= '${endDate}'
+        WHERE t.submitted_on_date = '${endDate}'
+            AND t.transaction_type_enum = 26
+            AND (t.office_id = ${officeId})
+
+        UNION ALL
+
+        SELECT '${endDate}' AS transactiondate,
+            t.id,
+            l.name AS product,
+            t.transaction_type_enum AS transaction_type,
+            d.payment_type_id,
+            mc.name AS chargetype,
+            false AS reversal_indicator,
+            'Penalty' AS Allocation_Type,
+            CASE
+                WHEN pd.amount is null THEN 0
+                ELSE pd.amount
+            END AS amount,
+            CASE
+                WHEN e.status = 'ACTIVE'
+                AND e.settlement_date &lt; '${endDate}' THEN e.owner_id
+            END AS asset_owner_id,
+            CASE
+                WHEN t.transaction_type_enum = 27
+                OR (m.charged_off_on_date &lt;= t.transaction_date) THEN m.charge_off_reason_cv_id
+            END AS charge_off_reason_id
+        FROM m_loan_transaction t
+            JOIN m_loan m ON m.id = t.loan_id
+            JOIN m_product_loan l ON l.id = m.product_id
+            JOIN m_loan_charge_paid_by pd ON pd.loan_transaction_id = t.id
+            JOIN m_loan_charge c ON c.id = pd.loan_charge_id
+            JOIN m_charge mc ON mc.id = c.charge_id AND mc.is_penalty = true
+            LEFT JOIN m_payment_detail d ON d.id = t.payment_detail_id
+            LEFT JOIN m_external_asset_owner_transfer e ON e.loan_id = t.loan_id
+            AND e.settlement_date &lt; '${endDate}'
+            AND e.effective_date_to &gt;= '${endDate}'
+        WHERE t.submitted_on_date = '${endDate}'
+            AND t.transaction_type_enum = 26
+            AND (t.office_id = ${officeId})
+
+        UNION ALL
+
+        SELECT '${endDate}' AS transactiondate,
+            t.id,
+            t.name AS product,
+            t.transaction_type_enum AS transaction_type,
+            t.payment_type_id,
+            '' AS chargetype,
+            false AS reversal_indicator,
+            'Interest' AS Allocation_Type,
+            CASE
+                WHEN t.interest_portion_derived is null THEN 0
+                ELSE t.interest_portion_derived
+            END AS amount,
+            CASE
+                WHEN t.status = 'ACTIVE'
+                AND t.settlement_date &lt; '${endDate}' THEN t.owner_id
+            END AS asset_owner_id,
+            CASE
+                WHEN t.transaction_type_enum = 27
+                OR (t.charged_off_on_date &lt;= t.transaction_date) THEN t.charge_off_reason_cv_id
+            END AS charge_off_reason_id
+        FROM slt_charge_adj AS t
+
+        UNION ALL
+
+        SELECT '${endDate}' AS transactiondate,
+            t.id,
+            t.name AS product,
+            t.transaction_type_enum AS transaction_type,
+            t.payment_type_id,
+            '' AS chargetype,
+            false AS reversal_indicator,
+            'Principal' AS Allocation_Type,
+            CASE
+                WHEN t.principal_portion_derived is null THEN 0
+                ELSE t.principal_portion_derived
+            END AS amount,
+            CASE
+                WHEN t.status = 'ACTIVE'
+                AND t.settlement_date &lt; '${endDate}' THEN t.owner_id
+            END AS asset_owner_id,
+            CASE
+                WHEN t.transaction_type_enum = 27
+                OR (t.charged_off_on_date &lt;= t.transaction_date) THEN t.charge_off_reason_cv_id
+            END AS charge_off_reason_id
+        FROM slt_charge_adj AS t
+
+        UNION ALL
+
+        SELECT '${endDate}' AS transactiondate,
+            t.id,
+            t.name AS product,
+            t.transaction_type_enum AS transaction_type,
+            t.payment_type_id,
+            '' AS chargetype,
+            false AS reversal_indicator,
+            'Unallocated Credit (UNC)' AS Allocation_Type,
+            CASE
+                WHEN t.overpayment_portion_derived is null THEN 0
+                ELSE t.overpayment_portion_derived
+            END AS amount,
+            CASE
+                WHEN t.status = 'ACTIVE'
+                AND t.settlement_date &lt; '${endDate}' THEN t.owner_id
+            END AS asset_owner_id,
+            CASE
+                WHEN t.transaction_type_enum = 27
+                OR (t.charged_off_on_date &lt;= t.transaction_date) THEN t.charge_off_reason_cv_id
+            END AS charge_off_reason_id
+        FROM slt_charge_adj AS t
+
+        UNION ALL
+
+        SELECT '${endDate}' AS transactiondate,
+            t.id,
+            t.name AS product,
+            t.transaction_type_enum AS transaction_type,
+            t.payment_type_id,
+            '' AS chargetype,
+            true AS reversal_indicator,
+            'Principal' AS Allocation_Type,
+            CASE
+                WHEN t.transaction_type_enum in (1) THEN (
+                    CASE
+                        WHEN t.amount is null THEN 0
+                        WHEN t.overpayment_portion_derived is null THEN t.amount
+                        WHEN t.overpayment_portion_derived is not null THEN t.amount - t.overpayment_portion_derived
+                        ELSE t.amount
+                    END
+                )
+                ELSE (
+                    CASE
+                        WHEN t.principal_portion_derived is null THEN 0
+                        ELSE t.principal_portion_derived
+                    end
+                )
+            END amount,
+            CASE
+                WHEN t.status = 'ACTIVE'
+                AND t.settlement_date &lt; '${endDate}' THEN t.owner_id
+            END AS asset_owner_id,
+            CASE
+                WHEN t.transaction_type_enum = 27
+                OR (t.charged_off_on_date &lt;= t.transaction_date) THEN t.charge_off_reason_cv_id
+            END AS charge_off_reason_id
+        FROM rlt_except_charge_adj_and_accrual AS t
+
+        UNION ALL
+
+        SELECT '${endDate}' AS transactiondate,
+            t.id,
+            t.name AS product,
+            t.transaction_type_enum AS transaction_type,
+            t.payment_type_id,
+            '' AS chargetype,
+            true AS reversal_indicator,
+            'Interest' AS Allocation_Type,
+            CASE
+                WHEN t.interest_portion_derived is null THEN 0
+                ELSE t.interest_portion_derived
+            END AS amount,
+            CASE
+                WHEN t.status = 'ACTIVE'
+                AND t.settlement_date &lt; '${endDate}' THEN t.owner_id
+            END AS asset_owner_id,
+            CASE
+                WHEN t.transaction_type_enum = 27
+                OR (t.charged_off_on_date &lt;= t.transaction_date) THEN t.charge_off_reason_cv_id
+            END AS charge_off_reason_id
+        FROM rlt_except_charge_adj_and_accrual AS t
+
+        UNION ALL
+
+        SELECT '${endDate}' AS transactiondate,
+            t.id,
+            t.name AS product,
+            t.transaction_type_enum AS transaction_type,
+            t.payment_type_id,
+            '' AS chargetype,
+            true AS reversal_indicator,
+            'Fees' AS Allocation_Type,
+            CASE
+                WHEN t.fee_charges_portion_derived is null THEN 0
+                ELSE t.fee_charges_portion_derived
+            END AS amount,
+            CASE
+                WHEN t.status = 'ACTIVE'
+                AND t.settlement_date &lt; '${endDate}' THEN t.owner_id
+            END AS asset_owner_id,
+            CASE
+                WHEN t.transaction_type_enum = 27
+                OR (t.charged_off_on_date &lt;= t.transaction_date) THEN t.charge_off_reason_cv_id
+            END AS charge_off_reason_id
+        FROM rlt_except_charge_adj_and_accrual AS t
+
+        UNION ALL
+
+        SELECT '${endDate}' AS transactiondate,
+            t.id,
+            t.name AS product,
+            t.transaction_type_enum AS transaction_type,
+            t.payment_type_id,
+            '' AS chargetype,
+            true AS reversal_indicator,
+            'Penalty' AS Allocation_Type,
+            CASE
+                WHEN t.penalty_charges_portion_derived is null THEN 0
+                ELSE t.penalty_charges_portion_derived
+            END AS amount,
+            CASE
+                WHEN t.status = 'ACTIVE'
+                AND t.settlement_date &lt; '${endDate}' THEN t.owner_id
+            END AS asset_owner_id,
+            CASE
+                WHEN t.transaction_type_enum = 27
+                OR (t.charged_off_on_date &lt;= t.transaction_date) THEN t.charge_off_reason_cv_id
+            END AS charge_off_reason_id
+        FROM rlt_except_charge_adj_and_accrual AS t
+
+        UNION ALL
+
+        SELECT '${endDate}' AS transactiondate,
+            t.id,
+            t.name AS product,
+            t.transaction_type_enum AS transaction_type,
+            t.payment_type_id,
+            '' AS chargetype,
+            true AS reversal_indicator,
+            'Unallocated Credit (UNC)' AS Allocation_Type,
+            CASE
+                WHEN t.overpayment_portion_derived is null THEN 0
+                ELSE t.overpayment_portion_derived
+            END AS amount,
+            CASE
+                WHEN t.status = 'ACTIVE'
+                AND t.settlement_date &lt; '${endDate}' THEN t.owner_id
+            END AS asset_owner_id,
+            CASE
+                WHEN t.transaction_type_enum = 27
+                OR (t.charged_off_on_date &lt;= t.transaction_date) THEN t.charge_off_reason_cv_id
+            END AS charge_off_reason_id
+        FROM rlt_except_charge_adj_and_accrual AS t
+
+        UNION ALL
+
+        SELECT '${endDate}' AS transactiondate,
+            t.id,
+            l.name AS product,
+            t.transaction_type_enum AS transaction_type,
+            null AS payment_type_id,
+            mc.name AS chargetype,
+            true AS reversal_indicator,
+            'Fees' AS Allocation_Type,
+            CASE
+                WHEN pd.amount is null THEN 0
+                ELSE pd.amount
+            END AS amount,
+            CASE
+                WHEN e.status = 'ACTIVE'
+                AND e.settlement_date &lt; '${endDate}' THEN e.owner_id
+            END AS asset_owner_id,
+            CASE
+                WHEN t.transaction_type_enum = 27
+                OR (m.charged_off_on_date &lt;= t.transaction_date) THEN m.charge_off_reason_cv_id
+            END AS charge_off_reason_id
+        FROM m_loan_transaction t
+            JOIN m_loan m ON m.id = t.loan_id
+            JOIN m_product_loan l ON l.id = m.product_id
+            JOIN m_loan_charge_paid_by pd ON pd.loan_transaction_id = t.id
+            JOIN m_loan_charge c ON c.id = pd.loan_charge_id
+            JOIN m_charge mc ON mc.id = c.charge_id AND mc.is_penalty = false
+            LEFT JOIN m_external_asset_owner_transfer e ON e.loan_id = t.loan_id
+            AND e.settlement_date &lt; '${endDate}'
+            AND e.effective_date_to &gt;= '${endDate}'
+        WHERE t.reversed_on_date = '${endDate}'
+            AND t.transaction_type_enum = 10
+            AND t.is_reversed = true
+            AND (t.office_id = ${officeId})
+
+        UNION ALL
+
+        SELECT '${endDate}' AS transactiondate,
+            t.id,
+            l.name AS product,
+            t.transaction_type_enum AS transaction_type,
+            null AS payment_type_id,
+            mc.name AS chargetype,
+            true AS reversal_indicator,
+            'Penalty' AS Allocation_Type,
+            CASE
+                WHEN pd.amount is null THEN 0
+                ELSE pd.amount
+            END AS amount,
+            CASE
+                WHEN e.status = 'ACTIVE'
+                AND e.settlement_date &lt; '${endDate}' THEN e.owner_id
+            END AS asset_owner_id,
+            CASE
+                WHEN t.transaction_type_enum = 27
+                OR (m.charged_off_on_date &lt;= t.transaction_date) THEN m.charge_off_reason_cv_id
+            END AS charge_off_reason_id
+        FROM m_loan_transaction t
+            JOIN m_loan m ON m.id = t.loan_id
+            JOIN m_product_loan l ON l.id = m.product_id
+            JOIN m_loan_charge_paid_by pd ON pd.loan_transaction_id = t.id
+            JOIN m_loan_charge c ON c.id = pd.loan_charge_id
+            JOIN m_charge mc ON mc.id = c.charge_id AND mc.is_penalty = true
+            LEFT JOIN m_external_asset_owner_transfer e ON e.loan_id = t.loan_id
+            AND e.settlement_date &lt; '${endDate}'
+            AND e.effective_date_to &gt;= '${endDate}'
+        WHERE t.reversed_on_date = '${endDate}'
+            AND t.transaction_type_enum = 10
+            AND t.is_reversed = true
+            AND (t.office_id = ${officeId})
+
+        UNION ALL
+
+        SELECT '${endDate}' AS transactiondate,
+            t.id,
+            l.name AS product,
+            t.transaction_type_enum AS transaction_type,
+            null AS payment_type_id,
+            '' AS chargetype,
+            true AS reversal_indicator,
+            'Interest' AS Allocation_Type,
+            CASE
+                WHEN t.interest_portion_derived is null THEN 0
+                ELSE t.interest_portion_derived
+            END AS amount,
+            CASE
+                WHEN e.status = 'ACTIVE'
+                AND e.settlement_date &lt; '${endDate}' THEN e.owner_id
+            END AS asset_owner_id,
+            CASE
+                WHEN t.transaction_type_enum = 27
+                OR (m.charged_off_on_date &lt;= t.transaction_date) THEN m.charge_off_reason_cv_id
+            END AS charge_off_reason_id
+        FROM m_loan_transaction t
+            JOIN m_loan m ON m.id = t.loan_id
+            JOIN m_product_loan l ON l.id = m.product_id
+            LEFT JOIN m_external_asset_owner_transfer e ON e.loan_id = t.loan_id
+            AND e.settlement_date &lt; '${endDate}'
+            AND e.effective_date_to &gt;= '${endDate}'
+        WHERE t.reversed_on_date = '${endDate}'
+            AND t.transaction_type_enum = 10
+            AND t.is_reversed = true
+            AND (t.office_id = ${officeId})
+
+        UNION ALL
+
+        SELECT '${endDate}' AS transactiondate,
+            t.id,
+            l.name AS product,
+            t.transaction_type_enum AS transaction_type,
+            d.payment_type_id,
+            mc.name AS chargetype,
+            true AS reversal_indicator,
+            'Fees' AS Allocation_Type,
+            CASE
+                WHEN pd.amount is null THEN 0
+                ELSE pd.amount
+            END AS amount,
+            CASE
+                WHEN e.status = 'ACTIVE'
+                AND e.settlement_date &lt; '${endDate}' THEN e.owner_id
+            END AS asset_owner_id,
+            CASE
+                WHEN t.transaction_type_enum = 27
+                OR (m.charged_off_on_date &lt;= t.transaction_date) THEN m.charge_off_reason_cv_id
+            END AS charge_off_reason_id
+        FROM m_loan_transaction t
+            JOIN m_loan m ON m.id = t.loan_id
+            JOIN m_product_loan l ON l.id = m.product_id
+            JOIN m_loan_charge_paid_by pd ON pd.loan_transaction_id = t.id
+            JOIN m_loan_charge c ON c.id = pd.loan_charge_id
+            JOIN m_charge mc ON mc.id = c.charge_id AND mc.is_penalty = false
+            LEFT JOIN m_payment_detail d ON d.id = t.payment_detail_id
+            LEFT JOIN m_external_asset_owner_transfer e ON e.loan_id = t.loan_id
+            AND e.settlement_date &lt; '${endDate}'
+            AND e.effective_date_to &gt;= '${endDate}'
+        WHERE t.reversed_on_date = '${endDate}'
+            AND t.transaction_type_enum = 26
+            AND (t.office_id = ${officeId})
+
+        UNION ALL
+
+        SELECT '${endDate}' AS transactiondate,
+            t.id,
+            l.name AS product,
+            t.transaction_type_enum AS transaction_type,
+            d.payment_type_id,
+            mc.name AS chargetype,
+            true AS reversal_indicator,
+            'Penalty' AS Allocation_Type,
+            CASE
+                WHEN pd.amount is null THEN 0
+                ELSE pd.amount
+            END AS amount,
+            CASE
+                WHEN e.status = 'ACTIVE'
+                AND e.settlement_date &lt; '${endDate}' THEN e.owner_id
+            END AS asset_owner_id,
+            CASE
+                WHEN t.transaction_type_enum = 27
+                OR (m.charged_off_on_date &lt;= t.transaction_date) THEN m.charge_off_reason_cv_id
+            END AS charge_off_reason_id
+        FROM m_loan_transaction t
+            JOIN m_loan m ON m.id = t.loan_id
+            JOIN m_product_loan l ON l.id = m.product_id
+            JOIN m_loan_charge_paid_by pd ON pd.loan_transaction_id = t.id
+            JOIN m_loan_charge c ON c.id = pd.loan_charge_id
+            JOIN m_charge mc ON mc.id = c.charge_id AND mc.is_penalty = true
+            LEFT JOIN m_payment_detail d ON d.id = t.payment_detail_id
+            LEFT JOIN m_external_asset_owner_transfer e ON e.loan_id = t.loan_id
+            AND e.settlement_date &lt; '${endDate}'
+            AND e.effective_date_to &gt;= '${endDate}'
+        WHERE t.reversed_on_date = '${endDate}'
+            AND t.transaction_type_enum = 26
+            AND (t.office_id = ${officeId})
+
+        UNION ALL
+
+        SELECT '${endDate}' AS transactiondate,
+            t.id,
+            t.name AS product,
+            t.transaction_type_enum AS transaction_type,
+            t.payment_type_id,
+            '' AS chargetype,
+            true AS reversal_indicator,
+            'Interest' AS Allocation_Type,
+            CASE
+                WHEN t.interest_portion_derived is null THEN 0
+                ELSE t.interest_portion_derived
+            END AS amount,
+            CASE
+                WHEN t.status = 'ACTIVE'
+                AND t.settlement_date &lt; '${endDate}' THEN t.owner_id
+            END AS asset_owner_id,
+            CASE
+                WHEN t.transaction_type_enum = 27
+                OR (t.charged_off_on_date &lt;= t.transaction_date) THEN t.charge_off_reason_cv_id
+            END AS charge_off_reason_id
+        FROM rlt_charge_adj AS t
+
+        UNION ALL
+
+        SELECT '${endDate}' AS transactiondate,
+            t.id,
+            t.name AS product,
+            t.transaction_type_enum AS transaction_type,
+            t.payment_type_id,
+            '' AS chargetype,
+            true AS reversal_indicator,
+            'Principal' AS Allocation_Type,
+            CASE
+                WHEN t.principal_portion_derived is null THEN 0
+                ELSE t.principal_portion_derived
+            END AS amount,
+            CASE
+                WHEN t.status = 'ACTIVE'
+                AND t.settlement_date &lt; '${endDate}' THEN t.owner_id
+            END AS asset_owner_id,
+            CASE
+                WHEN t.transaction_type_enum = 27
+                OR (t.charged_off_on_date &lt;= t.transaction_date) THEN t.charge_off_reason_cv_id
+            END AS charge_off_reason_id
+        FROM rlt_charge_adj AS t
+
+        UNION ALL
+
+        SELECT '${endDate}' AS transactiondate,
+            t.id,
+            t.name AS product,
+            t.transaction_type_enum AS transaction_type,
+            t.payment_type_id,
+            '' AS chargetype,
+            true AS reversal_indicator,
+            'Unallocated Credit (UNC)' AS Allocation_Type,
+            CASE
+                WHEN t.overpayment_portion_derived is null THEN 0
+                ELSE t.overpayment_portion_derived
+            END AS amount,
+            CASE
+                WHEN t.status = 'ACTIVE'
+                AND t.settlement_date &lt; '${endDate}' THEN t.owner_id
+            END AS asset_owner_id,
+            CASE
+                WHEN t.transaction_type_enum = 27
+                OR (t.charged_off_on_date &lt;= t.transaction_date) THEN t.charge_off_reason_cv_id
+            END AS charge_off_reason_id
+        FROM rlt_charge_adj AS t
+
+        UNION ALL
+
+        SELECT t.transactiondate,
+            t.id,
+            t.name AS product,
+            9999 AS transaction_type,
+            null AS payment_type_id,
+            '' AS chargetype,
+            false AS reversal_indicator,
+            'Principal' AS Allocation_type,
+            t.principal_outstanding_derived AS amount,
+            t.owner_id AS asset_owner_id,
+            null AS charge_off_reason_id
+        FROM active_external_asset_owner_transfers AS t
+        WHERE t.principal_outstanding_derived &gt; 0
+
+        UNION ALL
+
+        SELECT t.transactiondate,
+            t.id,
+            t.name AS product,
+            9999 AS transaction_type,
+            null AS payment_type_id,
+            '' AS chargetype,
+            false AS reversal_indicator,
+            'Interest' AS Allocation_type,
+            t.interest_outstanding_derived AS amount,
+            t.owner_id AS asset_owner_id,
+            null AS charge_off_reason_id
+        FROM active_external_asset_owner_transfers AS t
+        WHERE t.interest_outstanding_derived &gt; 0
+
+        UNION ALL
+
+        SELECT t.transactiondate,
+            t.id,
+            t.name AS product,
+            9999 AS transaction_type,
+            null AS payment_type_id,
+            '' AS chargetype,
+            false AS reversal_indicator,
+            'Fees' AS Allocation_type,
+            t.fee_charges_outstanding_derived AS amount,
+            t.owner_id AS asset_owner_id,
+            null AS charge_off_reason_id
+        FROM active_external_asset_owner_transfers AS t
+        WHERE t.fee_charges_outstanding_derived &gt; 0
+
+        UNION ALL
+
+        SELECT t.transactiondate,
+            t.id,
+            t.name AS product,
+            9999 AS transaction_type,
+            null AS payment_type_id,
+            '' AS chargetype,
+            false AS reversal_indicator,
+            'Penalty' AS Allocation_type,
+            t.penalty_charges_outstanding_derived AS amount,
+            t.owner_id AS asset_owner_id,
+            null AS charge_off_reason_id
+        FROM active_external_asset_owner_transfers AS t
+        WHERE t.penalty_charges_outstanding_derived &gt; 0
+
+        UNION ALL
+
+        SELECT t.transactiondate,
+            t.id,
+            t.name AS product,
+            9999 AS transaction_type,
+            null AS payment_type_id,
+            '' AS chargetype,
+            false AS reversal_indicator,
+            'Unallocated Credit (UNC)' AS Allocation_type,
+            t.total_overpaid_derived AS amount,
+            t.owner_id AS asset_owner_id,
+            null AS charge_off_reason_id
+        FROM active_external_asset_owner_transfers AS t
+        WHERE t.total_overpaid_derived &gt; 0
+
+        UNION ALL
+
+        SELECT '${endDate}' AS transactiondate,
+            t.id,
+            t.name AS product,
+            99999 AS transaction_type,
+            null AS payment_type_id,
+            '' AS chargetype,
+            false AS reversal_indicator,
+            'Principal' AS Allocation_type,
+            t.principal_outstanding_derived AS amount,
+            null AS asset_owner_id,
+            CASE
+                WHEN t.charged_off_on_date &lt;= t.settlement_date THEN t.charge_off_reason_cv_id
+            END AS charge_off_reason_id
+        FROM buyback_external_asset_owner_transfers AS t
+        WHERE t.principal_outstanding_derived &gt; 0
+
+        UNION ALL
+
+        SELECT '${endDate}' AS transactiondate,
+            t.id,
+            t.name AS product,
+            99999 AS transaction_type,
+            null AS payment_type_id,
+            '' AS chargetype,
+            false AS reversal_indicator,
+            'Interest' AS Allocation_type,
+            t.interest_outstanding_derived AS amount,
+            null AS asset_owner_id,
+            CASE
+                WHEN t.charged_off_on_date &lt;= t.settlement_date THEN t.charge_off_reason_cv_id
+            END AS charge_off_reason_id
+        FROM buyback_external_asset_owner_transfers AS t
+        WHERE t.interest_outstanding_derived &gt; 0
+
+        UNION ALL
+
+        SELECT '${endDate}' AS transactiondate,
+            t.id,
+            t.name AS product,
+            99999 AS transaction_type,
+            null AS payment_type_id,
+            '' AS chargetype,
+            false AS reversal_indicator,
+            'Fees' AS Allocation_type,
+            t.fee_charges_outstanding_derived AS amount,
+            null AS asset_owner_id,
+            CASE
+                WHEN t.charged_off_on_date &lt;= t.settlement_date THEN t.charge_off_reason_cv_id
+            END AS charge_off_reason_id
+        FROM buyback_external_asset_owner_transfers AS t
+        WHERE t.fee_charges_outstanding_derived &gt; 0
+
+        UNION ALL
+
+        SELECT '${endDate}' AS transactiondate,
+            t.id,
+            t.name AS product,
+            99999 AS transaction_type,
+            null AS payment_type_id,
+            '' AS chargetype,
+            false AS reversal_indicator,
+            'Penalty' AS Allocation_type,
+            t.penalty_charges_outstanding_derived AS amount,
+            null AS asset_owner_id,
+            CASE
+                WHEN t.charged_off_on_date &lt;= t.settlement_date THEN t.charge_off_reason_cv_id
+            END AS charge_off_reason_id
+        FROM buyback_external_asset_owner_transfers AS t
+        WHERE t.penalty_charges_outstanding_derived &gt; 0
+
+        UNION ALL
+
+        SELECT '${endDate}' AS transactiondate,
+            t.id,
+            t.name AS product,
+            99999 AS transaction_type,
+            null AS payment_type_id,
+            '' AS chargetype,
+            false AS reversal_indicator,
+            'Unallocated Credit (UNC)' AS Allocation_type,
+            t.total_overpaid_derived * -1 AS amount,
+            null AS asset_owner_id,
+            CASE
+                WHEN t.charged_off_on_date &lt;= t.settlement_date THEN t.charge_off_reason_cv_id
+            END AS charge_off_reason_id
+        FROM buyback_external_asset_owner_transfers AS t
+        WHERE t.total_overpaid_derived &gt; 0
+
+    ) a
+GROUP BY a.transactiondate,
+    a.product,
+    a.transaction_type,
+    a.payment_type_id,
+    a.chargetype,
+    a.reversal_indicator,
+    a.Allocation_Type,
+    a.asset_owner_id,
+    a.charge_off_reason_id
+ORDER BY 1,2,3,4,5,6,7,8,9,10"/>
+            <where>report_name='Transaction Summary Report with Asset Owner'</where>
+        </update>
+    </changeSet>
+</databaseChangeLog>
diff --git a/integration-tests/src/test/java/org/apache/fineract/integrationtests/investor/externalassetowner/InitiateExternalAssetOwnerTransferTest.java b/integration-tests/src/test/java/org/apache/fineract/integrationtests/investor/externalassetowner/InitiateExternalAssetOwnerTransferTest.java
index dcab08598..bee35a68a 100644
--- a/integration-tests/src/test/java/org/apache/fineract/integrationtests/investor/externalassetowner/InitiateExternalAssetOwnerTransferTest.java
+++ b/integration-tests/src/test/java/org/apache/fineract/integrationtests/investor/externalassetowner/InitiateExternalAssetOwnerTransferTest.java
@@ -28,6 +28,7 @@ import static org.apache.fineract.client.models.ExternalTransferData.SubStatusEn
 import static org.apache.fineract.client.models.ExternalTransferData.SubStatusEnum.UNSOLD;
 import static org.apache.fineract.infrastructure.businessdate.domain.BusinessDateType.BUSINESS_DATE;
 import static org.junit.jupiter.api.Assertions.assertEquals;
+import static org.junit.jupiter.api.Assertions.assertFalse;
 import static org.junit.jupiter.api.Assertions.assertNotNull;
 import static org.junit.jupiter.api.Assertions.assertNull;
 import static org.junit.jupiter.api.Assertions.assertThrows;
@@ -39,6 +40,7 @@ import io.restassured.http.ContentType;
 import io.restassured.path.json.JsonPath;
 import io.restassured.specification.RequestSpecification;
 import io.restassured.specification.ResponseSpecification;
+import java.io.IOException;
 import java.math.BigDecimal;
 import java.time.LocalDate;
 import java.time.format.DateTimeFormatter;
@@ -46,6 +48,7 @@ import java.time.format.DateTimeFormatterBuilder;
 import java.util.ArrayList;
 import java.util.HashMap;
 import java.util.List;
+import java.util.Map;
 import java.util.Objects;
 import java.util.Optional;
 import java.util.UUID;
@@ -63,12 +66,14 @@ import org.apache.fineract.client.models.PostInitiateTransferResponse;
 import org.apache.fineract.client.models.PostLoansLoanIdTransactionsRequest;
 import org.apache.fineract.client.util.CallFailedRuntimeException;
 import org.apache.fineract.infrastructure.event.external.service.validation.ExternalEventDTO;
+import org.apache.fineract.integrationtests.client.IntegrationTest;
 import org.apache.fineract.integrationtests.common.BusinessDateHelper;
 import org.apache.fineract.integrationtests.common.BusinessStepHelper;
 import org.apache.fineract.integrationtests.common.ClientHelper;
 import org.apache.fineract.integrationtests.common.CollateralManagementHelper;
 import org.apache.fineract.integrationtests.common.ExternalAssetOwnerHelper;
 import org.apache.fineract.integrationtests.common.GlobalConfigurationHelper;
+import org.apache.fineract.integrationtests.common.OfficeHelper;
 import org.apache.fineract.integrationtests.common.SchedulerJobHelper;
 import org.apache.fineract.integrationtests.common.Utils;
 import org.apache.fineract.integrationtests.common.accounting.Account;
@@ -88,14 +93,11 @@ import org.junit.jupiter.api.Assertions;
 import org.junit.jupiter.api.BeforeAll;
 import org.junit.jupiter.api.Test;
 import org.junit.jupiter.api.extension.ExtendWith;
-import org.slf4j.Logger;
-import org.slf4j.LoggerFactory;
 
 @SuppressWarnings("rawtypes")
 @ExtendWith({ LoanTestLifecycleExtension.class, ExternalEventsExtension.class })
-public class InitiateExternalAssetOwnerTransferTest {
+public class InitiateExternalAssetOwnerTransferTest extends IntegrationTest {
 
-    private static final Logger LOG = LoggerFactory.getLogger(InitiateExternalAssetOwnerTransferTest.class);
     private static ResponseSpecification RESPONSE_SPEC;
     private static RequestSpecification REQUEST_SPEC;
     private static Account ASSET_ACCOUNT;
@@ -108,9 +110,10 @@ public class InitiateExternalAssetOwnerTransferTest {
     private static ExternalAssetOwnerHelper EXTERNAL_ASSET_OWNER_HELPER;
     private static LoanTransactionHelper LOAN_TRANSACTION_HELPER;
     private static SchedulerJobHelper SCHEDULER_JOB_HELPER;
+    private static OfficeHelper OFFICE_HELPER;
     private static LocalDate TODAYS_DATE;
     public String ownerExternalId;
-    private DateTimeFormatter dateFormatter = new DateTimeFormatterBuilder().appendPattern("dd MMMM yyyy").toFormatter();
+    private final DateTimeFormatter dateFormatter = new DateTimeFormatterBuilder().appendPattern("dd MMMM yyyy").toFormatter();
 
     @BeforeAll
     public static void setupInvestorBusinessStep() {
@@ -123,6 +126,7 @@ public class InitiateExternalAssetOwnerTransferTest {
         SCHEDULER_JOB_HELPER = new SchedulerJobHelper(REQUEST_SPEC);
         FINANCIAL_ACTIVITY_ACCOUNT_HELPER = new FinancialActivityAccountHelper(REQUEST_SPEC);
         LOAN_TRANSACTION_HELPER = new LoanTransactionHelper(REQUEST_SPEC, RESPONSE_SPEC);
+        OFFICE_HELPER = new OfficeHelper(REQUEST_SPEC, RESPONSE_SPEC);
 
         TODAYS_DATE = Utils.getLocalDateOfTenant();
         new BusinessStepHelper().updateSteps("LOAN_CLOSE_OF_BUSINESS", "APPLY_CHARGE_TO_OVERDUE_LOANS", "LOAN_DELINQUENCY_CLASSIFICATION",
@@ -936,6 +940,248 @@ public class InitiateExternalAssetOwnerTransferTest {
         }
     }
 
+    @Test
+    public void transactionSummaryReportWithAssetOwner() throws IOException {
+        try {
+            GlobalConfigurationHelper.manageConfigurations(REQUEST_SPEC, RESPONSE_SPEC,
+                    GlobalConfigurationHelper.ENABLE_AUTOGENERATED_EXTERNAL_ID, true);
+            setInitialBusinessDate("2020-03-02");
+
+            ExternalEventHelper.deleteAllExternalEvents(REQUEST_SPEC, new ResponseSpecBuilder().expectStatusCode(Matchers.is(204)).build());
+            ExternalEventHelper.changeEventState(REQUEST_SPEC, RESPONSE_SPEC, "LoanOwnershipTransferBusinessEvent", true);
+
+            final var officeId = OFFICE_HELPER.createOffice("1 January 2020");
+            final var clientID = ClientHelper.createClient(REQUEST_SPEC, RESPONSE_SPEC, "1 January 2020", officeId.toString());
+            final var loanID = createLoanForClient(clientID);
+            addPenaltyForLoan(loanID, "10");
+
+            final var saleTransferResponse = createSaleTransfer(loanID, "2020-03-02");
+            validateResponse(saleTransferResponse, loanID);
+            getAndValidateExternalAssetOwnerTransferByLoan(loanID,
+                    ExpectedExternalTransferData.expected(PENDING, saleTransferResponse.getResourceExternalId(), "2020-03-02", "2020-03-02",
+                            "9999-12-31", false, new BigDecimal("15767.420000"), new BigDecimal("15000.000000"),
+                            new BigDecimal("757.420000"), new BigDecimal("10.000000"), new BigDecimal("0.000000"),
+                            new BigDecimal("0.000000")));
+            getAndValidateThereIsNoActiveMapping(saleTransferResponse.getResourceExternalId());
+            var retrieveResponse = EXTERNAL_ASSET_OWNER_HELPER.retrieveTransfersByLoanId(loanID.longValue());
+            retrieveResponse.getContent().forEach(transfer -> getAndValidateThereIsNoJournalEntriesForTransfer(transfer.getTransferId()));
+
+            updateBusinessDateAndExecuteCOBJob("2020-03-03");
+            getAndValidateExternalAssetOwnerTransferByLoan(loanID,
+                    ExpectedExternalTransferData.expected(PENDING, saleTransferResponse.getResourceExternalId(), "2020-03-02", "2020-03-02",
+                            "2020-03-02", false, new BigDecimal("15767.420000"), new BigDecimal("15000.000000"),
+                            new BigDecimal("757.420000"), new BigDecimal("10.000000"), new BigDecimal("0.000000"),
+                            new BigDecimal("0.000000")),
+                    ExpectedExternalTransferData.expected(ACTIVE, saleTransferResponse.getResourceExternalId(), "2020-03-02", "2020-03-03",
+                            "9999-12-31", true, new BigDecimal("15767.420000"), new BigDecimal("15000.000000"),
+                            new BigDecimal("757.420000"), new BigDecimal("10.000000"), new BigDecimal("0.000000"),
+                            new BigDecimal("0.000000")));
+
+            final var allExternalEvents = ExternalEventHelper.getAllExternalEvents(REQUEST_SPEC, RESPONSE_SPEC);
+            Assertions.assertEquals(1, allExternalEvents.size());
+            Assertions.assertEquals("LoanOwnershipTransferBusinessEvent", allExternalEvents.get(0).getType());
+            Assertions.assertEquals(Long.valueOf(loanID), allExternalEvents.get(0).getAggregateRootId());
+
+            ExternalEventHelper.deleteAllExternalEvents(REQUEST_SPEC, new ResponseSpecBuilder().expectStatusCode(Matchers.is(204)).build());
+            ExternalEventHelper.changeEventState(REQUEST_SPEC, RESPONSE_SPEC, "LoanOwnershipTransferBusinessEvent", true);
+
+            getAndValidateThereIsActiveMapping(loanID);
+
+            final var expectedDate = LocalDate.of(2020, 3, 2);
+            final var initial = 0;
+
+            final var buybackTransferResponse = createBuybackTransfer(loanID, "2020-03-03");
+            validateResponse(buybackTransferResponse, loanID);
+            getAndValidateExternalAssetOwnerTransferByLoan(loanID,
+                    ExpectedExternalTransferData.expected(PENDING, saleTransferResponse.getResourceExternalId(), "2020-03-02", "2020-03-02",
+                            "2020-03-02", false, new BigDecimal("15767.420000"), new BigDecimal("15000.000000"),
+                            new BigDecimal("757.420000"), new BigDecimal("10.000000"), new BigDecimal("0.000000"),
+                            new BigDecimal("0.000000")),
+                    ExpectedExternalTransferData.expected(ACTIVE, saleTransferResponse.getResourceExternalId(), "2020-03-02", "2020-03-03",
+                            "9999-12-31", true, new BigDecimal("15767.420000"), new BigDecimal("15000.000000"),
+                            new BigDecimal("757.420000"), new BigDecimal("10.000000"), new BigDecimal("0.000000"),
+                            new BigDecimal("0.000000")),
+                    ExpectedExternalTransferData.expected(BUYBACK, buybackTransferResponse.getResourceExternalId(), "2020-03-03",
+                            "2020-03-03", "9999-12-31", false, new BigDecimal("15767.420000"), new BigDecimal("15000.000000"),
+                            new BigDecimal("757.420000"), new BigDecimal("10.000000"), new BigDecimal("0.000000"),
+                            new BigDecimal("0.000000")));
+            getAndValidateThereIsActiveMapping(loanID);
+            retrieveResponse = EXTERNAL_ASSET_OWNER_HELPER.retrieveTransfersByLoanId(loanID.longValue());
+            getAndValidateThereIsNoJournalEntriesForTransfer(retrieveResponse.getContent().get(initial + 2).getTransferId());
+
+            LOAN_TRANSACTION_HELPER.makeLoanRepayment((long) loanID, new PostLoansLoanIdTransactionsRequest().dateFormat("dd MMMM yyyy")
+                    .transactionDate(dateFormatter.format(expectedDate)).locale("en").transactionAmount(5.0));
+
+            updateBusinessDateAndExecuteCOBJob("2020-03-04");
+            getAndValidateExternalAssetOwnerTransferByLoan(loanID,
+                    ExpectedExternalTransferData.expected(PENDING, saleTransferResponse.getResourceExternalId(), "2020-03-02", "2020-03-02",
+                            "2020-03-02", false, new BigDecimal("15767.420000"), new BigDecimal("15000.000000"),
+                            new BigDecimal("757.420000"), new BigDecimal("10.000000"), new BigDecimal("0.000000"),
+                            new BigDecimal("0.000000")),
+                    ExpectedExternalTransferData.expected(ACTIVE, saleTransferResponse.getResourceExternalId(), "2020-03-02", "2020-03-03",
+                            "2020-03-03", true, new BigDecimal("15767.420000"), new BigDecimal("15000.000000"),
+                            new BigDecimal("757.420000"), new BigDecimal("10.000000"), new BigDecimal("0.000000"),
+                            new BigDecimal("0.000000")),
+                    ExpectedExternalTransferData.expected(BUYBACK, buybackTransferResponse.getResourceExternalId(), "2020-03-03",
+                            "2020-03-03", "2020-03-03", true, new BigDecimal("15762.420000"), new BigDecimal("15000.000000"),
+                            new BigDecimal("757.420000"), new BigDecimal("5.000000"), new BigDecimal("0.000000"),
+                            new BigDecimal("0.000000")));
+            getAndValidateThereIsNoActiveMapping(saleTransferResponse.getResourceExternalId());
+            retrieveResponse = EXTERNAL_ASSET_OWNER_HELPER.retrieveTransfersByLoanId(loanID.longValue());
+
+            final var reportResult = fineract().reportsRun.runReportGetFile("Transaction Summary Report with Asset Owner",
+                    Map.of("R_endDate", "2020-03-03", "R_officeId", officeId.toString(), "output-type", "CSV"), false).execute();
+
+            assertNotNull(reportResult.body());
+            final var csvContent = reportResult.body().string();
+            final var jsonPath = JsonPath.from(csvContent);
+
+            assertNotNull(jsonPath.getString("columnHeaders[0].columnType"));
+            assertNotNull(jsonPath.getString("columnHeaders[0].columnDisplayType"));
+            assertFalse(jsonPath.getBoolean("columnHeaders[0].isColumnNullable"));
+
+            assertNotNull(jsonPath.getString("columnHeaders[1].columnType"));
+            assertNotNull(jsonPath.getString("columnHeaders[1].columnDisplayType"));
+            assertFalse(jsonPath.getBoolean("columnHeaders[1].isColumnNullable"));
+
+            assertNotNull(jsonPath.getString("columnHeaders[2].columnType"));
+            assertNotNull(jsonPath.getString("columnHeaders[2].columnDisplayType"));
+            assertFalse(jsonPath.getBoolean("columnHeaders[2].isColumnNullable"));
+
+            assertNotNull(jsonPath.getString("columnHeaders[3].columnType"));
+            assertNotNull(jsonPath.getString("columnHeaders[3].columnDisplayType"));
+            assertFalse(jsonPath.getBoolean("columnHeaders[3].isColumnNullable"));
+
+            assertNotNull(jsonPath.getString("columnHeaders[4].columnType"));
+            assertNotNull(jsonPath.getString("columnHeaders[4].columnDisplayType"));
+            assertFalse(jsonPath.getBoolean("columnHeaders[4].isColumnNullable"));
+
+            assertNotNull(jsonPath.getString("columnHeaders[5].columnType"));
+            assertNotNull(jsonPath.getString("columnHeaders[5].columnDisplayType"));
+            assertFalse(jsonPath.getBoolean("columnHeaders[5].isColumnNullable"));
+
+            assertNotNull(jsonPath.getString("columnHeaders[6].columnType"));
+            assertNotNull(jsonPath.getString("columnHeaders[6].columnDisplayType"));
+            assertFalse(jsonPath.getBoolean("columnHeaders[6].isColumnNullable"));
+
+            assertNotNull(jsonPath.getString("columnHeaders[7].columnType"));
+            assertNotNull(jsonPath.getString("columnHeaders[7].columnDisplayType"));
+            assertFalse(jsonPath.getBoolean("columnHeaders[7].isColumnNullable"));
+
+            assertNotNull(jsonPath.getString("columnHeaders[8].columnType"));
+            assertNotNull(jsonPath.getString("columnHeaders[8].columnDisplayType"));
+            assertFalse(jsonPath.getBoolean("columnHeaders[8].isColumnNullable"));
+
+            assertNotNull(jsonPath.getString("columnHeaders[9].columnType"));
+            assertNotNull(jsonPath.getString("columnHeaders[9].columnDisplayType"));
+            assertFalse(jsonPath.getBoolean("columnHeaders[9].isColumnNullable"));
+
+            assertNotNull(retrieveResponse.getContent().get(0).getOwner());
+            final var ownerId = retrieveResponse.getContent().get(0).getOwner().getExternalId();
+
+            assertEquals("2020-03-03", jsonPath.getString("data[0].row[0]"));
+            assertTrue(jsonPath.getString("data[0].row[1]").matches("^LOAN_PRODUCT_.{6}$"));
+            assertEquals("Apply Charges", jsonPath.getString("data[0].row[2]"));
+            assertNull(jsonPath.getString("data[0].row[3]"));
+            assertEquals("", jsonPath.getString("data[0].row[4]"));
+            assertFalse(jsonPath.getBoolean("data[0].row[5]"));
+            assertEquals("Interest", jsonPath.getString("data[0].row[6]"));
+            assertNull(jsonPath.getString("data[0].row[7]"));
+            assertEquals(9.68, jsonPath.getDouble("data[0].row[8]"), 0.01);
+            assertEquals(ownerId, jsonPath.getString("data[0].row[9]"));
+
+            assertEquals("2020-03-03", jsonPath.getString("data[1].row[0]"));
+            assertTrue(jsonPath.getString("data[1].row[1]").matches("^LOAN_PRODUCT_.{6}$"));
+            assertEquals("Asset Buyback", jsonPath.getString("data[1].row[2]"));
+            assertNull(jsonPath.getString("data[1].row[3]"));
+            assertEquals("", jsonPath.getString("data[1].row[4]"));
+            assertFalse(jsonPath.getBoolean("data[1].row[5]"));
+            assertEquals("Interest", jsonPath.getString("data[1].row[6]"));
+            assertNull(jsonPath.getString("data[1].row[7]"));
+            assertEquals(-757.42, jsonPath.getDouble("data[1].row[8]"), 0.01);
+            assertNull(jsonPath.getString("data[1].row[9]"));
+
+            assertEquals("2020-03-03", jsonPath.getString("data[2].row[0]"));
+            assertTrue(jsonPath.getString("data[2].row[1]").matches("^LOAN_PRODUCT_.{6}$"));
+            assertEquals("Asset Buyback", jsonPath.getString("data[2].row[2]"));
+            assertNull(jsonPath.getString("data[2].row[3]"));
+            assertEquals("", jsonPath.getString("data[2].row[4]"));
+            assertFalse(jsonPath.getBoolean("data[2].row[5]"));
+            assertEquals("Penalty", jsonPath.getString("data[2].row[6]"));
+            assertNull(jsonPath.getString("data[2].row[7]"));
+            assertEquals(-5.00, jsonPath.getDouble("data[2].row[8]"), 0.01);
+            assertNull(jsonPath.getString("data[2].row[9]"));
+
+            assertEquals("2020-03-03", jsonPath.getString("data[3].row[0]"));
+            assertTrue(jsonPath.getString("data[3].row[1]").matches("^LOAN_PRODUCT_.{6}$"));
+            assertEquals("Asset Buyback", jsonPath.getString("data[3].row[2]"));
+            assertNull(jsonPath.getString("data[3].row[3]"));
+            assertEquals("", jsonPath.getString("data[3].row[4]"));
+            assertFalse(jsonPath.getBoolean("data[3].row[5]"));
+            assertEquals("Principal", jsonPath.getString("data[3].row[6]"));
+            assertNull(jsonPath.getString("data[3].row[7]"));
+            assertEquals(-15000.00, jsonPath.getDouble("data[3].row[8]"), 0.01);
+            assertNull(jsonPath.getString("data[3].row[9]"));
+
+            assertEquals("2020-03-03", jsonPath.getString("data[4].row[0]"));
+            assertTrue(jsonPath.getString("data[4].row[1]").matches("^LOAN_PRODUCT_.{6}$"));
+            assertEquals("Repayment", jsonPath.getString("data[4].row[2]"));
+            assertNull(jsonPath.getString("data[4].row[3]"));
+            assertEquals("", jsonPath.getString("data[4].row[4]"));
+            assertFalse(jsonPath.getBoolean("data[4].row[5]"));
+            assertEquals("Fees", jsonPath.getString("data[4].row[6]"));
+            assertNull(jsonPath.getString("data[4].row[7]"));
+            assertEquals(0.00, jsonPath.getDouble("data[4].row[8]"), 0.01);
+            assertEquals(ownerId, jsonPath.getString("data[4].row[9]"));
+
+            assertEquals("2020-03-03", jsonPath.getString("data[5].row[0]"));
+            assertTrue(jsonPath.getString("data[5].row[1]").matches("^LOAN_PRODUCT_.{6}$"));
+            assertEquals("Repayment", jsonPath.getString("data[5].row[2]"));
+            assertNull(jsonPath.getString("data[5].row[3]"));
+            assertEquals("", jsonPath.getString("data[5].row[4]"));
+            assertFalse(jsonPath.getBoolean("data[5].row[5]"));
+            assertEquals("Interest", jsonPath.getString("data[5].row[6]"));
+            assertNull(jsonPath.getString("data[5].row[7]"));
+            assertEquals(0.00, jsonPath.getDouble("data[5].row[8]"), 0.01);
+            assertEquals(ownerId, jsonPath.getString("data[5].row[9]"));
+
+            assertEquals("2020-03-03", jsonPath.getString("data[6].row[0]"));
+            assertTrue(jsonPath.getString("data[6].row[1]").matches("^LOAN_PRODUCT_.{6}$"));
+            assertEquals("Repayment", jsonPath.getString("data[6].row[2]"));
+            assertNull(jsonPath.getString("data[6].row[3]"));
+            assertEquals("", jsonPath.getString("data[6].row[4]"));
+            assertFalse(jsonPath.getBoolean("data[6].row[5]"));
+            assertEquals("Penalty", jsonPath.getString("data[6].row[6]"));
+            assertNull(jsonPath.getString("data[6].row[7]"));
+            assertEquals(-5.00, jsonPath.getDouble("data[6].row[8]"), 0.01);
+            assertEquals(ownerId, jsonPath.getString("data[6].row[9]"));
+
+            assertEquals("2020-03-03", jsonPath.getString("data[7].row[0]"));
+            assertTrue(jsonPath.getString("data[7].row[1]").matches("^LOAN_PRODUCT_.{6}$"));
+            assertEquals("Repayment", jsonPath.getString("data[7].row[2]"));
+            assertNull(jsonPath.getString("data[7].row[3]"));
+            assertEquals("", jsonPath.getString("data[7].row[4]"));
+            assertFalse(jsonPath.getBoolean("data[7].row[5]"));
+            assertEquals("Principal", jsonPath.getString("data[7].row[6]"));
+            assertNull(jsonPath.getString("data[7].row[7]"));
+            assertEquals(0.00, jsonPath.getDouble("data[7].row[8]"), 0.01);
+            assertEquals(ownerId, jsonPath.getString("data[7].row[9]"));
+
+            assertEquals("2020-03-03", jsonPath.getString("data[8].row[0]"));
+            assertTrue(jsonPath.getString("data[8].row[1]").matches("^LOAN_PRODUCT_.{6}$"));
+            assertEquals("Repayment", jsonPath.getString("data[8].row[2]"));
+            assertNull(jsonPath.getString("data[8].row[3]"));
+            assertEquals("", jsonPath.getString("data[8].row[4]"));
+            assertFalse(jsonPath.getBoolean("data[8].row[5]"));
+            assertEquals("Unallocated Credit (UNC)", jsonPath.getString("data[8].row[6]"));
+            assertNull(jsonPath.getString("data[8].row[7]"));
+            assertEquals(0.00, jsonPath.getDouble("data[8].row[8]"), 0.01);
+            assertEquals(ownerId, jsonPath.getString("data[8].row[9]"));
+        } finally {
+            cleanUpAndRestoreBusinessDate();
+        }
+    }
+
     private void updateBusinessDateAndExecuteCOBJob(String date) {
         BusinessDateHelper.updateBusinessDate(REQUEST_SPEC, RESPONSE_SPEC, BUSINESS_DATE, LocalDate.parse(date));
         SCHEDULER_JOB_HELPER.executeAndAwaitJob("Loan COB");