Projects:Intrastat/Technical Document

Contents 1 TECHNICAL DOCUMENTATION: INTRASTAT 2 Purpose of the Project 3 Projects and Modules Information 3.1 Forge projects 3.2 Modules 4 Database and Application Dictionary 4.1 Reference Lists 4.2 Tables, Windows & Tabs 4.3 1. INTR_C_UOM. 4.4 2 .INTR_SETUP. 4.5 3. INTR_M_PRODUCT 4.6 4. INTR_C_BPARTNER 4.7 5. INTR_C_ORDERLINE 4.8 6. INTR_M_INOUTLINE 4.9 7. INTR_C_INVOICELINE 4.10 8. INTR_HEADER 4.11 9. INTR_LINES 4.12 Reports & Process 4.13 Columns in core's tables 4.14 Datasets 4.15 CHANGES REQUIRED IN CORE FOR C_BPARTNER TABLE 4.16 BUSINESS FLOWS

TECHNICAL DOCUMENTATION: INTRASTAT

Purpose of the Project

The purpose of the Project is to add the Intrastat report to the Openbravo ERP. The funcionality will be mainly focus on the Spanish Intrastat's format, but it will also try to be as much generic as possible to be adapted to other European Union countries Intrastat's formats.

Intrastat is a statistical declaration of goods trade within the European Union, which provides tax authorities with good movements information within European Union after European Union customs removal in 1993.

Intrastat report must be sent to the Tax Authorities monthly, within the 12 natural days of the corresponding month after the month in which the transactions were done.

The report must be sent through the INTERNET – on line submission of a valid Intrastat plain file.

Projects and Modules Information

The development of this project will be based on modules. As usual, we will use the Openbravo's forge to centralice the project's information and development status.

The development will be divided into four different modules, which its correspondant forge's projects:

• Intrastat: which provides the generic framework of tables, columns, windows, fields and data; and also the Java interface that the country-specific implementations must fulfill.
• Intrastat – Spanish translation: which provides the Spanish translation of the previous “Intrastat” module.
• Intrastat – Spanish implementation: which implements the concrete Intrastat format for Spain.
• Incoterms: which provides a dataset with the Intrastat's Incoterms

Forge projects

The Intrastat forge project will be created with the following information:

• Project's name: Intrastat
• URL: http://forge.openbravo.com/projects/intrastat
• Description: Intrastat is a statistical declaration of goods trade within the European Union.The purpose of this project is to create an Intrast framework in Openbravo ERP, allowing concrete implementations for the different European Union countries.
• License: Openbravo Commercial License
• Forums: Yes
• Wiki: Yes
• Bug tracking: https://issues.openbravo.com/set_project.php?project_id=17;20
• Code: Mercurial repository at https://code.openbravo.com/erp/pmods/org.openbravo.module.intrastat
• Related project: Spanish Professional Localization Pack, Incoterms

The Intrastat – Spanish translation forge project will be created with the following information:

• Project's name: Intrastat – Spanish Translation
• URL: http://forge.openbravo.com/projects/intrastateses
• Description: Intrastat is a statistical declaration of goods trade within the European Union.The purpose of this project is to create the Spanish (Spain) translation of the Intrastat framework module.
• License: Openbravo Commercial License
• Forums: Yes
• Wiki: Yes
• Bug tracking: https://issues.openbravo.com/set_project.php?project_id=17;20
• Code: Mercurial repository at https://code.openbravo.com/erp/pmods/org.openbravo.module.intrastat.es_ES
• Related project: Spanish Professional Localization Pack, Intrastat

The Intrastat – Spanish implementation forge project will be created with the following information:

• Project's name: Intrastat for Spain
• URL: http://forge.openbravo.com/projects/intrastatspain
• Description: Intrastat is a statistical declaration of goods trade within the European Union.The purpose of this project is to create the Spanish implementation based on the Intrastat framework module.
• License: Openbravo Commercial License
• Forums: Yes
• Wiki: Yes
• Bug tracking: https://issues.openbravo.com/set_project.php?project_id=17;20
• Code: Mercurial repository at https://code.openbravo.com/erp/pmods/org.openbravo.module.intrastat.spain
• Related project: Spanish Professional Localization Pack, Intrastat, Intrastat – Spanish Translation

The Incoterms forge project will be created with the following information:

• Project's name: Incoterms
• URL: http://forge.openbravo.com/projects/incoterms
• Description: Incoterms or International Commercial Terms are a series of international sales terms, published by International Chamber of Commerce (ICC) and widely used in international commercial transactions. They are used to divide transaction costs and responsibilities between buyer and seller and reflect state-of-the-art transportation practices
• License: Openbravo Commercial License
• Forums: Yes
• Wiki: Yes
• Bug tracking: https://issues.openbravo.com/set_project.php?project_id=17;20
• Code: Mercurial repository at https://code.openbravo.com/erp/pmods/org.openbravo.module.incoterms

Modules

The Intastat module will be created with the following information:

• Name: Intrastat
• Java Package: org.openbravo.module.intrastat
• Version: 1.0.0
• Description: Intrastat framework for Openbravo ERP.
• Help: The purpose of the Intrastat is to provide tax authorities with good movements information within European Union after European Union customs removal in 1993.
  Intrastat report must be sent to the Tax Authorities monthly, within the 12 natural days of the corresponding month after the month in which the transactions were done
• Translation Required: Yes
• Module Language: English (USA)
• License: Openbravo Commercial License
• IsCommercial: Yes
• License text: 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 or in the legal folder of this module distribution.
• Author: Openbravo SLU
• URL: http://forge.openbravo.com/projects/intrastat
• DB Prefix: INTR
• Module Dependencies:
• Core 2.50.15807
• European Union Countries 1.0.1
• Incoterms 1.0.0

The Intrastat – Spanish translation module will be created with the following information:

