Overview

You will receive a recall notice from a buyer in your e-mail inbox. Most of the time the recall will come from Ruby (pharmaceutical) but it can come from any buyer. As soon as you receive a recall notice you must start this process as Health Canada mandates that recalls are performed immediately and are considered priority one. Ultimately, this process is about preparing the recall worksheet that the buyer has created in such a way as to allow all others in the recall chain to process the incoming recalled product, as per the recall and use the recall worksheet to do so. There are certain things that must be done manually to the recall sheet in order to allow this to happen. What follows are the steps that must be taken to adequately prepare a new recall worksheet.

Instructions

Preliminary Steps

When a new recall notice is received by e-mail the very first step is verifying what type of recall it is. There are four types: Type I – Public Advisory, Type II – Advisory to Pharmacies, Type III – Advisory to Wholesalers & Voluntary Withdrawal. The type of recall determines how much manual preparation needs to be done on the recall sheet before the rest of the departments can use it. The buyers are supposed to indicate the type of recall in the subject of their e-mail but sometimes they neglect to do this. Either way it is good practice to go into the recall sheet (they usually provide a copy in their e-mail) by double clicking and scrolling down on the first tab (uniPHARM Recall Notice) and seeing which type of recall is selected on the ‘CLASS OF RECALL’ section at the bottom of the page. If the buyers don’t have a copy of the recall notice included in their e-mail you can find it by going into the GMP directory on Superserver (\\Superserver\GMP\GMP Recall Log\ Recalls 20XX). Recalls that are Type I – Public Advisory, Type 2 – Advisory to Pharmacies (most common) or Voluntary Withdrawal are handled the standard way. Type 3 – Advisory to Wholesalers do not require that we send the recall notice to our stores so they are handled a different way. The following steps are for those recalls (Type I, Type II & Voluntary Withdrawal) that fall into the standard category. A modified set of instructions will follow for the Type 3 – Advisory to Wholesalers.

Standard Method – Phase I (InfoNet)

When a new recall notice is received for a Type I, Type II or Voluntary Withdrawal the first step is going into InfoNet and creating a recall report for the item being recalled. There may be more than one item being recalled so this needs to be done for each item.

Log into InfoNet and under the Inquiry column select Recalls.
Type in the item number and select the date range.

Typically I use a two year date range from yesterday. For example, if the recall is sent to my inbox on Aug. 3, 2014 I will select a date range for the item from Aug. 2, 2012 – Aug. 2, 2014. The reason behind this is because I want a full day of transactions and depending on the time of day the recall notice is sent it most likely will be a partial day. I also go two years back to hopefully include all instances of the LOT numbers that the recall is referencing. We do not track LOT numbers so going back two years helps catch them.

Once all fields are filled select Search and wait for the results to display below.
When results are displayed select Export to Excel and you will get an information box on the bottom of your screen.
Select the down arrow next to the Save box and choose Save As.
Choose the Desktop as the save location (I will often change the file name to the item number to avoid confusion if there are more than one item being recalled) and select Save.
When the download is complete close the information box at the bottom of your screen by clicking on the X at the far right of the box.
Repeat this process for each of the items being recalled to speed up the process.
Once all recall reports have been created and saved you can log out of InfoNet and close it down.

The next step involves transferring the information over from the reports that were just created from InfoNet into the actual recall. To do this you have to open up the actual recall worksheet file from the GMP directory and then using the same instance of Excel you have to open the corresponding file on your desktop. The reason behind this is because in order to copy the information over between the two files they must be both opened in the same instance of Excel.

Find the recall worksheet in the \\Superserver\GMP\GMP Recall Log\ Recalls 20XX directory and open it with a new instance of Excel.
Using that same instance of Excel open the corresponding recall report saved on your desktop.
Next on the top of your screen you will see a yellow box indicating that the file is in Protected View. Select Enable Editing and it will go away.
The recall worksheet is set up to accept the data from the InfoNet recall report expect for the Attention column. Delete the Attention column but make sure that any data that may be in there is moved over to Address 2 or Address 3 first.
Next the data needs to be sorted. Highlight everything from A4 to KX (whatever the last row is) and then select Sort from the Data menu.
Configure the sort as follows: Make sure ‘My data has headers’ is unchecked. Sort by Column C. Select Add Level and Then by Column B. Select OK to begin sort.
It’s a good idea to verify that there are no duplicates as this can sometime happen (I’m not sure why).

