adamsaghy commented on code in PR #5745: URL: https://github.com/apache/fineract/pull/5745#discussion_r3051527778
########## fineract-provider/src/main/resources/db/changelog/tenant/parts/0226_trial_balance_summary_fix_originator_join_conditions.xml: ########## @@ -0,0 +1,449 @@ +<?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" context="mysql"> + <update tableName="stretchy_report"> + <column name="report_sql"><![CDATA[ +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, + COALESCE(e.originator_external_ids, ' ') AS originator_external_ids + FROM acc_gl_journal_entry_annual_summary e, + m_product_loan lp + WHERE e.office_id = ${officeId} + AND lp.id = product_id + AND EXTRACT(YEAR FROM e.year_end_date) < EXTRACT(YEAR FROM CAST('${endDate}' AS DATE)) + GROUP BY gl_code, lp.name, office_id, owner_external_id, originator_external_ids), + loan_originators AS (SELECT mlom.loan_id, + GROUP_CONCAT(DISTINCT mlo.external_id ORDER BY mlo.external_id SEPARATOR ', ') AS originator_external_ids + FROM m_loan_originator_mapping mlom + JOIN m_loan_originator mlo ON mlo.id = mlom.originator_id + GROUP BY mlom.loan_id), + aggregated_date AS (SELECT MAX(aggregated_on_date_to) AS latest + FROM m_journal_entry_aggregation_tracking + WHERE aggregated_on_date_to < '${endDate}'), + summary_snapshot_baseline_data AS (SELECT lp.NAME AS productname, + acc_gl_account.gl_code AS glcode, + acc_gl_account.NAME AS glname, + CASE + WHEN ags.external_owner_id IS NULL THEN 0 + ELSE ags.external_owner_id END AS assetowner, + COALESCE(ags.originator_external_ids, ' ') AS originator_external_ids, + SUM(ags.debit_amount) AS debitamount, + SUM(ags.credit_amount) AS creditamount + FROM acc_gl_account + JOIN m_journal_entry_aggregation_summary ags + ON acc_gl_account.id = ags.gl_account_id + JOIN m_product_loan lp ON lp.id = ags.product_id + WHERE ags.entity_type_enum = 1 + AND ags.manual_entry = FALSE + AND ags.aggregated_on_date <= (SELECT latest FROM aggregated_date) + AND (ags.office_id = ${officeId}) + GROUP BY productname, glcode, glname, assetowner, originator_external_ids), + post_snapshot_delta_data AS (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 AS 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, + COALESCE(lo.originator_external_ids, ' ') AS originator_external_ids + 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 + LEFT JOIN loan_originators lo ON lo.loan_id = m.id + WHERE acc_gl_journal_entry.entity_type_enum = 1 + AND acc_gl_journal_entry.manual_entry = FALSE + AND ( + (SELECT latest FROM aggregated_date) IS NULL + OR + acc_gl_journal_entry.submitted_on_date > (SELECT latest FROM aggregated_date) + ) + AND acc_gl_journal_entry.submitted_on_date < '${endDate}' + AND (acc_gl_journal_entry.office_id = ${officeId}) + GROUP BY productname, glcode, glname, assetowner, originator_external_ids), + merged_historical_data AS (SELECT COALESCE(s.productname, p.productname) AS productname, + COALESCE(s.glcode, p.glcode) AS glcode, + COALESCE(s.glname, p.glname) AS glname, + COALESCE(s.assetowner, p.assetowner, 0) AS assetowner, + COALESCE(s.debitamount, 0) + COALESCE(p.debitamount, 0) AS debitamount, + COALESCE(s.creditamount, 0) + COALESCE(p.creditamount, 0) AS creditamount, + COALESCE(p.originator_external_ids, ' ') AS originator_external_ids Review Comment: `AND s.originator_external_ids = p.originator_external_ids` ensures, its always the same -- 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: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
