modules/org.openbravo.advpaymentmngt/src-util/modulescript/src/org/openbravo/advpaymentmngt/modulescript/UpdateGeneratedUsedCreditColumns_Data.xsql
changeset 9076 c4f1a5c7d7c2
child 10776 af843b0eb622
equal deleted inserted replaced
9075:b3ea04de2722 9076:c4f1a5c7d7c2
       
     1 <?xml version="1.0" encoding="UTF-8" ?>
       
     2 <!--
       
     3  *************************************************************************
       
     4  * The contents of this file are subject to the Openbravo  Public  License
       
     5  * Version  1.0  (the  "License"),  being   the  Mozilla   Public  License
       
     6  * Version 1.1  with a permitted attribution clause; you may not  use this
       
     7  * file except in compliance with the License. You  may  obtain  a copy of
       
     8  * the License at http://www.openbravo.com/legal/license.html
       
     9  * Software distributed under the License  is  distributed  on  an "AS IS"
       
    10  * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
       
    11  * License for the specific  language  governing  rights  and  limitations
       
    12  * under the License.
       
    13  * The Original Code is Openbravo ERP.
       
    14  * The Initial Developer of the Original Code is Openbravo SLU
       
    15  * All portions are Copyright (C) 2010 Openbravo SLU
       
    16  * All Rights Reserved.
       
    17  * Contributor(s):  ______________________________________.
       
    18  *************************************************************************
       
    19 -->
       
    20 <SqlClass name="UpdateGeneratedUsedCreditColumnsData" package="org.openbravo.advpaymentmngt.modulescript">
       
    21   <SqlClassComment></SqlClassComment>
       
    22   <SqlMethod name="select" type="preparedStatement" connection="true" return="multiple">
       
    23     <SqlMethodComment></SqlMethodComment>
       
    24     <Sql>
       
    25       <![CDATA[
       
    26       SELECT 1 AS row_count, '' AS TYPE
       
    27       FROM DUAL
       
    28       ]]>
       
    29     </Sql>
       
    30   </SqlMethod>
       
    31   <SqlMethod name="hasGeneratedCreditToUpdate" type="preparedStatement" connection="true" return="boolean">
       
    32     <SqlMethodComment></SqlMethodComment>
       
    33     <Sql>
       
    34       <![CDATA[
       
    35         SELECT count(*) AS TYPE
       
    36         FROM DUAL
       
    37         WHERE EXISTS (SELECT 1
       
    38                       FROM fin_payment p, fin_payment_detail pd, fin_payment_scheduledetail psd
       
    39                       WHERE p.fin_payment_id = pd.fin_payment_id
       
    40                         AND pd.fin_payment_detail_id = psd.fin_payment_detail_id
       
    41                         AND psd.fin_payment_schedule_invoice is null
       
    42                         AND psd.fin_payment_schedule_order is null
       
    43                         AND pd.c_glitem_id is null
       
    44                         AND p.generated_credit = 0
       
    45                         AND pd.refund='N')
       
    46       ]]>
       
    47     </Sql>
       
    48   </SqlMethod>
       
    49   <SqlMethod name="hasRefundToUpdate" type="preparedStatement" connection="true" return="boolean">
       
    50     <SqlMethodComment></SqlMethodComment>
       
    51     <Sql>
       
    52       <![CDATA[
       
    53         SELECT count(*) AS TYPE
       
    54         FROM DUAL
       
    55         WHERE EXISTS (SELECT 1
       
    56                       FROM fin_payment p, fin_payment_detail pd
       
    57                       WHERE p.fin_payment_id = pd.fin_payment_id
       
    58                         AND pd.refund = 'Y'
       
    59                         AND pd.amount > 0
       
    60                         AND p.used_credit = 0)
       
    61       ]]>
       
    62     </Sql>
       
    63   </SqlMethod>
       
    64   <SqlMethod name="updateRefund" type="preparedStatement" connection="true" return="rowCount">
       
    65     <SqlMethodComment></SqlMethodComment>
       
    66     <Sql>
       
    67       <![CDATA[
       
    68       UPDATE fin_payment
       
    69       SET used_credit = (SELECT ABS(SUM(pd.amount))
       
    70                     FROM fin_payment_detail pd
       
    71                     WHERE pd.fin_payment_id = fin_payment.fin_payment_id
       
    72                     AND pd.refund = 'Y')
       
    73       WHERE EXISTS (SELECT 1
       
    74                     FROM fin_payment_detail pd
       
    75                     WHERE pd.fin_payment_id = fin_payment.fin_payment_id
       
    76                       AND pd.refund = 'Y')
       
    77       ]]>
       
    78     </Sql>
       
    79   </SqlMethod>
       
    80   <SqlMethod name="updateRefundPrepayment" type="preparedStatement" connection="true" return="rowCount">
       
    81     <SqlMethodComment></SqlMethodComment>
       
    82     <Sql>
       
    83       <![CDATA[
       
    84       UPDATE fin_payment_detail
       
    85       SET isprepayment = 'Y'
       
    86       WHERE refund= 'Y'
       
    87       ]]>
       
    88     </Sql>
       
    89   </SqlMethod>
       
    90   <SqlMethod name="updateRefundDetail" type="preparedStatement" connection="true" return="rowCount">
       
    91     <SqlMethodComment></SqlMethodComment>
       
    92     <Sql>
       
    93       <![CDATA[
       
    94       UPDATE fin_payment_detail
       
    95       SET refund = 'N'
       
    96       WHERE refund= 'Y'
       
    97         AND amount > 0
       
    98       ]]>
       
    99     </Sql>
       
   100   </SqlMethod>
       
   101   <SqlMethod name="updateGeneratedCredit" type="preparedStatement" connection="true" return="rowCount">
       
   102     <SqlMethodComment></SqlMethodComment>
       
   103     <Sql>
       
   104       <![CDATA[
       
   105       UPDATE fin_payment
       
   106       SET generated_credit = (SELECT SUM(psd.amount)
       
   107                     FROM fin_payment_detail pd, fin_payment_scheduledetail psd
       
   108                     WHERE pd.fin_payment_id = fin_payment.fin_payment_id
       
   109                       AND pd.fin_payment_detail_id = psd.fin_payment_detail_id
       
   110                       AND psd.fin_payment_schedule_order is null
       
   111                       AND psd.fin_payment_schedule_invoice is null
       
   112                       AND pd.c_glitem_id is null
       
   113                       AND pd.refund = 'N')
       
   114       WHERE EXISTS (SELECT 1
       
   115                     FROM fin_payment_detail pd, fin_payment_scheduledetail psd
       
   116                     WHERE pd.fin_payment_id = fin_payment.fin_payment_id
       
   117                       AND pd.fin_payment_detail_id = psd.fin_payment_detail_id
       
   118                       AND psd.fin_payment_schedule_order is null
       
   119                       AND psd.fin_payment_schedule_invoice is null
       
   120                       AND pd.c_glitem_id is null
       
   121                       AND pd.refund = 'N')
       
   122         AND generated_credit = 0
       
   123       ]]>
       
   124     </Sql>
       
   125   </SqlMethod>
       
   126   <SqlMethod name="initializeGenerated" type="preparedStatement" connection="true" return="rowCount">
       
   127     <SqlMethodComment></SqlMethodComment>
       
   128     <Sql>
       
   129       <![CDATA[
       
   130       UPDATE fin_payment
       
   131       SET generated_credit = 0
       
   132       WHERE generated_credit IS NULL
       
   133       ]]>
       
   134     </Sql>
       
   135   </SqlMethod>
       
   136   <SqlMethod name="initializeUsed" type="preparedStatement" connection="true" return="rowCount">
       
   137     <SqlMethodComment></SqlMethodComment>
       
   138     <Sql>
       
   139       <![CDATA[
       
   140       UPDATE fin_payment
       
   141       SET used_credit = 0
       
   142       WHERE used_credit IS NULL
       
   143       ]]>
       
   144     </Sql>
       
   145   </SqlMethod>
       
   146 </SqlClass>