# 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. 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.creditstatus.0 - AUTO – Allow system to manage this field.loan.creditstatus.11 - Current account. (0-29 days past the due date)loan.creditstatus.13 - Paid or closed account/zero balance.loan.creditstatus.5 - Account transferred to another officeloan.creditstatus.61 - Account paid in full, was a voluntary surrender.loan.creditstatus.62 - Account paid in full, was a collection account.loan.creditstatus.63 - Account paid in full, was repossessionloan.creditstatus.64 - Account paid in full, was a charge-off.loan.creditstatus.71 - Account 30-59 days past the due date.loan.creditstatus.78 - Account 60-89 days past the due dateloan.creditstatus.80 - Account 90-119 days past the due dateloan.creditstatus.82 - Account 120-149 days past the due dateloan.creditstatus.83 - Account 150-179 days past the due dateloan.creditstatus.84 - Account 180 days or more past the due dateloan.creditstatus.93 - Account assigned to internal or external collectionsloan.creditstatus.95 - Voluntary surrender.loan.creditstatus.96 - Merchandise was repossessed; there may be a balance due.loan.creditstatus.97 - Unpaid balance reported as a loss (charge-off)loan.creditstatus.99 - Do not sendloan.creditstatus.DA - Deletes entire account (for reasons other than fraud).loan.creditstatus.DF - Delete entire account due to confirmed fraud (fraud investigation completed). 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. stoplight.display.greenstoplight.display.yellowstoplight.display.red 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. loan.ecoacodes.0 – Not Specifiedloan.ecoacodes.1 – Individual/Primaryloan.ecoacodes.2 – Joint Contractloan.ecoacodes.7 – Makerloan.ecoacodes.A – System Managedloan.ecoacodes.T – Associate Terminatedloan.ecoacodes.X – Consumer Deceasedloan.ecoacodes.Z – Delete Borrower calced_ecoa_cobuyer char(20) ECOA code for the co-borrower. loan.ecoacodes.0 – Not Specifiedloan.ecoacodes.1 – Individual/Primaryloan.ecoacodes.2 – Joint Contractloan.ecoacodes.7 – Makerloan.ecoacodes.A – System Managedloan.ecoacodes.T – Associate Terminatedloan.ecoacodes.X – Consumer Deceasedloan.ecoacodes.Z – Delete 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.