TimingOfVatBooking/Technical Specification Timing of VAT Booking

Contents 1 Purpose 2 Introduction 3 Application Dictionary Changes 4 DB Changes 5 Accounting Template for VAT Posting 6 Accounting Template for VAT Posting - Advanced Payables and Receivables Compatible 6.1 Special considerations 6.2 Accounting entries example 7 Reference Data 8 Open Discussion Items 9 Closed Discussion Items

Purpose

The purpose of this document is to describe the technical Specifications for an extension module to be developed in OB ERP version 2.50 called Timings of VAT Booking.

Introduction

Application Dictionary Changes

NEW MODULE CREATION

Log in as "System Administrator" and Navigate to the below Application Path.

|| Application Dictionary || Module || Module

Module Name  : Timing of VAT Booking.

Java Package  : org.openbravo.obtvb

Version  : 1.0.0

Type  : Module

Trans.Required : True

License Type  : Openbravo Public License

DB PREFIX

Application Path : Application Dictionary || Module || Module >> DB Prefix

Name : OBTVB

DATA PACKAGE

Application Path : Application Dictionary || Module || Module >> Data Package

Name  : Timing of VAT Booking

Java Package : org.openbravo.obtvb

MODULE DEPENDENCY

Include the core as Dependent Module for Timing of VAT Booking.

Application Path : Application Dictionary || Module || Module >> Dependency

GENERAL PACKAGE STRUCTURE FOR OTHER UTILITY CLASS

• For Accounting Template
  • org.openbravo.obtvb.process

DB Changes

NEW COLUMN IN AD_ORG TABLES

The new column needs to be added as below in the ad_org table.

OBTVB_IsSettlementVAT char(1), DEFAULT IS 'N'.

This column is used to configure the system whether to use the Post VAT in settlement or not. If this flag is = true, then the posting of VAT for the Sales Invoice will be posted at the time of settlement. If the flag is = false, then the posting of VAT for Sales Invoice will be posted while posting the invoice itself.

UI Changes ( Configuration )

The system needs to be configured In-order to let the system to use the Default Tax Accounting(Invoice Tax Accounting) or New Tax Accounting (Settlement Tax Accounting).

The Configuration should be done at organization level, so that the tax setting will be used at Organization level.

The existing UI has been modified in order have the configuration changes on the UI as below.

General Setup || Enterprise || Organization || Organization

FUNCTIONAL BEHAVIOUR

• Existing Functional Behavior

• • Posting of the VAT for the sales invoice in current system will be done at the time of posting the Sales Invoice. There is not flag exists in the current system to configure the post vat in settlement. All the tax will be posted at the time of posting the Invoices, this is the current behavior.

• New Functional Behavior

• • The new functionality is required on the system to post the VAT tax at the time of Settlement. So, the functionality of the current system requires modification on the UI and DB as well.

As we have seen the db change(em_obtvb_issettlementvat) & UI change has to be modified in order to port the new functionality on the system.

So, to post the VAT at the time of settlement, system should be configured by checking "Post VAT in settlement" flag as true in the below Application Path.

General Setup || Enterprise || Organization || Organization

• CHANGES ON C_ACCTSCHEMA_DEFAULT TABLE

The new column called "EM_OBTVB_TRANSITORY_ACCT" should be added in the c_acctschema_default table for "Transitory Account". In the UI this column will be reflected in the below Application Path under the Tax Category Section.

Financial Management || Accounting || Setup || Accounting Schema || Accounting Schema >> Defaults

XML CODE SNIPPET

<column name="EM_OBTVB_TRANSITORY_ACCT" primaryKey="false" required="false" type="VARCHAR" size="32" autoIncrement="false">
<default/>
<onCreateDefault/>
</column>

• CHANGES ON C_TAX_ACCT TABLE

The new column called "EM_OBTVB_T_TRANSITORY_ACCT" needs be added in the C_TAX_ACCT table for "Transitory Account". In the UI this column will be reflected in the below Application Path.

Financial Management || Accounting || Setup || Tax Rate || Tax >> Accounting

XML CODE SNIPPET

