#10766 Invoice communication Type / Status
Adds Comm_Method (Email / Phone Call / Online) and Claim_Status (claim status lookup) to Communication
on every agency_* database, plus Invoice_ID where it is still missing.
Invoice threads are filed under the agency's active Billing communication subject so they keep showing in the Communication Log;
the subject is added for agencies that do not have one.
Important: Always backup databases before running migrations.
Safe to re-run: existing columns and an existing active Billing subject are left as they are.
SQL preview (per agency database)
ALTER TABLE {agency_db}.Communication ADD COLUMN `#col.name#` #col.ddl#; -- only if missing
INSERT INTO {agency_db}.CommunicationSubjects (Agency_ID, Subject, active, Created_By, Created_Date)
VALUES ({agency_id}, '#billingSubject#', 'Y', {your employee id}, NOW()); -- only if no active '#billingSubject#' subject
Migration Progress
SELECT SCHEMA_NAME AS db_name
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME LIKE 'agency\_%' ESCAPE '\\'
AND SCHEMA_NAME NOT LIKE '%lookup%'
ORDER BY SCHEMA_NAME
Found #getAgencies.recordCount# agency database(s)
SELECT TABLE_NAME FROM information_schema.TABLES
WHERE TABLE_SCHEMA =
AND TABLE_NAME IN ('Communication', 'CommunicationSubjects')
SELECT COLUMN_NAME FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA =
AND TABLE_NAME = 'Communication'
ALTER TABLE #db_name#.Communication ADD COLUMN `#col.name#` #col.ddl#
SELECT CommSub_ID FROM #db_name#.CommunicationSubjects
WHERE Agency_ID =
AND Subject =
AND active = 'Y'
ORDER BY CommSub_ID
LIMIT 1
INSERT INTO #db_name#.CommunicationSubjects (Agency_ID, Subject, active, Created_By, Created_Date)
VALUES (
,
,
'Y',
,
NOW()
)
Error: #EncodeForHTML(cfcatch.message)# #EncodeForHTML(cfcatch.detail)#
| Database | Status | Columns added | Billing subject | Message |
|---|
| #r.db# |
#r.status# |
#Len(r.added) ? EncodeForHTML(r.added) : "(already present)"# |
#r.subject# |
#EncodeForHTML(r.message)# |
Done. #ArrayLen(results)# database(s), #totalErrors# error(s).