ABPN IT

Sometimes program users will accidentally create a duplicate physician when they intended to transfer an existing user into their program, and these duplicate physician records will need to be deleted.  Note - if the physician gets further into their process and has made a payment to the ABPN we realize that they have two person records in BC, keep the person that has the payment and invoice.  The process is as follows:

Run the following Select queries to see which tables need to have the duplicate physician records deleted

select * from person_name where person_key = 226829

select * from userPhysicianViewHistory where person_key = 226829

select * from medical_education where person_key = 226829

select * from personTraining where person_key = 226829

select * from clinicalSkill where person_key = 226829

Run the following Delete statements in order (NOTE: it is recommended to enclose each one in a transaction first to ensure you are deleting the expected number of rows)

DECLARE @Person_Key AS INT

SET @Person_Key = 231221

delete from UserPhysicianViewHistory where person_key = @Person_Key

delete from medical_education where person_key = @Person_Key

delete from postgraduateyear where trainingid in (

     select trainingid from training where person_key = @Person_Key)

delete from training where person_key = @Person_Key

delete from PersonTraining where person_key = @Person_Key

delete from TrainingNote where person_key = @Person_Key

delete from ClinicalSkill where person_key = @Person_Key

delete from person_name where person_key = @Person_Key

delete from person_address where person_key = @Person_Key

delete from PhysicianDemographicRace where PhysicianDemographicId in (

     select PhysicianDemographicId from PhysicianDemographic where person_key = @Person_Key)

delete from PhysicianDemographic where person_key = @Person_Key

delete from Message where person_key = @Person_Key

--Lastly, delete the record from the Person table and then from AspNetUsers if they have a web account

delete from person where person_key = @Person_Key

delete from AspNetUsers where username = 'EmailAddress'

/*

Also In case if you might have visited Boardcert and navigated to the deleted person key in the past. You might want to delete that record from UserPhysicianViewHistory table as well. 

failing to delete from the table above might result in getting a 500 error in Boardcert, and you may not be able to login to Boardcert.

*/

delete from UserPhysicianViewHistory WHERE Person_Key = @Person_Key

Note:

1. If the physician gets further into their process and has made a payment to the ABPN we realize that they have two person records in BC, keep the person that has the payment and invoice.  

2. Ask requestor if it is ok to delete any training (PGYs, CSEs on the person they ask you to delete.  Usually it is ok, but always ask first before deleting.