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