src/org/openbravo/erpCommon/ad_reports/ReportCashflowForecast_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 1120 347aec194219
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="ReportCashflowForecastData" package="org.openbravo.erpCommon.ad_reports">
carlos@0
    26
  <SqlClassComment>Clase ReportToInvoiceConsignmentData</SqlClassComment>
carlos@0
    27
  <SqlMethod name="select" type="preparedStatement" return="multiple">
carlos@0
    28
    <Sql>
carlos@0
    29
    <![CDATA[
carlos@0
    30
      SELECT BANKACCOUNT, 
carlos@0
    31
             INITIALBALANCE,
carlos@0
    32
             TRUNC(NOW()) AS CURRENTDATE, 
carlos@0
    33
             INCOME, 
carlos@0
    34
             PAYMENT, 
carlos@0
    35
             INCOME-PAYMENT AS INCPAY, 
carlos@0
    36
             INITIALBALANCE+INCOME-PAYMENT AS 
carlos@0
    37
             FINALSUMMARY, C_BANKACCOUNT_ID,             
carlos@0
    38
             '' AS C_DEBT_PAYMENT_ID, 
carlos@0
    39
             '' AS DATEPLANNED, 
carlos@0
    40
             '' AS BPARTNER,
carlos@0
    41
             '' AS DESCRIPTION, 
carlos@0
    42
             '' AS INVOICENO, 
carlos@0
    43
             '' AS DATEINVOICED,
carlos@0
    44
             '' AS AMOUNT,
carlos@0
    45
             '' AS ISRECEIPT,
carlos@0
    46
             '' AS URL,
carlos@0
    47
             '' AS CLASSTYPE,
carlos@0
    48
             '' AS ISRECEIPTMESSAGE FROM (     
carlos@0
    49
            SELECT (B.CODEBANK || '/' || B.CODEBRANCH || B.DIGITCONTROL || BA.CODEACCOUNT || '.' || BA.DIGITCONTROL) AS BANKACCOUNT,
carlos@0
    50
             SUM(c_currency_convert(STMTAMT, BL.C_CURRENCY_ID, BA.C_CURRENCY_ID, BL.VALUTADATE, NULL, ba.ad_client_id, ba.ad_org_id)) AS INITIALBALANCE,
carlos@0
    51
             COALESCE((SELECT SUM(c_currency_convert(DP.AMOUNT, dp.C_CURRENCY_ID, ba.c_Currency_ID, dp.dateplanned,null, ba.ad_client_id, ba.ad_org_id))
carlos@0
    52
                FROM C_DEBT_PAYMENT_V DP
carlos@0
    53
               WHERE C_SETTLEMENT_CANCEL_ID IS NULL
carlos@0
    54
                 AND DP.C_BANKACCOUNT_ID IS NOT NULL         
carlos@0
    55
                 AND C_DEBT_PAYMENT_STATUS(C_SETTLEMENT_CANCEL_ID, CANCEL_PROCESSED, GENERATE_PROCESSED, DP.ISPAID, ISVALID, C_CASHLINE_ID, C_BANKSTATEMENTLINE_ID) = 'P'
carlos@0
    56
                 AND ISRECEIPT = 'Y'
carlos@0
    57
                 AND DP.C_BANKACCOUNT_ID = BA.C_BANKACCOUNT_ID
carlos@0
    58
                 AND 1=1
carlos@0
    59
                 AND NOT EXISTS (SELECT 1 
carlos@0
    60
                           FROM C_REMITTANCELINE  RL,
carlos@0
    61
                                C_REMITTANCE      R,
carlos@0
    62
                                C_REMITTANCE_TYPE RT
carlos@0
    63
                          WHERE R.C_REMITTANCE_ID = RL.C_REMITTANCE_ID
carlos@0
    64
                            AND R.C_REMITTANCE_TYPE_ID = RT.C_REMITTANCE_TYPE_ID
carlos@0
    65
                            AND CONSOLIDATE = 'Y' 
carlos@0
    66
                            AND (C_DEBT_PAYMENT_ID  = DP.C_DEBT_PAYMENT_ID
carlos@0
    67
                             OR C_DEBT_PAYMENT_CANCELLED = DP.C_DEBT_PAYMENT_ID)
carlos@0
    68
                            AND NOT EXISTS (SELECT 1   /*IF CANCEL DP IS IN BANK (IT IS RETURNED) IT HAS TO APPEAR*/
carlos@0
    69
                                              FROM C_BANKSTATEMENTLINE BSL,
carlos@0
    70
                                                   C_BANKSTATEMENT B
carlos@0
    71
                                            WHERE BSL.C_DEBT_PAYMENT_ID = RL.C_DEBT_PAYMENT_CANCELLED
carlos@0
    72
                                              AND BSL.C_BANKSTATEMENT_ID = B.C_BANKSTATEMENT_ID
carlos@0
    73
                                              AND B.PROCESSED='Y'))
carlos@0
    74
              ),0) AS INCOME,
carlos@0
    75
            -COALESCE((SELECT SUM(c_currency_convert(DP.AMOUNT, dp.C_CURRENCY_ID, ba.c_Currency_ID, dp.dateplanned,null, ba.ad_client_id, ba.ad_org_id))
carlos@0
    76
                FROM C_DEBT_PAYMENT_V DP
carlos@0
    77
               WHERE C_SETTLEMENT_CANCEL_ID IS NULL
carlos@0
    78
                 AND DP.C_BANKACCOUNT_ID IS NOT NULL         
carlos@0
    79
                 AND C_DEBT_PAYMENT_STATUS(C_SETTLEMENT_CANCEL_ID, CANCEL_PROCESSED, GENERATE_PROCESSED, DP.ISPAID, ISVALID, C_CASHLINE_ID, C_BANKSTATEMENTLINE_ID) = 'P'
carlos@0
    80
                 AND ISRECEIPT='N'
carlos@0
    81
                 AND DP.C_BANKACCOUNT_ID = BA.C_BANKACCOUNT_ID
carlos@0
    82
                 AND 2=2
carlos@0
    83
                 AND NOT EXISTS (SELECT 1 
carlos@0
    84
                           FROM C_REMITTANCELINE  RL,
carlos@0
    85
                                C_REMITTANCE      R,
carlos@0
    86
                                C_REMITTANCE_TYPE RT
carlos@0
    87
                          WHERE R.C_REMITTANCE_ID = RL.C_REMITTANCE_ID
carlos@0
    88
                            AND R.C_REMITTANCE_TYPE_ID = RT.C_REMITTANCE_TYPE_ID
carlos@0
    89
                            AND CONSOLIDATE = 'Y' 
carlos@0
    90
                            AND (C_DEBT_PAYMENT_ID  = DP.C_DEBT_PAYMENT_ID
carlos@0
    91
                             OR C_DEBT_PAYMENT_CANCELLED = DP.C_DEBT_PAYMENT_ID)
carlos@0
    92
                            AND NOT EXISTS (SELECT 1   /*IF CANCEL DP IS IN BANK (IT IS RETURNED) IT HAS TO APPEAR*/
carlos@0
    93
                                              FROM C_BANKSTATEMENTLINE BSL,
carlos@0
    94
                                                   C_BANKSTATEMENT B
carlos@0
    95
                                            WHERE BSL.C_DEBT_PAYMENT_ID = RL.C_DEBT_PAYMENT_CANCELLED
carlos@0
    96
                                              AND BSL.C_BANKSTATEMENT_ID = B.C_BANKSTATEMENT_ID
carlos@0
    97
                                              AND B.PROCESSED='Y'))
carlos@0
    98
               ),0) AS PAYMENT,
carlos@0
    99
             BA.C_BANKACCOUNT_ID
carlos@0
   100
      FROM C_BANKSTATEMENTLINE BL,
carlos@0
   101
           C_BANKSTATEMENT     BS,
carlos@0
   102
           C_BANK              B,
carlos@0
   103
           C_BANKACCOUNT       BA
carlos@0
   104
      WHERE BS.C_BANKSTATEMENT_ID = BL.C_BANKSTATEMENT_ID
carlos@0
   105
        AND BA.C_BANKACCOUNT_ID = BS.C_BANKACCOUNT_ID
carlos@0
   106
        AND BA.C_BANK_ID = B.C_BANK_ID 
carlos@0
   107
        AND BS.PROCESSED='Y'  
carlos@0
   108
        AND BS.STATEMENTDATE <= NOW()
carlos@0
   109
        AND 3=3
carlos@0
   110
        AND B.AD_CLIENT_ID IN ('1')
carlos@0
   111
        AND B.AD_ORG_ID IN ('1') 
carlos@0
   112
       GROUP BY (B.CODEBANK || '/' || B.CODEBRANCH || B.DIGITCONTROL || BA.CODEACCOUNT || '.' || BA.DIGITCONTROL), BA.C_BANKACCOUNT_ID, ba.c_Currency_ID, ba.ad_client_id, ba.ad_org_id 
carlos@0
   113
       ) AAA  
carlos@0
   114
       ORDER BY 1
carlos@0
   115
     ]]></Sql>
