Synergy Custom Shipping Cost Import

The purpose of this document is to outline the custom work we need to be doing in order to calculate and keep the shipping costs of the products per warehouse

The client complicated shipping cost calculations for some of the warehouses that will be used. Some are hardcoded, some are picked from files and calculated based on some logic. The shipping costs generated by this script will later be used by the ‘Synergy Price Calculations Script’ to calculate the final price of the product (per warehouse). The script will need to fill in the ‘Location Quantity Part Synergy Price > Shipping Cost’ field.

We will have separate files that contain shipping costs for most of the warehouses. For some of them, we will need to read more than 1 file for different products and also there is a ‘shipping corrections’ file that will contain products for multiple warehouses. We will need to check the warehouse key when reading this file in order to know what exactly we need to update.

In order to incorporate this we want to introduce a new paths label for the already existing FTP Credential > Types called - PartSynergyGetShipping and PartSynergyGetShippingProcessed this way way we can use the same credentials but use different paths.

The logic how we will import the shipping cost is explain in the below table. For all warehouses where we have Free Shipping we do not want to import anything as our default value for Item Location Quantity Part Synergy Price > Shipping Cost is 0.

IF Column I = ‘TRUE’ AND Column HColumn K < 100, then Column W + 11.5 = Item Location Quantity Part Synergy Price > Shipping Cost IF Column I = ‘TRUE’ AND Column HColumn K ≥ 100, then Column W + 5 = Item Location Quantity Part Synergy Price > Shipping Cost IF Column I = ‘FALSE’, then Column W + Column X = Item Location Quantity Part Synergy Price > Shipping Cost | Mapping required for Master Line Code. Column C = ‘Warehouse Line Code’

*Same file used as with the initial price import script

If the product do not match any of the cases we want to skip it. <v1.1> Change within the mapping columns </v1.1>

<v1.4>We want to trim from Column E = and “ because they may have leading zeros (e.g., '00346' becomes '346').</v1.4> | | Marine | B | marine.csv | | Column C (mapping) + | + Column D = Product Location Stock Update > Product Identifier IF Column S = ‘TRUE’ AND (Column R + Column T)Column U < 100, then Column AA + 11.5 = Item Location Quantity Part Synergy Price > Shipping Cost IF Column S = ‘TRUE’ AND (Column R + Column T)Column U ≥ 100, then Column AA + 5 = Item Location Quantity Part Synergy Price > Shipping Cost IF Column S = ‘FALSE’, then Column AA + Column AB = Item Location Quantity Part Synergy Price > Shipping Cost | Mapping required for Master Line Code. Column C = ‘Warehouse Line Code’

*Same file used as with the initial price import script

If the product do not match any of the cases we want to skip it. <v1.1> Change within the mapping columns </v1.1>

<v1.4>We want to trim from Column E = and “ because they may have leading zeros (e.g., '00346' becomes '346').</v1.4> | | LKQ | F | LKQ Shipping.csv | | Column B = Product Location Stock Update > Product Identifier Column C = Item Location Quantity Part Synergy Price > Shipping Cost | | | OE Wheels | 2 | OE Wheel Shipping.xlsx | | Column B = Product Location Stock Update > Product Identifier Column C = Item Location Quantity Part Synergy Price > Shipping Cost | | | Meyer | E | meyer.csv | | Column D (replace first 3 symbols with “Master Line Code” +”|”) = Product Location Stock Update > Product Identifier IF Column I = ‘FALSE’ AND Column G < 250, then 10 = Item Location Quantity Part Synergy Price > Shipping Cost IF Column I = ‘FALSE’ AND Column G ≥ 250 and < 500, then 15 = Item Location Quantity Part Synergy Price > Shipping Cost IF Column I = ‘FALSE’ AND Column G ≥ 500, then 25 = Item Location Quantity Part Synergy Price > Shipping Cost IF Column I = ‘TRUE’, then 150 = Item Location Quantity Part Synergy Price > Shipping Cost | Mapping required for Master Line Code. Warehouse Line Code incorporated as first 3 symbols in column C (Item Number).

*Same file used as with the initial price import script

If the product do not match any of the cases we want to skip it. | | Motorstate | Y | motorstate.csv | | Column A (replace first 3 symbols with “Master Line Code” +”|”) = Product Location Stock Update > Product Identifier IF Column H = ‘NO’ AND Column C < 25, then 15.99 = Item Location Quantity Part Synergy Price > Shipping Cost IF Column H = ‘NO’ AND Column C ≥ 25 and < 60, then 12.99 = Item Location Quantity Part Synergy Price > Shipping Cost IF Column H = ‘NO’ AND Column C ≥ 60 and < 160, then 11.49 = Item Location Quantity Part Synergy Price > Shipping Cost IF Column H = ‘NO’ AND Column C ≥ 160, then 9.99 = Item Location Quantity Part Synergy Price > Shipping Cost IF Column H = ‘YES’, then 180 = Item Location Quantity Part Synergy Price > Shipping Cost | Mapping required for Master Line Code. Warehouse Line Code incorporated as first 3 symbols in column A (PartNumber).

