Create Reports

A guide on using the PayConex Reporting Services API to create transaction reports

The Bluefin Reporting Service API (RSAPI) responds to requests with a report of all transactions for a given day, or a range of dates.

Responses from the API can be provided in CSV, JSON, or XML formats. For security and PCI compliance the reporting data will contain no card/account numbers, PIN blocks, or card verification values.

API URLs

For the certification test environment:

https://cert.payconex.net/api/rsapi/3.8

For the production environment:

https://secure.payconex.net/api/rsapi/3.8

Reporting API Requests and Timing Considerations

Because some data is reconciled with financial institutions nightly the most recent date a report should be ran is from previous business day.

If you plan on running automated reports for multiple single dates, the suggested time to run these reports is between 4:00 AM – 6:00 AM eastern. This time range should give you optimal server response time and should allow enough time for the previous days records to reconcile from any west coast entities.

Reporting Services API Examples

Report for a specific day

This example uses the transaction_date parameter to get a complete list of transactions for the date of 2021-07-04 (YYYY-MM-DD formatting required).

curl --request POST \
  --url https://cert.payconex.net/api/rsapi/3.8 \
  --data account_id=220614966801 \
  --data api_accesskey=32d42f0f0b6c89616d42aed8c96801e6 \
  --data response_format=JSON \
  --data transaction_date=2021-08-10

🚧

Notes on the 'date' variables.

Using, or including, the start_date and end_date variables will override a transaction_date value, even if start_date is blank. The start_date and end_date variable combination should be used for getting data from a date range, and transaction_date should be used for getting data for a particular date.

Report for a range of dates

This example uses the start_date and end_date parameters to get all transactions that fell between the two given dates.

curl --request POST \
  --url https://cert.payconex.net/api/rsapi/3.8 \
  --data account_id=220614966801 \
  --data api_accesskey=32d42f0f0b6c89616d42aed8c96801e6 \
  --data start_date=2021-08-10 \
  --data end_date=2021-08-12 \
  --data response_format=JSON

📘

Did you know?

If you specify a start_date but include no end_date variable or value, it is assumed that you want all transactions from the “start_date” to today (now).

If you specify an end_date but include no value for start_date, it is assumed that you want all transactions from your account start date up to the end_date.

Report for a single transaction

In some cases a full list of transactions isn't needed. If you have a transaction_id value in your system and need more information you can send a request to the API for that transaction's full details.

curl --request POST \
  --url https://cert.payconex.net/api/rsapi/3.8 \
  --data account_id=220614966801 \
  --data api_accesskey=32d42f0f0b6c89616d42aed8c96801e6 \
  --data response_format=JSON \
  --data transaction_id=000000101161

Wildcard Searches

Wildcard searches are possible for the transaction_date, action_date, group, cashier, custom_id, and name variables. By default all parameters are treated as exact matches. A question mark (?) is used for a SINGLE missing character, and an asterisk (*) specifies zero or more unknown characters.

curl --request POST \
  --url https://cert.payconex.net/api/rsapi/3.8 \
  --data account_id=220614966801 \
  --data api_accesskey=32d42f0f0b6c89616d42aed8c96801e6 \
  --data start_date=2021-08-10 \
  --data end_date=2021-08-12 \
  --data response_format=JSON \
  --data 'name=T*'

Date/Time Wildcard Search

Wildcard searching is also possible for the transaction_date parameter.

These wildcard searches are formatted similarly to regular wildcard searches with * or ? characters.

For example, here the day value is replace by ?? to provide transactions from any day of the specific month: transaction_date=2018-08-??

Here is an example of a transaction_date formatted with a timestamp where the hour is known and the wildcard search is on minutes and seconds: transaction_date=2018-08-22 14:*:*.

curl --request POST \
  --url https://cert.payconex.net/api/rsapi/3.8 \
  --cookie BLUEFINA=rd4o00000000000000000000ffffac128117o443 \
  --data account_id=220614966801 \
  --data api_accesskey=32d42f0f0b6c89616d42aed8c96801e6 \
  --data response_format=JSON \
  --data 'transaction_date=2021-08-12 20:*:*'

📘

Did you know?

Reports can also be requested using variables like custom_id, card_brand, group etc... For a full list of parameters that can be used please see the API Request Format table.

ACH Reporting

