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 TIUDocuments
--
-- Maintenance Log:
--
-- Update By Update Date Description
-- Jayme --------- ----------------------------
-- =============================================
CREATE PROCEDURE zraw.[uspDoSyncTIUDocuments]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRY
--clean TIUDocuments
EXEC zraw.uspDoCleanTIUDocuments;
--Sync TIUDocumentDefinition
EXEC zraw.[uspDoSyncTIUDocumentDefinitions];
--drop the #batchTable if it exists...
--SQL 2016 script-- DROP TABLE IF EXISTS #batchTableTIUDocuments;
--SQL 2014 script--
--Drop temp table
IF OBJECT_ID(N'tempdb..#batchTableTIUDocuments') IS NOT NULL
BEGIN
DROP TABLE #batchTableTIUDocuments;
END
--need to restrict the records we modified to only those available for internalprocessing
SELECT BatchLogID, TableName, BatchDate
INTO #batchTableTIUDocuments
FROM dbo.ufnGetBatchLogsForTableProcessing ('INTPROCESSING', 'zraw._TIUDocuments');
--Update existing records where necessary
UPDATE TIUDocuments SET
RecordStatusID = rawTx.RecordStatusID
, BatchLogID = rawTx.BatchLogID
, CDWExtractBatchsysNumber = rawTx.ExtractBatchID
, CDWTIUDocumentsysNumber = rawTx.TIUDocumentSID
FROM zraw._TIUTIUDocuments rawTx
INNER JOIN #batchTableTIUDocuments bt ON rawTx.BatchLogID = bt.BatchLogID
INNER JOIN TIUDocuments ON rawTx.StationID = TIUDocuments.StationID
AND rawTx.TIUDocumentIEN = TIUDocuments.TIUDocumentIEN
WHERE rawTx.StationID IS NOT NULL
AND rawTx.TIUDocumentID IS NOT NULL;
--insert new records found
INSERT INTO TIUDocuments WITH (TABLOCK)
(
ParentTIUDocumentID
,TIUDocumentDefinitionID
,ParentTIUDocumentDefinitonID
,PatientID
,StationID
,InstitutionID
,DocumentLocationID
,ConsultID
,TIUOrderStatusID
,ReportUrgencyID
,SignedByStaffUserID
,RecordStatusID
,BatchLogID
,CDWTIUDocumentsysnumber
,CDWParentTIUDocumentsysnumber
,EntryDateTime
,EpisodeBeginDateTime
,EpisodeEndDateTime
,SignatureDateTime
,ReferenceDateTime
,VerificationDateTime
,DeletionDateTime
,CDWExtractBatchsysNumber
,TIUDocumentIEN
,VisitType
,DeletionReason
,DocumentSubject
,ProcedureSummaryCode
)
SELECT DISTINCT
ParentTIUDocumentID
,TIUDocumentDefinitionID
,ParentTIUDocumentDefinitonID
,PatientID
,StationID
,InstitutionID
,DocumentLocationID
,ConsultID
,TIUStatusID
,ReportUrgencyID
,SignedByStaffUserID
,RecordStatusID
,rawtx.BatchLogID
,CDWTIUDocumentsysnumber
,CDWParentTIUDocumentsysnumber
,EntryDateTime
,EpisodeBeginDateTime
,EpisodeEndDateTime
,SignatureDateTime
,ReferenceDateTime
,VerificationDateTime
,DeletionDateTime
,CDWExtractBatchsysNumber
,TIUDocumentIEN
,VisitType
,DeletionReason
,DocumentSubject
,ProcedureSummaryCode
FROM zraw._TIUTIUDocuments rawTx
INNER JOIN #batchTableTIUDocuments bt ON rawTx.BatchLogID = bt.BatchLogID
INNER JOIN TIUDocuments ON rawTx.StationID = TIUDocuments.StationID
AND rawTx.TIUDocumentIEN = TIUDocuments.TIUDocumentIEN
WHERE rawTx.StationID IS NOT NULL
AND rawTx.TIUDocumentID IS NOT NULL;
--final cleanup...
--SQL 2016 script-- DROP TABLE IF EXISTS #batchTableTIUDocuments;
--SQL 2014 script--
--Drop temp table
IF OBJECT_ID(N'tempdb..#batchTableTIUDocuments') IS NOT NULL
BEGIN
DROP TABLE #batchTableTIUDocuments;
END
END TRY
BEGIN CATCH
--identify that records in the batch have errored
UPDATE BatchLogs SET
LoadStatus = 'INTERROR'
WHERE EXISTS (SELECT 1 FROM #batchTableTIUDocuments
WHERE BatchLogs.BatchLogID = #batchTableTIUDocuments.BatchLogID);
--drop any raw index objects
--drop the ##batchTableStopCodes if it exists...
--SQL 2016 script-- DROP TABLE IF EXISTS #batchTableTIUDocuments;
--SQL 2014 script--
--Drop temp table
IF OBJECT_ID(N'tempdb..#batchTableTIUDocuments') IS NOT NULL
BEGIN
DROP TABLE #batchTableTIUDocuments;
END
PRINT 'There was an error sychronizing TIU Document data' + CHAR(13);
THROW;
END CATCH
END
GO