How to create an Official Fiscal Report Based on the Tax Report Launcher

Contents 1 About this document 2 Recommended articles 3 Estimated effort 4 Introduction 5 Prerequisites 6 Setting up the environment 6.1 Recommendations 6.2 Installing the dependencies 6.3 Applying the taxes dataset 7 Defining the fiscal report configuration 7.1 A bit of theory 7.1.1 Tax Report window 7.1.2 Tax Rate || Tax Parameter tab 7.2 Creating the fiscal report configuration 7.2.1 Tax Report window 7.2.2 Tax Rate || Tax Parameter tab 8 Creating the module definition 9 Creating the dataset definition 10 Testing the Dataset 11 Implementing the Java code 11.1 Setting up Eclipse 11.2 OBTL_TaxReport_I Interface 12 Getting the report file: Tax Report Launcher window 13 Packaging the module 14 Dummy fiscal report example 14.1 Creating the dummy fiscal report configuration 14.1.1 Tax Report 14.1.2 Tax Rates association 14.2 Implementing the Java code 14.3 Testing the code

About this document

This document is part of the Localization Guide for Openbravo and describes the process of creating a module to generate official fiscal reports ready to be sent to the Tax Authorities.

Although it is written as a howto, this document contains detailed information about the Tax Report Launcher that makes it useful to be consulted as a reference guide.

The Dummy Fiscal Report module, which is used as an example during this howto, is freely available to be installed and can be used as a base for developing fiscal reports.

Recommended articles

Before reading this howto it is important to have a clear understanding about the Modularity Concepts, specially the process of creating and packing modules.

During the development of this module it will be necessary to create a dataset that includes the fiscal report configuration. In case you haven't created yet a dataset, it is recommended to firstly read the How to create a Dataset article to get a general idea before moving forward.

The Tax Report Launcher is the framework used to create our fiscal report modules. It is recommended to read the documentation about it available at the Tax Report Launcher home page.

The logic to create the fiscal report must be writing using Java. It is required to have a good level about this programming language to afford the development of a fiscal report.

Related to Java, it is highly recommended to use the Eclipse IDE while developing this module. Please refer to the How to setup Eclipse IDE article if you haven't done it yet.

The last important aspect involved in the development of a fiscal report is the Data Access Layer, which is used for retrieving data from the database. For getting accurate data to populate the report, you must have enough knowledge to write complex queries based on Hibernate. Please read the Data Access Layer documentation, and specially the How to do a complex query using the DAL-1 and How to do a complex query using the DAL-2 articles and ensure you are able to understand these examples.

From the functional perspective, it is mandatory to know the way Tax Rates work in Openbravo. In fact any fiscal report module must depend on a previously created Taxes module. In case you haven't created this module yet, please read the How to create a Taxes Module first before moving forward.

Estimated effort

Developing a fiscal report is probably the most difficult task involved in a localization project. The persons in charge of the development must have very good development skills as well as functional experience. The experience is an aspect that will definitely affect the development effort.

Take into account that, besides the development time, you must also include a deep testing plan to ensure your report generates accurate data. Remember that the report will be directly submitted to the Tax Authorities, so it's critical for our users to ensure a good quality.

The last aspect that can obviously affect the fiscal report development is the complexity of the report: the number of taxes to include, the structure of the report, etc.

All the previous aspects define the effort required for creating a fiscal report. As rough estimation for an average difficulty report created by an experience developer could be around 15 days of work.

Introduction

Companies are forced to submit a set of official fiscal reports to the Administration on a regular basis. Tax Authorities usually allow companies to submit online their fiscal reports which must observe a concrete structure defined by the Authorities: plain text based on positions, CSV file, XML file, etc.

These kind of reports may:

• summarize the tax payable and receivable in a period of time
• list transactions related to a set of tax rates (for example, real estate operations, imports and exports, etc.)
• specify the withholding applies per business partner
• list the transactions that exceed an amount limit
• etc.

The objective of a fiscal report module is to allow the end-user to get an official fiscal report file generated from the transactions registered in the ERP.

Prerequisites

A taxes module for the country must be ready, and the end-user must only use the tax rates defined in this module while registering their transactions. If there is no tax rates module for your country yet, you must first create it before trying to develop the official fiscal report. Please read the How to create a Taxes Module for more information.

Setting up the environment

Recommendations

During the development of the fiscal report module, we are going to create a dataset that will depend on your taxes module dataset as we will see later on. This dataset will have a complex structure difficult to review, so we must try to simplify as much as possible our environment during the development.

