src/org/openbravo/erpCommon/ad_actionButton/CreateFile_data.xsql
changeset 0 0247c26f10c6
child 381 c94c8e05919d
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/src/org/openbravo/erpCommon/ad_actionButton/CreateFile_data.xsql	Tue Nov 06 08:33:31 2007 +0000
@@ -0,0 +1,168 @@
+<?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(DP.AMOUNT*100,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(sum(DP.AMOUNT)*100,10,0),16,' ') AS PAYAMT, 
+      LPAD(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>
+