Real Systems Do Extracts - Part 2

This is a continuing series on extracts.  In Part 1, I set the stage for the various components in place to do an extract.  To review, those are:

• The transaction log (a database table, actually)
• Internal result code ('irc') column on the tranLog
• Recon (i.e., 'reconciliation' or 'extract') ID column on the tranLog
• Reversal ID column on the tranLog
• xx_capture_date.xml component in the deploy directory (we're talking jPOS here)
• xx_extract.xml component in the deploy directory

Now, in Part 2 (i.e., this post), I'll focus on the extract from an Acquirer's standpoint.  [I'll cover Issuer-side extracts - an entirely different animal - in future posts.]  And, furthermore, we'll assume that the intent of the extract is to produce reconciliation files for the endpoint organization(s).  ALL credit card authorizer/gateways (e.g., AMEX, Discover, any MC/Visa processor) will want an extract file.  Increasingly, Debit/EBT gateways demand extract files, too (although the intent and usage of that file is quite different...that's a subject for another post).

Here are my goals for an Acquirer-side extract:

1. All extracts should be performed on a captureDate basis.
2. You should be able to specify captureDate (for which you are extracting) in the extract – not specifying one in an execution would infer that today’s calendar date (local server time) is the captureDate.  [Note that in full-blown production, most enterprise-class acquirers run their extract after midnight to extract all of yesterday’s data (this assumes that captureDate is set to mimic calendar date).  So, if the extract time specified is < the captureDate time, then the default for the that run is to extract yesterday's data.]
3. You should be able to run extracts multiple times per day.  If, for example, you were executing the extract for the second time within a given captureDate, that second extract would exclude any record for that captureDate where reconID != 0. 
4. There should be included a ‘no-op’-type extract for non-extractable tran types like coupons, employee discount cards, etc.  This operation would simply by mark all records of that type with the reconID (see more below on mechanics of that).
5. All extracts get a unique extract ID which is placed into the reconID field of any transaction (including non-financial transactions, reversed items, non-approved items, reversals themselves) on the tranLog which is considered for extract during that run.
6. After the last extract runs for a given captureDate, all transactions in the tranLog for that captureDate ought to have a reconID != 0.  [Note that how and when an online transaction is tagged with a specific captureDate is an online consideration related to business policies; for the purposes of this discussion, we just assume that all records have valid captureDate values.]
7. For re-runs, it is assumed to be the technicians’ responsibility to execute (as a preliminary step) an SQL query that will reset the reconID values for the appropriate records back to 0 (the reconID index on the tranLog aids in these types of operations).
8. Your responsibility as an implementer is to place formatted extract files into designated output directories.  Delivery, batch/settlement side telecommunication set-up, settlement line monitoring and delivery confirmation are typically the responsibility of your client (although you should surely confirm the handoff point with them).

Now, here's a real-life example of just such an implementation:

deploy/01_capture_date.xml – The value assigned to the property name ‘cutover’ in this XML file determines the value placed into the captureDate field on tranLog for all transactions.  For example, if the cutover value is “15:45:00” then all transactions received after 3:45 PM and prior to midnight the following day are logged with a captureDate equal to the next calendar date.  Transactions received and processed prior to 3:45 PM are logged with a captureDate equal to today’s date.  To set up a scenario where calendar and business date are one in the same, the cutover value can be set to “23:59:59.”   

deploy/90_extract.xml – The value assigned to the property name ‘start’ in this XML file determines when the extract kicks off every day.  For example, if the start value is “00:10” then extract runs automatically (started by OLS.Switch) each evening at ten minutes after midnight. 

90_extract uses the cutover value from 01_capture_date to determine which day to extract.  For example, if the cutover on Day X is 15:45 and the extract is run at 17:00, then the extract runs for Day X.  If the extract is scheduled to run after midnight, then it extracts Day X – 1 and will do so if scheduled for any time prior to 15:45 PM that following day.  To stop or temporarily suspend automated extracts, you can remove the file 90_extract.xml from the deploy directory.

sysconfig table in SQL database (accessed by jPOS via JDBC connection) – The sysconfig table contains a parameter (id column) called RECON_ID.  The value contains the version of the last extract file created.  So, for example, if the value associated with RECON_ID is 23, then the last extract file placed into the spool directory would be xxx_extract.000023 (where 'xxx' = the endpoint name).

‘Extract’ tab in your UI – An extract can be executed manually via the Extract tab (make it viewable and executable only to Admin-level users).  It can also be used to re-run an extract for a specified date (assumes that the appropriate reconID values were set back to the 0 on the tran log – see below).  We implemented this feature using the EE-WEB component of jPOS-EE.

tranlog and reconID values – An extract for Day X will review all records on the tranLog where reconID equals 0 (zero) and captureDate equals Day X.  Suppose the extract is run and is in the process of creating xxx_extract.000024.  The following tranLog records would be tagged with the reconID = –24 (i.e., reset from the starting value of 0):

• Records related to all 'non-financial' applications like coupons, special discounts, verifications, etc.
• ‘Non-financial’ Debit/EBT and Credit Card transactions (balance inquiries) - these are discernible via the internal tran code system you've implemented.
• Reversed Debit/EBT and Credit card transactions - these are discernible because revId is NOT NULL and != 0.
• The reversals themselves - discernible via tran code
• Non-approved Debit/EBT transactions - these are discernible because irc != 0.

The following records would be tagged with the reconID = 24:

• All approved, non-reversed, balance-affecting Debit/EBT and Credit transactions.  For each of these records, a corresponding transaction detail record is formatted (per the gateway provider's specficiation) and placed into the appropriate extract file.

The use of positive/negative values in the reconId is a superb 'value add' that is Alejandro's brain-child.  You can instantly look at a record on the tran Log and determine whether it was included or excluded for extract.

Should a re-run be required (for whatever reason), you can use SQL techniques to flip the appropriate reconIDs back to zero (given the specific occassion, you may need to flip back only the positive values, not the negative ones...suppose for example you only need to re-create the outgoing extract file).

In the next post, I'll show how we make use of jPOS' FSDMsg component to format and write the extract records.