• Name: Intrastat – Spanish translation
• Java Package: org.openbravo.module.intrastat.es_ES
• Version: 1.0.0
• Description: Spanish translation for the Openbravo ERP Intrastat framework.
• Translation Required: No
• Is Translation Module: Yes
• Module Language: Spanish (Spain)
• License: Openbravo Commercial License
• IsCommercial: Yes
• License text: 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 or in the legal folder of this module distribution.
• Author: Openbravo SLU
• URL: http://forge.openbravo.com/projects/intrastateses
• Module Dependencies:
• Intrastat 1.0.0

The Intrastat – Spanish implementation module will be created with the following information:

• Name: Intrastat for Spain
• Java Package: org.openbravo.module.intrastat.spain
• Version: 1.0.0
• Description: Intrastat implementation for Spain based on the Intrastat framework module.
• Translation Required: Yes
• Is Translation Module: No
• Module Language: Spanish (Spain)
• License: Openbravo Commercial License
• IsCommercial: Yes
• License text: 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 or in the legal folder of this module distribution.
• Author: Openbravo SLU
• DB Prefix: INTRES
• URL: http://forge.openbravo.com/projects/intrastatspain
• Module Dependencies:
• Core 2.50.15807
• Intrastat 1.0.0
• Intrastat – Spanish translation 1.0.0

The Incoterms module will be created with the following information:

• Name: Incoterms
• Java Package: org.openbravo.module.incoterms
• Version: 1.0.0
• Description: This modules adds the Incoterms list to Openbravo ERP
• Help: Incoterms or International Commercial Terms are a series of international sales terms, published by International Chamber of Commerce (ICC) and widely used in international commercial transactions. They are used to divide transaction costs and responsibilities between buyer and seller and reflect state-of-the-art transportation practices
• Translation Required: No
• Module Language: English (USA)
• License: Openbravo Commercial License
• IsCommercial: Yes
• License text: 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 or in the legal folder of this module distribution.
• Author: Openbravo SLU
• URL: http://forge.openbravo.com/projects/incoterms
• Module Dependencies:
• Core 2.50.15807

Database and Application Dictionary

Reference Lists

The following reference list will be created at Application Dictionary || Reference:

Intrastat module:

1. INTR_TRANSPORTATION_TYPE. Validation Type “List Validation”. List Reference: 8 records

• Search Key: 1, Name: Sea transport
• Search Key: 2, Name: Rail transport
• Search Key: 3, Name: Road transport
• Search Key: 4, Name: Air transport
• Search Key: 5, Name: Postal consignment
• Search Key: 7, Name: Fixed transport installations
• Search Key: 8, Name: Inland waterway transport
• Search Key: 9, Name: Own propulsion

1. INTR_REPORT_TYPE. Validation Type “List Validation”. List Reference: 3 records

• Search Key: N, Name: Normal declaration Description: Declarations for both acquisition and delivery transactions
• Search Key: C, Name: Complementary declaration Description: Declarations for those cases in which some transactions were not included in a normal declaration regardless those one should have been included
• Search Key: V, Name: Void declaration Description: Voids all the previous declarations for this period and it generates a new Normal one

1. INTR_DECLARATION_TYPE. Validation Type “List Validation”. List Reference: 2 records

• Search Key: ACQ, Name: Intrastat for Acquisitions Description: Declarations for acquisition transactions
• Search Key: DEL, Name: Intrastat for Deliveries Description: Declarations for delivery transactions

1. INTR_TRANSACTION_TYPE. Validation Type “List Validation”. List Reference: 0 records
2. INTR_PORT. Validation Type “List Validation”. List Reference: 0 records

1. INTR_STATISTICAL_REGIME. Validation Type “List Validation”. List Reference: 0 records.
2. INTR_STATUS. Validation Type “List Validation”. List Reference: 2 records:

• Search Key: DR, Name: Draft
• Search Key: PR, Name: Processed
• Search Key: VO, Name: Voided

Intrastat – Spanish implementation module:

1. INTR_TRANSACTION_TYPE. Validation Type “List Validation”. (Reference already created in Intrastat Module, need to add the following list references) List Reference: 17 records

• Search Key: 11, Name: Compra/venta en firme, Description: Transacciones que supongan un traspaso de propiedad real o previsto y una contrapartida (financiera o de otro tipo)
• Search Key: 12, Name: Entrega para venta a la vista o de prueba, Description: Suministro para la venta salvo aprobación o de prueba para consignación o con la mediación de un agente comisionado.
• Search Key: 13, Name: Trueque (compensación en especie), Description: Transacciones que supongan un traspaso de propiedad real o previsto y una contrapartida (financiera o de otro tipo)
• Search Key: 14, Name: Compras por particulares, Description: Transacciones que supongan un traspaso de propiedad real o previsto y una contrapartida (financiera o de otro tipo)
• Search Key: 15 Name: Arrendamiento financiero (alquiler-compra), Description: Transacciones que supongan un traspaso de propiedad real o previsto y una contrapartida (financiera o de otro tipo)
  

• Search Key: 21, Name: Devolución de mercancías, Description: Devolución de mercancías tras registro de la transacción original en el epígrafe 1; sustitución gratuita de mercancías.
• Search Key: 22, Name: Sustitución de mercancías devueltas, Description: Devolución de mercancías tras registro de la transacción original en el epígrafe 1; sustitución gratuita de mercancías.
• Search Key: 23, Name: Sustitución de mercancías no devueltas, Description: Sustitución (por ejemplo, bajo garantía) de mercancías no devueltas. Devolución de mercancías tras registro de la transacción original en el epígrafe 1; sustitución gratuita de mercancías.
  

