src/org/openbravo/erpCommon/businessUtility/AccountTree_data.xsql
author Víctor Martínez Romanos <victor.martinez@openbravo.com>
Tue, 09 Aug 2016 09:50:55 +0200
changeset 30081 72acd4fea07e
parent 30080 87c9f038283f
child 30082 036a261e3b6f
permissions -rw-r--r--
Fixed issue 33460: code review improvements

Avoid NullPointerException in non-compartive mode
In JasperReport:
* Band height for details restored to 15, so we have the same number of lines as before the change
* Period N-1 label only shown in comparative mode
carlos@0
     1
<?xml version="1.0" encoding="UTF-8" ?>
carlos@0
     2
<!--
carlos@0
     3
 ******************************************************************************
carlos@0
     4
 * The contents of this file are subject to the   Compiere License  Version 1.1
carlos@0
     5
 * ("License"); You may not use this file except in compliance with the License
carlos@0
     6
 * You may obtain a copy of the License at http://www.compiere.org/license.html
carlos@0
     7
 * Software distributed under the License is distributed on an  "AS IS"  basis,
carlos@0
     8
 * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License for
carlos@0
     9
 * the specific language governing rights and limitations under the License.
carlos@0
    10
 * The Original Code is                  Compiere  ERP & CRM  Business Solution
carlos@0
    11
 * The Initial Developer of the Original Code is Jorg Janke  and ComPiere, Inc.
carlos@0
    12
 * Portions created by Jorg Janke are Copyright (C) 1999-2001 Jorg Janke, parts
carlos@0
    13
 * created by ComPiere are Copyright (C) ComPiere, Inc.;   All Rights Reserved.
ggi@6701
    14
 * Contributor(s): Openbravo SLU
victor@30081
    15
 * Contributions are Copyright (C) 2001-2016 Openbravo S.L.U.
carlos@0
    16
 ******************************************************************************
carlos@0
    17
-->
carlos@0
    18
carlos@0
    19
carlos@0
    20
carlos@0
    21
carlos@0
    22
stefan@3932
    23
<SqlClass name="AccountTreeData" package="org.openbravo.erpCommon.businessUtility" accessModifier="public">
carlos@0
    24
  <SqlMethod name="select" type="preparedStatement" return="multiple">
carlos@0
    25
    <SqlMethodComment></SqlMethodComment>
carlos@0
    26
      <Sql><![CDATA[
eduardo@19657
    27
        SELECT tn.Node_ID,tn.Parent_ID,tn.SeqNo, m.C_ElementValue_ID AS ID, 
eduardo@19657
    28
        ((CASE ? WHEN 'Y' THEN TO_CHAR(m.Value) || ' - ' ELSE '' END) || m.Name) AS NAME,m.Description, m.IsSummary, 
carlos@0
    29
        m.ACCOUNTSIGN, m.ShowElement, 0 as element_level, 0 as qty, 0 as qty_ref, 0 as qty_operation, 0 as qty_operation_ref, 
eduardo@19657
    30
        0 as QTYCREDIT, 0 as QTYCREDIT_REF, m.ShowValueCond, m.ElementLevel, m.Value, 
eduardo@19657
    31
        'N' AS CALCULATED, 'N' AS SVCRESET, 'N' AS SVCRESETREF, m.isalwaysshown, '' as sign
carlos@0
    32
        FROM AD_TreeNode tn, C_ElementValue m
carlos@0
    33
        WHERE tn.IsActive='Y' 
carlos@0
    34
        AND tn.Node_ID = m.C_ElementValue_ID 
juanpablo@1605
    35
        AND tn.AD_Tree_ID = ? 
juanpablo@1605
    36
        ORDER BY COALESCE(tn.Parent_ID, '-1'), tn.SeqNo
carlos@0
    37
      ]]></Sql>
carlos@0
    38
        <Parameter name="conCodigo"/>
carlos@0
    39
        <Parameter name="adTreeId"/>
carlos@0
    40
  </SqlMethod>
carlos@0
    41
  <SqlMethod name="selectTrl" type="preparedStatement" return="multiple">
carlos@0
    42
    <SqlMethodComment></SqlMethodComment>