It is important to note that ACH transactions are processed by banks differently than credit cards. While credit card transactions respond with approvals or declines immediately, ACH transactions are submitted in daily batches. The banking and financial institutions often take multiple business days to respond with a status to an ACH transaction.

If you are running reports to capture your previous day’s transactions, ACH transactions will show “BATCHED” in the “transaction_date” records. This indicates that the ACH transaction was successfully sent at the end of the business day to the processor.

Since the time for an ACH transaction reply can vary it is suggested to run a separate daily report to determine what updates (if any) were made by the financial institutions. To run this report the parameter action_date will be used.

When the action_date parameter is used the API will only respond with ACH transactions that had a status update on the date value provided in the action_date. This means if there are ACH transactions processed in different batches they could be all be updated on the same action_date and included in the report.

Here is a full example request:

curl --request POST \
  --url https://cert.payconex.net/api/rsapi/3.8 \
  --data account_id=220614966801 \
  --data api_accesskey=32d42f0f0b6c89616d42aed8c96801e6 \
  --data response_format=JSON \
  --data action_date=2020-07-20

Handling Report Responses

Responses from the reporting API can be provided in CSV, JSON, or XML formats by setting the response_format parameter.

Here is an example JSON response for a single transaction report:

{
  "api": "RSAPI",
  "version": "3.8",
  "count": 1,
  "transactions": [
    {
      "transaction_id": "000000101161",
      "account_id": "220614966801",
      "authorization_date": "2021-08-12 20:53:13",
      "time_zone": "MDT",
      "tender_type": "CARD",
      "transaction_type": "SALE",
      "keyed": true,
      "swiped": false,
      "transaction_amount": 1,
      "name": null,
      "card_brand": "VISA",
      "last4": "9990",
      "card_expiration": "1230",
      "description": null,
      "user_data": null,
      "authorization_msg": "APPROVED",
      "authorization_code": "332363",
      "avs_response": null,
      "cvv2_response": "N",
      "ip_address": null,
      "cashier": "QSAPI 3.8",
      "street_address1": null,
      "city": null,
      "state": null,
      "zip": null,
      "country": null,
      "phone": null,
      "email": null,
      "group": null,
      "refund_id": null,
      "refund_balance": "0.00",
      "custom_id": null,
      "action_date": null,
      "noc_data": null,
      "recurring_id": null,
      "input_group": null,
      "invoice_entry": "",
      "trace_num": "000000088401",
      "company": null,
      "entry_mode": "keyed"
    }
  ]
}

The API responds with header information in order to provide information about the response format and MIME/Type.

For example, for CSV files, the HTTP Header will include: Content-type: text/csv

A custom response code is also provided within the HTTP header: QS-response-code: NNN Message

For error conditions (Response Codes other than 100), a human-readable error message will be returned as the body of the response: QS-response-code: 601 Authentication failed Account number and/or API access key is missing or invalid.. A list of these response codes can be found at the bottom of this page in the RSAPI HTTP Response Format table.

📘

Did you know?

In combination with the Reporting Service API, PayConex provides a webhook called POSTback. POSTback allows a developer to receive an HTTP POST after every transaction request. For more information on using POSTback please see this article.

RSAPI Request Format

The following table includes an index of all API request posts for RSAPI.

