src/org/openbravo/erpCommon/ad_actionButton/CreateFrom_CRemittance_data.xsql
author Juan Pablo Aroztegi <juanpablo.aroztegi@openbravo.com>
Wed, 03 Sep 2008 17:55:37 +0000
changeset 1605 8a0fe0193bef
parent 423 ecf368072c48
child 1792 c6e03a54d35c
permissions -rw-r--r--
Merge r2.5x intro trunk
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="CreateFromCRemittanceData" package="org.openbravo.erpCommon.ad_actionButton">
carlos@0
    26
  <SqlMethod name="select" type="preparedStatement" return="multiple">
carlos@0
    27
    <SqlMethodComment></SqlMethodComment>
carlos@0
    28
    <Sql>
carlos@0
    29
      <![CDATA[
carlos@0
    30
        SELECT DP.C_DEBT_PAYMENT_ID, L.NAME AS PAYMENTRULE, B.NAME AS BPARTNER, 
carlos@0
    31
        Ad_Column_Identifier(to_char('C_Invoice'), to_char(i.C_Invoice_ID), to_char(?)) AS INVOICE, DP.DATEPLANNED, 
carlos@0
    32
        DP.AMOUNT, C.ISO_CODE,  O.Name as Org, 
juanpablo@1605
    33
        (CASE ? WHEN 'Y' THEN DP.C_DEBT_PAYMENT_ID ELSE '-1' END) AS MARCAR_ID, 
juanpablo@1605
    34
        (CASE ? WHEN 'Y' THEN '-1' ELSE DP.C_DEBT_PAYMENT_ID END) AS DISABLE_INPUTS,
carlos@0
    35
        L1.name as status, '' as line
carlos@0
    36
        FROM C_DEBT_PAYMENT DP left join AD_REF_LIST_V L on DP.PAYMENTRULE = L.VALUE
carlos@0
    37
                                                        and L.AD_REFERENCE_ID = '195'
carlos@0
    38
                                                        and L.AD_LANGUAGE = ?
carlos@0
    39
                               left join C_BPARTNER B    on DP.C_BPARTNER_ID = B.C_BPARTNER_ID 
carlos@0
    40
                               left join C_INVOICE I     on DP.C_INVOICE_ID = I.C_INVOICE_ID
carlos@0
    41
                               left join AD_REF_LIST_V L1 on DP.STATUS = L1.value
carlos@0
    42
                                                         and l1.ad_reference_id = '800070'
carlos@0
    43
                                                         and l1.Ad_Language = ?,
carlos@0
    44
           C_CURRENCY C, AD_Org O 
carlos@0
    45
        WHERE DP.ISACTIVE = 'Y' 
carlos@0
    46
        AND DP.AD_Org_ID = O.AD_Org_ID 
carlos@0
    47
        AND DP.AD_CLIENT_ID IN ('1') 
carlos@0
    48
        AND DP.AD_ORG_ID IN ('1') 
carlos@0
    49
        AND  DP.AD_ORG_ID IN ('2') 
carlos@0
    50
        AND DP.C_CURRENCY_ID = C.C_CURRENCY_ID 
carlos@0
    51
        AND C_DEBT_PAYMENT_STATUS(DP.C_Settlement_Cancel_ID, DP.Cancel_Processed, DP.Generate_Processed, 
carlos@0
    52
        DP.IsPaid, DP.IsValid, DP.C_CashLine_ID, DP.C_BankStatementLine_ID)= 'P' 
carlos@0
    53
        AND NOT EXISTS (SELECT 1 FROM C_BankStatementLine l WHERE DP.C_DEBT_Payment_ID=l.C_DEBT_PAYMENT_ID) 
carlos@0
    54
        AND NOT EXISTS (SELECT 1 FROM C_CashLine l WHERE DP.C_DEBT_Payment_ID=l.C_DEBT_PAYMENT_ID) 
carlos@0
    55
        AND NOT EXISTS (SELECT 1 FROM C_RemittanceLine rl, C_Remittance r, C_REMITTANCE_TYPE rt 
carlos@0
    56
																 WHERE r.C_REMITTANCE_ID = rl.C_REMITTANCE_ID
carlos@0
    57
																 AND rt.C_REMITTANCE_TYPE_ID = r.C_REMITTANCE_TYPE_ID
carlos@0
    58
																   AND r.PROCESSED = 'Y'
carlos@0
    59
																   AND rt.STATUS_RETURNED <> dp.STATUS
carlos@0
    60
																	 AND rl.C_DEBT_PAYMENT_ID = dp.C_DEBT_PAYMENT_ID)
carlos@0
    61
        ORDER BY DP.DATEPLANNED DESC 
carlos@0
    62
carlos@0
    63
carlos@0
    64
      ]]>
