Difference between revisions of "MDCCD Data Warehouse Data Dictionary"

Latest revision as of 14:10, 28 August 2024

Overview

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

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

Medical Devices

MD_INS_CASE

Return to Home Page.

This table holds the main inspection information.


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

MD_INS_CASE_CAPA

Return to Home Page.

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


Column Name	DataType	Key Type	Nullable	Data Default	Example Data	Description	Comments
CAPA_ID	VARCHAR2(36 CHAR)	Primary	No	NULL	5c6afa3c-3f8a-4641-8da4-9588ec7769d7	This is the unique record identifier for the CAPA.
INSP_ID	VARCHAR2(36 CHAR)	Foreign	No	NULL	5c6afa3c-3f8a-4641-8da4-9588ec7769d7	This is the unique record identifier for the parent inspection.	Use to join with MD_INS_CASE.INSP_ID.
INSP_NUM	VARCHAR2(200 CHAR)	Foreign	No	NULL	00070-MDCCD HEALTHCARE INC	This is the parent inspection number.
OBS_ID	VARCHAR2(36 CHAR)	Foreign	No	NULL	5c6afa3c-3f8a-4641-8da4-9588ec7769d7	This is the unique record identifier for the parent observation the CAPA is linked to.
CAPA_TYPE	VARCHAR2(100 CHAR)		Yes	NULL	Follow Up Plan	This is a free text field used to identify the type of CAPA.
CAPA_PLAN	VARCHAR2(2000 CHAR)		Yes	NULL	The manufacturer sent an email to the inspector to indicate that the program has been solved.	This is the CAPA as inputted by the inspector.
CREATEDBYNAME	VARCHAR2(100 CHAR)		Yes	NULL	John Smith	This identifies the resource that created the record.
CREATEDON	DATE		Yes	NULL	22-08-26	This identifies the date that record was created.
MODIFIEDBYNAME	VARCHAR2(100 CHAR)		Yes	NULL	John Smith	This identifies the resource that last modified the record.
MODIFIEDON	DATE		Yes	NULL	22-08-26	This identifies the date that record was last modified.

MD_INS_CASE_DESIGNATION

Return to Home Page.

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


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

MD_INS_CASE_ESTACTIVITY

Return to Home Page.

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


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

MD_INS_CASE_INSPECTORS

Return to Home Page.

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


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

MD_INS_CASE_NOTES

Return to Home Page.

This table holds notes related to the inspection.


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

MD_INS_CASE_OBSERVATIONS

Return to Home Page.

This table holds information about the observations for an inspection.


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

MD_INS_INS_CASE_WORKFLOW

Return to Home Page.

This table holds information about the workflow of the inspection.


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

MD_INS_INS_IID

Return to Home Page.

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


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

MD_INS_INS_INSPECTORLIST

Return to Home Page.

This table holds a list of inspectors within the system.


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

MD_INS_INS_IRC_ENFORCEMENT

Return to Home Page.

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


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

MD_INS_INS_IRC_MEASURES

Return to Home Page.

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


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

MD_INS_INS_IRC_OBSERVATIONS

Return to Home Page.

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


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

MD_INS_INS_SITE

Return to Home Page.

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


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

MD_INS_INS_SITE_CONTACT

Return to Home Page.

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


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

MD_INS_INS_LICENCE

Return to Home Page.

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


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

MD_INS_INS_SITELICENCEACTIVITY

Return to Home Page.

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


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

Difference between revisions of "MDCCD Data Warehouse Data Dictionary"

Latest revision as of 14:10, 28 August 2024

Contents

Overview

Medical Devices

MD_INS_CASE

MD_INS_CASE_CAPA

MD_INS_CASE_DESIGNATION

MD_INS_CASE_ESTACTIVITY

MD_INS_CASE_INSPECTORS

MD_INS_CASE_NOTES

MD_INS_CASE_OBSERVATIONS

MD_INS_INS_CASE_WORKFLOW

MD_INS_INS_IID

MD_INS_INS_INSPECTORLIST

MD_INS_INS_IRC_ENFORCEMENT

MD_INS_INS_IRC_MEASURES

MD_INS_INS_IRC_OBSERVATIONS

MD_INS_INS_SITE

MD_INS_INS_SITE_CONTACT

MD_INS_INS_LICENCE

MD_INS_INS_SITELICENCEACTIVITY

Navigation menu

Search

