wiki

Changes

MDCCD Data Warehouse Data Dictionary (view source)

Revision as of 15:10, 23 May 2024

4,682 bytes added ,  23 May
→‎MD_INS_CASE
Line 5: Line 5:     
=== MD_INS_CASE ===
 
=== MD_INS_CASE ===
 +
This table holds the main inspection information.
 +
{| class="wikitable"
 +
|+
 +
!Column Name
 +
!DataType
 +
!Nullable
 +
!Data Default
 +
!Example Data
 +
!Description
 +
|-
 +
|INSP_ID
 +
|VARCHAR2(36 CHAR)
 +
|No
 +
|
 +
|
 +
|-
 +
|INSP_NUM
 +
|VARCHAR2(200 CHAR)
 +
|No
 +
|
 +
|
 +
|-
 +
|SITE_ID
 +
|VARCHAR2(36 CHAR)
 +
|No
 +
|
 +
|
 +
|-
 +
|SITE_NAME
 +
|VARCHAR2(160 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|LICENCE_NO
 +
|NUMBER(6,0)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|COMPANY_ID
 +
|NUMBER(6,0)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|REG_FILE_NUMBER
 +
|VARCHAR2(250 BYTE)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|REGION_CD_EN
 +
|VARCHAR2(50 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|REGION_CD_FR
 +
|VARCHAR2(50 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|INSP_TYPE_EN
 +
|VARCHAR2(100 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|INSP_TYPE_FR
 +
|VARCHAR2(100 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|LEAD_INSPECTOR_ID
 +
|VARCHAR2(36 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|LEAD_INSPECTOR
 +
|VARCHAR2(200 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|LEAD_INSPECTOR_BADGE
 +
|VARCHAR2(50 BYTE)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|SUPERVISOR_ID
 +
|VARCHAR2(36 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|SUPERVISOR
 +
|VARCHAR2(200 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|INSP_STATUS_EN
 +
|VARCHAR2(100 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|INSP_STATUS_FR
 +
|VARCHAR2(100 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|COMP_STANDARD_EN
 +
|VARCHAR2(100 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|COMP_STANDARD_FR
 +
|VARCHAR2(100 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|DRAFT_REPORT_RATING_EN
 +
|VARCHAR2(100 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|DRAFT_REPORT_RATING_FR
 +
|VARCHAR2(100 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|DRAFT_REPORT_ISSUE_DATE
 +
|DATE
 +
|Yes
 +
|
 +
|
 +
|-
 +
|FINAL_REPORT_RATING_EN
 +
|VARCHAR2(100 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|FINAL_REPORT_RATING_FR
 +
|VARCHAR2(100 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|FINAL_REPORT_ISSUE_DATE
 +
|DATE
 +
|Yes
 +
|
 +
|
 +
|-
 +
|AMENDED_REPORT_ISSUE_DATE
 +
|DATE
 +
|Yes
 +
|
 +
|
 +
|-
 +
|SAMPLES_TAKEN
 +
|VARCHAR2(2000 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|INSP_START_DATE
 +
|DATE
 +
|Yes
 +
|
 +
|
 +
|-
 +
|INSP_END_DATE
 +
|DATE
 +
|Yes
 +
|
 +
|
 +
|-
 +
|CLOSING_MEETING_DATE
 +
|DATE
 +
|Yes
 +
|
 +
|
 +
|-
 +
|NEXT_INSPECTION_DATE
 +
|DATE
 +
|Yes
 +
|
 +
|
 +
|-
 +
|AMENDED_CD_EN
 +
|VARCHAR2(5 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|AMENDED_CD_FR
 +
|VARCHAR2(5 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|AMENDED_BY
 +
|VARCHAR2(200 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|AMENDED_DATE
 +
|DATE
 +
|Yes
 +
|
 +
|
 +
|-
 +
|FIRST_COMM_DATE
 +
|DATE
 +
|Yes
 +
|
 +
|
 +
|-
 +
|FIRST_COMM_MODE
 +
|VARCHAR2(100 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|FIRST_COMM_MODE_EN
 +
|VARCHAR2(100 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|FIRST_COMM_MODE_FR
 +
|VARCHAR2(100 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|EMERGENCY_CONTACT_NAME
 +
|VARCHAR2(100 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|EMERGENCY_CONTACT_PHONE
 +
|VARCHAR2(100 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|SENIOR_OFFICIAL_OFFICIAL_ID
 +
|VARCHAR2(36 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|SENIOR_OFFICIAL_NAME
 +
|VARCHAR2(200 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|SENIOR_OFFICIAL_TITLE
 +
|VARCHAR2(100 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|SITE_CONTACT_ID
 +
|VARCHAR2(36 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|SITE_CONTACT_NAME
 +
|VARCHAR2(200 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|SITE_CONTACT_TITLE
 +
|VARCHAR2(100 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|SITE_CONTACT_PHONE
 +
|VARCHAR2(100 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|SITE_CONTACT_FAX
 +
|VARCHAR2(100 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|SITE_CONTACT_EMAIL
 +
|VARCHAR2(100 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|SITE_ADDRESS_1
 +
|VARCHAR2(250 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|SITE_ADDRESS_2
 +
|VARCHAR2(250 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|SITE_ADDRESS_3
 +
|VARCHAR2(250 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|SITE_CITY
 +
|VARCHAR2(80 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|SITE_PROVINCE
 +
|VARCHAR2(50 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|SITE_POSTALCODE
 +
|VARCHAR2(20 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|SITE_COUNTRY
 +
|VARCHAR2(80 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|INSPECTION_SUMMARY
 +
|CLOB
 +
|Yes
 +
|
 +
|
 +
|-
 +
|REPORT_DELIVERED_TO_ID
 +
|VARCHAR2(36 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|REPORT_DELIVERED_TO
 +
|VARCHAR2(200 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|RESPONSE_EXPECTED_DATE
 +
|DATE
 +
|Yes
 +
|
 +
|
 +
|-
 +
|INTERNAL_CLOSING_COMMENTS
 +
|CLOB
 +
|Yes
 +
|
 +
|
 +
|-
 +
|PERSONNEL_CHANGES
 +
|VARCHAR2(2000 BYTE)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|IID_STATUS_EN
 +
|VARCHAR2(50 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|IID_STATUS_FR
 +
|VARCHAR2(50 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|IID_STATUS_DATE
 +
|DATE
 +
|Yes
 +
|
 +
|
 +
|-
 +
|IID_REVISION_LOG
 +
|VARCHAR2(2000 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|IRC_STATUS_EN
 +
|VARCHAR2(50 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|IRC_STATUS_FR
 +
|VARCHAR2(50 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|INSP_RATING_EN
 +
|VARCHAR2(100 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|INSP_RATING_FR
 +
|VARCHAR2(100 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|INSP_OUTCOME_EN
 +
|VARCHAR2(2000 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|INSP_OUTCOME_FR
 +
|VARCHAR2(2000 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|INSP_REPORT_WAITING
 +
|VARCHAR2(200 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|INSP_REPORT_WAITING_DATE
 +
|DATE
 +
|Yes
 +
|
 +
|
 +
|-
 +
|INSP_REPORT_APPROVED_BY
 +
|VARCHAR2(200 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|INSP_REPORT_APPROVED_BY_DATE
 +
|DATE
 +
|Yes
 +
|
 +
|
 +
|-
 +
|INSP_CLOSED_WAITING
 +
|VARCHAR2(200 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|INSP_CLOSED_WAITING_DATE
 +
|DATE
 +
|Yes
 +
|
 +
|
 +
|-
 +
|INSP_CLOSED_APPROVED
 +
|VARCHAR2(200 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|INSP_CLOSED_APPROVED_DATE
 +
|DATE
 +
|Yes
 +
|
 +
|
 +
|-
 +
|CREATEDBYNAME
 +
|VARCHAR2(100 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|CREATEDON
 +
|DATE
 +
|Yes
 +
|
 +
|
 +
|-
 +
|MODIFIEDBYNAME
 +
|VARCHAR2(100 CHAR)
 +
|Yes
 +
|
 +
|
 +
|-
 +
|MODIFIEDON
 +
|DATE
 +
|Yes
 +
|
 +
|
 +
|}
    
=== MD_INS_CASE_CAPA ===
 
=== MD_INS_CASE_CAPA ===
Line 29: Line 555:  
|NULL
 
|NULL
 
|3d7a309e-ce46-40c5-a6c2-f3ea80971d48
 
|3d7a309e-ce46-40c5-a6c2-f3ea80971d48
−
|
+
|This is the unique record identifier for the parent inspection.
 
|-
 
|-
 
|INSP_NUM
 
|INSP_NUM
Line 36: Line 562:  
|NULL
 
|NULL
 
|00086-BENEMAX DBA. BENEMAX HEALTH AND WELLNESS  
 
|00086-BENEMAX DBA. BENEMAX HEALTH AND WELLNESS  
−
|
+
|This is the parent inspection number.
 
|-
 
|-
 
|OBS_ID
 
|OBS_ID
Line 43: Line 569:  
|NULL
 
|NULL
 
|07244e35-fbf1-ee11-a1fe-6045bd6005cc
 
|07244e35-fbf1-ee11-a1fe-6045bd6005cc
−
|
+
|This is the unique record identifier for the parent observation the CAPA is linked to.
 
|-
 
|-
 
|CAPA_TYPE
 
|CAPA_TYPE
Line 50: Line 576:  
|NULL
 
|NULL
 
|Follow Up Plan
 
|Follow Up Plan
−
|
+
|This is a free text field used to identify the type of CAPA.
 
|-
 
|-
 
|CAPA_PLAN
 
|CAPA_PLAN
Line 57: Line 583:  
|NULL
 
|NULL
 
|The inspector sent follow-up letter to address Physio-controls response--the manufacturer can have more than one ID on the label as long as it clearly marks what the device identifier is and that this identifier matches what is in the MDALL. March 6, 2024 The company sent follow-up from manufacturer.  The manufacturer has sent in a fax back to Health Canada to include the device ID (11141-000165) under their license. This adequately addresses the observation.
 
|The 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
 
|CREATEDBYNAME
Line 64: Line 590:  
|NULL
 
|NULL
 
|Jeff Tyndall
 
|Jeff Tyndall
−
|
+
|This identifies the resource that created the record.
 
|-
 
|-
 
|CREATEDON
 
|CREATEDON
Line 71: Line 597:  
|NULL
 
|NULL
 
|22-08-26
 
|22-08-26
−
|
+
|This identifies the date that record was created.
 
|-
 
|-
 
|MODIFIEDBYNAME
 
|MODIFIEDBYNAME
Line 78: Line 604:  
|NULL
 
|NULL
 
|Jeff Tyndall
 
|Jeff Tyndall
−
|
+
|This identifies the resource that las modified the record.
 
|-
 
|-
 
|MODIFIEDON
 
|MODIFIEDON
Line 85: Line 611:  
|NULL
 
|NULL
 
|22-08-26
 
|22-08-26
−
|
+
|This identifies the date that record was last modified.
 
|}
 
|}
  
Jeff.tyndall
67

edits

Retrieved from "https://wiki.gccollab.ca/Special:MobileDiff/120142...120536"