carlos@0
   116
    <Parameter name="dateMax" optional="true" after="AND 1=1"><![CDATA[ AND DP.DATEPLANNED <= TO_DATE(?) ]]></Parameter>
carlos@0
   117
    <Parameter name="dateMax" optional="true" after="AND 2=2"><![CDATA[ AND DP.DATEPLANNED <= TO_DATE(?) ]]></Parameter>
carlos@423
   118
    <Parameter name="bankaccount" optional="true" after="AND 3=3"><![CDATA[ AND BA.C_BANKACCOUNT_ID = TO_NUMBER(?) ]]></Parameter>
carlos@0
   119
    <Parameter name="adUserClient" type="replace" optional="true" after="AND B.AD_CLIENT_ID IN (" text="'1'"/>
carlos@0
   120
    <Parameter name="adUserOrg" type="replace" optional="true" after="AND B.AD_ORG_ID IN (" text="'1'"/> 
carlos@0
   121
  </SqlMethod>
carlos@0
   122
carlos@0
   123
 <SqlMethod name="selectLines" type="preparedStatement" return="multiple">
carlos@0
   124
    <Sql>
carlos@0
   125
      <![CDATA[
carlos@0
   126
      SELECT DATEPLANNED, 
carlos@0
   127
              ISRECEIPT,
carlos@0
   128
              COALESCE(mt.msgtext,m.msgtext) as IsReceiptMessage, 
carlos@0
   129
              DP.C_DEBT_PAYMENT_ID, 
carlos@0
   130
              BP.NAME AS BPARTNER,
carlos@0
   131
              DP.DESCRIPTION, 
carlos@0
   132
              I.DOCUMENTNO AS INVOICENO, 
carlos@0
   133
              TO_CHAR(I.DATEINVOICED,TO_CHAR(?)) AS DATEINVOICED,
carlos@0
   134
              c_currency_convert(DP.AMOUNT, dp.C_CURRENCY_ID, ba.c_Currency_ID, dp.dateplanned,null, ba.ad_client_id, ba.ad_org_id) AS AMOUNT,
carlos@0
   135
              (CASE WHEN DP.C_Invoice_ID IS NOT NULL
carlos@0
   136
                THEN (CASE ISRECEIPT WHEN 'Y' THEN 'SalesInvoice/Payment_Relation.html' WHEN 'N' THEN 'PurchaseInvoice/Payment_Relation.html' END) 
carlos@0
   137
               ELSE
carlos@0
   138
                CASE WHEN DP.C_Order_ID IS NOT NULL
carlos@0
   139
                  THEN (CASE ISRECEIPT WHEN 'Y' THEN 'SalesOrder/Payment_Relation.html' WHEN 'N' THEN 'PurchaseOrder/Payment_Relation.html' END)
carlos@0
   140
                ELSE
carlos@0
   141
                  CASE WHEN DP.C_Settlement_Generate_ID IS NOT NULL
carlos@0
   142
                    THEN 'Settlement/CreatedPayments_Relation.html'
carlos@0
   143
                  END                  
carlos@0
   144
                END
carlos@0
   145
               END) AS URL,
carlos@0
   146
              (SELECT SUM(c_currency_convert(STMTAMT, BL.C_CURRENCY_ID, BA.C_CURRENCY_ID, BL.VALUTADATE, NULL, ba.ad_client_id, ba.ad_org_id)) AS BALANCE             
carlos@0
   147
                 FROM C_BANKSTATEMENTLINE BL,
carlos@0
   148
                      C_BANKSTATEMENT     BS
carlos@0
   149
                 WHERE BS.C_BANKSTATEMENT_ID = BL.C_BANKSTATEMENT_ID
carlos@0
   150
                   AND BS.PROCESSED='Y'  
carlos@0
   151
                   AND BS.STATEMENTDATE <= NOW()
carlos@0
   152
                   AND BS.C_BANKACCOUNT_ID = DP.C_BANKACCOUNT_ID ) as initialbalance,
carlos@0
   153
              (case when DP.AMOUNT>0 then 'BordesVerdeClaro' else 'BordesRojoClaro' end) AS CLASSTYPE
carlos@0
   154
        FROM C_DEBT_PAYMENT_V DP LEFT JOIN C_INVOICE I ON DP.C_INVOICE_ID = I.C_INVOICE_ID,
carlos@0
   155
             AD_MESSAGE M        LEFT JOIN AD_MESSAGE_TRL MT ON m.ad_message_id = mt.ad_message_id         
carlos@0
   156
                                                            and mt.ad_language = ?,
carlos@0
   157
             C_BPARTNER BP,
carlos@0
   158
             C_BANKACCOUNT       BA             
carlos@0
   159
       WHERE BA.C_BANKACCOUNT_ID = DP.C_BANKACCOUNT_ID
carlos@0
   160
         AND C_SETTLEMENT_CANCEL_ID IS NULL
carlos@0
   161
         AND DP.C_BANKACCOUNT_ID IS NOT NULL         
carlos@0
   162
         AND C_DEBT_PAYMENT_STATUS(C_SETTLEMENT_CANCEL_ID, CANCEL_PROCESSED, GENERATE_PROCESSED, DP.ISPAID, ISVALID, C_CASHLINE_ID, C_BANKSTATEMENTLINE_ID) = 'P'
carlos@0
   163
         AND BP.C_BPARTNER_ID = DP.C_BPARTNER_ID
carlos@423
   164
         AND DP.C_BANKACCOUNT_ID = TO_NUMBER(?)
carlos@0
   165
         AND M.VALUE = (CASE DP.ISRECEIPT WHEN 'Y' THEN 'Income' WHEN 'N' THEN 'Payments' END)
carlos@0
   166
         AND NOT EXISTS (SELECT 1 
carlos@0
   167
                           FROM C_REMITTANCELINE  RL,
carlos@0
   168
                                C_REMITTANCE      R,
carlos@0
   169
                                C_REMITTANCE_TYPE RT
carlos@0
   170
                          WHERE R.C_REMITTANCE_ID = RL.C_REMITTANCE_ID
carlos@0
   171
                            AND R.C_REMITTANCE_TYPE_ID = RT.C_REMITTANCE_TYPE_ID
carlos@0
   172
                            AND CONSOLIDATE = 'Y' 
carlos@0
   173
                            AND (C_DEBT_PAYMENT_ID  = DP.C_DEBT_PAYMENT_ID
carlos@0
   174
                             OR C_DEBT_PAYMENT_CANCELLED = DP.C_DEBT_PAYMENT_ID)
carlos@0
   175
                            AND NOT EXISTS (SELECT 1   /*IF CANCEL DP IS IN BANK (IT IS RETURNED) IT HAS TO APPEAR*/
carlos@0
   176
                                              FROM C_BANKSTATEMENTLINE BSL,
carlos@0
   177
                                                   C_BANKSTATEMENT B
carlos@0
   178
                                            WHERE BSL.C_DEBT_PAYMENT_ID = RL.C_DEBT_PAYMENT_CANCELLED
carlos@0
   179
                                              AND BSL.C_BANKSTATEMENT_ID = B.C_BANKSTATEMENT_ID
carlos@0
   180
                                              AND B.PROCESSED='Y'))
carlos@0
   181
         AND 1=1
carlos@0
   182
       ORDER BY 1,2 DESC
carlos@0
   183
    ]]></Sql>
carlos@0
   184
    <Parameter name="dateFormat"/>
carlos@0
   185
    <Parameter name="language"/>
carlos@0
   186
    <Parameter name="bankaccount"/>
carlos@0
   187
    <Parameter name="dateMax" optional="true" after="AND 1=1"><![CDATA[ AND DP.DATEPLANNED <= TO_DATE(?) ]]></Parameter>
carlos@0
   188
    <Parameter name="order" optional="true" type="replace"  after="ORDER BY " text="1,2,3 DESC"/>
carlos@0
   189
  </SqlMethod>
carlos@0
   190
carlos@0
   191
  <SqlMethod name="getDate" type="preparedStatement" return="String">
carlos@0
   192
    <Sql>
carlos@0
   193
      SELECT TO_CHAR(NOW(),TO_CHAR(?)) AS CURRENTDATE FROM DUAL
carlos@0
   194
    </Sql>
carlos@0
   195
    <Parameter name="dateFormat"/>
carlos@0
   196
  </SqlMethod>
juanpablo@152
   197
</SqlClass>