carlos@0
    65
    </Sql>
carlos@0
    66
    <Field name="rownum" value="count"/>
carlos@0
    67
    <Parameter name="language"/>
carlos@0
    68
    <Parameter name="marcartodos"/>
carlos@0
    69
    <Parameter name="marcartodos"/>
carlos@0
    70
    <Parameter name="language"/>
carlos@0
    71
    <Parameter name="language"/>
carlos@0
    72
    <Parameter name="userClient" optional="true" type="replace" after="AND DP.AD_CLIENT_ID IN (" text="'1'"/>
carlos@0
    73
    <Parameter name="userOrg" optional="true" type="replace" after="AND DP.AD_ORG_ID IN (" text="'1'"/>    
carlos@0
    74
    <Parameter name="adOrgId" optional="true" type="replace" after="AND  DP.AD_ORG_ID IN (" text="'2'"/>    
juanpablo@1605
    75
    <Parameter name="cBPartnerId" optional="true" after="AND DP.C_CURRENCY_ID = C.C_CURRENCY_ID " text=" AND DP.C_BPARTNER_ID = ? "/>
carlos@0
    76
    <Parameter name="paymentrule" optional="true" after="AND DP.C_CURRENCY_ID = C.C_CURRENCY_ID " text=" AND DP.PAYMENTRULE = ? "/>
carlos@0
    77
    <Parameter name="planneddateFrom" optional="true" after="AND DP.C_CURRENCY_ID = C.C_CURRENCY_ID "><![CDATA[ AND DP.DATEPLANNED >= TO_DATE(?) ]]></Parameter>
carlos@0
    78
    <Parameter name="planneddateTo" optional="true" after="AND DP.C_CURRENCY_ID = C.C_CURRENCY_ID "><![CDATA[ AND DP.DATEPLANNED <= TO_DATE(?) ]]></Parameter>
carlos@0
    79
    <Parameter name="isreceipt" optional="true" after="AND DP.C_CURRENCY_ID = C.C_CURRENCY_ID " text=" AND DP.ISRECEIPT = ? "/>
carlos@0
    80
    <Parameter name="amountFrom" optional="true" after="AND DP.C_CURRENCY_ID = C.C_CURRENCY_ID "><![CDATA[ AND DP.AMOUNT >= ? ]]></Parameter>
carlos@0
    81
    <Parameter name="amountTo" optional="true" after="AND DP.C_CURRENCY_ID = C.C_CURRENCY_ID "><![CDATA[ AND DP.AMOUNT <= ? ]]></Parameter>
carlos@0
    82
    <Parameter name="amount" optional="true" after="AND DP.C_CURRENCY_ID = C.C_CURRENCY_ID "><![CDATA[ AND ABS(DP.AMOUNT) < ? ]]></Parameter>
carlos@0
    83
    <Parameter name="statusFrom" optional="true" after="AND DP.C_CURRENCY_ID = C.C_CURRENCY_ID " text=" AND DP.STATUS = ? "/>
carlos@0
    84
  </SqlMethod>
carlos@0
    85
  <SqlMethod name="set" type="constant" return="multiple">
carlos@0
    86
      <SqlMethodComment></SqlMethodComment>
carlos@0
    87
      <Sql></Sql>
carlos@0
    88
  </SqlMethod>
carlos@0
    89
  <SqlMethod name="bpartner" type="preparedStatement" return="String" default="">
