Database – Loan Transactions (loan_tx)
Description
This table holds a record of all transactions that are currently applying to a loan. If a transaction has been reversed or deleted, it will not appear in this table. Records from this table are deleted completely when no longer needed (we refer to this as a hard delete). Records that were hard deleted are show in the deletion_audit table. This table is used to build the transactions report in each loan. Because of this, it will show scheduled payments and forecast payments in addition to transactions that have been logged on the loan.
Common Uses
This table is commonly used to find:
- Information about transactions that currently apply to a loan
- Current loan amortization schedule
Column |
Data Type |
Column Info |
Value Notes |
Other |
id |
int(11) |
The ID of the transaction. This will be the actual ID for logged transactions, or an incremented ID (unique to this table) for scheduled and forecast payments. Scheduled and forecast payments supply information about the amortization of the loan and make the appropriate adjustments to future principal and interest, but are not transactions that were actually logged on the loan. |
||
entity_id |
int(11) |
The ID of the entity the transaction is associated with. |
||
entity_type |
varchar(100) |
The type of entity the transaction is associated with. This field should always equal Entity.Loan, because transactions should be associated with loans. |
||
tx_id |
char(40) |
This is the unique identifier for a transaction used within this table. This is a concatenation of the entity_id, the mod_id, the type, and the period. If it is a logged transaction, instead of using the payment period, it will concatenate a transaction ID assigned for this table, with a 0 at the end. It will look something like this: 1-0-fpm5. This transaction is from loan 1, mod 0. It is a forecast payment in payment period number 5. |
||
mod_id |
int(11) |
When a loan is modified, the user can choose whether transactions will persist on the loan after the modification. This ID shows the modification that a transaction is associated with. Modifications can be found in the loan_modification_entity table. |
||
date |
datetime |
This is the date the transaction applies to the loan. |
||
period |
int(11) |
This is the payment period in which the transaction applies. This is an integer value. Payment periods are number starting with 0. |
||
period_start |
date |
This is the start date for the payment period in which the transaction applies. |
||
period_end |
date |
This is the end date for the period in which the transaction applies. |
||
title |
varchar(255) |
This is a human readable title for scheduled and forecast payments. For logged transactions, this title was added by the user. |
||
type |
char(20) |
This is the type of transaction. The options are origination, scheduledPayment, forecastPayment, payment, credit, advancement, fee, suspendInterest, resumeInterest, apdAdjustment, and dpdAdjustment |
|
|
info_only |
tinyint(1) |
This shows whether a transaction is information-only. The following types of transaction are considered info-only: origination, apdAdjustment, dpdAdjustment, suspendInterest, resumeInterest |
1 - yes, 0 - no |
|
info_details |
varchar(255) |
If there is additional information associated with an info-only transaction, this field with hold a JSON object containing that information. |
||
payment_id |
bigint(21) |
This is an ID that is used in this table to differentiate logged transactions of the same type, that occur in the same period. This ID will be present for all logged transactions, not just payments. |
||
payment_display_id |
bigint(21) |
This value is only relevant to payments, and is the ID of the payment that is shown to the user in the software user interface. |
||
payment_amount |
decimal(10,2) |
This field is relevant to transactions with a type of payment, forecastPayment, advancement, or credit. This is the amount of the transaction. For advancement transactions, this value with be negative. |
||
payment_i |
decimal(10,2) |
This field is relevant to transactions with a type of payment, forecastPayment, or credit. This is the amount of the transaction that applied to interest on the loan. |
||
payment_p |
decimal(10,2) |
This field is relevant to transactions with a type of payment, forecastPayment, advancement, or credit. This is the amount of the transaction that applied to principal on the loan. For advancement transactions, this value will be negative. |
||
payment_d |
decimal(10,2) |
This field is relevant to transactions with a type of payment, forecastPayment, advancement, or credit. This is the amount of the transaction that applied to discount on the loan. For advancement transactions, this value will be negative. |
||
payment_f |
decimal(10,2) |
This field is relevant to transactions with a type of payment, forecastPayment, or credit. This is the amount of the transaction that applied to fees on the loan. |
||
payment_e |
decimal(10,2) |
This field is relevant to transactions with a type of payment, forecastPayment, or credit. This is the amount of the transaction that applied to escrow on the loan. |
||
payment_e_breakdown |
varchar(2000) |
If a transaction applied to escrow, this field will hold a JSON object showing the IDs of the escrow buckets it applied to, and the amounts that applied to each bucket. Escrow buckets can be found in the escrow_subset_entity table. |
||
fees_paid_details |
varchar(2000) |
If a transaction applied to fees, this field will hold a JSON object showing the IDs of the fees it applied to, and the amounts applied to each fee. Fees can be found in the charge_entity table. |
||
charge_amount |
decimal(10,2) |
This field applies to transactions with a type of fee or scheduledPayment. This is the amount of the transaction. |
||
charge_i |
decimal(10,2) |
This field applies to transactions with a type of scheduledPayment. This is the interest portion of the payment. Scheduled payments are not logged, so this will only show the amount scheduled for the payment, and not anything that actually applied to the loan. |
||
charge_p |
decimal(10,2) |
This field applies to transactions with a type of scheduledPayment. This is the principal portion of the payment. Scheduled payments are not logged, so this will only show the amount scheduled for the payment, and not anything that actually applied to the loan. |
||
charge_d |
decimal(10,2) |
This field applies to transactions with a type of scheduledPayment. This is the discount portion of the payment. Scheduled payments are not logged, so this will only show the amount scheduled for the payment, and not anything that actually applied to the loan. |
||
charge_f |
decimal(10,2) |
This field applies to transactions with a type of fee or scheduledPayment. This is the fees portion of the payment or fee. Scheduled payments are not logged, so this will only show the amount scheduled for the payment, and not anything that actually applied to the loan. |
||
charge_e |
decimal(10,2) |
This field applies to transactions with a type of scheduledPayment. This is the escrow portion of the payment. Scheduled payments are not logged, so this will only show the amount scheduled for the payment, and not anything that actually applied to the loan. |
||
charge_e_breakdown |
varchar(2000) |
If a portion of a scheduled payment applied to escrow, this field will hold a JSON object showing the IDs of the escrow buckets it applied to, and the amounts that applied to each bucket. Escrow buckets can be found in the escrow_subset_entity table. |
||
future |
tinyint(4) |
This value shows whether the transaction will apply to the loan in the future, meaning after today. This value gets updated with daily maintenance to ensure it is correct. |
1 - yes, 0 - no |
|
principalonly |
tinyint(4) |
This value shows whether the transaction will apply to principal only. This is only relevant to payment, scheduled payment, forecast payment, and credit transactions. |
1 - yes, 0 - no |
|
advancement |
tinyint(1) |
This value shows whether the transaction is an advancement. This should only show 1 for transactions with a type of advancement. |
1 - yes, 0 - no |
|
payoff_fee |
tinyint(4) |
This value shows whether the transaction is a payoff fee. Payoff fees don't come due on the loan, but must be paid in order to pay off the loan. This is only relevant to transactions with a type of fee. |
1 - yes, 0 - no |
|
charge_off |
tinyint(4) |
This value shows whether the transaction has been flagged as a charge off. A charge off transaction is like an accounting write down or write off of the loan. It is meant to lower the amount owed on the loan, when some portion of the loan is deemed uncollectible. This is only relevant to transactions with the type of payment or credit. |
1 - yes, 0 - no |
|
payment_type |
int(11) |
The ID of the payment type. Payment type specifies the waterfall application of payments. Payment types can be found in the custom_payment_type_entity table. |
||
adb_days |
int(11) |
This is the number of days used to calculate the average daily loan balance for a payment period in which the transaction occurred. This should equal the number of days in the payment period. This is not relevant to info_only transactions. |
||
adb |
decimal(10,2) |
This is the average principal balance of the loan during the payment period in which the transaction occurred. This is not relevant to info_only transactions. |
||
principal_balance |
decimal(10,2) |
This is the loan's principal balance after the transaction applies. This is not relevant to info_only transactions. |
||
displayorder |
decimal(10,2) |
This is a numeric value that shows the order in which transactions that occur on the same date should be shown in the transactions report. |
||
deleted |
tinyint(4) |
This shows whether the entity has been deleted. This table does not use this field, but rather completely deletes records when they aren't needed. |
1 - yes, 0 - no |
|
lastUpdated |
timestamp |
Timestamp of when the record was last updated. |