IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TransformProgramHasResultFactProc]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[TransformProgramHasResultFactProc]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[TransformProgramHasResultFactProc] (@WaterMark xml)
AS
BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON
--------------------------------------------------------
-- get watermark
--------------------------------------------------------
-- drop table
IF OBJECT_ID('TEMPDB..#wm') IS NOT NULL
DROP TABLE #wm;
-- get watermark info
SELECT * INTO #wm FROM DWRepository.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$Program'
-- get current UTC datetime
SELECT @MaxActivityTimestamp = GETUTCDATE()
--------------------------------------------------------
-- date loop
--------------------------------------------------------
-- is there data in the fact table?
IF (SELECT MAX(DateKey) FROM ProgramHasResultFactvw ) IS NOT NULL
BEGIN
--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.ProgramDimvw ) IS NOT NULL
(
SELECT @ProcessingDateKey = REPLACE(CONVERT(varchar, MIN(CreatedDate),102), '.', '')
FROM dbo.ProgramDimvw
)
ELSE
(
-- change the date to NOT enter the loop becuase there is no data
SELECT @ProcessingDateKey = REPLACE(CONVERT(varchar, DATEADD(dd, 1, GetUTCDate()),102), '.', '')
)
SET @ProcessingDate = SUBSTRING( CONVERT( varchar, @ProcessingDateKey ), 5, 2 ) + '/' +
RIGHT( CONVERT( varchar, @ProcessingDateKey ), 2 ) + '/' +
LEFT( CONVERT( varchar, @ProcessingDateKey ), 4 );
END
SET @EndDateKey = REPLACE(CONVERT( varchar, GETUTCDATE(), 102), '.', '');
--------------------------------------------------------
-- get active programs for today
--------------------------------------------------------
IF OBJECT_ID('TEMPDB..#ProgramsActiveOnProcessingDate') IS NOT NULL
DROP TABLE #ProgramsActiveOnProcessingDate;
SELECT
pd.ProgramDimKey,
pd.Threshold,
phcf.ProgramHasCategory_CategoryDimKey,
phcf.DateKey,
pd.CreatedDate,
phcf.DeletedDate,
phcf.InsertedBatchId,
phcf.UpdatedBatchId
INTO #ProgramsActiveOnProcessingDate
FROM [dbo].[ProgramDimvw] pd
LEFT JOIN [dbo].[ProgramHasCategoriesFactvw] phcf ON pd.ProgramDimKey = phcf.ProgramDimKey
WHERE @ProcessingDate BETWEEN ISNULL(CAST(FLOOR(CAST(phcf.CreatedDate as float)) as datetime),'01/01/2001')
AND ISNULL(CAST(FLOOR(CAST(phcf.DeletedDate as float)) as datetime), '12/31/2099')
AND CAST(FLOOR(CAST(pd.CreatedDate as float)) as datetime) <= @ProcessingDate
AND ( pd.IsDeleted <> 1 OR pd.IsDeleted 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 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 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 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 phco.ProgramDimKey, phco.Threshold, CC.PolicyItemContainsCompliancePolicyItem_ControlObjectiveDimKey as ControlObjectiveDimKey
FROM dbo.ProgramHasCOFactvw AS CC
INNER JOIN ProgramHasCOFactvwActiveOnProcessingDate AS phco
ON CC.PolicyItemContainsCompliancePolicyItem_ControlObjectiveDimKey = phco.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')
AND phco.ControlObjectiveDimKey NOT IN (
SELECT phco.ControlObjectiveDimKey FROM dbo.ProgramHasCOFactvw
)
)
SELECT DISTINCT ProgramDimKey, Threshold, ControlObjectiveDimKey into #ProgramHasCOFactvwActiveOnProcessingDate
FROM ProgramHasCOFactvwActiveOnProcessingDate;
--------------------------------------------------------
-- Get all distinct CO
--------------------------------------------------------
IF OBJECT_ID('TEMPDB..##ProgramCOActiveOnProcessingDate') IS NOT NULL
DROP TABLE ##ProgramCOActiveOnProcessingDate;
SELECT DISTINCT ProgramDimKey, ControlObjectiveDimKey
INTO ##ProgramCOActiveOnProcessingDate
FROM
(
SELECT DISTINCT ProgramDimKey, ControlObjectiveDimKey
FROM #CategoryHasCOFactvwActiveOnProcessingDate
UNION ALL
SELECT DISTINCT ProgramDimKey, ControlObjectiveDimKey
FROM #ProgramHasCOFactvwActiveOnProcessingDate
) ProgramCO
--------------------------------------------------------
-- get CO Contained in the CO's for today
--------------------------------------------------------
IF OBJECT_ID('TEMPDB..#COHasCOFactvwActiveOnProcessingDate') IS NOT NULL
DROP TABLE #COHasCOFactvwActiveOnProcessingDate;
WITH COHasCOFactvwActiveOnProcessingDate (ProgramDimKey, ControlObjectiveDimKey)
AS
(
SELECT coopd.ProgramDimKey, chco.PolicyItemContainsCompliancePolicyItem_ControlObjectiveDimKey as ControlObjectiveDimKey
FROM COHasCOFactvw chco
JOIN ##ProgramCOActiveOnProcessingDate coopd ON coopd.ControlObjectiveDimKey = chco.ControlObjectiveDimKey
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, CC.PolicyItemContainsCompliancePolicyItem_ControlObjectiveDimKey as ControlObjectiveDimKey
FROM dbo.COHasCOFactvw AS CC
INNER JOIN COHasCOFactvwActiveOnProcessingDate 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, ControlObjectiveDimKey
into #COHasCOFactvwActiveOnProcessingDate FROM COHasCOFactvwActiveOnProcessingDate;
--------------------------------------------------------
-- Get all distinct CO
--------------------------------------------------------
IF OBJECT_ID('TEMPDB..##ProgramCOAllOnProcessingDate') IS NOT NULL
DROP TABLE ##ProgramCOAllOnProcessingDate;
SELECT DISTINCT ProgramDimKey, ControlObjectiveDimKey
INTO ##ProgramCOAllOnProcessingDate
FROM
(
SELECT DISTINCT ProgramDimKey, ControlObjectiveDimKey
FROM #COHasCOFactvwActiveOnProcessingDate
UNION ALL
SELECT DISTINCT ProgramDimKey, ControlObjectiveDimKey
FROM ##ProgramCOActiveOnProcessingDate
) ProgramCOActive
IF OBJECT_ID('TEMPDB..##ProgramCONoDuplicates') IS NOT NULL
DROP TABLE ##ProgramCONoDuplicates;
SELECT DISTINCT
p.[ControlObjectiveDimKey] as ControlObjectiveDimKey,
convert(varchar, @ProcessingDateKey) as DateKey,
p.[ProgramDimKey] as ProgramDimKey,
convert(varchar, @BatchId)as InsertedBatchId,
convert(varchar, @BatchId) as UpdatedBatchId
INTO ##ProgramCONoDuplicates
FROM ##ProgramCOAllOnProcessingDate p
WHERE CONVERT( varchar, p.[ProgramDimKey] ) +
CONVERT( varchar, p.[ControlObjectiveDimKey] ) +
CONVERT( varchar, convert(varchar, @ProcessingDateKey)) NOT IN
(SELECT ISNULL( CONVERT( varchar, [ProgramDimKey] ), '-') +
CONVERT( varchar, [ControlObjectiveDimKey] ) +
CONVERT( varchar, [DateKey] )
FROM ProgramHasResultFactvw)
INSERT INTO ProgramHasResultFactvw
(
[ControlObjectiveDimKey] ,
[DateKey] ,
[ProgramDimKey] ,
[InsertedBatchId] ,
[UpdatedBatchId]
)
SELECT *
FROM ##ProgramCONoDuplicates p
--------------------------------------------------------
-- get next date
--------------------------------------------------------
----------------------------------------------------------
---- update watermark
----------------------------------------------------------
UPDATE #wm SET WaterMark = CONVERT(nvarchar(64), ISNULL(@MaxActivityTimestamp,'1/1/1900'),109)
WHERE WarehouseEntityName = N'MTV_System$Compliance$Program' 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