Toolbox

This document is part of the Localization Guide for Openbravo and describes the process of creating a module to generate remittance files ready to be sent to banks.

Estimated effort

A great percentage of the time invested in this module will be spent in developing the Java code for generating the remittance file. The effort directly depends on the complexity of the remittance file and the general experience developing in Java. A rough estimation could be less than 6 days for an average Java developer with the necessary experience in Openbravo.

Introduction

A remittance is a group of payments which can be remitted to the bank. The bank will then manage either the collection of the money from the customers or the payment to the vendors/suppliers. The list of payments the bank must manage is usually included into a plain text file with an official structure defined by the bank.

Openbravo provides a module called Remittances infrastructure, distributed under the Openbravo Public License, that, apart from including a set of windows and process for working with remittances, it also includes a framework that helps in the creation of the remittance files with the list of payments.

This module supports several remittance types:

"Remittance for Discount" - it is possible to remit "Payment In" for which the bank will manage its collection before its due date.
"Remittance Not for Discount" - it is possible to remit "Payment In/Out" for which the bank will manage its collection/payment on its due date.
"Single payment in remittances" - It is possible to remit several sales invoices/order to the bank in a single payment.
"Single payment out remittances" - It is possible to remit several purchase invoices/order to the bank in a single payment.

The objective of our module is to implement the logic for creating a remittance file which fulfills the specifications accepted by a concrete bank using the Openbravo's Remittances infrastructure.

Setting up the environment

Installing the dependencies

Our remittance file module must mandatory depend on the the Remittances infrastructure module, so the first step we must complete is to install it. This module is distributed under the Openbravo Public License, and can be installed with no additional cost in any Openbravo instance using the Module Management window.

As usual, after a successful installation we must rebuild the system and restart our tomcat instance.

Creating the module definition

Each time we plan to develop a new module, the first step must always be to create the module's definition and to register it in the Central Repository.

These are the special considerations for our module:

You should try to follow the Naming guidelines for modules
The flag Has reference data must be set because our module will contain a dataset with the required configuration. Remember to write any useful information inside the Reference Data Description field.
Inside the Dependency tab, include the mandatory dependency to the Remittances infrastructure module.
Include a DB Prefix for your module in case it's necessary. For example, your module will probably define new messages that will require a DB Prefix.
If your module has UI elements that can be translated (like messages), set the Translation Required checkbox and specify your Module Language.
Remember to register the module in case you want to publish it in the Forge

Now you should export the database to generate the file structure inside the module folder

ant export.database

Finally, inside the modules/<remittance file java package> directory create the src folder, where we will store the Java class that implements the logic for generating the remittance file.

Setting up Eclipse

In case you are using Eclipse, you must now add the modules' src directory in the Openbravo project Build path. Right click on the openbravo project and select Build Path > Configure Build Path

Now, inside the Source tab, press Add Folder... button and select your module src directory.

In case the src directory is not shown, you may first need to refresh the openbravo project in Eclipse.

Creating new client

Our remittance file module will have a dataset with the required configuration for creating the file.

When creating a dataset, we need to be sure only the required data is included into the dataset. To make the dataset development easier, it is highly recommended to create a new empty client in which we only define the configuration for our remittance file which will later on be included into the dataset. With this simple trick we reduce the risk of inserting wrong data in the dataset.

We can create a new client at the Initial Client Setup window when logged as the System Administrator role. In the example, the new client is called Remittance to make its purpose clear. In the Reference data section, we must include the datasets that belong to the following modules:

Advanced Payables and Receivables.
Remittances Infrastructure.

Defining the dataset

Required Configuration

Inside our recently created Remittance client, we must enter the required configuration that will be later on include into the dataset.

The configuration only requires:

A Payment Method associated with the In remittance Execution Process (defined on the Remittances infrastructure module). The end-user will include this payment method inside the desired Financial Account.
During the Payment Method configuration, it is important to define whether payment IN and/or payment OUT is allowed. This depends on the concrete remittance file format you are developing (some of them allow to collect and to pay and other exclusively to collect or to pay).
The Execution Type must be set to Automatic and the In Remittance Execution Process must be selected. It is recommended to set the Deferred flag. Remember that this payment method definition is used as a template that the end-user can change when including it into the Financial Account.

Please read the Payment Methods configuration for remittances chapter for further information.
Remittance Type, which is where the behaviour of the remittance is configured. The previous Payment Method must be linked to a remittance type and the Java Class Name (which must be inside the module Java package) that will implement the logic for creating the remittance file.
The Parameters tab allows to enter additional parameters needed for creating the remittance file, like for example the bank Contract number.