Variable Name Max Type Req'd Description
account_id 12 Numeric Yes This is the Payconex account identification number that you are issued after your account has been set up.
ach_settle_date 10 YYYY-MM-DD No Used for getting all ACH transactions with the value supplied.
Note: The value for tender_type must be ACH when using this.
action_date * 10 YYYY-MM-DD No Download transactions with this action date (for ACH only; default: yesterday). This is useful for viewing ACH updated transactions on a specific date.
amount 10 Decimal No The amount of the original transaction. Must be zero or positive decimal numbers.
amount_max 10 Decimal No The maximum amount of a transaction amount range. Use with amount_min. Must be zero or positive decimal numbers.
amount_min 10 Decimal No The minimum amount of a transaction amount range. Use with amount_max. Must be zero or positive decimal numbers.
api_accesskey 32 Alphanumeric Yes This is a secret key that you will be provided when your Payconex account is set up and when you’ve requested access to QSAPI.
batch_detail 1 Boolean No If sent as "1", batch number and batch date/time will be returned.
cashier * 100 Alphanumeric No The cashier that created the original transaction.
custom_id * 50 Alphanumeric No Any custom text value that may have been used.
date 1 Enumerated No Value can be either "ACTION" or "BATCH".
ACTION = will return ACH records with action_date in the range (similar to using action_date with its single value)
BATCH = will return records with a batch_date in the range.
end_date * 10 YYYY-MM-DD No Used for getting transactions in a date range. Use with start_date.
group * 25 Alphanumeric No Groups are flexible groups that can be used for various reasons, including: a) assign transactions to a specific group. b) direct transactions to separate back-end or depository accounts.
include_ach_settle_date 1 Boolean No TIf sent as "1", the ACH transacitons settle date will be returned.
name 100 Alphanumeric No The name used for the customer on the original transaction.
reportable_fields 1 Boolean No If sent as "1", any reportable field values from HPF (Hosted Payment Forms) will be added to the end of the RSAPI response, with column names mathing account setup.
response_format 4 Enumerated No How the resulting data is returned. Valid options are:
CSV: Comma-separated value formatted file (default)
JSON: JavaScript Object Notation formatted file.
XML: Extended markup language formatted file.
start_date * 10 YYYY-MM-DD No Used for getting transactions in a date range. Use with end_date.
status 8 Enumerated No Value can be either "APPROVED" or "DECLINED" which will return matching transactions. If omitted, BOTH types will be returned.
tender_type 4 Enumerated No Payment type to include in the report:
ALL, CARD, ACH, EBT, or GIFT (default: ALL).
transaction_date * 19 YYYY-MM-DD HH:MM:SS No Download transactions ran for this date (default: yesterday).
transaction_id 12 Numeric No The "front-end" transaction id for a transaction.

RSAPI Response Format

The following table includes an index of all API responses for RSAPI.

