This post is part of my continuing series on how to implement extracts. As noted previously, it's cool, sexy and mission-critical to implement an ISO8583-based OLTP auth engine with jPOS, but the prosaic parts (read: the 'batch' or offline side) of the total solution are equally important. Namely, you're never going to certify with anyone unless you can successfully extract what you process and log. [Let's assume here that we're talking about an Acquirer-side extract. I'll discuss some Issuer-side-specific concepts in a subsequent post.]
To recap, in Part 1 I discussed the various fields on the transaction log ('tranLog') and associated processing practices that lay the groundwork for an extract. For example, in that piece I noted that our tranLog has a column called 'refId' and that we use that to link originals and reversals. [The so-called 'transaction log' in a good jPOS-based system is actually a database table, so - unlike legacy payment systems applications like ON/2 - we can go back to the original when processing the reversal and tag it as being reversed.]
In Part 2, I laid out the framework for the extract itself, which covered some overarching goals for extracts in general, the various jPOS 'deploy' components we use (e.g., a 'capture_date' component and an 'extract' component to trigger certain events), and how the extract selects records and tags them (I laid out Alejandro's very useful concept of 'positive' vs. 'negative' reconId tags, which act as a really helpful visual queue to see which records passed the 'isExtractable' test and which ones didn't).
Now, in Part 3 I want to show how you can use jPOS' Field Separator Delimited ('FSD') facility to help implement your extracts. Here's the thing: suppose you've got a project where you're implementing a Debit/EBT/MC/Visa gateway (like FDR), a direct connection to American Express (hereafter referred to as 'AMEX') and another direct link to Discover. From an online perspective, what you have is three ISO8583 interfaces, each of which is a slightly different variation and implementation of the standard (okay, I'm generalizing a bit here: a Debit/EBT gateway has encryption elements and the bar is higher on reversals, but the underlying ISO8583 concepts are the same).
On the offline/extract/settlement/recon/batch (etc.) side, you're not so lucky. The best way to describe the settlement file format that is sent to each insititution is: PROPRIETARY. So, FDR's so-called 'PTS' format (which covers Debit, EBT and Credit) is radically different than AMEX's format, which in turn bears no resemblance to the format required by Discover. At one level, when you're confronted with this reality, there's just no getting around the fact that you need to write at least one program to do this necessary evil (again, reality is that no matter how spiffy and spot-on your OLTP engine is, you're not going to certify unless you can produce a corresponding extract).
From the Part 2 post, you can see that one or more times in the day, we interrogate the records on the tranLog to see which of them are extractable. [I've laid our some loose guidelines there regarding extractable vs. non-extractable records...an inquiry isn't extractable, nor is a rejected transaction, etc., etc....you get the idea.] Now, at this point we pick up from that post and say, okay, we have an extractable record. But (from our example), this record may need to go into an FDR file, AMEX file or a Discover file. So, you need three sub-programs put into play to build these files. But let's look at how FSD can help. As in previous posts, I'll use AMEX as the example. I do this because AMEX puts its documents online in a free and accessible manner (follow the links on this page to the "Global Financial Settlement Guide"). Also, the specs are very well-written. They are concise and clear. Finally, the record formats themselves - though proprietary - are very straightforward. [That, unfortunately, can't be said for all the files you'll encounter.]
You can review that document for more details. But, for the sake of a clean example, let's assume a vanilla implementation that requires none of the industry-specific transaction addendum records described there. That means we have a record set that looks like this:
- TFH - Transaction File Header (one per file)
- TAB - Transaction Advice Basic (one per transaction)
- TBT - Transaction Batch Trailer (one per store/origination point, called an ‘SE’ – Service Establishment – by AMEX)
- TFS - Transaction File Summary (one per file)
The devil is in the details here, so behind the scenes we have a spec for each of our jPOS implementations which describes very specifically how to populate each field in each of the records. But using FSD helps us minimize the programming required to do that field-mapping work. Here's what the four FSD records look like (where 'K' defines a constant value, 'A' is alphanumeric and 'N' is numeric):
<?xml version="1.0" encoding="UTF-8"?>
<schema id='TFH'> <!-- Transaction File Header record -->
<field id='submitter-id' type='K' length='11'>123456 </field>
<field id='filler' type='K' length='21' > </field>
<field id='submitter-ref-num' type='A' length='9' />
<field id='submitter-seq-num' type='N' length='9' />
<field id='file-creation-date' type='N' length='8' />
<field id='file-creation-time' type='N' length='6' />
<field id='variable-length-ind' type='K' length='7' >0502000</field>
<field id='line-feed' type='B' length='1' />
</schema>
[NOTE: submitter-id constant is assigned to each Acquirer. '123456' used here an example.]
<?xml version="1.0" encoding="UTF-8"?>
<schema id='TAB'> <!-- Transaction Advice (Basic) record -->
<field id='transaction-identifier' type='N' length='15'/>
<field id='format-code' type='K' length='2' >02</field>
<field id='media-code' type='K' length='2' >01</field>
<field id='submission-method' type='K' length='2' >03</field>
<field id='filler-1' type='K' length='10'> </field>
<field id='approval-code' type='A' length='6' />
<field id='primary-account-number' type='A' length='19'/>
<field id='expiration-date' type='N' length='4' />
<field id='transaction-date' type='N' length='8' />
<field id='transaction-time' type='N' length='6' />
<field id='filler-2' type='K' length='3' >000</field>
<field id='transaction-amount' type='N' length='12'/>
<field id='processing-code' type='N' length='6' />
<field id='currency-code' type='K' length='3' >840</field>
<field id='extended-payment-data' type='K' length='2' >01</field>
<field id='merchant-id' type='A' length='15'/>
<field id='merchant-location' type='A' length='15'/>
<field id='merchant-contact' type='A' length='40'/>
<field id='terminal-id' type='A' length='8' />
<field id='pos-data-code' type='A' length='12'/>
<field id='filler-3' type='K' length='3' >000</field>
<field id='filler-4' type='K' length='12'>000000000000</field>
<field id='filler-5' type='K' length='3' > </field>
<field id='invoice-ref-number' type='A' length='30'/>
<field id='filler-6' type='K' length='15'> </field>
<field id='line-feed' type='B' length='1' />
</schema>
<?xml version="1.0" encoding="UTF-8"?>
<schema id='TBT'> <!-- Transaction Batch Trailer record -->
<field id='merchant-id' type='A' length='15'/>
<field id='filler-1' type='K' length='15'> </field>
<field id='tbt-id-number' type='N' length='15'/>
<field id='tbt-creation-date' type='N' length='8' />
<field id='number-of-tabs' type='N' length='8' />
<field id='filler-2' type='K' length='3' >000</field>
<field id='tbt-amount' type='N' length='20'/>
<field id='tbt-amount-sign' type='A' length='1' />
<field id='tbt-currency-code' type='K' length='3' >840</field>
<field id='filler-3' type='K' length='3' >000</field>
<field id='filler-4' type='K' length='20'>00000000000000000000</field>
<field id='filler-5' type='K' length='3' > </field>
<field id='line-feed' type='B' length='1' />
</schema>
<?xml version="1.0" encoding="UTF-8"?>
<schema id='TFS'> <!-- Transaction File Summary -->
<field id='number-of-debits' type='N' length='8' />
<field id='filler-1' type='K' length='3' >000</field>
<field id='hash-tot-debit-amt' type='N' length='20'/>
<field id='number-of-credits' type='N' length='8' />
<field id='filler-2' type='K' length='3' >000</field>
<field id='hash-tot-credit-amt' type='N' length='20'/>
<field id='filler-3' type='K' length='3' >000</field>
<field id='hash-tot-amt' type='N' length='20'/>
<field id='line-feed' type='B' length='1' />
</schema>
Your program is going to sequence the records like this:
// TFH writeHeaderRecord
// TAB writeTransactionRecord
// TAB
// TAB
// (etc.) for store 1
// TBT summarize store 1 writeStoreRecord
// TAB
// TAB
// TAB
// (etc.) for store 2
// TBT summarize store 2 (etc.)
// TFS writeTotalsRecord
And, to populate a record, you program will do something like this (as an example, I'm showing here the pertinent lines applicable to populating the 'TAB' record...note how you don't have to specify the fields that are defined as constants (type "K"):
public static final String AMEX_EXTRACT_SCHEMA = "file:cfg/amex-extract-";
private void writeTransactionRecord (CreditTranLog tl, int seqno, Map sec)
throws JDOMException, MalformedURLException, ISOException, IOException
{
FSDMsg m = new FSDMsg (AMEX_EXTRACT_SCHEMA);
m.set ("record-id", "TAB");
m.set ("record-number", Integer.toString(seqno));
m.set ("transaction-identifier", tl.getProgramData()); // AMEX's new "Lifecycle ID"
m.set ("approval-code", tl.getApprovalNumber());
m.set ("primary-account-number", (String) sec.get ("PAN")); // PAN is encrypted on tranLog
m.set ("expiration-date", exp);
m.set ("transaction-date", ISODate.formatDate (tl.getDate(), "YYYYMMdd"));
m.set ("transaction-time", ISODate.formatDate (tl.getDate(), "hhmmss"));
m.set ("transaction-amount", getAmountAsString (tl.getAmount()));
m.set ("process-code", isDebit(tl) ? "000000" : "200000");
m.set ("merchant-id", tl.getMerchantNumber());
try {
Store store = (Store) db.session().load (Store.class, tl.getStoreNumber());
m.set ("merchant-location", toUpper (store.getName()));
m.set ("merchant-contact", toUpper (store.getContact()));
} catch (HibernateException e) {
getLog().error (e);
}
m.set ("terminal-id", getterminalNumber);
m.set ("pos-data-code", isSwiped(tl) ? "200101200040" : "200101600040");
m.set ("invoice-ref-number", ISOUtil.zeropad (Long.toString(tl.getId().longValue() % 100000000L), 9));
ps.println (m.pack());
Your implementation will vary. Again, this specific implementation was the outgrowth of a carefully constructed specification that detailed how to map fields from the the tranLog to the AMEX extract file. In a good-sized enterprise implementation, you'll have quite a few extract requirements (my experience is that your responsibilities on that end will always grow and never, ever shrink). The FSD approach is a really good way to standardize extract and reporting implementations to meet your new or chnaging requirements.
Comments