/*

signoff_integrity_check v2.sql
	
Written by Ideagen (email: support.pentana-audit@ideagen.com)

Purpose of script:
	This script identifies records where the following problems exist:

	- SignoffID is NULL or 0 (it should always be > 0)
	- Signoff is 2 (Completed) but there is no Completion ID OR there is an Approval ID
	- Signoff is 3 (Approved) but there is no CompletionID or Approval ID

After identifying issues, we can then create a fix script.

Changes:

	v2 adds checks for the AuditProcess and Timesheet tables

To run:

Just run against the Pentana Audit database and copy the output.  No data or obects are modified.
 
*/


SELECT 'EntityProcessPeriod' AS [Entity], [ID], [SignOffStateID] FROM [EntityProcessPeriod] WHERE 
	([SignOffStateID] = 0 OR [SignOffStateID] IS NULL) OR 
	([SignOffStateID] = 1 AND ([CompletionID] IS NOT NULL)) OR
	([SignOffStateID] = 2 AND ([CompletionID] IS NULL OR [ApprovalID] IS NOT NULL)) OR
	([SignOffStateID] = 3 AND ([CompletionID] IS NULL OR [ApprovalID] IS NULL))
  
SELECT 'Incident' AS [Entity], [ID], [SignOffStateID] FROM [Incident] WHERE 
	([SignOffStateID] = 0 OR [SignOffStateID] IS NULL) OR 
	([SignOffStateID] = 1 AND ([CompletionID] IS NOT NULL)) OR
	([SignOffStateID] = 2 AND ([CompletionID] IS NULL OR [ApprovalID] IS NOT NULL)) OR
	([SignOffStateID] = 3 AND ([CompletionID] IS NULL OR [ApprovalID] IS NULL))
  
SELECT 'KeyIssue' AS [Entity], [ID], [SignOffStateID] FROM [KeyIssue] WHERE 
	([SignOffStateID] = 0 OR [SignOffStateID] IS NULL) OR 
	([SignOffStateID] = 1 AND ([CompletionID] IS NOT NULL)) OR
	([SignOffStateID] = 2 AND ([CompletionID] IS NULL OR [ApprovalID] IS NOT NULL)) OR
	([SignOffStateID] = 3 AND ([CompletionID] IS NULL OR [ApprovalID] IS NULL))
  
SELECT 'Audit' AS [Entity], [ID], [SignOffStateID] FROM [Audit] WHERE 
	([SignOffStateID] = 0 OR [SignOffStateID] IS NULL) OR 
	([SignOffStateID] = 1 AND ([CompletionID] IS NOT NULL)) OR
	([SignOffStateID] = 2 AND ([CompletionID] IS NULL OR [ApprovalID] IS NOT NULL)) OR
	([SignOffStateID] = 3 AND ([CompletionID] IS NULL OR [ApprovalID] IS NULL))
  
SELECT 'WorkPlan' AS [Entity], [ID], [SignOffStateID] FROM [WorkPlan] WHERE 
	([SignOffStateID] = 0 OR [SignOffStateID] IS NULL) OR 
	([SignOffStateID] = 1 AND ([CompletionID] IS NOT NULL)) OR
	([SignOffStateID] = 2 AND ([CompletionID] IS NULL OR [ApprovalID] IS NOT NULL)) OR
	([SignOffStateID] = 3 AND ([CompletionID] IS NULL OR [ApprovalID] IS NULL))
  
SELECT 'Step' AS [Entity], [ID], [SignOffStateID] FROM [Step] WHERE 
	([SignOffStateID] = 0 OR [SignOffStateID] IS NULL) OR 
	([SignOffStateID] = 1 AND ([CompletionID] IS NOT NULL)) OR
	([SignOffStateID] = 2 AND ([CompletionID] IS NULL OR [ApprovalID] IS NOT NULL)) OR
	([SignOffStateID] = 3 AND ([CompletionID] IS NULL OR [ApprovalID] IS NULL))
  
SELECT 'Objective' AS [Entity], [ID], [SignOffStateID] FROM [Objective] WHERE 
	([SignOffStateID] = 0 OR [SignOffStateID] IS NULL) OR 
	([SignOffStateID] = 1 AND ([CompletionID] IS NOT NULL)) OR
	([SignOffStateID] = 2 AND ([CompletionID] IS NULL OR [ApprovalID] IS NOT NULL)) OR
	([SignOffStateID] = 3 AND ([CompletionID] IS NULL OR [ApprovalID] IS NULL))

SELECT 'Risk' AS [Entity], [ID], [SignOffStateID] FROM [Risk] WHERE 
	([SignOffStateID] = 0 OR [SignOffStateID] IS NULL) OR 
	([SignOffStateID] = 1 AND ([CompletionID] IS NOT NULL)) OR
	([SignOffStateID] = 2 AND ([CompletionID] IS NULL OR [ApprovalID] IS NOT NULL)) OR
	([SignOffStateID] = 3 AND ([CompletionID] IS NULL OR [ApprovalID] IS NULL))

SELECT 'Control' AS [Entity], [ID], [SignOffStateID] FROM [Control] WHERE 
	([SignOffStateID] = 0 OR [SignOffStateID] IS NULL) OR 
	([SignOffStateID] = 1 AND ([CompletionID] IS NOT NULL)) OR
	([SignOffStateID] = 2 AND ([CompletionID] IS NULL OR [ApprovalID] IS NOT NULL)) OR
	([SignOffStateID] = 3 AND ([CompletionID] IS NULL OR [ApprovalID] IS NULL))