That basically means that it's recommended to start working in a fresh instance with no modules installed (apart from the distributed by Openbravo 3). This will reduce the possibilities of inserting wrong data into the dataset.

Installing the dependencies

In order to make it easier to develop this kind of fiscal report modules, Openbravo provides a module called Tax Report Launcher, which is a framework that includes the following features:

• Provides new windows and tabs that allow the developer to define the tax report structure and to associate tax rates with concrete tax report parameters as we will see later on.
• Contains a set of Java utilities to facilitate the development of fiscal reports.
• Creates a new window for launching any fiscal report based on the Tax Report Launcher.

So the first thing we must do is to install the Tax Report Launcher module, which is distributed under the Openbravo Public License, into our development instance along with the taxes module for your country. Both of them will be mandatory dependencies for your fiscal report module.

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

Applying the taxes dataset

To finish with the environment setup, we will apply our taxes module to any of the clients registered in the instance. For example we can use the F&B Client distributed as sample data, or any client you have already registered in the system.

It is critical for the success of the fiscal report development to ensure this is the first time you have applied the taxes dataset in the instance. This golden rule must always be followed whenever we work with dataset. Please read the UUIDs management chapter of the How to create a Taxes Module for more information about this topic.

If you have already applied the taxes dataset in the past, you must work with the tax rates applied from the dataset for the first time. For example, if you have applied the taxes dataset to the organization A, and after that to the organization B and C, you must only work with the tax rates defined for the organization A. In case you don't remember whether you have applied the taxes dataset before, it's better to work in another fresh Openbravo instance. This will save you lot of time (and headaches) in the future.

Defining the fiscal report configuration

A bit of theory

Tax Report window

The Tax Report Launcher inserts a new window available at the Financial Management || Accounting || Setup || Tax Report application menu path which defines the fiscal report configuration.

Inside the header we set the name (that will be later on displayed at the Tax Report Launcher window), the periodicity for the report (which can be Annually, Monthly or Quarterly basis) and the Java class name that will implement the logic for generating the fiscal report file and which must obviously be inside the fiscal report module.

The Tax Report Group tab is only used to group together similar Tax Report Parameters, which are the real fiscal report protagonists.

A Tax Report Parameter is the representation of something relevant for the report, and must be any of the following types:

• Input: Used to get data required to generate the report that is not currently available in the system. For example: contact phone number, number of contract, file name used for the generated report...
  These input parameters, which must be either Text or Checkbox, are automatically generated on the fly by the Tax Report Launcher window when launching the report as we will see later on.
• Constant: which allows the developer to define constants that will be included in the report file. For example a constant for the report identifier that must include in a concrete position in the file, etc. You can either define the constants as tax report parameters or directly hardcode them into the Java code, being recommend the former option.
• Output: which are the most important type of tax report parameters. Output parameters are associated with tax rat(es) -later on we will see how to do it- and it's the way to know the transactions that must be included in a concrete parameter.
  Let's see an example. Imagine your fiscal report must inform about the sum of the tax base amount for all the import of goods and export of goods in a period of time. In this scenario we should create two output parameters, one for the import of goods and the other one for the export of goods. The former will be associated with all the tax rates that represent import of goods and the latter to the tax rates that represent export of goods.
  When developing the report we will get the list of tax rates associated with each tax report parameter, and we will search for all the transactions in the system (usually only invoices) that have registered any of these tax rates for a period of time. This is the logic we are going to follow to define/develop our fiscal reports.

Apart for the previous fields, the Tax Report Parameter tab includes the Tax key, Tax subkey and Transaction code fields that can optionally be used in case your fiscal report requires any of them. Normally you won't use them, because they are oriented to the Spanish legal requirements for some reports.

Tax Rate || Tax Parameter tab

We have just commented that Output parameters are associated with Tax rates. The way to associate them is through the Tax Parameter tab of the Tax Rate window, which is only available when installing the Tax Report Launcher module.

A tax rate can be associated with one or more tax report parameters, and the same tax report parameter can be associated with one or more tax rates.

This association is the key point to make it possible to develop fiscal reports.

Creating the fiscal report configuration

Once the theory is clear, it's time to create the fiscal report configuration. We will see later on a real example, but these are the list of general considerations to take into account:

Tax Report window

• The record must belong to any legal entity with accounting organization. In case your client does not have any of them, you must first create (and set as ready) one. Please read the Initial organization setup article for more information.
• Remember to define the Java class name inside the java package of your future module.
• Set the right periodicity. In case several periodicities are allowed for the same report, you must create different headers for each periodicity.
• Try to encapsulate tax report parameters inside tax report groups. That makes it easier to understand the purpose of the parameters.
• Study the legal requirements for your official tax report and try to identify the necessary tax report parameters. Specially think about the output parameters that will be later on associated with the tax rates.

