Real Systems Do Extracts - Part 1

Most of us in the payment systems world focus on OLTP aspects - ISO8583, TPS, QMUX configurations, making use of replicated spaces, logging subsystems, optimizing your implementation for maximum throughput, etc.  You can look at the posts on the jPOS Users' and Developers' list and see that the vast majority of all queries emanate from meeting needs like that.

But, in the real world, it's not enough to excel at OLTP, which of course is jPOS' forte.  Because Real Systems Do Extracts.  Regardless of whether you're an Issuer or an Acquirer, you need in place some type of tight, repeatable, well-defined, extensible process that will allow you to go through your transaction log, indentify the extractable transactions and shape the information into some externally-mandated format.  Very broadly and generally, for an Issuer this format is typically the one designed by the Issuer itself (e.g., your extract may feed a DDA posting system); and, for an Acquirer, you'll probably have a unique extract/settlement file per endpoint.  In an implementation I'm working on right now, we have a Combined Debit/EBT + MC/VS Credit file going to FDR, an AMEX file going to AMEX and a Discover file going to Discover.  [This is a pretty typical set-up for an enterprise-class acquirer.]

A good jPOS (or jPOS-EE) implementation starts with that end in mind.  You'll want to lay the building blocks upfront to ensure you can put a really good extract process in place.  Although extracts are definitely something I consider a 'continuous improvement' exercise, it's important you get the basics  right first time out.  In this post, I'll review the building blocks we typically put into place and show how jPOS and jPOS-EE facilitates all that. Then in Part 2 which will follow, I'll take you through the actual mechanics of the extract.

Now, some of these building blocks may seem blazingly obvious to many of you, but it's worth mentioning each one of them...

tranLog - It goes without saying, you need a tranLog.  The word 'log' here is a bit deceiving.  This is actually a SQL database table.  That's an important distinction.  In legacy OLTP systems (like on the Stratus, for example), these were indeed logs.  The fact that we can now make use of SQL database tables is huge here, because it takes the efficacy of reversal processing to the next level (see further comments below).  It's of massive importance here that jPOS makes use of Hibernate to build a level of separation between the application itself and the SQL implementation.  That's because SQL choices are a near-religious thing at most companies.  You need to let your client make that choice independent of your leanings.  A good client will not only make that choice, but also provide technical leadership in terms of administering and managing the DB side of your implementation.

Here's an example of a tranLog.  [A lot of that 'Request' section consists of some esoteric stuff related to a specific OLS.Switch implementation.  I'm posting it here only to draw your attention to the blue, bolded fields.]

internalResultCode - Was the transaction successful or not?  You need to record this faithfully on the tranLog.  The extract rests of this foundation.  In OLS.Switch (a jPOS-EE-based product implementation), we've developed our own list of internal codes and related mnemonics (we use the mnemonics in the code because it becomes self-documenting).

reconId - Every transaction has an associated recon indentifer.  The jPOS-EE OLTP side of the implementation logs every transaction with reconID = 0.  This field is the linchpin between the OLTP and extract sides of the processing cycle.  Most of my Part II post will center around this field and how the extract uses it.

revInd - This is the reversal indicator.  On all original transactions, this column is initally logged with a NULL value.  When an associated reversal for the original is processed successfully, a good jPOS implementation needs to tag the original as being reversed.  [For more information on the intracies of of acquirer-side reversals and the different scenarios you should account for, please see this extensive previous post.]  In general, a reversal works if all the following conditions are true:

• The original was received and logged.
• The original was successful (i.e., the internalResultCode was logged as 0000).
• The original  was not already reversed (i.e., reversalInd is NULL)
• The original was not already extracted (i.e., reconId = 0)

refId - The reference ID field is used in conjunction with the revInd field.  Alejandro and I both advocate and have jointly implemented systems in which successfully-reversed originals contain specific references to the row ID of the reversal itself, and the associated reversals contain row ID references back to the original.  The ability to make these types of associations in the tranLog is big leap forward from log-based legacy payment systems.  The orginal/reversal cross-reference is a great troubleshooting tool.

