Lending reports

Detailed descriptions of available lending reports and instructions for accessing them.

Lending reports are available by request via the Report File Hub or a destination of your choosing. Your data delivery options are outlined in our Data on Demand article. These reports are designed to help simplify the process of internal reporting, but can also make it easy to share reports with sponsor banks. The report options include both summary reports and detailed reports that provide granular insights. This article will define each of the available reports and show you how to access them within your tenant.

LoanPro gives you multiple options for getting data in and out of our platform and connecting to external systems, but many of our clients use a similar approach for reconciling line for credit data from LoanPro with their other tools, outlined in our Line of Credit Reconciliation Strategy.

Available reports

Lending reports are scheduled to query the database daily and deliver outputs to your Report File Hub or chosen location. In most cases, the report will display data from the previous day (T-1) although some reports pull data from the previous month.

Each available report is listed below. For an in-depth guide, click the report title.

Loan tape

The ___ report provides a comprehensive overview of ___.

Sample (link)

Report values

Column Name	Description

Payment breakdown

The ___ report provides a comprehensive overview of ___.

Sample (link)

Report values

Column Name	Description

Reversed payments

The reverse payments report provides a comprehensive overview of payment transactions that have been reversed within loan accounts. It includes information about the original payment details, customers, collateral, and the specific reasons and timestamps associated with the reversal action.

Report values

Column Name	Description
Account	Loan display ID.
Primary Customer	Full name of the primary borrower.
Collateral	A combination of collateral fields A, B, C, and D.
Reverse Date	The date the payment was reversed.
Log Date PST	The creation timestamp of the payment in PST.
Payment ID	The payment display ID.
Extra Towards	Determines how extra towards is applied on the payment.
Payment Type Name	The name or title of the Custom Payment Type Entity.
Amount	The amount of the payment.
Reversal Reason Name	The reason for payment reversal.
Nacha Return Code	The return code processed by NACHA.
Created	The creation timestamp of the payment.

SQL Query Example

 with loan_info as (
    SELECT le.display_id account,
        le.id,
        case 
            when ce.customer_type = 'customer.type.company' then ce.company_name
            else concat(IFNULL(ce.first_name, '') ," ",IFNULL(ce.middle_name, '')," ",IFNULL(ce.last_name, '')) 
        end primary_customer,
        CONCAT(IFNULL(col.a, '')," ",col.b," ",IFNULL(col.c, '')," ",IFNULL(col.d, '')) AS collateral
    from loan_entity le
        left join loan__customer lc on le.id = lc.loan_id and lc.customer_role = "loan.customerRole.primary"
        left join customer_entity ce on ce.id = lc.customer_id
        left join collateral_entity col on col.loan_id = le.id      
)
select li.account,
    li.primary_customer,
    li.collateral, 
    pe.reverse_date,
    DATE(CONVERT_TZ(pe.created, 'UTC', 'America/Los_Angeles')) AS log_date_pst,
    pe.display_id as payment_id,
    CASE
        WHEN pe.extra LIKE '%principalonly' THEN 'Principal Only'
        WHEN pe.extra IN ('payment.extra.tx.classic', 'payment.extra.tx.classicv1', 'payment.extra.tx.classicv2') THEN 'N/A'
        WHEN pe.extra IN ('payment.extra.periods.next', 'periods.next') THEN 'Next Payment'
        WHEN pe.extra IN ('payment.extra.tx.principal', 'tx.principal') THEN 'Principal'
        ELSE pe.extra
    END AS extra_towards,
    cpte.title AS payment_type_name,
    cpme.title AS payment_method_name,
    pe.amount,
    CASE pe.reverse_reason
        WHEN 'payment.reverse.canadaErrorCode' THEN 'Other'
        WHEN 'payment.reverse.checkBounce' THEN 'Check Bounced'
        WHEN 'checkBounce' THEN 'Check Bounced'
        WHEN 'payment.reverse.clericalError' THEN 'Clerical Error'
        WHEN 'clericalError' THEN 'Clerical Error'
        WHEN 'payment.reverse.nachaErrorCode' THEN 'NACHA Error Code'
        WHEN 'nachaErrorCode' THEN 'NACHA Error Code'
        WHEN 'payment.reverse.nsf' THEN 'Insufficient Funds (NSF)'
        WHEN 'insufficientFunds' THEN 'Insufficient Funds (NSF)'
        WHEN 'payment.reverse.other' THEN 'Other'
        WHEN 'other' THEN 'Other'
        ELSE pe.reverse_reason
    END AS reversal_reason_name,
    pe.nacha_return_code,
    pe.created 
from payment_entity pe
    left join loan_info li on pe.entity_id = li.id
    left join custom_payment_type_entity cpte on pe.payment_type_id = cpte.id
    left join custom_payment_method_entity cpme on pe.payment_method_id = cpme.id
where pe.reverse_date is not NULL

Charges

The charges report provides a comprehensive overview of fees and assessments applied to loan accounts. It includes information about accounts, primary customers, collateral details, and a breakdown of specific charge statuses, amounts, and dates applied.

Report values

Column Name	Description
Account	The loan display ID.
ID	The ID of the borrower.
Primary Customer	The full name of the primary borrower.
Collateral	A combination of collateral fields A, B, C, and D.
Entity ID	The ID of the associated entity. This will be the loan ID.
Status	The active status of the charge. 1 - Active, 0 - Inactive
Amount	The dollar amount of the charge.
Paid Amount	The dollar amount of the fee paid.
Log Date	The timestamp of when the charge was last updated.
Applied Date	The date the charge applies to the loan.
Type	The ID of the charge type.
Application	The charge application type.
Info	Additional information about the charge.
Display ID	The Display ID of the charge.
Related Payment ID	The associated payment ID if applicable.

SQL Query Example

 with loan_info as (
    SELECT le.display_id account,
        le.id,
        case 
            when ce.customer_type = 'customer.type.company' then ce.company_name
            else concat(IFNULL(ce.first_name, '') ," ",IFNULL(ce.middle_name, '')," ",IFNULL(ce.last_name, '')) 
        end primary_customer,
        CONCAT(IFNULL(col.a, '')," ",col.b," ",IFNULL(col.c, '')," ",IFNULL(col.d, '')) AS collateral
    from loan_entity le
        left join loan__customer lc on le.id = lc.loan_id and lc.customer_role = "loan.customerRole.primary"
        left join customer_entity ce on ce.id = lc.customer_id
        left join collateral_entity col on col.loan_id = le.id      
),
charge_info as (
    select case 
        when ce.active = 1 then "Active"
            else "Reversed"
        end status,
        ce.amount,
        ce.paid_amount,
        ce.lastUpdated log_date,
        ce.apply_date applied_date, 
        ccte.title type,
        ce.charge_application_type Application,
        ce.info,
        ce.display_id,
        ce.related_payment_id,
        ce.entity_id,
        ce.lastUpdated
    from charge_entity ce 
        left join custom_charge_type_entity ccte  on ce.`type`  = ccte.id
    where ce.entity_type = "entity.loan"
)
SELECT li.account, 
    li.id, 
    li.primary_customer, 
    li.collateral,
    ci.entity_id ,
    ci.status,
    ci.amount,
    ci.paid_amount, 
    ci.log_date, 
    ci.applied_date,
    ci.`type`, 
    ci.Application, 
    ci.info, 
    ci.display_id, 
    ci.related_payment_id
