Difference between revisions of "MDCCD Data Warehouse Data Dictionary"

From wiki
Jump to navigation Jump to search
Line 5: Line 5:
  
 
=== MD_INS_CASE ===
 
=== MD_INS_CASE ===
 +
This table holds the main inspection information.
 +
{| class="wikitable"
 +
|+
 +
!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 ===
 
=== MD_INS_CASE_CAPA ===

Revision as of 14:10, 23 May 2024

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.

MD_INS_CASE_DESIGNATION

MD_CASE_ESTACTIVITY

Retrieved from "https://wiki.gccollab.ca/index.php?title=MDCCD_Data_Warehouse_Data_Dictionary&oldid=120536"