src/org/openbravo/erpCommon/ad_forms/DebtPaymentUnapply_data.xsql
author Phil Heenan <phillip.heenan@openbravo.com>
Tue, 06 May 2008 11:14:14 +0000
changeset 817 f4b6b1139d06
parent 683 8cc2b7ffa4f4
child 1605 8a0fe0193bef
permissions -rw-r--r--
BUG - [ 1822856 ] AT235: Java error on Change Payment Status. Instantiate OBError correctly. Cast expception being thrown when attempting to create the error. Cleaned the table presentation (Amount not being rendered properly due to currency being included in field) breaking out the currency into a seperate column. Adjusted column widths. Corrected error in query which was defaulting to language es_ES and not returning data correctly, language is now replaced by currect application language
<?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="DebtPaymentUnapplyData" package="org.openbravo.erpCommon.ad_forms">
  <SqlClassComment></SqlClassComment>
  <SqlMethod name="select" type="preparedStatement" return="multiple">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
    <![CDATA[
      SELECT c_debt_payment.c_debt_payment_id, c_debt_payment.c_settlement_cancel_id, 
      c_debt_payment.c_settlement_generate_id, AD_Column_Identifier(TO_CHAR('C_Settlement'),TO_CHAR(s1.C_Settlement_Id),TO_CHAR(?)) AS settlement_cancel, 
      AD_Column_Identifier(TO_CHAR('C_Settlement'),TO_CHAR(s2.C_Settlement_Id),TO_CHAR(?)) AS settlement_generate,AD_Column_Identifier(TO_CHAR('C_Invoice'),TO_CHAR(c_invoice.c_invoice_id),TO_CHAR(?)) AS invoice, c_debt_payment.DATEPLANNED,
      c_bpartner.NAME AS bpartner, c_debt_payment.AMOUNT AS amount, c_currency.ISO_CODE AS currency, ad_ref_list_v.NAME AS paymentrule,
      '' as iscancel
      FROM c_debt_payment left join c_settlement s1 on s1.c_settlement_id = c_debt_payment.c_settlement_cancel_id
                          left join c_settlement s2 on s2.c_settlement_id = c_debt_payment.c_settlement_generate_id
                          left join c_invoice       on c_invoice.C_INVOICE_ID = c_debt_payment.C_INVOICE_ID,
      c_bpartner,  ad_ref_list_v, c_currency
      WHERE  c_bpartner.C_BPARTNER_ID = c_debt_payment.C_BPARTNER_ID
      AND c_debt_payment.paymentrule = ad_ref_list_v.VALUE
      AND ad_ref_list_v.ad_reference_id = 195
      AND ad_ref_list_v.ad_language = 'es_ES' 
      AND c_debt_payment.C_CURRENCY_ID = c_currency.c_currency_id
      AND c_debt_payment.ispaid = 'Y'
      AND c_debt_payment.c_cashline_id IS NULL
      AND c_debt_payment.c_bankstatementline_id IS NULL
      AND c_debt_payment.AD_ORG_ID IN ('1')
      AND c_debt_payment.AD_CLIENT_ID IN ('1') 
      ORDER BY c_debt_payment.DATEPLANNED
      ]]></Sql>
   <Field name="rownum" value="count"/>
   <Parameter name="language"/>
   <Parameter name="language"/>
   <Parameter name="language"/>
   <Parameter name="language" type="replace" optional="true" after="AND ad_ref_list_v.ad_language = '" text="es_ES"/>
   <Parameter name="adOrgClient" type="replace" optional="true" after="AND c_debt_payment.AD_ORG_ID IN (" text="'1'"/>
   <Parameter name="adUserClient" type="replace" optional="true" after="AND c_debt_payment.AD_CLIENT_ID IN (" text="'1'"/>   
  </SqlMethod>
  <SqlMethod name="set" type="constant" return="multiple">
      <SqlMethodComment></SqlMethodComment>
      <Sql></Sql>
  </SqlMethod>
  <SqlMethod name="selectRecord" type="preparedStatement" return="multiple">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
    <![CDATA[
      SELECT (CASE WHEN C_SETTLEMENT_CANCEL_ID IS NULL THEN 'N' ELSE 'Y' END) AS ISCANCEL, C_DEBT_PAYMENT_ID FROM C_DEBT_PAYMENT
      WHERE C_DEBT_PAYMENT_ID IN ('1')
      ]]></Sql>
        <Parameter name="cDebtPaymentId" type="replace" optional="true" after="C_DEBT_PAYMENT_ID IN " text="('1')"/>
  </SqlMethod>
  <SqlMethod name="updateGenerate" type="preparedStatement" return="rowcount">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
      UPDATE C_DEBT_PAYMENT SET ispaid = 'N'
      WHERE c_debt_payment_ID = TO_NUMBER(?)
    </Sql>
   <Parameter name="cDebtPaymentId"/>
  </SqlMethod>
  <SqlMethod name="updateCancel" type="preparedStatement" return="rowcount">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
      UPDATE C_DEBT_PAYMENT SET ispaid = 'N', c_settlement_cancel_id = NULL, CANCEL_PROCESSED = 'N'
      WHERE c_debt_payment_ID = TO_NUMBER(?)
    </Sql>
   <Parameter name="cDebtPaymentId"/>
  </SqlMethod>
</SqlClass>