[hqlpickandexecute] Error shown while creating a Return from Customer order
authorSandra Huguet <sandra.huguet@openbravo.com>
Thu, 20 Nov 2014 17:14:20 +0100
changeset 25457 289b897908c3
parent 25456 7afceccc98c2
child 25458 e0dd378785e1
[hqlpickandexecute] Error shown while creating a Return from Customer order
src-db/database/sourcedata/AD_TABLE.xml
src/org/openbravo/common/actionhandler/SRMOPickEditLines.java
src/org/openbravo/common/datasource/ReturnFromCustomerHQLInserter.java
src/org/openbravo/common/datasource/ReturnToFromCustomerVendorHQLTransformer.java
--- a/src-db/database/sourcedata/AD_TABLE.xml	Thu Nov 20 17:13:42 2014 +0100
+++ b/src-db/database/sourcedata/AD_TABLE.xml	Thu Nov 20 17:14:20 2014 +0100
@@ -11791,12 +11791,6 @@
 where io.businessPartner.id = :businessPartnerId 
 and io.processed = true and io.documentStatus <> 'VO' 
 and io.salesTransaction = @isSalesTransaction@ 
-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)
 and @insertion_point_0@
 and @additional_filters@]]></HQLQUERY>
 <!--CDB9DC9655F24DF8AB41AA0ADBD04390-->  <ENTITY_ALIAS><![CDATA[iol]]></ENTITY_ALIAS>