from charge_info ci 
    left join loan_info li on ci.entity_id = li.id

Enhanced funding

The enhanced funding report provides a comprehensive overview of funding transactions associated with loan accounts. It includes information about accounts, primary customers, collateral descriptions, and the specific lifecycle of each funding transaction, including status tracking, payment methods, and any reversal details.

Report values

Column Name	Description
Account	The ID of the loan.
ID	The ID of the Enhanced Funding.
Primary Customer	The full name of the primary borrower.
Collateral	A combination of collateral fields A, B, C, and D.
Transaction ID	The ID of the transaction.
Fund Who	The type of the entity receiving funding.
Category	The category of funding.
Method	The method funding is provided.
Status	The status of the funding.
Cash Drawer ID	The ID of the cash drawer.
Cash Drawer Transaction	The cash drawer transaction ID.
Cash Drawer TX Status	The cash drawer transaction status.
Cash Drawer Terminal ID	The ID of the cash drawer terminal.
Payment ID	The ID of the payment.
Agent ID	The ID of the agent.
Merchant Processor	The name of the merchant processor.
Merchant Payment Status	The merchant processor payment status.
Authorization Type	The type of funding authorization.
Reversal Reason	The reason for reversing the payment.
Reversal Date	The date of the reversal.
Reversal Code	The reversal code.
Reversal Comment	The text description of the reversal.
Date	The date the enhanced funding was applied.
Amount	The dollar amount of the enhanced funding.

SQL Query Example

 with loan_info as (
    SELECT le.display_id account,
        le.id,
        case 
            when ce.customer_type = 'customer.type.company' then ce.company_name
            else concat(IFNULL(ce.first_name, '') ," ",IFNULL(ce.middle_name, '')," ",IFNULL(ce.last_name, '')) 
        end primary_customer,
        CONCAT(IFNULL(col.a, '')," ",col.b," ",IFNULL(col.c, '')," ",IFNULL(col.d, '')) AS collateral
    from loan_entity le
        left join loan__customer lc on le.id = lc.loan_id and lc.customer_role = "loan.customerRole.primary"
        left join customer_entity ce on ce.id = lc.customer_id
        left join collateral_entity col on col.loan_id = le.id      
)
SELECT
    li.*,
    lfte.id AS transaction_id,
    IF(lfte.customer_id IS NOT NULL, 'Customer', 'Source Company') AS fund_who,
    fce.name AS 'category',
    CASE
        WHEN lfte.method = 'loan.funding.method.deposit' THEN 'Bank Account/ACH Deposit'
        WHEN lfte.method = 'loan.funding.method.credit' THEN 'Debit/Credit Card'
        WHEN lfte.method = 'loan.funding.method.transfer' THEN 'EFT Transfer'
        WHEN lfte.method = 'loan.funding.method.other' THEN 'Other'
    END
    AS 'Method',
    SUBSTRING_INDEX(lfte.status, ".", -1) AS 'Status',
    lfte.cash_drawer_id ,
    lfte.cash_drawer_tx_id cash_drawer_ransaction,
    lfte.cash_drawer_tx_status cash_drawer_tx_status,
    lfte.cash_drawer_terminal_id AS cash_drawer_terminal_id,
    lfte.payment_id ,
    lfte.agent_id ,
    lfte.merchant_tx_processor merchant_processor,
    lfte.merchant_tx_status merchant_payment_status,
    lfte.authorization_type,
    lfte.reversal_reason,
    lfte.reversal_date,
    lfte.reversal_code,
    lfte.reversal_comment,
    lfte.date,
    lfte.amount
FROM loan_funding_transaction_entity lfte 
    LEFT JOIN funding_category_entity fce ON fce.id = lfte.category_id
    left join loan_info li on li.id = lfte.loan_id

New accounts

The new accounts report provides a comprehensive overview of newly established loan accounts. It includes information about the accounts, primary customers, collateral information, and the specific terms and conditions of the loan setup, such as interest rates, payment frequencies, and underwriting fee details.

Report values

Column Name	Description
Account	The loan display ID.
Primary Customer	The full name of the primary borrower.
Collateral	A combination of collateral fields A, B, C, and D.
Term	The total number of payment periods on the loan.
Last Updated	A timestamp of when the loan setup values were last updated.
First Due Date	The date the first payment is due.
Interest Rate	The interest rate.
Discount	The dollar amount of the discount.
Underwriting	The dollar amount of the underwriting fee.
Frequency	The frequency at which payments will come due.
Modification	The ID of the loan modification the Loan Setup is associated to.

SQL Query Example

 with loan_info as (
    SELECT le.display_id account,
        le.id,
        case 
            when ce.customer_type = 'customer.type.company' then ce.company_name
            else concat(IFNULL(ce.first_name, '') ," ",IFNULL(ce.middle_name, '')," ",IFNULL(ce.last_name, '')) 
        end primary_customer,
        CONCAT(IFNULL(col.a, '')," ",col.b," ",IFNULL(col.c, '')," ",IFNULL(col.d, '')) AS collateral
    from loan_entity le
        left join loan__customer lc on le.id = lc.loan_id and lc.customer_role = "loan.customerRole.primary"
        left join customer_entity ce on ce.id = lc.customer_id
        left join collateral_entity col on col.loan_id = le.id      
),
new_account as (
    SELECT
        term,
        lastUpdated,
        first_due_date,
        interest_rate,
        discount,
        underwriting,
        frequency,
        Modification,
        loan_id
    FROM
        (
            SELECT
                lse.loan_term AS term,
                lse.lastUpdated,
                lse.first_payment_date AS first_due_date,
                lse.loan_rate AS interest_rate,
                lse.discount,
                lse.underwriting,
                SUBSTRING_INDEX(lse.payment_frequency,'.',-1) frequency,
                lse.loan_id,
                CASE
                    WHEN lse.mod_id = 0 THEN 'Original'
                    ELSE CAST(lse.mod_id AS CHAR) 
                END AS Modification,
                ROW_NUMBER() OVER (
                    PARTITION BY lse.loan_id
                    ORDER BY lse.mod_id DESC
                ) as rn 
            FROM
                loan_setup_entity lse
        ) AS RankedModifications
    WHERE
        rn = 1 
)
select li.account,
    li.primary_customer,
    li.collateral,
    na.term, 
    na.lastUpdated, 
    NULLIF(na.first_due_date, '0000-00-00 00:00:00') AS first_due_date,
    na.interest_rate, 
    na.discount, 
    na.underwriting, 
    na.frequency, 
    na.Modification
from new_account na left join loan_info li on na.loan_id = li.id
where na.lastUpdated = DATE_SUB(CURRENT_DATE(), interval 1 day)

Promises