<column name="EM_OBTVB_TRANSITORY_ACCT" primaryKey="false" required="false" type="VARCHAR" size="32" autoIncrement="false">
<default/>
<onCreateDefault><![CDATA[get_uuid()]]></onCreateDefault>
</column>

Accounting Template for VAT Posting

The new 2 Accounting Templates are needed to Implement the required new functionality and also support the existing functionality as below.

• 1. DocInvoiceVatPosting.java

• 2. DocPaymentVatPosting.java

1. DocInvoiceVatPosting.java

This accounting template will contain the functionality to support both current and new behavior of posting the invoices. If the Organization has been configured to use the "Post VAT @ settlement", the the posting of invoice will take place at the time of Settlement. If not, then the posting will be done at the time of posting the invoice itself.

JAVA CODE SNIPPET (DocInvoiceVatPosting.java)

public class DocInvoiceVatPosting extends DocInvoiceTemplate
{
public Fact createFact(AcctSchema as, ConnectionProvider conn, Connection con,
VariablesSecureApp vars) throws ServletException
{

if(isSettlementVat=true)
{
// Do the new functionality for posting the invoice
You code goes here......... 
}
else
{
// Do the current functional behavior  for posting the invoice.
You code goes here......... 
}
}
}

2. DocPaymentVatPosting.java

JAVA CODE SNIPPET (DocPaymentVatPosting.java)

public class DocPaymentVatPosting extends DocPaymentTemplate
{
public Fact createFact(AcctSchema as, ConnectionProvider conn, Connection con,
VariablesSecureApp vars) throws ServletException
{

if(isSettlementVat=true)
{
// Do the new functionality for posting the invoice
You code goes here......... 
}
else
{
// Do the current functional behavior  for posting the invoice.
You code goes here......... 
}
}
}

These 2 Accounting template classes needs to be configured as below.

• These 2 template class needs to be configured in the system, so then these classes will be mapped with appropriate table( c_invoice & c_settlement ). The template classes should be configured in the below application path.

Financial Management || Accounting || Setup || Accounting templates || Templates

DocInvoiceVatPosting

DocPaymentVatPosting

• Mapping the Template class with Accounting Schema Table

• • The Timing of VAT Booking feature requires the difference posting functionality, so the posting of the Sales Invoice has be different functional behavior, so the Accounting has been modified to fit the required functionality as below.

The new 2 template class has been defined for this functionality as mentioned above, those templates needs to be mapped with accounting schema table in the below Application Path.

• FOR INVOICE

• • Table  : c_invoice
  • Template class : DocInvoiceVatPosting

• FOR SETTLEMENT

• • Table  : c_settlement
  • Template class : DocPaymentVatPosting

Accounting Template for VAT Posting - Advanced Payables and Receivables Compatible

In order to support the new Advanced Payables and Receivables functionality, three new Java classes will be created:

• 1. DocFINPaymentVatPosting, which extends the DocFINPaymentTemplate class. It implements the logic for accounting Financial Payments. If the payment's organization is configured to "Post VAT on settlement", a new entry will be created moving the VAT amount from the temporal account to the final one. If the organization is not configured to Post VAT on settlement, the normal accounting logic will be used.

• 2. DocFINAccTransactionVatPosting, which extends the DocFINAccTransactionTemplate class. It implements the logic for accounting Financial Transactions. If the payment's organization is configured to "Post VAT on settlement", a new entry will be created moving the VAT amount from the temporal account to the final one. If the organization is not configured to Post VAT on settlement, the normal accounting logic will be used.

• 3. DocFINReconciliationVatPosting, which extends the DocFINReconciliationTemplate class. It implements the logic for accounting Financial Reconciliations. If the payment's organization is configured to "Post VAT on settlement", a new entry will be created moving the VAT amount from the temporal account to the final one. If the organization is not configured to Post VAT on settlement, the normal accounting logic will be used.

The user's configuration for these classes is exactly the same as the non-advanced-payables-and-receivables compatible shown in the previous section.

Special considerations

