Cash VAT Management/Technical Documentation
Contents |
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