Toolbox

The purpose of this document is to explain how existing "Invoice Taxes Report" must be enhanced to help the users to manually fill in and submit some Tax Reports such as few Spanish ones.

"Invoice Taxes Report" enhancements are going to be implemented as a new commercial module (extension module) which will require OB Commercial License as a new report named "Multidimensional Tax Report".

The "Invoice Tax Report" enhancements described in this document could be extensible to others countries regardless this analysis is based on the Spanish Tax Requirements and the Spanish "GPS" team input, because the main goal of those enhancements is to allow the end-user to exploit the tax information registered in the system.

Overview and Justification

There are two types of Tax Reports:

1) “Statement” Tax Reports.

There are some tax reports that are complex to fill-in and submit to the tax authorities because they need to retrieve or take into account data from many sources besides the tax records saved in the system.

At the same time, the information required while being generated is also complex and cannot be easily handled by what we call a “Multidimensional Tax Report”. "Multidimensional Tax Report" concept is explained later.

This is the case of M-347, M-349, M-340 tax reports.

These reports must be implemented through the Tax Report Launcher, therefore those ones can be generated as a valid file to be submitted to the tax authorities in the AEAT web-site.

2) “Settlement” Tax Reports.

These reports are not so complex than the previous ones because its main purpose is to get a tax settlement based on tax records saved in the system.

These reports can be handle by using a “Multidimensional Tax Report” which can help the end-user to get the basis information in order to fill-in manually these tax reports in the AEAT web-site.

This is the case of M-303 and M-110 tax reports, M-303 is a VAT settlement tax report and M-110 is a Withtholding settlement tax report (of some kind of withholdings).

In addition, the "Multidimensional Tax Report" could also help the end-user to manually fill in few other tax reports that are not planned to be delivered now as part of the next version of the SPLP such as M-190, (which is the M-110 anual version) M-390 (which is the -303 annual version); and few others such as M-115, M-216, M-123 withholding tax reports. Anyway, an additional research is required to better understand if they can be filled-in manually by the end-user by using the "Multidimensional Tax Report" or not and how.

Section 2 : Scope

The approach Localization team is going to take in order to get (2) "Settlement Tax Reports" is to enhance the standard Openbravo "Invoice Taxes Report" as a Multidimensional Tax Report.

Invoice Taxes Report can be found now at the application path: Financial Management // Receivable & Payables // Analysis Tools // Invoice Taxes Report.

New Report could be found at the application path:Financial Management // Receivable & Payables // Analysis Tools // Multidimensional Tax Report.

Section 3 - Functional Requirements

Prior to any Functional Requirement definition, it is important to highlight that:

Tax Rates such as a VAT, which are the taxes calculated and posted when an Invoice is posted:
- can be setup at the application path: "Financial Management || Accounting || Setup || Tax Rate || Tax"
- and can either be purchase or sales tax type and therefore will be shown in the Multidimensional Tax Report as either part of the Sales/Receipt Transactions or the Purchase/Payments Transactions.

Withholding Rates (or Withholding on Invoice) calculated and posted when an Invoice or a Manual Settlement is posted:
- can be setup at the application path: "Financial Management || Accounting || Setup || Tax Rate || Tax", setup as "Withholding Tax".
- and can either be purchase or sales tax type and therefore will be shown in the Multidimensional Tax Report as either part of the Sales/Receipt Transactions or the Purchase/Payments Transactions.

Withholdings (or Withholding on Settlement) calculated and posted when an Invoice or a Manual Settlement is settle and posted:
- this type can be setup at the application path: "Master Data Management || Business Partner Setup || Withholding || Withholding" and once created can be:
  - assigned to a Vendor BP at the application path: Master Data Management || Business Partner || Business Partner >> Withholding
    - In this scenario, this type of withholdings can only be purchase type and therefore will be shown in the Multidimensional Tax Report just a part of the Purchase/Payments Transactions
  - and linked to a GL item at the application path: Financial Management || Accounting || Setup || G/L Item ||
    - In this scenario, this type of withholdings can be used either while creating or creating/posting a Manual Settlement, or while posting a Bank Statement or a Cash Journal.

Overall, there are 2 possible setup:

End-user setting up and using Tax and Withholding Rates for purchase/sales invoices.
End-user setting up and using Tax Rates for purchase/sales invoices and Withholdings just for purchase transactions.
Withholdings on payment can be used for rest of document types (Manual Settlements, ...)

Above means it is not likely end-user is going to use both Withholding Rates and Withholdings within the same Entity.

As described in above sections the Functional Requirements of this feature implementation are a List of "Invoice Taxes Report" enhancements in order to get a Multidimensional Tax Report:

Functional Requirement 1 - Tables

"Invoice Taxes Report" data will be retrieved from the management tables listed below (Not from the accounting table FACT_ACCT) :

c_invoice
c_invoicetax
c_debt_payment
c_bankstatmentline
c_cashline
gl_journalline
c_debt_payment_balancing

Functional Requirement 2 - Data

Overall It is important to remark that G/L items do not linked to either a "Withholding Tax" or a "Withholding" will not be taken into account.

Therefore either Bank Statement/Cash Journals do not linked to a Payment but to a G/L item will not be taken into account unless those G/L items are linked to either a Withholding Tax or a Withholding.

Besides, an in order to get reliable tax data we need to take into account below documents/transactions:

AP/AR Invoices & AP/AR Credit Memos used to post received and issued invoices/credit memos, which could contain:
- Taxes such as VAT and Withholding Taxes, in the Invoice Line field named "Tax"
  - Note = 1 above should be captured at Invoice posting (Tax tab), for more info see Use Case 1
- Withholdings, in the Invoice Header
  - Note = 2 above should be capture at Settlement (Cancelled Payments tab), for more info see Use Case 2

Manual Settlement used to create transactions to be settle not coming from an Invoice, which could contain:
- A G/L item linked to a "Tax Rate" (we can not think on a real scenario for this) and/or a G/L item linked to a "Withholding Rate", for more info see Use Case 3.
- A G/L item linked to a "Tax Rate" (we can not think on a real scenario for this) and/or a G/L item linked to a "Withholding", for more info see Use Case 4.

Bank Statement used to reconcile bank statements, which could contain:
- An Invoice linked to a "Withholding Rate"
  - Note = 1 above should be captured at Invoice Posting (Tax Tab), for more info see Use Case 1.
