Synergy Custom Stock & Price Import

Purpose of this document is to outline the custom work which we need to do in order to onboard Synergy.

Summary of changes:

The custom script is needed to import mostly stock levels and prices, often from a combination of 2+ columns in the file.

<v1.3>We will have to build a complete new structure because the client is a dropshipper working with many vendors. Each vendor represent a warehouse and from each warehouse we will receive a file with different structure and logic which we should apply. The first logic is to generate the SKUs which are combination of two fields - those are the SKUs that will be used in Hemi. In order to do that, we need to to be checking different columns from the import files that will store the ‘Warehouse Line Code’. Then, we are to map the ‘Warehouse Line Code’ (usually will be 2 or 3 characters long) to an actual numeric ‘Master Line Code’ according to the ‘Warehouse Line Codes Check.xlsx’ file (attached below). The file will be stored on an FTP server and we will need a setup in form 130 with type and path label = “PartsSynergy Main Mapping File”. When checking this file, according to the warehouse file we are reading, we need to map column A (Warehouse Name) to the location in Hemi with the same name - we will be updating only products located in that location from the selected file. Then, we are checking the ‘Warehouse Line Code’ from the file we are reading, we map it to column C (”LC (From Warehouse - Mostly)”) in the mapping file and search for a SKU that has a combination of the ‘Master Line Code’ (column D from the mapping file) + “|” + the actual part number received in the stock/price file, this is the actual Product Location Stock Update > Product Identifier that we should be looking for and updating. After finding the actual SKU, we need to update it’s Product Location Stock Update > Quantity, `Item Location Quantity Part Synergy Price > Priceand **Item Location Quantity Part Synergy Price > MAP Pric**eandItem Location Quantity Part Synergy Price> Pack Quantity` according to the logic (if the field is picked from multiple columns) shown in the mapping table. </v1.3>

The column combinations that are used to match the SKU, quantity, prices (Initial price, MAP price, shipping cost, ect) are shown in the mapping table below.

Warehouse Line Codes Check.xlsx

We are to read warehouse stock/price every file from an FTP server/directory that is set in the FTP Credentials table (form id: 130) with path labels “PartSynergyGetStock” and “PartSynergyGetStockProcessed”. There will be a separate ‘FTP Credentials’ record for every different file/warehouse.

Second logic is to map the columns accordingly because we may have the same product which is selling from more than one vendor so we will need to store each vendor price and quantity. Thus additional table is required which will be dependent on Item Location Quantity holding the Price. Third logic is to calculate the correct price from each vendor based on a formula with fixed values but different for each vendor. The formula will pick the price form each file and based on the Values in our lookup list we will calculate the prices. The formula is - *((Item Price * PackQty)/Markup)+(Shipping/ShipMkup)*

In order to incorporate all this we will have to create a new ‘Item Location Quantitiy Prices Synergy’ table with the following fields in it

If the MAP price is greater than the calculated price, we should use it as a sale price for the product. If the calculated price is greater, we use it instead. These calculations, however, will not be done by the script so it’s job ends here.

Here is a list of all the vendors, their files, FTP credentials, Mappings and any additional file logic.

Column G =Product Location Stock Update > Quantity Column F =Item Location Quantity Part Synergy Price > Price Column E = Item Location Quantity Part Synergy Price > Map Price | Master Line Code =1409 (hardcoded) | | Factory Reproduction | *.csv | | “233” + | + Column A = Product Location Stock Update > Product Identifier Column E =Product Location Stock Update > Quantity Column I =Item Location Quantity Part Synergy Price > Price | Master Line Code =233 (hardcoded)</v1.4> | | RRW | RRW_20241204.xlsx | | Column A =Product Location Stock Update > Product Identifier Column I = Product Location Stock Update > Quantity Column M = Item Location Quantity Part Synergy Price > Price Column D = Item Location Quantity Part Synergy Price > MAP Price | We should be skipping rows for the master products, i.e when column B is empty