Please read the Remittance Type configuration for remittances chapter for further information.

Dataset definition

Dataset definition is a key step in this process. A wrong dataset definition can waste all our previous work, so it is important to follow all these considerations:

The dataset must belong to your remittance file module
Try to avoid strange characters in the dataset name. This string is used for generating the XML file name that stores the dataset.
The Data Access Level must be set to Client/Organization, which means we allow users to apply the configuration either at Client level (Organization *) or at Organization level.
The Export allowed flag must be set.
Inside the Table tab we must include the following tables:
- FIN_PaymentMethod, which stores the Payment Methods
- FIN_Remittance_Type, which is associated with the Remittance Type window
- FIN_Remittance_Type_Param, which stores the Parameters of the Remittance Type. Notice the Include All Columns flag is set to No, because we want to exclude the Value column from the Parameters. These values are manually set by the end-user and should not be overridden when applying again a new version of the dataset.
  Inside the Column tab, we must only insert the Name and REM_Remittance_Type_ID columns.
The HQL/SQL Where clause is a very important field, because it allows us to filter the records we want to include in the dataset. If you remember, at the beginning of this document we proposed to create a new empty client called Remittance in which define our configuration. In the example above we have used the client.name='Remittance' clause to get only the records that belong to this client.
To be sure we don't include extra records, we have also restricted the where clause to only get records which contain Dummy on its name. This is a trick we can extrapolate to other developments.

The dataset definition is ready, so we just need to export it to a file pressing the Export Reference Data button. This process queries the previous tables and gets all the records that fulfill the HQL/SQL Where clause, generating a XML file inside the module's referencedata/standard directory. As a fast check, you can open this file using any plain text editor and you can verify that it contains several lines.

In case the file is empty, you should double check the dataset definition, specially the HQL/SQL Where clause used for each table.

Testing the Dataset

The real test to ensure the taxes dataset is OK can be done inside our development instance. The test consists on creating a new client running the Initial Client Setup as we saw in the Creating new client section, but in this time, apart from selecting the Advanced Payables and Receivables and the Remittance Infrastructure datasets, we must also select our new dummy remittance file dataset.

If the data inside the dataset are consistent, the Initial Client Setup Process should be completed successfully, otherwise it will fail giving a description about the error.

After a successful Initial Client Setup, we just need to login into the new client, go to the Payment Method and Remittance Type windows and check no records are missed for these windows and their tabs.

Implementing the Java code

Up to now we have just created the module definition and the required configuration that is included in a dataset. It is time now to develop the logic for creating the remittance file.

Remittance file example

The dummy remittance file for this howto will be a plain text file divided in positions with the following structure:

A header with information about the organization that is submitting the remittance:

Value	Start Position	End Position
Constant=”Dummy Bank”	0	20
Organization tax id	21	30
Contract Parameter	31	40
Date (format dd-MM-yyyy)	41	51

The lines that represent each payment to include in the remittance:

Value	Start Position	End Position
Business Partner tax id	0	10
Amount (two decimals, without comma separator). Example: 123, 45 is “12345”	11	20
Due Date (format dd-MM-yyyy)	21	31

Example:

Dummy Bank 123456789Z000000334601-06-2012
987654321Y0000000989515-05-2012
A38335432X0000001106516-05-2012
B44335444Z0000002309820-05-2012

Creating the Java Package

Inside the Required Configuration chapter, we set org.openbravo.localizationguide.remittancefile.DummyRemittance as the Java Class Name that will implement the logic for generating the remittance file.

Let's first create the org.openbravo.localizationguide.remittancefile package inside our module src directory. If you are using Eclipse you can easily create (Ctrl-N) the new package inside the modules/<your module>/src folder

Creating the Java Class

Inside the recently created package, we must now create the Java Class that will be in charge of generating the remittance file. This class, that in our example is called DummyRemittance, must extend from the org.openbravo.module.remittance.utility.REM_RemittanceCreateFile class:

Extending from the REM_RemittanceCreateFile class implies we must override the generate(Remittance remittance) method.

package org.openbravo.localizationguide.remittancefile;
 
import org.openbravo.module.remittance.Remittance;
import org.openbravo.module.remittance.utility.REM_RemittanceCreateFile;
 
public class DummyRemittance extends REM_RemittanceCreateFile {
  @Override
  public StringBuffer generate(Remittance _remittance) {
    
  }
}

