Explanation of SQL
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.
Risk of Data Corruption if Run Improperly
None. This is a selection query and no data is modified in the running of it.
SQL
-- 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
Version Information
- Entered : 01/2011
- Version : 3.4+
You could leave a comment if you were logged in.