Delete registry applications

Owned by Robert Adach, created
Mar 21, 2022
2 min read

MSERV may ask that a registry application be deleted entirely from the system.

 Pre-requisite

Before beginning, make sure that the request contains the following

  1. Candidate ID number of individual assigned to registry application.

This process will delete ALL registry related items for the Candidate ID provided. It does NOT remove the Candidate ID from the system.

Script

  1. Update the script below by replacing <CandidateID> with the Candidate ID provided

  2. DECLARE @CandidateID INT = <CandidateID> DECLARE @Continue INT = 0 IF (@Continue = 0) BEGIN SELECT * FROM tblRegCandidateAdd ca WHERE ca.CandidateID = @CandidateID END IF (@Continue = 1) BEGIN TRY DELETE FROM tblRegCandidateAdd WHERE CandidateID = @CandidateID DELETE FROM tblRegEducation WHERE CandidateID = @CandidateID DELETE FROM tblRegExperience WHERE CandidateID = @CandidateID DELETE FROM tblRegLicensure WHERE CandidateID = @CandidateID DELETE FROM tblRegOtherDocumentation WHERE CandidateID = @CandidateID DELETE FROM tblRegContinuingEd WHERE CandidateID = @CandidateID DELETE FROM tblRegReference WHERE CandidateID = @CandidateID DELETE FROM tblRegSupervision WHERE CandidateID = @CandidateID DELETE FROM tblRegTracking WHERE OwnerID = @CandidateID DELETE od FROM tblOrderDetail od INNER JOIN tblOrder o ON o.OrderID = od.OrderID WHERE o.OrderType = 3 AND o.CandidateID = @CandidateID DELETE FROM tblOrder WHERE OrderType = 3 and CandidateID = @CandidateID END TRY BEGIN CATCH PRINT FORMATMESSAGE('[%s] :: Error removing the Registry records due to the following error(s) [%s]: %s.',FORMAT(GETDATE(),'MM-dd-yyyy HH:mm:ss'),CAST(ERROR_NUMBER() AS VARCHAR), ERROR_MESSAGE()) END CATCH

  3. If a record is found,

    1. update the [Continue] variable to 1

    2. run the script again to delete the information

    3. Respond to the ticket with the following:

      1. “The registry application and all related information has been deleted from the system for the Candidate ID provided”.

  4. If no record was found, place the ticket into pending system and respond to the requester with “We were unable to locate a registry application with the Candidate ID provided. Can you please confirm and respond with the correct ID number?”

 Related articles