• Search Key: 31, Name: Mercancías suministradas financiados CE, Description: Mercancías entregadas bajo programas de ayuda gestionados o financiados por la Comunidad Europea. Transacciones (no temporales) que supongan un traspaso de propiedad sin contrapartida (financiera ni de otro tipo).
• Search Key: 32, Name: Otras entregas de ayuda gubernamental, Description: Transacciones (no temporales) que supongan un traspaso de propiedad sin contrapartida (financiera ni de otro tipo).
• Search Key: 33, Name: Otras entregas de ayuda (particulares, orgs. no gubernamentales), Description: Transacciones (no temporales) que supongan un traspaso de propiedad sin contrapartida (financiera ni de otro tipo).
• Search Key: 34 Name: Otros, Description: Transacciones (no temporales) que supongan un traspaso de propiedad sin contrapartida (financiera ni de otro tipo).
  

• Search Key: 40 Name: Operaciones con miras al trabajo por encargo, Description: Operaciones con miras al trabajo por encargo (e) (excepto las que se registren en el epígrafe 7).
  

• Search Key: 50 Name: Operaciones tras el trabajo por encargo, Description: Operaciones tras el trabajo por encargo (e) (excepto las que se registren en el epígrafe 7).
  

• Search Key: 70 Name: Operaciones en programas comunes de defensa/intergubernamentales, Description: Operaciones en el marco de programas comunes de defensa u otros programas intergubernamentales de producción conjunta.
  

• Search Key: 80 Name: Suministro de materiales de construcción y maquinaria, Description: Suministro de materiales de construcción y maquinaria para trabajos en el marco de un contrato general de construcción o ingeniería
  

• Search Key: 90 Name: Otras transacciones

1. INTR_PORT. Validation Type “List Validation”. (Reference already created in Intrastat Module, need to add the following list references) List Reference: 59 records.

Search Key                                                Name

Note: Take a look at SQL scripts

1. INTR_STATISTICAL_REGIME. Validation Type “List Validation”. (Reference already created in Intrastat Module, need to add the following list references) List Reference: 10 records.

• Search Key: Acq_1 Name: Comunitarias destino final Estado miembro introducción, Description: Llegadas de mercancías comunitarias con destino final en el Estado miembro de introducción.
• Search Key: Acq_2 Name: Temporales para ser reexpedidas a un Estado miembro, Description: Llegadas temporales de mercancías comunitarias para ser reexpedias al Estado miembro de procedencia o a otro Estado miembro, en el mismo estado en que llegaron.
• Search Key: Acq_3 Name: Temporales a otro Estado miembro con transformación, Description: Llegadas temporales de mercancías comunitarias para ser reexpedidas al Estado miembro de procedencia o a otro Estado miembro, después de sufir una operación de transformación.
• Search Key: Acq_4 Name: Comunitarias devueltas al mismo estado expedidas, Description: Llegadas de mercancías comunitarias, devueltas en el mismo estado en el que fueron previament expedidas al Estado miembro de procedencia o a otro Estados miembros.
• Search Key: Acq_5 Name: Comunitarias devueltas transformadas, Description: Llegadas de mercancías comunitarias, devueltas después de haber sufrido una operación de reparación o transformación, previamente expedidos al Estado miembro de procedencia o a otro Estado miembro
  

• Search Key: Del_1 Name: Salida mercancías a estado miembro, Description: Salida de mercancía comunitarias con destino final en el Estado miembro de destino.
• Search Key: Del_2 Name: Salida temporal para reintroducir en el mismo estado, Description: Salida temporal de mercancías comunitarias para ser reintroducidas con posterioridad desde el Estado miembro de destino o desde otro Estado miembro en el mismo estado en que son expedidas.
• Search Key: Del_3 Name: Salida temporal para reintroducir tras transformación, Description: Salida temporal de mercancías comunitarias para ser reintroducidas con posterioridad, desde el Estado miembro de destion o desde otro Estado miembro después de haber sufrido una operación de reparación o transformación.
• Search Key: Del_4 Name: Salida para devolución de mercancías, Description: Salida de mercancías comunitarias, que se devuelven en el mismo estado en el que previamente llegaron procedentes del Estado miembro de destino o procedentes de otro Estado miembro.
• Search Key: Del_5 Name: Salida de mercancías tras transformación, Description: Salida de mercancías comunitarias, que se devuelven después de haber sufrido una operación de transformación, previamente recibidas del Estado miembro de destino o de otro Estado miembro.

Tables, Windows & Tabs

Intrastat module:

1. INTR_C_UOM.

• Name: INTRACUOM
• Description: Supplementary units
• Help: It stores the supplementary units required by the Intrastat resport
• Java Class Name: IntrastatUOM
• Data Access Level: Client/Org

Column	Req.	Datatype	Description	Notes
INTR_C_UOM_ID	Y	VARCHAR(32)	Supplementary Unit of Measure for Intrastat report	PK
AD_CLIENT_ID	Y	VARCHAR(32)	Audit data	FK: AD_Client
AD_ORG_ID	Y	VARCHAR(32)	Audit data	FK: AD_Org
CREATED	Y	DATE	Audit data	DF: SYSDATE
CREATEDBY	Y	VARCHAR(32)	Audit data
UPDATED	Y	DATE	Audit data	DF: SYSDATE
UPDATEDBY	Y	VARCHAR(32)	Audit data
ISACTIVE	Y	CHAR(1)	Active	Y,N. DF:Y
VALUE	Y	NVARCHAR2(60)	Supplementary UOM search key	- Updateable = N - Identifier
NAME	Y	NVARCHAR2(60)	Supplementary UOM name
DESCRIPTION	N	NVARCHAR2(255)	Supplementary UOM description

• This table corresponds to a new Window/Tab, called Intrastat Supplementary Unit of Measure, inside Master Data Management || Product Setup.

2 .INTR_SETUP.

• Name: INTRASetup
• Description: Instrastat setup
• Java Class Name: IntrastatSetup
• Data Access Level: Organization
• Unique: AD_Org_ID, C_Year_ID, TRANS_TYPE