To check for duplicates you can either scan the list manually and delete any duplicates or you can insert a column in Column A and create a formula to calculate whether there are any duplicates. The formula I use is: =IF(B5-B4=0,"DUPLICATE","") and I place this on the cell A5 and then copy it down. It will then indicate where the duplicates are by matching the Customer numbers. All you need to do is delete the duplicates and then delete Column A.

The last step in this process is to highlight all information. Make sure you highlight just the data and not the column headers (from A4:KX).
Next open the recall worksheet and choose the Cust. Info tab.
In the Review menu choose Unprotect Sheet and type in the password: recall
Next go back to the InfoNet recall report and copy the highlighted selection.
Switch back to the Recall Worksheet and right click in the cell A31 and choose Paste Values. This will copy all of the selected data over and keep the formatting consistent with the Recall worksheet.
Next I like to hit CTRL-Home just to reset the cursor back to the Home position and then chose Protect Sheet from the Review menu.
Type: recall in the “Password to unprotect sheet box” and select OK. Then retype the password: recall and select OK. Now you should be unable to select any of the stores that you just pasted into the worksheet.

You now have to slightly change the sorting on the recall file on your desktop and then copy and paste the data into the Recall Tracking worksheet in the recall form.

Select the Recall Tracking worksheet and in the Review menu choose Unprotect Sheet and type in the password: recall
This worksheet has handy macro control built into it to automatically hide and unhide columns. Make sure the columns are first unhidden so click on the Unhide Columns button and type in the password: recall
Next re-sort the recall report as follows: Sort by Column B only A to Z. Select OK to begin sort.
Copy the sorted date (just like in step 20) and paste values in cell A18 of the Recall Tracking sheet.
Now click the Hide Columns button at the top of the sheet and type in the password: recall to hide the columns: E, H & K.
Next I like to hit CTRL-Home just to reset the cursor back to the Home position and then chose Protect Sheet from the Review menu.
Type: recall in the “Password to unprotect sheet box” and select OK. Then retype the password: recall and select OK. Now you should be unable to select any of the stores that you have just pasted into the worksheet except the area under the Recall Acknowledgement section.
Finally close down the recall spreadsheet and delete it as you will no longer need it.

You have now completed phase one of the standard manual recall process. The reasoning behind doing this is so that we can provide the buyer with a list of customers that have purchased the recalled product within the last two years. They then can go and fax all of the CU stores as they are not part of the regular SH fax that goes out with each recall.

Standard Method – Phase II (Recall Worksheet)

Phase II is all about preparing the Recall Worksheet for everyone else in the recall process to use it correctly. The remaining areas to be manually adjusted are the Store Return to DC tab and the Product Return to Vendor tab.

First select the Store Return to DC tab and you will see all of the stores that purchased the recalled item listed down the left side of the sheet. Again select Unprotect Sheet from the Review menu and type in the password: recall and select OK.
Highlight all of the customers starting from A35:BXX and right click and choose Format Cells…
Choose the Protection tab and check the Locked box and then select OK.
Next press CTRL-Home to reset the cursor back to the Home position.
The LOT numbers have to be entered across the top of this sheet in the cells between the Date and Total columns. To do this go to the uniPHARM Recall Notice tab and select all of the LOT numbers listed in the LOT numbers box. They should be separated by a comma. Highlight the numbers and copy them and then return to the Store Return to DC tab.
I like to go to cell AF33 and paste the LOT numbers there. If there is only one LOT number then proceed to step 7, otherwise continue. From the Data menu select Text to Columns and select the Delimited radio button and then select Next. Make sure only Comma and Space are checked under the Delimiters section and then select Next. Choose text as a data format for each of the columns by clicking into each column with the mouse and choosing Text and then select Finish when complete. You should now see all of the LOT numbers in separate columns.
Highlight LOT numbers and copy them and then right click in D33 and choose Paste Values. Delete any excess columns that are blank. It is important to note here that are only 6 blank cells to begin with as a default number. If there are more LOT numbers then blank cells to copy them to then you will have to insert additional columns until you have the correct number of empty cells to fit all of the LOT numbers. I usually go to the ‘I’ column and insert from there. Also be aware that any columns you insert you will have to copy the SUM formula over that is in Row 30 as this will be missing. Just drag the formula from an adjacent cell over all inserted cells.
Once finished delete all of the LOT numbers from AF30, press CTRL-Home to reset the cursor and select Protect Sheet from the Review menu.
Password to unprotect sheet: recall and press OK and then enter the password: recall a second time and press OK to lock the page.

The last worksheet we need to manually adjust is the Product Return to Vendor worksheet. This worksheet is used by the Returns Department to match up the items returned from the stores to Debit Notes returning the product to the vendor to ensure that nothing is missed.

