src/org/openbravo/erpCommon/ad_reports/GenerateModel347_data.xsql
author David Baz Fayos <david.baz@openbravo.com>
Tue, 27 May 2008 15:26:10 +0000
changeset 1044 8691bbc94032
parent 423 ecf368072c48
child 1285 78a451d563c4
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="GenerateModel347Data" 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@423
    31
      SELECT CONSTANT1, MODEL, EJERCICIO, NIF_DECLARANTE, NOMBRE_DECLARANTE, SOPORTE, PERSONA, NUMERO_JUSTIF, TIPO_DECLARACION, TIPO_DECLARACION2, NUMERO_DEC, LPAD(TO_CHAR(COUNT(DISTINCT C_BPARTNER_ID)), 9, '0') AS NUMERO_PERSONAS, LPAD(REPLACE(TO_CHAR(SUM(TO_NUMBER(IMPORTE)), 'FM000000000000.00'), '.', ''), 15, '0') AS IMPORTE, TOTAL_INMUEBLES, IMPORTE_TOTAL_INMUEBLES, BLANCOS, '' AS NIF_DECLARADO, '' AS NIF_REPRESENTANTE, '' AS NOMBRE_SOCIAL, '' AS CODIGO_PROVINCIA, '' AS CODIGO_PAIS, '' AS CLAVE_CODIGO, '' AS OPERACION_SEGURO, '' AS ARRENDAMIENTO