SELECT 'Test' AS [Entity], [ID], [SignOffStateID] FROM [Test] WHERE 
	([SignOffStateID] = 0 OR [SignOffStateID] IS NULL) OR 
	([SignOffStateID] = 1 AND ([CompletionID] IS NOT NULL)) OR
	([SignOffStateID] = 2 AND ([CompletionID] IS NULL OR [ApprovalID] IS NOT NULL)) OR
	([SignOffStateID] = 3 AND ([CompletionID] IS NULL OR [ApprovalID] IS NULL))

SELECT 'Problem' AS [Entity], [ID], [SignOffStateID] FROM [Problem] WHERE 
	([SignOffStateID] = 0 OR [SignOffStateID] IS NULL) OR 
	([SignOffStateID] = 1 AND ([CompletionID] IS NOT NULL)) OR
	([SignOffStateID] = 2 AND ([CompletionID] IS NULL OR [ApprovalID] IS NOT NULL)) OR
	([SignOffStateID] = 3 AND ([CompletionID] IS NULL OR [ApprovalID] IS NULL))
  
SELECT 'Finding' AS [Entity], [ID], [SignOffStateID] FROM [Finding] WHERE 
	([SignOffStateID] = 0 OR [SignOffStateID] IS NULL) OR 
	([SignOffStateID] = 1 AND ([CompletionID] IS NOT NULL)) OR
	([SignOffStateID] = 2 AND ([CompletionID] IS NULL OR [ApprovalID] IS NOT NULL)) OR
	([SignOffStateID] = 3 AND ([CompletionID] IS NULL OR [ApprovalID] IS NULL))

SELECT 'Action' AS [Entity], [ID], [SignOffStateID] FROM [Action] WHERE 
	([SignOffStateID] = 0 OR [SignOffStateID] IS NULL) OR 
	([SignOffStateID] = 1 AND ([CompletionID] IS NOT NULL)) OR
	([SignOffStateID] = 2 AND ([CompletionID] IS NULL OR [ApprovalID] IS NOT NULL)) OR
	([SignOffStateID] = 3 AND ([CompletionID] IS NULL OR [ApprovalID] IS NULL))

SELECT 'AuditReport' AS [Entity], [ID], [SignOffStateID] FROM [AuditReport] WHERE 
	([SignOffStateID] = 0 OR [SignOffStateID] IS NULL) OR 
	([SignOffStateID] = 1 AND ([CompletionID] IS NOT NULL)) OR
	([SignOffStateID] = 2 AND ([CompletionID] IS NULL OR [ApprovalID] IS NOT NULL)) OR
	([SignOffStateID] = 3 AND ([CompletionID] IS NULL OR [ApprovalID] IS NULL))
  
SELECT 'AuditQuestionnaire' AS [Entity], [ID], [SignOffStateID] FROM [AuditQuestionnaire] WHERE 
	([SignOffStateID] = 0 OR [SignOffStateID] IS NULL) OR 
	([SignOffStateID] = 1 AND ([CompletionID] IS NOT NULL)) OR
	([SignOffStateID] = 2 AND ([CompletionID] IS NULL OR [ApprovalID] IS NOT NULL)) OR
	([SignOffStateID] = 3 AND ([CompletionID] IS NULL OR [ApprovalID] IS NULL))

SELECT 'AuditQuestionnaireSection' AS [Entity], [ID], [SignOffStateID] FROM [AuditQuestionnaireSection] WHERE 
	([SignOffStateID] = 0 OR [SignOffStateID] IS NULL) OR 
	([SignOffStateID] = 1 AND ([CompletionID] IS NOT NULL)) OR
	([SignOffStateID] = 2 AND ([CompletionID] IS NULL OR [ApprovalID] IS NOT NULL)) OR
	([SignOffStateID] = 3 AND ([CompletionID] IS NULL OR [ApprovalID] IS NULL))
  
SELECT 'ReviewPoint' AS [Entity], [ID], [SignOffStateID] FROM [ReviewPoint] WHERE 
	([SignOffStateID] = 0 OR [SignOffStateID] IS NULL) OR 
	([SignOffStateID] = 1 AND ([CompletionID] IS NOT NULL)) OR
	([SignOffStateID] = 2 AND ([CompletionID] IS NULL OR [ApprovalID] IS NOT NULL)) OR
	([SignOffStateID] = 3 AND ([CompletionID] IS NULL OR [ApprovalID] IS NULL))

SELECT 'AuditProcess' AS [Entity], [ID], [SignOffStateID] FROM [AuditProcess] WHERE 
	([SignOffStateID] = 0 OR [SignOffStateID] IS NULL) OR 
	([SignOffStateID] = 1 AND ([CompletionID] IS NOT NULL)) OR
	([SignOffStateID] = 2 AND ([CompletionID] IS NULL OR [ApprovalID] IS NOT NULL)) OR
	([SignOffStateID] = 3 AND ([CompletionID] IS NULL OR [ApprovalID] IS NULL))

SELECT 'TimeSheet' AS [Entity], [ID], [SignOffStateID] FROM [TimeSheet] WHERE 
	([SignOffStateID] = 0 OR [SignOffStateID] IS NULL) OR 
	([SignOffStateID] = 1 AND ([CompletionID] IS NOT NULL)) OR
	([SignOffStateID] = 2 AND ([CompletionID] IS NULL OR [ApprovalID] IS NOT NULL)) OR
	([SignOffStateID] = 3 AND ([CompletionID] IS NULL OR [ApprovalID] IS NULL))