Tax Rate || Tax Parameter tab

• Work only with the tax rates defined in the taxes module for your country. Specially, remember to work only with the set of tax rates applied for the first time in the instance.
• Associate each tax rate involved into the report to its correspondent tax report parameter.
• In case you have created several tax reports definitions for different periodicities, remember to associate the right tax report parameters for all the declared tax reports.
• If you want to include a summary level tax rate, you must only create the association for the child tax rates involved into the report (and not the summary level one).

Creating the module definition

Once we have the fiscal report definition ready and all the tax rates involved into the report have been properly associated with the tax report parameters, it's time to create the module definition.

The fiscal report module definition is as any other module that contains a dataset. Here is the list of special considerations for our module:

• You should try to follow the Naming guidelines for modules
• The Java Package Name for the module must be related to the Java Class Name previously defined at the Tax Report window.
• The flag Has reference data must be set because our module will contain a dataset with the previous configuration. Remember to write any useful information inside the Reference Data Description field.
• Inside the Dependency tab, include the mandatory dependency to:
  • The Tax Report Launcher module
  • The Taxes module for your country
• 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/<fiscal report java package> directory create the src folder, where we will store the Java class that implements the logic for generating the report file.

Creating the 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 the fiscal report 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 Organization, which means we allow users to apply the configuration only at Organization level. In fact the end-user must only apply this dataset to the legal entities with accounting organizations for which he wants to get the fiscal report. Please take this into account when writing the user documentation for the module.
• The Export allowed flag must be set.
• Inside the Table tab we must include the following tables:
  • OBTL_Tax_Report, which is the table that stores the Tax Report configuration. Please note the IsBusinessObject flag is set to Yes, which implies all the records related to the tax report header will be also included in the dataset; in our case, all the Tax Report Groups and Tax Report Parameters declared in the report.
  • OBTL_Tax_Parameter, which stores the relations between tax rates and tax report parameters. In this case the IsBusinessObject flag is set to No.
• 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. In our example we filter by the tax reports with a searchkey value like '%Dummy%'. Notice the where clause for the OBTL_Tax_Parameter table also filters by the tax report searchkey value, but in this case we are navigating until finding the taxReport object: taxReportParameter.taxReportGroup.taxReport.searchKey. You can use the same trick for your dataset definition.

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 applying the dataset to another legal entity organization, either in the same client or in another client of the same instance.

If you apply it to an organization that belongs to another client, you must be sure that the taxes module dataset has been applied first (either at client level or to any of the organizations in the parent tree), otherwise it will fail because it won't find the tax rates that have been associated with the tax report parameters, showing an error similar to this one:

If during the development phase you realize that you want to modify (delete or insert) something in the report configuration, you will need to export the dataset again to reflect the new changes. In this scenario you must take into account these two important considerations:

• If you have applied the dataset to two or more organizations, you must be sure that the data that you are going to include into the dataset is not repeated
• Remember that whenever we develop a dataset, we must work only with the data that was applied for the first time.

These two considerations basically imply that we need to pay special attention to the HQL/SQL Where clause used for all the tables included into the dataset. In the screenshot shown in the previous chapter we have also filtered by the client name Report. This is a trick you can use while developing, and it helps you to remember that the dataset must only be exported from the data inside that client. You can use a similar trick for your instance (filter by a concrete organization, client, etc.).

Implementing the Java code

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

Setting up Eclipse

Eclipse is in general a great tool for developing Java code, and it is specially helpful during the development of this module. Let's add now 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.

OBTL_TaxReport_I Interface

Inside the Tax Report window we have previously defined a class name that will be in charge of generating the fiscal report file. This class must implement the org.openbravo.module.taxreportlauncher.erpCommon.ad_reports.OBTL_TaxReport_I Java Interface.

public interface OBTL_TaxReport_I {
  public HashMap<String, Object> generateElectronicFile(String strOrgId, String strReportId,
                                     String strAcctSchemaId, String strYearId, String strPeriodId, Map<String, String> inputParams)
                                     throws OBException, Exception;
}

This interface only defines the public method generateElectronicFile(), which has the following parameters that represent the data introduced by the end-user when launching the report:

• strOrgId: Organization ID to get the report
• strReportId: Tax Report ID we are launching
• strAcctSchemaId: General Ledger configuration ID
• strYearId: Year ID selected when launching the report
• strPeriodId: period ID selected when launching the report. In case of quarterly or anually basis reports, this string contains a comma-separated list of the periods
• inputParams: Map<String, String> containing all the input parameters manually introduced by the user when launching the report. The key String is the input parameter search key defined at the report configuration

This method must return a HashMap<String, Object> with the following keys and values:

• fileName: is a String that contains the file name for the report. It can be generated from the value of an input parameter introduced by the end-user. In case it is not specify, the system will generate an automatic file name.
• file: is a StringBuffer which stores the content of the generated report.
• files: which can store a Set of Files (Set<Files>) that we want to deliver to the end-user. This can be useful, for example in case our fiscal report is divided into several files, or if we want to include a log file along with the fiscal report, etc.

If the HashMap contains a file key, then the Tax Report Launcher framework automatically creates a plain text file based on the content of the StringBuffer. If it doesn't contain the file key, the Tax Report Launcher framework will automatically search for the files key and it will generate a ZIP file that stores the Set<Files>.

Later on we will see a detailed example that will show us how to properly implement this method along with the facilities the Tax Report Launcher provides to the developer.

Getting the report file: Tax Report Launcher window

The last important component of the Tax Report Launcher framework is the window used to generate the fiscal reports. It can be found in the Financial Management || Accounting || Tax Report Launcher application menu path. This is the window the end-user must use to get the fiscal reports.

After selecting the Organization (only legal entities with accounting are displayed), we can select any of its fiscal reports (defined at the Tax Report window). We also select the General Ledger, year and period(s) to generate the report. Take into account that the Period field will be automatically populated with the right data depending on the report periodicity. In the screenshot above, the Dummy Fiscal Report has been declared to be submitted on a quarterly basis, that's why we can only select quarters.

After entering all these primary filters we must press the Input Parameters button which automatically opens a new popup with the input parameters declared for the selected report. Take into account that this popup is generated on the fly by the Tax Report Launcher framework, so the fiscal report developer doesn't need to worry about it.

In our example we have just defined two input parameters: the File Name and the Phone number. Observe that the input parameters are inside field groups that correspond to the groups defined at the Tax Report configuration.

When pressing the Generate Electronic file button, the generateElectronicFile() method of our report Java class will be automatically executed. When the method returns the HashMap, the Tax Report Launcher framework automatically generates the output file and the end-user is prompted to download it to his computer.

As you can see now, the Tax Report Launcher framework is a great tool that creates a generic interface for launching fiscal reports, and allows the developer to focus only in the logic for generating the fiscal report file, making transparent the rest of components related to the User Interface.

Packaging the module

Once the code is ready, and after a deep testing, we must package the module as usual.

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=<fiscal report module java package>

• We can now publish the module in the Central Repository

Dummy fiscal report example

Up to now we should have a clear idea about the general steps to develop a fiscal report based on the Tax Report Launcher. In the following sections we are going to see the concrete steps to develop a fiscal report. You will see real code examples that show the way to code this kind of reports along with some tricks that can be used for this kind of reports.

As an example for this howto, we are going to develop a dummy fiscal report that will include the list of posted sales invoices related to capital goods transactions in a period of time (submitted on a quarterly basis).

To make this example easier to understand, we have developed a real module called Dummy Fiscal Report that can be freely installed as usual from the Module Management window. This module contains everything explained here, so you can study it in depth on your computer, but it can also be used as a base for developing other fiscal reports.

The dummy report is a plain text file with the following structure:

• A header line with the following fields:

Value	Position	Length
Constant=”DUMMYREPORT”	1	11
Report From date (format dd/MM/yyyy)	2	10
Report To date (format dd/MM/yyyy)	3	10
Organization Legal Name (aligned to the right, filled in with blank spaces)	4	100
Organization Tax ID (aligned to right, filled in with 0)	5	10
Contact Phone Number (aligned to the left, filled in with blank spaces)	6	10

• Data lines with the following fields:

Value	Position	Length
Constant=”01_CAPITAL_GOODS_S”. It only appears one time	0	No limit
Invoice number	1	No limit
Invoice date (format dd/MM/yyyy)	2	No limit
Tax Amount	3	No limit
Tax Base Amount	4	No limit
Currency ISO Code	5	No limit

All the fields in a line are separated by a semicolon (;).

Example of a dummy report file:

DUMMYREPORT;01/04/2012;30/06/2012;                                                                                   My Legal Name Org;00HG263901;976001125 
01_CAPITAL_GOODS_S
1000021;10/06/2012;36.72;204.00;USD
1000022;12/06/2012;2.39;13.28;USD
1000025;12/06/2012;-2.24;-12.45;USD
1000033;23/06/2012;-3.67;-20.40;USD