Column	Req.	Datatype	Description	Notes
INTR_SETUP_ID	Y	VARCHAR(32)	Intrastat's setup	PK
AD_CLIENT_ID	Y	VARCHAR(32)	Audit data	FK: AD_Client
AD_ORG_ID	Y	VARCHAR(32)	Audit data	- FK: AD_Org - Link to parent column
CREATED	Y	DATE	Audit data	DF: SYSDATE
CREATEDBY	Y	VARCHAR(32)	Audit data
UPDATED	Y	DATE	Audit data	DF: SYSDATE
UPDATEDBY	Y	VARCHAR(32)	Audit data
ISACTIVE	Y	CHAR(1)	Active	Y,N. DF:Y
C_CALENDAR_ID	Y	VARCHAR(32)	Reporting Calendar	- Just display the ones marked for Intrastat (C_Calendar.Intrastat=Y)
C_YEAR_ID	Y	VARCHAR(32)	The Intrastat's setup for this year	- FK to C_Year.- Only Years of the selected calendar
LIMITAMT	Y	DECIMAL	Limit Amount
YTDREPORTED_AMT	Y	DECIMAL	YTD Reported Amount	Updateable = N
DECLARATION_TYPE	Y	VARCHAR(60)	Intrastat declaration type	Reference: List (INTR_DECLARATION_TYPE)
TODO: INSTRASTAT_FORMAT			Spain, Germany, etc.
C_INCOTERMS_ID	N	VARCHAR(32)	Incoterm (Delivery Terms)	Ref: TableDir FK: C_INCOTERMS_ID
TRANSACTION_TYPE	N	VARCHAR(60)	Transaction Type	Reference: List (INTR_TRANSACTION_TYPE)
TRANSPORTATION_TYPE	N	VARCHAR(60)	Transportation Type	Reference: List (INTR_TRANSPORTATION_TYPE)
PORT	N	VARCHAR(60)	Port/Airport	- Reference: List (INTR_PORT) - Display logic: when TRANSPORTATION_TYPE IN (1,3) Nice to have
STATISTICAL_REGIME	N	VARCHAR(60)	Statistical Regime	Reference: List (INTR_STATISTICAL_REGIME)
PROCESS	Y	CHAR(1)	Configure next year	-Reference: Button -Process: INTR_CONFIG_YEAR -Default: N

• This table corresponds to a new tab, called Intrastat, inside General Setup || Enterprise || Organization.

3. INTR_M_PRODUCT

• Name: INTRProduct
• Description: Default Intrastat definition for a product
• Java Class Name: IntrastatProduct
• Data Access Level: Client/Org

Column	Req.	Datatype	Description	Notes
INTR_M_PRODUCT_ID	Y	VARCHAR(32)	Intrastat definition for a product	PK
AD_CLIENT_ID	Y	VARCHAR(32)	Audit data	FK: AD_Client
AD_ORG_ID	Y	VARCHAR(32)	Audit data	FK: AD_Org
CREATED	Y	DATE	Audit data	DF: SYSDATE
CREATEDBY	Y	VARCHAR(32)	Audit data
UPDATED	Y	DATE	Audit data	DF: SYSDATE
UPDATEDBY	Y	VARCHAR(32)	Audit data
ISACTIVE	Y	CHAR(1)	Active	Y,N. DF:Y
M_PRODUCT_ID	Y	VARCHAR(32)	Product	FK: M_Product
NC8_CODE	N	VARCHAR(8)	NC8 Item code
HASUNIT	Y	CHAR(1)	Has Supplementary Unit	-Reference: YesNo -Default: N
INTR_C_UOM_ID	N	VARCHAR(32)	Supplementary Unit	- FK: INTR_C_UOM - Display Logic (HASUNIT=Y)
CONVERSION	N	DECIMAL	Conversion rate	- Display Logic (HASUNIT=Y)
NETMASS_KG	Y	DECIMAL	Net Mass (Kg)

• Trigger need on insert and on update: INTR_M_PRODUCT_TRG. It controls the user just inserts or updates records if M_Product.ProductType=Item and only one record per product is allowed
• This table corresponds to a new tab, called Intrastat, inside Master Data Management || Product. The UI Pattern of this tab is Standard.

4. INTR_C_BPARTNER

• Name: INTRProduct
• Description: Default Intrastat definition for a product
• Java Class Name: IntrastatProduct
• Data Access Level: Client/Org

Column	Req.	Datatype	Description	Notes
INTR_C_BPARTNER_ID	Y	VARCHAR(32)	Intrastat definition for a Business Partner	PK
AD_CLIENT_ID	Y	VARCHAR(32)	Audit data	FK: AD_Client
AD_ORG_ID	Y	VARCHAR(32)	Audit data	FK: AD_Org
CREATED	Y	DATE	Audit data	DF: SYSDATE
CREATEDBY	Y	VARCHAR(32)	Audit data
UPDATED	Y	DATE	Audit data	DF: SYSDATE
UPDATEDBY	Y	VARCHAR(32)	Audit data
ISACTIVE	Y	CHAR(1)	Active	Y,N. DF:Y
C_BPARTNER_ID	Y	VARCHAR(32)	Business Partner	FK: C_BPartner
PO_TRANSPORTATION_TYPE	N	VARCHAR(60)	Transportation Type	Reference: List (INTR_TRANSPORTATION_TYPE) DL: Only for Vendors
PO_PORT	N	VARCHAR(60)	Port/Airport	- Reference: List (INTR_PORT) DL: Only for Vendors - Display logic: when TRANSPORTATION_TYPE IN (1,3) Nice to have
SO_TRANSPORTATION_TYPE	N	VARCHAR(60)	Transportation Type	Reference: List (INTR_TRANSPORTATION_TYPE) DL: Only for Customers
SO_PORT	N	VARCHAR(60)	Port/Airport	- Reference: List (INTR_PORT) DL: Only for Customers - Display logic: when TRANSPORTATION_TYPE IN (1,3) Nice to have
ORIG_C_COUNTRY_ID	N	VARCHAR(32)	Origin Country	- FK: C_Country - DL: Only for Vendors

