“I didn’t buy ten cylinders of CO2! Why is PCard telling me I did?”

There were a pile of tickets in Adam’s queue, slightly larger than the proverbial molehill, but nowhere near mountain status either. Several employees saw incorrect charges on their purchasing cards, including one of the VPs. The first suspicion was fraud, but these charges looked legitimate: plane tickets to cities where the company had plants, raw materials purchases from vendors the plants usually did business with, etc. Since a VP was impacted, this confusion needed to be resolved ASAP, which meant Adam was pulling long hours to fix it.

“PCard” was the name for the purchasing card application. The purpose of the tool was to allow managers and executives validate the purchases made on the purchasing cards, and determine if they really belonged there or should have had a purhase order. It was one of Adam’s nuisance products, simply because it mostly worked fine- but minor issues were viewed as “my hair’s on fire” class emergencies. The only silver lining was that the application was considered “mature”, which meant that in three months, support would move to a much cheaper offshore team.

The bulk of the application’s logic was an SSIS package. BankCo, the bank which provided the purchasing cards, handled all of the billing and payments, but didn’t provide any sort of reporting on purchases. They did provide a steady feed of flat-files. It true enterprise IT fashion, these files ping-ponged across a series of SFTP servers and network shares until they dropped in a folder that the SSIS package monitored.

Adam started with the bank. Over the phone, they assured him that the charges were correct, and that the problem must be in how Adam’s tool processed the data. Adam wasn’t convinced, but he agreed to examine the import job in detail.

The job imported two major files. The first was a record of every purchasing card issued by BankCo: 

4916086563859697 Bobsen     Joebob          032015…
4916410586003002 Bobsen     Sallybob        052015…

The first column was the card number, and the remaining columns contained all of the important metadata- holder, expiration date, date issued, etc. The SSIS job just blindly grabbed this data and jammed it into a table called PCARDS . The schema of the table was nearly identical to the flat file, save for an additional column called PC4, which contained the 4-digit suffix of the card number.

The second file was the actual list of transactions: 

9697 Office Max             473.75  04272014…
9697 HmtpnInnSuites         578.95  03192014…
3002 CndaGas Co.            1047.20 05162014…

Again, the PTRANS table was nearly identical to this file format. For relational integrity, the 4-digit suffix linked this table to the underlying purchasing card. The fact that transactions keyed off of the last four digits of the card number immediately caught Adam’s eye. He checked the purchasing card table and confirmed that several cards did have the same 4-digit suffix.

Adam called the bank. “You’ve issued our company multiple cards with the same suffix, but when you send us transactions, the only identifier you send us is the last four digits. So we can’t actually tell which card that transaction is for.”

“That’s impossible. There should never be any repeats in the suffix.”

“And yet,” Adam said, “there are. Can you change the file to send us the whole card number, instead?”

“No! Too many companies depend on the format staying the way it is. We’re not going to make a custom format for you.”

“Couldn’t you add a column at the end? Something? Someone else must have this problem!”

The conversation chased itself in circles until it fell over, panting. Adam hung up the phone with zero progress made. The bank was going to do nothing. Stuck, Adam went to his boss, Steve.

“What I was thinking,” Adam said, “was that we could put all of the unclear transactions in a bucket. When users log in, they’ll be prompted to claim any transactions that might be theirs. There could be an escalation path if transactions remain unclaimed-”

“No!”, Steve said. “First, the business units already feel that the application requires too much data entry. Second, they don’t have a budget to add features, only to fix bugs. Third, no one on the business side is going to actually monitor and maintain that. Fix the bug. Don’t invent new functionality.”

Stymied by the bank and his boss, Adam realized that he was going to have to solve this problem on his own. There was no perfect solution, but Adam was feeling… lucky.

Only a handful of people had duplicate suffixes on their p-cards. Most of the time, the cards were used for one off purchases or to resupply raw materials outside of a regular purchase-order. This meant than some users were heavy users of the cards, but most weren’t. It also meant that many of the common transactions would show repeat business with the same vendor.

Armed with that information, Adam was ready to “fix the bug”. He changed the primary key of the PCARDS table to an auto-generated numeric ID. Then, on PTRANS, he removed the foreign key on the suffix, and replaced it with a foreign key to the new ID field. Finally, he did a bit of creative programming in the SSIS package. If there were multiple purchasing cards that could link to the same transaction, the loading job differentiated them this way:

  • If one and only one card had previous transactions with this vendor, assign the transaction to that card.
  • In all other cases, assign the transaction to the card which had the most transactions for the month.

Adam slapped a comment on the SSIS package to explain the WTFery he had perpetrated. In the long run, his simple heuristic would almost certainly fail to categorize transactions correctly, but Adam wasn’t concerned with the “long run”. He just needed it to work for three more months, until the support for the app moved offshore.

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!