Recognize and Prevent Medication Diversion

1. Pyxis and Meditech Data Recognize and Prevent Medication Diversion Jamie McDonald

2. Proactive Diversion Report allows the facility to view controlled medication activity Topics • Primary objective • Technical issues • Data decisions • Reports developed

3. Primary Objective Monitor controlled medications withdrawals and administrations to identify possible diversions before it gets out of hand

4. Items to Resolve • Archive Files from Pyxis (technical issues) • Automate the archival process • Automate the process of importing Pyxis data into the Data Repository • Measuring the data (data issues) • What method of measurement? • How are we going to display the data? • User names that don’t match in Pyxis and MT

5. Automating Pyxis Archive Solution • We have a PC that has the location of the Pyxis Archive Files mapped to the W drive \\172.20.100.63\f$\v4\maintain\archives\ • We move the Pyxis Archive Files to a network folder.   “S:\Pharmacy\PyxisNT\” • At 5am each day, the files are moved from one location to another.  This is accomplished using Window’s Task Scheduler and a batch file.  “CopyPyxisArchiveData2NetworkFolder.bat” • The batch file command is made up of one line of text.  “move w:\*.* s:\Pharmacy\PyxisNT\”

6. PyxisMedTransactions Data Import • Task: Automate the moving of data from daily files (through yesterday) into database tables. • Wrinkles: • "Daily" files are moved by a human to their folder. Which means yesterday's file may not have been moved into the folder yet today. • Perhaps more than just yesterday’s file may not have been moved into the folder (vacations happen). • This automated daily job may not have run yesterday for some reason.

7. PyxisMedTransactions Data Import • Solution Algorithm: • Givens: • File has name of mmddyy.PYX and will always be in the same folder. • General Solution: • Generate all filenames holding data – starting with the last date this automated job ran through yesterday. • Pull data from all those files and insert into a table in the DR.

8. PyxisMedTransactions Data Import Algorithm Schematic Start Run DTS to pull Filename from “Found” table, Move data from “Found” File to “Today’s-run” table Update “Today’s Run” table Generate today’s filename Any Missing Files Found? Move Missing Filename to “Found” table Copy “Today’s-Run” Table to Final Destination table Yes Done No

9. PyxisMedTransactions Data Import • Specific Algorithm: • 1. Create a Stored Procedure that will generate all possible filenames from the last date this automatic job was run through yesterday to be sought in the folder and insert the names into a "Missing" table in the database. • To do the above, we pull the “Last Date" that the "Missing" filenames were generated from the Final Destination table. • Start date for generating the "Missing" filenames is the last date they were generated + 1 day. • If the date for the filename we just generated is not yesterday, insert the filename into the “Missing” table and continue generating the next filename by adding 1 day to that last filename.

10. PyxisMedTransactions Data Import • Specific Algorithm (continued) • 2. Create a Stored Procedure to see if any file in the "Missing" table is now in the folder, if so then move the filename from the “Missing” table to the “Found” table (remove the filename from the “Missing” table).

11. PyxisMedTransactions Data Import • Specific Algorithm (continued) • 3. Create a DTS that walks down the “Found” table to import the data from the filenames obtained from the “Found” table until no more filenames are left in the “Found” table. • DTS pulls the top filename from the "Found" table and stores in a global variable. • If no filename is found (“Found” table is empty) then skip move data step otherwise set DataSource to be the global variable. • Move Data from DataSource to “Today’s-run” table. • Delete the top filename from the "Found" table. • If “Found” table is not empty loop to first step of DTS. • "Found" table is empty – DTS returns success result.

12. PyxisMedTransactions Data Import The DTS workhorse… Dynamically set global variable filename from top of “Found” table for use by Connection1. If “Found” table is empty we will skip Transform Data Task. Else reset DataSource for Connection1 to be global variable filename. Move data from the Connection1 DataSource file to “Today’s-run” table in the database. Update “Found” table. If more filenames in “Found” loop back to set global variable else report successful completion.

13. PyxisMedTransactions Import Data • Specific Algorithm (continued) • 4. Create a stored procedure to update a couple of columns in the "Today's-run” table (per customer request). • 5. Create a stored procedure to copy all "Today's-run" table data to final destination table if it exists. Whether there is data in “Today’s-run” table or not, update “Last Date” field in final destination table to be today’s date.

14. PyxisMedTransactions Import Data • Finish automating the task by creating a job with these 5 steps and schedule it for nightly run.

15. User Names….. What did we do? Created a table that maps all Meditech UserID’S and Pyxis UserID’S that are not the same • Issues • Table has to be maintained by pharmacy and IT. • Current Maintenance • Communication between PHA on Pyxis user id changes and Monitor Meditech user name changes.

16. General Data Decisions • Use statistically significant data to determine outliers • Display Meditech data along with Pyxis transaction data. • All transactions performed in Pyxis for a particular medication and location • Summary of transactions per patient and medication.

17. Reports Developed • Proactive Diversion Report, main report Parameters passed (data range, location, medication) • Patient Details by Nurse - drill down report (1)Parameters passed (date range, nurse, medication) • Patient Details by Medication - drill down report (1a)Parameters passed (account number, medication) • Pyxis Transactions by User - drill down report (2)Parameters passed (date range, nurse, medication) • All Medications for Nurse and Selected Date Range - drill down report (3)Parameters passed (date range, nurse)

18. Report on Report Manager View Dropdown selection for a single medication or all Dropdown for all locations or a specific department The Statistics show the number of days a nurse withdrew a medication for a location, the number of doses, the number of patients, the doses per day, and the standard deviation of the doses per day along with the total withdrawals from Pyxis and the total administrations in Meditech

19. Exported View of Diversion Report 1. Drill down on patient count 3. Drill down on user name 2. Drill down on count of Days

20. (1) Drill down on Patients from Main Report 1-a. Drill down to patient account for all user transactions for this medication

21. ( 1 a) Drill down on patient from Patient Details by Nurse Displays all medication withdrawals, cancellations, wastes, and administrations

22. (2) Drill down on Days from Diversion Report Displays all transactions out of Pyxis for selected Date Range and Medication for the user

23. (3) Drill down on user name from Diversion Report Calculates stats for all controlled medications for the user and selected date range.

24. Other reports options not shown • Automated time period (last 30 days) • Email to Nurse Managers weekly • Set of a reports to monitor high dollar medications

25. Thank you for attending! Acmeware Inc. Jamie McDonald 781-329-4300 ext. 203 jmcdonald@acmeware.com