Creating the dummy fiscal report configuration

Tax Report

According to the previous specifications, we can create the following tax report configuration:

• An unique Tax Report header, with a Quarterly basis periodicity and org.openbravo.localizationguide.dummyfiscalreport.DummyFiscalReport as the Java class that will implement the code.
• Several Tax Report Groups are inserted, like Constants (to group together all the constant parameters), Sales Operations to include all the parameters related to sales operations, etc. There is no general rule for defining groups, and it's up to the developer to define a good structure.
  
• Inside each Group we create the Tax Report Parameters:

Group	Seachkey	Name	Type
FILE_NAME	FileName	File name	Input (text)
CONSTANT	REPORT	Report Identifier	Constant, value=”DUMMYREPORT”
CONTACT	PHONE	Phone number	Input (text)
SALES_OPERATIONS	CAPITAL_GOODS	Capital Good Sales	Output

In the example we have created two input parameters that represent data not available in the ERP, like the file name for the report or a contact phone number. There is also a constant parameter that represents a constant string that must be included in the file.

Finally we have an output parameter which represents the capital goods transactions that must be included in the report. In our example, to make it simple, we have just included one output parameter, but in the real life you will probably need more output parameters to represent all different transactions involved into the fiscal report.

Tax Rates association

The final step for the configuration is to associate the list of tax rates with the correct output parameters. In our example, to make it simple, we have just associate the Spanish Entregas Bienes Inversión 18% tax rate (available in the Taxes module for Spain) with our Capital Goods Sales output parameter created before.

However, in the real life a tax report parameter is usually associated with more than one tax rate, an even the same tax rate may also be associated with different tax report parameters usually from different Tax Reports. Take this into account while creating the association for your report.

Implementing the Java code

In our example we have set the org.openbravo.localizationguide.dummyfiscalreport.DummyFiscalReport class as the responsible to generate the fiscal report. So let's first create the org.openbravo.localizationguide.dummyfiscalreport java package inside our module directory and the Java class. Remember that this class must implement the org.openbravo.module.taxreportlauncher.erpCommon.ad_reports.OBTL_TaxReport_I Java Interface.

And here is the content of our DummyFiscalReport class:

package org.openbravo.localizationguide.dummyfiscalreport;
 
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
 
import org.hibernate.criterion.Restrictions;
import org.openbravo.base.exception.OBException;
import org.openbravo.dal.service.OBDao;
import org.openbravo.erpCommon.utility.OBMessageUtils;
import org.openbravo.model.common.enterprise.Organization;
import org.openbravo.model.common.invoice.InvoiceTax;
import org.openbravo.model.financialmgmt.accounting.coa.AcctSchema;
import org.openbravo.model.financialmgmt.calendar.Period;
import org.openbravo.model.financialmgmt.calendar.Year;
import org.openbravo.model.financialmgmt.tax.TaxRate;
import org.openbravo.module.taxreportlauncher.TaxReport;
import org.openbravo.module.taxreportlauncher.TaxReportGroup;
import org.openbravo.module.taxreportlauncher.TaxReportParameter;
import org.openbravo.module.taxreportlauncher.Dao.TaxReportLauncherDao;
import org.openbravo.module.taxreportlauncher.Exception.OBTL_Exception;
import org.openbravo.module.taxreportlauncher.Utility.OBTL_Utility;
import org.openbravo.module.taxreportlauncher.erpCommon.ad_reports.OBTL_TaxReport_I;
 
public class DummyFiscalReport implements OBTL_TaxReport_I {
  private TaxReport taxReport;
  private Map<String, String> constantParameters;
  private Year year;
  private List<Period> periods;
  private AcctSchema acctSchema;
  private Organization org;
 
  private final TaxReportLauncherDao dao = new TaxReportLauncherDao();
  private final DummyFiscalReportDao reportDao = new DummyFiscalReportDao();
 
  private static final DateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");
  private static final String SEPARATOR = ";";
 
  @Override
  public HashMap<String, Object> generateElectronicFile(String strOrgId, String strReportId,
      String strAcctSchemaId, String strYearId, String strPeriodId, Map<String, String> inputParams)
      throws OBException, Exception {
    initializeAttributes(strYearId, strAcctSchemaId, strReportId, strOrgId, strPeriodId);
 
    StringBuffer sb = new StringBuffer();
    sb.append(generateHeader(inputParams));
    sb.append(generateLines("SALES_OPERATIONS"));
 
    // Generate output
    final HashMap<String, Object> returnedMap = new HashMap<String, Object>();
    // File name. Input Parameter with Search Key "FileName"
    returnedMap.put("fileName", inputParams.get("FileName"));
    returnedMap.put("file", sb);
    return returnedMap;
  }
 
