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 Consult Factors
--
-- Maintenance Log:
--
-- Update By Update Date Description
-- ----------- --------- ----------------------------
-- =============================================
CREATE PROCEDURE zraw.[uspDoSyncConsultFactors]
@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._conConsultFactor';

--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 ConsultFactors
EXEC zraw.uspDoCleanConsultFactors;

--drop the #batchTableConsultFactors if it exists...
--SQL 2016 script-- DROP TABLE IF EXISTS #batchTableConsultFactors;

--SQL 2014 script--
--Drop temp table
IF OBJECT_ID(N'tempdb..#batchTableConsultFactors') IS NOT NULL
BEGIN
DROP TABLE #batchTableConsultFactors;
END

--populate #batchTableConsultFactors with BatchLogIDs that are ok to process per INTPROCESSING
SELECT BatchLogID, TableName, BatchDate
INTO #batchTableConsultFactors
FROM dbo.ufnGetBatchLogsForTableProcessing ('INTPROCESSING', @TABLE_NAME);

--Update existing records where necessary
UPDATE ConsultFactors SET --UPDATE CORE TABLE FROM THE RAW TABLE; SET EPRS IDS AND CORE TABLE ELMENTS ARE NOT NOT NULL

ConsultID = rawTx.ConsultID
,ConsultActivityID = rawTx.ConsultActivityID
,SConsultActivityCommentID = rawTx.SConsultActivityCommentID
,ConsultFactorTypeID = rawTx.ConsultFactorTypeID
,PatientID = rawTx.PatientID
,StaffUserID = rawTx.StaffUserID
,RecordStatusID = rawTx.RecordStatusID
,BatchLogID = rawTx.BatchLogID

,CDWExtractBatchsysNumber = rawTx.ExtractBatchID
,ActivityDateTime = rawTx.ActivityDateTime
,FactorData = rawTx.FactorData
,DateFactorText = rawTx.DateFactorText
,DateFactorDateTime = rawTx.DateFactorDateTime

,CTBItemFlag = rawTx.CTBItemFlag


FROM zraw._conConsultFactor rawTx
INNER JOIN #batchTableConsultFactors bt ON rawTx.BatchLogID = bt.BatchLogID
INNER JOIN ConsultFactors ON rawTx.StationID = ConsultFactors.StationID
AND rawTx.ConsultFactorID = ConsultFactors.CDWConsultFactorSysnumber
AND try_cast(rawTx.ConsultIEN as bigint) = ConsultFactors.Consultien

WHERE rawTx.StationID IS NOT NULL
AND rawTx.ConsultFactorID IS NOT NULL
AND rawTx.ConsultID IS NOT NULL
AND rawTx.PatientID IS NOT NULL

--insert new records found (ConsultFactorID is PK system generated)
INSERT INTO ConsultFactors WITH (TABLOCK)
(
ConsultFactorID
,StationID
,ConsultID
,ConsultActivityID
,SConsultActivityCommentID
,ConsultFactorTypeID
,PatientID
,StaffUserID
,RecordStatusID
,BatchLogID

,CDWExtractBatchsysNumber

,ActivityDateTime
,DateFactorDateTime
,FactorData
,CTBItemFlag
)
SELECT DISTINCT
rawTx.ConsultFactorID
,rawTx.StationID
,rawTx.ConsultID
,rawTx.ConsultActivityID
,rawTx.SConsultActivityCommentID
,rawTx.ConsultFactorTypeID
,rawTx.PatientID
,rawTx.StaffUserID
,rawTx.RecordStatusID
,rawTx.BatchLogID


,rawTx.CDWExtractBatchsysNumber

,rawTx.ActivityDateTime
,rawTx.DateFactorDateTime
,FactorData
,rawTx.CTBItemFlag

FROM zraw._conConsultFactor rawTx
INNER JOIN #batchTableConsultFactors bt ON rawTx.BatchLogID = bt.BatchLogID
WHERE NOT EXISTS (SELECT 1 FROM ConsultFactors
WHERE rawTx.StationID = Consultfactors.StationID
AND try_cast(rawTx.ConsultIEN as bigint) = ConsultFactors.Consultien
AND rawTx.ConsultFactorSID = ConsultFactors.CDWConsultFactorSysnumber
AND rawTx.ConsultFactorID IS NOT NULL
AND rawTx.ConsultID IS NOT NULL
AND rawTx.PatientID 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 #batchTableConsultFactors;

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 ConsultFactors table, so this snippet won't work
--EXEC uspSetSynchLogs 'ConsultFactors', @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 #batchTableConsultFactors
WHERE BatchLogID > @batchLogID;
END

--update status on batch table records
UPDATE BatchLogs SET
LoadStatus = 'INTCOMPLETE'
, LoadCompleteDate = GETDATE()
WHERE EXISTS (SELECT 1 FROM #batchTableConsultFactors
WHERE BatchLogs.BatchLogID = #batchTableConsultFactors.BatchLogID);

--final cleanup...
--SQL 2016 script-- DROP TABLE IF EXISTS #batchTableConsultFactors;

--SQL 2014 script--
--Drop temp table
IF OBJECT_ID(N'tempdb..#batchTableConsultFactors') IS NOT NULL
BEGIN
DROP TABLE #batchTableConsultFactors;
END

END TRY
BEGIN CATCH
--identify that records in the batch have errored
UPDATE BatchLogs SET
LoadStatus = 'INTERROR'
WHERE EXISTS (SELECT 1 FROM #batchTableConsultFactors
WHERE BatchLogs.BatchLogID = #batchTableConsultFactors.BatchLogID);

--drop the #batchTableConsultFactors if it exists...
--SQL 2016 script-- DROP TABLE IF EXISTS #batchTableConsultFactors;

--SQL 2014 script--
--Drop temp table
IF OBJECT_ID(N'tempdb..#batchTableConsultFactors') IS NOT NULL
BEGIN
DROP TABLE #batchTableConsultFactors;
END

PRINT 'There was an error synchronizing raw Consult Factor data' + CHAR(13);
THROW;
END CATCH
END