• This table corresponds to a new tab, called Intrastat, inside Master Data Management || Business Partner || Customer and Master Data Management || Business Partner || Vendor. The UI Pattern of this tab is Standard.
• Unique: C_BPartner_ID (INTR_C_BPARTNER_UN)

5. INTR_C_ORDERLINE

• Name: INTROrderLine
• Description: Intrastat Definition for an Order line's product
• Java Class Name: IntrastatOrderLine
• Data Access Level: Organization
• Unique: AD_Client_ID, C_Orderline_ID
  

Column	Req.	Datatype	Description	Notes
INTR_C_ORDERLINE_ID	Y	VARCHAR(32)	Intrastat definition for an Order line's product	PK
AD_CLIENT_ID	Y	VARCHAR(32)	Audit data	FK: AD_Client
AD_ORG_ID	Y	VARCHAR(32)	Audit data	FK: AD_Org
CREATED	Y	DATE	Audit data	DF: SYSDATE
CREATEDBY	Y	VARCHAR(32)	Audit data
UPDATED	Y	DATE	Audit data	DF: SYSDATE
UPDATEDBY	Y	VARCHAR(32)	Audit data
ISACTIVE	Y	CHAR(1)	Active	Y,N. DF:Y
C_ORDERLINE_ID	Y	VARCHAR(32)	Order line	Link to parent column
M_PRODUCT_ID	Y	VARCHAR(32)	Product	FK: M_Product
C_INCOTERMS_ID	N	VARCHAR(32)	Incoterm (Delivery Terms)	Ref: TableDir FK: C_INCOTERMS_ID
TRANSACTION_TYPE	N	VARCHAR(60)	Transaction Type	Reference: List (INTR_TRANSACTION_TYPE)
TRANSPORTATION_TYPE	N	VARCHAR(60)	Transportation Type	Reference: List (INTR_TRANSPORTATION_TYPE)
PORT	N	VARCHAR(60)	Port/Airport	- Reference: List (INTR_PORT) - Display logic: when TRANSPORTATION_TYPE IN (1,3) Nice to have
STATISTICAL_REGIME	N	VARCHAR(60)	Statistical Regime	Reference: List (INTR_STATISTICAL_REGIME)
ORIG_C_COUNTRY_ID	N	VARCHAR(32)	Origin Country	- FK: C_Country - DL: Only for Purchase
NC8_CODE	N	VARCHAR(8)	NC8 Item code
HASUNIT	Y	CHAR(1)	Has Supplementary Unit	-Reference: YesNo -Default: N
INTR_C_UOM_ID	N	VARCHAR(32)	Supplementary Unit	- FK: INTR_C_UOM - Display Logic (HASUNIT=Y)
CONVERSION	N	DECIMAL	Conversion rate	- Display Logic (HASUNIT=Y)
NETMASS_KG	N	DECIMAL	Net Mass (Kg)	- Display Logic (HASUNIT=Y)

• This table corresponds to a new tab, called Intrastat, inside Purchase/Sales Order | Lines. The UI Pattern of this tab is Standard.

6. INTR_M_INOUTLINE

• Name: INTRINOUTLine
• Description: Intrastat Definition for a Shipment/Receipt line's product
• Java Class Name: IntrastatInOutLine
• Data Access Level: Organization

• Unique: AD_Client_ID, M_InOutline_ID

Column	Req.	Datatype	Description	Notes
INTR_M_INOUTLINE_ID	Y	VARCHAR(32)	Intrastat definition for a Shipment/Receipt line's product	PK
AD_CLIENT_ID	Y	VARCHAR(32)	Audit data	FK: AD_Client
AD_ORG_ID	Y	VARCHAR(32)	Audit data	FK: AD_Org
CREATED	Y	DATE	Audit data	DF: SYSDATE
CREATEDBY	Y	VARCHAR(32)	Audit data
UPDATED	Y	DATE	Audit data	DF: SYSDATE
UPDATEDBY	Y	VARCHAR(32)	Audit data
ISACTIVE	Y	CHAR(1)	Active	Y,N. DF:Y
M_INOUTLINE_ID	Y	VARCHAR(32)	Shipment/Receipt line	Link to parent column
M_PRODUCT_ID	Y	VARCHAR(32)	Product	FK: M_Product
C_INCOTERMS_ID	N	VARCHAR(32)	Incoterm (Delivery Terms)	Ref: TableDir FK: C_INCOTERMS_ID
TRANSACTION_TYPE	N	VARCHAR(60)	Transaction Type	Reference: List (INTR_TRANSACTION_TYPE)
TRANSPORTATION_TYPE	N	VARCHAR(60)	Transportation Type	Reference: List (INTR_TRANSPORTATION_TYPE)
PORT	N	VARCHAR(60)	Port/Airport	- Reference: List (INTR_PORT) - Display logic: when TRANSPORTATION_TYPE IN (1,3) Nice to have
STATISTICAL_REGIME	N	VARCHAR(60)	Statistical Regime	Reference: List (INTR_STATISTICAL_REGIME)
ORIG_C_COUNTRY_ID	N	VARCHAR(32)	Origin Country	- FK: C_Country - DL: Only for Purchase
NC8_CODE	N	VARCHAR(8)	NC8 Item code
HASUNIT	Y	CHAR(1)	Has Supplementary Unit	-Reference: YesNo -Default: N
INTR_C_UOM_ID	N	VARCHAR(32)	Supplementary Unit	- FK: INTR_C_UOM - Display Logic (HASUNIT=Y)
CONVERSION	N	DECIMAL	Conversion rate	- Display Logic (HASUNIT=Y)
NETMASS_KG	N	DECIMAL	Net Mass (Kg)	- Display Logic (HASUNIT=Y)

• This table corresponds to a new tab, called Intrastat, inside Good Shipment/Good Receipt | Lines. The UI Pattern of this tab is Standard

7. INTR_C_INVOICELINE

• Name: INTRInvoiceLine
• Description: Intrastat Definition for an Invoice line's product
• Java Class Name: IntrastatInvoiceLine
• Data Access Level: Organization

