src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerJournal_data.xsql
author David Baz Fayos <david.baz@openbravo.com>
Tue, 27 May 2008 15:26:10 +0000
changeset 1044 8691bbc94032
parent 642 6be6adb4c859
child 1605 8a0fe0193bef
permissions -rw-r--r--
Removed old frame parameter of windowTableId
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 Openbravo  Public  License
carlos@0
     5
 * Version  1.0  (the  "License"),  being   the  Mozilla   Public  License
carlos@0
     6
 * Version 1.1  with a permitted attribution clause; you may not  use this
carlos@0
     7
 * file except in compliance with the License. You  may  obtain  a copy of
carlos@0
     8
 * the License at http://www.openbravo.com/legal/license.html 
carlos@0
     9
 * Software distributed under the License  is  distributed  on  an "AS IS"
carlos@0
    10
 * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
carlos@0
    11
 * License for the specific  language  governing  rights  and  limitations
carlos@0
    12
 * under the License. 
carlos@0
    13
 * The Original Code is Openbravo ERP. 
carlos@0
    14
 * The Initial Developer of the Original Code is Openbravo SL 
carlos@0
    15
 * All portions are Copyright (C) 2001-2006 Openbravo SL 
carlos@0
    16
 * All Rights Reserved. 
carlos@0
    17
 * Contributor(s):  ______________________________________.
carlos@0
    18
 ************************************************************************
carlos@0
    19
-->
carlos@0
    20
carlos@0
    21
carlos@0
    22
carlos@0
    23
carlos@0
    24
carlos@0
    25
<SqlClass name="ReportGeneralLedgerJournalData" package="org.openbravo.erpCommon.ad_reports">
carlos@0
    26
  <SqlClassComment></SqlClassComment>
carlos@0
    27
  <SqlMethod name="select" type="preparedStatement" return="multiple">
carlos@0
    28
    <SqlMethodComment></SqlMethodComment>
carlos@0
    29
    <Sql>
carlos@0
    30
    <![CDATA[
carlos@562
    31
      SELECT SCHEMA_ID, SCHEMA_NAME, IDENTIFIER, DATEACCT, VALUE, NAME, ID, AD_TABLE_ID, DOCBASETYPE, SEQNO, '' AS TOTAL, '' AS DESCRIPTION,
carlos@0
    32
      (CASE AMTACCTDR WHEN 0 THEN NULL ELSE AMTACCTDR END) AS AMTACCTDR, (CASE AMTACCTCR WHEN 0 THEN NULL ELSE AMTACCTCR END) AS AMTACCTCR
carlos@0
    33
      FROM 
carlos@642
    34
      (SELECT F.C_ACCTSCHEMA_ID AS SCHEMA_ID, SC.NAME AS SCHEMA_NAME, F.FACT_ACCT_GROUP_ID AS IDENTIFIER, F.DATEACCT,
carlos@0
    35
      F.ACCTVALUE AS VALUE, F.ACCTDESCRIPTION AS NAME,F.RECORD_ID AS ID, F.AD_TABLE_ID, F.DOCBASETYPE,
eduardo@445
    36
      sum(F.AMTACCTDR) AS AMTACCTDR, sum(F.AMTACCTCR) AS AMTACCTCR, MIN(SEQNO) AS SEQNO
carlos@562
    37
      FROM FACT_ACCT F, C_ACCTSCHEMA SC  
carlos@0
    38
      WHERE F.AD_CLIENT_ID IN ('1')
carlos@0
    39
      AND F.AD_ORG_ID IN('1')
carlos@0
    40
      AND 1=1
carlos@0
    41
      AND f.AD_ORG_ID IN('2')
carlos@562
    42
      AND f.C_ACCTSCHEMA_ID = SC.C_ACCTSCHEMA_ID
carlos@562
    43
      GROUP BY f.C_ACCTSCHEMA_ID, SC.NAME, F.AD_TABLE_ID, F.DATEACCT, F.ACCTDESCRIPTION, F.ACCTVALUE, F.DOCBASETYPE, F.RECORD_ID, 
carlos@0
    44
      F.FACT_ACCT_GROUP_ID, F.ACCOUNT_ID,
carlos@0
    45
      (CASE F.AMTACCTDR WHEN 0 THEN (CASE SIGN(F.AMTACCTCR) WHEN -1 THEN 1 ELSE 2 END) ELSE (CASE SIGN(F.AMTACCTDR) WHEN -1 THEN 3 ELSE 4 END) END)) AA
carlos@562
    46
      ORDER BY SCHEMA_NAME, DATEACCT, IDENTIFIER, SEQNO
carlos@0
    47
      ]]></Sql>
