/*

Pentana Audit - find child items preventing unmapping of Entity Process.sql

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

Purpose of script:
	You are unable to unmap an Entity Process, delete an Entity or mark it or its parent Org Unit inactive if it is used in an active scope 
	(see https://pentanaaudit.help.ideagen.com/hc/en-gb/articles/10563506389778-Pentana-Audit-Cannot-unmap-Entity-Process-or-mark-Entity-or-parent-Org-Unit-as-not-Active-even-though-Entity-has-no-active-Audits-Entity-Objectives-Incidents-Key-Issues-Problems-Audit-Findings-or-Actions)
	
	This script finds all child items for an Entity Process that:
		- are not Approved, where the record has a state (Draft, Open, Prepared, Confirmed, Completed, Approved, Closed)
		- are not Closed, where the record has an Audit-style sign off state (Open, Prepared, Confirmed, Completed, Approved)

Changes:

To run:

- The first line of SQL specifies the Entity Process to investigate.  Ensure that this is correct
- This script makes no changes to data
 
*/

-- Specify the Entity Process to search for
	DECLARE @name AS NVARCHAR(555) = 'Cape Town Warehouse (03 - IT Processes)'  -- Change this to search for another Entity Process

-- Go over each type of child record and find the details of non-closed or non- ones
    DECLARE @id AS INT = (SELECT ID FROM EntityProcess WHERE Name = @name)

	-- Entity Objectives
	SELECT Name AS [Entity Objective Name] FROM [EntityObjective] WHERE [EntityProcessID] = @id AND [ActiveStateID] <> 3 -- exclude closed

		-- Entity Actions
	SELECT Name AS [Entity Action Name] FROM Action WHERE EntityProcessID = @id AND FindingID IS NULL AND SignOffStateID <> 3

	-- Audits					   
	SELECT A.Name AS [Audit Name]
		FROM Audit AS A
			INNER JOIN AuditScope AS SC
		ON A.ID = SC.SourceID
	WHERE SC.TargetID = @id
		AND [AuditStateID] <> 5;

	-- Incidents
	SELECT I.Name AS [Incident Name]
		FROM Incident AS I
			INNER JOIN IncidentScope AS SC
			ON I.ID = SC.SourceID
		WHERE SC.TargetID = @id
			AND [AuditStateID] <> 5;

	-- Key Issues
	SELECT K.Name AS [Key Issue Name]
		FROM KeyIssue AS K
			INNER JOIN KeyIssueScope AS SC
		ON K.ID = SC.SourceID
	WHERE SC.TargetID = @id
			AND [AuditStateID] <> 5;

	-- Problems.  Note that Problems live inside Audits but can have scope set independently
	SELECT A.Name AS [Parent Audit Name], P.Name AS [Problem Name]
		FROM Problem AS P
			INNER JOIN ProblemScope AS SC
		ON P.ID = SC.SourceID
			INNER JOIN Audit AS A
		ON P.AuditID = A.ID
	WHERE SC.TargetID = @id
		AND P.SignOffStateID <> 3;

	-- Findings
	SELECT Name AS [Finding Name] FROM Finding WHERE EntityProcessID = @id AND SignOffStateID <> 3
 
	-- Audit Actions
	SELECT Name AS [Audit Action Name] FROM Action WHERE EntityProcessID = @id AND FindingID IS NOT NULL AND SignOffStateID <> 3