src-util/modulescript/src/org/openbravo/modulescript/UpdateQtyDelivered_data.xsql
author Nono Carballo <nonofce@gmail.com>
Mon, 22 Apr 2019 15:49:11 -0400
changeset 35881 e7d637ae77a1
parent 32569 3a1554ca086e
permissions -rw-r--r--
Fixes issue 40560: Uses bind parameters in query

When creating the query to get exchange rates by documents, instead of use
string concatenation, bind parameters are uses.
<?xml version="1.0" encoding="UTF-8" ?>
<!--
 *************************************************************************
 * The contents of this file are subject to the Openbravo  Public  License
 * Version  1.1  (the  "License"),  being   the  Mozilla   Public  License
 * Version 1.1  with a permitted attribution clause; you may not  use this
 * file except in compliance with the License. You  may  obtain  a copy of
 * the License at http://www.openbravo.com/legal/license.html
 * Software distributed under the License  is  distributed  on  an "AS IS"
 * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
 * License for the specific  language  governing  rights  and  limitations
 * under the License.
 * The Original Code is Openbravo ERP.
 * The Initial Developer of the Original Code is Openbravo SLU
 * All portions are Copyright (C) 2016-2017 Openbravo SLU
 * All Rights Reserved.
 * Contributor(s):  ______________________________________.
 ************************************************************************
-->
<SqlClass name="UpdateQtyDeliveredData" package="org.openbravo.modulescript">
  <SqlMethod name="select" type="preparedStatement" return="multiple">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
      <![CDATA[
        select 1 as dummy from dual
      ]]>
    </Sql>
  </SqlMethod>
  <SqlMethod name="updateQtyDelivered" type="preparedStatement" return="rowCount">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
     <![CDATA[
        UPDATE c_orderline ol
        SET qtydelivered = COALESCE((SELECT SUM(COALESCE(iol.movementqty, 0))
                                     FROM m_inoutline iol
                                     JOIN m_inout io ON iol.m_inout_id = io.m_inout_id AND io.processed = 'Y'
                                     WHERE iol.c_orderline_id = ol.c_orderline_id
                                     ), 0)
        WHERE EXISTS (SELECT 1
                      FROM c_order o
                      JOIN c_doctype dt ON o.c_doctypetarget_id = dt.c_doctype_id
                      WHERE o.c_order_id = ol.c_order_id
                      AND dt.isreturn = 'Y'
                      AND o.issotrx = 'N'
                      AND o.processed = 'Y'
                     )
        AND ol.qtydelivered = 0
      ]]>
    </Sql>
  </SqlMethod>
  <SqlMethod name="updateIsDelivered" type="preparedStatement" return="rowCount">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
     <![CDATA[
        UPDATE C_Order o
        SET IsDelivered = 'Y'
        WHERE o.IsDelivered = 'N'
        AND o.processed = 'Y'
        AND (o.issotrx = 'Y' OR (o.issotrx = 'N' AND (SELECT dt.isreturn FROM c_doctype dt WHERE o.c_doctypetarget_id = dt.c_doctype_id) = 'Y'))
        AND NOT EXISTS (SELECT 1
                        FROM c_orderline ol
                        WHERE o.c_order_id = ol.c_order_id
                        AND ol.c_order_discount_id IS NULL
                        AND ol.QTYORDERED <> ol.QTYDELIVERED
                       )
      ]]>
    </Sql>
  </SqlMethod>
</SqlClass>