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?
My first SSRS report was for finance looking into Windcave errors. I used auth code - is that going to be helpful for you?
We've also had 2 years of issues with Windcave after hosted payments kicked in and we were made to use void rather than refund for transactions. A lot of the issue is that first data sits between our bank and Windcave and bounces void transactions. Getting an understanding of that took a long time.
We're building a Tessitura-Windcave reconciliation report right now (we're also using Auth Code as the way to match things up). Windcave sends us auto-reports, so we set up an automated procedure to insert some of that transaction data into a separate database on a daily basis so that the data can be queried by our report procedure.
Kirk McMahon Do you have a method of "americanizing" the dates? I've tried in Excel and SQL, but haven't figured it out yet. I think maybe the dates are actually coming over a mix of formats?
Also, the dates are clearly local somewhere else:
Tessitura: 2021-09-01 21:14:37.070
Windcave: 2021-09-02 00:14:34.000 (Eastern?)
It looks like auth_no is something that gets cycled on a regular basis? Taking one from my Windcave report I get three entries in T_PAYMENT; 2013, 2019 and the recent payment.
I am getting a number of null values both in T_PAYMENT and the Windcave report for auth_no/auth_code, do you see that? Looks like refunds?
In our Full Transactions report from Windcave, the date appears correctly and is able to be inserted into a SQL table. In Windcave's Payline portal, there's a section in the menu on the left called My User where you can set the Date Format and Time Zone. If you can't see that in Payline, you might want to call Windcave support and ask for them to expose that to your user.
That's a good point about the re-use of Auth Codes. So far we've only been reconciling smaller time periods (we reconcile daily) where it's not likely a code will be reused, and so we haven't run into the issue yet of Auth Codes showing up for multiple transactions, but based on what you said, it seems like it'd be possible.
I spot checked some of our Windcave reports and it appears the vast majority of transaction rows that don't have an Auth Code are Declined or Error responses. Most of the refund transactions have an Auth Code, but I do see a handful of refunds that don't have an Auth Code and instead have a value in the Reference column on the Windcave report, which makes me think that those are Refund by Reference transactions put through Tessitura.
Kirk McMahon said: Most of the refund transactions have an Auth Code, but I do see a handful of refunds that don't have an Auth Code and instead have a value in the Reference column on the Windcave report, which makes me think that those are Refund by Reference transactions put through Tessitura.
Virtually all of ours should be Refund by Reference. If that's the case it's a shame it doesn't refer back to the Tessitura transaction. Does the Reference field refer to the reference number, or some other number, on the original payment?
I just made a post related to this here. Would someone be willing to share their reconciliation report? I can make any adjustments to accommodate our organization but it would be super helpful to have an existing report to work off of.
Thanks!
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.
Auth code isn't unique, you wantto use the transaction locator which in most cases will match the reference number column in T_Payment_gateway
It was originally for the Accountant to reconcile single orders but for large scale matchups in Windcave it's best to match up using DpsTxnRef from the Windcave: Full Transaction Report and ccref_no in T_PAYMENTS.