Base RDFs
This page contains details about each of the Base RDFs, which are the default RDFs. See About Raw Data Files (RDFs) for general information about these files. These CSV files contain all of the possible fields for each RDF.
- Customer Master RDF
- Account Card RDF
- Authorized Transactions RDF
- Posted Transactions RDF
- Lookup file
Note
New fields will be appended to these files as the need arises. For new data elements (DEs), Galileo will add them periodically as the networks provide them and will communicate these additions prior to their implementation. You can also request additional fields, subject to approval, by submitting a client request.
Customer Master RDF
The Customer Master RDF is a list of all of your primary accounts. The first Customer Master RDF that you receive contains all of your account records. The RDFs after that contain only the new records and the changed records from the previous day.
A primary account in this RDF is determined by these rules:
- There is only one primary account per balance ID, which is
GALILEO ACCOUNT IDin the RDFs. - The primary account is the most recent PRN that belongs to the first account holder to be associated with the balance ID.
Example
- Jared signs up for a debit card. The account is assigned PRN 2222 and balance ID 3333. PRN 2222 is the primary account.
- Jared's wife Josephine signs up for the same debit card, as a secondary account, that shares the balance with Jared's account. Her PRN is 4444 with balance ID 3333. This is not the primary account.
- Jared signs up for a virtual card that shares the balance with his first card. The PRN is 5555 and the balance ID is 3333. PRN 5555 is now the primary account for balance ID 3333.
This table contains all possible fields for the Customer Master RDF.
- The primary key for this file is
GALILEO ACCOUNT ID+UNIQUE PROGRAM ID. - The filename format is
fw_customermasterfile_yyyymmdd.csv. - "Signed" amounts have a
-sign for debits and no sign for credits:-30.25for a debit and23.67for a credit. - All
DATETIMEfields areYYYY-MM-DD h24:mm:ss. - All fields except for the primary-key fields can potentially return a null value.
| Field name | Data type | Description |
|---|---|---|
UNIQUE PROGRAM ID | NUMBER (18) | The identifier that Galileo has assigned to your program |
ISSUED DATE | DATETIME | The date-time when the customer account was first changed to status: N (active). Called start_date in other contexts. |
ID | VARCHAR2 (100) | The customer's ID that was used for KYC/CIP, usually the SSN. You can see an SSN only if you are PCI compliant; otherwise, you get blank spaces. |
ID TYPE | NUMBER (18) | The type of identifier in the ID field, such as SSN or driver license. See the Customer ID Types enumeration or the Lookup file for the possible values. |
PRIMARY CARDHOLDERS FNAME | VARCHAR2 (32) | The customer's first name |
PRIMARY CARDHOLDERS LNAME | VARCHAR2 (32) | The customer's last name |
ADDRESS LINE 1 | VARCHAR2 (48) | Customer's address line 1 |
ADDRESS LINE 2 | VARCHAR2 (48) | Customer's address line 2 |
CITY | VARCHAR2 (48) | The city of the customer's primary address |
STATE | VARCHAR2 (16) | The state or province of the customer's primary address |
ZIP CODE | VARCHAR2 (10) | The postal code of the customer's primary address |
PRIMARY PHONE NUMBER | VARCHAR2 (53) | The phone number that the customer gave as the primary number |
SECONDARY PHONE NUMBER | VARCHAR2 (29) | Encrypted phone number that the customer gave as the secondary (other) number; decrypted for PCI-compliant clients |
STATUS | CHAR (1) | The status of the account |
CURRENT BALANCE | NUMBER (10,2) | Signed. The balance of the account as of 23:59:59 on the previous day. This is the ledger balance, which is the total of all posted transactions. Pending transactions such as authorization holds are not subtracted out. This field is blank if there have been no transactions since account creation. |
CREATED DATE | DATETIME | The date-time when a customer first applied for an account, regardless of whether the application is approved or denied. Called application_date in other contexts. |
NEGATIVE BALANCE DATE | DATETIME | Date-time on which the available balance was driven negative |
NEGATIVE BALANCE FEE AMOUNT | NUMBER (9,2) | Fee assessed to the account when it was driven negative |
RELOADABLE INDICATOR | CHAR (1) | Whether the card is reloadable: 0 (not reloadable) or 1 (reloadable) |
GALILEO ACCOUNT ID | NUMBER (18) | The balance ID, which is different from the PRN. It is possible for multiple PRNs to share the same balance ID. |
TYPE OF ACCOUNT | NUMBER (18) | The ID of the account type; see the Lookup file to interpret the values. |
AVAILABLE BALANCE | NUMBER (9,2) | Signed. The available balance (OTB) as of 23:59:59 on the previous day. This is the CURRENT BALANCE with pending transactions subtracted out. This field is blank if there have been no transactions since account creation. |
EXTERNAL ACCOUNT NUMBER | CHAR (53) | An identifier that you provide for your own tracking purposes; you pass this value in the externalAccountId parameter of the Create Account or Update Account endpoint. |
LAST TRANSACTION DATE | DATETIME | The date-time of the last posted transaction on this account as of 23:59:59 on the previous day, including payments, settlements, adjustments, and fees. This value does not include unsettled authorizations or pending transactions. |
DATE OF BIRTH | VARCHAR2 (29) | Account holder date of birth, encrypted. Decrypted only for PCI compliant clients: MMDDYYYY |
BILL CYCLE DAY | NUMBER (18) | Day of the month for the billing cycle |
LOCATION ID | NUMBER (18) | Location at which the account was created, if applicable. Value can be provided by you or by Galileo. |
AGENT USER ID | VARCHAR2 (30) | ID of the CST agent who submitted the account application |
ID2 | VARCHAR2 (100) | Secondary identity number, if provided, such as driver license or information provided to the embosser |
ID2 TYPE | NUMBER (18) | The type of identifier in ID2, such as driver license or client-generated ID. See the Customer ID Types enumeration or the Lookup file for the possible values. |
PRN | CHAR (12) | The payment reference number, a unique Galileo-generated identifier for an account. This is the PRN for the primary account holder. |
PRODUCT ID | NUMBER (18) | Identifier for the account's product in the Galileo system |
ACCOUNT STATUS CHANGE DATE | DATETIME | Date-time when the account status was last changed |
AFFILIATE HIT ID | VARCHAR2 (50) | The contents of the userData parameter in the account-enrollment endpoint request |
APPLICATION ID | NUMBER (18) | Identifier (appId) as returned by the account-enrollment endpoints |
WATCH FLAG | CHAR (1) | Account-level watch flag: F (fraudulent), S (suspicious) or N (normal, default) |
BUSINESS NAME | VARCHAR2 (150) | Name of the business that owns the business account |
CIP APPLICATION ID | NUMBER (18) | Galileo-generated identifier for the call to the CIP system |
CIP ID | VARCHAR2 (50 bytes) | String returned in the CIP call to Galileo |
CIP STATUS | CHAR (1) | CIP status: F (failed), R (referral), S (system issue) or P (pass) |
GALILEO CLIENT ID | NUMBER (18) | Galileo-generated identifier for the account holder |
COUNTRY | VARCHAR2 (3) | ISO 3166-1 code for the customer's country of residence |
CREDIT LIMIT | NUMBER (9,2) | Credit limit |
EMAIL ADDRESS | VARCHAR2 (64) | Email address of the account holder |
SSN | VARCHAR2 (29) | Encrypted Social Security number; decrypted for PCI-compliant clients |
IP ADDRESS | VARCHAR2 (16) | IP address of the user who applied for the account |
BUSINESS PRODUCT INDICATOR | CHAR (1) | Whether the account is a business product account: Y (business account) or N (not a business account) |
LAST FREEZE DATE | DATETIME | Date-time at which the card was last frozen |
XID | NUMBER (18) | Internal account identifier; has a 1:1 relationship with the PRN |
THIRD PHONE NUMBER | VARCHAR2 (100) | Third or other phone number, often a mobile number |
CIP INFORMATION | VARCHAR2 (4000) | Contents of the model_results object in the enrollment endpoint responses |
OCCUPATION | VARCHAR2 (60) | Contents of the occupation parameter in enrollment endpoint requests |
OD BAL ID | NUMBER | Balance ID for the overdraft account, if any |
OVERDRAFT LIMIT | VARCHAR2 (20) | Overdraft limit |
OPTED IN DATE | DATETIME | Date-time when the account holder last opted in to overdraft |
OPTED OUT DATE | DATETIME | Date-time when the account holder last opted out of overdraft |
TOTAL ID OVERRIDE | CHAR (1) | Whether ID verification was skipped for manual approval: Y (skipped) or N (not skipped) |
PRIMARY ACCNT ACTIVE | CHAR (1) | Specifies whether the primary account is active: Y (active) or N (not active) |
PRIMARY ACCOUNT STATUS | CHAR (1) | Status of the primary account |
SAVINGS GALILEO ID | NUMBER | Balance ID of the savings account that is associated with the primary account |
TOTAL ID PASS | CHAR (1) | Whether the account holder passed ID verification: Y (passed) or N (did not pass) |
Account Card RDF
The Account Card RDF is a list of all cards that you have issued. The first Account Card RDF that you receive contains all of your card records. The RDFs after that contain only the new records and the changed records from the previous day.
Note
Any changes to a card record, including resetting the PIN-fail count by a backend process, results in the card record being included in the differential file. Some of these changes are not reflected in the data in the RDF.
This table contains all of the possible fields for the Account Card RDF.
- The primary key for this file is
UNIQUE PROGRAM ID+CARD ID. - The filename format is
fw_accountcard_yyyymmdd.csv. - All
DATETIMEfields areYYYY-MM-DD h24:mm:ss. - All fields except for the primary-key fields can potentially return a null value.
| Field name | Data type | Description |
|---|---|---|
UNIQUE PROGRAM ID | CHAR (12) | The identifier that Galileo assigned to the program |
GROUP ID | NUMBER | Source or affiliate ID. Usually the same as LOCATION ID in the Customer Master RDF. |
PRODUCT ID | NUMBER (18) | Identifier for the account's product in the Galileo system. |
GALILEO ACCOUNT ID | NUMBER | The balance ID, which is different from the PRN. It is possible for multiple PRNs to share the same balance ID. |
PRN | NUMBER | The payment reference number, a Galileo-generated ID for the card's account |
EXPIRATION DATE | DATETIME | The card's expiration date-time. If you are not PCI compliant you will receive empty spaces for this value. |
CARD STATUS | CHAR (1) | The card status |
CARD IDENTIFIER | NUMBER | Sometimes called the CAD or card_id, this identifier is given to each unique card that is issued to an account. There is a 1:1 relationship between a card ID and a PAN. If you are not PCI compliant, you can use this identifier instead of the PAN. |
CARD NUMBER | VARCHAR2 (53) | The card's PAN. If you are not PCI compliant you will get empty spaces for this value. |
TYPE OF CARD | NUMBER | The branded network to which the card belongs, such as Mastercard or Visa, as well as the type of card, such as personalized or instant issue. See the Lookup file to interpret the values. |
ACCOUNT TYPE | CHAR (1) | Account type |
ACTIVATION DATE | DATETIME | Date-time at which the card was activated |
CARD STATUS CHANGE DATE | DATETIME | Date-time on which the card status last changed |
GALILEO CLIENT ID | NUMBER | Identifier that Galileo assigned to the cardholder |
EMBOSS DATE | DATETIME | Date-time at which the card was last embossed |
EMBOSS EXPIRY DATE | DATE | Expiry date of the latest embossed card |
FROZEN | CHAR (1) | Whether the card is currently frozen: Y (frozen) or N (unfrozen) |
LAST 4 PAN | VARCHAR2 (4) | Last 4 digits of the PAN |
LOCK END DATE | DATETIME | Date-time when the card was last frozen |
LOCK START DATE | DATETIME | Date-time when the card was last unfrozen |
SEMI ENCRYPTED PAN | VARCHAR2 (32) | 16-digit masked PAN: first 6 and last 4 digits visible and middle 6 digits encrypted |
PRIMARY ACCOUNT STATUS | CHAR (1) | Status of the primary account |
SHIP TYPE | CHAR (1) | Shipment type: 1 (standard) or 2 (express) |
XID | NUMBER | Internal account identifier; has a 1:1 correspondence with the PRN |
ZIP | VARCHAR2 (10) | Ship-to postal code |
ACTIVATION CHANNEL | VARCHAR2 (20) | Method by which the card was activated: CST, Web or IVR (automated phone system) |
DB ID | NUMBER | Galileo client identifier |
ADDR1 | VARCHAR2 (73) | Ship-to address, line 1 |
ADDR2 | VARCHAR2 (73) | Ship-to address, line 2 |
CITY | VARCHAR2 (93) | Ship-to city |
SHIPTO NAME | VARCHAR2 (136) | Ship-to name |
STATE | VARCHAR2 (26) | Ship-to state |
ACTIVE ACCOUNT | CHAR (1) | Whether the card account is active: Y (active) or N (not active) |
CARD STATUS UPDATE DATE | DATETIME | Date-time of the last card status update |
Authorized Transactions RDF
The Authorized Transactions RDF contains transactions that passed through the authorization stream during the previous day. These transactions include:
- Authorization and preauthorization requests
- Reversals
- Completions
- AVS-only checks
- Provisioning requests
- Balance inquiries
This RDF does not always include these authorization events:
- Mastercard Banknet merchant credits (adjustments)
- Visa Money Transfer and ReadyLink card loads (payments)
Only a subset of the transactions in this RDF will also be included in a Posted Transactions RDF. For example, authorization reversals, AVS-only checks, provisioning requests, and denied transactions are not in the Posted Transactions RDF because they do not settle.
Note
By default, denied transactions are included with the Authorized Transactions RDF. As desired, you can ask Galileo to omit denied transactions from the file.
Foreign authorizations
When a transaction takes place in a country that is foreign relative to the account's country, the AUTHORIZATION AMOUNT is in the currency of the account. For example, if an account is located in the United States and the cardholder makes a purchase in Canada, the AUTHORIZATION AMOUNT is in U.S. dollars and the TRANSACTION CURRENCY CODE is 124 (Canadian dollar). The currency conversion is performed by the card network, and it reflects the conversion rate at the time of the authorization.
Keep in mind that some online merchants conduct business in the currency of the customer instead of the location of the merchant, depending on where the customer is located. For example, a website that is based in the U.K. could have a U.S. version of its website, and so transactions on that version would be conducted in U.S. dollars.
For examples of foreign transactions see International transaction in Card Transaction Examples and these scenarios:
Authorized Transactions RDF fields
This table contains the default fields for the Authorized Transactions RDF.
- The primary key for this file is
UNIQUE PROGRAM ID+AUTHORIZATION CODE+NETWORK CODE. - The filename format is
fw_authorizedtransactions_yyyymmdd.csv. - The Source column indicates where the value came from: Galileo-provided or a data element (DE) in the ISO 8583 message in the authorization stream. The actual DE number for a field may vary by network, and not all DEs are present for all networks.
- "Signed" amounts have a
-sign for debits and no sign for credits:-30.25for a debit and23.67for a credit. - All
DATETIMEfields areYYYY-MM-DD h24:mm:ss. - All fields except for the primary-key fields can potentially return a null value.
- See Data Elements to correlate the DEs with other Galileo resources.
| Field name | Source | Data type | Description |
|---|---|---|---|
UNIQUE PROGRAM ID | Galileo | NUMBER | The identifier that Galileo has assigned to your program |
GALILEO ACCOUNT ID | Galileo | NUMBER | The balance ID, which is different from the PRN. It is possible for two or more PRNs to share the same balance ID. |
CARD IDENTIFIER | Galileo | NUMBER | Sometimes called the CAD, this identifier is given to each unique card that is issued to an account. There is a 1:1 relationship between a card ID and a PAN. If you are not PCI compliant, you can use this identifier instead of the PAN. |
TRANSACTION DATE/TIME | Galileo | DATETIME | The time that Galileo responded to the authorization request |
TRANSACTION CURRENCY CODE | DE049 | CHAR (3) | The currency code at the point of sale |
ADDRESS VERIFICATION RESPONSE | Galileo | CHAR (1) | The single-character result of the AVS check that Galileo passed to the network. See AVS Codes for valid values. |
AUTHORIZATION RESPONSE | DE039 | CHAR (2) | The response code that you or Galileo returned to the network. See the Authorization Response Codes enumeration for possible values. |
TRANSACTION AMOUNT | DE004 | NUMBER | Signed. The amount that you or Galileo authorized for the transaction, in the currency of the account. This amount may be different from the authorization request in the case of a partial authorization. If AUTHORIZATION RESPONSE is 10 or 87, the amount is a partial authorization. |
AUTHORIZATION CODE | Galileo | NUMBER (18) | A Galileo-generated identifier (auth_id). See Transaction IDs for information about unique identifiers and ID collisions. |
NETWORK CODE | Galileo | CHAR (1) | A single-letter identifier to specify the subnetwork over which the authorization request arrived. See Network Codes for possible values. |
MERCHANT NUMBER | DE042 | CHAR (15) | An identifier that each network assigns to a merchant location |
MERCHANT DESCRIPTION | DE043 | CHAR (40) | Descriptive text provided by the merchant to identify the merchant name, location and sometimes the transaction type |
MERCHANT CATEGORY CODE | DE018 | NUMBER | The ISO 18245 four-digit identifier that specifies the type of merchant, such as ATM, gas pump, bookseller, airline, hotel, and so on |
TRANSACTION CODE | Galileo | CHAR (2) | A numeric value that corresponds to the type of transaction that is being attempted |
MERCHANT COUNTRY CODE | DE049 | VARCHAR2 (3) | The ISO 3166-1 three-digit code for the country where the merchant is officially located |
PRN | Galileo | CHAR (12) | The payment reference number, a unique Galileo-generated identifier for an account |
PRODUCT ID | Galileo | NUMBER | Identifier for the account's product in the Galileo system |
ACQUIRING ID | DE032 | VARCHAR2 (11) | Identifier for the merchant's acquirer |
ACTIVITY ID | Galileo | NUMBER (18) | Identifier for the transaction in the activity table, which contains anything that affects the available balance |
ASSOCIATION TRANS ID | DE062 | VARCHAR2 (15) | Visa only. The transaction ID provided by the network |
AUTHORIZATION STATUS | Galileo | CHAR (1) | Status of the authorization |
AUTH TRANSACTION CODE | Galileo | VARCHAR2 (10) | The transaction code (act_type + otype) |
BAI | DE104 | VARCHAR2 (3) | Business Application Identifier. A value provided by Visa to identify the type of Visa Money Transfer that is being performed |
BILLING AMOUNT | DE006 | NUMBER (16,4) | Signed. Transaction amount in the billing currency, which is the same as the account's currency |
BILLING CURRENCY CODE | DE051 | CHAR (3) | Currency code for BILLING AMOUNT |
CASHBACK AMOUNT | DE054 | NUMBER (9,2) | Original cashback request amount |
CONTACTLESS TRANSACTIONS | DE022 | CHAR (1) | Whether the card was authenticated using contactless technology: Y (contactless) or N (not contactless) |
CARD PRESENT INDICATOR | DE061 | CHAR (1) | Whether this was a card-present transaction: Y (card present) or N (card not present) |
CREDIT INDICATOR | Galileo | CHAR (1) | Whether the transaction arrived at Galileo over credit rails: Y (credit rails) or N (debit rails) |
DE28 | DE028 DE046 (Allpoint) | VARCHAR2 (9) | Local surcharge amount, a fee applied to the ATM withdrawal amount by the ATM's operator |
RETRIEVAL REF NUMBER | DE037 | VARCHAR2 (12) | Retrieval reference number. |
EXTERNAL TRANSACTION ID | DE038 | CHAR (6) | The authorization identification response, which is a numeric code that Galileo returns to a merchant to verify that the transaction was authorized. This number is often printed on customer receipts. |
OTC FLAG | DE003 | CHAR (1) | Whether the transaction included an over-the-counter cash advance: Y (cash advance) or N (no cash advance) |
PIN FLAG | Galileo | CHAR (1) | Whether a PIN was used to authenticate the card: Y (PIN) or N (no PIN) |
LOCAL AMOUNT | DE004 | NUMBER (16,4) | Signed. Transaction amount in the currency at the point of sale |
LOCAL CURRENCY CODE | DE049 | CHAR (3) | Currency code for LOCAL AMOUNT |
LOCAL SURCHARGE AMOUNT | DE028 | NUMBER (16,4) | Local surcharge amount, a fee applied to the ATM withdrawal amount by the ATM's operator |
MERCHANT POSTAL CODE | VARCHAR2 (5) | Postal code for the merchant's location | |
MOBILE TRANSACTIONS | Galileo | CHAR (1) | Whether a token requestor ID was present: Y (mobile wallet) or N (not mobile wallet) |
MESSAGE TYPE FLAG | Galileo | CHAR (2) | The otype of the authorization |
MTI | CHAR (3) | The message type indicator for the authorization response from Galileo, minus the first digit | |
PIN ENTRY CAPABILITY | DE022 | CHAR (1) | Third character of DE022. Whether the card reader had PIN-entry capability. See DE022 Codes. |
POS ENTRY MODE | DE022 | NUMBER (2) | First two characters of DE022. How the PAN was entered, such as magnetic stripe, EMV chip, or contactless. See DE022 Codes. |
POS TYPE | DE003 | CHAR (3) | Type of card authentication at the point of sale: SIG (signature) or PIN |
REVERSAL ID | Galileo | NUMBER | Contains the authorization ID of the previous authorization in a series, such as with incremental authorizations, reversals, and completions. See Linking transactions in the About Transactions guide for more information. |
RISK SCORE | DE048 | VARCHAR2 (10) | Risk score that was calculated by the network |
SETTLE AMOUNT | DE005 | NUMBER (16,4) | Signed. Transaction amount in the settlement currency |
SETTLE CURRENCY CODE | DE050 | CHAR (3) | Currency code for SETTLEMENT AMOUNT |
SLI INDICATOR | DE048SE42 | CHAR (1) | Security level indicator, included with Mastercard 3DS authentication |
SOURCE | Galileo | NUMBER | Unique number assigned to a network:1 (Maestro)2 (Mastercard credit)3 (STAR)4 (Visa)5 (Discover)6 (Allpoint)7 (Pulse) |
TERMINAL ID | DE041 | CHAR (8) | Identifier of the card reader |
TERMINAL NETWORK | Galileo | VARCHAR2 (50) | Visa only. Network that the card reader used |
TOKEN_REQUESTOR_ID | VARCHAR2 (11) | Identifier for the token requestor. See the Lookup file for valid values. | |
TOKEN TYPE | VARCHAR2 (30) | Type of token requested: Apple Pay, Samsung Pay or Google Pay | |
XID | Galileo | NUMBER | Internal account identifier; has a 1:1 relationship with the PRN |
AAV INDICATOR | DE048SE43 | CHAR (2) | Universal cardholder authentication field (UCAF) or account holder authentication value (AAV) |
CONVERSION RATE | DE010 | VARCHAR2 (8) | The conversion rate from TRANSACTION AMOUNT to BILLING AMOUNT. See Conversion rates to interpret the value. |
CONVERSION RATE - RECON | DE009 | VARCHAR2 (8) | The conversion rate from TRANSACTION AMOUNT to SETTLE AMOUNT. See Conversion rates to interpret the value. |
STIP RESPONSE | DE039 | CHAR (2) | Mastercard and Visa only. The response code that the network returned to the merchant during stand-in processing (STIP) |
SLI | VARCHAR2 (19) | Security level indicator | |
STIP STATUS | Galileo | VARCHAR2 (20) | Whether the STIP transaction was an approval or denial: STIP (transaction approved) or STIP Decline (transaction denied) |
DE121 | DE121 | CHAR (11) | Authorizing agent ID code. The actual processing facility that approved or declined an authorization request message |
DE38 | DE038 | Authorization identification response | |
DE60 | DE060 | VARCHAR2 (61) | Advice reason code. Indicates the specific reason for an advice. |
DE63_BRN | DE063 | VARCHAR2 (9) | Banknet reference number. Value received for Maestro updates |
DE63_IPM | DE063 | VARCHAR2 (16) | Mastercard only. Transaction lifecycle ID, which allows tracking throughout a transaction's lifecycle |
IAC_TAX | NUMBER | Mastercard only. Impuesto al consumo. Consumption tax. Colombia only. | |
IVA TAX | NUMBER | Mastercard only. Impuesto al valor agregado. Value-added tax. Colombia only. | |
POS CARDHOLDER PRESENCE | DE061 | CHAR(1) | Indicates whether the cardholder was present at the point of sale. See the DE061 Subelements enumeration for subfield 4. |
Posted Transactions RDF
The Posted Transactions RDF contains every transaction that has been posted to a customer account during a 24-hour period: settlements, payments, adjustments and fees. To match a settlement with its corresponding authorization, use the AUTHORIZATION CODE or SOURCE ID (which are identical for card transactions).
These types of transactions do not have corresponding entries in the Authorized Transactions RDF, because they do not originate in the authorization stream:
- Adjustments
- Most payments, except for Mastercard and Maestro loads (
PMML,PMMX) - Visa, Green Dot, and MoneyGram card loads
- Account transfers
- Mastercard Banknet merchant credits
- Most fees
- The authorization request amount from an ATM includes all fees.
- ATM fees that you assess are broken out separately in the Posted Transactions RDF. The
ORIGINAL AUTH CODEfor the fee contains theAUTHORIZATION CODEof the original ATM authorization. - About Fees provides additional information about fees.
- ACH transactions
- Direct deposit
- Bill pay
- Chargebacks, second presentments, provisional credit, and arbitration
- Charge offs
- Force-posted settlements
- Roundups
Post time for completions
The timestamp for a posted transaction usually corresponds to the time Galileo posts the transaction to the cardholder account. However, in the case of completions (otype: C), the completion time in the Posted Transactions RDF is the time of the completion, whereas the amount is settled later, after Galileo receives the batch settlement file.
For example, a cardholder gets a preauthorization at a gas pump for 75.00 at 09:00:01 and Galileo places a 75.00 hold on the account. The completion for 35.00 arrives through the authorization stream at 09:04:30. Galileo backs out the 75.00 and places a 35.00 hold on the account. The timestamp for the completion in the Authorized Transactions RDF and the Posted Transactions RDF is 09:04:30, but the actual settlement will be a day or so later. This later settlement is visible in the CST and Program API responses but not in the Posted Transactions RDF.
For an example of a completion at a gas pump see Five-step sequence in Card Transaction Examples and Scenario 2: Preauthorization with Completion.
Foreign settlements
When a foreign transaction is settled, TRANSACTION AMOUNT is in the currency of the account rather than the currency of the merchant. The currency conversion is performed by the card network, and it reflects the conversion rate at the time the transaction cleared, which may be a different rate than when the transaction was authorized.
See Foreign authorizations for more information.
Posted Transactions RDF fields
This table contains the default fields for the Posted Transactions RDF.
- The primary key for this file is
UNIQUE PROGRAM ID+AUTHORIZATION CODE+TRANSACTION CODE/TYPE. - The filename format is
fw_postedtransactions_yyyymmdd.csv. - The Source column indicates where the value came from: Galileo, a Nacha file, a private data subelement (PDS) from a network settlement file, or a data element (DE) in the ISO 8583 message in the authorization stream. The actual element/subelement number for a field may vary by network, and not all elements/subelements are present for all networks.
- "Signed" amounts have a
-sign for debits and no sign for credits:-30.25for a debit and23.67for a credit. - All
DATETIMEfields areYYYY-MM-DD h24:mm:ss. - All fields except for the primary-key fields can potentially return a null value.
- See Data Elements to correlate the DEs with other Galileo resources.
| Field | Source | Data type | Description |
|---|---|---|---|
UNIQUE PROGRAM ID | Galileo | NUMBER (18) | The identifier that Galileo has assigned your program |
GALILEO ACCOUNT ID | Galileo | NUMBER (18) | The balance ID, which is different from the account ID (PRN). It is possible for two or more accounts to share the same balance ID. |
CARD IDENTIFIER | Galileo | NUMBER (18) | Sometimes called the CAD, this identifier is given to each unique card that is issued to an account. If you are not PCI compliant, you can sometimes use this identifier instead of the PAN. |
TRANSACTION DATE/TIME | Galileo | DATETIME | The time that Galileo responded to the authorization request, or when a non-card transaction posted to the account |
TRANSACTION CODE/TYPE | Galileo | CHAR (6) | The concatenation of the activity type (act_type) and the transaction type (otype). For an explanation of transaction codes see Classifying transactions in the About Transactions guide. |
TRANSACTION AMOUNT | Galileo | NUMBER (9,2) | Signed. The amount that was posted to the account, in the currency of the account. In the case of a settlement, the amount may be different from its corresponding authorization amount. |
TRANSACTION CURRENCY CODE | DE049 | CHAR (3) | The currency code at the point of sale, for card transactions |
AUTHORIZATION CODE | Galileo | NUMBER (18) | A Galileo-generated identifier (auth_id). See Transaction IDs for information about unique identifiers and ID collisions. |
POST DATE | Galileo | DATETIME | The date when Galileo posted the transaction to the customer account |
NETWORK CODE | Galileo | VARCHAR2 (1) | A single letter to specify the subnetwork over which the authorization request arrived. This field is populated only for card transactions. See Network Codes for possible values. |
MERCHANT NUMBER | DE042 | CHAR (25) | An identifier that each network assigns to a merchant location. For non-card transactions, this may be a location ID provided by you or Galileo. |
MERCHANT DESCRIPTION | DE043 | CHAR (40) | Descriptive text provided by the merchant to identify the merchant name and location and sometimes the transaction type |
MERCHANT CATEGORY CODE | DE018 | NUMBER (18) | The ISO 18245 four-digit identifier that specifies the type of merchant, such as ATM, gas pump, bookseller, airline, hotel, and so on |
MERCHANT COUNTRY CODE | DE049 | VARCHAR2 (3) | The ISO 3166-1 three-digit code for the country where the merchant is officially located |
INTERCHANGE FEE AMOUNT | NUMBER (9,2) | Signed. The amount of interchange or fee associated with this transaction. ATM fees and interchange reversals are negative; interchange earned is positive. This field is populated only for card transactions. This value is rounded to 2 decimal places; for the full amount without rounding, see IC FEE AMOUNT. | |
EXTERNAL TRANSACTION ID | DE038 | VARCHAR2 (60) | Populated according to the source of the transaction: transactionId of the API request. trans_id |
PRN | Galileo | CHAR (12) | The payment reference number, a unique Galileo-generated identifier for an account |
PRODUCT ID | Galileo | NUMBER (18) | Identifier for the account's product in the Galileo system |
SETTLE DTL ID | Galileo | NUMBER (18) | The identifier for the transaction in the settlement table. This field is populated only for card transactions. |
ACTUAL POST DATE | Galileo | DATETIME | Date-time when the ACH credit was posted to the account |
ORIGINAL SETTLEMENT DATE | Nacha file | DATETIME | Date-time when the ACH credit was originally to be posted |
ACH_RETURN CODE | Nacha file | CHAR (3) | Code that indicates why an ACH request was returned |
ACQUIRING ID | DE032 | VARCHAR2 (11) | Identifier for the merchant's acquirer |
ACTIVITY ID | Galileo | NUMBER | Identifier for the transaction in the activity table, which includes anything that affects the available balance; ID is unique per core |
AGENT NAME | Galileo | VARCHAR2 (125) | Name of the CST agent that initiated the transaction |
ARN | VARCHAR2 (23) | Mastercard only. Acquirer reference number | |
ASSOCIATION | Galileo | CHAR (3) | Three-letter abbreviation for the card network: DEB (Maestro)MAS (Mastercard credit)STA (STAR)VIS (Visa)DIS (Discover)ALL (Allpoint)PLS (Pulse) |
ASSOCIATION TRANS ID | DE062 | VARCHAR2 (15) | Visa only. Transaction identifier that was assigned by the network |
AUTH AMOUNT | NUMBER (9,2) | Amount that was authorized. | |
AUTHORIZATION RESPONSE | DE039 | CHAR (2) | The Response code that was returned to the merchant |
AUTHORIZATION STATUS | Galileo | CHAR (1) | Status of the authorization |
AUTH TRANSACTION CODE | Galileo | VARCHAR2 (10) | Transaction code for the original authorization |
BAI | DE104 | VARCHAR2 (3) | Visa only. Business Application Identifier, which identifies the type of Visa card load |
BATCH HEADER | Nacha file | VARCHAR2 (94) | Record header for incoming ACH credits |
BILLING AMOUNT | DE006 | NUMBER (16,4) | Signed. Transaction amount in the billing currency, which is the cardholder account currency |
BILLING CURRENCY CODE | DE051 | CHAR (3) | Currency code for BILLING AMOUNT |
CASE SENSITIVE FLAG | Galileo | VARCHAR2 (1) | Which case the otype in TRANSACTION CODE/TYPE is: Y (uppercase) , N (lowercase) or blank (not specified) |
CASHBACK AMOUNT | DE054 | NUMBER (9,2) | Original cashback request amount |
CCA FEE | DE111 | NUMBER (9,2) | Currency conversion assessment (CCA) fee |
CCA FEE AMOUNT SETTLE | DE111 | NUMBER (9,2) | Amount of the settlement on which the CCA fee was assessed |
CCA FEE CURRENCY CODE | DE111 | VARCHAR2 (3) | Currency code for CCA FEE |
CCA FEE SIGN | Galileo | VARCHAR2 (1) | CCA fee sign: C (credit) or D (debit) |
CHECK NUMBER | Galileo | NUMBER (19) | Number of the check |
COMPANY DESC | Nacha file | VARCHAR2 (15) | Company description for incoming ACH transactions |
COMPANY NAME | Nacha file | VARCHAR2 (20) | Company name for incoming ACH transactions |
CONTACTLESS TRANSACTIONS | DE022 | CHAR (1) | Whether the card was authenticated using contactless technology: Y (contactless) or N (not contactless) |
CONVERSION RATE - BILLING | DE010 | VARCHAR2 (9) | The currency conversion rate when converting TRANSACTION AMOUNT to BILLING AMOUNT. See Conversion rates to interpret the value. |
CONVERSION RATE - RECON | DE009 | VARCHAR2 (8) | The currency conversion rate when converting TRANSACTION AMOUNT to SETTLE AMOUNT. See Conversion rates to interpret the value. |
CARD PRESENT INDICATOR | DE061 | CHAR (1) | Whether it was a card-present transaction: Y (card present) or N (card not present) |
CREDIT INDICATOR | Galileo | CHAR (1) | Whether the transaction arrived at Galileo over credit rails: Y (credit rails) or N (debit rails) |
SETTLEMENT DATE | DE015 | CHAR (4) | Date on which the card transaction settled |
DE28 | DE028 | VARCHAR2 (9) | Local surcharge amount, a fee applied to the ATM withdrawal amount by the ATM's operator |
RETRIEVAL REF NUMBER | DE037 | VARCHAR2 (12) | Retrieval reference number |
REASON CODES | DE044 | VARCHAR2 (25) | Visa only. Full contents of DE044, miscellaneous response data. |
AVS CHECK | DE063 | VARCHAR2 (50) | Data used to perform the AVS check. |
ICA | VARCHAR2 (6) | Mastercard only. Interbank Card Association number, assigned by Mastercard to a financial institution | |
DRAWDOWN SETTLEMENT DATE | DATETIME | Date-time when the amount was taken from the issuing bank account | |
EFFECTIVE_DT | Nacha file | DATETIME | For an ACH transaction, the effective date-time when the ACH payment should post. Usually set by the bank to the actual date when the payment is posted, in case Galileo posts the payment early. |
EXPIRED AUTH CODE | Galileo | NUMBER (10) | AUTHORIZATION CODE of the expired authorization that corresponds to this force-posted transaction. This field is populated only for card transactions. |
FEE PROGRAM INDICATOR | DE063SE19 | VARCHAR2 (3) | Visa only. Interchange reimbursement fee program indicator, applied to select ATM transactions |
FULL MERCHANT DESCRIPTION | DE043 | VARCHAR2 | Merchant name and full address and store number if provided by the network at the time of settlement |
GROUP_ID | NUMBER | Identifier of the store where the card was sold | |
IC FEE AMOUNT | FLOAT (12,6) | Signed. Interchange fee amount | |
INTERCHANGE RATE DESIGNATOR | PDS0158 | VARCHAR2 (2 bytes) | The interchange rate and editing rules applied to the transaction |
IS AFT TRANSACTION | DE003 | CHAR (1) | Whether this is an account funding transaction: Y (AFT) or N (not AFT) |
FOREIGN TRANSACTION INDICATOR | Galileo | CHAR (1) | Whether the transaction is foreign: Y (foreign) or N or null (domestic). This field is populated only for card transactions. |
OTC FLAG | DE003 | CHAR (1) | Whether the transaction included an over-the-counter cash advance: Y (cash advance) or N (no cash advance) |
PIN FLAG | Galileo | CHAR (1) | Whether a PIN was used to authenticate the card: Y (PIN) or N (no PIN) |
LOAD DT | Nacha file | DATETIME | ACH load date (the date-time the incoming ACH was loaded) |
LOCAL AMOUNT | DE004 | NUMBER (16,4) | Signed. Transaction amount in the currency at the point of sale |
LOCAL CURRENCY CODE | DE049 | CHAR (3) | Currency code for LOCAL AMOUNT |
LOCAL SURCHARGE AMOUNT | DE028 | NUMBER (16,4) | Signed. Local surcharge amount, a fee applied to the ATM withdrawal amount by the ATM's operator |
MC SEQ COUNT | Galileo | NUMBER (2) | Multi-clearing sequence count. Total number of clearings in this sequence |
MC SEQ NUMBER | Galileo | NUMBER (2) | Multi-clearing sequence number. Position of this clearing in the sequence |
MOBILE TRANSACTIONS | Galileo | CHAR (1) | Whether a token requestor ID was present: Y (mobile wallet) or N (not mobile wallet) |
MESSAGE TYPE FLAG | Galileo | CHAR (2) | The otype of the authorization |
NAME | Nacha file | VARCHAR2 | Payee name for direct deposit |
FILE ID | Settlement file | VARCHAR2 (25) | Visa only. Identifier for the settlement file |
CHECK NUMBER | VARCHAR2 | Number of the remote-deposit check | |
ROLLING BALANCE | Galileo | NUMBER (9,2) | Signed. Available balance after this transaction took place. If you are the system of record instead of Galileo, this value may be inaccurate. |
ORIGINAL AUTH CODE | Galileo | NUMBER | Source ID for the transaction that this fee was assessed to |
SECURE CODE | PDS0052 | VARCHAR2 | Mastercard only. Secure code from the settlement file |
POS CONDITION CODE | DE025 | VARCHAR2 (2) | Visa and Discover only. The conditions under which the transaction took place at the point of sale. See DE025 Codes. |
POS ENTRY MODE | DE022 | NUMBER (2) | First two characters of DE022. How the PAN was entered, such as magnetic stripe, EMV chip, or contactless. See DE022 Codes. |
POS TYPE | Galileo | CHAR (3) | Type of card authentication at the point of sale: SIG (signature) or PIN |
POSTED EARLY | Nacha file | CHAR (1) | Whether the ACH transaction was posted early: Y (early) or N (not early) |
ENDORSEMENT DATE | DATETIME | Presentment date-time for a paper check | |
PRIOR ID | Galileo | NUMBER (12) | The ID of a transaction that is linked to this transaction, such as the transaction that triggered this transaction |
RECURRING TRANSACTION FLAG | DE061 | CHAR (1) | Whether this is a recurring transaction: Y (recurring) or N (not recurring) |
RECEIVE DT | Nacha file | DATETIME | Date-time that the Nacha file arrived at Galileo |
CHECK RETURN CODE | CHAR (3) | Code to indicate why a paper check was returned | |
REVERSAL ID | Galileo | NUMBER | Contains the authorization ID of the previous authorization in a series, such as with incremental authorizations, reversals, and completions. This field is populated only for card transactions. See Linking transactions in the About Transactions guide for more information. |
SETTLE AMOUNT | DE005 | NUMBER (16,4) | Signed. Transaction amount in the settlement currency |
SETTLE CURRENCY CODE | DE050 | CHAR (3) | Currency code for SETTLEMENT AMOUNT |
SETTLE SERVICE ID | PDS0159SE4 | CHAR (10) | Uniquely identifies the settlement service |
SETTLE DATE | Galileo | DATETIME | Date and time when the transaction was settled |
SETTLEMENT SERVICE CODE | PDS0159SE3 | CHAR (1) | Identifies the settlement level of the settlement service: 1 or (regional) 3 (intracurrency) |
SETTLEMENT SERVICE ID | PDS0159SE4 | CHAR (10) | Duplicate of SETTLE SERVICE ID |
SLI INDICATOR | DE048SE42 | CHAR (1) | Security level indicator, included with Mastercard 3DS authentication |
SOURCE | Galileo | NUMBER (1) | Number assigned to a network: 1 (Maestro)2 (Mastercard credit)3 (STAR)4 (Visa)5 (Discover)6 (Allpoint)7 (Pulse) |
SOURCE ID | Galileo | NUMBER (18) | A Galileo-generated identifier (source_id) that maps back to the original transaction table. See Transaction IDs for information about unique identifiers and ID collisions. |
SOURCE INSTITUTION NAME | Nacha file | VARCHAR2 (35 bytes) | The source institution name for the incoming ACH credit |
SOURCE TRACE | Nacha file | VARCHAR2 (20 bytes) | ACH trace number assigned by the external originating institution to incoming ACH transactions |
SOURCE TYPE | CHAR (1) | Type of source: S (settlements) or A (activity) | |
TERMINAL ID | DE041 | CHAR (8) | Identifier of the card reader |
TERMINAL NETWORK | Galileo | VARCHAR2 (50) | Network that the card reader used |
TOKEN REQUESTOR ID | VARCHAR2 (11) | Identifier for the token requestor. Present for mobile-wallet transactions. See the Lookup file for valid values. | |
TRACE NUMBER | DE011 Nacha file | VARCHAR2 (20) | For a card transaction, an identifier from the merchant that is included with all messages related to that transaction. For an ACH transaction the trace number assigned by Galileo to outgoing ACH transactions. |
TRANSACTION DESCRIPTION | VARCHAR2 (100) | Description for certain transactions | |
XID | Galileo | NUMBER | Internal account identifier; has a 1:1 relationship with the PRN |
LOADS AUTH ID | Galileo | NUMBER | Original authorization ID for card loads |
PDS1002 | PDS1002 | VARCHAR2 (13) | Custom field |
PDS1018 | PDS1018 | VARCHAR2 (13) | Custom field |
REASON CODES | DE041SE1 | VARCHAR2 (1) | Visa only. First character only of DE044. The response source/reason code that identifies the source of the DE039 response. |
ACH TRACE NUMBER | VARCHAR2 (20) | ACH trace number | |
IVA TAX | NUMBER | Mastercard only. Impuesto al valor agregado. Value-added tax. Colombia only. | |
IAC_TAX | NUMBER | Mastercard only. Impuesto al consumo. Consumption tax. Colombia only. | |
IPM AUTH CODE | Galileo | CHAR (6) | Numeric tag generated by Galileo during authorization, which is sent in the response, and then is returned in the settlement batch file to help link authorizations with settlements |
MSG REASON CODE | DE025 | CHAR (4) | Mastercard only. Indicates why a message was sent. Consult the Mastercard IPM Clearing Formats document to interpret the code. |
MERCHANT POSTAL CODE | VARCHAR2 (5) | Postal code for the merchant's location. | |
CONVERSION RATE SETTLEMENT | DE009 | VARCHAR2 (8) | Visa only. The currency conversion rate when converting TRANSACTION AMOUNT to SETTLE AMOUNT. See Conversion rates to interpret the value. |
CONVERSION RATE | DE010 | VARCHAR2 (8) | Visa only. The currency conversion rate when converting TRANSACTION AMOUNT to BILLING AMOUNT. See Conversion rates to interpret the value. |
TRANSACTION LIFECYCLE ID | DE063 | VARCHAR2 (16) | Mastercard only. Transaction lifecycle identifier, which allows tracking throughout a transaction's lifecycle |
TRANSACTION LIFECYCLE ID/ ASSOCIATION TRANSACTION ID FOR ALL NETWORKS | VARCHAR2 (15) | Transaction lifecycle ID/ association transaction ID for all networks | |
PDS0185 | PDS0185 | VARCHAR2 (33) | Accountholder authentication value (AAV) (Mastercard) or cardholder authentication verification value (CAAV) (Visa), returned for 3DS transactions |
PIN ENTRY CAPABILITY | DE022 | CHAR (1) | Indicates methods available at the terminal for PIN entry. See PIN entry capability. |
DE60 | DE060 | VARCHAR2 (61) | Advice reason code. Indicates the specific reason for an advice. |
DE121 | DE121 | CHAR (11) | Authorizing agent ID code, the actual processing facility that approved or declined an authorization request message |
DE63_BRN | DE063 | VARCHAR2 (9) | Banknet reference number; value received for Maestro updates |
PDS0180 | PDS 0180 | VARCHAR2 (20) | Domestic card acceptor tax ID. U.S. tax ID of the card acceptor |
Conversion rates
The conversion rate fields have the pattern <shift digit><exchange rate digits>. The first digit specifies how many places from the right the decimal is located. For example, 69985022 translates to an exchange rate of 9.985022%.
Lookup file
Use the Lookup file to interpret the codes in some of the RDF fields. The Lookup file for a particular day contains only those values that are included with the other RDFs on that day, so this file will include some values on one day that it does not include on other days.
The DESCRIPTION field contains a short description rather than a long description, because the specific meaning for some fields is different for each Galileo client according to their setup.
The primary key for this table is FILE TYPE + FIELD NAME + CODE. There is no foreign key to link this file to the other RDFs. All fields are strings. All fields except for the primary-key fields can potentially return a null value.
| Field name | Length | Description |
|---|---|---|
CODE | 10 | Possible values for the field |
DESCRIPTION | 50 | Description of the CODE value |
FIELD NAME | 30 | Name of the field in the RDF |
FILE TYPE | 1 | The RDF that contains the field:A (Authorized Transactions)C (Customer Master)D (Account Card)P (Posted Transactions) |
Updated 23 days ago