abraham-menyhart commented on code in PR #3380: URL: https://github.com/apache/fineract/pull/3380#discussion_r1298461212
########## fineract-provider/src/main/resources/db/changelog/tenant/parts/0123_transaction_summary_with_asset_owner_report_typo_fix_3.xml: ########## @@ -0,0 +1,32 @@ +<?xml version="1.0" encoding="UTF-8"?> +<!-- + + Licensed to the Apache Software Foundation (ASF) under one + or more contributor license agreements. See the NOTICE file + distributed with this work for additional information + regarding copyright ownership. The ASF licenses this file + to you under the Apache License, Version 2.0 (the + "License"); you may not use this file except in compliance + with the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, + software distributed under the License is distributed on an + "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + KIND, either express or implied. See the License for the + specific language governing permissions and limitations + under the License. + +--> +<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"; + xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"; + xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.3.xsd";> + <changeSet author="fineract" id="1"> + <update tableName="stretchy_report"> + <column name="report_sql" + value="SELECT '${endDate}' AS TransactionDate, a.product AS Product, case when a.transaction_type = 9999 then 'Asset Transfer' when a.transaction_type = 99999 then 'Asset Buyback' else(SELECT enum_message_property FROM r_enum_value WHERE enum_name = 'transaction_type_enum' and enum_id = a.transaction_type) end as TransactionType_Name, (select value from m_payment_type where id = a.payment_type_id) as PaymentType_Name, a.chargetype as chargetype, a.reversal_indicator AS Reversed, a.Allocation_Type AS Allocation_Type, '' 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) 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)AND a.reversal_indicator = true then sum(a.amount) * + 1 when a.transaction_type IN (1, 10, 25, 20) AND a.reversa l_indicator = false then sum(a.amount) * + 1 when a.transaction_type IN (1, 10, 25, 20) AND a.reversal_indicator = true then sum(a.amount) * -1 end AS Transaction_Amount, (select external_id from m_external_asset_owner where id =a.asset_owner_id) AS Asset_owner_id FROM (SELECT '${endDate}' AS transactiondate, t.id, l.name AS product, t.transaction_type_enum AS transaction_type, d.payment_type_id, '' as chargetype, false AS reversal_indicator, 'Principal' AS Allocation_Type, CASE when t.transaction_type_enum in (1) then (case when t.amount is null then 0 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 e.status = 'ACTIVE' and e.settlement_date < '${endDate}' then e.owner_id end as 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_payment_detail d on d.id = t.payment_detail_id left join m_external_asset_owner_tran sfer e ON e.loan_id = t.loan_id and e.settlement_date < '${endDate}' and e.effective_date_to >= '${endDate}' WHERE t.submitted_on_date = '${endDate}' and t.transaction_type_enum not in (10, 26) and (t.office_id = ${officeId}) UNION ALL SELECT '${endDate}' AS transactiondate, t.id, l.name AS product, t.transaction_type_enum AS transaction_type, d.payment_type_id, '' as chargetype, false AS reversal_indicator, 'Interest' AS Allocation_Type, case when t.interest_portion_derived is null then 0 else t.interest_portion_derived end AS amount, case when e.status = 'ACTIVE' and e.settlement_date < '${endDate}' then e.owner_id end as 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_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 < '${endDate}' and e.effective_date_to >= '${endDate}' WHERE t.submitted_on_date = '${endDate}' and t.transaction_type_enum not in (10, 26) and (t.office_id = ${officeId}) UNION ALL SELECT '${endDate}' AS transactiondate, t.id, l.name AS product, t.transaction_type_enum AS transaction_type, d.payment_type_id, '' 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 e.status = 'ACTIVE' and e.settlement_date < '${endDate}' then e.owner_id end as 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_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 < '${endDate}' and e.effective_date_to >= '${endDate}' WHERE t.submitted_on_date = '${endDate}' and t.transaction_type_enum not in (10, 26) and (t.office_id = ${officeId}) UNION ALL SELECT '${endDate}' AS transactionda te, t.id, l.name AS product, t.transaction_type_enum AS transaction_type, d.payment_type_id, '' as chargetype, false AS reversal_indicator, 'Penalty' AS Allocation_Type, case when t.penalty_charges_portion_derived is null then 0 else t.penalty_charges_portion_derived end as amount, case when e.status = 'ACTIVE' and e.settlement_date < '${endDate}' then e.owner_id end as 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_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 < '${endDate}' and e.effective_date_to >= '${endDate}' WHERE t.submitted_on_date = '${endDate}' and t.transaction_type_enum not in (10, 26) and (t.office_id = ${officeId}) UNION ALL SELECT '${endDate}' AS transactiondate, t.id, l.name AS product, t.transaction_type_enum AS transaction_type, d.payment_type_id, '' as chargetype, false AS revers al_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 e.status = 'ACTIVE' and e.settlement_date < '${endDate}' then e.owner_id end as 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_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 < '${endDate}' and e.effective_date_to >= '${endDate}' WHERE t.submitted_on_date = '${endDate}' and t.transaction_type_enum not in (10, 26) and (t.office_id = ${officeId}) UNION ALL SELECT '${endDate}' AS transactiondate, t.id, l.name AS product, t.transaction_type_enum AS transaction_type, null, mc.name, false AS reversal_indicator, 'Fees' AS Allocation_Type, case when pd.amount is null then 0 else pd.amount end as amount, case when e.status = 'ACTIVE' and e.settlement_date < '${endDate}' then e.owner_id end as 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 < '${endDate}' and e.effective_date_to >= '${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, mc.name, false AS reversal_indicator, 'Penalty' AS Allocation_Type, case when pd.amount is null then 0 else pd.amount end as amount, case when e.status = 'ACTIVE' and e.settlement_date < '${endDate}' then e.owner_id end as asse t_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 < '${endDate}' and e.effective_date_to >= '${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 chargetype, false AS reversal_indicator, 'Interest' AS Allocation_Type, case when t.interest_portion_derived is null then 0 else t.interest_portion_derived end AS amount, case when e.status = 'ACTIVE' and e.settlement_date < '${endDate}' then e.owner_id end as asset_owner_id FROM m_lo an_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 < '${endDate}' and e.effective_date_to >= '${endDate}' WHERE t.submitted_on_date = '${endDate}' and t.transaction_type_enum = 10 and t.is_reversed = false and (t.office_id = ${officeId}) UNION ALL SELECT '${endDate}' AS transactiondate, t.id, l.name AS product, t.transaction_type_enum AS transaction_type, d.payment_type_id, mc.name, false AS reversal_indicator, 'Fees' AS Allocation_Type, case when pd.amount is null then 0 else pd.amount end as amount, case when e.status = 'ACTIVE' and e.settlement_date < '${endDate}' then e.owner_id end as 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_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 < '${endDate}' and e.effective_date_to >= '${endDate}' WHERE t.submitted_on_date = '${endDate}' and t.transaction_type_enum = 26 and (t.office_id = ${officeId}) UNION ALL SELECT '${endDate}' AS transactiondate, t.id, l.name AS product, t.transaction_type_enum AS transaction_type, d.payment_type_id, mc.name, false AS reversal_indicator, 'Penalty' AS Allocation_Type, case when pd.amount is null then 0 else pd.amount end AS amount, case when e.status = 'ACTIVE' and e.settlement_date < '${endDate}' then e.owner_id end as 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_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 < '${endDate}' and e.effective_date_to >= '${endDate}' WHERE t.submitted_on_date = '${endDate}' and t.transaction_type_enum = 26 and (t.office_id = ${officeId}) UNION ALL SELECT '${endDate}' AS transactiondate, t.id, l.name AS product, t.transaction_type_enum AS transaction_type, d.payment_type_id, '' as chargetype, false AS reversal_indicator, 'Interest' AS Allocation_Type, case when t.interest_portion_derived is null then 0 else t.interest_portion_derived end AS amount, case when e.status = 'ACTIVE' and e.settlement_date < '${endDate}' then e.owner_id end as 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_payment_detail d on d.id = t.payment_detail_id left join m_external_asset_owner_transfer e ON e.loan_id = t.loan_i d and e.settlement_date < '${endDate}' and e.effective_date_to >= '${endDate}' WHERE t.submitted_on_date = '${endDate}' and t.transaction_type_enum = 26 and (t.office_id = ${officeId}) UNION ALL SELECT '${endDate}' AS transactiondate, t.id, l.name AS product, t.transaction_type_enum AS transaction_type, d.payment_type_id, '' 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 e.status = 'ACTIVE' and e.settlement_date < '${endDate}' then e.owner_id end as 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_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 < '${endDate}' and e.effective_date_to >= '${endDate}' WHERE t.submitted_on_date = '${endDate}' and t.transaction_type _enum = 26 and (t.office_id = ${officeId}) UNION ALL SELECT '${endDate}' AS transactiondate, t.id, l.name AS product, t.transaction_type_enum AS transaction_type, d.payment_type_id, '' 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 e.status = 'ACTIVE' and e.settlement_date < '${endDate}' then e.owner_id end as 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_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 < '${endDate}' and e.effective_date_to >= '${endDate}' WHERE t.submitted_on_date = '${endDate}' and t.transaction_type_enum = 26 and (t.office_id = ${officeId}) UNION ALL SELECT '${endDate}' AS transactiondate, t.id, l.name AS product, t.transacti on_type_enum AS transaction_type, d.payment_type_id, '' as chargetype, true AS reversal_indicator, 'Principal' AS Allocation_Type, CASE when t.transaction_type_enum in (1) then (case when t.amount is null then 0 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 e.status = 'ACTIVE' and e.settlement_date < '${endDate}' then e.owner_id end as 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_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 < '${endDate}' and e.effective_date_to >= '${endDate}' WHERE t.reversed_on_date = '${endDate}' and t.transaction_type_enum not in (10, 26) and (t.office_id = ${officeId}) UNION ALL SELECT '${endDate}' AS transactiondate, t.id, l.name AS product, t.transaction_type_enum AS transaction_typ e, d.payment_type_id, '' as chargetype, true AS reversal_indicator, 'Interest' AS Allocation_Type, case when t.interest_portion_derived is null then 0 else t.interest_portion_derived end AS amount, case when e.status = 'ACTIVE' and e.settlement_date < '${endDate}' then e.owner_id end as 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_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 < '${endDate}' and e.effective_date_to >= '${endDate}' WHERE t.reversed_on_date = '${endDate}' and t.transaction_type_enum not in (10, 26) and (t.office_id = ${officeId}) UNION ALL SELECT '${endDate}' AS transactiondate, t.id, l.name AS product, t.transaction_type_enum AS transaction_type, d.payment_type_id, '' 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 e.status = 'ACTIVE' and e.settlement_date < '${endDate}' then e.owner_id end as 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_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 < '${endDate}' and e.effective_date_to >= '${endDate}' WHERE t.reversed_on_date = '${endDate}' and t.transaction_type_enum not in (10, 26) and (t.office_id = ${officeId}) UNION ALL SELECT '${endDate}' AS transactiondate, t.id, l.name AS product, t.transaction_type_enum AS transaction_type, d.payment_type_id, '' 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 e.status = 'ACTIVE' and e.settlement_date < '${endDate}' t hen e.owner_id end as 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_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 < '${endDate}' and e.effective_date_to >= '${endDate}' WHERE t.reversed_on_date = '${endDate}' and t.transaction_type_enum not in (10, 26) and (t.office_id = ${officeId}) UNION ALL SELECT '${endDate}' AS transactiondate, t.id, l.name AS product, t.transaction_type_enum AS transaction_type, d.payment_type_id, '' 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 e.status = 'ACTIVE' and e.settlement_date < '${endDate}' then e.owner_id end as asset_owner_id FROM m_loan_transaction t JOIN m_loan m ON m.id = t.loan_id JOIN m_product_loan l O N 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 < '${endDate}' and e.effective_date_to >= '${endDate}' WHERE t.reversed_on_date = '${endDate}' and t.transaction_type_enum not in (10, 26) and (t.office_id = ${officeId}) UNION ALL SELECT '${endDate}' AS transactiondate, t.id, l.name AS product, t.transaction_type_enum AS transaction_type, null, mc.name, true AS reversal_indicator, 'Fees' AS Allocation_Type, case when pd.amount is null then 0 else pd.amount end as amount, case when e.status = 'ACTIVE' and e.settlement_date < '${endDate}' then e.owner_id end as 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 le ft join m_external_asset_owner_transfer e ON e.loan_id = t.loan_id and e.settlement_date < '${endDate}' and e.effective_date_to >= '${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, mc.name, true AS reversal_indicator, 'Penalty' AS Allocation_Type, case when pd.amount is null then 0 else pd.amount end as amount, case when e.status = 'ACTIVE' and e.settlement_date < '${endDate}' then e.owner_id end as 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 < '${endDate}' and e.effective_date_to >= '${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 chargetype, true AS reversal_indicator, 'Interest' AS Allocation_Type, case when t.interest_portion_derived is null then 0 else t.interest_portion_derived end AS amount, case when e.status = 'ACTIVE' and e.settlement_date < '${endDate}' then e.owner_id end as 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 < '${endDate}' and e.effective_date_to >= '${endDate}' WHERE t.reversed_on_date = '${endDate}' and t.transaction_type_enum = 10 and t.is_reversed = true and (t.office_id = ${officeId}) UNION ALL SELECT '${endDate}' AS transactiondate, t.id, l.name AS product, t.transaction_type_enum AS transaction_type, d.payment_type_id, mc.name, true AS reversal_indicator, 'Fees' AS Allocation_Type, case when pd.amount is null then 0 else pd.amount end as amount, case when e.status = 'ACTIVE' and e.settlement_date < '${endDate}' then e.owner_id end as 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_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 < '${endDate}' and e.effective_date_to >= '${endDate}' WHERE t.reversed_on_date = '${endDate}' and t.transaction_type_enum = 26 and (t.office_id = ${officeId}) UNION ALL SELECT '${endDate}' AS transactiondate, t.id, l.name AS product, t.transaction_type_enum AS transaction_type, d.payment_type_id, mc.name, true AS reversal_indicator, 'Penalty' AS Allocation_Type, case when pd.amount is null then 0 else pd.amount end AS amount, case when e.status = 'ACTIVE' and e.settlement_date < '${endDate}' then e.owner_id end as 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_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 < '${endDate}' and e.effective_date_to >= '${endDate}' WHERE t.reversed_on_date = '${endDate}' and t.transaction_type_enum = 26 and (t.office_id = ${officeId}) UNION ALL SELECT '${endDate}' AS transactiondate, t.id, l.name AS product, t.transaction_type_enum AS transaction_type, d.payment_type_id, '' as chargetype, true AS reversal_indicator, 'Interest' AS Allocation_Type, case when t.interest_portion_derived is null then 0 else t.interest_portion_derived end AS amount, case when e.status = 'ACTIVE' and e.settlement_date < '${endDate}' then e.owner_id end as 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_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 < '${endDate}' and e.effective_date_to >= '${endDate}' WHERE t.reversed_on_date = '${endDate}' and t.transaction_type_enum = 26 and (t.office_id = ${officeId}) UNION ALL SELECT '${endDate}' AS transactiondate, t.id, l.name AS product, t.transaction_type_enum AS transaction_type, d.payment_type_id, '' as chargetype, true AS reversal_indicato r, 'Principal' AS Allocation_Type, case when t.principal_portion_derived is null then 0 else t.principal_portion_derived end AS amount, case when e.status = 'ACTIVE' and e.settlement_date < '${endDate}' then e.owner_id end as 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_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 < '${endDate}' and e.effective_date_to >= '${endDate}' WHERE t.reversed_on_date = '${endDate}' and t.transaction_type_enum = 26 and (t.office_id = ${officeId}) UNION ALL SELECT '${endDate}' AS transactiondate, t.id, l.name AS product, t.transaction_type_enum AS transaction_type, d.payment_type_id, '' 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 amo unt, case when e.status = 'ACTIVE' and e.settlement_date < '${endDate}' then e.owner_id end as 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_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 < '${endDate}' and e.effective_date_to >= '${endDate}' WHERE t.reversed_on_date = '${endDate}' and t.transaction_type_enum = 26 and (t.office_id = ${officeId}) UNION ALL SELECT '${endDate}' AS transactiondate, t.id, p.name AS product, 9999 AS transaction_type, null, '' AS chargetype, false AS reversal_indicator, 'Principal' AS Allocation_type, dt.principal_outstanding_derived AS amount, t.owner_id AS asset_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 = 'ACTIVE' and c.office_id = ${officeId} and t.settlement_date = '${endDate}' and dt.principal_outstanding_derived > 0 UNION ALL SELECT '${endDate}' AS transactiondate, t.id, p.name AS product, 9999 AS transaction_type, null, '' AS chargetype, false AS reversal_indicator, 'Interest' AS Allocation_type, dt.interest_outstanding_derived AS amount, t.owner_id AS asset_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 = 'ACTIVE' and c.office_id = ${officeId} and t.settlement_date = '${endDate}' and dt.interest_outstanding_derived > 0 UNION ALL SELECT '${endDate}' AS transactiondate, t.id, p.name AS product, 9999 AS transaction_type, null, '' AS chargetype, false AS reversal_indicator, 'Fee' AS Allocation_type, dt.fee_ch arges_outstanding_derived AS amount, t.owner_id AS asset_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 = 'ACTIVE' and c.office_id = ${officeId} and t.settlement_date = '${endDate}' and dt.fee_charges_outstanding_derived > 0 UNION ALL SELECT '${endDate}' AS transactiondate, t.id, p.name AS product, 9999 AS transaction_type, null, '' AS chargetype, false AS reversal_indicator, 'Penalty' AS Allocation_type, dt.penalty_charges_outstanding_derived AS amount, t.owner_id AS asset_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 = 'ACTIVE' and c.office_id = ${o fficeId} and t.settlement_date = '${endDate}' and dt.penalty_charges_outstanding_derived > 0 UNION ALL SELECT '${endDate}' AS transactiondate, t.id, p.name AS product, 99999 AS transaction_type, null, '' AS chargetype, false AS reversal_indicator, 'Principal' AS Allocation_type, dt.principal_outstanding_derived AS amount, null AS asset_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 = 'BUYBACK' and c.office_id = ${officeId} and t.settlement_date = '${endDate}' and dt.principal_outstanding_derived > 0 UNION ALL SELECT '${endDate}' AS transactiondate, t.id, p.name AS product, 99999 AS transaction_type, null, '' AS chargetype, false AS reversal_indicator, 'Interest' AS Allocation_type, dt.interest_outstanding_derived AS amount, null AS asset_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 = 'BUYBACK' and c.office_id = ${officeId} and t.settlement_date = '${endDate}' and dt.interest_outstanding_derived > 0 UNION ALL SELECT '${endDate}' AS transactiondate, t.id, p.name AS product, 99999 AS transaction_type, null, '' AS chargetype, false AS reversal_indicator, 'Fee' AS Allocation_type, dt.fee_charges_outstanding_derived AS amount, null AS asset_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 = 'BUYBACK' and c.office_id = ${officeId} and t.settlement_date = '${endDate}' and dt.fee_charges_outstanding_derived > 0 UNIO N ALL SELECT '${endDate}' AS transactiondate, t.id, p.name AS product, 99999 AS transaction_type, null, '' AS chargetype, false AS reversal_indicator, 'Penalty' AS Allocation_type, dt.penalty_charges_outstanding_derived AS amount, null AS asset_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 = 'BUYBACK' and c.office_id = ${officeId} and t.settlement_date = '${endDate}' and dt.penalty_charges_outstanding_derived > 0)a GROUP BY a.transactiondate, a.product, a.transaction_type, a.payment_type_id, a.chargetype, a.reversal_indicator, a.Allocation_Type, a.asset_owner_id order by 1, 2, 3, 4, 5, 6, 7"/> Review Comment: Isn't it obvious from the SQL? :laughing: There was a surplus space in the `' Unallocated Credit (UNC)'`. I compared it to the simple transaction summary, and this space was not there. So I removed it from the `transaction summary with asset owner` report too. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@fineract.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
