Finding Missing Event Registrations

A recent bug caused some event registrations to not be created despite an invoice being generated for the registration. 

To help manage these occurrences, we recommend the following steps:

1. Locate affected invoices: Use a custom query in the Query Tool (code provided below) to identify any invoices created without corresponding event registrations.
    
2. Adjust and correct records: Adjust off the affected invoices and payments, create the correct registration within the event (this will generate a new invoice), and re-apply the original payment to the new invoice using an appropriate payment type (e.g., manual credit card). This ensures the payment record is correctly linked to the event registration.
    

How to create a query to find affected invoices:

1. Navigate to Reports > Query Tool > Create a New Query (top right).
2. Click View the SQL Code (left side) and check the box for "Use My Custom SQL."
3. Paste the query code (provided below) exactly into the box and click Run This Query.
4. Save the resulting query for future use.

SELECT
ili.*
,i.ProfileID
,i.BillToName
,p.Email
,i.BillToAddress1
,i.BillToAddress2
,i.BillToCity
,i.BillToState
,i.BillToZip
,i.BillToZipExt
,i.Attention
,p2.Email
FROM
InvoiceLineItem ILI
INNER JOIN Invoice I
ON ili.InvoiceNum = i.InvoiceNum
INNER JOIN [Profile] P
ON p.ProfileID = i.ProfileID
LEFT OUTER JOIN [Profile] P2
ON p2.ProfileID = i.CommunicateWithProfileID

WHERE
EventSignUpID <> 0
AND EventSignUpID IS NOT NULL
AND EventSignUpID NOT IN (
SELECT
EventSignUpID
FROM
EventSignUps
)
AND EventSignUpID NOT IN (
SELECT
EventSignUpID
FROM
WC_Deleted_EventSignUps
)