- An Invoice linked to a "Withholding"
  - Note = 2 above should be captured at Settlement (Cancelled Payments tab), for more info see Use Case 2.
- A Manual Settlement linked to a "Withholding Rate"
  - Note = 3 above should be captured at Manual Settlement Posting, for more info see Use Case 3.
- A Manual Settlement linked to a "Withholding"
  - Note = 4 above should be captured at Settlement (Cancelled Payments tab), for more info see Use Case 4.
- A G/L item linked to either a "Tax Rate" (we can not think on a real scenario for this) or a "Withholding Rate" or a "Withholding"
  - Note = 5 above should be captured at Settlement (Cancelled Payments tab), for more info see Use Case 5

Cash Journal used to manage company's cash transactions, which could contain:
- An Invoice linked to a "Withholding on Invoice" (Cash Type = Debt-Payment)
  - Note = 1 above should be captured at Invoice Posting (Tax Tab), for more info see Use Case 1.
- An Invoice Payment linked to a "Withholding on Settlment" (Cash Type = Debt-Payment)
  - Note = 2 above should be captured at Settlement (Cancelled Payments tab), for more info see Use Case 2
- A Manual Settlement linked to a "Withholding Rate" (Cash Type = Debt-Payment)
  - Note = 3 above should be captured at Manual Settlement Posting, for more info see Use Case 3.
- A Manual Settlement linked to a "Witholding" (Cash Type = Debt-Payment)
  - Note = 4 above should be captured at Settlement (Cancelled Payment tab), for more info see Use Case 4.
- A G/L item linked to either a "Tax Rate" (we can not think on a real scenario for this) or a "Withholding Rate" or a "Withholding"
  - Note = 5 above should be captured at Settlement (Cancelled Payments tab), for more info see Use Case 5

Settlements in case of payment cancellation including a withholding coming from a Cash Journal or a Bank Statement.

