src/org/openbravo/erpCommon/info/DebtPayment_data.xsql
author Iván Perdomo <ivan.perdomo@openbravo.com>
Mon, 21 Apr 2008 19:06:47 +0000
changeset 712 aa0c40278c5a
parent 423 ecf368072c48
child 1605 8a0fe0193bef
permissions -rw-r--r--
Merged changes from ajaxgrids branch.
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="DebtPaymentData" package="org.openbravo.erpCommon.info">
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[
ivan@712
    31
      	SELECT * FROM ( SELECT '0' AS RN1, A.* FROM (
ivan@712
    32
	        SELECT DP.C_DEBT_PAYMENT_ID, B.NAME AS BPARTNER, I.DOCUMENTNO AS INVOICE, DP.DATEPLANNED,
ivan@712
    33
	        O.DOCUMENTNO AS ORDERNO, DP.AMOUNT, DP.WRITEOFFAMT, C.ISO_CODE AS CURRENCY,
ivan@712
    34
	        S1.DOCUMENTNO AS DEBTCANCEL, S2.DOCUMENTNO AS DEBTGENERATE, COALESCE(RLT.NAME, RL.NAME) AS PAYMENTRULE,
ivan@712
    35
	        DP.C_DEBT_PAYMENT_ID || '#' || Ad_Column_Identifier(TO_CHAR('C_Debt_Payment'), TO_CHAR(dp.C_DEBT_PAYMENT_ID), TO_CHAR(?)) AS ROWKEY
ivan@712
    36
	        FROM C_DEBT_PAYMENT DP left join C_BPARTNER B on DP.C_BPARTNER_ID = B.C_BPARTNER_ID
ivan@712
    37
	                               left join C_INVOICE I  on DP.C_INVOICE_ID = I.C_INVOICE_ID
ivan@712
    38
	                               left join C_SETTLEMENT S1 on DP.C_SETTLEMENT_CANCEL_ID = S1.C_SETTLEMENT_ID
ivan@712
    39
	                               left join C_ORDER    O on DP.C_ORDER_ID = O.C_ORDER_ID
ivan@712
    40
	                               left join C_SETTLEMENT S2 on DP.C_SETTLEMENT_GENERATE_ID = S2.C_SETTLEMENT_ID,
ivan@712
    41
	             AD_REF_LIST RL    left join AD_REF_LIST_TRL RLT on RL.AD_REF_LIST_ID = RLT.AD_REF_LIST_ID
ivan@712
    42
	                                                            AND RLT.AD_LANGUAGE  = ?,
ivan@712
    43
	            C_CURRENCY C
ivan@712
    44
	        WHERE DP.C_CURRENCY_ID = C.C_CURRENCY_ID 
ivan@712
    45
	        AND DP.PAYMENTRULE = RL.VALUE
ivan@712
    46
	        AND RL.AD_REFERENCE_ID = 195       
ivan@712
    47
	        AND DP.AD_Client_ID IN ('1') 
ivan@712
    48
	        AND DP.AD_Org_ID IN ('1') 
ivan@712
    49
	        AND DP.IsActive='Y'
ivan@712
    50
	        AND DP.IsValid='Y'
ivan@712
    51
	        ORDER BY DP.C_DEBT_PAYMENT_ID
ivan@712
    52
	        ) A ) B
ivan@712
    53
		WHERE 1=1
carlos@0
    54
        ]]>
carlos@0
    55
        </Sql>
carlos@0
    56
        <Field name="position" value="count"/>
ivan@712
    57
        <Parameter name="rownum" type="replace" optional="true" after="FROM ( SELECT " text="'0'" />        
carlos@0
    58
        <Parameter name="language"/>
carlos@0
    59
        <Parameter name="language"/>
carlos@0
    60
        <Parameter name="adUserClient" type="replace" optional="true" after="DP.AD_Client_ID IN (" text="'1'"/>
carlos@0
    61
        <Parameter name="adUserOrg" type="replace" optional="true" after="DP.AD_Org_ID IN (" text="'1'"/>
carlos@423
    62
        <Parameter name="businesPartner" optional="true" after="AND DP.IsValid='Y'"><![CDATA[ AND b.C_BPartner_ID = TO_NUMBER(?) ]]></Parameter>
ivan@712
    63
        <Parameter name="dateFrom" optional="true" after="DP.IsValid='Y'"><![CDATA[ AND dp.Dateplanned >= TO_DATE(?) ]]></Parameter>
ivan@712
    64
        <Parameter name="dateTo" optional="true" after="DP.IsValid='Y'"><![CDATA[ AND dp.Dateplanned < TO_DATE(?) ]]></Parameter>
ivan@712
    65
        <Parameter name="AmountFrom" optional="true" after="DP.IsValid='Y'"><![CDATA[ AND dp.amount >= TO_NUMBER(?) ]]></Parameter>
ivan@712
    66
        <Parameter name="AmountTo" optional="true" after="DP.IsValid='Y'"><![CDATA[ AND dp.amount <= TO_NUMBER(?) ]]></Parameter>
carlos@0
    67
        <Parameter name="paymentRule" optional="true" after="DP.IsValid='Y'"><![CDATA[ AND dp.paymentrule = ? ]]></Parameter>
ivan@712
    68
        <Parameter name="isPaid" optional="true" after="DP.IsValid='Y'"><![CDATA[ AND dp.ispaid = ? ]]></Parameter>
ivan@712
    69
        <Parameter name="isReceipt" optional="true" after="DP.IsValid='Y'"><![CDATA[ AND dp.isreceipt = ? ]]></Parameter>
carlos@0
    70
        <Parameter name="isPending" optional="true" after="DP.IsValid='Y'" text="AND C_DEBT_PAYMENT_STATUS(DP.C_Settlement_Cancel_ID, DP.CANCEL_PROCESSED, DP.GENERATE_PROCESSED, DP.ISPAID, DP.ISVALID, DP.C_CASHLINE_ID, DP.C_BANKSTATEMENTLINE_ID) = ?"/>
carlos@0
    71
        <Parameter name="invoiceId" optional="true" after="DP.IsValid='Y'"><![CDATA[ AND i.documentno like ? ]]></Parameter>
carlos@0
    72
        <Parameter name="orderId" optional="true" after="DP.IsValid='Y'"><![CDATA[ AND o.documentno like ? ]]></Parameter>
ivan@712
    73
        <Parameter name="orderBy" type="replace" optional="true" after="ORDER BY " text="DP.C_DEBT_PAYMENT_ID" />
ivan@712
    74
        <Parameter name="oraLimit" type="argument" optional="true" after="WHERE 1=1"><![CDATA[AND RN1 BETWEEN ]]></Parameter>
ivan@712
    75
        <Parameter name="pgLimit" type="argument" optional="true" after="WHERE 1=1"><![CDATA[LIMIT ]]></Parameter>
carlos@0
    76
   </SqlMethod>
carlos@0
    77
        
carlos@0
    78
   <SqlMethod name="set" type="constant" return="multiple">
carlos@0
    79
      <SqlMethodComment></SqlMethodComment>
carlos@0
    80
      <Sql></Sql>
carlos@0
    81
   </SqlMethod>
carlos@0
    82
</SqlClass>