carlos@0
    90
    <SqlMethodComment></SqlMethodComment>
carlos@0
    91
    <Sql>
juanpablo@1605
    92
      SELECT NAME FROM C_BPARTNER WHERE C_BPARTNER_ID=?
carlos@0
    93
    </Sql>
carlos@0
    94
    <Parameter name="cBpartnerId"/>
carlos@0
    95
  </SqlMethod>
carlos@0
    96
  <SqlMethod name="NotIsCancelled" type="preparedStatement" connection="true" return="boolean">
carlos@0
    97
    <SqlMethodComment></SqlMethodComment>
carlos@0
    98
    <Sql><![CDATA[
carlos@0
    99
      SELECT COUNT(*) 
carlos@0
   100
      FROM C_DEBT_PAYMENT DP 
juanpablo@1605
   101
      WHERE DP.C_DEBT_PAYMENT_ID = ? 
carlos@0
   102
      AND C_DEBT_PAYMENT_STATUS(DP.C_Settlement_Cancel_ID, DP.Cancel_Processed, DP.Generate_Processed, 
carlos@0
   103
      DP.IsPaid, DP.IsValid, DP.C_CashLine_ID, DP.C_BankStatementLine_ID)='P' 
carlos@0
   104
      AND NOT EXISTS (SELECT * FROM C_BankStatementLine l WHERE DP.C_DEBT_Payment_ID=l.C_DEBT_PAYMENT_ID) 
carlos@0
   105
      AND NOT EXISTS (SELECT * FROM C_CashLine l WHERE DP.C_DEBT_Payment_ID=l.C_DEBT_PAYMENT_ID) 
carlos@0
   106
    ]]></Sql>
carlos@0
   107
    <Parameter name="cDebtPaymentId"/>
carlos@0
   108
  </SqlMethod>
carlos@0
   109
  <SqlMethod name="insert" connection="true" type="preparedStatement" return="rowCount">
carlos@0
   110
    <SqlMethodComment></SqlMethodComment>
carlos@0
   111
    <Sql>
carlos@0
   112
      <![CDATA[
carlos@0
   113
        insert into C_RemittanceLine(C_REMITTANCELINE_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, 
carlos@0
   114
                                     CREATED, CREATEDBY, UPDATED, UPDATEDBY, C_REMITTANCE_ID, LINE, C_DEBT_PAYMENT_ID)
juanpablo@1605
   115
         SELECT ?,?, AD_ORG_ID ,'Y', 
juanpablo@1605
   116
                now(), ?, now(), ?, ?,?, C_DEBT_PAYMENT_ID
carlos@0
   117
                FROM C_DEBT_PAYMENT
juanpablo@1605
   118
                WHERE C_DEBT_PAYMENT_ID = ?
carlos@0
   119
        ]]>
carlos@0
   120
    </Sql>
carlos@0
   121
    <Parameter name="LineID"/>
carlos@0
   122
    <Parameter name="client"/>
carlos@0
   123
    <Parameter name="adUserId"/>
carlos@0
   124
    <Parameter name="adUserId"/>
carlos@0
   125
    <Parameter name="key"/>
carlos@0
   126
    <Parameter name="lineNo"/>
carlos@0
   127
    <Parameter name="dpId"/>
carlos@0
   128
  </SqlMethod>
carlos@0
   129
carlos@0
   130
  
carlos@0
   131
carlos@0
   132
  <SqlMethod name="selectLineNo" type="preparedStatement" return="string" default="0">
carlos@0
   133
    <Sql>
carlos@0
   134
      <![CDATA[
carlos@0
   135
          SELECT COALESCE(MAX(LINE),0) AS LINE
carlos@0
   136
            FROM C_REMITTANCELINE 
juanpablo@1605
   137
           WHERE C_REMITTANCE_ID = ?
carlos@0
   138
        ]]>
carlos@0
   139
    </Sql>
carlos@0
   140
    <Parameter name="remittaceID"/>
carlos@0
   141
   </SqlMethod>
carlos@0
   142
   
carlos@0
   143
 </SqlClass>
carlos@0
   144