View source | Discuss this page | Page history | Printable version   

Spainaeatmodelo347/Technical Documentation

<<-- Back to main page

Contents

Introduction

Present document explains the development required to build a module on top of Openbravo ERP, that generates Spanish 347 annual tax statement as a valid file. The 347 annual tax statement is a mandatory tax statement for Spanish companies.

This module will be part of the Openbravo Professional Spanish Localization Pack version 1.0.

Content of the module

The “Modelo 347” 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 will be executed by using the Tax Report Launcher feature. 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 347 annual tax statement file.

Assumptions

This module will use the Tax Report Launcher feature developed as an extended module for Openbravo ERP. 347 annual tax statement 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. 347 Annual tax statement 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 347 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.

Description of the development

This module/tax statement has to gather a set of entries from accounting, 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).

According to the Law EHA/3012/2008, date on October 20th, approved 2008, this statement must collect all operations hold with business partners exceeding a total amount per business parter of 3.005,06 € in sales transactions, and/or 3.005,06 € in purchase transactions, for a give period of time which is the calendar year.

The data for filling in this tax statement will be obtained from the accounting information, not from the management information.

For obtaining this values, there are some tax rates which need to be setuo, as well as some tax report parameters:

Output type Tax Report Parameters to add:

Parameter Name
Type
Description
Purchase Operations Output Common Purchase Operations related tax rates
Sales Operations Output Common Sales Operations related tax rates
Services Purchase Operations Output Services-type Purchase Operations related tax rates
Services Sales Operations Output Services-type Sales Operations related tax rates

Existing tax rates (in the community Spanish taxes module) where this parameters will be added:

Name
Rate
Sales/Purchase
Tax Report Parameter
VAT 16% Purchase 16 Purchase Common Purchase Operations
VAT 4% Purchase 4 Purchase Common Purchase Operations
VAT 7% Purchase 7 Purchase Common Purchase Operations
VAT+EC 16+4% Purchase (+16%) 16 Purchase Common Purchase Operations
VAT+EC 16+4% Purchase (+4%) 4 Purchase Common Purchase Operations
VAT+EC 4+0,5% Purchase (+0,5%) 0,5 Purchase Common Purchase Operations
VAT+EC 4+0,5% Purchase (+4%) 4 Purchase Common Purchase Operations
VAT+EC 7+1% Purchase (+1%) 1 Purchase Common Purchase Operations
VAT+EC 7+1% Purchase (+7%) 7 Purchase Common Purchase Operations
VAT 16% Sales 16 Sales Common Sales Operations
VAT 4% Sales 4 Sales Common Sales Operations
VAT 7% Sales 7 Sales Common Sales Operations
VAT+EC 16+4% Sales (+16%) 16 Sales Common Sales Operations
VAT+EC 16+4% Sales (+4%) 4 Sales Common Sales Operations
VAT+EC 4+0,5% Sales (+0,5%) 0,5 Sales Common Sales Operations
VAT+EC 4+0,5% Sales (+4%) 4 Sales Common Sales Operations
VAT+EC 7+1% Sales (+1%) 1 Sales Common Sales Operations
VAT+EC 7+1% Sales (+7%) 7 Sales Common Sales Operations

New tax rates, included in this module:

Name
Rate
Sales/Purchase
Tax Report Parameter
Real Estate 16% Purchase 16 Purchase Real Estate Purchase Operations
Real Estate 7% Purchase 7 Purchase Real Estate Purchase Operations
Real Estate 4% Purchase 4 Purchase Real Estate Purchase Operations
Real Estate 16% Sales 16 Sales Real Estate Sales Operations
Real Estate 7% Sales 7 Sales Real Estate Sales Operations
Real Estate 4% Sales 4 Sales Real Estate Sales Operations

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.

Module definition

Name: Spain AEAT - Modelo 347

Type: Module

Java Package: org.openbravo.module.AEAT347.ES

DB Prefix: AEAT347_

Reference Data: Yes

Module Language: English (USA)

Translation Required: Yes

License Type: Openbravo Commercial License 1.0

Dependencies: Tax Report Launcher module, version 0.5.0

Dataset: C_TAX with all the required tax rates (and tax keys/subkeys):

Sales Transactions (7/B)

Purchase Transactions (7/A)

Real Estate Sales Transmissions Transaction (7/B)

Real Estate Purchase Transmissions Transaction (7/A)

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 
************************************************************************************ 

Changes in the ERP

Application Dictionary

New fields will be added to the Product window:


Table
Column
Field Printtext
Type
Description
M_PRODUCT EM_AEAT347_IsLeaseBusiness Lease Business Product CHAR(1 byte) Check-box that sets this product as a business lease element to report separately in the statement.
M_PRODUCT EM_AEAT347_Situation Situation Reference List Possible values:

1-Valid reference in Spain (not in País Vasco or Navarra)

2-Valid reference in País Vasco or Navarra

3-Not valid reference

4-Locates abroad

M_PRODUCT EM_AEAT347_ReferenceId Valid Reference Id NVARCHAR2 (25) Valid reference id of the lease business
M_PRODUCT EM_AEAT347_LocationType Type of Location Reference List “CALLE” - Street

“PLAZA” - Square

“AVDA “ - Avenue

M_PRODUCT EM_AEAT347_StreetName Street/Av/Square Name NVARCHAR2 (50) Name of the street/av/square
M_PRODUCT EM_AEAT347_StreetNumberType Street/Av/Square Number Type Reference List “NUM” - Number

“KM “ - Kilometer

M_PRODUCT EM_AEAT347_StreetNumber Street/Av/Square Number NVARCHAR2 (5) Number of the street/av/square
M_PRODUCT EM_AEAT347_StreetNumberQualifier Street/Av/Square Number Qualifier Reference List “BIS” - Bis

“DUP” - Duplicated

“MOD” - Modified

“ANT” - Old

M_PRODUCT EM_AEAT347_Building Building information NVARCHAR2 (3) Building information such us Block A, or Tower I
M_PRODUCT EM_AEAT347_Hall Hall Information NVARCHAR2 (3)
M_PRODUCT EM_AEAT347_Hallway Hallway Information NVARCHAR2 (3)
M_PRODUCT EM_AEAT347_Floor Floor NVARCHAR2 (3)
M_PRODUCT EM_AEAT347_Apartment Apartment NVARCHAR2 (3) Apartment, or letter
M_PRODUCT EM_AEAT347_Complement Complementary Address Information NVARCHAR2 (40) Free text for extra address information
M_PRODUCT EM_AEAT347_Location Location or City NVARCHAR2 (30)
M_PRODUCT EM_AEAT347_County County NVARCHAR2 (30)
M_PRODUCT EM_AEAT347_CountyCode County Code NVARCHAR2 (5) Valid County Code, according to http://www.ine.es/jaxi/menu.do?type pcaxis&path /t20/e245/codmun&file inebase&L 0
M_PRODUCT EM_AEAT347_RegionCode Region Code NVARCHAR2 (5) Valid Region Code
M_PRODUCT EM_AEAT347_ZipCode Zip Code NVARCHAR2 (5) Valid Zip Code

A new table will be created, called AEAT347_DOCTYPE. This table will hold all the document types that will be included in the statement. The window will be shown in the Financial Management > Analysis Tools entry of the menu. Will be created with next columns (fields):


Table
Column
Data Type
Nullable


AEAT347_DOCTYPE AD_CLIENT_ID String(32 byte) No
AEAT347_DOCTYPE AD_ORG_ID String(32 byte) No
AEAT347_DOCTYPE CREATED Date No
AEAT347_DOCTYPE CREATEDBY String(32 byte) No
AEAT347_DOCTYPE UPDATED Date No
AEAT347_DOCTYPE UPDATEDBY String(32 byte) No
AEAT347_DOCTYPE ISACTIVE Char(1 byte) No
AEAT347_DOCTYPE C_DOCTYPE_ID String(32 byte) No


New field will be added to the Cash Journal window:

Table
Column
Field Printtext
Type
Description
C_CASH EM_AEAT347_C_YEAR_ID 347 Year TABLE (C_Year) This field is used to enter the year in case the cash journal is collecting a transaction belonging to previous years. Example, we create the cash journal in 2010 to reflect the collection of money from an invoice of 2009.

If the transaction belongs to this year, it's not necessary to enter the year.

Database

Following the example of the function C_DEBT_PAYMENT_PAID, another function called AEAT347_DEBT_PAYMENT_AMOUNT_DEST, will return, for a payment id given, three amounts: total paid in cash, total paid in bank and total pending. This will be done using the fields C_CASHBOOK_ID and C_BANKACCOUNT_ID.

Development

Data retrieve

When the report is launched, there are some values which the user needs to fill-in which are related to: tax report launcher filters, and input parameter related fields:


Type
Description
Value
TEXT FILE
Input Name of the generated file Textbox
PARAMETER GROUP: IDENTIFICATION AND TOTALS
Tax Report Launcher Filter Year for which statement must be calculated C_YEAR.C_YEAR_ID
Tax Report Launcher Filter Organization for which statement must be calculated AD_ORG.AD_ORG_ID
Input Contact person surname and name Textbox
Input Contact person phone number Textbox
Input Substitutive Statement Checkbox
Input Former Statement Number Textbox
Input Legal representative Tax Id Textbox

