TimingOfVatBooking/Technical Specification Timing of VAT Booking
Contents |
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.
- Only one entry in xxx_bunit, could have "public"= true.
- 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