Archve:CustomRemittance

This example shows how to build your own remittance file, used for an automatic payment transaction. In order to create a new remittance we need to modify/create some files. Suposse that the name of our new remittance is 71.

1.-Create the CreateFile71_data.xsql with the following sqls querys, you can use as a model CreateFile_data.xsql.

ATTENTION because all the variables used in the xsql file must be used in the first method of the file[2], this is the reason for this "strange" query that return null values. It's also possible to add variables to a class by using:

<Field name="NAME_Id" value=""/>

after </Sql> tag[3]

For the Header:

<SqlMethod name="select" type="preparedStatement" return="multiple">
<SqlMethodComment></SqlMethodComment>
<Sql>
<![CDATA[
select oi.TAXID AS NIF, 
TO_CHAR(now(),'DD')||TO_CHAR(now(),'MM')||TO_CHAR(now(), 'YY') AS HOY,
TO_CHAR(r.Duedate,'DD')||TO_CHAR(r.Duedate,'MM')||TO_CHAR(r.Duedate,'YY')AS DATEPLANNED,LPAD(B.CODEBANK,4,0)||LPAD(B.CODEBRANCH,4,0)||B.DIGITCONTROL||ba.DIGITCONTROL||LPAD(ba.CODEACCOUNT,10,0) AS NCUENTA,
'B' AS BE,
lpad(rpad(c.NAME,40,' '),46,' ') AS ENTIDAD,
' ' AS TERCERO6,
' ' AS PAYAMT,
' ' AS TERCERO,
' ' AS POSTAL,
' ' AS DIRECCION,
' ' AS FECHAVENCIMIENTO,
' ' AS ENE,
' ' AS POBLACION,
' ' AS PROVINCIA,
' ' AS POREFERENCE,
' ' AS N_FACTURA,
' ' AS N_REGISTROS
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>

For the lines:

<SqlMethod name="selectLineas" type="preparedStatement" return="multiple">
<SqlMethodComment></SqlMethodComment>
<Sql>
<![CDATA[
SELECT RPAD(BP.NAME,6,' ') AS TERCERO6,
LPAD(sum(DP.AMOUNT)*100,10,0) AS PAYAMT,
RPAD(BP.NAME,40,' ') AS TERCERO,
'01' || TO_CHAR(L.POSTAL) AS POSTAL,
RPAD(L.ADDRESS1,40,' ') AS DIRECCION,
TO_CHAR(TO_CHAR(DP.DATEPLANNED, 'DDMMYY')) AS FECHAVENCIMIENTO,
'N' AS ENE,
RPAD(L.CITY,38,' ') AS POBLACION,
RPAD(L.REGIONNAME,38,' ') AS PROVINCIA,
stragg(TO_CHAR(C_INVOICE.POREFERENCE)) AS POREFERENCE
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 = ?
GROUP BY BP.NAME,
BP.NAME2,
L.CITY,
L.ADDRESS1,
DP.DATEPLANNED,
L.POSTAL,
BP.TAXID,
L.REGIONNAME
]]>
</Sql>
<Parameter name="cRemittanceId"/>
</SqlMethod>

For the Total:

<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,
LPAD(COUNT(*)+2,10,0) AS N_REGISTROS 
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>

And an additional check:

<SqlMethod name="selectComprobacion" type="preparedStatement" return="multiple">
<SqlMethodComment></SqlMethodComment>
<Sql>
<![CDATA[
SELECT RPAD(BP.NAME,6,' ') AS TERCERO6,
sum(DP.AMOUNT) AS PAYAMT,
RPAD(BP.NAME,40,' ') AS TERCERO,
'01' || TO_CHAR(L.POSTAL) AS POSTAL,
RPAD(L.ADDRESS1,40,' ') AS DIRECCION,
TO_CHAR(TO_CHAR(DP.DATEPLANNED, 'DDMMYY')) AS FECHAVENCIMIENTO,
'N' AS ENE,
RPAD(L.CITY,38,' ') AS POBLACION,
RPAD(L.REGIONNAME,38,' ') AS PROVINCIA,
stragg(TO_CHAR(C_INVOICE.POREFERENCE)) AS POREFERENCE
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 = ?
GROUP BY BP.NAME,
BP.NAME2,
L.CITY,
L.ADDRESS1,
DP.DATEPLANNED,
L.POSTAL,
BP.TAXID,
L.REGIONNAME
HAVING sum(DP.AMOUNT) <= 0    
]]>
</Sql>
<Parameter name="nLineas"/>
</SqlMethod>

-Pay attention to the stragg function[1] in the SqlMethod "selectLineas". It is NOT an ORACLE function and it needs to be created:

CREATE OR REPLACE TYPE string_agg_type 
AS OBJECT(total VARCHAR2(4000),STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT string_agg_type ) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateIterate(self IN OUT string_agg_type , value IN VARCHAR2 )
RETURN NUMBER,
MEMBER FUNCTION
ODCIAggregateTerminate(self IN string_agg_type,
returnValue OUT  varchar2,
flags IN number)
RETURN NUMBER,
MEMBER FUNCTION
ODCIAggregateMerge(self IN OUT string_agg_type,
ctx2 IN string_agg_type)
RETURN NUMBER
);

CREATE OR REPLACE TYPE BODY string_agg_type IS STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT string_agg_type) RETURN NUMBER IS BEGIN sctx := string_agg_type( null );
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateIterate(self IN OUT string_agg_type, value IN varchar2 ) RETURN NUMBER IS BEGIN self.total := self.total || '  ' || value;
RETURN ODCIConst.Success;
END;

