src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerJournal_data.xsql
author Pandeeswari Ramakrishnan <pandeeswari.ramakrishnan@openbravo.com>
Thu, 23 May 2013 17:50:17 +0530
changeset 20474 bb419c9b4257
parent 18518 4eb028c733a4
child 21883 eacc27c826cc
permissions -rw-r--r--
Fixes issue 22265: First entry in "journal entries report" is number 2
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
priya@9085
     5
 * Version  1.1  (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
david@11040
     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
david@11040
    12
 * under the License.
david@11040
    13
 * The Original Code is Openbravo ERP.
david@11040
    14
 * The Initial Developer of the Original Code is Openbravo SLU
pandeeswari@20474
    15
 * All portions are Copyright (C) 2001-2013 Openbravo SLU
david@11040
    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[
sandra@18495
    31
      SELECT SCHEMA_ID, SCHEMA_NAME, IDENTIFIER, DATEACCT, AA.VALUE, AA.NAME, ID, AD_TABLE_ID, DOCBASETYPE,AR.NAME as DOCNAME, SEQNO, '' AS TOTAL, DESCRIPTION,
ioritz@14162
    32
      (CASE FACTACCTTYPE WHEN 'O' THEN 1 WHEN 'N' THEN 2 WHEN 'R' THEN 3 ELSE 4 END) AS FACTACCTTYPE2,
ioritz@14162
    33
      (CASE AMTACCTDR WHEN 0 THEN NULL ELSE AMTACCTDR END) AS AMTACCTDR, (CASE AMTACCTCR WHEN 0 THEN NULL ELSE AMTACCTCR END) AS AMTACCTCR, 
sandra@18518
    34
      '' AS GROUPEDLINES, '' AS TAXID, AD_GETTAB_FROM_TABLE(AA.AD_TABLE_ID, AA.DOCBASETYPE, AA.AD_CLIENT_ID) AS TAB_ID  , '' as newStyle
carlos@0
    35
      FROM 
carlos@642
    36
      (SELECT F.C_ACCTSCHEMA_ID AS SCHEMA_ID, SC.NAME AS SCHEMA_NAME, F.FACT_ACCT_GROUP_ID AS IDENTIFIER, F.DATEACCT,
sandra@18518
    37
      F.ACCTVALUE AS VALUE, F.ACCTDESCRIPTION AS NAME, CASE WHEN 'Y' = 'Y' THEN TO_CHAR(F.DESCRIPTION) ELSE TO_CHAR('') END AS DESCRIPTION, F.RECORD_ID AS ID, F.AD_TABLE_ID, F.DOCBASETYPE,
ioritz@14163
    38
      sum(F.AMTACCTDR) AS AMTACCTDR, sum(F.AMTACCTCR) AS AMTACCTCR, MIN(SEQNO) AS SEQNO, F.FACTACCTTYPE AS FACTACCTTYPE, F.AD_CLIENT_ID
ioritz@14162
    39
      FROM FACT_ACCT F, C_ACCTSCHEMA SC  
carlos@0
    40
      WHERE F.AD_CLIENT_ID IN ('1')
carlos@0
    41
      AND F.AD_ORG_ID IN('1')
carlos@0
    42
      AND 1=1
carlos@0
    43
      AND f.AD_ORG_ID IN('2')
asier@1929
    44
      AND F.FactAcctType IN ('C','N','O','R')
carlos@562
    45
      AND f.C_ACCTSCHEMA_ID = SC.C_ACCTSCHEMA_ID
sandra@18518
    46
      GROUP BY f.C_ACCTSCHEMA_ID, SC.NAME, F.AD_TABLE_ID, F.DATEACCT, F.ACCTDESCRIPTION, CASE WHEN 'Y' = 'Y' THEN TO_CHAR(F.DESCRIPTION) ELSE TO_CHAR('') END, F.ACCTVALUE, F.DOCBASETYPE, F.RECORD_ID, 
sathiyan@8256
    47
      F.FACT_ACCT_GROUP_ID, F.ACCOUNT_ID,F.FACTACCTTYPE,
ioritz@17531
    48
      (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), F.AD_CLIENT_ID
sandra@18513
    49
      HAVING (sum(F.AMTACCTDR) <> 0 OR sum(F.AMTACCTCR) <> 0)) AA
sandra@18513
    50
      LEFT JOIN (select * from AD_REF_LIST_V WHERE AD_REFERENCE_ID = '183'  AND AD_LANGUAGE=?) AR  ON AR.VALUE=AA.DOCBASETYPE 
sandra@18518
    51
      ORDER BY SCHEMA_NAME, DATEACCT, FACTACCTTYPE2, IDENTIFIER, AA.AMTACCTDR DESC, AA.AMTACCTCR DESC, SEQNO
carlos@0
    52
      ]]></Sql>
