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

decimal(11,2)

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 office
  • loan.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 repossession
  • loan.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 date
  • loan.creditstatus.80 - Account 90-119 days past the due date
  • loan.creditstatus.82 - Account 120-149 days past the due date
  • loan.creditstatus.83 - Account 150-179 days past the due date
  • loan.creditstatus.84 - Account 180 days or more past the due date
  • loan.creditstatus.93 - Account assigned to internal or external collections
  • loan.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 send
  • loan.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.green
  • stoplight.display.yellow
  • stoplight.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 Specified
  • loan.ecoacodes.1 – Individual/Primary
  • loan.ecoacodes.2 – Joint Contract
  • loan.ecoacodes.7 – Maker
  • loan.ecoacodes.A – System Managed
  • loan.ecoacodes.T – Associate Terminated
  • loan.ecoacodes.X – Consumer Deceased
  • loan.ecoacodes.Z – Delete Borrower

calced_ecoa_cobuyer

char(20)

ECOA code for the co-borrower.

  • loan.ecoacodes.0 – Not Specified
  • loan.ecoacodes.1 – Individual/Primary
  • loan.ecoacodes.2 – Joint Contract
  • loan.ecoacodes.7 – Maker
  • loan.ecoacodes.A – System Managed
  • loan.ecoacodes.T – Associate Terminated
  • loan.ecoacodes.X – Consumer Deceased
  • loan.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.


How did we do?


Powered by HelpDocs (opens in a new tab)