Cash VAT Management/Technical Documentation

Contents 1 Introduction 2 Database 2.1 New Columns 2.1.1 C_Tax 2.1.2 C_AcctSchema_Default 2.1.3 C_Tax_Acct 2.1.4 C_BPartner 2.1.5 AD_OrgInfo 2.1.6 C_Invoice 2.1.7 C_Order 2.2 New Tables 2.2.1 C_Invoice_Tax_CashVAT 2.3 Modify Triggers 2.3.1 C_Tax_TRG 2.3.2 C_InvoiceLine_TRG 2.4 Modify PL/SQL functions 2.4.1 C_Order_Post 2.4.2 C_Invoice_Post 2.4.3 C_GetTax 2.5 New PL/SQL functions 2.5.1 C_GetTax_Override_CashVAT 3 Java 3.1 Modify Callouts 3.1.1 SE_Invoice_BPartner 3.1.2 SE_Order_BPartner 3.1.3 SL_Invoice_Product 3.1.4 SL_Order_Product 3.1.5 SL_Invoice_Amt 3.1.6 SL_Order_Amt 3.2 New Callouts 3.2.1 SE_Invoice_Organization 3.2.2 SE_Order_Organization 3.2.3 SE_Organization_NotTaxDeductable 3.2.4 SE_TaxRate_Withholding 3.3 Modify Java processes 3.3.1 InitialSetupUtility.java 3.3.2 FIN_PaymentProcess.java 3.4 Accounting engine 3.4.1 Modify DocInvoice 3.4.2 Modify DocFINPayment 3.4.3 DocFINFinAccTransaction 3.4.4 DocFINReconciliation 4 Open Topics 4.1 How and when to create the Invoice Cash VAT documents? 4.2 Is it necessary to modify the Orders Accounting? 4.3 Is it necessary to modify the Payment, Transaction or Reconciliation accounting?

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.

Database

New Columns

C_Tax

Column	Datatype	Nullable	Default	On Create Default	Foreign Key	Foreign table	AD Reference	Description	Other Info
IsCashVAT	CHAR(1 BYTE)	No	No	No	No		YesNo	Cash VAT Tax Rate	Show only when Tax Deductible=Y

C_AcctSchema_Default

Column	Datatype	Nullable	Default	On Create Default	Foreign Key	Foreign table	AD Reference	Description
T_Due_Trans_Acct	CHARACTER VARYING(32 BYTE)	Yes	Null	Null	Yes	C_ValidCombination	Valid Combination Selector	Transitory account for Cash VAT operations (Due)
T_Credit_Trans_Acct	CHARACTER VARYING(32 BYTE)	Yes	Null	Null	Yes	C_ValidCombination	Valid Combination Selector	Transitory account for Cash VAT operations (Credit)

C_Tax_Acct

Column	Datatype	Nullable	Default	On Create Default	Foreign Key	Foreign table	AD Reference	Description
T_Due_Trans_Acct	CHARACTER VARYING(32 BYTE)	Yes	Null	Null	Yes	C_ValidCombination	Valid Combination Selector	Transitory account for Cash VAT operations (Due)
T_Credit_Trans_Acct	CHARACTER VARYING(32 BYTE)	Yes	Null	Null	Yes	C_ValidCombination	Valid Combination Selector	Transitory account for Cash VAT operations (Credit)

C_BPartner

Column	Datatype	Nullable	Default	On Create Default	Foreign Key	Foreign table	AD Reference	Description	Other Info
IsCashVAT	CHAR(1 BYTE)	No	No	No	No		YesNo	Cash VAT Tax Rate	Show only in Vendor tab

AD_OrgInfo

Column	Datatype	Nullable	Default	On Create Default	Foreign Key	Foreign table	AD Reference	Description	Other Info
IsCashVAT	CHAR(1 BYTE)	No	No	No	No		YesNo	Cash VAT Tax Rate	Show only for legal entities

C_Invoice

Column	Datatype	Nullable	Default	On Create Default	Foreign Key	Foreign table	AD Reference	Description	Other Info
IsCashVAT	CHAR(1 BYTE)	No	No	No	No		YesNo	Cash VAT Tax Rate	Inherit the value from C_BPartner in Purchase Invoice. Editable Inherit the value from AD_OrgInfo in Sales Invoice. Editable

C_Order

Column	Datatype	Nullable	Default	On Create Default	Foreign Key	Foreign table	AD Reference	Description	Other Info
IsCashVAT	CHAR(1 BYTE)	No	No	No	No		YesNo	Cash VAT Tax Rate	Inherit the value from C_BPartner in Purchase Order. Editable Inherit the value from AD_OrgInfo in Sales Order. Editable

New Tables

C_Invoice_Tax_CashVAT

• Stores the information that represents the percentage and amount of the invoice's taxes paid or collected in a payment schedule detail.
• Displayed in a tab called Cash VAT Lines under:
  • Purchase Invoice | Taxes
  • Purchase Invoice | Payment Plan | Payment Schedule Details
  • Sales Invoice | Taxes
  • Sales Invoice | Payment Plan | Payment Schedule Details

