[
https://issues.apache.org/jira/browse/FINERACT-2482?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Ralph Hopman reassigned FINERACT-2482:
--------------------------------------
Assignee: Ralph Hopman
> 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
> Assignee: Ralph Hopman
> Priority: Minor
>
> 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)
