EVV Reason Codes Setup
This migration will create the necessary tables for tracking EVV update reason codes.
Migration Progress:
SELECT COUNT(*) as table_exists
FROM information_schema.tables
WHERE table_schema = '#Request.prefix_db_lookup#'
AND table_name = 'EVV_Reason_Codes'
CREATE TABLE #Request.prefix_db_lookup#.EVV_Reason_Codes (
Reason_Code_ID INT PRIMARY KEY,
Description VARCHAR(255) NOT NULL,
Status TINYINT DEFAULT 0,
Date_Create DATETIME DEFAULT CURRENT_TIMESTAMP,
Date_Modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
INSERT INTO #Request.prefix_db_lookup#.EVV_Reason_Codes (Reason_Code_ID, Description) VALUES
(1, 'Caregiver Error'),
(2, 'Member/Designee Unavailable to Verify'),
(3, 'Mobile Device Issue'),
(4, 'Telephony Issue No'),
(5, 'Member/Designee Refused Verification'),
(6, 'Unsafe Environment'),
(7, 'Member Refused Service'),
(8, 'Member No Show'),
(9, 'Other'),
(10, 'Caregiver No Show'),
(11, 'Clinical Need'),
(12, 'Live In/Onsite Caregiver'),
(13, 'Member Preference')
✓ Created EVV_Reason_Codes table and inserted 13 reason codes
✓ EVV_Reason_Codes table already exists - skipping
✗ Failed to create EVV_Reason_Codes table: #cfcatch.message#
SELECT COUNT(*) as table_exists
FROM information_schema.tables
WHERE table_schema = '#Request.prefix_db_lookup#'
AND table_name = 'EVV_Update_Log'
CREATE TABLE #Request.prefix_db_lookup#.EVV_Update_Log (
Update_Log_ID INT AUTO_INCREMENT PRIMARY KEY,
Schedule_ID INT NOT NULL,
Agency_ID INT NOT NULL,
EVV_ID INT NULL,
Date_Updated DATETIME NOT NULL,
Change_Memo TEXT NULL,
Changed_By INT NULL,
Changed_By_Email VARCHAR(255) NULL,
Reason_Code_ID INT NULL,
Date_Reason_Added DATETIME NULL,
Reason_Added_By INT NULL,
Status TINYINT DEFAULT 0,
Date_Create DATETIME DEFAULT CURRENT_TIMESTAMP,
Date_Modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_schedule_id (Schedule_ID),
INDEX idx_agency_id (Agency_ID),
INDEX idx_evv_id (EVV_ID),
INDEX idx_status (Status),
FOREIGN KEY (Reason_Code_ID) REFERENCES #Request.prefix_db_lookup#.EVV_Reason_Codes(Reason_Code_ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
✓ Created EVV_Update_Log table with proper indexes
✓ EVV_Update_Log table already exists - skipping
✗ Failed to create EVV_Update_Log table: #cfcatch.message#
SELECT DISTINCT table_schema as db_name
FROM information_schema.tables
WHERE table_name = 'pSchedules'
AND table_schema LIKE 'agency_%'
AND table_schema NOT LIKE '%lookup%'
Adding EVV_Reason_Code_ID column to pSchedules tables...
SELECT COUNT(*) as col_exists
FROM information_schema.columns
WHERE table_schema = '#db_name#'
AND table_name = 'pSchedules'
AND column_name = 'EVV_Reason_Code_ID'
ALTER TABLE #db_name#.pSchedules
ADD COLUMN EVV_Reason_Code_ID INT NULL
COMMENT 'EVV Update Reason Code ID'
CREATE INDEX idx_evv_reason_code ON #db_name#.pSchedules(EVV_Reason_Code_ID)
✓ Added EVV_Reason_Code_ID column to #db_name#.pSchedules
✓ EVV_Reason_Code_ID column already exists in #db_name#.pSchedules - skipping
✗ Failed to add column to #db_name#.pSchedules: #cfcatch.message#
✗ Failed to process pSchedules tables: #cfcatch.message#
Migration Complete!
The EVV reason codes system has been set up successfully.
Go to EVV Update Log