Database – Loan Table (loan_entity)
Description
This table holds core loan information, including IDs of associated entities and modification information. This is only the information required to create the loan, and does not contain the numbers used to calculate the loan, which can be found in the loan_setup_entity table.
Common Uses
This table is commonly used to find:
- IDs of entities associated to a loan
- Loan creation date
- Last date daily maintenance was run on a loan
When querying the database, it is common to join the loan_entity table with the loan_setup_entity table. It may be tempting to join the tables on the loan_id field like this:
SELECT *
FROM loan_entity le
INNER JOIN loan_settings_entity lse
ON lse.loan_id = le.id
However, this does not account for loan modifications. A loan modification will create a new loan_setup_entity record that is then used on the loan. If the loan has been modified, it is preferable to only pull the current loan setup. The setup_id in the loan_entity table will always have a value that equals the ID of the current loan setup.
A query like this will join the loan_entity with the current loan_setup_entity:
SELECT *
FROM loan_entity le
JOIN loan_setup_entity lse
ON le.setup_id = lse.id;
Table Fields
Column | Data Type | Column Info | Value Notes |
id | int(11) | The ID of the loan. This is the system ID, which is a column option in the Loan Manager. | |
display_id | char(50) | Loan display ID. This ID is assigned by the user and is displayed as the ID for the loan inside the LoanPro user interface. | |
title | char(255) | The title of the loan. This field is not commonly used, and is set to the display ID by default. It can't be updated via the UI, but can be changed using the API. | |
settings_id | int(11) | This is the ID of the associated Loan Settings entity. Loan settings include things loan status, and portfolio associations, credit reporting settings, and grace period. | |
setup_id | int(11) | This is the ID of the associated Loan Setup entity. Loan Setup includes things like the loan amount, loan term, interest rate, etc. | |
insurance_policy_id | int(11) | This is the ID of the associated Insurance entity. The Insurance entity keeps track of information for policies that insure loan collateral. | |
collateral_id | int(11) | This is the ID of the associated Collateral entity. The Collateral entity tracks collateral used to secure the loan. | |
linked_loan | int(11) | Loans can be linked to one another. This field holds the ID for the entity that records the link between this loan and the loan it's linked to. | |
mod_id | int(11) | When loans are modified, each new modification is assigned an ID. This field shows the ID for the active modification. If the loan has not been modified, the value of this field will be NULL. | |
mod_total | int(11) | This field shows the total number of modifications for a loan. If the loan has not been modified, it will show 0. | |
human_activity_date | date | This is the date on which the most recent action that required human activity was performed on the loan. | |
last_maint_run | datetime | Daily Maintenance is a process that runs to update loan information such as dates, accrued interest, automated fees, etc. This field holds the date and time when daily maintenance was last run on an account. | |
created | timestamp | This field holds the date and time when the loan was created, in the form of a timestamp that looks like this "/Date(1561593600)/". The numeric portion of the value is a Unix timestamp. | |
lastUpdated | timestamp | This field holds the date and time when the loan was last updated, in the form of a timestamp that looks like this "/Date(1561593600)/". The numeric portion of the value is a Unix timestamp. | |
created_by | int(11) | This field holds the ID of the Agent User that created the loan. The Agent Users table is not found in the database, but the IDs and associated names can be found in the user interface. | |
active | tinyint(1) | This shows whether the entity is active or not. This is not whether the loan is activated or whether the loan is live. This field/value will have no bearing on the loan. | 1 - yes, 0 - no |
archived | tinyint(1) | This value shows if the loan is archived (1) or active (0). | 1 - yes, 0 - no |
loan_alert | varchar(255) | This is the loan alert for the loan. If no loan alert has been created for a loan, the value will be NULL. | |
deleted | tinyint(1) | This value shows whether the entity has been deleted (1) or whether it is available (0). If the entity has been deleted, it will not be available via the UI or API. | 1 - yes, 0 - no |
deleted_at | datetime | This field holds the date and time when the loan was deleted, in the form of a timestamp that looks like this "/Date(1561593600)/". The numeric portion of the value is a Unix timestamp. | |
temporary_account | tinyint(1) | This value shows whether the account is temporary (1) or not(0). A temporary account is usually created via the LoanPro quick quote tool, and will be automatically deleted after 24 hours. | 1 - yes, 0 - no |