Remittance in case of a payment/payments including withholding to be collected in a Remittance (application path: Financial Management // Receivables & Payables // Transactions // Remittance) the corresponding withholding must be post at the time/date those payments are cancelled which means set to "Paid=Yes"; the fact of collecting payments into a remittance will not imply any withholding posting because that do not implies any payment/receipt but just a payment management.

Payment Status Management does not get any impact on this development as payment management do not imply those payments are paid and that is the key for us to take into account in case of payments containing a withholding; only when those are set to Paid=Yes will be taken into account.
- In case payment management means that the payment is moved or settle against a different Business Partner, that one will be taken into account instead of the original one set up at Invoice level.

It is important to remark that in case of "Tax Payment Settlement" end user should follow below steps:

Create a G/L Journal to post the corresponding Tax Settlement (4720 vs 4770 account in case of VAT, by example)
Create a G/L item named "Tax Payment Settlement" linked to 4750 account and do not linked to any Tax or Withholding Rate or Withholding
Settle that G/L item through a Bank Statement or Cash Journal (Bank Statement or Cash type = G/L item), see Use Case 5.

Functional Requirement 3 - Accounting Date

Transaction/Document Accounting date must be the date to take into account while retrieving tax transactions (*) see FR4 - Existing filters.

Functional Requirement 4 - Filters

There are a few new filters to be implemented:

Invoice Tax Reports has the following filter parameters:

Filter	Type	Mandatory	Description	New Requirements
From Date (*)	Date	YES	"From Date" to be entered by the end-user, system will retrieve tax transactions taken into account the "From Date" entered by the end-user.	Document/Transaction Accounting Date is the date to be taken into account.
To Date (*)	Date	YES	"To Date" to be entered by the end-user, system will retrieve tax transactions taken into account the "To Date" entered by the end-user.	Document/Transaction Accounting Date is the date to be taken into account.
Organization	Selector	YES	"Organization" to be selected by the end-user from a reference list, system will retrieve tax transactions for the Organization selected.	none
Show Invoice info	Check	NO	In case the end-user selects this check box below information must be displayed: Invoice Number Invoice Date Business Partner	This check must be renamed as: "Show Detail Info" as it should also allow the end user to get information about: Accounting Date Invoice Total Amount BP Country BP Region Besides: Invoice Number must be renamed as "Doc No" Invoice Date must be renamed as "Doc date"
Purchase Transaction	Radio Button	YES	In case the end-user selects this check system will retrieve tax transactions linked to purchase transactions	This check must be renamed as: "Purchase/Payment Transaction" because not only invoices must be taken into account but also payments
Sales Transaction	Radio Button	YES	In case the end-user selects this check system will retrieve tax transactions linked to sales transactions	This check must be renamed as: "Sales/Receipt Transaction" because not only invoices must be taken into account but also receipts
Both Transaction	Radio Button	YES	In case the end-user selects this check system will retrieve tax transactions regardless its type.	none

Following new filters are required to be implemented additionally, as shown in the screen below, (including the existing ones to be changed):

Filter	Type	Mandatory	Description
Tax Withholding	Radio Button	YES	Two options to split between Tax or Withholding. In case end-user select "Tax" - the system will retrieve tax transactions linked to any of the Tax Rates setup at application path: "Financial Management \|\| Accounting \|\| Setup \|\| Tax Rate \|\| Tax" which are NOT setup as "Withholding Tax"; therefore Tax Rate selector will be prompt. In case end-user selects "Withholding", the system will retrieve tax transactions linked to Tax Rates setup as withholdings or Withholding, depending on Withholding selectors described below.
Tax	Selector	NO	In case the end-user selects "Tax" option in the "Tax or Withholding" radio button, system will allow the end user to select a Tax Rate; otherwise any Tax Rate will be taken into account while retrieving tax transactions.
Withholding on Invoice	Selector	NO	In case the end-user selects a "Withholding on Invoice" or "Withholding rates", system will retrieve tax transactions linked to that specific tax Rates setup as Withholding Rates; otherwise system will retrieve tax transaction regardless its Withholding rate or type.In case end-user selects a Withholding on Invoice rate, "Withholding on Settlement" selector will be set to not editable.
Withholding on Settlement	Selector	NO	In case the end-user selects a "Withholding on Settlement" tax rate, system will retrieve tax transactions linked that specific withholding; otherwise system will retrieve tax transactions regardless its Withholding rate or type.In case end-user selects a Withholding on Settelment rate, "Withholding on Invoice" selector will be set to not editable.
Business Partner	Multiple Selector	NO	This multiple selector will allow the end-user to select a specific group of BP’s, therefore the system will retrieve the tax transactions linked to them. If this filter is blank system will retrieve tax transactions regadless the BP/BPs those tax transactions are linked to.
Group by Business Partner	Check	NO	It this check is marked the report will show the tax information grouped by BP.

Functional Requirement 5 - Report Layout

The current tax information displayed is not clear enough and it does not help the end-user to manually filled-in tax reports; therefore output layout must be re-designed the way it is described below:

Report - Header

Invoice Taxes Report must clearly state the transaction type for which tax information/transactions is being retrieved, therefore Header section must show any of the tax transaction types listed below:

Sales/Receipts
Purchase/Payments

Report - Lines

Multidimensional Tax Report must contain below information at lines level per each Tax or Withholding Rate, in case "Show Details info" is checked:

Column	Type	Content
Name	Text	Tax or Withholding Name
Rate	Number	Tax Rate or Withholding rate in %
Doc No	Text	Invoice number in case of invoice transactions, document number in case of payments/receipts
Doc Type	Text	Document type: AP/AR invoice, Manual Settlement, etc..
Doc Date	Date	Invoice date in case of invoice transactions, document date in case of payments/receipts
Acct Date	Date	Accounting date
BP	Text	Business Partner
BP Country	Text	Business Partner Country
BP Region	Text	Business Partner Region
Taxable Amount (to be renamed as "Tax Base Amount")	Number	Tax Base Amount
Tax Amount	Number	Tax or Withholding Amount
Total Amount	Number	Total amount including tax and/or Withholding

Report - Sub-Totals

Above "Number" type columns but "Rate" must be sum-up in an additional report line in order to get below Sub-Totals:

Total Tax Base Amount
Total Tax Amount
Total Amount

Report - Totals per Tax Category

Multidimensional Tax Report must also show under Sub-Totals line and before the next Report Summary section a "Totals" section per Tax Category.

End-user will be able to get information about:

The Summed-up Tax Base Amount, Tax Amount and number of BP per each Tax Category; split by Tax Rate (%), which means in the case of Spain:

Tax Categories = VAT16%, VAT16%Services, VAT16%RealEstate, VAT7%, VAT7%Services, VAT7%RealEstate, VAT4%, VAT4%Services
Tax Rates =
- VAT Rates = 16%, 7%, 4%, 0,5%, 1%, 4% and 0%
- Withholding Rates = 7%, 15%, 19% and 24%

Tax Category / Tax Name	Rate	Tax Base Amt	Tax Amt
VAT 16%	16
IVA 16% Import	16
IVA 16% National Purchase	16
IVA 16% National Purchase (RE 4%)	4
....others...
VAT 7%	7
IVA 7% Import	7
IVA 7% National Purchase	7
IVA 7% National Purchase (RE 1%)	1
....otros...

New!!! = There should also be a Business Partner summary per tax/withholding rate %, therefore end-user is informed about the number of BP related to a specific rate.

This information is useful for the Spanish tax report 110 in which the Number of BP related to a specific type of withholdings (either employment incomes or economics/professional services) must be submitted.

Report - Totals per Tax/Withholding rate (%)

Regardless the value selected in the "Tax or Withholding" radio button, Multidimensional Tax report must show below summary information at the end of the report, grouped first by Tax or Withholding Rate (including Tax Rate setup as "Withholding Tax" and "Withholdings") and besides by Business Partner in case the end-user selects that Filter:

Column	Type	Content
Name	Text	Tax or Withholding Name
Rate	Number	Tax or Withholding Rate
Total Tax Base Amount	Number	Total Tax Base amount per Tax or Withholding Rate
Tax Amount	Number	Total tax amount
Total Amount	Number	Total amount including Tax or Withholding amounts.
Number of BP	Number	Total sum of BP with the specific Tax or Withholding Rate (if a BP has several invoices, for example, with the same withholding the BP counts only once).

Report - Lines

Multidimensional Tax Report must contain below information at lines level per each Tax or Withholding Rate, in case "Show Deno Taxtails info" is NOT checked:

Column	Type	Content
Name	Text	Tax or Withholding Name
Rate	Number	Tax or Withholding Rate
Tax Base Amount	Number	Tax Base amount per Tax or Withholding Rate
Tax Amount	Number	Total tax amount per Tax or Withholding Rate
Total Amount	Number	Total amount including Tax or Withholding amounts.
Number of BP	Number	Total Sum of BP with the specific Tax or Withholding Rate (if a BP has several invoices, for example, with the same withholding the BP counts only once).

Multidimensional Tax Report must be export to Excel format by checking the corresponding "Menu" button"

Functional Requirement 6 - Tax and Withholding management

Tax and Withholding management as either sales/purchase rates in case of not Invoice related transactions:

Withholdings are going to be handle as purchase/payment withholding tax.
Tax and Withholding Rates are going to be handle as purchase/payment tax in case of Purchase Tax type being setup.
Tax and Withholding Rates are going to be handle as sales/receipt tax in case of Sales Tax type parameter being setup.

Uses Cases

Use Case 1 - A Purchase/Sales Invoice including Tax Rate/s such as VAT and Withholding Rates

In this scenario the end-user needs to enter in the system a Purchase Invoice coming from a vendor/supplier (same would apply to Sales Invoices) containing VAT and/or Withholding Rates, for getting that done end-user should follow below steps:

Go to Procurement Management // Transactions // Purchase Invoice // Header and create a new purchase invoice (Transaction Document = AP invoice)
Go to Lines, create a new line by entering a services and make sure field Tax get a Tax Rate including VAT and a Withholding tax rate, by example "Prestación de Servicios Nacional 16% - 15% R"
Once the Purchase Invoice is completed/posted, both the VAT and the Withholding amount are calculated and posted and can be found in the Tab = Tax
Puchase Invoice Posting should look like:

250.00 Expense (60000)	252.50 Vendor (40000)
40.00 VAT (47200)	37.50 Withholding (47510)

For getting the invoice paid, end user could use either a Bank Statement or a Cash Journal.
Once any of those is posted and the corresponding "Settlement - Cancellation Payment" is created by the system, below posting must be shown:

252.50 Pending (55500)	252.50 Bank (57200)
252.50 Vendor (40000)	252.50 Pending (55500)

For this scenario both VAT and Withholding must be taken into account and therefore shown in the Multidimensional Tax Report by the time the Invoice is posted, which means the Invoice Accounting Date.

Same applies to Sales Invoices.

Use Case 2 - A Purchase Invoice including Tax Rate/s such as VAT and Withholding on Settlement

In this scenario the end-user needs to enter in the system a Purchase Invoice coming from a vendor/supplier containing VAT and/or a Withholding, for getting that done end-user should follow below steps:

Go to Master Data Management || Business Partner Setup || Withholding || Withholding and create a new withholding, by example "Withholding 15%"
Go to Master Data Management || Business Partner || Business Partner and select a vendor and make sure that vendor is linked to "Withholding 15%" under "Withholding" tab.
Go to Procurement Management // Transactions // Purchase Invoice // Header and create a new purchase invoice (Transaction Document = AP Invoice)
Go to Lines, create a new line by entering a service and make sure field Tax get a Tax Rate including just VAT, by example "Adquisiciones IVA 16%"
Go back to Purchase - Header, field "Withholding" and select "Withholding 15%"
Once the Purchase Invoice is completed/posted, only VAT amount is calculated and posted and can be found in the Tab = Tax
Purchase Invoice Posting should look like:

250.00 Expense (60000)	290.00 Vendor (40000)
40.00 VAT (47200)

For getting the invoice paid, end user could use either a Bank Statement or a Cash Journal.
Once any of those is posted and the corresponding "Settlement-Cancellation Payment" is posted, below posting must be shown:

252.50 Pending (55500)	252.50 Bank (57200)
290.00 Vendor (44000)	252.50 Pending (55500)
	37.50 Withholding (457100)

For this scenario:

VAT amount (40.00) must be taken into account and therefore shown in the Multidimensional Tax Report at the time the Invoice is posted, which means by the Invoice Accounting Date.
but Withholding amount (-37.50) must be taken into account and therefore shown in the Multidimensional Tax Report at the time of payment settlement, which means by the Bank Statement or Cash Journal Accounting Date.
New!! It is important to remark that Invoice Total Amount should have into account VAT and Withholding, which means Invoice Total Amount = Payment or Settlement Amount.

Use Case 3 - A Manual Settlement including a G/L item linked to a "Tax Rate" and a G/L item linked to a "Withholding Rate".

Direct Posting = Yes.

In this scenario the end-user needs to enter in the system a Payment/Receipt which could contain either a tax rate (VAT and/or Withholding Tax Rate).

It is important to highlight that Sales/Purchase Invoices must be entered in the system in below application paths:
Procurement Management || Transactions || Purchase Invoice || Header - as AP Invoices/Credit Memos Document Types
Sales Management || Transactions || Sales Invoice || Header - as AR Invoices/Credit Memos Document Types
regardless Payments/Receipts can be manually entered as Manual Settlements

It is also important that:

Payments must be entered as "Positive" Manual Settlements by do not selecting "Receipt" flag and by getting VAT as a debit amount and Withholdings as credit amount.
- In case of Payments - VAT and Withholding must be shown in the Multidimensional Tax Report for Purchase/Payment transactions as described below:
- VAT - positive
- Withholding - negative
Receipts must be entered as "Positive" Manual Settlements by selecting "Receipt" flag and by getting VAT as a credit amount and Withholdings as debit amount.
- In case of Receipts - VAT and Withholding must be shown in the Multidimensional Tax Report for Sales/Receipt transactions as described below:
- VAT - positive
- Withholding - negative

Based on the above End-user could follow below steps in case of a payment (it will work the other way around in case of a receipt):

Create the corresponding G/L items by navigating to Financial Management // Accounting // Setup // G/L item
There should be at least below G/L items:
- Name = Expense - Account = 6000
- Name = VAT - Tax = "Prestacion Servicios 16% - 15%R (16%)- Account = 4720
- Name = Withholding Tax Rate - Tax = "Prestacion Servicios 16% - 15%R (-15%) - Account = 4750
- Name = Pending Balance - Account = 5550

After that end-user must enter a Manual Settlement by navigating to Financial Management // Receivables and Payables // Transactions // Manual Settlement
Once there end-user must create a new one by entering both the "Transaction Date" and the "Accounting Date";
At "Create Payment" tab:
- end user selects a BP
- enters amount (by example 1010.00)
- removes "Receipt" flag as this is a payment to a supplier or vendor
- and marks "Direct Posting"

At "Balance Payment" tab:
- end user enters G/L item Expense = Debit Amount = 1000.00
- enters G/L item VAT = Debit Amount = 160.00 (linked to a Tax Rate = 16%)
- and enters G/L item Withholding Tax Rate = Credit Amount = 150.00 (linked to a Withholding Rate = -15%)
Go Back to Create Payment Tab and enters next to Direct Posting flag the G/L item "Pendientes" (5550)
Once the Manual Settlement is processed and posted, posting must look like:

1000.00 Expense (6000)	1010.00 Pending (5550)
16.00 VAT (4720)	150.00 Withholding (4750)

For this scenario:

VAT amount (160,00) and Withholding amount (-150.00) must be taken into account and therefore shown in the Multidimensional Tax Report by the time the Manual Settlement is posted, which means by the Manual Settlement Accounting Date

Tax base amount (1.000,00) will be calculated taking into account the Tax % and the Tax Amount entered by the end-user and same applies to withholding amount.

After the above:

Then end-user needs to take the manual settlement into a Bank Statement or Cash Journal as a Payment.
Once the Bank Statement is completed and post, and the corresponding "Settlement-Cancellation Payment" is post; below posting must be shown:

1010.00 Pending (5550)	1010.00 Bank (5720)
1010.00 Pending (5550)	1010.00 Pending (5550)

Direct Posting = No.

End-user needs to be aware that in this case both the VAT and the Withholding tax are not posted and therefore shown in the report by the time Manual Settlement is processed but by the time the corresponding Settlement is posted in the application path: Financial Management || Receivables & Payables || Transactions || Settlement || Settlement .

End-user must follow below steps:

Create the corresponding G/L items by navigating to Financial Management // Accounting // Setup // G/L item
There should be at least below G/L items:
- Name = Expense - Debit/Credit Account = 6000
- Name = VAT - Tax = "Prestacion Servicios 16% - 15%R (16%) - Debit/Credit Account = 4720
- Name = Withholding Tax Rate - Tax = "Prestacion Servicios 16% - 15%R (-15%) - Debit/Credit Account = 4750
- Name = Pending Balance - Debit/Credit Account = 5550
After that end-user must enter a Manual Settlement by navigating to Financial Management // Receivables and Payables // Transactions // Manual Settlement
Once there end-user must create a new one by entering both the "Transaction Date" and the "Accounting Date"

At "Create Payment" tab:
- end user selects a BP
- enters Amount (by example 1010.00)
- removes "Receipt" flag as this is a payment to a supplier or vendor
- and do NOT mark "Direct Posting"

At "Balance Payment" tab:
- end user enters G/L item Expense = Debit Amount = 1000.00
- enters G/L item VAT = Debit Amount = 160.00 (linked to a tax rate = 16%)
- and enters G/L item Withholding = Credit Amount = 150.00 (linked to a withholding = -15%)
Go Back to Manual Settlement Tab and process
- No transaction must be shown in the report.

After the above:

Then end-user needs to take the manual settlement into a Bank Statement or Cash Journal as a Payment. Process and Post the bank statement, below posting will be shown:

1010.00 Pending (5550)

1010.00 Bank (5720)

No transaction must be shown in the report.

Once the corresponding "Settlement-Cancellation Payment" is posted in the application path: Financial Management || Receivables & Payables || Transactions || Settlement || Settlement , below posting will be shown which means that both VAT and Withholding rate are posted at settlement:

1000.00 Expense (6000)	1010.00 Pending (5550)
160.00 VAT (4720)	150.00 Withholding (4751)

For this scenario:

VAT amount (160,00) and Withholding amount (-150.00) will be taken into account and therefore shown in the Multidimensional Tax Report by the time the payment is cancelled or settle, which means by the Bank Statement or Cash Journal Accounting Date.

Tax base amount (1.000,00) will be calculated taking into account the Tax % and the Tax Amount entered by the end-user and same applies to withholding amount.

Use Case 4 - A Manual Settlement including a G/L item linked to a "Tax Rate" and a G/L item linked to a "Withholding".

Direct Posting = Yes.

It is important to highlight that Sales/Purchase Invoices must be entered in the system in below application paths:

Procurement Management || Transactions || Purchase Invoice || Header - as AP I nvoices/Credit Memos Document Types
Sales Management || Transactions || Sales Invoice || Header - as AR Invoices/Credit Memos Document Types

regardless Payments/Receipts can be manually entered as Manual Settlements

It is also important that:

Payments must be entered as "Positive" Manual Settlements by do not selecting "Receipt" flag and by getting VAT as a debit amount and Withholdings as credit amount.
- In case of Payments - VAT and Withholding must be shown in the Multidimensional Tax Report for Purchase/Payment transactions as described below:
- VAT - positive
- Withholding - negative

It is not possible to enter "Receipts" linked to a withholding as those ones do only work on Purchase/Payment transactions.

Based on the above End-user could follow below steps in case of a payment (it will have to work the other way around in case of a receipt):

Create the corresponding G/L items by navigating to Financial Management // Accounting // Setup // G/L item
There should be at least below G/L items:
- Name = Expense - Accounting = 6000
- Name = VAT - Tax = "Prestación Servicios 16%" - Accounting =4720
- Name = Withholding - Withholding = W15% - Accounting = 4751 - This time a withholding on settlement.
- Name = Pending - Accounting = 5550
After that end-user enters a Manual Settlement
Once there a new one must be created and end-user needs to enter both "Transaction Date" and "Accounting Date"
At "Create Payment" tab:
- End-user selects a BP
- Enters transaction amount (by example 1010.00)
- removes "Receipt" flag as this is a vendor payment
- and marks "Direct Posting"
At "Balance Payment" tab:
- end user selects G/L item Expense = Debit Amount = 1000.00
- enters G/L item VAT = Debit Amount = 160.00
- and enters G/L item Withholding = Credit Amount = 150.00
Go back to Create Payment tab and enters next to Direct Posting flag the G/L item Pendientes (5550)
Once the Manual Settlement is processed and posted, posting looks like:

1000.00 Expense (6000)	1010.00 Pending (5550)
160.00 VAT (4720)	150.00 Withholding (4751)

For this scenario:

VAT amount (160,00) and Withholding amount (-150.00) will be taken into account and therefore shown in the Multidimensional Tax Report by the time the Manual Settlement is posted, which means by the Manual Settlement Accounting Date
Tax base amount (1.000,00) will be calculated taking into account the Tax % and the Tax Amount entered by the end-user and same applies to withholding amount.

After the above:

Then end-user needs to take the manual settlement into a Bank Statement or Cash Journal as a Payment.
Once the Bank Statement is completed/posted, and the corresponding "Settlement - Cancellation Payment" is created by the system, posting should look like:

1010.00 Pending (5550)	1010.00 Bank (5720)
1010.00 Pending (5550)	1010.00 Pending (5550)

Direct Posting = NO.

End-user needs to be aware that in this case both the VAT and the Withholding will not be posted and therefore shown in the report by the time Manual Settlement is processed but by the time the corresponding Settlement is posted.

End user must follows below steps:

Create the corresponding G/L items by navigating to Financial Management // Accounting // Setup // G/L item
There should be at least below G/L items:
- Name = Expense - Accounting = 6000
- Name = VAT - Tax = "Prestación Servicios 16%" - Accounting =4720
- Name = Withholding - Withholding = W15% - Accounting = 4751
- Name = Pending - Accounting = 5550
After that end-user enters a Manual Settlement
Once there a new one must be created and end-user needs to enter both "Transaction Date" and "Accounting Date"
At "Create Payment" tab:
- End-user selects a BP
- Enters transaction amount (by example 1010.00)
- removes "Receipt" flag as this is a vendor payment
- and do NOT mark "Direct Posting"
At "Balance Payment" tab:
- end user selects G/L item Expense = Debit Amount = 1000.00
- enters G/L item VAT = Debit Amount = 160.00
- and enters G/L item Withholding = Credit Amount = 150.00

Go Back to Manual Settlement Tab
Once there Manual Settlement is processed and there is no posting.
Then end-user needs to take the manual settlement into a Bank Statement or Cash Journal as a Payment.
Once the Bank Statement is post, below posting is shown:

1010.00 Pending (5550)

1010.00 Bank (5720)

No transaction must appear in the report.

Finally, once the "Settlement-Cancellation Payment" is posted in the application path: Financial Management || Receivables & Payables || Transactions || Settlement || Settlement , below posting must be shown:

1000.00 Expense (6000)	1010.00 Pending (5550)
160.00 VAT (4720)	150.00 Withholding (4751)

For this scenario:

VAT amount (160,00) and Withholding amount (-150.00) will be taken into account and therefore shown in the Multidimensional Tax Report by the time the payment is cancelled, which means by the Bank Statement or Cash Journal Accounting Date.
Tax base amount (1000) will be calculated taking into account the Tax % and the Tax Amount entered by the end-user and same applies to withholding amount.

Use Case 5 - A Bank Statement or a Cash Journal including a G/L item do not linked to either a "Tax Rate" and/or a "Withholding Rate" or a "Withholding"

We can not think on a real scenario for a G/L item linked to either a "Tax Rate" and/or a "Withholding Rate" or a "Withholding" but the other way around.

In this scenario end user might follow below steps:

Go to Financial Management // Receivable and Payables // Transactions // Bank Statement // Header and create a new Bank Statement by entering below data:
- Name = BStatement G/L item
- Transaction Date = 25-06-2010
- Begining Balance = 15800.00
Then he/she goes to Lines Tab, creates a new one and enters below information:
- G/L item = Tax Payment Settlement - Debit/Credit Account = 4750
Go back to Header, process and post. System will show below posting:

15800.00 Bank (5720)

15800.00 VAT Settlement (4750)

Tax Report examples (M-303,M-110 and M-115)

As described above "Invoice Taxes Report" enhancements are based on Spanish Tax Requirements and Spanish "GPS" team input, regardless it could also be applied to other countries as the main goal is to provide the end-user with a tool to exploit tax information saved in the system.

As a real example or use case scenario, there are two Spanish Tax Report M-303 and M-110 end-user could manually filled in by using the Invoice Taxes Report enhanced as "Multidimensional Tax Report"

M-303 Use Case Scenario

M-303 tax report is a "VAT Settlement" tax report
It can be manually submitted by filling in the form which can be found here:

https://www2.agenciatributaria.gob.es/es13/h/ie93030b.html

M-303 tax report requires below data.

Overall Data section

End-user must enter/select information detailed below:

Tax ID
Company Name
Period (Referece List : 1T, 2T, 3T or 4T)
Year

Payable VAT section (in Spanish "IVA Devengado")

This section collects the Payable VAT coming from:

a) National sales invoices of goods & services split by VAT Rate (16%, 7% and 4%).

