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: Master raw synchronizer proc, synching data into ODS from incoming data found in the incoming raw table(s)
--
-- Maintenance Log:
--
-- Update By Update Date Description
-- ----------- --------- ----------------------------
-- =============================================
CREATE PROCEDURE zraw.[uspMasterDoSynchOneConsultDataFromRaw]
--not so sure about the cleaning for this one--
@canCleanStagingTables bit
AS
BEGIN
DECLARE @batchDateTable TABLE
(BatchLogID bigint,
TableName varchar (50),
BatchDate datetime2(7)
)
DECLARE @batchLogID BIGINT,
@resultOk int,
@canProcess bit;
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--Initialize certain processing variables
SET @canProcess = 0;
BEGIN TRY
--Get BatchDate to process (from BatchLogs)
INSERT INTO @batchDateTable (BatchLogID,TableName, BatchDate)
SELECT BatchLogID, TableName, BatchDate
FROM dbo.ufnGetBatchLogBatchDate('EXTCOMPLETE', null)
ORDER BY BatchDate;
--Check to see if there is actually any data in the raws to load for these periods
--the data may have been removed from raws after the batch log was told ok, set to EXTCOMPLETE
--Check VetGEO first
IF (SELECT COUNT(1) FROM zraw._conConsult WHERE BatchLogID IN(SELECT BatchLogID FROM @batchDateTable)) > 0
BEGIN
SET @canProcess = 1;
END
IF @canProcess = 1
--If there are any periods to do stuff to, process away!
--Because of problems with log file growing to enormous size, a shrink log is done after every sync step.
BEGIN
--Set all BatchLogs LoadStatus fields = 'INTPROCESSING'
UPDATE dbo.BatchLogs SET LoadStatus = 'INTPROCESSING'
WHERE BatchLogID In(SELECT BatchLogID FROM @batchDateTable);
/****CREATE NCI values needed****/
--CREATE NCI indexes for raw table before sync
EXEC dbo.[uspDoCreateRawConsultIndexsPreCoreLoad];
/*****INITIAL CLEANERS OF RAW DATA****/
--EXEC uspMasterDoCleanRaw
--instead of cleaning raw, we'll probably just have to truncate the table for CDW Enclave
/*****SYNC DEFINITIONS***/
--synch Status types
EXEC dbo.uspDoSyncStatusTypes;
--DBCC SHRINKFILE (N'LogData' , 0, TRUNCATEONLY) WITH NO_INFOMSGS --wont be able to do this @CDW
--synch Request Services
EXEC dbo.uspDoSyncRequestServices;
/****FILL IN FK HOOKS PRIOR TO CORE SYNC****/
-- fill station hooks
EXEC dbo.uspDoSyncRawFKOneConsultStations;
/****CORE SYNC to OneConsults table ****/
---load consults data
EXEC dbo.uspDoSynchOneConsults;
--update CORE with BIT value
EXEC dbo.uspDoSetOneConsultsHasProvisionalDiagnosis;
/****Drop NCI values no longer needed****/
EXEC dbo.[uspDoDropRawConsultIndexsPostCoreLoad];
--Next, set all BatchLogs LoadStatus fields = 'INTCOMPLETE' where matches the
--@batchDateTable choices, to show the load was completed
UPDATE dbo.BatchLogs SET LoadStatus = 'INTCOMPLETE', LoadCompleteDate = GETDATE()
WHERE BatchLogID In(SELECT BatchLogID FROM @batchDateTable);
END
END TRY
BEGIN CATCH
PRINT 'There was an error with the master batch synchronizer' + CHAR(13);
THROW;
END CATCH
END