MEMBER FUNCTION ODCIAggregateTerminate(self IN string_agg_type, returnValue OUT varchar2, flags IN number) RETURN NUMBER IS BEGIN returnValue:=ltrim(self.total,'  ');
return ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateMerge(self IN OUT string_agg_type, ctx2 IN string_agg_type) RETURN NUMBER IS BEGIN self.total := self.total || ctx2.total;
return ODCIConst.Success;
END;
end;

CREATE or replace FUNCTION stragg(input VARCHAR2) RETURN varchar2 PARALLEL_ENABLE AGGREGATE USING string_agg_type;

2.-Create the function printPageFind71 IN CreateFile.java. You can use as example the following function

void printPageFind71(HttpServletResponse response, VariablesSecureApp vars, String strKey) throws IOException, ServletException{
   if (log4j.isDebugEnabled()) log4j.debug("Output: pageFind71");
   StringBuffer strBuf = new StringBuffer();
   String strMessage = "";
   //Get Header's Info
   CreateFile71Data[] Principio = CreateFile71Data.select(this, strKey);
   //Get Line's Info
   CreateFile71Data[] Lineas = CreateFile71Data.selectLineas(this,strKey);
   //Get Total's Info
   CreateFile71Data[] Total = CreateFile71Data.selectTotal(this,strKey);
   //Check amount > 0 ELSE EXIT;
  CreateFile71Data[] Comprobacion = CreateFile71Data.selectComprobacion(this, strKey);
     if(Comprobacion.length != 0){
     if (log4j.isDebugEnabled()) log4j.debug("Error: Negative DebPayments:" + Comprobacion.length);
     strMessage = Utility.messageBD(this, "CreateFile71Error: Negative DebPayments", vars.getLanguage());
     printPage(response, vars, strKey, "", "", strMessage);
   }
   if (log4j.isDebugEnabled()) log4j.debug("check1 ok");
   String strLinea = "";
   //Debugging Lines - Remittance header
   if (Principio == null || Principio.length == 0){
       strMessage = Utility.messageBD(this, "DefaultAccountError", vars.getLanguage());
       printPage(response, vars, strKey, "", "", strMessage);
       return;
}
   if (Lineas == null || Total == null)return;
   if (Principio[0].nif == null || Principio[0].nif.equals("")){
       strMessage = Utility.messageBD(this, "NIFError", vars.getLanguage());
       if (log4j.isDebugEnabled()) log4j.debug("NIFError");
   }
   if (Principio[0].hoy == null || Principio[0].hoy.equals("")){
       strMessage = Utility.messageBD(this, "HoyError", vars.getLanguage());
       if (log4j.isDebugEnabled()) log4j.debug("HoyError");
   }
   if (Principio[0].dateplanned == null || Principio[0].dateplanned.equals("")){
       strMessage = Utility.messageBD(this, "DatePlannedError", vars.getLanguage());
       if (log4j.isDebugEnabled()) log4j.debug("DatePlannedError");
   }
   if (Principio[0].ncuenta == null || Principio[0].ncuenta.equals("")){
       strMessage = Utility.messageBD(this, "NumeroCuentaError", vars.getLanguage());
       if (log4j.isDebugEnabled()) log4j.debug("NumeroCuentaError");
   }
   if (Principio[0].entidad == null || Principio[0].entidad.equals("")){
       strMessage = Utility.messageBD(this, "EntidadError", vars.getLanguage());
       if (log4j.isDebugEnabled()) log4j.debug("EntidadError");
   }
   if (log4j.isDebugEnabled()) log4j.debug("check2 ok");
   //1st Header's Line
   strBuf = strBuf.append("71E").append(Principio[0].nif).append(Principio[0].hoy).append(Principio[0].dateplanned).append(Principio[0].ncuenta).append(Total[0].payamt).append(Total[0].nRegistros).append(Total[0].nFactura).append(Principio[0].be).append("\r\n");
   //2nd Header's Line
   strBuf = strBuf.append("71O").append(Principio[0].nif).append(Principio[0].entidad).append("A TEXT FIELD NEEDED IN THIS FILE").append("\r\n");
   // Lines
   for (int i=0;i<Lineas.length;i++){
       //Debugging Lines - Lines
       if (Lineas[i].tercero6 == null || Lineas[i].tercero6.equals("")){
           if (log4j.isDebugEnabled()) log4j.debug("Tercero6Error");
           strMessage = Utility.messageBD(this, "Tercero6Error", vars.getLanguage()) + Lineas[i].tercero;
       }
       if (Lineas[i].payamt == null || Lineas[i].payamt.equals("")){
         if (log4j.isDebugEnabled()) log4j.debug("PaymentAmountError");
           strMessage = Utility.messageBD(this, "PaymentAmountError", vars.getLanguage()) + Lineas[i].tercero;
       }
       if (Lineas[i].direccion == null || Lineas[i].direccion.equals("")){
         if (log4j.isDebugEnabled()) log4j.debug("AddressError");
           strMessage = Utility.messageBD(this, "AddressError", vars.getLanguage()) + Lineas[i].tercero;
       }
       if (Lineas[i].tercero == null || Lineas[i].tercero.equals("")){
           if (log4j.isDebugEnabled()) log4j.debug("TerceroError");
           strMessage = Utility.messageBD(this, "TerceroError", vars.getLanguage()) + Lineas[i].tercero;
       }
       if (Lineas[i].postal == null || Lineas[i].postal.equals("")){
         if (log4j.isDebugEnabled()) log4j.debug("PostCodeError");
           strMessage = Utility.messageBD(this, "PostCodeError", vars.getLanguage()) + Lineas[i].tercero;
       }
       if (Lineas[i].fechavencimiento == null || Lineas[i].fechavencimiento.equals("")){
         if (log4j.isDebugEnabled()) log4j.debug("FechaVencimientoError");
           strMessage = Utility.messageBD(this, "FechaVencimientoError", vars.getLanguage()) + Lineas[i].tercero;
       }
       if (Lineas[i].poblacion == null || Lineas[i].poblacion.equals("")){
         if (log4j.isDebugEnabled()) log4j.debug("PoblacionError");
           strMessage = Utility.messageBD(this, "PoblacionError", vars.getLanguage()) + Lineas[i].tercero;;
       }
       if (Lineas[i].provincia == null || Lineas[i].provincia.equals("")){
         if (log4j.isDebugEnabled()) log4j.debug("ProvinciaError");
           strMessage = Utility.messageBD(this, "ProvinciaError", vars.getLanguage()) + Lineas[i].tercero;;
       }
       if (Lineas[i].poreference == null || Lineas[i].poreference.equals("")){
         if (log4j.isDebugEnabled()) log4j.debug("POreferenceError");
           strMessage = Utility.messageBD(this, "POreferenceError", vars.getLanguage()) + Lineas[i].tercero;;
       }
       strBuf = strBuf.append("71B").append(Lineas[i].tercero6).append(Lineas[i].payamt).append(Lineas[i].tercero).append(Lineas[i].postal).append(Lineas[i].direccion).append(Lineas[i].fechavencimiento).append(Lineas[i].ene).append("\r\n");
       strBuf = strBuf.append("71P").append(Lineas[i].tercero6).append(Lineas[i].poblacion).append(Lineas[i].provincia).append("\r\n");
       strBuf = strBuf.append("71C").append(Lineas[i].tercero6).append(Lineas[i].poreference).append("\r\n");
   }
   if (log4j.isDebugEnabled()) log4j.debug("check3 ok");
   if (!strMessage.equals("")){
     printPage(response, vars, strKey, "", "", strMessage);
   }else{
     response.setContentType("application/rtf");
     response.setHeader("Content-Disposition","attachment; filename=BANK.DAT");
     PrintWriter out = response.getWriter();
     out.println(strBuf.toString());
     out.close();
   }
 }

Some issues that also handled by this function

-Pass one check (Comprobacion), using the SqlMethod "selectComprobacion"
-We parse the input to check not null values are introduced in the file (//Debugging Lines)

3.-Create a new "Cuaderno" in Openbravo (with the number 71)

4.-Modify getPrintPage function (in CreateFile.java) to call the already created printPageFind71 when our "Cuaderno" is used:

   ...    
   if (strCuaderno.equals("58")) printPageFind58(response, vars, strKey, strContract);
   else if (strCuaderno.equals("19")) printPageFind19(response, vars, strKey, strContract);
   else if (strCuaderno.equals("34")) printPageFind34(response, vars, strKey);
   else if (strCuaderno.equals("71")) printPageFind71(response, vars, strKey);
   else 
   ...

[1]Martin Chadderton has written the PL/SQL function called "stragg" http://www.oratechinfo.co.uk/delimited_lists_to_collections.html#custom_aggregation

I modified the separator ',' for ' ' (two blank spaces) as it was needed for this file.

[2]Thanks to "xeraf" from the irc #openbravo for pointing me out this issue

[3]Trick from jemmasta at http://forge.openbravo.com/plugins/espforum/view.php?group_id=100&forumid=549512&topicid=4885111#4885998