This is an automated email from the ASF dual-hosted git repository.
taskain 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 7d7669f57 FINERACT-1971: Transaction summary report fix + handling
journal entries for chargeback scenarios
7d7669f57 is described below
commit 7d7669f5701da85d084131e767f1b280fe70c7a1
Author: Arnold Galovics <[email protected]>
AuthorDate: Thu Jan 18 09:26:24 2024 +0100
FINERACT-1971: Transaction summary report fix + handling journal entries
for chargeback scenarios
---
.../investor/service/AccountingServiceImpl.java | 55 +-
.../db/changelog/tenant/changelog-tenant.xml | 1 +
...report_recovery_repayments_chargeoff_reason.xml | 898 +++++++++++++++++++++
3 files changed, 934 insertions(+), 20 deletions(-)
diff --git
a/fineract-investor/src/main/java/org/apache/fineract/investor/service/AccountingServiceImpl.java
b/fineract-investor/src/main/java/org/apache/fineract/investor/service/AccountingServiceImpl.java
index f85f3eab0..875935605 100644
---
a/fineract-investor/src/main/java/org/apache/fineract/investor/service/AccountingServiceImpl.java
+++
b/fineract-investor/src/main/java/org/apache/fineract/investor/service/AccountingServiceImpl.java
@@ -75,11 +75,6 @@ public class AccountingServiceImpl implements
AccountingService {
@NotNull
private List<JournalEntry> createJournalEntries(Loan loan,
ExternalAssetOwnerTransfer transfer, boolean isReversalOrder) {
this.helper.checkForBranchClosures(loan.getOffice().getId(),
transfer.getSettlementDate());
- // loan properties
- final Long loanProductId = loan.getLoanProduct().getId();
- final Long loanId = loan.getId();
- final Office office = loan.getOffice();
- final String currencyCode = loan.getCurrencyCode();
// transaction properties
final Long transactionId = transfer.getId();
final LocalDate transactionDate = transfer.getSettlementDate();
@@ -90,11 +85,11 @@ public class AccountingServiceImpl implements
AccountingService {
final BigDecimal overPaymentAmount = loan.getTotalOverpaid();
// Moving money to asset transfer account
- List<JournalEntry> journalEntryList =
createJournalEntries(loanProductId, loanId, office, currencyCode, transactionId,
- transactionDate, principalAmount, interestAmount, feesAmount,
penaltiesAmount, overPaymentAmount, !isReversalOrder);
+ List<JournalEntry> journalEntryList = createJournalEntries(loan,
transactionId, transactionDate, principalAmount, interestAmount,
+ feesAmount, penaltiesAmount, overPaymentAmount,
!isReversalOrder);
// Moving money from asset transfer account
- journalEntryList.addAll(createJournalEntries(loanProductId, loanId,
office, currencyCode, transactionId, transactionDate,
- principalAmount, interestAmount, feesAmount, penaltiesAmount,
overPaymentAmount, isReversalOrder));
+ journalEntryList.addAll(createJournalEntries(loan, transactionId,
transactionDate, principalAmount, interestAmount, feesAmount,
+ penaltiesAmount, overPaymentAmount, isReversalOrder));
return journalEntryList;
}
@@ -121,24 +116,38 @@ public class AccountingServiceImpl implements
AccountingService {
});
}
- private List<JournalEntry> createJournalEntries(Long loanProductId, Long
loanId, Office office, String currencyCode, Long transactionId,
- LocalDate transactionDate, BigDecimal principalAmount, BigDecimal
interestAmount, BigDecimal feesAmount,
- BigDecimal penaltiesAmount, BigDecimal overPaymentAmount, boolean
isReversalOrder) {
+ private List<JournalEntry> createJournalEntries(Loan loan, Long
transactionId, LocalDate transactionDate, BigDecimal principalAmount,
+ BigDecimal interestAmount, BigDecimal feesAmount, BigDecimal
penaltiesAmount, BigDecimal overPaymentAmount,
+ boolean isReversalOrder) {
+ Long loanProductId = loan.productId();
+ Long loanId = loan.getId();
+ Office office = loan.getOffice();
+ String currencyCode = loan.getCurrencyCode();
List<JournalEntry> journalEntryList = new ArrayList<>();
BigDecimal totalDebitAmount = BigDecimal.ZERO;
Map<GLAccount, BigDecimal> accountMap = new LinkedHashMap<>();
// principal entry
if (principalAmount != null &&
principalAmount.compareTo(BigDecimal.ZERO) > 0) {
+ AccountingConstants.AccrualAccountsForLoan accrualAccount =
AccountingConstants.AccrualAccountsForLoan.LOAN_PORTFOLIO;
+ if (loan.isChargedOff()) {
+ if (loan.isFraud()) {
+ accrualAccount =
AccountingConstants.AccrualAccountsForLoan.CHARGE_OFF_FRAUD_EXPENSE;
+ } else {
+ accrualAccount =
AccountingConstants.AccrualAccountsForLoan.CHARGE_OFF_EXPENSE;
+ }
+ }
totalDebitAmount = totalDebitAmount.add(principalAmount);
- GLAccount account =
this.helper.getLinkedGLAccountForLoanProduct(loanProductId,
-
AccountingConstants.AccrualAccountsForLoan.LOAN_PORTFOLIO.getValue());
+ GLAccount account =
this.helper.getLinkedGLAccountForLoanProduct(loanProductId,
accrualAccount.getValue());
accountMap.put(account, principalAmount);
}
// interest entry
if (interestAmount != null &&
interestAmount.compareTo(BigDecimal.ZERO) > 0) {
+ AccountingConstants.AccrualAccountsForLoan accrualAccount =
AccountingConstants.AccrualAccountsForLoan.INTEREST_RECEIVABLE;
+ if (loan.isChargedOff()) {
+ accrualAccount =
AccountingConstants.AccrualAccountsForLoan.INCOME_FROM_CHARGE_OFF_INTEREST;
+ }
totalDebitAmount = totalDebitAmount.add(interestAmount);
- GLAccount account =
this.helper.getLinkedGLAccountForLoanProduct(loanProductId,
-
AccountingConstants.AccrualAccountsForLoan.INTEREST_RECEIVABLE.getValue());
+ GLAccount account =
this.helper.getLinkedGLAccountForLoanProduct(loanProductId,
accrualAccount.getValue());
if (accountMap.containsKey(account)) {
BigDecimal amount =
accountMap.get(account).add(interestAmount);
accountMap.put(account, amount);
@@ -148,9 +157,12 @@ public class AccountingServiceImpl implements
AccountingService {
}
// fee entry
if (feesAmount != null && feesAmount.compareTo(BigDecimal.ZERO) > 0) {
+ AccountingConstants.AccrualAccountsForLoan accrualAccount =
AccountingConstants.AccrualAccountsForLoan.FEES_RECEIVABLE;
+ if (loan.isChargedOff()) {
+ accrualAccount =
AccountingConstants.AccrualAccountsForLoan.INCOME_FROM_CHARGE_OFF_FEES;
+ }
totalDebitAmount = totalDebitAmount.add(feesAmount);
- GLAccount account =
this.helper.getLinkedGLAccountForLoanProduct(loanProductId,
-
AccountingConstants.AccrualAccountsForLoan.FEES_RECEIVABLE.getValue());
+ GLAccount account =
this.helper.getLinkedGLAccountForLoanProduct(loanProductId,
accrualAccount.getValue());
if (accountMap.containsKey(account)) {
BigDecimal amount = accountMap.get(account).add(feesAmount);
accountMap.put(account, amount);
@@ -160,9 +172,12 @@ public class AccountingServiceImpl implements
AccountingService {
}
// penalty entry
if (penaltiesAmount != null &&
penaltiesAmount.compareTo(BigDecimal.ZERO) > 0) {
+ AccountingConstants.AccrualAccountsForLoan accrualAccount =
AccountingConstants.AccrualAccountsForLoan.PENALTIES_RECEIVABLE;
+ if (loan.isChargedOff()) {
+ accrualAccount =
AccountingConstants.AccrualAccountsForLoan.INCOME_FROM_CHARGE_OFF_PENALTY;
+ }
totalDebitAmount = totalDebitAmount.add(penaltiesAmount);
- GLAccount account =
this.helper.getLinkedGLAccountForLoanProduct(loanProductId,
-
AccountingConstants.AccrualAccountsForLoan.PENALTIES_RECEIVABLE.getValue());
+ GLAccount account =
this.helper.getLinkedGLAccountForLoanProduct(loanProductId,
accrualAccount.getValue());
if (accountMap.containsKey(account)) {
BigDecimal amount =
accountMap.get(account).add(penaltiesAmount);
accountMap.put(account, amount);
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 f8b434ecc..87c20bb25 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
@@ -152,4 +152,5 @@
<include file="parts/0130_add_create_delinquency_action_permission.xml"
relativeToChangelogFile="true" />
<include file="parts/0131_add_configuration_maker_checker.xml"
relativeToChangelogFile="true" />
<include
file="parts/0132_add_configuration_loan_next_repayment_date_calculation.xml"
relativeToChangelogFile="true" />
+ <include
file="parts/0133_transaction_summary_with_asset_owner_report_recovery_repayments_chargeoff_reason.xml"
relativeToChangelogFile="true" />
</databaseChangeLog>
diff --git
a/fineract-provider/src/main/resources/db/changelog/tenant/parts/0133_transaction_summary_with_asset_owner_report_recovery_repayments_chargeoff_reason.xml
b/fineract-provider/src/main/resources/db/changelog/tenant/parts/0133_transaction_summary_with_asset_owner_report_recovery_repayments_chargeoff_reason.xml
new file mode 100644
index 000000000..c40dfee7b
--- /dev/null
+++
b/fineract-provider/src/main/resources/db/changelog/tenant/parts/0133_transaction_summary_with_asset_owner_report_recovery_repayments_chargeoff_reason.xml
@@ -0,0 +1,898 @@
+<?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,
+ (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) 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,
+ (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,
+ case when t.transaction_type_enum = 27 or (m.charged_off_on_date
<= t.transaction_date) then m.charge_off_reason_cv_id end AS
charge_off_reason_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,
+ '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,
+ case when t.transaction_type_enum = 27 or (m.charged_off_on_date
<= t.transaction_date) then m.charge_off_reason_cv_id end AS
charge_off_reason_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,
+ case when t.transaction_type_enum = 27 or (m.charged_off_on_date
<= t.transaction_date) then m.charge_off_reason_cv_id end AS
charge_off_reason_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,
+ '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,
+ case when t.transaction_type_enum = 27 or (m.charged_off_on_date
<= t.transaction_date) then m.charge_off_reason_cv_id end AS
charge_off_reason_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,
+ '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,
+ case when t.transaction_type_enum = 27 or (m.charged_off_on_date
<= t.transaction_date) then m.charge_off_reason_cv_id end AS
charge_off_reason_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,
+ case when t.transaction_type_enum = 27 or (m.charged_off_on_date
<= t.transaction_date) then m.charge_off_reason_cv_id end AS
charge_off_reason_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 asset_owner_id,
+ case when t.transaction_type_enum = 27 or (m.charged_off_on_date
<= t.transaction_date) then m.charge_off_reason_cv_id end AS
charge_off_reason_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,
+ case when t.transaction_type_enum = 27 or (m.charged_off_on_date
<= t.transaction_date) then m.charge_off_reason_cv_id end AS
charge_off_reason_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.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,
+ case when t.transaction_type_enum = 27 or (m.charged_off_on_date
<= t.transaction_date) then m.charge_off_reason_cv_id end AS
charge_off_reason_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,
+ case when t.transaction_type_enum = 27 or (m.charged_off_on_date
<= t.transaction_date) then m.charge_off_reason_cv_id end AS
charge_off_reason_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,
+ case when t.transaction_type_enum = 27 or (m.charged_off_on_date
<= t.transaction_date) then m.charge_off_reason_cv_id end AS
charge_off_reason_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,
+ '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,
+ case when t.transaction_type_enum = 27 or (m.charged_off_on_date
<= t.transaction_date) then m.charge_off_reason_cv_id end AS
charge_off_reason_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,
+ case when t.transaction_type_enum = 27 or (m.charged_off_on_date
<= t.transaction_date) then m.charge_off_reason_cv_id end AS
charge_off_reason_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,
+ 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,
+ case when t.transaction_type_enum = 27 or (m.charged_off_on_date
<= t.transaction_date) then m.charge_off_reason_cv_id end AS
charge_off_reason_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,
+ '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,
+ case when t.transaction_type_enum = 27 or (m.charged_off_on_date
<= t.transaction_date) then m.charge_off_reason_cv_id end AS
charge_off_reason_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,
+ case when t.transaction_type_enum = 27 or (m.charged_off_on_date
<= t.transaction_date) then m.charge_off_reason_cv_id end AS
charge_off_reason_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}'
+ then e.owner_id end
as asset_owner_id,
+ case when t.transaction_type_enum = 27 or (m.charged_off_on_date
<= t.transaction_date) then m.charge_off_reason_cv_id end AS
charge_off_reason_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,
+ case when t.transaction_type_enum = 27 or (m.charged_off_on_date
<= t.transaction_date) then m.charge_off_reason_cv_id end AS
charge_off_reason_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,
+ 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,
+ case when t.transaction_type_enum = 27 or (m.charged_off_on_date
<= t.transaction_date) then m.charge_off_reason_cv_id end AS
charge_off_reason_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.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,
+ case when t.transaction_type_enum = 27 or (m.charged_off_on_date
<= t.transaction_date) then m.charge_off_reason_cv_id end AS
charge_off_reason_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,
+ case when t.transaction_type_enum = 27 or (m.charged_off_on_date
<= t.transaction_date) then m.charge_off_reason_cv_id end AS
charge_off_reason_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,
+ case when t.transaction_type_enum = 27 or (m.charged_off_on_date
<= t.transaction_date) then m.charge_off_reason_cv_id end AS
charge_off_reason_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,
+ case when t.transaction_type_enum = 27 or (m.charged_off_on_date
<= t.transaction_date) then m.charge_off_reason_cv_id end AS
charge_off_reason_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,
+ case when t.transaction_type_enum = 27 or (m.charged_off_on_date
<= t.transaction_date) then m.charge_off_reason_cv_id end AS
charge_off_reason_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,
+ '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,
+ case when t.transaction_type_enum = 27 or (m.charged_off_on_date
<= t.transaction_date) then m.charge_off_reason_cv_id end AS
charge_off_reason_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 amount,
+ case
+ when e.status = 'ACTIVE' and e.settlement_date <
'${endDate}'
+ then e.owner_id end
as asset_owner_id,
+ case when t.transaction_type_enum = 27 or (m.charged_off_on_date
<= t.transaction_date) then m.charge_off_reason_cv_id end AS
charge_off_reason_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,
+ null AS charge_off_reason_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,
+ null AS charge_off_reason_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,
+ 'Fees' AS Allocation_type,
+ dt.fee_charges_outstanding_derived AS amount,
+ t.owner_id AS asset_owner_id,
+ null AS charge_off_reason_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,
+ null AS charge_off_reason_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.penalty_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,
+ 'Unallocated Credit (UNC)' AS Allocation_type,
+ dt.total_overpaid_derived AS amount,
+ t.owner_id AS asset_owner_id,
+ null AS charge_off_reason_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.total_overpaid_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,
+ case when l.charged_off_on_date <= t.settlement_date then
l.charge_off_reason_cv_id end AS charge_off_reason_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,
+ case when l.charged_off_on_date <= t.settlement_date then
l.charge_off_reason_cv_id end AS charge_off_reason_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,
+ 'Fees' AS Allocation_type,
+ dt.fee_charges_outstanding_derived AS amount,
+ null AS asset_owner_id,
+ case when l.charged_off_on_date <= t.settlement_date then
l.charge_off_reason_cv_id end AS charge_off_reason_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
+ UNION 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,
+ case when l.charged_off_on_date <= t.settlement_date then
l.charge_off_reason_cv_id end AS charge_off_reason_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
+ UNION ALL
+ SELECT '${endDate}' AS transactiondate,
+ t.id,
+ p.name AS product,
+ 99999 AS transaction_type,
+ null,
+ '' AS chargetype,
+ false AS reversal_indicator,
+ 'Unallocated Credit (UNC)' AS Allocation_type,
+ dt.total_overpaid_derived AS amount,
+ null AS asset_owner_id,
+ case when l.charged_off_on_date <= t.settlement_date then
l.charge_off_reason_cv_id end AS charge_off_reason_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.total_overpaid_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, a.charge_off_reason_id
+order by 1, 2, 3, 4, 5, 6, 7"/>
+ <where>report_name='Transaction Summary Report with Asset
Owner'</where>
+ </update>
+ </changeSet>
+</databaseChangeLog>