Column C = Product Location Stock Update > Quantity Column D + Column E = Item Location Quantity Part Synergy Price > Price | Mapping required for Master Line Code. Column A = ‘Warehouse Line Code’ | | Spyder Auto | spyder_20241202.csv | | “847” + “|” + Column B =Product Location Stock Update > Product Identifier Column G = Product Location Stock Update > Quantity Column F = Item Location Quantity Part Synergy Price > Price | Master Line Code = 847 (hardcoded) | | Siyam Radiator | Siyam Radiator.csv | | “Master Line Code” + | + Column A =Product Location Stock Update > Product Identifier Column B = Product Location Stock Update > Quantity Column D =Item Location Quantity Part Synergy Price > Price | <v1.1> Master Line Code = 1504 (hardcoded) </v1.1> | | Simple Tire | simpletire_20241203.csv | | “145” + | + Column A =Product Location Stock Update > Product Identifier Column B =Product Location Stock Update > Quantity Column C =Item Location Quantity Part Synergy Price > Price | Master Line Code = 145 (hardcoded) | | RSL - Race Support Lighting | rsl_20241208.csv | | “329” + | + Column A =Product Location Stock Update > Product Identifier Column D =Product Location Stock Update > Quantity Column B =Item Location Quantity Part Synergy Price > Price Column E = Item Location Quantity Part Synergy Price > MAP Price | Master Line Code = 329 (hardcoded)

Need to store column E (MAP) to check with the calculated final price | | Premier | Premier_20241203.csv | | Column B (mapping) + | + Column D = Product Location Stock Update > Product Identifier Column I = Product Location Stock Update > Quantity Column F + Column G =Item Location Quantity Part Synergy Price > Price Column E =Item Location Quantity Part Synergy Price > MAP Price Column H = Item Location Quantity Part Synergy Price> Pack Quantity | Mapping required for Master Line Code. Column B = ‘Warehouse Line Code’ Need to store Column E to check with the calculated final price

• Can’t be found in the mapping file. | | PFG | pfg_20241203.txt | | “366” + | + Column A = Product Location Stock Update > Product Identifier Column P = Product Location Stock Update > Quantity Column F = Item Location Quantity Part Synergy Price > Price | Tab delimited .txt file Master Line Code = 366 (hardcoded)

• Do we do something with column G (COST), Column H (SHIPPING_COST) and Column I (HANDLING_COST)		Parts Authority	pa_20241203.csv		Column B (mapping) +	+ Column C = Product Location Stock Update > Product Identifier SUM(Column H to Column N) = Item Location Quantity > Quantity (Column F + Column G)*Column P = Item Location Quantity Part Synergy Price > Price	Mapping required for Master Line Code. Column B = ‘Warehouse Line Code’		OE Wheels	oe_20241202		“387” + “	” + Column A =Product Location Stock Update > Product Identifier Column C + Column D = Product Location Stock Update > Quantity Column E = Item Location Quantity Part Synergy Price > MAP Price	Master Line Code = 387 (hardcoded) Price will be in a separate file, but we need to keep Column E (MAP) as as will need to check if ‘MAP’ > ‘Final Price’ (after calculations) and use the calculated final price only if ‘Final Price’ > ‘MAP’		NPW	npw_20241202.csv		Column A (mapping) +	+ Column B =Product Location Stock Update > Product Identifier Column C = Product Location Stock Update > Quantity Column D + Column E = Item Location Quantity Part Synergy Price > Price	Mapping required for Master Line Code. Column A = ‘Warehouse Line Code’		Motorstate	motorstate_20241202.csv		Column A (replace first 3 symbols with “Master Line Code” +”	”) = Product Location Stock Update > Product Identifier Column D = Product Location Stock Update > Quantity Column C = Item Location Quantity Part Synergy Price > Price Column E = Item Location Quantity Part Synergy Price > MAP Price	Mapping required for Master Line Code. Warehouse Line Code incorporated as first 3 symbols in column A (PartNumber). We need to store Column E (MapPrice) to be able to check if it will be greater than the calculated ‘Final Price’		Meyer	Meyer_20241203.csv		Column C (replace first 3 symbols with “Master Line Code” +”	”) = Product Location Stock Update > Product Identifier Column D = Product Location Stock Update > Quantity	Mapping required for Master Line Code. Warehouse Line Code incorporated as first 3 symbols in column C (Item Number). *This is the stock file. The price file is on the bottom of the table		LKQ Canada	lkq_20241203.csv					LKQ	lkq_20241203.csv		“455” +	+ Column C = Product Location Stock Update > Product Identifier Column AB =Product Location Stock Update > Quantity Column AA = Item Location Quantity Part Synergy Price > Price	Master Line Code = 455 (hardcoded)
  Marine	Marine_20241202.csv		Column C (mapping) +	+ Column D = Product Location Stock Update > Product Identifier

  Column AM = Product Location Stock Update > Quantity (Column R + Column T)*Column U = Item Location Quantity Part Synergy Price > Price | Mapping required for Master Line Code. Column C = ‘Warehouse Line Code’

