Projects:Price Inclusive of Taxes/Functional Specification
Price Inclusive Of Taxes
Overview
Price inclusive of Taxes feature lets you maintain prices inclusive of taxes in pricelists - and splits the total price into basic sales price and the tax components while posting into the GL
Purpose
Openbravo ERP has an indicator in pricelist, wherein it is possible to indicate that the price-list is inclusive of taxes. However this functionality is not continued on to the transaction screens of procurement and sales. This is a critical functionality, especially in the retail industry, where prices ( inclusive of taxes ) needs to be published
Scope
- When "inclusive of taxes" price is selected in any of the transaction screens that include prices ( typically quotation, order and invoice screens ) then the Gross Unit Price ( inclusive of taxes ) as well as the Net Unit Price should be shown. Taxes should be calculated based on the net price. Another field Line Gross Amount should be shown
- When transactions are posted ( accounting entries generated ) the sale/purchase transaction should reflect the net price and the receivable/payable transaction should reflect the gross price.
References
Existing feature request for this functionality https://issues.openbravo.com/view.php?id=4175
Documentation updated Price list
Design Considerations
The functional component - that is mapping a price inclusive of tax to the price before tax , is identical irrespective of whether the functionality is required at the time of creation of purchase or sales order , or purchase and sales invoice - so this functionality will be implemented separately.
Assumptions
Dependencies
Constraints
Glossary
Functional Requirements
Business process definition
- Price list window
- Ability to mark a price list as its prices definition includes taxes
- Ability to create price list version based on Gross Unit Price
- Ability to define the Gross Unit Price per each product
- Unit Price, List Price and Price Limit can be now gross or net prices depending on
- Price List Schema window
- The ability to create a new price list version depends on the price list schema meaning that we need to add several fields related to the gross unit price similar to Net Unit Price
- Transactional windows
- All transactional windows will have two more fields: Gross Unit Price and Line Gross Amount
- When selecting the product the system retrieves the gross price
- All processes that creates invoices based on orders must take into account the field Gross Unit Price
- All processes that copy orders/copy line orders must take into account the field Gross Unit Price
- Rounding issues
- For sales/purchase orders the rounding differences will be calculated and applied to the highest tax amount
- For sales/purchase invoices the rounding differences will be calculated and new tax line will be created to correct the difference. This tax will be the highest tax amount
 | Note: If price list does not include taxes, the flow should be equal as it is now |
