Difference between revisions of "MDCCD Data Warehouse Data Dictionary"
Jump to navigation
Jump to search
Jeff.tyndall (talk | contribs) |
Jeff.tyndall (talk | contribs) |
||
| (14 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
| + | {{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''. | ||
== 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 |
| − | ! | + | ! scope="col" style="width: 50px;" |Key Type |
| − | + | ! 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 31: | Line 36: | ||
|No | |No | ||
|NULL | |NULL | ||
| − | |00070- | + | |00070-MDCCD HEALTHCARE INC |
|This is the inspection number. | |This is the inspection number. | ||
| | | | ||
| Line 40: | Line 45: | ||
|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 51: | ||
|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 72: | ||
|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 81: | ||
|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 87: | ||
|REGION_CD_EN | |REGION_CD_EN | ||
| | | | ||
| − | |VARCHAR2(50 CHAR) | + | | VARCHAR2(50 CHAR) |
| − | |Yes | + | | Yes |
|NULL | |NULL | ||
|East | |East | ||
| Line 95: | Line 100: | ||
|NULL | |NULL | ||
|Est | |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 | |Domestic - New - Onsite | ||
|This is the type of inspection in English. | |This is the type of inspection in English. | ||
| Line 112: | Line 117: | ||
|Yes | |Yes | ||
|NULL | |NULL | ||
| − | |Domestique - Nouvelle - Sur place | + | | Domestique - Nouvelle - Sur place |
|This is the type of inspection in French. | |This is the type of inspection in French. | ||
| | | | ||
| Line 130: | Line 135: | ||
|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 141: | ||
|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 153: | ||
|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 159: | ||
|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 175: | Line 180: | ||
|Yes | |Yes | ||
|NULL | |NULL | ||
| − | |Ouvert | + | | Ouvert |
|This is the status of the inspection in French. | |This is the status of the inspection in French. | ||
| | | | ||
| Line 185: | Line 190: | ||
|NULL | |NULL | ||
|Food & Drugs Act, Medical Device Regulations | |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 198: | ||
|Yes | |Yes | ||
|NULL | |NULL | ||
| − | |Loi sur les aliments et drogues, Règlement sur les instruments médicaux | + | | 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 | ||
| Line 219: | Line 224: | ||
|DATE | |DATE | ||
|Yes | |Yes | ||
| − | |NULL | + | | NULL |
|24-03-21 | |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 231: | ||
|FINAL_REPORT_RATING_EN | |FINAL_REPORT_RATING_EN | ||
| | | | ||
| − | |VARCHAR2(100 CHAR) | + | | VARCHAR2(100 CHAR) |
|Yes | |Yes | ||
|NULL | |NULL | ||
| Line 245: | Line 250: | ||
| | | | ||
|DATE | |DATE | ||
| − | |Yes | + | | Yes |
|NULL | |NULL | ||
|24-03-21 | |24-03-21 | ||
| Line 263: | Line 268: | ||
| | | | ||
|VARCHAR2(2000 CHAR) | |VARCHAR2(2000 CHAR) | ||
| − | |Yes | + | | Yes |
| − | |NULL | + | | NULL |
| − | |None | + | | None |
|This indicates if any samples were taken during the inspection. | |This indicates if any samples were taken during the inspection. | ||
| | | | ||
| Line 276: | Line 281: | ||
|24-01-30 | |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 | ||
| Line 289: | Line 294: | ||
|CLOSING_MEETING_DATE | |CLOSING_MEETING_DATE | ||
| | | | ||
| − | |DATE | + | | DATE |
|Yes | |Yes | ||
|NULL | |NULL | ||
| − | |23-11-15 | + | | 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. | ||
| | | | ||
| Line 298: | Line 303: | ||
|NEXT_INSPECTION_DATE | |NEXT_INSPECTION_DATE | ||
| | | | ||
| − | |DATE | + | | DATE |
|Yes | |Yes | ||
| − | |NULL | + | | NULL |
|24-03-15 | |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 | ||
| Line 328: | Line 333: | ||
|Yes | |Yes | ||
|NULL | |NULL | ||
| − | | | + | |John Smith |
|This indicates who amended the inspection. | |This indicates who amended the inspection. | ||
| | | | ||
| Line 334: | Line 339: | ||
|AMENDED_DATE | |AMENDED_DATE | ||
| | | | ||
| − | |DATE | + | | DATE |
|Yes | |Yes | ||
| − | |NULL | + | | NULL |
|24-03-01 | |24-03-01 | ||
|This indicates when the report was amended. | |This indicates when the report was amended. | ||
| Line 379: | Line 384: | ||
|EMERGENCY_CONTACT_NAME | |EMERGENCY_CONTACT_NAME | ||
| | | | ||
| − | |VARCHAR2(100 CHAR) | + | | VARCHAR2(100 CHAR) |
|Yes | |Yes | ||
|NULL | |NULL | ||
| Line 391: | Line 396: | ||
|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. | ||
| | | | ||
| Line 400: | Line 405: | ||
|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. | ||
| | | | ||
| Line 409: | Line 414: | ||
|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. |
| | | | ||
|- | |- | ||
| Line 417: | Line 422: | ||
|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 426: | Line 431: | ||
|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 435: | Line 440: | ||
|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 442: | Line 447: | ||
| | | | ||
|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 452: | Line 457: | ||
|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 459: | Line 464: | ||
| | | | ||
|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 470: | Line 475: | ||
|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 479: | Line 484: | ||
|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 488: | Line 493: | ||
|Yes | |Yes | ||
|NULL | |NULL | ||
| − | |Suite 101 | + | | Suite 101 |
|This is secondary the address of the company being inspected. | |This is secondary the address of the company being inspected. | ||
| | | | ||
| Line 506: | Line 511: | ||
|Yes | |Yes | ||
|NULL | |NULL | ||
| − | | | + | |OTTAWA |
|This is the city of the company being inspected. | |This is the city of the company being inspected. | ||
|- | |- | ||
| Line 522: | Line 527: | ||
|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 539: | Line 544: | ||
| | | | ||
|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 550: | Line 555: | ||
|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 556: | Line 561: | ||
|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 577: | Line 582: | ||
|Yes | |Yes | ||
|NULL | |NULL | ||
| − | |20230406 Inspector spoke with company to schedule inspection. Follow-up email sent to company | + | |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 | + | |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 599: | Line 604: | ||
|DHPID Transparency IID | |DHPID Transparency IID | ||
|- | |- | ||
| − | |IID_STATUS_FR | + | | IID_STATUS_FR |
| | | | ||
|VARCHAR2(50 CHAR) | |VARCHAR2(50 CHAR) | ||
| Line 613: | Line 618: | ||
|Yes | |Yes | ||
|NULL | |NULL | ||
| − | |24-03-15 | + | | 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 623: | Line 628: | ||
|NULL | |NULL | ||
|Updated deficiency. | |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 637: | Line 642: | ||
|IRC_STATUS_FR | |IRC_STATUS_FR | ||
| | | | ||
| − | |VARCHAR2(50 CHAR) | + | | VARCHAR2(50 CHAR) |
|Yes | |Yes | ||
|NULL | |NULL | ||
| Line 687: | Line 692: | ||
|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 693: | Line 698: | ||
|INSP_REPORT_WAITING_DATE | |INSP_REPORT_WAITING_DATE | ||
| | | | ||
| − | |DATE | + | | DATE |
|Yes | |Yes | ||
|NULL | |NULL | ||
| Line 705: | Line 710: | ||
|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 711: | Line 716: | ||
|INSP_REPORT_APPROVED_BY_DATE | |INSP_REPORT_APPROVED_BY_DATE | ||
| | | | ||
| − | |DATE | + | | DATE |
|Yes | |Yes | ||
| − | |NULL | + | | NULL |
|23-11-27 | |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 723: | Line 728: | ||
|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 | ||
| Line 740: | Line 745: | ||
|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 758: | Line 763: | ||
|Yes | |Yes | ||
|NULL | |NULL | ||
| − | | | + | |John Smith |
|This identifies the resource that created the record. | |This identifies the resource that created the record. | ||
| | | | ||
| Line 764: | Line 769: | ||
|CREATEDON | |CREATEDON | ||
| | | | ||
| − | |DATE | + | | DATE |
|Yes | |Yes | ||
|NULL | |NULL | ||
| Line 776: | Line 781: | ||
|Yes | |Yes | ||
|NULL | |NULL | ||
| − | | | + | | John Smith |
| − | |This identifies the resource that last modified the record. | + | | This identifies the resource that last modified the record. |
| | | | ||
|- | |- | ||
| Line 791: | Line 796: | ||
===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 |
| − | ! | + | ! 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 | ||
| − | | | + | |5c6afa3c-3f8a-4641-8da4-9588ec7769d7 |
|This is the unique record identifier for the CAPA. | |This is the unique record identifier for the CAPA. | ||
| | | | ||
| Line 817: | Line 824: | ||
|No | |No | ||
|NULL | |NULL | ||
| − | | | + | |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 826: | Line 833: | ||
|No | |No | ||
|NULL | |NULL | ||
| − | | | + | |00070-MDCCD HEALTHCARE INC |
|This is the parent inspection number. | |This is the parent inspection number. | ||
| | | | ||
| Line 835: | Line 842: | ||
|No | |No | ||
|NULL | |NULL | ||
| − | | | + | |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 843: | Line 850: | ||
| | | | ||
|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 849: | Line 856: | ||
|- | |- | ||
|CAPA_PLAN | |CAPA_PLAN | ||
| − | |VARCHAR2(2000 CHAR) | + | | VARCHAR2(2000 CHAR) |
| | | | ||
|Yes | |Yes | ||
|NULL | |NULL | ||
| − | |The | + | |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 862: | Line 869: | ||
|Yes | |Yes | ||
|NULL | |NULL | ||
| − | | | + | | John Smith |
|This identifies the resource that created the record. | |This identifies the resource that created the record. | ||
| | | | ||
| Line 880: | Line 887: | ||
|Yes | |Yes | ||
|NULL | |NULL | ||
| − | | | + | | John Smith |
| − | |This identifies the resource that last modified the record. | + | | This identifies the resource that last modified the record. |
| | | | ||
|- | |- | ||
| Line 895: | Line 902: | ||
===MD_INS_CASE_DESIGNATION=== | ===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. | 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" | + | {| class="wikitable sortable mw-collapsible" |
|+ | |+ | ||
| − | !Column Name | + | ! scope="col" style="width: 350px;" | Column Name |
| − | ! | + | ! scope="col" style="width: 100px;" |DataType |
| − | !Key Type | + | ! scope="col" style="width: 50px;" |Key Type |
| − | !Nullable | + | ! scope="col" style="width: 50px;" |Nullable |
| − | !Example Data | + | ! scope="col" style="width: 50px;" |Data Default |
| − | !Description | + | ! scope="col" style="width: 500px;" |Example Data |
| − | !Comments | + | ! scope="col" style="width: 350px;" | Description |
| + | ! scope="col" style="width: 350px;" |Comments | ||
|- | |- | ||
|INSP_DESIGNATION_ID | |INSP_DESIGNATION_ID | ||
| Line 910: | Line 920: | ||
|Primary | |Primary | ||
|No | |No | ||
| − | | | + | |NULL |
| + | |5c6afa3c-3f8a-4641-8da4-9588ec7769d7 | ||
|This is the unique record identifier for the designation. | |This is the unique record identifier for the designation. | ||
| | | | ||
| Line 918: | Line 929: | ||
|Foreign | |Foreign | ||
|No | |No | ||
| − | | | + | |NULL |
| + | |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 926: | Line 938: | ||
|Foreign | |Foreign | ||
|No | |No | ||
| − | |00070- | + | |NULL |
| + | | 00070-MDCCD HEALTHCARE INC | ||
|This is the parent inspection number. | |This is the parent inspection number. | ||
| + | | | ||
|- | |- | ||
|DESIGNATION_ID | |DESIGNATION_ID | ||
| Line 933: | Line 947: | ||
|Foreign | |Foreign | ||
|No | |No | ||
| − | | | + | |NULL |
| + | |5c6afa3c-3f8a-4641-8da4-9588ec7769d7 | ||
|This is the unique record identifier for the designation code. | |This is the unique record identifier for the designation code. | ||
| | | | ||
|- | |- | ||
|DESIGNATION_CODE | |DESIGNATION_CODE | ||
| − | |VARCHAR2(100 CHAR) | + | | VARCHAR2(100 CHAR) |
| | | | ||
|Yes | |Yes | ||
| + | |NULL | ||
|5006 | |5006 | ||
|This is the designation code. | |This is the designation code. | ||
| Line 946: | Line 962: | ||
|- | |- | ||
|DESIGNATION_NAME | |DESIGNATION_NAME | ||
| − | |VARCHAR2(250 CHAR) | + | | VARCHAR2(250 CHAR) |
| | | | ||
|Yes | |Yes | ||
| + | |NULL | ||
|Covid | |Covid | ||
|This is the name of the designation code. | |This is the name of the designation code. | ||
| − | |||
|- | |- | ||
|DESIGNATION_DECODE_EN | |DESIGNATION_DECODE_EN | ||
| Line 957: | Line 973: | ||
| | | | ||
|Yes | |Yes | ||
| + | |NULL | ||
|Covid | |Covid | ||
|This is the name of the designation code in English. | |This is the name of the designation code in English. | ||
| Line 962: | Line 979: | ||
|- | |- | ||
|DESIGNATION_DECODE_FR | |DESIGNATION_DECODE_FR | ||
| − | |VARCHAR2(100 CHAR) | + | | VARCHAR2(100 CHAR) |
| | | | ||
|Yes | |Yes | ||
| − | |Covid | + | |NULL |
| + | | Covid | ||
|This is the name of the designation code in French. | |This is the name of the designation code in French. | ||
| | | | ||
| Line 973: | Line 991: | ||
| | | | ||
|Yes | |Yes | ||
| − | | | + | |NULL |
| + | | John Smith | ||
|This identifies the resource that created the record. | |This identifies the resource that created the record. | ||
| | | | ||
| Line 981: | Line 1,000: | ||
| | | | ||
|Yes | |Yes | ||
| + | |NULL | ||
|22-08-26 | |22-08-26 | ||
|This identifies the date that record was created. | |This identifies the date that record was created. | ||
| + | | | ||
|- | |- | ||
|MODIFIEDBYNAME | |MODIFIEDBYNAME | ||
| Line 988: | Line 1,009: | ||
| | | | ||
|Yes | |Yes | ||
| − | | | + | |NULL |
| − | |This identifies the resource that last modified the record. | + | | John Smith |
| + | | This identifies the resource that last modified the record. | ||
| + | | | ||
|- | |- | ||
|MODIFIEDON | |MODIFIEDON | ||
| Line 995: | Line 1,018: | ||
| | | | ||
|Yes | |Yes | ||
| + | |NULL | ||
|22-08-26 | |22-08-26 | ||
|This identifies the date that record was last modified. | |This identifies the date that record was last modified. | ||
| + | | | ||
|} | |} | ||
===MD_INS_CASE_ESTACTIVITY=== | ===MD_INS_CASE_ESTACTIVITY=== | ||
| − | This table holds information | + | [[MDCCD Data Warehouse|Return to Home Page.]] |
| − | {| class="wikitable" | + | |
| + | 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" | ||
|+ | |+ | ||
| − | !Column Name | + | ! scope="col" style="width: 350px;" | Column Name |
| − | ! | + | ! scope="col" style="width: 100px;" |DataType |
| − | ! Key Type | + | ! scope="col" style="width: 50px;" |Key Type |
| − | !Nullable | + | ! scope="col" style="width: 50px;" |Nullable |
| − | !Example Data | + | ! scope="col" style="width: 50px;" |Data Default |
| − | !Description | + | ! scope="col" style="width: 500px;" |Example Data |
| − | !Comments | + | ! scope="col" style="width: 350px;" | Description |
| + | ! scope="col" style="width: 350px;" |Comments | ||
|- | |- | ||
|INSP_ACTIVITY_ID | |INSP_ACTIVITY_ID | ||
| Line 1,015: | Line 1,043: | ||
|Primary | |Primary | ||
|No | |No | ||
| − | | | + | |NULL |
| + | |5c6afa3c-3f8a-4641-8da4-9588ec7769d7 | ||
|This is the unique record identifier for the establishment activity. | |This is the unique record identifier for the establishment activity. | ||
| | | | ||
| Line 1,023: | Line 1,052: | ||
|Foreign | |Foreign | ||
|No | |No | ||
| − | | | + | |NULL |
| + | |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 1,031: | Line 1,061: | ||
|Foreign | |Foreign | ||
|No | |No | ||
| − | | | + | |NULL |
| + | |00070-MDCCD HEALTHCARE INC | ||
|This is the parent inspection number. | |This is the parent inspection number. | ||
| | | | ||
| Line 1,039: | Line 1,070: | ||
|Foreign | |Foreign | ||
|No | |No | ||
| − | | | + | |NULL |
| + | |5c6afa3c-3f8a-4641-8da4-9588ec7769d7 | ||
|This is the unique record identifier for the activity value. | |This is the unique record identifier for the activity value. | ||
| | | | ||
|- | |- | ||
|ACTIVITY_NAME | |ACTIVITY_NAME | ||
| − | |VARCHAR2(205 CHAR) | + | | VARCHAR2(205 CHAR) |
| | | | ||
| Yes | | Yes | ||
| − | | | + | |NULL |
| + | |Importateur - Classe 2 | ||
|This is the establishment activity. | |This is the establishment activity. | ||
| | | | ||
| Line 1,054: | Line 1,087: | ||
|VARCHAR2(100 CHAR) | |VARCHAR2(100 CHAR) | ||
| | | | ||
| − | |Yes | + | | Yes |
| − | | | + | |NULL |
| + | |Importer - Class 2 | ||
|This is the name of the establishment activity in English. | |This is the name of the establishment activity in English. | ||
| | | | ||
| Line 1,063: | Line 1,097: | ||
| | | | ||
|Yes | |Yes | ||
| − | | | + | |NULL |
| + | | Importateur - Classe 2 | ||
|This is the name of the establishment activity in French. | |This is the name of the establishment activity in French. | ||
| | | | ||
| Line 1,071: | Line 1,106: | ||
| | | | ||
|Yes | |Yes | ||
| − | | | + | |NULL |
| + | | John Smith | ||
|This identifies the resource that created the record. | |This identifies the resource that created the record. | ||
| | | | ||
| Line 1,079: | Line 1,115: | ||
| | | | ||
|Yes | |Yes | ||
| − | | | + | |NULL |
| + | |22-08-26 | ||
|This identifies the date that record was created. | |This identifies the date that record was created. | ||
| | | | ||
| Line 1,087: | Line 1,124: | ||
| | | | ||
|Yes | |Yes | ||
| − | | | + | |NULL |
| + | | John Smith | ||
| This identifies the resource that last modified the record. | | This identifies the resource that last modified the record. | ||
| | | | ||
| Line 1,095: | Line 1,133: | ||
| | | | ||
|Yes | |Yes | ||
| − | | | + | |NULL |
| + | |22-08-26 | ||
|This identifies the date that record was last modified. | |This identifies the date that record was last modified. | ||
| | | | ||
| Line 1,101: | Line 1,140: | ||
===MD_INS_CASE_INSPECTORS=== | ===MD_INS_CASE_INSPECTORS=== | ||
| − | This table holds | + | [[MDCCD Data Warehouse|Return to Home Page.]] |
| − | {| class="wikitable" | + | |
| + | This table holds a listing of inspectors assigned to an inspection. | ||
| + | {| class="wikitable sortable mw-collapsible" | ||
|+ | |+ | ||
| − | !Column Name | + | ! scope="col" style="width: 350px;" | Column Name |
| − | ! | + | ! scope="col" style="width: 100px;" |DataType |
| − | !Key Type | + | ! scope="col" style="width: 50px;" |Key Type |
| − | !Nullable | + | ! scope="col" style="width: 50px;" |Nullable |
| − | !Example Data | + | ! scope="col" style="width: 50px;" |Data Default |
| − | !Description | + | ! scope="col" style="width: 500px;" |Example Data |
| − | !Comments | + | ! scope="col" style="width: 350px;" | Description |
| + | ! scope="col" style="width: 350px;" |Comments | ||
|- | |- | ||
|BK_ID | |BK_ID | ||
|VARCHAR2(36 CHAR) | |VARCHAR2(36 CHAR) | ||
| + | |Primary | ||
|No | |No | ||
| − | | | + | |NULL |
| − | | | + | |5c6afa3c-3f8a-4641-8da4-9588ec7769d7 |
|This is the unique record identifier for the inspector listing. | |This is the unique record identifier for the inspector listing. | ||
| | | | ||
| Line 1,122: | Line 1,165: | ||
|INSP_ID | |INSP_ID | ||
|VARCHAR2(36 CHAR) | |VARCHAR2(36 CHAR) | ||
| + | |Foreign | ||
|No | |No | ||
| − | | | + | |NULL |
| − | | | + | |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 1,130: | Line 1,174: | ||
|INSP_NUM | |INSP_NUM | ||
|VARCHAR2(200 CHAR) | |VARCHAR2(200 CHAR) | ||
| + | |Foreign | ||
|No | |No | ||
| − | | | + | |NULL |
| − | | | + | |00070-MDCCD HEALTHCARE INC |
|This is the parent inspection number. | |This is the parent inspection number. | ||
| | | | ||
| Line 1,138: | Line 1,183: | ||
|INSPECTOR_NAME | |INSPECTOR_NAME | ||
|VARCHAR2(100 CHAR) | |VARCHAR2(100 CHAR) | ||
| + | | | ||
|Yes | |Yes | ||
| − | | | + | |NULL |
| − | | | + | |John Smith |
|This is the name of the inspector. | |This is the name of the inspector. | ||
| | | | ||
| Line 1,146: | Line 1,192: | ||
|LEADINSPECTOR_EN | |LEADINSPECTOR_EN | ||
|VARCHAR2(3 CHAR) | |VARCHAR2(3 CHAR) | ||
| − | |||
| − | |||
| | | | ||
| + | | Yes | ||
| + | |NULL | ||
| + | |Yes | ||
|This indicates if the inspector is the lead inspector on the inspection in English. | |This indicates if the inspector is the lead inspector on the inspection in English. | ||
| | | | ||
| Line 1,154: | Line 1,201: | ||
|LEADINSPECTOR_FR | |LEADINSPECTOR_FR | ||
|VARCHAR2(3 CHAR) | |VARCHAR2(3 CHAR) | ||
| − | |||
| − | |||
| | | | ||
| + | | Yes | ||
| + | |NULL | ||
| + | |Oui | ||
|This indicates if the inspector is the lead inspector on the inspection in French. | |This indicates if the inspector is the lead inspector on the inspection in French. | ||
| | | | ||
| Line 1,162: | Line 1,210: | ||
|ASSIGNSTATUS_EN | |ASSIGNSTATUS_EN | ||
|VARCHAR2(100 CHAR) | |VARCHAR2(100 CHAR) | ||
| + | | | ||
|Yes | |Yes | ||
| − | | | + | |NULL |
| − | | | + | |Scheduled |
|**Not used by program.** | |**Not used by program.** | ||
|Contains default system information. | |Contains default system information. | ||
| Line 1,170: | Line 1,219: | ||
|ASSIGNSTATUS_FR | |ASSIGNSTATUS_FR | ||
|VARCHAR2(100 CHAR) | |VARCHAR2(100 CHAR) | ||
| − | |||
| | | | ||
| + | | Yes | ||
| + | |NULL | ||
| | | | ||
|**Not used by program.** | |**Not used by program.** | ||
| Line 1,178: | Line 1,228: | ||
|STARTTIME | |STARTTIME | ||
|DATE | |DATE | ||
| + | | | ||
|Yes | |Yes | ||
| − | | | + | |NULL |
| − | | | + | |23-10-20 |
|**Not used by program.** | |**Not used by program.** | ||
|Contains default system information. | |Contains default system information. | ||
| Line 1,186: | Line 1,237: | ||
|ENDTIME | |ENDTIME | ||
|DATE | |DATE | ||
| + | | | ||
|Yes | |Yes | ||
| − | | | + | |NULL |
| − | | | + | |23-10-20 |
|**Not used by program.** | |**Not used by program.** | ||
|Contains default system information. | |Contains default system information. | ||
| Line 1,194: | Line 1,246: | ||
|CREATEDBYNAME | |CREATEDBYNAME | ||
|VARCHAR2(100 CHAR) | |VARCHAR2(100 CHAR) | ||
| + | | | ||
|Yes | |Yes | ||
| − | | | + | |NULL |
| − | | | + | | John Smith |
|This identifies the resource that created the record. | |This identifies the resource that created the record. | ||
| | | | ||
| Line 1,202: | Line 1,255: | ||
|CREATEDON | |CREATEDON | ||
|DATE | |DATE | ||
| + | | | ||
|Yes | |Yes | ||
| − | | | + | |NULL |
| − | | | + | |22-08-26 |
| − | |This identifies the date that record was created. | + | |This identifies the date that record was created. |
| | | | ||
|- | |- | ||
|MODIFIEDBYNAME | |MODIFIEDBYNAME | ||
|VARCHAR2(100 CHAR) | |VARCHAR2(100 CHAR) | ||
| + | | | ||
|Yes | |Yes | ||
| − | | | + | |NULL |
| − | | | + | | John Smith |
| − | |This identifies the resource that last modified the record. | + | | This identifies the resource that last modified the record. |
| | | | ||
|- | |- | ||
|MODIFIEDON | |MODIFIEDON | ||
|DATE | |DATE | ||
| + | | | ||
|Yes | |Yes | ||
| − | | | + | |NULL |
| − | | | + | |22-08-26 |
|This identifies the date that record was last modified. | |This identifies the date that record was last modified. | ||
| | | | ||
| Line 1,226: | Line 1,282: | ||
===MD_INS_CASE_NOTES=== | ===MD_INS_CASE_NOTES=== | ||
| − | This table holds | + | [[MDCCD Data Warehouse|Return to Home Page.]] |
| − | {| class="wikitable" | + | |
| + | This table holds notes related to the inspection. | ||
| + | {| class="wikitable sortable mw-collapsible" | ||
|+ | |+ | ||
| − | !Column Name | + | ! scope="col" style="width: 350px;" | Column Name |
| − | ! | + | ! scope="col" style="width: 100px;" |DataType |
| − | !Key Type | + | ! scope="col" style="width: 50px;" |Key Type |
| − | !Nullable | + | ! scope="col" style="width: 50px;" |Nullable |
| − | !Example Data | + | ! scope="col" style="width: 50px;" |Data Default |
| − | !Description | + | ! scope="col" style="width: 500px;" |Example Data |
| − | !Comments | + | ! 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=== | ===MD_INS_CASE_OBSERVATIONS=== | ||
| − | This table holds information | + | [[MDCCD Data Warehouse|Return to Home Page.]] |
| − | {| class="wikitable" | + | |
| + | This table holds information about the observations for an inspection. | ||
| + | {| class="wikitable sortable mw-collapsible" | ||
|+ | |+ | ||
| − | !Column Name | + | ! scope="col" style="width: 350px;" | Column Name |
| − | ! | + | ! scope="col" style="width: 100px;" |DataType |
| − | !Key Type | + | ! scope="col" style="width: 50px;" |Key Type |
| − | !Nullable | + | ! scope="col" style="width: 50px;" |Nullable |
| − | !Example Data | + | ! scope="col" style="width: 50px;" |Data Default |
| − | !Description | + | ! scope="col" style="width: 500px;" |Example Data |
| − | ! Comments | + | ! 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. | |
| − | This | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
|- | |- | ||
| + | |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 | ||
| + | |- | ||
| + | | | ||
| + | | | ||
| + | | | ||
| + | | | ||
| + | | | ||
| + | | | ||
| + | | | ||
| + | |- | ||
| + | | | ||
| + | | | ||
| + | | | ||
| + | | | ||
| + | | | ||
| + | | | ||
| | | | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
|- | |- | ||
| | | | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| | | | ||
| | | | ||
| Line 1,371: | Line 2,805: | ||
| | | | ||
| | | | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
|- | |- | ||
| | | | ||
| Line 1,408: | Line 2,829: | ||
| | | | ||
| | | | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
|- | |- | ||
| | | | ||
| Line 1,445: | Line 2,853: | ||
| | | | ||
| | | | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
|- | |- | ||
| | | | ||
| Line 1,482: | Line 2,877: | ||
| | | | ||
| | | | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
|- | |- | ||
| | | | ||
| Line 1,518: | Line 2,900: | ||
| | | | ||
| | | | ||
| − | | | + | |} |
| − | | | + | |
| + | ===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. | |
| − | This table holds information | + | {| class="wikitable sortable mw-collapsible" |
| − | {| class="wikitable" | ||
|+ | |+ | ||
| − | !Column Name | + | ! scope="col" style="width: 350px;" | Column Name |
| − | ! | + | ! scope="col" style="width: 100px;" |DataType |
| − | !Key Type | + | ! scope="col" style="width: 50px;" | Key Type |
| − | !Nullable | + | ! scope="col" style="width: 50px;" |Nullable |
| − | !Example Data | + | ! scope="col" style="width: 50px;" |Data Default |
| − | !Description | + | ! scope="col" style="width: 500px;" |Example Data |
| − | !Comments | + | ! scope="col" style="width: 350px;" |Description |
| + | ! scope="col" style="width: 350px;" |Comments | ||
|- | |- | ||
| | | | ||
| Line 1,550: | Line 2,934: | ||
|- | |- | ||
| | | | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| | | | ||
| | | | ||
| Line 1,593: | Line 2,949: | ||
| | | | ||
| | | | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
|- | |- | ||
| | | | ||
| Line 1,630: | Line 2,973: | ||
| | | | ||
| | | | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
|- | |- | ||
| | | | ||
| Line 1,660: | Line 2,990: | ||
| | | | ||
|- | |- | ||
| − | |||
| | | | ||
| | | | ||
Latest revision as of 14:10, 28 August 2024
 |
This page has been highlighted as a candidate for deletion. Please see the discussion. Reason: Moved to internal Health Canada wiki. Internal database structure should not be public. |
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
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 | 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
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
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
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
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
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
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. */
|
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
|
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
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
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
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
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
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
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
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
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
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
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 |
|---|---|---|---|---|---|---|---|
