Projects:Intrastat/Technical Document
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:
- 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
- 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
- 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
- INTR_TRANSACTION_TYPE. Validation Type “List Validation”. List Reference: 0 records
- INTR_PORT. Validation Type “List Validation”. List Reference: 0 records
- INTR_STATISTICAL_REGIME. Validation Type “List Validation”. List Reference: 0 records.
- 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:
- 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
- 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
- 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:
- 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:
- 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 |
- 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
- 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
