ABPN IT

 For the HD - [#6160] Reinstate certification #169058 : American Board of Psychiatry and Neurology, 

-- Reinstate steps

-- CCStatusId = 1 - meeting, 2 - voluntarily meeting (lifetimers certs), 3 - Not meeting , 4 - Not required, We set it to null when we lapse a CC

-- CC requirements lapsing requirements reasons - License, fees , Activities, assessments due

-- 

 -- Diagnostic queries - to determine the status of the physician

select * from Certificate WHERE Person_key = 169058

Select * from certificate WHERE Person_Key = 169058

Select * from CertificateHistory WHERE Person_Key = 169058 AND CertificateId = 104209

Select * from CCEnrollment WHERE Person_Key = 169058

Select * from Person WHERE Person_Key =169058

Select * from Diplomate WHERE Person_Key =169058

Select * from Diplomate_History WHERE Diplomate_Key = 126246

Select * from Invoice WHERE  Person_Key =169058

Select * from InvoiceLineItem Where InvoiceId IN (

Select InvoiceId from Invoice WHERE  Person_Key =169058

)

Select * from InvoiceStatus 

-- CCAssessmentType = 2 Exams, 1 = ABCC

-- Double check from the queries below if they were ever a part of ABCC, Or they switched back to the exam pathway from ABCC pathway

Select * from CCEnrollmentCertificate WHERE CertificateID = 104209

Select * from CCArticleResult WHERE Person_Key = 169058

Select * from AbccAssessment WHERE Person_Key = 169058

--Select * from AbccAssessmentModule WHERE ABCCAssessmentId = <Provide from above>

Select * from CCBlock WHERE CCEnrollmentId = 15802

Select * from CCEnrollment WHERE CCEnrollmentId = 15802

-- Update queries required to set up the person to their previous state

BEGIN TRAN

UPDATE [Certificate] SET 

UpdatedBy = 'aagrawal',

UpdatedDate = GetDate(),

CertificateStatus = 1,

Diplomate_History_Status_Key = 1, 

CCStatusId = 3, 

ExpirationDate = '2027-12-31'

WHERE CertificateId = 104209

ROLLBACK TRAN

UPDATE CCEnrollmentCertificate SET

UpdatedDate = Getdate(), 

UpdatedBy = 'aagrawal',

EnrollmentEndDate = null

WHERE CCEnrollmentCertificateId = 20589

UPDATE CCEnrollment SET UpdatedBy = 'aagrawal' , UpdatedDate = Getdate(),  LastEvaluationDate = null  WHERE person_Key = 169058 AND CCEnrollmentId = 15802

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, Note, 

Date_Added, Date_Updated, User_Key)

VALUES

(126246, null, 1, Getdate(), '2017-02-06', '2027-12-31',1,'2027-12-31','Re-instated from CC Lapse', Getdate(),GetDate(), 193 )

UPDATE Person SET CurrentCCEnrollmentId = 15802   WHERE Person_Key = 169058