1. Immediately lapse the certificate
USE WEBCAD
DECLARE @PersonKey AS INT
DECLARE @EffectiveDate AS DATETIME
DECLARE @UserName AS VARCHAR(20)SET @PersonKey = 144118
DECLARE @UserKey AS INT
SET @EffectiveDate = GETDATE()
SET @UserName = 'JVollmer';PRINT 'Updating Certificates'
SET @UserKey = 188
UPDATE [Certificate]
SET CertificateStatus = 2, Diplomate_History_Status_Key = 1, CCStatusid = NULL, ExpirationDate = @EffectiveDate, UpdatedBy = @UserName, UpdatedDate = GETDATE()
WHERE Person_Key = @PersonKey AND CertificateStatus = 1
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)
SELECT Diplomate_History.Diplomate_Key, Candidacy_Key, 1, @EffectiveDate, Diplomate_History_Award_Date,
Diplomate_History_Certificate_Date, Diplomate_History_Recertification_Flag, @Effectivedate, 'Lapsed Certificate due to Surrender',
GETDATE(), GETDATE(), User_Key
FROM Diplomate_History
INNER JOIN (SELECT Diplomate_History.Diplomate_Key, MAX(Diplomate_History.Diplomate_History_Key) AS MaxDipKey FROM Diplomate
INNER JOIN Diplomate_History ON Diplomate_History.Diplomate_Key = Diplomate.Diplomate_Key
WHERE Diplomate.Person_Key = @PersonKey
GROUP BY Diplomate_History.Diplomate_Key) AS MaxDip ON MaxDip.MaxDipKey = Diplomate_History.Diplomate_History_Key
WHERE Diplomate_History.Diplomate_History_Expiration_Date > GETDATE()
// Update CCEnrollmentCertificate EndDate to match above expiration date Check portal to make sure CC Enrollment displays correctly
// If there is an unpaid invoice for years that are unpaid after the ccenrollmentcertificate end date, the Invoice Status must be set to abandoned
// The invoices must be manually removed from business central by Finance, send an email to them to complete