carlos@0
    32
      FROM (SELECT C_INVOICE.C_BPARTNER_ID, to_char('1') AS CONSTANT1, to_char('347') AS MODEL, to_number(TO_CHAR(now(), 'YYYY'))-1 AS EJERCICIO, 
carlos@0
    33
      COALESCE(LPAD(replace(translate((SELECT MAX(TAXID) FROM AD_ORGINFO),'.-_/'||CHR(92),' '),' ',''), 9, '0'), '000000000') AS NIF_DECLARANTE, 
carlos@0
    34
      COALESCE(TO_CHAR(RPAD(UPPER(AD_CLIENT.DESCRIPTION), 40, ' ')), '                                        ') AS NOMBRE_DECLARANTE, 
carlos@0
    35
      to_char('D') AS SOPORTE,
carlos@0
    36
      COALESCE(RPAD('000000000'||'APELLIDOS NOMBRE', 49, ' '), '                                                 ') AS PERSONA, 
carlos@0
    37
      '348000000'||to_number(TO_CHAR(now(), 'YYYY'))-1 AS NUMERO_JUSTIF, 
carlos@0
    38
      (CASE (to_char(?)) WHEN 'New' THEN ' ' WHEN 'Sustitutive' THEN ' ' WHEN 'Complementary' THEN 'C' END) AS TIPO_DECLARACION, 
carlos@0
    39
      (CASE (to_char(?)) WHEN 'New' THEN ' ' WHEN 'Sustitutive' THEN 'S' WHEN 'Complementary' THEN ' ' END) AS TIPO_DECLARACION2,
carlos@423
    40
      LPAD(TO_CHAR(CASE (to_char(?)) WHEN 'Sustitutive' THEN TO_NUMBER(?) ELSE 0 END), 13, '0') AS NUMERO_DEC, 
carlos@0
    41
      CASE WHEN SUM(C_INVOICE.GRANDTOTAL) IS NULL THEN '  ' ELSE TO_CHAR(
carlos@0
    42
      ROUND(SUM(C_INVOICE.TOTALLINES+(SELECT SUM(IT.TAXAMT)
carlos@0
    43
                                                     FROM C_INVOICETAX IT
carlos@0
    44
                                                    WHERE IT.C_INVOICE_ID = C_INVOICE.C_INVOICE_ID
carlos@0
    45
                                                      AND EXISTS (SELECT 1                                                      
carlos@0
    46
                                                                    FROM C_TAX T LEFT JOIN C_REGION R ON R.C_REGION_ID = T.TO_REGION_ID,
carlos@0
    47
                                                                         C_COUNTRY C1, C_COUNTRY C2
carlos@0
    48
                                                                   WHERE COALESCE(T.C_COUNTRY_ID,0) = C1.C_COUNTRY_ID 
carlos@0
    49
                                                                     AND COALESCE(T.TO_COUNTRY_ID,0)  = C2.C_COUNTRY_ID
carlos@0
    50
                                                                     AND C1.COUNTRYCODE = 'ES'
carlos@0
    51
                                                                     AND C2.COUNTRYCODE = 'ES'
carlos@0
    52
                                                                     AND IT.C_INVOICE_ID = C_INVOICE.C_INVOICE_ID
carlos@0
    53
                                                                     AND T.C_TAX_ID = IT.C_TAX_ID
carlos@0
    54
                                                                     AND UPPER(T.NAME) NOT LIKE '%I%R%P%F%'
carlos@0
    55
                                                                     AND UPPER(T.NAME) NOT LIKE '%RETEN%'
carlos@0
    56
                                                                     AND (R.C_REGION_ID IS NULL
carlos@0
    57
                                                                          OR R.NAME NOT IN ('BALEARES', 'CEUTA', 'MELILLA', 'TENERIFE', 'LAS PALMAS'))
carlos@0
    58
                                                                UNION       
carlos@0
    59
                                                                SELECT 1
carlos@0
    60
                                                                    FROM C_TAX_ZONE T LEFT JOIN C_REGION R ON R.C_REGION_ID = T.TO_REGION_ID,
carlos@0
    61
                                                                         C_COUNTRY C1, C_COUNTRY C2, C_TAX CT
carlos@0
    62
                                                                   WHERE COALESCE(T.FROM_COUNTRY_ID,0) = C1.C_COUNTRY_ID 
carlos@0
    63
                                                                     AND COALESCE(T.TO_COUNTRY_ID,0)  = C2.C_COUNTRY_ID
carlos@0
    64
                                                                     AND C1.COUNTRYCODE = 'ES'
carlos@0
    65
                                                                     AND C2.COUNTRYCODE = 'ES'
carlos@0
    66
                                                                     AND IT.C_INVOICE_ID = C_INVOICE.C_INVOICE_ID
carlos@0
    67
                                                                     AND T.C_TAX_ID = IT.C_TAX_ID
carlos@0
    68
                                                                     AND CT.C_TAX_ID = T.C_TAX_ID
carlos@0
    69
                                                                     AND UPPER(CT.NAME) NOT LIKE '%I%R%P%F%'
carlos@0
    70
                                                                     AND UPPER(CT.NAME) NOT LIKE '%RETEN%'
carlos@0
    71
                                                                     AND (R.C_REGION_ID IS NULL
carlos@0
    72
                                                                          OR R.NAME NOT IN ('BALEARES', 'CEUTA', 'MELILLA', 'TENERIFE', 'LAS PALMAS'))))), 2)) END AS IMPORTE,
carlos@423
    73
      LPAD('0', 9, '0') AS TOTAL_INMUEBLES, LPAD('0', 15, '0') AS IMPORTE_TOTAL_INMUEBLES,  
carlos@0
    74
      LPAD(' ', 67, ' ') AS BLANCOS
carlos@0
    75
      FROM C_INVOICE, AD_CLIENT, C_BPARTNER_LOCATION, C_LOCATION
carlos@0
    76
      WHERE C_INVOICE.AD_CLIENT_ID = AD_CLIENT.AD_CLIENT_ID
carlos@0
    77
      AND C_INVOICE.C_BPARTNER_LOCATION_ID = C_BPARTNER_LOCATION.C_BPARTNER_LOCATION_ID
carlos@0
    78
      AND C_BPARTNER_LOCATION.C_LOCATION_ID = C_LOCATION.C_LOCATION_ID
carlos@0
    79
      AND EXISTS (SELECT 1
carlos@0
    80
                    FROM C_TAX T LEFT JOIN C_REGION R ON R.C_REGION_ID = T.TO_REGION_ID,
carlos@0
    81
                         C_COUNTRY C1, C_COUNTRY C2,
carlos@0
    82
                         C_INVOICETAX IT
carlos@0
    83
                   WHERE COALESCE(T.C_COUNTRY_ID,0) = C1.C_COUNTRY_ID 
carlos@0
    84
                     AND COALESCE(T.TO_COUNTRY_ID,0)  = C2.C_COUNTRY_ID
carlos@0
    85
                     AND C1.COUNTRYCODE = 'ES'
carlos@0
    86
                     AND C2.COUNTRYCODE = 'ES'
carlos@0
    87
                     AND IT.C_INVOICE_ID = C_INVOICE.C_INVOICE_ID
carlos@0
    88
                     AND T.C_TAX_ID = IT.C_TAX_ID
carlos@0
    89
                     AND UPPER(T.NAME) NOT LIKE '%I%R%P%F%'
carlos@0
    90
                     AND UPPER(T.NAME) NOT LIKE '%RETEN%'
carlos@0
    91
                     AND (R.C_REGION_ID IS NULL
carlos@0
    92
                          OR R.NAME NOT IN ('BALEARES', 'CEUTA', 'MELILLA', 'TENERIFE', 'LAS PALMAS'))
carlos@0
    93
                UNION       
carlos@0
    94
                SELECT 1
carlos@0
    95
                    FROM C_TAX_ZONE T LEFT JOIN C_REGION R ON R.C_REGION_ID = T.TO_REGION_ID,
carlos@0
    96
                         C_COUNTRY C1, C_COUNTRY C2,
carlos@0
    97
                         C_INVOICETAX IT, C_TAX CT
carlos@0
    98
                   WHERE COALESCE(T.FROM_COUNTRY_ID,0) = C1.C_COUNTRY_ID 
carlos@0
    99
                     AND COALESCE(T.TO_COUNTRY_ID,0)  = C2.C_COUNTRY_ID
carlos@0
   100
                     AND C1.COUNTRYCODE = 'ES'
carlos@0
   101
                     AND C2.COUNTRYCODE = 'ES'
carlos@0
   102
                     AND IT.C_INVOICE_ID = C_INVOICE.C_INVOICE_ID
carlos@0
   103
                     AND T.C_TAX_ID = IT.C_TAX_ID
carlos@0
   104
                     AND CT.C_TAX_ID = T.C_TAX_ID
carlos@0
   105
                     AND UPPER(CT.NAME) NOT LIKE '%I%R%P%F%'
carlos@0
   106
                     AND UPPER(CT.NAME) NOT LIKE '%RETEN%'
carlos@0
   107
                     AND (R.C_REGION_ID IS NULL
carlos@0
   108
                          OR R.NAME NOT IN ('BALEARES', 'CEUTA', 'MELILLA', 'TENERIFE', 'LAS PALMAS')))
carlos@0
   109
      AND C_INVOICE.DATEINVOICED >= TO_DATE(?)
carlos@0
   110
      AND C_INVOICE.DATEINVOICED < TO_DATE(?)
carlos@0
   111
      AND C_INVOICE.AD_ORG_ID IN ('1')
carlos@0
   112
      GROUP BY AD_CLIENT.DESCRIPTION, C_INVOICE.C_BPARTNER_ID, C_INVOICE.ISSOTRX
carlos@0
   113
      HAVING ROUND(SUM(C_INVOICE.TOTALLINES+(SELECT SUM(IT.TAXAMT)
carlos@0
   114
                                                     FROM C_INVOICETAX IT
carlos@0
   115
                                                    WHERE IT.C_INVOICE_ID = C_INVOICE.C_INVOICE_ID
carlos@0
   116
                                                      AND EXISTS (SELECT 1                                                      
carlos@0
   117
                                                                    FROM C_TAX T LEFT JOIN C_REGION R ON R.C_REGION_ID = T.TO_REGION_ID,
carlos@0
   118
                                                                         C_COUNTRY C1, C_COUNTRY C2
carlos@0
   119
                                                                   WHERE COALESCE(T.C_COUNTRY_ID,0) = C1.C_COUNTRY_ID 
carlos@0
   120
                                                                     AND COALESCE(T.TO_COUNTRY_ID,0)  = C2.C_COUNTRY_ID
carlos@0
   121
                                                                     AND C1.COUNTRYCODE = 'ES'
carlos@0
   122
                                                                     AND C2.COUNTRYCODE = 'ES'
carlos@0
   123
                                                                     AND IT.C_INVOICE_ID = C_INVOICE.C_INVOICE_ID
carlos@0
   124
                                                                     AND T.C_TAX_ID = IT.C_TAX_ID
carlos@0
   125
                                                                     AND UPPER(T.NAME) NOT LIKE '%I%R%P%F%'
carlos@0
   126
                                                                     AND T.RATE > 0
carlos@0
   127
                                                                     AND (R.C_REGION_ID IS NULL
carlos@0
   128
                                                                          OR R.NAME NOT IN ('BALEARES', 'CEUTA', 'MELILLA', 'TENERIFE', 'LAS PALMAS'))
carlos@0
   129
                                                                UNION       
carlos@0
   130
                                                                SELECT 1
carlos@0
   131
                                                                    FROM C_TAX_ZONE T LEFT JOIN C_REGION R ON R.C_REGION_ID = T.TO_REGION_ID,
carlos@0
   132
                                                                         C_COUNTRY C1, C_COUNTRY C2, C_TAX CT
carlos@0
   133
                                                                   WHERE COALESCE(T.FROM_COUNTRY_ID,0) = C1.C_COUNTRY_ID 
carlos@0
   134
                                                                     AND COALESCE(T.TO_COUNTRY_ID,0)  = C2.C_COUNTRY_ID
carlos@0
   135
                                                                     AND C1.COUNTRYCODE = 'ES'
carlos@0
   136
                                                                     AND C2.COUNTRYCODE = 'ES'
carlos@0
   137
                                                                     AND IT.C_INVOICE_ID = C_INVOICE.C_INVOICE_ID
carlos@0
   138
                                                                     AND T.C_TAX_ID = IT.C_TAX_ID
carlos@0
   139
                                                                     AND CT.C_TAX_ID = T.C_TAX_ID
carlos@0
   140
                                                                     AND UPPER(CT.NAME) NOT LIKE '%I%R%P%F%'
carlos@0
   141
                                                                     AND UPPER(CT.NAME) NOT LIKE '%RETEN%'
carlos@0
   142
                                                                     AND (R.C_REGION_ID IS NULL
carlos@0
   143
                                                                          OR R.NAME NOT IN ('BALEARES', 'CEUTA', 'MELILLA', 'TENERIFE', 'LAS PALMAS'))))), 2) >= 3005.06) AA
