This How-To guide details how to take an excel spreadsheet of prices from a vendor, and view/update those prices with a SQL report.

The goal is to take an excel spreadsheet and put it in a hot folder under a specific name to allow a SQL report to read this file, and then give you a preview report of prices that would change based on the Excel. The report matches up the SKU in the excel file with the SKU that is detailed on the “Part” tab of the part setup screen for a given part. The second half involves running a report that will do the actual update in the system and requires a SSLIP restart to take effect.

This How-To guide contains a report that runs an UPDATE function on the database. There is the potential that if fields do not match up correclty, you could overwrite the incorrect value in your system and lose the original pricing for your parts. ALWAYS run the preview version first to verify that the parts that will be updated are correct and that they are updating to their correct price.

A few things must be initially setup in the system for this method to work:

• All parts must be assigned a SKU according to the value that a vendor would use.
  • If ANY parts have a matching SKU, both would be updated with this method
• The excel file must be formated in a specific way, will list the details below.
• A hotfolder and a reserved name for the update file must be determined beforehand
  • For the purposes of this How-To, we'll be naming the file “Current Price Update.csv”, and a hotfolder of “C:\Hot Folder\Excel Pricing Spreadsheets

• The excel file will consist of 2 columns, and however many rows for parts that are being updated
  • Column 1 should have the header of SKU. This will contain the vendor's SKU that matches up with the parts in Control
  • Column 2 should have the header of YourPrice. This is the vendor's current price for the given part that you are updating.
• After making sure the file is formatted in this way, do a Save As and save the file as a .csv

• The folder patch that will be used is going to be in relation to the SSLIP's access on the server. No local or desktop path's should be used.
• EX: C:\Hot Folder\Excel Pricing Spreadsheets\ would be a location on the actual server on the C:\ drive and not on the computer that you may be working from

1. Go to Setup → Reporting Setup
2. Click on Reports on Main Menu, and click on “Add…”, and select New Report Group. Name this group Pricing Updates
3. Select this new group and click on “Add…” again, this time select New SQL report.
4. Name the report “View Pricing Updates”, and make sure the button labeled “SQL” is selected.
5. Use this query for the report:

IF OBJECT_ID(N'tempdb..#MyPrices', N'U') IS NOT NULL
DROP TABLE #MyPrices;
-- This query views the parts pricing to be updated
-- based on the CSV files delivered.
--
-- Before running, be sure to update the text file path.
--
-- Create a temporary table to import it in
CREATE TABLE #MyPrices(
[VendorSKU] [VARCHAR](50) NOT NULL,
[YourPrice] [FLOAT] NOT NULL
) ON [PRIMARY]
-- Enter the path to the comma delimited file.
-- Note: This parth is relative to SQL Server user,
-- not the user running it. (No Desktop or My Documents paths.)
--
bulk INSERT #MyPrices
FROM 'C:\Hot Folder\Excel Pricing Spreadsheets\Current Price Update.csv'
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
;
SELECT ItemName, SKU, '$' + CAST((YourPrice) AS nvarchar(20)) AS VendorPrice, '$' + CAST((UnitCost) AS nvarchar(20)) AS MyPrice, '$' + CAST((YourPrice-UnitCost) AS nvarchar(20)) AS Difference, CAST(Round(((YourPrice-UnitCost)/YourPrice)*100,2) AS nvarchar(20)) +'%' AS Percentage
FROM Part LEFT OUTER JOIN #MyPrices ON Part.SKU = #MyPrices.VendorSKU WHERE SKU = VendorSKU
;
DROP TABLE #MyPrices
;

6. Make sure the option under the SQL for “Create View and Save” is checked

7. Go to the Options tab, and make sure under Show Detail, that the file type is changed to “Excel”, and you give it a name of something like “Price Change View”.

1. Go to Setup → Reporting Setup
2. Click on Reports on Main Menu, click on the report category of Pricing Updates from the previous step
3. Click on “Add…” , and select New SQL report.
4. Name the report “Update Pricing Changes”, and make sure the button labeled “SQL” is selected.
5. Use this query for the report:

IF OBJECT_ID(N'tempdb..#MyPrices', N'U') IS NOT NULL
DROP TABLE #MyPrices;
-- This query updates the parts pricing
-- based on the CSV files delivered.
--
-- Before running, be sure to update the text file path.
--
-- Create a temporary table to import it in
CREATE TABLE #MyPrices(
[VendorSKU] [VARCHAR](50) NOT NULL,
[YourPrice] [FLOAT] NOT NULL
) ON [PRIMARY]
-- Enter the path to the comma delimited file.
-- Note This parth is relative to SQL Server user,
-- not the user running it. (No Desktop or My Documents paths.)
--
bulk INSERT #MyPrices
FROM 'C:\Hot Folder\Excel Pricing Spreadsheets\Current Price Update.csv'
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
;
UPDATE Part
SET UnitCost = YourPrice,
ModifiedDate = GetDate(),
Part.SeqID = Part.SeqID + 1,
LastModifiedDate = GetDate(),
ModifiedByUser = 'Pricing Update SQL'
FROM #MyPrices WITH(nolock)
WHERE #MyPrices.VendorSKU = Part.SKU
;
UPDATE Inventory
SET AverageCost = YourPrice,
ModifiedDate = GetDate(),
Inventory.SeqID = Inventory.SeqID + 1,
ModifiedByUser = 'Pricing Update SQL'
FROM #MyPrices WITH(nolock)
LEFT OUTER JOIN Part WITH(nolock) ON #MyPrices.VendorSKU = Part.SKU
WHERE Part.ID = Inventory.PartID AND Inventory.ClassTypeID = 12201 AND Inventory.DivisionID = 10
;
SELECT Part.ItemName, Part.UnitCost, Inventory.AverageCost FROM Inventory WITH(nolock) LEFT OUTER JOIN Part ON part.ID = Inventory.PartID LEFT OUTER JOIN #MyPrices WITH(nolock) ON Part.SKU = #MyPrices.VendorSKU WHERE Part.SKU = VendorSKU AND Inventory.ClassTypeID = 12201 AND Inventory.DivisionID = 10
;
DROP TABLE #MyPrices

6. Make sure the option under the SQL for “Create View and Save” is checked

7. Go to the Options tab, and make sure under Show Detail, that the file type is changed to “Excel”, and you give it a name of something like “Updated Price Changes”.

This will show a list of all the parts that were updated and what the price was changed to.

The last step is to make sure everyone is out of Control, and to restart the SSLIP. This is to ensure that every system sees these updates to the prices and that everyone is looking at the most current pricing.

Contributor: kking, Cyrious

Date: 5 / 27 /2011

Version: Control 4.5