Is anyone doing this? We are clearly getting having more issues than those picked up by the Incomplete Credit Card Transaction Report, and we're working towards frequent reconciliation with Windcave for card purchases, so I am investigating importing their reports into a local table to do some of the reconciliation automatically. I don't really understand all of the columns, for instance there are several columns that look like they might be a transaction id column, but none are unique, even with just a month's transactions.
Also, is it just me or is the time field mangled, with month encoded as day and vice-versa?
We were having similar issues reconciling with Worldpay so we built multiple pieces to import the settlement data into Tessitura. It is not a small undertaking.
The first step was to set up a table to store the settlement data from the settlement report out of World Pay. Additional columns were added to hold the matching Tessitura data and other controls such as the method used to match the transaction to the data in tesstitura.
Because credit card data gets written differently in T_payment_Gateway_Activity depending on things like payment type and source we had to write several different ways to match the settlement record to to the Tessitura record.
After that process was completed we needed to provide the finance team with a method to import the data keeping in mind there would be instances where they needed to run the same date range over multiple days due to some credit cards having different settlement time rates or time of day of the transaction.
While matching happens on about 99.9 percent of transactions there are still the occasional transaction that requires a manual match due to missing data so they also needed a way to search for transactions and match them to settlement records.
To do this we built a .Net application
Lastly we gave them a report to show unmatched settlement records
Importing the settlement data into Tessitura has allowed us to do a number of other things like bank reconciliation relative to the transaction date as well as identifying transactions in T_Payment_Gateway that never settled.