*Same file used as with the initial price import script

If the product do not match any of the cases we want to skip it. | | NPW | N | N/A | | Item Location Quantity Part Synergy Price > Shipping Cost | Hardcoded value - 12.99 | | Parts Authority | P | Shipping Corrections LTL.csv | | Column B = Product Location Stock Update > Product Identifier Column C = Item Location Quantity Part Synergy Price > Shipping Cost | <v1.3> All the PA products that are not in the file should have hardcoded value = 20.00 <v1.3> | | PFG | J | pfg.txt | | “366” + | + Column A = Product Location Stock Update > Product Identifier Column H + Column I = Item Location Quantity Part Synergy Price > Shipping Cost | | | Simple Tire | Z | SimpleTire FET.csv | | “145” + | + Column A = Product Location Stock Update > Product Identifier Column C = Item Location Quantity Part Synergy Price > Shipping Cost | All the products that are not in the file should have hardcoded value = 0 | | The Parts House | T | TPH Shipping.xlsx | | Column C = Product Location Stock Update > Product Identifier Column E = Item Location Quantity Part Synergy Price > Shipping Cost | The same file is read for ‘The Parts House’ and ‘XLP’ warehouses. | | XLP | L | TPH Shipping.xlsx | | Column C = Product Location Stock Update > Product Identifier Column E = Item Location Quantity Part Synergy Price > Shipping Cost | The same file is read for ‘The Parts House’ and ‘XLP’ warehouses. | | Whole Sale Marine | { | WSM Shipping.csv | | Column A = Product Location Stock Update > Product Identifier Column C = Item Location Quantity Part Synergy Price > Shipping Cost | | | Spyder Auto | $ | Spyder Shipping.csv | | Column A = Product Location Stock Update > Product Identifier Column C = Item Location Quantity Part Synergy Price > Shipping Cost | The rest is picked from the ‘Shipping Corrections.csv’ file | | Spyder Auto | $ | Shipping Corrections APF2.csv | | Column B = Product Location Stock Update > Product Identifier Column C = Item Location Quantity Part Synergy Price > Shipping Cost | Should work only for the APFusion account Id = 13 (skipped for now) | | Wheel Pros | 3 | WP_Shipping.csv | | Column B + “|” + Column C = Product Location Stock Update > Product Identifier Column D = Item Location Quantity Part Synergy Price > Shipping Cost | The rest is picked from the ‘Shipping Corrections.csv’ file | | Burco | 6 | <v1.5>Burco Shipping.csv | | Column A= Product Location Stock Update > Product Identifier Column B = Item Location Quantity Part Synergy Price > Shipping Cost | Rest is picked from the ‘Shipping Corrections.csv’ file </v1.5> | | RSL - Race Support Lighting | 8 | N/A | | Item Location Quantity Part Synergy Price > Shipping Cost | Default value = 23.5. Rest is picked from the ‘Shipping Corrections.csv’ file | | Tonsa | O | Tonsa Shipping.xlsx | | Column D = Product Location Stock Update > Product Identifier Column E = Item Location Quantity Part Synergy Price > Shipping Cost | The rest is picked from the ‘Shipping Corrections.csv’ file | | Turn 14 - T14 | U | T14-Shipping.xlsx | | Column A = Product Location Stock Update > Product Identifier Column B = Item Location Quantity Part Synergy Price > Shipping Cost | The rest is picked from the ‘Shipping Corrections.csv’ file | | Multiple warehouses | | Shipping Corrections.csv | | Column A (mapping) = Warehouse Key Column B = Product Location Stock Update > Product Identifier Column C = Item Location Quantity Part Synergy Price > Shipping Cost | Mapping required for ‘Warehouse Key’ so we know which warehouse we are updating the product for. | | Custom Carpets | \ | CC Shipping.csv | | Column A = Product Location Stock Update > Product Identifier Column D = Item Location Quantity Part Synergy Price > Shipping Cost | | | RTX Wheels | @ | N/A | | Item Location Quantity Part Synergy Price > Shipping Cost | Hardcoded value - 25.00 |

For all warehouse where we have default or hardcoded value and we wont be receiving any files we want simply just to update all the Item Location Quantity Part Synergy Price > Shipping Cost for the relevant warehouses but the activation for this will be to have the correct path. So for example for NPW if we have FTP Credentials Type = PartSynergy Stock File npw and we have PartSynergyGetShipping this should activate and update all shipping costs as 12.99.

Link to the google drive containing the files from the table above:

https://drive.google.com/drive/folders/15fs1PdpoPzKu-nX9-kgLJsKTsbAgRgHq?usp=drive_link

On top of this there will be another file which will be used for correction which will be drop manually on a separate FTP where we want to have separate label and path.

FTP Credentials > Type - PartSynergy Shipping Corrections FTP Path > Label - PartSynergyGetCorrections <v1.2>The correction is something which we do not want to process but always pick with every shipping import. The file will sit in the folder and we do not want to move it, remove it or rename it.</v1.2>

The file structure is:

Shipping Corrections.csv

This will overwrite all already import shipping costs in our system.