View source | Discuss this page | Page history | Printable version   
Toolbox
Main Page
Upload file
What links here
Recent changes
Help

PDF Books
Add page
Show collection (0 pages)
Collections help

Search

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

Contents

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:

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:

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.

LocGuide TRL 10.png

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.

LocGuide TRL 20.png

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.

LocGuide TRL 30.png

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.

LocGuide TRL 40.png

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

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.

LocGuide TRL 50.png

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

Tax Rate || Tax Parameter tab

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:

LocGuide TRL 60.png

LocGuide TRL 70.png

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.

LocGuide TRL 80.png

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:

LocGuide TRL 90.png

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:

LocGuide TRL 100.png


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:

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

LocGuide Remittance 50.png

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

LocGuide TRL 120.png

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:

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

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.

LocGuideTRL.png

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.

LocGuide TRL 126.png

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:

ant export.database
ant package.module -Dmodule=<fiscal report module java package>

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.

LocGuide TRL 130.png


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

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


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:

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.

LocGuide TRL 50.png

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.

LocGuide TRL 200.png

LocGuide TRL 210.png


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:

throw new OBTL_Exception(OBMessageUtils.messageBD("Error"), OBMessageUtils.messageBD("DFRLG_NO_SOCIALNAME"));
String phone = OBTL_Utility.getConstantOrInputParameter("PHONE", constantParameters, inputParams);
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:

Set<TaxRate> taxRates = dao.getTaxRates(paramList);
for (InvoiceTax invoiceTax : reportDao.getInvoiceTax(org, taxRates, periods, acctSchema)) {}
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();
    }
  }
}

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.

LocGuide TRL 230.png


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.

LocGuide TRL 125.png

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.

LocGuide TRL 240.png


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

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

This page has been accessed 14,114 times. This page was last modified on 16 November 2012, at 10:14. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.