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
  • Entered : 01/2011
  • Version : 3.4+
You could leave a comment if you were logged in.