carlos@0
    43
      <Sql><![CDATA[
carlos@0
    44
        SELECT tn.Node_ID,tn.Parent_ID,tn.SeqNo, m.C_ElementValue_ID AS ID, ((CASE ? WHEN 'Y' THEN TO_CHAR(m.Value) || ' - ' ELSE '' END) || COALESCE(mt.Name, m.Name)) as Name, 
carlos@0
    45
        COALESCE(mt.Description, m.Description) as description ,m.IsSummary, m.ACCOUNTSIGN, 
carlos@0
    46
        m.ShowElement, 0 as element_level, 0 as qty, 0 as qty_ref, 0 as qty_operation, 0 as qty_operation_ref, 
david@11300
    47
        m.ShowValueCond, m.ElementLevel, m.Value, 'N' AS CALCULATED, 'N' AS SVCRESET, 'N' AS SVCRESETREF, m.isalwaysshown
carlos@0
    48
        FROM C_ElementValue m left join C_ElementValue_Trl mt on m.C_ElementValue_ID = mt.C_ElementValue_ID 
carlos@0
    49
                                                              and mt.AD_Language = ? ,
carlos@0
    50
              AD_TreeNode tn
carlos@0
    51
        WHERE tn.IsActive='Y' 
carlos@0
    52
        AND tn.Node_ID = m.C_ElementValue_ID 
juanpablo@1605
    53
        AND tn.AD_Tree_ID = ? 
juanpablo@1605
    54
        ORDER BY COALESCE(tn.Parent_ID, '-1'), tn.SeqNo 
carlos@0
    55
      ]]></Sql>
carlos@0
    56
        <Parameter name="conCodigo"/>
carlos@0
    57
        <Parameter name="adLanguage"/>
carlos@0
    58
        <Parameter name="adTreeId"/>        
carlos@0
    59
  </SqlMethod>
carlos@0
    60
  <SqlMethod name="selectAcct" type="preparedStatement" return="multiple">
carlos@0
    61
    <SqlMethodComment></SqlMethodComment>
carlos@0
    62
      <Sql><![CDATA[
carlos@0
    63
        SELECT ID, SUM(QTY) AS QTY, SUM(QTYCREDIT) AS QTYCREDIT, SUM(QTY_REF) AS QTY_REF, SUM(QTYCREDIT_REF) AS QTYCREDIT_REF 
carlos@0
    64
        FROM (
carlos@0
    65
        SELECT m.C_ElementValue_ID as id, (COALESCE(f.AMTACCTDR,0) - COALESCE(f.AMTACCTCR, 0)) AS qty, 
carlos@0
    66
        (COALESCE(f.AMTACCTCR,0) - COALESCE(f.AMTACCTDR, 0)) AS qtyCredit, 0 as qty_ref, 0 as qtyCredit_ref
carlos@0
    67
                FROM C_ElementValue m, Fact_Acct f, C_Period p, C_Year y 
ioritz@18389
    68
                WHERE m.AD_Org_ID IN ('1') 
staff@21270
    69
                AND m.AD_Client_ID IN ('1')
carlos@0
    70
                AND 1=1 
carlos@0
    71
                AND f.FACTACCTTYPE <> 'R'
carlos@0
    72
                AND f.FACTACCTTYPE <> 'C'
carlos@0
    73
                AND m.C_ElementValue_ID = f.Account_ID
carlos@0
    74
            AND 0=0 AND f.AD_ORG_ID IN ('1')
carlos@0
    75
            AND f.C_PERIOD_ID = p.C_PERIOD_ID 
carlos@0
    76
            AND p.C_YEAR_ID = y.C_YEAR_ID
david@3989
    77
            AND Y.YEAR IN ('1') 
carlos@0
    78
        UNION ALL
carlos@0
    79
        SELECT m.C_ElementValue_ID as id, 0 AS QTY, 0 as qtyCredit, (COALESCE(f.AMTACCTDR,0) - COALESCE(f.AMTACCTCR, 0)) AS qty_ref, 
carlos@0
    80
                (COALESCE(f.AMTACCTCR,0) - COALESCE(f.AMTACCTDR, 0)) AS qtyCredit_ref 
carlos@0
    81
                FROM C_ElementValue m, Fact_Acct f, C_Period p, C_Year y 
ioritz@18389
    82
                WHERE m.AD_Org_ID IN('2') 
carlos@0
    83
                AND m.AD_Client_ID IN('2') 
carlos@0
    84
                AND 2=2 
carlos@0
    85
                AND f.FACTACCTTYPE <> 'R'
carlos@0
    86
                AND f.FACTACCTTYPE <> 'C'
carlos@0
    87
                AND m.C_ElementValue_ID = f.Account_ID
carlos@0
    88
            AND 1=1 AND f.AD_ORG_ID IN ('1')
carlos@0
    89
            AND f.C_PERIOD_ID = p.C_PERIOD_ID 
carlos@0
    90
            AND p.C_YEAR_ID = y.C_YEAR_ID
david@3989
    91
            AND y.YEAR IN ('1') 
carlos@0
    92
        ) AA
carlos@0
    93
        GROUP BY ID
carlos@0
    94
      ]]></Sql>
