src/org/openbravo/erpCommon/ad_reports/ReportTaxInvoice_data.xsql
changeset 1271 762533f2593a
parent 423 ecf368072c48
child 1605 8a0fe0193bef
equal deleted inserted replaced
1270:c9b59ec0d21a 1271:762533f2593a
    23   <SqlMethod name="select" type="preparedStatement" return="multiple">
    23   <SqlMethod name="select" type="preparedStatement" return="multiple">
    24     <SqlMethodComment></SqlMethodComment>
    24     <SqlMethodComment></SqlMethodComment>
    25     <Sql>
    25     <Sql>
    26     <![CDATA[
    26     <![CDATA[
    27       SELECT C_INVOICE.ISSOTRX AS SALES, 'Y' AS NATIONAL, C_INVOICE.DOCUMENTNO AS DOCUMENTNO, C_INVOICE.DATEINVOICED, C_BPARTNER.NAME AS BPARTNER, LTRIM(RTRIM(C_TAX.NAME, ' '), ' ') AS TAXNAME, 
    27       SELECT C_INVOICE.ISSOTRX AS SALES, 'Y' AS NATIONAL, C_INVOICE.DOCUMENTNO AS DOCUMENTNO, C_INVOICE.DATEINVOICED, C_BPARTNER.NAME AS BPARTNER, LTRIM(RTRIM(C_TAX.NAME, ' '), ' ') AS TAXNAME, 
    28       SUM(C_INVOICETAX.TAXBASEAMT) AS TAXBASEAMT, SUM(C_INVOICETAX.TAXAMT) AS TAXAMT, '' AS ADTREEID
    28       SUM(CASE WHEN DOCBASETYPE IN ('ARC','APC') THEN -1 * C_INVOICETAX.TAXBASEAMT ELSE C_INVOICETAX.TAXBASEAMT END) AS TAXBASEAMT, 
    29       FROM C_TAX, C_INVOICETAX, C_INVOICE, C_BPARTNER_LOCATION, C_LOCATION, C_BPARTNER
    29       SUM(CASE WHEN DOCBASETYPE IN ('ARC','APC') THEN -1 * C_INVOICETAX.TAXAMT ELSE C_INVOICETAX.TAXAMT END) AS TAXAMT, '' AS ADTREEID
       
    30       FROM C_TAX, C_INVOICETAX, C_INVOICE, C_BPARTNER_LOCATION, C_LOCATION, C_BPARTNER, C_DOCTYPE
    30       WHERE C_TAX.C_TAX_ID=C_INVOICETAX.C_TAX_ID
    31       WHERE C_TAX.C_TAX_ID=C_INVOICETAX.C_TAX_ID
    31       AND C_INVOICETAX.C_INVOICE_ID=C_INVOICE.C_INVOICE_ID
    32       AND C_INVOICETAX.C_INVOICE_ID=C_INVOICE.C_INVOICE_ID
    32       AND C_INVOICE.C_BPARTNER_LOCATION_ID = C_BPARTNER_LOCATION.C_BPARTNER_LOCATION_ID
    33       AND C_INVOICE.C_BPARTNER_LOCATION_ID = C_BPARTNER_LOCATION.C_BPARTNER_LOCATION_ID
    33       AND C_BPARTNER_LOCATION.C_LOCATION_ID = C_LOCATION.C_LOCATION_ID
    34       AND C_BPARTNER_LOCATION.C_LOCATION_ID = C_LOCATION.C_LOCATION_ID
    34       AND C_INVOICE.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
    35       AND C_INVOICE.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
       
    36       AND C_INVOICE.C_DOCTYPE_ID = C_DOCTYPE.C_DOCTYPE_ID
    35       AND C_LOCATION.C_COUNTRY_ID = TO_NUMBER(?)
    37       AND C_LOCATION.C_COUNTRY_ID = TO_NUMBER(?)
    36       AND C_INVOICE.PROCESSED = 'Y'
    38       AND C_INVOICE.PROCESSED = 'Y'
    37       AND C_INVOICE.ISACTIVE = 'Y'
    39       AND C_INVOICE.ISACTIVE = 'Y'
    38       AND C_INVOICE.AD_CLIENT_ID IN ('1')
    40       AND C_INVOICE.AD_CLIENT_ID IN ('1')
    39       AND 1=1
    41       AND 1=1
    70 
    72 
    71   <SqlMethod name="selectForeign" type="preparedStatement" return="multiple">
    73   <SqlMethod name="selectForeign" type="preparedStatement" return="multiple">
    72     <SqlMethodComment></SqlMethodComment>
    74     <SqlMethodComment></SqlMethodComment>
    73     <Sql>
    75     <Sql>
    74     <![CDATA[
    76     <![CDATA[
    75       SELECT C_INVOICE.ISSOTRX AS SALES, 'N' AS NATIONAL, C_INVOICE.DOCUMENTNO AS DOCUMENTNO, C_INVOICE.DATEINVOICED, C_BPARTNER.NAME AS BPARTNER, LTRIM(RTRIM(C_TAX.NAME, ' '), ' ') AS TAXNAME, 
    77       SELECT C_INVOICE.ISSOTRX AS SALES, 'N' AS NATIONAL, C_INVOICE.DOCUMENTNO AS DOCUMENTNO, C_INVOICE.DATEINVOICED, 
    76       SUM(C_INVOICETAX.TAXBASEAMT) AS TAXBASEAMT, SUM(C_INVOICETAX.TAXAMT) AS TAXAMT, '' AS ADTREEID
    78       C_BPARTNER.NAME AS BPARTNER, LTRIM(RTRIM(C_TAX.NAME, ' '), ' ') AS TAXNAME, 
    77       FROM C_TAX, C_INVOICETAX, C_INVOICE, C_BPARTNER_LOCATION, C_LOCATION, C_BPARTNER
    79       SUM(CASE WHEN DOCBASETYPE IN ('ARC','APC') THEN -1 * C_INVOICETAX.TAXBASEAMT ELSE C_INVOICETAX.TAXBASEAMT END) AS TAXBASEAMT, 
       
    80       SUM(CASE WHEN DOCBASETYPE IN ('ARC','APC') THEN -1 * C_INVOICETAX.TAXAMT ELSE C_INVOICETAX.TAXAMT END) AS TAXAMT, '' AS ADTREEID
       
    81       FROM C_TAX, C_INVOICETAX, C_INVOICE, C_BPARTNER_LOCATION, C_LOCATION, C_BPARTNER, C_DOCTYPE
    78       WHERE C_TAX.C_TAX_ID=C_INVOICETAX.C_TAX_ID
    82       WHERE C_TAX.C_TAX_ID=C_INVOICETAX.C_TAX_ID
    79       AND C_INVOICETAX.C_INVOICE_ID=C_INVOICE.C_INVOICE_ID
    83       AND C_INVOICETAX.C_INVOICE_ID=C_INVOICE.C_INVOICE_ID
    80       AND C_INVOICE.C_BPARTNER_LOCATION_ID = C_BPARTNER_LOCATION.C_BPARTNER_LOCATION_ID
    84       AND C_INVOICE.C_BPARTNER_LOCATION_ID = C_BPARTNER_LOCATION.C_BPARTNER_LOCATION_ID
    81       AND C_BPARTNER_LOCATION.C_LOCATION_ID = C_LOCATION.C_LOCATION_ID
    85       AND C_BPARTNER_LOCATION.C_LOCATION_ID = C_LOCATION.C_LOCATION_ID
    82       AND C_INVOICE.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
    86       AND C_INVOICE.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
       
    87       AND C_INVOICE.C_DOCTYPE_ID = C_DOCTYPE.C_DOCTYPE_ID
    83       AND C_LOCATION.C_COUNTRY_ID <> TO_NUMBER(?)
    88       AND C_LOCATION.C_COUNTRY_ID <> TO_NUMBER(?)
    84       AND C_INVOICE.PROCESSED = 'Y'
    89       AND C_INVOICE.PROCESSED = 'Y'
    85       AND C_INVOICE.ISACTIVE = 'Y'
    90       AND C_INVOICE.ISACTIVE = 'Y'
    86       AND C_INVOICE.AD_CLIENT_ID IN ('1')
    91       AND C_INVOICE.AD_CLIENT_ID IN ('1')
    87       AND 1=1
    92       AND 1=1