  private void initializeAttributes(final String strYearId, final String strAcctSchemaId,
      final String strReportId, final String strOrgId, final String strPeriodIds) {
    year = dao.getYear(strYearId);
    acctSchema = dao.getAcctSchema(strAcctSchemaId);
    org = dao.getOrg(strOrgId);
    taxReport = dao.getTaxReport(strReportId);
    constantParameters = OBTL_Utility.getValueConstantParameters(taxReport);
    periods = dao.getPeriods(strPeriodIds);
  }
 
  /**
   * Generates the header
   */
  private StringBuffer generateHeader(Map<String, String> inputParams) {
    StringBuffer header = new StringBuffer();
    header.append(constantParameters.get("REPORT")).append(SEPARATOR);
    header.append(dateFormat.format(periods.get(0).getStartingDate())).append(SEPARATOR);
    header.append(dateFormat.format(periods.get(periods.size() - 1).getEndingDate())).append(
        SEPARATOR);
    if (org.getSocialName() == null || org.getSocialName().equals("")) {
      throw new OBTL_Exception(OBMessageUtils.messageBD("Error"),
          OBMessageUtils.messageBD("DFRLG_NO_SOCIALNAME"));
    }
    header.append(
        OBTL_Utility.format(org.getSocialName(), 100, null, true, "Organization Social Name"))
        .append(SEPARATOR);
    if (org.getOrganizationInformationList().size() == 0
        || org.getOrganizationInformationList().get(0).getTaxID() == null
        || org.getOrganizationInformationList().get(0).getTaxID().equals("")) {
      throw new OBTL_Exception(OBMessageUtils.messageBD("Error"),
          OBMessageUtils.messageBD("DFRLG_NO_ORG_TAXID"));
    }
    header.append(
        OBTL_Utility.format(org.getOrganizationInformationList().get(0).getTaxID(), 10, '0', true,
            "Organization Tax ID")).append(SEPARATOR);
 
    String phone = OBTL_Utility.getConstantOrInputParameter("PHONE", constantParameters,
        inputParams);
    if (phone == null || "".equals(phone)) {
      throw new OBTL_Exception(OBMessageUtils.messageBD("Error"),
          OBMessageUtils.messageBD("DFRLG_NO_PHONE"));
    } else {
      header.append(OBTL_Utility.format(phone, 10, null, false, "Contact Phone Number"));
    }
    header.append("\n");
    return header;
  }
 
  /**
   * Generates the lines with the tax information
   */
  private StringBuffer generateLines(String groupSearchKey) {
    StringBuffer lines = new StringBuffer();
 
    TaxReportGroup group = (TaxReportGroup) OBDao.getFilteredCriteria(TaxReportGroup.class,
        Restrictions.eq(TaxReportGroup.PROPERTY_SEARCHKEY, groupSearchKey)).uniqueResult();
 
    for (TaxReportParameter taxReportParam : group.getOBTLTaxReportParameterList()) {
      // Only output parameters, which are related to tax rates
      if (taxReportParam.getType().equals("O")) {
        lines.append(taxReportParam.getDescription());
        lines.append("\n");
        List<TaxReportParameter> paramList = new ArrayList<TaxReportParameter>();
        paramList.add(taxReportParam);
        Set<TaxRate> taxRates = dao.getTaxRates(paramList);
 
        for (InvoiceTax invoiceTax : reportDao.getInvoiceTax(org, taxRates, periods, acctSchema)) {
          BigDecimal lineTaxAmount = invoiceTax.getTaxAmount();
          BigDecimal lineTaxableAmount = invoiceTax.getTaxableAmount();
 
          // Credit memo opposite sign
          if (invoiceTax.getInvoice().getDocumentType().isReversal()) {
            lineTaxAmount = lineTaxAmount.negate();
            lineTaxableAmount = lineTaxableAmount.negate();
          }
 
          lines.append(createLine(invoiceTax, lineTaxAmount, lineTaxableAmount));
        }
      }
    }
 
    return lines;
  }
 