• Unique: AD_Client_ID, C_Invoiceline_ID

Column	Req.	Datatype	Description	Notes
INTR_C_INVOICELINE_ID	Y	VARCHAR(32)	Intrastat definition for an Invoice line's product	PK
AD_CLIENT_ID	Y	VARCHAR(32)	Audit data	FK: AD_Client
AD_ORG_ID	Y	VARCHAR(32)	Audit data	FK: AD_Org
CREATED	Y	DATE	Audit data	DF: SYSDATE
CREATEDBY	Y	VARCHAR(32)	Audit data
UPDATED	Y	DATE	Audit data	DF: SYSDATE
UPDATEDBY	Y	VARCHAR(32)	Audit data
ISACTIVE	Y	CHAR(1)	Active	Y,N. DF:Y
C_INVOICELINE_ID	Y	VARCHAR(32)	Invoice line	Link to parent column
M_PRODUCT_ID	Y	VARCHAR(32)	Product	FK: M_Product
C_INCOTERMS_ID	N	VARCHAR(32)	Incoterm (Delivery Terms)	Ref: TableDir FK: C_INCOTERMS_ID
TRANSACTION_TYPE	N	VARCHAR(60)	Transaction Type	Reference: List (INTR_TRANSACTION_TYPE)
TRANSPORTATION_TYPE	N	VARCHAR(60)	Transportation Type	Reference: List (INTR_TRANSPORTATION_TYPE)
PORT	N	VARCHAR(60)	Port/Airport	- Reference: List (INTR_PORT) - Display logic: when TRANSPORTATION_TYPE IN (1,3) Nice to have
STATISTICAL_REGIME	N	VARCHAR(60)	Statistical Regime	Reference: List (INTR_STATISTICAL_REGIME)
ORIG_C_COUNTRY_ID	N	VARCHAR(32)	Origin Country	- FK: C_Country - DL: Only for Purchase
NC8_CODE	N	VARCHAR(8)	NC8 Item code
HASUNIT	Y	CHAR(1)	Has Supplementary Unit	-Reference: YesNo -Default: N
INTR_C_UOM_ID	N	VARCHAR(32)	Supplementary Unit	- FK: INTR_C_UOM - Display Logic (HASUNIT=Y)
CONVERSION	N	DECIMAL	Conversion rate	- Display Logic (HASUNIT=Y)
NETMASS_KG	N	DECIMAL	Net Mass (Kg)	- Display Logic (HASUNIT=Y)

• This table corresponds to a new tab, called Intrastat, inside Purchase/Sales Invoice | Lines. The UI Pattern of this tab is Single Record.
• The following mockup represents the tab for the Order/Shipment|Receipt/Invoice line:

8. INTR_HEADER

• Name: INTRHeader
• Description: Intrastat Report Header
• Java Class Name: IntrastatHeader
• Data Access Level: Organization
• All fields are readonly. The are automatically populated by the Application (Intrastat Launcher).
• This table corresponds to a new tab, called Intrastat, inside Intrastat/Intrastat report.

Column	Req.	Datatype	Description	Notes
INTR_HEADER_ID	Y	VARCHAR(32)	Intrastat report header	PK
AD_CLIENT_ID	Y	VARCHAR(32)	Audit data	FK: AD_Client
AD_ORG_ID	Y	VARCHAR(32)	Audit data	FK: AD_Org
CREATED	Y	DATE	Audit data	DF: SYSDATE
CREATEDBY	Y	VARCHAR(32)	Audit data
UPDATED	Y	DATE	Audit data	DF: SYSDATE
UPDATEDBY	Y	VARCHAR(32)	Audit data
ISACTIVE	Y	CHAR(1)	Active	Y,N. DF:Y
NAME	Y	NVARCHAR2(60)	Supplementary UOM name
C_Year_ID	Y	TableDir	Year
C_Period_ID	Y	TableDir	Period
DECLARATION_TYPE	Y	VARCHAR(60)	Declaration Type	List: INTR_DECLARATION_TYPE
REPORT_TYPE	Y	VARCHAR(60)	Intrastat report type	Reference: List (INTR_REPORT_TYPE)
STATUS	Y	VARCHAR(60)	Intrastat Header Status	List: INTR_STATUS
TOTAL_AMT	Y	DECIMAL	Total Amount Declared	DF: 0
PROCESSED	Y	CHAR(1)	Button: Process the header	DF: N
ISGENERATED	Y	CHAR(1)	Button: Generate file	DF: N

9. INTR_LINES

• Name: INTRLines
• Description: Intrastat Report Lines
• Java Class Name: IntrastatLine

• Data Access Level: Organization

