Summary Table
Categories |
Total Count |
PII |
0 |
URL |
0 |
DNS |
0 |
EKL |
0 |
IP |
0 |
PORT |
0 |
VsID |
0 |
CF |
0 |
AI |
0 |
VPD |
0 |
PL |
0 |
Other |
0 |
File Content
?-- =============================================
-- Description: Synchs the Services from OneConsult data pull field ToRequestServiceName
--
-- Maintenance Log:
--
-- Update By Update Date Description
-- ----------- --------- ----------------------------
-- =============================================
CREATE PROCEDURE zraw.[uspDoSyncStopCodes]
@canCleanStagingTables bit
AS
DECLARE @batchLogID bigint,
@resultOk bit,
@TABLE_NAME varchar(100); --make constant value for use thoughout
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRY
--Set @TABLE_NAME value
SET @TABLE_NAME = 'zraw._dimStopCode';
--If there are any records in BatchLogs for dimSta3n that are marked EXTCOMPLETE
--update the status to INTPROCESSING;
UPDATE dbo.BatchLogs SET
LoadStatus = 'INTPROCESSING'
WHERE LoadStatus = 'EXTCOMPLETE'
AND TableName = @TABLE_NAME;
--clean RequestServices
EXEC zraw.uspDoCleanStopCodes;
--drop the #batchTable if it exists...
--SQL 2016 script-- DROP TABLE IF EXISTS #batchTableStopCodes;
--SQL 2014 script--
--Drop temp table
IF OBJECT_ID(N'tempdb..#batchTableStopCodes') IS NOT NULL
BEGIN
DROP TABLE #batchTableStopCodes;
END
--need to restrict the records we modified to only those available for internalprocessing
SELECT BatchLogID, TableName, BatchDate
INTO #batchTableStopCodes
FROM dbo.ufnGetBatchLogsForTableProcessing ('INTPROCESSING', @TABLE_NAME);
--Update existing records where necessary
--RestrictionType, replace [E = either, P = primary, S = secondary]
UPDATE StopCodes SET
RecordStatusID = rawTx.RecordStatusID
, BatchLogID = rawTx.BatchLogID
, StopCodeName = rawTx.StopCodeName
, CDWExtractBatchNumber = rawTx.ExtractBatchID
, CDWStopCodeSysNumber = rawTx.StopCodeSID
, ConvertToStopCodeIEN = rawTx.ConvertToStopCodeIEN
, RestrictionType = CASE rawTx.RestrictionType
WHEN 'P' THEN 'Primary'
WHEN 'S' THEN 'Secondary'
WHEN 'E' THEN 'Either'
ELSE RestrictionType
END
, IsInactive = CASE WHEN rawTx.InactiveDate IS NULL
THEN 0
ELSE 1
END
FROM zraw._dimStopCode rawTx
INNER JOIN #batchTableStopCodes bt ON rawTx.BatchLogID = bt.BatchLogID
INNER JOIN StopCodes ON rawTx.StationID = StopCodes.StationID
AND rawTx.StopCodeIEN = StopCodes.StopCodeIEN
WHERE rawTx.StationID IS NOT NULL
AND rawTx.StopCodeName IS NOT NULL
;
--insert new records found
INSERT INTO StopCodes WITH (TABLOCK)
(
StationID
, RecordStatusID
, BatchLogID
, CDWStopCodeSysNumber
, CDWExtractBatchNumber
, StopCodeIEN
, ConvertToStopCodeIEN
, StopCode
, StopCodeName
, RestrictionType
, IsInactive
)
SELECT DISTINCT
StationID
, RecordStatusID
, rawTx.BatchLogID
, StopCodeSID
, ExtractBatchID
, StopCodeIEN
, ConvertToStopCodeIEN
, StopCode
, StopCodeName
, CASE rawTx.RestrictionType
WHEN 'P' THEN 'Primary'
WHEN 'S' THEN 'Secondary'
WHEN 'E' THEN 'Either'
ELSE RestrictionType
END AS RestrictionType
, CASE WHEN rawTx.InactiveDate IS NULL
THEN 0
ELSE 1
END AS IsInactive
FROM zraw._dimStopCode rawTx
INNER JOIN #batchTableStopCodes bt ON rawTx.BatchLogID = bt.BatchLogID
WHERE NOT EXISTS (SELECT 1 FROM StopCodes
WHERE rawTx.StationID = StopCodes.StationID
AND rawTx.StopCodeIEN = StopCodes.StopCodeIEN)
AND rawTx.StationID IS NOT NULL
AND rawTx.StopCodeName IS NOT NULL
AND rawTx.StopCode IS NOT NULL;
--Log the records touched and delete records from raw
--HOWEVER, only do this if the incoming parameter @canCleanStagingTables = true (1)
SELECT @batchLogID = MIN(BatchLogID)
FROM #batchTableStopCodes;
WHILE @batchLogID <> 0
BEGIN
--update synch logs for how many fact records inserted/updated
--note that the first param (@tableName) will need to include the schema name, if not dbo
--We aren't tracking batchlog identifiers in StopCodes table, so this snippet won't work
--EXEC uspSetSynchLogs 'StopCodes', @batchLogID
IF @canCleanStagingTables = 1
BEGIN
--delete the raws
EXEC uspDoDeleteRaw @batchLogID, @TABLE_NAME, @resultOk OUTPUT;
DBCC SHRINKFILE (N'LogData' , 0, TRUNCATEONLY) WITH NO_INFOMSGS;
END
--get the next key in the batch table
SELECT @batchLogID = MIN(BatchLogID)
FROM #batchTableStopCodes
WHERE BatchLogID > @batchLogID;
END
--update status on batch table records
UPDATE BatchLogs SET
LoadStatus = 'INTCOMPLETE'
, LoadCompleteDate = GETDATE()
WHERE EXISTS (SELECT 1 FROM #batchTableStopCodes
WHERE BatchLogs.BatchLogID = #batchTableStopCodes.BatchLogID);
--final cleanup...
--SQL 2016 script-- DROP TABLE IF EXISTS #batchTableStopCodes;
--SQL 2014 script--
--Drop temp table
IF OBJECT_ID(N'tempdb..#batchTableStopCodes') IS NOT NULL
BEGIN
DROP TABLE #batchTableStopCodes;
END
END TRY
BEGIN CATCH
--identify that records in the batch have errored
UPDATE BatchLogs SET
LoadStatus = 'INTERROR'
WHERE EXISTS (SELECT 1 FROM #batchTableStopCodes
WHERE BatchLogs.BatchLogID = #batchTableStopCodes.BatchLogID);
--drop the #batchTableStopCodes if it exists...
--SQL 2016 script-- DROP TABLE IF EXISTS #batchTableStopCodes;
--SQL 2014 script--
--Drop temp table
IF OBJECT_ID(N'tempdb..#batchTableStopCodes') IS NOT NULL
BEGIN
DROP TABLE #batchTableStopCodes;
END
PRINT 'There was an error synchronizing raw Stop Code data' + CHAR(13);
THROW;
END CATCH
END