carlos@0
    48
    <Field name="rownum" value="count"/>
carlos@0
    49
    <Parameter name="adUserClient" type="replace" optional="true" after="WHERE F.AD_CLIENT_ID IN (" text="'1'"/>
carlos@0
    50
    <Parameter name="adUserOrg" type="replace" optional="true" after="AND F.AD_ORG_ID IN(" text="'1'"/>
carlos@0
    51
    <Parameter name="parDateFrom" optional="true" after="AND 1=1"><![CDATA[ AND f.dateacct >= TO_DATE(?)]]></Parameter>
carlos@0
    52
    <Parameter name="parDateTo" optional="true" after="AND 1=1"><![CDATA[ AND f.dateacct < TO_DATE(?)]]></Parameter>
carlos@0
    53
    <Parameter name="docbasetype" optional="true" after="AND 1=1"><![CDATA[ AND f.DOCBASETYPE = ?]]></Parameter>
eduardo@445
    54
    <Parameter name="acctschema" optional="true" after="AND 1=1"><![CDATA[ AND f.C_ACCTSCHEMA_ID = to_number(?)]]></Parameter>
carlos@0
    55
    <Parameter name="orgFamily" type="replace" optional="true" after="AND f.AD_ORG_ID IN(" text="'2'"/>
carlos@0
    56
  </SqlMethod>
carlos@0
    57
  <SqlMethod name="selectCount" type="preparedStatement" return="string" default="0">
carlos@0
    58
    <SqlMethodComment></SqlMethodComment>
carlos@0
    59
    <Sql>
carlos@0
    60
    <![CDATA[
carlos@0
    61
        SELECT COUNT(FACT_ACCT_GROUP_ID) AS TOTAL
carlos@0
    62
        FROM (
carlos@0
    63
        SELECT F.DATEACCT, F.FACT_ACCT_GROUP_ID  
carlos@0
    64
        FROM FACT_ACCT F left join AD_TABLE T on F.AD_TABLE_ID = T.AD_TABLE_ID
carlos@0
    65
                         left join AD_TAB TB  on T.AD_TABLE_ID = TB.AD_TABLE_ID
carlos@0
    66
                         left join AD_COLUMN C on T.AD_TABLE_ID = C.AD_TABLE_ID 
carlos@0
    67
                                              AND C.ISKEY = 'Y'  
carlos@0
    68
                         left join AD_WINDOW W on TB.AD_WINDOW_ID = W.AD_WINDOW_ID
carlos@0
    69
        WHERE F.AD_CLIENT_ID IN ('1')
carlos@0
    70
        AND F.AD_ORG_ID IN('1')
carlos@0
    71
        AND 1=1
carlos@0
    72
        AND f.AD_ORG_ID IN('2')
carlos@0
    73
        AND (CASE (SELECT MAX(ISSOTRX) FROM C_DOCTYPE D 
carlos@0
    74
                    WHERE D.DOCBASETYPE = F.DOCBASETYPE) WHEN 'N' THEN COALESCE(T.PO_WINDOW_ID, T.AD_WINDOW_ID) ELSE T.AD_WINDOW_ID END) = W.AD_WINDOW_ID 
carlos@0
    75
        GROUP BY F.DATEACCT, F.FACT_ACCT_GROUP_ID) AA
carlos@423
    76
        WHERE (DATEACCT< TO_DATE(?) OR (DATEACCT=TO_DATE(?) AND FACT_ACCT_GROUP_ID < TO_NUMBER(?)))
carlos@0
    77
      ]]></Sql>
