Spainaeatmodelo349/Technical Documentation

Contents 1 Introduction 2 Content of the module 3 Assumptions 4 Module definition 5 Tax rates, Tax keys and Tax parameters 6 Development 6.1 Modifying core 6.2 Improving core's window through the module 6.3 Data retrieved from user interface / report configuration 6.4 Data retrieved from the database 6.5 Java Class for building the 349 file 6.6 File format 6.7 File structure 6.7.1 Line type 1 – Deponent line 6.7.2 Line type 2 – Intra Community operator line 6.7.3 Line type 2 – Corrections line 7 Open Discussion Items

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:

Parameter Name	Tax key	Type	Description
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:

Location	Name	VAT group	Rate	Sales/Purchase	Tax Report Parameter	Summary level	Children Tax Rates
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:

Type	Report Group	Name	Description
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 Note: 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 Note: in case of the presenter is an individual, it has to be as below: surname name
58	58	A 1	Presentation type	Constant parameter = “T” on-line presentation
59	107	A 49	Contact person information 59-67 Phone number 68-107 Surname and Name	Input parameter= phone number and contact person
108	120	Num 13	Declaration number 349 summary statement number	Output parameter= It has to be a value like the one below: 343255789785 it can be obtained here: http://www.aeat.es/aeat/jsp/numrefEditran.jsp?mod=349
121	121	A 1	Complementary declaration in case of operations do not included in a former declaration for the same period/quarter	Blank (out of scope)
122	122	A 1	Substitutive declaration in case the current declaration is a new one with the aim of cancel a former declaration for the same period/quarter	Input parameter = “S”
123	135	Num 13	Former declaration number only in case of substitutive declaration otherwise fill-in with zeros.	Input parameter “Former declaration number”, otherwise zeros (000000000000)
136	137	An 2	Period/Year – for annual declaration Period/Quarter – for quarterly declaration	Input parameter: Fixed values: 0A- annual declaration Quarterly declaration: 1T 2T 3T 4T
138	146	Num 9	Total number of EU vendors/suppliers - operators	Output parameter: Sum of records of all the operation with keys “E”, “A” and “T” coming from raw type 2 – position 133
147	161	Num 15	Intra-community operation total amount 147 – 159 integer amount 160 – 161 decimal amount (2 decimals) filled-in as zeros in case of not amount	Output parameter: Sum of all base amounts, positions 134-136 and all operation keys “E”, “A” and “T” coming from raw type 2 – position 133 filled-in as zeros in case of not amount
162	170	An 9	Total number of corrective operators (Business Partner)	Output parameter: Sum of records of all corrective operation keys “E”, “A” and “T” coming from raw type 2 corrections – position 133
171	185	Num 15	Corrective operations total amount 171-183 integer amount 184-185 decimal amount filled-in as zeros in case of not amount	Output parameter: Sum of all corrective base amounts, positions 134-136 and all operation keys “E”, “A” and “T” coming from raw type 2 – corrections position 133 filled-in as zeros in case of not amount
186	237	----	Blanks	blanks
238	250	An 13	Electronic signature the electronic signature is generated by the AEAT applications so it should be blank in any other case.	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 Note: 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: 76-77 (2) – country code 78-92 (15) – VAT id number Germany – DE + 9 Num characters 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 Netherlands – NL + 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 Luxembourg – LU + 8 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 this section will include the total tax base amount for every kind of operation key“E”or “A” or “T” and that implies + or – amounts (credit invoices) must be accumulated in case – amounts are not corrections (see corrections row) to former 340 reports already submitted to the tax authorities If in the same period there have been several transactions with an intra-community operator, those transactions + or - must be accumulated by key operation code in order to enter a single record for each key operation and period.	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 Note: 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: 76-77 (2) – country code 78-92 (15) – VAT id number Germany – DE + 9 Num characters 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 Netherlands – NL + 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 Luxembourg – LU + 8 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. 147 – 150 Period to be corrected 151 – 152 Quarter to be corrected 153 – 165 Corrected tax base amount: 153 – 163 – integer amount 164-165 – decimal amount 166-178 Former tax base amount already submitted 166-176 – integer 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. 147 – 150 (4) input parameter: Period/Year to be corrected 151 – 152 (2) input parameter: Fixed values: 0A- annual declaration and for Quarterly declaration: 1T 2T 3T 4T 153-165 (13) 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. Output parameter based on former tax base amount manually entered by the end user (positions 166 – 178) and current transaction amount. . 166-178 (13) 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