Projects:GrossAmountsinInvoiceLines Technical Documentation

Contents 1 Purpose of the Project 2 Introduction 3 High Level Functional Requirements 4 Technical Implementation 4.1 Module Definition 5 High Level Technical Requirements 6 New database objects 6.1 New columns in C_INVOICELINE and C_ORDERLINE tables 7 Application Dictionary Changes 7.1 Changes in Existing Fields 7.2 New Fields 7.3 Reference 7.4 Messages 7.5 Extension Point setup 7.6 Reference Data 7.7 Processes 8 Open Discussion Items 9 Closed Discussion Items

Purpose of the Project

The purpose of this document is to describe the Technical Specifications required for a new extension module called Gross Amounts in Invoices and Orders.

Introduction

This Technical Specification document outlines the implementation of Gross Amounts in Invoices and Orders feature.

High Level Functional Requirements

This module will provide the following functionalities:

1. Public Sector organizations to enter and properly post either Gross or Net amount “National” purchase invoices in the system. Public Sector organizations cannot deduct VAT paid which means VAT amount is an expense for them.

2. Commercial Units to enter and properly post either Gross or Net amount “National” purchase invoices in the system. Commercial Units can deduct VAT paid.

3. Public Sector organizations and Commercial Units to enter and properly post Net amount “Intra-community” purchase invoices in the system. Intra-community purchase invoice do not include VAT regardless of what VAT must be paid at destination country. Public Sector organizations will not be able to deduct Intra-community VAT but Commercial organizations.

Technical Implementation

Module Definition

• Name: Gross Amounts in Invoices and Orders
• Java Package: org.openbravo.module.grossamountinvoicelines
• Version: 1.0.0
• Description: Gross amounts (Amounts including Taxes) can be entered at order/invoice line Level
• Help/Comment: This module will allow the end user to enter in the system Gross Amounts (Amounts including Taxes, by example VAT), at sales/purchase order line level as well as sales/purchase invoice line level. The system will re-calculate Net amounts based on the Gross amount and tax amount entered by the end-user
• Translation Required: checked
• Module Language: English
• IsCommercial: checked
• License Type=OB Commercial License
• 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 the Openbravo ERP core distribution.
• Author/Licensor = Openbravo S.L.
• URL = forge.openbravo.com/projects/amountandvat
• Dependency with: core
• DB Prefix:GNAT

High Level Technical Requirements

1. In Purchase/Sales Order/Invoice Header, the following fields are to be renamed.

1.1 Summed Line Amount as Total Net Amount

1.2 Grand Total Amount as Total Gross Amount

2. In Purchase/Sales Order/Invoice Lines tab, the following new fields are to be added in Amounts section.

2.1 Tax Amount

2.2 Gross Unit Price

2.3 Gross List Price

2.4 Gross Standard Price (in case of Purchase/Sales Orders)

2.5 Line Gross Amount

2.6 Edit Line Net Amount - flag used to allow the end-user to edit Line Net Amount field.

2.7 Edit Line Gross Amount - flag used to allow the end-user to edit Line Gross Amount field.

3. In Purchase/Sales Order/Invoice Lines tab, the following fields are to be renamed.

3.1 Unit Price as Net Unit Price

3.2 List Price as Net List Price

3.3 Standard Price as Net Standard Price (in case of Purchase/Sales Orders)

4. In Purchase/Sales Invoice Lines tab, the Line Net Amount field is to be modified as editable one based on Edit Line Net Amount flag and it must be moved to Amounts section.

5. If Edit Line Net Amount is checked, then it is normal OB behavior(means considered as Net Amount Invoice). So the user can able to enter/edit Line Net Amount field and choose the Tax. Based on these inputs, the System must calculate the following.

5.1 Tax Amount= Line Net Amount * VAT Rate/100

5.2 Line Gross Amount= Line Net Amount + Tax Amount

5.3 Gross Unit Price= Net Unit Price + (Net Unit Price* VAT Rate/100)

6. If Edit Line Gross Amount is checked, then it means the invoice is considered as Gross Amount Invoice. So the user can able to enter/edit Line Gross Amount field and choose the Tax. Based on these inputs, the System must calculate the following.

6.1 Tax Amount= Line Gross Amount * VAT Rate/(100 + VAT Rate)

6.2 Line Net Amount= Line Gross Amount *100 / (100 + VAT Rate)

6.3 Gross Unit Price= Net Unit Price + (Net Unit Price* VAT Rate/100)

To Implement above functionalities (5 and 6), we need to write a callout that updates the Tax Amount, Line Gross Amount and Gross Unit Price, while Line Net Amount is edited and updates the Tax Amount, Line Net Amount and Net Unit Price, while Line Gross Amount is edited.