Column	Req.	Datatype	Description	Notes
INTR_LINES_ID	Y	VARCHAR(32)	Intrastat report lines	PK
AD_CLIENT_ID	Y	VARCHAR(32)	Audit data	FK: AD_Client
AD_ORG_ID	Y	VARCHAR(32)	Audit data	FK: AD_Org
CREATED	Y	DATE	Audit data	DF: SYSDATE
CREATEDBY	Y	VARCHAR(32)	Audit data
UPDATED	Y	DATE	Audit data	DF: SYSDATE
UPDATEDBY	Y	VARCHAR(32)	Audit data
ISACTIVE	Y	CHAR(1)	Active	Y,N. DF:Y
INTR_HEADER_ID	Y	VARCHAR(32)	Intrastat report header	- FK: INTR_HEADER - Link to parent column
ISINCLUDED	Y	CHAR(1)	Included	DF: Y
ISMANUALCHANGE	Y	CHAR(1)	Manual Change	DF: N
C_INVOICELINE_ID	N	VARCHAR(32)	Invoice Line	-FK: C_INVOICELINE
INVOICE_DATE	N	DATE	Invoice date
INVOICELINE_AMT	Y	DECIMAL	Invoice Line Amount
C_BPARTNER_ID	N	VARCHAR(32)	Business Partner	FK: C_BPARTNER
C_INCOTERMS_ID	N	VARCHAR(32)	Incoterm (Delivery Terms)	Ref: TableDir FK: C_INCOTERMS_ID
TRANSACTION_TYPE	N	VARCHAR(60)	Transaction Type	Reference: List (INTR_TRANSACTION_TYPE)
TRANSPORTATION_TYPE	N	VARCHAR(60)	Transportation Type	Reference: List (INTR_TRANSPORTATION_TYPE)
PORT	N	VARCHAR(60)	Port/Airport	- Reference: List (INTR_PORT) - Display logic: when TRANSPORTATION_TYPE IN (1,3) Nice to have
STATISTICAL_REGIME	N	VARCHAR(60)	Statistical Regime	Reference: List (INTR_STATISTICAL_REGIME)
ORIG_C_COUNTRY_ID	N	VARCHAR(32)	Origin Country	- FK: C_Country - DL: Only for Purchase
NC8_CODE	N	VARCHAR(8)	NC8 Item code
HASUNIT	Y	CHAR(1)	Has Supplementary Unit	-Reference: YesNo -Default: N
INTR_C_UOM_ID	N	VARCHAR(32)	Supplementary Unit	- FK: INTR_C_UOM - Display Logic (HASUNIT=Y)
CONVERSION	N	DECIMAL	Conversion rate	- Display Logic (HASUNIT=Y)
NETMASS_KG	N	DECIMAL	Net Mass (Kg)	- Display Logic (HASUNIT=Y)

This table corresponds to a new tab, called Intrastat, inside Intrastat/Intrastat report/Lines.

Reports & Process

Intrastat module:

1. INTR_LAUNCHER

• Name: Intrastat Launcher
• Data Access Level: Organization
• UI Pattern: Standard
• Report: No
• Process Class:
• Java Class Name: org.openbravo.module.intrastat.src.IntrastatLauncher
• Default: Y
• Parameters:

Column	Req.	Reference	Description	Validation
AD_ORG_ID	Y	TableDir	Organization	Show only Legal Entities
C_Year_ID	Y	TableDir	Year	Only Years of the selected organization (or its parents)
C_Period_ID	Y	TableDir	Period	Only Periods of the selected year
DECLARATION_TYPE	Y	List: INTR_DECLARATION_TYPE	Declaration Type
REPORT_TYPE	Y	List: INTR_REPORT_TYPE	Instrastat Declaration

2. INTR_COPY_YEAR

• Name: Intrastat Copy to next year
• Data Access Level: Organization
• UI Pattern: Standard
• Report: No
• Process Class:
• Java Class Name: org.openbravo.module.intrastat.src.IntrastatCopyYear
  Default: Y

Columns in core's tables

Intrastat module:

1. C_REGION

Column	Req.	Datatype	Description	Notes
EM_INTR_CODE	N	VARCHAR(2)	Intrastat Region code

2. C_DOCTYPE

Column	Req.	Datatype	Description	Notes
EM_INTR_TRANSACTION_TYPE	N	VARCHAR(60)	Transaction Type	- Reference: List (INTR_TRANSACTION_TYPE) - Display Logic: DOCBASETYPE IN ('ARI', 'API')
EM_INTR_STATISTICAL_REGIME	N	VARCHAR(60)	Statistical Regime	Reference: List (INTR_STATISTICAL_REGIME) - Display Logic: DOCBASETYPE IN ('ARI', 'API')

3. M_FREIGHTCATEGORY

Column	Req.	Datatype	Description	Notes
EM_INTR_TRANSPORTATION_TYPE	N	VARCHAR(60)	Transportation Type	Reference: List (INTR_TRANSPORTATION_TYPE)

4. C_CALENDAR

Column	Req.	Datatype	Description	Notes
EM_INTR_ISINTRASTAT	Y	CHAR(1)	Intrastat Calendar	- DF: Y - On create default: 'Y'

Datasets

Incoterms module:

• SearchKey: Incoterms
• Name: Incoterms
• Data Access Level: Client/Org
• Table: C_INCOTERMS:
  • - Include All Columns = Y
  • - IsBusinessObject = N
  • - Exclude Audit Info = Y
• Columns:

• • Name: EXW, Description: Ex Works - The seller makes the goods available at his premises. The buyer is responsible for all charges
  • Name: FCA, Description: Free Carrier - The seller hands over the goods,cleared for export, into the custody of the first carrier (named by the buyer) at the named place.
  • Name: FAS, Description: Free Alongside Ship - The seller must place the goods alongside the ship at the named port. The seller must clear the goods for export; this changed in the 2000 version of the Incoterms. Suitable for maritime transport only.
  • Name: FOB, Description: Free on board - The seller must load the goods on board the ship nominated by the buyer, cost and risk being divided at ship's rail. The seller must clear the goods for export.
  • Name: CFR, Description: Cost and Freight - Seller must pay the costs and freight to bring the goods to the port of destination. However, risk is transferred to the buyer once the goods have crossed the ship's rail. Maritime transport only.
  • Name: CIF, Description: Cost, Insurance and Freight - Seller pays the costs, insurance and freight to the port of destination. Risk is transferred to the buyer once the goods have crossed the ship's rail.
  • Name: CPT, Description: Carriage Paid To - The general/containerised/multimodal equivalent of CFR. The seller pays for carriage to the named point of destination, but risk passes when the goods are handed over to the first carrier.
  • Name: CIP, Description: Carriage and Insurance Paid - The containerised transport/multimodal equivalent of CIF. Seller pays for carriage and insurance to the named destination point, but risk passes when the goods are handed over to the first carrier.
  • Name: DAF, Description: Delivered At Frontier - The seller pays for transportation to the named place of delivery at the frontier. The buyer arranges for customs clearance and pays for transportation from the frontier to his factory. The passing of risk occurs at the frontier.
  • Name: DES, Description: Delivered Ex Ship - Where goods are delivered ex ship, the passing of risk does not occur until the goods are made available for unloading to the buyer. The seller pays the freight and insurance costs.
  • Name: DEQ, Description: Delivered Ex Quay - This is similar to DES, but the passing of risk does not occur until the goods have been unloaded at the port of destination.
  • Name: DDU, Description: Delivered Duty Unpaid - The seller delivers the goods to the buyer at the named destination. The buyer is responsible for the costs and risks for the unloading, duty and any subsequent delivery beyond the place of destination.
  • Name: DDP, Description: Delivered Duty Paid - The seller pays for all transportation costs and bears all risk until the goods have been delivered and pays the duty.
  • Name: XXX, Description: Other - Other delivery term

