Back to Home

Back to Checklist Setup

p11_AuditChecklist

Stores relationship for which Checklists and Sections have been selected on an intake form. Primary Key for UniqueAuditChecklistNo. Foreign Key UniqueNo from table [smart].[p11_AuditChecklist_PlanSelection] UniqueNo is 0 by default for account level checklists

p11_AuditChecklist_PlanSelection

Stores the relationship for the plannames and checklists. Necessary because one checklist can have multiple plans. PlanNameCount stores the number of plans that are on a single checklist. The Opportunity and ErrorPoint calculations are deprecated and not used. Primary Key UniqueNo. Foreign keys UniqueInstanceNo

p11_AuditChecklist_PlanNameList

Similar to p11_AuditChecklist_PlanSelection but is separated by plan names. So if one checklist is set for plan1, plan2, p11_AuditChecklist_PlanNameList will each plan and checklist separately. (might not need p11_AuditChecklist_PlanSelection if we just have p11_AuditChecklist_PlanNameList) Primary Key UniquePlanNameNo. Foreign keys UniqueInstanceNo and UniqueNo UniqueNo is from the p11_AuditChecklist_PlanSelection table

p11_AuditChecklist_FieldList

Stores the errors at the field level. Has data for all error information One of the most important tables for error calculations Primary Key UniqueAuditFieldListNo. Foreign Key UniqueAuditChecklistNo and UniqueInstanceNo1

p11_AuditChecklist_Confirmed

Stores the information for confirmation of no errors at the field level. Primary Key UniqueNo. Foreign Key UniqueAuditChecklistNo

p11_AuditChecklist_PlanName

Stores which plans the error is for On the error popup page, a QR can select a subset of plans to assess the error on. This table let's you know which ones.Primary Key UniqueNo. Foreign Key UniqueAuditChecklistNo and UniquePlanNameNo Connected to

Use script and tables below to better understand flow

use Core_CEM

select UniqueAuditChecklistNo, UniqueInstanceNo, a.ChecklistNo, ChecklistTitle, a.SectionNo, SectionTitle, a.Deleted, UniqueNo from smart.p11_AuditChecklist a inner join smart.p11_Master_Checklist b on a.ChecklistNo = b.ChecklistNo inner join smart.p11_SectionList c on a.SectionNo = c.SectionNo where a.Deleted = 0 --will insert another row when section is selected from plan level --inserts on account level checklist when section is selected and saved

select * from smart.p11_AuditChecklist_PlanSelection where Deleted= 0 --inserts when you input plan name in text box and click 'add'

select * from smart.p11_AuditChecklist_PlanNameList where Deleted= 0 --inserts when you input plan name in text box and click 'add'

select * from smart.p11_AuditChecklist_FieldList where Deleted= 0 --Inserts when an error is charged on fieldlist level

select * from smart.p11_AuditChecklist_Confirmed where Deleted= 0 --inserts when a no error is selected on field level.

select * from smart.p11_AuditChecklist_PlanName where Deleted= 0 --Inserts when an error is charged on fieldlist level 'UniqueAuditChecklistNo' --links with UniqueInstanceNo in other tables. --plan name that the error is charged on

Below cases should cause relevant data to be deleted.

1. section list changes / unselected
2. plan checklist is deleted / removed
3. plan checklist name is modified.

Use tables below to better familiarize

1. select * from smart.p11_AuditChecklist
2. select * from smart.p11_Master_Checklist
3. select * from smart.p11_SectionList