"0.00" and (productParticipation.act_arch_short_name = "act.supplierOrderItem") and (orders.status = "POSTED" or orders.status is null) and (deliveryStatus.value <> "FULL" or deliveryStatus.value is null) and stockParticipation.entity_id = stockLocation.entity_id union select stockLocation.entity_id as stockLocationId, stockLocation.name as stockLocation, supplier.entity_id as supplierId, supplier.name as supplier, product.entity_id as productId, product.name as product, currentqty.value as current, idealqty.value as ideal, criticalqty.value as critical, packageSize.value as packsize, preferred.value as prefSupplier, reordercode.value as reorderCode, reorderdesc.value as reorderDesc, nettprice.value as nettPrice, 0 as orderedQty, 0 as receivedQty, 0 as cancelledQty, packageSize.value as orderSize from entities product join entity_relationships productStock on product.entity_id = productStock.source_id and productStock.arch_short_name = "entityRelationship.productStockLocation" left outer join entity_relationship_details currentqty on currentqty.entity_relationship_id = productStock.entity_relationship_id and currentqty.name = "quantity" left outer join entity_relationship_details idealqty on idealqty.entity_relationship_id = productStock.entity_relationship_id and idealqty.name = "idealQty" left outer join entity_relationship_details criticalqty on criticalqty.entity_relationship_id = productStock.entity_relationship_id and criticalqty.name = "criticalQty" join entities stockLocation on stockLocation.entity_id = productStock.target_id left outer join entity_classifications productgroup on productgroup.entity_id = product.entity_id left outer join lookups grouplookup on grouplookup.lookup_id = productgroup.lookup_id join entity_relationships productSupplier on product.entity_id = productSupplier.source_id and productSupplier.arch_short_name = "entityRelationship.productSupplier" left outer join entity_relationship_details packageSize on packageSize.entity_relationship_id = productSupplier.entity_relationship_id and packageSize.name = "packageSize" left outer join entity_relationship_details preferred on preferred.entity_relationship_id = productSupplier.entity_relationship_id and preferred.name = "preferred" left outer join entity_relationship_details reordercode on reordercode.entity_relationship_id = productSupplier.entity_relationship_id and reordercode.name = "reorderCode" left outer join entity_relationship_details reorderdesc on reorderdesc.entity_relationship_id = productSupplier.entity_relationship_id and reorderdesc.name = "reorderDescription" left outer join entity_relationship_details nettprice on nettprice.entity_relationship_id = productSupplier.entity_relationship_id and nettprice.name = "nettPrice" join entities supplier on supplier.entity_id = productSupplier.target_id where product.name like $P{Product} and product.active = 1 and stockLocation.name like $P{Stock Location} and supplier.name like $P{Supplier} and if($P{Classification} = "%",(grouplookup.name like $P{Classification} or grouplookup.lookup_id is null),grouplookup.name like $P{Classification}) and preferred.value = "true" and idealqty.value <> "0.00" ) as tmp group by stockLocationId, supplierId, productId having (current + onorder) <= critical and toorder > 0]]>