(fineract) branch develop updated: FINERACT-2467: Add previous owner id to transaction summary report with asset owner

[adamsaghy]

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 7faaf96246 FINERACT-2467: Add previous owner id to transaction summary 
report with asset owner
7faaf96246 is described below

commit 7faaf96246713946bc2d1f240966d7ce244ad8ac
Author: mariiaKraievska <riask...@gmail.com>
AuthorDate: Fri Feb 6 15:20:32 2026 +0200

    FINERACT-2467: Add previous owner id to transaction summary report with 
asset owner
---
 .../db/changelog/tenant/changelog-tenant.xml       |    1 +
 ...ner_and_from_asset_owner_id_for_asset_sales.xml | 3120 ++++++++++++++++++++
 .../InitiateExternalAssetOwnerTransferTest.java    |  111 +
 3 files changed, 3232 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 231310cf67..34da1913f5 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
@@ -227,4 +227,5 @@
     <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" />
+    <include 
file="parts/0209_transaction_summary_with_asset_owner_and_from_asset_owner_id_for_asset_sales.xml"
 relativeToChangelogFile="true" />
 </databaseChangeLog>
diff --git 
a/fineract-provider/src/main/resources/db/changelog/tenant/parts/0209_transaction_summary_with_asset_owner_and_from_asset_owner_id_for_asset_sales.xml
 