carlos@0
    78
    <Parameter name="adUserClient" type="replace" optional="true" after="WHERE F.AD_CLIENT_ID IN (" text="'1'"/>
carlos@0
    79
    <Parameter name="adUserOrg" type="replace" optional="true" after="AND F.AD_ORG_ID IN(" text="'1'"/>
carlos@0
    80
    <Parameter name="parDateFrom" optional="true" after="AND 1=1"><![CDATA[ AND f.dateacct >= TO_DATE(?)]]></Parameter>
carlos@0
    81
    <Parameter name="parDateTo" optional="true" after="AND 1=1"><![CDATA[ AND f.dateacct < TO_DATE(?)]]></Parameter>
carlos@0
    82
    <Parameter name="docbasetype" optional="true" after="AND 1=1"><![CDATA[ AND f.DOCBASETYPE = ?]]></Parameter>
eduardo@445
    83
    <Parameter name="acctschema" optional="true" after="AND 1=1"><![CDATA[ AND f.C_ACCTSCHEMA_ID = to_number(?)]]></Parameter>
carlos@0
    84
    <Parameter name="orgFamily" type="replace" optional="true" after="AND f.AD_ORG_ID IN(" text="'2'"/>
carlos@0
    85
    <Parameter name="dateAcct"/>
carlos@0
    86
    <Parameter name="dateAcct"/>
carlos@0
    87
    <Parameter name="factAcctGroupId"/>
carlos@0
    88
  </SqlMethod>
carlos@0
    89
  <SqlMethod name="set" type="constant" return="multiple">
carlos@0
    90
      <SqlMethodComment></SqlMethodComment>
carlos@0
    91
      <Sql></Sql>
eduardo@445
    92
    <Parameter name="rownum"/>      
carlos@0
    93
  </SqlMethod>
carlos@0
    94
  <SqlMethod name="treeOrg" type="preparedStatement" return="string">
carlos@0
    95
    <SqlMethodComment></SqlMethodComment>
carlos@0
    96
    <Sql>
carlos@0
    97
        SELECT AD_TREE_ORG_ID FROM AD_CLIENTINFO
eduardo@445
    98
        WHERE AD_CLIENT_ID = ?
carlos@0
    99
    </Sql>
carlos@0
   100
    <Parameter name="client"/>
carlos@0
   101
  </SqlMethod>
carlos@0
   102
  <SqlMethod name="selectDirect" type="preparedStatement" return="multiple">
carlos@0
   103
    <SqlMethodComment></SqlMethodComment>
carlos@0
   104
    <Sql>
carlos@0
   105
    <![CDATA[
carlos@642
   106
      SELECT SCHEMA_ID, SCHEMA_NAME, IDENTIFIER, DATEACCT, VALUE, NAME, ID, AD_TABLE_ID, DOCBASETYPE, SEQNO, '' AS TOTAL, '' AS DESCRIPTION,
carlos@0
   107
      (CASE AMTACCTDR WHEN 0 THEN NULL ELSE AMTACCTDR END) AS AMTACCTDR, (CASE AMTACCTCR WHEN 0 THEN NULL ELSE AMTACCTCR END) AS AMTACCTCR
carlos@0
   108
      FROM 
carlos@642
   109
      (SELECT F.C_ACCTSCHEMA_ID AS SCHEMA_ID, SC.NAME AS SCHEMA_NAME, F.FACT_ACCT_GROUP_ID AS IDENTIFIER, F.DATEACCT,
carlos@0
   110
      F.ACCTVALUE AS VALUE, F.ACCTDESCRIPTION AS NAME,F.RECORD_ID AS ID, F.AD_TABLE_ID, F.DOCBASETYPE,
eduardo@445
   111
      sum(F.AMTACCTDR) AS AMTACCTDR, sum(F.AMTACCTCR) AS AMTACCTCR, MIN(SEQNO) AS SEQNO
carlos@642
   112
      FROM FACT_ACCT F, C_ACCTSCHEMA SC 
carlos@0
   113
      WHERE F.AD_CLIENT_ID IN ('1')
carlos@0
   114
      AND F.AD_ORG_ID IN('1')
carlos@0
   115
      AND 1=1
carlos@642
   116
      AND f.C_ACCTSCHEMA_ID = SC.C_ACCTSCHEMA_ID
carlos@642
   117
      GROUP BY f.C_ACCTSCHEMA_ID, SC.NAME, F.AD_TABLE_ID, F.DATEACCT, F.ACCTDESCRIPTION, F.ACCTVALUE, F.DOCBASETYPE, F.RECORD_ID, 
carlos@0
   118
      F.FACT_ACCT_GROUP_ID, F.ACCOUNT_ID,
carlos@0
   119
      (CASE F.AMTACCTDR WHEN 0 THEN (CASE SIGN(F.AMTACCTCR) WHEN -1 THEN 1 ELSE 2 END) ELSE (CASE SIGN(F.AMTACCTDR) WHEN -1 THEN 3 ELSE 4 END) END)) AA
carlos@642
   120
      ORDER BY SCHEMA_NAME, DATEACCT, IDENTIFIER, SEQNO
carlos@0
   121
      ]]></Sql>