b) National sales invoices of goods & services under Equivalence Charge regimen split by VAT Rate (4%, 1%, 0,5%)

c) Intra-community purchase of goods summed-up and do not split by VAT Rate

For getting above information end-user should run Multidimensional Tax Report by selecting:

"Tax" Radio button selected.
No Tax in the "Tax Selector"
No BP in the "BP Selector"
"Show Details Info" flag selected
"Grouped by BP" flag do not selected
"Sales/Collection Transactions" Radio button selected.

a) National Sales invoices of goods&services split by VAT Rate (16%, 7% and 4%).

End-user should get and fill in Sales VAT information: Tax Base Amount and Tax Amount , coming from any sales transaction linked to the VAT rates belonging to the Tax Categories shown below, summed-up and split by VAT rate (16, 7 or 4) and taking into account the Invoice Accounting Date.

Tax Categories :

VAT16%, VAT7%, VAT4%, excluding Intra-Community VAT related tax rates (both Intra-Community Purchase and Intra-Community Sales tax rates)
VAT16%Services, VAT7%Services, VAT4%Services excluding "Reversed Charge" rates (In Spanish "Inversión de Sujeto Pasivo").
VAT16%RealEstate, VAT7%RealEstate.

b) National Sales invoices of goods&services under Equivalence Charge regimen summed-up and split by VAT Rate (4%, 1%, 0,5%)

