We have been automatically storing tokenized cards and would like to continue using this feature.
Our ticket kiosks use a dummy constituent ‘Kiosk User’ as a way to offer a quick, anonymous guest checkout experience. Since this dummy account is used by many different users each day, we realized it has been storing hundreds of tokenized cards on the record, which we do not want.
The only way I can find within the client to clear out just this specific account is to delete the cards one-by-one from the record. The parameters on the Credit Card Tokenization and Deletion Utility can be set to EXCLUDE a list of constituents, but not to target specific ones.
I am looking for:
I’ve been in touch with TASK, and am currently looking into developing a stored procedure based on AP_PURGE_T_ACCOUNT_DATA. Before we spend a lot of time on it, I wanted to check if anyone else already has a solution.
I’ve tested simply deleting the lines from T_ACCOUNT_DATA for that specific customer_no, and haven’t found any issues yet. Could it be as simple as that, or am I missing something?
We are on 12.5.1 and use Payment Express.
Jennifer K. Miller (Past Member) said:I’ve tested simply deleting the lines from T_ACCOUNT_DATA for that specific customer_no, and haven’t found any issues yet. Could it be as simple as that, or am I missing something?
Probably: I'd still look at the guts of AP_PURGE_T_ACCOUNT_DATA to be sure they aren't doing anything else.
Alternatively you could create an autogenerate list of all customers with credit cards except the kiosk customer. It would hopefully not be too crazy a list to use as an exclusion, though I'd suppose you'd have to worry about the time between the regeneration of the list and the execution of the delete in AP_PURGE_T_ACCOUNT_DATA.
Hi Jennifer - there are other tables that potentially need to be updated when a row is removed from T_ACCOUNT_DATA. For example, tables like t_payment, t_contribution, etc contain an act_id column that references T_ACCOUNT_DATA.[id] that should be set to 0 or NULL (it seems to vary whether it should be 0 or NULL depending on the table). As Gawain pointed out, looking at AP_PURGE_T_ACCOUNT_DATA should allow you to quickly find the related tables.
Good luck!
David
Hello Jennifer,
We recently had the same problem and we had so many credit cards stored on our Kiosk Anonymous User constituent record that if you tried to open that screen, the application crashed.
I created a list that was all constituent ID's not equal to my Kiosk record and used that as an exclusion parameter within the CC Deletion Utility. Nearly 48K cc's were deleted (which I had validated with our DBM in advance, was the correct number of cc's being stored on that 1 record). The Deletion Utility ran quite quickly - less than 30 seconds I would say. Though it took slightly longer the first time I ran in review mode, which I assumed was due to the list being generated for the first time.
We did a few tests in our TEST database first, and then ran in PROD this past Monday at 5PM as the Museum was closing.
In testing, we did discover that we could no longer perform a full cc order search for kiosk orders, but a partial cc turned up results.
Thanks, RhondaLeigh! We discussed creating a list of all users except the Kiosk, but were afraid it would get too crazy. Good to know there's already been a successful test.
Gawain Lavers said:create an autogenerate list of all customers with credit cards except the kiosk customer
I like the idea of limiting the list to only accounts with a stored credit card, to keep it from getting out of control, but I haven't found the right criteria to actually build it. Any ideas on how to make that happen?
Yeah, you'd probably have to enter that manually.. Let me know if you need a query.
Running a raw query in SSMS for our organization nets ~50k card holders in about 2 seconds. List autogeneration will probably have a little overhead on that.