Price calculator functional specification
General overview of the marketplace
The purpose of this document is to describe what this feature needs to have in order to work for us. It needs to be able to calculate the prices automatically with some input starting with the price provided by our suppliers and ending with an integration with fixer.io for currency rates.
Prerequisites
1. Calculations parameters
SKU
Currency
RRP Net - This is our starting point always. - item_account.original_price
Weight in grams - has to be converted to kilograms and rounded to the first digit after the decimal with a step of 0.5 (E.g. if the product is 2.31 it should be rounded to 2.5 and if its 2.6 should be rounded to 3 etc.) - we need to have weight on item level
HTS code - if it's missing the calculation can not be done and we need to throw some kind of an error/alert. - we need to have HTS/HSN or whatever code the country need for import duties on item_account - most probably there would be a need of multiple fields for one item_account
Discount - from seller table.
Marketplace - account table - country of marketplace
Shipping rate matrix provided by DHL where we have costs by weight and zone - attached
List of duties/taxes surcharge estimation to research where we can pull it off automatically - check other questions section
Integration to platform that can provide proper currency rates - fixer.io
Marketplace fee - will be provided in some sort of a lookup list. Link in more questions section.
Target country VAT - account table
Margin - account.account_seller - waiting for default values and to be calculated in percentages. profit margin. +10%
Margin for calculations.
Feature - specifics
2. Calculations
Discounted Net Price = RRP Net * (1 - Discount)
Shipping - based on the matrix provided by DHL for each country, a lookup list which have all regions indexed based on the shipping origin country, Marketplace account country and weight of the product we have to find the correct shipping cost.
E.g. country of origin is Thailand, Marketplace we sell on is eBay UK, DHL list shows UK as part of Zone 4, Weight of the product 3.50 which in the Thai matrix shows that the shipping cost is - 1,175 GB (Assumption: the currency of the shipping rates)
If the final price is calculated to a number ending in 0 or 5 (10,15,20,25, etc.) and has a number after the decimal we should be rounding down to the previous number decimal 99 (25.99 becomes 24.99). Check with Nikola if it's for 10 and above only Angel Sveshtnikov (Unlicensed) - confirmed
If the marketplace is Japanese we have to take into account that the currency doesn't have decimal point. No smaller version of the currency then an integer number.
In the future if there is another currency (different for JPY) that follows the rule from the above we need to be informed to take this currency into account.
In order for the calculations to work all the things from the prerequisites have to be available.
SKU - will be taken from the product section
Currency - the currency we are converting from will be taken from the table where we store all sellers for a given account and the currency we are converting to is going to be on account level within the setup of the marketplace account.
RRP Net - In product account this will be taken from original price field
Weight in grams - is being provided by the sellers in grams so we have convert it to kilograms and round to the first digit after the decimal with a step of 0.5 (E.g. if the product is 2.31 it should be rounded to 2.5 and if its 2.6 should be rounded to 3 etc.) the weight will be provided in field TBC
HTS code - We are going to have a new table for Tariff (table 1245)codes in it we will be populating specific tariff codes and their duty percentage in order for the calculator to have it. The actual tariff code should be added on product account level in order for the calculator to look it up in the new table. If the tariff code is missing on product account or tariff table.
Discount - This discount is within the seller table.
Country of Delivery - will be taken from the Tariff table.
Shipping rate matrix provided by DHL where we have costs by weight and zone - attached
- dhl_shipping_zone_country
- dhl_shipping_rate
List of duties/taxes surcharge estimation to research where we can pull it off automatically - new tariff table (table 1245).
Integration to platform that can provide proper currency rates - integration with fixer.io has been established. There is a new table in the database which will contain the conversion rates that we have downloaded from the website.
Marketplace fee - A new table has been introduced (table 1240) In it we popiluate the fee for a specific account and specific major categor for this account.
Target country VAT - account table
Profit Margin - it is a field in the Account Seller table and is set for each Seller added to the account. This is a percentage that we want to add to the price as a profit(commission) for WeArePentagon. (add in UI (%))
Margin for calculations.
On product account level you can find a few new fields that are mandatory for the calculations to work:
- Tariff code - this one should be the Tariff code for the country you are importing to and it should be also available in the Tariff table.
- Tax Category ID - after you create the Tax categories and then assign them to the accounts with their respective fees you should be assigning those categories to your products in order for the calculations to work.
On product level there is one new field that is mandatory for the calculations to work:
- Weight (gr) - this is the weight of the products ALWAYS in Grams
Discount is being set in the Seller table as show below:
Tariff table, Tax Categories and Marketplace tax have to be populated by the user of the calculator as follows:
In the Tariff Code table you need to insert the Tariff code the Country of origin the Country of Delivery the Import Tax - to become Duty and Duty tax - to become DTP Fee.
In the Marketplace Tax - to become Marketplace fee table you need to populate the Account for which you want to have the specific category fee in percentages the actual Category and the Percentage itself.
Tax Percentage - to become Marketplace Fee (%)
Tax Category ID - to become Marketplace Fee Category ID - change in item_account as well.
In the Tax Categories table you need to create the actual Categories themselves. If you create all relevant categories for which your products are going to be available you can then set them up in the Marketplace Tax table for each account you want to have the calculator running on.
Tax Categories to become Marketplace Fee Categories
After all calculations run you will have the calculated price in the price field on product account level.
On Account level we have DTP threshold. If the calculated amount is above this Threshold then the calculator has to include the Duty Tax in the price. It's a mandatory field which will have a default value of 1 million for each new account added. If the calculator needs to work on the given Account the user should change the DTP Threshold to the relevant value he/she needs.
In Account Seller table you need to set the proper currency for the Seller in order for the calculator to be calculating correctly with the proper currency rates.
Fuel surcharge has to be added to the calculations it wont be changing by zone but every month it will change
DHL are reuglarly doing different kinds of discounts so we will need to have in somewhere % discount and absolute discount
Name of the script is : /var/www/mvc/App/Crons/Scripts# php cron_arc_001_add_seller_in_order_items.php
Other - specifics
If any of the prerequisites are missing we shouldn't be calculating the price and should be throwing an error/alert.
Add a way to activate/deactivate this feature for a specific account within one WAPT instance.
Add duty thresholds - on account level in the field DTP Threshold - if empty - throw an error. (default value of 1 milion).
Outstanding questions
- work with fixer.io
- (DEPRECATED) find database for tariff - https://github.com/ec-europa - if that git project does not work for us then (/DEPRECATED)
- we need to create our own DB (or just a table) with HTS codes and import duty taxes. HTS code, country of origin, country of delivery, import tax %.
- category list - https://docs.google.com/spreadsheets/d/1Lhrdr_aXxQQSwnrmX9_ErSUK364XCWtt0dtcSIeFU0Q/edit#gid=0
Q: How many times and when to start the two scripts?
A: Once a day for both scripts. Specific scheduling will be confirmed on a later stage.
Q: Where will be currency rate percent? (In Account table may be)
A: Yes. On account level within the exchange calculator fields.
Q: Where we will store history? - (log file or table)
A: Nikolay Ivanov (Unlicensed) - Niki, I believe it's good to have them in a separate history table. Please confirm if that will be the best solution. For Log files I can not advise.
Q: What we will do with Category List(Marketplace fees) file and where to store the data? (May be new file with more information)
A: We need to create a table in which users will be able to input the percentages for the given categories(as in the google document) for the given marketplaces. Nikolay Ivanov (Unlicensed) please advise on the specific table structure.
Q: From where to get all the currencies for given Hemi? (In account table exchange_calc_currency)
A: Account table exchange calculator currency.
Q: How many tariff codes(HTS, HSN, etc.) can one single product have?
Q: Visible shipping cost can it be only one specific number for the whole account?
A: Yes, it can and it will be taken into account for the calculations only if it is presented in the specific field on account level.
Useful URL's https://qa3.wearepentagon.com/mvc-old_database/en/table-view/1241 - Marketplace category fees https://qa3.wearepentagon.com/mvc-old_database/en/table-view/1240 - Enumeration for Marketplace categories https://qa3.wearepentagon.com/mvc-old_database/en/table-view/1245 - Tariff codes (HTS, HSN) https://qa3.wearepentagon.com/phpmyadmin/sql.php?server=1&db=hub&table=dhl_shipping_zone_country&pos=0 - DHL shipping zones https://qa3.wearepentagon.com/phpmyadmin/sql.php?server=1&db=hub&table=dhl_shipping_rate&pos=0 - DHL shipping rates