Intrastat – Spanish implementation module:

1. Supplementary Unit of Measure

• SearchKey: IntrUOM
• Name: Supplementary Unit of Measure
• Data Access Level: Client/Org
• Table:

Table	Activ	SQL where clause	Notes
INTR_C_UOM	Y		- Include All Columns = Y - IsBusinessObject = N - Exclude Audit Info = Y

• The dataset must contain the following data:
  

Value	Name
BO	BOMBONA
BR	ARQUEO BRUTO
CE	CENTENAS (100 UNIDADES)
CL	NUMERO DE CELDAS
CT	CAPACIDAD CARGA UTIL EN TONELADAS METRICAS
GI	GRAMOS ISOTOPOS FISIONABLES
GN	GRAMOS NETOS
HA	GRADO ALCOHOLICO VOLUMENTRICO POR NÚMERO DE HETOLITROS
HG	HETOLITROS DE ALCOHOL PURO 100%
HL	HECTOLITROS
HM	HECTOMETROS
HP	HECTOLITRO x GRADO PLATO
H1	HECTOKILOGRAMOS DE AZUCAR BLACA (100kg)
H2	HECTOKILOGRAMOS DE TRIGO BLANCO (100kg)
H3	HECTOKILOGRAMOS NETO DE MATERIA SECA (100kg)
H4	HECTOKILOGRAMOS NETOS POR FRACIÓN DEL 1% DEL PESO
EN	SACAROSA
H5	HECTOLILOGRAMOS DE PESO VIVO
KA	KILOGRAMOS TOTAL ALCOHOL
KL	MILES DE LITROS
KN	KILOGRAMOS NETOS
KP	KILOGRAMO DE MATERIA LACTICA
KT	KILOGRAMO DE MATERIA SECA LACTICA
LA	LITROS DE ALCHOHOL PURO 100%
LT	LITROS
M1	METROS LINEALES
M2	METROS CUADRADOS
M3	METROS CUBICOS
M4	MIL METROS CUBICOS
MI	MILLARES
MK	MILES KILOVATIOS/HORA
PA	NUMERO DE PARES
QE	QUINTALES DE PESO NETO ESCURRIDO
QN	QUINTALES NETOS
TA	TONELADAS DE CLORURO POTASICO
TB	TONELADAS NETAS DE MATERIA SECA
TC	TONELADA NETA POR FRACCIÓN DEL 1% DEL PESO
TJ	TERAJOULE
TN	TONELADAS NETAS
UN	UNIDADES
WA	KILOGRAMOS NETOS DE DIHIDROESTRETOMICINA
WB	KILOGRAMOS NETOS DE COLINCLORURO
WE	KILOGRAMOS DE PESO NETO ESCURRIDO
WL	NUMERO DE QUILATES
W1	KILOGRAMOS NETOS DE K20 (OXIDO DE POTASION)
W2	KILOGRAMOS NETOS DE KOH (HIDROXIDO DE POTASION)
W3	KILOGRAMOS NETOS DE N (NITROGENO)
W4	KILOGRAMOS NETOS DE NaOH (HIDROXIDO DE SODIO)
W5	KILOGRAMOS NETOS DE P205 (PENTAOXIDO DE DIFOSFORO)
W6	KILOGRAMOS NETOS DE U (URANIO)
W7	KILOGRAMOS NETOS DE MATERIA SECA AL 90%
W8	KILOGRAMOS NETOS DE AGUA OXIGENADA H2O2
W9	KILOGRAMOS DE METILAMINA

1. Intrastat Spanish Region codes

• SearchKey: IntrESRegionCodes
• Name: Intrastat Spanish Region Codes
• Data Access Level: System only
• Table:

Table	Activ	SQL where clause	Notes
C_Region	Y	country.name='Spain'	- Include All Columns = N - IsBusinessObject = N - Exclude Audit Info = Y

• Column:

Column	Active	Excluded
EM_INTR_CODE	Y	N

The EM_INTR_CODE column of the C_Region table must be filled with the following data, where “CODIGO” represents the EM_INTR_CODE, and “PROVINCIA” represents the Spanish region (NAME column of the C_Region table):

CHANGES REQUIRED IN CORE FOR C_BPARTNER TABLE

1. Add new columns to C_BPartner table:

Column	Req.	Datatype	Description	Notes
PO_C_INCOTERMS_ID	N	VARCHAR(32)	Incoterm (Delivery Terms)	Reference: TableDir (C_INCOTERMS).Display only for vendor
SO_C_INCOTERMS_ID	N	VARCHAR(32)	Incoterm (Delivery Terms)	Reference: TableDir (C_INCOTERMS).Display only for customers

Modify SE_Order_BPartner callout. This callout is used when introducing a Business Partner into the Purchase/Sales Order window. It fills all the data related with the Business Partner, like the Address, Contact, etc. We must extend the callout to also fill the default Incoterm configured at the Business Partner window.

BUSINESS FLOWS

1. Intrastat Launcher (INTR_LAUNCHER)

2. Copy Intrastat configuration (INTR_CONFIG_YEAR)

3. INTR_C_ORDERLINE_TRG

4. INTR_M_INOUTLINE_TRG

5. INTR_C_INVOICELINE_TRG

6. Copy Business Partner and Product info from Defaults

Category: Projects ERP