src-util/buildvalidation/src/org/openbravo/buildvalidation/GLJournalAccountingCheck_data.xsql
author Alvaro Ferraz <alvaro.ferraz@openbravo.com>
Thu, 26 Mar 2015 11:01:08 +0100
changeset 26270 d21dd3116c92
child 26384 79bf4403631f
permissions -rw-r--r--
Related to issue 29222: Add a build validation to create an alert if wrong data

A build validation will be executed to check if it exists wrong data (g/l journal accounting with wrong amounts) related to the issue. In that case an alert will be created to tell the user to reset accounting, but when fixed it will be deactivated to avoid execute it again.
The build validation will only be executed when updating from Q1 or Q1.1, because the issue is introduced in Q1 and resolved in Q1.2.
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
alvaro@26270
    58
         AND (t1.amtsourcedr <> t2.amtsourcedr
alvaro@26270
    59
         OR t1.amtsourcecr <> t2.amtsourcecr
alvaro@26270
    60
         OR t1.amtacctdr <> t2.amtacctdr
alvaro@26270
    61
         OR t1.amtacctcr <> t2.amtacctcr)
alvaro@26270
    62
      ]]>
alvaro@26270
    63
    </Sql>
alvaro@26270
    64
  </SqlMethod>  
alvaro@26270
    65
  <SqlMethod name="insertAlertRule" type="preparedStatement" return="rowcount">
alvaro@26270
    66
    <SqlMethodComment></SqlMethodComment>
alvaro@26270
    67
    <Sql><![CDATA[
alvaro@26270
    68
      INSERT INTO AD_ALERTRULE (
alvaro@26270
    69
        AD_ALERTRULE_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE,
alvaro@26270
    70
        CREATED, CREATEDBY,  UPDATED, UPDATEDBY,
alvaro@26270
    71
        NAME, AD_TAB_ID, FILTERCLAUSE, TYPE, SQL
alvaro@26270
    72
      ) VALUES (
alvaro@26270
    73
        get_uuid(), ?, '0', 'Y',
alvaro@26270
    74
        now(), '100', now(), '100',
alvaro@26270
    75
        ?, ?, '', 'E', ''
alvaro@26270
    76
      )
alvaro@26270
    77
    ]]></Sql>
alvaro@26270
    78
    <Parameter name="clientId"/>
alvaro@26270
    79
    <Parameter name="name"/>
alvaro@26270
    80
    <Parameter name="tabId"/>
alvaro@26270
    81
  </SqlMethod>
alvaro@26270
    82
  <SqlMethod name="existsAlertRule" type="preparedStatement" return="boolean">
alvaro@26270
    83
    <SqlMethodComment></SqlMethodComment>
alvaro@26270
    84
    <Sql><![CDATA[
alvaro@26270
    85
       SELECT COUNT(*) AS EXISTING
alvaro@26270
    86
       FROM AD_ALERTRULE
alvaro@26270
    87
       WHERE NAME = ?
alvaro@26270
    88
       AND ISACTIVE = 'Y'
alvaro@26270
    89
       AND AD_CLIENT_ID = ?
alvaro@26270
    90
      ]]>
alvaro@26270
    91
    </Sql>
alvaro@26270
    92
    <Parameter name="alertRule"/>
alvaro@26270
    93
    <Parameter name="client"/>
alvaro@26270
    94
  </SqlMethod>
alvaro@26270
    95
  <SqlMethod name="getAlertRuleId" type="preparedStatement" return="string">
alvaro@26270
    96
    <SqlMethodComment></SqlMethodComment>
alvaro@26270
    97
    <Sql><![CDATA[
alvaro@26270
    98
       SELECT MAX(ad_alertrule_id) AS name
alvaro@26270
    99
       FROM AD_ALERTRULE
alvaro@26270
   100
       WHERE NAME LIKE ?
alvaro@26270
   101
       AND AD_CLIENT_ID = ?
alvaro@26270
   102
       AND ISACTIVE = 'Y'
alvaro@26270
   103
      ]]></Sql>
alvaro@26270
   104
    <Parameter name="name"/>
alvaro@26270
   105
    <Parameter name="client"/>
alvaro@26270
   106
  </SqlMethod> 
alvaro@26270
   107
  <SqlMethod name="getWrongGLJournalAccountingDocuments" type="preparedStatement" return="multiple">
alvaro@26270
   108
    <SqlMethodComment></SqlMethodComment>
