Spainaeatmodelo349/Technical Documentation
Contents
|
Introduction
Present document explains the development required to build a module on top of Openbravo ERP, that generates Spanish 349 summary statement of Intra Community transactions as a valid file according to the Public Treasury requirements. The 349 tax report is a VAT statement which lists all the transactions related to movements of goods within the European Union, either sales of goods from Spain to other EU country or purchases of goods from any EU country to Spain.
This module will be part of the Openbravo Professional Spanish Localization Pack version 1.0.
Content of the module
The “Modelo 349” module will contain all the application dictionary elements and Java classes needed for Openbravo ERP to generate a file compatible with the AEAT (Spanish tax authorities) specifications.
It will also contain all the configuration needed for the report to run and to be executed using the Tax Report Launcher module. Tax Report Launcher is guided through tax rates: several tax rates will be set with this module by default. Those tax rates will be used in the products included in the invoices. When those invoices are posted, the information about the tax rate associated is also stored. Afterwards, when generating the report, accounting information is gathered to calculate the values to include in the 349 report.
Assumptions
This module will use the Tax Report Launcher feature developed as an extended module for Openbravo ERP. 349 report will not be installed if the Tax Report Launcher module is not previously installed.
After installing the module, user will need to configure the ERP to use the provided tax rates while operating with the system. According to each company's activity, this configuration may differ from one to another. 349 Functional Specification explains how to setup and how to use the provided configuration set. It is also important to note that each tax rate must be linked to the correspondent 349 tax key/sub-key so the required transactions associated to those tax rates are collected by the tax report launcher as appropriate.
Report will be launched just for accounting schemes which currency is Euro.
Module definition
Name: Modelo 349
Type: Module
Java Package: org.openbravo.module.aeat349.es
DB Prefix: AEAT349
Reference Data: Yes
Module Language: Spanish (Spain)
Translation Required: Yes
License Type: Openbravo Commercial License 1.0
Dependencies: Tax Report Launcher module 0.5.0, Spain Taxes 1.0.7
Dataset:
- 349_TaxKey, including the tax keys used by this report ('A' and 'E')
- 349_TaxParameters, including the tax parameters used by this report
- 349_ReportConfig, configuration of the report
A file will be added in the root folder of the module called OBC_Licensing.txt, with the licensing.
Header of all files included in the module will be:
************************************************************************************ * Copyright (C) 2009 Openbravo S.L. * 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 ************************************************************************************
Tax rates, Tax keys and Tax parameters
This module/tax statement has to gather a set of entries from accounting (not from management), calculate some amounts and number of business partners, and generate a plain text file suitable to be uploaded to AEAT web, as an official statement, according to the file structure published in AEAT web, and Official State Gazette (BOE).
For obtaining this values, there are some tax rates which need to be setup, as well as some tax report parameters:
Output type Tax Report Parameters in 349 report:
| | | |
Intra Community Sales of Goods Operations | E | Output | Connects with the Intra Community Sales of Goods tax rates |
Intra Community Purchase of Goods Operations | A | Output | Connects with the Intra Community Purchase of Goods tax rates |
Taxes used by the 349 report:
| | | | | | | |
Tax module | Intra Community Sales | VAT 16 | 0 | Sales | Intra Community Sales of Goods Operations | N | |
Tax module | Intra Community Sales | VAT 7 | 0 | Sales | Intra Community Sales of Goods Operations | N | |
Tax module | Intra Community Sales | VAT 4 | 0 | Sales | Intra Community Sales of Goods Operations | N | |
Tax module | Intra Community Purchase | VAT 16 | 0 | Purchase | Intra Community Purchase of Goods Operations | Y | - VAT 16% (VAT Credit) - VAT -16% (VAT Debit) |
Tax module | Intra Community Purchase | VAT 7 | 0 | Purchase | Intra Community Purchase of Goods Operations | Y | - VAT 7% (VAT Credit) - VAT -7% (VAT Debit) |
Tax module | Intra Community Purchase | VAT 4 | 0 | Purchase | Intra Community Purchase of Goods Operations | Y | - VAT 4% (VAT Credit) - VAT -4% (VAT Debit)
|
Development
All the development will be released as a unique module, and no change in core is required. New elements will have to be added to the application in order to obtain all the necessary data from the system. The development will have two steps: data retrieve, and file build.
Modifying core
349 supports the creation of corrective invoices to rectify previously introduced invoices. However the system currently does not connect "normal" invoices with the "rectified" invoices. This feature of connecting the invoices is very interesting, that's why this improvement is going to developed in core (#10586). A new tab called Reversed Invoices will be added to both Purchase and Sales invoice windows. Inside this tab the user will be able to connect the reversed invoices with the "normal" ones.
Improving core's window through the module
The 349 module will introduce five new fields inside the new Reversed Invoices tab. This fields are:
- 349 correction. Checkbox to indicate whether it is a correction to a previous period
- Periodicity. Combobox that shows whether the 349 report is quarterly or annually submitted.
- Year. Combobox depending on the Legal entity with accounting fiscal calendar
- Period. Combobox that shows the period to be included. Only displayed in case of annual periodicity
- 349 base amount. Textbox where the user introduces the declared base amount for the invoice's business partner in the introduced reversed invoice's period. Only visible when the previous checkbox='Y'
A table level trigger will be created on the C_Invoice_Reverse table to check the introduced amount is always the same in case the user has previously introduced a base amount for the same business partner and period. A table level trigger has been chosen because it is the only way for avoiding the mutating table issue in databases.
As a nice to have feature, a callout should be created to automatically fill this textbox based on the values previously introduced by the user.
Data retrieved from user interface / report configuration
The basic data the report launcher needs to create the 349 file will be retrieved from the Tax Report Launcher user interface and input parameters, and from the 349 configuration (output and constant parameters). Here is the list of the needed parameters:
| | | |
Launcher UI | Organization | Organization for which statement must be calculated | |
Launcher UI | Report | Report file to be generated | |
Launcher UI | Accounting Schema | Accounting schema used to calculate the | |
Launcher UI | Year | Year for which statement must be calculated | |
Launcher UI | Period | Period for which statement must be calculated | |
Input - Textbox | File | File | Name of the generated file |
Input - Textbox | Identification and totals | Contact person | Contact person surname and name |
Input - Textbox | Identification and totals | Contact phone | Contact person phone number |
Input - Checkbox | Identification and totals | Substitutive Statement | In case of substitutive statement |
Input - Textbox | Identification and totals | Former Statement ID | Former Statement ID (just valid in case of Substitutive statement) |
Output | Transactions | Intra Community Sales of Goods Operations | Linked to Tax Key E |
Output | Transactions | Intra Community Purchase of Goods Operations | Linked to Tax Key A
|
Reversed Invoice tab | 349 corrections | Base amount declared for a business partner in a previous period |
Data retrieved from the database
The information about transactions needed for generating the 349 report file will be retrieved from the accounting data available in the database. To query the database, the Data Access Layer (DAL) based on Hibernate will be used.
For keeping the way to access the data as much independent as possible, the Data Access Object (DAO) design pattern will be implemented. This design pattern ensures that the Java class in charge of generating the 349 text file is totally independent of the way we use to get the data from the database.
Java Class for building the 349 file
The Java class that builds the 349 report file will use the StringBuffer class since it is safe in multi-threads environments. The insert() method of this class will be constantly used to ensure no chars are overwritten by error, because the insert() method throws a Java IndexOutOfBoundsException when this happens.
Moreover, the static OBTL_Utility.format() method developed in the Tax Report Launcher module will be used to format and validate the strings introduced in each position of the report file. This method throws exceptions that the Tax Report Launcher catches informing the user about the problems.
File format
The file must be generated and saved using ISO-8859-1 charset.
Not all alphanumeric characters are valid; the only valid characters (and its hex ASCII code) are: “blank” (20), “ ‘ “ (27), “,” (2C), “-” (2D), “.” (2E), “A”-”Z” (41)-(5A), “0”-”9” (30)-(39), “ ´ ” (B4), “Ç” (C7) and “Ñ” (D1).
General rules:
- Every amount entered has to be a positive amount and in Euro as default currency
- Empty numeric type fields must be filled-in with zeros
- Empty alphanumeric fields must be filled-in with blanks
- Numeric and alphanumerical fields must be right side aligned and filled-in with zeros in the left empty positions if any.
- Alphabetical fields must be left side aligned and filled-in with blanks in the right empty positions if any
There will exist three different types of lines in the text file:
- Type I: Deponent line
- Type II: Intra Community Operator line
- Type II: Corrections line
File will be created with: one line of type I, one line of type II-Intra Community Operator line per business partner and tax key, and one line of type II-Corrections line per business partner and tax key.
File structure
Line type 1 – Deponent line
Position From | Position To | Type
Number of positions | Description | OB ERP input (Table/field or user input) |
---|---|---|---|---|
1 | 1 | Num
1 | Line type | Constant parameter= “1” |
2 | 4 | Num
3 | Declaration type | Constant parameter = “349” |
5 | 8 | Num
4 | Period / Year | Input parameter = Year |
9 | 17 | An
9 | Deponent VAT id number | Output parameter =
Legal entity VAT id number
aligned to right, last position has to be the control character. The system has to fulfill with zero the positions left (example: 02889768W) |
18 | 57 | An
40 | Deponent identification (surname, name or company name) | Output parameter =
Legal entity name
in case of the presenter is an individual, it has to be as below: surname name |
58 | 58 | A
1 | Presentation type | Constant parameter =
|
59 | 107 | A
49 | Contact person information
68-107 Surname and Name | Input parameter=
phone number and contact person
|
108 | 120 | Num
13 | Declaration number
| Output parameter=
It has to be a value like the one below: 343255789785
http://www.aeat.es/aeat/jsp/numrefEditran.jsp?mod=349
|
121 | 121 | A
1 | Complementary declaration
| Blank (out of scope) |
122 | 122 | A
1 | Substitutive declaration
| Input parameter = “S” |
123 | 135 | Num
13 | Former declaration number
| Input parameter “Former declaration number”,
|
136 | 137 | An
2 | Period/Year – for annual declaration
Period/Quarter – for quarterly declaration | Input parameter:
Fixed values:
Quarterly declaration: 1T 2T 3T 4T
|
138 | 146 | Num
9 | Total number of EU vendors/suppliers - operators | Output parameter:
|
147 | 161 | Num
15 | Intra-community operation total amount
160 – 161 decimal amount (2 decimals)
| Output parameter:
|
162 | 170 | An
9 | Total number of corrective operators (Business Partner) | Output parameter:
|
171 | 185 | Num
15
| Corrective operations total amount
184-185 decimal amount
| Output parameter:
|
186 | 237 | ---- | Blanks | blanks |
238 | 250 | An
13 | Electronic signature
| Electronic signature to be filled in by the Public Treasury (AEAT)
otherwise blank |
Line type 2 – Intra Community operator line
Position From | Position To | Type
Number of positions | Description | OB ERP input (Table/field or user input) |
---|---|---|---|---|
1 | 1 | Num
1 | Line type | Constant parameter= “1” |
2 | 4 | Num
3 | Declaration type | Constant parameter = “349” |
5 | 8 | Num
4 | Period / Year | Input parameter = Year |
9 | 17 | An
9 | Deponent VAT id number | Output parameter =
Legal entity VAT id number
aligned to right, last position has to be the control character. The system has to fulfill with zero the positions left (example: 02889768W) |
18 | 75 | An
58
| Blanks | Blanks |
76 | 92 | An
17 | Intra-community operator VAT id number, according to rules below:
78-92 (15) – VAT id number
Austria – AT + 9 An characters Belgium – BE + 9 or 10 Num characters Bulgaria – BG + 9 or 10 Num characters Cyprus – CY + 9 An characters Denmark – DK + 8 Num characters Slovenia – SI + 8 Num characters Estonia – EE + 9 Num characters Finland – FI + 8 Num characters France – FR + 11 An characters Greece – EL + 9 Num characters Great Britain – GB + 5,9 or 12 An characters
Hungary – HU + 8 Num characters Italy – IT + 11 Num characters Ireland – IE + 8 An characters Latvia – LV + 11 Num characters Lithuania – LT + 9 or 12 Num characters
Malta – MT + 8 Num characters Poland – PL + 10 Num characters Portugal - PT + 9 Num characters Check Republic – CZ + 8, 9, 10 Num characters Slovakia – SK + 9 or 10 Num characters Romania – RO + 2 to 10 Num characters Sweden – SE + 12 Num characters | Output parameter =
Business partner (intra-community operator) VAT id number |
93 | 132 | A
40 | Intra-community operator name or business name | Output parameter=
Business Partner name /identification
|
133 | 133 | A
1 | Operation key:
“E” - EU supplies VAT exempt “A” - EU acquisitions “T” - EU supplies after an exempt EU acquisition
| Output parameter=
“E” for EU sales transactions “A” for EU purchase transactions or “T” in case of “Operaciones Triangulares”
|
134 | 136 | Num
13 | Tax base amount
134-144 – integer part 145-146 – decimal part
| Output parameter=
Positive total tax base amount (without VAT) per operation key “E”, “A” or “T” and per business partner as a summary of positive / negative transactions within same period.
|
147 | 250 | ---- | Blanks | blanks |
Line type 2 – Corrections line
Position From | Position To | Type
Number of positions | Description | OB ERP input (Table/field or user input) |
---|---|---|---|---|
1 | 1 | Num
1 | Line type | Constant value = “2” |
2 | 4 | Num
3 | Declaration type | Constant value = “349” |
5 | 8 | Num
4 | Period / Year | Input parameter = Year |
9 | 17 | An
9 | Deponent VAT id number | Output parameter =
Legal entity VAT id number
aligned to right, last position has to be the control character. The system has to fulfill with zero the positions left (example: 02889768W) |
18 | 75 | An
58
| Blanks | Blanks |
76 | 92 | An
17 | Intra-community operator VAT id number, according to rules below:
78-92 (15) – VAT id number
Austria – AT + 9 An characters Belgium – BE + 9 or 10 Num characters Bulgaria – BG + 9 or 10 Num characters Cyprus – CY + 9 An characters Denmark – DK + 8 Num characters Slovenia – SI + 8 Num characters Estonia – EE + 9 Num characters Finland – FI + 8 Num characters France – FR + 11 An characters Greece – EL + 9 Num characters Great Britain – GB + 5,9 or 12 An characters
Hungary – HU + 8 Num characters Italy – IT + 11 Num characters Ireland – IE + 8 An characters Latvia – LV + 11 Num characters Lithuania – LT + 9 or 12 Num characters
Malta – MT + 8 Num characters Poland – PL + 10 Num characters Portugal - PT + 9 Num characters Check Republic – CZ + 8, 9, 10 Num characters Slovakia – SK + 9 or 10 Num characters Romania – RO + 2 to 10 Num characters Sweden – SE + 12 Num characters | Output parameter=
Business partner (intra-community operator) VAT id number |
93 | 132 | A
40 | Intra-community operator name or business name | Output parameter=
Business Partner name /identification |
133 | 133 | A
1 | Operation key:
“E” - EU supplies VAT exempt corrections “A” - EU acquisitions corrections “T” - EU supplies after an exempt EU acquisition corrections
| Output parameter=
“E” for EU sales transactions “A” for EU purchase “T” for Operaciones Triangulares
|
134 | 146 | ---- | Blanks | Blanks |
147 | 178 | An
32 | Section to be filled-in only in case of errors or omissions to either “E”, “A” or “T” operations which mean a correction to the tax base amount already submitted for a previous year or period.
Corrections are accrued when the correction fact has been notify to the destination company by issuing a credit purchase invoice or a credit sales invoice depending on the transaction type.
151 – 152 Quarter to be corrected
153 – 163 – integer amount 164-165 – decimal amount
177-178 – decimal amount | In the case of negative operations a window should be shown with all those negatives operations per intra-community operator, so the end-user can select those ones which are corrections to a former 349 declaration, so those are not accumulated to the total tax base amount but deducted from a former tax base amount already submitted.
input parameter: Period/Year to be corrected
input parameter: Fixed values: 0A- annual declaration and for Quarterly declaration: 1T 2T 3T 4T
New and corrected tax base amount accumulated per operation key “E”, “A” or “T” and per intra-community operator for the correspondent former period/quarter selected by the end-user.
.
user input: Former tax base amount already submitted.
|
179 | 250 | ---- | Blanks | blanks |
Open Discussion Items
- Memo Invoices with negative amounts are not taken into account for the 349 report
- Automatic voiding invoices does not introduce base amount