Create Bulk Incidents

Owned by Robert Adach, created
Jan 31, 2022
4 min read

When an event occurs that prevents a candidate from taking/completing an exam (weather related, proctor sick), or does something to jeopardize the security of the exam (alleged cheating, failure to follow rules), Pearson Vue will create an “Incident” and notify Candidate Services.

The creation of incidents in the legacy system is quite tedious and as a result, an arrangement with IT has been made that if there are more than 10 entries of the same incident to be entered, the following occurs:

  • CSC creates a “source incident” for IT to use as a template for all other incidents requested

  • a Jira ticket will be created by CSC

    • This ticket MUST contain the list of Candidates in the PearsonVue format (see supporting document below)

    • The ACTNO of the candidate the Incident was created against

  • IT will add the incidents to the system

  • IT updates and closes the ticket.

Creating bulk incidents is a time consuming process in some cases, and it is recommended that you reply to the Jira ticket letting the requester know that you have started working on the ticket as to ensure we meet our initial response SLA.

 Prerequisites

  1. Access to the ASWBSQL2\ASWBSQL2\ASWB database

  2. The list of candidate information in the format shown in the supporting document

 Instructions

Step 1: Obtaining Source Incident Number

  1. Execute the following script against the ASWB database

  2. SELECT TOP 1 IncidentID FROM tblIncident where ACTNo = <ACT_NO>ORDER BY ReportDate DESC

    1. Replace <ActNo> with the ACTno provided in the Jira Ticket

  3. Copy the IncidentID returned

Step 2: Preparing the excel workbook

  1. Open the Excel workbook provided by CSC

  2. Create a new header in the Y column titled “Backdate”

  3. Create a new header in the Z column titled “SourceIncidentID”

  4. Create a new header in the AA column titled “SPROC”

  5. Select all rows in columns A:AA

  6. Create a table on the selected cells

  7. Locate the record that contains the Candidate that the source incident was created for. This is done by dropping the last 2 numbers of the ACTNo, and searching for the remaining string in the ClientCandidateID column.

    1. For example, if the ACTNO provided was 12345678901, you would search for 123456789

  8. When the record has been found, delete the entire line from the table.

Step 3: Adding Formulas

  1. In the “Backdate” column (Y) insert the following formula on all table rows with data

  2. =IF([@ApptStartDateTime]<TODAY(),1,0)

  3. In the “SourceIncident” column enter the number you obtained from the query run in Step 1 above

  4. In the “SPROC” column (Z) insert the following formula on all table rows with data

  5. ="EXEC [dbo].[DevOps_InsertIncidentv2] @SourceIncidentID = "&[@SourceIncidentID]&", @CandidateID = "&[@ClientCandidateID]&", @ExamDate = '"&TEXT([@ApptStartDateTime],"MM/DD/YYYY")&"', @SiteID = "&[@[Site_ID]]&", @Backdate = "&[@Backdate]

Step 4: Inserting the incidents

To simplify the insertion of the incident information into the database, a stored procedure has been created

  1. Select and copy all of the data from the “SPROC” column (AA)

  2. Access SSMS

  3. Paste the data you copied into Excel

  4. You have two options to run the inserted statements

    1. You can run all of the exec statements at once

    2. You can run each exec statement individually

  5. When the stored procedure is run, there will be a message outputted indicating a success or failure of the execution. Make a note of all of the Candidate IDs which failed

Step 5: Addressing Failed Executions

Unable to locate ACTNo for Candidate ID ######

These errors are most commonly caused by the Candidate having no open Approvals.

  1. Using the Candidate ID in the error find the SPROC execution statement you ran for the candidate

    1. The SMSS find function can be used.

  2. Change the end of the execution statement from “@Backdate = 1” to “@Backdate = 0”

  3. Rerun ONLY the one statement you changed

If this produces the same error, then the issue is caused by an expired registration

  1. Execute the following script to confirm the expired registration

    1. Replace <CandidateID> in the script above with the Candidate ID from the failed error message

  3. Make note of the date listed in the registration expiration date field and the ACTNO returned by the query

  4. Execute the following script to temporarily update the registration expiration date field

    1. Replace <ACTNO> with the ACTNO returned in Step 2

  6. Return back to the stored procedure execution statement and revert the change to the script, changing the item from “@Backdate = 0” to “@Backdate = 1”

  7. Rerun the stored procedure execution

  8. Revert the change to the registration expiration date by running the following script

    1. Replace <ACTNO> with the ACTNO returned in Step 2

Step 5: Providing incident numbers

  1. Identify the unique dates in the Workbook column “ApptStartDateTime”

    1. We only need unique dates and not times

  2. Execute the following script

    1. Replace <DATES> with the SQL query formatted dates

  4. Copy and paste the results in a new excel notebook

  5. In the original file from CSC delete Columns Y, Z and AA

  6. Create a new column Y with the header Incident Number

  7. Using a method of your choosing update the Column Y with the incident number associated with the Candidate ID

    1. A VLOOKUP formula is usually the easiest method.

Step 6: Finishing the process

  1. After updating the original workbook from CSC with the incident numbers save the workbook.

  2. Using the Jira reply to customer option in the Jira ticket:

    1. attach the updated workbook

    2. Respond with “The incidents has been created as requested. Attached please find the updated information.”

 Related articles