Spainaeatmodelo347/Technical Documentation
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:
| | | |
| 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:
| | | | |
| 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:
| | | | |
| 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:
| | | | | |
| 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):
| | | |
|
| 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:
| | | | | |
| 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:
| | | |
| 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
| INPUT
Contact person phone number |
| 68 | 107 | A
40 | Contact person information
| INPUT
Contact person surname and name |
| 108 | 120 | Num
13 | Declaration/Statement number
| CONSTANT
“3470000000000” |
| 121 | 121 | A
1 | Complementary declaration/statement
| CONSTANT
Blank. |
| 122 | 122 | A
1 | Substitutive declaration/statement
| CONSTANT
“S” value if the input Substitutive Statement checkbox is activated; otherwise blank. |
| 123 | 135 | Num
13 | Former declaration/statement number
| 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
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
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
| 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
| Field 1.E4 |
| 79 | 80 | NUM | 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
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
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
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
|
| 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
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)
| 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 |