carlos@0
   144
      GROUP BY CONSTANT1, MODEL, EJERCICIO, NIF_DECLARANTE, NOMBRE_DECLARANTE, SOPORTE, PERSONA, NUMERO_JUSTIF, TIPO_DECLARACION, TIPO_DECLARACION2, NUMERO_DEC, TOTAL_INMUEBLES, IMPORTE_TOTAL_INMUEBLES, BLANCOS
carlos@0
   145
     ]]></Sql>
carlos@0
   146
     <Parameter name="type"/>
carlos@0
   147
     <Parameter name="type"/>
carlos@0
   148
     <Parameter name="type"/>
carlos@0
   149
     <Parameter name="complementar"/>
carlos@0
   150
     <Parameter name="dateFrom"/>
carlos@0
   151
     <Parameter name="dateTo"/>
carlos@0
   152
      <Parameter name="adOrgId" optional="true" type="replace" after="AND C_INVOICE.AD_ORG_ID IN (" text="'1'"/>
carlos@0
   153
  </SqlMethod>
carlos@0
   154
carlos@0
   155
  <SqlMethod name="set" type="constant" return="multiple">
carlos@0
   156
      <SqlMethodComment></SqlMethodComment>
carlos@0
   157
      <Sql></Sql>
carlos@0
   158
  </SqlMethod>