Query 1

FACT_ACCT table must be queried. This query or queries must have next characteristics:

Once the previous query is solved by the database, it's content is processed in the Java class to obtain the “fields”:

Query 2

C_INVOICE table is queried, using all the different C_INVOICE_ID values obtained in the query 1, and filtered by GRANDTOTAL >= 6.000 € -with its correspondent currency conversion, if necessary-. Using AEAT347_DEBT_PAYMENT_AMOUNT_DEST function, have for each invoice included in the statement, all the payments created for the invoice, and for each of them, the amount paid by cash.

Query 3

With the C_BPARTNER_ID elements obtained in query 1, for those which field 1.D is different from zero, different M_PRODUCT_ID elements are obtained, with the sum of amounts for each asset. FACT_ACCT table is queried.

File build

Once all the information required is gathered, it must be formatted, ordered and written down into a plain text file.

As an example, sample report for Tax Report Launcher can be consulted.

Java Class that builds file

The Java class that will build the file will create StringBuffer object, and concatenate different Strings using the insert method.

File must be generated and saved using ISO-8859-1 charset.

Format

Not all alphanumeric characters are valid, as described in AEAT web. 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).

The file will be built by concatenating different fields of a fixed length. There are two different types of fields: numerics and alphanumerics. Alphanumeric fields will be left aligned, filled with blanks where necessary. Numeric fields will be right aligned, filled with zeros in the left and with no sign.

There will exist three different types of lines in the text file: Type I, Type II-Transactions and Type II-Lease. File will be created with: one line of type I, one line of type II-transactions per business partner and tax key, and one line of type II-lease per group of operations with one business partner related to a product marked as “Business Lease”.

Fields to add to the type I line are (in red, data that should be calculated, and the way of calculating):


Pos. From Pos. To Type /

Number of positions

Description Value. Data source.
1 1 Num

1

Register type CONSTANT

Fixed value “1”

2 4 Num

3

Tax Model CONSTANT

Fixed value “347”

5 8 Num

4

Period (Year) Using parameter C_YEAR.C_YEAR_ID, retrieve C_YEAR.NAME, and obtain first four numbers. Check that corresponds to a valid number.
9 17 An

9

Legal Entity Tax id number Using parameter AD_ORG.AD_ORG_ID, retrieve AD_ORGINFO.TAXID
18 57 An

40

Legal Entity Name (surname, name or company name) Using parameter AD_ORG.AD_ORG_ID, retrieve AD_ORG.NAME
58 58 A

1

Presentation type CONSTANT

Fixed value “T”

59 67 A

9

Contact person information


59-67 Phone number

INPUT

Contact person phone number

68 107 A

40

Contact person information


68-107 Surname and Name

INPUT

Contact person surname and name

108 120 Num

13

Declaration/Statement number


347 summary statement number

CONSTANT

“3470000000000”

121 121 A

1

Complementary declaration/statement


In case of operations do not included in a former declaration for the same period

CONSTANT

Blank.

122 122 A

1

Substitutive declaration/statement


In case the current declaration is a new one with the aim of cancel a former declaration for the same period/quarter

CONSTANT

“S” value if the input Substitutive Statement checkbox is activated; otherwise blank.

123 135 Num

13

Former declaration/statement number


Only in case of a complementary or substitutive declaration otherwise fill-in with zeros.

INPUT

If the input Substitutive Statement checkbox is activated, here the input Former Statement Number textbox content will be printed. Otherwise blank.

136 144 Num

9

Total number of entities (Business partners) included in the declaration Field P: Count of the number of different business partners obtained in the database query
145 159 Num

15

Total Transactions amount


145-157 integer amount

158-159 decimal amount

Field O: Sum of all the amounts of the operations with all business partner, and tax keys
160 168 Num

9

Total number of real estate


Field 1.D: Number of different products of the group, for which M_PRODUCT.AEAT347_IsLeaseBusiness is 'Y'
169 183 Num

15

Total lease transactions amount


169 – 181 integer amount

182-183 decimal amount

Field 1.C: Sum of all the transactions included in register type 2 Lease information, positions 100 to 114.



184 390 ---- Blanks Blanks
391 399 An

9

Legal representative Tax Id INPUT

Legal representative Tax Id

400 487 ---- Blanks Blanks
488 500 An

13

Electronic signature


the electronic signature is generated by the AEAT applications so it should be blank in any other case.

3470000000000

One type II-Transaction line will be added per each combination of business partner and tax id obtained in the query to FACT_ACCT table. Fields to add to the type II-Transactions lines are (in red, data that should be calculated, and the way of calculating):


