0029235: can't filter by Writeoff in Order/Invoice P&E window

Description

When the user tries to filter the Order/Invoice Pick and Execute grid using the writeoff column, not results are returned and the following exception is shown in the log:

1299601 [http-8080-9] ERROR org.openbravo.client.kernel.KernelUtils - unexpected char: '@' [select hqlagg(psd.id) as paymentScheduleDetail, hqlagg(ord.documentNo) as salesOrderNo, case when (inv.salesTransaction = false and oinfo is not null and oinfo.aPRMPaymentDescription like 'Supplier Reference') then inv.orderReference else inv.documentNo end as invoiceNo, COALESCE(ips.finPaymentmethod.id, ops.finPaymentmethod.id) as paymentMethod, COALESCE(ipsfp.name, opsfp.name) as paymentMethodName, COALESCE(invbp.id, ordbp.id) as businessPartner, COALESCE(invbp.name, ordbp.name) as businessPartnerName, COALESCE(ips.expectedDate, ops.expectedDate) as expectedDate, max(COALESCE(ips.amount, ops.amount)) as expectedAmount, max(COALESCE(inv.grandTotalAmount, 0)) as invoicedAmount, SUM(psd.amount + psd.writeoffAmount) as outstandingAmount, COALESCE(sum(pd.amount), 0) as amount, case when sum(psd.writeoffAmount) <> 0 then true else false end as writeoff, case when max(fp.id) is not null then true else false end as OB_Selected from FIN_Payment_ScheduleDetail as psd left outer join psd.orderPaymentSchedule as ops left outer join ops.order as ord with ord.salesTransaction = :isSalesTransaction and ord.currency.id = :currencyId and ord.businessPartner.id = :businessPartnerId left outer join ord.businessPartner as ordbp left outer join ops.fINPaymentPriority as opriority left outer join ops.finPaymentmethod opsfp left outer join psd.invoicePaymentSchedule as ips left outer join ips.invoice as inv with inv.salesTransaction = :isSalesTransaction and inv.currency.id = :currencyId and inv.businessPartner.id = :businessPartnerId left outer join inv.businessPartner as invbp left outer join ips.fINPaymentPriority as ipriority left outer join ips.finPaymentmethod as ipsfp left outer join psd.organization as org left outer join org.organizationInformationList as oinfo left outer join psd.paymentDetails as pd left outer join pd.finPayment as fp where psd.client.id in ('0', '23C59575B9CF467C9620760EB255B389') AND psd.organization in ('E443A31992CB4635AFCAEABE7183CE85','0','19404EAD144C49A0AF37D54377CF452D','B843C30461EA4501935CB1D125C9C25A') and (psd.paymentDetails is null or fp.id = :paymentId) and coalesce(ips,ops) is not null and psd.organization.id in :orgIds and (oinfo is null or oinfo.active = true) and ( ( inv.salesTransaction = :isSalesTransaction and invbp.id = :businessPartnerId and inv.currency.id = :currencyId ) ) group by inv.documentNo, COALESCE(ips.finPaymentmethod.id, ops.finPaymentmethod.id), COALESCE(ipsfp.name, opsfp.name), COALESCE(ips.expectedDate, ops.expectedDate), COALESCE(ipriority.priority, opriority.priority), inv.salesTransaction, oinfo.organization, oinfo.aPRMPaymentDescription, inv.orderReference, COALESCE(invbp.id, ordbp.id), COALESCE(invbp.name, ordbp.name) having ( ( @writeoff@ = :alias_0 ) ) ORDER BY CASE WHEN MAX(fp.id) IS NOT NULL THEN 0 ELSE 1 END , COALESCE(ipriority.priority, opriority.priority) , COALESCE(ips.expectedDate, ops.expectedDate) , inv.documentNo ]
org.hibernate.QueryException: unexpected char: '@' [select hqlagg(psd.id) as paymentScheduleDetail, hqlagg(ord.documentNo) as salesOrderNo, case when (inv.salesTransaction = false and oinfo is not null and oinfo.aPRMPaymentDescription like 'Supplier Reference') then inv.orderReference else inv.documentNo end as invoiceNo, COALESCE(ips.finPaymentmethod.id, ops.finPaymentmethod.id) as paymentMethod, COALESCE(ipsfp.name, opsfp.name) as paymentMethodName, COALESCE(invbp.id, ordbp.id) as businessPartner, COALESCE(invbp.name, ordbp.name) as businessPartnerName, COALESCE(ips.expectedDate, ops.expectedDate) as expectedDate, max(COALESCE(ips.amount, ops.amount)) as expectedAmount, max(COALESCE(inv.grandTotalAmount, 0)) as invoicedAmount, SUM(psd.amount + psd.writeoffAmount) as outstandingAmount, COALESCE(sum(pd.amount), 0) as amount, case when sum(psd.writeoffAmount) <> 0 then true else false end as writeoff, case when max(fp.id) is not null then true else false end as OB_Selected from FIN_Payment_ScheduleDetail as psd left outer join psd.orderPaymentSchedule as ops left outer join ops.order as ord with ord.salesTransaction = :isSalesTransaction and ord.currency.id = :currencyId and ord.businessPartner.id = :businessPartnerId left outer join ord.businessPartner as ordbp left outer join ops.fINPaymentPriority as opriority left outer join ops.finPaymentmethod opsfp left outer join psd.invoicePaymentSchedule as ips left outer join ips.invoice as inv with inv.salesTransaction = :isSalesTransaction and inv.currency.id = :currencyId and inv.businessPartner.id = :businessPartnerId left outer join inv.businessPartner as invbp left outer join ips.fINPaymentPriority as ipriority left outer join ips.finPaymentmethod as ipsfp left outer join psd.organization as org left outer join org.organizationInformationList as oinfo left outer join psd.paymentDetails as pd left outer join pd.finPayment as fp where psd.client.id in ('0', '23C59575B9CF467C9620760EB255B389') AND psd.organization in ('E443A31992CB4635AFCAEABE7183CE85','0','19404EAD144C49A0AF37D54377CF452D','B843C30461EA4501935CB1D125C9C25A') and (psd.paymentDetails is null or fp.id = :paymentId) and coalesce(ips,ops) is not null and psd.organization.id in :orgIds and (oinfo is null or oinfo.active = true) and ( ( inv.salesTransaction = :isSalesTransaction and invbp.id = :businessPartnerId and inv.currency.id = :currencyId ) ) group by inv.documentNo, COALESCE(ips.finPaymentmethod.id, ops.finPaymentmethod.id), COALESCE(ipsfp.name, opsfp.name), COALESCE(ips.expectedDate, ops.expectedDate), COALESCE(ipriority.priority, opriority.priority), inv.salesTransaction, oinfo.organization, oinfo.aPRMPaymentDescription, inv.orderReference, COALESCE(invbp.id, ordbp.id), COALESCE(invbp.name, ordbp.name) having ( ( @writeoff@ = :alias_0 ) ) ORDER BY CASE WHEN MAX(fp.id) IS NOT NULL THEN 0 ELSE 1 END , COALESCE(ipriority.priority, opriority.priority) , COALESCE(ips.expectedDate, ops.expectedDate) , inv.documentNo ]
    at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:229)
    at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:136)
    at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:101)
    at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:80)
    at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:124)
    at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:156)
    at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:135)
    at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1770)
    at org.openbravo.service.datasource.HQLDataSourceService.getQuery(HQLDataSourceService.java:328)
    at org.openbravo.service.datasource.HQLDataSourceService.getData(HQLDataSourceService.java:165)
    at org.openbravo.service.datasource.ReadOnlyDataSourceService.fetchJSONObject(ReadOnlyDataSourceService.java:137)
    at org.openbravo.service.datasource.ReadOnlyDataSourceService.fetch(ReadOnlyDataSourceService.java:70)
    at org.openbravo.service.datasource.DataSourceServlet.doFetch(DataSourceServlet.java:261)
    at org.openbravo.service.datasource.DataSourceServlet.doPost(DataSourceServlet.java:747)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:643)
    at org.openbravo.base.HttpBaseServlet.serviceInitialized(HttpBaseServlet.java:222)
    at org.openbravo.base.secureApp.HttpSecureAppServlet.service(HttpSecureAppServlet.java:449)
    at org.openbravo.client.kernel.BaseKernelServlet.callServiceInSuper(BaseKernelServlet.java:88)
    at org.openbravo.client.kernel.BaseKernelServlet.service(BaseKernelServlet.java:59)
    at org.openbravo.service.datasource.DataSourceServlet.service(DataSourceServlet.java:140)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:723)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.openbravo.utils.SessionExpirationFilter.doFilter(SessionExpirationFilter.java:66)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.openbravo.utils.CharsetFilter.doFilter(CharsetFilter.java:35)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.openbravo.client.kernel.KernelFilter$1.doAction(KernelFilter.java:62)
    at org.openbravo.dal.core.ThreadHandler.run(ThreadHandler.java:46)
    at org.openbravo.client.kernel.KernelFilter.doFilter(KernelFilter.java:71)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.openbravo.dal.core.DalRequestFilter$1.doAction(DalRequestFilter.java:81)
    at org.openbravo.dal.core.ThreadHandler.run(ThreadHandler.java:46)
    at org.openbravo.dal.core.DalRequestFilter.doFilter(DalRequestFilter.java:103)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:861)
    at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:606)
    at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)

Steps To Reproduce

As F&B Admin
- Open the Payment In window
- Create a record in the header tab
- Click on the Add Details button
- In the Order/Invoice grid, try to filter using the writeoff column. An error will be shown in the log

Issue History
Date Modified	Username	Field	Change
2015-03-11 10:11	AugustoMauch	New Issue
2015-03-11 10:11	AugustoMauch	Assigned To	=> Sandrahuguet
2015-03-11 10:11	AugustoMauch	Modules	=> Core
2015-03-11 10:11	AugustoMauch	Triggers an Emergency Pack	=> No
2015-03-11 10:11	AugustoMauch	Relationship added	related to 0029224
2015-03-11 10:11	AugustoMauch	Relationship added	related to 0029225
2015-03-11 10:48	AugustoMauch	Relationship added	related to 0029237
2015-03-11 10:53	AugustoMauch	Relationship added	related to 0029239
2017-05-29 13:45	Sandrahuguet	Assigned To	Sandrahuguet => Triage Finance

Notes
There are no notes attached to this issue.