Projects:Tax As Expenses Revenue/Technical Documentation
Contents |
Summary
This development will be released as a core enhancement, more than in a module.
The objective of the development is to allow user to chose, when posting a purchase or sales invoice, for each of the lines, whether to use (1) the VAT deductible/liabilities accounts defined in the tax rate definition, or (2) the expense/revenue accounts associated to the product of the invoice line.
The first behavior is the one existing currently in the application. The second behavior is the one to be implemented in this project. In order to follow one behavior or the other one, this conditions are checked:
Next table shows when a behavior will be taken, and in which cases the other one:
| Organization Not tax deductable flag | Tax Rate Not tax deductable flag | Tax Rate Tax deductable flag | Tax amounts posted as |
| Not Active | Not Active | Not Active | VAT deductible/liabilities |
| Active | Not Active | Not Active | Product Expenses |
| Any | Active | Not Active | Product Expenses |
| Any | Not Active | Active | VAT deductible/liabilities |
Current algorithm for posting invoices
When an invoice is processed, all the taxing information of the invoice is stored in the c_invoicetax table. For each different tax rate present in any line of the invoice, a new line is added to c_invoicetax table. At the same time, taxing information of individual lines is also stored in the c_invoicelinetax table, the same way taxing information of invoices is stored in c_invoicetax table.
Whenever a purchase/sales invoice is posted, DocInvoice.java is called. In the DocInvoice.loadDocumentDetails function all information necessary to post the document is gathered. In order to obtain the taxing information of the invoice, DocInvoice.loadTaxes() is called.
DocInvoice.loadTaxes() retrieves from database all the taxing information of the invoice, stored in the c_invoicetax table. In order to do that, calls DocInvoiceData.select function. All this information is stored in DocInvoice.m_taxes array declared at a class level.
After retrieving all necessary information from database through DocInvoice.loadDocumentDetails, DocInvoice.createFact function is called. This function contains all the logic that creates the lines in the Fact_Acct table. Several document types are posted executing this function: API, ARI, APC and ARC. An if statement while execute one piece of code or the other according to the document type, although all of them have got a similar algorithm.
ARI (lines 274-321)
posts customer receivables part of the entry (lines 278-295)
posts taxing part of the entry (lines 296-305)
posts revenues part of the entry (lines 306-312)
ARC (lines 323-366)
posts customer receivables part of the entry (lines 326-343)
posts taxing part of the entry (lines 344-352)
posts customer revenues part of the entry (lines 353-357)
API (lines 368-415)
posts vendor liabilities part of the entry (lines 371-391)
posts taxing part of the entry (lines 392-400)
posts expenses part of the entry (lines 401-413)
APC (lines 417-458)
posts vendor liabilities part of the entry (lines 419-436)
posts taxing part of the entry (lines 437-445)
posts expenses part of the entry (lines 446-459)
Obviously, in this development we are going to pay attention to the taxing part of the entries. Actually, the way this part is managed is quite easy: as explained before, DocInvoice.m_taxes array stores all the information about the taxing of the invoice. This array is gone through, and for each of the elements a new row is added in the Fact_Acct table through the Fact.createLine function. Let's see this with one example:
A Purchase Invoice with 2 lines is processed:
Line 1- Base amount: 100, Tax Rate: 7%.
Line 2- Base amount: 100, Tax Rate: 7%.
Line 3- Base amount: 100, Tax Rate: 16%.
While processing, two new lines are added to the c_invoicetax table:
tax base amount: 200.00; tax amount: 14.00;
tax base amount: 100.00; tax amount: 16.00;
and three more to the c_invoicelinetax table:
line: 1; tax base amount: 100.00; tax amount: 7.00;
line: 2; tax base amount: 100.00; tax amount: 7.00;
line: 3; tax base amount: 100.00; tax amount: 16.00;
When posting the invoice, the createFact function will create this two lines in the Fact_Acct table, according to the information contained in c_invoicetax table:
account: tax debit account of 7% tax rate; debit amount: 14.00
account: tax debit account of 16% tax rate; debit amount: 16.00
The purpose of this development is to change this behavior, so the account to use is not taken from the tax rate accounting definition, but from the product accounting definition. As the product is at a line level, c_invoicelinetax table will be used (currently is not needed). This will be implemented in the calculation of the taxing part of the entry in the createFact function.
Development description
Database modifications
New column in ad_orginfo table:
Name: IsTaxUndeductable
Type: Character(1)
Nullable: No
Default Value: 'N'
OnCreateDefault Value: 'N'
New column in ad_orginfo table:
Name: C_Tax_Id
Type, Nullable, Default Value, ... : The same than C_Tax_Id in C_InvoiceLine
Foreign key: c_tax.c_tax_id
New column in C_Tax table:
Name: IsTaxDeductable
Type: Character(1)
Nullable: No
Default Value: 'N'
OnCreateDefault Value: 'N'
Application Dictionary modifications
New checkbox in Organization window -> Organization Information tab:
New Element: IsOrgTaxUndeductable
Name and display name: Is Not Tax Deductable Organization.
Description: If active, the purchase invoices of this organization will post the taxes amounts against the product expenses account, and sales invoices will always have got the exempt tax rate set in the Sales Invoices Exempt Tax Rate field. Nevertheless, there exists two flags in tax rate definition Is Tax Deductable and Is Not Tax Deductable that will override this flag, and will always post taxes amounts against VAT deductible/liabilities account and product expenses account respectively.
Help: same than description.
Length: 1
Reference: YesNo
Default value: 'N'
Updateable: Yes
All the rest of checkboxes: No
Development status: Ready
New combo in Organization window -> Organization Information tab:
New Element: SalesTaxExemptRate
Name and display name: Sales Invoices Exempt Tax Rate
Description: Tax Rate to be used in all sales invoices belonging to this Organization (in except of tax rates included in sales invoices, that have got the Is Tax Deductable flag active).
Help: Same than description.
Display Logic:@Istaxundeductable@='Y'
Validation SalesTaxExemptRate(need to setup in aplication dictionary):(C_TAX.SOPOTYPE = 'S' OR C_TAX.SOPOTYPE = 'B') AND C_TAX.ISTAXEXEMPT = 'Y' AND C_TAX.AD_CLIENT_ID=@AD_CLIENT_ID@
We have to add new column of C_Tax and its Element. We also add new field (checkbox) in Tax Rate window -> Tax tab: Column configuration looks like below:
Length: 1
Reference: YesNo
Default value: 'N'
Updateable: Yes
All the rest of checkboxes: No
Development status: Ready
Code Modifications
- DocTax.java class must include two new boolean defined at a class level and these values must be set in a new class constructor (clone of the existing one, but with a new parameter), so these lines must be added:
// m_isTaxUndeductable will be used for public organization. public boolean m_isTaxUndeductable = false; // m_isTaxDeductable will be used for commercial organization in intracommunity. public boolean m_isTaxDeductable = false;
old code was in constructor
public DocTax(String C_Tax_ID, String name, String rate, String taxBaseAmt, String amount) {
m_C_Tax_ID = C_Tax_ID;
m_name = name;
m_rate = rate;
m_amount = amount;
} // DocTax
Constructor should be changed like below:
public DocTax(String C_Tax_ID, String name, String rate, String taxBaseAmt, String amount) {
this(C_Tax_ID, name, rate, taxBaseAmt, amount, false, false);
} // DocTax
public DocTax(String C_Tax_ID, String name, String rate, String taxBaseAmt, String amount,
boolean isUndeductable, boolean isTaxDeductable) {
m_C_Tax_ID = C_Tax_ID;
m_name = name;
m_rate = rate;
m_amount = amount;
m_isTaxUndeductable = isUndeductable;
m_isTaxDeductable = isTaxDeductable;
} // DocTax
- DocInvoice_data.xsql must be modified, so the select function will retrieve as well the information about the posting of the tax rate: as a tax, or as an expense/revenue, "Not Tax Undeductable" flag, "Is Tax Deductable" flag. select statement is modified (lines 25-44) from:
SELECT it.C_Tax_ID, t.Name, t.Rate, it.TaxBaseAmt, it.TaxAmt, ... FROM C_Tax t, C_InvoiceTax it WHERE t.C_Tax_ID=it.C_Tax_ID AND it.C_Invoice_ID=?
to:
SELECT t.IsTaxUndeductable as rateTaxUndeductable, (select IsTaxUndeductable from ad_org o
where o.ad_org_id=it.ad_org_id)
as orgTaxUndeductable, it.C_Tax_ID, t.Name, t.Rate, t.IsTaxDeductable, ...
FROM C_Tax t, C_InvoiceTax it
WHERE t.C_Tax_ID=it.C_Tax_ID AND it.C_Invoice_ID=?
furthermore, a new sql will be added, with name selectProductAcct, and three parameters (cAcctSchemaId, cTaxId and cInvoiceId). Will return multiple rows:
select a.p_revenue_acct, a.p_expense_acct, t.taxamt from c_invoicelinetax t, c_invoiceline l, m_product p, m_product_acct a where t.c_invoiceline_id = l.c_invoiceline_id and l.m_product_id = p.m_product_id and p.m_product_id = a.m_product_id and a.c_acctschema_id = ? and t.c_tax_id= ? and t.c_invoice_id= ?
"" AS P_REVENUE_ACCT,"" AS P_EXPENSE_ACCT will be added in the first sql method of docInvoice_data.xsql to generate variable for the above method.
- DocInvoice.loadTaxes(), when retrieving taxing information from database, must save this new value in the DocTax element. In the for statement located in lines 171-180 is run for each of the c_taxrates elements obtained in database. A new line will be added to obtain new value:
boolean isTaxDeductable = false;
boolean isTaxUndeductable = ("Y".equals(data[i].ratetaxundeductable))
|| ("Y".equals(data[i].orgtaxundeductable));
if ("Y".equals(data[i].orgtaxundeductable)) {
/*
* override isTaxUndeductable flag if any tax line level override for intracommunity public
* organization
*/
if ("Y".equals(data[i].istaxdeductable)) {
isTaxUndeductable = false;
isTaxDeductable = true;
}
} else {
// configured for intracommunity with tax liability
if ("Y".equals(data[i].istaxdeductable)) {
isTaxDeductable = true;
}
}
DocTax taxLine = new DocTax(C_Tax_ID, name, rate, taxBaseAmt, amount, isTaxUndeductable,
isTaxDeductable);
- DocInvoice.createFact() performs the required actions to have the invoice posted. Part of this actions are related to the taxing part of the invoice (for all the possible 4 different document types). This is currently implemented with a for statement that goes through the DocInvoice.m_taxes array, and posts each of the entries of this array. The first statement of the for will now be an if that will check whether this tax must be posted as expenses/revenues, or as a deductable tax. If it's a deductable tax, then it behaves as currently; otherwise, new logic will be added:
for (int i = 0; m_taxes != null && i < m_taxes.length; i++) {
if (m_taxes[i].m_isUndeductable) {
... code to post non-deductable taxes ...
} else {
... current code ...
}
}
If the tax is a non-deductable one, then existing lines in c_invoicelinetax table associated to that rate are retrieved using the newly created DocInvoiceData.selectProductAcct function. It has got three parameters, and all of them are available in the java: C_Invoice_Id is available in the Record_ID class parameter of DocInvoice, c_Tax_Id is available in m_taxes[i].m_C_Tax_ID and c_AcctSchema_Id is available in as.getC_AcctSchema_ID().
This is what implementations of the code to post non-deductable taxes is:
DocLine docLine = new DocLine(DocumentType, Record_ID, ""); docLine.m_C_Tax_ID = m_taxes[i].m_C_Tax_ID; execute DocInvoiceData.selectProductAcct. For each of the returned elements do: fact.createLine(...);
the createLine call will be different depending on the document type, assuming that in a variable called data[] is stored the result of the execution of DocInvoiceData.selectProductAcct:
Old code was in API:
fact.createLine(docLine, m_taxes[i].getAccount(DocTax.ACCTTYPE_TaxCredit, as, conn),
this.C_Currency_ID, m_taxes[i].getAmount(), "", Fact_Acct_Group_ID, nextSeqNo(SeqNo),
DocumentType, conn);
New code will be in API:
if (m_taxes[i].m_isTaxUndeductable) {
DocInvoiceData[] data = null;
data = DocInvoiceData.selectProductAcct(conn, as.getC_AcctSchema_ID(),
m_taxes[i].m_C_Tax_ID, Record_ID);
for (int j = 0; j < data.length; j++) {
fact.createLine(docLine, Account.getAccount(conn, data[j].pExpenseAcct),
this.C_Currency_ID, data[j].taxamt, "", Fact_Acct_Group_ID, nextSeqNo(SeqNo),
DocumentType, conn);
}
} else {
if (m_taxes[i].m_isTaxDeductable) {
fact.createLine(docLine, m_taxes[i].getAccount(DocTax.ACCTTYPE_TaxLiability, as, conn),
this.C_Currency_ID, m_taxes[i].getAmount(), "", Fact_Acct_Group_ID,
nextSeqNo(SeqNo), DocumentType, conn);
} else {// If Tax rate is not configured with any parameter
fact.createLine(docLine, m_taxes[i].getAccount(DocTax.ACCTTYPE_TaxCredit, as, conn),
this.C_Currency_ID, m_taxes[i].getAmount(), "", Fact_Acct_Group_ID,
nextSeqNo(SeqNo), DocumentType, conn);
}
}
New code in APC
for (int i = 0; m_taxes != null && i < m_taxes.length; i++) {
// New docLine created to assign C_Tax_ID value to the entry
DocLine docLine = new DocLine(DocumentType, Record_ID, "");
docLine.m_C_Tax_ID = m_taxes[i].m_C_Tax_ID;
if (m_taxes[i].m_isTaxUndeductable) {
DocInvoiceData[] data = null;
data = DocInvoiceData.selectProductAcct(conn, as.getC_AcctSchema_ID(),
m_taxes[i].m_C_Tax_ID, Record_ID);
for (int j = 0; j < data.length; j++) {
fact.createLine(docLine, Account.getAccount(conn, data[j].pExpenseAcct),
this.C_Currency_ID, "", data[j].taxamt, Fact_Acct_Group_ID, nextSeqNo(SeqNo),
DocumentType, conn);
}
} else {
if (m_taxes[i].m_isTaxDeductable) {
fact.createLine(docLine, m_taxes[i].getAccount(DocTax.ACCTTYPE_TaxLiability, as, conn),
this.C_Currency_ID, "", m_taxes[i].getAmount(), Fact_Acct_Group_ID,
nextSeqNo(SeqNo), DocumentType, conn);
} else {// If Tax rate is not configured with any parameter
fact.createLine(docLine, m_taxes[i].getAccount(DocTax.ACCTTYPE_TaxCredit, as, conn),
this.C_Currency_ID, "", m_taxes[i].getAmount(), Fact_Acct_Group_ID,
nextSeqNo(SeqNo), DocumentType, conn);
}
}
}
ARI: fact.createLine(docLine, data[i].p_revenue_acct, this.C_Currency_ID, "", data[i].taxamt, Fact_Acct_Group_ID, nextSeqNo(SeqNo),DocumentType, conn);
New code in ARI
for (int i = 0; m_taxes != null && i < m_taxes.length; i++) {
// New docLine created to assign C_Tax_ID value to the entry
DocLine docLine = new DocLine(DocumentType, Record_ID, "");
docLine.m_C_Tax_ID = m_taxes[i].m_C_Tax_ID;
if (m_taxes[i].m_isTaxUndeductable) {
DocInvoiceData[] data = null;
data = DocInvoiceData.selectProductAcct(conn, as.getC_AcctSchema_ID(),
m_taxes[i].m_C_Tax_ID, Record_ID);
for (int j = 0; j < data.length; j++) {
fact.createLine(docLine, Account.getAccount(conn, data[j].pRevenueAcct),
this.C_Currency_ID, "", data[j].taxamt, Fact_Acct_Group_ID, nextSeqNo(SeqNo),
DocumentType, conn);
}
} else {
if (m_taxes[i].m_isTaxDeductable) {
fact.createLine(docLine, m_taxes[i].getAccount(DocTax.ACCTTYPE_TaxLiability, as, conn),
this.C_Currency_ID, "", m_taxes[i].getAmount(), Fact_Acct_Group_ID,
nextSeqNo(SeqNo), DocumentType, conn);
} else {// If Tax rate is not configured with any parameter
fact.createLine(docLine, m_taxes[i].getAccount(DocTax.ACCTTYPE_TaxDue, as, conn),
this.C_Currency_ID, "",m_taxes[i].m_amount, Fact_Acct_Group_ID,
nextSeqNo(SeqNo), DocumentType, conn);
}
}
}
ARC: fact.createLine(docLine, data[i].p_revenue_acct, this.C_Currency_ID, data[i].taxamt, "", Fact_Acct_Group_ID, nextSeqNo(SeqNo),DocumentType, conn);
New code in ARC
for (int i = 0; m_taxes != null && i < m_taxes.length; i++) {
// New docLine created to assign C_Tax_ID value to the entry
DocLine docLine = new DocLine(DocumentType, Record_ID, "");
docLine.m_C_Tax_ID = m_taxes[i].m_C_Tax_ID;
if (m_taxes[i].m_isTaxUndeductable) {
DocInvoiceData[] data = null;
data = DocInvoiceData.selectProductAcct(conn, as.getC_AcctSchema_ID(),
m_taxes[i].m_C_Tax_ID, Record_ID);
for (int j = 0; j < data.length; j++) {
fact.createLine(docLine, Account.getAccount(conn, data[j].pRevenueAcct),
this.C_Currency_ID, data[j].taxamt, "", Fact_Acct_Group_ID, nextSeqNo(SeqNo),
DocumentType, conn);
}
} else {
if (m_taxes[i].m_isTaxDeductable) {
fact.createLine(docLine, m_taxes[i].getAccount(DocTax.ACCTTYPE_TaxLiability, as, conn),
this.C_Currency_ID, m_taxes[i].getAmount(), "", Fact_Acct_Group_ID,
nextSeqNo(SeqNo), DocumentType, conn);
} else {// If Tax rate is not configured with any parameter
fact.createLine(docLine, m_taxes[i].getAccount(DocTax.ACCTTYPE_TaxDue, as, conn),
this.C_Currency_ID, m_taxes[i].getAmount(), "", Fact_Acct_Group_ID,
nextSeqNo(SeqNo), DocumentType, conn);
}
}
}
Trigger in AD_ORGINFO table
Trigger name= AD_ORGINFO_TRG
Table=AD_ORGINFO
Event= for each row on insert, update and delete
BEGIN
IF AD_isTriggerEnabled()='N' THEN RETURN;
END IF;
IF(INSERTING OR UPDATING) THEN
IF((:NEW.ISTAXUNDEDUCTABLE = 'Y') AND (:NEW.C_TAX_ID is null OR :NEW.C_TAX_ID=))THEN
RAISE_APPLICATION_ERROR(-20000, '@PublicOrganizationTaxExemptRateCheck@') ;
END IF;
END IF;
END AD_ORGINFO_TRG
changes in c_gettax method
-- If Not Tax Deductable(public) organization for sales transaction
--then we have to return configured sales tax rate which is exempt true
IF(p_IsSOTrx = 'Y') THEN
SELECT MAX(C_TAX_ID)
INTO v_TaxID
FROM AD_ORGINFO
WHERE ISTAXUNDEDUCTABLE='Y' AND AD_ORG_ID = p_org_id;
IF(v_TaxID IS NOT NULL) THEN
RETURN v_TaxID;
END IF;
END IF;
Trigger on C_Tax in tax rate screen
Tax Rate with NOT Tax deductable and IS Tax deductable flag won't be true at the same time. We have to protect this through trigger on C_Tax table. We need to throw error message is it not so configured. We have to include message in the AD for this purpose.
--isTaxUndeductable and isTaxDeductable should not be true at the same time.
IF(:NEW.ISTAXUNDEDUCTABLE = 'Y' AND :NEW.ISTAXDEDUCTABLE='Y') THEN
RAISE_APPLICATION_ERROR(-20000, '@InvalidCombinationOfTaxDeductableFlag@') ;
END IF;