Revisions
Date | Description |
|---|---|
| 02/24/2022 |
|
| 01/11/2022 |
|
| 09/13/2021 |
|
| 01/03/2021 |
|
| 05/14/2021 |
|
dim_address
# | Column Name | Data Type | Allow Nulls | Max Length | Description |
|---|---|---|---|---|---|
| 1 | address_id | integer | No | PK | |
| 2 | address1 | string | Yes | 255 | |
| 3 | address2 | string | Yes | 255 | |
| 4 | city | string | Yes | 255 | |
| 5 | state | string | Yes | 255 | |
| 6 | postalcode | string | Yes | 30 | |
| 7 | country_id | integer | Yes | ||
| 8 | province_id | integer | Yes | ||
| 9 | tenant_id | integer | Yes | FK tenant | |
| 10 | sourcerecord_id | integer | No |
dim_artifact
# | Column Name | Data Type | Allow Nulls | Max Length | Description |
|---|---|---|---|---|---|
| 1 | artifact_id | integer | No | PK | |
| 2 | tenant_id | integer | No | FK tenant | |
| 3 | storageprovider | integer | No | ||
| 4 | storagecontainer | string | No | 50 | |
| 5 | storagekey | string | No | 355 | |
| 6 | filename | string | Yes | 355 | |
| 7 | filesize | bigint | No | ||
| 8 | fileformat_id | integer | No | ||
| 9 | is_transparentimage | boolean | Yes | ||
| 10 | sourcerecord_id | integer | Yes | ||
| 11 | auditanswerartifact_id | integer | Yes | ||
| 12 | auditanswer_id | integer | Yes | FK fact_auditanswer | |
| 13 | mitigationartifact_id | integer | Yes | ||
| 14 | mitigation_id | integer | Yes | FK fact_mitigationactivity | |
| 15 | documentartifact_id | integer | Yes | ||
| 16 | document_id | integer | Yes | FK dim_document | |
| 17 | description | string | Yes | 255 | |
| 18 | url | string | Yes | 255 | |
| 19 | auditquestionartifact_id | integer | Yes | ||
| 20 | auditquestion_id | integer | Yes | FK fact_auditquestion | |
| 21 | capaartifact_id | integer | Yes | ||
| 22 | capaevent_id | integer | Yes | FK fact_capaevent |
dim_auditcategory
# | Column Name | Data Type | Allow Nulls | Max Length | Description |
|---|---|---|---|---|---|
| 1 | auditcategory_id | integer | No | PK | |
| 2 | name | string | No | 50 | |
| 3 | is_enabled | boolean | Yes | ||
| 4 | tenant_id | integer | Yes | FK tenant | |
| 5 | is_lpa | boolean | Yes | ||
| 6 | is_readonly | boolean | Yes | ||
| 7 | baseauditsubtype_id | integer | Yes | ||
| 8 | is_suppliertemplate | boolean | Yes | ||
| 9 | sourcerecord_id | integer | Yes |
dim_auditquestiontag
# | Column Name | Data Type | Allow Nulls | Max Length | Description |
|---|---|---|---|---|---|
| 1 | auditquestiontag_id | integer | No | PK | |
| 2 | tenant_id | integer | No | FK tenant | |
| 3 | auditquestion_id | integer | No | FK fact_auditquestion | |
| 4 | tag_id | integer | No | FK dim_tag | |
| 5 | sourcerecord_id | integer | Yes |
dim_calendarevent
# | Column Name | Data Type | Allow Nulls | Max Length | Description |
|---|---|---|---|---|---|
| 1 | calendarevent_id | integer | No | PK | |
| 2 | tenant_id | integer | No | FK tenant | |
| 3 | calendar_id | integer | No | ||
| 4 | eventtype | integer | No | 0 = All, 1 = TimeOff, 2 = EmployeeTimeOff, 3 = Audit, 4 = OrgLayer | |
| 5 | eventdetails | integer | No | ||
| 6 | title | string | Yes | 255 | |
| 7 | description | string | Yes | 255 | |
| 8 | allday | boolean | No | ||
| 9 | start | timestamp | Yes | UTC | |
| 10 | due | timestamp | Yes | UTC | |
| 11 | is_deleted | boolean | No |
dim_capaeventapprover
# | Column Name | Data Type | Allow Nulls | Max Length | Description |
|---|---|---|---|---|---|
| 1 | capaeventapprover_id | integer | No | PK | |
| 2 | tenant_id | integer | No | FK tenant | |
| 3 | capaevent_id | integer | No | FK fact_capaevent | |
| 4 | user_id | integer | No | FK dim_user | |
| 5 | approvalstatus | integer | No | ||
| 6 | comment | string | Yes | 1000 | |
| 7 | is_deleted | boolean | No | ||
| 8 | deletedby | integer | Yes | FK dim_user | |
| 9 | datedeleted | timestamp | Yes | UTC |
dim_capaeventassociation
# | Column Name | Data Type | Allow Nulls | Max Length | Description |
|---|---|---|---|---|---|
| 1 | capaeventassociation_id | integer | No | PK | |
| 2 | tenant_id | integer | No | FK tenant | |
| 3 | capaevent_id | integer | No | FK fact_capaevent | |
| 4 | capaeventassociationtype | integer | No | 0 = None, 1 = Audit, 2 = Location, 3 = Customer | |
| 5 | is_required | boolean | No | ||
| 6 | name | string | 255 | ||
| 7 | auditevent_id | integer | Yes | FK fact_auditevent | |
| 8 | companysite_id | integer | Yes | FK dim_company | |
| 9 | location_id | integer | Yes | FK dim_location |
dim_capaeventassociationsetup
# | Column Name | Data Type | Allow Nulls | Max Length | Description |
|---|---|---|---|---|---|
| 1 | capaeventassociationsetup_id | integer | No | PK | |
| 2 | tenant_id | integer | No | FK tenant | |
| 3 | documentrev_id | integer | Yes | FK dim_documentrev | |
| 4 | capaeventassociationtype | integer | No | 0 = None, 1 = Audit, 2 = Location, 3 = Customer | |
| 5 | is_required | boolean | No | ||
| 6 | name | string | Yes | 255 | |
| 7 | capaevent_id | integer | Yes | FK fact_capaevent | |
| 8 | is_enabled | boolean | No |
dim_capaeventlineitem
# | Column Name | Data Type | Allow Nulls | Max Length | Description |
|---|---|---|---|---|---|
| 1 | capaeventlineitem_id | integer | No | PK | |
| 2 | tenant_id | integer | No | FK tenant | |
| 3 | capatask_id | integer | No | FK dim_capatask | |
| 4 | sequencenum | integer | No | ||
| 5 | capaeventid | integer | No | FK fact_capaevent | |
| 6 | mitigationactivity_id | integer | No | FK fact_mitigationactivity | |
| 7 | is_locked | boolean | No |
dim_capaeventowner
# | Column Name | Data Type | Allow Nulls | Max Length | Description |
|---|---|---|---|---|---|
| 1 | capaeventowner_id | integer | No | PK | |
| 2 | tenant_id | integer | No | FK tenant | |
| 3 | capavent_id | integer | No | FK fact_capaevent | |
| 4 | user_id | integer | No | FK dim_user | |
| 5 | reviewstatus | integer | No | 0 = Open, 1 = InReview, 2 = Approved, 3 = Rejected | |
| 6 | is_deleted | integer | No | ||
| 7 | deletedby | integer | Yes | FK dim_user | |
| 8 | datedeleted | timestamp | Yes | UTC |
dim_capasetting
# | Column Name | Data Type | Allow Nulls | Max Length | Description |
|---|---|---|---|---|---|
| 1 | capasetting_id | integer | No | PK | |
| 2 | tenant_id | integer | No | FK tenant | |
| 3 | documentrev_id | integer | No | FK dim_documentrev | |
| 4 | is_duedaterequired | boolean | No | ||
| 5 | is_severityrequired | boolean | No | ||
| 6 | is_escapepointrequired | boolean | No | ||
| 7 | is_escapevaluerequired | boolean | No | ||
| 8 | is_siterequired | boolean | No | ||
| 9 | enableseverity | boolean | No | ||
| 10 | enableescapepoint | boolean | No | ||
| 11 | enableescapevalue | boolean | No | ||
| 12 | severitycustomgroup_id | integer | Yes | FK dim_customgroup | |
| 13 | escapepointcustomgroup_id | integer | Yes | FK dim_customgroup | |
| 14 | escapevaluecustomgroup_id | integer | Yes | FK dim_customgroup |
dim_capatask
# | Column Name | Data Type | Allow Nulls | Max Length | Description |
|---|---|---|---|---|---|
| 1 | capatask_id | integer | No | PK | |
| 2 | tenant_id | integer | No | FK tenant | |
| 3 | title | string | Yes | 500 | |
| 4 | description | string | Yes | 500 | |
| 5 | duedate | timestamp | Yes | UTC | |
| 6 | is_attachementrequired | boolean | No | ||
| 7 | is_approvalrequired | boolean | No | ||
| 8 | enablenotifynexttaskowner | boolean | No | ||
| 9 | enablenotifycapaowner | boolean | No | ||
| 10 | mitigationactivity_id | integer | Yes | FK fact_mitigationactivity | |
| 11 | is_deleted | boolean | No | ||
| 12 | deletedby | integer | Yes | FK dim_user | |
| 13 | datedeleted | timestamp | Yes | UTC | |
| 14 | is_validation | boolean | No | ||
| 15 | is_duetodaynotified | boolean | No | ||
| 16 | daysdueafterreopened | integer | Yes |
dim_company
# | Column Name | Data Type | Allow Nulls | Max Length | Description |
|---|---|---|---|---|---|
| 1 | company_id | integer | No | PK | |
| 2 | tenant_id | integer | No | FK tenant | |
| 3 | name | string | No | 255 | |
| 4 | is_deleted | boolean | No | ||
| 5 | mappedbby | integer | Yes | FK dim_user | |
| 6 | mappedon | timestamp | Yes | UTC | |
| 7 | sourcerecord_id | integer | Yes |
dim_companysite
# | Column Name | Data Type | Allow Nulls | Max Length | Description |
|---|---|---|---|---|---|
| 1 | companysite_id | integer | No | PK | |
| 2 | tenant_id | integer | No | FK tenant | |
| 3 | siteinfo_id | integer | No | FK dim_siteinfo | |
| 4 | company_id | integer | No | FK dim_company | |
| 5 | name | string | No | 255 | |
| 6 | is_supplier | boolean | No | ||
| 7 | is_customer | boolean | No | ||
| 8 | is_deleted | boolean | No | ||
| 9 | mappedby | integer | Yes | FK dim_user | |
| 10 | mappedon | timestamp | Yes | UTC | |
| 11 | sourcerecord_is | integer | Yes |
dim_contact
# | Column Name | Data Type | Allow Nulls | Max Length | Description |
|---|---|---|---|---|---|
| 1 | contact_id | integer | No | PK | |
| 2 | tenant_id | integer | No | FK tenant | |
| 3 | site_id | integer | No | FK dim_siteinfo | |
| 4 | name | string | Yes | 255 | |
| 5 | jobtitle | string | Yes | 255 | |
| 6 | workphone | string | Yes | 50 | |
| 7 | cellphone | string | Yes | 50 | |
| 8 | details | string | Yes | 255 | |
| 9 | address_id | integer | Yes | FK dim_address | |
| 10 | company_id | integer | Yes | FK dim_company | |
| 11 | companysite_id | integer | Yes | FK dim_companysite | |
| 12 | is_primarycontactatsite | boolean | No | ||
| 13 | is_primarycontactatcompany | boolean | No | ||
| 14 | is_deleted | boolean | No |
dim_customcategory
# | Column Name | Data Type | Allow Nulls | Max Length | Description |
|---|---|---|---|---|---|
| 1 | customcategory_id | integer | No | PK | |
| 2 | tenant_id | integer | No | FK tenant | |
| 3 | name | string | Yes | 255 | |
| 4 | customgroup_id | integer | No | FK dim_customgroup | |
| 5 | is_deleted | boolean | No | ||
| 6 | deletedby_id | integer | Yes | FK dim_user | |
| 7 | deletedate | timestamp | Yes | UTC | |
| 8 | sourcerecord_id | integer | Yes |
dim_customcategorylineitem
# | Column Name | Data Type | Allow Nulls | Max Length | Description |
|---|---|---|---|---|---|
| 1 | customcategorylinteitem_id | integer | No | PK | |
| 2 | tenant_id | integer | No | FK tenant | |
| 3 | customcategory_id | integer | No | FK dim_customcategory | |
| 4 | customitemanswers_id | integer | No | FK dim_customitemanswers |
dim_customfield
# | Column Name | Data Type | Allow Nulls | Max Length | Description |
|---|---|---|---|---|---|
| 1 | customfield_id | integer | No | PK | |
| 2 | tenant_id | integer | No | FK tenant | |
| 3 | fieldtype_id | integer | No | enum | |
| 4 | name | string | Yes | 500 | |
| 5 | entity_id | integer | Yes | ??? | |
| 6 | entitytype_id | integer | Yes | 0 = Undefined, 1 = Audit, 2 = AuditQuestion, 3 = AuditAnswer, 4 = Job, 5 = Customer, 6 = User, 7 = MitigationActivity, 8 = Shift, 9 = ShiftManager, 10 = Site, 11 = Location, 12 = OrgLayer, 13 = Department, 14 = Scope, 15 = UserRole, 16 = Rank, 17 = IssueClassification, 18 = CustomCategory, 19 = CustomItem, 20 = DocumentRev, 21 = CapaEvent, 22 = CapaEventOwner, 23 = CapaEventApprover, 24 = CapaArtifact, 25 = CustomFieldValue, 26 = CapaTask, 27 = Document, 28 = MitigationArtifact, 29 = CapaEventAssociation | |
| entitysubtype | integer | Yes | ??? | ||
| 8 | entityfilter | string | Yes | 255 | |
| 9 | restrictentity | string | Yes | 255 | |
| 10 | restrictrange | string | Yes | 255 | |
| 11 | restricttogroup | integer | Yes | ??? | |
| 12 | restricttocategory | integer | Yes | ??? | |
| 13 | cardinality | string | Yes | 1 | |
| 14 | is_Deleted | boolean | Yes | ||
| 15 | deletedby_id | integer | Yes | FK dim_user | |
| 16 | deletedate | timestamp | Yes | UTC | |
| 17 | nullable | boolean | Yes | ||
| 18 | is_required | boolean | Yes | ||
| 19 | sourcerecord_id | integer | Yes |
dim_customfieldvalue
# | Column Name | Data Type | Allow Nulls | Max Length | Description |
|---|---|---|---|---|---|
| 1 | customfieldvalue_id | integer | No | PK | |
| 2 | tenant_id | integer | No | FK tenant | |
| 3 | parententitytype_id | integer | Yes | ??? | |
| 4 | parententity_id | integer | Yes | ??? | |
| 5 | entitytype_id | integer | Yes | 0 = Undefined, 1 = Audit, 2 = AuditQuestion, 3 = AuditAnswer, 4 = Job, 5 = Customer, 6 = User, 7 = MitigationActivity, 8 = Shift, 9 = ShiftManager, 10 = Site, 11 = Location, 12 = OrgLayer, 13 = Department, 14 = Scope, 15 = UserRole, 16 = Rank, 17 = IssueClassification, 18 = CustomCategory, 19 = CustomItem, 20 = DocumentRev, 21 = CapaEvent, 22 = CapaEventOwner, 23 = CapaEventApprover, 24 = CapaArtifact, 25 = CustomFieldValue, 26 = CapaTask, 27 = Document, 28 = MitigationArtifact, 29 = CapaEventAssociation | |
| 6 | entity_id | integer | Yes | ??? | |
| 7 | customfieldgroup_id | integer | Yes | FK dim_customgroup | |
| 8 | groupkeyvalue | integer | Yes | ||
| 9 | customfield_id | integer | Yes | FK dim_customfield | |
| 10 | fieldvaluestr | string | Yes | 200 | |
| 11 | fieldvaluenum | double | Yes | ||
| 12 | fieldvaluedate | timestamp | Yes | UTC | |
| 13 | fieldvaluecustomitem_id | integer | Yes | FK dim_customitemanswers | |
| 14 | fieldvaluecustomcategory_id | integer | Yes | FK dim_customcategory | |
| 15 | is_deleted | boolean | Yes | ||
| 16 | sourcerecord_id | integer | Yes |
dim_customgroup
# | Column Name | Data Type | Allow Nulls | Max Length | Description |
|---|---|---|---|---|---|
| 1 | customgroup_id | integer | No | PK | |
| 2 | tenant_id | integer | No | FK tenant | |
| 3 | name | string | No | 255 | |
| 4 | is_deleted | boolean | Yes | ||
| 5 | deleteby_id | integer | Yes | FK dim_user | |
| 6 | deletedate | timestamp | Yes | UTC | |
| 7 | is_systemgroup | boolean | Yes | ||
| 8 | systemgrouptype | integer | Yes | internal | |
| 9 | sourcerecord_id | integer | No |
dim_customitemanswers
# | Column Name | Data Type | Allow Nulls | Max Length | Description |
|---|---|---|---|---|---|
| 1 | customitemanswers_id | integer | No | PK | |
| 2 | tenant_id | integer | No | FK tenant | |
| 3 | name | string | Yes | 500 | |
| 4 | is_deleted | boolean | No | ||
| 5 | deletedby | integer | Yes | FK dim_user | |
| 6 | deletedate | timestamp | Yes | UTC | |
| 7 | sourcerecord_id | integer | Yes |
dim_customitemtag
# | Column Name | Data Type | Allow Nulls | Max Length | Description |
|---|---|---|---|---|---|
| 1 | customitemtag_id | integer | No | PK | |
| 2 | tenant_id | integer | No | FK tenant | |
| 3 | customitemanswers_id | integer | No | FK dim_customitemanswers | |
| 4 | tag_id | integer | No | FK dim_tag | |
| 5 | sourcerecord_id | integer | No |
dim_department
# | Column Name | Data Type | Allow Nulls | Max Length | Description |
|---|---|---|---|---|---|
| 1 | department_id | integer | No | PK | |
| 2 | siteinfo_id | integer | No | FK dim_siteinfo | |
| 3 | name | string | Yes | 50 | |
| 4 | description | string | Yes | 255 | |
| 5 | manager_id | integer | Yes | FK dim_user | |
| 6 | is_deleted | boolean | No | ||
| 7 | tenant_id | integer | No | FK tenant | |
| 8 | sourcerecord_id | integer | Yes |
dim_document
# | Column Name | Data Type | Allow Nulls | Max Length | Description |
|---|---|---|---|---|---|
| 1 | document_id | integer | No | PK | |
| 2 | name | string | Yes | 150 | |
| 3 | is_published | boolean | Yes | ||
| 4 | tenant_id | integer | No | FK tenant | |
| 5 | scope_id | integer | Yes | ||
| 6 | is_deleted | boolean | No | ||
| 7 | locationtierlevels | string | Yes | 255 | |
| 8 | documentrev_id | integer | Yes | FK dim_documentrev | |
| 9 | altmanager_id | integer | Yes | FK dim_user | |
| 10 | sourcerecord_id | integer | Yes |
dim_documentrev
# | Column Name | Data Type | Allow Nulls | Max Length | Description |
|---|---|---|---|---|---|
| 1 | documentrev_id | integer | No | PK | |
| 2 | document_id | integer | No | ||
| 3 | part_id | integer | Yes | internal | |
| 4 | department_id | integer | Yes | FK dim_department | |
| 5 | createdby_id | integer | Yes | FK dim_user | |
| 6 | createdon | timestamp | Yes | UTC | |
| 7 | effectivefrom | timestamp | Yes | UTC | |
| 8 | effectiveto | timestamp | Yes | UTC | |
| 9 | inuseby_id | integer | Yes | internal | |
| 10 | checkedoutby_id | integer | Yes | internal | |
| 11 | checkedouton | timestamp | Yes | UTC | |
| 12 | originalfilepath | string | Yes | 255 | |
| 13 | originalfilename | string | Yes | 255 | |
| 14 | originalfileformat_id | integer | Yes | internal | |
| 15 | siteinfo_id | integer | No | FK dim_siteinfo | |
| 16 | requestingparty | string | Yes | 255 | |
| 17 | manager_id | integer | Yes | FK dim_user | |
| 18 | documenttype_id | integer | Yes | Enum: Audit, Corrective Action | |
| 19 | documentsubtype | integer | Yes | FK dim_auditcategory | |
| 20 | rev | integer | No | ||
| 21 | revby | integer | No | FK dim_user | |
| 22 | revon | timestamp | No | UTC | |
| 23 | releasestatus_id | integer | No | enum | |
| 24 | changerequest_id | integer | Yes | internal | |
| 25 | workingdocdata_id | integer | Yes | internal | |
| 26 | releasedocdata_id | integer | Yes | internal | |
| 27 | is_deleted | integer | Yes | ||
| 28 | orglayer_id | integer | Yes | FK dim_orglayer | |
| 29 | tenant_id | integer | No | FK tenant | |
| 30 | isworkingdoc | boolean | Yes | ||
| 31 | sourcerecord_id | integer | Yes |
dim_documentteammember
# | Column Name | Data Type | Allow Nulls | Max Length | Description |
|---|---|---|---|---|---|
| 1 | documentteammember_id | integer | No | PK | |
| 2 | user_id | integer | No | FK dim_user | |
| 3 | tenant_id | integer | No | FK tenant | |
| 4 | canapprove | boolean | No | ||
| 5 | canmodify | boolean | No | ||
| 6 | is_deleted | boolean | No | ||
| 7 | deletedby | integer | Yes | FK dim_user | |
| 8 | datedeleted | timestamp | Yes | UTC | |
| 9 | documentrev_id | integer | Yes | FK dim_documentrev |
dim_documentteammemberapproval
# | Column Name | Data Type | Allow Nulls | Max Length | Description |
|---|---|---|---|---|---|
| 1 | documentteammemberapproval_id | integer | No | PK | |
| 2 | documentteammember_id | integer | No | FK dim_documentteammember | |
| 3 | appovaldate | timetamp | Yes | UTC | |
| 4 | documentrev_id | integer | No | FK dim_documentrev | |
| 5 | tenant_id | integer | No | FK tenant | |
| 6 | status | integer | No | enum | |
| 7 | comment | string | Yes | 255 | |
| 8 | lastupdate | timestamp | NO | UTC |
dim_location
# | Column Name | Data Type | Allow Nulls | Max Length | Description |
|---|---|---|---|---|---|
| 1 | location_id | integer | No | PK | |
| 2 | tenant_id | integer | Yes | FK tenant | |
| 3 | siteinfo_id | integer | Yes | FK dim_siteinfo | |
| 4 | locationtype_id | integer | No | 0 = None, 1 = Organization, 2 = Division, 3 = Site, 4 = Workgroup, 5 = Workcenter, 6 = Workstation | |
| 5 | name | string | Yes | 50 | |
| 6 | description | string | Yes | 500 | |
| 7 | address_id | integer | Yes | FK dim_address | |
| 8 | is_deleted | boolean | Yes | ||
| 9 | sequence | integer | Yes | Deprecated | |
| 10 | deletedby_id | integer | Yes | FK dim_user | |
| 11 | deletedate | timestamp | UTC | ||
| 12 | organization | integer | Yes | ||
| 13 | divisiontier5 | integer | Yes | Self reference to location_id | |
| 14 | divisiontier4 | integer | Yes | Self reference to location_id | |
| 15 | divisiontier3 | integer | Yes | Self reference to location_id | |
| 16 | divisiontier2 | integer | Yes | Self reference to location_id | |
| 17 | division | integer | Yes | Self reference to location_id | |
| 18 | site_id | integer | Yes | Self reference to location_id | |
| 19 | area_id | integer | Yes | Self reference to location_id | |
| 20 | workcenter_id | integer | Yes | Self reference to location_id | |
| 21 | station_id | integer | Yes | Self reference to location_id |
dim_orglayer
# | Column Name | Data Type | Allow Nulls | Max Length | Description |
|---|---|---|---|---|---|
| 1 | orglayer_id | integer | No | PK | |
| 2 | tenant_id | integer | No | FK tenant | |
| 3 | number | integer | No | ||
| 4 | is_deleted | iboolean | Yes |
dim_orglayersitedetail
# | Column Name | Data Type | Allow Nulls | Max Length | Description |
|---|---|---|---|---|---|
| 1 | orglayersitedetail | integer | No | PK | |
| 2 | tenant_id | integer | No | FK tenant | |
| 3 | siteinfo_id | integer | No | FK dim_siteinfo | |
| 4 | orglayer_id | integer | Yes | FK dim_orglayer | |
| 5 | name | string | Yes | 50 | |
| 6 | description | string | Yes | 255 |
dim_randomquestionconfig
# | Column Name | Data Type | Allow Nulls | Max Length | Description |
|---|---|---|---|---|---|
| 1 | randomquestionconfig_id | integer | No | PK | |
| 2 | tenant_id | integer | No | FK tenant | |
| 3 | numquestions | integer | Yes |
dim_randomquestiontag
# | Column Name | Data Type | Allow Nulls | Max Length | Description |
|---|---|---|---|---|---|
| 1 | randomequestiontag_id | integer | No | PK | |
| 2 | tenant_id | integer | No | FK tenant | |
| 3 | randomquestionconfig_id | integer | No | FK dim_randomquestionconfig | |
| 4 | tag_id | integer | No | FK dim_tag |
dim_rank
# | Column Name | Data Type | Allow Nulls | Max Length | Description |
|---|---|---|---|---|---|
| 1 | rank_id | integer | No | PK | |
| 2 | tenant_id | integer | No | FK tenant | |
| 3 | name | string | Yes | 100 | |
| 4 | is_notificationenabled | boolean | Yes | ||
| 5 | resolutionperiod | integer | Yes | ||
| 6 | is_additionalapprovalenabled | boolean | Yes | ||
| 7 | is_default | boolean | Yes | ||
| 8 | is_deleted | boolean | Yes | ||
| 9 | description | string | Yes | 255 | |
| 10 | siteinfo_id | integer | Yes | FK dim_siteinfo | |
| 11 | sourcerecord_id | integer | No |
dim_role
# | Column Name | Data Type | Allow Nulls | Max Length | Description |
|---|---|---|---|---|---|
| 1 | role_id | integer | No | PK | |
| 2 | site_id | integer | No | FK dim_siteinfo | |
| 3 | name | string | No | 30 | |
| 4 | description | string | Yes | 255 | |
| 5 | roletype_id | integer | Yes | internal | |
| 6 | tenant_id | integer | No | FK tenant |
dim_shift
# | Column Name | Data Type | Allow Nulls | Max Length | Description |
|---|---|---|---|---|---|
| 1 | shift_id | integer | No | PK | |
| 2 | tenant_id | integer | No | FK tenant | |
| 3 | siteinfo_id | integer | No | FK dim_siteinfo | |
| 4 | sequence | integer | No | ||
| 5 | name | string | No | 255 | |
| 6 | description | string | No | 255 | |
| 7 | sourcerecord_id | integer | No |
dim_schedulemaintenance
# | Column Name | Data Type | Allow Nulls | Max Length | Description |
|---|---|---|---|---|---|
| 1 | schedulemaintenance_id | integer | No | PK | |
| 2 | tenant_id | integer | No | FK tenant | |
| 3 | siteinfo_id | integer | No | FK dim_siteinfo | |
| 4 | auditevent_id | integer | No | FK fact_auditevent | |
| 5 | auditor_id | integer | No | FK dim_user | |
| 6 | auditor_comment | varchar | Yes | ||
| 7 | approver_id | integer | No | FK dim_user | |
| 8 | approver_comment | varchar | Yes | ||
| 9 | status | integer | No | 0 = Open, 1 = Approved, 2 = Reassigned, 3 = Rejected | |
| 10 | create_date | timestamp | No | ||
| 11 | reject_date | timestamp | Yes | ||
| 12 | last_update | timestamp | Yes | ||
| 13 | request_type | int | No | 0 = Missed Audit Delete Request, 1 = Upcoming Audit Delete Request |
dim_siteinfo
# | Column Name | Data Type | Allow Nulls | Max Length | Description |
|---|---|---|---|---|---|
| 1 | siteinfo_id | integer | No | PK | |
| 2 | tenant_id | integer | No | FK tenant | |
| 3 | code | string | Yes | 6 | |
| 4 | name | string | No | 6 | |
| 5 | description | string | Yes | 255 | |
| 6 | is_deleted | boolean | No | ||
| 7 | useshifts | boolean | No | ||
| 8 | timezone | string | Yes | 255 | |
| 9 | createddate | timestamp | Yes | UTC | |
| 10 | sourcerecord_id | integer | Yes |
dim_tag
# | Column Name | Data Type | Allow Nulls | Max Length | Description |
|---|---|---|---|---|---|
| 1 | tag_id | integer | No | PK | |
| 2 | tenant_id | integer | No | FK tenant | |
| 3 | label | string | Yes | 255 | |
| 4 | is_systemtag | boolean | Yes | ||
| 5 | auditsubtype_id | integer | Yes | FK dim_auditcategory | |
| 6 | is_deleted | boolean | Yes | ||
| 7 | deletedby_id | integer | Yes | FK dim_user | |
| 8 | deletedate | timetamp | Yes | UTC | |
| 9 | sourcerecord_id | integer | Yes |
dim_user
# | Column Name | Data Type | Allow Nulls | Max Length | Description |
|---|---|---|---|---|---|
| 1 | user_id | integer | No | PK | |
| 2 | siteinfo_id | integer | No | FK dim_siteinfo | |
| 3 | tenant_id | integer | No | FK tenant | |
| 4 | department_id | integer | No | FK dim_department | |
| 5 | scope_id | integer | Yes | ||
| 6 | orglayer_id | integer | Yes | FK dim_orglayer | |
| 7 | firstname | string | No | 100 | |
| 8 | lastname | string | No | 100 | |
| 9 | is_validated | boolean | Yes | ||
| 10 | is_locked | boolean | Yes | ||
| 11 | is_deleted | boolean | No | ||
| 12 | string | Yes | 255 | ||
| 13 | shift_id | integer | Yes | ||
| 14 | is_usernotified | boolean | Yes | ||
| 15 | createdate | timestamp | Yes | UTC | |
| 16 | language | string | Yes | 10 | |
| 17 | adminscope_id | integer | Yes | ||
| 18 | deletedby_id | integer | Yes | Self reference to user_id | |
| 19 | datedeleted | timestamp | Yes | UTC | |
| 20 | is_securitylocked | boolean | Yes | ||
| 21 | failedloginattempts | integer | Yes | ||
| 22 | sourcerecord_id | integer | Yes | ||
| 23 | withoutemail | boolean | Yes |
dim_userrole
# | Column Name | Data Type | Allow Nulls | Max Length | Description |
|---|---|---|---|---|---|
| 1 | userrole_id | integer | No | PK | |
| 2 | user_id | integer | No | FK dim_user | |
| 3 | role_id | integer | No | FK dim_role | |
| 4 | name | string | No | 30 | |
| 5 | tenant_id | integer | Yes | ||
| 6 | sourcerecord_id | integer | Yes |
fact_auditanswer
# | Column Name | Data Type | Allow Nulls | Max Length | Description |
|---|---|---|---|---|---|
| 1 | auditanswer_id | integer | No | PK | |
| 2 | auditevent_id | integer | No | FK fact_auditevent | |
| 3 | auditquestion_id | integer | No | FK fact_auditquestion | |
| 4 | auditquestioncategory_id | integer | No | ||
| 5 | tenant_id | integer | No | FK tenant | |
| 6 | comment | string | Yes | 1000 | |
| 7 | auditorverifydate | timestamp | Yes | DEPRECATED | |
| 8 | altmanager_id | integer | Yes | FK dim_user | |
| 9 | pointvalue | integer | Yes | DEPRECATED | |
| 10 | hasnonconformance | boolean | Yes | ||
| 11 | issueclassification_id | integer | Yes | ||
| 12 | is_na | boolean | Yes | ||
| 13 | is_passed | boolean | Yes | ||
| 14 | is_failed | boolean | Yes | ||
| 15 | customcategory_id | integer | Yes | FK dim_customcategory | |
| 16 | customitem_id | integer | Yes | FK dim_customitemanswers | |
| 17 | score | integer | Yes | Replaced point value, actual score no need for -1 | |
| 18 | passfailansweroption | integer | Yes | Will replace is_passed, is_failed and is_na. 0 = undefined, 1 = passed, 2 = failed, NA = 3, unanswered = 4 |
fact_auditevent
# | Column Name | Data Type | Allow Nulls | Max Length | Description |
|---|---|---|---|---|---|
| 1 | auditevent_id | integer | No | PK | |
| 2 | tenant_id | integer | No | FK tenant | |
| 3 | description | string | Yes | 150 | |
| 4 | daterquired | timestamp | No | UTC | |
| 5 | daterequiredlocal | timestamp | Yes | Local site time | |
| 6 | siteinfo_id | integer | Yes | FK dim_siteinfo | |
| 7 | location_id | integer | Yes | FK dim_location | |
| 8 | documentrev_id | integer | Yes | FK dim_documentrev | |
| 9 | document_id | integer | Yes | FK dim_document | |
| 10 | shiftmanager_id | integer | Yes | FK dim_user | |
| 11 | status | integer | Yes | 0 = Not Started, 1 = Complete, 2 = Partially Complete, 3 = Missed, 4 = Completed Late, 5 = Past Due | |
| 12 | hasnonconformance | boolean | No | ||
| 13 | is_passed | boolean | Yes | ||
| 14 | calendarevent_id | integer | Yes | FK dim_calendarevent | |
| 15 | datestarted | timestamp | Yes | UTC | |
| 16 | dateupdated | timestamp | Yes | UTC | |
| 17 | datecompleted | timestamp | Yes | UTC | |
| 18 | department_id | integer | Yes | FK dim_department | |
| 19 | auditoruser_id | integer | Yes | FK dim_user | |
| 20 | percentcomplete | integer | Yes | ||
| 21 | score | integer | Yes | ||
| 22 | auditcategory | integer | Yes | FK dim_auditcategory | |
| 23 | is_deleted | boolean | Yes | ||
| 24 | is_adhoc | boolean | Yes | ||
| 25 | deletedbyuser_id | integer | Yes | FK dim_user | |
| 26 | deletedate | timestamp | Yes | UTC | |
| 27 | job_id | integer | Yes | Deprecated | |
| 28 | orglayer_id | integer | Yes | FK dim_orglayer | |
| 29 | shift_id | integer | Yes | ||
| 30 | assignedbyuser_id | integer | Yes | FK dim_user | |
| 31 | dateentered | timestamp | Yes | UTC | |
| 32 | enteredbyuser_id | integer | Yes | FK dim_user | |
| 33 | companysite_id | integer | Yes | FK dim_companysite | |
| 34 | graceperioddays | integer | No | ||
| 35 | pastduedays | integer | No | ||
| 36 | is_autogenerated | boolean | Yes | ||
| 37 | startwindow | integer | Yes | ||
| 38 | sourcerecord_id | integer | Yes | ||
| 39 | timezone_id | string | Yes | 255 | |
| 40 | auditcompliance_id | integer | Yes | ||
| 41 | auditcompliancestartdate | timestamp | Yes | UTC | |
| 42 | auditcomplianceenddate | timestamp | Yes | UTC | |
| 43 | compliancestart | integer | Yes |
fact_auditquestion
# | Column Name | Data Type | Allow Nulls | Max Length | Description |
|---|---|---|---|---|---|
| 1 | auditquestion_id | integer | No | PK | |
| 2 | description | string | Yes | 250 | |
| 3 | questiontext | string | Yes | 1500 | |
| 4 | auditcategory | integer | No | FK dim_auditcategory | |
| 5 | location_id | integer | Yes | FK dim_location | |
| 6 | pointvalue | integer | No | ||
| 7 | weight | integer | No | ||
| 8 | is_critical | boolean | Yes | ||
| 9 | tenant_id | integer | Yes | FK dim_tenant | |
| 10 | is_generic | boolean | Yes | ||
| 11 | allowauditortoassignaltmanager | boolean | Yes | ||
| 12 | questiontype | integer | No | 0 = None, 1 = Pass/Fail, 2 = Point Value, 3 = Multiple Choice, 4 = Multi Select, 5 = Text, 6 = Numeric | |
| 13 | passdescription | string | Yes | 1000 | |
| 14 | faildescription | string | Yes | 1000 | |
| 15 | original_id | integer | Yes | Self reference to auditquestion_id | |
| 16 | customfield_id | integer | Yes | FK dim_customfield | |
| 17 | photoinputresponseoption | integer | Yes | 0 = Off, 1 = Optional, 2 = RequiredOnFail, 3 = Required, 99 = Modified | |
| 18 | minimumpassingscore | integer | Yes | ||
| 19 | mitigationresponseoption | integer | Yes | 0 = Off, 1 = Optional, 2 = Required, 99 = Modified | |
| 20 | is_deleted | boolean | Yes | ||
| 21 | rev | integer | Yes | ||
| 22 | revby | integer | Yes | FK dim_user | |
| 23 | is_latestrev | bool | Yes | ||
| 24 | auditquestionset_id | integer | Yes | ||
| 25 | revon | datetime | Yes | UTC | |
| 26 | passingmin | double | Yes | ||
| 27 | passingmax | double | Yes | ||
| 28 | comparison | integer | Yes | ||
| 29 | reactionplan | string | Yes | 2000 | |
| 30 | scope_id | integer | Yes | ||
| 31 | is_passfailtype | boolean | Yes | ||
| 32 | sourcerecord_id | integer | Yes | ||
| 33 | auditquestiongroupid | integer | Yes | ||
| 34 | aduitquestiongroup | string | Yes | 2000 |
fact_capaevent
# | Column Name | Data Type | Allow Nulls | Max Length | Description |
|---|---|---|---|---|---|
| 1 | capaevent_id | integer | No | PK | |
| 2 | tenant_id | integer | No | FK dim_tenant | |
| 3 | name | string | Yes | 300 | |
| 4 | duedate | timestamp | Yes | Utc | |
| 5 | pastduedays | integer | No | ||
| 6 | graceperioddays | integer | No | ||
| 7 | capastatus | integer | No | ||
| 8 | dateopened | timestamp | Yes | UTC | |
| 9 | severitycustomitem_id | integer | Yes | FK dim_customitemanswers | |
| 10 | escapepointcustomitem_id | integer | Yes | FK dim_customitemanswers | |
| 11 | esacpevaluecustomitem_id | integer | Yes | FK dim_customitemanswers | |
| 12 | siteinfo_id | integer | No | FK dim_siteinfo | |
| 13 | createdbyuser_id | integer | No | FK dim_user | |
| 14 | summary | string | Yes | 3000 | |
| 15 | documentrev_id | integer | Yes | FK dim_documentrev | |
| 16 | document_id | integer | Yes | FK dim_document | |
| 17 | capasetting_id | integer | No | FK dim_capasetting | |
| 18 | is_deleted | boolean | No | ||
| 19 | deletedby | integer | Yes | FK dim_user | |
| 20 | datedeleted | timestamp | UTC | ||
| 21 | dateclosed | timestamp | UTC | ||
| 22 | validationstatus | integer | No | enum | |
| 23 | is_duetodaynotified | boolean | No | ||
| 24 | comments | string | Yes | 350 |
fact_mitigationactivity
# | Column Name | Data Type | Allow Nulls | Max Length | Description |
|---|---|---|---|---|---|
| 1 | mitigationactivity_id | integer | No | PK | |
| 2 | tenant_id | integer | No | FK dim_tenant | |
| 3 | siteinfo_id | integer | No | FK dim_siteinfo | |
| 4 | sequence | integer | No | ||
| 5 | responsiblepartyuser_id | integer | Yes | FK dim_user | |
| 6 | status | integer | No | 0 = Open-On Time, 1 = Open-Past Due, 2 = Pending-On Time, 3 = Pending-Past Due, 4 = Closed-On Time, 5 = Closed-Past Due | |
| 7 | comment | string | Yes | 1500 | |
| 8 | auditevent_id | integer | Yes | FK fact_auditevent | |
| 9 | dateopened | datetime | Yes | UTC | |
| 10 | dateclosed | datetime | Yes | UTC | |
| 11 | auditanswer_id | integer | Yes | FK fact_auditanswer | |
| 12 | is_pastdue | boolean | No | ||
| 13 | pastduedays | integer | Yes | ||
| 14 | rank_id | integer | Yes | FK dim_rank | |
| 15 | countermeasurecustomitem_id | integer | Yes | FK dim_customitemanswers | |
| 16 | approvingmanager_id | integer | Yes | ||
| 17 | customfieldvalue_id | integer | Yes | FK dim_customfieldvalue | |
| 18 | name | string | Yes | 300 | |
| 19 | location_id | integer | Yes | FK dim_location | |
| 20 | creator_id | integer | Yes | FK dim_user | |
| 21 | description | string | Yes | 500 | |
| 22 | issueclassification_id | integer | Yes | ||
| 23 | mitigationtype_id | integer | Yes | ||
| 24 | approvingpartycomment | string | Yes | 1000 | |
| 25 | is_deleted | boolean | No | ||
| 26 | deletedby_id | integer | Yes | FK dim_user | |
| 27 | deletedate | timestamp | Yes | UTC | |
| 28 | is_partofcapa | boolean | Yes | ||
| 29 | sourcerecord_id | integer | No | ||
| 30 | customcategory_id | integer | Yes | FK dim_customcategory | |
| 31 | customitem_id | integer | Yes | FK dim_customitemanswers |
Comments
0 comments
Please sign in to leave a comment.