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 e46ec2c98 FINERACT-1853: Fix general accounting reports reconciliation
e46ec2c98 is described below
commit e46ec2c984c66453e87c9e772c134a1170f889a9
Author: Jose Alberto Hernandez <[email protected]>
AuthorDate: Mon Mar 20 17:03:13 2023 -0600
FINERACT-1853: Fix general accounting reports reconciliation
---
.../db/changelog/tenant/changelog-tenant.xml | 1 +
...097_update_accounting_summary_table_reports.xml | 915 +++++++++++++++++++++
2 files changed, 916 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 1ffe88d13..d94e54d15 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
@@ -116,4 +116,5 @@
<include file="parts/0094_add_external_event_default_configuration.xml"
relativeToChangelogFile="true" />
<include
file="parts/0095_add_delinquency_and_arrears_display_configuration.xml"
relativeToChangelogFile="true" />
<include
file="parts/0096_modify_created_and_sent_at_date_external_events.xml"
relativeToChangelogFile="true" />
+ <include file="parts/0097_update_accounting_summary_table_reports.xml"
relativeToChangelogFile="true" />
</databaseChangeLog>
diff --git
a/fineract-provider/src/main/resources/db/changelog/tenant/parts/0097_update_accounting_summary_table_reports.xml
b/fineract-provider/src/main/resources/db/changelog/tenant/parts/0097_update_accounting_summary_table_reports.xml
new file mode 100644
index 000000000..791b61cd7
--- /dev/null
+++
b/fineract-provider/src/main/resources/db/changelog/tenant/parts/0097_update_accounting_summary_table_reports.xml
@@ -0,0 +1,915 @@
+<?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";>
+ <!-- Transaction Summary Report -->
+ <changeSet author="fineract" id="1">
+ <update tableName="stretchy_report">
+ <column name="report_sql" value="SELECT
+ '${endDate}' AS TransactionDate,
+ a.product AS Product,
+ (
+ SELECT
+ enum_message_property
+ FROM
+ r_enum_value
+ WHERE
+ enum_name = 'transaction_type_enum'
+ and enum_id = a.transaction_type
+ ) 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 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.reversal_indicator = false then sum(a.amount) * + 1 when
a.transaction_type IN (1, 10, 25,20)
+ AND a.reversal_indicator = true then sum(a.amount) * -1 end AS
Transaction_Amount
+ 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
+ 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
+ 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
+ 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
+ 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
+ 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
+ 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,
+ 'Penalty' AS Allocation_Type,
+ case when t.penalty_charges_portion_derived is null then 0
else t.penalty_charges_portion_derived end as amount
+ 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
+ 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,
+ 'Unallocated Credit (UNC)' AS Allocation_Type,
+ case when t.overpayment_portion_derived is null then 0 else
t.overpayment_portion_derived end as amount
+ 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
+ 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 t.fee_charges_portion_derived is null then 0 else
t.fee_charges_portion_derived end as amount
+ 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
+ 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 t.penalty_charges_portion_derived is null then 0
else t.penalty_charges_portion_derived end as amount
+ 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
+ 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
+ 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
+ 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 t.fee_charges_portion_derived is null then 0 else
t.fee_charges_portion_derived end as amount
+ 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
+ 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 t.penalty_charges_portion_derived is null then 0
else t.penalty_charges_portion_derived end AS amount
+ 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
+ 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
+ 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
+ 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
+ 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
+ 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
+ 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
+ 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,
+ 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
+ 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
+ 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,
+ 'Interest' AS Allocation_Type,
+ case when t.interest_portion_derived is null then 0 else
t.interest_portion_derived end AS amount
+ 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
+ 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
+ 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
+ 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
+ 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
+ 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
+ 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
+ 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 t.fee_charges_portion_derived is null then 0 else
t.fee_charges_portion_derived end as amount
+ 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
+ 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 t.penalty_charges_portion_derived is null then 0
else t.penalty_charges_portion_derived end as amount
+ 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
+ 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
+ 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
+ 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,
+ 'Fees' AS Allocation_Type,
+ case when t.fee_charges_portion_derived is null then 0 else
t.fee_charges_portion_derived end as amount
+ 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
+ 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 t.penalty_charges_portion_derived is null then 0
else t.penalty_charges_portion_derived end AS amount
+ 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
+ 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
+ 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
+ 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,
+ 'Principal' AS Allocation_Type,
+ case when t.principal_portion_derived is null then 0 else
t.principal_portion_derived end AS amount
+ 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
+ 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 amount
+ 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
+ WHERE
+ t.reversed_on_date = '${endDate}'
+ and t.transaction_type_enum = 26
+ and (t.office_id = ${officeId})
+ ) a
+ GROUP BY
+ a.transactiondate,
+ a.product,
+ a.transaction_type,
+ a.payment_type_id,
+ a.chargetype,
+ a.reversal_indicator,
+ a.Allocation_Type
+ order by
+ 1,
+ 2,
+ 3,
+ 4,
+ 5,
+ 6,
+ 7"/>
+ <where>report_name='Transaction Summary Report'</where>
+ </update>
+ </changeSet>
+ <!-- Trial Balance Summary report -->
+ <changeSet author="fineract" id="2">
+ <update tableName="stretchy_report">
+ <column name="report_sql" value="SELECT *
+ FROM
+ (
+ SELECT
+ '${endDate}' AS PostingDate,
+ loan.pname AS Product,
+ loan.gl_code AS GlAcct,
+ loan.glname AS Description,
+ loan.openingbalance AS BeginningBalance,
+ (loan.debitamount * 1) AS DebitMovement,
+ (loan.creditamount *-1) AS CreditMovement,
+ (
+ loan.openingbalance + loan.debitamount - loan.creditamount
+ ) AS EndingBalance
+ FROM
+ (
+ SELECT
+ g.pname,
+ g.gl_code,
+ g.glname,
+ COALESCE(debits.debitamount, 0) -
COALESCE(debits.creditamount, 0) AS openingbalance,
+ COALESCE(loanproduct.debitamount, 0) AS debitamount,
+ COALESCE(loanproduct.creditamount, 0) AS creditamount
+ FROM
+ (
+ SELECT
+ ag.gl_code,
+ pl.name AS pname,
+ ag.name AS glname
+ FROM
+ acc_gl_account ag
+ JOIN acc_product_mapping am ON am.gl_account_id = ag.id
+ AND am.product_type = 1
+ JOIN m_product_loan pl ON pl.id = am.product_id
+ ) g
+ LEFT JOIN (
+ SELECT
+ lp.name AS productname,
+ acc_gl_account.gl_code AS glcode,
+ acc_gl_account.name AS glname,
+ sum(
+ case when acc_gl_journal_entry.type_enum = 2 then
amount ELSE 0 end
+ ) AS debitamount,
+ sum(
+ case when acc_gl_journal_entry.type_enum = 1 then
amount ELSE 0 end
+ ) AS creditamount
+ FROM
+ acc_gl_account
+ JOIN acc_gl_journal_entry on acc_gl_account.id =
acc_gl_journal_entry.account_id
+ JOIN m_loan m ON m.id = acc_gl_journal_entry.entity_id
+ JOIN m_product_loan lp ON lp.id = m.product_id
+ WHERE
+ acc_gl_journal_entry.entity_type_enum = 1
+ AND acc_gl_journal_entry.manual_entry = false
+ and acc_gl_journal_entry.submitted_on_date <
'${endDate}'
+ and (
+ acc_gl_journal_entry.office_id = ${officeId}
+ )
+ group by
+ productname,
+ glcode,
+ glname
+ order by
+ glcode
+ ) debits ON g.gl_code = debits.glcode
+ AND debits.productname = g.pname
+ LEFT JOIN (
+ SELECT
+ lp.name AS productname, account_id,
+ acc_gl_account.gl_code AS glcode,
+ acc_gl_account.name AS glname,
+ sum(
+ case when acc_gl_journal_entry.type_enum = 2 then
amount ELSE 0 END
+ ) AS debitamount,
+ sum(
+ case when acc_gl_journal_entry.type_enum = 1 then
amount ELSE 0 END
+ ) AS creditamount
+ FROM
+ acc_gl_journal_entry
+ JOIN acc_gl_account on acc_gl_account.id =
acc_gl_journal_entry.account_id
+ JOIN m_loan m ON m.id = acc_gl_journal_entry.entity_id
+ JOIN m_product_loan lp ON lp.id = m.product_id
+ WHERE
+ acc_gl_journal_entry.entity_type_enum = 1
+ AND acc_gl_journal_entry.manual_entry = false
+ and acc_gl_journal_entry.submitted_on_date =
'${endDate}'
+ AND (
+ acc_gl_journal_entry.office_id = ${officeId}
+ )
+ group by
+ productname,
+ account_id,
+ glcode,
+ glname
+ order by
+ glcode
+ ) loanproduct ON g.gl_code = loanproduct.glcode
+ AND loanproduct.productname = g.pname
+ ) loan
+ UNION
+ SELECT
+ '${endDate}' AS PostingDate,
+ loan.pname AS Product,
+ loan.gl_code AS GlAcct,
+ loan.glname AS Description,
+ loan.openingbalance AS Beginning_Balance,
+ loan.debitamount AS Debit_Movement,
+ loan.creditamount AS Credit_Movement,
+ (
+ loan.openingbalance + loan.debitamount - loan.creditamount
+ ) AS Ending_Balance
+ FROM
+ (
+ SELECT
+ g.pname,
+ g.gl_code,
+ g.glname,
+ COALESCE(debits.debitamount, 0) -
COALESCE(debits.creditamount, 0) AS openingbalance,
+ COALESCE(loanproduct.debitamount, 0) AS debitamount,
+ COALESCE(loanproduct.creditamount, 0) AS creditamount
+ FROM
+ (
+ SELECT
+ ag.gl_code,
+ pl.name AS pname,
+ ag.name AS glname
+ FROM
+ acc_gl_account ag
+ JOIN acc_product_mapping am ON am.gl_account_id = ag.id
+ AND am.product_type = 2
+ JOIN m_savings_product pl ON pl.id = am.product_id
+ ) g
+ LEFT join (
+ SELECT
+ lp.name productname,
+ acc_gl_account.gl_code AS glcode,
+ acc_gl_account.name AS glname,
+ sum(
+ case when acc_gl_journal_entry.type_enum = 2 then
amount ELSE 0 end
+ ) AS debitamount,
+ sum(
+ case when acc_gl_journal_entry.type_enum = 1 then
amount ELSE 0 end
+ ) AS creditamount
+ FROM
+ acc_gl_account
+ join acc_gl_journal_entry on acc_gl_account.id =
acc_gl_journal_entry.account_id
+ JOIN m_savings_account m ON m.id =
acc_gl_journal_entry.entity_id
+ JOIN m_savings_product lp ON lp.id = m.product_id
+ WHERE
+ acc_gl_journal_entry.entity_type_enum = 2
+ AND acc_gl_journal_entry.manual_entry = false
+ and acc_gl_journal_entry.submitted_on_date <
'${endDate}'
+ and (
+ acc_gl_journal_entry.office_id = ${officeId}
+ )
+ group by
+ productname,
+ glcode,
+ glname
+ order by
+ glcode
+ ) debits ON g.gl_code = debits.glcode
+ AND debits.productname = g.pname
+ left JOIN (
+ SELECT
+ lp.name productname,
+ acc_gl_account.gl_code AS glcode,
+ acc_gl_account.name AS glname,
+ sum(
+ case when acc_gl_journal_entry.type_enum = 2 then
amount ELSE 0 end
+ ) AS debitamount,
+ sum(
+ case when acc_gl_journal_entry.type_enum = 1 then
amount ELSE 0 end
+ ) AS creditamount
+ from
+ acc_gl_journal_entry
+ join acc_gl_account on acc_gl_account.id =
acc_gl_journal_entry.account_id
+ JOIN m_savings_account m ON m.id =
acc_gl_journal_entry.entity_id
+ JOIN m_savings_product lp ON lp.id = m.product_id
+ WHERE
+ acc_gl_journal_entry.entity_type_enum = 2
+ AND acc_gl_journal_entry.manual_entry = false
+ and acc_gl_journal_entry.submitted_on_date =
'${endDate}'
+ and (
+ acc_gl_journal_entry.office_id = ${officeId}
+ )
+ group by
+ productname,
+ glcode,
+ glname
+ order by
+ glcode
+ ) loanproduct ON g.gl_code = loanproduct.glcode
+ AND loanproduct.productname = g.pname
+ ) loan
+ UNION
+ SELECT
+ '${endDate}' AS PostingDate,
+ 'manual' AS Product,
+ loan.gl_code AS GlAcct,
+ loan.glname AS Description,
+ loan.openingbalance AS Beginning_Balance,
+ loan.debitamount AS Debit_Movement,
+ loan.creditamount AS Credit_Movement,
+ (
+ loan.openingbalance + loan.debitamount - loan.creditamount
+ ) AS Ending_Balance
+ FROM
+ (
+ SELECT
+ g.gl_code,
+ g.name AS glname,
+ COALESCE(debits.debitamount, 0) -
COALESCE(debits.creditamount, 0) AS openingbalance,
+ COALESCE(loanproduct.debitamount, 0) AS debitamount,
+ COALESCE(loanproduct.creditamount, 0) AS creditamount
+ FROM
+ acc_gl_account g
+ LEFT join (
+ SELECT
+ acc_gl_account.gl_code AS glcode,
+ acc_gl_account.name AS glname,
+ sum(
+ case when acc_gl_journal_entry.type_enum = 2 then
amount ELSE 0 end
+ ) AS debitamount,
+ sum(
+ case when acc_gl_journal_entry.type_enum = 1 then
amount ELSE 0 end
+ ) AS creditamount
+ FROM
+ acc_gl_account
+ JOIN acc_gl_journal_entry on acc_gl_account.id =
acc_gl_journal_entry.account_id
+ WHERE
+ acc_gl_journal_entry.manual_entry = true
+ and acc_gl_journal_entry.submitted_on_date <
'${endDate}'
+ and (
+ acc_gl_journal_entry.office_id = ${officeId}
+ )
+ group by
+ glcode,
+ glname
+ order by
+ glcode
+ ) debits ON g.gl_code = debits.glcode
+ left JOIN (
+ SELECT
+ acc_gl_account.gl_code AS glcode,
+ acc_gl_account.name AS glname,
+ sum(
+ case when acc_gl_journal_entry.type_enum = 2 then
amount ELSE 0 end
+ ) AS debitamount,
+ sum(
+ case when acc_gl_journal_entry.type_enum = 1 then
amount ELSE 0 end
+ ) AS creditamount
+ FROM
+ acc_gl_journal_entry
+ join acc_gl_account on acc_gl_account.id =
acc_gl_journal_entry.account_id
+ where
+ acc_gl_journal_entry.manual_entry = true
+ and acc_gl_journal_entry.submitted_on_date =
'${endDate}'
+ and (
+ acc_gl_journal_entry.office_id = ${officeId}
+ )
+ group by
+ glcode,
+ glname
+ order by
+ glcode
+ ) loanproduct ON g.gl_code = loanproduct.glcode
+ ) loan
+ ) a
+ where
+ a.EndingBalance != 0 or a.DebitMovement != 0 or a.CreditMovement
!= 0 "/>
+ <where>report_name='Trial Balance Summary Report'</where>
+ </update>
+ </changeSet>
+</databaseChangeLog>
