src/org/openbravo/erpCommon/ad_actionButton/CreateFile_data.xsql
author Carlos Romero <carlos.romero@openbravo.com>
Tue, 12 Feb 2008 18:53:44 +0000
changeset 423 ecf368072c48
parent 381 c94c8e05919d
child 1605 8a0fe0193bef
permissions -rw-r--r--
Fixed bug [ 1889741 ] Openbravo PostgreSQL 8.3 compatible
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 id="class" name="CreateFileData" package="org.openbravo.erpCommon.ad_actionButton">
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[
carlos@0
    31
				select B.NAME AS BANK,b.codebank, b.codebranch, b.DIGITCONTROL as digitcontrol1, ba.DIGITCONTROL as digitcontrol2, 
carlos@0
    32
      ba.CODEACCOUNT, 
carlos@423
    33
			LPAD(B.CODEBANK,4,TO_CHAR('0'))||LPAD(B.CODEBRANCH,4,TO_CHAR('0'))||B.DIGITCONTROL||ba.DIGITCONTROL||LPAD(ba.CODEACCOUNT,10,TO_CHAR('0')) AS N_CUENTA,
carlos@423
    34
      lpad(rpad(c.NAME,40,' '),46,TO_CHAR(' ')) 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, 
carlos@423
    35
			LPAD(RPAD(LPAD(B.CODEBANK,4,TO_CHAR('0'))||LPAD(B.CODEBRANCH,4,TO_CHAR('0')),74,' '),94,TO_CHAR(' ')) AS ENTOFI, LPAD(B.INE_NUMBER,61,TO_CHAR(' ')) AS INE, 
carlos@0
    36
			'' AS TERCERO,
carlos@0
    37
      '' AS FECHA_FACTURA, '' AS FECHA_VENCIMIENTO, '' AS PAYAMT, '' AS CREDITCARDNUMBER, '' AS N_FACTURA, '' AS N_FACTURA19, '' AS CIUDAD, '' AS DIRECCION,
carlos@0
    38
      '' AS CONCEPTO, '' AS PLAZA, '' AS LOCALIDAD, '' AS POSTAL, '' AS CODIGO_PROVINCIA, '' AS LINEAS, '' AS ORDENANTES, '' AS HUECO, '' AS CONTENT
carlos@0
    39
      from c_remittance r, ad_client c, 
carlos@0
    40
					 c_bankAccount		ba,
carlos@0
    41
					 c_Bank						b,
carlos@0
    42
					 ad_orgInfo				oi
carlos@0
    43
		 where r.ad_client_id = c.ad_client_id
carlos@0
    44
		 	 and   r.AD_ORG_ID = oi.ad_org_id
carlos@0
    45
			 and r.C_BANKACCOUNT_ID = ba.c_bankAccount_id
carlos@0
    46
			 and b.c_bank_id = ba.c_bank_Id
carlos@423
    47
       and r.C_Remittance_ID = TO_NUMBER(?)
carlos@0
    48
    ]]>
carlos@0
    49
    </Sql>
carlos@0
    50
    <Parameter name="cRemittanceId"/>
carlos@0
    51
    </SqlMethod>
carlos@0
    52
  <SqlMethod name="selectLineas" type="preparedStatement" return="multiple">
carlos@0
    53
    <SqlMethodComment></SqlMethodComment>
carlos@0
    54
    <Sql>
