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: Syncs the Consults
--
-- Maintenance Log:
--
-- Update By Update Date Description
-- ----------- --------- ----------------------------
-- =============================================

CREATE PROCEDURE zraw.[uspDoSyncConsults]
@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._conConsult';

--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 Consults
EXEC zraw.uspDoCleanConsults;

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

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

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

--Update existing records where necessary
UPDATE Consults SET
PatientID = rawTx.PatientID
,StationID = rawTx.StationID
,OrderingStationID = rawTx.StationID
,RemoteConsultStationID = rawTX.RemoteConsultStationID


FROM zraw._conConsult rawTx
INNER JOIN #batchTableConsults bt ON rawTx.BatchLogID = bt.BatchLogID
INNER JOIN Consults ON rawTx.StationID = Consults.StationID
AND rawTx.ConsultIEN = Consults.ConsultIEN
WHERE rawTx.StationID IS NOT NULL
AND rawTx.ConsultIEN IS NOT NULL;

--insert new records found
INSERT INTO Consults WITH (TABLOCK)
(
StationID
, RecordStatusID
, BatchLogID
, CDWConsultSysNumber

, ConsultIEN

)
SELECT DISTINCT
rawTx.StationID
,rawTx.RecordStatusID
, rawTx.BatchLogID
, rawTx.ConsultSID
, rawTx.ExtractBatchID
, rawTx.ConsultIEN
, rawTx.ServiceName
, rawTx.ServiceAbbreviation
, rawTx.ConsultCategory
, rawTx.IsCommunityCareRelated
FROM zraw._conConsult rawTx
INNER JOIN #batchTableConsults bt ON rawTx.BatchLogID = bt.BatchLogID
WHERE NOT EXISTS (SELECT 1 FROM Consults
WHERE rawTx.StationID = Consults.StationID
AND rawTx.ConsultIEN = Consults.ConsultIEN)
AND rawTx.ServiceName IS NOT NULL
AND rawTx.StationID IS NOT NULL
AND rawTx.ConsultIEN IS NOT NULL;
/**
ConsultID bigint IDENTITY(1,1) NOT NULL, --EPRS assigned (PK)
PatientID bigint NOT Null, --EPRS assigned (FK), referencess Patients
StationID smallint NULL, --EPRS assigned (FK), references Stations
OrderingStationID smallint NULL, --EPRS assigned (FK), references Stations
RemoteConsultStationID smallint NULL, --EPRS assigned (FK), references Stations
SendingStaffUserID bigint NULL, --EPRS assigned (FK), referencess StaffUsers
ToStaffUserID bigint NULL, --EPRS assigned (FK), referencess StaffUsers
InstitutionID tinyint NULL, --EPRS assigned (FK), references Institutions
OrderingInstitutionID tinyint NULL, --EPRS assigned (FK), references Institutions
RemoteConsultInstitutionID tinyint NULL, --EPRS assigned (FK), references Institutions
PatientLocationID int NULL, --EPRS assigned (FK), references Locations
FromLocationID int NULL, --EPRS assigned (FK), references Locations
UrgencyProtocolID int NULL, --EPRS assigned (FK), referencess Protocals
ConsultPlaceProtocolID int NULL, --EPRS assigned (FK), referencess Protocals
ToRequestServiceID int NULL, --EPRS assigned (FK), references RequestServices
GMRCProcedureSubspecialtyTypeID int NULL, --EPRS assigned (FK), references ProcedureSubspeicaltyTypes
ClinicalProcedureSubspecialtyTypeID int NULL, --EPRS assigned (FK), references ProcedureSubspeicaltyTypes
OrderStatusID tinyint NULL, --EPRS assigned (FK), references Statuses
CPRSStatusTypeID tinyint NULL, --EPRS assigned (FK), references StatusTypes
ActivityTypeID int NULL, --EPRS assigned (FK), references ActivityTypes
RecentActivityTypeID int NULL, --EPRS assigned (FK), references ActivityTypes
RecordStatusID tinyint NULL, --EPRS assigned (Fk), referencess RecordStatuses
TIUDocumentID int NULL, --EPRS Assigned (FK), referencess TIUDocument
BatchLogID bigint NULL, --EPRS assigned (FK), referencess BatchLogs

CDWConsultSysNumber bigint NOT NULL, --CDW ConsultSID
CDWRemoteConsultSysNumber bigint NOT NULL, --CDW RemoteConsultSID
CDWExtractBatchSysNumber bigint NULL, --extractBatchID

ConsultIEN bigint NULL, --CDW ConsultIEN

RequestDateTime datetime2(7) NULL, --CDW value; can be null, use FileEntryDateTime
RequestVistaErrorDate datetime2(7) NULL, --Check VARCHAR
FileEntryDateTime datetime2(7) NULL, --Consult entered datetime
FileEntryVistaErrorDate datetime2(7) NULL, --Check VARCHAR
EarliestDate datetime2(7) NULL, --CID
EarliestVistaErrorDate datetime2(7) NULL, --CDW EarliestVistaErrorDate
VistaCreateDate datetime2(7) NULL, --ETL capture date
VistaEditDate datetime2(7) NULL, --ETL changes based on opcode changes

RequestType VARCHAR (50) NULL, --CDW Value; This field will indicate whether the order is a consult or procedurerequest order. C = Consult P= Procedure Request
ProvisionalDiagnosisCode varchar (10) NULL, --CDWValue; indicates the DX upon entry of the consult
ProvisionalDiagnosis VARCHAR (255) NULL, --CDWValue; the text definition of the DX code entered upon consult creatation
ProvisionalDiagnosisMergeCode VARCHAR (255) NULL, --Mine and merge CDWProvisionalDx and CDWProvisionalDx to have a full saturated list
RemoteService VARCHAR (100) NULL,
RemoteRole VARCHAR (50) NULL,
PlaceOfConsultation VARCHAR (50) NULL, --where the provider wrote up the consult ie, Bed side, ER, on call.
InpatOutpat varchar(50) NULL, --results of a query do not match definition provided many unknowns...
SignificantFindings VARCHAR (5) NULL, --inconsistant values and no magic decoder ring
**/
--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 #batchTableConsults;

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

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

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

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

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

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

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

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