carlos@0
   122
    <Field name="rownum" value="count"/>
carlos@0
   123
    <Parameter name="adUserClient" type="replace" optional="true" after="WHERE F.AD_CLIENT_ID IN (" text="'1'"/>
carlos@0
   124
    <Parameter name="adUserOrg" type="replace" optional="true" after="AND F.AD_ORG_ID IN(" text="'1'"/>
eduardo@445
   125
    <Parameter name="table" optional="true" after="AND 1=1"><![CDATA[ AND f.ad_table_Id = to_number(?)]]></Parameter>
eduardo@445
   126
    <Parameter name="record" optional="true" after="AND 1=1"><![CDATA[ AND f.record_Id = to_number(?)]]></Parameter>   
carlos@423
   127
    <Parameter name="table" optional="true" after="AND 1=1"><![CDATA[ AND f.ad_table_ID = TO_NUMBER(?)]]></Parameter>
carlos@423
   128
    <Parameter name="record" optional="true" after="AND 1=1"><![CDATA[ AND f.record_ID = TO_NUMBER(?)]]></Parameter>
carlos@0
   129
  </SqlMethod>
carlos@0
   130
  <SqlMethod name="selectCountDirect" type="preparedStatement" return="string" default="0">
carlos@0
   131
    <SqlMethodComment></SqlMethodComment>
carlos@0
   132
    <Sql>
carlos@0
   133
    <![CDATA[
carlos@0
   134
        SELECT COUNT(FACT_ACCT_GROUP_ID) AS TOTAL
carlos@0
   135
        FROM (
carlos@0
   136
        SELECT F.DATEACCT, F.FACT_ACCT_GROUP_ID  
carlos@0
   137
        FROM  AD_TABLE T left join AD_TAB TB on T.AD_TABLE_ID = TB.AD_TABLE_ID
carlos@0
   138
                         left join AD_COLUMN C  on T.AD_TABLE_ID = C.AD_TABLE_ID 
carlos@0
   139
                                               AND C.ISKEY = 'Y'
carlos@0
   140
                         left join FACT_ACCT F on F.AD_TABLE_ID = T.AD_TABLE_ID
carlos@0
   141
                         left join AD_WINDOW W on TB.AD_WINDOW_ID = W.AD_WINDOW_ID 
carlos@0
   142
        WHERE F.AD_CLIENT_ID IN ('1')
carlos@0
   143
        AND F.AD_ORG_ID IN('1')
carlos@0
   144
        AND 1=1
carlos@0
   145
        AND (CASE (SELECT MAX(ISSOTRX) FROM C_DOCTYPE D 
carlos@0
   146
        WHERE D.DOCBASETYPE = F.DOCBASETYPE) WHEN 'N' THEN COALESCE(T.PO_WINDOW_ID, T.AD_WINDOW_ID) ELSE T.AD_WINDOW_ID END) = W.AD_WINDOW_ID 
carlos@0
   147
        GROUP BY F.DATEACCT, F.FACT_ACCT_GROUP_ID) AA
carlos@423
   148
        WHERE (DATEACCT< TO_DATE(?) OR (DATEACCT=to_date(?) AND FACT_ACCT_GROUP_ID < TO_NUMBER(?)))
carlos@0
   149
      ]]></Sql>