Variable Max Type Description
account_id 12 Numeric The account ID for the transactions
ach_return_code 4 alphanumeric The ACH Return Code for ACH transactions. Will only be included for ACH transactions that have cleared and batched (could be up to 5 days depending on the banks).
ach_settle_date 16 Character The date and time of the ACH transactions settlement. May be empty if not included in a batch yet. Can be used with action_date value if a transaction was returned to get more historical info. ONLY returned if include_ach_settle_date=1 was submitted.
action_date 10 Date (YYYY-MM-DD) The action date indicates that an ACH update has been applied to a previous ACH transaction. A status response to an ACH settlement takes days to receive. By using action_date and the date you wish to check you can see what ACH transactions have been updated on a specific day. Responses are:
DECLINED\ERRORS
DECLINED\RETURNED
SETTLED\FUNDED
authorization_code 6 Alphanumeric The authorization code returned by the processor.
authorization_date 19 YYYY-MM-DD HH:MM:SS The date and time when the transaction was initiated (YYYY-MM-DD HH:MM:SS)
authorization_msg 50 Alphanumeric APPROVED or the auth message from the processor (e.g. AUTH DECLINED 200).
avs_response 1 Enumerated A single letter address verification response:
DFJMQVXY Address and ZIP code match
LWZ ZIP code match, address is wrong
ABOP Address match, ZIP code is wrong
KN No match, address and ZIP is wrong
U No data from issuer/banknet switch
R AVS System unable to process
S Issuing bank does not support AVS
E Error, AVS not supported for your business
C Invalid address and ZIP format (International)
I Address not verifiable (International)
G Global non-verifiable address (International)
? Unrecognized codes (none of the above)
_ No AVS data (blank)
batch_date 16 Character The batch date and time of the transaction. May be empty if not included in a batch yet. ONLY returned if batch_detail=1 was submitted.
batch_id 4 Numeric The batch number (ID) of the transaction. May be empty if not included in a batch yet. ONLY returned if batch_detail=1 was submitted.
card_brand n/a Enumerated Customers brand of card used. VISA, MASTERCARD, AMERICAN EXPRESS, DISCOVER, ACH, EBT.
card_expiration 4 Numeric The month and year of the card expiration date in the format MMYY. For example, 0115 for January 2015.
cashier 100 Character The cashier value from the transaction
city 100 Character Customer’s city
company 50 String Company name field from the transaction
country 3 Alphanumeric Customer’s country
custom_id 50 Character Custom ID value from the transaction.
cvv2_response 1 Enumerated Single letter card verification value response:
M CVV match
N CVV does not match
P CVV not processed
S Card has CVV, customer says it doesn't
U No CVV data from issuer
? Unrecognized codes (none of the above)
_ No CVV data (blank)
description 65K Character The description entered in the transaction
email 100 Character Customer’s email
entry_mode 17 Enumerated The value of the entry mode from the transaction:
KEYED
SWIPED
EMV
CONTACTLESS
FALLBACK SWIPED
group 12 Alphanumeric Group value from the transaction
input_group 10 Character Input group from the transaction
invoice_entry 65k Character Concatenated list of invoice line description and amounts, each separated by &, when invoice feature is used for transactions.
ip_address 15 NNN.NNN.NNN.NNN IP address of the client which initiated the transaction.
keyed 1 Enumerated 1 indicates key entry of card information, 0 indicates not a keyed entry.
last4 4 Numeric The last four digits of the card number or Primary Account Number (PAN). For ACH, it is the last four digits of the account number.
name 100 Character Customer name
noc_data 50 Character The NOC data from the transaction
phone 20 Alphanumeric Customer’s phone
recurring_id 12 Numeric This unique identifier is provided in addition to the transaction ID to allow for identifying and segmenting recurring transactions. This variable is in version 3.6.1 and higher.
refund_balance 9 Decimal Refund balance (if applicable) from the transaction
refund_id 12 * Numeric Refund ID from the transaction (length may be longer if multiple refunds were ran, all refund transaction id's separated by a pipe indicator)
Reportable Fields ** ? Character If "reportable_fields" was set to "1" and there are reportable fields on the HPF (Hosted Payment Form), those columns will be included at the end of the response.
state 2 Alphanumeric Customer’s state
street_address1 50 Character Customer’s street address
swiped 1 Enumerated 1 indicates swiped entry of card information, 0 indicates not a swiped entry.
tender_type 4 Enumerated The method of transaction that was made: CARD, ACH, EFT, EBT, USDA, FNS, GIFT.
time_zone 3 Character This is a timezone indicator for the RSAPI fields that contain actual times. Would be similar to EST, CST, etc (batch_date will ALWAYS be in CST)
trace_num 12 Integer back-end transaction number that might be used by some end processors for reporting or communication.
transaction_amount 9 Numeric w/ decimal Amount of funds involved in the transaction.
transaction_id 12 Numeric The transaction id for the new transaction. When using tokenization, this is the transaction_id that you submit as the token_id.
transaction_type 14 Enumerated Type of transaction: AUTHORIZATION, SALE, REFUND, CREDIT, CAPTURE, SETTLE-BATCH, STORE, FORCE.
user_data 65K Character User data entered in the transaction. Originates from the QSAPI variable "custom_data".
zip 10 Numeric with hyphen Customer’s zip

Please note: For your security, transactions older than 18 months may be purged from our system.

RSAPI HTTP Response Format

RSAPI HTTP Response Format Variables Index In addition to the RSAPI responses shown above, in the HTTP header of the response, you will find an additional piece of data that you may wish to capture, called “QS-response-code”. An example of an HTTP header for a RSAPI request is similar to the following:

HTTP/1.1 200 OK Date: Tue, 03 Aug 2013 12:23:05 GMT Server: Apache QS-response-code: 601 Authentication failed Connection: close Vary: Accept-Encoding,User-Agent Content-Encoding: gzip X-Frame-Options: SAMEORIGIN Cache-Control: private, no-cache, no-store, proxy-revalidate, no-transform Pragma: no-cache Content-Length: 63 Content-Type: text/html; charset=UTF-8

Values for the “QS-response-code” variable (Code and Message concatenated) are as follows:

Response Code Response Message Description
100 OK Success. Report file follows.
601 Authentication Failed Account number and/or API access key is incorrect.
607 Invalid Date Date format is incorrect (YYYY-MM-DD) or invalid date (2009-02-31).
608 No Data No data or transactions matched the search criteria.
611 Data Unavailable A temporary system error has prevented access to the data requested. Please try again later or contact administrator.
621 Invalid Parameter One or more parameters do not match the RSAPI Request Format specification.
622 Missing Parameter One or more required parameters are missing.
623 Conflicting Parameters Combination of parameters is invalid (e.g., specifying action_date for card transactions).

If “QS-response-code” is NOT equal to “100 OK”, then you will receive the Description from above in the actual response from your request.


What’s Next

Follow these links to other API examples

Did this page help you?