alvaro@26270
   109
    <Sql><![CDATA[
alvaro@26270
   110
      SELECT DISTINCT t1.gl_journal_id, ad_column_identifier('GL_JOURNAL', t1.gl_journal_id, 'en_US') as recordinfo
alvaro@26270
   111
      FROM gl_journalline t1, fact_acct t2
alvaro@26270
   112
      WHERE t1.gl_journal_id = t2.record_id
alvaro@26270
   113
      AND t1.gl_journalline_id = t2.line_id
alvaro@26270
   114
      AND t1.c_validcombination_id IS NOT NULL
alvaro@26270
   115
      AND t1.ad_client_id = ?
alvaro@26270
   116
      AND (t1.amtsourcedr <> t2.amtsourcedr
alvaro@26270
   117
      OR t1.amtsourcecr <> t2.amtsourcecr
alvaro@26270
   118
      OR t1.amtacctdr <> t2.amtacctdr
alvaro@26270
   119
      OR t1.amtacctcr <> t2.amtacctcr)
alvaro@26270
   120
      ]]>
alvaro@26270
   121
    </Sql>
alvaro@26270
   122
    <Parameter name="client"/>
alvaro@26270
   123
  </SqlMethod>
alvaro@26270
   124
  <SqlMethod name="existsAlert" type="preparedStatement" return="boolean">
alvaro@26270
   125
    <SqlMethodComment></SqlMethodComment>
alvaro@26270
   126
    <Sql><![CDATA[
alvaro@26270
   127
       SELECT COUNT(*) AS EXISTING
alvaro@26270
   128
       FROM AD_ALERT
alvaro@26270
   129
       WHERE AD_ALERTRULE_ID = ?
alvaro@26270
   130
       AND REFERENCEKEY_ID = ?
alvaro@26270
   131
       AND ISFIXED = 'N'
alvaro@26270
   132
      ]]>
alvaro@26270
   133
    </Sql>
alvaro@26270
   134
    <Parameter name="alertRule"/>
alvaro@26270
   135
    <Parameter name="referencekey_id"/>
alvaro@26270
   136
  </SqlMethod>
alvaro@26270
   137
  <SqlMethod name="insertAlert" type="preparedStatement" return="rowcount">
alvaro@26270
   138
    <SqlMethodComment></SqlMethodComment>
alvaro@26270
   139
    <Sql><![CDATA[
alvaro@26270
   140
      INSERT INTO AD_Alert (
alvaro@26270
   141
        AD_Alert_ID, AD_Client_ID, AD_Org_ID, IsActive,
alvaro@26270
   142
        Created, CreatedBy, Updated, UpdatedBy,
alvaro@26270
   143
        Description, AD_AlertRule_ID, AD_Role_ID, Record_Id, Referencekey_ID
alvaro@26270
   144
      ) VALUES (
alvaro@26270
   145
        get_uuid(), ?, '0', 'Y',
alvaro@26270
   146
        NOW(), '0', NOW(), '0',
alvaro@26270
   147
        ?, ?, '0', ?, ?)
alvaro@26270
   148
      ]]>
alvaro@26270
   149
    </Sql>
alvaro@26270
   150
    <Parameter name="client"/>
alvaro@26270
   151
    <Parameter name="description" />
alvaro@26270
   152
    <Parameter name="adAlertRuleId" />
alvaro@26270
   153
    <Parameter name="recordId" />
alvaro@26270
   154
    <Parameter name="referencekey_id" />
alvaro@26270
   155
  </SqlMethod>
alvaro@26270
   156
  <SqlMethod name="createPreference" type="preparedStatement" return="rowcount">
alvaro@26270
   157
    <SqlMethodComment></SqlMethodComment>
alvaro@26270
   158
    <Sql>
alvaro@26270
   159
      <![CDATA[
alvaro@26270
   160
        INSERT INTO ad_preference (
alvaro@26270
   161
          ad_preference_id, ad_client_id, ad_org_id, isactive,
alvaro@26270
   162
          createdby, created, updatedby, updated,
alvaro@26270
   163
          attribute
alvaro@26270
   164
        ) VALUES (
alvaro@26270
   165
          get_uuid(), '0', '0', 'Y',
alvaro@26270
   166
          '0', NOW(), '0', NOW(),
alvaro@26270
   167
          'GLJournalAccountingCheck'
alvaro@26270
   168
        )
alvaro@26270
   169
      ]]>
alvaro@26270
   170
    </Sql>
alvaro@26270
   171
  </SqlMethod>
alvaro@26270
   172
</SqlClass>