carlos@0
    95
        <Parameter name="adOrgClient" type="replace" optional="true" after="m.AD_Org_ID IN (" text="'1'"/>
carlos@0
    96
        <Parameter name="adUserClient" type="replace" optional="true" after="m.AD_Client_ID IN (" text="'1'"/>
carlos@0
    97
        <Parameter name="dateFrom" optional="true" after="AND 1=1 "><![CDATA[ AND f.DATEACCT >= to_date(?)]]></Parameter>
carlos@0
    98
        <Parameter name="dateTo" optional="true" after="AND 1=1 "><![CDATA[ AND f.DATEACCT < to_date(?)]]></Parameter>
juanpablo@1605
    99
        <Parameter name="acctschema" optional="true" after="AND 1=1 "><![CDATA[ AND f.C_ACCTSCHEMA_ID = ?]]></Parameter>
carlos@0
   100
        <Parameter name="org" optional="true" type="replace" after="AND 0=0 AND f.AD_ORG_ID IN (" text="'1'"/>
david@3989
   101
        <Parameter name="agno" optional="true" type="replace" after="AND Y.YEAR IN (" text="'1'"/>
carlos@0
   102
        <Parameter name="adOrgClient" type="replace" optional="true" after="m.AD_Org_ID IN(" text="'2'"/>
carlos@0
   103
        <Parameter name="adUserClient" type="replace" optional="true" after="m.AD_Client_ID IN(" text="'2'"/>
carlos@0
   104
        <Parameter name="dateFromRef" optional="true" after="AND 2=2 "><![CDATA[ AND f.DATEACCT >= to_date(?)]]></Parameter>
carlos@0
   105
        <Parameter name="dateToRef" optional="true" after="AND 2=2 "><![CDATA[ AND f.DATEACCT < to_date(?)]]></Parameter>
juanpablo@1605
   106
        <Parameter name="acctschema" optional="true" after="AND 2=2 "><![CDATA[ AND f.C_ACCTSCHEMA_ID = ?]]></Parameter>
carlos@0
   107
        <Parameter name="org" optional="true" type="replace" after="AND 1=1 AND f.AD_ORG_ID IN (" text="'1'"/>
david@3989
   108
        <Parameter name="agnoRef" optional="true" type="replace" after="AND y.YEAR IN (" text="'1'"/>
carlos@0
   109
  </SqlMethod>
staff@21270
   110
  <SqlMethod name="selectFactAcct" type="preparedStatement" return="multiple">
staff@21270
   111
    <SqlMethodComment></SqlMethodComment>
