Ralph Hopman created FINERACT-2482:
--------------------------------------
Summary: On-hold transactions API returns empty results for group
savings accounts
Key: FINERACT-2482
URL: https://issues.apache.org/jira/browse/FINERACT-2482
Project: Apache Fineract
Issue Type: Bug
Components: Groups, Savings
Reporter: Ralph Hopman
The {{/savingsaccounts/\{savingsId}/onholdtransactions}} API endpoint returns
empty results for group savings accounts, even when holds exist on those
accounts. This breaks the ability to view guarantor holds placed on group
savings accounts.
h2. Steps to Reproduce
# Create a group savings account (where {{client_id}} is NULL and {{group_id}}
is set)
# Deposit funds into the account
# Place a hold on the account using {{holdAmountInSavingsAccount}}
# Call {{GET /savingsaccounts/\{savingsId}/onholdtransactions}}
# Observe the response
h2. Expected vs Actual Behavior
*Expected:* The API should return the hold transactions with proper group name
displayed in {{savingsClientName}} field.
*Actual:* The API returns an empty result:
{code:json}
{
"totalFilteredRecords": 0,
"pageItems": []
}
{code}
h2. Root Cause
The SQL query in {{DepositAccountOnHoldTransactionReadPlatformServiceImpl}}
uses {{INNER JOIN}} on {{{}m_client.client_id{}}}:
{code:sql}
join m_client sc on sc.id = sa.client_id
{code}
Since group savings accounts have {{client_id = NULL}} (they use {{group_id}}
instead), the INNER JOIN excludes all group account rows from the result set.
Additionally, the query lacks joins to {{m_group}} table, so even if rows were
returned, the group names cannot be displayed.
h2. Proposed Solution
Three changes are needed in the SQL query:
# *Change INNER JOIN to LEFT JOIN* for {{m_client}} (savings accounts):
{code:java}
left join m_client sc on sc.id = sa.client_id
{code}
# *Add LEFT JOIN for {{m_group}}* (savings accounts) and update the client
name to use COALESCE:
{code:java}
left join m_group sg on sg.id = sa.group_id
COALESCE(sc.display_name, sg.display_name) as savingsClientName
{code}
# *Add LEFT JOIN for {{m_group}}* (loan accounts) to handle group loans as
guarantors:
{code:java}
left join m_group lg on lg.id = ml.group_id
COALESCE(lc.display_name, lg.display_name) as loanClientName
{code}
h2. Testing Approach
Will add integration test {{testOnHoldTransactionsApiForGroupSavingsAccount}}
in {{GroupSavingsIntegrationTest}} that:
* Creates a group savings account
* Places a hold on the account
* Calls the on-hold transactions API
* Verifies the response contains the hold transaction
* *Verifies {{savingsClientName}} equals the group name* (currently returns
NULL)
* Validates transaction amount, type, and other fields
{*}Note{*}: To avoid bug FINERACT-2476, the loan product must be created
{{{}.withOnHoldFundDetails("0", "0", "0"){}}}.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
