Spainaeatmodelo340/Technical Documentation
Contents |
MODELO 340: TECHNICAL SPECIFICATION
Purpose of the project
The purpose of this project is to create a new module for generating the Spanish official 340 VAT statement (Modelo 340) valid file. This module will be part of the Spanish Professional Localization Pack.
340 VAT statement is aimed to include every invoice contained in the "Invoice Register Books"
340 VAT statement must be sent to the Tax Authorities either monthly or quarterly within the 20 days of the next Month/Quarter, respectively.
This report must be mandatory sent through the INTERNET – on line submission of a valid 340 file.
On line submission implies that the Spanish companies must have a Tax ID as well as a user Certificate X.509.V3 issued by FNMT or any other valid Certificate according to tax authorities requirements.
Module definition
- Name: Tax Report Modelo 340 (Spain)
- Java Package: org.openbravo.module.aeat340.es
- Version: 1.0.0
- Description: Declaración informativa de operaciones en libros de registro
- Translation Required: Yes
- Module Language: Spanish (Spain)
- License: Openbravo Commercial License
- IsCommercial: Yes
- Has reference data: Yes
- Reference Data Description: Configuración lanzador del Modelo 340
- License text: Copyright (C) 2011 Openbravo S.L.U. Licensed under the Openbravo Commercial License version 1.0. You may obtain a copy of the License at http://www.openbravo.com/legal/obcl.html or in the legal folder of this module distribution.
- Author: Openbravo S.L.U.
- URL: http://forge.openbravo.com/projects/spainaeatmodelo340
- DB Prefix: AEAT340
- Module Dependencies:
- Core from 2.50MP33 to 3.0MP2
- European Union Countries 1.0.2
- Invoices Register Book 1.2.7
- Tax Report Launcher 1.0.12
Introduction
The key point of this development is to adapt the already created Invoices Register Book in the system to the 340 file structure. There is no need to calculate data or amounts, all the information will be available in the Invoices Register Book lines. That's why this module depends on the Invoice Register Book one.
The 340 report is based on a tax configuration, exactly the same as other reports available for Openbravo, like 303, 347 or 349. However, the 340 report is clearly more related to the Invoices Register Book lines than with other transactions in the system. That's why it doesn't make sense to use the Tax Report launcher infrastructure for this report. So, unlike previous reports available in Openbravo, the 340 won't provide a tax report configuration and won't use the Tax Report Launcher window.
The 340 report is a plain text file, with a structure quite similar to any of the reports based on the Tax Report Launcher. From a development perspective, we can reuse a lot of the code available in the Tax Report Launcher, specially the one for introducing data inside the file in the right possition. That's why the Tax Report launcher module is a dependency for the 340, but not from a user perspective but from a developer perspective.
A 340 report for Openbravo consists of a set of lines:
- Line type 1. Mandatory. It provides general information about the declaration, like the Organization's name, period, user contact, etc.
- Line type 2: Issued Register Book. Not mandatory. Each type 2 line corresponds to an Issued Register Book line.
- Line type 2: Received Register Book. Not mandatory. Each type 2 line corresponds to an Received Register Book line.
At least one Invoices Register Book (issued or Received) needs to be included into the report. This requirement makes it impossible to add button inside the Invoices Register Book window for generating the file for this book. Instead of that we will need to create a new window where the user can select both register books for a period of time. We will see this window structure later on.
Changes outside of the module
The 340 developement requires several changes to be done outside of the module.
Core
In general, organizations need to have an user contact. In particular, 340 report needs to get the organization's user contact to introduce it into the report.
Currently there is no such field into the Openbravo's core, so we need to include it for this development.
The field will be available inside the Organization | Information tab, and it will be not mandatory to avoid issues in old instances.
So, a new column called AD_USER_ID will be added to the AD_ORGINFO table, having a foreign key to the AD_USER table.
A new feature request have been created for managing this development:
- https://issues.openbravo.com/view.php?id=18092 for Openbravo 3
- https://issues.openbravo.com/view.php?id=18093 for Openbravo 2.50
Invoices Register Book
For setting a book as final, the Invoice Register Book module currently forces the user to close the period. This limitation is a great impediment for the 340 user, and it is not an essential feature for the Invoice Register Book, so we have decided to remove this check.
A new feature request have been created for managing this development:
Configuración Impuestos de Libro de Facturas 2011
This module provides a dataset that automatically creates the proper configuration for both the received and issued invoices register book.
Some new taxes need to be added to the dataset:
- Adquisiciones B. Inversión 18%
- Adquisiciones Intracomunitarias B. Inversión 18%
- Importaciones Bienes Inversión 18%
- Importaciones Bienes Inversión 18%=0%
A new feature request have been created for managing this development:
Database & Application Dictionary
New Tables
AEAT340_LAUNCHREPORT
This table is related to the window for launching the 340 report. It stores all the necessary data for generating the 340 file.
| Column Name | Data Type | Nullable | Data Default |
| AEAT340_LAUNCHREPORT_ID | VARCHAR2(32 BYTE) | No | |
| AD_CLIENT_ID | VARCHAR2(32 BYTE) | No | |
| AD_ORG_ID | VARCHAR2(32 BYTE) | No | |
| ISACTIVE | CHAR(1 BYTE) | No | 'Y' |
| CREATED | DATE | No | SYSDATE |
| CREATEDBY | VARCHAR2(32 BYTE) | No | |
| UPDATED | DATE | No | SYSDATE |
| UPDATEDBY | VARCHAR2(32 BYTE) | No | |
| OBIRB_ISSUED_INVBOOK_ID | VARCHAR2(32 BYTE) | Yes | |
| OBIRB_RECEIVED_INVBOOK_ID | VARCHAR2(32 BYTE) | Yes | |
| C_PERIOD_FROM_ID | VARCHAR2(32 BYTE) | Yes | |
| C_PERIOD_TO_ID | VARCHAR2(32 BYTE) | Yes | |
| ELECTRONIC_CODE | NVARCHAR2(16 CHAR) | Yes | |
| ISSUPPLEMENTARY | CHAR(1 BYTE) | No | 'N' |
| SUPPLEMENTARYNO | NVARCHAR2(13 CHAR) | Yes | |
| PROCESSED | CHAR(1 BYTE) | No | 'N' |
| Constraint Name | Constraint Type | Search Condition | Referenced Table | Reference Constraint Name |
| AEAT340_LAUNCH_AD_CLIENT | Foreign_Key | AD_CLIENT | AD_CLIENT_KEY |
| AEAT340_LAUNCH_AD_ORG | Foreign_Key | AD_ORG | AD_ORG_KEY |
| AEAT340_LAUNCH_C_PERIOD_FROM | Foreign_Key | C_PERIOD | C_PERIOD_KEY |
| AEAT340_LAUNCH_C_PERIOD_TO | Foreign_Key | C_PERIOD | C_PERIOD_KEY |
| AEAT340_LAUNCH_ISACTIVE_CHK | Check | ISACTIVE IN ('Y', 'N') | |
| AEAT340_LAUNCH_ISSUPPL_CHK | Check | ISSUPPLEMENTARY IN ('Y', 'N') | |
| AEAT340_LAUNCH_OBIRB_INVBOOK_E | Foreign_Key | OBIRB_INVBOOK | OBIRB_INVBOOK_KEY |
| AEAT340_LAUNCH_OBIRB_INVBOOK_R | Foreign_Key | OBIRB_INVBOOK | OBIRB_INVBOOK_KEY |
| AEAT340_LAUNCH_PROCESSED_CHK | Check | PROCESSED IN ('Y', 'N') | |
| AEAT340_LAUNCHREPORT_KEY | Primary_Key | ||
Lanzar Modelo 340 window
For generating the 340 file, a WAD generated window, based on the AEAT340_LAUNCHREPORT table, will be used. The tab has been defined as a Single record tab (i.e. Edit only, no insert nor delete). This way we avoid the need of creating a manual window for launching the report, which requires manual maintenance.
On the other hand, this decission forces us to provide a dataset for inserting the unique record available for the client. The dataset will just contain an empty record for the AEAT340_LAUNCHREPORT table. The dataset is defined at System/Client level, so it's impossible to duplicate it once applied for any organization.
The user needs to apply this dataset before beeing able to launch the 340 report.
This window implements some checks that makes easier for the user to not make mistakes. This checks are developed using validation rules directly applied to the window's fields. This solution is easier and faster than using callouts.
The validation rules implemented are:
| Field | Validation Rule |
| Organization | Gets all the organizations that have one or more final invoices register book available |
| Period From | Gets all the “periods from” for the selected organization that have one or more final invoices register book available |
| Period To | Gets all the “periods to” for the selected organization that have one or more final invoices register book available |
| Issued Invoices Register Book | Gets all the final issued register book available for the selected organization and period from/to |
| Received Invoices Register Book | Gets all the final received register book available for the selected organization and period from/to |
Java code structure
When pressing the “Lanzar Modelo 340” button, the GenerateAEAT340Report servlet is called. It delegates all the logic for generating the 340 contents to the AEAT340Report class, which defines the structure of the 340 file. Its constructor receives an AEAT340LaunchReport object (or ID) that corresponds to a record in the AEAT340_LAUNCHREPORT table, which has all the information needed for generating the 340 content (organization, issued/received books, period from/to, etc.).
The AEAT340Report class is the most important one, and it's in charge of generating the valid 340 structure. As it is shown in the Class diagram, the AEAT340Report class depends on other classes like:
- AEAT340Report, which defines the structure of the strings allowed in the 340 declaration
- NumericAmount340, which defines the format of the amounts in the 340 declaration
- NumericTaxRate340, extending NumericAmount340, which defines the format of the tax rates amounts in the 340 declaration.
Each time the AEAT340Report class needs to enter a String, Amount or Tax Rate amount, creates an object of the suitable class.
The AEAT340ReportDao isolates the methods to access to the data stored in the database using DAL (Hibernate).
When the AEAT340Report class has created the 340 content, the GenerateAEAT340Report calls the AEAT340Report.getReportData() method that returns a StringBuffer with the 340 content ready to be included into a file. The servlet generates the file and returns it to the user.