Difference between revisions of "MDCCD Data Warehouse Data Dictionary"

From wiki
Jump to navigation Jump to search
Line 9: Line 9:
 
|+
 
|+
 
!Column Name
 
!Column Name
 +
!Key Type
 
!DataType
 
!DataType
 
!Nullable
 
!Nullable
Line 14: Line 15:
 
!Example Data
 
!Example Data
 
!Description
 
!Description
 +
!Comments
 
|-
 
|-
 
|INSP_ID
 
|INSP_ID
 +
|Primary
 
|VARCHAR2(36 CHAR)
 
|VARCHAR2(36 CHAR)
 
|No
 
|No
 +
|NULL
 
|
 
|
 +
|This is the unique record identifier for the inspection.
 
|
 
|
 
|-
 
|-
 
|INSP_NUM
 
|INSP_NUM
 +
|
 
|VARCHAR2(200 CHAR)
 
|VARCHAR2(200 CHAR)
 
|No
 
|No
 +
|NULL
 
|
 
|
 +
|This is the inspection number.
 
|
 
|
 
|-
 
|-
 
|SITE_ID
 
|SITE_ID
 +
|Foreign
 
|VARCHAR2(36 CHAR)
 
|VARCHAR2(36 CHAR)
 
|No
 
|No
 +
|NULL
 
|
 
|
 +
|This is the unique record identifier for the company inspected.
 
|
 
|
 
|-
 
|-
 
|SITE_NAME
 
|SITE_NAME
 +
|
 
|VARCHAR2(160 CHAR)
 
