71
edits
Changes
Jump to navigation
Jump to search
Line 1:
Line 1: + − == Medical Devices[https://wiki.gccollab.ca/index.php?title=MDCCD_Data_Warehouse_DataDictionary&action=edit Edit source] ==+ − + − + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + − + − + − + − + − + − + + + + Line 21:
Line 2,021: + Line 28:
Line 2,029: + + Line 35:
Line 2,038: + Line 42:
Line 2,046: + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +
no edit summary
{{Delete|reason=Moved to internal Health Canada wiki. Internal database structure should not be public.}}
== 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''.
=== MD_INS_CASE_CAPA ===
== Medical Devices ==
{| class="wikitable"
=== MD_INS_CASE ===
[[MDCCD Data Warehouse|Return to Home Page.]]
This table holds the main inspection information.
{| 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_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===
[[MDCCD Data Warehouse|Return to Home Page.]]
This table holds information about the corrective action plan for an inspection observation.
{| 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
|-
| 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===
[[MDCCD Data Warehouse|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.
{| 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"
|+
|+
!Column Name
! scope="col" style="width: 350px;" | Column Name
!Data Type
! scope="col" style="width: 100px;" |DataType
!Nullable
! scope="col" style="width: 50px;" | Key Type
!Data Default
! scope="col" style="width: 50px;" |Nullable
!Example Data
! scope="col" style="width: 50px;" |Data Default
!Description
! 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
|-
|
|
|
|
|
|
|
|-
|
|
|
|
|
|
|
|-
|
|
|
|
|
|
|
|
|-
|
|
|
|
|
|
|
|-
|
|
|
|
|
|
|
|-
|
|
|
|
|
|
|
|-
|
|
|
|
|
|
|
|-
|
|
|
|
|
|
|
|-
|
|
|
|
|
