go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Asamco BV - Alex
-- Create date: 25-01-2025
-- Description: Health check function on possible Evo data issues.
-- Note: HEAVY query! Careful in usage!
-- =============================================
CREATE or ALTER FUNCTION dbo._as_Health_Overview
(
-- Add the parameters for the function here
@checkDate date /*only for queries requiring a check date.*/
)
RETURNS
@tblHealth TABLE
(
-- Add the column definitions for the TABLE variable here
ErrorType nvarchar(150), /*Critical, High, Warning, Info*/
Module nvarchar(20), /*DB, AR, AP, GL, Budget, etc*/
HealthMessage nvarchar(max),
PossibleImpact nvarchar(max),
NoOfIssues int,
DetailQuery nvarchar(max),
DetailData nvarchar(max),
IsInfoOnly bit default 0
)
AS
BEGIN
/*Usage:
select * from dbo._as_Health_Overview(null)
*/
-- Fill the table variable with the rows for your result set
--DATABASE HEALTH CHECKS
--D1. Missing indices, more than x impact
--D2. Fragmented indices
--D3. Free space, if Express edition.
--D4. ...
--EVOLUTION DATA HEALTH CHECKS
--1. Duplicate budget rows.
;with dbr as (select x.iBudgetAccountID,x.iBudgetProjectID, x.iBudgetPeriodID,x.iBudgetTxBranchId, count(*) NoOfDuplicates
from dbo._etblBudgets x
group by x.iBudgetAccountID,x.iBudgetProjectID, x.iBudgetPeriodID,x.iBudgetTxBranchId
having count(*) > 1)
INSERT INTO @tblHealth (ErrorType,Module,HealthMessage,PossibleImpact,NoOfIssues,DetailQuery,IsInfoOnly)
SELECT CASE WHEN EXISTS(SELECT * FROM DBR) THEN 'ERROR' ELSE 'OK' END
,'Budgets',
CASE WHEN EXISTS(SELECT * FROM DBR) THEN CONCAT('Duplicate budget rows exist.',(SELECT COUNT(*) FROM DBR), ' cases of duplication')
ELSE 'OK. No duplicate budget rows exist.' END
,Impact = CASE WHEN EXISTS(SELECT * FROM DBR) THEN 'Impact: Incorrect budget values & integration issues.' else '' END
,NoOfIssues = COALESCE((SELECT COUNT(*) FROM DBR),0)
,DetailQuery = N'SELECT * FROM _etblBudgets b
LEFT JOIN (select x.iBudgetAccountID,x.iBudgetProjectID, x.iBudgetPeriodID,x.iBudgetTxBranchId, count(*) NoOfDuplicates
from dbo._etblBudgets x
group by x.iBudgetAccountID,x.iBudgetProjectID, x.iBudgetPeriodID,x.iBudgetTxBranchId
having count(*) > 1
) dupes on b.iBudgetAccountID = dupes.iBudgetAccountID and b.iBudgetProjectID = dupes.iBudgetProjectID
and b.iBudgetPeriodID = dupes.iBudgetPeriodID and b.iBudgetTxBranchID = dupes.iBudgetTxBranchID
WHERE dupes.iBudgetAccountID is not null'
,InfoOnly = CASE WHEN EXISTS(SELECT * FROM DBR) THEN 0 ELSE 1 END
--2. Duplicate GL Account codes
;with dGLr as (select g.Master_Sub_Account, count(*) NoOfDuplicates
from dbo.Accounts g group by g.Master_Sub_Account having count(*) > 1)
INSERT INTO @tblHealth (ErrorType,Module,HealthMessage,PossibleImpact,NoOfIssues,DetailQuery,DetailData,IsInfoOnly)
SELECT CASE WHEN EXISTS(SELECT * FROM dGLr) THEN 'ERROR' ELSE 'OK' END
,'GL',
CASE WHEN EXISTS(SELECT * FROM dGLr) THEN CONCAT('Duplicate GL accounts exist.',(SELECT COUNT(*) FROM dGLr), ' cases of duplication')
ELSE 'OK. No duplicate GL accounts exist.' END
,Impact = CASE WHEN EXISTS(SELECT * FROM dGLr) THEN 'Impact: Incorrect report, integration, user confusion' else '' END
,NoOfIssues = COALESCE((SELECT COUNT(*) FROM dGLr),0)
,DetailQuery = N'select a.AccountLink, a.Master_Sub_Account,a.Description FROM Accounts a
where a.Master_Sub_Account IN (select g.Master_Sub_Account
from dbo.Accounts g group by g.Master_Sub_Account having count(*) > 1)'
,DetailData = (select STRING_AGG(a.Master_Sub_Account,',') FROM dGLr a)
,InfoOnly = CASE WHEN EXISTS(SELECT * FROM dGLr) THEN 0 ELSE 1 END
--3. Duplicate AR Account codes
;with dARr as (select ar.Account, count(*) NoOfDuplicates
from dbo.Client ar group by ar.Account having count(*) > 1)
INSERT INTO @tblHealth (ErrorType,Module,HealthMessage,PossibleImpact,NoOfIssues,DetailQuery,DetailData,IsInfoOnly)
SELECT CASE WHEN EXISTS(SELECT * FROM dARr) THEN 'ERROR' ELSE 'OK' END
,'AR',
CASE WHEN EXISTS(SELECT * FROM dARr) THEN CONCAT('Duplicate AR accounts exist.',(SELECT COUNT(*) FROM dARr), ' cases of duplication')
ELSE 'OK. No duplicate AR accounts exist.' END
,Impact = CASE WHEN EXISTS(SELECT * FROM dARr) THEN 'Impact: Incorrect report, integration, user confusion' else '' END
,NoOfIssues = COALESCE((SELECT COUNT(*) FROM dARr),0)
,DetailQuery = N'select c.DCLink,c.Account,C.Name
FROM Client c
where c.Account IN (select ar.Account from dbo.Client ar group by ar.Account having count(*) > 1)'
,DetailData = (select STRING_AGG(a.Account,',') FROM dARr a)
,InfoOnly = CASE WHEN EXISTS(SELECT * FROM dARr) THEN 0 ELSE 1 END
--4. Duplicate AP Account codes
;with dAPr as (select ar.Account, count(*) NoOfDuplicates
from dbo.Vendor ar group by ar.Account having count(*) > 1)
INSERT INTO @tblHealth (ErrorType,Module,HealthMessage,PossibleImpact,NoOfIssues,DetailQuery,DetailData,IsInfoOnly)
SELECT CASE WHEN EXISTS(SELECT * FROM dAPr) THEN 'ERROR' ELSE 'OK' END
,'AP',
CASE WHEN EXISTS(SELECT * FROM dAPr) THEN CONCAT('Duplicate AP accounts exist.',(SELECT COUNT(*) FROM dAPr), ' cases of duplication')
ELSE 'OK. No duplicate AP accounts exist.' END
,Impact = CASE WHEN EXISTS(SELECT * FROM dAPr) THEN 'Impact: Incorrect report, integration, user confusion' else '' END
,NoOfIssues = COALESCE((SELECT COUNT(*) FROM dAPr),0)
,DetailQuery = N'select c.DCLink,c.Account,C.Name
FROM Vendor c
where c.Account IN (select ar.Account from dbo.Vendor ar group by ar.Account having count(*) > 1)'
,DetailData = (select STRING_AGG(a.Account,',') FROM dAPr a)
,InfoOnly = CASE WHEN EXISTS(SELECT * FROM dAPr) THEN 0 ELSE 1 END
--5. Audit trail out of balance.
;with OOB as (SELECT cAuditNumber FROM PostGL p WHERE p.Period > 0 GROUP BY cAuditNumber HAVING ABS(ROUND(SUM(Debit-Credit),2)) > 0.01)
INSERT INTO @tblHealth (ErrorType,Module,HealthMessage,PossibleImpact,NoOfIssues,DetailQuery,DetailData,IsInfoOnly)
SELECT CASE WHEN EXISTS(SELECT * FROM OOB) THEN 'ERROR' ELSE 'OK' END
,'GL',
CASE WHEN EXISTS(SELECT * FROM OOB) THEN CONCAT('Audit Nos out of balance exist.',(SELECT COUNT(*) FROM OOB), ' cases.')
ELSE 'OK. No Audit numbers out of balance.' END
,Impact = CASE WHEN EXISTS(SELECT * FROM OOB) THEN 'Impact: TB out of balance!' else '' END
,NoOfIssues = COALESCE((SELECT COUNT(*) FROM OOB),0)
,DetailQuery = N'SELECT cAuditNumber, * FROM PostGL
WHERE cAuditNumber IN ( SELECT cAuditNumber FROM PostGL GROUP BY cAuditNumber HAVING ABS(ROUND(SUM(Debit-Credit),2)) > 0.01
)
AND Period > 0 --excludes CB unreconciled tx from prior years.
ORDER BY PostGL.cAuditNumber'
,DetailData = CASE WHEN COALESCE((SELECT COUNT(*) FROM OOB),0) > 10 THEN 'First 10:' +
(select STRING_AGG(cAuditNumber,',') FROM (SELECT DISTINCT TOP 10 cAuditNumber FROM OOB) x)
ELSE (select STRING_AGG(cAuditNumber,',') FROM OOB)
END
,InfoOnly = CASE WHEN EXISTS(SELECT * FROM OOB) THEN 0 ELSE 1 END
RETURN
END
GO |