--- a/src/org/openbravo/common/actionhandler/SRMOPickEditLines.java	Thu Nov 20 17:13:42 2014 +0100
+++ b/src/org/openbravo/common/actionhandler/SRMOPickEditLines.java	Thu Nov 20 17:14:20 2014 +0100
@@ -182,7 +182,7 @@
         tax = shipmentLine.getSalesOrderLine().getTax();
       } else {
         String taxId = "";
-        if (selectedLine.get("tax").equals(null)) {
+        if (selectedLine.get("tax").equals(null) || selectedLine.get("tax").equals("")) {
           List<Object> parameters = new ArrayList<Object>();
 
           parameters.add(product.getId());
--- a/src/org/openbravo/common/datasource/ReturnFromCustomerHQLInserter.java	Thu Nov 20 17:13:42 2014 +0100
+++ b/src/org/openbravo/common/datasource/ReturnFromCustomerHQLInserter.java	Thu Nov 20 17:14:20 2014 +0100
@@ -41,7 +41,7 @@
     // discounts
     String buttonOwnerViewTabId = requestParameters.get("buttonOwnerViewTabId");
     if (RETURN_FROM_CUSTOMER_TAB_ID.equals(buttonOwnerViewTabId)) {
-      return " iol.salesOrderLine.orderDiscount is null";
+      return " (select iol.salesOrderLine.orderDiscount from MaterialMgmtShipmentInOutLine as e where e.id = iol) is null ";
     } else {
       return null;
     }
--- a/src/org/openbravo/common/datasource/ReturnToFromCustomerVendorHQLTransformer.java	Thu Nov 20 17:13:42 2014 +0100
+++ b/src/org/openbravo/common/datasource/ReturnToFromCustomerVendorHQLTransformer.java	Thu Nov 20 17:14:20 2014 +0100
@@ -33,8 +33,8 @@
 
   private static final String rtv_unitPriceLeftClause = "(case when (select e.salesOrderLine.salesOrder.priceList.priceIncludesTax from ProcurementPOInvoiceMatch as e where e.goodsShipmentLine = iol) = true then  coalesce((select ol.unitPrice from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine = iol), (select e.salesOrderLine.grossUnitPrice from ProcurementPOInvoiceMatch as e where e.goodsShipmentLine = iol)) else   coalesce((select ol.unitPrice from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine = iol), (select e.salesOrderLine.unitPrice from ProcurementPOInvoiceMatch as e where e.goodsShipmentLine = iol)) end)";
   private static final String rtv_orderNoLeftClause = " coalesce ((select e.salesOrderLine.salesOrder.documentNo from ProcurementPOInvoiceMatch as e where e.goodsShipmentLine = iol), '0')";
-  private static final String rfc_unitPriceLeftClause = "(case when (iol.salesOrderLine.salesOrder.priceList.priceIncludesTax) = true then  coalesce((select ol.unitPrice from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine = iol), (iol.salesOrderLine.grossUnitPrice)) else   coalesce((select ol.unitPrice from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine = iol), (iol.salesOrderLine.unitPrice)) end)";
-  private static final String rfc_orderNoLeftClause = " coalesce ((iol.salesOrderLine.salesOrder.documentNo), '0')";
+  private static final String rfc_unitPriceLeftClause = "(case when (iol.salesOrderLine.salesOrder.priceList.priceIncludesTax) = true then  coalesce((select ol.unitPrice from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine = iol), (iol.salesOrderLine.grossUnitPrice)) else   coalesce((select ol.unitPrice from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine = iol), (coalesce(iol.salesOrderLine.unitPrice,''))) end)";
+  private static final String rfc_orderNoLeftClause = " (select e.salesOrderLine.salesOrder.documentNo from MaterialMgmtShipmentInOutLine as e where e.id = iol)";;
   private static final String returnedLeftClause = " coalesce((select ol.orderedQuantity from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine = iol),0)";
   private static final String returnedOthersLeftClause = " 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)";
   private static final String returnReasonLeftClause = " coalesce((select ol.returnReason.id from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine = iol), '')";
@@ -44,15 +44,15 @@
   private static final String grossUnitPriceProperty = "grossUnitPrice";
 
   private static final String rtv_orderNo = "(select e.salesOrderLine.salesOrder.documentNo from ProcurementPOInvoiceMatch as e where e.goodsShipmentLine = iol)";
-  private static final String rfc_orderNo = "iol.salesOrderLine.salesOrder.documentNo";
+  private static final String rfc_orderNo = "(select e.salesOrderLine.salesOrder.documentNo from MaterialMgmtShipmentInOutLine as e where e.id = iol)";
   private static final String rtv_unitPrice = "(select e.salesOrderLine.unitPrice from ProcurementPOInvoiceMatch as e where e.goodsShipmentLine = iol)";
-  private static final String rfc_unitPrice = "iol.salesOrderLine.unitPrice";
+  private static final String rfc_unitPrice = "(select e.salesOrderLine.unitPrice from MaterialMgmtShipmentInOutLine as e where e.id = iol)";
   private static final String rtv_grossUnitPrice = "(select e.salesOrderLine.grossUnitPrice from ProcurementPOInvoiceMatch as e where e.goodsShipmentLine = iol)";
-  private static final String rfc_grossUnitPrice = "iol.salesOrderLine.grossUnitPrice";
+  private static final String rfc_grossUnitPrice = "(select e.salesOrderLine.grossUnitPrice from MaterialMgmtShipmentInOutLine as e where e.id = iol)";
   private static final String rtv_tax = "(select e.salesOrderLine.tax from ProcurementPOInvoiceMatch as e where e.goodsShipmentLine = iol)";
-  private static final String rfc_tax = "iol.salesOrderLine.tax";
+  private static final String rfc_tax = "(select e.salesOrderLine.tax from MaterialMgmtShipmentInOutLine as e where e.id = iol)";
   private static final String rtv_priceIncludeTax = "(select e.salesOrderLine.salesOrder.priceList.priceIncludesTax from ProcurementPOInvoiceMatch as e where e.goodsShipmentLine = iol)";
-  private static final String rfc_priceIncludeTax = "iol.salesOrderLine.salesOrder.priceList.priceIncludesTax";
+  private static final String rfc_priceIncludeTax = "(select e.priceList.priceIncludesTax from Order as e where e.id = :salesOrderId)";
 
   @Override
   public String transformHqlQuery(String hqlQuery, Map<String, String> requestParameters,