The promises report provides a comprehensive overview of commitment tracking for loan accounts, specifically regarding promises to pay (PTP). It includes detailed information about accounts, primary customers, collateral information, and the status of specific promises, including fulfillment details and notes.

Report values

Column Name	Description
Account	The loan display ID.
Primary Customer	The full name of the primary borrower.
Collateral	A combination of collateral fields A, B, C, and D.
Promise ID	The promise ID.
Subject	The subject of the promise.
Note	An attached note describing the promise.
Amount	The dollar amount of the promise.
Type	The type of the promise.
Fulfilled	The fulfilled status of the promise.
Due Date	The promise due date.
Created	The timestamp the promise was created.
Logged By	The user who logged the promise
Fulfilled Date	The date the promise was fulfilled
Fulfilled By	The user who marked the promise fulfilled.

SQL Query Example

 with loan_info as (
    SELECT le.display_id account,
        le.id,
        case 
            when ce.customer_type = 'customer.type.company' then ce.company_name
            else concat(IFNULL(ce.first_name, '') ," ",IFNULL(ce.middle_name, '')," ",IFNULL(ce.last_name, '')) 
        end primary_customer,
        CONCAT(IFNULL(col.a, '')," ",col.b," ",IFNULL(col.c, '')," ",IFNULL(col.d, '')) AS collateral
    from loan_entity le
        left join loan__customer lc on le.id = lc.loan_id and lc.customer_role = "loan.customerRole.primary"
        left join customer_entity ce on ce.id = lc.customer_id
        left join collateral_entity col on col.loan_id = le.id      
)
select li.account,
    li.primary_customer,
    li.collateral,
    lpe.id Promise_id,
    lpe.subject,
    lpe.note,
    lpe.amount,
    lpe.`type`,
    lpe.fulfilled,
    lpe.due_date,
    lpe.created,
    lpe.logged_by,
    lpe.fulfilled_date,
    lpe.fulfilled_by 
from loan_promise_entity lpe 
    left join loan_info li on lpe.loan_id = li.id

Charge-off Detail

The charge-off Detail report provides a comprehensive overview of loan accounts that have undergone a charge-off or recovery process. It includes information about accounts, primary customers, collateral information, and the specific financial breakdown of both the charge-off amounts and any subsequent recoveries applied.

Report values

Column Name	Description
Account	The loan display ID.
Primary Customer	The full name of the primary borrower.
Collateral	A combination of collateral fields A, B, C, and D.
Date	The date of the charge off.
Payment ID	The associated payment ID.
Credit ID	The associated credit ID.
Type	The type of the charge off.
Charge Off Amount	The dollar amount of the charge off.
Recovery Amount	The dollar amount of recovery.

SQL Query Example

 with loan_info as (
    SELECT le.display_id account,
        le.id,
        case 
            when ce.customer_type = 'customer.type.company' then ce.company_name
            else concat(IFNULL(ce.first_name, '') ," ",IFNULL(ce.middle_name, '')," ",IFNULL(ce.last_name, '')) 
        end primary_customer,
        CONCAT(IFNULL(col.a, '')," ",col.b," ",IFNULL(col.c, '')," ",IFNULL(col.d, '')) AS collateral
    from loan_entity le
        left join loan__customer lc on le.id = lc.loan_id and lc.customer_role = "loan.customerRole.primary"
        left join customer_entity ce on ce.id = lc.customer_id
        left join collateral_entity col on col.loan_id = le.id      
)
select
    li.account,
    li.primary_customer ,
    li.collateral,
    lct.`date` ,
    lct.payment_id,
    lct.credit_id,
    SUBSTRING_INDEX(lct.`type`, '.', -1) type,
    lct.charge_off_amount,
    lct.recovery_amount
from loan_chargeoff__transactions lct left join loan_info li on lct.entity_id = li.id
where lct.entity_type = 'Entity.Loan'

Charge off summary

The ___ report provides a comprehensive overview of ___.

Report values

Column Name	Description

SQL Query Example

 with loan_info as (
    SELECT le.display_id account,
        le.id,
        case 
            when ce.customer_type = 'customer.type.company' then ce.company_name
            else concat(IFNULL(ce.first_name, '') ," ",IFNULL(ce.middle_name, '')," ",IFNULL(ce.last_name, '')) 
        end primary_customer,
        CONCAT(IFNULL(col.a, '')," ",col.b," ",IFNULL(col.c, '')," ",IFNULL(col.d, '')) AS collateral
    from loan_entity le
        left join loan__customer lc on le.id = lc.loan_id and lc.customer_role = "loan.customerRole.primary"
        left join customer_entity ce on ce.id = lc.customer_id
        left join collateral_entity col on col.loan_id = le.id      
)
select
    li.account,
    li.primary_customer ,
    li.collateral,
    max(lct.`date`) latest_date,
    count(lct.payment_id) number_of_payment_id,
    count(lct.credit_id) number_of_credit_id,
    SUBSTRING_INDEX(lct.`type`, '.', -1) type,
    sum(lct.charge_off_amount) sum_charge_off_amount,
    sum(lct.recovery_amount) sum_recovery_amount
from loan_chargeoff__transactions lct left join loan_info li on lct.entity_id = li.id
where lct.entity_type = 'Entity.Loan' 
group by li.account,
    li.primary_customer,
    li.collateral,
    lct.`type`

Interest adjustment

The interest rate adjustments report provides a comprehensive overview of changes made to the interest rates of loan accounts. It includes information about the accounts, primary customers, collateral information, and the specifics of each adjustment, such as the date, category, and whether the rate was increased or decreased.

Report values

Column Name	Description
Account	The loan display ID.
Primary Customer	The full name of the primary borrower.
Collateral	A combination of collateral fields A, B, C, and D.
Title	The title of the interest rate adjustment.
Logged Date	The date the interest rate adjustment was logged.
Apply Date	The date the interest rate adjustment was applied.
Category	The category of the interest rate adjustment.
Amount	The dollar amount of the interest rate adjustment.
Type	The type of the interest rate adjustment.

SQL Query Example

 with loan_info as (
    SELECT le.display_id account,
        le.id,
        case 
            when ce.customer_type = 'customer.type.company' then ce.company_name
            else concat(IFNULL(ce.first_name, '') ," ",IFNULL(ce.middle_name, '')," ",IFNULL(ce.last_name, '')) 
        end primary_customer,
        CONCAT(IFNULL(col.a, '')," ",col.b," ",IFNULL(col.c, '')," ",IFNULL(col.d, '')) AS collateral
    from loan_entity le
        left join loan__customer lc on le.id = lc.loan_id and lc.customer_role = "loan.customerRole.primary"
        left join customer_entity ce on ce.id = lc.customer_id
        left join collateral_entity col on col.loan_id = le.id      
)
SELECt li.account,
    li.primary_customer,
    li.collateral,
    irae.title as 'Title',
    irae.`date` as 'Logged Date',
    irae.`date` as 'Apply Date',
    ce.title as 'Category',
    irae.amount as 'Amount',
    SUBSTRING_INDEX(irae.`type`, '.', -1) as 'Type'