  private StringBuffer createLine(InvoiceTax invoiceTax, BigDecimal lineTaxAmount,
      BigDecimal lineTaxableAmount) {
    StringBuffer line = new StringBuffer();
    line.append(invoiceTax.getInvoice().getDocumentNo()).append(SEPARATOR);
    line.append(dateFormat.format(invoiceTax.getInvoice().getInvoiceDate())).append(SEPARATOR);
    line.append(lineTaxAmount).append(SEPARATOR);
    line.append(lineTaxableAmount).append(SEPARATOR);
    line.append(invoiceTax.getInvoice().getCurrency().getISOCode());
    line.append("\n");
    return line;
  }
}

Let's comment now the most important parts of the example:

• The generateElectronicFile method is the starting point for the class and receives all the relevant parameters to create the fiscal report.
• We have defined the initializeAttributes method to get the objects from the IDs received as parameters in the generateElectronicFile method. Observe the usage of the TaxReportLauncherDao instance and the OBTL_Utility class. Both contain many public methods that can be very useful while developing our report.
• We have created the generateHeader and generateLines methods that are in charge of generating the report header and data lines respectively. Both create a StringBuffer and append data to it in a sequential way.
• In case of any error, we just need to launch an OBTL_Exception(String Title, String Message). The system will automatically print the error message in the User Interface.

throw new OBTL_Exception(OBMessageUtils.messageBD("Error"), OBMessageUtils.messageBD("DFRLG_NO_SOCIALNAME"));

• Observe the usage of the OBTL_Utility.format(String str, int length, Character fillChar, boolean toRight, String paramName) method that adapts the output format of the str String. We can define the final length of the output string, the character used to fill in case the original str string is shorter than the final length, the way to align the output string and a paramName string that will be displayed to the user in case something went wrong during the format process.
• All the input parameters can be transformed by the end-user to constant parameters (changing its definition at the Tax Report window). This is very useful when we are launching the report several times and we don't want to force the user to enter the same data every time. To support it we use the OBTL_Utility.getConstantOrInputParameter() method. Example:

String phone = OBTL_Utility.getConstantOrInputParameter("PHONE", constantParameters, inputParams);

• Back to the generateElectronicFile method, once we have the StringBuffer ready, we just need to generate the HashMap to be returned. In the example we want to return a plain text file, so we use the file key:

final HashMap<String, Object> returnedMap = new HashMap<String, Object>();
// File name. Input Parameter with Search Key "FileName"
returnedMap.put("fileName", inputParams.get("FileName"));
returnedMap.put("file", sb);
return returnedMap;

The last part to comment is the way we have used to take all the transactions to be included in the report. Let's see it now:

• The generateLines() method iterates over all the output parameters in a Tax Report Group. For each output parameter, it takes the associated tax rates using the getTaxRates() method of the TaxReportLauncherDao class:

Set<TaxRate> taxRates = dao.getTaxRates(paramList);

• Once we have the set of tax rates related to the tax parameter, we just need to take the transactions that use any of these tax rates. This is obviously done querying the database through the Data Access Layer.
  The objective of the Dummy Fiscal Report is to list all the posted invoices that have used the Spanish Entregas Bienes Inversión 18% tax rate. So we need to find all the invoices registered in the system with this tax rate. When working with invoices, it is very useful to take the information from the Sales/Purchase Invoice || Tax tab that lists: the tax rates, tax amount and taxable amount of all the tax rates involved in the invoice.
  
• Going back to the code you can observe that we have created the DummyFiscalReportDao class that contains the getInvoiceTax(), which returns a list of InvoiceTax object (related to the data stored in the Tax tab of the Purchase/Sales Invoice), that we iterate to fill in the fiscal report data:

for (InvoiceTax invoiceTax : reportDao.getInvoiceTax(org, taxRates, periods, acctSchema)) {}

• Let's see now the code of the DummyFiscalReportDao class. The getInvoiceTax() method receives as parameters the organization, tax rates, periods and general ledger configuration, which are used to filter the right InvoiceTax records.

package org.openbravo.localizationguide.dummyfiscalreport;
 
import java.util.Collection;
import java.util.List;
 
import org.apache.commons.lang.time.DateUtils;
import org.openbravo.dal.core.OBContext;
import org.openbravo.dal.security.OrganizationStructureProvider;
import org.openbravo.dal.service.OBDal;
import org.openbravo.dal.service.OBQuery;
import org.openbravo.model.common.enterprise.Organization;
import org.openbravo.model.common.invoice.Invoice;
import org.openbravo.model.common.invoice.InvoiceTax;
import org.openbravo.model.financialmgmt.accounting.AccountingFact;
import org.openbravo.model.financialmgmt.accounting.coa.AcctSchema;
import org.openbravo.model.financialmgmt.calendar.Period;
import org.openbravo.model.financialmgmt.tax.TaxRate;
 