StringBuffer resultado = new StringBuffer();

    resultado.append("var calloutName='SL_Invoice_GrossNet_Amt';\n\n");
    resultado.append("var respuesta = new Array(");

    BigDecimal taxAmt, calculatedLineGrossAmt, calculatedLineNetAmt;
    BigDecimal LineGrossAmt = strLineGrossAmt.equals("") ? ZERO : new BigDecimal(strLineGrossAmt);
    BigDecimal LineNetAmt = strLineNetAmt.equals("") ? ZERO : new BigDecimal(strLineNetAmt);
    BigDecimal netUnitPrice = strPriceActual.equals("") ? ZERO : new BigDecimal(strPriceActual);
    BigDecimal netListPrice = strPriceList.equals("") ? ZERO : new BigDecimal(strPriceList);
    BigDecimal qtyInvoice = strQtyInvoice.equals("") ? ZERO : new BigDecimal(strQtyInvoice);

    SLInvoiceGrossNetAmtData[] data = SLInvoiceGrossNetAmtData.select(this, strTaxId, strInvoiceId);

    BigDecimal taxRate = data[0].rate.equals("") ? new BigDecimal(1)
        : new BigDecimal(data[0].rate);

    Integer taxScale = new Integer(data[0].priceprecision);

    BigDecimal grossUnitPrice = netUnitPrice.add(netUnitPrice.multiply(taxRate).divide(HUNDERED));
    BigDecimal grossListPrice = netListPrice.add(netListPrice.multiply(taxRate).divide(HUNDERED));

    if (strIncludeVAT.equalsIgnoreCase("Y")) {
      taxAmt = LineGrossAmt.multiply(taxRate).divide(HUNDERED.add(taxRate), 12,
          BigDecimal.ROUND_HALF_EVEN).setScale(taxScale, BigDecimal.ROUND_HALF_UP);

      LineNetAmt = LineGrossAmt.multiply(new BigDecimal("100").divide(HUNDERED.add(taxRate), 12,
          BigDecimal.ROUND_HALF_EVEN)).setScale(taxScale, BigDecimal.ROUND_HALF_UP);

      resultado.append("new Array(\"inplinenetamt\", " + LineNetAmt.toString() + "),");
      resultado.append("new Array(\"inptaxamt\", " + taxAmt.toString() + "),");
    } else {
      taxAmt = LineNetAmt.multiply(taxRate).divide(HUNDERED, 12, BigDecimal.ROUND_HALF_EVEN)
          .setScale(taxScale, BigDecimal.ROUND_HALF_UP);

      LineGrossAmt = LineNetAmt.add(taxAmt);

      resultado.append("new Array(\"inpemGnatLinegrossamt\", " + LineGrossAmt.toString() + "),");
      resultado.append("new Array(\"inptaxamt\", " + taxAmt.toString() + "),");
    }

    resultado.append("new Array(\"inpemGnatGrossunitprice\", " + grossUnitPrice.toString() + "),");
    resultado.append("new Array(\"inpemGnatGrosslistprice\", " + grossListPrice.toString() + ")");

    calculatedLineNetAmt = netUnitPrice.multiply(qtyInvoice);
    calculatedLineGrossAmt = calculatedLineNetAmt.add(taxAmt);
    if (log4j.isDebugEnabled()) {
      log4j.debug("calculatedLineNetAmt:" + calculatedLineNetAmt);
      log4j.debug("calculatedLineGrossAmt:" + calculatedLineGrossAmt);
    }

    if (!calculatedLineGrossAmt.equals(calculatedLineGrossAmt)
        || !calculatedLineNetAmt.equals(LineNetAmt)) {
      resultado.append(", new Array('MESSAGE', \""
          + Utility.messageBD(this, "GNAT_GROSSNET_AMOUNT_ERROR", vars.getLanguage()) + "\")");
    }

    resultado.append(");");
    xmlDocument.setParameter("array", resultado.toString());
    xmlDocument.setParameter("frameName", "appFrame");
    response.setContentType("text/html; charset=UTF-8");
    PrintWriter out = response.getWriter();
    out.println(xmlDocument.print());
    out.close();

7. There is no need write any business logic to update Total Net Amount and Total Gross Amount, as these value matches with sum of all invoice lines net amount and gross amounts.

8: Need to implement radio button logic at both Edit Line Net Amount and Edit Line Gross Amount as follows.

8.1 if Edit Line Net Amount is checked, then it means the invoice is Net Amount Invoice. So, Edit Line Gross Amount flag is unchecked and Line Gross Amountis set as an read-only field.

8.2 if Edit Line Gross Amount is checked, then it means the invoice is Gross Amount Invoice. So, Edit Line Net Amount flag is unchecked and Line Net Amountis set as an read-only field.

@Iseditlinenetamt@='N'

800px