FROM interest_rate_adjustment_entity irae 
    left JOIN category_entity ce ON ce.id = irae.category_id and ce.entity_type = 'Entity.InterestAdjustment'
    left join loan_info li on irae.entity_id = li.id AND irae.entity_type = 'Entity.Loan'

Advancements

The advancements report provides a comprehensive overview of funds advanced to loan accounts after the initial funding event. It includes information about the accounts, primary customers, collateral information, and the specific breakdown of each advancement, including the amount, date, and how it was applied to the loan.

Report values

Column Name	Description
Account	The loan display ID.
Primary Customer	The full name of the primary borrower.
Collateral	A combination of collateral fields A, B, C, and D.
ID	The ID of the advancement.
Title	The title of the advancement.
Logged Date	The date the advancement was logged.
Apply Date	The apply date of the advancement.
Type	The type of the advancement.
Amount	The amount of the advancement.
Category	The category of the advancement.
Application	The application type of the advancement.

SQL Query Example

 with loan_info as (
    SELECT le.display_id account,
        le.id,
        case 
            when ce.customer_type = 'customer.type.company' then ce.company_name
            else concat(IFNULL(ce.first_name, '') ," ",IFNULL(ce.middle_name, '')," ",IFNULL(ce.last_name, '')) 
        end primary_customer,
        CONCAT(IFNULL(col.a, '')," ",col.b," ",IFNULL(col.c, '')," ",IFNULL(col.d, '')) AS collateral
    from loan_entity le
        left join loan__customer lc on le.id = lc.loan_id and lc.customer_role = "loan.customerRole.primary"
        left join customer_entity ce on ce.id = lc.customer_id
        left join collateral_entity col on col.loan_id = le.id      
)
SELECT li.account,
    li.primary_customer,
    li.collateral,
    lae.id,
    lae.title,
    lae.created logged_date,
    lae.`date` apply_date,
    "Advancment" type,
    lae.amount,
    ace.title category,
    "Principal" application
FROM loan_advancement_entity lae 
    left join advance_category_entity ace on lae.category = ace.id
    left join loan_info li on li.id = lae.entity_id 
where lae.entity_type = "Entity.Loan"

Credits

The credits report provides a comprehensive overview of credit transactions applied to loan accounts. It includes information about the accounts, primary customers, collateral information, and the breakdown of credit amounts applied to interest, principal, discounts, and fees.

Report Values

Column Name	Description
Account	The loan display ID.
Primary Customer	The full name of the primary borrower.
Collateral	A combination of Collateral fields A, B, C, and D.
ID	The ID of the credit.
Title	The title of the credit.
Logged Date	The timestamp of when the credit was logged.
Apply Date	The date the credit was applied.
Credit	A hardcoded string returning "credit".
Amount	The total dollar amount of the credit.
Category	The category of the credit.
Application	The application type of the credit.
Interest	Portions of the transaction applied to the loan's interest.
Principal	Portions of the transaction applied to the loan's principal balance.
Discount	Portions of the transaction applied to the loan's discount.
Fees	Portions of the transaction applied to loan fees; relevant for payments, forecasted payments, or credits.
Date	The effective date the transaction applies to the loan account.

SQL Query Example

with loan_info as (
    SELECT le.display_id account,
        le.id,
        case 
            when ce.customer_type = 'customer.type.company' then ce.company_name
            else concat(IFNULL(ce.first_name, '') ," ",IFNULL(ce.middle_name, '')," ",IFNULL(ce.last_name, '')) 
        end primary_customer,
        CONCAT(IFNULL(col.a, '')," ",col.b," ",IFNULL(col.c, '')," ",IFNULL(col.d, '')) AS collateral
    from loan_entity le
        left join loan__customer lc on le.id = lc.loan_id and lc.customer_role = "loan.customerRole.primary"
        left join customer_entity ce on ce.id = lc.customer_id
        left join collateral_entity col on col.loan_id = le.id      
),
credit as ( 
    select lce.id,
        lce.title,
        lce.created logged_date,
        lce.date apply_date,
        "credit" credit,
        lce.amount,
        cce.title category,
        cpte.title application
    from loan_credit_entity lce 
        left join credit_category_entity cce on lce.category = cce.id 
        left join custom_payment_type_entity cpte on lce.payment_type = cpte.id 
)
select li.account,
    li.primary_customer, 
    li.collateral, 
    c.id, 
    c.title, 
    c.logged_date, 
    c.apply_date, 
    c.credit, 
    c.amount, 
    c.category, 
    c.application,
    tx.payment_i interest,
    tx.payment_p prinicpal,
    tx.payment_d discount,  
    tx.payment_f fees,
    tx.`date` 
from loan_tx tx 
    left join credit c on tx.payment_id = c.id
    left join loan_info li on tx.entity_id = li.id
where tx.`type` = "credit"
    and tx.entity_type = "Entity.Loan"

Escrow transaction

The escrow transaction report provides a comprehensive overview of escrow activity across loan accounts. It includes detailed information about the accounts, primary customers, collateral details, and a full breakdown of escrow transactions, including deposits, withdrawals, vendor information, and ending balances.

Report Values

Column Name	Description
Account	The loan display ID.
Primary Customer	The full name of the primary borrower.
Collateral	A combination of Collateral fields A, B, C, and D
Escrow Name	The name or title of the escrow transaction.
Date	The date the transaction occurred.
Transaction Type	The type of transaction, such as a deposit or withdrawal.
Amount	The total dollar amount of the escrow transaction.
Category	The category of the escrow transaction.
Vendor	The name of the vendor associated with the transaction.
Description	A text description providing additional context for the transaction.
Escrow Balance	The ending balance of the escrow account following the transaction.

SQL Query Example

with loan_info as (
    SELECT le.display_id account,
        le.id,
        case 
            when ce.customer_type = 'customer.type.company' then ce.company_name
            else concat(IFNULL(ce.first_name, '') ," ",IFNULL(ce.middle_name, '')," ",IFNULL(ce.last_name, '')) 
        end primary_customer,
        CONCAT(IFNULL(col.a, '')," ",col.b," ",IFNULL(col.c, '')," ",IFNULL(col.d, '')) AS collateral
    from loan_entity le
        left join loan__customer lc on le.id = lc.loan_id and lc.customer_role = "loan.customerRole.primary"
        left join customer_entity ce on ce.id = lc.customer_id
        left join collateral_entity col on col.loan_id = le.id      
)
select li.account,
    li.primary_customer,
    li.collateral,
    ese.title escrow_namem,
    ete.`date`,
    substring_index(ete.`type`,'.',-1) trasaction_type,
    ete.amount,
    etce.title category,
    ve.vendor_name vendor,
    ete.description,
    et.balance escrow_balance
from escrow_transaction_entity ete 
    left join vendor_entity ve on ete.vendor_id = ve.id
    left join escrow_subset_entity ese on ete.subset = ese.id
    left join escrow_tx_category_entity etce on ete.category = etce.id
    left join escrow__transactions et on et.escrow_transaction_id = ete.id
    left join loan_info li on li.id = ete.loan_id