staff@21270
   112
      <Sql><![CDATA[
staff@21270
   113
        SELECT ID, SUM(QTY) AS QTY, SUM(QTYCREDIT) AS QTYCREDIT, SUM(QTY_REF) AS QTY_REF, SUM(QTYCREDIT_REF) AS QTYCREDIT_REF 
staff@21270
   114
        FROM (
staff@21270
   115
        SELECT m.C_ElementValue_ID as id, (COALESCE(f.AMTACCTDR,0) - COALESCE(f.AMTACCTCR, 0)) AS qty, 
staff@21270
   116
        (COALESCE(f.AMTACCTCR,0) - COALESCE(f.AMTACCTDR, 0)) AS qtyCredit, 0 as qty_ref, 0 as qtyCredit_ref
staff@21270
   117
                FROM C_ElementValue m, Fact_Acct f, C_Period p, C_Year y 
staff@21270
   118
                WHERE m.AD_Org_ID IN ('1') 
staff@21270
   119
                AND m.AD_Client_ID IN ('1')
staff@21270
   120
                AND 1=1 
staff@21270
   121
                AND f.FACTACCTTYPE <> 'R'
staff@21270
   122
                AND f.FACTACCTTYPE <> 'C'
staff@21270
   123
                AND m.C_ElementValue_ID = f.Account_ID
staff@21270
   124
            AND 0=0 AND f.AD_ORG_ID IN ('1')
staff@21270
   125
            AND f.C_PERIOD_ID = p.C_PERIOD_ID 
staff@21270
   126
            AND p.C_YEAR_ID = y.C_YEAR_ID
staff@21270
   127
            AND Y.YEAR IN ('1') 
staff@21270
   128
            and not exists (select 1 from ad_org_closing, c_year year 
staff@21270
   129
                           where open_fact_acct_group_id = f.fact_acct_group_id
staff@21270
   130
                           and ad_org_closing.c_year_id = year.c_year_id
staff@21270
   131
                           and year.year <> ?)
staff@21270
   132
        UNION ALL
staff@21270
   133
        SELECT m.C_ElementValue_ID as id, 0 AS QTY, 0 as qtyCredit, (COALESCE(f.AMTACCTDR,0) - COALESCE(f.AMTACCTCR, 0)) AS qty_ref, 
staff@21270
   134
                (COALESCE(f.AMTACCTCR,0) - COALESCE(f.AMTACCTDR, 0)) AS qtyCredit_ref 
staff@21270
   135
                FROM C_ElementValue m, Fact_Acct f, C_Period p, C_Year y 
staff@21270
   136
                WHERE m.AD_Org_ID IN('2') 
sanjota@30080
   137
                AND m.AD_Client_ID IN('2')
sanjota@30080
   138
                AND (1=2 )  
staff@21270
   139
                AND 2=2 
staff@21270
   140
                AND f.FACTACCTTYPE <> 'R'
staff@21270
   141
                AND f.FACTACCTTYPE <> 'C'
staff@21270
   142
                AND m.C_ElementValue_ID = f.Account_ID
staff@21270
   143
            AND 1=1 AND f.AD_ORG_ID IN ('1')
staff@21270
   144
            AND f.C_PERIOD_ID = p.C_PERIOD_ID 
staff@21270
   145
            AND p.C_YEAR_ID = y.C_YEAR_ID
staff@21270
   146
            AND y.YEAR IN ('1') 
staff@21270
   147
            and not exists (select 1 from ad_org_closing, c_year year 
staff@21270
   148
                           where open_fact_acct_group_id = f.fact_acct_group_id
staff@21270
   149
                           and ad_org_closing.c_year_id = year.c_year_id
staff@21270
   150
                           and year.year <> ?)
staff@21270
   151
        ) AA
staff@21270
   152
        GROUP BY ID
staff@21270
   153
      ]]></Sql>
staff@21270
   154
        <Parameter name="adOrgClient" type="replace" optional="true" after="m.AD_Org_ID IN (" text="'1'"/>
staff@21270
   155
        <Parameter name="adUserClient" type="replace" optional="true" after="m.AD_Client_ID IN (" text="'1'"/>
staff@21270
   156
        <Parameter name="dateFrom" optional="true" after="AND 1=1 "><![CDATA[ AND f.DATEACCT >= to_date(?)]]></Parameter>
staff@21270
   157
        <Parameter name="dateTo" optional="true" after="AND 1=1 "><![CDATA[ AND f.DATEACCT < to_date(?)]]></Parameter>
staff@21270
   158
        <Parameter name="acctschema" optional="true" after="AND 1=1 "><![CDATA[ AND f.C_ACCTSCHEMA_ID = ?]]></Parameter>
staff@21270
   159
        <Parameter name="org" optional="true" type="replace" after="AND 0=0 AND f.AD_ORG_ID IN (" text="'1'"/>
staff@21270
   160
        <Parameter name="agno" optional="true" type="replace" after="AND Y.YEAR IN (" text="'1'"/>
staff@21270
   161
        <Parameter name="openingEntryOwner"/>
staff@21270
   162
        <Parameter name="adOrgClient" type="replace" optional="true" after="m.AD_Org_ID IN(" text="'2'"/>