public class DummyFiscalReportDao {
 
  /**
   * Returns the list of Invoice Tax for the given Organization (and its children), list of tax
   * rates, periods and general ledger configuration
   * 
   */
  public List<InvoiceTax> getInvoiceTax(Organization org, Collection<TaxRate> taxRates,
      List<Period> periods, AcctSchema acctSchema) {
    try {
      OBContext.setAdminMode();
      final StringBuffer whereClause = new StringBuffer();
 
      whereClause.append(" as it  ");
      whereClause.append(" inner join fetch it." + InvoiceTax.PROPERTY_INVOICE + " as i ");
      whereClause.append(" where exists (select 1 from FinancialMgmtAccountingFact as fa ");
      whereClause.append("                where i.id = fa." + AccountingFact.PROPERTY_RECORDID);
      whereClause.append("                and fa." + AccountingFact.PROPERTY_ACCOUNTINGSCHEMA
          + "= :acctSchema");
      whereClause.append("                and fa." + AccountingFact.PROPERTY_TABLE
          + ".id = :invoiceTableId ");
      whereClause.append("               )");
 
      whereClause.append(" and i." + Invoice.PROPERTY_POSTED + "='Y' ");
 
      whereClause.append(" and i." + Invoice.PROPERTY_ACCOUNTINGDATE + ">= :fromDate ");
      whereClause.append(" and i." + Invoice.PROPERTY_ACCOUNTINGDATE + "< :toDate ");
 
      whereClause.append(" and i." + Invoice.PROPERTY_ORGANIZATION + ".id in (:orgs) ");
 
      whereClause.append(" and it." + InvoiceTax.PROPERTY_TAX + " in (:taxes) ");
 
      final OBQuery<InvoiceTax> obQuery = OBDal.getInstance().createQuery(InvoiceTax.class,
          whereClause.toString());
      obQuery.setNamedParameter("acctSchema", acctSchema);
      obQuery.setNamedParameter("invoiceTableId", "318");
      obQuery.setNamedParameter("fromDate", periods.get(0).getStartingDate());
      obQuery.setNamedParameter("toDate",
          DateUtils.addDays(periods.get(periods.size() - 1).getEndingDate(), 1));
      OrganizationStructureProvider osp = OBContext.getOBContext()
          .getOrganizationStructureProvider(org.getClient().getId());
      obQuery.setNamedParameter("orgs", osp.getChildTree(org.getId(), true));
      obQuery.setNamedParameter("taxes", taxRates);
      obQuery.setFilterOnActive(false);
 
      return obQuery.list();
    } finally {
      OBContext.restorePreviousMode();
    }
  }
}

• As you can see, we get all the InvoiceTax records that have been posted to the selected General Ledger, between the first period starting date and the last period ending date, for the selected organization and its child tree and which contain any of the tax rates passed as parameter. This is done through a Hibernate HQL Query using the Data Access Layer.

We have seen all the code required for generating our Dummy Fiscal Report. It's highly recommended to install this module in your instance and study it deeply to fully understand all the code.

Testing the code

To test our code we just need to post several sales invoices that include the tax rates involved into our fiscal report. In the dummy fiscal report example we have only associated the Spanish Entregas Bienes Inversión 18% tax rate.

If you haven't done it yet, install the Dummy Fiscal Report module, apply the Spanish Taxes dataset to any client and the dummy fiscal report dataset to a legal entity with accounting organization. You can for example use the F&B sample client distributed with Openbravo.

After we have complete these prerequisites, we just need to go to the Sales Invoice window and create and post several invoices for the legal entity organization we have previously applied the dummy fiscal report dataset. Remember to include the Spanish Entregas Bienes Inversión 18% tax rate in the invoice lines.

Now go to the Tax Report Launcher window, and select the organization, the dummy fiscal report, general ledger and period. Ensure the selected period is the one used before for creating the sales invoices.

Press the Input Parameters button and enter any string in the next popup. If everything goes well, the system will return us the dummy fiscal report that includes the previously created sales invoices, otherwise, in case of any problem, the system will inform the user about the error.

If we open the generated file with any text editor, we will see that the content is the expected one. Example:

DUMMYREPORT;01/04/2012;30/06/2012;                                                                                   My Legal Name Org;00HG263901;976001125 
01_CAPITAL_GOODS_S
1000021;10/06/2012;36.72;204.00;USD
1000022;12/06/2012;2.39;13.28;USD
1000025;12/06/2012;-2.24;-12.45;USD
1000033;23/06/2012;-3.67;-20.40;USD

Category: Localization Guide