| | Note: Gross Unit Price and Line Gross Amount are not going to be shown in the grid out of the box. Administrator needs to do it |
PHASE I: Functional requirements based on business processes
| Id | Requirement | Importance | Status |
| 1.1 | Show the flag Price Includes Taxes | Must have | Completed |
| 1.2 | Change the name Net Unit Price to Unit Price | Must have | Completed |
| 1.3 | Change the name Net List Price to List Price | Must have | Completed |
| 1.4 | Change the name Net Price Limit to Price Limit | Must have | Completed |
| Id | Requirement | Importance | Status |
| 2.1 | Rename all fields in lines tab. Remove the word Net from all fields | Must have | Completed |
| Id | Requirement | Importance | Status |
| 3.1 | Add field Gross Unit Price. Editable. Default value 0 | Must have | Completed |
| 3.2 | Add display logic for field Gross Unit Price. When it is NOT included taxes this field must be hidden | Must have | Completed |
| 3.3 | Add field Line Gross Amount. Not editable | Must have | Completed |
| 3.4 | Add display logic for field Line Gross Amount. When it is NOT included taxes this field must be hidden | Must have | Completed |
| 3.5 | Modify call-out (the one related with the product) to retrieve the Gross Unit Price in case price list include taxes | Must have | Completed |
| 3.6 | Create call-out so when Gross Unit Price is changed, Line Gross Amount is calculated as Qty*Gross Unit Price | Must have | Completed |
| 3.7 | The above call-out (3.6) must calculate the Net Unit Price based on the Gross Unit Price plus the Tax. Create a new store procedure similar to C_OrderLineTax_Insert but returning the Net Unit Price. It needs to be called with Price Precision instead of standard precision. This procedure will also be called by the other call-out needed for invoices. The name of the procedure must be meaningful (i.e C_Get_NetUnitPrice) | Must have | Completed |
| 3.8 | Read Only logic: When price includes taxes, Net Unit Price must be not editable. Also Net List Price and Discount % should be hidden. | Must have | Completed |
| 3.9 | Button Copy Lines: Support Price includes taxes | Should have | Completed |
| 3.10 | Button Copy From Order: Support Price includes taxes | Should have | Completed |
| 3.11 | Tax Rate Change: Tax rate change should result in change in net price in case the product is inclusive of tax | Should have | Completed |
| Id | Requirement | Importance | Status |
| 4.1 | Add field Gross Unit Price. Editable. Default value 0 | Must have | Completed |
| 4.2 | Add display logic for field Gross Unit Price. When it is NOT included taxes this field must be hidden | Must have | Completed |
| 4.3 | Add field Line Gross Amount. Not editable | Must have | Completed |
| 4.4 | Add display logic for field Line Gross Amount. When it is NOT included taxes this field must be hidden | Must have | Completed |
| 4.5 | Modify call-out (the one related with the product) to retrieve the Gross Unit Price in case price list include taxes | Must have | Completed |
| 4.6 | Create call-out so when Gross Unit Price is changed, Line Gross Amount is calculated as Qty*Gross Unit Price | Must have | Completed |
| 4.7 | The above call-out (4.6) must calculate the Net Unit Price based on the Gross Unit Price plus the Tax. Use the store procedure created in point 3.6 | Must have | Completed |
| 4.8 | Read Only logic: When price includes taxes, Net Unit Price must be not editable | Must have | Completed |
| 4.9 | Button Copy Lines: Support Price includes taxes | Should have | Completed |
| 4.10 | Button Create Lines From: Support Price includes taxes | Must have | Completed |
| 3.11 | Tax Rate Change: Tax rate change should result in change in net price in case the product is inclusive of tax | Should have | Completed |
| Id | Requirement | Importance | Status |
| 7.1 | Purchase/Sales Order: Rounding differences will be adjusted into the c_ordertax record with the higher tax amount | Must have | Completed |
| 7.2 | Purchase/Sales Invoice: Rounding differences will be adjusted | Must have | Completed |
| Id | Requirement | Importance | Status |
| 8.1 | Rename the fields Net Unit Price, Net List Price and Net Price Limit removing the word Net | Must have | Completed |
| Id | Requirement | Importance | Status |
| 9.1 | Under Price tab rename the fields Net Unit Price, Net List Price and Net Price Limit removing the word Net | Must have | Completed |
| Id | Requirement | Importance | Status |
| 10.1 | Pick&Edit button must support price including taxes (RTV) | Must have | Completed |
| 10.1 | Pick&Edit button must support price including taxes (RFC) | Must have | Completed |
PHASE II: Functional requirements based on business processes
| Id | Requirement | Importance | Status |
| 1.1 | Add field Gross Unit Price. Editable. Default value 0 | Must have | Completed |
| 1.2 | Add display logic for field Gross Unit Price. When it is NOT included taxes this field must be hidden. It is more restrictive price list at line level | Must have | Completed |
| 1.3 | Add display logic for fields Net Unit Price, Net List Price, Discount %. When it is included taxes these fields must be hidden. It is more restrictive price list at line level | Must have | Completed |
| 1.4 | Modify call-out (the one related with the product) to retrieve the Gross Unit Price in case price list include taxes | Must have | Completed |
| 1.5 | Modify call-out (the one related with the price list) to retrieve the Gross Unit Price in case price list include taxes | Must have | Completed |
| 1.6 | Button Create Purchase Orders: In manage requisitions must support Price includes taxes. This process must calculate the Net Unit Price | Must have | Completed |
| 1.7 | Procurement - Transactions - Requisition to Order: Support Price includes taxes. This process must calculate the Net Unit Price. It could be the same process as 6.6 | Must have | Completed |
| Id | Requirement | Importance | Status |
| 2.1 | Sales order | Must have | Completed |
| 2.2 | Sales invoice | Must have | Completed |
| 2.3 | Purchase order | Must have | Completed |
| 2.4 | Purchase invoice | Must have | Completed |
PHASE III: Functional requirements based on business processes
| Id | Requirement | Importance | Status |
| 1.1 | New column: grosspricestd. Same as pricestd | Must have | Completed |
| 1.2 | New field for grosspricestd.Name: "Base Gross Unit Price". Display logic so it will be shown when price including taxes | Must have | Completed |
| 1.3 | Add display logic for field "Base Net Unit Price". It will be shown when price does not include taxes | Must have | Completed |
| 1.5 | Modify the call-out related when selecting the product. new column grosspricestd must be filled with the same value as the price defined in the price list (Unit price). Similar as it happens for "base net unit price" | Must have | Completed |
| 1.6 | Modify the way discount is calculated when price including taxes. It must be as follow: ((Gross List price - grosspricestd)/grosslistprice)*100 | Must have | Completed |
| Id | Requirement | Importance | Status |
| 2.1 | New column: priceoffergross. Same as priceoffer | Must have | Completed |
| 2.2 | New field for priceoffergross.Name: "Base Gross Unit Price". Display logic so it will be shown when price including taxes | Must have | Completed |
| 2.3 | Add display logic for field "Base Net Unit Price". It will be shown when price does not include taxes | Must have | Completed |
| Id | Requirement | Importance | Status |
| 3.1 | New column: grosspricestd. Same as pricestd | Must have | Completed |
| 3.2 | New field for grosspricestd.Name: "Base Gross Unit Price". Display logic so it will be shown when price including taxes | Must have | Completed |
| 3.3 | Add display logic for field "Base Net Unit Price". It will be shown when price does not include taxes | Must have | Completed |
| 3.4 | Modify the call-out related when selecting the product. new column grosspricestd must be filled with the same value as the price defined in the price list (Unit price). Similar as it happens for "base net unit price" | Must have | Completed |
| 3.5 | New column: grosspricelist. Same as pricelist | Must have | Completed |
| 3.6 | New field for grosspricelist.Name: "Gross Unit Price". Display logic so it will be shown when price including taxes | Must have | Completed |
| 3.7 | Modify the call-out related when selecting the product. new column grosspricelist must be filled with the same value as the price defined in the price list (List price). Similar as it happens for "List price" | Must have | Completed |
| Id | Requirement | Importance | Status |
| 4.1 | New column: priceoffergross. Same as priceoffer | Must have | Completed |
| 4.2 | New field for priceoffergross.Name: "Base Gross Unit Price". Display logic so it will be shown when price including taxes | Must have | Completed |
| 4.3 | Add display logic for field "Base Net Unit Price". It will be shown when price does not include taxes | Must have | Completed |
| Id | Requirement | Importance | Status |
| 5.1 | Modify the procedure store to copy the values in the newly columns from the order lines to the invoice lines | Must have | Completed |
| 5.2 | Modify the procedure store to copy the values in the newly columns from the order offer lines to the invoice offer lines | Must have | Completed |
PHASE IV: Functional requirements based on business processes
| Id | Requirement | Importance | Status |
| 1.1 | Implement alternate tax base amount | Must have | Not started |
| Id | Requirement | Importance | Status |
| 2.2 | MRP (mrp_Purchaseorder) | Should have | Completed |
| 2.3 | Create orders from projects (Sales order and purchase orders) | Nice to have | Completed |
Functional requirements descriptions
In this section are described all the functional requirements to fulfill this development.
The aim of this project is to fully support price inclusive taxes. Many flows are affected by this functionality since pricing is a critical part of any procurement/sales flow.
Configuration price list window
The first thing to develop is to be able to define a price that includes taxes. To do so it will be enough to define just one price per each product. Right now it is possible to define three different Net Prices (Unit price, List price and Limit price) when taxes are not included. But because of this project we will rename the name of these fields to be used when price include taxes. Openbravo also allows you to create different price lists based on another price list but applying a price list schema. The name of these fields will be modify as well.
Configuration of transactional windows
In Procurement/Sales flow (orders/invoices) a couple of fields will be added. When the price list selected in the header of the document is defined as include taxes then these two new fields will be shown in the lines. Net Unit Price will be not editable and it will be calculated based on the Gross price and the tax contained in the line. When the price list does not include taxes the current functionality should remain as it is
Rounding issues
Due to this (Net amount vs Gross amount) a very important issue needs to be addressed in this project which is rounding differences.
As an example : If price inclusive of tax is originally 135.50 and rate is 4.5 % then the rounded price before tax would be 129.67
The order line would stay:
Quantity: 1 Net Unit Price: 129.67 Line Net Amount: 129.67 Gross Unit Price: 135.50 Line Gross Amount: 135.50
But the total gross amount (calculated by the system) would be 135.51 (tax base amount:129.67 + tax amount:5.84) and what it has to be clear is that the final result must be 135.50 (The customer bought 1 unit which price is 135.50). This difference is going to be solved adjusting taxes:
Adjust for Sales/Purchase Order
The system will adjust the difference summing or subtracting this difference with the tax that has the highest amount. So in this example instead of having a tax line where the amount is 5.84€, the amount will be 5.83€ (5.84-0.01)
So finally the total gross amount for the sales order would be 135.50 (129.67+5.83) which is what we want
Technical description for orders
The current tax implementation for orders:
- An Order Line is inserted and the C_OrderLine_Trg2 trigger is executed. This trigger calls the C_OrderLineTax_Insert procedure.
- The C_OrderLineTax_Insert procedure calculates and inserts recursively all the taxes of the order line in the C_OrderLineTax table, there is one c_orderlinetax record for each order line and tax_id..
- When a new C_OrderLineTax is inserted the C_OrderLineTax_Trg is executed. This trigger updates and creates the corresponding C_OrderTax records for each C_Tax_ID.
- The C_OrderLineTax_Trg might recalculate the tax amount based on the tax base amount when the tax is configured to be calculated at document level.
- When the C_OrderLineTax_Insert procedure finishes the GrandTotal amount of the order is updated by the C_OrderLine_Trg2 trigger.
Taking into account the steps described above. The adjustment will be done just before the last step. Once all order line taxes and order taxes have been calculated following the standard procedures. So, just after the C_OrderLineTax_Insert procedure is executed it has to be checked if some rounding is needed. The taxes have been calculated using the Line Net Amounts of the Order Lines. And might not match the gross amounts defined by the price list. At this point it is possible to calculate the tax amounts, summing the tax amount of the order taxes. And based on the gross amount of the lines it is possible to get the desired tax amounts. If there is a difference it is adjusted the c_ordertax record with the higher tax amount
Adjust for Sales/Purchase Invoice
The same as Orders. So finally the total gross amount for the sales order would be 135.50 (129.67+5.84-0.01) which is what we want
Technical description for invoices
In case of the invoices we can follow a similar approach than with the orders. The current process is analogous to the order flow.
- The c_invoiceline_trg2 trigger creates the C_InvoiceLine_Tax lines using the C_InvoiceLineTax_Insert procedure.
- A trigger in the C_InvoiceLineTax manages the C_InvoiceTax records.The c_tax_id should be the one with the higher amount to the corresponding invoice line. The rounding has to be calculated and inserted when the invoice line. This is, in the c_invoiceline_trg2 trigger after the C_InvoiceLineTax_Insert procedure execution.
- If there is a difference it is adjusted the c_invoicetax record with the higher tax amount
The way to get this -0.01 would be summing all the Line Gross Amount per different Tax and subtracting from the sum of all the line net amounts. This way you get the desire tax amount. When it is different from the calculated tax amount then we have the rounding issue
Generate invoices
Everything will remain exactly as it is with the exception of Customer schedule after delivery invoice rule. For this specific case sales orders will be grouped either by price lists that include taxes or don't include taxes. What we won't allow is to mix lines that belong to sales orders that include taxes and orders that don't include taxes
Create lines from - Invoices
The behaviour of this button has been changed.
- Orders combo: It is filtered depending on the price list of the header of the invoice. If the price list includes taxes then it only shows orders where their price list includes taxes and viceversa
- Shipment/receipt combo:
- It is filtered as the order combo. It only shows shipments where theirs lines belong to a order where their price list includes taxes and viceversa
- If the lines don't belong to any order then the prices are calculated based on the price list of the header
Copy from Order
The behaviour of this button has been changed.
- If the order has a price list that includes taxes and you select an order that includes taxes then the gross price is calculated based on the price list of the header
- If the order has a price list that includes taxes and you select an order that doesn't include taxes then the gross price is calculated based on the price list of the header
- If the order has a price list that doesn't include taxes and you select an order that includes taxes then the net unit price is calculated based on the price list of the header and the gross price will be zero
- If the order has a price list that doesn't include taxes and you select an order that doesn't include taxes then the net price is calculated based on the price list of the header and the gross price will be zero
Copy lines - Invoice
- If the flag Select Price from Price List is marked then the prices are calculated based on the price list defined in the header. Gross unit price if it has taxes and net unit price if not
- If the flag Select Price from Price List is not marked then the prices are copied from the invoice you are copying if includes taxes as the header and if not then recalculates the prices and viceversa
Copy lines - Order It shows lines depending on the price list of the header, I mean, if includes taxes then show lines that comes from order that included taxes. If not then only shows lines that comes from orders that didn't include taxes. Depending on this will show gross prices or net prices
P&E button in Return to vendor and Return from customer
- When the price list of the header includes taxes the pick&edit will only show orders/shipments where the price list included taxes and viceversa
- When the price list of the header includes taxes and you return shipments without orders the price is calculated based on the price list of the header. If the product is not in the list then the price is zero and viceversa