b/fineract-provider/src/main/resources/db/changelog/tenant/parts/0209_transaction_summary_with_asset_owner_and_from_asset_owner_id_for_asset_sales.xml
new file mode 100644
index 0000000000..db2db12d25
--- /dev/null
+++ 
b/fineract-provider/src/main/resources/db/changelog/tenant/parts/0209_transaction_summary_with_asset_owner_and_from_asset_owner_id_for_asset_sales.xml
@@ -0,0 +1,3120 @@
+<?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-pg" context="postgresql">
+        <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,
+        CASE
+            WHEN d.payment_type_id IS NULL AND t.classification_cv_id IS NOT 
NULL THEN
+                (SELECT code_value FROM m_code_value WHERE id = 
t.classification_cv_id)
+            ELSE NULL
+        END AS classification_name,
+        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, 32, 34, 36, 39)
+        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,
+        CASE
+            WHEN d.payment_type_id IS NULL AND t.classification_cv_id IS NOT 
NULL THEN
+                (SELECT code_value FROM m_code_value WHERE id = 
t.classification_cv_id)
+            ELSE NULL
+        END AS classification_name,
+        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, 32, 34, 36, 39)
+        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})
+),
+
+slt_cap_income_amortization AS (
+    SELECT '${endDate}' AS transactiondate,
+        t.id,
+        l.name,
+        t.transaction_type_enum,
+        d.payment_type_id,
+        CASE
+            WHEN d.payment_type_id IS NULL AND bt.classification_cv_id IS NOT 
NULL
+                THEN
+                    (SELECT code_value FROM m_code_value WHERE id = 
bt.classification_cv_id)
+            ELSE NULL
+            END AS classification_name,
+        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
+        JOIN m_loan_amortization_allocation_mapping map
+            ON map.amortization_loan_transaction_id = t.id
+        JOIN m_loan_transaction bt ON bt.id = map.base_loan_transaction_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.is_reversed = false
+        AND t.transaction_type_enum IN (36, 39)
+        AND (t.office_id = ${officeId})
+),
+rlt_cap_income_amortization AS (
+    SELECT '${endDate}' AS transactiondate,
+        t.id,
+        l.name,
+        t.transaction_type_enum,
+        d.payment_type_id,
+        CASE
+            WHEN d.payment_type_id IS NULL AND bt.classification_cv_id IS NOT 
NULL
+                THEN
+                    (SELECT code_value FROM m_code_value WHERE id = 
bt.classification_cv_id)
+            ELSE NULL
+            END AS classification_name,
+        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
+        JOIN m_loan_amortization_allocation_mapping map
+            ON map.amortization_loan_transaction_id = t.id
+        JOIN m_loan_transaction bt ON bt.id = map.base_loan_transaction_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.is_reversed = true
+        AND t.transaction_type_enum IN (36, 39)
+        AND (t.office_id = ${officeId})
+),
+active_external_asset_owner_transfers AS (
+    SELECT '${endDate}' AS transactiondate,
+        t.id,
+        p.name,
+        t.owner_id,
+        t.previous_owner_id,
+        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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+        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,
+        t.owner_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 in ('BUYBACK', 'BUYBACK_INTERMEDIATE')
+        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,
+    COALESCE(
+               (SELECT value FROM m_payment_type WHERE id = a.payment_type_id),
+               a.classification_name
+       ) 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, 31, 33, 34, 37, 39)
+        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, 31, 33, 34, 37, 39)
+        AND a.reversal_indicator = true THEN sum(a.amount) * + 1
+        WHEN a.transaction_type IN (1, 10, 25, 20, 35, 36)
+        AND a.reversal_indicator = false THEN sum(a.amount) * + 1
+        WHEN a.transaction_type IN (1, 10, 25, 20, 35, 36)
+        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,
+    (SELECT external_id FROM m_external_asset_owner WHERE id = 
a.from_asset_owner_id) AS From_asset_owner_id
+FROM (
+        SELECT t.transactiondate,
+            t.id,
+            t.name AS product,
+            t.transaction_type_enum AS transaction_type,
+            t.payment_type_id,
+            t.classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null::bigint AS from_asset_owner_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,
+            t.classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null::bigint AS from_asset_owner_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,
+            t.classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null::bigint AS from_asset_owner_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,
+            t.classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null::bigint AS from_asset_owner_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,
+            t.classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null::bigint AS from_asset_owner_id
+        FROM slt_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,
+            t.classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE') AND 
t.settlement_date &lt; '${endDate}'
+                THEN t.owner_id
+            END AS asset_owner_id,
+            CASE
+                WHEN t.charged_off_on_date &lt;= t.transaction_date
+                THEN t.charge_off_reason_cv_id
+            END AS charge_off_reason_id,
+            null::bigint AS from_asset_owner_id
+        FROM slt_cap_income_amortization 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,
+            t.classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE') AND 
t.settlement_date &lt; '${endDate}'
+                THEN t.owner_id
+            END AS asset_owner_id,
+            CASE
+                WHEN t.charged_off_on_date &lt;= t.transaction_date
+                THEN t.charge_off_reason_cv_id
+            END AS charge_off_reason_id,
+            null::bigint AS from_asset_owner_id
+        FROM slt_cap_income_amortization 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,
+            t.classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE') AND 
t.settlement_date &lt; '${endDate}'
+                THEN t.owner_id
+            END AS asset_owner_id,
+            CASE
+                WHEN t.charged_off_on_date &lt;= t.transaction_date
+                THEN t.charge_off_reason_cv_id
+            END AS charge_off_reason_id,
+            null::bigint AS from_asset_owner_id
+        FROM slt_cap_income_amortization 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,
+            t.classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE') AND 
t.settlement_date &lt; '${endDate}'
+                THEN t.owner_id
+            END AS asset_owner_id,
+            CASE
+                WHEN t.charged_off_on_date &lt;= t.transaction_date
+                THEN t.charge_off_reason_cv_id
+            END AS charge_off_reason_id,
+            null::bigint AS from_asset_owner_id
+        FROM slt_cap_income_amortization 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,
+            t.classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE') AND 
t.settlement_date &lt; '${endDate}'
+                THEN t.owner_id
+            END AS asset_owner_id,
+            CASE
+                WHEN t.charged_off_on_date &lt;= t.transaction_date
+                THEN t.charge_off_reason_cv_id
+            END AS charge_off_reason_id,
+            null::bigint AS from_asset_owner_id
+        FROM slt_cap_income_amortization 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,
+            t.classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE') AND 
t.settlement_date &lt; '${endDate}'
+                THEN t.owner_id
+            END AS asset_owner_id,
+            CASE
+                WHEN t.charged_off_on_date &lt;= t.transaction_date
+                THEN t.charge_off_reason_cv_id
+            END AS charge_off_reason_id,
+            null::bigint AS from_asset_owner_id
+        FROM rlt_cap_income_amortization 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,
+            t.classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE') AND 
t.settlement_date &lt; '${endDate}'
+                THEN t.owner_id
+            END AS asset_owner_id,
+            CASE
+                WHEN t.charged_off_on_date &lt;= t.transaction_date
+                THEN t.charge_off_reason_cv_id
+            END AS charge_off_reason_id,
+            null::bigint AS from_asset_owner_id
+        FROM rlt_cap_income_amortization 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,
+            t.classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE') AND 
t.settlement_date &lt; '${endDate}'
+                THEN t.owner_id
+            END AS asset_owner_id,
+            CASE
+                WHEN t.charged_off_on_date &lt;= t.transaction_date
+                THEN t.charge_off_reason_cv_id
+            END AS charge_off_reason_id,
+            null::bigint AS from_asset_owner_id
+        FROM rlt_cap_income_amortization 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,
+            t.classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE') AND 
t.settlement_date &lt; '${endDate}'
+                THEN t.owner_id
+            END AS asset_owner_id,
+            CASE
+                WHEN t.charged_off_on_date &lt;= t.transaction_date
+                THEN t.charge_off_reason_cv_id
+            END AS charge_off_reason_id,
+            null::bigint AS from_asset_owner_id
+        FROM rlt_cap_income_amortization 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,
+            t.classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE') AND 
t.settlement_date &lt; '${endDate}'
+                THEN t.owner_id
+            END AS asset_owner_id,
+            CASE
+                WHEN t.charged_off_on_date &lt;= t.transaction_date
+                THEN t.charge_off_reason_cv_id
+            END AS charge_off_reason_id,
+            null::bigint AS from_asset_owner_id
+        FROM rlt_cap_income_amortization 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,
+            null AS classification_name,
+            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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null::bigint AS from_asset_owner_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,
+            null AS classification_name,
+            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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null::bigint AS from_asset_owner_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,
+            null AS classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null::bigint AS from_asset_owner_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 in (10, 34)
+            AND t.is_reversed = false
+            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,
+            null AS classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null::bigint AS from_asset_owner_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,
+            null AS classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null::bigint AS from_asset_owner_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,
+            null AS classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null::bigint AS from_asset_owner_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,
+            null AS classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null::bigint AS from_asset_owner_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,
+            null AS classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null::bigint AS from_asset_owner_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,
+            t.classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null::bigint AS from_asset_owner_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,
+            t.classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null::bigint AS from_asset_owner_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,
+            t.classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null::bigint AS from_asset_owner_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,
+            t.classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null::bigint AS from_asset_owner_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,
+            t.classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null::bigint AS from_asset_owner_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,
+            null AS classification_name,
+            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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null::bigint AS from_asset_owner_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,
+            null AS classification_name,
+            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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null::bigint AS from_asset_owner_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,
+            null AS classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null::bigint AS from_asset_owner_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,
+            t.name AS product,
+            t.transaction_type_enum AS transaction_type,
+            t.payment_type_id,
+            null AS classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null::bigint AS from_asset_owner_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,
+            null AS classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null::bigint AS from_asset_owner_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,
+            null AS classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null::bigint AS from_asset_owner_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,
+            null AS classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null::bigint AS from_asset_owner_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,
+            null AS classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null::bigint AS from_asset_owner_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,
+            null AS classification_name,
+            '' AS chargetype,
+            false AS reversal_indicator,
+            'Principal' AS Allocation_type,
+            t.principal_outstanding_derived AS amount,
+            t.owner_id 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,
+            t.previous_owner_id AS from_asset_owner_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,
+            null AS classification_name,
+            '' AS chargetype,
+            false AS reversal_indicator,
+            'Interest' AS Allocation_type,
+            t.interest_outstanding_derived AS amount,
+            t.owner_id 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,
+            t.previous_owner_id AS from_asset_owner_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,
+            null AS classification_name,
+            '' AS chargetype,
+            false AS reversal_indicator,
+            'Fees' AS Allocation_type,
+            t.fee_charges_outstanding_derived AS amount,
+            t.owner_id 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,
+            t.previous_owner_id AS from_asset_owner_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,
+            null AS classification_name,
+            '' AS chargetype,
+            false AS reversal_indicator,
+            'Penalty' AS Allocation_type,
+            t.penalty_charges_outstanding_derived AS amount,
+            t.owner_id 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,
+            t.previous_owner_id AS from_asset_owner_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,
+            null AS classification_name,
+            '' 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,
+            CASE
+                WHEN t.charged_off_on_date &lt;= t.settlement_date THEN 
t.charge_off_reason_cv_id
+            END AS charge_off_reason_id,
+            t.previous_owner_id AS from_asset_owner_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,
+            null AS classification_name,
+            '' 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,
+            t.owner_id AS from_asset_owner_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,
+            null AS classification_name,
+            '' 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,
+            t.owner_id AS from_asset_owner_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,
+            null AS classification_name,
+            '' 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,
+            t.owner_id AS from_asset_owner_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,
+            null AS classification_name,
+            '' 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,
+            t.owner_id AS from_asset_owner_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,
+            null AS classification_name,
+            '' 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,
+            t.owner_id AS from_asset_owner_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.classification_name,
+    a.chargetype,
+    a.reversal_indicator,
+    a.Allocation_Type,
+    a.asset_owner_id,
+    a.charge_off_reason_id,
+    a.from_asset_owner_id
+ORDER BY 1,2,3,4,5,6,7,8,9,10,11"/>
+            <where>report_name='Transaction Summary Report with Asset 
Owner'</where>
+        </update>
+    </changeSet>
+    <changeSet author="fineract" id="1-mysql" context="mysql">
+        <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,
+        CASE
+            WHEN d.payment_type_id IS NULL AND t.classification_cv_id IS NOT 
NULL THEN
+                (SELECT code_value FROM m_code_value WHERE id = 
t.classification_cv_id)
+            ELSE NULL
+        END AS classification_name,
+        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, 32, 34, 36, 39)
+        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,
+        CASE
+            WHEN d.payment_type_id IS NULL AND t.classification_cv_id IS NOT 
NULL THEN
+                (SELECT code_value FROM m_code_value WHERE id = 
t.classification_cv_id)
+            ELSE NULL
+        END AS classification_name,
+        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, 32, 34, 36, 39)
+        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})
+
+),
+
+slt_cap_income_amortization AS (
+    SELECT '${endDate}' AS transactiondate,
+        t.id,
+        l.name,
+        t.transaction_type_enum,
+        d.payment_type_id,
+        CASE
+            WHEN d.payment_type_id IS NULL AND bt.classification_cv_id IS NOT 
NULL
+                THEN
+                    (SELECT code_value FROM m_code_value WHERE id = 
bt.classification_cv_id)
+            ELSE NULL
+            END AS classification_name,
+        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
+        JOIN m_loan_amortization_allocation_mapping map
+            ON map.amortization_loan_transaction_id = t.id
+        JOIN m_loan_transaction bt ON bt.id = map.base_loan_transaction_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.is_reversed = false
+        AND t.transaction_type_enum IN (36, 39)
+        AND (t.office_id = ${officeId})
+),
+rlt_cap_income_amortization AS (
+    SELECT '${endDate}' AS transactiondate,
+        t.id,
+        l.name,
+        t.transaction_type_enum,
+        d.payment_type_id,
+        CASE
+            WHEN d.payment_type_id IS NULL AND bt.classification_cv_id IS NOT 
NULL
+                THEN
+                    (SELECT code_value FROM m_code_value WHERE id = 
bt.classification_cv_id)
+            ELSE NULL
+            END AS classification_name,
+        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
+        JOIN m_loan_amortization_allocation_mapping map
+            ON map.amortization_loan_transaction_id = t.id
+        JOIN m_loan_transaction bt ON bt.id = map.base_loan_transaction_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.is_reversed = true
+        AND t.transaction_type_enum IN (36, 39)
+        AND (t.office_id = ${officeId})
+),
+active_external_asset_owner_transfers AS (
+    SELECT '${endDate}' AS transactiondate,
+        t.id,
+        p.name,
+        t.owner_id,
+        t.previous_owner_id,
+        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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+        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,
+        t.owner_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 in ('BUYBACK', 'BUYBACK_INTERMEDIATE')
+        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,
+    COALESCE(
+               (SELECT value FROM m_payment_type WHERE id = a.payment_type_id),
+               a.classification_name
+       ) 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, 31, 33, 34, 37, 39)
+        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, 31, 33, 34, 37, 39)
+        AND a.reversal_indicator = true THEN sum(a.amount) * + 1
+        WHEN a.transaction_type IN (1, 10, 25, 20, 35, 36)
+        AND a.reversal_indicator = false THEN sum(a.amount) * + 1
+        WHEN a.transaction_type IN (1, 10, 25, 20, 35, 36)
+        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,
+    (SELECT external_id FROM m_external_asset_owner WHERE id = 
a.from_asset_owner_id) AS From_asset_owner_id
+FROM (
+        SELECT t.transactiondate,
+            t.id,
+            t.name AS product,
+            t.transaction_type_enum AS transaction_type,
+            t.payment_type_id,
+            t.classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null AS from_asset_owner_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,
+            t.classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null AS from_asset_owner_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,
+            t.classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null AS from_asset_owner_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,
+            t.classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null AS from_asset_owner_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,
+            t.classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null AS from_asset_owner_id
+        FROM slt_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,
+            t.classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE') AND 
t.settlement_date &lt; '${endDate}'
+                THEN t.owner_id
+            END AS asset_owner_id,
+            CASE
+                WHEN t.charged_off_on_date &lt;= t.transaction_date
+                THEN t.charge_off_reason_cv_id
+            END AS charge_off_reason_id,
+            null AS from_asset_owner_id
+        FROM slt_cap_income_amortization 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,
+            t.classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE') AND 
t.settlement_date &lt; '${endDate}'
+                THEN t.owner_id
+            END AS asset_owner_id,
+            CASE
+                WHEN t.charged_off_on_date &lt;= t.transaction_date
+                THEN t.charge_off_reason_cv_id
+            END AS charge_off_reason_id,
+            null AS from_asset_owner_id
+        FROM slt_cap_income_amortization 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,
+            t.classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE') AND 
t.settlement_date &lt; '${endDate}'
+                THEN t.owner_id
+            END AS asset_owner_id,
+            CASE
+                WHEN t.charged_off_on_date &lt;= t.transaction_date
+                THEN t.charge_off_reason_cv_id
+            END AS charge_off_reason_id,
+            null AS from_asset_owner_id
+        FROM slt_cap_income_amortization 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,
+            t.classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE') AND 
t.settlement_date &lt; '${endDate}'
+                THEN t.owner_id
+            END AS asset_owner_id,
+            CASE
+                WHEN t.charged_off_on_date &lt;= t.transaction_date
+                THEN t.charge_off_reason_cv_id
+            END AS charge_off_reason_id,
+            null AS from_asset_owner_id
+        FROM slt_cap_income_amortization 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,
+            t.classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE') AND 
t.settlement_date &lt; '${endDate}'
+                THEN t.owner_id
+            END AS asset_owner_id,
+            CASE
+                WHEN t.charged_off_on_date &lt;= t.transaction_date
+                THEN t.charge_off_reason_cv_id
+            END AS charge_off_reason_id,
+            null AS from_asset_owner_id
+        FROM slt_cap_income_amortization 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,
+            t.classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE') AND 
t.settlement_date &lt; '${endDate}'
+                THEN t.owner_id
+            END AS asset_owner_id,
+            CASE
+                WHEN t.charged_off_on_date &lt;= t.transaction_date
+                THEN t.charge_off_reason_cv_id
+            END AS charge_off_reason_id,
+            null AS from_asset_owner_id
+        FROM rlt_cap_income_amortization 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,
+            t.classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE') AND 
t.settlement_date &lt; '${endDate}'
+                THEN t.owner_id
+            END AS asset_owner_id,
+            CASE
+                WHEN t.charged_off_on_date &lt;= t.transaction_date
+                THEN t.charge_off_reason_cv_id
+            END AS charge_off_reason_id,
+            null AS from_asset_owner_id
+        FROM rlt_cap_income_amortization 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,
+            t.classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE') AND 
t.settlement_date &lt; '${endDate}'
+                THEN t.owner_id
+            END AS asset_owner_id,
+            CASE
+                WHEN t.charged_off_on_date &lt;= t.transaction_date
+                THEN t.charge_off_reason_cv_id
+            END AS charge_off_reason_id,
+            null AS from_asset_owner_id
+        FROM rlt_cap_income_amortization 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,
+            t.classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE') AND 
t.settlement_date &lt; '${endDate}'
+                THEN t.owner_id
+            END AS asset_owner_id,
+            CASE
+                WHEN t.charged_off_on_date &lt;= t.transaction_date
+                THEN t.charge_off_reason_cv_id
+            END AS charge_off_reason_id,
+            null AS from_asset_owner_id
+        FROM rlt_cap_income_amortization 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,
+            t.classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE') AND 
t.settlement_date &lt; '${endDate}'
+                THEN t.owner_id
+            END AS asset_owner_id,
+            CASE
+                WHEN t.charged_off_on_date &lt;= t.transaction_date
+                THEN t.charge_off_reason_cv_id
+            END AS charge_off_reason_id,
+            null AS from_asset_owner_id
+        FROM rlt_cap_income_amortization 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,
+            null AS classification_name,
+            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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null AS from_asset_owner_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,
+            null AS classification_name,
+            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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null AS from_asset_owner_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,
+            null AS classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null AS from_asset_owner_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 in (10, 34)
+            AND t.is_reversed = false
+            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,
+            null AS classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null AS from_asset_owner_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,
+            null AS classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null AS from_asset_owner_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,
+            null AS classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null AS from_asset_owner_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,
+            null AS classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null AS from_asset_owner_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,
+            null AS classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null AS from_asset_owner_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,
+            t.classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null AS from_asset_owner_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,
+            t.classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null AS from_asset_owner_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,
+            t.classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null AS from_asset_owner_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,
+            t.classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null AS from_asset_owner_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,
+            t.classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null AS from_asset_owner_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,
+            null AS classification_name,
+            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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null AS from_asset_owner_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,
+            null AS classification_name,
+            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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null AS from_asset_owner_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,
+            null AS classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null AS from_asset_owner_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,
+            t.name AS product,
+            t.transaction_type_enum AS transaction_type,
+            t.payment_type_id,
+            null AS classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null AS from_asset_owner_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,
+            null AS classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null AS from_asset_owner_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,
+            null AS classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null AS from_asset_owner_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,
+            null AS classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null AS from_asset_owner_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,
+            null AS classification_name,
+            '' 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 in ('ACTIVE', 'ACTIVE_INTERMEDIATE')
+                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,
+            null AS from_asset_owner_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,
+            null AS classification_name,
+            '' AS chargetype,
+            false AS reversal_indicator,
+            'Principal' AS Allocation_type,
+            t.principal_outstanding_derived AS amount,
+            t.owner_id 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,
+            t.previous_owner_id AS from_asset_owner_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,
+            null AS classification_name,
+            '' AS chargetype,
+            false AS reversal_indicator,
+            'Interest' AS Allocation_type,
+            t.interest_outstanding_derived AS amount,
+            t.owner_id 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,
+            t.previous_owner_id AS from_asset_owner_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,
+            null AS classification_name,
+            '' AS chargetype,
+            false AS reversal_indicator,
+            'Fees' AS Allocation_type,
+            t.fee_charges_outstanding_derived AS amount,
+            t.owner_id 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,
+            t.previous_owner_id AS from_asset_owner_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,
+            null AS classification_name,
+            '' AS chargetype,
+            false AS reversal_indicator,
+            'Penalty' AS Allocation_type,
+            t.penalty_charges_outstanding_derived AS amount,
+            t.owner_id 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,
+            t.previous_owner_id AS from_asset_owner_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,
+            null AS classification_name,
+            '' 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,
+            CASE
+                WHEN t.charged_off_on_date &lt;= t.settlement_date THEN 
t.charge_off_reason_cv_id
+            END AS charge_off_reason_id,
+            t.previous_owner_id AS from_asset_owner_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,
+            null AS classification_name,
+            '' 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,
+            t.owner_id AS from_asset_owner_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,
+            null AS classification_name,
+            '' 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,
+            t.owner_id AS from_asset_owner_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,
+            null AS classification_name,
+            '' 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,
+            t.owner_id AS from_asset_owner_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,
+            null AS classification_name,
+            '' 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,
+            t.owner_id AS from_asset_owner_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,
+            null AS classification_name,
+            '' 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,
+            t.owner_id AS from_asset_owner_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.classification_name,
+    a.chargetype,
+    a.reversal_indicator,
+    a.Allocation_Type,
+    a.asset_owner_id,
+    a.charge_off_reason_id,
+    a.from_asset_owner_id
+ORDER BY 1,2,3,4,5,6,7,8,9,10,11"/>
+            <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 54a000985e..7262613a93 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
@@ -53,6 +53,7 @@ import java.util.Objects;
 import java.util.Optional;
 import java.util.UUID;
 import lombok.RequiredArgsConstructor;
+import okhttp3.ResponseBody;
 import org.apache.fineract.accounting.common.AccountingConstants;
 import org.apache.fineract.accounting.journalentry.domain.JournalEntryType;
 import org.apache.fineract.client.models.ExternalAssetOwnerRequest;
@@ -105,6 +106,7 @@ import org.junit.jupiter.api.BeforeAll;
 import org.junit.jupiter.api.Test;
 import org.junit.jupiter.api.extension.ExtendWith;
 import org.springframework.lang.NonNull;
+import retrofit2.Response;
 
 @SuppressWarnings("rawtypes")
 @ExtendWith({ ExternalEventsExtension.class })
@@ -1080,8 +1082,14 @@ public class InitiateExternalAssetOwnerTransferTest 
extends BaseLoanIntegrationT
             
assertNotNull(jsonPath.getString("columnHeaders[9].columnDisplayType"));
             
assertFalse(jsonPath.getBoolean("columnHeaders[9].isColumnNullable"));
 
+            assertNotNull(jsonPath.getString("columnHeaders[10].columnType"));
+            
assertNotNull(jsonPath.getString("columnHeaders[10].columnDisplayType"));
+            
assertFalse(jsonPath.getBoolean("columnHeaders[10].isColumnNullable"));
+
             assertNotNull(retrieveResponse.getContent().get(0).getOwner());
             final var ownerId = 
retrieveResponse.getContent().get(0).getOwner().getExternalId();
+            
assertNotNull(retrieveResponse.getContent().get(2).getPreviousOwner());
+            final var previousOwnerId = 
retrieveResponse.getContent().get(2).getPreviousOwner().getExternalId();
 
             assertEquals("2020-03-03", jsonPath.getString("data[0].row[0]"));
             
assertTrue(jsonPath.getString("data[0].row[1]").matches("^LOAN_PRODUCT_.{6}$"));
@@ -1093,6 +1101,7 @@ public class InitiateExternalAssetOwnerTransferTest 
extends BaseLoanIntegrationT
             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]"));
