Database Payment Information
Introduction
This article will cover some of the basics of pulling payment data through the database. For a comprehensive list of all database tables, see our Database Tables article.
Tables
There are two primary tables where payment data can be found; these two tables are named loan_tx and payment_entity.
loan_tx
The loan_tx table holds all loan transactions. Some information is present in the table for the loan that a transaction is related to. This table consists of the following fields:
Column | Data Type | Column Info | Value Notes | Other |
id | int(11) | The ID of the entity | ||
entity_id | int(11) | ID of the associated entity | ||
entity_type | varchar(100) | Type of the associated entity | ||
tx_id | char(40) | Transaction ID | ||
mod_id | int(11) | The ID of the associated loan modification | ||
date | datetime | Transaction date | ||
period | int(11) | Transaction period | ||
period_start | date | Transaction period start | ||
period_end | date | Transaction period end | ||
title | varchar(255) | The name/title of the entity instance | ||
type | char(20) | Transaction type | ||
info_only | tinyint(1) | Transaction info | 1 - yes, 0 - no | |
info_details | varchar(255) | Transaction details | ||
payment_id | bigint(21) | Associated payment ID | ||
payment_display_id | bigint(21) | Payment display ID | ||
payment_amount | decimal(10,2) | Payment amount | ||
payment_i | decimal(10,2) | Payment interest amount | ||
payment_p | decimal(10,2) | Payment principal amount | ||
payment_d | decimal(10,2) | Payment discount amount | ||
payment_f | decimal(10,2) | Fees paid | ||
payment_e | decimal(10,2) | Payment escrow amount | ||
payment_e_breakdown | varchar(2000) | Payment escrow breakdown | ||
fees_paid_details | varchar(2000) | Payment fees paid details | ||
charge_amount | decimal(10,2) | Amount charge | ||
charge_i | decimal(10,2) | Charge interest | ||
charge_p | decimal(10,2) | Charge principal | ||
charge_d | decimal(10,2) | Charge discount | ||
charge_f | decimal(10,2) | Charge fees | ||
charge_e | decimal(10,2) | Charge escrow | ||
charge_e_breakdown | varchar(2000) | Charge escrow breakdown | ||
future | tinyint(4) | States if the payment applies to the future | 1 - yes, 0 - no | |
principalonly | tinyint(4) | States if the payment is principal only | 1 - yes, 0 - no | |
advancement | tinyint(1) | Transaction is an advancement | 1 - yes, 0 - no | |
payoff_fee | tinyint(4) | Transaction is a payoff fee | 1 - yes, 0 - no | |
charge_off | tinyint(4) | States if the payment is a charge off | 1 - yes, 0 - no | |
payment_type | int(11) | Payment type ID | ||
adb_days | int(11) | Average Daily Balance Days | ||
adb | decimal(10,2) | Average Daily Balance | ||
principal_balance | decimal(10,2) | Principal balance | ||
displayorder | decimal(10,2) | Display order | ||
deleted | tinyint(4) | States if the entity was deleted | 1 - yes, 0 - no | |
lastUpdated | timestamp | Timestamp of when the entity was last updated |
payment_entity
The payment_entity table only holds data about payment transactions. This table consists of the following fields:
Data Type | Data Type | Column Info | Value Notes | Other |
id | int(11) | The ID of the entity | ||
display_id | int(11) | Payment display ID | ||
payment_info_id | int(11) | Payment info ID | ||
payment_type_id | int(11) | Payment type ID | ||
payment_processor_id | int(11) | Payment processor ID | ||
payment_method_id | int(11) | Payment method ID | ||
payment_account_id | int(11) | Payment account ID | ||
tx_snapshot_id | int(11) | Transaction snapshot ID | ||
amount | decimal(11,2) | Payment amount | ||
apply_date | date | Date of application | ||
early | tinyint(1) | States whether this payment is early | 1 - yes, 0 - no | |
payoff_flag | tinyint(1) | States whether this is a payoff payment | 1 - yes, 0 - no | |
payoff_diff_apply | char(40) | Determines whether payoff difference should be applied | ||
payoff_options | varchar(500) | Payoff options | ||
custom_application | varchar(500) | custom application | ||
info | varchar(255) | Extra information about an entity instance | ||
extra | varchar(100) | Payment extra application | ||
parent | int(11) | Parent payment ID | ||
child | int(11) | Child payment ID | ||
charge_fee_type | varchar(20) | Servicing fee type | loan.cardfees.type.* | |
charge_fee_amount | decimal(11,2) | Servicing fee amount | ||
charge_fee_percentage | decimal(11,2) | Servicing fee percentage | ||
echeck_auth_type | varchar(100) | E-Check authorization type | payment.echeckauth.* | |
cash_drawer_id | int(11) | Cash drawer ID | ||
cash_drawer_tx_id | int(11) | Cash drawer transaction ID | ||
status | varchar(45) | Status code of the entity | ||
reverse_reason | varchar(45) | Payment reverse reason | ||
reverse_date | date | Payment reverse date | ||
comments | varchar(250) | Payment comments | ||
source_company | int(11) | Associated source company ID | ||
loan_status | int(11) | Associated loan status ID | ||
loan_substatus | int(11) | Associated loan sub status ID | ||
before_principal_balance | decimal(11,2) | Principal balance before payment | ||
before_payoff | decimal(11,2) | Payoff before payment | ||
before_next_due_date | date | Next due date before payment | ||
before_next_due_amount | decimal(11,2) | Next due amount before payment | ||
before_amount_past_due | decimal(11,2) | Amount past due on the loan before the payment was applied | ||
before_days_past_due | int(11) | Days past due before payment | ||
after_principal_balance | decimal(11,2) | Principal balance after payment | ||
after_payoff | decimal(11,2) | Payoff after payment | ||
after_next_due_date | date | Next due date after payment | ||
after_next_due_amount | decimal(11,2) | Next due amount after payment | ||
after_amount_past_due | decimal(11,2) | Amount past due (APD) after payment | ||
after_days_past_due | int(11) | Days past due (DPD) after payment | ||
system_comments | text | System comments | ||
charge_off_recovery | tinyint(1) | Charge-off recovery | 1 - yes, 0 - no | |
reset_past_due | tinyint(1) | States if reset days past due | 1 - yes, 0 - no | |
apd_adjustment_id | int(11) | Associated APD adjustment ID | ||
dpd_adjustment_id | int(11) | Associated DPD adjustment ID | ||
active | tinyint(1) | States if the entity is active | 1 - yes, 0 - no | |
deleted | tinyint(1) | States if the entity was deleted | 1 - yes, 0 - no | |
created | timestamp | Timestamp of when the entity was created | ||
lastUpdated | timestamp | Timestamp of when the entity was last updated | ||
mod_id | int(11) | The ID of the associated loan modification | ||
entity_id | int(11) | ID of the associated entity | ||
entity_type | varchar(100) | Type of the associated entity | ||
payment_method_option | varchar(45) | Payment method option | ||
payment_method_name | varchar(255) | Payment method name | ||
is_split | tinyint(1) | States if there is a payment split | 1 - yes, 0 - no | |
split_id | int(11) | Split payment ID | ||
nacha_return_code | varchar(255) | NACHA Return Code | ||
autopay_id | int(11) | Associated AutoPay ID | ||
last_extra | varchar(100) | Encoded information about the last extra amount |
Queries
There are several queries that customers use to pull payment data. The following examples will help you understand how to use these tables.
Pull All Payments
If you want to pull all payment data, we recommend a query similar to the following:
SELECT t.* from loan_tx t, payment_entity p
WHERE t.type = 'payment'
AND t.payment_id = p.id
;
It is important to note that the payment ID, as it appears in the software, can be found in the payment_id field of the loan_tx table and the ID field of the payment_entity table.
Pull Payments Logged Over a Date Range
SELECT t.* from loan_tx t, payment_entity p
WHERE t.type = 'payment'
AND t.payment_id = p.id
AND (date(t.date) between '2018-08-01' and '2018-08-31')
;
Pull Payments With IDs that Fall Into a Range
SELECT t.* from loan_tx t, payment_entity p
WHERE t.type = 'payment'
AND t.payment_id = p.id
AND (payment_id between 1 and 35)
;