carlos@0
   159
carlos@0
   160
  <SqlMethod name="selectType2" type="preparedStatement" return="multiple">
carlos@0
   161
  <SqlMethodComment></SqlMethodComment>
carlos@0
   162
    <Sql>
carlos@0
   163
    <![CDATA[
carlos@0
   164
      SELECT '2' AS CONSTANT1, '347' AS MODEL, 
carlos@0
   165
      to_number(TO_CHAR(now(), 'YYYY'))-1 AS EJERCICIO, 
carlos@0
   166
      COALESCE(LPAD(replace(translate((SELECT MAX(TAXID) FROM AD_ORGINFO),'.-_/'||CHR(92),' '),' ',''), 9, '0'), '000000000') AS NIF_DECLARANTE, 
carlos@0
   167
      COALESCE(LPAD((CASE BP.TAXID WHEN NULL THEN '0' ELSE replace(translate(BP.TAXID,'.-_/'||CHR(92),' '),' ','') END), 9, '0'), '000000000') AS NIF_DECLARADO, 
carlos@0
   168
      COALESCE(LPAD(' ', 9, ' '), '         ') AS NIF_REPRESENTANTE, 
carlos@0
   169
      COALESCE(TO_CHAR(RPAD(UPPER((CASE BP.NAME2 WHEN NULL THEN BP.NAME ELSE BP.NAME2 END)), 40, ' ')), '                                        ') AS NOMBRE_SOCIAL, 
carlos@0
   170
      'D' AS TIPO_DECLARACION, 
carlos@0
   171
      /*LPAD(R.value,2,'0') AS CODIGO_PROVINCIA, this should be like this when region.value is added*/
carlos@0
   172
      COALESCE(SUBSTR(L.POSTAL,1,2), '  ') AS CODIGO_PROVINCIA, 
carlos@0
   173
      '000' AS CODIGO_PAIS, 
carlos@0
   174
      (CASE I.ISSOTRX WHEN 'Y' THEN 'B' ELSE 'A' END) AS CLAVE_CODIGO, 
carlos@0
   175
      COALESCE(LPAD(REPLACE(TO_CHAR(ROUND(SUM(I.TOTALLINES+(SELECT SUM(IT.TAXAMT)
carlos@0
   176
                                                     FROM C_INVOICETAX IT
carlos@0
   177
                                                    WHERE IT.C_INVOICE_ID = I.C_INVOICE_ID
carlos@0
   178
                                                      AND EXISTS (SELECT 1                                                      
carlos@0
   179
                                                                    FROM C_TAX T LEFT JOIN C_REGION R ON R.C_REGION_ID = T.TO_REGION_ID,
carlos@0
   180
                                                                         C_COUNTRY C1, C_COUNTRY C2
carlos@0
   181
                                                                   WHERE COALESCE(T.C_COUNTRY_ID,0) = C1.C_COUNTRY_ID 
carlos@0
   182
                                                                     AND COALESCE(T.TO_COUNTRY_ID,0)  = C2.C_COUNTRY_ID
carlos@0
   183
                                                                     AND C1.COUNTRYCODE = 'ES'
carlos@0
   184
                                                                     AND C2.COUNTRYCODE = 'ES'
carlos@0
   185
                                                                     AND IT.C_INVOICE_ID = I.C_INVOICE_ID
carlos@0
   186
                                                                     AND T.C_TAX_ID = IT.C_TAX_ID
carlos@0
   187
                                                                     AND UPPER(T.NAME) NOT LIKE '%I%R%P%F%'
carlos@0
   188
                                                                     AND UPPER(T.NAME) NOT LIKE '%RETEN%'
carlos@0
   189
                                                                     AND (R.C_REGION_ID IS NULL
carlos@0
   190
                                                                          OR R.NAME NOT IN ('BALEARES', 'CEUTA', 'MELILLA', 'TENERIFE', 'LAS PALMAS'))
carlos@0
   191
                                                                UNION       
carlos@0
   192
                                                                SELECT 1
carlos@0
   193
                                                                    FROM C_TAX_ZONE T LEFT JOIN C_REGION R ON R.C_REGION_ID = T.TO_REGION_ID,
carlos@0
   194
                                                                         C_COUNTRY C1, C_COUNTRY C2, C_TAX CT
carlos@0
   195
                                                                   WHERE COALESCE(T.FROM_COUNTRY_ID,0) = C1.C_COUNTRY_ID 
carlos@0
   196
                                                                     AND COALESCE(T.TO_COUNTRY_ID,0)  = C2.C_COUNTRY_ID
carlos@0
   197
                                                                     AND C1.COUNTRYCODE = 'ES'
carlos@0
   198
                                                                     AND C2.COUNTRYCODE = 'ES'
carlos@0
   199
                                                                     AND IT.C_INVOICE_ID = I.C_INVOICE_ID
carlos@0
   200
                                                                     AND T.C_TAX_ID = IT.C_TAX_ID
carlos@0
   201
                                                                     AND CT.C_TAX_ID = T.C_TAX_ID
carlos@0
   202
                                                                     AND UPPER(CT.NAME) NOT LIKE '%I%R%P%F%'
carlos@0
   203
                                                                     AND UPPER(CT.NAME) NOT LIKE '%RETEN%'
carlos@0
   204
                                                                     AND (R.C_REGION_ID IS NULL
carlos@0
   205
                                                                          OR R.NAME NOT IN ('BALEARES', 'CEUTA', 'MELILLA', 'TENERIFE', 'LAS PALMAS'))))), 2), 'FM000000000000.00'), '.', ''), 15, '0'), '000000000000000') AS IMPORTE,
carlos@0
   206
      ' ' AS OPERACION_SEGURO, 
carlos@0
   207
      ' ' AS ARRENDAMIENTO, 
carlos@0
   208
      LPAD(' ', 151, ' ') AS BLANCOS
carlos@0
   209
      FROM C_INVOICE  I, 
carlos@0
   210
           C_BPARTNER BP, 
carlos@0
   211
           C_BPARTNER_LOCATION BPL, 
carlos@0
   212
           C_LOCATION L,
carlos@0
   213
           C_REGION   R
carlos@0
   214
      WHERE I.C_BPARTNER_ID = BP.C_BPARTNER_ID
carlos@0
   215
      AND I.C_BPARTNER_LOCATION_ID = BPL.C_BPARTNER_LOCATION_ID
carlos@0
   216
      AND BPL.C_LOCATION_ID = L.C_LOCATION_ID 
carlos@0
   217
      AND R.C_REGION_ID = L.C_REGION_ID
carlos@0
   218
      AND I.DATEINVOICED >= TO_DATE(?)
carlos@0
   219
      AND I.DATEINVOICED < TO_DATE(?)
carlos@0
   220
      AND EXISTS (SELECT 1
carlos@0
   221
                    FROM C_TAX T LEFT JOIN C_REGION R ON R.C_REGION_ID = T.TO_REGION_ID,
carlos@0
   222
                         C_COUNTRY C1, C_COUNTRY C2,
carlos@0
   223
                         C_INVOICETAX IT
carlos@0
   224
                   WHERE COALESCE(T.C_COUNTRY_ID,0) = C1.C_COUNTRY_ID 
carlos@0
   225
                     AND COALESCE(T.TO_COUNTRY_ID,0)  = C2.C_COUNTRY_ID
carlos@0
   226
                     AND C1.COUNTRYCODE = 'ES'
carlos@0
   227
                     AND C2.COUNTRYCODE = 'ES'
carlos@0
   228
                     AND IT.C_INVOICE_ID = I.C_INVOICE_ID
carlos@0
   229
                     AND T.C_TAX_ID = IT.C_TAX_ID
carlos@0
   230
                     AND UPPER(T.NAME) NOT LIKE '%I%R%P%F%'
carlos@0
   231
                     AND UPPER(T.NAME) NOT LIKE '%RETEN%'
carlos@0
   232
                     AND (R.C_REGION_ID IS NULL
carlos@0
   233
                          OR R.NAME NOT IN ('BALEARES', 'CEUTA', 'MELILLA', 'TENERIFE', 'LAS PALMAS'))
carlos@0
   234
                UNION       
carlos@0
   235
                SELECT 1
carlos@0
   236
                    FROM C_TAX_ZONE T LEFT JOIN C_REGION R ON R.C_REGION_ID = T.TO_REGION_ID,
carlos@0
   237
                         C_COUNTRY C1, C_COUNTRY C2,
carlos@0
   238
                         C_INVOICETAX IT, C_TAX CT
carlos@0
   239
                   WHERE COALESCE(T.FROM_COUNTRY_ID,0) = C1.C_COUNTRY_ID 
carlos@0
   240
                     AND COALESCE(T.TO_COUNTRY_ID,0)  = C2.C_COUNTRY_ID
carlos@0
   241
                     AND C1.COUNTRYCODE = 'ES'
carlos@0
   242
                     AND C2.COUNTRYCODE = 'ES'
carlos@0
   243
                     AND IT.C_INVOICE_ID = I.C_INVOICE_ID
carlos@0
   244
                     AND T.C_TAX_ID = IT.C_TAX_ID
carlos@0
   245
                     AND CT.C_TAX_ID = T.C_TAX_ID
carlos@0
   246
                     AND UPPER(CT.NAME) NOT LIKE '%I%R%P%F%'
carlos@0
   247
                     AND UPPER(CT.NAME) NOT LIKE '%RETEN%'
carlos@0
   248
                     AND (R.C_REGION_ID IS NULL
carlos@0
   249
                          OR R.NAME NOT IN ('BALEARES', 'CEUTA', 'MELILLA', 'TENERIFE', 'LAS PALMAS')))
carlos@0
   250
      AND I.AD_ORG_ID IN ('1')      
carlos@0
   251
      GROUP BY BP.NAME, BP.NAME2, BP.TAXID, I.ISSOTRX, SUBSTR(L.POSTAL,1,2)
carlos@0
   252
      HAVING ROUND(SUM(I.TOTALLINES+(SELECT SUM(IT.TAXAMT)
carlos@0
   253
                                                     FROM C_INVOICETAX IT
carlos@0
   254
                                                    WHERE IT.C_INVOICE_ID = I.C_INVOICE_ID
carlos@0
   255
                                                      AND EXISTS (SELECT 1                                                      
carlos@0
   256
                                                                    FROM C_TAX T LEFT JOIN C_REGION R ON R.C_REGION_ID = T.TO_REGION_ID,
carlos@0
   257
                                                                         C_COUNTRY C1, C_COUNTRY C2
carlos@0
   258
                                                                   WHERE COALESCE(T.C_COUNTRY_ID,0) = C1.C_COUNTRY_ID 
carlos@0
   259
                                                                     AND COALESCE(T.TO_COUNTRY_ID,0)  = C2.C_COUNTRY_ID
carlos@0
   260
                                                                     AND C1.COUNTRYCODE = 'ES'
carlos@0
   261
                                                                     AND C2.COUNTRYCODE = 'ES'
carlos@0
   262
                                                                     AND IT.C_INVOICE_ID = I.C_INVOICE_ID
carlos@0
   263
                                                                     AND T.C_TAX_ID = IT.C_TAX_ID
carlos@0
   264
                                                                     AND UPPER(T.NAME) NOT LIKE '%I%R%P%F%'
carlos@0
   265
                                                                     AND T.RATE > 0
carlos@0
   266
                                                                     AND (R.C_REGION_ID IS NULL
carlos@0
   267
                                                                          OR R.NAME NOT IN ('BALEARES', 'CEUTA', 'MELILLA', 'TENERIFE', 'LAS PALMAS'))
carlos@0
   268
                                                                UNION       
carlos@0
   269
                                                                SELECT 1
carlos@0
   270
                                                                    FROM C_TAX_ZONE T LEFT JOIN C_REGION R ON R.C_REGION_ID = T.TO_REGION_ID,
carlos@0
   271
                                                                         C_COUNTRY C1, C_COUNTRY C2, C_TAX CT
carlos@0
   272
                                                                   WHERE COALESCE(T.FROM_COUNTRY_ID,0) = C1.C_COUNTRY_ID 
carlos@0
   273
                                                                     AND COALESCE(T.TO_COUNTRY_ID,0)  = C2.C_COUNTRY_ID
carlos@0
   274
                                                                     AND C1.COUNTRYCODE = 'ES'
carlos@0
   275
                                                                     AND C2.COUNTRYCODE = 'ES'
carlos@0
   276
                                                                     AND IT.C_INVOICE_ID = I.C_INVOICE_ID
carlos@0
   277
                                                                     AND T.C_TAX_ID = IT.C_TAX_ID
carlos@0
   278
                                                                     AND CT.C_TAX_ID = T.C_TAX_ID
carlos@0
   279
                                                                     AND UPPER(CT.NAME) NOT LIKE '%I%R%P%F%'
carlos@0
   280
                                                                     AND UPPER(CT.NAME) NOT LIKE '%RETEN%'
carlos@0
   281
                                                                     AND (R.C_REGION_ID IS NULL
carlos@0
   282
                                                                          OR R.NAME NOT IN ('BALEARES', 'CEUTA', 'MELILLA', 'TENERIFE', 'LAS PALMAS'))))), 2) >= 3005.06
carlos@0
   283
      
carlos@0
   284
     ]]></Sql>
carlos@0
   285
    <Parameter name="dateFrom"/>
carlos@0
   286
    <Parameter name="dateTo"/>
carlos@0
   287
    <Parameter name="adOrgId" optional="true" type="replace" after="AND I.AD_ORG_ID IN (" text="'1'"/>
carlos@0
   288
  </SqlMethod>
carlos@0
   289
</SqlClass>