Database – Payments (payment_entity)
Description
This table holds the information for payments made on loans. This is the primary record for payments in the database.
Common Uses
This table is commonly used to find:
- Payments that were made
- Payment dates
- Payment amounts
- Payment Status
Column | Data Type | Column Info | Value Notes |
id | int(11) | The id of the payment. This is the internal ID, which is what should be used to update the payment through the API. | |
display_id | int(11) | The ID for the payment that is displayed in the LoanPro user interface. This ID will be different from the ID in the "id" field. | |
payment_info_id | int(11) | If the payment was processed through Secure Payments, this field will show the ID for the associated payment info record. Payment info can be found in the payment_info_entity table. | |
payment_type_id | int(11) | The ID for the payment type. Payment types can be found in the custom_payment_type_entity table. | |
payment_processor_id | int(11) | The ID of the payment processor. Payment processors are set up to work with either a third-party payment processor or through NACHA. Payment processors can be found in the mc_processor_extender table. | |
payment_method_id | int(11) | The ID for the payment method. Payment methods can be found in the custom_payment_method_entity table. | |
payment_account_id | int(11) | The ID of the payment account or payment profile used to transact funds on the payment. If the payment was simply logged in LoanPro with no processing through Secure Payments, this value will be NULL. Payment account information can be found in the payment_account_entity table. | |
tx_snapshot_id | int(11) | When a payment is reversed in LoanPro, a record of some of the payment application and loan data is made. This data doesn't change when future activity happens on the loan. It is therefore referred to as a snapshot. These records can be found in the payment_reversed_tx_snapshot table. | |
amount | decimal(11,2) | The amount of the payment. | |
apply_date | date | The date on which the payment applies to the loan. This may be different than the day the payment was logged, depending on the payment or loan settings. | |
early | tinyint(1) | This field is only applicable to payments on loans that have a between-periods interest application. This field determines if less interest will accrue when payments are made early on a loan. | 1 - yes, 0 - no |
payoff_flag | tinyint(1) | Whether the payment is a payoff payment. Payoff payments should pay the whole outstanding balance on a loan. | 1 - yes, 0 - no |
payoff_diff_apply | char(40) | This field is only applicable to payoff payments. If there is a difference between the payoff amount calculated by LoanPro and the payoff amount entered by the user, this field will show the type of adjusting transaction used to offset the difference. The options are chargeoff, credit, advancement, and charge. The value of this field will be specific from one of LoanPro's collections. | payoff.applydiff.chargeoff payoff.applydiff.credit payoff.applydiff.alt.advance payoff.applydiff.alt.charge |
payoff_options | varchar(500) | This field is only relevant to payoff payments. The payoff options field holds a JSON object with 3 key, value pairs: | |
custom_application | varchar(500) | LoanPro allows the user to allocate payments towards interest, principal, standard fees, payoff fees, and escrow in different amounts than the amounts LoanPro calculates. This field holds a JSON object that shows how the payment applied between the different payment components. | |
info | varchar(255) | Information about the payment entered by the user at the time the payment was logged. | |
extra | varchar(100) | Sometimes a payment is logged for more than what is due on a loan. The value of this field is used to determine what will happen with the "extra" amount. The options are different depending on the interest application on the loan. The options are towards principal, towards principal only (doesn't affect future due amount), and classic (towards the next payment and when that gets paid, towards principal). | payment.extra.periods.next payment.extra.periods.principalonly payment.extra.tx.classic payment.extra.tx.principal payment.extra.tx.principalonly |
parent | int(11) | This field is only relevant to edited payments. When a payment is edited, the original payment no longer applies to the loan, and a new payment is created. This is the ID of the original payment before it was edited. The parent payment should also be present in this table. | |
child | int(11) | This field is only relevant to edited payments. When a payment is edited, the original payment no longer applies to the loan, and a new payment is created. If this payment has been edited, this is the ID of the new payment that was created. The child payment should also be present in this table. | |
charge_fee_type | varchar(20) | This field is only relevant to payments that were processed through Secure Payments. When a payment is processed, a convenience fee may be charged. This field shows the type of the convenience fee. The value for this field comes from one of LoanPro's collections. The options are to waive the fee, charge a flat dollar amount, charge the greater of a flat dollar amount or a percentage of the payment, or charge the lesser of a flat dollar amount or a percentage of the payment. |
|
charge_fee_amount | decimal(11,2) | If a convenience fee is charged to process a payment, this is the flat dollar amount used for the fee. For more explanation, see the charge_fee_type field. | |
charge_fee_percentage | decimal(11,2) | If a convenience fee is charged to process a payment, this is the percentage of the payment used for the fee. For more explanation, see the charge_fee_type field. | |
echeck_auth_type | varchar(100) | When processing a payment through echeck, ACH, or EFT, the customer will authorize the payment in a specific way. Usually a company is set up to receive these payments through one of the following methods: through the web (WEB), over the phone (TEL), through a personal signature (PPD), or through a company signature (CCD). The values for this field come from one of LoanPro's collections. | payment.echeckauth.WEB payment.echeckauth.TEL payment.echeckauth.CCD payment.echeckauth.PPD |
cash_drawer_id | int(11) | Cash drawers may be used when payments are taken at a physical location. This field shows the ID of the cash drawer that received this payment. Cash drawers can be found in the cash_drawer_entity table. | |
cash_drawer_tx_id | int(11) | If a payment is taken at a physical location, it may be associated with a cash drawer. Each cash drawer transaction has an ID. This field holds the ID of the cash drawer transaction. Cash drawer transactions can be found in the cash_drawer_transaction_entity table. | |
cash_drawer_tx_status | char(32) | The status of the payment transaction related to the cash drawer. For example, whether the transaction has been cleared, reconciled, etc. | |
cash_drawer_terminal_number | char(32) | The terminal number where the payment was received. | |
cash_drawer_tx_id | char(32) | The ID of the cash drawer associated with the payment. Cash drawer IDs can be found in the cash_drawer_entity table. | |
status | varchar(45) | This field indicates the status of a processed payment. The options seen most often are none, success, failed, and voided. A log-only payment will always be given the status of none. For a full list, see the Payment Status Collection. It should be noted that this is not the Secure Payments status of the payment. | payment.status.none payment.status.success payment.status.failed payment.status.voided |
reverse_reason | varchar(45) | This field is only relevant to reversed payments. This is the reason the user selected for reversing the payment. The options are: check bounced, clerical error, NACHA error code, insufficient funds, and other. The values come from one of LoanPro's collections. | payment.reverse.checkBounce payment.reverse.clericalError payment.reverse.nachaErrorCode payment.reverse.nsf payment.reverse.other |
reverse_date | date | This field is only relevant to reversed payments. This is the date on which the payment was reversed. | |
comments | varchar(250) | This field is only relevant to reversed payments. This is the comment entered by the user about why the payment was reversed. | |
source_company | int(11) | The ID of the source company that was associated with the loan at the time the payment was logged. Source companies can be found in the source_company_entity table. | |
loan_status | int(11) | The ID of the loan status associated with the loan at the time the payment was logged. Loan statuses can be found in the loan_status_entity table. | |
loan_substatus | int(11) | The ID of the loan sub status associated with the loan at the time the payment was logged. Loan sub statuses can be found in the loan_sub_status_entity table. | |
before_principal_balance | decimal(11,2) | This is the principal balance of the loan before the payment applied to the loan. | |
before_payoff | decimal(11,2) | This is the payoff amount of the loan before the payment applied to the loan. | |
before_next_due_date | date | This is the next payment due date on the loan before the payment applied to the loan. | |
before_next_due_amount | decimal(11,2) | This is the amount of the next payment that was due on the loan before the payment applied to the loan. | |
before_amount_past_due | decimal(11,2) | This is the amount that the loan was past due before the payment applied on the loan. | |
before_days_past_due | int(11) | This is the number of days the loan was past due before the payment applied on the loan. | |
after_principal_balance | decimal(11,2) | This is the principal balance immediately after the payment applied to the loan. | |
after_payoff | decimal(11,2) | This is the payoff amount on the loan immediately after the payment applied to the loan. | |
after_next_due_date | date | This is the date the next payment was due on the loan ,after the payment applied to the loan. | |
after_next_due_amount | decimal(11,2) | This is the amount of the next payment that was due on the loan after the payment applied to the loan. | |
after_amount_past_due | decimal(11,2) | This is the amount that was past due on the loan, after the payment applied to the loan. | |
after_days_past_due | int(11) | This is the number of days that the loan was past due, after the payment applied to the loan. | |
system_comments | text | These are any comments the system added to the payment. This includes things like whether the loan was activated at the time the payment was made. | |
charge_off_recovery | tinyint(1) | This flags the payment as a recovery payment. If a portion of the loan has been charged off, a recovery payment will apply to the charged-off portion of a loan. | 1 - yes, 0 - no |
reset_past_due | tinyint(1) | If there is still an amount past due on a loan, after a payment has been made, the loan will still be past due by default. This field shows whether the amount and days past due were reset when the payment was made. | 1 - yes, 0 - no |
apd_adjustment_id | int(11) | If the amount past due was reset when the payment was made, this field will show the ID of the amount past due adjustment. Amount past due adjustments can found in the loan_apd_adjustment_entity. | |
dpd_adjustment_id | int(11) | If the days past due were reset when the payment was made, this field will show the ID of the days past due adjustment. Days past due adjustments can found in the loan_dpd_adjustment_entity. | |
active | tinyint(1) | This value shows whether the payment is active. If the payment was reversed or edited, or part of an inactive loan modification, this value may be 0. | 1 - yes, 0 - no |
deleted | tinyint(1) | This value shows whether the payment is marked as deleted. | 1 - yes, 0 - no |
created | timestamp | A timestamp showing when the payment was created. | |
lastUpdated | timestamp | A timestamp showing when the payment was last updated. | |
mod_id | int(11) | The ID of the loan modification the payment is associated with. When a loan is modified, a modification is created. Payments and a loan setup entity are associated to the modification. This is the ID of the modification that is currently active on the loan. If the loan has not been modified, the value will be NULL. Loan modifications can be found in the loan_modification_entity table. | |
entity_id | int(11) | The ID of the loan the payment applies to. Loans can be found in the loan_entity table. | |
entity_type | varchar(100) | The type of entity that the entity_id refers to. With payments, this value should be Entity.Loan. | |
payment_method_option | varchar(45) | If the payment was processed through Secure Payments and it was made through a bank account, this field will hold the account number and routing number. | |
payment_method_name | varchar(255) | This is the name of the payment profile used to process the payment. Payment profiles are created and stored in Secure Payments. | |
is_split | tinyint(1) | Payments can be split across linked loans. This value shows whether the payment applied to more than one loan. | 1 - yes, 0 - no |
split_id | int(11) | If a payment is split (see is_split field), this is the ID used to identify the payment on each of the loans where it applied. The split ID will be the same for this payment and the payments on each of the other loans where it applied. If two or more payments have the same split ID, they started as the same payment. | |
nacha_return_code | varchar(255) | This is the return code from the financial institution that processed NACHA (US) or CPA-005 (Canada) payments. There may be a NACHA return code for any payment processed from a bank account (NACHA, CPA, ACH, EFT). | |
autopay_id | int(11) | If the payment was processed automatically through an AutoPay, this field will show the ID of the associated AutoPay. AutoPays can be found in the loan_autopay_entity table. | |
last_extra | varchar(100) | This field holds the previous extra towards value (see the extra field) when the loan changes interest application. |