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: SCHEDULED FOR DELETION: Synchs the OneConsult data needed for facting from Con.Consult ETL
--
-- Maintenance Log:
--
-- Update By Update Date Description
-- ----------- --------- ----------------------------
-- =============================================
CREATE PROCEDURE zraw.[uspDoSynchOneConsults]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @batchTable TABLE
(
BatchLogID bigint Primary Key,
TableName varchar (50),
BatchDate datetime2(7)
)
BEGIN TRY
--need to restrict the records we modified to only those available for internalprocessing
INSERT INTO @batchTable (BatchLogID, TableName, BatchDate)
SELECT * FROM dbo.ufnGetBatchLogBatchDate ('INTPROCESSING', 'zraw._conConsult')
--update existing records for any changes in non-static data
UPDATE [zraw].[_conConsult]
SET OneConsultID = OC.OneConsultID
FROM dbo.OneConsults OC
INNER JOIN [zraw].[_conConsult] rawtx on OC.CDWConsultSysNumber = rawtx.ConsultSID
INNER JOIN @batchTable bt ON rawtx.BATCHLOGID = bt.BatchLogID
--update the OneConsults data for any data that changed
UPDATE dbo.OneConsults
SET StatusTypeID = rawtx.StatusTypeID
,RequestServiceID = rawtx.RequestServiceID
,BatchLogID = rawTx.BATCHLOGID
,ProvisionalDiagnosisText = rawtx.ProvisionalDiagnosis
,ProvisionalDiagnosisCode = rawtx.ProvisionalDiagnosisCode
,DateSynchronized = GETDATE()
FROM [zraw].[_conConsult] rawtx
INNER JOIN @batchTable bt ON rawTx.BATCHLOGID = bt.BatchLogID
INNER JOIN dbo.OneConsults ON rawTx.OneConsultID = dbo.OneConsults.OneConsultID
--insert new records found
--_conOneConsult
INSERT INTO dbo.OneConsults WITH (TABLOCK)
([StationID]
,[StatusTypeID]
,[RequestServiceID]
,[BatchLogID]
,CDWConsultSysNumber
,[ProvisionalDiagnosisText]
,[ProvisionalDiagnosisCode]
,[FileEntryDateTime]
,[DateSynchronized])
SELECT [StationID]
,[StatusTypeID]
,[RequestServiceID]
,rawtx.[BatchLogID]
,[ConsultSID]
,ProvisionalDiagnosis AS ProvisionalDiagnosisText
,[ProvisionalDiagnosisCode]
,[FileEntryDateTime]
,GETDATE()
FROM [zraw].[_conConsult] rawtx
INNER JOIN @batchTable bt ON rawtx.BATCHLOGID = bt.BatchLogID
WHERE NOT EXISTS (SELECT 1 FROM dbo.OneConsults
WHERE rawtx.ConsultSID = dbo.OneConsults.CDWConsultSysNumber)
END TRY
BEGIN CATCH
PRINT 'There was an error synchronizing OneConsult data' + CHAR(13);
THROW;
END CATCH
END