src/org/openbravo/erpCommon/ad_actionButton/CreateFile34_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 4680 6eaf0aea2ddf
permissions -rw-r--r--
Merge r2.5x intro trunk
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="CreateFile34Data" 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[      
juanpablo@1605
    31
    select lpad(oi.taxID,10)                                                             AS NIF,
carlos@0
    32
    TO_CHAR(now(), 'DD')||TO_CHAR(now(), 'MM')||TO_CHAR(now(), 'YY')                     AS HOY,
carlos@0
    33
    TO_CHAR(r.duedate, 'DD')||TO_CHAR(r.duedate, 'MM')||TO_CHAR(r.duedate, 'YY')         AS DUEDATE,
juanpablo@1605
    34
    LPAD(B.CODEBANK,4,'0')||LPAD(B.CODEBRANCH,4,'0')||LPAD(ba.CODEACCOUNT,10,'0')        AS N_CUENTA,
juanpablo@1605
    35
    LPAD(B.DIGITCONTROL||ba.DIGITCONTROL,2,'0')                                          AS DC,
juanpablo@1605
    36
    RPAD(C.NAME,36)                                                                      AS NOMBRE,
juanpablo@1605
    37
    RPAD(l.Address1||' '||l.Address2,36)                                                 AS DOMICILIO,
juanpablo@1605
    38
    RPAD(l.City,36)                                                                      AS PLAZA,
carlos@0
    39
    rpad(' ',7,' ')                                                                      AS HUECO,
carlos@0
    40
    ''                                                                                   AS PROVINCIA,
carlos@0
    41
    ''                                                                                   AS CONCEPTO,
carlos@0
    42
    ''                                                                                   AS PAYAMT,
carlos@0
    43
    ''                                                                                   AS LINEAS,
carlos@0
    44
    ''                                                                                   AS ORDENANTES,
carlos@0
    45
    oi.taxID                                                                             AS TAXID,
carlos@0
    46
    ''                                                                                   AS DOM,
carlos@0
    47
    ''                                                                                   AS PLA,
carlos@0
    48
    ''                                                                                   AS PROV,
carlos@0
    49
    ''                                                                                   AS NOM,
carlos@0
    50
    B.CODEBANK || B.CODEBRANCH || ba.CODEACCOUNT                                         AS ACCT
carlos@0
    51
     from  c_bankAccount    ba,
carlos@0
    52
           ad_client        c,
carlos@0
    53
           c_Bank           b,
carlos@0
    54
           ad_orgInfo       oi,
carlos@0
    55
           c_remittance     r,
carlos@0
    56
           c_Location       l
carlos@0
    57
     where c.Ad_Client_ID = r.AD_Client_Id
carlos@0
    58
       and r.AD_ORG_ID = oi.ad_org_id
carlos@0
    59
       and r.C_BANKACCOUNT_ID = ba.c_bankAccount_id
carlos@0
    60
       and b.c_bank_id = ba.c_bank_Id
carlos@0
    61
       and oi.C_Location_ID = l.C_location_ID
juanpablo@1605
    62
      and r.C_Remittance_ID = ?
carlos@0
    63
    ]]>
carlos@0
    64
    </Sql>
carlos@0
    65
    <Parameter name="cRemittanceId"/>
carlos@0
    66
    </SqlMethod>
carlos@0
    67
carlos@0
    68
   <SqlMethod name="selectLineas" type="preparedStatement" return="multiple">
carlos@0
    69
    <SqlMethodComment></SqlMethodComment>
carlos@0
    70
    <Sql>