+            assertNull(jsonPath.getString("data[0].row[10]"));
 
             assertEquals("2020-03-03", jsonPath.getString("data[1].row[0]"));
             
assertTrue(jsonPath.getString("data[1].row[1]").matches("^LOAN_PRODUCT_.{6}$"));
@@ -1104,6 +1113,7 @@ public class InitiateExternalAssetOwnerTransferTest 
extends BaseLoanIntegrationT
             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(previousOwnerId, 
jsonPath.getString("data[1].row[10]"));
 
             assertEquals("2020-03-03", jsonPath.getString("data[2].row[0]"));
             
assertTrue(jsonPath.getString("data[2].row[1]").matches("^LOAN_PRODUCT_.{6}$"));
@@ -1115,6 +1125,7 @@ public class InitiateExternalAssetOwnerTransferTest 
extends BaseLoanIntegrationT
             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(previousOwnerId, 
jsonPath.getString("data[2].row[10]"));
 
             assertEquals("2020-03-03", jsonPath.getString("data[3].row[0]"));
             
assertTrue(jsonPath.getString("data[3].row[1]").matches("^LOAN_PRODUCT_.{6}$"));
@@ -1126,6 +1137,7 @@ public class InitiateExternalAssetOwnerTransferTest 
extends BaseLoanIntegrationT
             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(previousOwnerId, 
jsonPath.getString("data[3].row[10]"));
 
             assertEquals("2020-03-03", jsonPath.getString("data[4].row[0]"));
             
