Difference between revisions of "MDCCD Data Warehouse Data Dictionary"

From wiki
Jump to navigation Jump to search
 
(21 intermediate revisions by the same user not shown)
Line 1: Line 1:
 
== Overview ==
 
== Overview ==
 
This page provides a data dictionary for each table that is held within the [[MDCCD Data Warehouse]].
 
This page provides a data dictionary for each table that is held within the [[MDCCD Data Warehouse]].
 +
 +
'''DISCLAIMER''': All data in the "Example Data" column is fictitious and does not represent parties authorized under the ''Food and Drugs Act'' and/or the ''Medical Device Regulations''.
  
 
== Medical Devices ==
 
== Medical Devices ==
  
 
=== MD_INS_CASE ===
 
=== MD_INS_CASE ===
 +
[[MDCCD Data Warehouse|Return to Home Page.]]
 +
 
This table holds the main inspection information.
 
This table holds the main inspection information.
−
{| class="wikitable"
+
{| class="wikitable sortable mw-collapsible"
 
|+
 
|+
−
!Column Name
+
! scope="col" style="width: 350px;" | Column Name
−
!Key Type
+
! scope="col" style="width: 100px;" |DataType
−
!DataType
+
! scope="col" style="width: 50px;" |Key Type
−
!Nullable
+
! scope="col" style="width: 50px;" |Nullable
−
!Data Default
+
! scope="col" style="width: 50px;" |Data Default
−
!Example Data
+
! scope="col" style="width: 500px;" |Example Data
−
!Description
+
! scope="col" style="width: 350px;" | Description
−
!Comments
+
! scope="col" style="width: 350px;" |Comments
 
|-
 
|-
 
|INSP_ID
 
|INSP_ID
Line 22: Line 26:
 
|No
 
|No
 
|NULL
 
|NULL
−
|
+
|5c6afa3c-3f8a-4641-8da4-9588ec7769d7
 
|This is the unique record identifier for the inspection.
 
|This is the unique record identifier for the inspection.
 
|
 
|
 
|-
 
|-
 
|INSP_NUM
 
|INSP_NUM
−
|
+
|Foreign
 
|VARCHAR2(200 CHAR)
 
|VARCHAR2(200 CHAR)
 
|No
 
|No
 
|NULL
 
|NULL
−
|
+
|00070-MDCCD HEALTHCARE INC
 
|This is the inspection number.
 
|This is the inspection number.
 
|
 
|
Line 40: Line 44:
 
|No
 
|No
 
|NULL
 
|NULL
−
|
+
|5c6afa3c-3f8a-4641-8da4-9588ec7769d7
 
|This is the unique record identifier for the company inspected.
 
|This is the unique record identifier for the company inspected.
 
|
 
|
Line 46: Line 50:
 
|SITE_NAME
 
|SITE_NAME
 
|
 
|
−
|VARCHAR2(160 CHAR)
+
| VARCHAR2(160 CHAR)
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
|MDCCD HEALTHCARE INC
 
|This is the name of the company insepcted.
 
|This is the name of the company insepcted.
 
|
 
|
 
|-
 
|-
 
|LICENCE_NO
 
|LICENCE_NO
−
|Foreign
+
| Foreign
 
|NUMBER(6,0)
 
|NUMBER(6,0)
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
| 1234
 
|This is the establishment licence number of the company inspected.
 
|This is the establishment licence number of the company inspected.
 
|Use to join with MDS table ESTABLISHMENT.ESTABLISHMENT_ID.
 
|Use to join with MDS table ESTABLISHMENT.ESTABLISHMENT_ID.
Line 67: Line 71:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
|123456
 
|This is the internal Health Canada company identifier.
 
|This is the internal Health Canada company identifier.
 
|Use to join with MDS table ESTABLISHMENT.COMPANY_ID or COMPANY.COMPANY_ID.
 
|Use to join with MDS table ESTABLISHMENT.COMPANY_ID or COMPANY.COMPANY_ID.
Line 76: Line 80:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
|HC1-11-11IM
 
|This is the internal program based file identifier.
 
|This is the internal program based file identifier.
 
|
 
|
Line 82: Line 86:
 
|REGION_CD_EN
 
|REGION_CD_EN
 
|
 
|
−
|VARCHAR2(50 CHAR)
+
| VARCHAR2(50 CHAR)
−
|Yes
+
| Yes
 
|NULL
 
|NULL
−
|
+
|East
 
|This is the operational centre assigned to the inspection in English.
 
|This is the operational centre assigned to the inspection in English.
 
|
 
|
Line 94: Line 98:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
|Est
−
|This is the operational centre assigned to the inspection in French.
+
| 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
+
| NULL
−
|
+
|Domestic - New - Onsite
 
|This is the type of inspection in English.
 
|This is the type of inspection in English.
 
|
 
|
Line 112: Line 116:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
| Domestique - Nouvelle - Sur place
 
|This is the type of inspection in French.
 
|This is the type of inspection in French.
 
|
 
|
Line 121: Line 125:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
|0bb101ab-5009-ee11-8f6e-000d3ae86452
 
|This is the unique record identifier of the lead inspector.
 
|This is the unique record identifier of the lead inspector.
 
|
 
|
Line 130: Line 134:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
| John Smith
 
|This is the name of the lead inspector.
 
|This is the name of the lead inspector.
 
|
 
|
Line 136: Line 140:
 
|LEAD_INSPECTOR_BADGE
 
|LEAD_INSPECTOR_BADGE
 
|
 
|
−
|VARCHAR2(50 BYTE)
+
| VARCHAR2(50 BYTE)
 
|Yes
 
|Yes
−
|NULL
+
| NULL
−
|
+
|9988776
 
|This is the badge number of the lead inspector.
 
|This is the badge number of the lead inspector.
 
|
 
|
Line 148: Line 152:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
|5c6afa3c-3f8a-4641-8da4-9588ec7769d7
 
|This is the unique record identifier of the supervisor for the inspection.
 
|This is the unique record identifier of the supervisor for the inspection.
 
|
 
|
Line 154: Line 158:
 
|SUPERVISOR
 
|SUPERVISOR
 
|
 
|
−
|VARCHAR2(200 CHAR)
+
| VARCHAR2(200 CHAR)
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
| John Smith
 
|This is the name of the supervisor for the inspection.
 
|This is the name of the supervisor for the inspection.
 
|
 
|
Line 166: Line 170:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
|Open
 
|This is the status of the inspection in English.
 
|This is the status of the inspection in English.
 
|
 
|
Line 175: Line 179:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
| Ouvert
 
|This is the status of the inspection in French.
 
|This is the status of the inspection in French.
 
|
 
|
Line 184: Line 188:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
|Food & Drugs Act, Medical Device Regulations
−
|This is the compliance standard that the site was inspected against in English. Either the FDA or MDR.  
+
|This is the compliance standard that the site was inspected against in English. Either the FDA or MDR.
 
|
 
|
 
|-
 
|-
Line 193: Line 197:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
| Loi sur les aliments et drogues, Règlement sur les instruments médicaux
−
|This is the compliance standard that the site was inspected against in French. Either the FDA or MDR.  
+
|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
 
|NULL
−
|
+
|Compliant
 
|This is the draft inspection rating in English.
 
|This is the draft inspection rating in English.
 
|
 
|
Line 211: Line 215:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
|Conforme
 
|This is the draft inspection rating in French.
 
|This is the draft inspection rating in French.
 
|
 
|
Line 219: Line 223:
 
|DATE
 
|DATE
 
|Yes
 
|Yes
−
|NULL
+
| NULL
−
|
+
|24-03-21
 
|This is the date that the draft inspection rating was issued.
 
|This is the date that the draft inspection rating was issued.
 
|
 
|
Line 226: Line 230:
 
|FINAL_REPORT_RATING_EN
 
|FINAL_REPORT_RATING_EN
 
|
 
|
−
|VARCHAR2(100 CHAR)
+
| VARCHAR2(100 CHAR)
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
|Compliant
 
|This is the final inspection rating in English.
 
|This is the final inspection rating in English.
 
|
 
|
Line 238: Line 242:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
|Conforme
 
|This is the final inspection rating in French.
 
|This is the final inspection rating in French.
 
|
 
|
Line 245: Line 249:
 
|
 
|
 
|DATE
 
|DATE
−
|Yes
+
| Yes
 
|NULL
 
|NULL
−
|
+
|24-03-21
 
|This is the date that the final inspection rating was issued.
 
|This is the date that the final inspection rating was issued.
 
|
 
|
Line 256: Line 260:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
|24-03-22
 
|This is the date that an amended inspection report was issued to the company.
 
|This is the date that an amended inspection report was issued to the company.
 
|
 
|
Line 263: Line 267:
 
|
 
|
 
|VARCHAR2(2000 CHAR)
 
|VARCHAR2(2000 CHAR)
−
|Yes
+
| Yes
−
|NULL
+
| NULL
−
|
+
| None
 
|This indicates if any samples were taken during the inspection.
 
|This indicates if any samples were taken during the inspection.
 
|
 
|
Line 274: Line 278:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
|24-01-30
 
|This indicates when the inspection was started.
 
|This indicates when the inspection was started.
−
|Use this to calculate the number of inspections per month for the dashboard.
+
| Use this to calculate the number of inspections per month for the dashboard.
 
|-
 
|-
 
|INSP_END_DATE
 
|INSP_END_DATE
 
|
 
|
−
|DATE
+
| DATE
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
|24-01-30
 
|This indicates when the inspection was finished.
 
|This indicates when the inspection was finished.
 
|Use this to calculate the number of completed inspections per month/quarterly compliance ratings for the dashboard.
 
|Use this to calculate the number of completed inspections per month/quarterly compliance ratings for the dashboard.
Line 289: Line 293:
 
|CLOSING_MEETING_DATE
 
|CLOSING_MEETING_DATE
 
|
 
|
−
|DATE
+
| DATE
 
|Yes
 
|Yes
 
|NULL
 
|NULL
 +
| 23-11-15
 +
|This indicates when the inspector had the closing meeting with the company.
 
|
 
|
−
|This indicates when the inspector had the closing meeting with the company.
 
 
|-
 
|-
 
|NEXT_INSPECTION_DATE
 
|NEXT_INSPECTION_DATE
 
|
 
|
−
|DATE
+
| DATE
 
|Yes
 
|Yes
−
|NULL
+
| NULL
 +
|24-03-15
 +
|This indicates when the company is due for its next inspection
 
|
 
|
−
|This indicates when the company is due for its next inspection
 
 
|-
 
|-
 
|AMENDED_CD_EN
 
|AMENDED_CD_EN
Line 308: Line 314:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
 +
|Yes
 +
|This indicates whether the inspection report has been amended in English.
 
|
 
|
−
|This indicates whether the inspection report has been amended in English.
 
 
|-
 
|-
 
|AMENDED_CD_FR
 
|AMENDED_CD_FR
Line 316: Line 323:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
 +
|Oui
 +
|This indicates whether the inspection report has been amended in French.
 
|
 
|
−
|This indicates whether the inspection report has been amended in French.
 
 
|-
 
|-
 
|AMENDED_BY
 
|AMENDED_BY
Line 324: Line 332:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
 +
|John Smith
 +
|This indicates who amended the inspection.
 
|
 
|
−
|This indicates who amended the inspection.
 
 
|-
 
|-
 
|AMENDED_DATE
 
|AMENDED_DATE
 
|
 
|
−
|DATE
+
| DATE
 
|Yes
 
|Yes
−
|NULL
+
| NULL
 +
|24-03-01
 +
|This indicates when the report was amended.
 
|
 
|
−
|This indicates when the report was amended.
 
 
|-
 
|-
 
|FIRST_COMM_DATE
 
|FIRST_COMM_DATE
Line 340: Line 350:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
 +
|24-02-21
 +
|This is the date the inspector first communicated with the company for the inspection.
 
|
 
|
−
|This is the date the inspector first communicated with the company for the inspection.
 
 
|-
 
|-
 
|FIRST_COMM_MODE
 
|FIRST_COMM_MODE
Line 348: Line 359:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
 +
|E-mail
 +
|This is how the inspector first communicated with the company for the inspection.
 
|
 
|
−
|This is how the inspector first communicated with the company for the inspection.
 
 
|-
 
|-
 
|FIRST_COMM_MODE_EN
 
|FIRST_COMM_MODE_EN
Line 358: Line 370:
 
|
 
|
 
|This is how the inspector first communicated with the company for the inspection in English.
 
|This is how the inspector first communicated with the company for the inspection in English.
 +
|
 
|-
 
|-
 
|FIRST_COMM_MODE_FR
 
|FIRST_COMM_MODE_FR
Line 366: Line 379:
 
|
 
|
 
|This is how the inspector first communicated with the company for the inspection in French.
 
|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
 
|NULL
 +
|John Smith
 +
|This is the name of the emergency contact for the company at the time of inspection.
 
|
 
|
−
|This is the name of the emergency contact for the company at the time of inspection.
 
 
|-
 
|-
 
|EMERGENCY_CONTACT_PHONE
 
|EMERGENCY_CONTACT_PHONE
Line 380: Line 395:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
 +
|613-123-4567
 +
|This is the phone number of the emergency contact for the company at the time of inspection.
 
|
 
|
−
|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
Line 388: Line 404:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
 +
|5c6afa3c-3f8a-4641-8da4-9588ec7769d7
 +
|This is the unique record identifier of the senior official of the company at the time of insepction.
 
|
 
|
−
|This is the unique record identifier of the senior official of the company at the time of insepction.
 
 
|-
 
|-
 
|SENIOR_OFFICIAL_NAME
 
|SENIOR_OFFICIAL_NAME
Line 396: Line 413:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
 +
|John Smith
 +
| This is the name of the senior official for the company at the time of inspection.
 
|
 
|
−
|This is the name of the senior official for the company at the time of inspection.
 
 
|-
 
|-
 
|SENIOR_OFFICIAL_TITLE
 
|SENIOR_OFFICIAL_TITLE
Line 403: Line 421:
 
|VARCHAR2(100 CHAR)
 
|VARCHAR2(100 CHAR)
 
|Yes
 
|Yes
−
|NULL
+
| NULL
−
|
+
|General Manager
 
|This is the job title of the senior official for the company at the time of inspection.
 
|This is the job title of the senior official for the company at the time of inspection.
 
|-
 
|-
Line 412: Line 430:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
|5c6afa3c-3f8a-4641-8da4-9588ec7769d7
 
|This is the unique record identifier of the contact of the company at the time of inspection.
 
|This is the unique record identifier of the contact of the company at the time of inspection.
 
|
 
|
Line 421: Line 439:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
|John Smith
 
|This is the name of the contact of the company at the time of inspection.
 
|This is the name of the contact of the company at the time of inspection.
 
|
 
|
Line 428: Line 446:
 
|
 
|
 
|VARCHAR2(100 CHAR)
 
|VARCHAR2(100 CHAR)
−
|Yes
+
| Yes
 
|NULL
 
|NULL
−
|
+
|Manger, Quality Assurance
 
|This is the job title of the contact of the company at the time of inspection.
 
|This is the job title of the contact of the company at the time of inspection.
 
|
 
|
Line 438: Line 456:
 
|VARCHAR2(100 CHAR)
 
|VARCHAR2(100 CHAR)
 
|Yes
 
|Yes
−
|NULL
+
| NULL
−
|
+
|613-123-4567
 
|This is the phone number of the contact of the company at the time of inspection.
 
|This is the phone number of the contact of the company at the time of inspection.
 
|-
 
|-
Line 445: Line 463:
 
|
 
|
 
|VARCHAR2(100 CHAR)
 
|VARCHAR2(100 CHAR)
−
|Yes
+
| Yes
 
|NULL
 
|NULL
−
|
+
| 613-123-4567
 
|This is the fax of the contact of the company at the time of inspection.
 
|This is the fax of the contact of the company at the time of inspection.
 
|
 
|
Line 456: Line 474:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
|john@mdccdhealthcare.com
 
|This is the e-mail of the contact of the company at the time of inspection.
 
|This is the e-mail of the contact of the company at the time of inspection.
 
|
 
|
Line 465: Line 483:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
| 123 Main St
 
|This is the address of the company being inspected.
 
|This is the address of the company being inspected.
 
|
 
|
Line 474: Line 492:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
| Suite 101
 
|This is secondary the address of the company being inspected.
 
|This is secondary the address of the company being inspected.
 
|
 
|
Line 492: Line 510:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
|OTTAWA
 
|This is the city of the company being inspected.
 
|This is the city of the company being inspected.
 
|-
 
|-
Line 500: Line 518:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
|ON
 
|This is the province of the company being inspected.
 
|This is the province of the company being inspected.
 
|
 
|
Line 508: Line 526:
 
|VARCHAR2(20 CHAR)
 
|VARCHAR2(20 CHAR)
 
|Yes
 
|Yes
−
|NULL
+
| NULL
−
|
+
|A1B 2C3
 
|This is the postal code of the company being inspected.
 
|This is the postal code of the company being inspected.
 
|
 
|
Line 518: Line 536:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
|CANADA
 
|This is the country of the company being inspected.
 
|This is the country of the company being inspected.
 
|
 
|
Line 525: Line 543:
 
|
 
|
 
|CLOB
 
|CLOB
−
|Yes
+
| Yes
 
|NULL
 
|NULL
−
|
+
|The meeting occured on March 1, 1994 with John Smith (Manager at MDCCD Healthcare and Mohamed Ali (Inspector at Health Canada)
 
|This is the inspectors summary of the inspection.
 
|This is the inspectors summary of the inspection.
 
|
 
|
Line 536: Line 554:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
|5c6afa3c-3f8a-4641-8da4-9588ec7769d7
 
|This is the unique record identifier of the contact of the company who received the final inspection report.
 
|This is the unique record identifier of the contact of the company who received the final inspection report.
 
|
 
|
Line 542: Line 560:
 
|REPORT_DELIVERED_TO
 
|REPORT_DELIVERED_TO
 
|
 
|
−
|VARCHAR2(200 CHAR)
+
| VARCHAR2(200 CHAR)
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
| Mohamed Ali
 
|This is the name of the contact of the company who received the final inspection report.
 
|This is the name of the contact of the company who received the final inspection report.
 
|
 
|
Line 554: Line 572:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
|24-02-28
 
|This is the date that Health Canada is expecting a response from the company about information captured in the final inspection report.
 
|This is the date that Health Canada is expecting a response from the company about information captured in the final inspection report.
 
|
 
|
Line 563: Line 581:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
|20230406 Inspector spoke with company to schedule inspection. Follow-up email sent to company.
 
|This is the inspector's internal comments and recommendations after the completion of the inspection.
 
|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
+
| NULL
−
|
+
|Change in Regulatory Affairs department. Mr. Smith now in charge of complaint handling.
 
|This indicates any personnel changes at the company inspected since their previous inspection.
 
|This indicates any personnel changes at the company inspected since their previous inspection.
 
|
 
|
Line 581: Line 599:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
|In Progress
 
|This indicates the status of the IID in English.
 
|This indicates the status of the IID in English.
 
|DHPID Transparency IID
 
|DHPID Transparency IID
 
|-
 
|-
−
|IID_STATUS_FR
+
| IID_STATUS_FR
 
|
 
|
 
|VARCHAR2(50 CHAR)
 
|VARCHAR2(50 CHAR)
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
|En cours
 
|This indicates the status of the IID in French.
 
|This indicates the status of the IID in French.
 
|DHPID Transparency IID
 
|DHPID Transparency IID
Line 599: Line 617:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
| 24-03-15
 
|This indicates the status date of the IID.
 
|This indicates the status date of the IID.
 
|DHPID Transparency IID
 
|DHPID Transparency IID
Line 608: Line 626:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
|Updated deficiency.
−
|This indicates any revisions carried out to the IID.
+
| This indicates any revisions carried out to the IID.
 
|DHPID Transparency IID
 
|DHPID Transparency IID
 
|-
 
|-
Line 617: Line 635:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
|IRC Ready For Publication
 
|This indicates the status of the IRC in English.
 
|This indicates the status of the IRC in English.
 
|DHPID Transparency IRC
 
|DHPID Transparency IRC
Line 623: Line 641:
 
|IRC_STATUS_FR
 
|IRC_STATUS_FR
 
|
 
|
−
|VARCHAR2(50 CHAR)
+
| VARCHAR2(50 CHAR)
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
|IRC prêt pour la publication
 
|This indicates the status of the IRC in French.
 
|This indicates the status of the IRC in French.
 
|DHPID Transparency IRC
 
|DHPID Transparency IRC
Line 635: Line 653:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
|Compliant
 
|This indicates the final inspection report rating for the IRC in English.
 
|This indicates the final inspection report rating for the IRC in English.
−
|DHPID Transparency IRC. Matches the FINAL_REPORT_RATING_EN value.
+
|DHPID Transparency IRC.  
 +
Matches the FINAL_REPORT_RATING_EN value.
 
|-
 
|-
 
|INSP_RATING_FR
 
|INSP_RATING_FR
Line 644: Line 663:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
|Conforme
 
|This indicates the final inspection report rating for the IRC in French.
 
|This indicates the final inspection report rating for the IRC in French.
−
|DHPID Transparency IRC. Matches the FINAL_REPORT_RATING_FR value.
+
|DHPID Transparency IRC.  
 +
Matches the FINAL_REPORT_RATING_FR value.
 
|-
 
|-
 
|INSP_OUTCOME_EN
 
|INSP_OUTCOME_EN
Line 653: Line 673:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
|The inspection resulted in a ‘compliant’ inspection rating. A compliant inspection rating allows a company to keep their licence and continue conducting business.  A compliant rating does not mean that there were no observations during the inspection.  If a company receives a compliant inspection rating and if observations were noted, the company is required to develop a plan to correct the observations to prevent them from occurring again.
 
|This is a standard line related to the INSP_RATING_EN value.
 
|This is a standard line related to the INSP_RATING_EN value.
 
|DHPID Transparency IRC
 
|DHPID Transparency IRC
Line 662: Line 682:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
|Une cote « conforme » a été émise aux termes de l’inspection. \r\n\r\nUne cote d’inspection « conforme » permet à une entreprise de conserver sa licence et de poursuivre ses activités. Une cote « conforme » ne signifie toutefois pas qu’aucune observation n’a été relevée pendant l’inspection. Si une entreprise reçoit une cote d’inspection « conforme » et si des observations ont été relevées, elle doit élaborer un plan pour corriger ces observations et éviter qu’elles ne se reproduisent
 
|This is a standard line related to the INSP_RATING_FR value.
 
|This is a standard line related to the INSP_RATING_FR value.
 
|DHPID Transparency IRC
 
|DHPID Transparency IRC
Line 671: Line 691:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
|John Smith
 
|This is the inspector's name who sent the inspection report for approval in the record log.
 
|This is the inspector's name who sent the inspection report for approval in the record log.
 
|
 
|
Line 677: Line 697:
 
|INSP_REPORT_WAITING_DATE
 
|INSP_REPORT_WAITING_DATE
 
|
 
|
−
|DATE
+
| DATE
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
|23-11-27
 
|This is the date the inspector sent the inspection report for approval in the recocrd log.
 
|This is the date the inspector sent the inspection report for approval in the recocrd log.
 
|
 
|
Line 689: Line 709:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
|John Smith
 
|This is the supervisor's name who approved the inspection report in the record log.
 
|This is the supervisor's name who approved the inspection report in the record log.
 
|
 
|
Line 695: Line 715:
 
|INSP_REPORT_APPROVED_BY_DATE
 
|INSP_REPORT_APPROVED_BY_DATE
 
|
 
|
−
|DATE
+
| DATE
 
|Yes
 
|Yes
−
|NULL
+
| NULL
−
|
+
|23-11-27
 
|This is the date the supervisor approved the inspection report in the record log.
 
|This is the date the supervisor approved the inspection report in the record log.
 
|
 
|
Line 707: Line 727:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
|John Smith
 
|This is the inspector's name who sent the inspection for closure in the record log.
 
|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
 
|NULL
−
|
+
|23-11-29
 
|This is the date the inspector sent the inspection report for closure in the record log.
 
|This is the date the inspector sent the inspection report for closure in the record log.
 
|
 
|
Line 724: Line 744:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
|John Smith
 
|This is the supervisor's name who approved the inspection for closure in the record log.
 
|This is the supervisor's name who approved the inspection for closure in the record log.
 
|
 
|
Line 733: Line 753:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
|23-11-29
 
|This is the date the supervisor approved the inspection for closure in the record log.
 
|This is the date the supervisor approved the inspection for closure in the record log.
 
|
 
|
Line 742: Line 762:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
|John Smith
 
|This identifies the resource that created the record.
 
|This identifies the resource that created the record.
 
|
 
|
Line 748: Line 768:
 
|CREATEDON
 
|CREATEDON
 
|
 
|
−
|DATE
+
| DATE
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
|23-08-14
 
|This identifies the date that record was created.
 
|This identifies the date that record was created.
 
|
 
|
Line 760: Line 780:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
| John Smith
−
|This identifies the resource that las modified the record.
+
| This identifies the resource that last modified the record.
 
|
 
|
 
|-
 
|-
Line 769: Line 789:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|
+
|23-12-25
 
|This identifies the date that record was last modified.
 
|This identifies the date that record was last modified.
 
|
 
|
 
|}
 
|}
  
−
=== MD_INS_CASE_CAPA ===
+
===MD_INS_CASE_CAPA===
 +
[[MDCCD Data Warehouse|Return to Home Page.]]
 +
 
 
This table holds information about the corrective action plan for an inspection observation.
 
This table holds information about the corrective action plan for an inspection observation.
−
{| class="wikitable"
+
{| class="wikitable sortable mw-collapsible"
 
|+
 
|+
−
!Column Name
+
! scope="col" style="width: 350px;" | Column Name
−
!Data Type
+
! scope="col" style="width: 100px;" |DataType
−
!Key Type
+
! scope="col" style="width: 50px;" |Key Type
−
!Nullable
+
! scope="col" style="width: 50px;" |Nullable
−
!Data Default
+
! scope="col" style="width: 50px;" |Data Default
−
!Example Data
+
! scope="col" style="width: 500px;" |Example Data
−
!Description
+
! scope="col" style="width: 350px;" | Description
−
!Comments
+
! scope="col" style="width: 350px;" |Comments
 
|-
 
|-
−
|CAPA_ID
+
| CAPA_ID
 
|VARCHAR2(36 CHAR)
 
|VARCHAR2(36 CHAR)
 
|Primary
 
|Primary
 
|No
 
|No
 
|NULL
 
|NULL
−
|ad5e55f6-8b21-ee11-9966-000d3ae86452
+
|5c6afa3c-3f8a-4641-8da4-9588ec7769d7
 
|This is the unique record identifier for the CAPA.
 
|This is the unique record identifier for the CAPA.
 
|
 
|
Line 801: Line 823:
 
|No
 
|No
 
|NULL
 
|NULL
−
|3d7a309e-ce46-40c5-a6c2-f3ea80971d48
+
|5c6afa3c-3f8a-4641-8da4-9588ec7769d7
 
|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.
 
|Use to join with MD_INS_CASE.INSP_ID.
Line 807: Line 829:
 
|INSP_NUM
 
|INSP_NUM
 
|VARCHAR2(200 CHAR)
 
|VARCHAR2(200 CHAR)
−
|
+
|Foreign
 
|No
 
|No
 
|NULL
 
|NULL
−
|00086-BENEMAX DBA. BENEMAX HEALTH AND WELLNESS
+
|00070-MDCCD HEALTHCARE INC
 
|This is the parent inspection number.
 
|This is the parent inspection number.
 
|
 
|
Line 819: Line 841:
 
|No
 
|No
 
|NULL
 
|NULL
−
|07244e35-fbf1-ee11-a1fe-6045bd6005cc
+
|5c6afa3c-3f8a-4641-8da4-9588ec7769d7
 
|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.
 
|
 
|
Line 827: Line 849:
 
|
 
|
 
|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.
Line 833: Line 855:
 
|-
 
|-
 
|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 manufacturer sent an email to the inspector to indicate that the program has been solved.
 
|This is the CAPA as inputted by the inspector.
 
|This is the CAPA as inputted by the inspector.
 
|
 
|
Line 846: Line 868:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|Jeff Tyndall
+
| John Smith
 
|This identifies the resource that created the record.
 
|This identifies the resource that created the record.
 
|
 
|
Line 864: Line 886:
 
|Yes
 
|Yes
 
|NULL
 
|NULL
−
|Jeff Tyndall
+
| John Smith
−
|This identifies the resource that las modified the record.
+
| This identifies the resource that last modified the record.
 
|
 
|
 
|-
 
|-
Line 878: Line 900:
 
|}
 
|}
  
−
=== MD_INS_CASE_DESIGNATION ===
+
===MD_INS_CASE_DESIGNATION===
 +
[[MDCCD Data Warehouse|Return to Home Page.]]
  
−
=== MD_CASE_ESTACTIVITY ===
+
This table holds information that categorizes or "tags" an inspection. These are flexible codes used to capture information if a designated field does not exist.
 +
{| class="wikitable sortable mw-collapsible"
 +
|+
 +
! scope="col" style="width: 350px;" | Column Name
 +
! scope="col" style="width: 100px;" |DataType
 +
! scope="col" style="width: 50px;" |Key Type
 +
! scope="col" style="width: 50px;" |Nullable
 +
! scope="col" style="width: 50px;" |Data Default
 +
! scope="col" style="width: 500px;" |Example Data
 +
! scope="col" style="width: 350px;" | Description
 +
! scope="col" style="width: 350px;" |Comments
 +
|-
 +
|INSP_DESIGNATION_ID
 +
|VARCHAR2(36 CHAR)
 +
|Primary
 +
|No
 +
|NULL
 +
|5c6afa3c-3f8a-4641-8da4-9588ec7769d7
 +
|This is the unique record identifier for the designation.
 +
|
 +
|-
 +
|INSP_ID
 +
|VARCHAR2(36 CHAR)
 +
|Foreign
 +
|No
 +
|NULL
 +
|5c6afa3c-3f8a-4641-8da4-9588ec7769d7
 +
|This is the unique record identifier for the parent inspection.
 +
|Use to join with MD_INS_CASE.INSP_ID.
 +
|-
 +
|INSP_NUM
 +
|VARCHAR2(200 CHAR)
 +
|Foreign
 +
|No
 +
|NULL
 +
| 00070-MDCCD HEALTHCARE INC
 +
|This is the parent inspection number.
 +
|
 +
|-
 +
|DESIGNATION_ID
 +
|VARCHAR2(36 CHAR)
 +
|Foreign
 +
|No
 +
|NULL
 +
|5c6afa3c-3f8a-4641-8da4-9588ec7769d7
 +
|This is the unique record identifier for the designation code.
 +
|
 +
|-
 +
|DESIGNATION_CODE
 +
| VARCHAR2(100 CHAR)
 +
|
 +
|Yes
 +
|NULL
 +
|5006
 +
|This is the designation code.
 +
|
 +
|-
 +
|DESIGNATION_NAME
 +
| VARCHAR2(250 CHAR)
 +
|
 +
|Yes
 +
|NULL
 +
|Covid
 +
|This is the name of the designation code.
 +
|-
 +
|DESIGNATION_DECODE_EN
 +
|VARCHAR2(100 CHAR)
 +
|
 +
|Yes
 +
|NULL
 +
|Covid
 +
|This is the name of the designation code in English.
 +
|
 +
|-
 +
|DESIGNATION_DECODE_FR
 +
| VARCHAR2(100 CHAR)
 +
|
 +
|Yes
 +
|NULL
 +
| Covid
 +
|This is the name of the designation code in French.
 +
|
 +
|-
 +
|CREATEDBYNAME
 +
|VARCHAR2(100 CHAR)
 +
|
 +
|Yes
 +
|NULL
 +
| John Smith
 +
|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
 +
| John Smith
 +
| This identifies the resource that last modified the record.
 +
|
 +
|-
 +
|MODIFIEDON
 +
|DATE
 +
|
 +
|Yes
 +
|NULL
 +
|22-08-26
 +
|This identifies the date that record was last modified.
 +
|
 +
|}
 +
 
 +
===MD_INS_CASE_ESTACTIVITY===
 +
[[MDCCD Data Warehouse|Return to Home Page.]]
 +
 
 +
This table holds information on the activities conducted by the company at the time of the inspection as recorded by the inspector.
 +
{| class="wikitable sortable mw-collapsible"
 +
|+
 +
! scope="col" style="width: 350px;" | Column Name
 +
! scope="col" style="width: 100px;" |DataType
 +
! scope="col" style="width: 50px;" |Key Type
 +
! scope="col" style="width: 50px;" |Nullable
 +
! scope="col" style="width: 50px;" |Data Default
 +
! scope="col" style="width: 500px;" |Example Data
 +
! scope="col" style="width: 350px;" | Description
 +
! scope="col" style="width: 350px;" |Comments
 +
|-
 +
|INSP_ACTIVITY_ID
 +
|VARCHAR2(36 CHAR)
 +
|Primary
 +
|No
 +
|NULL
 +
|5c6afa3c-3f8a-4641-8da4-9588ec7769d7
 +
|This is the unique record identifier for the establishment activity.
 +
|
 +
|-
 +
|INSP_ID
 +
|VARCHAR2(36 CHAR)
 +
|Foreign
 +
|No
 +
|NULL
 +
|5c6afa3c-3f8a-4641-8da4-9588ec7769d7
 +
|This is the unique record identifier for the parent inspection.
 +
|Use to join with MD_INS_CASE.INSP_ID.
 +
|-
 +
|INSP_NUM
 +
|VARCHAR2(200 CHAR)
 +
|Foreign
 +
|No
 +
|NULL
 +
|00070-MDCCD HEALTHCARE INC
 +
|This is the parent inspection number.
 +
|
 +
|-
 +
|ACTIVITY_ID
 +
|VARCHAR2(36 CHAR)
 +
|Foreign
 +
|No
 +
|NULL
 +
|5c6afa3c-3f8a-4641-8da4-9588ec7769d7
 +
|This is the unique record identifier for the activity value.
 +
|
 +
|-
 +
|ACTIVITY_NAME
 +
| VARCHAR2(205 CHAR)
 +
|
 +
| Yes
 +
|NULL
 +
|Importateur - Classe 2
 +
|This is the establishment activity.
 +
|
 +
|-
 +
|ACTIVITY_DECODE_EN
 +
|VARCHAR2(100 CHAR)
 +
|
 +
| Yes
 +
|NULL
 +
|Importer - Class 2
 +
|This is the name of the establishment activity in English.
 +
|
 +
|-
 +
|ACTIVITY_DECODE_FR
 +
|VARCHAR2(100 CHAR)
 +
|
 +
|Yes
 +
|NULL
 +
| Importateur - Classe 2
 +
|This is the name of the establishment activity in French.
 +
|
 +
|-
 +
|CREATEDBYNAME
 +
|VARCHAR2(100 CHAR)
 +
|
 +
|Yes
 +
|NULL
 +
| John Smith
 +
|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
 +
| John Smith
 +
| This identifies the resource that last modified the record.
 +
|
 +
|-
 +
|MODIFIEDON
 +
|DATE
 +
|
 +
|Yes
 +
|NULL
 +
|22-08-26
 +
|This identifies the date that record was last modified.
 +
|
 +
|}
 +
 
 +
===MD_INS_CASE_INSPECTORS===
 +
[[MDCCD Data Warehouse|Return to Home Page.]]
 +
 
 +
This table holds a listing of inspectors assigned to an inspection.
 +
{| class="wikitable sortable mw-collapsible"
 +
|+
 +
! scope="col" style="width: 350px;" | Column Name
 +
! scope="col" style="width: 100px;" |DataType
 +
! scope="col" style="width: 50px;" |Key Type
 +
! scope="col" style="width: 50px;" |Nullable
 +
! scope="col" style="width: 50px;" |Data Default
 +
! scope="col" style="width: 500px;" |Example Data
 +
! scope="col" style="width: 350px;" | Description
 +
! scope="col" style="width: 350px;" |Comments
 +
|-
 +
|BK_ID
 +
|VARCHAR2(36 CHAR)
 +
|Primary
 +
|No
 +
|NULL
 +
|5c6afa3c-3f8a-4641-8da4-9588ec7769d7
 +
|This is the unique record identifier for the inspector listing.
 +
|
 +
|-
 +
|INSP_ID
 +
|VARCHAR2(36 CHAR)
 +
|Foreign
 +
|No
 +
|NULL
 +
|5c6afa3c-3f8a-4641-8da4-9588ec7769d7
 +
|This is the unique record identifier for the parent inspection.
 +
|Use to join with MD_INS_CASE.INSP_ID.
 +
|-
 +
|INSP_NUM
 +
|VARCHAR2(200 CHAR)
 +
|Foreign
 +
|No
 +
|NULL
 +
|00070-MDCCD HEALTHCARE INC
 +
|This is the parent inspection number.
 +
|
 +
|-
 +
|INSPECTOR_NAME
 +
|VARCHAR2(100 CHAR)
 +
|
 +
|Yes
 +
|NULL
 +
|John Smith
 +
|This is the name of the inspector.
 +
|
 +
|-
 +
|LEADINSPECTOR_EN
 +
|VARCHAR2(3 CHAR)
 +
|
 +
| Yes
 +
|NULL
 +
|Yes
 +
|This indicates if the inspector is the lead inspector on the inspection in English.
 +
|
 +
|-
 +
|LEADINSPECTOR_FR
 +
|VARCHAR2(3 CHAR)
 +
|
 +
| Yes
 +
|NULL
 +
|Oui
 +
|This indicates if the inspector is the lead inspector on the inspection in French.
 +
|
 +
|-
 +
|ASSIGNSTATUS_EN
 +
|VARCHAR2(100 CHAR)
 +
|
 +
|Yes
 +
|NULL
 +
|Scheduled
 +
|**Not used by program.**
 +
|Contains default system information.
 +
|-
 +
|ASSIGNSTATUS_FR
 +
|VARCHAR2(100 CHAR)
 +
|
 +
| Yes
 +
|NULL
 +
|
 +
|**Not used by program.**
 +
|Contains default system information.
 +
|-
 +
|STARTTIME
 +
|DATE
 +
|
 +
|Yes
 +
|NULL
 +
|23-10-20
 +
|**Not used by program.**
 +
|Contains default system information.
 +
|-
 +
|ENDTIME
 +
|DATE
 +
|
 +
|Yes
 +
|NULL
 +
|23-10-20
 +
|**Not used by program.**
 +
|Contains default system information.
 +
|-
 +
|CREATEDBYNAME
 +
|VARCHAR2(100 CHAR)
 +
|
 +
|Yes
 +
|NULL
 +
| John Smith
 +
|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
 +
| John Smith
 +
| This identifies the resource that last modified the record.
 +
|
 +
|-
 +
|MODIFIEDON
 +
|DATE
 +
|
 +
|Yes
 +
|NULL
 +
|22-08-26
 +
|This identifies the date that record was last modified.
 +
|
 +
|}
 +
 
 +
===MD_INS_CASE_NOTES===
 +
[[MDCCD Data Warehouse|Return to Home Page.]]
 +
 
 +
This table holds notes related to the inspection.
 +
{| class="wikitable sortable mw-collapsible"
 +
|+
 +
! scope="col" style="width: 350px;" | Column Name
 +
! scope="col" style="width: 100px;" |DataType
 +
! scope="col" style="width: 50px;" |Key Type
 +
! scope="col" style="width: 50px;" |Nullable
 +
! scope="col" style="width: 50px;" |Data Default
 +
! scope="col" style="width: 500px;" |Example Data
 +
! scope="col" style="width: 350px;" | Description
 +
! scope="col" style="width: 350px;" |Comments
 +
|-
 +
|ANNOTATION_ID
 +
|VARCHAR2(36 CHAR)
 +
|Primary
 +
|No
 +
|NULL
 +
|5c6afa3c-3f8a-4641-8da4-9588ec7769d7
 +
|This is the unique record identifier for the note.
 +
|
 +
|-
 +
|INSP_ID
 +
|VARCHAR2(36 CHAR)
 +
|Foreign
 +
|No
 +
|NULL
 +
|5c6afa3c-3f8a-4641-8da4-9588ec7769d7
 +
|This is the unique record identifier for the parent inspection.
 +
|Use to join with MD_INS_CASE.INSP_ID.
 +
|-
 +
|INSP_NUM
 +
|VARCHAR2(200 CHAR)
 +
|Foreign
 +
|No
 +
| NULL
 +
|00070-MDCCD HEALTHCARE INC
 +
|This is the parent inspection number.
 +
|
 +
|-
 +
|SUBJECT
 +
|VARCHAR2(500 CHAR)
 +
|
 +
|Yes
 +
|NULL
 +
|Talked to client and set out inspection plan.
 +
|This is the subject of the note.
 +
|
 +
|-
 +
| NOTETEXT
 +
|CLOB
 +
|
 +
|Yes
 +
|NULL
 +
| Communication with company on 2023-09-01.
 +
|This is the body of the note.
 +
|
 +
|-
 +
|CREATEDBYNAME
 +
|VARCHAR2(100 CHAR)
 +
|
 +
|Yes
 +
|NULL
 +
| John Smith
 +
|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
 +
| John Smith
 +
| This identifies the resource that last modified the record.
 +
|
 +
|-
 +
|MODIFIEDON
 +
|DATE
 +
|
 +
|Yes
 +
|NULL
 +
|22-08-26
 +
|This identifies the date that record was last modified.
 +
|
 +
|}
 +
 
 +
===MD_INS_CASE_OBSERVATIONS===
 +
[[MDCCD Data Warehouse|Return to Home Page.]]
 +
 
 +
This table holds information about the observations for an inspection.
 +
{| class="wikitable sortable mw-collapsible"
 +
|+
 +
! scope="col" style="width: 350px;" | Column Name
 +
! scope="col" style="width: 100px;" |DataType
 +
! scope="col" style="width: 50px;" |Key Type
 +
! scope="col" style="width: 50px;" |Nullable
 +
! scope="col" style="width: 50px;" |Data Default
 +
! scope="col" style="width: 500px;" |Example Data
 +
! scope="col" style="width: 350px;" | Description
 +
! scope="col" style="width: 350px;" |Comments
 +
|-
 +
|OBS_ID
 +
|VARCHAR2(36 CHAR)
 +
|Primary
 +
|No
 +
|NULL
 +
|5c6afa3c-3f8a-4641-8da4-9588ec7769d7
 +
|This is the unique record identifier for the observation.
 +
|
 +
|-
 +
|OBS_NUM
 +
|VARCHAR2(200 CHAR)
 +
|
 +
|No
 +
|NULL
 +
|00070-MDCCD HEALTHCARE INC -obs0001132
 +
|This is the observation number.
 +
|
 +
|-
 +
|INSP_ID
 +
|VARCHAR2(36 CHAR)
 +
|Foreign
 +
|No
 +
|NULL
 +
|5c6afa3c-3f8a-4641-8da4-9588ec7769d7
 +
|This is the unique record identifier for the parent inspection.
 +
|Use to join with MD_INS_CASE.INSP_ID.
 +
|-
 +
|INSP_NUM
 +
|VARCHAR2(200 CHAR)
 +
|Foreign
 +
|No
 +
|NULL
 +
|00070-MDCCD HEALTHCARE INC
 +
|This is the parent inspection number.
 +
|-
 +
|OBS_TYPE_EN
 +
|VARCHAR2(200 CHAR)
 +
|
 +
|Yes
 +
|NULL
 +
|MDR - Establishment licence
 +
|This identifies the overall category of the Medical Device Regulations that the observation is cited under in English.
 +
|
 +
|-
 +
|OBS_TYPE_FR
 +
|VARCHAR2(200 CHAR)
 +
|
 +
|Yes
 +
|NULL
 +
|RIM - Licence d’établissement
 +
|This identifies the overall category of the Medical Device Regulations that the observation is cited under in French.
 +
|
 +
|-
 +
|REG_SECTION_EN
 +
|VARCHAR2(150 CHAR)
 +
|
 +
|Yes
 +
|NULL
 +
|s.45 (f) Device classes
 +
|This identifies the specific section/subsection of the Medical Device Regulations that the observation is cited under in English.
 +
|
 +
|-
 +
|REG_SECTION_FR
 +
|VARCHAR2(150 CHAR)
 +
|
 +
|Yes
 +
|NULL
 +
|MDR s.45 (f) Classes des instruments  
 +
|This identifies the specific section/subsection of the Medical Device Regulations that the observation is cited under in French.
 +
|
 +
|-
 +
|OBS_RISK
 +
|NUMBER(1,0)
 +
|
 +
|Yes
 +
|NULL
 +
|1
 +
|This identifies the risk level of the observation.
 +
|Scale 1-3. Risk 1 is the highest and risk 3 is the lowest.
 +
|-
 +
|OBS_REPEAT_EN
 +
|VARCHAR2(30 CHAR)
 +
|
 +
|Yes
 +
|NULL
 +
|Repeat Observation
 +
|This indicates if the observation has occurred before on a previous inspection with the company in English.
 +
|
 +
|-
 +
|OBS_REPEAT_FR
 +
|VARCHAR2(30 CHAR)
 +
|
 +
|Yes
 +
|NULL
 +
|Observation répétée
 +
|This indicates if the observation has occurred before on a previous inspection with the company in French.
 +
|
 +
|-
 +
|OBS_STATUS_EN
 +
|VARCHAR2(50 CHAR)
 +
|
 +
|Yes
 +
|NULL
 +
|Open
 +
|This indicates the status of the observation in English.
 +
|-
 +
|OBS_STATUS_FR
 +
|VARCHAR2(50 CHAR)
 +
|
 +
|Yes
 +
|NULL
 +
|Ouvert
 +
|This indicates the status of the observation in French.
 +
|
 +
|-
 +
|OBS_INCLUDEREPORT_EN
 +
|VARCHAR2(3 CHAR)
 +
|
 +
|Yes
 +
|NULL
 +
|Yes
 +
|This indicates whether the observation is included on the inspection report in English.
 +
|
 +
|-
 +
|OBS_INCLUDEREPORT_FR
 +
|VARCHAR2(3 CHAR)
 +
|
 +
|Yes
 +
|NULL
 +
|Oui
 +
|This indicates whether the observation is included on the inspection report in French.
 +
|
 +
|-
 +
|OBS_SEQ_NUM
 +
|NUMBER(3,0)
 +
|
 +
|Yes
 +
|NULL
 +
|1
 +
|This indicates the order that the observations will print on the inspection report.
 +
|
 +
|-
 +
|OBS_STDLINE_EN
 +
|VARCHAR2(1000 CHAR)
 +
|
 +
|Yes
 +
|NULL
 +
|A written procedure was not provided.
 +
|This is a standard line related to the section of the regulation cited in English.
 +
|
 +
|-
 +
|OBS_STDLINE_FR
 +
|VARCHAR2(1000 CHAR)
 +
|
 +
|Yes
 +
|NULL
 +
|Une procédure écrite n'avait pas été fournie
 +
|This is a standard line related to the section of the regulation cited in French.
 +
|
 +
|-
 +
|OBS_NARRATIVE
 +
|VARCHAR2(4000 CHAR)
 +
|
 +
|Yes
 +
|NULL
 +
|/*A written procedure was not provided. */
 +
 
 +
 
 +
The company did not have a written procedure in place for complaint handling.
 +
|This is the inspector's narrative related to the observation.
 +
|
 +
|-
 +
|OBS_SUMMARY
 +
|VARCHAR2(2000 CHAR)
 +
|
 +
|Yes
 +
|NULL
 +
|Observation #: 01
 +
 
 +
Status / Statut : Open / Ouvert
 +
 
 +
Risk / Risque :  1
 +
 
 +
MDR - Establishment licence  / RIM - Licence d’établissement
 +
 
 +
MDR s.45 (f) Device classes / MDR s.45 (f) Classes des instruments
 +
 
 +
 
 +
/*A written procedure was not provided. */
 +
 
 +
 
 +
The company did not have a written procedure in place for complaint handling.
 +
|This is the overall summary of the observation that built from the above columns.
 +
|
 +
|-
 +
|CREATEDBYNAME
 +
|VARCHAR2(100 CHAR)
 +
|
 +
|Yes
 +
|NULL
 +
|John Smith
 +
|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
 +
|John Smith
 +
|This identifies the resource that last modified the record.
 +
|
 +
|-
 +
|MODIFIEDON
 +
|DATE
 +
|
 +
|Yes
 +
|NULL
 +
|22-08-26
 +
|This identifies the date that record was last modified.
 +
|
 +
|}
 +
 
 +
===MD_INS_INS_CASE_WORKFLOW===
 +
[[MDCCD Data Warehouse|Return to Home Page.]]
 +
 
 +
This table holds information about the workflow of the inspection.
 +
{| class="wikitable sortable mw-collapsible"
 +
|+
 +
! scope="col" style="width: 350px;" | Column Name
 +
! scope="col" style="width: 100px;" |DataType
 +
! scope="col" style="width: 50px;" | Key Type
 +
! scope="col" style="width: 50px;" |Nullable
 +
! scope="col" style="width: 50px;" |Data Default
 +
! scope="col" style="width: 500px;" |Example Data
 +
! scope="col" style="width: 350px;" |Description
 +
! scope="col" style="width: 350px;" |Comments
 +
|-
 +
|WF_ID
 +
|VARCHAR2(36 CHAR)
 +
|Primary
 +
|No
 +
|NULL
 +
|5c6afa3c-3f8a-4641-8da4-9588ec7769d7
 +
|This is the unique record identifier for the workflow.
 +
|
 +
|-
 +
|WF_NAME
 +
|VARCHAR2(50 BYTE)
 +
|
 +
|Yes
 +
|NULL
 +
|Inspection Business Process
 +
|This is the name of the workflow.
 +
|
 +
|-
 +
|INSP_ID
 +
|VARCHAR2(36 CHAR)
 +
|Foreign
 +
|No
 +
|NULL
 +
|5c6afa3c-3f8a-4641-8da4-9588ec7769d7
 +
|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
 +
|00070-MDCCD HEALTHCARE INC
 +
|This is the parent inspection number.
 +
|
 +
|-
 +
|ACTIVESTAGEID
 +
|VARCHAR2(36 CHAR)
 +
|
 +
|No
 +
|NULL
 +
|5c6afa3c-3f8a-4641-8da4-9588ec7769d7
 +
|This is the unique record identifier for the current stage of the workflow.
 +
|
 +
|-
 +
|STAGE_NAME_EN
 +
|VARCHAR2(50 BYTE)
 +
|
 +
|Yes
 +
|NULL
 +
|Start Of Inspection
 +
|This is the name of the current workflow stage in English.
 +
|
 +
|-
 +
|STAGE_NAME_FR
 +
|VARCHAR2(50 BYTE)
 +
|
 +
|Yes
 +
|NULL
 +
|Début de l'inspection
 +
|This is the name of the current workflow stage in French.
 +
|
 +
|-
 +
|STAGE_START_DATE
 +
|DATE
 +
|
 +
|Yes
 +
|NULL
 +
|23-11-09
 +
|This is the date that the current workflow stage was started.
 +
|
 +
|-
 +
|WF_COMPLETE_DATE
 +
|DATE
 +
|
 +
|Yes
 +
|NULL
 +
|23-11-09
 +
|This is the date the workflow was completed.
 +
|
 +
|-
 +
|WF_DURATION
 +
|NUMBER(4,0)
 +
|
 +
|Yes
 +
|NULL
 +
|42
 +
|This identifies how many days the workflow was active.
 +
|
 +
|-
 +
|CREATEDBYNAME
 +
|VARCHAR2(100 CHAR)
 +
|
 +
|Yes
 +
|NULL
 +
|John Smith
 +
|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
 +
|John Smith
 +
|This identifies the resource that last modified the record.
 +
|
 +
|-
 +
|MODIFIEDON
 +
|DATE
 +
|
 +
|Yes
 +
|NULL
 +
|22-08-26
 +
|This identifies the date that record was last modified.
 +
|
 +
|}
 +
 
 +
 
 +
===MD_INS_INS_IID===
 +
[[MDCCD Data Warehouse|Return to Home Page.]]
 +
 
 +
This table holds the Initial Inspection Deficiency information for an inspection that is posted on the DHPID.
 +
{| class="wikitable sortable mw-collapsible"
 +
|+
 +
! scope="col" style="width: 350px;" | Column Name
 +
! scope="col" style="width: 100px;" |DataType
 +
! scope="col" style="width: 50px;" | Key Type
 +
! scope="col" style="width: 50px;" |Nullable
 +
! scope="col" style="width: 50px;" |Data Default
 +
! scope="col" style="width: 500px;" |Example Data
 +
! scope="col" style="width: 350px;" |Description
 +
! scope="col" style="width: 350px;" |Comments
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|}
 +
 
 +
===MD_INS_INS_INSPECTORLIST===
 +
[[MDCCD Data Warehouse|Return to Home Page.]]
 +
 
 +
This table holds a list of inspectors within the system.
 +
{| class="wikitable sortable mw-collapsible"
 +
|+
 +
! scope="col" style="width: 350px;" | Column Name
 +
! scope="col" style="width: 100px;" |DataType
 +
! scope="col" style="width: 50px;" | Key Type
 +
! scope="col" style="width: 50px;" |Nullable
 +
! scope="col" style="width: 50px;" |Data Default
 +
! scope="col" style="width: 500px;" |Example Data
 +
! scope="col" style="width: 350px;" |Description
 +
! scope="col" style="width: 350px;" |Comments
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|}
 +
 
 +
===MD_INS_INS_IRC_ENFORCEMENT===
 +
[[MDCCD Data Warehouse|Return to Home Page.]]
 +
 
 +
This table holds the list of enforcement actions taken by Health Canada for the Inspection Report Card posted on the DHPID.
 +
{| class="wikitable sortable mw-collapsible"
 +
|+
 +
! scope="col" style="width: 350px;" | Column Name
 +
! scope="col" style="width: 100px;" |DataType
 +
! scope="col" style="width: 50px;" | Key Type
 +
! scope="col" style="width: 50px;" |Nullable
 +
! scope="col" style="width: 50px;" |Data Default
 +
! scope="col" style="width: 500px;" |Example Data
 +
! scope="col" style="width: 350px;" |Description
 +
! scope="col" style="width: 350px;" |Comments
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|}
 +
 
 +
===MD_INS_INS_IRC_MEASURES===
 +
[[MDCCD Data Warehouse|Return to Home Page.]]
 +
 
 +
This table holds the list of corrective measures taken by Health Canada for the Inspection Report Card posted on the DHPID.
 +
{| class="wikitable sortable mw-collapsible"
 +
|+
 +
! scope="col" style="width: 350px;" | Column Name
 +
! scope="col" style="width: 100px;" |DataType
 +
! scope="col" style="width: 50px;" | Key Type
 +
! scope="col" style="width: 50px;" |Nullable
 +
! scope="col" style="width: 50px;" |Data Default
 +
! scope="col" style="width: 500px;" |Example Data
 +
! scope="col" style="width: 350px;" |Description
 +
! scope="col" style="width: 350px;" |Comments
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|}
 +
 
 +
===MD_INS_INS_IRC_OBSERVATIONS===
 +
[[MDCCD Data Warehouse|Return to Home Page.]]
 +
 
 +
This table holds the list of observations for the Inspection Report Card posted on the DHPID.
 +
{| class="wikitable sortable mw-collapsible"
 +
|+
 +
! scope="col" style="width: 350px;" | Column Name
 +
! scope="col" style="width: 100px;" |DataType
 +
! scope="col" style="width: 50px;" | Key Type
 +
! scope="col" style="width: 50px;" |Nullable
 +
! scope="col" style="width: 50px;" |Data Default
 +
! scope="col" style="width: 500px;" |Example Data
 +
! scope="col" style="width: 350px;" |Description
 +
! scope="col" style="width: 350px;" |Comments
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|}
 +
 
 +
===MD_INS_INS_SITE===
 +
[[MDCCD Data Warehouse|Return to Home Page.]]
 +
 
 +
This table holds the site/company dictionary. This information is migrated from MDS.
 +
{| class="wikitable sortable mw-collapsible"
 +
|+
 +
! scope="col" style="width: 350px;" | Column Name
 +
! scope="col" style="width: 100px;" |DataType
 +
! scope="col" style="width: 50px;" | Key Type
 +
! scope="col" style="width: 50px;" |Nullable
 +
! scope="col" style="width: 50px;" |Data Default
 +
! scope="col" style="width: 500px;" |Example Data
 +
! scope="col" style="width: 350px;" |Description
 +
! scope="col" style="width: 350px;" |Comments
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|}
 +
 
 +
===MD_INS_INS_SITE_CONTACT===
 +
[[MDCCD Data Warehouse|Return to Home Page.]]
 +
 
 +
This table holds the site/company contact listing. This information is partially migrated from MDS.
 +
{| class="wikitable sortable mw-collapsible"
 +
|+
 +
! scope="col" style="width: 350px;" |Column Name
 +
! scope="col" style="width: 100px;" |DataType
 +
! scope="col" style="width: 50px;" |Key Type
 +
! scope="col" style="width: 50px;" |Nullable
 +
! scope="col" style="width: 50px;" |Data Default
 +
! scope="col" style="width: 500px;" |Example Data
 +
! scope="col" style="width: 350px;" |Description
 +
! scope="col" style="width: 350px;" |Comments
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|}
 +
 
 +
===MD_INS_INS_LICENCE===
 +
[[MDCCD Data Warehouse|Return to Home Page.]]
 +
 
 +
This table holds the establishment licence dictionary. This information is migrated from MDS.
 +
{| class="wikitable sortable mw-collapsible"
 +
|+
 +
! scope="col" style="width: 350px;" | Column Name
 +
! scope="col" style="width: 100px;" |DataType
 +
! scope="col" style="width: 50px;" | Key Type
 +
! scope="col" style="width: 50px;" |Nullable
 +
! scope="col" style="width: 50px;" |Data Default
 +
! scope="col" style="width: 500px;" |Example Data
 +
! scope="col" style="width: 350px;" | Description
 +
! scope="col" style="width: 350px;" |Comments
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|}
 +
 
 +
===MD_INS_INS_SITELICENCEACTIVITY===
 +
[[MDCCD Data Warehouse|Return to Home Page.]]
 +
 
 +
This table holds the establishment licence activity dictionary. This information is migrated from MDS.
 +
{| class="wikitable sortable mw-collapsible"
 +
|+
 +
! scope="col" style="width: 350px;" | Column Name
 +
! scope="col" style="width: 100px;" |DataType
 +
! scope="col" style="width: 50px;" | Key Type
 +
! scope="col" style="width: 50px;" |Nullable
 +
! scope="col" style="width: 50px;" |Data Default
 +
! scope="col" style="width: 500px;" |Example Data
 +
! scope="col" style="width: 350px;" |Description
 +
! scope="col" style="width: 350px;" |Comments
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|-
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|
 +
|}

Latest revision as of 16:00, 24 May 2024

Overview

This page provides a data dictionary for each table that is held within the MDCCD Data Warehouse.

DISCLAIMER: All data in the "Example Data" column is fictitious and does not represent parties authorized under the Food and Drugs Act and/or the Medical Device Regulations.

Medical Devices

MD_INS_CASE

Return to Home Page.

This table holds the main inspection information.

Column Name DataType Key Type Nullable Data Default Example Data Description Comments
INSP_ID Primary VARCHAR2(36 CHAR) No NULL 5c6afa3c-3f8a-4641-8da4-9588ec7769d7 This is the unique record identifier for the inspection.
INSP_NUM Foreign VARCHAR2(200 CHAR) No NULL 00070-MDCCD HEALTHCARE INC This is the inspection number.
SITE_ID Foreign VARCHAR2(36 CHAR) No NULL 5c6afa3c-3f8a-4641-8da4-9588ec7769d7 This is the unique record identifier for the company inspected.
SITE_NAME VARCHAR2(160 CHAR) Yes NULL MDCCD HEALTHCARE INC This is the name of the company insepcted.
LICENCE_NO Foreign NUMBER(6,0) Yes NULL 1234 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 123456 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 HC1-11-11IM This is the internal program based file identifier.
REGION_CD_EN VARCHAR2(50 CHAR) Yes NULL East This is the operational centre assigned to the inspection in English.
REGION_CD_FR VARCHAR2(50 CHAR) Yes NULL Est This is the operational centre assigned to the inspection in French.
INSP_TYPE_EN VARCHAR2(100 CHAR) Yes NULL Domestic - New - Onsite This is the type of inspection in English.
INSP_TYPE_FR VARCHAR2(100 CHAR) Yes NULL Domestique - Nouvelle - Sur place This is the type of inspection in French.
LEAD_INSPECTOR_ID Foreign VARCHAR2(36 CHAR) Yes NULL 0bb101ab-5009-ee11-8f6e-000d3ae86452 This is the unique record identifier of the lead inspector.
LEAD_INSPECTOR VARCHAR2(200 CHAR) Yes NULL John Smith This is the name of the lead inspector.
LEAD_INSPECTOR_BADGE VARCHAR2(50 BYTE) Yes NULL 9988776 This is the badge number of the lead inspector.
SUPERVISOR_ID Foreign VARCHAR2(36 CHAR) Yes NULL 5c6afa3c-3f8a-4641-8da4-9588ec7769d7 This is the unique record identifier of the supervisor for the inspection.
SUPERVISOR VARCHAR2(200 CHAR) Yes NULL John Smith This is the name of the supervisor for the inspection.
INSP_STATUS_EN VARCHAR2(100 CHAR) Yes NULL Open This is the status of the inspection in English.
INSP_STATUS_FR VARCHAR2(100 CHAR) Yes NULL Ouvert This is the status of the inspection in French.
COMP_STANDARD_EN VARCHAR2(100 CHAR) Yes NULL Food & Drugs Act, Medical Device Regulations 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 Loi sur les aliments et drogues, Règlement sur les instruments médicaux 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 Compliant This is the draft inspection rating in English.
DRAFT_REPORT_RATING_FR VARCHAR2(100 CHAR) Yes NULL Conforme This is the draft inspection rating in French.
DRAFT_REPORT_ISSUE_DATE DATE Yes NULL 24-03-21 This is the date that the draft inspection rating was issued.
FINAL_REPORT_RATING_EN VARCHAR2(100 CHAR) Yes NULL Compliant This is the final inspection rating in English.
FINAL_REPORT_RATING_FR VARCHAR2(100 CHAR) Yes NULL Conforme This is the final inspection rating in French.
FINAL_REPORT_ISSUE_DATE DATE Yes NULL 24-03-21 This is the date that the final inspection rating was issued.
AMENDED_REPORT_ISSUE_DATE DATE Yes NULL 24-03-22 This is the date that an amended inspection report was issued to the company.
SAMPLES_TAKEN VARCHAR2(2000 CHAR) Yes NULL None This indicates if any samples were taken during the inspection.
INSP_START_DATE DATE Yes NULL 24-01-30 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 24-01-30 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 23-11-15 This indicates when the inspector had the closing meeting with the company.
NEXT_INSPECTION_DATE DATE Yes NULL 24-03-15 This indicates when the company is due for its next inspection
AMENDED_CD_EN VARCHAR2(5 CHAR) Yes NULL Yes This indicates whether the inspection report has been amended in English.
AMENDED_CD_FR VARCHAR2(5 CHAR) Yes NULL Oui This indicates whether the inspection report has been amended in French.
AMENDED_BY VARCHAR2(200 CHAR) Yes NULL John Smith This indicates who amended the inspection.
AMENDED_DATE DATE Yes NULL 24-03-01 This indicates when the report was amended.
FIRST_COMM_DATE DATE Yes NULL 24-02-21 This is the date the inspector first communicated with the company for the inspection.
FIRST_COMM_MODE VARCHAR2(100 CHAR) Yes NULL E-mail 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 John Smith This is the name of the emergency contact for the company at the time of inspection.
EMERGENCY_CONTACT_PHONE VARCHAR2(100 CHAR) Yes NULL 613-123-4567 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 5c6afa3c-3f8a-4641-8da4-9588ec7769d7 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 John Smith This is the name of the senior official for the company at the time of inspection.
SENIOR_OFFICIAL_TITLE VARCHAR2(100 CHAR) Yes NULL General Manager 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 5c6afa3c-3f8a-4641-8da4-9588ec7769d7 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 John Smith This is the name of the contact of the company at the time of inspection.
SITE_CONTACT_TITLE VARCHAR2(100 CHAR) Yes NULL Manger, Quality Assurance This is the job title of the contact of the company at the time of inspection.
SITE_CONTACT_PHONE VARCHAR2(100 CHAR) Yes NULL 613-123-4567 This is the phone number of the contact of the company at the time of inspection.
SITE_CONTACT_FAX VARCHAR2(100 CHAR) Yes NULL 613-123-4567 This is the fax of the contact of the company at the time of inspection.
SITE_CONTACT_EMAIL VARCHAR2(100 CHAR) Yes NULL john@mdccdhealthcare.com This is the e-mail of the contact of the company at the time of inspection.
SITE_ADDRESS_1 VARCHAR2(250 CHAR) Yes NULL 123 Main St This is the address of the company being inspected.
SITE_ADDRESS_2 VARCHAR2(250 CHAR) Yes NULL Suite 101 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 OTTAWA This is the city of the company being inspected.
SITE_PROVINCE VARCHAR2(50 CHAR) Yes NULL ON This is the province of the company being inspected.
SITE_POSTALCODE VARCHAR2(20 CHAR) Yes NULL A1B 2C3 This is the postal code of the company being inspected.
SITE_COUNTRY VARCHAR2(80 CHAR) Yes NULL CANADA This is the country of the company being inspected.
INSPECTION_SUMMARY CLOB Yes NULL The meeting occured on March 1, 1994 with John Smith (Manager at MDCCD Healthcare and Mohamed Ali (Inspector at Health Canada) This is the inspectors summary of the inspection.
REPORT_DELIVERED_TO_ID Foreign VARCHAR2(36 CHAR) Yes NULL 5c6afa3c-3f8a-4641-8da4-9588ec7769d7 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 Mohamed Ali This is the name of the contact of the company who received the final inspection report.
RESPONSE_EXPECTED_DATE DATE Yes NULL 24-02-28 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 20230406 Inspector spoke with company to schedule inspection. Follow-up email sent to company. This is the inspector's internal comments and recommendations after the completion of the inspection.
PERSONNEL_CHANGES VARCHAR2(2000 BYTE) Yes NULL Change in Regulatory Affairs department. Mr. Smith now in charge of complaint handling. This indicates any personnel changes at the company inspected since their previous inspection.
IID_STATUS_EN VARCHAR2(50 CHAR) Yes NULL In Progress This indicates the status of the IID in English. DHPID Transparency IID
IID_STATUS_FR VARCHAR2(50 CHAR) Yes NULL En cours This indicates the status of the IID in French. DHPID Transparency IID
IID_STATUS_DATE DATE Yes NULL 24-03-15 This indicates the status date of the IID. DHPID Transparency IID
IID_REVISION_LOG VARCHAR2(2000 CHAR) Yes NULL Updated deficiency. This indicates any revisions carried out to the IID. DHPID Transparency IID
IRC_STATUS_EN VARCHAR2(50 CHAR) Yes NULL IRC Ready For Publication This indicates the status of the IRC in English. DHPID Transparency IRC
IRC_STATUS_FR VARCHAR2(50 CHAR) Yes NULL IRC prêt pour la publication This indicates the status of the IRC in French. DHPID Transparency IRC
INSP_RATING_EN VARCHAR2(100 CHAR) Yes NULL Compliant 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 Conforme 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 The inspection resulted in a ‘compliant’ inspection rating. A compliant inspection rating allows a company to keep their licence and continue conducting business.  A compliant rating does not mean that there were no observations during the inspection.  If a company receives a compliant inspection rating and if observations were noted, the company is required to develop a plan to correct the observations to prevent them from occurring again. This is a standard line related to the INSP_RATING_EN value. DHPID Transparency IRC
INSP_OUTCOME_FR VARCHAR2(2000 CHAR) Yes NULL Une cote « conforme » a été émise aux termes de l’inspection. \r\n\r\nUne cote d’inspection « conforme » permet à une entreprise de conserver sa licence et de poursuivre ses activités. Une cote « conforme » ne signifie toutefois pas qu’aucune observation n’a été relevée pendant l’inspection. Si une entreprise reçoit une cote d’inspection « conforme » et si des observations ont été relevées, elle doit élaborer un plan pour corriger ces observations et éviter qu’elles ne se reproduisent This is a standard line related to the INSP_RATING_FR value. DHPID Transparency IRC
INSP_REPORT_WAITING VARCHAR2(200 CHAR) Yes NULL John Smith This is the inspector's name who sent the inspection report for approval in the record log.
INSP_REPORT_WAITING_DATE DATE Yes NULL 23-11-27 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 John Smith This is the supervisor's name who approved the inspection report in the record log.
INSP_REPORT_APPROVED_BY_DATE DATE Yes NULL 23-11-27 This is the date the supervisor approved the inspection report in the record log.
INSP_CLOSED_WAITING VARCHAR2(200 CHAR) Yes NULL John Smith This is the inspector's name who sent the inspection for closure in the record log.
INSP_CLOSED_WAITING_DATE DATE Yes NULL 23-11-29 This is the date the inspector sent the inspection report for closure in the record log.
INSP_CLOSED_APPROVED VARCHAR2(200 CHAR) Yes NULL John Smith This is the supervisor's name who approved the inspection for closure in the record log.
INSP_CLOSED_APPROVED_DATE DATE Yes NULL 23-11-29 This is the date the supervisor approved the inspection for closure in the record log.
CREATEDBYNAME VARCHAR2(100 CHAR) Yes NULL John Smith This identifies the resource that created the record.
CREATEDON DATE Yes NULL 23-08-14 This identifies the date that record was created.
MODIFIEDBYNAME VARCHAR2(100 CHAR) Yes NULL John Smith This identifies the resource that last modified the record.
MODIFIEDON DATE Yes NULL 23-12-25 This identifies the date that record was last modified.

MD_INS_CASE_CAPA

Return to Home Page.

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

Column Name DataType Key Type Nullable Data Default Example Data Description Comments
CAPA_ID VARCHAR2(36 CHAR) Primary No NULL 5c6afa3c-3f8a-4641-8da4-9588ec7769d7 This is the unique record identifier for the CAPA.
INSP_ID VARCHAR2(36 CHAR) Foreign No NULL 5c6afa3c-3f8a-4641-8da4-9588ec7769d7 This is the unique record identifier for the parent inspection. Use to join with MD_INS_CASE.INSP_ID.
INSP_NUM VARCHAR2(200 CHAR) Foreign No NULL 00070-MDCCD HEALTHCARE INC This is the parent inspection number.
OBS_ID VARCHAR2(36 CHAR) Foreign No NULL 5c6afa3c-3f8a-4641-8da4-9588ec7769d7 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 manufacturer sent an email to the inspector to indicate that the program has been solved. This is the CAPA as inputted by the inspector.
CREATEDBYNAME VARCHAR2(100 CHAR) Yes NULL John Smith 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 John Smith This identifies the resource that last modified the record.
MODIFIEDON DATE Yes NULL 22-08-26 This identifies the date that record was last modified.

MD_INS_CASE_DESIGNATION

Return to Home Page.

This table holds information that categorizes or "tags" an inspection. These are flexible codes used to capture information if a designated field does not exist.

Column Name DataType Key Type Nullable Data Default Example Data Description Comments
INSP_DESIGNATION_ID VARCHAR2(36 CHAR) Primary No NULL 5c6afa3c-3f8a-4641-8da4-9588ec7769d7 This is the unique record identifier for the designation.
INSP_ID VARCHAR2(36 CHAR) Foreign No NULL 5c6afa3c-3f8a-4641-8da4-9588ec7769d7 This is the unique record identifier for the parent inspection. Use to join with MD_INS_CASE.INSP_ID.
INSP_NUM VARCHAR2(200 CHAR) Foreign No NULL 00070-MDCCD HEALTHCARE INC This is the parent inspection number.
DESIGNATION_ID VARCHAR2(36 CHAR) Foreign No NULL 5c6afa3c-3f8a-4641-8da4-9588ec7769d7 This is the unique record identifier for the designation code.
DESIGNATION_CODE VARCHAR2(100 CHAR) Yes NULL 5006 This is the designation code.
DESIGNATION_NAME VARCHAR2(250 CHAR) Yes NULL Covid This is the name of the designation code.
DESIGNATION_DECODE_EN VARCHAR2(100 CHAR) Yes NULL Covid This is the name of the designation code in English.
DESIGNATION_DECODE_FR VARCHAR2(100 CHAR) Yes NULL Covid This is the name of the designation code in French.
CREATEDBYNAME VARCHAR2(100 CHAR) Yes NULL John Smith 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 John Smith This identifies the resource that last modified the record.
MODIFIEDON DATE Yes NULL 22-08-26 This identifies the date that record was last modified.

MD_INS_CASE_ESTACTIVITY

Return to Home Page.

This table holds information on the activities conducted by the company at the time of the inspection as recorded by the inspector.

Column Name DataType Key Type Nullable Data Default Example Data Description Comments
INSP_ACTIVITY_ID VARCHAR2(36 CHAR) Primary No NULL 5c6afa3c-3f8a-4641-8da4-9588ec7769d7 This is the unique record identifier for the establishment activity.
INSP_ID VARCHAR2(36 CHAR) Foreign No NULL 5c6afa3c-3f8a-4641-8da4-9588ec7769d7 This is the unique record identifier for the parent inspection. Use to join with MD_INS_CASE.INSP_ID.
INSP_NUM VARCHAR2(200 CHAR) Foreign No NULL 00070-MDCCD HEALTHCARE INC This is the parent inspection number.
ACTIVITY_ID VARCHAR2(36 CHAR) Foreign No NULL 5c6afa3c-3f8a-4641-8da4-9588ec7769d7 This is the unique record identifier for the activity value.
ACTIVITY_NAME VARCHAR2(205 CHAR) Yes NULL Importateur - Classe 2 This is the establishment activity.
ACTIVITY_DECODE_EN VARCHAR2(100 CHAR) Yes NULL Importer - Class 2 This is the name of the establishment activity in English.
ACTIVITY_DECODE_FR VARCHAR2(100 CHAR) Yes NULL Importateur - Classe 2 This is the name of the establishment activity in French.
CREATEDBYNAME VARCHAR2(100 CHAR) Yes NULL John Smith 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 John Smith This identifies the resource that last modified the record.
MODIFIEDON DATE Yes NULL 22-08-26 This identifies the date that record was last modified.

MD_INS_CASE_INSPECTORS

Return to Home Page.

This table holds a listing of inspectors assigned to an inspection.

Column Name DataType Key Type Nullable Data Default Example Data Description Comments
BK_ID VARCHAR2(36 CHAR) Primary No NULL 5c6afa3c-3f8a-4641-8da4-9588ec7769d7 This is the unique record identifier for the inspector listing.
INSP_ID VARCHAR2(36 CHAR) Foreign No NULL 5c6afa3c-3f8a-4641-8da4-9588ec7769d7 This is the unique record identifier for the parent inspection. Use to join with MD_INS_CASE.INSP_ID.
INSP_NUM VARCHAR2(200 CHAR) Foreign No NULL 00070-MDCCD HEALTHCARE INC This is the parent inspection number.
INSPECTOR_NAME VARCHAR2(100 CHAR) Yes NULL John Smith This is the name of the inspector.
LEADINSPECTOR_EN VARCHAR2(3 CHAR) Yes NULL Yes This indicates if the inspector is the lead inspector on the inspection in English.
LEADINSPECTOR_FR VARCHAR2(3 CHAR) Yes NULL Oui This indicates if the inspector is the lead inspector on the inspection in French.
ASSIGNSTATUS_EN VARCHAR2(100 CHAR) Yes NULL Scheduled **Not used by program.** Contains default system information.
ASSIGNSTATUS_FR VARCHAR2(100 CHAR) Yes NULL **Not used by program.** Contains default system information.
STARTTIME DATE Yes NULL 23-10-20 **Not used by program.** Contains default system information.
ENDTIME DATE Yes NULL 23-10-20 **Not used by program.** Contains default system information.
CREATEDBYNAME VARCHAR2(100 CHAR) Yes NULL John Smith 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 John Smith This identifies the resource that last modified the record.
MODIFIEDON DATE Yes NULL 22-08-26 This identifies the date that record was last modified.

MD_INS_CASE_NOTES

Return to Home Page.

This table holds notes related to the inspection.

Column Name DataType Key Type Nullable Data Default Example Data Description Comments
ANNOTATION_ID VARCHAR2(36 CHAR) Primary No NULL 5c6afa3c-3f8a-4641-8da4-9588ec7769d7 This is the unique record identifier for the note.
INSP_ID VARCHAR2(36 CHAR) Foreign No NULL 5c6afa3c-3f8a-4641-8da4-9588ec7769d7 This is the unique record identifier for the parent inspection. Use to join with MD_INS_CASE.INSP_ID.
INSP_NUM VARCHAR2(200 CHAR) Foreign No NULL 00070-MDCCD HEALTHCARE INC This is the parent inspection number.
SUBJECT VARCHAR2(500 CHAR) Yes NULL Talked to client and set out inspection plan. This is the subject of the note.
NOTETEXT CLOB Yes NULL Communication with company on 2023-09-01. This is the body of the note.
CREATEDBYNAME VARCHAR2(100 CHAR) Yes NULL John Smith 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 John Smith This identifies the resource that last modified the record.
MODIFIEDON DATE Yes NULL 22-08-26 This identifies the date that record was last modified.

MD_INS_CASE_OBSERVATIONS

Return to Home Page.

This table holds information about the observations for an inspection.

Column Name DataType Key Type Nullable Data Default Example Data Description Comments
OBS_ID VARCHAR2(36 CHAR) Primary No NULL 5c6afa3c-3f8a-4641-8da4-9588ec7769d7 This is the unique record identifier for the observation.
OBS_NUM VARCHAR2(200 CHAR) No NULL 00070-MDCCD HEALTHCARE INC -obs0001132 This is the observation number.
INSP_ID VARCHAR2(36 CHAR) Foreign No NULL 5c6afa3c-3f8a-4641-8da4-9588ec7769d7 This is the unique record identifier for the parent inspection. Use to join with MD_INS_CASE.INSP_ID.
INSP_NUM VARCHAR2(200 CHAR) Foreign No NULL 00070-MDCCD HEALTHCARE INC This is the parent inspection number.
OBS_TYPE_EN VARCHAR2(200 CHAR) Yes NULL MDR - Establishment licence This identifies the overall category of the Medical Device Regulations that the observation is cited under in English.
OBS_TYPE_FR VARCHAR2(200 CHAR) Yes NULL RIM - Licence d’établissement This identifies the overall category of the Medical Device Regulations that the observation is cited under in French.
REG_SECTION_EN VARCHAR2(150 CHAR) Yes NULL s.45 (f) Device classes This identifies the specific section/subsection of the Medical Device Regulations that the observation is cited under in English.
REG_SECTION_FR VARCHAR2(150 CHAR) Yes NULL MDR s.45 (f) Classes des instruments   This identifies the specific section/subsection of the Medical Device Regulations that the observation is cited under in French.
OBS_RISK NUMBER(1,0) Yes NULL 1 This identifies the risk level of the observation. Scale 1-3. Risk 1 is the highest and risk 3 is the lowest.
OBS_REPEAT_EN VARCHAR2(30 CHAR) Yes NULL Repeat Observation This indicates if the observation has occurred before on a previous inspection with the company in English.
OBS_REPEAT_FR VARCHAR2(30 CHAR) Yes NULL Observation répétée This indicates if the observation has occurred before on a previous inspection with the company in French.
OBS_STATUS_EN VARCHAR2(50 CHAR) Yes NULL Open This indicates the status of the observation in English.
OBS_STATUS_FR VARCHAR2(50 CHAR) Yes NULL Ouvert This indicates the status of the observation in French.
OBS_INCLUDEREPORT_EN VARCHAR2(3 CHAR) Yes NULL Yes This indicates whether the observation is included on the inspection report in English.
OBS_INCLUDEREPORT_FR VARCHAR2(3 CHAR) Yes NULL Oui This indicates whether the observation is included on the inspection report in French.
OBS_SEQ_NUM NUMBER(3,0) Yes NULL 1 This indicates the order that the observations will print on the inspection report.
OBS_STDLINE_EN VARCHAR2(1000 CHAR) Yes NULL A written procedure was not provided. This is a standard line related to the section of the regulation cited in English.
OBS_STDLINE_FR VARCHAR2(1000 CHAR) Yes NULL Une procédure écrite n'avait pas été fournie This is a standard line related to the section of the regulation cited in French.
OBS_NARRATIVE VARCHAR2(4000 CHAR) Yes NULL /*A written procedure was not provided. */


The company did not have a written procedure in place for complaint handling.

This is the inspector's narrative related to the observation.
OBS_SUMMARY VARCHAR2(2000 CHAR) Yes NULL Observation #: 01

Status / Statut : Open / Ouvert

Risk / Risque :  1

MDR - Establishment licence  / RIM - Licence d’établissement

MDR s.45 (f) Device classes / MDR s.45 (f) Classes des instruments


/*A written procedure was not provided. */


The company did not have a written procedure in place for complaint handling.

This is the overall summary of the observation that built from the above columns.
CREATEDBYNAME VARCHAR2(100 CHAR) Yes NULL John Smith 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 John Smith This identifies the resource that last modified the record.
MODIFIEDON DATE Yes NULL 22-08-26 This identifies the date that record was last modified.

MD_INS_INS_CASE_WORKFLOW

Return to Home Page.

This table holds information about the workflow of the inspection.

Column Name DataType Key Type Nullable Data Default Example Data Description Comments
WF_ID VARCHAR2(36 CHAR) Primary No NULL 5c6afa3c-3f8a-4641-8da4-9588ec7769d7 This is the unique record identifier for the workflow.
WF_NAME VARCHAR2(50 BYTE) Yes NULL Inspection Business Process This is the name of the workflow.
INSP_ID VARCHAR2(36 CHAR) Foreign No NULL 5c6afa3c-3f8a-4641-8da4-9588ec7769d7 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 00070-MDCCD HEALTHCARE INC This is the parent inspection number.
ACTIVESTAGEID VARCHAR2(36 CHAR) No NULL 5c6afa3c-3f8a-4641-8da4-9588ec7769d7 This is the unique record identifier for the current stage of the workflow.
STAGE_NAME_EN VARCHAR2(50 BYTE) Yes NULL Start Of Inspection This is the name of the current workflow stage in English.
STAGE_NAME_FR VARCHAR2(50 BYTE) Yes NULL Début de l'inspection This is the name of the current workflow stage in French.
STAGE_START_DATE DATE Yes NULL 23-11-09 This is the date that the current workflow stage was started.
WF_COMPLETE_DATE DATE Yes NULL 23-11-09 This is the date the workflow was completed.
WF_DURATION NUMBER(4,0) Yes NULL 42 This identifies how many days the workflow was active.
CREATEDBYNAME VARCHAR2(100 CHAR) Yes NULL John Smith 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 John Smith This identifies the resource that last modified the record.
MODIFIEDON DATE Yes NULL 22-08-26 This identifies the date that record was last modified.


MD_INS_INS_IID

Return to Home Page.

This table holds the Initial Inspection Deficiency information for an inspection that is posted on the DHPID.

Column Name DataType Key Type Nullable Data Default Example Data Description Comments

MD_INS_INS_INSPECTORLIST

Return to Home Page.

This table holds a list of inspectors within the system.

Column Name DataType Key Type Nullable Data Default Example Data Description Comments

MD_INS_INS_IRC_ENFORCEMENT

Return to Home Page.

This table holds the list of enforcement actions taken by Health Canada for the Inspection Report Card posted on the DHPID.

Column Name DataType Key Type Nullable Data Default Example Data Description Comments

MD_INS_INS_IRC_MEASURES

Return to Home Page.

This table holds the list of corrective measures taken by Health Canada for the Inspection Report Card posted on the DHPID.

Column Name DataType Key Type Nullable Data Default Example Data Description Comments

MD_INS_INS_IRC_OBSERVATIONS

Return to Home Page.

This table holds the list of observations for the Inspection Report Card posted on the DHPID.

Column Name DataType Key Type Nullable Data Default Example Data Description Comments

MD_INS_INS_SITE

Return to Home Page.

This table holds the site/company dictionary. This information is migrated from MDS.

Column Name DataType Key Type Nullable Data Default Example Data Description Comments

MD_INS_INS_SITE_CONTACT

Return to Home Page.

This table holds the site/company contact listing. This information is partially migrated from MDS.

Column Name DataType Key Type Nullable Data Default Example Data Description Comments

MD_INS_INS_LICENCE

Return to Home Page.

This table holds the establishment licence dictionary. This information is migrated from MDS.

Column Name DataType Key Type Nullable Data Default Example Data Description Comments

MD_INS_INS_SITELICENCEACTIVITY

Return to Home Page.

This table holds the establishment licence activity dictionary. This information is migrated from MDS.

Column Name DataType Key Type Nullable Data Default Example Data Description Comments
Retrieved from "https://wiki.gccollab.ca/index.php?title=MDCCD_Data_Warehouse_Data_Dictionary&oldid=120684"