carlos@0
    71
    <![CDATA[
juanpablo@1605
    72
      SELECT LPAD(BP.TAXID,12,' ')                      AS NIF, BP.TAXID,
juanpablo@1605
    73
             LPAD(TO_CHAR(DP.AMOUNT*100),12,'0')                   AS PAYAMT,
juanpablo@1605
    74
             SUBSTR(TRIM(LPAD(BP_A.ACCOUNTNO,20,'0')),1,4)|| /*BANCO*/ 
juanpablo@1605
    75
             SUBSTR(TRIM(LPAD(BP_A.ACCOUNTNO,20,'0')),5,4)|| /*OFICINA*/ 
juanpablo@1605
    76
             SUBSTR(TRIM(LPAD(BP_A.ACCOUNTNO,20,'0')),11) AS N_CUENTA,
juanpablo@1605
    77
             SUBSTR(TRIM(LPAD(BP_A.ACCOUNTNO,20,'0')),9,2)AS DC, BP_A.ACCOUNTNO AS ACCT,
carlos@0
    78
             RPAD(COALESCE(BP.NAME2,BP.NAME),36,' ')    AS nombre, COALESCE(BP.NAME2,BP.NAME) AS NOM,
carlos@0
    79
             RPAD(L.ADDRESS1||' '||L.ADDRESS2,36,' ')   AS DOMICILIO,L.ADDRESS2||L.ADDRESS1 AS DOM,
carlos@0
    80
             RPAD(L.POSTAL,5)||RPAD(L.CITY,31,' ')      AS PLAZA, L.POSTAL||L.CITY AS PLA,
carlos@0
    81
             RPAD(COALESCE(to_char(R.DESCRIPTION),' '),36,' ')                AS PROVINCIA, R.DESCRIPTION AS PROV,
carlos@0
    82
             RPAD(COALESCE(to_char(DP.DESCRIPTION),' '),36,' ')                 AS CONCEPTO
carlos@0
    83
             FROM c_remittanceline S, C_DEBT_PAYMENT DP,
carlos@0
    84
                                  C_BPARTNER BP left join C_BP_BANKACCOUNT BP_A on BP.C_BPARTNER_ID = BP_A.C_BPARTNER_ID
carlos@0
    85
                                  LEFT JOIN C_BPARTNER_LOCATION BP_L on BP.C_BPARTNER_ID = BP_L.C_BPARTNER_ID
carlos@0
    86
                                  LEFT JOIN C_LOCATION L on BP_L.C_LOCATION_ID = L.C_LOCATION_ID 
carlos@0
    87
                                  LEFT JOIN C_REGION R on L.C_REGION_ID = R.C_REGION_ID 
carlos@0
    88
      WHERE S.C_debt_payment_id = DP.C_debt_payment_id
carlos@0
    89
      AND DP.C_BPARTNER_ID = BP.C_BPARTNER_ID
carlos@0
    90
      AND BP_L.C_BPARTNER_LOCATION_ID = COALESCE(C_GETBPLOCATIONID(BP.C_BPARTNER_ID, 'B'),BP_L.C_BPARTNER_LOCATION_ID)
juanpablo@1605
    91
      and s.C_Remittance_ID = ?
carlos@0
    92
    ]]>
carlos@0
    93
    </Sql>
carlos@0
    94
    <Parameter name="cRemittanceId"/>
carlos@0
    95
    </SqlMethod>
carlos@0
    96
  
carlos@0
    97
  
carlos@0
    98
  <SqlMethod name="selectTotal" type="preparedStatement" return="multiple">
carlos@0
    99
    <SqlMethodComment></SqlMethodComment>
carlos@0
   100
    <Sql>
carlos@0
   101
    <![CDATA[
juanpablo@1605
   102
          SELECT lpad(Lpad(TO_CHAR(sum(DP.AMOUNT)*100),12,'0'),27,' ') AS PAYAMT
carlos@0
   103
      FROM C_DEBT_PAYMENT DP,
carlos@0
   104
           c_remittanceLine rl
carlos@0
   105
     where dp.c_debt_payment_id = rl.c_debt_payment_id
juanpablo@1605
   106
       and rl.C_Remittance_ID = ?
carlos@0
   107
    ]]>
carlos@0
   108
    </Sql>
carlos@0
   109
    <Parameter name="cRemittanceId"/>
carlos@0
   110
    </SqlMethod>
carlos@0
   111
carlos@0
   112
    
carlos@0
   113
  <SqlMethod name="selectNLineas" type="preparedStatement" return="multiple">
carlos@0
   114
    <SqlMethodComment></SqlMethodComment>
carlos@0
   115
    <Sql>
carlos@0
   116
    <![CDATA[
juanpablo@1605
   117
       SELECT LPAD(?,10,'0') AS LINEAS, 
juanpablo@1605
   118
              LPAD('1',8,'0')  AS ORDENANTES, 
juanpablo@1605
   119
              LPAD(' ',13,' ') AS HUECO 
carlos@0
   120
         FROM DUAL
carlos@0
   121
    ]]>
carlos@0
   122
    </Sql>
carlos@0
   123
    <Parameter name="nLineas"/>
carlos@0
   124
    </SqlMethod>
carlos@0
   125
carlos@0
   126
    <SqlMethod name="selectComprobacion341" type="preparedStatement" return="string">
carlos@0
   127
    <SqlMethodComment></SqlMethodComment>
carlos@0
   128
    <Sql>
carlos@0
   129
    <![CDATA[
carlos@0
   130
           select count(*)
carlos@0
   131
             from dual
carlos@0
   132
            where to_date(?,'DDMMYY') > to_date(?,'DDMMYY')
carlos@0
   133
    ]]>
