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

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:


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:

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:

There will exist three different types of lines in the text file:

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

Retrieved from "http://wiki.openbravo.com/wiki/Spainaeatmodelo349/Technical_Documentation"

This page has been accessed 443 times. This page was last modified on 26 March 2018, at 15:08. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.