Information Systems:Upload Vendor Price Lists

Vendors can send us spreadsheets of upcoming price changes. They are all in different formats, may or may not include UWD's item number, and can include the vendor's entire range of products; not just the ones we carry.

This means it was a lot of work for the buyers and buyer's assistants to link the items on the spreadsheets with our items, and enter the new prices.

We developed an flexible import, edit, and update process in WebSmart.

Upload Price List

Go to InfoNet / uniVIEW / Purchasing / Upload Vendor Price List.

Either the vendor item number, UPC, or uniPHARM's item number must be in the spreadsheet in order for the computer to match with our item master file. DIN or NPN are not enough as they are the same for different units of measure. But they can help the buyer manually link to our item number.

If there are spaces or dashes within the UPC number, the program will remove them.

If leading zeros have been dropped for any of the identifying numbers (which is what happens in a numberic field in a spreasheet), the program will put them back.

The vendor selling price to either *ANY, MAI, or CGY must be there. If they are both missing, the program will interpret the line as being headings or a comment, and will not include it.

The program may misinterpret headings or comments as items; you will have to omit them when you are reviewing the price list.

You do not have to reformat the price columns; the program will remove commas and dollar signs.

Save as a 'CSV (comma delimited)' file - NOT 'CSV (Macintosh)'.

CSV File - click on browse to locate the CSV file you have created, and select it

Planner - This is just used to help keep track which price lists you are to review and update.

Vendor - This is used when linking to our item master using the vendor item number - it must be accurate.

Description - This is to help you keep track of what each price list is.

Effective Date - This is the starting date used when the price file is updated. The ending date for the currently effective prices will be changed to the previous day.

If no SRP use CUSU - This will put uniPHARM's current SRP's into the vendor SRP column. You can then see what the GM % would be if they were left as it, and can change them. If, when the price file is updated, the new vendor SRP is the same as the current CUSU, no update will be done.

Indicate columns in CSV file - As every vendor can have their own layout, you will have to tell the program from where to get the data. The minimum columns you must indicate are; one of vendor item number, UPC, and uniPHARM item #; and at least one of the three price fields. DIN, NPN, and description are to help you make sure the links are correct, and to update our master file to make the link. The unit of measure is to help you decide when a conversion is required.

- Suggested retail price. This will be in the same unit of measure as the vendor selling price; it will be converted to the same unit of measure as our current CUSU.

Work with Uploaded Price Lists

Go to InfoNet / uniVIEW / Purchasing / Work with Vendor Price Lists.

Typically, you would change the 'include' filter to only show open price lists, the the 'buyer' filter to only show yours.

Be very careful not to click on 'Update' until you have reviewed and corrected the details!

Actions

Cancel

If you need to reload a price list (the columns were wrong, or one of the values keyed while uploading was incorrect) you can cancel it here. It will not actually be removed, but will be flagged so that the lines cannot update uniPHARM's price file.

Update

After you have reviewed the details, you can add the new prices to uniPHARM's price file. Records that either have been approved, or have a blank status and have been linked to uniPHARM's item master, will be included. Current VEBA's and CUSU's will be expired as of the day before the price list effective date, and new ones will be added, starting with the effective date and ending 31 December 2050.

VEBA's can be set up either for warehouse *ANY, or for MAI, CGY, and RET. We don't actually have warehouse CGY any more, but we use the logic to deal with different vendor pricing between BC and Alberta (this difference is mandated by the fact that the two provincial governments sometimes reimburse different amounts for the same drugs). Stores in Alberta, and Medicine Chest stores in the Yukon, have their prices based on the VEBA's for CGY; all others use MAI. RET is set up with the same VEBA's as MAI.

If the vendor has a single price for all provinces, any VEBA records - whatever warehouse they are for - will get that price.

If the vendor prices by province, but they happen to be the same, any VEBA records - whatever warehouse they are for - will get that price.

If the vendor prices by province, they are different for BC and Alberta, and our VEBA records are by warehouse, MAI and RET will get BC prices, and CGY will get Alberta prices.

If the vendor prices by province, they are different for BC and Alberta, and our VEBA record is *ANY, it will be expired, and records by warehouse added; MAI and RET will get BC prices, and CGY will get Alberta prices.

Details

Will show you the lines that were uploaded from the CSV file, allow you to correct the link to uniPHARM's item master file, change the new SRP, then either approve for or omit from the update.

Display

There is a lot of information to see, and it will not all fit across the screen. But it does not all have to be shown at the same time. When you are checking the matching between the vendor spreadsheet and uniPHARM's item master file, you only need to see the vendor link fields; not prices and dates.

Once you have matched as many items as you can, you no longer have to see the vendor link fields. If you are working with pharmaceuticals, you need to look at all the vendor selling prices; otherwise you only need to see 'prices for any province'.

If you don't need to see the effective dates, don't show them on the page.

No matter what the selections are, all fields will be written to Excel.

Validate

The first step is to check the matching between the price list and our item master file. To reduce the data on the screen, you can say 'no' to showing all fields except for the vendor links.