End-user should get and fill in Sales VAT information: Tax Base Amount and Tax Amount , coming from any sales transaction linked to the VAT rates belonging to the Tax Categories shown below, summed-up and split by VAT rate (4%, 1%, 0,5%) and taking into account Invoice/Manual Settlement Accounting Date.

Tax Categories:

VAT16%, VAT7%, VAT4%.
VAT16%Services, VAT7%Services, VAT4%Services if any

c) Intra-community purchase of goods summed-up and do not split by VAT Rate.

End-user should get and fill in Purchase VAT information: Tax Base Amount and Tax Amount , coming from any purchase transaction linked to the VAT rates belonging to the Tax Categories shown below, summed-up regardless VAT rate (16, 7 or 4)

Tax Categories:

VAT16%, VAT7%, VAT4%, just for Intracommunity Purchase VAT Rates.

Deductible VAT section (in Spanish "IVA Deducible"):

This section collects the VAT which can be deductible 100% (otherwise VAT proportion rules must be applied) related to:

a) National Purchase of Goods/Services

b) National Purchase of Real Estate

c) Import of Goods/Services

d) Import of Real Estate

e) Intra-community Purchase of Goods/Services

f) Intra-community Purchase of Real Estate

For getting above information end-user should run Multidimensional Tax Report by selecting:

"Tax" Radio button
No Tax in the "Tax Selector"
No BP in the "BP Selector"
"Show Details Info" flag selected
"Grouped by BP" flag do not selected
"Purchase/Payment Transactions" flag selected.

a) National Purchase of Goods/Services:

Tax Categories:

VAT16%, VAT7%, VAT4% , excluding Intra-Community Purchase VAT rates and Import VAT rates.
VAT16%Services, VAT7%Services and VAT4%Services, Excluding "Reversed Charge" rates (In Spanish "Inversión de Sujeto Pasivo").

b) National Purchase of Real Estate:

End-user should get and fill in Purchase VAT information: Tax Base Amount and Tax Amount, coming from any purchase transaction linked to the VAT rates belonging to the Tax Categories shown below, summed-up regardless of VAT rates (16, 7 or 4)

Tax Categories:

VAT16%RealEstate, VAT7%RealEstate

c) Import of Goods/Services:

End-user should get and fill in Purchase VAT information: Tax Base Amount and Tax Amount c, coming from any purchase transaction linked to the VAT rates belonging to the Tax Categories shown below, summed-up regardless of VAT rates (16, 7 or 4)

Tax Categories:

VAT16%, VAT7%, VAT4%, excluding National Purchase rates and Intra-community Purchase rates and excluding "Reversed Charge" rates (In Spanish "Inversión de Sujeto Pasivo").

