Toolbox

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:

Fields:
- 1.A- Sum of the result of substracting the fields amtacctdr-amtacctcr for purchase operations (tax key A), and amtacctcr-amtaccrdr for sales operations (tax key B) of all the rows of the group.
- 1.B- Tax Key of the rows of the group.
- 1.C- Sum of the amounts (calculated as in field A) of those rows in which M_PRODUCT.AEAT347_IsLeaseBusiness is 'Y'.
  1.D- Number of different fact_acct_group_id values of the rows of the group, for which M_PRODUCT.AEAT347_IsLeaseBusiness is 'Y'.
- 1.E- Business Partner information:
  - 1.E1- C_BPARTNER.TAXID
  - 1.E2- C_BPARTNER.NAME
  - 1.E3- C_BPARTNER_LOCATION.C_COUNTRY_ID
  - 1.E4- C_BPARTNER_LOCATION.C_REGION_ID
  - 1.E5- C_BPARTNER_ID
- 1.F- Sum of the amounts (calculated as in field A) of those operations where Real Estate Transmissions Transaction tax rate has been applied.
Join FACT_ACCT with M_PRODUCT, C_TAX, C_DOCTYPE, C_BPARTNER, C_BPARTNER_LOCATION and C_LOCATION tables.
Filtered by document type. Only elements included in AEAT347_DOCTYPE.C_DOCTYPE_ID where AEAT347_DOCTYPE.ISACTIVE is 'Y' are included.
Filtered by date (year chosen in the filters when launching report)
Filtered by amount: only elements which amount (field 1.A) is bigger than 3.005,06 € will be included.
Filtered by tax rate: only elements with a C_INVOICELINE.C_TAX_ID that corresponds to a tax rate associated to one of the tax key/s of the module.
Grouped by business partner, tax key and tax sub-key.

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

O- The sum of all the field 1.A values.
P- The number of different business partners obtained in the database query is summed up.
Q- The BP Tax Id obtained in the field 1.E1 is processed. If it do not follow the structure of a valid Spanish tax id (one letter and nine numbers) then, this value is replaced for an empty value.
R- The BP country value retrieved in field 1.E3, is converted into the official code (see Appendix I).
S- The BP region value retrieved in field 1.E4, is converted into an empty value if country in 1.E3 field is different from Spain, or the correspondent official code (see Appendix II) in other case.

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.

Fields
- 2.A- Business Partner ID
- 2.B- Total amount paid in cash for all the invoices of the BP.
Join with C_BPARTNER table.
Filter by amount paid in cash, because of one only invoice bigger than 6.000 €.
Filter by C_INVOICE_ID elements present in the FACT_ACCT rows obtained in query 1.
Group by C_BPARTNER_ID.

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.

Fields:
- 3.A- M_PRODUCT_ID
- 3.B- FACT_ACCT.C_BPARTNER_ID
- 3.C- Sum of the amounts (calculated as in field 1.A)
- 3.D- Asset information:
  - 3.D1- AEAT347_Situation
  - 3.D2- AEAT347_ReferenceId
  - 3.D3- AEAT347_LocationType
  - 3.D4- AEAT347_StreetName
  - 3.D5- AEAT347_StreetNumberType
  - 3.D6- AEAT347_StreetNumber
  - 3.D7- AEAT347_StreetNumberQualifier
  - 3.D8- AEAT347_Building
  - 3.D9- AEAT347_Hall
  - 3.D10- AEAT347_Hallway
  - 3.D11- AEAT347_Floor
  - 3.D12- AEAT347_Apartment
  - 3.D13- AEAT347_Complement
  - 3.D14- AEAT347_Location
  - 3.D15- AEAT347_County
  - 3.D16- AEAT347_CountyCode
  - 3.D17- AEAT347_RegionCode
  - 3.D18- AEAT347_ZipCode
Joined with M_PRODUCT table
Filtered by C_BPARTNER_ID elements from Query 1, with field 1.D different from zero.
Grouped by M_PRODUCT_ID.

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

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,153 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.

Toolbox

PDF Books

Search

Participate

Spainaeatmodelo347/Technical Documentation

Contents

Introduction