assertTrue(jsonPath.getString("data[4].row[1]").matches("^LOAN_PRODUCT_.{6}$"));
@@ -1137,6 +1149,7 @@ public class InitiateExternalAssetOwnerTransferTest 
extends BaseLoanIntegrationT
             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]"));
+            assertNull(jsonPath.getString("data[4].row[10]"));
 
             assertEquals("2020-03-03", jsonPath.getString("data[5].row[0]"));
             
assertTrue(jsonPath.getString("data[5].row[1]").matches("^LOAN_PRODUCT_.{6}$"));
@@ -1148,6 +1161,7 @@ public class InitiateExternalAssetOwnerTransferTest 
extends BaseLoanIntegrationT
             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]"));
+            assertNull(jsonPath.getString("data[5].row[10]"));
 
             assertEquals("2020-03-03", jsonPath.getString("data[6].row[0]"));
             
assertTrue(jsonPath.getString("data[6].row[1]").matches("^LOAN_PRODUCT_.{6}$"));
@@ -1159,6 +1173,7 @@ public class InitiateExternalAssetOwnerTransferTest 
extends BaseLoanIntegrationT
             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]"));
+            assertNull(jsonPath.getString("data[6].row[10]"));
 
             assertEquals("2020-03-03", jsonPath.getString("data[7].row[0]"));
             