carlos@0
   134
    </Sql>
carlos@0
   135
    <Parameter name="pNow"/>
carlos@0
   136
    <Parameter name="pDueDate"/>
carlos@0
   137
    </SqlMethod>
carlos@0
   138
carlos@0
   139
<!--    
carlos@0
   140
   <SqlMethod name="selectParam" type="preparedStatement" return="String">
carlos@0
   141
    <SqlMethodComment></SqlMethodComment>
carlos@0
   142
    <Sql>
carlos@0
   143
    <![CDATA[
carlos@0
   144
      select Content
carlos@0
   145
        from c_Remittance           r,
carlos@0
   146
             c_Remittance_Type      rt,
carlos@0
   147
             c_Remittance_Parameter rp
carlos@0
   148
       where r.C_Remittance_Type_id = rt.C_Remittance_Type_id
carlos@0
   149
         and rp.C_Remittance_Type_Id =  rt.C_Remittance_Type_id
juanpablo@1605
   150
         and r.C_Remittance_id = ?
carlos@0
   151
         and upper(rp.name)=upper(to_char(?))
carlos@0
   152
    ]]>
carlos@0
   153
    </Sql>
carlos@0
   154
    <Parameter name="cRemittanceId"/>
carlos@0
   155
    <Parameter name="param"/>
carlos@0
   156
    </SqlMethod>
carlos@0
   157
carlos@0
   158
    <Parameter name="cRemittanceId"/>
carlos@0
   159
    </SqlMethod> 
carlos@0
   160
  <SqlMethod name="selectComprobacion1" type="preparedStatement" return="string">
carlos@0
   161
    <SqlMethodComment></SqlMethodComment>
carlos@0
   162
    <Sql>
carlos@0
   163
    <![CDATA[
carlos@0
   164
           select count(*)
carlos@0
   165
             from dual
carlos@0
   166
            where to_date('220606','DDMMYY') > to_date('080606','DDMMYY')
carlos@0
   167
    ]]>
carlos@0
   168
    </Sql>
carlos@0
   169
    <Parameter name="cRemittanceId"/>
carlos@0
   170
    </SqlMethod>
carlos@0
   171
  <SqlMethod name="selectComprobacion2" type="preparedStatement" return="string">
carlos@0
   172
    <SqlMethodComment></SqlMethodComment>
carlos@0
   173
    <Sql>
carlos@0
   174
    <![CDATA[
carlos@0
   175
      SELECT COUNT(*) 
carlos@0
   176
      FROM c_remittanceLine rl 
juanpablo@1605
   177
    WHERE rl.c_remittance_id = ?
carlos@0
   178
    ]]>
carlos@0
   179
    </Sql>
carlos@0
   180
    <Parameter name="cRemittanceId"/>
carlos@0
   181
    </SqlMethod>
carlos@0
   182
  <SqlMethod name="selectComprobacion3" type="preparedStatement" return="string">
carlos@0
   183
    <SqlMethodComment></SqlMethodComment>
carlos@0
   184
    <Sql>
carlos@0
   185
    <![CDATA[
carlos@0
   186
      select count(*)
carlos@0
   187
      from c_remittance
carlos@0
   188
    where processed='Y'
juanpablo@1605
   189
     and c_remittance_id = ?
carlos@0
   190
    ]]>
carlos@0
   191
    </Sql>
carlos@0
   192
    <Parameter name="cRemittanceId"/>
carlos@0
   193
    </SqlMethod>
carlos@0
   194
  <SqlMethod name="selectComprobacion4" type="preparedStatement" return="string">
carlos@0
   195
    <SqlMethodComment></SqlMethodComment>
carlos@0
   196
    <Sql>
carlos@0
   197
    <![CDATA[
carlos@0
   198
         SELECT count(*)
carlos@0
   199
     FROM C_DEBT_PAYMENT DP,
carlos@0
   200
          c_remittanceLine rl
carlos@0
   201
    WHERE DP.C_DEBT_PAYMENT_ID = rl.C_DEBT_PAYMENT_id
carlos@0
   202
    AND DP.ISPAID = 'N'
carlos@0
   203
    AND DP.C_BPARTNER_ID IS NOT NULL
juanpablo@1605
   204
    and rl.c_remittance_id = ?
carlos@0
   205
    ]]>
carlos@0
   206
    </Sql>
carlos@0
   207
    <Parameter name="cRemittanceId"/>
carlos@0
   208
    </SqlMethod> 
carlos@0
   209
    -->
carlos@0
   210
</SqlClass>