Column	Datatype	Nullable	Default	Foreign Key	Foreign table	AD Reference	Description	Other Info
C_Invoice_Tax_CashVAT_ID	VARCHAR2(32 BYTE)	No	get_uuid()	No		ID
AD_CLIENT_ID	VARCHAR2(32 BYTE)	No		Yes	AD_Client	TableDir
AD_ORG_ID	VARCHAR2(32 BYTE)	No		Yes	AD_Org	TableDir
ISACTIVE	CHAR(1 BYTE)	No	Yes	No		YesNo
CREATED	DATE	No	now()	No		DateTime
CREATEDBY	VARCHAR2(32 BYTE)	No		No		Search
UPDATED	DATE	No	now()	No		DateTime
UPDATEDBY	VARCHAR2(32 BYTE)	No		No		Search
C_INVOICETAX_ID	VARCHAR2(32 BYTE)	No		Yes	C_INVOICETAX	TableDir		The C_Tax_ID can be reached through the c_invoicetax table
PERCENTAGE_TAX	NUMERIC	No		No		Amount	Stores the percentage of the c_invoicetax paid with this line
AMOUNT_TAX	NUMERIC	No		No		Amount	Stores the amount of the c_invoicetax paid with this line
FIN_Payment_ScheduleDetail_ID	VARCHAR2(32 BYTE)	Yes		Yes	FIN_Payment_ScheduleDetail	TableDir	Filled when the line comes from a FIN_Payment.
C_Invoice_CashVAT_EndYear_ID	VARCHAR2(32 BYTE)	Yes		No		TableDir	Filled when the line comes from a C_Invoice_CashVAT_EndYear

Modify Triggers

C_Tax_TRG

• This trigger is in charge of populating the tax rate accounts based on the general ledger default configuration. Since we have added the two new transitory accounts for register the Cash VAT, we need to modify the trigger to fill also both accounts only in case of Cash VAT tax rates

C_InvoiceLine_TRG

• Modify this trigger to set the invoice header's cash VAT flag based on the inserted/updated C_Tax_ID. This way we ensure the invoices created from massive process keep on working with the new cash VAT taxes.

Modify PL/SQL functions

C_Order_Post

• Add a validation that checks only Cash VAT tax rates have been included into the lines when the header's Cash VAT flag is set to Yes.

• Add a validation that checks no Cash VAT tax rates have been included into the lines when the header's Cash VAT flag is set to No.

C_Invoice_Post

• Add a validation that checks only Cash VAT tax rates have been included into the lines when the header's Cash VAT flag is set to Yes.

• Add a validation that checks no Cash VAT tax rates have been included into the lines when the header's Cash VAT flag is set to No.

C_GetTax

• This function is in charge of getting the default tax rate for the following parameters: p_product_id, p_shipdate, p_org_id, p_warehouse_id, p_billbpartnerloc_id, p_shipbpartnerloc_id, p_project_id, p_issotrx and p_glitem_id.

• Since we allow to override the default Cash VAT configuration for the business partner and organization into Orders and Invoices, we can't modify this function to discriminate using the default configuration. To fix it we should pass a new parameter to discriminate based on the order's/invoice's cash vat flag, however this will break the API.

• On the other hand, modifying this function is not needed, because the best way to manage it is:
  • Purchase flows: through the Business Partner Tax Category. The Cash VAT tax rates should include an independent Business Partner Tax Category that should be associated to the Cash VAT business partners.
  • Sales flows: through the organization's Cash VAT flag. In the C_GetTax function we receive the ad_org_id as a parameter, so we can know the Cash VAT setting.

• The only scenario not directly support is the exceptions in orders/invoices where the user changes the value of the Cash VAT flag in the header. In this case, the way to control it is to modify the Product callouts in orders and invoices to check whether it's an exception or not.

In case of an exception, the callout will call a new pl/sql function (c_gettax_override_cashvat), which will be based on the c_gettax, but that will override the organization/business partner cash VAT configuration. Otherwise the behaviour will be the same. This way we ensure we don't break the API in any manner.

New PL/SQL functions

C_GetTax_Override_CashVAT

• New PL/SQL function based on the C_GetTax that will receive a new parameter forcedCashVAT, which represents whether we must override the business partner/organization default cash VAT configuration by this value.

• This function will be called by the SL_Invoice_Product and SL_Order_Product callouts when they detect a cash VAT exception in the invoice or the order respectively

Java

Modify Callouts

SE_Invoice_BPartner

• Inherit the business partner's Cash VAT flag to the invoice only for purchase invoices

SE_Order_BPartner

• Inherit the business partner's Cash VAT flag to the order only for purchase orders

SL_Invoice_Product

• Call the new C_GetTax_Override_CashVAT ps/sql function in case the invoice is a cash VAT exception (the business partner cash VAT flag is different from the one declared at the purchase invoice header or the organization cash VAT flag is different from the one declared at the sales invoice header)

SL_Order_Product

• Call the new C_GetTax_Override_CashVAT ps/sql function in case the order is a cash VAT exception (the business partner cash VAT flag is different from the one declared at the purchase order header or the organization cash VAT flag is different from the one declared at the sales order header)