APD adjustments

The APD adjustments report provides a comprehensive overview of Amount Past Due (APD) adjustments applied to loan accounts. It includes information about the accounts, primary customer names, collateral descriptions, and the specific nature of each adjustment, such as the date, type, and dollar amount applied.

Report Values

Column Name	Description
Account	Loan display ID. This ID is assigned by the user and is displayed as the ID for the loan inside the LoanPro user interface.
Primary Customer	The full name of the primary borrower.
Collateral	A combination of collateral fields A, B, C, and D.
Apply Date	The date the APD adjustment is applied to the loan.
Type	The category of the adjustment.
Dollar Amount	The dollar amount of the APD adjustment.

SQL Query Example

with loan_info as (
    SELECT le.display_id account,
        le.id,
        case 
            when ce.customer_type = 'customer.type.company' then ce.company_name
            else concat(IFNULL(ce.first_name, '') ," ",IFNULL(ce.middle_name, '')," ",IFNULL(ce.last_name, '')) 
        end primary_customer,
        CONCAT(IFNULL(col.a, '')," ",col.b," ",IFNULL(col.c, '')," ",IFNULL(col.d, '')) AS collateral
    from loan_entity le
        left join loan__customer lc on le.id = lc.loan_id and lc.customer_role = "loan.customerRole.primary"
        left join customer_entity ce on ce.id = lc.customer_id
        left join collateral_entity col on col.loan_id = le.id      
)
select li.account,
    li.primary_customer,
    li.collateral,
    laae.date as apply_date,
    CASE laae.type
        WHEN 'loan.apd.adjust.type.fixed' THEN 'Fixed Dollar Amount'
        WHEN 'loan.apd.adjust.type.zero' THEN 'Zero Balance'
    END as type,
    laae.dollar_amount
from loan_apd_adjustment_entity laae 
    join loan_info li on laae.entity_id = li.id
where laae.deleted = 0

DPD adjustments

The DPD adjustments report provides a comprehensive overview of Days Past Due (DPD) adjustments applied to loan accounts. It includes information about the accounts, primary customers, collateral information, and the specific adjustment dates to track account status changes.

Report Values

Column Name	Description
Account	The loan display ID.
Primary Customer	The full name of the primary borrower
Collateral	A combination of collateral fields A, B, C, and D.
Apply Date	The date the DPD adjustment is applied to the loan.

SQL Query Example

 with loan_info as (
    SELECT le.display_id account,
        le.id,
        case 
            when ce.customer_type = 'customer.type.company' then ce.company_name
            else concat(IFNULL(ce.first_name, '') ," ",IFNULL(ce.middle_name, '')," ",IFNULL(ce.last_name, '')) 
        end primary_customer,
        CONCAT(IFNULL(col.a, '')," ",col.b," ",IFNULL(col.c, '')," ",IFNULL(col.d, '')) AS collateral
    from loan_entity le
        left join loan__customer lc on le.id = lc.loan_id and lc.customer_role = "loan.customerRole.primary"
        left join customer_entity ce on ce.id = lc.customer_id
        left join collateral_entity col on col.loan_id = le.id      
)
select li.account,
    li.primary_customer,
    li.collateral,
    ldae.date as apply_date
from loan_dpd_adjustment_entity ldae
    join loan_info li on ldae.entity_id = li.id
where ldae.deleted = 0

Due date changes

The due date changes report provides a comprehensive overview of manual adjustments made to loan payment due dates. It includes information about the accounts, primary customers, and a tracking history of original versus new due dates to maintain an accurate audit trail of schedule modifications.

Report values

Column Name	Description
Account	The loan display ID.
Primary Customer	The full name of the primary borrower.
Collateral	A combination of collateral fields A, B, C, and D.
ID	The ID of the due date change.
Original Date	The due date prior to the changed date in the schedule.
New Date	The date that the changed date was changed to.
Changed Date	The due date from the schedule that was changed.
Last Updated	Timestamp of when the entity was last updated in the system.

SQL Query Example

 with loan_info as (
    SELECT le.display_id account,
        le.id,
        case 
            when ce.customer_type = 'customer.type.company' then ce.company_name
            else concat(IFNULL(ce.first_name, '') ," ",IFNULL(ce.middle_name, '')," ",IFNULL(ce.last_name, '')) 
        end primary_customer,
        CONCAT(IFNULL(col.a, '')," ",col.b," ",IFNULL(col.c, '')," ",IFNULL(col.d, '')) AS collateral
    from loan_entity le
        left join loan__customer lc on le.id = lc.loan_id and lc.customer_role = "loan.customerRole.primary"
        left join customer_entity ce on ce.id = lc.customer_id
        left join collateral_entity col on col.loan_id = le.id      
)
select li.account,
    li.primary_customer,
    li.collateral,
    ldce.id,
    ldce.original_date,
    ldce.new_date,
    ldce.changed_date,
    ldce.lastUpdated 
from loan_duedate_change_entity ldce left join loan_info li on ldce.entity_id = li.id
    where ldce.entity_type = 'Entity.Loan'

Suspend/Resume interest

The suspend/resume interest report provides a comprehensive overview of actions taken to halt or restart interest accrual on loan accounts. It includes information about the accounts, primary customers, collateral, and the specific date and type of interest status change.

Report values

Column Name	Description
Account	The loan display ID.
Primary Customer	The full name of the primary borrower.
Collateral	A combination of collateral fields A, B, C, and D
Date	The specific date the transaction (suspend or resume) occurred.
Type	Indicates the intended action for interest.

SQL Query Example

 with loan_info as (
    SELECT le.display_id account,
        le.id,
        case 
            when ce.customer_type = 'customer.type.company' then ce.company_name
            else concat(IFNULL(ce.first_name, '') ," ",IFNULL(ce.middle_name, '')," ",IFNULL(ce.last_name, '')) 
        end primary_customer,
        CONCAT(IFNULL(col.a, '')," ",col.b," ",IFNULL(col.c, '')," ",IFNULL(col.d, '')) AS collateral
    from loan_entity le
        left join loan__customer lc on le.id = lc.loan_id and lc.customer_role = "loan.customerRole.primary"
        left join customer_entity ce on ce.id = lc.customer_id
        left join collateral_entity col on col.loan_id = le.id      
)
select li.account,
    li.primary_customer,
    li.collateral,
    lt.`date`,
    lt.title type 
from loan_tx lt left join loan_info li on lt.entity_id = li.id 
where lt.`type` in ('suspendInterest','resumeInterest')
    and lt.entity_type = "Entity.Loan"

Monthly forecasted payments

The ___ report provides a comprehensive overview of ___.

Report values

Column Name	Description