staff@21270
   163
        <Parameter name="adUserClient" type="replace" optional="true" after="m.AD_Client_ID IN(" text="'2'"/>
sanjota@30080
   164
        <Parameter name="compareTo" optional="true" after="AND (1=2 "><![CDATA[ OR to_char('Y')=to_char(?)]]></Parameter>
staff@21270
   165
        <Parameter name="dateFromRef" optional="true" after="AND 2=2 "><![CDATA[ AND f.DATEACCT >= to_date(?)]]></Parameter>
staff@21270
   166
        <Parameter name="dateToRef" optional="true" after="AND 2=2 "><![CDATA[ AND f.DATEACCT < to_date(?)]]></Parameter>
staff@21270
   167
        <Parameter name="acctschema" optional="true" after="AND 2=2 "><![CDATA[ AND f.C_ACCTSCHEMA_ID = ?]]></Parameter>
staff@21270
   168
        <Parameter name="org" optional="true" type="replace" after="AND 1=1 AND f.AD_ORG_ID IN (" text="'1'"/>
staff@21270
   169
        <Parameter name="agnoRef" optional="true" type="replace" after="AND y.YEAR IN (" text="'1'"/>
staff@21270
   170
        <Parameter name="openingEntryOwnerRef"/>
staff@21270
   171
  </SqlMethod>  
carlos@0
   172
  <SqlMethod name="selectForms" type="preparedStatement" return="multiple">
carlos@0
   173
    <SqlMethodComment></SqlMethodComment>
carlos@0
   174
      <Sql><![CDATA[
carlos@0
   175
        SELECT m.C_ElementValue_ID as id, o.account_id as node_id, o.sign as ACCOUNTSIGN
carlos@0
   176
        FROM C_ElementValue m, C_ELEMENTVALUE_OPERAND o  
carlos@0
   177
        WHERE m.isActive='Y' 
carlos@0
   178
        AND m.AD_Org_ID IN('1') 
carlos@0
   179
        AND m.AD_Client_ID IN('1') 
carlos@0
   180
        AND m.C_ElementValue_ID = o.C_ElementValue_ID
carlos@0
   181
        AND o.isactive = 'Y' 
carlos@0
   182
        order by m.C_elementvalue_id, o.seqno
carlos@0
   183
      ]]></Sql>
carlos@0
   184
        <Parameter name="adOrgClient" type="replace" optional="true" after="m.AD_Org_ID IN(" text="'1'"/>
carlos@0
   185
        <Parameter name="adUserClient" type="replace" optional="true" after="m.AD_Client_ID IN(" text="'1'"/>
carlos@0
   186
  </SqlMethod>
eduardo@19657
   187
  <SqlMethod name="selectOperands" type="preparedStatement" return="multiple">
eduardo@19657
   188
    <SqlMethodComment></SqlMethodComment>
eduardo@19657
   189
      <Sql><![CDATA[
eduardo@19657
   190
        SELECT m.C_ElementValue_ID as id, o.account_id as node_id, o.sign
eduardo@19657
   191
        FROM C_ElementValue m, C_ELEMENTVALUE_OPERAND o, C_ElementValue n
eduardo@19657
   192
        WHERE m.isActive='Y' 
eduardo@19657
   193
        AND m.AD_Org_ID IN('1') 
eduardo@19657
   194
        AND m.AD_Client_ID IN('1') 
eduardo@19657
   195
        AND m.C_Element_ID = ?
eduardo@19657
   196
        AND m.C_ElementValue_ID = o.C_ElementValue_ID
eduardo@19657
   197
        AND n.C_ElementValue_ID = o.C_ElementValue_ID
eduardo@19657
   198
        AND o.isactive = 'Y' 
eduardo@19657
   199
        order by m.C_elementvalue_id, o.seqno
eduardo@19657
   200
      ]]></Sql>
eduardo@19657
   201
        <Parameter name="adOrgClient" type="replace" optional="true" after="m.AD_Org_ID IN(" text="'1'"/>
eduardo@19657
   202
        <Parameter name="adUserClient" type="replace" optional="true" after="m.AD_Client_ID IN(" text="'1'"/>
eduardo@19657
   203
        <Parameter name="element"/>
eduardo@19657
   204
  </SqlMethod>
carlos@0
   205
</SqlClass>