/
***************************************************************************
*****************
WAAOD → PHAOD MIGRATION FRAMEWORK
------------------------------------------------------------------
Components:
1. AOD_MigrationLog - Logs each CHID migration result
2. AOD_MigrationBatchLog - Logs each batch run summary
3. sp_WAAOD_to_PHAOD - Processes a single CHID transactionally
4. sp_Run_WAAOD_to_PHAOD_Migration - Driver for one or multiple
TeamIDs (restart-safe)
***************************************************************************
*****************/
/*********************************************
STEP 1: CREATE LOGGING TABLES
*********************************************/
IF OBJECT_ID('dbo.AOD_MigrationLog') IS NULL
BEGIN
CREATE TABLE dbo.AOD_MigrationLog (
LogID INT IDENTITY(1,1) PRIMARY KEY,
CHID INT NOT NULL,
Status VARCHAR(20) NOT NULL, -- 'SUCCESS' or 'FAILED'
Message NVARCHAR(4000) NULL,
CreatedOn DATETIME NOT NULL DEFAULT GETDATE()
);
PRINT '✅ Created table: AOD_MigrationLog';
END
ELSE
PRINT 'ℹ️Table AOD_MigrationLog already exists, skipping.';
IF OBJECT_ID('dbo.AOD_MigrationBatchLog') IS NULL
BEGIN
CREATE TABLE dbo.AOD_MigrationBatchLog (
BatchID INT IDENTITY(1,1) PRIMARY KEY,
TeamList NVARCHAR(500),
StartTime DATETIME DEFAULT GETDATE(),
EndTime DATETIME NULL,
TotalCHIDs INT DEFAULT 0,
SuccessCount INT DEFAULT 0,
FailedCount INT DEFAULT 0,
Status VARCHAR(20) DEFAULT 'IN PROGRESS',
Comments NVARCHAR(1000) NULL
);
PRINT '✅ Created table: AOD_MigrationBatchLog';
END
ELSE
PRINT 'ℹ️Table AOD_MigrationBatchLog already exists, skipping.';
/*********************************************
STEP 2: CREATE CORE PROCEDURE - sp_WAAOD_to_PHAOD
*********************************************/
GO
CREATE OR ALTER PROCEDURE [dbo].[sp_WAAOD_to_PHAOD]
@CHID INT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION;
DECLARE @HospitalEpisodeID INT,
@ReferralID INT,
@TreatmentSettingID INT,
@InjectingDrugUseStatusID INT,
@PHAODEpisodeID INT;
-- Get HospitalEpisodeID
SELECT TOP 1 @HospitalEpisodeID = [Link]
FROM EpisodeEvent ee
INNER JOIN ClinicalHeader ch ON [Link] =
[Link]
INNER JOIN HospitalEpisode he ON [Link] =
[Link]
WHERE [Link] = @CHID;
IF @HospitalEpisodeID IS NULL
BEGIN
INSERT INTO dbo.AOD_MigrationLog (CHID, Status, Message)
VALUES (@CHID, 'FAILED', 'No HospitalEpisode found');
ROLLBACK TRANSACTION;
RETURN;
END
-- Get ReferralID
SELECT @ReferralID = ReferralID
FROM HospitalEpisode
WHERE HospitalEpisodeID = @HospitalEpisodeID;
-- Get Drug Use Assessment data
SELECT
@TreatmentSettingID = AODTSTreatmentSettingID,
@InjectingDrugUseStatusID = InjectingDrugUseStatusID
FROM DrugUseAssessment
WHERE CHID = @CHID;
-- Update HospitalEpisode flags
UPDATE HospitalEpisode
SET AOD = 0, PHAOD = 1
WHERE HospitalEpisodeID = @HospitalEpisodeID;
-- Insert into PHAODEpisode
INSERT INTO PHAODEpisode (
HospitalEpisodeID, ContractNumber, ReferralID, GenderID,
CountryOfBirth, PreferredLanguage,
EnglishProficiencyID, StateID, Suburb, PostCode, DOB,
IndigenousStatusID, EmploymentStatusID,
ClientTypeID, TreatmentSettingID, InjectingDrugUseStatusID,
CompletionStatusID, ClientConsentAnonymised, SexID
SELECT
@HospitalEpisodeID, 'CON1234', @ReferralID, GenderID,
CountryOfBirth, PreferredLanguage,
EnglishProficiencyID, StateID, Suburb, PostCode, DOB,
IndigenousStatusID, EmploymentStatusID,
1, @TreatmentSettingID, @InjectingDrugUseStatusID,
DischargeReasonLookupID, 1, SexAtBirth
FROM AODWAEpisode
WHERE CHID = @CHID;
SET @PHAODEpisodeID = SCOPE_IDENTITY();
IF @PHAODEpisodeID IS NULL
BEGIN
INSERT INTO dbo.AOD_MigrationLog (CHID, Status, Message)
VALUES (@CHID, 'FAILED', 'Insert into PHAODEpisode failed');
ROLLBACK TRANSACTION;
RETURN;
END
-- Insert Substance Use
INSERT INTO PHAODSubstanceUse (PHAODEpisodeID,
SubstanceTypeID, PHAODTreatmentTypeID, RouteID)
SELECT
@PHAODEpisodeID, SubstanceTypeID, AODTSTreatmentTypeID,
RouteID
FROM SubstanceUse
WHERE CHID = @CHID;
-- Soft delete ClinicalHeader
UPDATE ClinicalHeader
SET DateDeleted = GETDATE(), DeletedBy = -1
WHERE CHID = @CHID;
COMMIT TRANSACTION;
INSERT INTO dbo.AOD_MigrationLog (CHID, Status, Message)
VALUES (@CHID, 'SUCCESS', 'Processed successfully');
PRINT CONCAT('✅ Successfully processed CHID ', @CHID);
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
INSERT INTO dbo.AOD_MigrationLog (CHID, Status, Message)
VALUES (@CHID, 'FAILED', ERROR_MESSAGE());
PRINT CONCAT('❌ Error processing CHID ', @CHID, ': ',
ERROR_MESSAGE());
END CATCH
END;
GO
/*********************************************
STEP 3: CREATE DRIVER PROCEDURE -
sp_Run_WAAOD_to_PHAOD_Migration
*********************************************/
GO
CREATE OR ALTER PROCEDURE [dbo].
[sp_Run_WAAOD_to_PHAOD_Migration]
@TeamList NVARCHAR(MAX) -- Example: '70,73,15'
AS
BEGIN
SET NOCOUNT ON;
DECLARE @BatchID INT, @Total INT, @Processed INT = 0;
-- Start batch log
INSERT INTO dbo.AOD_MigrationBatchLog (TeamList)
VALUES (@TeamList);
SET @BatchID = SCOPE_IDENTITY();
PRINT '-------------------------------------------';
PRINT CONCAT('🏁 Starting WAAOD → PHAOD migration for Teams: ',
@TeamList);
PRINT CONCAT('Batch ID: ', @BatchID);
PRINT '-------------------------------------------';
-- Convert comma-separated TeamIDs to table
DECLARE @Team TABLE (TeamID INT);
INSERT INTO @Team (TeamID)
SELECT CAST([value] AS INT)
FROM STRING_SPLIT(@TeamList, ',');
-- Update teams’ OSScreen
UPDATE Team
SET OSScreen = 15
WHERE OSScreen = 11
AND TeamID IN (SELECT TeamID FROM @Team);
PRINT '✅ OSScreen updated for provided teams.';
-- Build CHID list for migration (restart-safe)
IF OBJECT_ID('tempdb..#CHList') IS NOT NULL DROP TABLE #CHList;
SELECT DISTINCT [Link]
INTO #CHList
FROM ClinicalHeader ch
INNER JOIN EpisodeEvent ee ON [Link] =
[Link]
INNER JOIN HospitalEpisode he ON [Link] =
[Link]
WHERE [Link] = 'WAAOD Episode'
AND [Link] = 2
AND [Link] IN (SELECT TeamID FROM @Team)
AND [Link] IS NULL
AND [Link] NOT IN (SELECT CHID FROM dbo.AOD_MigrationLog
WHERE Status = 'SUCCESS');
SELECT @Total = COUNT(*) FROM #CHList;
PRINT CONCAT('🔎 Found ', @Total, ' CHIDs pending migration.');
-- Process each CHID
DECLARE @CHID INT;
WHILE EXISTS (SELECT 1 FROM #CHList)
BEGIN
SELECT TOP 1 @CHID = CHID FROM #CHList ORDER BY CHID;
EXEC sp_WAAOD_to_PHAOD @CHID;
SET @Processed += 1;
DELETE FROM #CHList WHERE CHID = @CHID;
IF @Processed % 10 = 0
PRINT CONCAT('...Processed ', @Processed, ' of ', @Total, ' CHIDs
so far.');
END;
-- Batch summary
DECLARE @Success INT = (SELECT COUNT(*) FROM
dbo.AOD_MigrationLog WHERE Status = 'SUCCESS'),
@Failed INT = (SELECT COUNT(*) FROM dbo.AOD_MigrationLog
WHERE Status = 'FAILED');
UPDATE dbo.AOD_MigrationBatchLog
SET EndTime = GETDATE(),
TotalCHIDs = @Total,
SuccessCount = @Success,
FailedCount = @Failed,
Status = 'COMPLETED',
Comments = 'Batch completed successfully'
WHERE BatchID = @BatchID;
PRINT '-------------------------------------------';
PRINT '📊 Migration Summary for Current Run:';
PRINT '-------------------------------------------';
SELECT Status, COUNT(*) AS TotalCount
FROM dbo.AOD_MigrationLog
GROUP BY Status
ORDER BY Status;
PRINT '-------------------------------------------';
PRINT CONCAT('✅ Batch ', @BatchID, ' completed successfully for teams
', @TeamList);
PRINT '-------------------------------------------';
END;
GO
/*********************************************
STEP 4: SAMPLE EXECUTIONS
*********************************************/
-- Test with one team
-- EXEC sp_Run_WAAOD_to_PHAOD_Migration '70';
-- Run for multiple teams
-- EXEC sp_Run_WAAOD_to_PHAOD_Migration '70,73,15,72';
-- Check batch logs
-- SELECT * FROM dbo.AOD_MigrationBatchLog ORDER BY StartTime DESC;
-- Check detailed CHID logs
-- SELECT TOP 100 * FROM dbo.AOD_MigrationLog ORDER BY CreatedOn
DESC;
PRINT '✅ WAAOD → PHAOD Migration Framework successfully deployed.';