carlos@0
   150
    <Parameter name="adUserClient" type="replace" optional="true" after="WHERE F.AD_CLIENT_ID IN (" text="'1'"/>
carlos@0
   151
    <Parameter name="adUserOrg" type="replace" optional="true" after="AND F.AD_ORG_ID IN(" text="'1'"/>
carlos@423
   152
    <Parameter name="table" optional="true" after="AND 1=1"><![CDATA[ AND f.ad_table_ID = TO_NUMBER(?)]]></Parameter>
carlos@423
   153
    <Parameter name="record" optional="true" after="AND 1=1"><![CDATA[ AND f.record_ID = TO_NUMBER(?)]]></Parameter>
carlos@0
   154
    <Parameter name="dateAcct"/>
carlos@0
   155
    <Parameter name="dateAcct"/>
carlos@0
   156
    <Parameter name="factAcctGroupId"/>
carlos@0
   157
  </SqlMethod>
carlos@0
   158
  <SqlMethod name="selectDirect2" type="preparedStatement" return="multiple">
carlos@0
   159
    <SqlMethodComment></SqlMethodComment>
carlos@0
   160
    <Sql>
carlos@0
   161
    <![CDATA[
carlos@642
   162
      SELECT SCHEMA_ID, SCHEMA_NAME, IDENTIFIER, DATEACCT, VALUE, NAME, ID, AD_TABLE_ID, DOCBASETYPE, SEQNO, '' AS TOTAL, '' AS DESCRIPTION,
carlos@0
   163
      (CASE AMTACCTDR WHEN 0 THEN NULL ELSE AMTACCTDR END) AS AMTACCTDR, (CASE AMTACCTCR WHEN 0 THEN NULL ELSE AMTACCTCR END) AS AMTACCTCR
carlos@0
   164
      FROM 
carlos@642
   165
      (SELECT F.C_ACCTSCHEMA_ID AS SCHEMA_ID, SC.NAME AS SCHEMA_NAME, F.FACT_ACCT_GROUP_ID AS IDENTIFIER, F.DATEACCT,
carlos@0
   166
      F.ACCTVALUE AS VALUE, F.ACCTDESCRIPTION AS NAME,F.RECORD_ID AS ID, F.AD_TABLE_ID, F.DOCBASETYPE,
eduardo@445
   167
      sum(F.AMTACCTDR) AS AMTACCTDR, sum(F.AMTACCTCR) AS AMTACCTCR, MIN(SEQNO) AS SEQNO
carlos@642
   168
      FROM FACT_ACCT F, C_ACCTSCHEMA SC 
carlos@0
   169
      WHERE F.AD_CLIENT_ID IN ('1')
carlos@0
   170
      AND F.AD_ORG_ID IN('1')
carlos@642
   171
      AND f.C_ACCTSCHEMA_ID = SC.C_ACCTSCHEMA_ID
carlos@423
   172
      AND F.Fact_Acct_Group_ID = TO_NUMBER(?)
carlos@642
   173
      GROUP BY f.C_ACCTSCHEMA_ID, SC.NAME, F.AD_TABLE_ID, F.DATEACCT, F.ACCTDESCRIPTION, F.ACCTVALUE, F.DOCBASETYPE, F.RECORD_ID, 
carlos@0
   174
      F.FACT_ACCT_GROUP_ID, F.ACCOUNT_ID,
carlos@0
   175
      (CASE F.AMTACCTDR WHEN 0 THEN (CASE SIGN(F.AMTACCTCR) WHEN -1 THEN 1 ELSE 2 END) ELSE (CASE SIGN(F.AMTACCTDR) WHEN -1 THEN 3 ELSE 4 END) END)) AA
carlos@642
   176
      ORDER BY SCHEMA_NAME, DATEACCT, IDENTIFIER, SEQNO
carlos@0
   177
      ]]></Sql>