|VARCHAR2(160 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
 +
|This is the name of the company insepcted.
 
|
 
|
 
|-
 
|-
 
|LICENCE_NO
 
|LICENCE_NO
 +
|Foreign
 
|NUMBER(6,0)
 
|NUMBER(6,0)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
−
|
+
|This is the establishment licence number of the company inspected.
 +
|Use to join with MDS table ESTABLISHMENT.ESTABLISHMENT_ID.
 
|-
 
|-
 
|COMPANY_ID
 
|COMPANY_ID
 +
|Foreign
 
|NUMBER(6,0)
 
|NUMBER(6,0)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
−
|
+
|This is the internal Health Canada company identifier.
 +
|Use to join with MDS table ESTABLISHMENT.COMPANY_ID or COMPANY.COMPANY_ID.
 
|-
 
|-
 
|REG_FILE_NUMBER
 
|REG_FILE_NUMBER
 +
|
 
|VARCHAR2(250 BYTE)
 
|VARCHAR2(250 BYTE)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
 +
|This is the internal program based file identifier.
 
|
 
|
 
|-
 
|-
 
|REGION_CD_EN
 
|REGION_CD_EN
 +
|
 
|VARCHAR2(50 CHAR)
 
|VARCHAR2(50 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
 +
|This is the operational centre assigned to the inspection in English.
 
|
 
|
 
|-
 
|-
 
|REGION_CD_FR
 
|REGION_CD_FR
 +
|
 
|VARCHAR2(50 CHAR)
 
|VARCHAR2(50 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
 +
|This is the operational centre assigned to the inspection in French.
 
|
 
|
 
|-
 
|-
 
|INSP_TYPE_EN
 
|INSP_TYPE_EN
 +
|
 
|VARCHAR2(100 CHAR)
 
|VARCHAR2(100 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
 +
|This is the type of inspection in English.
 
|
 
|
 
|-
 
|-
 
|INSP_TYPE_FR
 
|INSP_TYPE_FR
 +
|
 
|VARCHAR2(100 CHAR)
 
|VARCHAR2(100 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
 +
|This is the type of inspection in French.
 
|
 
|
 
|-
 
|-
 
|LEAD_INSPECTOR_ID
 
|LEAD_INSPECTOR_ID
 +
|Foreign
 
|VARCHAR2(36 CHAR)
 
|VARCHAR2(36 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
 +
|This is the unique record identifier of the lead inspector.
 
|
 
|
 
|-
 
|-
 
|LEAD_INSPECTOR
 
|LEAD_INSPECTOR
 +
|
 
|VARCHAR2(200 CHAR)
 
|VARCHAR2(200 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
 +
|This is the name of the lead inspector.
 
|
 
|
 
|-
 
|-
 
|LEAD_INSPECTOR_BADGE
 
|LEAD_INSPECTOR_BADGE
 +
|
 
|VARCHAR2(50 BYTE)
 
|VARCHAR2(50 BYTE)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
 +
|This is the badge number of the lead inspector.
 
|
 
|
 
|-
 
|-
 
|SUPERVISOR_ID
 
|SUPERVISOR_ID
 +
|Foreign
 
|VARCHAR2(36 CHAR)
 
|VARCHAR2(36 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
 +
|This is the unique record identifier of the supervisor for the inspection.
 
|
 
|
 
|-
 
|-
 
|SUPERVISOR
 
|SUPERVISOR
 +
|
 
|VARCHAR2(200 CHAR)
 
|VARCHAR2(200 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
 +
|This is the name of the supervisor for the inspection.
 
|
 
|
 
|-
 
|-
 
|INSP_STATUS_EN
 
|INSP_STATUS_EN
 +
|
 
|VARCHAR2(100 CHAR)
 
|VARCHAR2(100 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
 +
|This is the status of the inspection in English.
 
|
 
|
 
|-
 
|-
 
|INSP_STATUS_FR
 
|INSP_STATUS_FR
 +
|
 
|VARCHAR2(100 CHAR)
 
|VARCHAR2(100 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
 +
|This is the status of the inspection in French.
 
|
 
|
 
|-
 
|-
 
|COMP_STANDARD_EN
 
|COMP_STANDARD_EN
 +
|
 
|VARCHAR2(100 CHAR)
 
|VARCHAR2(100 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
 +
|This is the compliance standard that the site was inspected against in English. Either the FDA or MDR.
 
|
 
|
 
|-
 
|-
 
|COMP_STANDARD_FR
 
|COMP_STANDARD_FR
 +
|
 
|VARCHAR2(100 CHAR)
 
|VARCHAR2(100 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
 +
|This is the compliance standard that the site was inspected against in French. Either the FDA or MDR.
 
|
 
|
 
|-
 
|-
 
|DRAFT_REPORT_RATING_EN
 
|DRAFT_REPORT_RATING_EN
 +
|
 
|VARCHAR2(100 CHAR)
 
|VARCHAR2(100 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
 +
|This is the draft inspection rating in English.
 
|
 
|
 
|-
 
|-
 
|DRAFT_REPORT_RATING_FR
 
|DRAFT_REPORT_RATING_FR
 +
|
 
|VARCHAR2(100 CHAR)
 
|VARCHAR2(100 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
 +
|This is the draft inspection rating in French.
 
|
 
|
 
|-
 
|-
 
|DRAFT_REPORT_ISSUE_DATE
 
|DRAFT_REPORT_ISSUE_DATE
 +
|
 
|DATE
 
|DATE
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
 +
|This is the date that the draft inspection rating was issued.
 
|
 
|
 
|-
 
|-
 
|FINAL_REPORT_RATING_EN
 
|FINAL_REPORT_RATING_EN
 +
|
 
|VARCHAR2(100 CHAR)
 
|VARCHAR2(100 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
 +
|This is the final inspection rating in English.
 
|
 
|
 
|-
 
|-
 
|FINAL_REPORT_RATING_FR
 
|FINAL_REPORT_RATING_FR
 +
|
 
|VARCHAR2(100 CHAR)
 
|VARCHAR2(100 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
 +
|This is the final inspection rating in French.
 
|
 
|
 
|-
 
|-
 
|FINAL_REPORT_ISSUE_DATE
 
|FINAL_REPORT_ISSUE_DATE
 +
|
 
|DATE
 
|DATE
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
 +
|This is the date that the final inspection rating was issued.
 
|
 
|
 
|-
 
|-
 
|AMENDED_REPORT_ISSUE_DATE
 
|AMENDED_REPORT_ISSUE_DATE
 +
|
 
|DATE
 
|DATE
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
 +
|This is the date that an amended inspection report was issued to the company.
 
|
 
|
 
|-
 
|-
 
|SAMPLES_TAKEN
 
|SAMPLES_TAKEN
 +
|
 
|VARCHAR2(2000 CHAR)
 
|VARCHAR2(2000 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
 +
|This indicates if any samples were taken during the inspection.
 
|
 
|
 
|-
 
|-
 
|INSP_START_DATE
 
|INSP_START_DATE
 +
|
 
|DATE
 
|DATE
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
−
|
+
|This indicates when the inspection was started.
 +
|Use this to calculate the number of inspections per month for the dashboard.
 
|-
 
|-
 
|INSP_END_DATE
 
|INSP_END_DATE
 +
|
 
|DATE
 
|DATE
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
−
|
+
|This indicates when the inspection was finished.
 +
|Use this to calculate the number of completed inspections per month/quarterly compliance ratings for the dashboard.
 
|-
 
|-
 
|CLOSING_MEETING_DATE
 
|CLOSING_MEETING_DATE
 +
|
 
|DATE
 
|DATE
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
−
|
+
|This indicates when the inspector had the closing meeting with the company.
 
|-
 
|-
 
|NEXT_INSPECTION_DATE
 
|NEXT_INSPECTION_DATE
 +
|
 
|DATE
 
|DATE
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
−
|
+
|This indicates when the company is due for its next inspection
 
|-
 
|-
 
|AMENDED_CD_EN
 
|AMENDED_CD_EN
 +
|
 
|VARCHAR2(5 CHAR)
 
|VARCHAR2(5 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
−
|
+
|This indicates whether the inspection report has been amended in English.
 
|-
 
|-
 
|AMENDED_CD_FR
 
|AMENDED_CD_FR
 +
|
 
|VARCHAR2(5 CHAR)
 
|VARCHAR2(5 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
−
|
+
|This indicates whether the inspection report has been amended in French.
 
|-
 
|-
 
|AMENDED_BY
 
|AMENDED_BY
 +
|
 
|VARCHAR2(200 CHAR)
 
|VARCHAR2(200 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
−
|
+
|This indicates who amended the inspection.
 
|-
 
|-
 
|AMENDED_DATE
 
|AMENDED_DATE
 +
|
 
|DATE
 
|DATE
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
−
|
+
|This indicates when the report was amended.
 
|-
 
|-
 
|FIRST_COMM_DATE
 
|FIRST_COMM_DATE
 +
|
 
|DATE
 
|DATE
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
−
|
+
|This is the date the inspector first communicated with the company for the inspection.
 
|-
 
|-
 
|FIRST_COMM_MODE
 
|FIRST_COMM_MODE
 +
|
 
|VARCHAR2(100 CHAR)
 
|VARCHAR2(100 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
−
|
+
|This is how the inspector first communicated with the company for the inspection.
 
|-
 
|-
 
|FIRST_COMM_MODE_EN
 
|FIRST_COMM_MODE_EN
 +
|
 
|VARCHAR2(100 CHAR)
 
|VARCHAR2(100 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
−
|
+
|This is how the inspector first communicated with the company for the inspection in English.
 
|-
 
|-
 
|FIRST_COMM_MODE_FR
 
|FIRST_COMM_MODE_FR
 +
|
 
|VARCHAR2(100 CHAR)
 
|VARCHAR2(100 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
−
|
+
|This is how the inspector first communicated with the company for the inspection in French.
 
|-
 
|-
 
|EMERGENCY_CONTACT_NAME
 
|EMERGENCY_CONTACT_NAME
 +
|
 
|VARCHAR2(100 CHAR)
 
|VARCHAR2(100 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
−
|
+
|This is the name of the emergency contact for the company at the time of inspection.
 
|-
 
|-
 
|EMERGENCY_CONTACT_PHONE
 
|EMERGENCY_CONTACT_PHONE
 +
|
 
|VARCHAR2(100 CHAR)
 
|VARCHAR2(100 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
−
|
+
|This is the phone number of the emergency contact for the company at the time of inspection.
 
|-
 
|-
 
|SENIOR_OFFICIAL_OFFICIAL_ID
 
|SENIOR_OFFICIAL_OFFICIAL_ID
 +
|Foreign
 
|VARCHAR2(36 CHAR)
 
|VARCHAR2(36 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
−
|
+
|This is the unique record identifier of the senior official of the company at the time of insepction.
 
|-
 
|-
 
|SENIOR_OFFICIAL_NAME
 
|SENIOR_OFFICIAL_NAME
 +
|
 
|VARCHAR2(200 CHAR)
 
|VARCHAR2(200 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
−
|
+
|This is the name of the senior official for the company at the time of inspection.
 
|-
 
|-
 
|SENIOR_OFFICIAL_TITLE
 
|SENIOR_OFFICIAL_TITLE
 +
|
 
|VARCHAR2(100 CHAR)
 
|VARCHAR2(100 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
−
|
+
|This is the job title of the senior official for the company at the time of inspection.
 
|-
 
|-
 
|SITE_CONTACT_ID
 
|SITE_CONTACT_ID
 +
|Foreign
 
|VARCHAR2(36 CHAR)
 
|VARCHAR2(36 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
 +
|This is the unique record identifier of the contact of the company at the time of inspection.
 
|
 
|
 
|-
 
|-
 
|SITE_CONTACT_NAME
 
|SITE_CONTACT_NAME
 +
|
 
|VARCHAR2(200 CHAR)
 
|VARCHAR2(200 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
 +
|This is the name of the contact of the company at the time of inspection.
 
|
 
|
 
|-
 
|-
 
|SITE_CONTACT_TITLE
 
|SITE_CONTACT_TITLE
 +
|
 
|VARCHAR2(100 CHAR)
 
|VARCHAR2(100 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
 +
|This is the job title of the contact of the company at the time of inspection.
 
|
 
|
 
|-
 
|-
 
|SITE_CONTACT_PHONE
 
|SITE_CONTACT_PHONE
 +
|
 
|VARCHAR2(100 CHAR)
 
|VARCHAR2(100 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
−
|
+
|This is the phone number of the contact of the company at the time of inspection.
 
|-
 
|-
 
|SITE_CONTACT_FAX
 
|SITE_CONTACT_FAX
 +
|
 
|VARCHAR2(100 CHAR)
 
|VARCHAR2(100 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
 +
|This is the fax of the contact of the company at the time of inspection.
 
|
 
|
 
|-
 
|-
 
|SITE_CONTACT_EMAIL
 
|SITE_CONTACT_EMAIL
 +
|
 
|VARCHAR2(100 CHAR)
 
|VARCHAR2(100 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
 +
|This is the e-mail of the contact of the company at the time of inspection.
 
|
 
|
 
|-
 
|-
 
|SITE_ADDRESS_1
 
|SITE_ADDRESS_1
 +
|
 
|VARCHAR2(250 CHAR)
 
|VARCHAR2(250 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
 +
|This is the address of the company being inspected.
 
|
 
|
 
|-
 
|-
 
|SITE_ADDRESS_2
 
|SITE_ADDRESS_2
 +
|
 
|VARCHAR2(250 CHAR)
 
|VARCHAR2(250 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
 +
|This is secondary the address of the company being inspected.
 
|
 
|
 
|-
 
|-
 
|SITE_ADDRESS_3
 
|SITE_ADDRESS_3
 +
|
 
|VARCHAR2(250 CHAR)
 
|VARCHAR2(250 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
 +
|This is the tertiary address of the company being inspected.
 
|
 
|
 
|-
 
|-
 
|SITE_CITY
 
|SITE_CITY
 +
|
 
|VARCHAR2(80 CHAR)
 
|VARCHAR2(80 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
−
|
+
|This is the city of the company being inspected.
 
|-
 
|-
 
|SITE_PROVINCE
 
|SITE_PROVINCE
 +
|
 
|VARCHAR2(50 CHAR)
 
|VARCHAR2(50 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
 +
|This is the province of the company being inspected.
 
|
 
|
 
|-
 
|-
 
|SITE_POSTALCODE
 
|SITE_POSTALCODE
 +
|
 
|VARCHAR2(20 CHAR)
 
|VARCHAR2(20 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
 +
|This is the postal code of the company being inspected.
 
|
 
|
 
|-
 
|-
 
|SITE_COUNTRY
 
|SITE_COUNTRY
 +
|
 
|VARCHAR2(80 CHAR)
 
|VARCHAR2(80 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
 +
|This is the country of the company being inspected.
 
|
 
|
 
|-
 
|-
 
|INSPECTION_SUMMARY
 
|INSPECTION_SUMMARY
 +
|
 
|CLOB
 
|CLOB
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
 +
|This is the inspectors summary of the inspection.
 
|
 
|
 
|-
 
|-
 
|REPORT_DELIVERED_TO_ID
 
|REPORT_DELIVERED_TO_ID
 +
|Foreign
 
|VARCHAR2(36 CHAR)
 
|VARCHAR2(36 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
 +
|This is the unique record identifier of the contact of the company who received the final inspection report.
 
|
 
|
 
|-
 
|-
 
|REPORT_DELIVERED_TO
 
|REPORT_DELIVERED_TO
 +
|
 
|VARCHAR2(200 CHAR)
 
|VARCHAR2(200 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
 +
|This is the name of the contact of the company who received the final inspection report.
 
|
 
|
 
|-
 
|-
 
|RESPONSE_EXPECTED_DATE
 
|RESPONSE_EXPECTED_DATE
 +
|
 
|DATE
 
|DATE
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
 +
|This is the date that Health Canada is expecting a response from the company about information captured in the final inspection report.
 
|
 
|
 
|-
 
|-
 
|INTERNAL_CLOSING_COMMENTS
 
|INTERNAL_CLOSING_COMMENTS
 +
|
 
|CLOB
 
|CLOB
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
 +
|This is the inspector's internal comments and recommendations after the completion of the inspection.
 
|
 
|
 
|-
 
|-
 
|PERSONNEL_CHANGES
 
|PERSONNEL_CHANGES
 +
|
 
|VARCHAR2(2000 BYTE)
 
|VARCHAR2(2000 BYTE)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
 +
|This indicates any personnel changes at the company inspected since their previous inspection.
 
|
 
|
 
|-
 
|-
 
|IID_STATUS_EN
 
|IID_STATUS_EN
 +
|
 
|VARCHAR2(50 CHAR)
 
|VARCHAR2(50 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
−
|
+
|This indicates the status of the IID in English.
 +
|DHPID Transparency IID
 
|-
 
|-
 
|IID_STATUS_FR
 
|IID_STATUS_FR
 +
|
 
|VARCHAR2(50 CHAR)
 
|VARCHAR2(50 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
−
|
+
|This indicates the status of the IID in French.
 +
|DHPID Transparency IID
 
|-
 
|-
 
|IID_STATUS_DATE
 
|IID_STATUS_DATE
 +
|
 
|DATE
 
|DATE
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
−
|
+
|This indicates the status date of the IID.
 +
|DHPID Transparency IID
 
|-
 
|-
 
|IID_REVISION_LOG
 
|IID_REVISION_LOG
 +
|
 
|VARCHAR2(2000 CHAR)
 
|VARCHAR2(2000 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
−
|
+
|This indicates any revisions carried out to the IID.
 +
|DHPID Transparency IID
 
|-
 
|-
 
|IRC_STATUS_EN
 
|IRC_STATUS_EN
 +
|
 
|VARCHAR2(50 CHAR)
 
|VARCHAR2(50 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
−
|
+
|This indicates the status of the IRC in English.
 +
|DHPID Transparency IRC
 
|-
 
|-
 
|IRC_STATUS_FR
 
|IRC_STATUS_FR
 +
|
 
|VARCHAR2(50 CHAR)
 
|VARCHAR2(50 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
−
|
+
|This indicates the status of the IRC in French.
 +
|DHPID Transparency IRC
 
|-
 
|-
 
|INSP_RATING_EN
 
|INSP_RATING_EN
 +
|
 
|VARCHAR2(100 CHAR)
 
|VARCHAR2(100 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
−
|
+
|This indicates the final inspection report rating for the IRC in English.
 +
|DHPID Transparency IRC. Matches the FINAL_REPORT_RATING_EN value.
 
|-
 
|-
 
|INSP_RATING_FR
 
|INSP_RATING_FR
 +
|
 
|VARCHAR2(100 CHAR)
 
|VARCHAR2(100 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
−
|
+
|This indicates the final inspection report rating for the IRC in French.
 +
|DHPID Transparency IRC. Matches the FINAL_REPORT_RATING_FR value.
 
|-
 
|-
 
|INSP_OUTCOME_EN
 
|INSP_OUTCOME_EN
 +
|
 
|VARCHAR2(2000 CHAR)
 
|VARCHAR2(2000 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
−
|
+
|This is a standard line related to the INSP_RATING_EN value.
 +
|DHPID Transparency IRC
 
|-
 
|-
 
|INSP_OUTCOME_FR
 
|INSP_OUTCOME_FR
 +
|
 
|VARCHAR2(2000 CHAR)
 
|VARCHAR2(2000 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
−
|
+
|This is a standard line related to the INSP_RATING_FR value.
 +
|DHPID Transparency IRC
 
|-
 
|-
 
|INSP_REPORT_WAITING
 
|INSP_REPORT_WAITING
 +
|
 
|VARCHAR2(200 CHAR)
 
|VARCHAR2(200 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
 +
|This is the inspector's name who sent the inspection report for approval in the record log.
 
|
 
|
 
|-
 
|-
 
|INSP_REPORT_WAITING_DATE
 
|INSP_REPORT_WAITING_DATE
 +
|
 
|DATE
 
|DATE
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
 +
|This is the date the inspector sent the inspection report for approval in the recocrd log.
 
|
 
|
 
|-
 
|-
 
|INSP_REPORT_APPROVED_BY
 
|INSP_REPORT_APPROVED_BY
 +
|
 
|VARCHAR2(200 CHAR)
 
|VARCHAR2(200 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
 +
|This is the supervisor's name who approved the inspection report in the record log.
 
|
 
|
 
|-
 
|-
 
|INSP_REPORT_APPROVED_BY_DATE
 
|INSP_REPORT_APPROVED_BY_DATE
 +
|
 
|DATE
 
|DATE
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
 +
|This is the date the supervisor approved the inspection report in the record log.
 
|
 
|
 
|-
 
|-
 
|INSP_CLOSED_WAITING
 
|INSP_CLOSED_WAITING
 +
|
 
|VARCHAR2(200 CHAR)
 
|VARCHAR2(200 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
−
|
+
|This is the inspector's name who sent the inspection for closure in the record log.
 
|-
 
|-
 
|INSP_CLOSED_WAITING_DATE
 
|INSP_CLOSED_WAITING_DATE
 +
|
 
|DATE
 
|DATE
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
 +
|This is the date the inspector sent the inspection report for closure in the record log.
 
|
 
|
 
|-
 
|-
 
|INSP_CLOSED_APPROVED
 
|INSP_CLOSED_APPROVED
 +
|
 
|VARCHAR2(200 CHAR)
 
|VARCHAR2(200 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
 +
|This is the supervisor's name who approved the inspection for closure in the record log.
 
|
 
|
 
|-
 
|-
 
|INSP_CLOSED_APPROVED_DATE
 
|INSP_CLOSED_APPROVED_DATE
 +
|
 
|DATE
 
|DATE
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
 +
|This is the date the supervisor approved the inspection for closure in the record log.
 
|
 
|
 
|-
 
|-
 
|CREATEDBYNAME
 
|CREATEDBYNAME
 +
|
 
|VARCHAR2(100 CHAR)
 
|VARCHAR2(100 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
 +
|This identifies the resource that created the record.
 
|
 
|
 
|-
 
|-
 
|CREATEDON
 
|CREATEDON
 +
|
 
|DATE
 
|DATE
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
 +
|This identifies the date that record was created.
 
|
 
|
 
|-
 
|-
 
|MODIFIEDBYNAME
 
|MODIFIEDBYNAME
 +
|
 
|VARCHAR2(100 CHAR)
 
|VARCHAR2(100 CHAR)
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
 +
|This identifies the resource that las modified the record.
 
|
 
|
 
|-
 
|-
 
|MODIFIEDON
 
|MODIFIEDON
 +
|
 
|DATE
 
|DATE
 
|Yes
 
|Yes
 +
|NULL
 
|
 
|
 +
|This identifies the date that record was last modified.
 
|
 
|
 
|}
 
|}
Line 538: Line 780:
 
!Column Name
 
!Column Name
 
!Data Type
 
!Data Type
 +
!Key Type
 
!Nullable
 
!Nullable
 
!Data Default
 
!Data Default
 
!Example Data
 
!Example Data
 
!Description
 
!Description
 +
!Comments
 
|-
 
|-
 
|CAPA_ID
 
|CAPA_ID
 
|VARCHAR2(36 CHAR)
 
|VARCHAR2(36 CHAR)
 +
|Primary
 
|No
 
|No
 
|NULL
 
|NULL
 
|ad5e55f6-8b21-ee11-9966-000d3ae86452
 
|ad5e55f6-8b21-ee11-9966-000d3ae86452
 
|This is the unique record identifier for the CAPA.
 
|This is the unique record identifier for the CAPA.
 +
|
 
|-
 
|-
 
|INSP_ID
 
|INSP_ID
 
|VARCHAR2(36 CHAR)
 
|VARCHAR2(36 CHAR)
 +
|Foreign
 
|No
 
|No
 
|NULL
 
|NULL
 
|3d7a309e-ce46-40c5-a6c2-f3ea80971d48
 
|3d7a309e-ce46-40c5-a6c2-f3ea80971d48
 
|This is the unique record identifier for the parent inspection.
 
|This is the unique record identifier for the parent inspection.
 +
|Use to join with MD_INS_CASE.INSP_ID.
 
|-
 
|-
 
|INSP_NUM
 
|INSP_NUM
 
|VARCHAR2(200 CHAR)
 
|VARCHAR2(200 CHAR)
 +
|
 
|No
 
|No
 
|NULL
 
|NULL
 
|00086-BENEMAX DBA. BENEMAX HEALTH AND WELLNESS  
 
|00086-BENEMAX DBA. BENEMAX HEALTH AND WELLNESS  
 
|This is the parent inspection number.
 
|This is the parent inspection number.
 +
|
 
|-
 
|-
 
|OBS_ID
 
|OBS_ID
 
|VARCHAR2(36 CHAR)
 
|VARCHAR2(36 CHAR)
 +
|Foreign
 
|No
 
|No
 
|NULL
 
|NULL
 
|07244e35-fbf1-ee11-a1fe-6045bd6005cc
 
|07244e35-fbf1-ee11-a1fe-6045bd6005cc
 
|This is the unique record identifier for the parent observation the CAPA is linked to.
 
|This is the unique record identifier for the parent observation the CAPA is linked to.
 +
|
 
|-
 
|-
 
|CAPA_TYPE
 
|CAPA_TYPE
 
|VARCHAR2(100 CHAR)
 
|VARCHAR2(100 CHAR)
 +
|
 
|Yes
 
|Yes
 
|NULL
 
|NULL
 
|Follow Up Plan
 
|Follow Up Plan
 
|This is a free text field used to identify the type of CAPA.
 
|This is a free text field used to identify the type of CAPA.
 +
|
 
|-
 
|-
 
|CAPA_PLAN
 
|CAPA_PLAN
 
|VARCHAR2(2000 CHAR)
 
|VARCHAR2(2000 CHAR)
 +
|
 
|Yes
 
|Yes
 
|NULL
 
|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.
 
|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.
 
|This is the CAPA as inputted by the inspector.
 +
|
 
|-
 
|-
 
|CREATEDBYNAME
 
|CREATEDBYNAME
 
|VARCHAR2(100 CHAR)
 
|VARCHAR2(100 CHAR)
 +
|
 
|Yes
 
|Yes
 
|NULL
 
|NULL
 
|Jeff Tyndall
 
|Jeff Tyndall
 
|This identifies the resource that created the record.
 
|This identifies the resource that created the record.
 +
|
 
|-
 
|-
 
|CREATEDON
 
|CREATEDON
 
|DATE
 
|DATE
 +
|
 
|Yes
 
|Yes
 
|NULL
 
|NULL
 
|22-08-26
 
|22-08-26
 
|This identifies the date that record was created.
 
|This identifies the date that record was created.
 +
|
 
|-
 
|-
 
|MODIFIEDBYNAME
 
|MODIFIEDBYNAME
 
|VARCHAR2(100 CHAR)
 
|VARCHAR2(100 CHAR)
 +
|
 
|Yes
 
|Yes
 
|NULL
 
|NULL
 
|Jeff Tyndall
 
|Jeff Tyndall
 
|This identifies the resource that las modified the record.
 
|This identifies the resource that las modified the record.
 +
|
 
|-
 
|-
 
|MODIFIEDON
 
|MODIFIEDON
 
|DATE
 
|DATE
 +
|
 
|Yes
 
|Yes
 
|NULL
 
|NULL
 
|22-08-26
 
|22-08-26
 
|This identifies the date that record was last modified.
 
|This identifies the date that record was last modified.
 +
|
 
|}
 
|}
  

Revision as of 14:50, 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 Key Type DataType Nullable Data Default Example Data Description Comments
INSP_ID Primary VARCHAR2(36 CHAR) No NULL This is the unique record identifier for the inspection.
INSP_NUM VARCHAR2(200 CHAR) No NULL This is the inspection number.
SITE_ID Foreign VARCHAR2(36 CHAR) No NULL This is the unique record identifier for the company inspected.
SITE_NAME VARCHAR2(160 CHAR) Yes NULL This is the name of the company insepcted.
LICENCE_NO Foreign NUMBER(6,0) Yes NULL This is the establishment licence number of the company inspected. Use to join with MDS table ESTABLISHMENT.ESTABLISHMENT_ID.
COMPANY_ID Foreign NUMBER(6,0) Yes NULL This is the internal Health Canada company identifier. Use to join with MDS table ESTABLISHMENT.COMPANY_ID or COMPANY.COMPANY_ID.
REG_FILE_NUMBER VARCHAR2(250 BYTE) Yes NULL This is the internal program based file identifier.
REGION_CD_EN VARCHAR2(50 CHAR) Yes NULL This is the operational centre assigned to the inspection in English.
REGION_CD_FR VARCHAR2(50 CHAR) Yes NULL This is the operational centre assigned to the inspection in French.
INSP_TYPE_EN VARCHAR2(100 CHAR) Yes NULL This is the type of inspection in English.
INSP_TYPE_FR VARCHAR2(100 CHAR) Yes NULL This is the type of inspection in French.
LEAD_INSPECTOR_ID Foreign VARCHAR2(36 CHAR) Yes NULL This is the unique record identifier of the lead inspector.
LEAD_INSPECTOR VARCHAR2(200 CHAR) Yes NULL This is the name of the lead inspector.
LEAD_INSPECTOR_BADGE VARCHAR2(50 BYTE) Yes NULL This is the badge number of the lead inspector.
SUPERVISOR_ID Foreign VARCHAR2(36 CHAR) Yes NULL This is the unique record identifier of the supervisor for the inspection.
SUPERVISOR VARCHAR2(200 CHAR) Yes NULL This is the name of the supervisor for the inspection.
INSP_STATUS_EN VARCHAR2(100 CHAR) Yes NULL This is the status of the inspection in English.
INSP_STATUS_FR VARCHAR2(100 CHAR) Yes NULL This is the status of the inspection in French.
COMP_STANDARD_EN VARCHAR2(100 CHAR) Yes NULL This is the compliance standard that the site was inspected against in English. Either the FDA or MDR.
COMP_STANDARD_FR VARCHAR2(100 CHAR) Yes NULL This is the compliance standard that the site was inspected against in French. Either the FDA or MDR.
DRAFT_REPORT_RATING_EN VARCHAR2(100 CHAR) Yes NULL This is the draft inspection rating in English.
DRAFT_REPORT_RATING_FR VARCHAR2(100 CHAR) Yes NULL This is the draft inspection rating in French.
DRAFT_REPORT_ISSUE_DATE DATE Yes NULL This is the date that the draft inspection rating was issued.
FINAL_REPORT_RATING_EN VARCHAR2(100 CHAR) Yes NULL This is the final inspection rating in English.
FINAL_REPORT_RATING_FR VARCHAR2(100 CHAR) Yes NULL This is the final inspection rating in French.
FINAL_REPORT_ISSUE_DATE DATE Yes NULL This is the date that the final inspection rating was issued.
AMENDED_REPORT_ISSUE_DATE DATE Yes NULL This is the date that an amended inspection report was issued to the company.
SAMPLES_TAKEN VARCHAR2(2000 CHAR) Yes NULL This indicates if any samples were taken during the inspection.
INSP_START_DATE DATE Yes NULL This indicates when the inspection was started. Use this to calculate the number of inspections per month for the dashboard.
INSP_END_DATE DATE Yes NULL This indicates when the inspection was finished. Use this to calculate the number of completed inspections per month/quarterly compliance ratings for the dashboard.
CLOSING_MEETING_DATE DATE Yes NULL This indicates when the inspector had the closing meeting with the company.
NEXT_INSPECTION_DATE DATE Yes NULL This indicates when the company is due for its next inspection
AMENDED_CD_EN VARCHAR2(5 CHAR) Yes NULL This indicates whether the inspection report has been amended in English.
AMENDED_CD_FR VARCHAR2(5 CHAR) Yes NULL This indicates whether the inspection report has been amended in French.
AMENDED_BY VARCHAR2(200 CHAR) Yes NULL This indicates who amended the inspection.
AMENDED_DATE DATE Yes NULL This indicates when the report was amended.
FIRST_COMM_DATE DATE Yes NULL This is the date the inspector first communicated with the company for the inspection.
FIRST_COMM_MODE VARCHAR2(100 CHAR) Yes NULL This is how the inspector first communicated with the company for the inspection.
FIRST_COMM_MODE_EN VARCHAR2(100 CHAR) Yes NULL This is how the inspector first communicated with the company for the inspection in English.
FIRST_COMM_MODE_FR VARCHAR2(100 CHAR) Yes NULL This is how the inspector first communicated with the company for the inspection in French.
EMERGENCY_CONTACT_NAME VARCHAR2(100 CHAR) Yes NULL This is the name of the emergency contact for the company at the time of inspection.
EMERGENCY_CONTACT_PHONE VARCHAR2(100 CHAR) Yes NULL This is the phone number of the emergency contact for the company at the time of inspection.
SENIOR_OFFICIAL_OFFICIAL_ID Foreign VARCHAR2(36 CHAR) Yes NULL This is the unique record identifier of the senior official of the company at the time of insepction.
SENIOR_OFFICIAL_NAME VARCHAR2(200 CHAR) Yes NULL This is the name of the senior official for the company at the time of inspection.
SENIOR_OFFICIAL_TITLE VARCHAR2(100 CHAR) Yes NULL This is the job title of the senior official for the company at the time of inspection.
SITE_CONTACT_ID Foreign VARCHAR2(36 CHAR) Yes NULL This is the unique record identifier of the contact of the company at the time of inspection.
SITE_CONTACT_NAME VARCHAR2(200 CHAR) Yes NULL This is the name of the contact of the company at the time of inspection.
SITE_CONTACT_TITLE VARCHAR2(100 CHAR) Yes NULL This is the job title of the contact of the company at the time of inspection.
SITE_CONTACT_PHONE VARCHAR2(100 CHAR) Yes NULL This is the phone number of the contact of the company at the time of inspection.
SITE_CONTACT_FAX VARCHAR2(100 CHAR) Yes NULL This is the fax of the contact of the company at the time of inspection.
SITE_CONTACT_EMAIL VARCHAR2(100 CHAR) Yes NULL This is the e-mail of the contact of the company at the time of inspection.
SITE_ADDRESS_1 VARCHAR2(250 CHAR) Yes NULL This is the address of the company being inspected.
SITE_ADDRESS_2 VARCHAR2(250 CHAR) Yes NULL This is secondary the address of the company being inspected.
SITE_ADDRESS_3 VARCHAR2(250 CHAR) Yes NULL This is the tertiary address of the company being inspected.
SITE_CITY VARCHAR2(80 CHAR) Yes NULL This is the city of the company being inspected.
SITE_PROVINCE VARCHAR2(50 CHAR) Yes NULL This is the province of the company being inspected.
SITE_POSTALCODE VARCHAR2(20 CHAR) Yes NULL This is the postal code of the company being inspected.
SITE_COUNTRY VARCHAR2(80 CHAR) Yes NULL This is the country of the company being inspected.
INSPECTION_SUMMARY CLOB Yes NULL This is the inspectors summary of the inspection.
REPORT_DELIVERED_TO_ID Foreign VARCHAR2(36 CHAR) Yes NULL This is the unique record identifier of the contact of the company who received the final inspection report.
REPORT_DELIVERED_TO VARCHAR2(200 CHAR) Yes NULL This is the name of the contact of the company who received the final inspection report.
RESPONSE_EXPECTED_DATE DATE Yes NULL This is the date that Health Canada is expecting a response from the company about information captured in the final inspection report.
INTERNAL_CLOSING_COMMENTS CLOB Yes NULL This is the inspector's internal comments and recommendations after the completion of the inspection.
PERSONNEL_CHANGES VARCHAR2(2000 BYTE) Yes NULL This indicates any personnel changes at the company inspected since their previous inspection.
IID_STATUS_EN VARCHAR2(50 CHAR) Yes NULL This indicates the status of the IID in English. DHPID Transparency IID
IID_STATUS_FR VARCHAR2(50 CHAR) Yes NULL This indicates the status of the IID in French. DHPID Transparency IID
IID_STATUS_DATE DATE Yes NULL This indicates the status date of the IID. DHPID Transparency IID
IID_REVISION_LOG VARCHAR2(2000 CHAR) Yes NULL This indicates any revisions carried out to the IID. DHPID Transparency IID
IRC_STATUS_EN VARCHAR2(50 CHAR) Yes NULL This indicates the status of the IRC in English. DHPID Transparency IRC
IRC_STATUS_FR VARCHAR2(50 CHAR) Yes NULL This indicates the status of the IRC in French. DHPID Transparency IRC
INSP_RATING_EN VARCHAR2(100 CHAR) Yes NULL This indicates the final inspection report rating for the IRC in English. DHPID Transparency IRC. Matches the FINAL_REPORT_RATING_EN value.
INSP_RATING_FR VARCHAR2(100 CHAR) Yes NULL This indicates the final inspection report rating for the IRC in French. DHPID Transparency IRC. Matches the FINAL_REPORT_RATING_FR value.
INSP_OUTCOME_EN VARCHAR2(2000 CHAR) Yes NULL This is a standard line related to the INSP_RATING_EN value. DHPID Transparency IRC
INSP_OUTCOME_FR VARCHAR2(2000 CHAR) Yes NULL This is a standard line related to the INSP_RATING_FR value. DHPID Transparency IRC
INSP_REPORT_WAITING VARCHAR2(200 CHAR) Yes NULL This is the inspector's name who sent the inspection report for approval in the record log.
INSP_REPORT_WAITING_DATE DATE Yes NULL This is the date the inspector sent the inspection report for approval in the recocrd log.
INSP_REPORT_APPROVED_BY VARCHAR2(200 CHAR) Yes NULL This is the supervisor's name who approved the inspection report in the record log.
INSP_REPORT_APPROVED_BY_DATE DATE Yes NULL This is the date the supervisor approved the inspection report in the record log.
INSP_CLOSED_WAITING VARCHAR2(200 CHAR) Yes NULL This is the inspector's name who sent the inspection for closure in the record log.
INSP_CLOSED_WAITING_DATE DATE Yes NULL This is the date the inspector sent the inspection report for closure in the record log.
INSP_CLOSED_APPROVED VARCHAR2(200 CHAR) Yes NULL This is the supervisor's name who approved the inspection for closure in the record log.
INSP_CLOSED_APPROVED_DATE DATE Yes NULL This is the date the supervisor approved the inspection for closure in the record log.
CREATEDBYNAME VARCHAR2(100 CHAR) Yes NULL This identifies the resource that created the record.
CREATEDON DATE Yes NULL This identifies the date that record was created.
MODIFIEDBYNAME VARCHAR2(100 CHAR) Yes NULL This identifies the resource that las modified the record.
MODIFIEDON DATE Yes NULL This identifies the date that record was last modified.

MD_INS_CASE_CAPA

This table holds information about the corrective action plan for an inspection observation.

Column Name Data Type Key Type Nullable Data Default Example Data Description Comments
CAPA_ID VARCHAR2(36 CHAR) Primary No NULL ad5e55f6-8b21-ee11-9966-000d3ae86452 This is the unique record identifier for the CAPA.
INSP_ID VARCHAR2(36 CHAR) Foreign No NULL 3d7a309e-ce46-40c5-a6c2-f3ea80971d48 This is the unique record identifier for the parent inspection. Use to join with MD_INS_CASE.INSP_ID.
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) Foreign 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=120544"