d) Import of Real Estate:

Tax Categories:

VAT16%RealEstate, VAT7%RealEstate

e) Intra-community Purchase of Goods

End-user should get and fill in Purchase VAT information: Tax Base Amount and Tax Amount, coming from any purchase transaction linked to the VAT rates belonging to the Tax Categories shown below, summed-up regardless of VAT rates (16, 7 or 4)

Tax Categories:

VAT16%, VAT7%, VAT4%, excluding National Purchase rates and Import Purchase rates.

f) Intra-community Purchase of Real Estate

End-user should get and fill in Purchase VAT information: Tax Base Amount and Tax Amount, coming from any purchase transaction linked to the VAT rates belonging to the Tax Categories shown below, summed-up regardless of the VAT rates (16, 7 or 4)

Tax Categories:

VAT16%RealEstate, VAT7%RealEstate, excluding National Purchase rates and Import Purchase rates.

VAT Settlement section (In Spanish "Liquidación"):

This sections collect exempt or not-subject to VAT transactions like:

a) Intracommunity Sales of Goods

b) Export of Goods

c) Reverse Charge

For getting above information end-user should run Multidimensional Tax Report by selecting:

"Tax" Radio button
No Tax in the "Tax Selector"
No BP in the "BP Selector"
"Show Details Info" flag selected
"Grouped by BP" flag do not selected
"Both Transactions" flag selected.

a) Intra-Community Sales of Goods:

End-user should get and fill in Sales VAT information: Tax Base Amount as these are VAT Exempt Transactions, coming from any sales transaction linked to the VAT rates belonging to the Tax Categories shown below:

Tax Categories:

Exempt, excluding National Sales rates and Export rates.
VAT16%, VAT7%, VAT4% , excluding National Sales rates and Export rates.

b) Export of Goods:

Tax Categories:

Exempt, excluding National Sales rates and Intra-Community Sales rates.
VAT16%, VAT7%, VAT4% , excluding National Sales rates and Intra-Community Sales rates.

c) Reverse Charge:

End-user should get and fill in Purchase VAT information: Tax Base Amount as these are not-subject to VAT Transactions, coming from any purchase transaction linked to the VAT rates belonging to the Tax Categories shown below.

Tax Categories:

VAT16%Services, VAT7%Services, VAT4%Services, just in case of Reverse Charge.
VAT16%, VAT7%, VAT4%, just in case of Reverse Charge.

Rest of Form fields are calculated fields within Tax Form.

M-110 Use Case Scenario

M-110 is a "Withholding Tax Report" which contains information about certain Monetary and Non-Monetary Incomes subject to withhold.

It can be manually submitted by filling in the form which can be found here:

http://www.aeat.es/AEAT/Contenidos_Comunes/La_Agencia_Tributaria/Modelos_y_formularios/Declaraciones/Modelos_100_al_199/110/mod110e.pdf

M-110 tax report requires below data.

Overall Data section

End-user must enter information detailed below:

Tax ID
Company Name
Period
Year

Withholding Settlement section (In Spanish "Liquidación")

This sections collects withholdings grouped based on incomes type, which are listed below:

a) Employment monetary and non-monetary income

- Organizations are required to withhold a certain % at the time these incomes are paid to their employees.

b) Economic activities monetary and non-monetary incomes

- Professionals or Companies are required to withhold a certain % at the time these activities take place and therefore are invoiced.

- Later on the amount to be paid will be less as it will be decreased by the withholding amount.

c) Prizes monetary and non-monetary incomes are also subject to withholding

d) Capital gains monetary and non-monetary incomes are also subject to withholding

e) Image rights transfers monetary and non-monetary incomes are also subject to withholding

Withholding information/transactions must be grouped by withholding or withholding rate, therefore end-user must create:

Different withholdings per each income type (employment)
Different withholdings or withholding rates per each income type (economic activities, prizes, capital gains and image rights)
And besides different withholding rates or withholdings for monetary incomes and for non-monetary incomes of each income type.

Withholding setup example:

Withholding = Monetary Employment Withholding (salaries) 25%
Withholding = Non-Monetary Employment Withholding (courses) 15%
Withholding = Monetary Employment Withholding (board of directors) 35%
Withholding = Non-Monetary Economic Withholding (board of directors) 35%
Withholding Rate = Monetary Economic Activity Withholding 15% (-15%)
Withholding Rate = Non-Monetary Economic Activity Withholding 15% (-15%)
Withholding Rate = Monetary Economic Activity Withholding 7% (-7%)
Withholding Rate = Non-Monetary Economic Activity Withholding 7% (-7%)
and so on so forth.....

Let's explain most common ones:

a) Employment monetary and in kind incomes

Example: Payroll being paid to an employee.

It is important to remark that in the case of Salary Posting, end-user should enter salaries per each employee and therefore withholding rate (%), as there is no way to get what should be the Taxable amount in case of mixing several withholding rates (%).

This kind of transactions could be entered in the system by using a Manual Settlement.

Manual Settlement:

End-user must follow below steps:

Create the corresponding G/L items by navigating to Financial Management // Accounting // Setup // G/L item

There should be at least below G/L items:
- Name = Peter Salary March - Account Debit /Credit = 6499
- Name = Social Security - Account Debit /Credit = 4760
- Name = Monetary Employment Withholding (salaries) 25% - Withholding = "Monetary Employment Withholding (salaries) 25%"; Account Debit/Credit = 4730

After that end-user must enter a Manual Settlement by navigating to Financial Management // Receivables and Payables // Transactions // Manual Settlement
Once there end-user must create a new one by entering both the "Transaction Date" and the "Accounting Date"
At "Create Payment" tab:
- end user selects a BP or employee
- enters transaction amount (by example 2025.00)
- removes "Receipt" flag as this is a payment to an employee
- and do not marks "Direct Posting"
At "Balance Payment" tab:
- end user enters G/L item "Peter Salary" = Debit Amount = 3000.00
- enters G/L item "Social Security" = Credit Amount = 225.00
- and enters G/L item "Monetary Employment Withholding (salaries) 25%" = Credit Amount = 750.00
Go Back to Manual Settlement Tab and process it, no posting take place
Then end-user needs to take the manual settlement into a Bank Statement
Once the Manual Settlement is processed and posted, posting must look like:

3000.00 Salaries (6499)	2025.00 Pending Balance (5550)
	225.00 Social Security (4760)
	750.00 Monetary Employment Withholding (salaries) 25% (4730)

2025.00 Pending (5550)

2025.00 Bank (5720)

For this scenario:

Withholding amount (750.00) must be taken into account and therefore shown in the Multidimensional Tax Report by the time the payment is cancelled, which means by the Bank Statement or Cash Journal Accounting Date.

b) Economic activities monetary and in kind incomes.

Example: Professional Services or any other kind of economic activity supplied by a BP

This kind of transactions can be entered in the system by using either a purchase invoice or a manual settlement.

Purchase Invoice - See Use Case 1 and 2.

Manual Settlement - See Use Case 3 and 4.

For getting above information end-user should run Multidimensional Tax Report by selecting:

"Withholding" Radio button
No Withholding in either "Withholding on Invoice" or "Withholding on Settlement", therefore the system will show tax transactions linked to both withholding types.
No BP in the "BP Selector"
"Show Details Info" flag selected
"Grouped by BP" flag selected
"Both Transactions" flag selected.

a) Employment Section

End-user should get and fill in below information per withholding rate (by example = Monetary Employment Withholding (salaries) 25%) or withholding (by example = Non-Monetary Employment Withholding (courses) 15%

Total number of BP
Total Tax Base amount
Total Withholding amount

b) Economic activities Section

End-user should get and fill in below information per withholding rate (by example = Monetary Economic Activity Withholding 15%) or withholding (by example = Non-Monetary Economic Activity Withholding 15%)

Total number of BP
Total Tax Base amount
Total Withholding amount

M-115 Use Case Scenario:

M-115 is a "Withholding Tax Report" which contains information about renting incomes subject to withholding

It can be manually submitted by filling in the form which can be found here:

http://www.aeat.es/AEAT/Contenidos_Comunes/La_Agencia_Tributaria/Modelos_y_formularios/Declaraciones/Modelos_100_al_199/115/mod115multidiomas.pdf#zoom=100

M-115 tax report requires below data.

Overall Data section

End-user must enter information detailed below:

Tax ID
Company Name
Period
Year

Withholding Settlement section (In Spanish "Liquidación")

Total number of BP
Total Tax Base amount
Total Withholding amount

Withholding information required as shown above must be grouped by withholding or withholding rate. It is required that the end-user creates different withholding rates or withholdings for each renting income subject to a different withholding %Withholding setup example:

Withholding = Renting 19%
Withholding = Renting 9% (Ceuta o Melilla)

This kind of transactions can be entered in the system by using either a Manual Settlement or a purchase invoice.

Manual Settlement

End-user must follow below steps:

Create the corresponding G/L items by navigating to Financial Management // Accounting // Setup // G/L item
There should be at least below G/L items:
- Name = Renting Building X - Account Debit /Credit = 6488
- Name = Renting 19% Withholding = "Renting 19%"; - Account Debit/Credit = 4730
After that end-user must enter a Manual Settlement by navigating to Financial Management // Receivables and Payables // Transactions // Manual Settlement
Once there end-user must create a new one by entering both the "Transaction Date" and the "Accounting Date"
At "Create Payment" tab:
- end user selects a employee
- enters transaction amount (by example 5870.00)
- removes "Receipt" flag as this is a payment
- and do not marks "Direct Posting"
At "Balance Payment" tab:
- end user enters G/L item "Renting Building X" = Debit Amount = 5870.00
- and enters G/L item "Renting 19%" = Credit Amount = 1115.30
Go Back to Manual Settlement Tab and process it, no posting take place
Then end-user needs to take the manual settlement into a Bank Statement
Once the Manual Settlement is processed and posted, posting must look like:

5870.00 Renting Building X (6488)	4754.70 Pending Balance (5550)
	1115.30 Renting 19% withholding (4730)

4754.070 Pending (5550)

4754.70 Bank (5720)

For this scenario:

Withholding amount (1115.30) must be taken into account and therefore shown in the Multidimensional Tax Report by the time the payment is cancelled, which means by the Bank Statement or Cash Journal Accounting Date.

For getting above information end-user should run Multidimensional Tax Report by selecting:

"Withholding" Radio button
No Withholding in either "Withholding on Invoice" or "Withholding on Settlement", therefore system will show tax transactions linked to both withholding types.
No BP in the "BP Selector"
"Show Details Info" flag selected
"Grouped by BP" flag selected
"Both Transactions" flag selected.

a) Settlement Section

End-user should get and fill in below information per withholding rate (by example = Renting 19%) or withholding (by example =Renting 9%)

Total number of BP
Total Tax Base amount
Total Withholding amount

Research on tax Spanish Tax Reports M-180

M-180 is the Annual version of M-115

For this one end-user should define year range, grouped by BP and showing details.

Retrieved from "http://wiki.openbravo.com/wiki/Projects:MultiTaxReport/Functional_Specification"

This page has been accessed 4,831 times. This page was last modified on 8 June 2012, at 05:28. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.

Toolbox

PDF Books

Search

Participate

Projects:MultiTaxReport/Functional Specification

Contents

Functional Specifications

Functional Specifications sign-off

Section 1: Overview & Justification

Purpose

Overview and Justification

Section 2 : Scope

Section 3 - Functional Requirements

Functional Requirement 1 - Tables

Functional Requirement 2 - Data

Functional Requirement 3 - Accounting Date

Functional Requirement 4 - Filters

Functional Requirement 5 - Report Layout

Functional Requirement 6 - Tax and Withholding management

Uses Cases

Use Case 1 - A Purchase/Sales Invoice including Tax Rate/s such as VAT and Withholding Rates

Use Case 2 - A Purchase Invoice including Tax Rate/s such as VAT and Withholding on Settlement

Use Case 3 - A Manual Settlement including a G/L item linked to a "Tax Rate" and a G/L item linked to a "Withholding Rate".

Use Case 4 - A Manual Settlement including a G/L item linked to a "Tax Rate" and a G/L item linked to a "Withholding".

Use Case 5 - A Bank Statement or a Cash Journal including a G/L item do not linked to either a "Tax Rate" and/or a "Withholding Rate" or a "Withholding"

Tax Report examples (M-303,M-110 and M-115)

M-303 Use Case Scenario

M-110 Use Case Scenario

M-115 Use Case Scenario:

Research on tax Spanish Tax Reports M-180

Research on tax Spanish Tax Reports M-180