Information Systems:Health Canada Drug Product Database
Database
Health Canada makes information on drugs and natural health products available to be downloaded from their web site. The drug files are refreshed at the beginning of every month; the NHP files less often. We display this information in Web Orders, and in item file maintenance and display.
Drug Product Database
- Go to Health Canada’s web site, www.hc-sc.gc.ca
- Click on ‘English’
- Under ‘Main Menu’, click on ‘Drugs & Health Products’
- Click on ‘Drug Products’
- Click on ‘Drug Product Database (DPD)’
- On the right hand side, click on ‘Access the Drug Product Database’
- On the upper part of the page, click on ‘DPD Data Extracts’
- Check the date under ‘Last Updated’ (right hand column). Don’t continue with this process unless this has changed. There shouldn’t be an issue as it is usually updated early on the first work day of the month.
- Download ‘ALL FILES’ (bottom of the list) to your PC; both active (allfiles.zip) and inactive (allfiles_ia.zip) - Open then Open
- Unzip and copy to SuperServer / staging / Health Canada - yes to replace
Natural Health Products Database
- Go to Health Canada’s web site, www.hc-sc.gc.ca
- Click on ‘English’
- Under ‘Main Menu’, click on ‘Drugs & Health Products’
- Click on ‘Natural Health Products’
- On the right hand side, click on ‘Licensed Natural Health Products Database’
- Scroll down to the bottom of the page, and click on ‘Access the Licensed Natural Health Products Database Data Extract’
- Health Canada updates these files less often the drug product ones. Check the last updated date (at the top of the list of files) to see if it is necessary to continue.
- Download ‘All NHP Files’ to your PC.
- Go to SuperServer / staging / Health Canada and move the previous files to the archive folder.
- Unzip and copy to SuperServer / staging / Health Canada
Update Health Canada Files on Bart
We have had issues with the Health Canada data files. They are apparently extracted manually, which means there can be errors. One month there were no record delimiters; which meant we couldn’t use them. Another time instead of all related files being zipped together, each was zipped separately, then zipped together.
Another issue is that users EODJOB and SHEILAV cannot access SUPERSERVER from the i, so the scheduled job has been changed to run under JOHNT (2014Dec04).
To validate the data files, manually run the rebuild in the test environment (PL).
- From a command line in ASW, key in 88, press Enter, key in PL, and press Enter twice.
- Key in SBMJOB CMD(CALL PGM(HCDBBLD01C)) JOBQ(QS36EVOKE) and press enter. (This is run from job queue QS36EVOKE so that production jobs are not delayed.) This program is being submitted to run in batch – you won’t see it on your screen.
- To see this job running, key in WRKACTJOB SBS(QBATCH) and press enter.
Work with Active Jobs BART
10/01/14 12:16:27
CPU %: .0 Elapsed time: 00:00:00 Active jobs: 563
Type options, press Enter.
2=Change 3=Hold 4=End 5=Work with 6=Release 7=Display message
8=Work with spooled files 13=Disconnect ...
Current
Opt Subsystem/Job User Type CPU % Function Status
QBATCH QSYS SBS .0 DEQW
QDFTJOBD SHEILAV BCH .0 PGM-HCDBBLD01C RUN
Parameters or command
===>
F3=Exit F5=Refresh F7=Find F10=Restart statistics
F11=Display elapsed data F12=Cancel F23=More options F24=More keys
- Press F5 or F10 to refresh.
- When completed the “RUN” will disappear
- Key in the command DSPFD UWDASWVOLD/HCDBDATAP and press enter. Then press page down until you see ‘Current number of records’ (about three times). There should be around 26,000 records in it (as of Nov 2014; it will increase with time). F3 to exit
Display Spooled File
File . . . . . : QPDSPFD Page/Line 2/11
Control . . . . . Columns 1 - 130
Find . . . . . .
*...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8....+....9....+....0....+....1
Number of key fields . . . . . . . . . . . : 1
Record format . . . . . . . . . . . . . . . : HCDBDATAR
Key field . . . . . . . . . . . . . . . . : HCPRDC
Sequence . . . . . . . . . . . . . . . : Ascending
Sign specified . . . . . . . . . . . . : UNSIGNED
Zone/digit specified . . . . . . . . . : *NONE
Alternative collating sequence . . . . : No
Sort Sequence . . . . . . . . . . . . . . . : SRTSEQ *HEX
Language identifier . . . . . . . . . . . . : LANGID ENU
Member Description
Member . . . . . . . . . . . . . . . . . . : MBR HCDBDATAP
Member level identifier . . . . . . . . . : 1140529130244
Member creation date . . . . . . . . . . : 05/29/14
Text 'description' . . . . . . . . . . . : TEXT Health Canada Data Base - consolidated
Expiration date for member . . . . . . . : EXPDATE *NONE
Access path maintenance . . . . . . . . . : MAINT *IMMED
Access path recovery . . . . . . . . . . : RECOVER *NO
Member size . . . . . . . . . . . . . . . : SIZE *NOMAX
Current number of records . . . . . . . . : 25717
F3=Exit F12=Cancel F19=Left F20=Right F24=More keys
- When this program has completed, go into Web Orders test to make sure the files have been correctly built.
If the files are correct in test, then they can be updated in production – not now, but tonight when stores are not using Web Orders.
- Go into ASW production by keying 88, enter, VA, enter, enter.
- Key in WRKJOBSCDE and press Enter.
- Page down to job HCDATABASE.
Work with Job Schedule Entries BART
10/01/14 12:34:49
Type options, press Enter.
2=Change 3=Hold 4=Remove 5=Display details 6=Release
8=Work with last submission 10=Submit immediately
Next
-----Schedule------ Recovery Submit
Opt Job Status Date Time Frequency Action Date
2 HCDATABASE SAV 09/05/14 06:00:00 *ONCE *NOSBM
__ IMRCHDJOB HLD USER DEF 04:30:00 *WEEKLY *SBMRLS 10/02/14
__ IMRSCKSTS1 SCD *ALL 05:30:00 *WEEKLY *SBMRLS 10/02/14
__ IMRSCKSTS2 SCD 10/01/14 12:35:00 *ONCE *SBMRLS 10/01/14
__ INTERREPLN HLD *TUE 05:00:00 *WEEKLY *NOSBM 10/07/14
__ MAXAVA SCD *SAT 20:00:00 *ONCE *NOSBM 10/04/14
__ MXIOSTATS2 SAV *TUE 12:30:00 *ONCE *SBMRLS
More...
Parameters or command
===>
F3=Exit F4=Prompt F5=Refresh F6=Add F9=Retrieve
F11=Display job queue data F12=Cancel F17=Top F18=Bottom
- Use option 2 to change it.
Change Job Schedule Entry (CHGJOBSCDE)
Type choices, press Enter.
Job name . . . . . . . . . . . . > HCDATABASE Name
Entry number . . . . . . . . . . > 572242 000001-999999, *ONLY
Command to run . . . . . . . . . SBMJOB CMD(CALL PGM(HCDBBLD01C)) JOBQ(QS36EV
OKE)____________________________________________________________________________
________________________________________________________________________________
________________________________________________________________________________
________________________________________________________________________________
________________________________________________________________________________
Frequency . . . . . . . . . . . *ONCE *SAME, *ONCE, *WEEKLY...
Schedule date . . . . . . . . . '10/02/14' Date, *SAME, *CURRENT...
Schedule day . . . . . . . . . . *NONE *SAME, *NONE, *ALL, *MON...
+ for more values _____
Schedule time . . . . . . . . . '06:00:00' Time, *SAME, *CURRENT
Bottom
F3=Exit F4=Prompt F5=Refresh F10=Additional parameters F12=Cancel
F13=How to use this display F24=More keys
- Set it to run tomorrow (the format is MM/DD/YY), and press enter twice.
Check it the next morning to make sure the files have been correctly built.
- Key in the command DSPFD UWDASWPRDD/HCDBDATAP and press enter. Then press page down until you see ‘Current number of records’ (about three times). There should be around 26,000 records in it (as of Nov 2014; it will increase with time). (Note that this command is slightly different than the previous one in that it looks at production instead of development.)
- Go into Web Orders production. (spot check several items, suggestion to look for DIN’s that are newer (higher numbers)).
Oxycodone Sales
We have to report sales of oxycodone to Health Canada. To build a detailed Excel file, go to InfoNet / uniVIEW / Health Canada / Monthly Reports. Key in a date range, select 'Oxycodone' from the report type drop down box, and press ‘Search’. Then press ‘Export to Excel’.
To see what items will be included, or to add or remove an item
- Go to a command line, key in ‘Unity’ and press enter
- 2 Start uniPharm Extensions
- 1 VA Company (PRODUCTION)
- 7 UWD Tables Menu
- 1 Work with UX Extension Tables
- Under ‘Start display at’ (upper left hand corner) key in ITMGRP and press enter
Table ITMGRPHDR defines any group of items that can be selected in InfoNet Sales Order Inquiry. At the moment, there is just one.
8/18/14 09:03:05 uniPHARM -- PRODUCTION TBRUP001/HEADER
CSU -- Show Table Entries CSU Release 6.0
UX ITMGRPHDR TABLE Table Library: RCSUTILPRD
UniPHARM Extensions Type
group of Items Header U USER CONTROLLED
Start Display at Display Order
N By Element Name
2=Update 4=Cancel 5=Display A=Audit Info
Element Description/Data Sort
HC01 Health Canada Oxycodone Prods
F5=Refresh F6=Add new elements F12=Previous
To see what items will be included in this selection, append the element code of the group you want, ie ‘HC01’, to ‘ITMGRP’. So, look at table ‘ITMGRPHC01’. You can then use option ‘5=elements’, and ‘F6=Add new elements’ or ‘4=Cancel’.