src/org/openbravo/erpCommon/ad_actionButton/CreateFile34_data.xsql
changeset 1605 8a0fe0193bef
parent 423 ecf368072c48
child 4680 6eaf0aea2ddf
equal deleted inserted replaced
1604:a1c518e6ed93 1605:8a0fe0193bef
    26   <SqlClassComment></SqlClassComment>
    26   <SqlClassComment></SqlClassComment>
    27   <SqlMethod name="select" type="preparedStatement" return="multiple">
    27   <SqlMethod name="select" type="preparedStatement" return="multiple">
    28     <SqlMethodComment></SqlMethodComment>
    28     <SqlMethodComment></SqlMethodComment>
    29     <Sql>
    29     <Sql>
    30     <![CDATA[      
    30     <![CDATA[      
    31     select lpad(TO_CHAR(oi.taxID),10)                                                             AS NIF,
    31     select lpad(oi.taxID,10)                                                             AS NIF,
    32     TO_CHAR(now(), 'DD')||TO_CHAR(now(), 'MM')||TO_CHAR(now(), 'YY')                     AS HOY,
    32     TO_CHAR(now(), 'DD')||TO_CHAR(now(), 'MM')||TO_CHAR(now(), 'YY')                     AS HOY,
    33     TO_CHAR(r.duedate, 'DD')||TO_CHAR(r.duedate, 'MM')||TO_CHAR(r.duedate, 'YY')         AS DUEDATE,
    33     TO_CHAR(r.duedate, 'DD')||TO_CHAR(r.duedate, 'MM')||TO_CHAR(r.duedate, 'YY')         AS DUEDATE,
    34     LPAD(TO_CHAR(B.CODEBANK),4,TO_CHAR('0'))||LPAD(TO_CHAR(B.CODEBRANCH),4,TO_CHAR('0'))||LPAD(TO_CHAR(ba.CODEACCOUNT),10,TO_CHAR('0')) AS N_CUENTA,
    34     LPAD(B.CODEBANK,4,'0')||LPAD(B.CODEBRANCH,4,'0')||LPAD(ba.CODEACCOUNT,10,'0')        AS N_CUENTA,
    35     LPAD(TO_CHAR(B.DIGITCONTROL||ba.DIGITCONTROL),2,TO_CHAR('0'))                                 AS DC,
    35     LPAD(B.DIGITCONTROL||ba.DIGITCONTROL,2,'0')                                          AS DC,
    36     RPAD(C.NAME,36)                                                                     AS NOMBRE,
    36     RPAD(C.NAME,36)                                                                      AS NOMBRE,
    37     RPAD(l.Address1||' '||l.Address2,36)                                                  AS DOMICILIO,
    37     RPAD(l.Address1||' '||l.Address2,36)                                                 AS DOMICILIO,
    38     RPAD(l.City,36)                                                                     AS PLAZA,
    38     RPAD(l.City,36)                                                                      AS PLAZA,
    39     rpad(' ',7,' ')                                                                      AS HUECO,
    39     rpad(' ',7,' ')                                                                      AS HUECO,
    40     ''                                                                                   AS PROVINCIA,
    40     ''                                                                                   AS PROVINCIA,
    41     ''                                                                                   AS CONCEPTO,
    41     ''                                                                                   AS CONCEPTO,
    42     ''                                                                                   AS PAYAMT,
    42     ''                                                                                   AS PAYAMT,
    43     ''                                                                                   AS LINEAS,
    43     ''                                                                                   AS LINEAS,
    57      where c.Ad_Client_ID = r.AD_Client_Id
    57      where c.Ad_Client_ID = r.AD_Client_Id
    58        and r.AD_ORG_ID = oi.ad_org_id
    58        and r.AD_ORG_ID = oi.ad_org_id
    59        and r.C_BANKACCOUNT_ID = ba.c_bankAccount_id
    59        and r.C_BANKACCOUNT_ID = ba.c_bankAccount_id
    60        and b.c_bank_id = ba.c_bank_Id
    60        and b.c_bank_id = ba.c_bank_Id
    61        and oi.C_Location_ID = l.C_location_ID
    61        and oi.C_Location_ID = l.C_location_ID
    62       and r.C_Remittance_ID = TO_NUMBER(?)
    62       and r.C_Remittance_ID = ?
    63     ]]>
    63     ]]>
    64     </Sql>
    64     </Sql>
    65     <Parameter name="cRemittanceId"/>
    65     <Parameter name="cRemittanceId"/>
    66     </SqlMethod>
    66     </SqlMethod>
    67 
    67 
    68    <SqlMethod name="selectLineas" type="preparedStatement" return="multiple">
    68    <SqlMethod name="selectLineas" type="preparedStatement" return="multiple">
    69     <SqlMethodComment></SqlMethodComment>
    69     <SqlMethodComment></SqlMethodComment>
    70     <Sql>
    70     <Sql>
    71     <![CDATA[
    71     <![CDATA[
    72       SELECT LPAD(TO_CHAR(BP.TAXID),12,TO_CHAR(' '))                      AS NIF, BP.TAXID,
    72       SELECT LPAD(BP.TAXID,12,' ')                      AS NIF, BP.TAXID,
    73              LPAD(TO_CHAR(DP.AMOUNT*100),12,TO_CHAR('0'))                   AS PAYAMT,
    73              LPAD(TO_CHAR(DP.AMOUNT*100),12,'0')                   AS PAYAMT,
    74              SUBSTR(TRIM(LPAD(TO_CHAR(BP_A.ACCOUNTNO),20,TO_CHAR('0'))),1,4)|| /*BANCO*/ 
    74              SUBSTR(TRIM(LPAD(BP_A.ACCOUNTNO,20,'0')),1,4)|| /*BANCO*/ 
    75              SUBSTR(TRIM(LPAD(TO_CHAR(BP_A.ACCOUNTNO),20,TO_CHAR('0'))),5,4)|| /*OFICINA*/ 
    75              SUBSTR(TRIM(LPAD(BP_A.ACCOUNTNO,20,'0')),5,4)|| /*OFICINA*/ 
    76              SUBSTR(TRIM(LPAD(TO_CHAR(BP_A.ACCOUNTNO),20,TO_CHAR('0'))),11) AS N_CUENTA,
    76              SUBSTR(TRIM(LPAD(BP_A.ACCOUNTNO,20,'0')),11) AS N_CUENTA,
    77              SUBSTR(TRIM(LPAD(TO_CHAR(BP_A.ACCOUNTNO),20,TO_CHAR('0'))),9,2)AS DC, BP_A.ACCOUNTNO AS ACCT,
    77              SUBSTR(TRIM(LPAD(BP_A.ACCOUNTNO,20,'0')),9,2)AS DC, BP_A.ACCOUNTNO AS ACCT,
    78              RPAD(COALESCE(BP.NAME2,BP.NAME),36,' ')    AS nombre, COALESCE(BP.NAME2,BP.NAME) AS NOM,
    78              RPAD(COALESCE(BP.NAME2,BP.NAME),36,' ')    AS nombre, COALESCE(BP.NAME2,BP.NAME) AS NOM,
    79              RPAD(L.ADDRESS1||' '||L.ADDRESS2,36,' ')   AS DOMICILIO,L.ADDRESS2||L.ADDRESS1 AS DOM,
    79              RPAD(L.ADDRESS1||' '||L.ADDRESS2,36,' ')   AS DOMICILIO,L.ADDRESS2||L.ADDRESS1 AS DOM,
    80              RPAD(L.POSTAL,5)||RPAD(L.CITY,31,' ')      AS PLAZA, L.POSTAL||L.CITY AS PLA,
    80              RPAD(L.POSTAL,5)||RPAD(L.CITY,31,' ')      AS PLAZA, L.POSTAL||L.CITY AS PLA,
    81              RPAD(COALESCE(to_char(R.DESCRIPTION),' '),36,' ')                AS PROVINCIA, R.DESCRIPTION AS PROV,
    81              RPAD(COALESCE(to_char(R.DESCRIPTION),' '),36,' ')                AS PROVINCIA, R.DESCRIPTION AS PROV,
    82              RPAD(COALESCE(to_char(DP.DESCRIPTION),' '),36,' ')                 AS CONCEPTO
    82              RPAD(COALESCE(to_char(DP.DESCRIPTION),' '),36,' ')                 AS CONCEPTO
    86                                   LEFT JOIN C_LOCATION L on BP_L.C_LOCATION_ID = L.C_LOCATION_ID 
    86                                   LEFT JOIN C_LOCATION L on BP_L.C_LOCATION_ID = L.C_LOCATION_ID 
    87                                   LEFT JOIN C_REGION R on L.C_REGION_ID = R.C_REGION_ID 
    87                                   LEFT JOIN C_REGION R on L.C_REGION_ID = R.C_REGION_ID 
    88       WHERE S.C_debt_payment_id = DP.C_debt_payment_id
    88       WHERE S.C_debt_payment_id = DP.C_debt_payment_id
    89       AND DP.C_BPARTNER_ID = BP.C_BPARTNER_ID
    89       AND DP.C_BPARTNER_ID = BP.C_BPARTNER_ID
    90       AND BP_L.C_BPARTNER_LOCATION_ID = COALESCE(C_GETBPLOCATIONID(BP.C_BPARTNER_ID, 'B'),BP_L.C_BPARTNER_LOCATION_ID)
    90       AND BP_L.C_BPARTNER_LOCATION_ID = COALESCE(C_GETBPLOCATIONID(BP.C_BPARTNER_ID, 'B'),BP_L.C_BPARTNER_LOCATION_ID)
    91       and s.C_Remittance_ID = TO_NUMBER(?)
    91       and s.C_Remittance_ID = ?
    92     ]]>
    92     ]]>
    93     </Sql>
    93     </Sql>
    94     <Parameter name="cRemittanceId"/>
    94     <Parameter name="cRemittanceId"/>
    95     </SqlMethod>
    95     </SqlMethod>
    96   
    96   
    97   
    97   
    98   <SqlMethod name="selectTotal" type="preparedStatement" return="multiple">
    98   <SqlMethod name="selectTotal" type="preparedStatement" return="multiple">
    99     <SqlMethodComment></SqlMethodComment>
    99     <SqlMethodComment></SqlMethodComment>
   100     <Sql>
   100     <Sql>
   101     <![CDATA[
   101     <![CDATA[
   102           SELECT lpad(Lpad(TO_CHAR(to_number(sum(DP.AMOUNT)*100)),12,TO_CHAR('0')),27,TO_CHAR(' ')) AS PAYAMT
   102           SELECT lpad(Lpad(TO_CHAR(sum(DP.AMOUNT)*100),12,'0'),27,' ') AS PAYAMT
   103       FROM C_DEBT_PAYMENT DP,
   103       FROM C_DEBT_PAYMENT DP,
   104            c_remittanceLine rl
   104            c_remittanceLine rl
   105      where dp.c_debt_payment_id = rl.c_debt_payment_id
   105      where dp.c_debt_payment_id = rl.c_debt_payment_id
   106        and rl.C_Remittance_ID = TO_NUMBER(?)
   106        and rl.C_Remittance_ID = ?
   107     ]]>
   107     ]]>
   108     </Sql>
   108     </Sql>
   109     <Parameter name="cRemittanceId"/>
   109     <Parameter name="cRemittanceId"/>
   110     </SqlMethod>
   110     </SqlMethod>
   111 
   111 
   112     
   112     
   113   <SqlMethod name="selectNLineas" type="preparedStatement" return="multiple">
   113   <SqlMethod name="selectNLineas" type="preparedStatement" return="multiple">
   114     <SqlMethodComment></SqlMethodComment>
   114     <SqlMethodComment></SqlMethodComment>
   115     <Sql>
   115     <Sql>
   116     <![CDATA[
   116     <![CDATA[
   117        SELECT LPAD(TO_CHAR(?),10,'0') AS LINEAS, 
   117        SELECT LPAD(?,10,'0') AS LINEAS, 
   118               LPAD(TO_CHAR(1),8,'0')  AS ORDENANTES, 
   118               LPAD('1',8,'0')  AS ORDENANTES, 
   119               LPAD(TO_CHAR(' '),13,' ') AS HUECO 
   119               LPAD(' ',13,' ') AS HUECO 
   120          FROM DUAL
   120          FROM DUAL
   121     ]]>
   121     ]]>
   122     </Sql>
   122     </Sql>
   123     <Parameter name="nLineas"/>
   123     <Parameter name="nLineas"/>
   124     </SqlMethod>
   124     </SqlMethod>
   145         from c_Remittance           r,
   145         from c_Remittance           r,
   146              c_Remittance_Type      rt,
   146              c_Remittance_Type      rt,
   147              c_Remittance_Parameter rp
   147              c_Remittance_Parameter rp
   148        where r.C_Remittance_Type_id = rt.C_Remittance_Type_id
   148        where r.C_Remittance_Type_id = rt.C_Remittance_Type_id
   149          and rp.C_Remittance_Type_Id =  rt.C_Remittance_Type_id
   149          and rp.C_Remittance_Type_Id =  rt.C_Remittance_Type_id
   150          and r.C_Remittance_ID = TO_NUMBER(?)
   150          and r.C_Remittance_id = ?
   151          and upper(rp.name)=upper(to_char(?))
   151          and upper(rp.name)=upper(to_char(?))
   152     ]]>
   152     ]]>
   153     </Sql>
   153     </Sql>
   154     <Parameter name="cRemittanceId"/>
   154     <Parameter name="cRemittanceId"/>
   155     <Parameter name="param"/>
   155     <Parameter name="param"/>
   172     <SqlMethodComment></SqlMethodComment>
   172     <SqlMethodComment></SqlMethodComment>
   173     <Sql>
   173     <Sql>
   174     <![CDATA[
   174     <![CDATA[
   175       SELECT COUNT(*) 
   175       SELECT COUNT(*) 
   176       FROM c_remittanceLine rl 
   176       FROM c_remittanceLine rl 
   177     WHERE rl.c_remittance_ID = TO_NUMBER(?)
   177     WHERE rl.c_remittance_id = ?
   178     ]]>
   178     ]]>
   179     </Sql>
   179     </Sql>
   180     <Parameter name="cRemittanceId"/>
   180     <Parameter name="cRemittanceId"/>
   181     </SqlMethod>
   181     </SqlMethod>
   182   <SqlMethod name="selectComprobacion3" type="preparedStatement" return="string">
   182   <SqlMethod name="selectComprobacion3" type="preparedStatement" return="string">
   184     <Sql>
   184     <Sql>
   185     <![CDATA[
   185     <![CDATA[
   186       select count(*)
   186       select count(*)
   187       from c_remittance
   187       from c_remittance
   188     where processed='Y'
   188     where processed='Y'
   189      and c_remittance_ID = TO_NUMBER(?)
   189      and c_remittance_id = ?
   190     ]]>
   190     ]]>
   191     </Sql>
   191     </Sql>
   192     <Parameter name="cRemittanceId"/>
   192     <Parameter name="cRemittanceId"/>
   193     </SqlMethod>
   193     </SqlMethod>
   194   <SqlMethod name="selectComprobacion4" type="preparedStatement" return="string">
   194   <SqlMethod name="selectComprobacion4" type="preparedStatement" return="string">
   199      FROM C_DEBT_PAYMENT DP,
   199      FROM C_DEBT_PAYMENT DP,
   200           c_remittanceLine rl
   200           c_remittanceLine rl
   201     WHERE DP.C_DEBT_PAYMENT_ID = rl.C_DEBT_PAYMENT_id
   201     WHERE DP.C_DEBT_PAYMENT_ID = rl.C_DEBT_PAYMENT_id
   202     AND DP.ISPAID = 'N'
   202     AND DP.ISPAID = 'N'
   203     AND DP.C_BPARTNER_ID IS NOT NULL
   203     AND DP.C_BPARTNER_ID IS NOT NULL
   204     and rl.c_remittance_ID = TO_NUMBER(?)
   204     and rl.c_remittance_id = ?
   205     ]]>
   205     ]]>
   206     </Sql>
   206     </Sql>
   207     <Parameter name="cRemittanceId"/>
   207     <Parameter name="cRemittanceId"/>
   208     </SqlMethod> 
   208     </SqlMethod> 
   209     -->
   209     -->