Database – Payments (payment_entity)

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.

• loan.cardfee.types.0 - Waive Card Payment Fee
• loan.cardfee.types.1 - Flat Fee
• loan.cardfee.types.2 - Percentage
• loan.cardfee.types.3 - Greater of Fee or Percentage
• loan.cardfee.types.4 - Lesser of Fee or Percentage

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.