carlos@0
    53
    <Field name="rownum" value="count"/>
sandra@18518
    54
    <Parameter name="descriptionGrouping" type="replace" optional="true" after="AS NAME, CASE WHEN " text="'Y'"/>
carlos@0
    55
    <Parameter name="adUserClient" type="replace" optional="true" after="WHERE F.AD_CLIENT_ID IN (" text="'1'"/>
carlos@0
    56
    <Parameter name="adUserOrg" type="replace" optional="true" after="AND F.AD_ORG_ID IN(" text="'1'"/>
carlos@0
    57
    <Parameter name="parDateFrom" optional="true" after="AND 1=1"><![CDATA[ AND f.dateacct >= TO_DATE(?)]]></Parameter>
carlos@0
    58
    <Parameter name="parDateTo" optional="true" after="AND 1=1"><![CDATA[ AND f.dateacct < TO_DATE(?)]]></Parameter>
carlos@0
    59
    <Parameter name="docbasetype" optional="true" after="AND 1=1"><![CDATA[ AND f.DOCBASETYPE = ?]]></Parameter>
juanpablo@1605
    60
    <Parameter name="acctschema" optional="true" after="AND 1=1"><![CDATA[ AND f.C_ACCTSCHEMA_ID = ?]]></Parameter>
carlos@0
    61
    <Parameter name="orgFamily" type="replace" optional="true" after="AND f.AD_ORG_ID IN(" text="'2'"/>
asier@1929
    62
    <Parameter name="checks" type="replace" optional="true" after="AND F.FactAcctType IN (" text="'C','N','O','R'"/>
sandra@18518
    63
    <Parameter name="descriptionGrouping" type="replace" optional="true" after="F.ACCTDESCRIPTION, CASE WHEN " text="'Y'"/>
asier@1929
    64
    <!--Parameter name="closing" optional="true" type="none" after="2=2" text=" AND F.FactAcctType = 'C'"/>
asier@1929
    65
    <Parameter name="regularization" optional="true" type="none" after="2=2" text=" AND F.FactAcctType = 'R'"/>
asier@1929
    66
    <Parameter name="opening" optional="true" type="none" after="2=2" text=" AND F.FactAcctType = 'O'"/-->
sandra@18243
    67
    <Parameter name="paramLanguage"></Parameter>
carlos@0
    68
  </SqlMethod>
victor@5394
    69
  <SqlMethod name="selectCountGroupedLines" type="preparedStatement" return="multiple">
victor@5394
    70
    <SqlMethodComment></SqlMethodComment>
victor@5394
    71
    <Sql>
