IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TransformControlObjectiveHasResultFactProc]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[TransformControlObjectiveHasResultFactProc]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[TransformControlObjectiveHasResultFactProc] (@WaterMark xml)
AS
BEGIN
--------------------------------------------------------
-- get watermark
--------------------------------------------------------
-- drop table
IF OBJECT_ID('TEMPDB..#wm') IS NOT NULL
DROP TABLE #wm;
-- get watermark info
SELECT * INTO #wm FROM etl.ShredWaterMark(@WaterMark);
-- get last run water mark
SELECT TOP 1 @ActivityTimestamp = WaterMark, @BatchId = ISNULL(BatchId, 1)
FROM #wm
WHERE WarehouseEntityName = N'MTV_System$Compliance$ControlObjective'
-- get current UTC datetime
SELECT @MaxActivityTimestamp = GETUTCDATE()
--------------------------------------------------------
-- date loop
--------------------------------------------------------
-- is there data in the fact table?
IF (SELECT MAX(DateKey) FROM dbo.ControlObjectiveHasResultFactvw ) IS NOT NULL
BEGIN
SELECT @ProcessingDateKey = MAX(DateKey) FROM dbo.ControlObjectiveHasResultFactvw
SET @ProcessingDate = SUBSTRING( CONVERT( varchar, @ProcessingDateKey ), 5, 2 ) + '/' +
RIGHT( CONVERT( varchar, @ProcessingDateKey ), 2 ) + '/' +
LEFT( CONVERT( varchar, @ProcessingDateKey ), 4 );
--Add 1 day to the Processing Day to begin processing for next day
SET @ProcessingDate = DATEADD(dd, 1, @ProcessingDate);
SELECT @ProcessingDateKey = REPLACE(CONVERT(varchar, DATEADD(dd, 1, @ProcessingDate),102), '.', '')
END
ELSE
BEGIN
-- is there data in the source table?
IF (SELECT MIN(CreatedDate) FROM dbo.ControlObjectiveDimvw ) IS NOT NULL
SELECT @ProcessingDateKey = REPLACE(CONVERT(varchar, MIN(CreatedDate),102), '.', '')
FROM dbo.ControlObjectiveDimvw
ELSE
-- change the date to NOT enter the loop becuase there is no data
SELECT @ProcessingDateKey = REPLACE(CONVERT(varchar, DATEADD(dd, 1, GetUTCDate()),102), '.', '');
--------------------------------------------------------
-- get active programs for today
--------------------------------------------------------
IF OBJECT_ID('TEMPDB..#ProgramsActiveOnProcessingDate') IS NOT NULL
DROP TABLE #ProgramsActiveOnProcessingDate;
SELECT
pd.ProgramDimKey,
pd.Threshold,
pd.CreatedDate,
pd.IsDeleted
INTO #ProgramsActiveOnProcessingDate
FROM [dbo].[ProgramDimvw] pd
WHERE CAST(FLOOR(CAST(pd.CreatedDate as float)) as datetime) <= @ProcessingDate
AND ( pd.IsDeleted <> 1 OR pd.IsDeleted IS NULL )
--------------------------------------------------------------
-- Get all policy exceptions related to the active programs
-- and also the corresponding CAs that are excluded as part of
-- those exceptions
----------------------------------------------------------------
IF OBJECT_ID('TEMPDB..#PolicyExceptionsActiveOnProcessingDate') IS NOT NULL
DROP TABLE #PolicyExceptionsActiveOnProcessingDate;
SELECT
ExPr.ExceptionRelatedPolicyItem_ProgramDimKey AS [ProgramDimKey],
ExCA.ExceptionRelatedWorkItem_ControlActivityDimKey AS [ControlActivityDimKey]
INTO
#PolicyExceptionsActiveOnProcessingDate
FROM ExceptionRelatesToProgramFactvw ExPr
JOIN ExceptionRelatesToCAFactvw ExCA
ON ExPr.ExceptionDimKey = ExCA.ExceptionDimKey
JOIN ExceptionDimvw Ex ON ExPr.ExceptionDimKey = Ex.ExceptionDimKey
AND Ex.Type_ExceptionTypeId = (SELECT ExceptiontypeId from ExceptionTypevw WHERE ID='ExceptionTypeEnum.Policy')
AND Ex.Status_ExceptionStatusId = (SELECT ExceptionStatusId from ExceptionStatusVw WHERE ID='ExceptionStatusEnum.Approved')
AND @ProcessingDate BETWEEN CAST(FLOOR(CAST(Ex.ApprovedStartDate as float)) as datetime)
AND CAST(FLOOR(CAST(Ex.ApprovedEndDate as float)) as datetime)
AND (Ex.IsDeleted <> 1 OR Ex.IsDeleted IS NULL )
JOIN #ProgramsActiveOnProcessingDate Pr ON Pr.ProgramDimKey = ExPr.ExceptionRelatedPolicyItem_ProgramDimKey
WHERE ExPr.DeletedDate IS NULL
AND ExCA.DeletedDate IS NULL
--------------------------------------------------------
-- get the categories
--------------------------------------------------------
IF OBJECT_ID('TEMPDB..#CategoryActiveOnProcessingDate') IS NOT NULL
DROP TABLE #CategoryActiveOnProcessingDate;
WITH CategoryActiveOnProcessingDate (ProgramDimKey, Threshold, CategoryDimKey)
AS
(
SELECT p.ProgramDimKey, p.Threshold, phc.ProgramHasCategory_CategoryDimKey AS CategoryDimKey
FROM [dbo].ProgramHasCategoriesFactvw phc
JOIN #ProgramsActiveOnProcessingDate p ON p.ProgramDimKey = phc.ProgramDimKey
WHERE phc.DeletedDate IS NULL
AND CAST(FLOOR(CAST(phc.CreatedDate as float)) as datetime) <= @ProcessingDate
UNION ALL
SELECT ProgramDimKey, Threshold, CategoryContainsCategory_CategoryDimKey as CategoryDimKey
FROM [dbo].CategoryContainsCategoryFactvw AS CC
INNER JOIN CategoryActiveOnProcessingDate AS CD
ON CC.CategoryDimKey = CD.CategoryDimKey
WHERE @ProcessingDate BETWEEN CAST(FLOOR(CAST(CC.CreatedDate as float)) as datetime)
AND ISNULL(CAST(FLOOR(CAST(CC.DeletedDate as float)) as datetime), '12/31/2099')
)
SELECT DISTINCT ProgramDimKey, Threshold, CategoryDimKey into #CategoryActiveOnProcessingDate FROM CategoryActiveOnProcessingDate;
--------------------------------------------------------
-- get CO of the categories for today
--------------------------------------------------------
IF OBJECT_ID('TEMPDB..#CategoryHasCOFactvwActiveOnProcessingDate') IS NOT NULL
DROP TABLE #CategoryHasCOFactvwActiveOnProcessingDate;
WITH CategoryHasCOFactvwActiveOnProcessingDate (ProgramDimKey, Threshold, CategoryDimKey, ControlObjectiveDimKey)
AS
(
SELECT caopd.ProgramDimKey, caopd.Threshold, caopd.CategoryDimKey, chco.CategoryContainsControlObjective_ControlObjectiveDimKey as ControlObjectiveDimKey
FROM [dbo].CategoryHasCOFactvw chco
JOIN #CategoryActiveOnProcessingDate caopd ON caopd.CategoryDimKey = chco.CategoryDimKey
WHERE @ProcessingDate BETWEEN CAST(FLOOR(CAST(chco.CreatedDate as float)) as datetime)
AND ISNULL(CAST(FLOOR(CAST(chco.DeletedDate as float)) as datetime), '12/31/2099')
UNION ALL
SELECT chco.ProgramDimKey, chco.Threshold, chco.CategoryDimKey, CC.PolicyItemContainsCompliancePolicyItem_ControlObjectiveDimKey as ControlObjectiveDimKey
FROM [dbo].COHasCOFactvw AS CC
INNER JOIN CategoryHasCOFactvwActiveOnProcessingDate AS chco
ON CC.ControlObjectiveDimKey = chco.ControlObjectiveDimKey
WHERE @ProcessingDate BETWEEN CAST(FLOOR(CAST(CC.CreatedDate as float)) as datetime)
AND ISNULL(CAST(FLOOR(CAST(CC.DeletedDate as float)) as datetime), '12/31/2099')
)
SELECT DISTINCT ProgramDimKey, Threshold, CategoryDimKey, ControlObjectiveDimKey into #CategoryHasCOFactvwActiveOnProcessingDate FROM CategoryHasCOFactvwActiveOnProcessingDate;
--------------------------------------------------------
-- get CO of the Programs for today
--------------------------------------------------------
IF OBJECT_ID('TEMPDB..#ProgramHasCOFactvwActiveOnProcessingDate') IS NOT NULL
DROP TABLE #ProgramHasCOFactvwActiveOnProcessingDate;
WITH ProgramHasCOFactvwActiveOnProcessingDate (ProgramDimKey, Threshold, ControlObjectiveDimKey)
AS
(
SELECT p.ProgramDimKey, p.Threshold, phco.PolicyItemContainsCompliancePolicyItem_ControlObjectiveDimKey as ControlObjectiveDimKey
FROM [dbo].ProgramHasCOFactvw phco
JOIN #ProgramsActiveOnProcessingDate p ON p.ProgramDimKey = phco.ProgramDimKey
WHERE @ProcessingDate BETWEEN CAST(FLOOR(CAST(phco.CreatedDate as float)) as datetime)
AND ISNULL(CAST(FLOOR(CAST(phco.DeletedDate as float)) as datetime), '12/31/2099')
UNION ALL
SELECT chco.ProgramDimKey, chco.Threshold, CC.PolicyItemContainsCompliancePolicyItem_ControlObjectiveDimKey as ControlObjectiveDimKey
FROM [dbo].COHasCOFactvw AS CC
INNER JOIN ProgramHasCOFactvwActiveOnProcessingDate AS chco
ON CC.ControlObjectiveDimKey = chco.ControlObjectiveDimKey
WHERE @ProcessingDate BETWEEN CAST(FLOOR(CAST(CC.CreatedDate as float)) as datetime)
AND ISNULL(CAST(FLOOR(CAST(CC.DeletedDate as float)) as datetime), '12/31/2099')
)
SELECT DISTINCT ProgramDimKey, Threshold, ControlObjectiveDimKey into #ProgramHasCOFactvwActiveOnProcessingDate
FROM ProgramHasCOFactvwActiveOnProcessingDate;
--------------------------------------------------------
-- combine the prog-cat-co and prog-co lists
--------------------------------------------------------
IF OBJECT_ID('TEMPDB..#ProgramCOCombo') IS NOT NULL
DROP TABLE #ProgramCOCombo;
SELECT DISTINCT ProgramDimKey, Threshold, ControlObjectiveDimKey
INTO #ProgramCOCombo
FROM
(
SELECT DISTINCT ProgramDimKey, Threshold, ControlObjectiveDimKey
FROM #CategoryHasCOFactvwActiveOnProcessingDate
UNION ALL
SELECT DISTINCT ProgramDimKey, Threshold, ControlObjectiveDimKey
FROM #ProgramHasCOFactvwActiveOnProcessingDate
) ProgCatCo;
--------------------------------------------------------
-- get CA of the COs for today
--------------------------------------------------------
IF OBJECT_ID('TEMPDB..##COHasCAFactvwActiveOnProcessingDate') IS NOT NULL
DROP TABLE ##COHasCAFactvwActiveOnProcessingDate;
WITH COHasCAFactvwActiveOnProcessingDate (ProgramDimKey, Threshold, ControlObjectiveDimKey, ControlActivityDimKey)
AS
(
SELECT chcof.ProgramDimKey, chcof.Threshold, chcof.ControlObjectiveDimKey,
cohcaf.ControlObjectiveContainsControlActivity_ControlActivityDimKey AS ControlActivityDimKey
FROM COHasCAFactvw cohcaf
JOIN #ProgramCOCombo chcof ON chcof.ControlObjectiveDimKey = cohcaf.ControlObjectiveDimKey
WHERE @ProcessingDate BETWEEN CAST(FLOOR(CAST(cohcaf.CreatedDate as float)) as datetime)
AND ISNULL(CAST(FLOOR(CAST(cohcaf.DeletedDate as float)) as datetime), '12/31/2099')
)
SELECT DISTINCT cohca.ProgramDimKey, Threshold, ControlObjectiveDimKey, cohca.ControlActivityDimKey
into ##COHasCAFactvwActiveOnProcessingDate
FROM COHasCAFactvwActiveOnProcessingDate cohca
-- Exclude CAs which are part of the Active policy exceptions
LEFT JOIN #PolicyExceptionsActiveOnProcessingDate PolEx
ON cohca.ProgramDimKey = PolEx.ProgramDimKey
AND cohca.ControlActivityDimKey = PolEx.ControlActivityDimKey
WHERE PolEx.ProgramDimKey IS NULL AND PolEx.ControlActivityDimKey IS NULL
--------------------------------------------------------
-- scoring
--------------------------------------------------------
IF OBJECT_ID('TEMPDB..##Scoring') IS NOT NULL
DROP TABLE ##Scoring;
SELECT t.ProgramDimKey, t.ControlObjectiveDimKey, t.ControlActivityDimKey,
ISNULL(t.Threshold, 100) 'Threshold',
ISNULL(c.CompliantCount, 0) 'CompliantCount',
ISNULL(n.NonCompliantCount, 0) 'NonCompliantCount',
ISNULL(tot.Total, 0) 'Total',
CASE
WHEN (CompliantCount * 100)/ Total >= Threshold THEN (SELECT CAScoreId FROM CAScorevw WHERE CAScoreValue='Compliant')
WHEN (NonCompliantCount * 100) / Total >= (100 - Threshold) THEN (SELECT CAScoreId FROM CAScorevw WHERE CAScoreValue='Non-Compliant')
ELSE (SELECT CAScoreId FROM CAScorevw WHERE CAScoreValue='Unknown')
END 'ScoreValue'
INTO ##Scoring
FROM
(
SELECT s.ProgramDimKey, s.ControlObjectiveDimKey, s.ControlActivityDimKey,
ISNULL(ISNULL(MAX(ControlActivityThreshold), MAX(ProgramThreshold)), 100) 'Threshold'
FROM ##COHasCAFactvwActiveOnProcessingDate s
JOIN ControlActivityHasResultFactvw c
ON s.ProgramDimKey = c.ProgramDimKey
AND s.ControlActivityDimKey = c.ControlActivityDimKey
WHERE c.DateKey = convert(varchar, @ProcessingDateKey)
group by s.ProgramDimKey, s.ControlObjectiveDimKey, s.ControlActivityDimKey
) t
LEFT OUTER JOIN
(
SELECT s.ProgramDimKey, s.ControlObjectiveDimKey, s.ControlActivityDimKey,
COUNT(1) 'CompliantCount'
FROM ##COHasCAFactvwActiveOnProcessingDate s
JOIN ControlActivityHasResultFactvw c
ON s.ProgramDimKey = c.ProgramDimKey
AND s.ControlActivityDimKey = c.ControlActivityDimKey
LEFT JOIN MERResult m ON m.MERResultId = c.MERResultId
LEFT JOIN CAResult car ON car.CAResultId = c.CAResultId
WHERE (m.MERResultValue = 'Compliant'
OR car.CAResultValue = 'Compliant')
AND c.DateKey = convert(varchar, @ProcessingDateKey)
GROUP BY s.ProgramDimKey, s.ControlObjectiveDimKey, s.ControlActivityDimKey
) c ON c.ControlObjectiveDimKey = t.ControlObjectiveDimKey
AND c.ControlActivityDimKey = t.ControlActivityDimKey
LEFT OUTER JOIN
(
SELECT s.ProgramDimKey, s.ControlObjectiveDimKey, s.ControlActivityDimKey,
ISNULL(COUNT(1), 0) 'NonCompliantCount'
FROM ##COHasCAFactvwActiveOnProcessingDate s
JOIN ControlActivityHasResultFactvw c
ON s.ProgramDimKey = c.ProgramDimKey
AND s.ControlActivityDimKey = c.ControlActivityDimKey
LEFT JOIN MERResult m ON m.MERResultId = c.MERResultId
LEFT JOIN CAResult car ON car.CAResultId = c.CAResultId
WHERE (m.MERResultValue = 'Non-Compliant'
OR car.CAResultValue = 'Non-Compliant')
AND c.DateKey = convert(varchar, @ProcessingDateKey)
GROUP BY s.ProgramDimKey, s.ControlObjectiveDimKey, s.ControlActivityDimKey
) n ON n.ControlObjectiveDimKey = t.ControlObjectiveDimKey
AND n.ControlActivityDimKey = t.ControlActivityDimKey
LEFT OUTER JOIN
(
SELECT s.ProgramDimKey, s.ControlObjectiveDimKey, s.ControlActivityDimKey,
COUNT(1) 'Total'
FROM ##COHasCAFactvwActiveOnProcessingDate s
JOIN ControlActivityHasResultFactvw c
ON s.ProgramDimKey = c.ProgramDimKey
AND s.ControlActivityDimKey = c.ControlActivityDimKey
WHERE c.DateKey = convert(varchar, @ProcessingDateKey)
GROUP BY s.ProgramDimKey, s.ControlObjectiveDimKey, s.ControlActivityDimKey
) tot ON tot.ControlObjectiveDimKey = t.ControlObjectiveDimKey
AND tot.ControlActivityDimKey = t.ControlActivityDimKey
IF OBJECT_ID('TEMPDB..##ControlObjectiveDups') IS NOT NULL
DROP TABLE ##ControlObjectiveDups;
SELECT DISTINCT
[ProgramDimKey] ,
[ControlObjectiveDimKey] ,
[ControlActivityDimKey] ,
convert(varchar, @ProcessingDateKey) AS [DateKey],
[ScoreValue] ,
convert(varchar, @BatchId) AS [InsertedBatchId],
convert(varchar, @BatchId) AS [UpdatedBatchId]
INTO ##ControlObjectiveDups
FROM ##Scoring s
WHERE CONVERT( varchar, s.[ProgramDimKey] ) +
CONVERT( varchar, s.[ControlObjectiveDimKey] ) +
CONVERT( varchar, s.[ControlActivityDimKey] ) +
CONVERT( varchar, convert(varchar, @ProcessingDateKey)) NOT IN
(SELECT '-'
UNION ALL
SELECT CONVERT( varchar, [ProgramDimKey] ) +
CONVERT( varchar, [ControlObjectiveDimKey] ) +
CONVERT( varchar, [ControlActivityDimKey] ) +
CONVERT( varchar, [DateKey] )
FROM ControlObjectiveHasResultFactVW)
--------------------------------------------------------
-- update watermark
--------------------------------------------------------
UPDATE #wm SET WaterMark = CONVERT(nvarchar(64), ISNULL(@MaxActivityTimestamp,'1/1/1900'),109)
WHERE WarehouseEntityName = N'MTV_System$Compliance$ControlObjective' and WaterMarkType= N'DateTime'
SELECT @WaterMark = (SELECT ModuleName AS "@ModuleName",
ProcessName AS "@ProcessName",
@BatchId AS "@BatchId",
(SELECT DISTINCT WarehouseEntityName AS "@WarehouseEntityName",
WarehouseEntityTypeName AS "@WarehouseEntityTypeName",
EntityGuid AS "@EntityGuid",
CASE WarehouseEntityTypeName
WHEN 'Inbound' THEN 'DateTime'
ELSE 'BatchId' END AS "@WaterMarkType",
WaterMark as "@WaterMark"
FROM #wm
FOR XML path('Entity'), type)
FROM (SELECT DISTINCT ModuleName, ProcessName from #wm) a
FOR XML path('Module'), type)
SELECT @WaterMark as WaterMark, @BatchId as BatchId, @Updated as UpdatedRowCount, @Inserted as InsertedRowCount
Home