MDCCD Data Warehouse Data Dictionary
Overview
This page provides a data dictionary for each table that is held within the MDCCD Data Warehouse.
Medical Devices
MD_INS_CASE
This table holds the main inspection information.
| Column Name | DataType | Nullable | Data Default | Example Data | Description |
|---|---|---|---|---|---|
| INSP_ID | VARCHAR2(36 CHAR) | No | |||
| INSP_NUM | VARCHAR2(200 CHAR) | No | |||
| SITE_ID | VARCHAR2(36 CHAR) | No | |||
| SITE_NAME | VARCHAR2(160 CHAR) | Yes | |||
| LICENCE_NO | NUMBER(6,0) | Yes | |||
| COMPANY_ID | NUMBER(6,0) | Yes | |||
| REG_FILE_NUMBER | VARCHAR2(250 BYTE) | Yes | |||
| REGION_CD_EN | VARCHAR2(50 CHAR) | Yes | |||
| REGION_CD_FR | VARCHAR2(50 CHAR) | Yes | |||
| INSP_TYPE_EN | VARCHAR2(100 CHAR) | Yes | |||
| INSP_TYPE_FR | VARCHAR2(100 CHAR) | Yes | |||
| LEAD_INSPECTOR_ID | VARCHAR2(36 CHAR) | Yes | |||
| LEAD_INSPECTOR | VARCHAR2(200 CHAR) | Yes | |||
| LEAD_INSPECTOR_BADGE | VARCHAR2(50 BYTE) | Yes | |||
| SUPERVISOR_ID | VARCHAR2(36 CHAR) | Yes | |||
| SUPERVISOR | VARCHAR2(200 CHAR) | Yes | |||
| INSP_STATUS_EN | VARCHAR2(100 CHAR) | Yes | |||
| INSP_STATUS_FR | VARCHAR2(100 CHAR) | Yes | |||
| COMP_STANDARD_EN | VARCHAR2(100 CHAR) | Yes | |||
| COMP_STANDARD_FR | VARCHAR2(100 CHAR) | Yes | |||
| DRAFT_REPORT_RATING_EN | VARCHAR2(100 CHAR) | Yes | |||
| DRAFT_REPORT_RATING_FR | VARCHAR2(100 CHAR) | Yes | |||
| DRAFT_REPORT_ISSUE_DATE | DATE | Yes | |||
| FINAL_REPORT_RATING_EN | VARCHAR2(100 CHAR) | Yes | |||
| FINAL_REPORT_RATING_FR | VARCHAR2(100 CHAR) | Yes | |||
| FINAL_REPORT_ISSUE_DATE | DATE | Yes | |||
| AMENDED_REPORT_ISSUE_DATE | DATE | Yes | |||
| SAMPLES_TAKEN | VARCHAR2(2000 CHAR) | Yes | |||
| INSP_START_DATE | DATE | Yes | |||
| INSP_END_DATE | DATE | Yes | |||
| CLOSING_MEETING_DATE | DATE | Yes | |||
| NEXT_INSPECTION_DATE | DATE | Yes | |||
| AMENDED_CD_EN | VARCHAR2(5 CHAR) | Yes | |||
| AMENDED_CD_FR | VARCHAR2(5 CHAR) | Yes | |||
| AMENDED_BY | VARCHAR2(200 CHAR) | Yes | |||
| AMENDED_DATE | DATE | Yes | |||
| FIRST_COMM_DATE | DATE | Yes | |||
| FIRST_COMM_MODE | VARCHAR2(100 CHAR) | Yes | |||
| FIRST_COMM_MODE_EN | VARCHAR2(100 CHAR) | Yes | |||
| FIRST_COMM_MODE_FR | VARCHAR2(100 CHAR) | Yes | |||
| EMERGENCY_CONTACT_NAME | VARCHAR2(100 CHAR) | Yes | |||
| EMERGENCY_CONTACT_PHONE | VARCHAR2(100 CHAR) | Yes | |||
| SENIOR_OFFICIAL_OFFICIAL_ID | VARCHAR2(36 CHAR) | Yes | |||
| SENIOR_OFFICIAL_NAME | VARCHAR2(200 CHAR) | Yes | |||
| SENIOR_OFFICIAL_TITLE | VARCHAR2(100 CHAR) | Yes | |||
| SITE_CONTACT_ID | VARCHAR2(36 CHAR) | Yes | |||
| SITE_CONTACT_NAME | VARCHAR2(200 CHAR) | Yes | |||
| SITE_CONTACT_TITLE | VARCHAR2(100 CHAR) | Yes | |||
| SITE_CONTACT_PHONE | VARCHAR2(100 CHAR) | Yes | |||
| SITE_CONTACT_FAX | VARCHAR2(100 CHAR) | Yes | |||
| SITE_CONTACT_EMAIL | VARCHAR2(100 CHAR) | Yes | |||
| SITE_ADDRESS_1 | VARCHAR2(250 CHAR) | Yes | |||
| SITE_ADDRESS_2 | VARCHAR2(250 CHAR) | Yes | |||
| SITE_ADDRESS_3 | VARCHAR2(250 CHAR) | Yes | |||
| SITE_CITY | VARCHAR2(80 CHAR) | Yes | |||
| SITE_PROVINCE | VARCHAR2(50 CHAR) | Yes | |||
| SITE_POSTALCODE | VARCHAR2(20 CHAR) | Yes | |||
| SITE_COUNTRY | VARCHAR2(80 CHAR) | Yes | |||
| INSPECTION_SUMMARY | CLOB | Yes | |||
| REPORT_DELIVERED_TO_ID | VARCHAR2(36 CHAR) | Yes | |||
| REPORT_DELIVERED_TO | VARCHAR2(200 CHAR) | Yes | |||
| RESPONSE_EXPECTED_DATE | DATE | Yes | |||
| INTERNAL_CLOSING_COMMENTS | CLOB | Yes | |||
| PERSONNEL_CHANGES | VARCHAR2(2000 BYTE) | Yes | |||
| IID_STATUS_EN | VARCHAR2(50 CHAR) | Yes | |||
| IID_STATUS_FR | VARCHAR2(50 CHAR) | Yes | |||
| IID_STATUS_DATE | DATE | Yes | |||
| IID_REVISION_LOG | VARCHAR2(2000 CHAR) | Yes | |||
| IRC_STATUS_EN | VARCHAR2(50 CHAR) | Yes | |||
| IRC_STATUS_FR | VARCHAR2(50 CHAR) | Yes | |||
| INSP_RATING_EN | VARCHAR2(100 CHAR) | Yes | |||
| INSP_RATING_FR | VARCHAR2(100 CHAR) | Yes | |||
| INSP_OUTCOME_EN | VARCHAR2(2000 CHAR) | Yes | |||
| INSP_OUTCOME_FR | VARCHAR2(2000 CHAR) | Yes | |||
| INSP_REPORT_WAITING | VARCHAR2(200 CHAR) | Yes | |||
| INSP_REPORT_WAITING_DATE | DATE | Yes | |||
| INSP_REPORT_APPROVED_BY | VARCHAR2(200 CHAR) | Yes | |||
| INSP_REPORT_APPROVED_BY_DATE | DATE | Yes | |||
| INSP_CLOSED_WAITING | VARCHAR2(200 CHAR) | Yes | |||
| INSP_CLOSED_WAITING_DATE | DATE | Yes | |||
| INSP_CLOSED_APPROVED | VARCHAR2(200 CHAR) | Yes | |||
| INSP_CLOSED_APPROVED_DATE | DATE | Yes | |||
| CREATEDBYNAME | VARCHAR2(100 CHAR) | Yes | |||
| CREATEDON | DATE | Yes | |||
| MODIFIEDBYNAME | VARCHAR2(100 CHAR) | Yes | |||
| MODIFIEDON | DATE | Yes |
MD_INS_CASE_CAPA
This table holds information about the corrective action plan for an inspection observation.
| Column Name | Data Type | Nullable | Data Default | Example Data | Description |
|---|---|---|---|---|---|
| CAPA_ID | VARCHAR2(36 CHAR) | No | NULL | ad5e55f6-8b21-ee11-9966-000d3ae86452 | This is the unique record identifier for the CAPA. |
| INSP_ID | VARCHAR2(36 CHAR) | No | NULL | 3d7a309e-ce46-40c5-a6c2-f3ea80971d48 | This is the unique record identifier for the parent inspection. |
| INSP_NUM | VARCHAR2(200 CHAR) | No | NULL | 00086-BENEMAX DBA. BENEMAX HEALTH AND WELLNESS | This is the parent inspection number. |
| OBS_ID | VARCHAR2(36 CHAR) | No | NULL | 07244e35-fbf1-ee11-a1fe-6045bd6005cc | This is the unique record identifier for the parent observation the CAPA is linked to. |
| CAPA_TYPE | VARCHAR2(100 CHAR) | Yes | NULL | Follow Up Plan | This is a free text field used to identify the type of CAPA. |
| CAPA_PLAN | VARCHAR2(2000 CHAR) | Yes | NULL | The inspector sent follow-up letter to address Physio-controls response--the manufacturer can have more than one ID on the label as long as it clearly marks what the device identifier is and that this identifier matches what is in the MDALL. March 6, 2024 The company sent follow-up from manufacturer. The manufacturer has sent in a fax back to Health Canada to include the device ID (11141-000165) under their license. This adequately addresses the observation. | This is the CAPA as inputted by the inspector. |
| CREATEDBYNAME | VARCHAR2(100 CHAR) | Yes | NULL | Jeff Tyndall | This identifies the resource that created the record. |
| CREATEDON | DATE | Yes | NULL | 22-08-26 | This identifies the date that record was created. |
| MODIFIEDBYNAME | VARCHAR2(100 CHAR) | Yes | NULL | Jeff Tyndall | This identifies the resource that las modified the record. |
| MODIFIEDON | DATE | Yes | NULL | 22-08-26 | This identifies the date that record was last modified. |
