src-util/buildvalidation/src/org/openbravo/buildvalidation/GLJournalAccountingCheck_data.xsql
author Eduardo Argal Guibert <eduardo.argal@openbravo.com>
Fri, 15 May 2015 12:01:31 +0200
changeset 26696 a2dd91b010a9
parent 26406 af63dcb16553
child 26688 3eef7954a071
permissions -rw-r--r--
Fixes issue 29899: False positives in GLJournalAccountingCheck validation
Missing ad_table_id constraint ends up in wrong validation when there are old records using numeric values for ids.
alvaro@26270
     1
<?xml version="1.0" encoding="UTF-8" ?>
alvaro@26270
     2
<!--
alvaro@26270
     3
 *************************************************************************
alvaro@26270
     4
 * The contents of this file are subject to the Openbravo  Public  License
alvaro@26270
     5
 * Version  1.1  (the  "License"),  being   the  Mozilla   Public  License
alvaro@26270
     6
 * Version 1.1  with a permitted attribution clause; you may not  use this
alvaro@26270
     7
 * file except in compliance with the License. You  may  obtain  a copy of
alvaro@26270
     8
 * the License at http://www.openbravo.com/legal/license.html
alvaro@26270
     9
 * Software distributed under the License  is  distributed  on  an "AS IS"
alvaro@26270
    10
 * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
alvaro@26270
    11
 * License for the specific  language  governing  rights  and  limitations
alvaro@26270
    12
 * under the License.
alvaro@26270
    13
 * The Original Code is Openbravo ERP.
alvaro@26270
    14
 * The Initial Developer of the Original Code is Openbravo SLU
alvaro@26270
    15
 * All portions are Copyright (C) 2015 Openbravo SLU
alvaro@26270
    16
 * All Rights Reserved.
alvaro@26270
    17
 * Contributor(s):  ______________________________________.
alvaro@26270
    18
 ************************************************************************
alvaro@26270
    19
-->
alvaro@26270
    20
alvaro@26270
    21
<SqlClass name="GLJournalAccountingCheckData" package="org.openbravo.buildvalidation">
alvaro@26270
    22
  <SqlMethod name="select" type="preparedStatement" return="multiple">
alvaro@26270
    23
    <SqlMethodComment></SqlMethodComment>
alvaro@26270
    24
    <Sql><![CDATA[
alvaro@26270
    25
      SELECT '' AS ad_client_id, '' AS ad_role_id, '' as recordinfo, '' AS gl_journal_id
alvaro@26270
    26
      FROM DUAL
alvaro@26270
    27
      ]]>
alvaro@26270
    28
    </Sql>
alvaro@26270
    29
  </SqlMethod>
alvaro@26270
    30
  <SqlMethod name="getModuleVersion" type="preparedStatement" return="string">
alvaro@26270
    31
    <SqlMethodComment></SqlMethodComment>
alvaro@26270
    32
    <Sql>
alvaro@26270
    33
      <![CDATA[
alvaro@26270
    34
        SELECT version
alvaro@26270
    35
        FROM ad_module
alvaro@26270
    36
        WHERE ad_module_id = '0'        
alvaro@26270
    37
      ]]>
alvaro@26270
    38
    </Sql>
alvaro@26270
    39
  </SqlMethod>
alvaro@26270
    40
  <SqlMethod name="hasPreference" type="preparedStatement" return="boolean">
alvaro@26270
    41
    <SqlMethodComment></SqlMethodComment>
alvaro@26270
    42
    <Sql>
alvaro@26270
    43
      <![CDATA[
alvaro@26270
    44
        SELECT count(*) as existpreference
alvaro@26270
    45
        FROM ad_preference
alvaro@26270
    46
        WHERE attribute = 'GLJournalAccountingCheck'        
alvaro@26270
    47
      ]]>
alvaro@26270
    48
    </Sql>
alvaro@26270
    49
  </SqlMethod>
alvaro@26270
    50
  <SqlMethod name="getWrongGLJournalAccountingClients" type="preparedStatement" return="multiple">
alvaro@26270
    51
    <SqlMethodComment></SqlMethodComment>
