If Control is terminated (e.g., CTRL-ALT-DEL) while processing an online credit card, the results of the credit card being run may not be written to the database. However, the C3S running as a separate service will still have a record of these.
This query searches for Credit Card Processed activities that do not have a completion event, and then looks up the status of those from the C3S tables. From there, the fix must still be applied manually (usually by giving the customer a manual credit), but this query will identify them.
The date and time of the range to be queries must be adjusted before running.
None. This is a selection query and no data is modified in the running of it.
-- This query identifies payments that -- were processed but a response was not -- logged in Control. SELECT Journal.StartDateTime AS [DATE AND TIME], Journal.ModifiedByUser AS [USER], SummaryAmount AS Amount, Journal.Description, CT.Amount AS CCAmount, WasSuccessful, AuthorizationCode, Account.CompanyName, Account.AccountNumber AS CompanyNumber, Journal.ID --, Journal.Notes --, CT.* FROM CCCSTransactions CT JOIN CCCSCustomer CC ON CT.CustomerGUID = CC.CustomerGUID JOIN Journal ON CC.CustomerID = Journal.AccountID LEFT JOIN Account ON Journal.AccountID = Account.ID WHERE Journal.ClassTypeID = 20030 AND Journal.Notes NOT LIKE '%success%' AND Journal.Notes NOT LIKE '%failed%' AND CT.ProcessedDateTime BETWEEN '11/1/2010' AND '2/1/2011' AND Journal.StartDateTime BETWEEN '11/1/2010' AND '2/1/2011' AND abs(DATEDIFF(MINUTE, CT.ProcessedDateTime, Journal.StartDateTime)) < 10 ORDER BY Journal.ID DESC