Database Payment Information
General
Some customers choose the optional feature of access to a read-only database replica. This is usually a good choice for large companies that need to pull reports on large data sets, because data can be pulled more quickly from a database than through the API. This article will cover some of the basics of pulling payment data through the database.
Tables
There are two primary tables where payment data can be found. The 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 detalis | ||
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) | whether or not the payment applies ot the future | 1 - yes, 0 - no | |
principalonly | tinyint(4) | whether or not the payment is principal only | 1 - yes, 0 - no | |
advancement | tinyint(1) | transaction is an advancment | 1 - yes, 0 - no | |
payoff_fee | tinyint(4) | transaction is a payoff fee | 1 - yes, 0 - no | |
charge_off | tinyint(4) | whether or not 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) | Whether or not 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) | Whether or not payment is early | 1 - yes, 0 - no | |
payoff_flag | tinyint(1) | Whether or not is a payoff payment | 1 - yes, 0 - no | |
payoff_diff_apply | char(40) | Whether or not should payoff difference 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 paymen tid | ||
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 transactoin 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) | dpd 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) | apd after payment | ||
after_days_past_due | int(11) | 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) | Whether or not to 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) | Whether or not the entity is active | 1 - yes, 0 - no | |
deleted | tinyint(1) | Whether or not 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) | Whether or not is 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.
Note: if you are calculating your loans using a between-transactions interest application and your extra towards setting is next, you may get multiple payments with the same ID. If this is the case, merge the payments by ID in order to view the amounts as originally logged.
Pull All Payments
If you want to pull all payment data, we recommend something like 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
);