Select 'similar' from the 'include' drop down box to look at items from the spreadsheet that the system was not able to match to UWD's item master file, but did find items with similar vendor item numbers (for example, we don't have '1018' on our file, but we do have 'B1018'). The 'vendor item #' column can show up to 4 numbers. The one listed first is the one being used to link. You can click on any of these numbers, which will bring it to the top of the list, and use it to link. If the UWD item shown is not correct, click on the original number; which will bring it back up to the top of the list and break the link.

Then use the 'include' drop down box to look at items that are 'unmatched', and the 'status' drop down box to include items that have not yet been flagged. Use InfoNet 'work with items' to try to match the items listed - UWD's file may have missing or incorrect vendor item numbers or UPC's. If so, correct them, and click on 'link'. If a match cannot be found, click on 'omit'. Either way, the line will no longer show on this page. You don't have to omit all the unmatched lines - they will not update the price file anyway, and you can remove them from this page by only including 'matched'.

If there are a lot of unmatched items, you may choose not to check them all manually, but to wait for the vendor to inform you that our purchase orders prices are wrong.

When you have matched all that you can, change the include drop down box to 'matched'. Now you can excluding vendor link fields, and include effective dates, and the prices by any province. Include prices by province if the vendor you are working with uses them.

Then use the 'status' drop down box to look at 'future prices'. This will show items that have prices that start after the effective date of the price list you are working on. Again, you can either correct the price file and click on 'link' to check it again, or click on 'omit' to exclude this line from processing. Items with future prices will not update automatically, as the program cannot decide how to deal with them.

You can now change the effective dates to stop showing.

Change 'status' to 'not flagged'. This will let you see the records you have not yet reviewed.

The next thing to look for is unit of measure issues. Sometimes the vendor sends us pricing by 'each', when we purchase by 'box' or 'case'. To find these, click on the column heading for 'cost chg %'. If you see, for example, an item with a cost change of -97%, and a conversion factor of 36, that conversion factor has to be applied to the vendor price. To do this, click on the conversion factor (right hand column). In this example, the change is recalculated to .37% - which is actually just rounding. Click on the column heading again to sort by highest % first - there could be a unit of measure conversion issue in the other direction.

If there is a unit of measure issue that clicking on the right hand column doesn't correct, you can enter a value manually in the 'vendor UoM conversion' column.

There are three more columns that can be used to find issues; vendor GM %, UWD GM %, and SRP change %. On each of these, click once to sort lowest to highest and check for extreme values, then click a second time to sort highest to lowest, and check the values again. Any out of range values could indicate items that were incorrectly linked, or have unit of measure problems. Click on either 'approve' or 'omit'. These statuses can be changed if you change your mind.

Vendor GM % - If this value is very high or very low, there could be an error on the spreadsheet.

UWD GM% - If this value is very high or very low, check the unit of measure on the VEBA and CUSU. If incorrect, fix it, and relink this item.

• • Note that the two gross margin fields are comparing the SRP to the shareholder price. Discontinued items can be in markup class 'INACTIVE', which has 100% markup; so the GM% shown will be very low. The reason for this is to make these items stand out if they appear in any list.**

Cost or SRP Change % - If these values are very high or very low, check that the vendor unit of measure matches that of our VEBA. If it doesn't key in the value by which you will multiply the vendor's selling price to have it match our VEBA. Then click on 'update'.

Note that after you make a correction to one of these, and the value on which you are sorting is changed, so the position of the item in the list will change.

Suggested Retail Prices

Rule is -

  .01 to  4.99 - ends with 9

 5.00 to  9.99 - ends with 29, 49, 69, or 99

10.00 to 14.99 - ends with 49 or 99

15.00 and over - ends with 99

The SRP's received from the vendor have been converted to the same unit of measure as our CUSU. If this has happened, or if you manually changed it, the original SRP will show above the input capable field.

You can change the vendor SRP's, and click on 'Update SRPs'. This will be the new CUSU that will be added. If you redo the link to our item master, this will be reset to what was in the CSV file.

If you don't want to change our CUSU to the vendor's SRP, but want to leave it as it is, click on the number in the 'CUSU' column. This will move it into the vendor SRP field. When our pricing file is updated, CUSU's and VEBA's that don't change will not be written to the file.

Action

You have three options -

approve - will flag this item to update uniPHARM's VEBA and CUSU file. A blank status will also update this file, but it makes it easier to review the price list if items you have completed drop off the list of 'not flagged' lines. You cannot approve a line that has been cancelled, has already updated the cost file, or has any future pricing.

omit - will flag this item to NOT update uniPHARM's VEBA and CUSU file, and to drop off the list of 'not flagged' lines. You cannot omit a line that has been cancelled or has already updated the cost file.

link - this has multiple purposes. You can manually add the vendor item number or UPC to our cross reference file, then try again to match the line to our item master file. If you have approved or omitted a line, this will undo it. If you have removed future prices from an item, this will reset the flag. It will also reset the vendor SRP to what was in the CSV file.