alvaro@26270
    52
    <Sql><![CDATA[
alvaro@26270
    53
         SELECT DISTINCT t1.ad_client_id
alvaro@26270
    54
         FROM gl_journalline t1, fact_acct t2
alvaro@26270
    55
         WHERE t1.gl_journal_id = t2.record_id
alvaro@26270
    56
         AND t1.gl_journalline_id = t2.line_id
alvaro@26270
    57
         AND t1.c_validcombination_id IS NOT NULL
eduardo@26696
    58
         AND AD_Table_ID = '224'
alvaro@26406
    59
         AND ((t1.amtsourcedr - t1.amtsourcecr) <> (t2.amtsourcedr - t2.amtsourcecr)
alvaro@26406
    60
         OR  (t1.amtacctdr - t1.amtacctcr) <> (t2.amtacctdr - t2.amtacctcr))
alvaro@26270
    61
      ]]>
alvaro@26270
    62
    </Sql>
alvaro@26270
    63
  </SqlMethod>  
alvaro@26270
    64
  <SqlMethod name="insertAlertRule" type="preparedStatement" return="rowcount">
alvaro@26270
    65
    <SqlMethodComment></SqlMethodComment>
alvaro@26270
    66
    <Sql><![CDATA[
alvaro@26270
    67
      INSERT INTO AD_ALERTRULE (
alvaro@26270
    68
        AD_ALERTRULE_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE,
alvaro@26270
    69
        CREATED, CREATEDBY,  UPDATED, UPDATEDBY,
alvaro@26270
    70
        NAME, AD_TAB_ID, FILTERCLAUSE, TYPE, SQL
alvaro@26270
    71
      ) VALUES (
alvaro@26270
    72
        get_uuid(), ?, '0', 'Y',
alvaro@26270
    73
        now(), '100', now(), '100',
alvaro@26270
    74
        ?, ?, '', 'E', ''
alvaro@26270
    75
      )
alvaro@26270
    76
    ]]></Sql>
alvaro@26270
    77
    <Parameter name="clientId"/>
alvaro@26270
    78
    <Parameter name="name"/>
alvaro@26270
    79
    <Parameter name="tabId"/>
alvaro@26270
    80
  </SqlMethod>
alvaro@26270
    81
  <SqlMethod name="existsAlertRule" type="preparedStatement" return="boolean">
alvaro@26270
    82
    <SqlMethodComment></SqlMethodComment>
alvaro@26270
    83
    <Sql><![CDATA[
alvaro@26270
    84
       SELECT COUNT(*) AS EXISTING
alvaro@26270
    85
       FROM AD_ALERTRULE
alvaro@26270
    86
       WHERE NAME = ?
alvaro@26270
    87
       AND ISACTIVE = 'Y'
alvaro@26270
    88
       AND AD_CLIENT_ID = ?
alvaro@26270
    89
      ]]>
alvaro@26270
    90
    </Sql>
alvaro@26270
    91
    <Parameter name="alertRule"/>
alvaro@26270
    92
    <Parameter name="client"/>
alvaro@26270
    93
  </SqlMethod>
alvaro@26270
    94
  <SqlMethod name="getAlertRuleId" type="preparedStatement" return="string">
alvaro@26270
    95
    <SqlMethodComment></SqlMethodComment>
alvaro@26270
    96
    <Sql><![CDATA[
alvaro@26270
    97
       SELECT MAX(ad_alertrule_id) AS name
alvaro@26270
    98
       FROM AD_ALERTRULE
alvaro@26270
    99
       WHERE NAME LIKE ?
alvaro@26270
   100
       AND AD_CLIENT_ID = ?
alvaro@26270
   101
       AND ISACTIVE = 'Y'
alvaro@26270
   102
      ]]></Sql>
alvaro@26270
   103
    <Parameter name="name"/>
alvaro@26270
   104
    <Parameter name="client"/>
alvaro@26270
   105
  </SqlMethod> 
alvaro@26270
   106
  <SqlMethod name="getWrongGLJournalAccountingDocuments" type="preparedStatement" return="multiple">
alvaro@26270
   107
    <SqlMethodComment></SqlMethodComment>