• A financial payment can be related to one or more invoices, or to one or more orders. These classes must loop through the payment details to find the invoice's taxes to account the correspondent tax amount. If no invoice is found, the order's taxes will be used.
• A financial payment can pay the whole invoice/order amount or just a percentage. In this case, the same percentage will be used to account the VAT amount. Example: an invoice with a total amount of 118 Euros (18 Euros VAT). A payment is receipt with half of the invoice's amount (59 Euros), then the system must create the accounting entry for the VAT with the 50% of the total VAT amount, in this case 9 Euros.
• A financial transaction and a financial reconciliation is made by payments, so the same logic used for the payments will be applied for them.

Accounting entries example

• Sales Invoice:
  • Total Amount: 40.60 €
  • VAT Amount: 5.60 €
  • Accounting entry (note 48000 has been configured as the temporal VAT account):

• Payment for previous invoice:
  • Total Amount: 40.60€
  • Accounting entry:

• After that, the correspondent transaction is posted:
  • Accounting entry:

Reference Data

DATASET

The new dataset will be created and packaged as part of the module. This dataset will contain new accounting template which is responsible for posting the VAT of Sales Invoice at the time settlement.

This Data Access Level of this dataset should be "System" level, So that this accounting template will be used at system level and widely available for all the client who wish to use this feature.

Open Discussion Items

1. In "Technical Constraints" section of Functional Document, there are two constraints mentioned.

1. Only one entry in xxx_bunit, could have "public"= true.
2. If public is true, consolidate is also true.

Since these constraints based on certain conditions on a a single record, it would not be feasible to use "CHECK" constraint. Rather it would be recommended to use trigger to ensure these conditions.

It should be captured in Technical Documentation.

2. In DATABASE / TABLE CHANGE REQUIREMENT section, the columns

VATTAXNUMBER VARCHAR 60, NOT NULL
TRADEREGISTERNUMBER VARCHAR 60, NULL 

are defined. Is there any business justification for having NULL/NOT NULL constraints in these columns?

3. As we are dealing with posting of Sales Invoice in ( Settlement window ).

• Is there any impact on Hiding/Disabling the Post button of Sales Invoice ?
• Does the automatic posting process will post the Sales Invoice ?
• How can we protect auto posting of Sales Invoice from AcctServer Process ? : Raj - 20-Oct-2009 : 7.35 PM

4. How to protect the "Sales Invoice" in Bank Statement ?

• Timing of VAT Booking requires the posting of Sales invoice in Settlement, But the sales invoice is visible for "Bank Statement" and "Settlement".

• How do we protect the Sales Invoice being visible in "Bank Statement" window, Is there any check needs to be done to protect the Sales Invoice being available at "Bank Statement" window ? Raj - 21-Oct-2009 : 12.22 AM

5. We have new table in functionality document as xxx_bunit. If it is not required in implementation, either it should be removed from functional document. So there should be matching with technical documentation and functional documentation. Sathiyan - 21-Oct-2009 : 1.50 PM

6. whether do we need to have extension points in the stored procedure or changes are required in stored procedure? If it is extension point where to insert it(before post or after post or hook way of doing).It is not explained in document. Sathiyan - 21-Oct-2009 : 1.50 PM

7. If we have some code snippets (not necessarily real code) what kind of changes are going to be at stored procedure, it will give some insights on work flow.Sathiyan - 21-Oct-2009 : 1.50 PM

8. How the Transitory Tax Account will be set(Configured) for Settlement Tax Accounting ? Raj : 21-OCT-2009 | 1.55 PM

9. We have only one field in the screen of organization(@postingInSettlement@='Y'). However we have two column in the column definition as 1. em_obtvb_actual character(1) NOT NULL DEFAULT 'N'::bpchar, 2. em_obtvb_aggregate character(1) NOT NULL DEFAULT 'Y'::bpchar. It is not clear. where is the one more field? what is the purpose of aggregate column. It is not covered in the document. Sathiyan - 21-Oct-2009 : 2 PM

10. From functional documentation:

The booking of VAT (for the CB parts of Universities) belonging to sales invoices could take place at one of two different points in the processing of a sales invoice.

This is to be controlled by a Invoice/Settlement Tax Accounting ("Soll/Ist-Versteuerung") switch.

• we have covered the second part in technical documentation through post in settlement flag. What about the first part? How do we distinguish CB parts of universities from non-CB parts of universities? Sathiyan - 21-Oct-2009 : 2.20 PM

Closed Discussion Items