Step 1
This query relies on the file ISOCUDTA in FILExx. The data in this file is preserved for 30 days* by default, and is not available in real-time. This file contains each “message” related to a debit card transaction (e.g., authorization, settlement, inquiries, etc.). Messages for specific transactions are grouped by the Account Base and Authorization ID. This query step groups messages over the previous 15 days in this manner and extracts:
  1. The most recent Activity Sequence Number, which can be linked to the posted transaction record in CU*BASE and is stored in TRANS1 (or month-end equivalent).
  2. The maximum ODP Return Flag. If none of the ISO messages related to a specific transaction (e.g., same Account Base and Authorization ID) have an ODP Return Flag on file, this value will be blank. If, at any point in the series of ISO messages, the transaction was rejected as NSF (Value = N) or used Overdraft/Negative Balance Limits (Value = Y/A), the ODP Return Flag will be populated.
  3. The date stamps for the first and last message transmission.
  4. The maximum “Messaged Available Balance” value.
Step 2
This query identifies all NSF/ANR fees (TRTYPE = 38 or 46) for debit card transactions (TRORIG = 13 or 16) over the previous 30 days from TRANS1/HTRANS1 and extracts:
  1. The fee posting transaction details.
  2. The transaction sequence number immediately preceding the fee posting, which can be tied to the posting record for the debit card transaction.
Step 3
This query excludes NSF/ANR fees (TRTYPE != 38 or 46) and identifies all debit card transactions (TRORIG = 13 or 16) over the previous 30 days from TRANS1/HTRANS1 and extracts:
  1. The debit card transaction details.
  2. The ISO Activity Sequence Number tied to the debit card transaction posting record.
Step 4
This query merges the data from Steps 1 through 3 in the following manner and uses the output from Step 2 as the primary file:
  1. Output from Step 2 equals the Output from Step 3 because the Account Base and Suffix match and the Transaction Sequence Number immediately preceding the Fee Posting matches the Transaction Sequence Number for the Debit Card Posting.
  2. Output from Step 3 equals the Output from Step 1 because the ISO Activity Sequence Number from the Debit Card Posting matches the MAXIMUM ISO Activity Sequence Number from the group of ISO Messages from Step 1 and the Account Base matches between the two files.
The record selection criteria are as follows:
  1. The Maximum ISO Activity Sequence Number from Step 1 is not zero, meaning that there was a successful match between the Output from Step 1 and the Output from Step 3.
  2. The Maximum ODP Return Flag is set to BLANK, meaning that nowhere in the series of ISO Messages grouped by Account Base and Authorization ID was the transaction marked as NSF (N) or as using Overdraft/Negative Balance Limits (Y/A). These are the transactions that can be considered as APSN, since the original authorization did not use any non-available funds.
  3. The posting date on the fee records is between 9 Days Prior and 3 Days Prior. This 7-Day window provides a sufficient lookback, while accounting for the non-real-time nature of the ISOCUDTA file in CU*BASE.
The final report (APSNWKLRPT) will be created in each DAILYxx output queue during beginning-of-day processing each Monday. During end-of-day processing, the report will be archived in CU*SPY.
Field Name Description
ACCTBS Account Base
ACTTYP Account Type
PRDATE Business Date
FEEAMT Fee Amount
REVFLG R=Tran has been reversed
LSTBAL Resulting Balance
EXTDESC Extended Fee Description
MERCHANT Merchant Name
TCARD4 Last 4 of Card
DBTAMT Debit Amount
AVAILBAL Available Balance at Authorization from ISO Messages
LSTBAL Resulting Balance

*Data for the ISOCUDTA table is extracted from the much larger and more complex “ISO files” used in daily EFT processing. These ISO files are not stored in FILExx nor accessible to credit unions for Query purposes, which is why we pull key data from them into the ICOSUDTA file on a daily basis. ISO data is not available in real time, and is stored for 30 days.