Update Alliance Parts Sell 01 Pricing in Spire

  1. Open the MyAlliance site at https://home.alliancels.net/home
  2. In the search bar in the landing screen in MyAlliance type, “Service Parts List Prices Manual (Excel) – US Order Desk” and click search
  3. Open the file that downloads
  4. Save the spreadsheet in the following location, S>PRICE UPDATES>YEAR>Alliance and save it with the default file name
  5. Be sure that the List Price tab is selected and minimize the spreadsheet
  6. Click on the Inventory module in Spire
  7. Click on the INV Price Update filter or create a filter with the following criteria
    1. Warehouse is one of W1-Phoenix Warehouse
  8. Unselect all columns except for Warehouse, Part No, Description, Available, On Hand, Committed, and Product Code
  9. Click the Export button near the top of the page
  10. Save the spreadsheet in the following location, S>PRICE UPDATES>YEAR>Alliance and name it Alliance W1
  11. Open the Alliance W1 spreadsheet
  12. Open the Service Parts List Prices Manual (Excel) – US Order Desk spreadsheet
  13. Copy columns A, B, and D (Part Number, Description, and List Price) from the Service Parts List Prices Manual (Excel) – US Order Desk spreadsheet and paste them into the Alliance W1 spreadsheet starting at column J.
  14. Insert a blank column after column J
  15. In Cell K2 type “=TRIM(J2)”
  16. Copy the formula for all cells in column K
  17. Type “New Price” in cell H1
  18. Type, “=INDEX($K$2:$M$40000,MATCH(B2,$K$2:$K$40000,0),3)” in cell H2
  19. Copy the formula for all cells in column H
  20. Insert filters for all columns in row 1
  21. Click the filter in column H
  22. Scroll down to the bottom of the numbers filtered and unselect #N/A
  23. Create a new tab in the spreadsheet near the bottom of the page, Sheet 2
  24. Copy columns A through H in Sheet 1 and paste in cell A1 in Sheet 2
  25. Insert filters for all columns in row 1 in Sheet 2
  26. Sort column G by A to Z
  27. Delete any parts that do not have an Alliance Product Code
  28. Remove the filters from row 1 in Sheet 2
  29. Move the Sheet 2 tab in front of the Sheet 1 tab near the bottom of the page
  30. Save and close the Service Parts List Prices Manual (Excel) – US Order Desk file and the Alliance W1 spreadsheets
  31. Open Spire and click on the Tools option near the top of the page and select the Import option
  32. In the window that opens, in the upper right corner click on the arrow down for Import Type and select Inventory
  33. Click on the browse files button to the right of the source field
  34. Select the Alliance W1 spread sheet in the following location S> Price Updates>Year>Alliance
  35. Click on the browse button next to the destination field and select the folder S:/PRICE UPDATES/2022/Alliance
  36. Click on the browse button next to the failure field and select the folder S:/PRICE UPDATES/2022/Alliance
  37. In the Import File options and Spire Fields options select Warehouse in both fields and click the Match button
  38. In the Import File options and Spire Fields options select Part No in both fields and click the Match button
  39. In the import File options and Spire Fields options select New Price in left field, and sell UOM: Selling Price 1 on right and click the Match button
  40. Select the Has Header Row check box
  41. Click the Import option near the top of the window
  42. Repeat steps 7 through 42 for warehouses W2, W3, W4, W5, and trucks 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 14, 15, 16
  43. Do not import for inactive warehouses, inactive trucks or E1/shop