Database – Loan Status Archive (loan_status_archive)
Description
This table holds the daily record of a loan's status, numbers, and other information that changes often. The snapshot will show the information as it was the last time a loan was changed on a given day.
Common Uses
This table is commonly used to find:
- Principal balance
- Loan status
- Amount due
- Last payment date
- Payoff amount
- Assigned portfolios
It is important to remember that each record in this table is for a specific date, so it's important to base your query on a date to make sure you get the correct numbers.
Column | Data Type | Column Info | Value Notes | Other |
loan_id | int(11) | The ID of the associated loan account. Loans can be found in the loan_entity table. | ||
date | date | The date the record is for. Records are updated the last time a loan is changed on this date. | ||
followup_date | date | This is the follow-up date on the loan. This is the date when a service should next follow up with a customer. | ||
amount_due | decimal(11,2) | This is the amount due on the loan, meaning the amount that has come due, but not yet been paid. Because the due_interest includes any interest accrued since the most-recent payment came due, adding up the due components may give a different number than the amount_due. | ||
due_interest | decimal(11,2) | This field shows accrued, but unpaid interest. This is interest that has come due as part of a payment, but has yet to be paid, and any interest accrued since the most-recent payment came due. | ||
due_principal | decimal(11,2) | This is the amount or principal due on the loan, meaning the amount that has come due, but not yet been paid. | ||
due_discount | decimal(11,2) | This is the amount of discount due on the loan, meaning the amount that has come due, but not yet been paid. | ||
due_escrow | decimal(11,2) | This is the amount of escrow due on the loan, meaning the amount that has come due, but not yet been paid. | ||
due_escrow_breakdown | varchar(255) | This field holds a JSON object that shows the IDs of the escrow buckets for which there are amounts due, and the amounts. Escrow buckets can be found in the escrow_subset_entity table. | ||
due_fees | decimal(11,2) | This is the amount of fees due on the loan, meaning the amount of fees that have come due, but have not been paid. Whether a fee has come due will depend somewhat on settings. Payoff fees will not be included in this number. | ||
due_pni | decimal(11,2) | This amount equals the due_interest + due_principal. Because due_interest includes any interest accrued since the most-recent payment came due, this may be higher than the amount_due. | ||
payoff_fees | decimal(11,2) | This is the amount of payoff fees. Payoff fees don't come due on the loan, but must be paid in order to pay off the loan. | ||
delinquent_bucket | tinyint(3) | This is the highest delinquency bucket that loan is in. Delinquency buckets show how many payments have been missed on the loan. | ||
date_of_first_delinquecy | date | This is the first date that the loan was delinquent, leading to the current delinquency. | ||
delinquent_bucket_balance | decimal(11,2) | This field holds a JSON object that gives a breakdown of the delinquency buckets on the loan. This includes the following information for each bucket: the bucket number, the date the payment came due for the bucket, the amount that came due, total past due for the bucket, and the scheduled-payment amount. | ||
next_payment_date | date | This is the next date on which a payment will come due. This date will always be in the future of the date for this record. | ||
next_payment_amount | decimal(11,2) | This is the amount of the next payment that will come due. | ||
last_payment_date | date | This is the last date on which a transaction is posted to the account. This could be a payment or a credit. | ||
last_payment_amount | decimal(11,2) | This is the amount of the last transaction posted to the account. It could be a payment or a credit. | ||
principal_balance | decimal(11,2) | This is the principal balance on the loan, which is the unpaid portion of the principal. | ||
amount_past_due_30 | decimal(11,2) | This is the amount that has been past due for more than 30 days. This amount came due with payments, at least 30 days ago, but hasn't been paid. | ||
days_past_due | int(11) | This is the number of days the loan is past due. This will equal the day after the earliest unpaid amount came due on the loan. | ||
date_last_current | date | This is the date the loan was last current, meaning it didn't have an unpaid amount due. | ||
date_last_current_30 | date | This is the last date the loan had been current for 30 days. This field is not used, and the value will likely be NULL. | ||
payoff | decimal(11,2) | This is the payoff amount on the loan. This includes all principal, interest, fees, and escrow that must be paid in order to pay the loan off. | ||
perdiem | decimal(11,2) | This is the calculated per diem interest. This is done by multiplying the principal balance by the daily interest rate. | ||
interest_accrued_today | decimal(11,2) | This is the actual amount of interest that accrued on the loan for this date. | ||
available_credit | decimal(11,2) | This field is only applicable if the loan has a credit limit. This is the amount of credit still available on the loan. | ||
credit_limit | decimal(11,2) | This is the credit limit on the loan. | ||
period_start | date | This is the start date of the payment period during which this record was made. | ||
period_end | date | This is the end date of the payment period during which this record was made. | ||
periods_remaining | int(11) | This is the number of payment periods remaining on the loan. | ||
escrow_balance | decimal(11,2) | This is the amount of due, but unpaid, escrow on the loan. | ||
escrow_balance_breakdown | varchar(255) | This field holds a JSON object that contains the ID of each escrow bucket, and the due, but unpaid, amount for the bucket. | ||
discount_remaining | decimal(11,2) | This is the amount of discount that has not yet been paid. Discount is a portion of the principal that is also revenue. Discount occurs when a loan is acquired for less than face value (e.g. the loan is for $5,000, but the note was purchase for $4,000). | ||
loan_status_id | int(11) | This is the ID of the loan status associated with the loan. Loan statuses can be found in the loan_status_entity table. | ||
loan_status_text | varchar(255) | This is the title or name of the loan status. | ||
loan_sub_status_id | int(11) | This is the ID of the loan sub status associated with the loan. Loan sub statuses can be found in the loan_sub_status_entity table. | ||
loan_sub_status_text | varchar(255) | This is the title or name of the loan sub status. | ||
credit_status | char(30) | This is the credit status of the loan. If credit is reported, this is the credit status that will be reported. |
| |
loan_age | int(11) | This is the number of days since the contract date on the loan. The contract date can be found in the loan_setup_entity table. | ||
loan_recency | int(11) | This is the number of days since a payment was made on the loan. | ||
last_human_activity | date | This is the last time the loan was updated by a human. Human activity requires more than the loan being opened. Something must be saved on the loan. | ||
stoplight | char(30) | This is the color of the stoplight for the loan. Stoplights change color based on user-defined rules. |
| |
final_payment_date | date | This is the date that the last payment is scheduled to be made on the loan. | ||
final_payment_amount | decimal(11,2) | This is the amount of the last scheduled payment on the loan. | ||
net_charge_off | decimal(11,2) | This is the total amount that has been charged off of the loan. | ||
first_delinquency_date | date | This is the first date on which the loan was delinquent without having come current. | ||
unique_delinquencies | int(11) | This is the number of unique delinquencies on the loan. That is the number of times the loan has been delinquent, but then come current and, if the loan is currently delinquent, the current delinquency. | ||
delinquency_percent | decimal(11,2) | This is the percentage of the life of the loan that the loan has been delinquent. | ||
delinquent_days | int(11) | This is the number of days during which the loan has been delinquent. | ||
calced_ecoa | char(20) | ECOA code for the borrower. |
| |
calced_ecoa_cobuyer | char(20) | ECOA code for the co-borrower. |
| |
deleted | tinyint(1) | This is whether the record has been marked as deleted. | 1 - yes, 0 - no | |
custom_fields_breakdown | varchar(4000) | This field holds a JSON object that shows the ID and the value for each custom field on the loan. Custom fields can be found in the custom_field table. | ||
portfolio_breakdown | varchar(4000) | This field contains an array of the IDs of the portfolios with which the loan is associated. | ||
sub_portfolio_breakdown | varchar(4000) | This field contains an array of the IDs of the subportfolios with which the loan is associated. | ||
lastUpdated | timestamp | Timestamp of when the record was last updated. | ||
source_company_id | decimal(11,2) | This is the ID of the source company with which the loan is associated. Source companies can be found in the source_company_entity table. | ||
source_company_text | decimal(11,2) | This is the title or name of the source company. |