Database Payment Information

General

Some customers choose the optional feature of access to a read-only database replica. This is usually a good choice for large companies that need to pull reports on large data sets, because data can be pulled more quickly from a database than through the API. This article will cover some of the basics of pulling payment data through the database.

Tables

There are two primary tables where payment data can be found. The two tables are named loan_tx and payment_entity.

loan_tx

The loan_tx table holds all loan transactions. Some information is present in the table for the loan that a transaction is related to. This table consists of the following fields:

Column

Data Type

Column Info

Value Notes

Other

id

int(11)

The id of the entity

entity_id

int(11)

ID of the associated entity

entity_type

varchar(100)

Type of the associated entity

tx_id

char(40)

Transaction ID

mod_id

int(11)

The id of the associated loan modification

date

datetime

transaction date

period

int(11)

transaction period

period_start

date

Transaction period start

period_end

date

Transaction period end

title

varchar(255)

The name/title of the entity instance

type

char(20)

transaction type

info_only

tinyint(1)

transaction info

1 - yes, 0 - no

info_details

varchar(255)

transaction details

payment_id

bigint(21)

Associated payment id

payment_display_id

bigint(21)

Payment display id

payment_amount

decimal(10,2)

payment amount

payment_i

decimal(10,2)

payment interest amount

payment_p

decimal(10,2)

payment principal amount

payment_d

decimal(10,2)

payment discount amount

payment_f

decimal(10,2)

Fees Paid

payment_e

decimal(10,2)

payment escrow amount

payment_e_breakdown

varchar(2000)

payment escrow breakdown

fees_paid_details

varchar(2000)

payment fees paid detalis

charge_amount

decimal(10,2)

amount charge

charge_i

decimal(10,2)

charge interest

charge_p

decimal(10,2)

charge principal

charge_d

decimal(10,2)

charge discount

charge_f

decimal(10,2)

charge fees

charge_e

decimal(10,2)

charge escrow

charge_e_breakdown

varchar(2000)

charge escrow breakdown

future

tinyint(4)

whether or not the payment applies ot the future

1 - yes, 0 - no

principalonly

tinyint(4)

whether or not the payment is principal only

1 - yes, 0 - no

advancement

tinyint(1)

transaction is an advancment

1 - yes, 0 - no

payoff_fee

tinyint(4)

transaction is a payoff fee

1 - yes, 0 - no

charge_off

tinyint(4)

whether or not the payment is a charge off

1 - yes, 0 - no

payment_type

int(11)

Payment type ID

adb_days

int(11)

Average Daily Balance Days

adb

decimal(10,2)

Average Daily Balance

principal_balance

decimal(10,2)

principal balance

displayorder

decimal(10,2)

display order

deleted

tinyint(4)

Whether or not the entity was deleted

1 - yes, 0 - no

lastUpdated

timestamp

Timestamp of when the entity was last updated

payment_entity

The payment_entity table only holds data about payment transactions. This table consists of the following fields:

Data Type

Data Type

Column Info

Value Notes

Other

id

int(11)

The id of the entity

display_id

int(11)

payment display id

payment_info_id

int(11)

Payment info id

payment_type_id

int(11)

Payment type id

payment_processor_id

int(11)

Payment processor id

payment_method_id

int(11)

Payment method id

payment_account_id

int(11)

Payment account id

tx_snapshot_id

int(11)

Transaction snapshot id

amount

decimal(11,2)

payment amount

apply_date

date

Date of application

early

tinyint(1)

Whether or not payment is early

1 - yes, 0 - no

payoff_flag

tinyint(1)

Whether or not is a payoff payment

1 - yes, 0 - no

payoff_diff_apply

char(40)

Whether or not should payoff difference be applied

payoff_options

varchar(500)

Payoff options

custom_application

varchar(500)

custom application

info

varchar(255)

Extra information about an entity instance

extra

varchar(100)

Payment extra application

parent

int(11)

Parent payment id

child

int(11)

child paymen tid

charge_fee_type

varchar(20)

Servicing fee type

loan.cardfees.type.*

charge_fee_amount

decimal(11,2)

servicing fee amount

charge_fee_percentage

decimal(11,2)

servicing fee percentage

echeck_auth_type

varchar(100)

E-Check authorization type

payment.echeckauth.*

cash_drawer_id

int(11)

Cash drawer ID

cash_drawer_tx_id

int(11)

Cash drawer transactoin ID

status

varchar(45)

Status code of the entity

reverse_reason

varchar(45)

Payment reverse reason

reverse_date

date

Payment reverse date

comments

varchar(250)

payment comments

source_company

int(11)

associated source company id

loan_status

int(11)

associated loan status id

loan_substatus

int(11)

associated loan sub status id

before_principal_balance

decimal(11,2)

Principal balance before payment

before_payoff

decimal(11,2)

payoff before payment

before_next_due_date

date

next due date before payment

before_next_due_amount

decimal(11,2)

next due amount before payment

before_amount_past_due

decimal(11,2)

Amount past due on the loan before the payment was applied

before_days_past_due

int(11)

dpd before payment

after_principal_balance

decimal(11,2)

principal balance after payment

after_payoff

decimal(11,2)

payoff after payment

after_next_due_date

date

next due date after payment

after_next_due_amount

decimal(11,2)

next due amount after payment

after_amount_past_due

decimal(11,2)

apd after payment

after_days_past_due

int(11)

dpd after payment

system_comments

text

system comments

charge_off_recovery

tinyint(1)

charge off recovery

1 - yes, 0 - no

reset_past_due

tinyint(1)

Whether or not to reset days past due

1 - yes, 0 - no

apd_adjustment_id

int(11)

associated apd adjustment id

dpd_adjustment_id

int(11)

associated dpd adjustment id

active

tinyint(1)

Whether or not the entity is active

1 - yes, 0 - no

deleted

tinyint(1)

Whether or not the entity was deleted

1 - yes, 0 - no

created

timestamp

Timestamp of when the entity was created

lastUpdated

timestamp

Timestamp of when the entity was last updated

mod_id

int(11)

The id of the associated loan modification

entity_id

int(11)

ID of the associated entity

entity_type

varchar(100)

Type of the associated entity

payment_method_option

varchar(45)

Payment method option

payment_method_name

varchar(255)

payment method name

is_split

tinyint(1)

Whether or not is payment split

1 - yes, 0 - no

split_id

int(11)

Split payment ID

nacha_return_code

varchar(255)

NACHA Return Code

autopay_id

int(11)

Associated autopay ID

last_extra

varchar(100)

Encoded information about the last extra amount

Queries

There are several queries that customers use to pull payment data. The following examples will help you understand how to use these tables.

Note: if you are calculating your loans using a between-transactions interest application and your extra towards setting is next, you may get multiple payments with the same ID. If this is the case, merge the payments by ID in order to view the amounts as originally logged.

Pull All Payments

If you want to pull all payment data, we recommend something like the following:

select t.* from loan_tx t, payment_entity p where t.type = 'payment' and t.payment_id = p.id;

It is important to note that the payment ID, as it appears in the software, can be found in the payment_id field of the loan_tx table and the id field of the payment_entity table.

Pull Payments Logged Over a Date Range

select t.* from loan_tx t, payment_entity p

where t.type = 'payment'

    and t.payment_id = p.id

   and (

        date(t.date) between '2018-08-01' and '2018-08-31'

    );

Pull Payments With IDs that Fall Into a Range

select t.* from loan_tx t, payment_entity p

where t.type = 'payment'

    and t.payment_id = p.id

   and (

       payment_id between 1 and 35

    );


How did we do?


Powered by HelpDocs (opens in a new tab)