0031454: Not possible to filter by Attribute Set Value in Return From Customer pick/edit lines

Notes
(0082785) vmromanos (manager) 2015-12-15 19:14	Assigned to platform: The problem seems to be related to the way the Entity Alias for the attributeSetValue column is defined, or with the assigned reference "PAttributte" The column attributeSetValue is defined with an Entity Alias = attributeSetValue, which seems to be OK. I have tried to modify it to iol.attributeSetValue (as in the left join), but without success. Log: org.hibernate.hql.ast.QuerySyntaxException: Invalid path: 'join_0.description' [select iol.id as id, iol.organization as organization, iol.client as client, iol.product as product, iol.uOM as uOM, iol.id as goodsShipmentLine, iol.movementQuantity as movementQuantity, iol.shipmentReceipt.businessPartner as businessPartner, iol.shipmentReceipt.documentNo as shipmentNumber, iol.shipmentReceipt.movementDate as movementDate, attributeSetValue as attributeSetValue, ( coalesce((select sum(ol.orderedQuantity) from OrderLine as ol left join ol.salesOrder as o where ol.goodsShipmentLine = iol and o.processed = true and o.documentStatus <> 'VO'), 0))(-1) as returnQtyOtherRM, (case when (select ('Y') from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine = iol) is null then false else true end) as obSelected, ( coalesce((select ol.orderedQuantity from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine = iol),0))(-1) as returned, (select ol.returnReason from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine = iol) as returnReason, coalesce((select ol.tax from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine = iol) , (select e.salesOrderLine.tax from MaterialMgmtShipmentInOutLine as e where e.id = iol)) as tax, (case when (select e.priceList.priceIncludesTax from Order as e where e.id = :salesOrderId) = true then coalesce((select ol.unitPrice from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine = iol), (select e.salesOrderLine.grossUnitPrice from MaterialMgmtShipmentInOutLine as e where e.id = iol)) else coalesce((select ol.unitPrice from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine = iol), (select e.salesOrderLine.unitPrice from MaterialMgmtShipmentInOutLine as e where e.id = iol)) end) as unitPrice, (select e.salesOrderLine.salesOrder.documentNo from MaterialMgmtShipmentInOutLine as e where e.id = iol) as orderNo FROM MaterialMgmtShipmentInOutLine as iol left join iol.attributeSetValue as attributeSetValue join iol.shipmentReceipt as io join io.documentType as dt where io.businessPartner.id = :businessPartnerId and dt.return = false and io.processed = true and io.documentStatus <> 'VO' and io.salesTransaction = true and (iol.movementQuantity+( coalesce((select sum(ol.orderedQuantity) from OrderLine as ol left join ol.salesOrder as o where ol.goodsShipmentLine = iol and o.processed = true and o.documentStatus <> 'VO'), 0)) <> 0) and (select iol.salesOrderLine.orderDiscount from MaterialMgmtShipmentInOutLine as e where e.id = iol) is null and iol.client.id in ('0', '23C59575B9CF467C9620760EB255B389') AND iol.organization in ('E443A31992CB4635AFCAEABE7183CE85','0','19404EAD144C49A0AF37D54377CF452D','B843C30461EA4501935CB1D125C9C25A') AND ( upper((COALESCE(to_char(join_0.description),''))) like upper(:alias_0) escape '\|' ) ORDER BY obSelected desc, obSelected desc, io.movementDate desc, io.documentNo desc, iol.lineNo] The automatically created HQL is wrong: it contains an alias "join_0", which seems to be a consequence of the reference "PAtrribute" used in the attributeSetValue column. According to the documentation [1], this kind of foreign key references must mandatory use the Table reference. Not sure if that's possible here or not. @Platform, please decide whether this is a design or a normal defect and the way to fix it.

(0082945) caristu (developer) 2015-12-22 15:56	Attached patch with the fix

(0083208) gorka_gil (administrator) 2016-01-12 18:08	Now the filtering is working for the PAttribute reference. In any case, as the documentation states[1], when defining a foreign key in a column of a HQL table, that column must use a Table reference. [1] http://wiki.openbravo.com/wiki/How_to_create_a_HQL_Based_Table#Column_Definition [^]