Pos. From Pos. To Type /

Number of positions

Description Value. Data source.
1 1 Num

1

Register type CONSTANT

Fixed value “2”

2 4 Num

3

Tax Model CONSTANT

Fixed value “347”

5 8 Num

4

Period (Year) Using parameter C_YEAR.C_YEAR_ID, retrieve C_YEAR.NAME, and obtain first four numbers. Check that corresponds to a valid number.
9 17 An

9

Legal Entity Tax id number Using parameter AD_ORG.AD_ORG_ID, retrieve AD_ORGINFO.TAXID
18 26 An

9

BP Tax Id (Spanish Tax Id) Field 1.E1: BP tax id.
27 35 An

9

Blanks Blanks
36 75 An

40

BP Name (surname, name or company name) Field 1.E2: BP Name
76 76 A

1

Sheet Type CONSTANT

Fixed value “D”

77 78 Nun


Region code


77-78 Region code

Field 1.E4
79 80 NUM Country code


79-80 Country code

Field 1.E3
81 81 ---- Blanks blanks
82 82 A

1

347 Operation Key Field 1B: Tax Key
83 97 Num

15

Transaction amount


83-95 integer amount

96-97 decimal amount

Field 1.A: Sum of the amount of the operations with one business partner, and one tax key
98 98 A

1

Insurance operation Blank
99 99 A

1

Lease operation If Field 1.D > 0, then “X”, else blank.
100 114 Num

15

Cash amounts


100-112 integer amount

113-114 decimal amount

Linking query 2 with query 1 where 2.A coincides with 1.E5. If some amount is obtained, write it down here. Otherwise, zeros.
115 129 Num

15

Real estate transmission amount


115-127 integer amount

128-129 decimal amount


Field 1.F if different from zero. Otherwise, zeros.
130 500 ---- Blanks blanks

Register 2 – Lease information


Pos. From Pos. To Type /

Number of positions

Description Value. Data source.
1 1 Num

1

Register type CONSTANT

Fixed value “2”

2 4 Num

3

Tax Model CONSTANT

Fixed value “347”

5 8 Num

4

Period (Year) INPUT


Same value as the one entered for Register Type 1

9 17 An

9

Legal Entity Tax id number Using parameter AD_ORG.AD_ORG_ID, retrieve AD_ORGINFO.TAXID
18 26 An

9

BP Tax Id (Spanish Tax Id) Field 1.E1: BP tax id.
27 35 An

9

Blanks Blanks
36 75 An

40

BP Name (surname, name or company name) Field 1.E2: BP Name
76 76 A

1

Sheet Type CONSTANT

Fixed value “I”

77 99 ---- Blanks blanks
100 114 Num

15

Lease transaction amount


100-112 integer amount

113-114 decimal amount

Field 3.C
115 115 Num

1

Asset-product (real estate) situation Field 3.D1
116 140 An

25

Valid Real Estate Reference Id Field 3.D2
141 333 An

193

Leased Real estate Location (Address)


141-145 Location type


146-195 Street name


196-198 Street number type


199-203 Street number


204-206 Street number qualifier


207-209 Building/Block


210-212 Hall


213-215 Ladder


216-218 Floor


219-221 Letter


222-261 Complementary address information


262-291 Location or city


292-321 County


322-326 Location code


324-328 Region code


329-333 Zip code

Fields 3.D3 to 3.D18
334 500 ---- Blanks Blanks

Appendix I

Countries1of4.png Countries2of4.png Countries3of4.png Countries4of4.png

Appendix II

Alava 1 León 24
Albacete 2 Lleida 25
Alicante 3 Lugo 27
Almería 4 Madrid 28
Asturias 33 Málaga 29
Avila 5 Melilla 52
Badajoz 6 Murcia 30
Barcelona 8 Navarra 31
Burgos 9 Ourense 32
Cáceres 10 Palencia 34
Cádiz 11 Palmas, Las 35
Cantabria 39 Pontevedra 36
Castellón 12 Rioja,La 26
Ceuta 51 Salamanca 37
Ciudad Real 13 S.C.Tenerife 38
Córdoba 14 Segovia 40
La Coruña 15 Sevilla 41
Cuenca 16 Soria 42
Girona 17 Tarragona 43
Granada 18 Teruel 44
Guadalajara 19 Toledo 45
Guipúzcoa 20 Valencia 46
Huelva 21 Valladolid 47
Huesca 22 Vizcaya 48
Islas Baleares 7 Zamora 49
Jaén 23 Zaragoza 50

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

This page has been accessed 3,267 times. This page was last modified on 8 March 2012, at 15:45. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.