SQL Query Example

 with loan_info as (
	SELECT le.display_id account,
		le.id,
		concat(IFNULL(ce.first_name, '') ," ",IFNULL(ce.middle_name, '')," ",IFNULL(ce.last_name, '')) primary_customer,
		CONCAT(IFNULL(col.a, '')," ",col.b," ",IFNULL(col.c, '')," ",IFNULL(col.d, '')) AS collateral
	from loan_entity le
		left join loan__customer lc on le.id = lc.loan_id and lc.customer_role = "loan.customerRole.primary"
		left join customer_entity ce on ce.id = lc.customer_id
		left join collateral_entity col on col.loan_id = le.id 		
)
select li.account,
	li.primary_customer,
	li.collateral,
	date(lt.`date`) as "Date",
	lt.payment_amount as "Amount",
	lt.payment_i as "Interest",
	lt.payment_p as "Principal",
	lt.payment_d as "Discount",
	lt.payment_f as "Fees",
	lt.payment_e as "Escrow"
from
	loan_tx lt
join loan_entity le 
	on	le.id = lt.entity_id
	and lt.entity_type = 'Entity.Loan'
left join loan_info li on lt.entity_id = li.id 
where lt.type = 'forecastedPayment' 
	and lt.date between
    date_format(curdate(), '%Y-%m-01')
	    and last_day(curdate())
		and le.deleted = 0
group by
	lt.id
order by
	lt.date

Daily snapshot

The daily snapshot report provides a comprehensive overview of the current status and financial health of all loan accounts as of a specific date. It includes information about the account, primary customer information, current balances (principal and payoff), delinquency tracking (DPD and amount past due), and credit reporting statuses.

Report values

Column Name	Description
Archive Date	The current date for which the snapshot was generated.
Account	The loan display ID.
Primary Customer	The full name of the primary borrower.
Collateral	A combination of collateral fields A, B, C, and D.
Archive Stoplight	The color of the stoplight for the loan, based on user-defined rules.
Archive Principal	The unpaid portion of the principal balance on the loan.
Archive Aging	The number of days elapsed since the loan's contract date.
Archive PD	The total amount currently due on the loan.
Archive PD 30	The portion of the amount due that has been past due for more than 30 days.
Archive DPD	Days Past Due. The number of days since the earliest unpaid amount came due.
Archive Status	The current title or name of the loan status.
Archive Credit Status	The credit status that will be reported if the loan is included in credit reporting.
Archive Payoff	The total amount required to pay the loan off, including principal, interest, fees, and escrow.
Archive Per Diem	The calculated daily interest amount based on the principal balance and interest rate.
Archive Chargeoff	The total amount that has been charged off on the loan.
Archive Last Current	The most recent date on which the loan had no unpaid amount due.
Archive Activity	The timestamp of the last manual update made to the loan by a user.

SQL Query Example

 with loan_info as (
    SELECT le.display_id account,
        le.id,
        case 
            when ce.customer_type = 'customer.type.company' then ce.company_name
            else concat(IFNULL(ce.first_name, '') ," ",IFNULL(ce.middle_name, '')," ",IFNULL(ce.last_name, '')) 
        end primary_customer,
        CONCAT(IFNULL(col.a, '')," ",col.b," ",IFNULL(col.c, '')," ",IFNULL(col.d, '')) AS collateral
    from loan_entity le
        left join loan__customer lc on le.id = lc.loan_id and lc.customer_role = "loan.customerRole.primary"
        left join customer_entity ce on ce.id = lc.customer_id
        left join collateral_entity col on col.loan_id = le.id      
)
select CURRENT_DATE() archive_date, 
    li.account, 
    li.primary_customer primary_customer, 
    li.collateral, 
    CASE 
        when lsa.stoplight like '%stoplight.display.red' THEN 'red'
        when lsa.stoplight like '%stoplight.display.green' THEN 'green'
        when lsa.stoplight like '%stoplight.display.yellow' THEN 'yellow'
    END as archive_stoplight,
    lsa.principal_balance archive_principal,
    lsa.loan_age archive_aging, 
    lsa.amount_due archive_PD,
    lsa.amount_past_due_30 archive_PD_30, 
    lsa.days_past_due archive_DPD,
    lsa.loan_status_text archive_status,
    CASE
        WHEN lsa.credit_status LIKE '%loan.creditstatus.11' THEN '11 – Current Account (0-29 days past the due date)'
        WHEN lsa.credit_status LIKE '%loan.creditstatus.71' THEN '71 – Account 30-59 days past the due date'
        WHEN lsa.credit_status LIKE '%loan.creditstatus.78' THEN '78 – Account 60-89 days past due date'
        WHEN lsa.credit_status LIKE '%loan.creditstatus.80' THEN '80 – Account 90 – 119 days past due date'
        WHEN lsa.credit_status LIKE '%loan.creditstatus.82' THEN '82 – Account 120 – 149 days past due date'
        WHEN lsa.credit_status LIKE '%loan.creditstatus.83' THEN '83 – Account 150 – 179 days past due date'
        WHEN lsa.credit_status LIKE '%loan.creditstatus.84' THEN '84 – Account 180 days or more past the due date'
        WHEN lsa.credit_status LIKE '%loan.creditstatus.0'  THEN '0 – Auto – System managed status'
        WHEN lsa.credit_status LIKE '%loan.creditstatus.05' THEN '05 – Account transferred to another office'
        WHEN lsa.credit_status LIKE '%loan.creditstatus.13' THEN '13 – Paid or closed account/zero balance'
        WHEN lsa.credit_status LIKE '%loan.creditstatus.99' THEN '99 – Do Not Send'
        WHEN lsa.credit_status LIKE '%loan.creditstatus.61' THEN '61 – Account paid in full, was a voluntary surrender'
        WHEN lsa.credit_status LIKE '%loan.creditstatus.62' THEN '62 – Account paid in full, was a collection account'
        WHEN lsa.credit_status LIKE '%loan.creditstatus.63' THEN '63 – Account paid in full, was repossession'
        WHEN lsa.credit_status LIKE '%loan.creditstatus.93' THEN '93 – Account assigned to internal or external collections'
        WHEN lsa.credit_status LIKE '%loan.creditstatus.95' THEN '95 – Voluntary Surrender'
        WHEN lsa.credit_status LIKE '%loan.creditstatus.96' THEN '96 – Merchandise was repossessed; there may be a balance due'
        WHEN lsa.credit_status LIKE '%loan.creditstatus.64' THEN '64 – Account paid in full, was a write-off'
        WHEN lsa.credit_status LIKE '%loan.creditstatus.97' THEN '97 – Unpaid balance reported as a loss (write-off)'
        WHEN lsa.credit_status LIKE '%loan.creditstatus.DA' THEN 'DA – Deletes entire account (for reasons other than fraud)'
        WHEN lsa.credit_status LIKE '%loan.creditstatus.DF' THEN 'DF – Delete entire account due to confirmed fraud (fraud investigation completed)'
    END AS archive_credit_status,
    lsa.payoff archive_payoff, 
    lsa.perdiem archive_per_diem,
    lsa.net_charge_off archive_chargeoff, 
    lsa.date_last_current archive_last_current,
    lsa.last_human_activity archive_activity
from loan_status_archive lsa 
left join loan_info li on lsa.loan_id = li.id
group by lsa.loan_id

Reverse calculated

The reverse calculation report provides a comprehensive overview of loan accounts with a focus on reconciling balances and aging. It includes information about accounts, customer details, current principal balances, and a detailed breakdown of amounts past due across interest, principal, and fees.

