Difference between revisions of "MDCCD Data Warehouse Data Dictionary"
Jump to navigation
Jump to search
Jeff.tyndall (talk | contribs) |
Jeff.tyndall (talk | contribs) |
||
| Line 5: | Line 5: | ||
=== 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 sortable" | {| class="wikitable sortable" | ||
|+ | |+ | ||
| − | ! scope="col" style="width: 350px;" |Column Name | + | ! scope="col" style="width: 350px;" | Column Name |
! scope="col" style="width: 100px;" |DataType | ! scope="col" style="width: 100px;" |DataType | ||
! scope="col" style="width: 50px;" |Key Type | ! scope="col" style="width: 50px;" |Key Type | ||
| Line 14: | Line 16: | ||
! scope="col" style="width: 50px;" |Data Default | ! scope="col" style="width: 50px;" |Data Default | ||
! scope="col" style="width: 500px;" |Example Data | ! scope="col" style="width: 500px;" |Example Data | ||
| − | ! scope="col" style="width: 350px;" |Description | + | ! scope="col" style="width: 350px;" | Description |
! scope="col" style="width: 350px;" |Comments | ! scope="col" style="width: 350px;" |Comments | ||
|- | |- | ||
| Line 46: | Line 48: | ||
|SITE_NAME | |SITE_NAME | ||
| | | | ||
| − | |VARCHAR2(160 CHAR) | + | | VARCHAR2(160 CHAR) |
|Yes | |Yes | ||
|NULL | |NULL | ||
| Line 53: | Line 55: | ||
| | | | ||
|- | |- | ||
| − | | LICENCE_NO | + | |LICENCE_NO |
| − | |Foreign | + | | Foreign |
|NUMBER(6,0) | |NUMBER(6,0) | ||
|Yes | |Yes | ||
|NULL | |NULL | ||
| − | |2027 | + | | 2027 |
| − | | 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 73: | Line 75: | ||
|REG_FILE_NUMBER | |REG_FILE_NUMBER | ||
| | | | ||
| − | | VARCHAR2(250 BYTE) | + | |VARCHAR2(250 BYTE) |
|Yes | |Yes | ||
|NULL | |NULL | ||
| − | | HC6-61-123/IM | + | |HC6-61-123/IM |
| − | | This is the internal program based file identifier. | + | |This is the internal program based file identifier. |
| | | | ||
|- | |- | ||
|REGION_CD_EN | |REGION_CD_EN | ||
| | | | ||
| − | |VARCHAR2(50 CHAR) | + | | VARCHAR2(50 CHAR) |
| − | |Yes | + | | Yes |
|NULL | |NULL | ||
|East | |East | ||
| Line 94: | Line 96: | ||
|Yes | |Yes | ||
|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. | ||
| | | | ||
| Line 100: | Line 102: | ||
|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 114: | ||
|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 132: | ||
|Yes | |Yes | ||
|NULL | |NULL | ||
| − | |Vanessa Toussaint | + | | Vanessa Toussaint |
|This is the name of the lead inspector. | |This is the name of the lead inspector. | ||
| | | | ||
| Line 136: | Line 138: | ||
|LEAD_INSPECTOR_BADGE | |LEAD_INSPECTOR_BADGE | ||
| | | | ||
| − | |VARCHAR2(50 BYTE) | + | | VARCHAR2(50 BYTE) |
|Yes | |Yes | ||
| − | |NULL | + | | NULL |
|54355335 | |54355335 | ||
|This is the badge number of the lead inspector. | |This is the badge number of the lead inspector. | ||
| Line 145: | Line 147: | ||
|SUPERVISOR_ID | |SUPERVISOR_ID | ||
|Foreign | |Foreign | ||
| − | | VARCHAR2(36 CHAR) | + | |VARCHAR2(36 CHAR) |
|Yes | |Yes | ||
|NULL | |NULL | ||
| Line 154: | Line 156: | ||
|SUPERVISOR | |SUPERVISOR | ||
| | | | ||
| − | |VARCHAR2(200 CHAR) | + | | VARCHAR2(200 CHAR) |
|Yes | |Yes | ||
|NULL | |NULL | ||
| − | |Ada Lai | + | | Ada Lai |
|This is the name of the supervisor for the inspection. | |This is the name of the supervisor for the inspection. | ||
| | | | ||
| Line 167: | Line 169: | ||
|NULL | |NULL | ||
|Open | |Open | ||
| − | | This is the status of the inspection in English. | + | |This is the status of the inspection in English. |
| | | | ||
|- | |- | ||
| − | | INSP_STATUS_FR | + | |INSP_STATUS_FR |
| | | | ||
|VARCHAR2(100 CHAR) | |VARCHAR2(100 CHAR) | ||
|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 190: | Line 192: | ||
|COMP_STANDARD_FR | |COMP_STANDARD_FR | ||
| | | | ||
| − | | VARCHAR2(100 CHAR) | + | |VARCHAR2(100 CHAR) |
|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. | ||
| | | | ||
| Line 199: | Line 201: | ||
|DRAFT_REPORT_RATING_EN | |DRAFT_REPORT_RATING_EN | ||
| | | | ||
| − | |VARCHAR2(100 CHAR) | + | | VARCHAR2(100 CHAR) |
|Yes | |Yes | ||
| − | | NULL | + | |NULL |
|Compliant | |Compliant | ||
|This is the draft inspection rating in English. | |This is the draft inspection rating in English. | ||
| Line 210: | Line 212: | ||
|VARCHAR2(100 CHAR) | |VARCHAR2(100 CHAR) | ||
|Yes | |Yes | ||
| − | | NULL | + | |NULL |
|Conforme | |Conforme | ||
|This is the draft inspection rating in French. | |This is the draft inspection rating in French. | ||
| Line 219: | Line 221: | ||
|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 228: | ||
|FINAL_REPORT_RATING_EN | |FINAL_REPORT_RATING_EN | ||
| | | | ||
| − | |VARCHAR2(100 CHAR) | + | | VARCHAR2(100 CHAR) |
|Yes | |Yes | ||
| − | | NULL | + | |NULL |
|Compliant | |Compliant | ||
|This is the final inspection rating in English. | |This is the final inspection rating in English. | ||
| Line 244: | Line 246: | ||
|FINAL_REPORT_ISSUE_DATE | |FINAL_REPORT_ISSUE_DATE | ||
| | | | ||
| − | | DATE | + | |DATE |
| − | |Yes | + | | Yes |
|NULL | |NULL | ||
| − | | 24-03-21 | + | |24-03-21 |
|This is the date that the final inspection rating was issued. | |This is the date that the final inspection rating was issued. | ||
| | | | ||
| Line 255: | Line 257: | ||
|DATE | |DATE | ||
|Yes | |Yes | ||
| − | | NULL | + | |NULL |
|24-03-22 | |24-03-22 | ||
|This is the date that an amended inspection report was issued to the company. | |This is the date that an amended inspection report was issued to the company. | ||
| Line 263: | Line 265: | ||
| | | | ||
|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 278: | ||
|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 |
|24-01-30 | |24-01-30 | ||
|This indicates when the inspection was finished. | |This indicates when the inspection was finished. | ||
| Line 292: | Line 294: | ||
|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 300: | ||
|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 308: | Line 310: | ||
| | | | ||
|VARCHAR2(5 CHAR) | |VARCHAR2(5 CHAR) | ||
| − | | Yes | + | |Yes |
|NULL | |NULL | ||
|Yes | |Yes | ||
| Line 332: | Line 334: | ||
| | | | ||
|- | |- | ||
| − | | AMENDED_DATE | + | |AMENDED_DATE |
| | | | ||
| − | |DATE | + | | DATE |
|Yes | |Yes | ||
| NULL | | NULL | ||
| Line 344: | Line 346: | ||
| | | | ||
|DATE | |DATE | ||
| − | | Yes | + | |Yes |
|NULL | |NULL | ||
|24-02-21 | |24-02-21 | ||
| Line 368: | Line 370: | ||
| | | | ||
|- | |- | ||
| − | | FIRST_COMM_MODE_FR | + | |FIRST_COMM_MODE_FR |
| | | | ||
|VARCHAR2(100 CHAR) | |VARCHAR2(100 CHAR) | ||
| Line 379: | Line 381: | ||
|EMERGENCY_CONTACT_NAME | |EMERGENCY_CONTACT_NAME | ||
| | | | ||
| − | |VARCHAR2(100 CHAR) | + | | VARCHAR2(100 CHAR) |
|Yes | |Yes | ||
|NULL | |NULL | ||
| Line 392: | Line 394: | ||
|NULL | |NULL | ||
|514-123-4566 | |514-123-4566 | ||
| − | | This is the phone number of the emergency contact for the company at the time of inspection. | + | |This is the phone number of the emergency contact for the company at the time of inspection. |
| | | | ||
|- | |- | ||
|SENIOR_OFFICIAL_OFFICIAL_ID | |SENIOR_OFFICIAL_OFFICIAL_ID | ||
| − | | Foreign | + | |Foreign |
|VARCHAR2(36 CHAR) | |VARCHAR2(36 CHAR) | ||
| − | | Yes | + | |Yes |
|NULL | |NULL | ||
|31175da9-efdf-ee11-904c-000d3a847fce | |31175da9-efdf-ee11-904c-000d3a847fce | ||
| Line 410: | Line 412: | ||
|NULL | |NULL | ||
|SVEN LILLEDRU | |SVEN LILLEDRU | ||
| − | |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 419: | ||
|VARCHAR2(100 CHAR) | |VARCHAR2(100 CHAR) | ||
|Yes | |Yes | ||
| − | |NULL | + | | NULL |
|G.M. | |G.M. | ||
|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 442: | Line 444: | ||
| | | | ||
|VARCHAR2(100 CHAR) | |VARCHAR2(100 CHAR) | ||
| − | |Yes | + | | Yes |
|NULL | |NULL | ||
|MANAGER, COMMERCIAL QUALITY & REG EU & CAN | |MANAGER, COMMERCIAL QUALITY & REG EU & CAN | ||
| Line 452: | Line 454: | ||
|VARCHAR2(100 CHAR) | |VARCHAR2(100 CHAR) | ||
|Yes | |Yes | ||
| − | |NULL | + | | NULL |
|416-765-0345 | |416-765-0345 | ||
|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 461: | ||
| | | | ||
|VARCHAR2(100 CHAR) | |VARCHAR2(100 CHAR) | ||
| − | |Yes | + | | Yes |
|NULL | |NULL | ||
| − | |514-123-4567 | + | | 514-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 471: | Line 473: | ||
|NULL | |NULL | ||
|sven@quarthealthcare.com | |sven@quarthealthcare.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 481: | ||
|Yes | |Yes | ||
|NULL | |NULL | ||
| − | |36 NORTHLINE ROAD, SUITE 9 | + | | 36 NORTHLINE ROAD, SUITE 9 |
|This is the address of the company being inspected. | |This is the address of the company being inspected. | ||
| | | | ||
| Line 488: | Line 490: | ||
|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. | ||
| | | | ||
|- | |- | ||
| − | | SITE_ADDRESS_3 | + | |SITE_ADDRESS_3 |
| | | | ||
|VARCHAR2(250 CHAR) | |VARCHAR2(250 CHAR) | ||
| Line 518: | Line 520: | ||
| | | | ||
|- | |- | ||
| − | | SITE_POSTALCODE | + | |SITE_POSTALCODE |
| | | | ||
|VARCHAR2(20 CHAR) | |VARCHAR2(20 CHAR) | ||
| − | | Yes | + | |Yes |
| NULL | | NULL | ||
|M4B 3E2 | |M4B 3E2 | ||
| − | | This is the postal code of the company being inspected. | + | |This is the postal code of the company being inspected. |
| | | | ||
|- | |- | ||
| Line 539: | Line 541: | ||
| | | | ||
|CLOB | |CLOB | ||
| − | |Yes | + | | Yes |
|NULL | |NULL | ||
|La réunion d'ouverture a eu lieu le 29 mars 2023 à distance (via VidCruiter) en présence de Éric Simard (Vice-Président et Secrétaire Trésorier de 1164250921 Quebec Inc Dba Kasla Entreprise Inc.) et My Din Phan (Inspecteur de Santé Canada). | |La réunion d'ouverture a eu lieu le 29 mars 2023 à distance (via VidCruiter) en présence de Éric Simard (Vice-Président et Secrétaire Trésorier de 1164250921 Quebec Inc Dba Kasla Entreprise Inc.) et My Din Phan (Inspecteur de Santé Canada). | ||
| Line 550: | Line 552: | ||
|Yes | |Yes | ||
|NULL | |NULL | ||
| − | | c7e5bef2-ecdf-ee11-904c-6045bd60a7a2 | + | |c7e5bef2-ecdf-ee11-904c-6045bd60a7a2 |
|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. | ||
| | | | ||
|- | |- | ||
| − | | REPORT_DELIVERED_TO | + | |REPORT_DELIVERED_TO |
| | | | ||
| − | |VARCHAR2(200 CHAR) | + | | VARCHAR2(200 CHAR) |
|Yes | |Yes | ||
|NULL | |NULL | ||
| − | |MARILU ALEJANDRA VALERIANO | + | | MARILU ALEJANDRA VALERIANO |
|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 566: | Line 568: | ||
| | | | ||
|DATE | |DATE | ||
| − | | Yes | + | |Yes |
|NULL | |NULL | ||
|24-02-28 | |24-02-28 | ||
| − | | This is the date that Health Canada is expecting a response from the company about information captured in the final inspection report. | + | |This is the date that Health Canada is expecting a response from the company about information captured in the final inspection report. |
| | | | ||
|- | |- | ||
| Line 581: | Line 583: | ||
| | | | ||
|- | |- | ||
| − | |PERSONNEL_CHANGES | + | | PERSONNEL_CHANGES |
| | | | ||
|VARCHAR2(2000 BYTE) | |VARCHAR2(2000 BYTE) | ||
| Line 593: | Line 595: | ||
| | | | ||
|VARCHAR2(50 CHAR) | |VARCHAR2(50 CHAR) | ||
| − | | Yes | + | |Yes |
|NULL | |NULL | ||
| − | | In Progress | + | |In Progress |
|This indicates the status of the IID in English. | |This indicates the status of the IID in English. | ||
|DHPID Transparency IID | |DHPID Transparency IID | ||
|- | |- | ||
| − | |IID_STATUS_FR | + | | IID_STATUS_FR |
| | | | ||
|VARCHAR2(50 CHAR) | |VARCHAR2(50 CHAR) | ||
|Yes | |Yes | ||
|NULL | |NULL | ||
| − | | En cours | + | |En cours |
| − | | This indicates the status of the IID in French. | + | |This indicates the status of the IID in French. |
|DHPID Transparency IID | |DHPID Transparency IID | ||
|- | |- | ||
| Line 613: | Line 615: | ||
|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 |
|- | |- | ||
|IID_REVISION_LOG | |IID_REVISION_LOG | ||
| Line 623: | Line 625: | ||
|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 630: | Line 632: | ||
|VARCHAR2(50 CHAR) | |VARCHAR2(50 CHAR) | ||
|Yes | |Yes | ||
| − | | NULL | + | |NULL |
|IRC Ready For Publication | |IRC Ready For Publication | ||
|This indicates the status of the IRC in English. | |This indicates the status of the IRC in English. | ||
| − | | DHPID Transparency IRC | + | |DHPID Transparency IRC |
|- | |- | ||
| − | | IRC_STATUS_FR | + | |IRC_STATUS_FR |
| | | | ||
| − | |VARCHAR2(50 CHAR) | + | | VARCHAR2(50 CHAR) |
|Yes | |Yes | ||
|NULL | |NULL | ||
| Line 673: | Line 675: | ||
|DHPID Transparency IRC | |DHPID Transparency IRC | ||
|- | |- | ||
| − | | INSP_OUTCOME_FR | + | |INSP_OUTCOME_FR |
| | | | ||
|VARCHAR2(2000 CHAR) | |VARCHAR2(2000 CHAR) | ||
| − | | Yes | + | |Yes |
|NULL | |NULL | ||
|Une cote « conforme » a été émise aux termes de l’inspection. \r\n\r\nUne cote d’inspection « conforme » permet à une entreprise de conserver sa licence et de poursuivre ses activités. Une cote « conforme » ne signifie toutefois pas qu’aucune observation n’a été relevée pendant l’inspection. Si une entreprise reçoit une cote d’inspection « conforme » et si des observations ont été relevées, elle doit élaborer un plan pour corriger ces observations et éviter qu’elles ne se reproduisent | |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 | ||
| Line 682: | Line 684: | ||
|DHPID Transparency IRC | |DHPID Transparency IRC | ||
|- | |- | ||
| − | | INSP_REPORT_WAITING | + | |INSP_REPORT_WAITING |
| | | | ||
|VARCHAR2(200 CHAR) | |VARCHAR2(200 CHAR) | ||
| Line 693: | Line 695: | ||
|INSP_REPORT_WAITING_DATE | |INSP_REPORT_WAITING_DATE | ||
| | | | ||
| − | |DATE | + | | DATE |
|Yes | |Yes | ||
|NULL | |NULL | ||
| Line 711: | Line 713: | ||
|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. | ||
| | | | ||
|- | |- | ||
| − | | INSP_CLOSED_WAITING | + | |INSP_CLOSED_WAITING |
| | | | ||
|VARCHAR2(200 CHAR) | |VARCHAR2(200 CHAR) | ||
|Yes | |Yes | ||
| − | | NULL | + | |NULL |
|Jeff Tyndall | |Jeff Tyndall | ||
|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. | ||
| Line 728: | Line 730: | ||
|INSP_CLOSED_WAITING_DATE | |INSP_CLOSED_WAITING_DATE | ||
| | | | ||
| − | |DATE | + | | DATE |
|Yes | |Yes | ||
|NULL | |NULL | ||
| Line 749: | Line 751: | ||
|Yes | |Yes | ||
|NULL | |NULL | ||
| − | | 23-11-29 | + | |23-11-29 |
|This is the date the supervisor approved the inspection for closure in the record log. | |This is the date the supervisor approved the inspection for closure in the record log. | ||
| | | | ||
| Line 758: | Line 760: | ||
|Yes | |Yes | ||
|NULL | |NULL | ||
| − | | Vanessa Toussaint | + | |Vanessa Toussaint |
|This identifies the resource that created the record. | |This identifies the resource that created the record. | ||
| | | | ||
| Line 771: | Line 773: | ||
| | | | ||
|- | |- | ||
| − | | MODIFIEDBYNAME | + | |MODIFIEDBYNAME |
| | | | ||
|VARCHAR2(100 CHAR) | |VARCHAR2(100 CHAR) | ||
|Yes | |Yes | ||
|NULL | |NULL | ||
| − | |Vanessa Toussaint | + | | Vanessa Toussaint |
| − | |This identifies the resource that last modified the record. | + | | This identifies the resource that last modified the record. |
| | | | ||
|- | |- | ||
| Line 794: | Line 796: | ||
{| class="wikitable sortable" | {| class="wikitable sortable" | ||
|+ | |+ | ||
| − | ! scope="col" style="width: 350px;" |Column Name | + | ! scope="col" style="width: 350px;" | Column Name |
! scope="col" style="width: 100px;" |DataType | ! scope="col" style="width: 100px;" |DataType | ||
! scope="col" style="width: 50px;" |Key Type | ! scope="col" style="width: 50px;" |Key Type | ||
| Line 800: | Line 802: | ||
! scope="col" style="width: 50px;" |Data Default | ! scope="col" style="width: 50px;" |Data Default | ||
! scope="col" style="width: 500px;" |Example Data | ! scope="col" style="width: 500px;" |Example Data | ||
| − | ! scope="col" style="width: 350px;" |Description | + | ! scope="col" style="width: 350px;" | Description |
! scope="col" style="width: 350px;" |Comments | ! scope="col" style="width: 350px;" |Comments | ||
|- | |- | ||
| − | |CAPA_ID | + | | CAPA_ID |
|VARCHAR2(36 CHAR) | |VARCHAR2(36 CHAR) | ||
|Primary | |Primary | ||
|No | |No | ||
|NULL | |NULL | ||
| − | | ad5e55f6-8b21-ee11-9966-000d3ae86452 | + | |ad5e55f6-8b21-ee11-9966-000d3ae86452 |
|This is the unique record identifier for the CAPA. | |This is the unique record identifier for the CAPA. | ||
| | | | ||
| Line 815: | Line 817: | ||
|VARCHAR2(36 CHAR) | |VARCHAR2(36 CHAR) | ||
|Foreign | |Foreign | ||
| − | | No | + | |No |
|NULL | |NULL | ||
|3d7a309e-ce46-40c5-a6c2-f3ea80971d48 | |3d7a309e-ce46-40c5-a6c2-f3ea80971d48 | ||
| Line 824: | Line 826: | ||
|VARCHAR2(200 CHAR) | |VARCHAR2(200 CHAR) | ||
|Foreign | |Foreign | ||
| − | | No | + | |No |
|NULL | |NULL | ||
|00086-BENEMAX DBA. BENEMAX HEALTH AND WELLNESS | |00086-BENEMAX DBA. BENEMAX HEALTH AND WELLNESS | ||
| Line 840: | Line 842: | ||
|- | |- | ||
|CAPA_TYPE | |CAPA_TYPE | ||
| − | | VARCHAR2(100 CHAR) | + | |VARCHAR2(100 CHAR) |
| | | | ||
|Yes | |Yes | ||
| − | |NULL | + | | NULL |
|Follow Up Plan | |Follow Up Plan | ||
|This is a free text field used to identify the type of CAPA. | |This is a free text field used to identify the type of CAPA. | ||
| Line 849: | Line 851: | ||
|- | |- | ||
|CAPA_PLAN | |CAPA_PLAN | ||
| − | |VARCHAR2(2000 CHAR) | + | | VARCHAR2(2000 CHAR) |
| | | | ||
|Yes | |Yes | ||
| Line 862: | Line 864: | ||
|Yes | |Yes | ||
|NULL | |NULL | ||
| − | |Jeff Tyndall | + | | Jeff Tyndall |
|This identifies the resource that created the record. | |This identifies the resource that created the record. | ||
| | | | ||
| Line 880: | Line 882: | ||
|Yes | |Yes | ||
|NULL | |NULL | ||
| − | |Jeff Tyndall | + | | Jeff Tyndall |
| − | |This identifies the resource that last modified the record. | + | | This identifies the resource that last modified the record. |
| | | | ||
|- | |- | ||
| Line 898: | Line 900: | ||
{| class="wikitable sortable" | {| class="wikitable sortable" | ||
|+ | |+ | ||
| − | ! scope="col" style="width: 350px;" |Column Name | + | ! scope="col" style="width: 350px;" | Column Name |
! scope="col" style="width: 100px;" |DataType | ! scope="col" style="width: 100px;" |DataType | ||
! scope="col" style="width: 50px;" |Key Type | ! scope="col" style="width: 50px;" |Key Type | ||
| Line 904: | Line 906: | ||
! scope="col" style="width: 50px;" |Data Default | ! scope="col" style="width: 50px;" |Data Default | ||
! scope="col" style="width: 500px;" |Example Data | ! scope="col" style="width: 500px;" |Example Data | ||
| − | ! scope="col" style="width: 350px;" |Description | + | ! scope="col" style="width: 350px;" | Description |
! scope="col" style="width: 350px;" |Comments | ! scope="col" style="width: 350px;" |Comments | ||
|- | |- | ||
| − | | INSP_DESIGNATION_ID | + | |INSP_DESIGNATION_ID |
|VARCHAR2(36 CHAR) | |VARCHAR2(36 CHAR) | ||
|Primary | |Primary | ||
| Line 922: | Line 924: | ||
|NULL | |NULL | ||
|3d7a309e-ce46-40c5-a6c2-f3ea80971d48 | |3d7a309e-ce46-40c5-a6c2-f3ea80971d48 | ||
| − | | This is the unique record identifier for the parent inspection. | + | |This is the unique record identifier for the parent inspection. |
|Use to join with MD_INS_CASE.INSP_ID. | |Use to join with MD_INS_CASE.INSP_ID. | ||
|- | |- | ||
| Line 930: | Line 932: | ||
|No | |No | ||
|NULL | |NULL | ||
| − | |00070-QUART HEALTHCARE INC. | + | | 00070-QUART HEALTHCARE INC. |
|This is the parent inspection number. | |This is the parent inspection number. | ||
| | | | ||
| Line 944: | Line 946: | ||
|- | |- | ||
|DESIGNATION_CODE | |DESIGNATION_CODE | ||
| − | |VARCHAR2(100 CHAR) | + | | VARCHAR2(100 CHAR) |
| | | | ||
|Yes | |Yes | ||
| − | | NULL | + | |NULL |
|5006 | |5006 | ||
|This is the designation code. | |This is the designation code. | ||
| | | | ||
|- | |- | ||
| − | | DESIGNATION_NAME | + | |DESIGNATION_NAME |
| − | |VARCHAR2(250 CHAR) | + | | VARCHAR2(250 CHAR) |
| | | | ||
|Yes | |Yes | ||
| Line 965: | Line 967: | ||
|Yes | |Yes | ||
|NULL | |NULL | ||
| − | | Covid | + | |Covid |
|This is the name of the designation code in English. | |This is the name of the designation code in English. | ||
| | | | ||
|- | |- | ||
|DESIGNATION_DECODE_FR | |DESIGNATION_DECODE_FR | ||
| − | |VARCHAR2(100 CHAR) | + | | VARCHAR2(100 CHAR) |
| | | | ||
|Yes | |Yes | ||
|NULL | |NULL | ||
| − | |Covid | + | | Covid |
|This is the name of the designation code in French. | |This is the name of the designation code in French. | ||
| | | | ||
| Line 983: | Line 985: | ||
|Yes | |Yes | ||
|NULL | |NULL | ||
| − | |Jeff Tyndall | + | | Jeff Tyndall |
|This identifies the resource that created the record. | |This identifies the resource that created the record. | ||
| | | | ||
|- | |- | ||
| − | | CREATEDON | + | |CREATEDON |
|DATE | |DATE | ||
| | | | ||
| Line 1,001: | Line 1,003: | ||
|Yes | |Yes | ||
|NULL | |NULL | ||
| − | |Jeff Tyndall | + | | Jeff Tyndall |
| − | |This identifies the resource that last modified the record. | + | | This identifies the resource that last modified the record. |
| | | | ||
|- | |- | ||
| Line 1,019: | Line 1,021: | ||
{| class="wikitable sortable" | {| class="wikitable sortable" | ||
|+ | |+ | ||
| − | ! scope="col" style="width: 350px;" |Column Name | + | ! scope="col" style="width: 350px;" | Column Name |
! scope="col" style="width: 100px;" |DataType | ! scope="col" style="width: 100px;" |DataType | ||
! scope="col" style="width: 50px;" |Key Type | ! scope="col" style="width: 50px;" |Key Type | ||
| Line 1,025: | Line 1,027: | ||
! scope="col" style="width: 50px;" |Data Default | ! scope="col" style="width: 50px;" |Data Default | ||
! scope="col" style="width: 500px;" |Example Data | ! scope="col" style="width: 500px;" |Example Data | ||
| − | ! scope="col" style="width: 350px;" |Description | + | ! scope="col" style="width: 350px;" | Description |
! scope="col" style="width: 350px;" |Comments | ! scope="col" style="width: 350px;" |Comments | ||
|- | |- | ||
| Line 1,065: | Line 1,067: | ||
|- | |- | ||
|ACTIVITY_NAME | |ACTIVITY_NAME | ||
| − | |VARCHAR2(205 CHAR) | + | | VARCHAR2(205 CHAR) |
| | | | ||
| − | |Yes | + | | Yes |
| − | | NULL | + | |NULL |
|Importateur - Classe 2 | |Importateur - Classe 2 | ||
|This is the establishment activity. | |This is the establishment activity. | ||
| Line 1,076: | Line 1,078: | ||
|VARCHAR2(100 CHAR) | |VARCHAR2(100 CHAR) | ||
| | | | ||
| − | |Yes | + | | Yes |
|NULL | |NULL | ||
|Importer - Class 2 | |Importer - Class 2 | ||
| Line 1,082: | Line 1,084: | ||
| | | | ||
|- | |- | ||
| − | | ACTIVITY_DECODE_FR | + | |ACTIVITY_DECODE_FR |
|VARCHAR2(100 CHAR) | |VARCHAR2(100 CHAR) | ||
| | | | ||
|Yes | |Yes | ||
|NULL | |NULL | ||
| − | |Importateur - Classe 2 | + | | 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,096: | Line 1,098: | ||
|Yes | |Yes | ||
|NULL | |NULL | ||
| − | |Jeff Tyndall | + | | Jeff Tyndall |
|This identifies the resource that created the record. | |This identifies the resource that created the record. | ||
| | | | ||
| Line 1,103: | Line 1,105: | ||
|DATE | |DATE | ||
| | | | ||
| − | | Yes | + | |Yes |
|NULL | |NULL | ||
|22-08-26 | |22-08-26 | ||
| Line 1,114: | Line 1,116: | ||
|Yes | |Yes | ||
|NULL | |NULL | ||
| − | |Jeff Tyndall | + | | Jeff Tyndall |
| − | |This identifies the resource that last modified the record. | + | | This identifies the resource that last modified the record. |
| | | | ||
|- | |- | ||
| Line 1,128: | Line 1,130: | ||
|} | |} | ||
| − | === MD_INS_CASE_INSPECTORS=== | + | ===MD_INS_CASE_INSPECTORS=== |
This table holds a listing of inspectors assigned to an inspection. | This table holds a listing of inspectors assigned to an inspection. | ||
{| class="wikitable sortable" | {| class="wikitable sortable" | ||
|+ | |+ | ||
| − | ! scope="col" style="width: 350px;" |Column Name | + | ! scope="col" style="width: 350px;" | Column Name |
! scope="col" style="width: 100px;" |DataType | ! scope="col" style="width: 100px;" |DataType | ||
! scope="col" style="width: 50px;" |Key Type | ! scope="col" style="width: 50px;" |Key Type | ||
| Line 1,138: | Line 1,140: | ||
! scope="col" style="width: 50px;" |Data Default | ! scope="col" style="width: 50px;" |Data Default | ||
! scope="col" style="width: 500px;" |Example Data | ! scope="col" style="width: 500px;" |Example Data | ||
| − | ! scope="col" style="width: 350px;" |Description | + | ! scope="col" style="width: 350px;" | Description |
! scope="col" style="width: 350px;" |Comments | ! scope="col" style="width: 350px;" |Comments | ||
|- | |- | ||
| Line 1,172: | Line 1,174: | ||
| | | | ||
|Yes | |Yes | ||
| − | | NULL | + | |NULL |
|Jeff Tyndall | |Jeff Tyndall | ||
|This is the name of the inspector. | |This is the name of the inspector. | ||
| Line 1,180: | Line 1,182: | ||
|VARCHAR2(3 CHAR) | |VARCHAR2(3 CHAR) | ||
| | | | ||
| − | |Yes | + | | Yes |
|NULL | |NULL | ||
|Yes | |Yes | ||
| Line 1,189: | Line 1,191: | ||
|VARCHAR2(3 CHAR) | |VARCHAR2(3 CHAR) | ||
| | | | ||
| − | |Yes | + | | Yes |
|NULL | |NULL | ||
|Oui | |Oui | ||
| Line 1,199: | Line 1,201: | ||
| | | | ||
|Yes | |Yes | ||
| − | | NULL | + | |NULL |
|Scheduled | |Scheduled | ||
|**Not used by program.** | |**Not used by program.** | ||
| Line 1,207: | Line 1,209: | ||
|VARCHAR2(100 CHAR) | |VARCHAR2(100 CHAR) | ||
| | | | ||
| − | |Yes | + | | Yes |
|NULL | |NULL | ||
| | | | ||
| Line 1,236: | Line 1,238: | ||
|Yes | |Yes | ||
|NULL | |NULL | ||
| − | |Jeff Tyndall | + | | Jeff Tyndall |
|This identifies the resource that created the record. | |This identifies the resource that created the record. | ||
| | | | ||
| Line 1,252: | Line 1,254: | ||
|VARCHAR2(100 CHAR) | |VARCHAR2(100 CHAR) | ||
| | | | ||
| − | | Yes | + | |Yes |
|NULL | |NULL | ||
| − | |Jeff Tyndall | + | | Jeff Tyndall |
| − | |This identifies the resource that last modified the record. | + | | This identifies the resource that last modified the record. |
| | | | ||
|- | |- | ||
| Line 1,272: | Line 1,274: | ||
{| class="wikitable sortable" | {| class="wikitable sortable" | ||
|+ | |+ | ||
| − | ! scope="col" style="width: 350px;" |Column Name | + | ! scope="col" style="width: 350px;" | Column Name |
! scope="col" style="width: 100px;" |DataType | ! scope="col" style="width: 100px;" |DataType | ||
! scope="col" style="width: 50px;" |Key Type | ! scope="col" style="width: 50px;" |Key Type | ||
| Line 1,278: | Line 1,280: | ||
! scope="col" style="width: 50px;" |Data Default | ! scope="col" style="width: 50px;" |Data Default | ||
! scope="col" style="width: 500px;" |Example Data | ! scope="col" style="width: 500px;" |Example Data | ||
| − | ! scope="col" style="width: 350px;" |Description | + | ! scope="col" style="width: 350px;" | Description |
! scope="col" style="width: 350px;" |Comments | ! scope="col" style="width: 350px;" |Comments | ||
|- | |- | ||
| Line 1,303: | Line 1,305: | ||
|Foreign | |Foreign | ||
|No | |No | ||
| − | |NULL | + | | NULL |
|00027-FUTURE HEALTH TECHNOLOGIES | |00027-FUTURE HEALTH TECHNOLOGIES | ||
|This is the parent inspection number. | |This is the parent inspection number. | ||
| Line 1,317: | Line 1,319: | ||
| | | | ||
|- | |- | ||
| − | |NOTETEXT | + | | NOTETEXT |
|CLOB | |CLOB | ||
| | | | ||
|Yes | |Yes | ||
|NULL | |NULL | ||
| − | |Communication with company on 2023-09-01. | + | | Communication with company on 2023-09-01. |
|This is the body of the note. | |This is the body of the note. | ||
| | | | ||
| Line 1,331: | Line 1,333: | ||
|Yes | |Yes | ||
|NULL | |NULL | ||
| − | |Jeff Tyndall | + | | Jeff Tyndall |
|This identifies the resource that created the record. | |This identifies the resource that created the record. | ||
| | | | ||
| Line 1,347: | Line 1,349: | ||
|VARCHAR2(100 CHAR) | |VARCHAR2(100 CHAR) | ||
| | | | ||
| − | | Yes | + | |Yes |
|NULL | |NULL | ||
| − | |Jeff Tyndall | + | | Jeff Tyndall |
| − | |This identifies the resource that last modified the record. | + | | This identifies the resource that last modified the record. |
| | | | ||
|- | |- | ||
| Line 1,367: | Line 1,369: | ||
{| class="wikitable sortable" | {| class="wikitable sortable" | ||
|+ | |+ | ||
| − | ! scope="col" style="width: 350px;" |Column Name | + | ! scope="col" style="width: 350px;" | Column Name |
! scope="col" style="width: 100px;" |DataType | ! scope="col" style="width: 100px;" |DataType | ||
! scope="col" style="width: 50px;" |Key Type | ! scope="col" style="width: 50px;" |Key Type | ||
| − | ! scope="col" style="width: 50px;" | Nullable | + | ! scope="col" style="width: 50px;" |Nullable |
| − | ! scope="col" style="width: 50px;" | Data Default | + | ! scope="col" style="width: 50px;" |Data Default |
! scope="col" style="width: 500px;" |Example Data | ! scope="col" style="width: 500px;" |Example Data | ||
| − | ! scope="col" style="width: 350px;" |Description | + | ! scope="col" style="width: 350px;" | Description |
! scope="col" style="width: 350px;" |Comments | ! scope="col" style="width: 350px;" |Comments | ||
|- | |- | ||
| Line 1,401: | Line 1,403: | ||
|} | |} | ||
| − | === MD_INS_CASE_WORKFLOW=== | + | ===MD_INS_CASE_WORKFLOW=== |
This table holds information that categorizes or "tags" an inspection. | This table holds information that categorizes or "tags" an inspection. | ||
{| class="wikitable sortable" | {| class="wikitable sortable" | ||
|+ | |+ | ||
| − | ! scope="col" style="width: 350px;" |Column Name | + | ! scope="col" style="width: 350px;" | Column Name |
! scope="col" style="width: 100px;" |DataType | ! scope="col" style="width: 100px;" |DataType | ||
! scope="col" style="width: 50px;" |Key Type | ! scope="col" style="width: 50px;" |Key Type | ||
| Line 1,411: | Line 1,413: | ||
! scope="col" style="width: 50px;" |Data Default | ! scope="col" style="width: 50px;" |Data Default | ||
! scope="col" style="width: 500px;" |Example Data | ! scope="col" style="width: 500px;" |Example Data | ||
| − | ! scope="col" style="width: 350px;" |Description | + | ! scope="col" style="width: 350px;" | Description |
! scope="col" style="width: 350px;" |Comments | ! scope="col" style="width: 350px;" |Comments | ||
|- | |- | ||
| Line 1,443: | Line 1,445: | ||
{| class="wikitable sortable" | {| class="wikitable sortable" | ||
|+ | |+ | ||
| − | ! scope="col" style="width: 350px;" |Column Name | + | ! scope="col" style="width: 350px;" | Column Name |
! scope="col" style="width: 100px;" |DataType | ! scope="col" style="width: 100px;" |DataType | ||
! scope="col" style="width: 50px;" |Key Type | ! scope="col" style="width: 50px;" |Key Type | ||
| Line 1,449: | Line 1,451: | ||
! scope="col" style="width: 50px;" |Data Default | ! scope="col" style="width: 50px;" |Data Default | ||
! scope="col" style="width: 500px;" |Example Data | ! scope="col" style="width: 500px;" |Example Data | ||
| − | ! scope="col" style="width: 350px;" |Description | + | ! scope="col" style="width: 350px;" | Description |
! scope="col" style="width: 350px;" |Comments | ! scope="col" style="width: 350px;" |Comments | ||
|- | |- | ||
| Line 1,481: | Line 1,483: | ||
{| class="wikitable sortable" | {| class="wikitable sortable" | ||
|+ | |+ | ||
| − | ! scope="col" style="width: 350px;" |Column Name | + | ! scope="col" style="width: 350px;" | Column Name |
! scope="col" style="width: 100px;" |DataType | ! scope="col" style="width: 100px;" |DataType | ||
! scope="col" style="width: 50px;" |Key Type | ! scope="col" style="width: 50px;" |Key Type | ||
| Line 1,487: | Line 1,489: | ||
! scope="col" style="width: 50px;" |Data Default | ! scope="col" style="width: 50px;" |Data Default | ||
! scope="col" style="width: 500px;" |Example Data | ! scope="col" style="width: 500px;" |Example Data | ||
| − | ! scope="col" style="width: 350px;" |Description | + | ! scope="col" style="width: 350px;" | Description |
! scope="col" style="width: 350px;" |Comments | ! scope="col" style="width: 350px;" |Comments | ||
|- | |- | ||
| Line 1,519: | Line 1,521: | ||
{| class="wikitable sortable" | {| class="wikitable sortable" | ||
|+ | |+ | ||
| − | ! scope="col" style="width: 350px;" |Column Name | + | ! scope="col" style="width: 350px;" | Column Name |
! scope="col" style="width: 100px;" |DataType | ! scope="col" style="width: 100px;" |DataType | ||
! scope="col" style="width: 50px;" |Key Type | ! scope="col" style="width: 50px;" |Key Type | ||
| Line 1,525: | Line 1,527: | ||
! scope="col" style="width: 50px;" |Data Default | ! scope="col" style="width: 50px;" |Data Default | ||
! scope="col" style="width: 500px;" |Example Data | ! scope="col" style="width: 500px;" |Example Data | ||
| − | ! scope="col" style="width: 350px;" |Description | + | ! scope="col" style="width: 350px;" | Description |
! scope="col" style="width: 350px;" |Comments | ! scope="col" style="width: 350px;" |Comments | ||
|- | |- | ||
| Line 1,557: | Line 1,559: | ||
{| class="wikitable sortable" | {| class="wikitable sortable" | ||
|+ | |+ | ||
| − | ! scope="col" style="width: 350px;" |Column Name | + | ! scope="col" style="width: 350px;" | Column Name |
! scope="col" style="width: 100px;" |DataType | ! scope="col" style="width: 100px;" |DataType | ||
! scope="col" style="width: 50px;" |Key Type | ! scope="col" style="width: 50px;" |Key Type | ||
| Line 1,563: | Line 1,565: | ||
! scope="col" style="width: 50px;" |Data Default | ! scope="col" style="width: 50px;" |Data Default | ||
! scope="col" style="width: 500px;" |Example Data | ! scope="col" style="width: 500px;" |Example Data | ||
| − | ! scope="col" style="width: 350px;" |Description | + | ! scope="col" style="width: 350px;" | Description |
! scope="col" style="width: 350px;" |Comments | ! scope="col" style="width: 350px;" |Comments | ||
|- | |- | ||
| Line 1,591: | Line 1,593: | ||
|} | |} | ||
| − | ===MD_INS_INS_IRC_OBSERVATIONS=== | + | === MD_INS_INS_IRC_OBSERVATIONS=== |
This table holds information that categorizes or "tags" an inspection. | This table holds information that categorizes or "tags" an inspection. | ||
{| class="wikitable sortable" | {| class="wikitable sortable" | ||
|+ | |+ | ||
| − | ! scope="col" style="width: 350px;" |Column Name | + | ! scope="col" style="width: 350px;" | Column Name |
! scope="col" style="width: 100px;" |DataType | ! scope="col" style="width: 100px;" |DataType | ||
! scope="col" style="width: 50px;" |Key Type | ! scope="col" style="width: 50px;" |Key Type | ||
| Line 1,601: | Line 1,603: | ||
! scope="col" style="width: 50px;" |Data Default | ! scope="col" style="width: 50px;" |Data Default | ||
! scope="col" style="width: 500px;" |Example Data | ! scope="col" style="width: 500px;" |Example Data | ||
| − | ! scope="col" style="width: 350px;" |Description | + | ! scope="col" style="width: 350px;" | Description |
! scope="col" style="width: 350px;" |Comments | ! scope="col" style="width: 350px;" |Comments | ||
|- | |- | ||
| Line 1,629: | Line 1,631: | ||
|} | |} | ||
| − | ===MD_INS_INS_SITE=== | + | === MD_INS_INS_SITE=== |
This table holds information that categorizes or "tags" an inspection. | This table holds information that categorizes or "tags" an inspection. | ||
{| class="wikitable sortable" | {| class="wikitable sortable" | ||
|+ | |+ | ||
| − | ! scope="col" style="width: 350px;" |Column Name | + | ! scope="col" style="width: 350px;" | Column Name |
! scope="col" style="width: 100px;" |DataType | ! scope="col" style="width: 100px;" |DataType | ||
! scope="col" style="width: 50px;" |Key Type | ! scope="col" style="width: 50px;" |Key Type | ||
| Line 1,639: | Line 1,641: | ||
! scope="col" style="width: 50px;" |Data Default | ! scope="col" style="width: 50px;" |Data Default | ||
! scope="col" style="width: 500px;" |Example Data | ! scope="col" style="width: 500px;" |Example Data | ||
| − | ! scope="col" style="width: 350px;" |Description | + | ! scope="col" style="width: 350px;" | Description |
! scope="col" style="width: 350px;" |Comments | ! scope="col" style="width: 350px;" |Comments | ||
|- | |- | ||
| Line 1,671: | Line 1,673: | ||
{| class="wikitable sortable" | {| class="wikitable sortable" | ||
|+ | |+ | ||
| − | ! scope="col" style="width: 350px;" |Column Name | + | ! scope="col" style="width: 350px;" | Column Name |
! scope="col" style="width: 100px;" |DataType | ! scope="col" style="width: 100px;" |DataType | ||
! scope="col" style="width: 50px;" |Key Type | ! scope="col" style="width: 50px;" |Key Type | ||
| Line 1,677: | Line 1,679: | ||
! scope="col" style="width: 50px;" |Data Default | ! scope="col" style="width: 50px;" |Data Default | ||
! scope="col" style="width: 500px;" |Example Data | ! scope="col" style="width: 500px;" |Example Data | ||
| − | ! scope="col" style="width: 350px;" |Description | + | ! scope="col" style="width: 350px;" | Description |
! scope="col" style="width: 350px;" |Comments | ! scope="col" style="width: 350px;" |Comments | ||
|- | |- | ||
| Line 1,709: | Line 1,711: | ||
{| class="wikitable sortable" | {| class="wikitable sortable" | ||
|+ | |+ | ||
| − | ! scope="col" style="width: 350px;" |Column Name | + | ! scope="col" style="width: 350px;" | Column Name |
! scope="col" style="width: 100px;" |DataType | ! scope="col" style="width: 100px;" |DataType | ||
! scope="col" style="width: 50px;" |Key Type | ! scope="col" style="width: 50px;" |Key Type | ||
| Line 1,715: | Line 1,717: | ||
! scope="col" style="width: 50px;" |Data Default | ! scope="col" style="width: 50px;" |Data Default | ||
! scope="col" style="width: 500px;" |Example Data | ! scope="col" style="width: 500px;" |Example Data | ||
| − | ! scope="col" style="width: 350px;" |Description | + | ! scope="col" style="width: 350px;" | Description |
! scope="col" style="width: 350px;" |Comments | ! scope="col" style="width: 350px;" |Comments | ||
|- | |- | ||
| Line 1,747: | Line 1,749: | ||
{| class="wikitable sortable" | {| class="wikitable sortable" | ||
|+ | |+ | ||
| − | ! scope="col" style="width: 350px;" |Column Name | + | ! scope="col" style="width: 350px;" | Column Name |
! scope="col" style="width: 100px;" |DataType | ! scope="col" style="width: 100px;" |DataType | ||
! scope="col" style="width: 50px;" |Key Type | ! scope="col" style="width: 50px;" |Key Type | ||
| Line 1,753: | Line 1,755: | ||
! scope="col" style="width: 50px;" |Data Default | ! scope="col" style="width: 50px;" |Data Default | ||
! scope="col" style="width: 500px;" |Example Data | ! scope="col" style="width: 500px;" |Example Data | ||
| − | ! scope="col" style="width: 350px;" |Description | + | ! scope="col" style="width: 350px;" | Description |
! scope="col" style="width: 350px;" |Comments | ! scope="col" style="width: 350px;" |Comments | ||
|- | |- | ||
Revision as of 16:07, 23 May 2024
Overview
This page provides a data dictionary for each table that is held within the MDCCD Data Warehouse.
Medical Devices
MD_INS_CASE
This table holds the main inspection information.
| Column Name | DataType | 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-QUART HEALTHCARE INC. | This is the inspection number. | |
| SITE_ID | Foreign | VARCHAR2(36 CHAR) | No | NULL | 7b06cddd-12d5-ee11-904d-002248b0a560 | This is the unique record identifier for the company inspected. | |
| SITE_NAME | VARCHAR2(160 CHAR) | Yes | NULL | QUART HEALTHCARE INC. | This is the name of the company insepcted. | ||
| LICENCE_NO | Foreign | NUMBER(6,0) | Yes | NULL | 2027 | 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 | 107232 | 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 | HC6-61-123/IM | 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 | Vanessa Toussaint | This is the name of the lead inspector. | ||
| LEAD_INSPECTOR_BADGE | VARCHAR2(50 BYTE) | Yes | NULL | 54355335 | This is the badge number of the lead inspector. | ||
| SUPERVISOR_ID | Foreign | VARCHAR2(36 CHAR) | Yes | NULL | ed91a4cf-777a-ee11-8179-002248b32010 | This is the unique record identifier of the supervisor for the inspection. | |
| SUPERVISOR | VARCHAR2(200 CHAR) | Yes | NULL | Ada Lai | 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 | Vanessa Toussaint | 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 | 514-123-4566 | 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 | 31175da9-efdf-ee11-904c-000d3a847fce | 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 | SVEN LILLEDRU | This is the name of the senior official for the company at the time of inspection. | ||
| SENIOR_OFFICIAL_TITLE | VARCHAR2(100 CHAR) | Yes | NULL | G.M. | 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 | c1d2e077-eddf-ee11-904d-002248b0ad37 | 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 | SVEN LILLEORU | This is the name of the contact of the company at the time of inspection. | ||
| SITE_CONTACT_TITLE | VARCHAR2(100 CHAR) | Yes | NULL | MANAGER, COMMERCIAL QUALITY & REG EU & CAN | This is the job title of the contact of the company at the time of inspection. | ||
| SITE_CONTACT_PHONE | VARCHAR2(100 CHAR) | Yes | NULL | 416-765-0345 | This is the phone number of the contact of the company at the time of inspection. | ||
| SITE_CONTACT_FAX | VARCHAR2(100 CHAR) | Yes | NULL | 514-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 | sven@quarthealthcare.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 | 36 NORTHLINE ROAD, SUITE 9 | 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 | TORONTO | 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 | M4B 3E2 | 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 | La réunion d'ouverture a eu lieu le 29 mars 2023 à distance (via VidCruiter) en présence de Éric Simard (Vice-Président et Secrétaire Trésorier de 1164250921 Quebec Inc Dba Kasla Entreprise Inc.) et My Din Phan (Inspecteur de Santé Canada). | This is the inspectors summary of the inspection. | ||
| REPORT_DELIVERED_TO_ID | Foreign | VARCHAR2(36 CHAR) | Yes | NULL | c7e5bef2-ecdf-ee11-904c-6045bd60a7a2 | 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 | MARILU ALEJANDRA VALERIANO | 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. Ms. Pace 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 | Jeff Tyndall | 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 | Vanessa Toussaint | 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 | Jeff Tyndall | 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 | Vanessa Toussaint | 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 | Vanessa Toussaint | 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 | Vanessa Toussaint | 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 | ad5e55f6-8b21-ee11-9966-000d3ae86452 | This is the unique record identifier for the CAPA. | |
| INSP_ID | VARCHAR2(36 CHAR) | Foreign | No | NULL | 3d7a309e-ce46-40c5-a6c2-f3ea80971d48 | This is the unique record identifier for the parent inspection. | Use to join with MD_INS_CASE.INSP_ID. |
| INSP_NUM | VARCHAR2(200 CHAR) | Foreign | No | NULL | 00086-BENEMAX DBA. BENEMAX HEALTH AND WELLNESS | This is the parent inspection number. | |
| OBS_ID | VARCHAR2(36 CHAR) | Foreign | No | NULL | 07244e35-fbf1-ee11-a1fe-6045bd6005cc | This is the unique record identifier for the parent observation the CAPA is linked to. | |
| CAPA_TYPE | VARCHAR2(100 CHAR) | Yes | NULL | Follow Up Plan | This is a free text field used to identify the type of CAPA. | ||
| CAPA_PLAN | VARCHAR2(2000 CHAR) | Yes | NULL | The inspector sent follow-up letter to address Physio-controls response--the manufacturer can have more than one ID on the label as long as it clearly marks what the device identifier is and that this identifier matches what is in the MDALL. March 6, 2024 The company sent follow-up from manufacturer. The manufacturer has sent in a fax back to Health Canada to include the device ID (11141-000165) under their license. This adequately addresses the observation. | This is the CAPA as inputted by the inspector. | ||
| CREATEDBYNAME | VARCHAR2(100 CHAR) | Yes | NULL | Jeff Tyndall | This identifies the resource that created the record. | ||
| CREATEDON | DATE | Yes | NULL | 22-08-26 | This identifies the date that record was created. | ||
| MODIFIEDBYNAME | VARCHAR2(100 CHAR) | Yes | NULL | Jeff Tyndall | This identifies the resource that 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 | dca846bf-cc2f-ee11-bdf4-002248b32010 | This is the unique record identifier for the designation. | |
| INSP_ID | VARCHAR2(36 CHAR) | Foreign | No | NULL | 3d7a309e-ce46-40c5-a6c2-f3ea80971d48 | This is the unique record identifier for the parent inspection. | Use to join with MD_INS_CASE.INSP_ID. |
| INSP_NUM | VARCHAR2(200 CHAR) | Foreign | No | NULL | 00070-QUART HEALTHCARE INC. | This is the parent inspection number. | |
| DESIGNATION_ID | VARCHAR2(36 CHAR) | Foreign | No | NULL | ea30c0ef-5214-ee11-9cbe-002248af8a65 | 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 | Jeff Tyndall | This identifies the resource that created the record. | ||
| CREATEDON | DATE | Yes | NULL | 22-08-26 | This identifies the date that record was created. | ||
| MODIFIEDBYNAME | VARCHAR2(100 CHAR) | Yes | NULL | Jeff Tyndall | This identifies the resource that 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 | 7a743745-5fec-ee11-a1fd-002248af8802 | This is the unique record identifier for the establishment activity. | |
| INSP_ID | VARCHAR2(36 CHAR) | Foreign | No | NULL | 4c1aa941-ff7d-4e44-9238-82f4ad624d7e | 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 | 00082-(DR. FARGO)GUANGZHOU HEHOU INFORMATION TECHNOLOGY CO., LTD. | This is the parent inspection number. | |
| ACTIVITY_ID | VARCHAR2(36 CHAR) | Foreign | No | NULL | d7ce3513-4d14-ee11-9cbe-002248af8a65 | 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 | Jeff Tyndall | This identifies the resource that created the record. | ||
| CREATEDON | DATE | Yes | NULL | 22-08-26 | This identifies the date that record was created. | ||
| MODIFIEDBYNAME | VARCHAR2(100 CHAR) | Yes | NULL | Jeff Tyndall | This identifies the resource that 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 | 10cd1617-e303-ef11-9f89-002248b15d0c | This is the unique record identifier for the inspector listing. | |
| INSP_ID | VARCHAR2(36 CHAR) | Foreign | No | NULL | 19c5e07e-63cd-49e6-9cdc-ac0f318f4752 | 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 | 00093-ROCHE DIAGNOSTICS | This is the parent inspection number. | |
| INSPECTOR_NAME | VARCHAR2(100 CHAR) | Yes | NULL | Jeff Tyndall | 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 | Jeff Tyndall | This identifies the resource that created the record. | ||
| CREATEDON | DATE | Yes | NULL | 22-08-26 | This identifies the date that record was created. | ||
| MODIFIEDBYNAME | VARCHAR2(100 CHAR) | Yes | NULL | Jeff Tyndall | This identifies the resource that 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 | d2fa45bc-f953-8e06-8fc1-e116086f174f | This is the unique record identifier for the note. | |
| INSP_ID | VARCHAR2(36 CHAR) | Foreign | No | NULL | 3d7a309e-ce46-40c5-a6c2-f3ea80971d48 | This is the unique record identifier for the parent inspection. | Use to join with MD_INS_CASE.INSP_ID. |
| INSP_NUM | VARCHAR2(200 CHAR) | Foreign | No | NULL | 00027-FUTURE HEALTH TECHNOLOGIES | 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 | Jeff Tyndall | This identifies the resource that created the record. | ||
| CREATEDON | DATE | Yes | NULL | 22-08-26 | This identifies the date that record was created. | ||
| MODIFIEDBYNAME | VARCHAR2(100 CHAR) | Yes | NULL | Jeff Tyndall | This identifies the resource that 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 that categorizes or "tags" an inspection.
| Column Name | DataType | Key Type | Nullable | Data Default | Example Data | Description | Comments |
|---|---|---|---|---|---|---|---|
MD_INS_CASE_WORKFLOW
This table holds information that categorizes or "tags" an inspection.
| Column Name | DataType | Key Type | Nullable | Data Default | Example Data | Description | Comments |
|---|---|---|---|---|---|---|---|
MD_INS_INS_IID
This table holds information that categorizes or "tags" an inspection.
| Column Name | DataType | Key Type | Nullable | Data Default | Example Data | Description | Comments |
|---|---|---|---|---|---|---|---|
MD_INS_INS_INSPECTORLIST
This table holds information that categorizes or "tags" an inspection.
| Column Name | DataType | Key Type | Nullable | Data Default | Example Data | Description | Comments |
|---|---|---|---|---|---|---|---|
MD_INS_INS_IRC_ENFORCEMENT
This table holds information that categorizes or "tags" an inspection.
| Column Name | DataType | Key Type | Nullable | Data Default | Example Data | Description | Comments |
|---|---|---|---|---|---|---|---|
MD_INS_INS_IRC_MEASURES
This table holds information that categorizes or "tags" an inspection.
| Column Name | DataType | Key Type | Nullable | Data Default | Example Data | Description | Comments |
|---|---|---|---|---|---|---|---|
MD_INS_INS_IRC_OBSERVATIONS
This table holds information that categorizes or "tags" an inspection.
| Column Name | DataType | Key Type | Nullable | Data Default | Example Data | Description | Comments |
|---|---|---|---|---|---|---|---|
MD_INS_INS_SITE
This table holds information that categorizes or "tags" an inspection.
| Column Name | DataType | Key Type | Nullable | Data Default | Example Data | Description | Comments |
|---|---|---|---|---|---|---|---|
MD_INS_INS_SITE_CONTACT
This table holds information that categorizes or "tags" an inspection.
| Column Name | DataType | Key Type | Nullable | Data Default | Example Data | Description | Comments |
|---|---|---|---|---|---|---|---|
MD_INS_INS_LICENCE
This table holds information that categorizes or "tags" an inspection.
| Column Name | DataType | Key Type | Nullable | Data Default | Example Data | Description | Comments |
|---|---|---|---|---|---|---|---|
MD_INS_INS_SITELICENCEACTIVITY
This table holds information that categorizes or "tags" an inspection.
| Column Name | DataType | Key Type | Nullable | Data Default | Example Data | Description | Comments |
|---|---|---|---|---|---|---|---|