victor@5394
    72
    <![CDATA[
victor@5394
    73
      SELECT COUNT(*) AS GROUPEDLINES, IDENTIFIER, SCHEMA_NAME, DATEACCT
victor@5394
    74
      FROM
victor@5394
    75
      (
victor@5394
    76
        SELECT SCHEMA_ID, SCHEMA_NAME, IDENTIFIER, DATEACCT, VALUE, NAME, ID, AD_TABLE_ID, DOCBASETYPE, SEQNO, '' AS TOTAL, '' AS DESCRIPTION,
sathiyan@8256
    77
        (CASE AMTACCTDR WHEN 0 THEN NULL ELSE AMTACCTDR END) AS AMTACCTDR, (CASE AMTACCTCR WHEN 0 THEN NULL ELSE AMTACCTCR END) AS AMTACCTCR
victor@5394
    78
        FROM 
victor@5394
    79
        (SELECT F.C_ACCTSCHEMA_ID AS SCHEMA_ID, SC.NAME AS SCHEMA_NAME, F.FACT_ACCT_GROUP_ID AS IDENTIFIER, F.DATEACCT,
victor@5394
    80
        F.ACCTVALUE AS VALUE, F.ACCTDESCRIPTION AS NAME,F.RECORD_ID AS ID, F.AD_TABLE_ID, F.DOCBASETYPE,
victor@5394
    81
        sum(F.AMTACCTDR) AS AMTACCTDR, sum(F.AMTACCTCR) AS AMTACCTCR, MIN(SEQNO) AS SEQNO
victor@5394
    82
        FROM FACT_ACCT F, C_ACCTSCHEMA SC  
victor@5394
    83
        WHERE F.AD_CLIENT_ID IN ('1')
victor@5394
    84
        AND F.AD_ORG_ID IN('1')
victor@5394
    85
        AND 1=1
victor@5394
    86
        AND f.AD_ORG_ID IN('2')
victor@5394
    87
        AND F.FactAcctType IN ('C','N','O','R')
victor@5394
    88
        AND f.C_ACCTSCHEMA_ID = SC.C_ACCTSCHEMA_ID
victor@5394
    89
        GROUP BY f.C_ACCTSCHEMA_ID, SC.NAME, F.AD_TABLE_ID, F.DATEACCT, F.ACCTDESCRIPTION, F.ACCTVALUE, F.DOCBASETYPE, F.RECORD_ID, 
sathiyan@8256
    90
        F.FACT_ACCT_GROUP_ID, F.ACCOUNT_ID,
david@8329
    91
        (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
victor@5394
    92
        ORDER BY SCHEMA_NAME, DATEACCT, IDENTIFIER, SEQNO
victor@5394
    93
      ) BB
victor@5394
    94
      GROUP BY IDENTIFIER, SCHEMA_NAME, DATEACCT
victor@5394
    95
      ORDER BY SCHEMA_NAME, DATEACCT, IDENTIFIER
victor@5394
    96
      ]]></Sql>
victor@5394
    97
    <Field name="rownum" value="count"/>
victor@5394
    98
    <Parameter name="adUserClient" type="replace" optional="true" after="WHERE F.AD_CLIENT_ID IN (" text="'1'"/>
victor@5394
    99
    <Parameter name="adUserOrg" type="replace" optional="true" after="AND F.AD_ORG_ID IN(" text="'1'"/>
victor@5394
   100
    <Parameter name="parDateFrom" optional="true" after="AND 1=1"><![CDATA[ AND f.dateacct >= TO_DATE(?)]]></Parameter>
victor@5394
   101
    <Parameter name="parDateTo" optional="true" after="AND 1=1"><![CDATA[ AND f.dateacct < TO_DATE(?)]]></Parameter>
victor@5394
   102
    <Parameter name="docbasetype" optional="true" after="AND 1=1"><![CDATA[ AND f.DOCBASETYPE = ?]]></Parameter>
victor@5394
   103
    <Parameter name="acctschema" optional="true" after="AND 1=1"><![CDATA[ AND f.C_ACCTSCHEMA_ID = ?]]></Parameter>
victor@5394
   104
    <Parameter name="orgFamily" type="replace" optional="true" after="AND f.AD_ORG_ID IN(" text="'2'"/>
victor@5394
   105
    <Parameter name="checks" type="replace" optional="true" after="AND F.FactAcctType IN (" text="'C','N','O','R'"/>
victor@5394
   106
    <!--Parameter name="closing" optional="true" type="none" after="2=2" text=" AND F.FactAcctType = 'C'"/>
victor@5394
   107
    <Parameter name="regularization" optional="true" type="none" after="2=2" text=" AND F.FactAcctType = 'R'"/>
victor@5394
   108
    <Parameter name="opening" optional="true" type="none" after="2=2" text=" AND F.FactAcctType = 'O'"/-->
victor@5394
   109
  </SqlMethod>
carlos@0
   110
  <SqlMethod name="selectCount" type="preparedStatement" return="string" default="0">
carlos@0
   111
    <SqlMethodComment></SqlMethodComment>
carlos@0
   112
    <Sql>
carlos@0
   113
    <![CDATA[
carlos@0
   114
        SELECT COUNT(FACT_ACCT_GROUP_ID) AS TOTAL
carlos@0
   115
        FROM (
carlos@0
   116
        SELECT F.DATEACCT, F.FACT_ACCT_GROUP_ID  
carlos@0
   117
        FROM FACT_ACCT F left join AD_TABLE T on F.AD_TABLE_ID = T.AD_TABLE_ID
carlos@0
   118
                         left join AD_TAB TB  on T.AD_TABLE_ID = TB.AD_TABLE_ID
carlos@0
   119
                         left join AD_COLUMN C on T.AD_TABLE_ID = C.AD_TABLE_ID 
carlos@0
   120
                                              AND C.ISKEY = 'Y'  
carlos@0
   121
                         left join AD_WINDOW W on TB.AD_WINDOW_ID = W.AD_WINDOW_ID
carlos@0
   122
        WHERE F.AD_CLIENT_ID IN ('1')
carlos@0
   123
        AND F.AD_ORG_ID IN('1')
carlos@0
   124
        AND 1=1
asier@1929
   125
        AND f.AD_ORG_ID IN('2')     
david@8329
   126
	      AND F.FactAcctType IN ('C','N','O','R')
carlos@0
   127
        AND (CASE (SELECT MAX(ISSOTRX) FROM C_DOCTYPE D 
sandra@18243
   128
                    WHERE D.DOCBASETYPE = F.DOCBASETYPE) WHEN 'N' THEN COALESCE(T.PO_WINDOW_ID, T.AD_WINDOW_ID) ELSE T.AD_WINDOW_ID END) = (CASE TO_CHAR(F.DOCBASETYPE) 
sandra@18243
   129
                    WHEN 'FAT' THEN '94EAA455D2644E04AB25D93BE5157B6D' ELSE W.AD_WINDOW_ID END)
carlos@0
   130
        GROUP BY F.DATEACCT, F.FACT_ACCT_GROUP_ID) AA
juanpablo@1605
   131
        WHERE (DATEACCT< TO_DATE(?) OR (DATEACCT=TO_DATE(?) AND FACT_ACCT_GROUP_ID < ?))
carlos@0
   132
      ]]></Sql>
carlos@0
   133
    <Parameter name="adUserClient" type="replace" optional="true" after="WHERE F.AD_CLIENT_ID IN (" text="'1'"/>
carlos@0
   134
    <Parameter name="adUserOrg" type="replace" optional="true" after="AND F.AD_ORG_ID IN(" text="'1'"/>
carlos@0
   135
    <Parameter name="parDateFrom" optional="true" after="AND 1=1"><![CDATA[ AND f.dateacct >= TO_DATE(?)]]></Parameter>
carlos@0
   136
    <Parameter name="parDateTo" optional="true" after="AND 1=1"><![CDATA[ AND f.dateacct < TO_DATE(?)]]></Parameter>
carlos@0
   137
    <Parameter name="docbasetype" optional="true" after="AND 1=1"><![CDATA[ AND f.DOCBASETYPE = ?]]></Parameter>
juanpablo@1605
   138
    <Parameter name="acctschema" optional="true" after="AND 1=1"><![CDATA[ AND f.C_ACCTSCHEMA_ID = ?]]></Parameter>
carlos@0
   139
    <Parameter name="orgFamily" type="replace" optional="true" after="AND f.AD_ORG_ID IN(" text="'2'"/>
asier@1929
   140
    <Parameter name="checks" type="replace" optional="true" after="AND F.FactAcctType IN (" text="'C','N','O','R'"/>
carlos@0
   141
    <Parameter name="dateAcct"/>
carlos@0
   142
    <Parameter name="dateAcct"/>
carlos@0
   143
    <Parameter name="factAcctGroupId"/>
asier@1929
   144
    <!--Parameter name="closing" optional="true" type="none" after="2=2" text=" AND F.FactAcctType = 'C'"/>
asier@1929
   145
    <Parameter name="regularization" optional="true" type="none" after="2=2" text=" AND F.FactAcctType = 'R'"/>
asier@1929
   146
    <Parameter name="opening" optional="true" type="none" after="2=2" text=" AND F.FactAcctType = 'O'"/-->
carlos@0
   147
  </SqlMethod>
carlos@0
   148
  <SqlMethod name="set" type="constant" return="multiple">
carlos@0
   149
      <SqlMethodComment></SqlMethodComment>
carlos@0
   150
      <Sql></Sql>
eduardo@445
   151
    <Parameter name="rownum"/>      
carlos@0
   152
  </SqlMethod>
carlos@0
   153
  <SqlMethod name="selectDirect" type="preparedStatement" return="multiple">
carlos@0
   154
    <SqlMethodComment></SqlMethodComment>
carlos@0
   155
    <Sql>
carlos@0
   156
    <![CDATA[
sandra@18518
   157
      SELECT SCHEMA_ID, SCHEMA_NAME, IDENTIFIER, DATEACCT, AA.VALUE, AA.NAME, ID, AD_TABLE_ID, DOCBASETYPE,AR.NAME as DOCNAME, SEQNO, '' AS TOTAL, 
ioritz@14162
   158
      (CASE FACTACCTTYPE WHEN 'O' THEN 1 WHEN 'N' THEN 2 WHEN 'R' THEN 3 ELSE 4 END) AS FACTACCTTYPE2,
ioritz@14163
   159
      (CASE AMTACCTDR WHEN 0 THEN NULL ELSE AMTACCTDR END) AS AMTACCTDR, (CASE AMTACCTCR WHEN 0 THEN NULL ELSE AMTACCTCR END) AS AMTACCTCR,
sandra@18518
   160
      AD_GETTAB_FROM_TABLE(AA.AD_TABLE_ID, AA.DOCBASETYPE, AA.AD_CLIENT_ID) AS TAB_ID  , '' as newStyle
carlos@0
   161
      FROM 
carlos@642
   162
      (SELECT F.C_ACCTSCHEMA_ID AS SCHEMA_ID, SC.NAME AS SCHEMA_NAME, F.FACT_ACCT_GROUP_ID AS IDENTIFIER, F.DATEACCT,
sandra@18518
   163
      F.ACCTVALUE AS VALUE, F.ACCTDESCRIPTION AS NAME, F.RECORD_ID AS ID, F.AD_TABLE_ID, F.DOCBASETYPE,
ioritz@14163
   164
      sum(F.AMTACCTDR) AS AMTACCTDR, sum(F.AMTACCTCR) AS AMTACCTCR, MIN(SEQNO) AS SEQNO, F.FACTACCTTYPE AS FACTACCTTYPE, F.AD_CLIENT_ID
carlos@642
   165
      FROM FACT_ACCT F, C_ACCTSCHEMA SC 
carlos@0
   166
      WHERE F.AD_CLIENT_ID IN ('1')
carlos@0
   167
      AND F.AD_ORG_ID IN('1')
carlos@0
   168
      AND 1=1
carlos@642
   169
      AND f.C_ACCTSCHEMA_ID = SC.C_ACCTSCHEMA_ID
sandra@18496
   170
      AND f.C_ACCTSCHEMA_ID = ?
sandra@18518
   171
      GROUP BY f.C_ACCTSCHEMA_ID, SC.NAME, F.AD_TABLE_ID, F.DATEACCT, F.ACCTDESCRIPTION,  F.ACCTVALUE, F.DOCBASETYPE, F.RECORD_ID, 
sathiyan@8256
   172
      F.FACT_ACCT_GROUP_ID, F.ACCOUNT_ID,F.FACTACCTTYPE,
ioritz@17531
   173
      (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), F.AD_CLIENT_ID
sandra@18513
   174
      HAVING (sum(F.AMTACCTDR) <> 0 OR sum(F.AMTACCTCR) <> 0)) AA
sandra@18513
   175
      LEFT JOIN (select * from AD_REF_LIST_V WHERE AD_REFERENCE_ID = '183'  AND AD_LANGUAGE=?) AR  ON AR.VALUE=AA.DOCBASETYPE 
sandra@18518
   176
      ORDER BY SCHEMA_NAME, DATEACCT, FACTACCTTYPE2, IDENTIFIER, AA.AMTACCTDR DESC, AA.AMTACCTCR DESC, 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'"/>
juanpablo@1605
   181
    <Parameter name="table" optional="true" after="AND 1=1"><![CDATA[ AND f.ad_table_Id = ?]]></Parameter>
juanpablo@1605
   182
    <Parameter name="record" optional="true" after="AND 1=1"><![CDATA[ AND f.record_Id = ?]]></Parameter>   
juanpablo@1605
   183
    <Parameter name="table" optional="true" after="AND 1=1"><![CDATA[ AND f.ad_table_ID = ?]]></Parameter>
juanpablo@1605
   184
    <Parameter name="record" optional="true" after="AND 1=1"><![CDATA[ AND f.record_ID = ?]]></Parameter>
sandra@18496
   185
    <Parameter name="cAcctshemaId"></Parameter>
sandra@18494
   186
    <Parameter name="paramLanguage"></Parameter>
carlos@0
   187
  </SqlMethod>
carlos@0
   188
  <SqlMethod name="selectCountDirect" type="preparedStatement" return="string" default="0">
carlos@0
   189
    <SqlMethodComment></SqlMethodComment>
carlos@0
   190
    <Sql>
carlos@0
   191
    <![CDATA[
carlos@0
   192
        SELECT COUNT(FACT_ACCT_GROUP_ID) AS TOTAL
carlos@0
   193
        FROM (
carlos@0
   194
        SELECT F.DATEACCT, F.FACT_ACCT_GROUP_ID  
carlos@0
   195
        FROM  AD_TABLE T left join AD_TAB TB on T.AD_TABLE_ID = TB.AD_TABLE_ID
carlos@0
   196
                         left join AD_COLUMN C  on T.AD_TABLE_ID = C.AD_TABLE_ID 
carlos@0
   197
                                               AND C.ISKEY = 'Y'
carlos@0
   198
                         left join FACT_ACCT F on F.AD_TABLE_ID = T.AD_TABLE_ID
carlos@0
   199
                         left join AD_WINDOW W on TB.AD_WINDOW_ID = W.AD_WINDOW_ID 
carlos@0
   200
        WHERE F.AD_CLIENT_ID IN ('1')
carlos@0
   201
        AND F.AD_ORG_ID IN('1')
carlos@0
   202
        AND 1=1
pandeeswari@20474
   203
        AND F.fact_acct_group_id = ?
carlos@0
   204
        AND (CASE (SELECT MAX(ISSOTRX) FROM C_DOCTYPE D 
carlos@0
   205
        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
   206
        GROUP BY F.DATEACCT, F.FACT_ACCT_GROUP_ID) AA
juanpablo@1605
   207
        WHERE (DATEACCT< TO_DATE(?) OR (DATEACCT=to_date(?) AND FACT_ACCT_GROUP_ID < ?))
carlos@0
   208
      ]]></Sql>
carlos@0
   209
    <Parameter name="adUserClient" type="replace" optional="true" after="WHERE F.AD_CLIENT_ID IN (" text="'1'"/>
carlos@0
   210
    <Parameter name="adUserOrg" type="replace" optional="true" after="AND F.AD_ORG_ID IN(" text="'1'"/>
juanpablo@1605
   211
    <Parameter name="table" optional="true" after="AND 1=1"><![CDATA[ AND f.ad_table_Id = ?]]></Parameter>
juanpablo@1605
   212
    <Parameter name="record" optional="true" after="AND 1=1"><![CDATA[ AND f.record_Id = ?]]></Parameter>
pandeeswari@20474
   213
    <Parameter name="factAcctGroup"/>
carlos@0
   214
    <Parameter name="dateAcct"/>
carlos@0
   215
    <Parameter name="dateAcct"/>
carlos@0
   216
    <Parameter name="factAcctGroupId"/>
carlos@0
   217
  </SqlMethod>
carlos@0
   218
  <SqlMethod name="selectDirect2" type="preparedStatement" return="multiple">
carlos@0
   219
    <SqlMethodComment></SqlMethodComment>
carlos@0
   220
    <Sql>
carlos@0
   221
    <![CDATA[
sandra@18518
   222
      SELECT SCHEMA_ID, SCHEMA_NAME, IDENTIFIER, DATEACCT, AA.VALUE, AA.NAME, ID, AD_TABLE_ID, DOCBASETYPE,AR.NAME as DOCNAME, SEQNO, '' AS TOTAL,  (CASE FACTACCTTYPE WHEN 'O' THEN 1 WHEN 'N' THEN 2 WHEN 'R' THEN 3 ELSE 4 END) AS FACTACCTTYPE2,
ioritz@14163
   223
      (CASE AMTACCTDR WHEN 0 THEN NULL ELSE AMTACCTDR END) AS AMTACCTDR, (CASE AMTACCTCR WHEN 0 THEN NULL ELSE AMTACCTCR END) AS AMTACCTCR,
sandra@18518
   224
      AD_GETTAB_FROM_TABLE(AA.AD_TABLE_ID, AA.DOCBASETYPE, AA.AD_CLIENT_ID) AS TAB_ID  , '' as newStyle
carlos@0
   225
      FROM 
carlos@642
   226
      (SELECT F.C_ACCTSCHEMA_ID AS SCHEMA_ID, SC.NAME AS SCHEMA_NAME, F.FACT_ACCT_GROUP_ID AS IDENTIFIER, F.DATEACCT,
sandra@18518
   227
      F.ACCTVALUE AS VALUE, F.ACCTDESCRIPTION AS NAME, F.RECORD_ID AS ID, F.AD_TABLE_ID, F.DOCBASETYPE,
ioritz@14163
   228
      sum(F.AMTACCTDR) AS AMTACCTDR, sum(F.AMTACCTCR) AS AMTACCTCR, MIN(SEQNO) AS SEQNO, F.FACTACCTTYPE AS FACTACCTTYPE, F.AD_CLIENT_ID
carlos@642
   229
      FROM FACT_ACCT F, C_ACCTSCHEMA SC 
carlos@0
   230
      WHERE F.AD_CLIENT_ID IN ('1')
carlos@0
   231
      AND F.AD_ORG_ID IN('1')
carlos@642
   232
      AND f.C_ACCTSCHEMA_ID = SC.C_ACCTSCHEMA_ID
juanpablo@1605
   233
      AND F.Fact_Acct_Group_ID = ?
sandra@18518
   234
      GROUP BY f.C_ACCTSCHEMA_ID, SC.NAME, F.AD_TABLE_ID, F.DATEACCT, F.ACCTDESCRIPTION, F.ACCTVALUE, F.DOCBASETYPE, F.RECORD_ID, 
sathiyan@8256
   235
      F.FACT_ACCT_GROUP_ID, F.ACCOUNT_ID,F.FACTACCTTYPE,
ioritz@17531
   236
      (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), F.AD_CLIENT_ID
sandra@18513
   237
      HAVING (sum(F.AMTACCTDR) <> 0 OR sum(F.AMTACCTCR) <> 0)) AA
sandra@18513
   238
      LEFT JOIN (select * from AD_REF_LIST_V WHERE AD_REFERENCE_ID = '183'  AND AD_LANGUAGE=?) AR  ON AR.VALUE=AA.DOCBASETYPE 
sandra@18518
   239
      ORDER BY SCHEMA_NAME, DATEACCT, FACTACCTTYPE2, IDENTIFIER, AA.AMTACCTDR DESC, AA.AMTACCTCR DESC, SEQNO
carlos@0
   240
      ]]></Sql>
carlos@0
   241
    <Field name="rownum" value="count"/>
carlos@0
   242
    <Parameter name="adUserClient" type="replace" optional="true" after="WHERE F.AD_CLIENT_ID IN (" text="'1'"/>
carlos@0
   243
    <Parameter name="adUserOrg" type="replace" optional="true" after="AND F.AD_ORG_ID IN(" text="'1'"/>
carlos@0
   244
    <Parameter name="factAcctGroupId"/>
sandra@18494
   245
    <Parameter name="paramLanguage"></Parameter>
carlos@0
   246
  </SqlMethod>
carlos@0
   247
  <SqlMethod name="selectCountDirect2" type="preparedStatement" return="string" default="0">
carlos@0
   248
    <SqlMethodComment></SqlMethodComment>
carlos@0
   249
    <Sql>
carlos@0
   250
    <![CDATA[
carlos@0
   251
        SELECT COUNT(FACT_ACCT_GROUP_ID) AS TOTAL
carlos@0
   252
        FROM (
carlos@0
   253
        SELECT F.DATEACCT, F.FACT_ACCT_GROUP_ID  
carlos@0
   254
        FROM AD_TABLE T left join AD_TAB TB    on T.AD_TABLE_ID = TB.AD_TABLE_ID
carlos@0
   255
                        left join AD_COLUMN C  on T.AD_TABLE_ID = C.AD_TABLE_ID
carlos@0
   256
                                              AND C.ISKEY = 'Y'
carlos@0
   257
                        left join FACT_ACCT F  on F.AD_TABLE_ID = T.AD_TABLE_ID 
carlos@0
   258
                        left join AD_WINDOW W  on TB.AD_WINDOW_ID = W.AD_WINDOW_ID
carlos@0
   259
        WHERE F.AD_CLIENT_ID IN ('1')
carlos@0
   260
        AND F.AD_ORG_ID IN('1')
carlos@0
   261
        AND 1=1
juanpablo@1605
   262
        and f.fact_acct_group_id = ?
carlos@0
   263
        AND (CASE (SELECT MAX(ISSOTRX) FROM C_DOCTYPE D 
carlos@0
   264
        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
   265
        GROUP BY F.DATEACCT, F.FACT_ACCT_GROUP_ID) AA
juanpablo@1605
   266
        WHERE (DATEACCT< to_date(?) OR (DATEACCT=to_date(?) AND FACT_ACCT_GROUP_ID < ?))
carlos@0
   267
      ]]></Sql>
carlos@0
   268
    <Parameter name="adUserClient" type="replace" optional="true" after="WHERE F.AD_CLIENT_ID IN (" text="'1'"/>
carlos@0
   269
    <Parameter name="adUserOrg" type="replace" optional="true" after="AND F.AD_ORG_ID IN(" text="'1'"/>
carlos@0
   270
    <Parameter name="factAcctGroupId"/>
carlos@0
   271
    <Parameter name="dateAcct"/>
carlos@0
   272
    <Parameter name="dateAcct"/>
carlos@0
   273
    <Parameter name="identifier"/>
carlos@0
   274
  </SqlMethod>
carlos@0
   275
  <SqlMethod name="selectCompany" type="preparedStatement" return="string" default="0">
carlos@0
   276
    <SqlMethodComment></SqlMethodComment>
carlos@0
   277
    <Sql>
carlos@0
   278
    <![CDATA[
carlos@0
   279
        SELECT NAME
carlos@0
   280
        FROM AD_CLIENT
juanpablo@1605
   281
        WHERE AD_CLIENT_ID = ?
carlos@0
   282
      ]]></Sql>
carlos@0
   283
    <Parameter name="client"/>
carlos@0
   284
  </SqlMethod>
egoitz@11621
   285
  <SqlMethod name="selectOrg" type="preparedStatement" return="string" default="0">
egoitz@11621
   286
    <SqlMethodComment></SqlMethodComment>
egoitz@11621
   287
    <Sql>
egoitz@11621
   288
    <![CDATA[
egoitz@11621
   289
        SELECT NAME
egoitz@11621
   290
        FROM AD_ORG
egoitz@11621
   291
        WHERE AD_ORG_ID = ?
egoitz@11621
   292
      ]]></Sql>
egoitz@11621
   293
    <Parameter name="orgId"/>
egoitz@11621
   294
  </SqlMethod>
david@11040
   295
  <SqlMethod name="selectOrgTaxID" type="preparedStatement" return="string">
david@11040
   296
    <SqlMethodComment></SqlMethodComment>
david@11040
   297
    <Sql>
david@11040
   298
    <![CDATA[
david@11040
   299
        SELECT MIN(I.TAXID)
david@11040
   300
        FROM AD_ORGINFO I
david@11040
   301
        WHERE I.AD_ORG_ID = ?
david@11040
   302
      ]]></Sql>
david@11040
   303
    <Parameter name="org"/>
david@11040
   304
  </SqlMethod>
carlos@0
   305
</SqlClass>