Report values

Column Name	Description
Account	The loan display ID.
Primary Customer	The full name of the primary borrower.
Collateral	A combination of collateral fields A, B, C, and D.
Principal Balance	The current total unpaid principal balance on the loan.
Payoff Fees	Total fees required to be paid as part of a full loan payoff.
Aging	The age of the loan account, typically represented in days.
PD	The total amount past due (specifically for a 30-day period).
DPD	Days Past Due. The number of days since the earliest unpaid amount came due.
Due Discount	The amount of discount currently due on the account.
Due Interest	The total amount of interest that is currently due and unpaid.
Due Fees	The total amount of fees that are currently due and unpaid.
Due Principal	The portion of the principal balance that is currently due and unpaid.
Daily Accrued Interest	The specific amount of interest that accrues on the loan account daily.

SQL Query Example

 with loan_info as (
    SELECT le.display_id account,
        le.id,
        case 
            when ce.customer_type = 'customer.type.company' then ce.company_name
            else concat(IFNULL(ce.first_name, '') ," ",IFNULL(ce.middle_name, '')," ",IFNULL(ce.last_name, '')) 
        end primary_customer,
        CONCAT(IFNULL(col.a, '')," ",col.b," ",IFNULL(col.c, '')," ",IFNULL(col.d, '')) AS collateral
    from loan_entity le
        left join loan__customer lc on le.id = lc.loan_id and lc.customer_role = "loan.customerRole.primary"
        left join customer_entity ce on ce.id = lc.customer_id
        left join collateral_entity col on col.loan_id = le.id      
)
select li.account,
    li.primary_customer,
    li.collateral,
    lrsa.principal_balance,
    lrsa.payoff_fees,
    lrsa.loan_age aging,
    lrsa.amount_past_due_30 pd,
    lrsa.days_past_due as dpd,
    lrsa.due_discount,
    lrsa.due_interest,
    lrsa.due_fees,
    lrsa.due_principal,
    lrsa.daily_accrued_interest
from loan_reverse_status_archive lrsa left join loan_info li on lrsa.loan_id = li.id

Calculated interest accrual

The daily interest accrual report provides a comprehensive overview of interest generated across loan accounts on a daily basis. It includes information about the account, primary customer names, collateral descriptions, and a breakdown of both the current principal due and the exact interest amount accrued for the reporting date.

Report values

Column Name	Description
Date	The current date for which the interest accrual is being reported.
Account	The loan display ID.
Primary Customer	The full name of the primary borrower.
Collateral	A combination of collateral fields A, B, C, and D.
Principal	The amount of principal due on the loan, representing the amount that has come due but has not yet been paid.
Interest Accrued Today	The actual amount of interest that accrued on the loan for the reporting date.

SQL Query Example

 with loan_info as (
    SELECT le.display_id account,
        le.id,
        concat(IFNULL(ce.first_name, '') ," ",IFNULL(ce.middle_name, '')," ",IFNULL(ce.last_name, '')) primary_customer,
        CONCAT(IFNULL(col.a, '')," ",col.b," ",IFNULL(col.c, '')," ",IFNULL(col.d, '')) AS collateral
    from loan_entity le
        left join loan__customer lc on le.id = lc.loan_id and lc.customer_role = "loan.customerRole.primary"
        left join customer_entity ce on ce.id = lc.customer_id
        left join collateral_entity col on col.loan_id = le.id      
)
select CURRENT_DATE(),
    li.account,
    li.primary_customer,
    li.collateral,
    lsa.due_principal principal,
    lsa.interest_accrued_today
from loan_status_archive lsa left join loan_info li on lsa.loan_id = li.id
where lsa.`date` = CURRENT_DATE()

Delinquency detail

The delinquency detail report provides a comprehensive overview of overdue loan accounts and their current financial state. It includes information about accounts, primary customers, collateral details, and a breakdown of amounts past due to assist in collections and risk management.

Report values

Column Name	Description
Account	The loan display ID.
Primary Customer	The full name of the primary borrower.
Payoff	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.
Collateral	A combination of collateral field A, B, C, and D.
Days Past Due	The number of days the loan is past due.
Amount Due	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.
Amount Past Due 30	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.
Next Payment Date	The next date on which a payment will come due. This date will always be in the future of the date for this record.
P I Past Due	The sum of due principal and due interest.

SQL Query Example

 with loan_info as (
	SELECT le.display_id account,
		le.id,
		case 
			when ce.customer_type = 'customer.type.company' then ce.company_name
			else concat(IFNULL(ce.first_name, '') ," ",IFNULL(ce.middle_name, '')," ",IFNULL(ce.last_name, '')) 
		end primary_customer,
		CONCAT(IFNULL(col.a, '')," ",col.b," ",IFNULL(col.c, '')," ",IFNULL(col.d, '')) AS collateral
	from loan_entity le
		left join loan__customer lc on le.id = lc.loan_id and lc.customer_role = "loan.customerRole.primary"
		left join customer_entity ce on ce.id = lc.customer_id
		left join collateral_entity col on col.loan_id = le.id 		
)
select li.account, 
	li.primary_customer, 
	lsa.payoff,
	li.collateral, 
	lsa.days_past_due,
	lsa.amount_due,
	lsa.amount_past_due_30,
	lsa.next_payment_date,
	(lsa.due_principal +lsa.due_interest) p_i_past_due
from loan_status_archive lsa 
	left join loan_info li on lsa.loan_id = li.id
where lsa.`date` = CURRENT_DATE()

Delinquency summary

The delinquency summary report provides a comprehensive overview of overdue loan accounts aggregated by delinquency severity. It includes information about the total number of accounts, cumulative payoff amounts, and a detailed breakdown of amounts past due within specific days-past-due (DPD) groups to assist in portfolio risk assessment.

Report values

Column Name	Description
Days Past Due Group	A classification of the loan's delinquency status based on the number of days elapsed since the oldest unpaid scheduled payment. This category groups accounts into "buckets" to represent the severity of the payment delay.
Total Accounts	The count of unique loan records falling within the specific Days Past Due Group.
Total Payoff	The sum of the "Payoff" for all loans in the group. This is the payoff amount on the loan, including all principal, interest, fees, and escrow required for full satisfaction.
Total Amount Due	The sum of the "amount due" for all loans in the group. This represents the total amount that has come due but remains unpaid.
Total Amount Past Due 30	The sum of the "amount past due" for all loans in the group that has been overdue for more than 30 days.
Total P I Past Due	The sum of the "principal and interest due" for all loans in the group, totaling due principal and due interest.