8.2 if Include VAT is not checked, then it means the invoice is Net Amount Invoice. So, Line Gross Amount field is set to be read-only and Line Net Amountis set as an editable field.

@EM_GNAT_ISINCLUDEVAT@='Y'

800px

New database objects

New columns in C_INVOICELINE and C_ORDERLINE tables

• ISEDITLINENETAMT CHAR

1.1 Create a new column ISEDITLINENETAMT in C_ORDERLINE table.

ALTER TABLE C_ORDERLINE ADD ISEDITLINENETAMT CHAR(1) DEFAULT 'N';

1.2 Create a new column ISEDITLINENETAMT in C_INVOICELINE table.

ALTER TABLE C_INVOICELINE ADD ISEDITLINENETAMT CHAR(1) DEFAULT 'N';

• EM_GNAT_ISEDITLINEGROSSAMT CHAR

1.1 Create a new column EM_GNAT_ISEDITLINEGROSSAMT in C_ORDERLINE table.

ALTER TABLE C_ORDERLINE ADD EM_GNAT_ISEDITLINEGROSSAMT CHAR(1) DEFAULT 'N';

1.2 Create a new column EM_GNAT_ISEDITLINEGROSSAMT in C_INVOICELINE table.

ALTER TABLE C_INVOICELINE ADD EM_GNAT_ISEDITLINEGROSSAMT CHAR(1) DEFAULT 'N';

• EM_GNAT_LINEGROSSAMT NUMBER

1.1 Create a new column EM_GNAT_LINEGROSSAMT in C_INVOICELINE table.

ALTER TABLE C_INVOICELINE ADD EM_GNAT_LINEGROSSAMT NUMBER(10) NOT NULL ENABLE;

1.2 Create a new column EM_GNAT_LINEGROSSAMT in C_ORDERLINE table.

ALTER TABLE C_ORDERLINE ADD EM_GNAT_LINEGROSSAMT NUMBER(10) NOT NULL ENABLE;

• EM_GNAT_GROSSUNITPRICE NUMBER

1.1 Create a new column EM_GNAT_GROSSUNITPRICE in C_INVOICELINE table.

ALTER TABLE C_INVOICELINE ADD EM_GNAT_GROSSUNITPRICE NUMBER DEFAULT '0' NOT NULL ENABLE;

1.2 Create a new column EM_GNAT_GROSSUNITPRICE in C_ORDERLINE table.

ALTER TABLE C_ORDERLINE ADD EM_GNAT_GROSSUNITPRICE NUMBER DEFAULT '0' NOT NULL ENABLE;

• EM_GNAT_GROSSLISTPRICE NUMBER

1.1 Create a new column EM_GNAT_GROSSLISTPRICE in C_INVOICELINE table.

ALTER TABLE C_INVOICELINE ADD EM_GNAT_GROSSLISTPRICE NUMBER DEFAULT 0;

1.2 Create a new column EM_GNAT_GROSSLISTPRICE in C_ORDERLINE table.

ALTER TABLE C_ORDERLINE ADD EM_GNAT_GROSSLISTPRICE NUMBER DEFAULT 0;

• EM_GNAT_GROSSSTDPRICE NUMBER

Create a new column EM_GNAT_GROSSSTDPRICE in C_ORDERLINE table.

ALTER TABLE C_ORDERLINE ADD EM_GNAT_GROSSSTDPRICE NUMBER DEFAULT 0;

Application Dictionary Changes

Changes in Existing Fields

1. Change the Line Net Amount field as editable field.

2. Change the label of Unit Price as Net Unit Price

New Fields

Go to the table window and do import of columns from database, this will import the new column into the Application Dictionary.

1. New Field Line Gross Amount is to be added in Purchase/Sales Invoice-Lines window.

1.1 Correct/add column information if needed.

1.2 Add a new field Line Gross Amount to the window Purchase/Sales Invoice-Lines tab.

2. New Field Gross Unit Price is to be added in Purchase/Sales Invoice-Lines window.

2.1 Correct/add column information if needed.

2.2 Add a new field Gross Unit Price to the window Purchase/Sales Invoice-Lines tab.

3. New Field Line Gross Amount is to be added in Purchase/Sales Order-Lines window tab.

3.1 Correct/add column information if needed.

3.2 Add a new field Line Gross Amount to the window Purchase/Sales Order-Lines tab.

4. New Field Gross Unit Price is to be added in Purchase/Sales Order-Lines window tab.

4.1 Correct/add column information if needed.

4.2 Add a new field Gross Unit Price to the window Purchase/Sales Order-Lines tab.

To link the new columns and fields to a label (i.e. an Element) run the Synchronize Terminology process.

After running this process there should be a new Element record called Line Gross Amount and Gross Unit Price.

Reference

Messages

Extension Point setup

Reference Data

Processes

Open Discussion Items

Closed Discussion Items