This method receives as parameter the Remittance for which we want to generate its file, and must return a StringBuffer that the system will automatically convert into a file. The end-user will automatically download this file from the user interface after pressing the Create Remittance File from the Remittance window.

As you can see the environment for developing the remittance file is quite simple and gives total freedom to developer to implement the logic.

Let's see how the dummy implementation looks like. Take into account that, for simplicity, we have reduced the code of this example. In a real development we should be more precise while developing.

public class DummyRemittance extends REM_RemittanceCreateFile {
  private static final DateFormat dateFormat = new SimpleDateFormat("dd-MM-yyyy");
  private static final String DUMMY_BANK = "Dummy Bank";
 
  @Override
  public StringBuffer generate(Remittance remittance) {
    // Header values
    String orgTaxId = remittance.getOrganization().getOrganizationInformationList().get(0)
        .getTaxID();
    if (orgTaxId == null) {
      setMyError("RFDB_NO_ORG_TAXID");
      return null;
    }
    
    String strContract = "";
    for (RemittanceTypeParameter parameter : remittance.getRemittanceType()
        .getREMRemittanceTypeParamList()) {
	// Get the CONTRACT parameter defined in the Remittance Type
      if ("CONTRACT".equalsIgnoreCase(parameter.getName())) {
        strContract = parameter.getValue();
      }
    }
    if (strContract == null || strContract.equals("")) {
      setMyError("RFDB_NO_CONTRACT");
      return null;
    }
 
    String today = dateFormat.format(new Date());
 
    // Insert header values
    StringBuffer sb = new StringBuffer();
    sb.append(rPad(DUMMY_BANK, ' ', 21));
    sb.append(rPad(orgTaxId, ' ', 10));
    sb.append(lPad(strContract, '0', 10));
    sb.append(today);
    sb.append("\n");
 
    // Insert line values
    // Instruction Lines
    for (Instruction line : remittance.getREMInstructionList()) {
      // Business Partner is mandatory
      if (line.getBusinessPartner() == null) {
        String trlBpMandatory = FIN_Utility.messageBD("REM_BusinessPartner_Mandatory");
        setMyError(String.format(trlBpMandatory, line.getLineNo().toString()));
        return null;
      }
      String bpTaxId = rPad(line.getBusinessPartner().getTaxID(), ' ', 11);
      String amount = lPad(line.getAmount().multiply(new BigDecimal("100")).toBigInteger()
          .toString(), '0', 10);
      String dueDate = dateFormat.format(line.getDueDate());
 
      sb.append(bpTaxId);
      sb.append(amount);
      sb.append(dueDate);
      sb.append("\n");
    }
 
    return sb;
  }
}

Let's comment the interesting parts of the example.

The Remittance object collects all the information from the Remittance header, like the Remittance Type, organization, document No., etc.

In case our Remittance type has parameters, we can easily get them with:

for (RemittanceTypeParameter parameter : remittance.getRemittanceType().getREMRemittanceTypeParamList()) {}

In case of errors, we can use the setMyError() method defined at the REM_RemittanceCreateFile, passing as parameter the search key of a message previously defined at the Application Dictionary
The payments we must include into the remittance file corresponds to the records inside the Bank Instructions tab from the Remittance window. We can iterate over them with:
```
for (Instruction line : remittance.getREMInstructionList()) {}
```
The REM_RemittanceCreateFile class also provides interesting methods for managing strings before appending them to the StringBuffer, like the rPad() and lPad() to pad the right and left side respectively of a string a concrete length using any character.

Remember to compile and to restart tomcat after finishing the code development.

Testing the code

Now we need to test our configuration and code from the user interface. Inside our development instance we can apply our dataset to the * Organization of the F&B sampledata client which has several business partners and financial accounts already created. Once the dataset has been applied, remember to include our Payment Method inside any Financial Account.

At the Remittance window we can create a new record for our Remittance Type and include several lines by pressing the Select Payments or Select Orders or Invoices buttons and Process the remittance.

The Create Remittance File button should now be displayed. When pressing it, our code will be executed and, if everything is OK, we will get the remittance file.

Packaging the Module

To generate the obx file we should follow the standard steps from the command line:

Export the database:

ant export.database

Package the module:

ant package.module -Dmodule=<remittance file module java package>

We can now publish the module in the Central Repository

Retrieved from "http://wiki.openbravo.com/wiki/How_to_create_a_Remittance_File_Module"

This page has been accessed 21,688 times. This page was last modified on 18 June 2012, at 09:50. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.

Category: Localization Guide