<v1.4>We want to trim from Column D = and “ because they may have leading zeros (e.g., '00346' becomes '346').</v1.4> | | Keystone | keystone_20241208.csv | | Column C (mapping) + | + Column E =Product Location Stock Update > Product Identifier Column AH = Product Location Stock Update > Quantity Column H*Column K = Item Location Quantity Part Synergy Price > Price Column K = Item Location Quantity Part Synergy Price> Pack Quantity | Mapping required for Master Line Code. Column C = ‘Warehouse Line Code’

<v1.4>We want to trim from Column E = and “ because they may have leading zeros (e.g., '00346' becomes '346').</v1.4> | | Jante | jante_20241203.csv | | “367” + | + Column B =Product Location Stock Update > Product Identifier Column C =Product Location Stock Update > Quantity Column D + Column E =Item Location Quantity Part Synergy Price > Price | Master Line Code = 367 (hardcoded) | | Dorman | dorman_20241203.csv | | “591” + | + Column A =Product Location Stock Update > Product Identifier Column B = Product Location Stock Update > Quantity | Master Line Code = 591 (hardcoded) Need to map from Column B: ”In Stock” = 5 ”Low Stock” = 2 ”Out of Stock” = 0

Prices will be in a separate file | | Burco | burco_20241203.csv | | “354” + | + Column B =Product Location Stock Update > Product Identifier Column C =Product Location Stock Update > Quantity Column D =Item Location Quantity Part Synergy Price > Price | Master Line Code = 354 (hardcoded) | | Blackburn | BB_20241203.csv | | “442” + | + Column B = Product Location Stock Update > Product Identifier Column J =Product Location Stock Update > Quantity Column L =Item Location Quantity Part Synergy Price > Price | Master Line Code = 442 (hardcoded) | | STM | STM.csv | | “Master Line Code” + | + Column A = Product Location Stock Update > Product Identifier Column H =Product Location Stock Update > Quantity Column I = Item Location Quantity Part Synergy Price > Price | <v1.1> Master Line Code = 150 (hardcoded) </v1.1> | | TWI | TWI.csv | | Column A (replace first 3 symbols with “Master Line Code” +”|”) = Product Location Stock Update > Product Identifier Column E = Product Location Stock Update > Quantity Column F = Item Location Quantity Part Synergy Price > Price | <v1.1> Master Line Code = 152 (hardcoded) </v1.1>

Column H = ‘Warehouse Line Code’ | | Meyer | Meyer Pricing.csv | | Column C (replace first 3 symbols with “Master Line Code” +”|”) = Product Location Stock Update > Product Identifier Column F = Item Location Quantity Part Synergy Price > Price Column I = Item Location Quantity Part Synergy Price > MAP Price | <v1.1> This file is only for the price imports for Meyer. The stock file is explained rows above. </v1.1> | | Custom Carpets | CC inventory.csv | | “Master Line Code” + | + Column A = Product Location Stock Update > Product Identifier Column E = Item Location Quantity Part Synergy Price > Price Column F = Product Location Stock Update > Quantity | <v1.2> added as a warehouse. We will need to download the file from the link below and drop it on an FTP server: </v1.2> Master Line Code = 358 | | RTX Wheels | BSA010_WHEEL.csv | | ="" | <v1.2> added as a warehouse. From the FTP we need to read file named ‘BSA010_WHEEL.csv’ </1.2> Master Line Code = 1750 |

1nxkqeJ2zzD6XSumbxWghSeBTj3-H2vzC

All the different files are uploaded in the drive folder above.