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