Cash VAT Management/Technical Documentation Manual Settlement

Contents 1 Introduction 2 Changes in Core 2.1 Database 2.1.1 Tables 2.1.2 Views 2.2 Application Dictionary 2.3 Java 3 Changes in Cash VAT module 3.1 Database 3.1.1 Tables 3.1.2 Indexes 3.1.3 Triggers 3.2 Application Dictionary 3.3 Java 3.4 Module Scripts 3.5 Other topics

Introduction

The Cash VAT management development is split in Core and an external module called Cash VAT Management, that can be installed from the Central Repository.

All the business logic is directly implemented in Core, and the external module only contains the User Interface artifacts (Tabs and Fields). This is an easy way to hide this functionality for the clients that won't use it.

This document describes the technical specification for the new feature Manual Cash VAT Settlement available since Cash VAT Management 1.0.100 and Openbravo PR16Q1. For the general Cash VAT technical documentation please visit Cash_VAT_Management/Technical_Documentation

Changes in Core

Database

Tables

• Added new column C_INVOICETAX_CASHVAT.ISMANUALSETTLEMENT (char=1, required, default value=N). The records created in the C_INVOICETAX_CASHVAT table by the Manual Cash VAT settlement process will have this flag = Y

Views

• C_INVOICETAX_CASHVAT_V:
  • Added new column ISMANUALSETTLEMENT from C_INVOICETAX_CASHVAT table
  • Modified INNER JOIN by LEFT JOIN for FIN_PAYMENT table, since from now on it's not necessary for the records in the C_INVOICETAX_CASHVAT to be linked to a FIN_PAYMENT_ID

Application Dictionary

• Added field Manual Cash VAT Settlement for Purchase/Sales Invoice | Cash VAT tab

Java

• CashVATUtil.java:
  • Deprecated method createFactCashVAT(AcctSchema as, ConnectionProvider conn, Fact fact, String Fact_Acct_Group_ID, DocLineCashVATReady_PaymentTransactionReconciliation line, Invoice invoice, final String documentType, final String cCurrencyID, final String SeqNo). The cCurrencyID parameter is not really needed, so a new method with similar signature and without the cCurrencyID param has been created.
  • In the new createFactCashVAT() method, we have replaced all the occurrences of the cCurrencyID param by the invoice.getCurrency().getId().

Changes in Cash VAT module

Database

Tables

• Created new table OBCVAT_MANUALSETTLEMENT. Stores the Manual Cash VAT settlement headers
• Created new table OBCVAT_MANUALSETTLEMENTLINE. Stores the Manual Cash VAT settlement lines

Indexes

• Created new index in C_Tax table for ISCASHVAT column. We use this column to filter in multiple queries

Triggers

The triggers ensure coherent data in case the UI read only logic is bypassed somehow.

• New trigger OBCVAT_MANUALSETTLEMENT_TRG for updates and delete over the OBCVAT_MANUALSETTLEMENT table. Usual control over modifications and deletes in processed/posted documents.
• New trigger OBCVAT_MANUALSETTLINE_TRG for updates and delete over the OBCVAT_MANUALSETTLEMENTLINE table. Usual control over modifications and deletes in processed/posted documents.

Application Dictionary

This section describes only the important stuff related to the application dictionary.

• Created table OBCVAT_CashVATForSettlement_HQL based on HQL query. It will be in charge of the Manual Cash VAT Settlement Pick and Execute. It will show all the invoices with any Cash VAT amount pending to be settled.

It automatically calculates the pending percentage, tax amount and taxable amount per Cash VAT tax rate.

If the line is already included in the Manual Cash VAT Settlement, then the line is automatically selected.

• Created computed column in C_InvoiceTax_CashVAT_V.EM_OBCVAT_MANUALSETTLEMENTLINE_ID with the SQL query, as a way to browse from the Purchase/Sales Invoice | Cash VAT record to the Manual Cash VAT Settlement line.

Note that the OBCVAT_MANUALSETTLEMENTLINE.C_INVOICETAX_CASHVAT_ID is a unique index, so the query doesn't affect performance.

• Added Dataset OBCVAT_GridConfiguration (System Level) with some tweaks to avoid performance issues in grids

• Added Dataset OBCVAT_DocumentType (Client/Org) with the new Document Type for Manual Cash VAT Settlement and its sequence

• Since the Manual Cash VAT Settlement can contain both purchase and sales invoice at the same time, it's necessary to add the right configuration in AD_TABLE_NAVIGATION

Java

• CashVATManualSettlementHandler.java: extends from BaseProcessActionHandler. Creates the Manual Settlement lines from the selected records. It first deletes all the lines and then created the new ones based on the P&E selection
• ReactivateCashVATManualSettlementHandler.java: extends from BaseProcessActionHandler. Reactivates a Manual Cash VAT Settlement. User can select to delete the settlement lines or not
• CashVATForSettlementTransformer.java: extends from HqlQueryTransformer. Modifies the OBCVAT_CashVATForSettlement_HQL to filter by the Manual Cash VAT Settlement header values (date range, organizations, etc.) and sets the order by documentNo and invoiceDate
• DocLine_ManualSettlement.java: extends from DocLineCashVATReady_PaymentTransactionReconciliation, related to Accounting. Adds the possibility to get the Invoice from a given Manual Cash VAT Settlement line
• DocManualSettlementCashVATTemplate.java: abstract class to support Accounting Templates
• DocManualSettlementCashVAT.java: extends from AcctServer and it is in charge of the Manual Cash VAT Settlement document accounting
• OBCVAT_Utility.java: Utility class with public static methods.
• SE_ManualCashVATSettlement_Org.java: Callout to set the default organization's currency

Module Scripts

• CreateAccountingConfiguration.java: This new module script is in charge of inserting the accounting schema tables for new table in the module and creating the Period Control configuration for the new DocBaseType.

Other topics

• The only way to insert or delete the lines selected in the Manual Cash VAT Settlement document is using the P&E window.
• In the P&E window you can save the changes in the Manual Cash VAT Settlement lines by pressing OK. Besides, when clicking Complete, the lines are inserted and/or deleted and the document is immediately processed.
• The Manual Cash VAT Settlement is allowed to be reactivated. When reactivating, user has the possibility to keep the previous selection or to delete all the lines.
• When the Manual Cash VAT Settlement has any line, there are some fields in the header that becomes read only. Deleting the lines will set them back as editable