(0083211) gorka_gil (administrator) 2016-01-12 18:16	Repository: erp/devel/pi Changeset: f659f0e3adc5dff45b36e66663c5fe3e2fcbe68d Author: Carlos Aristu <carlos.aristu <at> openbravo.com> Date: Tue Jan 12 11:09:07 2016 +0100 URL: http://code.openbravo.com/erp/devel/pi/rev/f659f0e3adc5dff45b36e66663c5fe3e2fcbe68d [^] [^] fixes issue 31454: Can not filter by Attribute Set in Return From Customer P&E We are making use of the existing creatingJoinsInWhereClauseIsPrevented flag, which is true for HQLDataSourceService, to avoid the join resolving in the computeLeftWhereClauseForIdentifier() method of the AdvancedQueryBuilder class. Before this fix, a new alias was being included in the where clause but it was never defined before, causing the generation of a wrong query. --- M modules/org.openbravo.service.json/src/org/openbravo/service/json/AdvancedQueryBuilder.java ---

(0083215) hgbot (developer) 2016-01-12 19:45	Repository: erp/devel/pi Changeset: c318f64d6d489cf0f62718487fd6dfafa88a461b Author: Carlos Aristu <carlos.aristu <at> openbravo.com> Date: Tue Jan 12 19:44:43 2016 +0100 URL: http://code.openbravo.com/erp/devel/pi/rev/c318f64d6d489cf0f62718487fd6dfafa88a461b [^] related to issue 31454: updated copyright year --- M modules/org.openbravo.service.json/src/org/openbravo/service/json/AdvancedQueryBuilder.java ---

(0083340) alostale (manager) 2016-01-15 11:29	code reviewed tested Attribute Set Value field in P&E: * Drop down shows all values * Typing restricts values in drop down * Selected items in drop down are correctly filtered in grid * Filtering by text typing correctly filters grid

(0085031) hudsonbot (developer) 2016-03-17 10:54	A changeset related to this issue has been promoted main and to the Central Repository, after passing a series of tests. Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/b22fb0500156 [^] Maturity status: Test

(0085032) hudsonbot (developer) 2016-03-17 10:54	A changeset related to this issue has been promoted main and to the Central Repository, after passing a series of tests. Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/b22fb0500156 [^] Maturity status: Test

Issue History
Date Modified	Username	Field	Change
2015-11-12 18:30	maite	New Issue
2015-11-12 18:30	maite	Assigned To	=> Triage Finance
2015-11-12 18:30	maite	Modules	=> Core
2015-11-12 18:30	maite	Resolution time	=> 1449702000
2015-11-12 18:30	maite	Triggers an Emergency Pack	=> No
2015-11-12 18:30	maite	Issue Monitored: networkb
2015-11-25 11:59	dmiguelez	Status	new => scheduled
2015-12-15 19:04	vmromanos	Assigned To	Triage Finance => platform
2015-12-15 19:14	vmromanos	Note Added: 0082785
2015-12-21 11:23	alostale	Relationship added	related to 0029241
2015-12-22 12:53	caristu	Target Version	3.0PR16Q1 => 3.0PR16Q2
2015-12-22 12:55	caristu	Assigned To	platform => caristu
2015-12-22 15:56	caristu	File Added: issue_31454.diff
2015-12-22 15:56	caristu	Note Added: 0082945
2016-01-12 18:08	gorka_gil	Review Assigned To	=> alostale
2016-01-12 18:08	gorka_gil	Note Added: 0083208
2016-01-12 18:08	gorka_gil	Status	scheduled => resolved
2016-01-12 18:08	gorka_gil	Resolution	open => fixed
2016-01-12 18:08	gorka_gil	Fixed in SCM revision	=> http://code.openbravo.com/erp/devel/pi/rev/f659f0e3adc5dff45b36e66663c5fe3e2fcbe68d [^]
2016-01-12 18:08	gorka_gil	Issue Monitored: alostale
2016-01-12 18:16	gorka_gil	Note Added: 0083211
2016-01-12 19:45	hgbot	Checkin
2016-01-12 19:45	hgbot	Note Added: 0083215
2016-01-15 11:29	alostale	Note Added: 0083340
2016-01-15 11:29	alostale	Status	resolved => closed
2016-01-15 11:29	alostale	Fixed in Version	=> 3.0PR16Q2
2016-03-17 10:54	hudsonbot	Checkin
2016-03-17 10:54	hudsonbot	Note Added: 0085031
2016-03-17 10:54	hudsonbot	Checkin
2016-03-17 10:54	hudsonbot	Note Added: 0085032