src/org/openbravo/erpCommon/ad_actionButton/CreateFile_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 2089 67d686dc704f
permissions -rw-r--r--
Merge r2.5x intro trunk
<?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 id="class" name="CreateFileData" package="org.openbravo.erpCommon.ad_actionButton">
  <SqlClassComment></SqlClassComment>
  <SqlMethod name="select" type="preparedStatement" return="multiple">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
    <![CDATA[
				select B.NAME AS BANK,b.codebank, b.codebranch, b.DIGITCONTROL as digitcontrol1, ba.DIGITCONTROL as digitcontrol2, 
      ba.CODEACCOUNT, 
			LPAD(B.CODEBANK,4,'0')||LPAD(B.CODEBRANCH,4,'0')||B.DIGITCONTROL||ba.DIGITCONTROL||LPAD(ba.CODEACCOUNT,10,'0') AS N_CUENTA,
      lpad(rpad(c.NAME,40,' '),46,' ') AS ENTIDAD, rpad(c.NAME,40,' ') AS ENTIDAD19, oi.TAXID AS NIF, TO_CHAR(r.Duedate  ,'DD')||TO_CHAR(r.Duedate,'MM')||TO_CHAR(r.Duedate,'YY')AS DATEPLANNED, TO_CHAR(now(), 'DD')||TO_CHAR(now(), 'MM')||TO_CHAR(now(), 'YY') AS HOY, 
			LPAD(RPAD(LPAD(B.CODEBANK,4,'0')||LPAD(B.CODEBRANCH,4,'0'),74,' '),94,' ') AS ENTOFI, LPAD(B.INE_NUMBER,61,' ') AS INE, 
			'' AS TERCERO,
      '' AS FECHA_FACTURA, '' AS FECHA_VENCIMIENTO, '' AS PAYAMT, '' AS CREDITCARDNUMBER, '' AS N_FACTURA, '' AS N_FACTURA19, '' AS CIUDAD, '' AS DIRECCION,
      '' AS CONCEPTO, '' AS PLAZA, '' AS LOCALIDAD, '' AS POSTAL, '' AS CODIGO_PROVINCIA, '' AS LINEAS, '' AS ORDENANTES, '' AS HUECO, '' AS CONTENT
      from c_remittance r, ad_client c, 
					 c_bankAccount		ba,
					 c_Bank						b,
					 ad_orgInfo				oi
		 where r.ad_client_id = c.ad_client_id
		 	 and   r.AD_ORG_ID = oi.ad_org_id
			 and r.C_BANKACCOUNT_ID = ba.c_bankAccount_id
			 and b.c_bank_id = ba.c_bank_Id
       and r.C_Remittance_ID = ?
    ]]>
    </Sql>
    <Parameter name="cRemittanceId"/>
    </SqlMethod>
  <SqlMethod name="selectLineas" type="preparedStatement" return="multiple">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
    <![CDATA[
      SELECT RPAD(COALESCE(BP.NAME2,BP.NAME),40,' ') AS TERCERO,RPAD(TO_CHAR(COALESCE(C_INVOICE.DATEINVOICED,DP.DATEPLANNED), 'DDMMYY'),14) AS FECHA_FACTURA, 
      TO_CHAR(TO_CHAR(DP.DATEPLANNED, 'DDMMYY')) AS FECHA_VENCIMIENTO,LPAD(TO_CHAR(ROUND(DP.AMOUNT*100,0)),10,'0') AS PAYAMT, 
      (SELECT MAX(BP_A.ACCOUNTNO) FROM C_BP_BANKACCOUNT BP_A WHERE BP.C_BPARTNER_ID = BP_A.C_BPARTNER_ID AND BP_A.ISACTIVE='Y')  AS CREDITCARDNUMBER,
      LPAD(BP.VALUE,12,'0') AS N_FACTURA, RPAD(LPAD(BP.VALUE,8,'0'),12,' ') AS N_FACTURA19,L.CITY AS CIUDAD, RPAD(L.ADDRESS1,40,' ') AS DIRECCION, 
      TO_CHAR(now(),'DD')||TO_CHAR(now(),'MM')||TO_CHAR(now(),'YY') AS HOY, RPAD(COALESCE(TO_CHAR(DP.DESCRIPTION),' '),40,' ') AS CONCEPTO,
      RPAD(L.CITY,35,' ') AS PLAZA, RPAD(L.CITY,38,' ') AS LOCALIDAD, L.POSTAL AS POSTAL, SUBSTR(L.POSTAL,0,2) AS CODIGO_PROVINCIA, BP.TAXID AS NIF
      FROM c_remittanceline S, C_BPARTNER_LOCATION BP_L,
      C_LOCATION L, C_BPARTNER BP, 
      C_DEBT_PAYMENT DP left join C_INVOICE on DP.C_INVOICE_ID = C_INVOICE.C_INVOICE_ID
      WHERE S.C_debt_payment_id = DP.C_debt_payment_id
      AND DP.C_BPARTNER_ID = BP.C_BPARTNER_ID
      AND BP_L.C_BPARTNER_LOCATION_ID = C_GETBPLOCATIONID(BP.C_BPARTNER_ID, 'B')
      AND BP_L.C_LOCATION_ID = L.C_LOCATION_ID
      and s.C_Remittance_ID = ?
    ]]>
    </Sql>
    <Parameter name="cRemittanceId"/>
    </SqlMethod>
  <SqlMethod name="selectTotal" type="preparedStatement" return="multiple">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
    <![CDATA[
      SELECT RPAD(LPAD(TO_CHAR(ROUND(sum(DP.AMOUNT)*100,0)),10,'0'),16,' ') AS PAYAMT, 
      LPAD(TO_CHAR(COUNT(*)),10,'0') AS N_FACTURA 
      FROM C_DEBT_PAYMENT DP,
			     c_remittanceLine rl
		 where dp.c_debt_payment_id = rl.c_debt_payment_id
       and rl.C_Remittance_ID = ?
    ]]>
    </Sql>
    <Parameter name="cRemittanceId"/>
    </SqlMethod>
  <SqlMethod name="selectNLineas" type="preparedStatement" return="multiple">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
    <![CDATA[
    SELECT RPAD(LPAD(?,10,'0'),48,' ') AS LINEAS, LPAD(RPAD(LPAD('1',4,'0'),20,' '),72,' ') AS ORDENANTES, LPAD(' ',72,' ') AS HUECO FROM DUAL
    ]]>
    </Sql>
    <Parameter name="nLineas"/>
    </SqlMethod>
   <SqlMethod name="selectParam" type="preparedStatement" return="String">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
    <![CDATA[
      select upper(Content)
        from c_Remittance           r,
             c_Remittance_Type      rt,
             c_Remittance_Parameter rp
			 where r.C_Remittance_Type_id = rt.C_Remittance_Type_id
			   and rp.C_Remittance_Type_Id =  rt.C_Remittance_Type_id
				 and r.C_Remittance_id = ?
				 and upper(rp.name)=upper(to_char(?))
    ]]>
    </Sql>
    <Parameter name="cRemittanceId"/>
    <Parameter name="param"/>
    </SqlMethod>
  <SqlMethod name="selectComprobacion1" type="preparedStatement" return="string">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
    <![CDATA[
    SELECT count(*)
		FROM C_DEBT_PAYMENT DP,
		     c_remittanceLine rl 
    WHERE rl.c_debt_payment_id = dp.c_debt_payment_id
    AND DP.ISPAID = 'N' 
    AND DP.C_BPARTNER_ID = NULL
		and rl.c_remittance_id = ?
    ]]>
    </Sql>
    <Parameter name="cRemittanceId"/>
    </SqlMethod>
  <SqlMethod name="selectComprobacion2" type="preparedStatement" return="string">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
    <![CDATA[
    	SELECT COUNT(*) 
		  FROM c_remittanceLine rl 
    WHERE rl.c_remittance_id = ?
    ]]>
    </Sql>
    <Parameter name="cRemittanceId"/>
    </SqlMethod>
  <SqlMethod name="selectComprobacion3" type="preparedStatement" return="string">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
    <![CDATA[
    	select count(*)
		  from c_remittance
		where processed='Y'
		 and c_remittance_id = ?
    ]]>
    </Sql>
    <Parameter name="cRemittanceId"/>
    </SqlMethod>
  <SqlMethod name="selectComprobacion4" type="preparedStatement" return="string">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
    <![CDATA[
    		 SELECT count(*)
		 FROM C_DEBT_PAYMENT DP,
		      c_remittanceLine rl
    WHERE DP.C_DEBT_PAYMENT_ID = rl.C_DEBT_PAYMENT_id
    AND DP.ISPAID = 'N'
    AND DP.C_BPARTNER_ID IS NOT NULL
		and rl.c_remittance_id = ?
    ]]>
    </Sql>
    <Parameter name="cRemittanceId"/>
    </SqlMethod>
</SqlClass>