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
<?xml version="1.0" encoding="UTF-8" ?>
<!--
 *************************************************************************
 * The contents of this file are subject to the Openbravo  Public  License
 * Version  1.0  (the  "License"),  being   the  Mozilla   Public  License
 * Version 1.1  with a permitted attribution clause; you may not  use this
 * file except in compliance with the License. You  may  obtain  a copy of
 * the License at http://www.openbravo.com/legal/license.html 
 * Software distributed under the License  is  distributed  on  an "AS IS"
 * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
 * License for the specific  language  governing  rights  and  limitations
 * under the License. 
 * The Original Code is Openbravo ERP. 
 * The Initial Developer of the Original Code is Openbravo SL 
 * All portions are Copyright (C) 2001-2006 Openbravo SL 
 * All Rights Reserved. 
 * Contributor(s):  ______________________________________.
 ************************************************************************
-->





<SqlClass name="GenerateModel347Data" package="org.openbravo.erpCommon.ad_reports">
  <SqlClassComment></SqlClassComment>
  <SqlMethod name="select" type="preparedStatement" return="multiple">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
    <![CDATA[
      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
      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, 
      COALESCE(LPAD(replace(translate((SELECT MAX(TAXID) FROM AD_ORGINFO),'.-_/'||CHR(92),' '),' ',''), 9, '0'), '000000000') AS NIF_DECLARANTE, 
      COALESCE(TO_CHAR(RPAD(UPPER(AD_CLIENT.DESCRIPTION), 40, ' ')), '                                        ') AS NOMBRE_DECLARANTE, 
      to_char('D') AS SOPORTE,
      COALESCE(RPAD('000000000'||'APELLIDOS NOMBRE', 49, ' '), '                                                 ') AS PERSONA, 
      '348000000'||to_number(TO_CHAR(now(), 'YYYY'))-1 AS NUMERO_JUSTIF, 
      (CASE (to_char(?)) WHEN 'New' THEN ' ' WHEN 'Sustitutive' THEN ' ' WHEN 'Complementary' THEN 'C' END) AS TIPO_DECLARACION, 
      (CASE (to_char(?)) WHEN 'New' THEN ' ' WHEN 'Sustitutive' THEN 'S' WHEN 'Complementary' THEN ' ' END) AS TIPO_DECLARACION2,
      LPAD(TO_CHAR(CASE (to_char(?)) WHEN 'Sustitutive' THEN TO_NUMBER(?) ELSE 0 END), 13, '0') AS NUMERO_DEC, 
      CASE WHEN SUM(C_INVOICE.GRANDTOTAL) IS NULL THEN '  ' ELSE TO_CHAR(
      ROUND(SUM(C_INVOICE.TOTALLINES+(SELECT SUM(IT.TAXAMT)
                                                     FROM C_INVOICETAX IT
                                                    WHERE IT.C_INVOICE_ID = C_INVOICE.C_INVOICE_ID
                                                      AND EXISTS (SELECT 1                                                      
                                                                    FROM C_TAX T LEFT JOIN C_REGION R ON R.C_REGION_ID = T.TO_REGION_ID,
                                                                         C_COUNTRY C1, C_COUNTRY C2
                                                                   WHERE COALESCE(T.C_COUNTRY_ID,0) = C1.C_COUNTRY_ID 
                                                                     AND COALESCE(T.TO_COUNTRY_ID,0)  = C2.C_COUNTRY_ID
                                                                     AND C1.COUNTRYCODE = 'ES'
                                                                     AND C2.COUNTRYCODE = 'ES'
                                                                     AND IT.C_INVOICE_ID = C_INVOICE.C_INVOICE_ID
                                                                     AND T.C_TAX_ID = IT.C_TAX_ID
                                                                     AND UPPER(T.NAME) NOT LIKE '%I%R%P%F%'
                                                                     AND UPPER(T.NAME) NOT LIKE '%RETEN%'
                                                                     AND (R.C_REGION_ID IS NULL
                                                                          OR R.NAME NOT IN ('BALEARES', 'CEUTA', 'MELILLA', 'TENERIFE', 'LAS PALMAS'))
                                                                UNION       
                                                                SELECT 1
                                                                    FROM C_TAX_ZONE T LEFT JOIN C_REGION R ON R.C_REGION_ID = T.TO_REGION_ID,
                                                                         C_COUNTRY C1, C_COUNTRY C2, C_TAX CT
                                                                   WHERE COALESCE(T.FROM_COUNTRY_ID,0) = C1.C_COUNTRY_ID 
                                                                     AND COALESCE(T.TO_COUNTRY_ID,0)  = C2.C_COUNTRY_ID
                                                                     AND C1.COUNTRYCODE = 'ES'
                                                                     AND C2.COUNTRYCODE = 'ES'
                                                                     AND IT.C_INVOICE_ID = C_INVOICE.C_INVOICE_ID
                                                                     AND T.C_TAX_ID = IT.C_TAX_ID
                                                                     AND CT.C_TAX_ID = T.C_TAX_ID
                                                                     AND UPPER(CT.NAME) NOT LIKE '%I%R%P%F%'
                                                                     AND UPPER(CT.NAME) NOT LIKE '%RETEN%'
                                                                     AND (R.C_REGION_ID IS NULL
                                                                          OR R.NAME NOT IN ('BALEARES', 'CEUTA', 'MELILLA', 'TENERIFE', 'LAS PALMAS'))))), 2)) END AS IMPORTE,
      LPAD('0', 9, '0') AS TOTAL_INMUEBLES, LPAD('0', 15, '0') AS IMPORTE_TOTAL_INMUEBLES,  
      LPAD(' ', 67, ' ') AS BLANCOS
      FROM C_INVOICE, AD_CLIENT, C_BPARTNER_LOCATION, C_LOCATION
      WHERE C_INVOICE.AD_CLIENT_ID = AD_CLIENT.AD_CLIENT_ID
      AND C_INVOICE.C_BPARTNER_LOCATION_ID = C_BPARTNER_LOCATION.C_BPARTNER_LOCATION_ID
      AND C_BPARTNER_LOCATION.C_LOCATION_ID = C_LOCATION.C_LOCATION_ID
      AND EXISTS (SELECT 1
                    FROM C_TAX T LEFT JOIN C_REGION R ON R.C_REGION_ID = T.TO_REGION_ID,
                         C_COUNTRY C1, C_COUNTRY C2,
                         C_INVOICETAX IT
                   WHERE COALESCE(T.C_COUNTRY_ID,0) = C1.C_COUNTRY_ID 
                     AND COALESCE(T.TO_COUNTRY_ID,0)  = C2.C_COUNTRY_ID
                     AND C1.COUNTRYCODE = 'ES'
                     AND C2.COUNTRYCODE = 'ES'
                     AND IT.C_INVOICE_ID = C_INVOICE.C_INVOICE_ID
                     AND T.C_TAX_ID = IT.C_TAX_ID
                     AND UPPER(T.NAME) NOT LIKE '%I%R%P%F%'
                     AND UPPER(T.NAME) NOT LIKE '%RETEN%'
                     AND (R.C_REGION_ID IS NULL
                          OR R.NAME NOT IN ('BALEARES', 'CEUTA', 'MELILLA', 'TENERIFE', 'LAS PALMAS'))
                UNION       
                SELECT 1
                    FROM C_TAX_ZONE T LEFT JOIN C_REGION R ON R.C_REGION_ID = T.TO_REGION_ID,
                         C_COUNTRY C1, C_COUNTRY C2,
                         C_INVOICETAX IT, C_TAX CT
                   WHERE COALESCE(T.FROM_COUNTRY_ID,0) = C1.C_COUNTRY_ID 
                     AND COALESCE(T.TO_COUNTRY_ID,0)  = C2.C_COUNTRY_ID
                     AND C1.COUNTRYCODE = 'ES'
                     AND C2.COUNTRYCODE = 'ES'
                     AND IT.C_INVOICE_ID = C_INVOICE.C_INVOICE_ID
                     AND T.C_TAX_ID = IT.C_TAX_ID
                     AND CT.C_TAX_ID = T.C_TAX_ID
                     AND UPPER(CT.NAME) NOT LIKE '%I%R%P%F%'
                     AND UPPER(CT.NAME) NOT LIKE '%RETEN%'
                     AND (R.C_REGION_ID IS NULL
                          OR R.NAME NOT IN ('BALEARES', 'CEUTA', 'MELILLA', 'TENERIFE', 'LAS PALMAS')))
      AND C_INVOICE.DATEINVOICED >= TO_DATE(?)
      AND C_INVOICE.DATEINVOICED < TO_DATE(?)
      AND C_INVOICE.AD_ORG_ID IN ('1')
      GROUP BY AD_CLIENT.DESCRIPTION, C_INVOICE.C_BPARTNER_ID, C_INVOICE.ISSOTRX
      HAVING ROUND(SUM(C_INVOICE.TOTALLINES+(SELECT SUM(IT.TAXAMT)
                                                     FROM C_INVOICETAX IT
                                                    WHERE IT.C_INVOICE_ID = C_INVOICE.C_INVOICE_ID
                                                      AND EXISTS (SELECT 1                                                      
                                                                    FROM C_TAX T LEFT JOIN C_REGION R ON R.C_REGION_ID = T.TO_REGION_ID,
                                                                         C_COUNTRY C1, C_COUNTRY C2
                                                                   WHERE COALESCE(T.C_COUNTRY_ID,0) = C1.C_COUNTRY_ID 
                                                                     AND COALESCE(T.TO_COUNTRY_ID,0)  = C2.C_COUNTRY_ID
                                                                     AND C1.COUNTRYCODE = 'ES'
                                                                     AND C2.COUNTRYCODE = 'ES'
                                                                     AND IT.C_INVOICE_ID = C_INVOICE.C_INVOICE_ID
                                                                     AND T.C_TAX_ID = IT.C_TAX_ID
                                                                     AND UPPER(T.NAME) NOT LIKE '%I%R%P%F%'
                                                                     AND T.RATE > 0
                                                                     AND (R.C_REGION_ID IS NULL
                                                                          OR R.NAME NOT IN ('BALEARES', 'CEUTA', 'MELILLA', 'TENERIFE', 'LAS PALMAS'))
                                                                UNION       
                                                                SELECT 1
                                                                    FROM C_TAX_ZONE T LEFT JOIN C_REGION R ON R.C_REGION_ID = T.TO_REGION_ID,
                                                                         C_COUNTRY C1, C_COUNTRY C2, C_TAX CT
                                                                   WHERE COALESCE(T.FROM_COUNTRY_ID,0) = C1.C_COUNTRY_ID 
                                                                     AND COALESCE(T.TO_COUNTRY_ID,0)  = C2.C_COUNTRY_ID
                                                                     AND C1.COUNTRYCODE = 'ES'
                                                                     AND C2.COUNTRYCODE = 'ES'
                                                                     AND IT.C_INVOICE_ID = C_INVOICE.C_INVOICE_ID
                                                                     AND T.C_TAX_ID = IT.C_TAX_ID
                                                                     AND CT.C_TAX_ID = T.C_TAX_ID
                                                                     AND UPPER(CT.NAME) NOT LIKE '%I%R%P%F%'
                                                                     AND UPPER(CT.NAME) NOT LIKE '%RETEN%'
                                                                     AND (R.C_REGION_ID IS NULL
                                                                          OR R.NAME NOT IN ('BALEARES', 'CEUTA', 'MELILLA', 'TENERIFE', 'LAS PALMAS'))))), 2) >= 3005.06) AA
      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
     ]]></Sql>
     <Parameter name="type"/>
     <Parameter name="type"/>
     <Parameter name="type"/>
     <Parameter name="complementar"/>
     <Parameter name="dateFrom"/>
     <Parameter name="dateTo"/>
      <Parameter name="adOrgId" optional="true" type="replace" after="AND C_INVOICE.AD_ORG_ID IN (" text="'1'"/>
  </SqlMethod>

  <SqlMethod name="set" type="constant" return="multiple">
      <SqlMethodComment></SqlMethodComment>
      <Sql></Sql>
  </SqlMethod>

  <SqlMethod name="selectType2" type="preparedStatement" return="multiple">
  <SqlMethodComment></SqlMethodComment>
    <Sql>
    <![CDATA[
      SELECT '2' AS CONSTANT1, '347' AS MODEL, 
      to_number(TO_CHAR(now(), 'YYYY'))-1 AS EJERCICIO, 
      COALESCE(LPAD(replace(translate((SELECT MAX(TAXID) FROM AD_ORGINFO),'.-_/'||CHR(92),' '),' ',''), 9, '0'), '000000000') AS NIF_DECLARANTE, 
      COALESCE(LPAD((CASE BP.TAXID WHEN NULL THEN '0' ELSE replace(translate(BP.TAXID,'.-_/'||CHR(92),' '),' ','') END), 9, '0'), '000000000') AS NIF_DECLARADO, 
      COALESCE(LPAD(' ', 9, ' '), '         ') AS NIF_REPRESENTANTE, 
      COALESCE(TO_CHAR(RPAD(UPPER((CASE BP.NAME2 WHEN NULL THEN BP.NAME ELSE BP.NAME2 END)), 40, ' ')), '                                        ') AS NOMBRE_SOCIAL, 
      'D' AS TIPO_DECLARACION, 
      /*LPAD(R.value,2,'0') AS CODIGO_PROVINCIA, this should be like this when region.value is added*/
      COALESCE(SUBSTR(L.POSTAL,1,2), '  ') AS CODIGO_PROVINCIA, 
      '000' AS CODIGO_PAIS, 
      (CASE I.ISSOTRX WHEN 'Y' THEN 'B' ELSE 'A' END) AS CLAVE_CODIGO, 
      COALESCE(LPAD(REPLACE(TO_CHAR(ROUND(SUM(I.TOTALLINES+(SELECT SUM(IT.TAXAMT)
                                                     FROM C_INVOICETAX IT
                                                    WHERE IT.C_INVOICE_ID = I.C_INVOICE_ID
                                                      AND EXISTS (SELECT 1                                                      
                                                                    FROM C_TAX T LEFT JOIN C_REGION R ON R.C_REGION_ID = T.TO_REGION_ID,
                                                                         C_COUNTRY C1, C_COUNTRY C2
                                                                   WHERE COALESCE(T.C_COUNTRY_ID,0) = C1.C_COUNTRY_ID 
                                                                     AND COALESCE(T.TO_COUNTRY_ID,0)  = C2.C_COUNTRY_ID
                                                                     AND C1.COUNTRYCODE = 'ES'
                                                                     AND C2.COUNTRYCODE = 'ES'
                                                                     AND IT.C_INVOICE_ID = I.C_INVOICE_ID
                                                                     AND T.C_TAX_ID = IT.C_TAX_ID
                                                                     AND UPPER(T.NAME) NOT LIKE '%I%R%P%F%'
                                                                     AND UPPER(T.NAME) NOT LIKE '%RETEN%'
                                                                     AND (R.C_REGION_ID IS NULL
                                                                          OR R.NAME NOT IN ('BALEARES', 'CEUTA', 'MELILLA', 'TENERIFE', 'LAS PALMAS'))
                                                                UNION       
                                                                SELECT 1
                                                                    FROM C_TAX_ZONE T LEFT JOIN C_REGION R ON R.C_REGION_ID = T.TO_REGION_ID,
                                                                         C_COUNTRY C1, C_COUNTRY C2, C_TAX CT
                                                                   WHERE COALESCE(T.FROM_COUNTRY_ID,0) = C1.C_COUNTRY_ID 
                                                                     AND COALESCE(T.TO_COUNTRY_ID,0)  = C2.C_COUNTRY_ID
                                                                     AND C1.COUNTRYCODE = 'ES'
                                                                     AND C2.COUNTRYCODE = 'ES'
                                                                     AND IT.C_INVOICE_ID = I.C_INVOICE_ID
                                                                     AND T.C_TAX_ID = IT.C_TAX_ID
                                                                     AND CT.C_TAX_ID = T.C_TAX_ID
                                                                     AND UPPER(CT.NAME) NOT LIKE '%I%R%P%F%'
                                                                     AND UPPER(CT.NAME) NOT LIKE '%RETEN%'
                                                                     AND (R.C_REGION_ID IS NULL
                                                                          OR R.NAME NOT IN ('BALEARES', 'CEUTA', 'MELILLA', 'TENERIFE', 'LAS PALMAS'))))), 2), 'FM000000000000.00'), '.', ''), 15, '0'), '000000000000000') AS IMPORTE,
      ' ' AS OPERACION_SEGURO, 
      ' ' AS ARRENDAMIENTO, 
      LPAD(' ', 151, ' ') AS BLANCOS
      FROM C_INVOICE  I, 
           C_BPARTNER BP, 
           C_BPARTNER_LOCATION BPL, 
           C_LOCATION L,
           C_REGION   R
      WHERE I.C_BPARTNER_ID = BP.C_BPARTNER_ID
      AND I.C_BPARTNER_LOCATION_ID = BPL.C_BPARTNER_LOCATION_ID
      AND BPL.C_LOCATION_ID = L.C_LOCATION_ID 
      AND R.C_REGION_ID = L.C_REGION_ID
      AND I.DATEINVOICED >= TO_DATE(?)
      AND I.DATEINVOICED < TO_DATE(?)
      AND EXISTS (SELECT 1
                    FROM C_TAX T LEFT JOIN C_REGION R ON R.C_REGION_ID = T.TO_REGION_ID,
                         C_COUNTRY C1, C_COUNTRY C2,
                         C_INVOICETAX IT
                   WHERE COALESCE(T.C_COUNTRY_ID,0) = C1.C_COUNTRY_ID 
                     AND COALESCE(T.TO_COUNTRY_ID,0)  = C2.C_COUNTRY_ID
                     AND C1.COUNTRYCODE = 'ES'
                     AND C2.COUNTRYCODE = 'ES'
                     AND IT.C_INVOICE_ID = I.C_INVOICE_ID
                     AND T.C_TAX_ID = IT.C_TAX_ID
                     AND UPPER(T.NAME) NOT LIKE '%I%R%P%F%'
                     AND UPPER(T.NAME) NOT LIKE '%RETEN%'
                     AND (R.C_REGION_ID IS NULL
                          OR R.NAME NOT IN ('BALEARES', 'CEUTA', 'MELILLA', 'TENERIFE', 'LAS PALMAS'))
                UNION       
                SELECT 1
                    FROM C_TAX_ZONE T LEFT JOIN C_REGION R ON R.C_REGION_ID = T.TO_REGION_ID,
                         C_COUNTRY C1, C_COUNTRY C2,
                         C_INVOICETAX IT, C_TAX CT
                   WHERE COALESCE(T.FROM_COUNTRY_ID,0) = C1.C_COUNTRY_ID 
                     AND COALESCE(T.TO_COUNTRY_ID,0)  = C2.C_COUNTRY_ID
                     AND C1.COUNTRYCODE = 'ES'
                     AND C2.COUNTRYCODE = 'ES'
                     AND IT.C_INVOICE_ID = I.C_INVOICE_ID
                     AND T.C_TAX_ID = IT.C_TAX_ID
                     AND CT.C_TAX_ID = T.C_TAX_ID
                     AND UPPER(CT.NAME) NOT LIKE '%I%R%P%F%'
                     AND UPPER(CT.NAME) NOT LIKE '%RETEN%'
                     AND (R.C_REGION_ID IS NULL
                          OR R.NAME NOT IN ('BALEARES', 'CEUTA', 'MELILLA', 'TENERIFE', 'LAS PALMAS')))
      AND I.AD_ORG_ID IN ('1')      
      GROUP BY BP.NAME, BP.NAME2, BP.TAXID, I.ISSOTRX, SUBSTR(L.POSTAL,1,2)
      HAVING ROUND(SUM(I.TOTALLINES+(SELECT SUM(IT.TAXAMT)
                                                     FROM C_INVOICETAX IT
                                                    WHERE IT.C_INVOICE_ID = I.C_INVOICE_ID
                                                      AND EXISTS (SELECT 1                                                      
                                                                    FROM C_TAX T LEFT JOIN C_REGION R ON R.C_REGION_ID = T.TO_REGION_ID,
                                                                         C_COUNTRY C1, C_COUNTRY C2
                                                                   WHERE COALESCE(T.C_COUNTRY_ID,0) = C1.C_COUNTRY_ID 
                                                                     AND COALESCE(T.TO_COUNTRY_ID,0)  = C2.C_COUNTRY_ID
                                                                     AND C1.COUNTRYCODE = 'ES'
                                                                     AND C2.COUNTRYCODE = 'ES'
                                                                     AND IT.C_INVOICE_ID = I.C_INVOICE_ID
                                                                     AND T.C_TAX_ID = IT.C_TAX_ID
                                                                     AND UPPER(T.NAME) NOT LIKE '%I%R%P%F%'
                                                                     AND T.RATE > 0
                                                                     AND (R.C_REGION_ID IS NULL
                                                                          OR R.NAME NOT IN ('BALEARES', 'CEUTA', 'MELILLA', 'TENERIFE', 'LAS PALMAS'))
                                                                UNION       
                                                                SELECT 1
                                                                    FROM C_TAX_ZONE T LEFT JOIN C_REGION R ON R.C_REGION_ID = T.TO_REGION_ID,
                                                                         C_COUNTRY C1, C_COUNTRY C2, C_TAX CT
                                                                   WHERE COALESCE(T.FROM_COUNTRY_ID,0) = C1.C_COUNTRY_ID 
                                                                     AND COALESCE(T.TO_COUNTRY_ID,0)  = C2.C_COUNTRY_ID
                                                                     AND C1.COUNTRYCODE = 'ES'
                                                                     AND C2.COUNTRYCODE = 'ES'
                                                                     AND IT.C_INVOICE_ID = I.C_INVOICE_ID
                                                                     AND T.C_TAX_ID = IT.C_TAX_ID
                                                                     AND CT.C_TAX_ID = T.C_TAX_ID
                                                                     AND UPPER(CT.NAME) NOT LIKE '%I%R%P%F%'
                                                                     AND UPPER(CT.NAME) NOT LIKE '%RETEN%'
                                                                     AND (R.C_REGION_ID IS NULL
                                                                          OR R.NAME NOT IN ('BALEARES', 'CEUTA', 'MELILLA', 'TENERIFE', 'LAS PALMAS'))))), 2) >= 3005.06
      
     ]]></Sql>
    <Parameter name="dateFrom"/>
    <Parameter name="dateTo"/>
    <Parameter name="adOrgId" optional="true" type="replace" after="AND I.AD_ORG_ID IN (" text="'1'"/>
  </SqlMethod>
</SqlClass>