alvaro@26270
   108
    <Sql><![CDATA[
alvaro@26270
   109
      SELECT DISTINCT t1.gl_journal_id, ad_column_identifier('GL_JOURNAL', t1.gl_journal_id, 'en_US') as recordinfo
alvaro@26270
   110
      FROM gl_journalline t1, fact_acct t2
alvaro@26270
   111
      WHERE t1.gl_journal_id = t2.record_id
alvaro@26270
   112
      AND t1.gl_journalline_id = t2.line_id
alvaro@26270
   113
      AND t1.c_validcombination_id IS NOT NULL
alvaro@26270
   114
      AND t1.ad_client_id = ?
eduardo@26696
   115
      AND AD_Table_ID = '224'
alvaro@26406
   116
      AND ((t1.amtsourcedr - t1.amtsourcecr) <> (t2.amtsourcedr - t2.amtsourcecr)
alvaro@26406
   117
      OR  (t1.amtacctdr - t1.amtacctcr) <> (t2.amtacctdr - t2.amtacctcr))
alvaro@26270
   118
      ]]>
alvaro@26270
   119
    </Sql>
alvaro@26270
   120
    <Parameter name="client"/>
alvaro@26270
   121
  </SqlMethod>
alvaro@26270
   122
  <SqlMethod name="existsAlert" type="preparedStatement" return="boolean">
alvaro@26270
   123
    <SqlMethodComment></SqlMethodComment>
alvaro@26270
   124
    <Sql><![CDATA[
alvaro@26270
   125
       SELECT COUNT(*) AS EXISTING
alvaro@26270
   126
       FROM AD_ALERT
alvaro@26270
   127
       WHERE AD_ALERTRULE_ID = ?
alvaro@26270
   128
       AND REFERENCEKEY_ID = ?
alvaro@26270
   129
       AND ISFIXED = 'N'
alvaro@26270
   130
      ]]>
alvaro@26270
   131
    </Sql>
alvaro@26270
   132
    <Parameter name="alertRule"/>
alvaro@26270
   133
    <Parameter name="referencekey_id"/>
alvaro@26270
   134
  </SqlMethod>
alvaro@26270
   135
  <SqlMethod name="insertAlert" type="preparedStatement" return="rowcount">
alvaro@26270
   136
    <SqlMethodComment></SqlMethodComment>
alvaro@26270
   137
    <Sql><![CDATA[
alvaro@26270
   138
      INSERT INTO AD_Alert (
alvaro@26270
   139
        AD_Alert_ID, AD_Client_ID, AD_Org_ID, IsActive,
alvaro@26270
   140
        Created, CreatedBy, Updated, UpdatedBy,
alvaro@26270
   141
        Description, AD_AlertRule_ID, AD_Role_ID, Record_Id, Referencekey_ID
alvaro@26270
   142
      ) VALUES (
alvaro@26270
   143
        get_uuid(), ?, '0', 'Y',
alvaro@26270
   144
        NOW(), '0', NOW(), '0',
alvaro@26270
   145
        ?, ?, '0', ?, ?)
alvaro@26270
   146
      ]]>
alvaro@26270
   147
    </Sql>
alvaro@26270
   148
    <Parameter name="client"/>
alvaro@26270
   149
    <Parameter name="description" />
alvaro@26270
   150
    <Parameter name="adAlertRuleId" />
alvaro@26270
   151
    <Parameter name="recordId" />
alvaro@26270
   152
    <Parameter name="referencekey_id" />
alvaro@26270
   153
  </SqlMethod>
alvaro@26270
   154
  <SqlMethod name="createPreference" type="preparedStatement" return="rowcount">
alvaro@26270
   155
    <SqlMethodComment></SqlMethodComment>
alvaro@26270
   156
    <Sql>
alvaro@26270
   157
      <![CDATA[
alvaro@26270
   158
        INSERT INTO ad_preference (
alvaro@26270
   159
          ad_preference_id, ad_client_id, ad_org_id, isactive,
alvaro@26270
   160
          createdby, created, updatedby, updated,
alvaro@26270
   161
          attribute
alvaro@26270
   162
        ) VALUES (
alvaro@26270
   163
          get_uuid(), '0', '0', 'Y',
alvaro@26270
   164
          '0', NOW(), '0', NOW(),
alvaro@26270
   165
          'GLJournalAccountingCheck'
alvaro@26270
   166
        )
alvaro@26270
   167
      ]]>
alvaro@26270
   168
    </Sql>
alvaro@26270
   169
  </SqlMethod>
eduardo@26696
   170
</SqlClass>