Outside the confines of the log, there are two other important building blocks: the concept of capture date and extract time.  captureDate is also a field on the tranLog.  Basically, each client is going to have a defined business date endpoint, a specified time which essentially says "transactions I receive after this time are to be dealt with (from an extract/settlement standpoint) tomorrow."  This concept is often referred to as the 'cutover.'  In a good jPOS-EE implementation, you'd specify something like that in an XML file in the deploy directory so you can adjust the time on the fly as the business model changes.  For example, we have a deploy file called:

01_capture_date.xml

...and it contains this entry:

<property name="cutover" value="13:45:00" />

Our client wanted a mid-afternoon cutover during beta, so they could monitor the extract / transmission / acknoweledgement receipt process and address any issues during business hours when knowledgeable hands are around.  After the beta is complete, they'll shift to a model in which the cutover is adjusted to a late-night value.  With jPOS-EE's hot deployment capabilities, our client can make this move without cycling the application.

There's another XML Deploy component that handles the extract called:

90_extract.xml

...and it contains this entry:

<property name='start' value="14:00" />

That means the automated extract kicks off at 2 PM each afternoon.  Right now, there's one extract a day defined at this particular client.  In this situation (where one extract takes place every cycle), the governing rule is that the extract 'start' property needs to specify a time that is later than the 'cutover' property in the capture_date component.  Otherwise, you'll skip over transactions.

In Part 2, I'll post more about what the extract actually does, how flexible it is in terms of re-runs (when combined with a little SQL Query knowledge) and how jPOS' Field Separator Delimited Message ("FSDMsg") class can really help you set up an easy-to-understand, extensible extract model.  Alejandro doesn't talk much about the FSDMsg capabilities, but it is jPOS' unsung hero, filling the gaps above and beyond his breakthrough ISO8583 packager concept. 

Comments

[Chhil]

Andy this is a great introduction...good job.

Just a few comments from my end..I could be way off..so take them with a pinch of salt.

One of the things I noticed is the cutover property in the xml that you use. Sometimes these cutovers could be network initiated as an online message and sometimes your system might have to trigger it and it would be nice to have something that differentiates that on a per network (I mean star,Amex etc)connection . Plus a having a window after the cutover to allow transactions that are in flight during the cutover to get properly extracted and put into correct batches.

Linking of transactions is indeed a great idea i.e. linking 0100 to 220 and a 420 if it was reversed or a 200 to a 420. Fully reversed transaction may not have a financial impact and may not need to be extracted or declined transactions don't need to get extracted.
Sometimes knowing the a state of the transaction is important too as it may be in a SAF state.

One of the things I noticed in the tran log is the one amoutn field..a request amount and a response amount may be beneficial if there are partial approvals.

Sometimes you may create DB' for OLTP having one structure and another DB for back office stuff like this with data normalized and preventing impact to the OLTP server.

Looking forward to part 2.

-chhil

[rohit garodia]

hey andy i am sorry to send a request to you like this but i would really appreciate if you can help me.

This is Rohit from Bangalore, India and got your webpage address from amazon's movie review page.
I would be grateful to you if you can send me the name and if possible lyrics of song from the movie 'Gegen die wand (head-on)' which was something like 'my love is unrequited...........' (this was like the theme song with the orchestra, being played 3-4 times during the movie)
Hope to get reply,

thanks a lot

regards,
Rohit

P.S. you look to be a voracious blogger!!! :)

[Andy Orrock]

chhil, your comments are all really good ones. Each one deserves a separate blog post. I'll be doing that over the next couple of days.

[Andy Orrock]

Rohit - I can't help you on the lyrics, but assuming you are talking about the shots of the orchestra playing on the banks of the Bosporus, that's Selim Sesler & Orchestra featuring Selim Sesler & Orchestra Idil Uner. [Sessler is a clarinetist; Uner is the singer.] I'm fairly sure the song you're referring to is Track 1 on the soundtrack. It's called "Saniye'em."

[rohit garodia]

andy - yeah thats exactly the song i was talking about, and its real gargantuan effort to get the lyrics. but i thank you a lot for ur concern,
regards,
Rohit