DPD Data Extract - Read Me File

The Drug Product Database (DPD) system captures information on Canadian human, veterinary and disinfectant products approved for use by Health Canada.

This extract contains both marketed (active) and discontinued (inactivated) products in separate files. Although the DPD is a relational database, there is a requirement to provide the data to users in a common format, therefore the data has been extracted into a flat file format. All files are comma delimited enclosed in "quotes". An entity relationship diagram can be found following this page. The data structure for marketed (active) and discontinued (inactivated) products is the same. The entity relationship diagram reflects both the marketed (active) and discontinued (inactivated) products can be found following this page.

It is recommended to load the marketed (active) product data into an 'active' database, and to load the discontinued (inactivated) product data into a separate 'inactive' database in order to keep marketed (active) and discontinued (inactivated) products distinct during querying.

It is possible to load the marketed (active) and discontinued (inactivated) products information into one database. To distinguish between marketed (active) and discontinued (inactivated) products, the STATUS field may be used in queries. The marketed (active) products have status of 'MARKETED (notified)', and discontinued (inactivated) products have one of the following DISCONTINUED statuses:

• 'DISCONTINUED (BY COMPANY POST-MARKET)',
• 'DISCONTINUED (ADMINISTRATIVE)',
• 'DISCONTINUED (NONCOMPLIANT-ANN/COST REC)',
• 'DISCONTINUED (GP TO DIN CONVERSION)',
• 'DISCONTINUED (TRANSITIONED TO NHPD)'.

Note that the GP_Flag field in the QRYM_DRUG_PRODUCT table has been replaced with the PEDIATRIC_Flag field which will identify product(s) destined for pediatric use. The products can be identified with the value 'Y' in the PEDIATRIC_Flag field.

Note that the DPD Online has recently undergone some changes/enhancements. As a result, a new field was inserted in the data extract. More specifically, the field 'Desc.', which will allow Health Canada to enter important text specific information with respect to the product, has been added to the QRYM_DRUG_PRODUCT table. Also note that some fields/tables have been modified to accept mixed cases.

Company Information

In the DPD, a product may have several companies associated with them. Each product will have a DIN Owner associated with it. If a DIN Owner has designated another company to receive their mailing, their name and address will also be included and can be identified with a 'Y' flag in the ADDRESS_MAILING_FLAG field.

The company contact information is not included in the extract (ATTENTION_TO, LANGUAGE, TELEPHONE_NUMBER, FAX_NUMBER, EMAIL_ADDRESS). The contact information in the database is generally a representative from the Regulatory Affairs department. It has come to our attention that some users are contacting these individuals to make general inquiries, we would like to recommend not doing so.

Marketed (active) Products

The files included in the zipped file for marketed (active) products are as follows:

• comp.txt QRYM_COMPANIES
• drug.txt QRYM_DRUG_PRODUCT
• form.txt QRYM_FORM
• ingred.txt QRYM_ACTIVE_INGREDIENTS
• package.txt QRYM_PACKAGING
• pharm.txt QRYM_PHARMACEUTICAL_STD
• route.txt QRYM_ROUTE
• schedule.txt QRYM_SCHEDULE
• status.txt QRYM_STATUS
• ther.txt QRYM_THERAPEUTIC_CLASS
• vet.txt QRYM_VETERINARY_SPECIES
• allfiles.zip all files above

Discontinued (inactivated) Products

Files containing the same types of information as for marketed (active) products have been added to the Data Extract to identify products that are no longer marketed in Canada. The files included in the zipped file containing information on discontinued (inactivated) products are as follows:

• comp_ia.txt QRYM_COMPANIES
• drug_ia.txt QRYM_DRUG_PRODUCT
• form_ia.txt QRYM_FORM
• ingred_ia.txt QRYM_ACTIVE_INGREDIENTS
• package_ia.txt QRYM_PACKAGING
• pharm_ia.txt QRYM_PHARMACEUTICAL_STD
• route_ia.txt QRYM_ROUTE
• schedule_ia.txt QRYM_SCHEDULE
• status_ia.txt QRYM_STATUS
• ther_ia.txt QRYM_THERAPEUTIC_CLASS
• vet_ia.txt QRYM_VETERINARY_SPECIES
• allfiles_ia.zip all files above

The file 'inactive.txt' which was originally added to identify products that are no longer marketed (active) in Canada is still available. The following information fields are included in the file:

• DRUG_CODE NUMBER (8)
• DRUG_IDENTIFICATION_NUMBER VARCHAR2(8)
• BRAND_NAME VARCHAR2(200)
• HISTORY_DATE DATE

French Characters

Starting with the July 1997 extract files, French characters are being incorporated into the database. Currently the QRYM_COMPANY file is the only file that has French characters in it. The DPD database uses ASCII character set 8859. The codes for the commonly used characters are provided below:

• 192 - A (accent grave)
• 194 - A (accent circonflexe)
• 199 - C (cedille)
• 200 - E (accent grave)
• 201 - E (accent aigu)
• 202 - E (accent circonflexe)
• 206 - I (accent circonflexe)
• 212 - O (accent circonflexe)
• 217 - U (accent grave)
• 219 - U (accent circonflexe)

Contact

If you require additional information regarding this data extraction, e-mail:
SIPDMail@hc-sc.gc.ca

Data Structure for Both Marketed (Active) and Discontinued (Inactivated) Products

QRYM_ACTIVE_INGREDIENTS

Name	Null?	Type
DRUG_CODE	NOT NULL	NUMBER(8)
ACTIVE_INGREDIENT_CODE		NUMBER(6)
INGREDIENT		VARCHAR2(240)
INGREDIENT_SUPPLIED_IND		VARCHAR2(1)
STRENGTH		VARCHAR2(20)
STRENGTH_UNIT		VARCHAR2(40)
STRENGTH_TYPE		VARCHAR2(40)
DOSAGE_VALUE		VARCHAR2(20)
BASE		VARCHAR2(1)
DOSAGE_UNIT		VARCHAR2(40)
NOTES		VARCHAR2(2000)

QRYM_COMPANIES

Name	Null?	Type
DRUG_CODE	NOT NULL	NUMBER(8)
MFR_CODE		VARCHAR2(5)
COMPANY_CODE		NUMBER(6)
COMPANY_NAME		VARCHAR2(80)
COMPANY_TYPE		VARCHAR2(40)
ADDRESS_MAILING_FLAG		VARCHAR2(1)
ADDRESS_BILLING_FLAG		VARCHAR2(1)
ADDRESS_NOTIFICATION_FLAG		VARCHAR2(1)
ADDRESS_OTHER		VARCHAR2(1)
SUITE_NUMBER		VARCHAR2(20)
STREET_NAME		VARCHAR2(80)
CITY_NAME		VARCHAR2(60)
PROVINCE		VARCHAR2(40)
COUNTRY		VARCHAR2(40)
POSTAL_CODE		VARCHAR2(20)
POST_OFFICE_BOX		VARCHAR2(15)

QRYM_DRUG_PRODUCT

Name	Null?	Type
DRUG_CODE	NOT NULL	NUMBER(8)
PRODUCT_CATEGORIZATION		VARCHAR2(80)
CLASS		VARCHAR2(40)
DRUG_IDENTIFICATION_NUMBER		VARCHAR2(8)
BRAND_NAME		VARCHAR2(200)
DESCRIPTOR		VARCHAR2(150)
PEDIATRIC_FLAG		VARCHAR2(1)
ACCESSION_NUMBER		VARCHAR2(5)
NUMBER_OF_AIS		VARCHAR2(10)
LAST_UPDATE_DATE		DATE
AI_GROUP_NO		VARCHAR2(10)

QRYM_STATUS

Name	Null?	Type
DRUG_CODE	NOT NULL	NUMBER(8)
CURRENT_STATUS_FLAG		VARCHAR2(1)
STATUS		VARCHAR2(40)
HISTORY_DATE		DATE

QRYM_FORM

Name	Null?	Type
DRUG_CODE	NOT NULL	NUMBER(8)
PHARM_FORM_CODE		NUMBER(7)
PHARMACEUTICAL_FORM		VARCHAR2(40)

QRYM_PACKAGING

Name	Null?	Type
DRUG_CODE	NOT NULL	NUMBER(8)
UPC		VARCHAR2(12)
PACKAGE_SIZE_UNIT		VARCHAR2(40)
PACKAGE_TYPE		VARCHAR2(40)
PACKAGE_SIZE		VARCHAR2(5)
PRODUCT_INFORMATION		VARCHAR2(80)

QRYM_PHARMACEUTICAL_STD

Name	Null?	Type
DRUG_CODE	NOT NULL	NUMBER(8)
PHARMACEUTICAL_STD		VARCHAR2(40)

QRYM_ROUTE

Name	Null?	Type
DRUG_CODE	NOT NULL	NUMBER(8)
ROUTE_OF_ADMINISTRATION_CODE		NUMBER(6)
ROUTE_OF_ADMINISTRATION		VARCHAR2(40)

QRYM_SCHEDULE

Name	Null?	Type
DRUG_CODE	NOT NULL	NUMBER(8)
SCHEDULE		VARCHAR2(40)

QRYM_THERAPEUTIC_CLASS

Name	Null?	Type
DRUG_CODE	NOT NULL	NUMBER(8)
TC_ATC_NUMBER		VARCHAR2(8)
TC_ATC		VARCHAR2(120)
TC_AHFS_NUMBER		VARCHAR2(20)
TC_AHFS		VARCHAR2(80)

QRYM_VETERINARY_SPECIES

Name	Null?	Type
DRUG_CODE	NOT NULL	NUMBER(8)
VET_SPECIES		VARCHAR2(80)
VET_SUB_SPECIES		VARCHAR2(80)

DPD Relationship Diagram