carlos@0
   178
    <Field name="rownum" value="count"/>
carlos@0
   179
    <Parameter name="adUserClient" type="replace" optional="true" after="WHERE F.AD_CLIENT_ID IN (" text="'1'"/>
carlos@0
   180
    <Parameter name="adUserOrg" type="replace" optional="true" after="AND F.AD_ORG_ID IN(" text="'1'"/>
carlos@0
   181
    <Parameter name="factAcctGroupId"/>
carlos@0
   182
  </SqlMethod>
carlos@0
   183
  <SqlMethod name="selectCountDirect2" type="preparedStatement" return="string" default="0">
carlos@0
   184
    <SqlMethodComment></SqlMethodComment>
carlos@0
   185
    <Sql>
carlos@0
   186
    <![CDATA[
carlos@0
   187
        SELECT COUNT(FACT_ACCT_GROUP_ID) AS TOTAL
carlos@0
   188
        FROM (
carlos@0
   189
        SELECT F.DATEACCT, F.FACT_ACCT_GROUP_ID  
carlos@0
   190
        FROM AD_TABLE T left join AD_TAB TB    on T.AD_TABLE_ID = TB.AD_TABLE_ID
carlos@0
   191
                        left join AD_COLUMN C  on T.AD_TABLE_ID = C.AD_TABLE_ID
carlos@0
   192
                                              AND C.ISKEY = 'Y'
carlos@0
   193
                        left join FACT_ACCT F  on F.AD_TABLE_ID = T.AD_TABLE_ID 
carlos@0
   194
                        left join AD_WINDOW W  on TB.AD_WINDOW_ID = W.AD_WINDOW_ID
carlos@0
   195
        WHERE F.AD_CLIENT_ID IN ('1')
carlos@0
   196
        AND F.AD_ORG_ID IN('1')
carlos@0
   197
        AND 1=1
carlos@423
   198
        and f.fact_acct_group_ID = TO_NUMBER(?)
carlos@0
   199
        AND (CASE (SELECT MAX(ISSOTRX) FROM C_DOCTYPE D 
carlos@0
   200
        WHERE D.DOCBASETYPE = F.DOCBASETYPE) WHEN 'N' THEN COALESCE(T.PO_WINDOW_ID, T.AD_WINDOW_ID) ELSE T.AD_WINDOW_ID END) = W.AD_WINDOW_ID 
carlos@0
   201
        GROUP BY F.DATEACCT, F.FACT_ACCT_GROUP_ID) AA
carlos@0
   202
        WHERE (DATEACCT< to_date(?) OR (DATEACCT=to_date(?) AND FACT_ACCT_GROUP_ID < to_number(?)))
carlos@0
   203
      ]]></Sql>
carlos@0
   204
    <Parameter name="adUserClient" type="replace" optional="true" after="WHERE F.AD_CLIENT_ID IN (" text="'1'"/>
carlos@0
   205
    <Parameter name="adUserOrg" type="replace" optional="true" after="AND F.AD_ORG_ID IN(" text="'1'"/>
carlos@0
   206
    <Parameter name="factAcctGroupId"/>
carlos@0
   207
    <Parameter name="dateAcct"/>
carlos@0
   208
    <Parameter name="dateAcct"/>
carlos@0
   209
    <Parameter name="identifier"/>
carlos@0
   210
  </SqlMethod>
carlos@0
   211
  <SqlMethod name="selectCompany" type="preparedStatement" return="string" default="0">
carlos@0
   212
    <SqlMethodComment></SqlMethodComment>
carlos@0
   213
    <Sql>
carlos@0
   214
    <![CDATA[
carlos@0
   215
        SELECT NAME
carlos@0
   216
        FROM AD_CLIENT
carlos@423
   217
        WHERE AD_CLIENT_ID = TO_NUMBER(?)
carlos@0
   218
      ]]></Sql>
carlos@0
   219
    <Parameter name="client"/>
carlos@0
   220
  </SqlMethod>
carlos@0
   221
</SqlClass>