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