Once again select the Unprotect Sheet from the Review menu and use the password: recall to unlock the worksheet.
You should see the LOT numbers listed down the left side of the worksheet. However, there is most likely additional words and zeros listed that we do not need. Delete everything that is not a LOT number.

If you had to add additional columns for LOT numbers on the Store Return to DC worksheet then the LOT number column in the Product Return to Vendor worksheet needs a bit of adjusting. The formulas in the A column picking up the LOT numbers from the Store Return to DC worksheet need to manually manipulated and corrected to ensure they pick up all of the LOT numbers. The reason it is broken is because adding columns creates a hole in the formula. I suggest manually going in and changing the formula to correct it until all LOT numbers are listed and then you can delete the rest and then continue on with step 2 above.

For the LOT numbers left highlight them and right click and choose Format Cells…
In the Protection tab check the Locked box and select OK.
The last thing I do is to make sure that the formula is accurate in the cell W5. It will be referencing the Total SUM cell on the Store Return to DC worksheet so just make sure this matches.
Finally lock the sheet back up again by selecting Protect Sheet from the Review menu and again using the password Recall and select OK and then enter recall again and select OK.

The last thing I do is to return to the Cust. Info worksheet and select the Home menu and then select Save. Once this is done I reply to the e-mail that I received from the buyer regarding the recall and say: the Customer Information for this recall has been completed. This process is done for each recall.

Standard Method

This method is only to be used when the recall is a Type III – Advisory to Wholesalers because we do not actually have to find the sales information regarding the recalled product as the product is only being recalled from our warehouse to the vendor. Because of this getting the report from InfoNet is no longer a necessary step and shortens the entire process. Basically all of the steps in Phase I for the standard method can be skipped. All we are doing is going straight to the recall worksheet and going into the Cust. Info tab and inserting the following phrase in cell D33:

WHOLESALER / DISTRIBUTOR ADVISORY – NO CUSTOMER INFORMATION

Make sure to paste special or match destination formatting. Once this information line is entered, the worksheet is locked back up and you can then continue on with Phase II of the instructions. Nothing else changes at this point except that when the method is complete and you are going to e-mail the buyer I tend to reiterate that this was a Type III recall and therefore there is no Customer Information but the recall sheet is ready for use.

Processing Recalls

Here's what to do when you're in charge of doing a recall.

Posting Product Recall Notices Online

Recall notices are sent via the GMP Recall group. This will provide you with a trigger to go to the “GMP Recall Log” located on the Superserver.
ONLY recalls with a selected Distribution level of: 'Retail Pharmacy notification' are to be posted to the website.

DO NOT POST RECALLS WITH A DISTRIBUTION LEVEL OF WAREHOUSE LEVEL ONLY.

Now that you are in the “GMP Recall Log”, select the sub-folder containing the original Excel recall notice(s).
Open the Excel document(s).
Click the “uniPHARM Recall Notice” tab on the bottom of the sheet.
Click File > Save As > change the type to PDF. Alternatively, you can print the recall notice and select the 'Microsoft Print to PDF' printer.
Leave the file name as is.
Save the PDF document in the folder named “Converted Recall Notices for the uniPHARM Website”.
Repeat the above steps for each product recall notice received in the e-mail.
Login to the Exware admin site for uniPHARM; http://www.unipharm.com/cgi/login.cgi (or login to unipharm.com , then launch https://www.unipharm.com/cgi/ex.cgi/admin)
Under the 'Content Management' area click the “Documents” icon.
This will open the 'Libraries in uniPHARM Wholesale Drugs' listing. Select 'Product Recall Notices' from the list.
To add a new recall PDF document select 'upload' near the top of the screen.
Select the 'Browse...' button under the Choose document section.
Browse to the PDF file that was saved in the Recall notices for website folder on step 6 above. Title and Description fields to be left blank.
To upload select the green 'Submit' button on the bottom of the page.
Once uploaded, click the 'Publish' menu button located near the the top of the screen and this will open an additional pop-up window confirming the document has been published. There are also links provided that can be selected to test and make sure the document is showing correctly.
Click the “Close Window” to exit the pop-up screen. Navigate back to the Product Recall Notices page to upload another document or close the Documents page by selecting the 'x' beside the Documents tab on the left that is highlighted in blue.
This process will automatically post the document on the 'Product Recall Notices' section of the members website area of unipharm.com. Navigate to that area of the website and make sure the recall posting is showing and selecting it brings up a copy of the recall notice for download or print.

Information Systems:Preparing and posting the GMP Recall Worksheet

Contents