Projects:MultiTaxReport/Technical Specification

Contents 1 Technical Specifications 1.1 Introduction 1.2 Content of the module 1.3 Module definition 1.4 Development 1.4.1 Application Dictionary Elements / Application ElementsReport and Process 1.4.2 Menu 1.5 Manual Development 1.5.1 Data retrieved from user interface 1.5.2 Data retrieved from the database 1.5.3 Transform DAL results to FieldProvider

Technical Specifications

Introduction

Present document explains the development required to build a module on top of Openbravo ERP, that generates a multidimensional Tax Report to help the users to manually fill in and submit  some Spanish Tax Reports.

This module will be a new commercial module which will require OB Commercial License.

Content of the module

The “Multidimensional Tax Report” module will contain all the application dictionary elements and software artifacts needed for Openbravo ERP to generate a report in several formats (PDF, HTML, XLS) with all the information about any transaction linked to any tax or withholding.

Module definition

• Name: Multidimensional Tax Report
• Type: Module
• Java Package: org.openbravo.module.invoiceTaxReportEnhanced
• DB Prefix: OBITRE
• Reference Data: No
• Module Language: English
• Translation Required: Yes
• License Type: Openbravo Commercial License 1.0
• Dependencies: core MP14

Development

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.

Application Dictionary Elements / Application ElementsReport and Process

• Name: Multidimensional Tax ReportJava
• Class: org.openbravo.module.invoiceTaxReportEnhanced.ad_reports.OBITRE_InvoiceTaxReportJR
• Process Mapping:
  • /org.openbravo.module.invoiceTaxReportEnhanced/ad_reports/OBITRE_InvoiceTaxReport.html (Default)
  • /org.openbravo.module.invoiceTaxReportEnhanced/ad_reports/OBITRE_InvoiceTaxReport_Excel.xls

Menu

• Name: Multidimensional Tax Report
• Action: Report
• Process: Multidimensional Tax Report

Manual Development

Data retrieved from user interface

There are some filters that will be used to retrieve the Database information:

Filter	Type	Mandatory	Description
From Date (*)	Date	YES	"From Date" to be entered by the end-user, system will retrieve tax transactions taken into account the "From Date" entered by the end-user.
To Date (*)	Date	YES	"To Date" to be entered by the end-user, system will retrieve tax transactions taken into account the "To Date" entered by the end-user.
Organization	Selector	YES	"Organization" to be selected by the end-user from a reference list, system will retrieve tax transactions for the Organization selected.
Show Details info	Check	NO	In case the end-user selects this check box below information must be displayed: - Invoice Number - Invoice Date - Business Partner
Purchase Transaction	Radio Button	YES	In case the end-user selects this check system will retrieve tax transactions linked to purchase transactions
Sales Transaction	Radio Button	YES	In case the end-user selects this check system will retrieve tax transactions linked to sales transactions
Both Transaction	Radio Button	YES	In case the end-user selects this check system will retrieve tax transactions regardless its type.
Tax Withholding	Radio Button	YES	Two options to split between Tax or Withholding. In case end-user select "Tax" - the system will retrieve tax transactions linked to any of the Tax Rates setup at application path: "Financial Management || Accounting || Setup || Tax Rate  ||  Tax"  which are NOT setup as  "Withholding Tax"; therefore Tax Rate selector will be prompt. In case end-user selects "Withholding", the system will retrieve tax transactions linked to Tax Rates setup as withholdings or Withholding, depending on Withholding selectors described below.
Tax	Selector	NO	In case the end-user selects "Tax" option in the "Tax or Withholding" radio button, system will allow the end user to select a Tax Rate; otherwise any Tax Rate will be taken into account while retrieving tax transactions.
Withholding on Invoice	Selector	NO	In case the end-user selects a "Withholding on Invoice" or "Withholding rates", system will retrieve tax transactions linked to that specific tax Rates setup as Withholding Rates; otherwise system will retrieve tax transaction regardless its Withholding rate or type.In case end-user selects a Withholding on Invoice rate, "Withholding on Settlement" selector will be set to not editable.
Withholding on Settlement	Selector	NO	In case the end-user selects a "Withholding on Settlement" tax rate, system will retrieve tax transactions linked that specific withholding; otherwise system will retrieve tax transactions regardless its Withholding rate or type.In case end-user selects a Withholding on Settelment rate, "Withholding on Invoice" selector will be set to not editable.
Business Partner	Multiple Selector	NO	This selector will allow the end-user to select a specific group of BP's. If it is blank it show information about all BP's with corresponding tax transactions.
Group by Business Partner	Check	NO	It this check is marked the report will show the tax information grouped by BP.

Data retrieved from the database

The information about transactions needed for the report will be retrieved from the following tables depending on the Use Case:Purchase / Payment Transactions:

1. Tax Rates and Withholding configured as Tax Rate (all tax information, i.e. tax name, rate, etc. from C_TAX)
   • Invoices
     • C_INVOICE_TAX
   • Manual Settlements
     • C_SETTLEMENT
     • C_DEBT_PAYMENT
     • C_DEBT_PAYMENT_BALANCING
     • C_GLITEM
   • GL Journals
     • GL_JOURNALLINE
     • GL_JORUNAL
   • Bank Statements
     • C_BANKSTATEMENTLINE
     • C_GLITEM
   • Cash Journals
     • C_CASHJOURNALLINE
     • C_GLITEM

1. Withholding (all tax information, i.e. tax name, rate, etc. from C_WITHHOLDING)
   • Invoices
     • C_SETTLEMENT
     • C_DEBT_PAYMENT
     • C_INVOICE
   • Manual Settlements
     • C_SETTLEMENT (Twice, cancelled and generated)
     • C_DEBT_PAYMENT_BALANCING
     • C_GLITEM
   • GL Journals
     • GL_JOURNALLINE
     • C_GLITEM
   • Manual Settlements Direct Posting
     • C_SETTLEMENT
     • C_DEBT_PAYMENT
     • C_DEBT_PAYMENT_BALANCING
     • C_GLITEM
   • Bank Statements
     • C_BANKSTATEMENTLINE
     • C_GLITEM
   • Cash Journals
     • C_CASHJOURNALLINE
     • C_GLITEM

To query the database, the Data Access Layer (DAL) based on Hibernate will be used. For keeping the way to access the data as much independent as possible, the Data Access Object (DAO) design pattern will be implemented. This design pattern ensures that the Java class in charge of generating the report is totally independent of the way we use to get the data from the database.

Transform DAL results to FieldProvider

The final report is developed using Jasper Reports . Jasper can retrieve the data directly from DB using SQL language embeded in report, or can print data structures received as a parameter. Since the complexity of multiple data sources of this report, we will send the data structure as a parameter. This parameter has to be a FieldProvider[] java object. So we have to convert from an HQL data structure to a FieldProvider array.We will obtain the data from DAL in several ArrayLists, then using convertToJasperDetailStructure we create an Array of HashMaps and after that we create the final FieldProvider using the class FieldProviderFactory.