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 4f470606f FINERACT-2081: Update Trial Balance Summary Report with Year
end retaining
4f470606f is described below
commit 4f470606ffd36b6e02f86b9a4aa85da943c386b9
Author: Adam Saghy <[email protected]>
AuthorDate: Mon Dec 16 14:10:15 2024 +0100
FINERACT-2081: Update Trial Balance Summary Report with Year end retaining
---
.../db/changelog/tenant/changelog-tenant.xml | 2 +
..._create_acc_gl_journal_entry_annual_summary.xml | 67 +++++++++++
...e_summary_with_asset_owner_year_end_summary.xml | 132 +++++++++++++++++++++
3 files changed, 201 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 24619ba2d..b540449e6 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
@@ -176,4 +176,6 @@
<include
file="parts/0155_add_configuration_enable_immediate_charge_accrual_post_maturity.xml"
relativeToChangelogFile="true" />
<include
file="parts/0156_add_disallow_interest_calc_on_past_due_field.xml"
relativeToChangelogFile="true" />
<include file="parts/0157_create_index_m_loan_transaction_relation.xml"
relativeToChangelogFile="true" />
+ <include file="parts/0158_create_acc_gl_journal_entry_annual_summary.xml"
relativeToChangelogFile="true" />
+ <include
file="parts/0159_trial_balance_summary_with_asset_owner_year_end_summary.xml"
relativeToChangelogFile="true" />
</databaseChangeLog>
diff --git
a/fineract-provider/src/main/resources/db/changelog/tenant/parts/0158_create_acc_gl_journal_entry_annual_summary.xml
b/fineract-provider/src/main/resources/db/changelog/tenant/parts/0158_create_acc_gl_journal_entry_annual_summary.xml
new file mode 100644
index 000000000..3014d3fa7
--- /dev/null
+++
b/fineract-provider/src/main/resources/db/changelog/tenant/parts/0158_create_acc_gl_journal_entry_annual_summary.xml
@@ -0,0 +1,67 @@
+<?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.1.xsd";>
+ <changeSet id="1" author="fineract" runInTransaction="false"
context="postgresql">
+ <createTable tableName="acc_gl_journal_entry_annual_summary">
+ <column autoIncrement="true" name="id" type="BIGINT">
+ <constraints nullable="false" primaryKey="true"
primaryKeyName="pk_acc_gl_journal_entry_annual_summary"/>
+ </column>
+ <column name="gl_code" type="VARCHAR(45)">
+ <constraints nullable="false"/>
+ </column>
+ <column name="product_id" type="BIGINT">
+ <constraints nullable="false"/>
+ </column>
+ <column name="office_id" type="BIGINT">
+ <constraints nullable="false"/>
+ </column>
+ <column name="opening_balance_amount" type="DECIMAL(19, 6)">
+ <constraints nullable="false"/>
+ </column>
+ <column name="currency_code" type="VARCHAR(3)">
+ <constraints nullable="false"/>
+ </column>
+ <column name="owner_external_id" type="VARCHAR(100)"/>
+ <column name="manual_entry" type="boolean"
defaultValueBoolean="false">
+ <constraints nullable="false"/>
+ </column>
+ <column name="year_end_date" type="DATE">
+ <constraints nullable="false"/>
+ </column>
+ <column name="created_by" type="BIGINT"/>
+ <column name="created_on_utc" type="timestamp with time zone"/>
+ <column name="last_modified_by" type="BIGINT"/>
+ <column name="last_modified_on_utc" type="timestamp with time
zone"/>
+ </createTable>
+ </changeSet>
+
+ <changeSet author="fineract" id="2" runInTransaction="false"
context="postgresql">
+ <preConditions onFail="MARK_RAN">
+ <indexExists tableName="acc_gl_journal_entry_annual_summary"
indexName="acc_gl_journal_entry_annual_summary_idx_1"/>
+ </preConditions>
+ <sql>
+ create index concurrently
acc_gl_journal_entry_annual_summary_idx_1 on
acc_gl_journal_entry_annual_summary(gl_code,product_name);
+ </sql>
+ </changeSet>
+</databaseChangeLog>
diff --git
a/fineract-provider/src/main/resources/db/changelog/tenant/parts/0159_trial_balance_summary_with_asset_owner_year_end_summary.xml
b/fineract-provider/src/main/resources/db/changelog/tenant/parts/0159_trial_balance_summary_with_asset_owner_year_end_summary.xml
new file mode 100644
index 000000000..516841989
--- /dev/null
+++
b/fineract-provider/src/main/resources/db/changelog/tenant/parts/0159_trial_balance_summary_with_asset_owner_year_end_summary.xml
@@ -0,0 +1,132 @@
+<?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="3">
+ <update tableName="stretchy_report">
+ <column name="report_sql" value="WITH retained_earning AS (SELECT
distinct '${endDate}' AS postingdate,
+ lp.name
AS product,
+ gl_code
AS glacct,
+ COALESCE((select name from
acc_gl_account where gl_code = e.gl_code),
+ '')
AS description,
+ COALESCE(e.owner_external_id,
'self') AS assetowner,
+ sum(opening_balance_amount)
AS beginningbalance,
+ 0
AS debitmovement,
+ 0
AS creditmovement,
+ sum(opening_balance_amount)
AS endingbalance
+ FROM acc_gl_journal_entry_annual_summary e,
m_product_loan lp
+ where e.office_id = ${officeId}
+ and lp.id = product_id
+ and date_part('year', e.year_end_date::date) <
date_part('year', '${endDate}'::date)
+ group by gl_code, lp.name, office_id,
owner_external_id)
+select *
+from (select *
+ from retained_earning
+ where glacct = (select gl_code from acc_gl_account where name ='Retained
+Earnings Prior Year')
+ UNION
+ select txnreport.postingdate,
+ txnreport.product,
+ txnreport.glacct,
+ txnreport.description,
+ txnreport.assetowner,
+ (COALESCE(txnreport.beginningbalance, 0) +
COALESCE(summary.beginningbalance, 0)) AS beginningbalance,
+ txnreport.debitmovement
AS debitmovement,
+ txnreport.creditmovement
AS creditmovement,
+ (COALESCE(txnreport.endingbalance, 0) +
COALESCE(summary.beginningbalance, 0)) AS endingbalance
+ from (select *
+ from (SELECT distinct '${endDate}'
AS postingdate,
+ loan.pname
AS product,
+ loan.gl_code
AS glacct,
+ loan.glname
AS description,
+ COALESCE((select external_id from
m_external_asset_owner where id = loan.assetowner),
+ 'self')
AS assetowner,
+ loan.openingbalance
AS beginningbalance,
+ (loan.debitamount * 1)
AS debitmovement,
+ (loan.creditamount * -1)
AS creditmovement,
+ (loan.openingbalance + loan.debitamount -
loan.creditamount) AS endingbalance
+ FROM (SELECT distinct COALESCE(g.pname,
loanproduct.productname) pname,
+ COALESCE(g.gl_code,
loanproduct.glcode) gl_code,
+ COALESCE(g.glname, loanproduct.glname)
glname,
+ COALESCE(debits.assetowner,
loanproduct.assetowner) assetowner,
+ COALESCE(debits.debitamount, 0) -
COALESCE(debits.creditamount, 0) AS openingbalance,
+ COALESCE(loanproduct.debitamount, 0)
AS debitamount,
+ COALESCE(loanproduct.creditamount, 0)
AS creditamount
+ FROM (SELECT distinct ag.gl_code, ag.id, pl.NAME AS
pname, ag.NAME AS glname
+ FROM acc_gl_account ag
+ JOIN acc_product_mapping am ON
am.gl_account_id = ag.id AND am.product_type = 1
+ JOIN m_product_loan pl ON pl.id =
am.product_id) g
+ LEFT JOIN (SELECT lp.NAME
AS productname,
+ acc_gl_account.gl_code
AS glcode,
+ acc_gl_account.NAME
AS glname,
+ case when aw.owner_id is
null then 0 else aw.owner_id end assetowner,
+ Sum(CASE WHEN
acc_gl_journal_entry.type_enum = 2 THEN amount ELSE 0 END) AS debitamount,
+ Sum(CASE WHEN
acc_gl_journal_entry.type_enum = 1 THEN amount ELSE 0 END) AS creditamount
+ FROM acc_gl_account
+ JOIN acc_gl_journal_entry
+ ON acc_gl_account.id
= acc_gl_journal_entry.account_id
+ JOIN m_loan m ON m.id =
acc_gl_journal_entry.entity_id
+ JOIN m_product_loan lp ON
lp.id = m.product_id
+ left join
m_external_asset_owner_journal_entry_mapping aw
+ on
aw.journal_entry_id = acc_gl_journal_entry.id
+ WHERE
acc_gl_journal_entry.entity_type_enum = 1
+ AND
acc_gl_journal_entry.manual_entry = false
+ AND
acc_gl_journal_entry.submitted_on_date < '${endDate}'
+ AND
(acc_gl_journal_entry.office_id = ${officeId})
+ GROUP BY productname, glcode,
glname, assetowner
+ ORDER BY glcode) debits
+ ON g.gl_code = debits.glcode AND
debits.productname = g.pname
+ full outer join (select lp.name
as productname,
+ account_id,
+
acc_gl_account.gl_code as
glcode,
+ acc_gl_account.name
as glname,
+ case when aw.owner_id
is null then 0 else aw.owner_id end assetowner,
+ sum(case when
acc_gl_journal_entry.type_enum = 2 then amount else 0 end) as debitamount,
+ sum(case when
acc_gl_journal_entry.type_enum = 1 then amount else 0 end) as creditamount
+ from acc_gl_journal_entry
+ join acc_gl_account
on acc_gl_account.id = acc_gl_journal_entry.account_id
+ join m_loan m on
m.id = acc_gl_journal_entry.entity_id
+ join m_product_loan
lp on lp.id = m.product_id
+ left join
m_external_asset_owner_journal_entry_mapping aw
+ on
aw.journal_entry_id = acc_gl_journal_entry.id
+ where
acc_gl_journal_entry.entity_type_enum = 1
+ and
acc_gl_journal_entry.manual_entry = false
+ and
acc_gl_journal_entry.submitted_on_date = '${endDate}'
+ and
(acc_gl_journal_entry.office_id = ${officeId})
+ group by productname,
account_id, glcode, glname, assetowner
+ order by glcode) loanproduct
+ on g.gl_code =
loanproduct.glcode and
+ loanproduct.productname =
g.pname and
+ debits.assetowner =
loanproduct.assetowner) loan) a) AS txnreport
+ left join retained_earning summary
+ on txnreport.glacct = summary.glacct and
txnreport.assetowner = summary.assetowner and
+ summary.product = txnreport.product) report
+where report.endingbalance != 0
+ or report.debitmovement != 0
+ or report.creditmovement != 0
+order by glacct"/>
+ <where>report_name='Trial Balance Summary Report with Asset
Owner'</where>
+ </update>
+ </changeSet>
+</databaseChangeLog>