assertTrue(jsonPath.getString("data[7].row[1]").matches("^LOAN_PRODUCT_.{6}$"));
@@ -1170,6 +1185,7 @@ public class InitiateExternalAssetOwnerTransferTest 
extends BaseLoanIntegrationT
             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]"));
+            assertNull(jsonPath.getString("data[7].row[10]"));
 
             assertEquals("2020-03-03", jsonPath.getString("data[8].row[0]"));
             
assertTrue(jsonPath.getString("data[8].row[1]").matches("^LOAN_PRODUCT_.{6}$"));
@@ -1181,6 +1197,101 @@ public class InitiateExternalAssetOwnerTransferTest 
extends BaseLoanIntegrationT
             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]"));
+            assertNull(jsonPath.getString("data[8].row[10]"));
+        } finally {
+            ExternalEventHelper.deleteAllExternalEvents(REQUEST_SPEC, new 
ResponseSpecBuilder().expectStatusCode(Matchers.is(204)).build());
+            cleanUpAndRestoreBusinessDate();
+        }
+    }
+
+    @Test
+    public void 
transactionSummaryReportWithAssetOwner_CheckFromAssetOwnerIdForBuyback() throws 
IOException {
+        try {
+            
globalConfigurationHelper.manageConfigurations(GlobalConfigurationConstants.ENABLE_AUTO_GENERATED_EXTERNAL_ID,
 true);
+            setInitialBusinessDate("2023-08-16");
+
+            ExternalEventHelper.deleteAllExternalEvents(REQUEST_SPEC, new 
ResponseSpecBuilder().expectStatusCode(Matchers.is(204)).build());
+            ExternalEventHelper.changeEventState(REQUEST_SPEC, RESPONSE_SPEC, 
"LoanOwnershipTransferBusinessEvent", true);
+
+            final Integer officeId = OFFICE_HELPER.createOffice("1 January 
2020");
+            final Integer clientID = ClientHelper.createClient(REQUEST_SPEC, 
RESPONSE_SPEC, "1 January 2020", officeId.toString());
+            final Integer loanID = createLoanForClient(clientID);
+
+            // Create first sale transfer
+            final PostInitiateTransferResponse firstSaleTransferResponse = 
createSaleTransfer(loanID, "2023-08-16");
+            validateResponse(firstSaleTransferResponse, loanID);
+
+            // Verify the transfer is PENDING initially
+            getAndValidateExternalAssetOwnerTransferByLoan(loanID,
+                    ExpectedExternalTransferData.expected(PENDING, 
firstSaleTransferResponse.getResourceExternalId(), "2023-08-16",
+                            "2023-08-16", "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")));
+
+            // Execute COB job on the next day to activate the transfer
+            updateBusinessDateAndExecuteCOBJob("2023-08-17");
+
+            // Verify the transfer is ACTIVE after COB job
+            getAndValidateExternalAssetOwnerTransferByLoan(loanID,
+                    ExpectedExternalTransferData.expected(PENDING, 
firstSaleTransferResponse.getResourceExternalId(), "2023-08-16",
+                            "2023-08-16", "2023-08-16", 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, 
firstSaleTransferResponse.getResourceExternalId(), "2023-08-16",
+                            "2023-08-17", "9999-12-31", true, new 
BigDecimal("15914.980000"), new BigDecimal("15000.000000"),
+                            new BigDecimal("757.420000"), new 
BigDecimal("157.560000"), new BigDecimal("0.000000"),
+                            new BigDecimal("0.000000")));
+
+            // Get the owner ID of the first transfer for later verification
+            PageExternalTransferData retrieveResponse = 
EXTERNAL_ASSET_OWNER_HELPER.retrieveTransfersByLoanId(loanID.longValue());
+            assertNotNull(retrieveResponse.getContent().get(1).getOwner());
+            final String firstOwnerId = 
retrieveResponse.getContent().get(1).getOwner().getExternalId();
+            
assertNull(retrieveResponse.getContent().get(1).getPreviousOwner(), "First sale 
transfer should not have previous_owner_id");
+
+            // Create buyback transfer
+            updateBusinessDateAndExecuteCOBJob("2023-08-18");
+            final PostInitiateTransferResponse buybackTransferResponse = 
createBuybackTransfer(loanID, "2023-08-18");
+            validateResponse(buybackTransferResponse, loanID);
+
+            // Execute COB job to process buyback
+            updateBusinessDateAndExecuteCOBJob("2023-08-19");
+
+            // Verify buyback has previous_owner_id set
+            retrieveResponse = 
EXTERNAL_ASSET_OWNER_HELPER.retrieveTransfersByLoanId(loanID.longValue());
+            
assertNotNull(retrieveResponse.getContent().get(2).getPreviousOwner());
+            assertEquals(firstOwnerId, 
retrieveResponse.getContent().get(2).getPreviousOwner().getExternalId(),
+                    "Buyback transfer should have previous_owner_id set to 
first owner");
+
+            // Run report on settlement date of buyback to check that 
from_asset_owner_id is populated
+            final Response<ResponseBody> reportResult = 
reportHelper.runReport("Transaction Summary Report with Asset Owner",
+                    Map.of("R_endDate", "2023-08-18", "R_officeId", 
officeId.toString(), "output-type", "CSV"));
+
+            assertNotNull(reportResult.body());
+            final String csvContent = reportResult.body().string();
+            final JsonPath jsonPath = JsonPath.from(csvContent);
+
+            // Find Asset Buyback entries to verify from_asset_owner_id is 
populated
+            final List<Map<String, Object>> buybackRows = new ArrayList<>();
+            final int dataSize = jsonPath.getInt("data.size()");
+            for (int i = 0; i < dataSize; i++) {
+                final String transactionType = jsonPath.getString("data[" + i 
+ "].row[2]");
+                final String transactionDate = jsonPath.getString("data[" + i 
+ "].row[0]");
+                // Find Asset Buyback entries for the buyback date
+                if ("Asset Buyback".equals(transactionType) && 
"2023-08-18".equals(transactionDate)) {
+                    buybackRows.add(jsonPath.getMap("data[" + i + "]"));
+                }
+            }
+
+            // Verify that Asset Buyback entries exist
+            assertFalse(buybackRows.isEmpty(), "Asset Buyback entries should 
exist in the report");
+
+            // Verify that from_asset_owner_id is populated with 
previous_owner_id for buyback
+            for (Map<String, Object> row : buybackRows) {
+                final List<Object> rowData = (List<Object>) row.get("row");
+                assertNotNull(rowData.get(10), "from_asset_owner_id should be 
populated for buyback transfer");
+                assertEquals(firstOwnerId, rowData.get(10),
+                        "from_asset_owner_id should equal the first owner's 
external ID for buyback transfer");
+            }
         } finally {
             ExternalEventHelper.deleteAllExternalEvents(REQUEST_SPEC, new 
ResponseSpecBuilder().expectStatusCode(Matchers.is(204)).build());
             cleanUpAndRestoreBusinessDate();