carlos@0
    55
    <![CDATA[
carlos@0
    56
      SELECT RPAD(COALESCE(BP.NAME2,BP.NAME),40,' ') AS TERCERO,RPAD(TO_CHAR(COALESCE(C_INVOICE.DATEINVOICED,DP.DATEPLANNED), 'DDMMYY'),14) AS FECHA_FACTURA, 
carlos@423
    57
      TO_CHAR(TO_CHAR(DP.DATEPLANNED, 'DDMMYY')) AS FECHA_VENCIMIENTO,LPAD(TO_CHAR(ROUND(DP.AMOUNT*100,0)),10,TO_CHAR('0')) AS PAYAMT, 
carlos@0
    58
      (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,
carlos@423
    59
      LPAD(TO_CHAR(BP.VALUE),12,TO_CHAR('0')) AS N_FACTURA, RPAD(LPAD(TO_CHAR(BP.VALUE),8,TO_CHAR('0')),12,' ') AS N_FACTURA19,L.CITY AS CIUDAD, RPAD(L.ADDRESS1,40,' ') AS DIRECCION, 
carlos@0
    60
      TO_CHAR(now(),'DD')||TO_CHAR(now(),'MM')||TO_CHAR(now(),'YY') AS HOY, RPAD(COALESCE(TO_CHAR(DP.DESCRIPTION),' '),40,' ') AS CONCEPTO,
carlos@0
    61
      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
carlos@0
    62
      FROM c_remittanceline S, C_BPARTNER_LOCATION BP_L,
carlos@0
    63
      C_LOCATION L, C_BPARTNER BP, 
carlos@0
    64
      C_DEBT_PAYMENT DP left join C_INVOICE on DP.C_INVOICE_ID = C_INVOICE.C_INVOICE_ID
carlos@0
    65
      WHERE S.C_debt_payment_id = DP.C_debt_payment_id
carlos@0
    66
      AND DP.C_BPARTNER_ID = BP.C_BPARTNER_ID
carlos@0
    67
      AND BP_L.C_BPARTNER_LOCATION_ID = C_GETBPLOCATIONID(BP.C_BPARTNER_ID, 'B')
carlos@0
    68
      AND BP_L.C_LOCATION_ID = L.C_LOCATION_ID
carlos@423
    69
      and s.C_Remittance_ID = TO_NUMBER(?)
carlos@0
    70
    ]]>
carlos@0
    71
    </Sql>
carlos@0
    72
    <Parameter name="cRemittanceId"/>
carlos@0
    73
    </SqlMethod>
carlos@0
    74
  <SqlMethod name="selectTotal" type="preparedStatement" return="multiple">
carlos@0
    75
    <SqlMethodComment></SqlMethodComment>
carlos@0
    76
    <Sql>
carlos@0
    77
    <![CDATA[
carlos@423
    78
      SELECT RPAD(LPAD(TO_CHAR(ROUND(sum(DP.AMOUNT)*100,0)),10,TO_CHAR('0')),16,TO_CHAR(' ')) AS PAYAMT, 
carlos@423
    79
      LPAD(TO_CHAR(COUNT(*)),10,TO_CHAR('0')) AS N_FACTURA 
carlos@0
    80
      FROM C_DEBT_PAYMENT DP,
carlos@0
    81
			     c_remittanceLine rl
carlos@0
    82
		 where dp.c_debt_payment_id = rl.c_debt_payment_id
carlos@423
    83
       and rl.C_Remittance_ID = TO_NUMBER(?)
carlos@0
    84
    ]]>
carlos@0
    85
    </Sql>
carlos@0
    86
    <Parameter name="cRemittanceId"/>
carlos@0
    87
    </SqlMethod>
carlos@0
    88
  <SqlMethod name="selectNLineas" type="preparedStatement" return="multiple">
carlos@0
    89
    <SqlMethodComment></SqlMethodComment>
carlos@0
    90
    <Sql>
carlos@0
    91
    <![CDATA[
carlos@423
    92
    SELECT RPAD(LPAD(TO_CHAR(?),10,TO_CHAR('0')),48,' ') AS LINEAS, LPAD(RPAD(LPAD(TO_CHAR(1),4,TO_CHAR('0')),20,' '),72,TO_CHAR(' ')) AS ORDENANTES, LPAD(' ',72,TO_CHAR(' ')) AS HUECO FROM DUAL
carlos@0
    93
    ]]>
carlos@0
    94
    </Sql>
carlos@0
    95
    <Parameter name="nLineas"/>
carlos@0
    96
    </SqlMethod>
carlos@0
    97
   <SqlMethod name="selectParam" type="preparedStatement" return="String">
carlos@0
    98
    <SqlMethodComment></SqlMethodComment>
carlos@0
    99
    <Sql>
carlos@0
   100
    <![CDATA[
carlos@0
   101
      select upper(Content)
carlos@0
   102
        from c_Remittance           r,
carlos@0
   103
             c_Remittance_Type      rt,
carlos@0
   104
             c_Remittance_Parameter rp
carlos@0
   105
			 where r.C_Remittance_Type_id = rt.C_Remittance_Type_id
carlos@0
   106
			   and rp.C_Remittance_Type_Id =  rt.C_Remittance_Type_id
carlos@423
   107
				 and r.C_Remittance_ID = TO_NUMBER(?)
carlos@0
   108
				 and upper(rp.name)=upper(to_char(?))
carlos@0
   109
    ]]>
carlos@0
   110
    </Sql>
carlos@0
   111
    <Parameter name="cRemittanceId"/>
carlos@0
   112
    <Parameter name="param"/>
carlos@0
   113
    </SqlMethod>
carlos@0
   114
  <SqlMethod name="selectComprobacion1" type="preparedStatement" return="string">
carlos@0
   115
    <SqlMethodComment></SqlMethodComment>
carlos@0
   116
    <Sql>
carlos@0
   117
    <![CDATA[
carlos@0
   118
    SELECT count(*)
carlos@0
   119
		FROM C_DEBT_PAYMENT DP,
carlos@0
   120
		     c_remittanceLine rl 
carlos@0
   121
    WHERE rl.c_debt_payment_id = dp.c_debt_payment_id
carlos@0
   122
    AND DP.ISPAID = 'N' 
carlos@0
   123
    AND DP.C_BPARTNER_ID = NULL
carlos@423
   124
		and rl.c_remittance_ID = TO_NUMBER(?)
carlos@0
   125
    ]]>
carlos@0
   126
    </Sql>
carlos@0
   127
    <Parameter name="cRemittanceId"/>
carlos@0
   128
    </SqlMethod>
carlos@0
   129
  <SqlMethod name="selectComprobacion2" type="preparedStatement" return="string">
carlos@0
   130
    <SqlMethodComment></SqlMethodComment>
carlos@0
   131
    <Sql>
carlos@0
   132
    <![CDATA[
carlos@0
   133
    	SELECT COUNT(*) 
carlos@0
   134
		  FROM c_remittanceLine rl 
carlos@423
   135
    WHERE rl.c_remittance_ID = TO_NUMBER(?)
carlos@0
   136
    ]]>
carlos@0
   137
    </Sql>
carlos@0
   138
    <Parameter name="cRemittanceId"/>
carlos@0
   139
    </SqlMethod>
carlos@0
   140
  <SqlMethod name="selectComprobacion3" type="preparedStatement" return="string">
carlos@0
   141
    <SqlMethodComment></SqlMethodComment>
carlos@0
   142
    <Sql>
carlos@0
   143
    <![CDATA[
carlos@0
   144
    	select count(*)
carlos@0
   145
		  from c_remittance
carlos@0
   146
		where processed='Y'
carlos@423
   147
		 and c_remittance_ID = TO_NUMBER(?)
carlos@0
   148
    ]]>
carlos@0
   149
    </Sql>
carlos@0
   150
    <Parameter name="cRemittanceId"/>
carlos@0
   151
    </SqlMethod>
carlos@0
   152
  <SqlMethod name="selectComprobacion4" type="preparedStatement" return="string">
carlos@0
   153
    <SqlMethodComment></SqlMethodComment>
carlos@0
   154
    <Sql>
carlos@0
   155
    <![CDATA[
carlos@0
   156
    		 SELECT count(*)
carlos@0
   157
		 FROM C_DEBT_PAYMENT DP,
carlos@0
   158
		      c_remittanceLine rl
carlos@0
   159
    WHERE DP.C_DEBT_PAYMENT_ID = rl.C_DEBT_PAYMENT_id
carlos@0
   160
    AND DP.ISPAID = 'N'
carlos@0
   161
    AND DP.C_BPARTNER_ID IS NOT NULL
carlos@423
   162
		and rl.c_remittance_ID = TO_NUMBER(?)
carlos@0
   163
    ]]>
carlos@0
   164
    </Sql>
carlos@0
   165
    <Parameter name="cRemittanceId"/>
carlos@0
   166
    </SqlMethod>
carlos@0
   167
</SqlClass>
carlos@0
   168