SQL Query Example

 with loan_info as (
    SELECT le.display_id account,
        le.id,
        case 
            when ce.customer_type = 'customer.type.company' then ce.company_name
            else concat(IFNULL(ce.first_name, '') ," ",IFNULL(ce.middle_name, '')," ",IFNULL(ce.last_name, '')) 
        end primary_customer,
        CONCAT(IFNULL(col.a, '')," ",col.b," ",IFNULL(col.c, '')," ",IFNULL(col.d, '')) AS collateral
    from loan_entity le
        left join loan__customer lc on le.id = lc.loan_id and lc.customer_role = "loan.customerRole.primary"
        left join customer_entity ce on ce.id = lc.customer_id
        left join collateral_entity col on col.loan_id = le.id      
)
SELECT
    CASE
        WHEN lsa.days_past_due = 0 THEN 'Current (0 Days)'
        WHEN lsa.days_past_due BETWEEN 1 AND 14 THEN '1-14 Days Past Due'
        WHEN lsa.days_past_due BETWEEN 15 AND 29 THEN '15-29 Days Past Due'
        WHEN lsa.days_past_due BETWEEN 30 AND 59 THEN '30-59 Days Past Due'
        WHEN lsa.days_past_due BETWEEN 60 AND 89 THEN '60-89 Days Past Due'
        WHEN lsa.days_past_due >= 90 THEN '90+ Days Past Due'
    END AS days_past_due_group,
    COUNT(li.account) AS total_accounts,
    SUM(lsa.payoff) AS total_payoff,
    SUM(lsa.amount_due) AS total_amount_due,
    SUM(lsa.amount_past_due_30) AS total_amount_past_due_30,
    SUM(lsa.due_principal + lsa.due_interest) AS total_p_i_past_due
FROM
    loan_status_archive lsa
LEFT JOIN
    loan_info li ON lsa.loan_id = li.id
WHERE
    lsa.`date` = CURRENT_DATE()
GROUP BY
    days_past_due_group

Paid off loans

The paid off report provides a comprehensive overview of loan accounts that have reached a closed status, typically through full satisfaction of the debt. It includes detailed information regarding accounts, primary customer names, collateral details, and a complete financial breakdown of the final payments, including principal, interest, fees, and escrow amounts applied upon payoff.

Report values

Column Name	Description
Account	The loan display ID.
ID	The loan system ID.
Primary Customer	The full name of the primary borrower.
Collateral	A combination of collateral fields A, B, C, and D.
Loan Product	This field contains an array of the IDs of the portfolios with which the loan is associated.
Payoff Date	The specific date the account was closed, if applicable.
Payoff Reason	The loan sub-status associated with the loan.
Principal Paid	The portion of the transaction amount applied specifically to the loan's principal balance.
Interest Paid	The portion of the transaction amount applied specifically to the loan's interest.
Fees Paid	The portion of the transaction amount applied specifically to loan fees.
Escrow Paid	The portion of the transaction amount applied specifically to the loan's escrow account.
Discount Paid	The portion of the transaction amount applied specifically to the loan's discount.
Total Received	The total amount of the final transaction received.
YTD Interest	The year-to-date interest applied to the loan through this transaction.
Original Loan Amount	The initial principal amount advanced, excluding the underwriting fee.
Original Final Maturity	The originally-calculated final payment date for the loan term.

SQL Query Example

 with loan_info as (
    SELECT le.display_id account,
        le.id,
        case 
            when ce.customer_type = 'customer.type.company' then ce.company_name
            else concat(IFNULL(ce.first_name, '') ," ",IFNULL(ce.middle_name, '')," ",IFNULL(ce.last_name, '')) 
        end primary_customer,
        CONCAT(IFNULL(col.a, '')," ",col.b," ",IFNULL(col.c, '')," ",IFNULL(col.d, '')) AS collateral
    from loan_entity le
        left join loan__customer lc on le.id = lc.loan_id and lc.customer_role = "loan.customerRole.primary"
        left join customer_entity ce on ce.id = lc.customer_id
        left join collateral_entity col on col.loan_id = le.id      
),
closed_off_loans as (
    select lse.loan_id,
        lse.closed_date Payoff_Date,
        lsa.portfolio_breakdown Loan_Product,
        lsa.loan_sub_status_text Payoff_Reason
    from loan_settings_entity lse 
        left join loan_status_archive lsa on lse.loan_id = lsa.loan_id 
            and lse.closed_date = lsa.`date`
    where lse.closed_date is not null and lse.closed_date != '0000-00-00'
),
payment_breakdown as (
    select 
        lt.entity_id,
        sum(lt.payment_p) Principal_Paid,           
        sum(lt.payment_i) Interest_Paid,     
        sum(lt.payment_f) Fees_Paid,                    
        sum(lt.payment_e) Escrow_Paid,  
        sum(lt.payment_d) Discount_Paid,
        sum(lt.payment_amount) Total_Received,
        sum(
            case 
                when year(lt.`date`) = YEAR(CURDATE()) then lt.payment_i
                else 0
            end
            ) YTD_Interest
    from loan_tx lt 
    where lt.entity_type = 'Entity.Loan'
    group by lt.entity_id 
),
og_loan_amt as (
    select *
    from
        (
            SELECT
                lse.loan_id,
                (lse.underwriting +lse.loan_amount) Original_loan_amount,
                lse.orig_final_payment_date Original_final_maturity,
                ROW_NUMBER() OVER (
                    PARTITION BY lse.loan_id
                    ORDER BY lse.mod_id DESC
                ) as rn 
            FROM
                loan_setup_entity lse
        ) AS RankedModifications
    WHERE
        rn = 1 
)
select li.account,
    li.id,
    li.primary_customer,
    li.collateral,
    cof.Loan_Product,
    cof.Payoff_Date,
    cof.payoff_reason,
    pb.Principal_Paid, 
    pb.Interest_Paid, 
    pb.Fees_Paid, 
    pb.Escrow_Paid, 
    pb.Discount_Paid, 
    pb.Total_Received, 
    pb.YTD_Interest,
    ola.Original_loan_amount, 
    ola.Original_final_maturity
from closed_off_loans cof 
    left join loan_info li on cof.loan_id = li.id 
    left join payment_breakdown pb on cof.loan_id = pb.entity_id
    left join og_loan_amt ola on  cof.loan_id = ola.loan_id

Accessing reports

To access and begin using line of credit reports, you must reach out to your LoanPro contact. Once you’ve set up this reporting feature, the reports will be uploaded to the location of your choosing. The options include:

Report File Hub (preferred)
Secure File Transfer Protocol (SFTP) connection
Google Drive

If you choose a delivery method other than the Report File Hub, the reports will be encrypted before they are delivered. The following file types are supported: .xls, .csv, .pdf, .txt, .jpeg, and .png. For a deeper explanation of each option, read about our delivery options here.

Report File Hub

Using the Report File Hub is our preferred method of delivery. It is a secure, easy-to-use location housed within the software. To open the Report File Hub, navigate to Reports > Administration > Report File Hub.

ab0ce897c84c76782b6e0ea593dd5e2f8ea50007789764f869db42f5561f082c.png

In the Report File Hub, you’ll be able to access any of the line of credit reports you requested as well as access or upload other documents you use in your tenant. Within the hub you can use search filters to find reports that have previously uploaded.

Other delivery options

If you have selected a different delivery option than the report file hub, your reports will be uploaded to the destination of your choosing. Open your selected location each day, and the reports will be available to you.