SL_Invoice_Amt

• Checks whether the selected-by-the-user tax rate matches the cash vat type declared at the header. If it doesn't match, show a warning message informing the user that the cash VAT declared into the header will be changed to match this Cash VAT type at saving time. Remember that later on, when processing the document, we will check again whether all the document's lines have the same Cash VAT type.

SL_Order_Amt

• Checks whether the selected-by-the-user tax rate matches the cash vat type declared at the header. If it doesn't match, show a warning message informing the user that the cash VAT declared into the header will be changed to match this Cash VAT type at saving time. Remember that later on, when processing the document, we will check again whether all the document's lines have the same Cash VAT type.

New Callouts

SE_Invoice_Organization

• Inherit the organization's Cash VAT flag to the invoice only for sales invoices

SE_Order_Organization

• Inherit the organization's Cash VAT flag to the order only for sales orders

SE_Organization_NotTaxDeductable

• Unset the Organization | Information -> Cash VAT flag when Tax Not Deductible flag is set to Yes

SE_TaxRate_Withholding

• Unset the Tax Rate -> Cash VAT flag when Withholding Tax Rate flag is set to Yes

Modify Java processes

InitialSetupUtility.java

• The process of importing Chart of Accounts files must support the new transitory Cash VAT accounts.
• The insertAcctSchemaDefault() method should add 2 new if clauses to support the T_DUE_TRANS_ACCT and T_CREDIT_TRANS_ACCT default accounts.
• Chart of Account documentation should reflect this change

FIN_PaymentProcess.java

• If any of the invoices paid/collected in the payment is a cash VAT invoice, the process will automatically create the correspondent records in the C_Invoice_Tax_CashVAT table
• However, the process should first look at the C_Invoice_Tax_CashVAT table to find an exception record (to manage 31/12 scenario) for the payment's invoice(s) entered before the payment's date. In this case, the process won't create a new C_Invoice_Tax_CashVAT.

Accounting engine

The usage of the Cash VAT transitory accounts forces us to modify the accounting engine:

Modify DocInvoice

• Class in charge of invoice's accounting
• When a Cash VAT invoice is posted, the tax amounts should go the transitory Cash VAT accounts instead of the normal ones
• When a Non Cash VAT invoice is posted, the behaviour is the current one.
• Example TBD

Modify DocFINPayment

• Class in charge of payment's accounting
• If a payment is posted (depending on the payment method configuration), and it contains a line linked to a cash VAT invoice, the process will move the correspondent amount (based on the percentage of the invoice paid or collected) from the transitory cash VAT accounts to the final ones.
• However, the process should first look at the C_Invoice_Tax_CashVAT table to find an exception record (to manage 31/12 scenario) for the selected invoice(s) entered before the payment's date. In this case, the process won't modify the accounting.
• Example TBD

DocFINFinAccTransaction

• Class in charge of financial account transaction's accounting
• If a transaction is posted (depending on the payment method configuration), and it contains a payment with a line linked to a cash VAT invoice, the process will move the correspondent amount (based on the percentage of the invoice paid or collected) from the transitory cash VAT accounts to the final ones.
• However, the process should first look at the C_Invoice_Tax_CashVAT table to find an exception record (to manage 31/12 scenario) for the selected invoice(s) entered before the payment's date. In this case, the process won't modify the accounting.
• Example TBD

DocFINReconciliation

• Class in charge of financial account reconciliation's accounting
• If a reconciliation is posted (depending on the payment method configuration), and it contains a payment with a line linked to a cash VAT invoice, the process will move the correspondent amount (based on the percentage of the invoice paid or collected) from the transitory cash VAT accounts to the final ones.
• However, the process should first look at the C_Invoice_Tax_CashVAT table to find an exception record (to manage 31/12 scenario) for the selected invoice(s) entered before the payment's date. In this case, the process won't modify the accounting.
• Example TBD

Open Topics

How and when to create the Invoice Cash VAT documents?

VMA: we have different possibilities to automatically create the document:

• Hook in FIN_PaymentProcess
• EventObserver in FIN_Payment
• Accounting Post-Process
• Trigger

VMA: We need also to decide when the document should be created (and this will give us the way to implement it). For example, shall we create the document when the payment is created or posted, or when the transaction is created or posted, or when the reconciliation is created or posted? Alternatively, should we create a configuration in the Payment Method to configure this behaviour?

ANSWER: The C_Invoice_Tax_CashVAT records will be created when processing the payment. However the accounting entries that represents the movement of the cash VAT amounts from the transitory accounts to the final ones will be executed when the payment/transaction/reconciliation is posted

Is it necessary to modify the Orders Accounting?

ANSWER: No, it is not necessary

Is it necessary to modify the Payment, Transaction or Reconciliation accounting?

VMA: I don't think it's necessary since these documents don't take taxes into account

ANSWER: It's necessary to move the cash VAT amounts from the transitory accounts to the final ones in the same moment where the payment, transaction or reconciliation have been posted