1. Investigate the state of the physician at the time they were updated to retired or deceased using the select queries below; these will help you determine what needs to be updated to get them back to where they were
2. Based on that, select the updates you need to make
3. Add you first name initial and last name (like JVollmer') for all UpdatedBy
4. Make sure you are adding the correct dates on the diplomate_history inserts!!! If you have questions ask Jenny.
5. Diplomate and DIplomate_History are legacy certificate tables. We created Certificate and Certificate_History during modernization, be have not completely been able to abandon use of Diplomate and Diplomate History. Diplomate History better records certificate events that we care about and that we need to send to ABMS monthly, while certificate_history does not. Future development will be done to create another version of Cert history that only records certificate events that we care about. Until then, we need to keep diplomate/diplomate history updated
6. We DO NOT delete diplomate history or certificate history rows; instead, we add. The history is a log of what occurred. Only in very special circumstances do we delete.
7. Background on why we are unretiring: We did a large update of older lifetimers to retired a few years ago without their consent (although we notified them). Since then, they are back and noticing that we updated them to retired and they aren't. Most of these older ones were not in CC....but some were. Most of the ones that we get to unretire are lifetimers who were not in CC.
a. An ABMS update file must be created and sent to ABMS to make sure that the change back to active is updated in ABMS's Certification Matters online diplomate lookup. Jenny handles this as part of the monthly ABMS updates.
NOTE: If you see multiple certificates for a user, make sure you are ONLY restoring the lifetime certs which were expired in 2023, and not any 10-year certs which were expired prior to that. Also make sure to ONLY insert diplomate history rows corresponding with the expired lifetime certs
use webcad
select * from person where person_key = 134780
select * from ccenrollment where person_key = 134780
--select * from ccblock where CCEnrollmentId = 526
select * from certificate where person_key= 134780
select * from invoice where person_key = 134780
select * from moc_payment_history where person_key = 137260
select * from diplomate where person_key = 134780
select * from diplomate_history where diplomate_key in (78036, 72582) order by diplomate_history_key
update person
set CurrentCCEnrollmentId = ###,
updatedby = 'JVollmer',
updateddate = GETDATE()
where person_key = ######
update ccenrollment
set LastEvaluationDate = NULL,
UpdatedBy = 'JVollmer',
UpdatedDate = GETDATE()
where ccenrollmentid = ###
update certificate
set
CertificateStatus = 1,
Diplomate_History_Status_Key = 1,
ExpirationDate = NULL,
CCStatusId = NULL,
UpdatedBy = 'JVollmer',
UpdatedDate = GETDATE()
where certificateid in (####)
update diplomate
set Date_Updated = GETDATE(),
user_key = 188
where diplomate_key = ####
--if physician needs to be put back in CC:
1. update current cc enrollment on person
update person
update currentccenrollmentid = ###
UpdatedBy = 'JVollmer',
UpdatedDate = GETDATE()
where person_key = ###
2. update cc enrollment to re-evaluate
update ccenrollment
set LastEvaluationDate = NULL,
UpdatedBy = 'JVollmer',
UpdatedDate = GETDATE()
where ccenrollmentid = ###
3. Update cc enrollment certificate to remove any enrollment end dates on active CC certificates and set them back to null
update ccenrollmentcertificate
set enrollmentenddate = NULL
UpdatedBy = 'JVollmer',
UpdatedDate = GETDATE()
where certificateid in (###)
INSERT INTO Diplomate_History (Diplomate_Key, Candidacy_Key, Diplomate_History_Status_Key, Diplomate_History_Status_Date, Diplomate_History_Award_Date,
Diplomate_History_Certificate_Date, Diplomate_History_Recertification_Flag, Diplomate_History_Expiration_Date,
Date_Added, Date_Updated, User_Key, Note)
VALUES
(67611, NULL, 1, GETDATE(), '1992-11-30', '9999-12-31', 0, '9999-12-31', GETDATE(), GETDATE(), 188, 'Updated physician from retired back to active')