@@ Line 1: / Line 1: @@
+{{Delete|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 ===
+[[MDCCD Data Warehouse|Return to Home Page.]]
 This table holds the main inspection information.
-{| class="wikitable sortable"
+{| class="wikitable sortable mw-collapsible"
 |+
-! scope="col" style="width: 350px;" |Column Name
+! scope="col" style="width: 350px;" | Column Name
 ! scope="col" style="width: 100px;" |DataType
 ! scope="col" style="width: 50px;" |Key Type
@@ Line 14: / Line 19: @@
 ! 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;" | Description
 ! scope="col" style="width: 350px;" |Comments
 |-
@@ Line 31: / Line 36: @@
 |No
 |NULL
-|00070-QUART HEALTHCARE INC.
+|00070-MDCCD HEALTHCARE INC
 |This is the inspection number.
 |
@@ Line 40: / Line 45: @@
 |No
 |NULL
-|7b06cddd-12d5-ee11-904d-002248b0a560
+|5c6afa3c-3f8a-4641-8da4-9588ec7769d7
 |This is the unique record identifier for the company inspected.
 |
@@ Line 46: / Line 51: @@
 |SITE_NAME
 |
-|VARCHAR2(160 CHAR)
+| VARCHAR2(160 CHAR)
 |Yes
 |NULL
-|QUART HEALTHCARE INC.
+|MDCCD HEALTHCARE INC
 |This is the name of the company insepcted.
 |
 |-
-| LICENCE_NO
+|LICENCE_NO
-|Foreign
+| Foreign
 |NUMBER(6,0)
 |Yes
 |NULL
-|2027
+| 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.
 |-
@@ Line 67: / Line 72: @@
 |Yes
 |NULL
-|107232
+|123456
 |This is the internal Health Canada company identifier.
 |Use to join with MDS table ESTABLISHMENT.COMPANY_ID or COMPANY.COMPANY_ID.
@@ Line 73: / Line 78: @@
 |REG_FILE_NUMBER
 |
-| VARCHAR2(250 BYTE)
+|VARCHAR2(250 BYTE)
 |Yes
 |NULL
-| HC6-61-123/IM
+|HC1-11-11IM
-| This is the internal program based file identifier.
+|This is the internal program based file identifier.
 |
 |-
 |REGION_CD_EN
 |
-|VARCHAR2(50 CHAR)
+| VARCHAR2(50 CHAR)
-|Yes
+| Yes
 |NULL
 |East
@@ Line 94: / Line 99: @@
 |Yes
 |NULL
-| Est
+|Est
 | This is the operational centre assigned to the inspection in French.
 |
@@ Line 100: / Line 105: @@
 |INSP_TYPE_EN
 |
-|VARCHAR2(100 CHAR)
+| VARCHAR2(100 CHAR)
-|Yes
+| Yes
-|NULL
+| NULL
 |Domestic - New - Onsite
 |This is the type of inspection in English.
@@ Line 112: / Line 117: @@
 |Yes
 |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
 |NULL
-|Vanessa Toussaint
+| John Smith
 |This is the name of the lead inspector.
 |
@@ Line 136: / Line 141: @@
 |LEAD_INSPECTOR_BADGE
 |
-|VARCHAR2(50 BYTE)
+| VARCHAR2(50 BYTE)
 |Yes
-|NULL
+| NULL
-|54355335
+|9988776
 |This is the badge number of the lead inspector.
 |
@@ Line 145: / Line 150: @@
 |SUPERVISOR_ID
 |Foreign
-| VARCHAR2(36 CHAR)
+|VARCHAR2(36 CHAR)
 |Yes
 |NULL
-|ed91a4cf-777a-ee11-8179-002248b32010
+|5c6afa3c-3f8a-4641-8da4-9588ec7769d7
 |This is the unique record identifier of the supervisor for the inspection.
 |
@@ Line 154: / Line 159: @@
 |SUPERVISOR
 |
-|VARCHAR2(200 CHAR)
+| VARCHAR2(200 CHAR)
 |Yes
 |NULL
-|Ada Lai
+| John Smith
 |This is the name of the supervisor for the inspection.
 |
@@ Line 167: / Line 172: @@
 |NULL
 |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)
 |Yes
 |NULL
-|Ouvert
+| Ouvert
 |This is the status of the inspection in French.
 |
@@ Line 190: / Line 195: @@
 |COMP_STANDARD_FR
 |
-| VARCHAR2(100 CHAR)
+|VARCHAR2(100 CHAR)
 |Yes
 |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.
 |
@@ Line 199: / Line 204: @@
 |DRAFT_REPORT_RATING_EN
 |
-|VARCHAR2(100 CHAR)
+| VARCHAR2(100 CHAR)
 |Yes
-| NULL
+|NULL
 |Compliant
 |This is the draft inspection rating in English.
@@ Line 210: / Line 215: @@
 |VARCHAR2(100 CHAR)
 |Yes
-| NULL
+|NULL
 |Conforme
 |This is the draft inspection rating in French.
@@ Line 219: / Line 224: @@
 |DATE
 |Yes
-|NULL
+| NULL
 |24-03-21
 |This is the date that the draft inspection rating was issued.
@@ Line 226: / Line 231: @@
 |FINAL_REPORT_RATING_EN
 |
-|VARCHAR2(100 CHAR)
+| VARCHAR2(100 CHAR)
 |Yes
-| NULL
+|NULL
 |Compliant
 |This is the final inspection rating in English.
@@ Line 244: / Line 249: @@
 |FINAL_REPORT_ISSUE_DATE
 |
-| DATE
+|DATE
-|Yes
+| Yes
 |NULL
-| 24-03-21
+|24-03-21
 |This is the date that the final inspection rating was issued.
 |
@@ Line 255: / Line 260: @@
 |DATE
 |Yes
-| NULL
+|NULL
 |24-03-22
 |This is the date that an amended inspection report was issued to the company.
@@ Line 263: / Line 268: @@
 |
 |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
 |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
 |
-|DATE
+| DATE
 |Yes
-| NULL
+|NULL
 |24-01-30
 |This indicates when the inspection was finished.
@@ Line 292: / Line 297: @@
 |Yes
 |NULL
-|23-11-15
+| 23-11-15
 |This indicates when the inspector had the closing meeting with the company.
 |
@@ Line 298: / Line 303: @@
 |NEXT_INSPECTION_DATE
 |
-|DATE
+| DATE
-| Yes
+|Yes
-|NULL
+| NULL
 |24-03-15
 |This indicates when the company is due for its next inspection
@@ Line 308: / Line 313: @@
 |
 |VARCHAR2(5 CHAR)
-| Yes
+|Yes
 |NULL
 |Yes
@@ Line 328: / Line 333: @@
 |Yes
 |NULL
-|Vanessa Toussaint
+|John Smith
 |This indicates who amended the inspection.
 |
 |-
-| AMENDED_DATE
+|AMENDED_DATE
 |
-|DATE
+| DATE
 |Yes
 | NULL
@@ Line 344: / Line 349: @@
 |
 |DATE
-| Yes
+|Yes
 |NULL
 |24-02-21
@@ Line 368: / Line 373: @@
 |
 |-
-| FIRST_COMM_MODE_FR
+|FIRST_COMM_MODE_FR
 |
 |VARCHAR2(100 CHAR)
@@ Line 379: / Line 384: @@
 |EMERGENCY_CONTACT_NAME
 |
-|VARCHAR2(100 CHAR)
+| VARCHAR2(100 CHAR)
 |Yes
 |NULL
@@ Line 391: / Line 396: @@
 |Yes
 |NULL
-|514-123-4566
+|613-123-4567
-| This is the phone number of the emergency contact for the company at the time of inspection.
+|This is the phone number of the emergency contact for the company at the time of inspection.
 |
 |-
 |SENIOR_OFFICIAL_OFFICIAL_ID
-| Foreign
+|Foreign
 |VARCHAR2(36 CHAR)
-| Yes
+|Yes
 |NULL
-|31175da9-efdf-ee11-904c-000d3a847fce
+|5c6afa3c-3f8a-4641-8da4-9588ec7769d7
 |This is the unique record identifier of the senior official of the company at the time of insepction.
 |
@@ Line 409: / Line 414: @@
 |Yes
 |NULL
-|SVEN LILLEDRU
+|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)
 |Yes
-|NULL
+| NULL
-|G.M.
+|General Manager
 |This is the job title of the senior official for the company at the time of inspection.
 |-
@@ Line 426: / Line 431: @@
 |Yes
 |NULL
-|c1d2e077-eddf-ee11-904d-002248b0ad37
+|5c6afa3c-3f8a-4641-8da4-9588ec7769d7
 |This is the unique record identifier of the contact of the company at the time of inspection.
 |
@@ Line 435: / Line 440: @@
 |Yes
 |NULL
-|SVEN LILLEORU
+|John Smith
 |This is the name of the contact of the company at the time of inspection.
 |
@@ Line 442: / Line 447: @@
 |
 |VARCHAR2(100 CHAR)
-|Yes
+| Yes
 |NULL
-|MANAGER, COMMERCIAL QUALITY & REG EU & CAN
+|Manger, Quality Assurance
 |This is the job title of the contact of the company at the time of inspection.
 |
@@ Line 452: / Line 457: @@
 |VARCHAR2(100 CHAR)
 |Yes
-|NULL
+| NULL
-|416-765-0345
+|613-123-4567
 |This is the phone number of the contact of the company at the time of inspection.
 |-
@@ Line 459: / Line 464: @@
 |
 |VARCHAR2(100 CHAR)
-|Yes
+| Yes
 |NULL
-|514-123-4567
+| 613-123-4567
 |This is the fax of the contact of the company at the time of inspection.
 |
@@ Line 470: / Line 475: @@
 |Yes
 |NULL
-|sven@quarthealthcare.com
+|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
 |NULL
-|36 NORTHLINE ROAD, SUITE 9
+| 123 Main St
 |This is the address of the company being inspected.
 |
@@ Line 488: / Line 493: @@
 |Yes
 |NULL
-|Suite 101
+| Suite 101
 |This is secondary the address of the company being inspected.
 |
 |-
-| SITE_ADDRESS_3
+|SITE_ADDRESS_3
 |
 |VARCHAR2(250 CHAR)
@@ Line 506: / Line 511: @@
 |Yes
 |NULL
-|TORONTO
+|OTTAWA
 |This is the city of the company being inspected.
 |-
@@ Line 518: / Line 523: @@
 |
 |-
-| SITE_POSTALCODE
+|SITE_POSTALCODE
 |
 |VARCHAR2(20 CHAR)
-| Yes
+|Yes
 | NULL
-|M4B 3E2
+|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
-|Yes
+| 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).
+|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.
 |
@@ Line 550: / Line 555: @@
 |Yes
 |NULL
-| c7e5bef2-ecdf-ee11-904c-6045bd60a7a2
+|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
+|REPORT_DELIVERED_TO
 |
-|VARCHAR2(200 CHAR)
+| VARCHAR2(200 CHAR)
 |Yes
 |NULL
-|MARILU ALEJANDRA VALERIANO
+| Mohamed Ali
 |This is the name of the contact of the company who received the final inspection report.
 |
@@ Line 566: / Line 571: @@
 |
 |DATE
-| Yes
+|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.
+|This is the date that Health Canada is expecting a response from the company about information captured in the final inspection report.
 |
 |-
@@ Line 577: / Line 582: @@
 |Yes
 |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.
 |
 |-
-|PERSONNEL_CHANGES
+| PERSONNEL_CHANGES
 |
 |VARCHAR2(2000 BYTE)
 |Yes
 | NULL
-|Change in Regulatory affairs department. Ms. Pace now in charge of complaint handling.
+|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.
 |
@@ Line 593: / Line 598: @@
 |
 |VARCHAR2(50 CHAR)
-| Yes
+|Yes
 |NULL
-| In Progress
+|In Progress
 |This indicates the status of the IID in English.
 |DHPID Transparency IID
 |-
-|IID_STATUS_FR
+| IID_STATUS_FR
 |
 |VARCHAR2(50 CHAR)
 |Yes
 |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
 |-
@@ Line 613: / Line 618: @@
 |Yes
 |NULL
-|24-03-15
+| 24-03-15
 |This indicates the status date of the IID.
-| DHPID Transparency IID
+|DHPID Transparency IID
 |-
 |IID_REVISION_LOG
@@ Line 623: / Line 628: @@
 |NULL
 |Updated deficiency.
-|This indicates any revisions carried out to the IID.
+| This indicates any revisions carried out to the IID.
 |DHPID Transparency IID
 |-
@@ Line 630: / Line 635: @@
 |VARCHAR2(50 CHAR)
 |Yes
-| NULL
+|NULL
 |IRC Ready For Publication
 |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
 |NULL
@@ Line 673: / Line 678: @@
 |DHPID Transparency IRC
 |-
-| INSP_OUTCOME_FR
+|INSP_OUTCOME_FR
 |
 |VARCHAR2(2000 CHAR)
-| Yes
+|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
@@ Line 682: / Line 687: @@
 |DHPID Transparency IRC
 |-
-| INSP_REPORT_WAITING
+|INSP_REPORT_WAITING
 |
 |VARCHAR2(200 CHAR)
 |Yes
 |NULL
-|Jeff Tyndall
+|John Smith
 |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
 |
-|DATE
+| DATE
 |Yes
 |NULL
@@ Line 705: / Line 710: @@
 |Yes
 |NULL
-|Vanessa Toussaint
+|John Smith
 |This is the supervisor's name who approved the inspection report in the record log.
 |
@@ Line 711: / Line 716: @@
 |INSP_REPORT_APPROVED_BY_DATE
 |
-|DATE
+| DATE
 |Yes
-|NULL
+| NULL
 |23-11-27
 |This is the date the supervisor approved the inspection report in the record log.
 |
 |-
-| INSP_CLOSED_WAITING
+|INSP_CLOSED_WAITING
 |
 |VARCHAR2(200 CHAR)
 |Yes
-| NULL
+|NULL
-|Jeff Tyndall
+|John Smith
 |This is the inspector's name who sent the inspection for closure in the record log.
 |-
 |INSP_CLOSED_WAITING_DATE
 |
-|DATE
+| DATE
 |Yes
 |NULL
@@ Line 740: / Line 745: @@
 |Yes
 |NULL
-|Vanessa Toussaint
+|John Smith
 |This is the supervisor's name who approved the inspection for closure in the record log.
 |
@@ Line 749: / Line 754: @@
 |Yes
 |NULL
-| 23-11-29
+|23-11-29
 |This is the date the supervisor approved the inspection for closure in the record log.
 |
@@ Line 758: / Line 763: @@
 |Yes
 |NULL
-| Vanessa Toussaint
+|John Smith
 |This identifies the resource that created the record.
 |
@@ Line 771: / Line 776: @@
 |
 |-
-| MODIFIEDBYNAME
+|MODIFIEDBYNAME
 |
 |VARCHAR2(100 CHAR)
 |Yes
 |NULL
-|Vanessa Toussaint
+| 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===
+[[MDCCD Data Warehouse|Return to Home Page.]]
 This table holds information about the corrective action plan for an inspection observation.
-{| class="wikitable sortable"
+{| class="wikitable sortable mw-collapsible"
 |+
-! scope="col" style="width: 350px;" |Column Name
+! scope="col" style="width: 350px;" | Column Name
 ! scope="col" style="width: 100px;" |DataType
 ! scope="col" style="width: 50px;" |Key Type
@@ Line 800: / Line 807: @@
 ! 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;" | Description
 ! scope="col" style="width: 350px;" |Comments
 |-
-|CAPA_ID
+| CAPA_ID
 |VARCHAR2(36 CHAR)
 |Primary
 |No
 |NULL
-| ad5e55f6-8b21-ee11-9966-000d3ae86452
+|5c6afa3c-3f8a-4641-8da4-9588ec7769d7
 |This is the unique record identifier for the CAPA.
 |
@@ Line 815: / Line 822: @@
 |VARCHAR2(36 CHAR)
 |Foreign
-| No
+|No
 |NULL
-|3d7a309e-ce46-40c5-a6c2-f3ea80971d48
+|5c6afa3c-3f8a-4641-8da4-9588ec7769d7
 |This is the unique record identifier for the parent inspection.
 |Use to join with MD_INS_CASE.INSP_ID.
@@ Line 824: / Line 831: @@
 |VARCHAR2(200 CHAR)
 |Foreign
-| No
+|No
 |NULL
-|00086-BENEMAX DBA. BENEMAX HEALTH AND WELLNESS
+|00070-MDCCD HEALTHCARE INC
 |This is the parent inspection number.
 |
@@ Line 835: / Line 842: @@
 |No
 |NULL
-|07244e35-fbf1-ee11-a1fe-6045bd6005cc
+|5c6afa3c-3f8a-4641-8da4-9588ec7769d7
 |This is the unique record identifier for the parent observation the CAPA is linked to.
 |
 |-
 |CAPA_TYPE
-| VARCHAR2(100 CHAR)
+|VARCHAR2(100 CHAR)
 |
 |Yes
-|NULL
+| NULL
 |Follow Up Plan
 |This is a free text field used to identify the type of CAPA.
@@ Line 849: / Line 856: @@
 |-
 |CAPA_PLAN
-|VARCHAR2(2000 CHAR)
+| 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.
+|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.
 |
@@ Line 862: / Line 869: @@
 |Yes
 |NULL
-|Jeff Tyndall
+| John Smith
 |This identifies the resource that created the record.
 |
@@ Line 880: / Line 887: @@
 |Yes
 |NULL
-|Jeff Tyndall
+| 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===
+[[MDCCD Data Warehouse|Return to Home Page.]]
 This table holds information that categorizes or "tags" an inspection. These are flexible codes used to capture information if a designated field does not exist.
-{| class="wikitable sortable"
+{| class="wikitable sortable mw-collapsible"
 |+
-! scope="col" style="width: 350px;" |Column Name
+! scope="col" style="width: 350px;" | Column Name
 ! scope="col" style="width: 100px;" |DataType
 ! scope="col" style="width: 50px;" |Key Type
@@ Line 904: / Line 913: @@
 ! 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;" | Description
 ! scope="col" style="width: 350px;" |Comments
 |-
-| INSP_DESIGNATION_ID
+|INSP_DESIGNATION_ID
 |VARCHAR2(36 CHAR)
 |Primary
 |No
 |NULL
-|dca846bf-cc2f-ee11-bdf4-002248b32010
+|5c6afa3c-3f8a-4641-8da4-9588ec7769d7
 |This is the unique record identifier for the designation.
 |
@@ Line 921: / Line 930: @@
 |No
 |NULL
-|3d7a309e-ce46-40c5-a6c2-f3ea80971d48
+|5c6afa3c-3f8a-4641-8da4-9588ec7769d7
-| This is the unique record identifier for the parent inspection.
+|This is the unique record identifier for the parent inspection.
 |Use to join with MD_INS_CASE.INSP_ID.
 |-
@@ Line 930: / Line 939: @@
 |No
 |NULL
-|00070-QUART HEALTHCARE INC.
+| 00070-MDCCD HEALTHCARE INC
 |This is the parent inspection number.
 |
@@ Line 939: / Line 948: @@
 |No
 |NULL
-|ea30c0ef-5214-ee11-9cbe-002248af8a65
+|5c6afa3c-3f8a-4641-8da4-9588ec7769d7
 |This is the unique record identifier for the designation code.
 |
 |-
 |DESIGNATION_CODE
-|VARCHAR2(100 CHAR)
+| VARCHAR2(100 CHAR)
 |
 |Yes
-| NULL
+|NULL
 |5006
 |This is the designation code.
 |
 |-
-| DESIGNATION_NAME
+|DESIGNATION_NAME
-|VARCHAR2(250 CHAR)
+| VARCHAR2(250 CHAR)
 |
 |Yes
@@ Line 965: / Line 974: @@
 |Yes
 |NULL
-| Covid
+|Covid
 |This is the name of the designation code in English.
 |
 |-
 |DESIGNATION_DECODE_FR
-|VARCHAR2(100 CHAR)
+| VARCHAR2(100 CHAR)
 |
 |Yes
 |NULL
-|Covid
+| Covid
 |This is the name of the designation code in French.
 |
@@ Line 983: / Line 992: @@
 |Yes
 |NULL
-|Jeff Tyndall
+| John Smith
 |This identifies the resource that created the record.
 |
 |-
-| CREATEDON
+|CREATEDON
 |DATE
 |
@@ Line 1,001: / Line 1,010: @@
 |Yes
 |NULL
-|Jeff Tyndall
+| John Smith
-|This identifies the resource that last modified the record.
+| This identifies the resource that last modified the record.
 |
 |-
@@ Line 1,016: / Line 1,025: @@
 ===MD_INS_CASE_ESTACTIVITY===
+[[MDCCD Data Warehouse|Return to Home Page.]]
 This table holds information on the activities conducted by the company at the time of the inspection as recorded by the inspector.
-{| class="wikitable sortable"
+{| class="wikitable sortable mw-collapsible"
 |+
-! scope="col" style="width: 350px;" |Column Name
+! scope="col" style="width: 350px;" | Column Name
 ! scope="col" style="width: 100px;" |DataType
 ! scope="col" style="width: 50px;" |Key Type
@@ Line 1,025: / Line 1,036: @@
 ! 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;" | Description
 ! scope="col" style="width: 350px;" |Comments
 |-
@@ Line 1,033: / Line 1,044: @@
 |No
 |NULL
-|7a743745-5fec-ee11-a1fd-002248af8802
+|5c6afa3c-3f8a-4641-8da4-9588ec7769d7
 |This is the unique record identifier for the establishment activity.
 |
@@ Line 1,042: / Line 1,053: @@
 |No
 |NULL
-|4c1aa941-ff7d-4e44-9238-82f4ad624d7e
+|5c6afa3c-3f8a-4641-8da4-9588ec7769d7
 |This is the unique record identifier for the parent inspection.
 |Use to join with MD_INS_CASE.INSP_ID.
@@ Line 1,051: / Line 1,062: @@
 |No
 |NULL
-|00082-(DR. FARGO)GUANGZHOU HEHOU INFORMATION TECHNOLOGY CO., LTD.
+|00070-MDCCD HEALTHCARE INC
 |This is the parent inspection number.
 |
@@ Line 1,060: / Line 1,071: @@
 |No
 |NULL
-|d7ce3513-4d14-ee11-9cbe-002248af8a65
+|5c6afa3c-3f8a-4641-8da4-9588ec7769d7
 |This is the unique record identifier for the activity value.
 |
 |-
 |ACTIVITY_NAME
-|VARCHAR2(205 CHAR)
+| VARCHAR2(205 CHAR)
 |
-|Yes
+| Yes
-| NULL
+|NULL
 |Importateur - Classe 2
 |This is the establishment activity.
@@ Line 1,076: / Line 1,087: @@
 |VARCHAR2(100 CHAR)
 |
-|Yes
+| Yes
 |NULL
 |Importer - Class 2
@@ Line 1,082: / Line 1,093: @@
 |
 |-
-| ACTIVITY_DECODE_FR
+|ACTIVITY_DECODE_FR
 |VARCHAR2(100 CHAR)
 |
 |Yes
 |NULL
-|Importateur - Classe 2
+| Importateur - Classe 2
 |This is the name of the establishment activity in French.
 |
@@ Line 1,096: / Line 1,107: @@
 |Yes
 |NULL
-|Jeff Tyndall
+| John Smith
 |This identifies the resource that created the record.
 |
@@ Line 1,103: / Line 1,114: @@
 |DATE
 |
-| Yes
+|Yes
 |NULL
 |22-08-26
@@ Line 1,114: / Line 1,125: @@
 |Yes
 |NULL
-|Jeff Tyndall
+| John Smith
-|This identifies the resource that last modified the record.
+| This identifies the resource that last modified the record.
 |
 |-
@@ Line 1,128: / Line 1,139: @@
 |}
-=== MD_INS_CASE_INSPECTORS===
+===MD_INS_CASE_INSPECTORS===
+[[MDCCD Data Warehouse|Return to Home Page.]]
 This table holds a listing of inspectors assigned to an inspection.
-{| class="wikitable sortable"
+{| class="wikitable sortable mw-collapsible"
 |+
-! scope="col" style="width: 350px;" |Column Name
+! scope="col" style="width: 350px;" | Column Name
 ! scope="col" style="width: 100px;" |DataType
 ! scope="col" style="width: 50px;" |Key Type
@@ Line 1,138: / Line 1,151: @@
 ! 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;" | Description
 ! scope="col" style="width: 350px;" |Comments
 |-
@@ Line 1,146: / Line 1,159: @@
 |No
 |NULL
-|10cd1617-e303-ef11-9f89-002248b15d0c
+|5c6afa3c-3f8a-4641-8da4-9588ec7769d7
 |This is the unique record identifier for the inspector listing.
 |
@@ Line 1,155: / Line 1,168: @@
 |No
 |NULL
-|19c5e07e-63cd-49e6-9cdc-ac0f318f4752
+|5c6afa3c-3f8a-4641-8da4-9588ec7769d7
 |This is the unique record identifier for the parent inspection.
 |Use to join with MD_INS_CASE.INSP_ID.
@@ Line 1,164: / Line 1,177: @@
 |No
 |NULL
-|00093-ROCHE DIAGNOSTICS
+|00070-MDCCD HEALTHCARE INC
 |This is the parent inspection number.
 |
@@ Line 1,172: / Line 1,185: @@
 |
 |Yes
-| NULL
+|NULL
-|Jeff Tyndall
+|John Smith
 |This is the name of the inspector.
 |
@@ Line 1,180: / Line 1,193: @@
 |VARCHAR2(3 CHAR)
 |
-|Yes
+| Yes
 |NULL
 |Yes
@@ Line 1,189: / Line 1,202: @@
 |VARCHAR2(3 CHAR)
 |
-|Yes
+| Yes
 |NULL
 |Oui
@@ Line 1,199: / Line 1,212: @@
 |
 |Yes
-| NULL
+|NULL
 |Scheduled
 |**Not used by program.**
@@ Line 1,207: / Line 1,220: @@
 |VARCHAR2(100 CHAR)
 |
-|Yes
+| Yes
 |NULL
 |
@@ Line 1,236: / Line 1,249: @@
 |Yes
 |NULL
-|Jeff Tyndall
+| John Smith
 |This identifies the resource that created the record.
 |
@@ Line 1,252: / Line 1,265: @@
 |VARCHAR2(100 CHAR)
 |
-| Yes
+|Yes
 |NULL
-|Jeff Tyndall
+| John Smith
-|This identifies the resource that last modified the record.
+| This identifies the resource that last modified the record.
 |
 |-
@@ Line 1,269: / Line 1,282: @@
 ===MD_INS_CASE_NOTES===
+[[MDCCD Data Warehouse|Return to Home Page.]]
 This table holds notes related to the inspection.
-{| class="wikitable sortable"
+{| class="wikitable sortable mw-collapsible"
 |+
-! scope="col" style="width: 350px;" |Column Name
+! scope="col" style="width: 350px;" | Column Name
 ! scope="col" style="width: 100px;" |DataType
 ! scope="col" style="width: 50px;" |Key Type
@@ Line 1,278: / Line 1,293: @@
 ! 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;" | Description
 ! scope="col" style="width: 350px;" |Comments
 |-
@@ Line 1,286: / Line 1,301: @@
 |No
 |NULL
-|d2fa45bc-f953-8e06-8fc1-e116086f174f
+|5c6afa3c-3f8a-4641-8da4-9588ec7769d7
 |This is the unique record identifier for the note.
 |
@@ Line 1,295: / Line 1,310: @@
 |No
 |NULL
-|3d7a309e-ce46-40c5-a6c2-f3ea80971d48
+|5c6afa3c-3f8a-4641-8da4-9588ec7769d7
 |This is the unique record identifier for the parent inspection.
 |Use to join with MD_INS_CASE.INSP_ID.
@@ Line 1,303: / Line 1,318: @@
 |Foreign
 |No
-|NULL
+| NULL
-|00027-FUTURE HEALTH TECHNOLOGIES
+|00070-MDCCD HEALTHCARE INC
 |This is the parent inspection number.
 |
@@ Line 1,317: / Line 1,332: @@
 |
 |-
-|NOTETEXT
+| NOTETEXT
 |CLOB
 |
 |Yes
 |NULL
-|Communication with company on 2023-09-01.
+| Communication with company on 2023-09-01.
 |This is the body of the note.
 |
@@ Line 1,331: / Line 1,346: @@
 |Yes
 |NULL
-|Jeff Tyndall
+| John Smith
 |This identifies the resource that created the record.
 |
@@ Line 1,347: / Line 1,362: @@
 |VARCHAR2(100 CHAR)
 |
-| Yes
+|Yes
 |NULL
-|Jeff Tyndall
+| John Smith
-|This identifies the resource that last modified the record.
+| This identifies the resource that last modified the record.
 |
 |-
@@ Line 1,364: / Line 1,379: @@
 ===MD_INS_CASE_OBSERVATIONS===
-This table holds information that categorizes or "tags" an inspection.
+[[MDCCD Data Warehouse|Return to Home Page.]]
-{| class="wikitable sortable"
+This table holds information about the observations for an inspection.
+{| class="wikitable sortable mw-collapsible"
 |+
-! scope="col" style="width: 350px;" |Column Name
+! scope="col" style="width: 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;" |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: 350px;" |Description
+! 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.
 |
-|}
-=== MD_INS_CASE_WORKFLOW===
-This table holds information that categorizes or "tags" an inspection.
-{| class="wikitable sortable"
-|+
-! scope="col" style="width: 350px;" |Column Name
-! scope="col" style="width: 100px;" |DataType
-! scope="col" style="width: 50px;" |Key Type
-! scope="col" style="width: 50px;" |Nullable
-! scope="col" style="width: 50px;" |Data Default
-! scope="col" style="width: 500px;" |Example Data
-! scope="col" style="width: 350px;" |Description
-! scope="col" style="width: 350px;" |Comments
 |-
+|OBS_STATUS_EN
+|VARCHAR2(50 CHAR)
 |
+|Yes
+|NULL
+|Open
+|This indicates the status of the observation in English.
+|-
+|OBS_STATUS_FR
+|VARCHAR2(50 CHAR)
 |
+|Yes
+|NULL
+|Ouvert
+|This indicates the status of the observation in French.
 |
+|-
+|OBS_INCLUDEREPORT_EN
+|VARCHAR2(3 CHAR)
 |
-|
+|Yes
-|
+|NULL
+|Yes
+|This indicates whether the observation is included on the inspection report in English.
 |
 |-
+|OBS_INCLUDEREPORT_FR
+|VARCHAR2(3 CHAR)
 |
+|Yes
+|NULL
+|Oui
+|This indicates whether the observation is included on the inspection report in French.
 |
+|-
+|OBS_SEQ_NUM
+|NUMBER(3,0)
 |
+|Yes
+|NULL
+|1
+|This indicates the order that the observations will print on the inspection report.
 |
+|-
+|OBS_STDLINE_EN
+|VARCHAR2(1000 CHAR)
 |
-|
+|Yes
+|NULL
+|A written procedure was not provided.
+|This is a standard line related to the section of the regulation cited in English.
 |
 |-
+|OBS_STDLINE_FR
+|VARCHAR2(1000 CHAR)
 |
+|Yes
+|NULL
+|Une procédure écrite n'avait pas été fournie
+|This is a standard line related to the section of the regulation cited in French.
 |
+|-
+|OBS_NARRATIVE
+|VARCHAR2(4000 CHAR)
 |
+|Yes
+|NULL
+|/*A written procedure was not provided. */
+The company did not have a written procedure in place for complaint handling.
+|This is the inspector's narrative related to the observation.
 |
+|-
+|OBS_SUMMARY
+|VARCHAR2(2000 CHAR)
 |
-|
+|Yes
-|
+|NULL
-|}
+|Observation #: 01
+Status / Statut : Open / Ouvert
+Risk / Risque :  1
-===MD_INS_INS_IID===
+MDR - Establishment licence  / RIM - Licence d’établissement
-This table holds information that categorizes or "tags" an inspection.
-{| class="wikitable sortable"
+MDR s.45 (f) Device classes / MDR s.45 (f) Classes des instruments
-|+
-! scope="col" style="width: 350px;" |Column Name
-! scope="col" style="width: 100px;" |DataType
+/*A written procedure was not provided. */
-! scope="col" style="width: 50px;" |Key Type
-! scope="col" style="width: 50px;" |Nullable
-! scope="col" style="width: 50px;" |Data Default
+The company did not have a written procedure in place for complaint handling.
-! scope="col" style="width: 500px;" |Example Data
+|This is the overall summary of the observation that built from the above columns.
-! scope="col" style="width: 350px;" |Description
+|
-! scope="col" style="width: 350px;" |Comments
 |-
+|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_INSPECTORLIST===
+===MD_INS_INS_CASE_WORKFLOW===
-This table holds information that categorizes or "tags" an inspection.
+[[MDCCD Data Warehouse|Return to Home Page.]]
-{| class="wikitable sortable"
+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: 350px;" | Column Name
 ! 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;" |Data Default
@@ Line 1,490: / Line 1,639: @@
 ! 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
-===MD_INS_INS_IRC_ENFORCEMENT===
+|22-08-26
-This table holds information that categorizes or "tags" an inspection.
+|This identifies the date that record was created.
-{| class="wikitable sortable"
+|-
+|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: 350px;" | Column Name
 ! 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;" |Data Default
@@ Line 1,551: / Line 1,804: @@
 |
 |
-|}
+|
+|-
-===MD_INS_INS_IRC_MEASURES===
+|
-This table holds information that categorizes or "tags" an inspection.
+|
-{| class="wikitable sortable"
+|
-|+
+|
-! 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,589: / Line 1,837: @@
 |
 |
-|}
-===MD_INS_INS_IRC_OBSERVATIONS===
-This table holds information that categorizes or "tags" an inspection.
-{| class="wikitable sortable"
-|+
-! 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,621: / Line 1,855: @@
 |-
 |
 |
 |
 |
 |
 |
 |
-|}
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|}
+===MD_INS_INS_INSPECTORLIST===
+[[MDCCD Data Warehouse|Return to Home Page.]]
+This table holds a list of inspectors within the system.
+{| class="wikitable sortable mw-collapsible"
+|+
+! scope="col" style="width: 350px;" | Column Name
+! scope="col" style="width: 100px;" |DataType
+! scope="col" style="width: 50px;" | Key Type
+! scope="col" style="width: 50px;" |Nullable
+! scope="col" style="width: 50px;" |Data Default
+! scope="col" style="width: 500px;" |Example Data
+! scope="col" style="width: 350px;" |Description
+! scope="col" style="width: 350px;" |Comments
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|}
+===MD_INS_INS_IRC_ENFORCEMENT===
+[[MDCCD Data Warehouse|Return to Home Page.]]
+This table holds the list of enforcement actions taken by Health Canada for the Inspection Report Card posted on the DHPID.
+{| class="wikitable sortable mw-collapsible"
+|+
+! scope="col" style="width: 350px;" | Column Name
+! scope="col" style="width: 100px;" |DataType
+! scope="col" style="width: 50px;" | Key Type
+! scope="col" style="width: 50px;" |Nullable
+! scope="col" style="width: 50px;" |Data Default
+! scope="col" style="width: 500px;" |Example Data
+! scope="col" style="width: 350px;" |Description
+! scope="col" style="width: 350px;" |Comments
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|}
+===MD_INS_INS_IRC_MEASURES===
+[[MDCCD Data Warehouse|Return to Home Page.]]
+This table holds the list of corrective measures taken by Health Canada for the Inspection Report Card posted on the DHPID.
+{| class="wikitable sortable mw-collapsible"
+|+
+! scope="col" style="width: 350px;" | Column Name
+! scope="col" style="width: 100px;" |DataType
+! scope="col" style="width: 50px;" | Key Type
+! scope="col" style="width: 50px;" |Nullable
+! scope="col" style="width: 50px;" |Data Default
+! scope="col" style="width: 500px;" |Example Data
+! scope="col" style="width: 350px;" |Description
+! scope="col" style="width: 350px;" |Comments
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|}
+===MD_INS_INS_IRC_OBSERVATIONS===
+[[MDCCD Data Warehouse|Return to Home Page.]]
+This table holds the list of observations for the Inspection Report Card posted on the DHPID.
+{| class="wikitable sortable mw-collapsible"
+|+
+! scope="col" style="width: 350px;" | Column Name
+! scope="col" style="width: 100px;" |DataType
+! scope="col" style="width: 50px;" | Key Type
+! scope="col" style="width: 50px;" |Nullable
+! scope="col" style="width: 50px;" |Data Default
+! scope="col" style="width: 500px;" |Example Data
+! scope="col" style="width: 350px;" |Description
+! scope="col" style="width: 350px;" |Comments
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|}
+===MD_INS_INS_SITE===
+[[MDCCD Data Warehouse|Return to Home Page.]]
+This table holds the site/company dictionary. This information is migrated from MDS.
+{| class="wikitable sortable mw-collapsible"
+|+
+! scope="col" style="width: 350px;" | Column Name
+! scope="col" style="width: 100px;" |DataType
+! scope="col" style="width: 50px;" | Key Type
+! scope="col" style="width: 50px;" |Nullable
+! scope="col" style="width: 50px;" |Data Default
+! scope="col" style="width: 500px;" |Example Data
+! scope="col" style="width: 350px;" |Description
+! scope="col" style="width: 350px;" |Comments
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|}
+===MD_INS_INS_SITE_CONTACT===
+[[MDCCD Data Warehouse|Return to Home Page.]]
+This table holds the site/company contact listing. This information is partially migrated from MDS.
+{| class="wikitable sortable mw-collapsible"
+|+
+! scope="col" style="width: 350px;" |Column Name
+! scope="col" style="width: 100px;" |DataType
+! scope="col" style="width: 50px;" |Key Type
+! scope="col" style="width: 50px;" |Nullable
+! scope="col" style="width: 50px;" |Data Default
+! scope="col" style="width: 500px;" |Example Data
+! scope="col" style="width: 350px;" |Description
+! scope="col" style="width: 350px;" |Comments
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|}
+===MD_INS_INS_LICENCE===
+[[MDCCD Data Warehouse|Return to Home Page.]]
+This table holds the establishment licence dictionary. This information is migrated from MDS.
+{| class="wikitable sortable mw-collapsible"
+|+
+! scope="col" style="width: 350px;" | Column Name
+! scope="col" style="width: 100px;" |DataType
+! scope="col" style="width: 50px;" | Key Type
+! scope="col" style="width: 50px;" |Nullable
+! scope="col" style="width: 50px;" |Data Default
+! scope="col" style="width: 500px;" |Example Data
+! scope="col" style="width: 350px;" | Description
+! scope="col" style="width: 350px;" |Comments
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|
+|-
+|
+|
+|
+|
+|
+|
+|}
+===MD_INS_INS_SITELICENCEACTIVITY===
+[[MDCCD Data Warehouse|Return to Home Page.]]
-===MD_INS_INS_SITE===
+This table holds the establishment licence activity dictionary. This information is migrated from MDS.
-This table holds information that categorizes or "tags" an inspection.
+{| class="wikitable sortable mw-collapsible"
-{| 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: 50px;" |Key Type
+! scope="col" style="width: 50px;" | Key Type
 ! scope="col" style="width: 50px;" |Nullable
 ! scope="col" style="width: 50px;" |Data Default
@@ Line 1,659: / Line 2,934: @@
 |-
 |
-|
-|
-|
-|
-|
-|
-|}
-===MD_INS_INS_SITE_CONTACT===
-This table holds information that categorizes or "tags" an inspection.
-{| class="wikitable sortable"
-|+
-! 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,703: / Line 2,949: @@
 |
 |
-|}
-===MD_INS_INS_LICENCE===
-This table holds information that categorizes or "tags" an inspection.
-{| class="wikitable sortable"
-|+
-! 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,741: / Line 2,973: @@
 |
 |
-|}
-===MD_INS_INS_SITELICENCEACTIVITY===
-This table holds information that categorizes or "tags" an inspection.
-{| class="wikitable sortable"
-|+
-! 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,772: / Line 2,990: @@
 |
 |-
-|
 |
 |