Line of credit reports
Detailed descriptions of available line of credit reports and instructions for accessing them.
Line of credit reports are pre-configured collections of data designed to help simplify the process of internal reporting and sharing data with sponsor banks. These reports are available by request and are delivered via the Report File Hub or to a destination of your choosing. Additional data delivery options are outlined in our Data on Demand article. The report options include both summary reports and detailed reports that provide granular insights. This article lists each of the available reports and explains you how to access them within your LoanPro account.
LoanPro provides 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
Line of credit reports query the LoanPro database daily and results are delivered to the Report File Hub or a chosen location. In most cases, the report will display data from the previous day (T-1); however, some reports pull data from the previous month.
Each available report is listed below. For an in-depth explanation of the data included in each report, click the report title.
Charge-off
Charge-off detailed
The Charge-Off Detailed Report report provides a comprehensive overview of all accounts that were in the ‘Closed - Charged Off’ status the previous day. It includes details such as dates, amounts, and credits during the Charge Off Process. Refer to the Charge Off Summary Report to view totals for each day of the current month.
Sample Charge Off Detailed Report.csv
Report values
| Column Name | Description |
|---|---|
| Account ID | Line of credit account ID |
| Date | Yesterday's date (this report lists data from the previous day) |
| Billing Cycle Start Date | Start date of the current billing cycle |
| Next Autopay Date | Upcoming autopay date |
| Next Autopay Amount | Upcoming autopay amount |
| Billing Cycle End Date | End date of the current billing cycle |
| Outstanding Balance | Total balance on the account |
| Interest Bearing Amount | Amount that bears interest on the account |
| Interest Charges | Amount of interest charges on the account |
| Fees | Total fees on the account |
| Purchases | Total amount accrued via swipes |
| Payments & Credits | Total amount of payments and credits |
| Available Credit | Amount of available credit |
| Credit Limit | Account’s total credit limit |
| Days Past Due | Amount of days the account is past due |
| Next Statement Date | The date the next statement will generate |
| Billing Cycle Due Date | The due date of the billing cycle |
| Next Due Date | The next date that payment is due |
| Status | The status of the line of credit account in text |
| Substatus | The sub-status of the line of credit account in text |
| Account Recency | The amount of days between the date on which the late payment occurred and now |
| Last Human Activity | The last date of human activity on the account |
| First Delinquency Date | The first date the account was delinquent |
| Unique Delinquencies | The number of delinquencies that have occurred on the account |
| Delinquency Percent | The percent of the account that is delinquent |
| Days Delinquent | Number of days the account is delinquent |
| ECOA (Primary Borrower) | Determined by the primary ECOA code |
| ECOA (Secondary Borrower) | Determined by the secondary ECOA code |
| Last Updated | The date the account was last updated |
| Minimum Payment | The minimum payment required on the account |
| Remaining Minimum Payment | The remaining minimum payment amount if applicable |
| Linked Accounts Total Balance | Sum total of total balances for all linked accounts |
| Linked Accounts Days Past Due | The highest number of days past due across all linked accounts |
| Linked Accounts Amount Past Due | Sum of amount past due for all linked accounts |
| Highest Credit Limit | Highest credit limit amount |
| Payment History String | This is a string of numbers and letters that represents the payment history |
| Date of First Delinquency | The date of the first delinquency on the account |
| Remaining Statement Balance | The amount remaining from the statement balance |
| Current Period Purchases | Swipe total from the current period |
| Credit Utilization | The percent of credit being utilized |
| Current Period Interest Charges | Interest charges for the current period |
| Collectible Charge Off Date | The date the account entered the Collectible Charge Off stage of the Charge Off Process |
| Total Balance as of Collectible Charge Off Date | The total balance to collect as of the Collectible Charge Off Date |
| Charge Off Date | Date the account was charged off |
| Charge Off Credit Amount | Total of all credits in the ‘Charge Off Credit’ credit category |
SQL query example
SELECT
locda.line_of_credit_id AS "Account ID",
DATE_FORMAT(locda.`date`, '%m/%d/%Y') AS "Date",
DATE_FORMAT(locda.billing_cycle_start_date, '%m/%d/%Y') AS "Billing Cycle Start Date",
IF (locda.next_autopay_date = "0000-00-00", "", DATE_FORMAT(locda.next_autopay_date, '%m/%d/%Y')) AS "Next AutoPay Date",
CONCAT('$', locda.next_autopay_amount) AS "Next AutoPay Amount",
DATE_FORMAT(locda.billing_cycle_end_date, '%m/%d/%Y') AS "Billing Cycle End Date",
CONCAT('$', locda.total_balance) AS "Outstanding Balance",
CONCAT('$', locda.total_interest_bearing_amount) "Interest Bearing Amount",
CONCAT('$', locda.total_interest_charges) AS "Interest Charges",
CONCAT('$', locda.total_fees) AS "Fees",
CONCAT('$', locda.total_swipes) AS "Purchases",
CONCAT('$', locda.total_payments_and_credits) AS "Payments & Credits",
CONCAT('$', locda.total_available_credit) AS "Available Credit",
CONCAT('$', locda.total_credit_limit) AS "Credit Limit",
locda.days_past_due AS "Days Past Due",
CONCAT('$', locda.amount_past_due) AS "Amount Past Due",
DATE_FORMAT(locda.next_statement_date, '%m/%d/%Y') AS "Next Statement Date",
DATE_FORMAT(locda.billing_cycle_due_date, '%m/%d/%Y') AS "Billing Cycle Due Date",
DATE_FORMAT(locda.next_due_date, '%m/%d/%Y') AS "Next Due Date",
locda.line_of_credit_status_text AS "Status",
locda.line_of_credit_sub_status_text AS "Substatus",
locda.line_of_credit_recency AS "Account Recency",
IF (locda.last_human_activity = "0000-00-00", "", DATE_FORMAT(locda.last_human_activity, '%m/%d/%Y')) AS "Last Human Activity",
IF (locda.first_delinquency_date = "0000-00-00", "", DATE_FORMAT(locda.first_delinquency_date, '%m/%d/%Y')) AS "First Delinquency Date",
locda.unique_delinquencies AS "Unique Delinquencies",
CONCAT(locda.delinquency_percent, '%') AS "Delinquency Percent",
locda.delinquent_days AS "Days Delinquent",
locda.calced_ecoa AS "ECOA (Primary Borrower)",
locda.calced_ecoa_cobuyer AS "ECOA (Secondary Borrower)",
DATE_FORMAT(locda.lastUpdated, '%m/%d/%Y %r') AS "Last Updated",
CONCAT('$', locda.minimum_payment) AS "Minimum Payment",
CONCAT('$', locda.remaining_minimum_payment) AS "Remaining Minimum Payment",
CONCAT('$', locda.linked_accounts_total_balance) AS "Linked Accounts Total Balance",
locda.linked_accounts_days_past_due AS "Linked Accounts Days Past Due",
CONCAT('$', locda.linked_accounts_amount_past_due) AS "Linked Accounts Amount Past Due",
CONCAT('$', locda.highest_credit) AS "Highest Credit Limit",
locda.payment_history_profile AS "Payment History String",
DATE_FORMAT(locda.date_of_first_delinquency, '%m/%d/%Y') AS "Date of First Delinquency",
CONCAT('$', locda.remaining_statement_balance) AS "Remaining Statement Balance",
CONCAT('$', locda.period_swipes) AS "Current Period Purchases",
CONCAT(locda.percent_credit_utilization, '%') AS "Credit Utilization",
CONCAT('$', locda.period_interest_charges) AS "Current Period Interest Charges",
CONCAT('$', locda.period_fees) AS "Current Period Fees",
CONCAT('$', locda.period_payments_and_credits) AS "Current Period Payments & Credits",
DATE_FORMAT(cfe17.custom_field_value, '%m/%d/%Y') AS "Collectible Charge Off Date",
CONCAT('$', FORMAT(cfe6.custom_field_value, 2)) AS "Total Balance as of Collectible Charge Off Date",
DATE_FORMAT(cfe4.custom_field_value, '%m/%d/%Y') AS "Charge Off Date",
CONCAT('$', loccte.sumCredits) AS "Charge Off Credit Amount"
FROM
line_of_credit_entity loce
-- Pulls only loans in Closed - Charged Off status
JOIN line_of_credit_daily_archive locda ON locda.line_of_credit_id = loce.id AND locda.`date` = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) AND locda.line_of_credit_sub_status_text = "Closed - Charged Off"
-- Collectible Charge Off Date custom field value
LEFT JOIN custom_field__entity cfe17 ON cfe17.entity_id = loce.settings_id AND cfe17.custom_field_id = 17
-- Total Balance as of Collectible Charge Off Date custom field value
LEFT JOIN custom_field__entity cfe6 ON cfe6.entity_id = loce.settings_id AND cfe6.custom_field_id = 6
-- Charge Off Date custom field value
LEFT JOIN custom_field__entity cfe4 ON cfe4.entity_id = loce.settings_id AND cfe4.custom_field_id = 4
-- Sum of all active credits with the credit category of 'Charge Off Credit'
LEFT JOIN (SELECT entity_id locID, SUM(amount) sumCredits FROM line_of_credit_credit_transaction_entity loccte WHERE loccte.category_id = 3 AND loccte.revert_date IS NULL AND loccte.deleted = 0 GROUP BY loccte.entity_id) loccte ON loccte.locID = loce.id
WHERE loce.deleted = 0
GROUP BY loce.id Charge-off summary
The Charge-Off Summary Report provides a comprehensive overview of all accounts that were in the ‘Closed - Charged Off’ status throughout the month. It includes details such as dates, amounts, and credits during the Charge Off Process. Refer to the Charge Off Detailed Report for more in depth information.
Sample Charge Off Summary Report.csv
Report values
| Column Name | Description |
|---|---|
| As of Date | The date the data was retrieved |
| Total Number of Accounts | Total number of accounts in the ‘Closed - Charged Off’ status |
| Total Charged Off Balance | The sum of all credits in the ‘Charged Off Credit’ credit category |
| Total Interest Bearing Amount | Total amount bearing interest |
| Total Interest Charged | Total amount charged in interest |
| Total Fees | Total amount charged in fees |
| Total Payments & Credits | Sum of payments and credits |
| Total Credit Limits | Total of all credit limits |
| Avg Unique Delinquencies | The average number of delinquencies that have occurred across accounts |
| Total of Highest Credit Limits | Sum of all the highest credit limits |
| Total Balances | Sum of total balances |
| Current Period Total Interest Charges | Sum of the amounts charged in interest for the current period |
| Current Period total Fees | Sum of the fees charged for the current period |
| Current Period Payments & Credits | Sum of payments and credits for the current period |
SQL query example
SELECT
DATE_FORMAT(locda.`date`, '%m/%d/%Y') AS "As of Date",
COUNT(loce.id) AS "Total Number of Accounts",
CONCAT('$', FORMAT(SUM(loccte.sumCredits), 2)) AS "Total Charged Off Balance",
CONCAT('$', FORMAT(SUM(locda.total_interest_bearing_amount), 2)) AS "Total Interest Bearing Amount",
CONCAT('$', FORMAT(SUM(locda.total_interest_charges), 2)) AS "Total Interest Charged",
CONCAT('$', FORMAT(SUM(locda.total_fees), 2)) AS "Total Fees",
CONCAT('$', FORMAT(SUM(locda.total_payments_and_credits), 2)) AS "Total Payments & Credits",
CONCAT('$', FORMAT(SUM(locda.total_credit_limit), 2)) AS "Total of Credit Limits",
ROUND(AVG(locda.unique_delinquencies), 0) AS "Avg Unique Delinquencies",
CONCAT('$', FORMAT(SUM(locda.highest_credit), 2)) AS "Total of Highest Credit Limits",
CONCAT('$', FORMAT(SUM(locda.total_balance), 2)) AS "Total Balances",
CONCAT('$', FORMAT(SUM(locda.period_interest_charges), 2)) AS "Current Period Total Interest Charges",
CONCAT('$', FORMAT(SUM(locda.period_fees), 2)) AS "Current Period Total Fees",
CONCAT('$', FORMAT(SUM(locda.period_payments_and_credits), 2)) AS "Current Period Payments & Credits"
FROM
line_of_credit_entity loce
JOIN
line_of_credit_daily_archive locda ON locda.line_of_credit_id = loce.id
AND MONTH(locda.`date`) = MONTH(CURRENT_DATE())
AND YEAR(locda.`date`) = YEAR(CURRENT_DATE())
AND locda.line_of_credit_sub_status_text = "Closed - Charged Off"
LEFT JOIN
(SELECT entity_id AS locID, SUM(amount) AS sumCredits
FROM line_of_credit_credit_transaction_entity loccte
WHERE loccte.category_id = 3
AND loccte.revert_date IS NULL
AND loccte.deleted = 0
GROUP BY loccte.entity_id) loccte
ON loccte.locID = loce.id
WHERE loce.deleted = 0
GROUP BY locda.`date`
ORDER BY locda.`date`;
Delinquency
All delinquency
The All Delinquency report provides a comprehensive overview of all accounts that are past due. It includes details such as days past due, amount past due, and account statuses.
Sample Delinquency (ALL).csv
Report values
| Column Name | Description |
|---|---|
| Program Name | The name of the line of credit program |
| LoC ID | The system ID of the line of credit |
| Date | The date the report was generated |
| Billing Cycle Start Date | The start date of the current billing cycle |
| Next Autopay Date | The next date that an autopay is scheduled to occur |
| Next Autopay Amount | The amount scheduled for the next autopay |
| Billing Cycle End Date | The end of the current billing cycle |
| Total Outstanding Balance | Total balance on the account |
| Total Interest Bearing Amount | The amount of the balance that bears interest |
| Total Interest Amount | The total amount of interest charges on the account |
| Total Fees | The total amount of fees charged on the account |
| Total Swipes | Total amount accrued via swipes |
| Total Payments & Credits | Total amount of payments and credits |
| Available Credit | Total available credit |
| Credit Limit | The account’s total credit limit |
| Days Past Due | The amount of days the account is past due |
| Amount Past Due | The amount the account is past due |
| Next Statement Date | The date the next statement will generate |
| Billing Cycle Due Date | The due date for the current billing cycle |
| Next Due Date | The next date the account is due |
| Line of Credit Status ID | The ID of the status on the account |
| Line of Credit Status | The text of the status on the account |
| Line of Credit Sub-Status ID | The ID of the substatus on the account |
| Line of Credit Sub-Status | The text of the substatus on the account |
| Line of Credit Age | The age of the account |
| Line of Credit Recency | The amount of days between the date on which the late payment occurred and now |
| Last Human Activity | The last date of human activity on the account |
| First Delinquency Date | First date the account was delinquent |
| Unique Delinquencies | Number of delinquencies that have occurred on the account |
| Delinquency Percent | Percent of the account that is delinquent |
| Days Delinquent | This is how many days the account is delinquent |
| ECOA | The ECOA code for the primary borrower |
| Last Updated | The last date the account was updated |
| Minimum Payment | The minimum required payment on the account |
| Remaining Minimum | Remaining minimum payment amount if applicable |
| Linked Accounts Total Balance | Sum of the total balances for all linked accounts |
| Linked Accounts Days Past Due | The highest number of days past due across all linked accounts |
| Linked Accounts Amount Past Due | The sum of the amount past due for all linked accounts |
| Highest Credit Amount | Amount of the highest credit limit |
| Payment History | String of number and letters that represent the payment history |
| Date of First Delinquency | Date of the first delinquency on the account |
| Remaining Statement Balance | The balance remaining on the statement |
| Current Period Transaction Balance | Account balance for the current period |
| Credit Utilization | Percent of utilized credit on the account |
| Current Period Interest Charges | Amount of interest charges for the current period |
| Current Period Fees | Amount of fees charges for the current period |
| Current Period Payments | Sum of payments and credits for the current period |
SQL query example
SELECT
pe.title AS "Program Name",
loc.id AS "LoC ID",
DATE_FORMAT(CURRENT_DATE(), '%m/%d/%Y') AS "Date",
DATE_FORMAT(archive.billing_cycle_start_date, '%m/%d/%Y') AS "Billing Cycle Start Date",
DATE_FORMAT(archive.next_autopay_date, '%m/%d/%Y') AS "Next Autopay Date",
CONCAT('$', FORMAT(archive.next_autopay_amount, 2)) AS "Next Autopay Amount",
DATE_FORMAT(archive.billing_cycle_end_date, '%m/%d/%Y') AS "Billing Cycle End Date",
CONCAT('$', FORMAT(archive.total_balance, 2)) AS "Total Outstanding Balance",
CONCAT('$', FORMAT(archive.total_interest_bearing_amount, 2)) AS "Total Interest Bearing Amount",
CONCAT('$', FORMAT(archive.total_interest_charges, 2)) AS "Total Interest Amount",
CONCAT('$', FORMAT(archive.total_fees, 2)) AS "Total Fees",
CONCAT('$', FORMAT(archive.total_swipes, 2)) AS "Total Swipes",
CONCAT('$', FORMAT(archive.total_payments_and_credits, 2)) AS "Total Payments & Credits",
CONCAT('$', FORMAT(archive.total_available_credit, 2)) AS "Available Credit",
CONCAT('$', FORMAT(archive.total_credit_limit, 2)) AS "Credit Limit",
archive.days_past_due AS "Days Past Due",
CONCAT('$', FORMAT(archive.amount_past_due, 2)) AS "Amount Past Due",
DATE_FORMAT(archive.next_statement_date, '%m/%d/%Y') AS "Next Statement Date",
DATE_FORMAT(archive.billing_cycle_due_date, '%m/%d/%Y') AS "Billing Cycle Due Date",
DATE_FORMAT(archive.next_due_date, '%m/%d/%Y') AS "Next Due Date",
settings.loan_status_id AS "Line of Credit Status ID",
status.title AS "Line of Credit Status",
settings.loan_sub_status_id AS "Line of Credit Sub-Status ID",
substatus.title AS "Line of Credit Sub-Status",
archive.line_of_credit_age AS "Line of Credit Age",
archive.line_of_credit_recency AS "Line of Credit Recency",
DATE_FORMAT(archive.last_human_activity, '%m/%d/%Y') AS "Last Human Activity",
DATE_FORMAT(archive.first_delinquency_date, '%m/%d/%Y') AS "First Delinquency Date",
archive.unique_delinquencies AS "Unique Delinquencies",
CONCAT(FORMAT(archive.delinquency_percent * 100, 0), '%') AS "Delinquency Percent",
archive.delinquent_days AS "Days Delinquent",
settings.ecoa_code AS "ECOA",
DATE_FORMAT(archive.lastUpdated, '%m/%d/%Y %r') AS "Last Updated",
CONCAT('$', FORMAT(archive.minimum_payment, 2)) AS "Minimum Payment",
CONCAT('$', FORMAT(archive.remaining_minimum_payment, 2)) AS "Remaining Minimum",
CONCAT('$', FORMAT(archive.linked_accounts_total_balance, 2)) AS "Linked Accounts Total Balance",
archive.linked_accounts_days_past_due AS "Linked Accounts Days Past Due",
CONCAT('$', FORMAT(archive.linked_accounts_amount_past_due, 2)) AS "Linked Accounts Amounts Past Due",
CONCAT('$', FORMAT(archive.highest_credit, 2)) AS "Highest Credit Amount",
archive.payment_history_profile AS "Payment History",
DATE_FORMAT(archive.date_of_first_delinquency, '%m/%d/%Y') AS "Date of First Delinquency",
CONCAT('$', FORMAT(archive.remaining_statement_balance, 2)) AS "Remaining Statement Balance",
CONCAT('$', FORMAT(archive.period_swipes, 2)) AS "Current Period Transaction Balance",
CONCAT(FORMAT(archive.percent_credit_utilization * 100, 0), '%') AS "Credit Utilization",
CONCAT('$', FORMAT(archive.period_interest_charges, 2)) AS "Current Period Interest Charges",
CONCAT('$', FORMAT(archive.period_fees, 2)) AS "Current Period Fees",
CONCAT('$', FORMAT(archive.period_payments_and_credits, 2)) AS "Current Period Payments"
FROM
line_of_credit_entity loc
LEFT JOIN line_of_credit_settings_entity settings ON settings.line_of_credit_id = loc.id
LEFT JOIN loan_status_entity status ON status.id = settings.loan_status_id
LEFT JOIN loan_sub_status_entity substatus ON substatus.id = settings.loan_sub_status_id
LEFT JOIN line_of_credit_daily_archive archive ON archive.line_of_credit_id = loc.id AND archive.date = CURRENT_DATE()
LEFT JOIN product_entity pe ON pe.id = loc.product_id
WHERE
archive.days_past_due > 0Delinquency 1-30
The Delinquency 1-30 report provides a comprehensive overview of all accounts that are between 1 and 30 days past due. It includes details such as days past due, amount past due, and account statuses.
Sample Delinquency (1 - 30).csv
Report values
| Column Name | Description |
|---|---|
| Program Name | The name of the line of credit program |
| LoC ID | The system ID of the line of credit |
| Date | The date the report was generated |
| Billing Cycle Start Date | The start date of the current billing cycle |
| Next Autopay Date | The next date that an autopay is scheduled to occur |
| Next Autopay Amount | The amount scheduled for the next autopay |
| Billing Cycle End Date | The end of the current billing cycle |
| Total Outstanding Balance | Total balance on the account |
| Total Interest Bearing Amount | The amount of the balance that bears interest |
| Total Interest Amount | The total amount of interest charges on the account |
| Total Fees | The total amount of fees charged on the account |
| Total Swipes | Total amount accrued via swipes |
| Total Payments & Credits | Total amount of payments and credits |
| Available Credit | Total available credit |
| Credit Limit | The account’s total credit limit |
| Days Past Due | The amount of days the account is past due |
| Amount Past Due | The amount the account is past due |
| Next Statement Date | The date the next statement will generate |
| Billing Cycle Due Date | The due date for the current billing cycle |
| Next Due Date | The next date the account is due |
| Line of Credit Status ID | The ID of the status on the account |
| Line of Credit Status | The text of the status on the account |
| Line of Credit Sub-Status ID | The ID of the substatus on the account |
| Line of Credit Sub-Status | The text of the substatus on the account |
| Line of Credit Age | The age of the account |
| Line of Credit Recency | The amount of days between the date on which the late payment occurred and now |
| Last Human Activity | The last date of human activity on the account |
| First Delinquency Date | First date the account was delinquent |
| Unique Delinquencies | Number of delinquencies that have occurred on the account |
| Delinquency Percent | Percent of the account that is delinquent |
| Days Delinquent | This is how many days the account is delinquent |
| ECOA | The ECOA code for the primary borrower |
| Last Updated | The last date the account was updated |
| Minimum Payment | The minimum required payment on the account |
| Remaining Minimum | Remaining minimum payment amount if applicable |
| Linked Accounts Total Balance | Sum of the total balances for all linked accounts |
| Linked Accounts Days Past Due | The highest number of days past due across all linked accounts |
| Linked Accounts Amount Past Due | The sum of the amount past due for all linked accounts |
| Highest Credit Amount | Amount of the highest credit limit |
| Payment History | String of number and letters that represent the payment history |
| Date of First Delinquency | Date of the first delinquency on the account |
| Remaining Statement Balance | The balance remaining on the statement |
| Current Period Transaction Balance | Account balance for the current period |
| Credit Utilization | Percent of utilized credit on the account |
| Current Period Interest Charges | Amount of interest charges for the current period |
| Current Period Fees | Amount of fees charges for the current period |
| Current Period Payments | Sum of payments and credits for the current period |
SQL query example
SELECT
pe.title AS "Program Name",
loc.id AS "LoC ID",
DATE_FORMAT(CURRENT_DATE(), '%m/%d/%Y') AS "Date",
DATE_FORMAT(archive.billing_cycle_start_date, '%m/%d/%Y') AS "Billing Cycle Start Date",
DATE_FORMAT(archive.next_autopay_date, '%m/%d/%Y') AS "Next Autopay Date",
CONCAT('$', FORMAT(archive.next_autopay_amount, 2)) AS "Next Autopay Amount",
DATE_FORMAT(archive.billing_cycle_end_date, '%m/%d/%Y') AS "Billing Cycle End Date",
CONCAT('$', FORMAT(archive.total_balance, 2)) AS "Total Outstanding Balance",
CONCAT('$', FORMAT(archive.total_interest_bearing_amount, 2)) AS "Total Interest Bearing Amount",
CONCAT('$', FORMAT(archive.total_interest_charges, 2)) AS "Total Interest Amount",
CONCAT('$', FORMAT(archive.total_fees, 2)) AS "Total Fees",
CONCAT('$', FORMAT(archive.total_swipes, 2)) AS "Total Swipes",
CONCAT('$', FORMAT(archive.total_payments_and_credits, 2)) AS "Total Payments & Credits",
CONCAT('$', FORMAT(archive.total_available_credit, 2)) AS "Available Credit",
CONCAT('$', FORMAT(archive.total_credit_limit, 2)) AS "Credit Limit",
archive.days_past_due AS "Days Past Due",
CONCAT('$', FORMAT(archive.amount_past_due, 2)) AS "Amount Past Due",
DATE_FORMAT(archive.next_statement_date, '%m/%d/%Y') AS "Next Statement Date",
DATE_FORMAT(archive.billing_cycle_due_date, '%m/%d/%Y') AS "Billing Cycle Due Date",
DATE_FORMAT(archive.next_due_date, '%m/%d/%Y') AS "Next Due Date",
settings.loan_status_id AS "Line of Credit Status ID",
status.title AS "Line of Credit Status",
settings.loan_sub_status_id AS "Line of Credit Sub-Status ID",
substatus.title AS "Line of Credit Sub-Status",
archive.line_of_credit_age AS "Line of Credit Age",
archive.line_of_credit_recency AS "Line of Credit Recency",
DATE_FORMAT(archive.last_human_activity, '%m/%d/%Y') AS "Last Human Activity",
DATE_FORMAT(archive.first_delinquency_date, '%m/%d/%Y') AS "First Delinquency Date",
archive.unique_delinquencies AS "Unique Delinquencies",
CONCAT(FORMAT(archive.delinquency_percent * 100, 0), '%') AS "Delinquency Percent",
archive.delinquent_days AS "Days Delinquent",
settings.ecoa_code AS "ECOA",
DATE_FORMAT(archive.lastUpdated, '%m/%d/%Y %r') AS "Last Updated",
CONCAT('$', FORMAT(archive.minimum_payment, 2)) AS "Minimum Payment",
CONCAT('$', FORMAT(archive.remaining_minimum_payment, 2)) AS "Remaining Minimum",
CONCAT('$', FORMAT(archive.linked_accounts_total_balance, 2)) AS "Linked Accounts Total Balance",
archive.linked_accounts_days_past_due AS "Linked Accounts Days Past Due",
CONCAT('$', FORMAT(archive.linked_accounts_amount_past_due, 2)) AS "Linked Accounts Amounts Past Due",
CONCAT('$', FORMAT(archive.highest_credit, 2)) AS "Highest Credit Amount",
archive.payment_history_profile AS "Payment History",
DATE_FORMAT(archive.date_of_first_delinquency, '%m/%d/%Y') AS "Date of First Delinquency",
CONCAT('$', FORMAT(archive.remaining_statement_balance, 2)) AS "Remaining Statement Balance",
CONCAT('$', FORMAT(archive.period_swipes, 2)) AS "Current Period Transaction Balance",
CONCAT(FORMAT(archive.percent_credit_utilization * 100, 0), '%') AS "Credit Utilization",
CONCAT('$', FORMAT(archive.period_interest_charges, 2)) AS "Current Period Interest Charges",
CONCAT('$', FORMAT(archive.period_fees, 2)) AS "Current Period Fees",
CONCAT('$', FORMAT(archive.period_payments_and_credits, 2)) AS "Current Period Payments"
FROM
line_of_credit_entity loc
LEFT JOIN line_of_credit_settings_entity settings ON settings.line_of_credit_id = loc.id
LEFT JOIN loan_status_entity status ON status.id = settings.loan_status_id
LEFT JOIN loan_sub_status_entity substatus ON substatus.id = settings.loan_sub_status_id
LEFT JOIN line_of_credit_daily_archive archive ON archive.line_of_credit_id = loc.id AND archive.date = CURRENT_DATE()
LEFT JOIN product_entity pe ON pe.id = loc.product_id
WHERE
archive.days_past_due BETWEEN 1 AND 30;
Delinquency 1-30.sql
Displaying Delinquency 1-30.sql.Delinquency 31-60
The Delinquency 31-60 report provides a comprehensive overview of all accounts that are between 31 and 60 days past due. It includes details such as days past due, amount past due, and account statuses.
Report values
| Column Name | Description |
|---|---|
| Program Name | The name of the line of credit program |
| LoC ID | The system ID of the line of credit |
| Date | The date the report was generated |
| Billing Cycle Start Date | The start date of the current billing cycle |
| Next Autopay Date | The next date that an autopay is scheduled to occur |
| Next Autopay Amount | The amount scheduled for the next autopay |
| Billing Cycle End Date | The end of the current billing cycle |
| Total Outstanding Balance | Total balance on the account |
| Total Interest Bearing Amount | The amount of the balance that bears interest |
| Total Interest Amount | The total amount of interest charges on the account |
| Total Fees | The total amount of fees charged on the account |
| Total Swipes | Total amount accrued via swipes |
| Total Payments & Credits | Total amount of payments and credits |
| Available Credit | Total available credit |
| Credit Limit | The account’s total credit limit |
| Days Past Due | The amount of days the account is past due |
| Amount Past Due | The amount the account is past due |
| Next Statement Date | The date the next statement will generate |
| Billing Cycle Due Date | The due date for the current billing cycle |
| Next Due Date | The next date the account is due |
| Line of Credit Status ID | The ID of the status on the account |
| Line of Credit Status | The text of the status on the account |
| Line of Credit Sub-Status ID | The ID of the substatus on the account |
| Line of Credit Sub-Status | The text of the substatus on the account |
| Line of Credit Age | The age of the account |
| Line of Credit Recency | The amount of days between the date on which the late payment occurred and now |
| Last Human Activity | The last date of human activity on the account |
| First Delinquency Date | First date the account was delinquent |
| Unique Delinquencies | Number of delinquencies that have occurred on the account |
| Delinquency Percent | Percent of the account that is delinquent |
| Days Delinquent | This is how many days the account is delinquent |
| ECOA | The ECOA code for the primary borrower |
| Last Updated | The last date the account was updated |
| Minimum Payment | The minimum required payment on the account |
| Remaining Minimum | Remaining minimum payment amount if applicable |
| Linked Accounts Total Balance | Sum of the total balances for all linked accounts |
| Linked Accounts Days Past Due | The highest number of days past due across all linked accounts |
| Linked Accounts Amount Past Due | The sum of the amount past due for all linked accounts |
| Highest Credit Amount | Amount of the highest credit limit |
| Payment History | String of number and letters that represent the payment history |
| Date of First Delinquency | Date of the first delinquency on the account |
| Remaining Statement Balance | The balance remaining on the statement |
| Current Period Transaction Balance | Account balance for the current period |
| Credit Utilization | Percent of utilized credit on the account |
| Current Period Interest Charges | Amount of interest charges for the current period |
| Current Period Fees | Amount of fees charges for the current period |
| Current Period Payments | Sum of payments and credits for the current period |
SQL query example
SELECT
pe.title AS "Program Name",
loc.id AS "LoC ID",
DATE_FORMAT(CURRENT_DATE(), '%m/%d/%Y') AS "Date",
DATE_FORMAT(archive.billing_cycle_start_date, '%m/%d/%Y') AS "Billing Cycle Start Date",
DATE_FORMAT(archive.next_autopay_date, '%m/%d/%Y') AS "Next Autopay Date",
CONCAT('$', FORMAT(archive.next_autopay_amount, 2)) AS "Next Autopay Amount",
DATE_FORMAT(archive.billing_cycle_end_date, '%m/%d/%Y') AS "Billing Cycle End Date",
CONCAT('$', FORMAT(archive.total_balance, 2)) AS "Total Outstanding Balance",
CONCAT('$', FORMAT(archive.total_interest_bearing_amount, 2)) AS "Total Interest Bearing Amount",
CONCAT('$', FORMAT(archive.total_interest_charges, 2)) AS "Total Interest Amount",
CONCAT('$', FORMAT(archive.total_fees, 2)) AS "Total Fees",
CONCAT('$', FORMAT(archive.total_swipes, 2)) AS "Total Swipes",
CONCAT('$', FORMAT(archive.total_payments_and_credits, 2)) AS "Total Payments & Credits",
CONCAT('$', FORMAT(archive.total_available_credit, 2)) AS "Available Credit",
CONCAT('$', FORMAT(archive.total_credit_limit, 2)) AS "Credit Limit",
archive.days_past_due AS "Days Past Due",
CONCAT('$', FORMAT(archive.amount_past_due, 2)) AS "Amount Past Due",
DATE_FORMAT(archive.next_statement_date, '%m/%d/%Y') AS "Next Statement Date",
DATE_FORMAT(archive.billing_cycle_due_date, '%m/%d/%Y') AS "Billing Cycle Due Date",
DATE_FORMAT(archive.next_due_date, '%m/%d/%Y') AS "Next Due Date",
settings.loan_status_id AS "Line of Credit Status ID",
status.title AS "Line of Credit Status",
settings.loan_sub_status_id AS "Line of Credit Sub-Status ID",
substatus.title AS "Line of Credit Sub-Status",
archive.line_of_credit_age AS "Line of Credit Age",
archive.line_of_credit_recency AS "Line of Credit Recency",
DATE_FORMAT(archive.last_human_activity, '%m/%d/%Y') AS "Last Human Activity",
DATE_FORMAT(archive.first_delinquency_date, '%m/%d/%Y') AS "First Delinquency Date",
archive.unique_delinquencies AS "Unique Delinquencies",
CONCAT(FORMAT(archive.delinquency_percent * 100, 0), '%') AS "Delinquency Percent",
archive.delinquent_days AS "Days Delinquent",
settings.ecoa_code AS "ECOA",
DATE_FORMAT(archive.lastUpdated, '%m/%d/%Y %r') AS "Last Updated",
CONCAT('$', FORMAT(archive.minimum_payment, 2)) AS "Minimum Payment",
CONCAT('$', FORMAT(archive.remaining_minimum_payment, 2)) AS "Remaining Minimum",
CONCAT('$', FORMAT(archive.linked_accounts_total_balance, 2)) AS "Linked Accounts Total Balance",
archive.linked_accounts_days_past_due AS "Linked Accounts Days Past Due",
CONCAT('$', FORMAT(archive.linked_accounts_amount_past_due, 2)) AS "Linked Accounts Amounts Past Due",
CONCAT('$', FORMAT(archive.highest_credit, 2)) AS "Highest Credit Amount",
archive.payment_history_profile AS "Payment History",
DATE_FORMAT(archive.date_of_first_delinquency, '%m/%d/%Y') AS "Date of First Delinquency",
CONCAT('$', FORMAT(archive.remaining_statement_balance, 2)) AS "Remaining Statement Balance",
CONCAT('$', FORMAT(archive.period_swipes, 2)) AS "Current Period Transaction Balance",
CONCAT(FORMAT(archive.percent_credit_utilization * 100, 0), '%') AS "Credit Utilization",
CONCAT('$', FORMAT(archive.period_interest_charges, 2)) AS "Current Period Interest Charges",
CONCAT('$', FORMAT(archive.period_fees, 2)) AS "Current Period Fees",
CONCAT('$', FORMAT(archive.period_payments_and_credits, 2)) AS "Current Period Payments"
FROM
line_of_credit_entity loc
LEFT JOIN line_of_credit_settings_entity settings ON settings.line_of_credit_id = loc.id
LEFT JOIN loan_status_entity status ON status.id = settings.loan_status_id
LEFT JOIN loan_sub_status_entity substatus ON substatus.id = settings.loan_sub_status_id
LEFT JOIN line_of_credit_daily_archive archive ON archive.line_of_credit_id = loc.id AND archive.date = CURRENT_DATE()
LEFT JOIN product_entity pe ON pe.id = loc.product_id
WHERE
archive.days_past_due BETWEEN 31 AND 60;Delinquency 61-90
The Delinquency 61-90 report provides a comprehensive overview of all accounts that are between 61 and 90 days past due. It includes details such as days past due, amount past due, and account statuses.
Report values
| Column Name | Description |
|---|---|
| Program Name | The name of the line of credit program |
| LoC ID | The system ID of the line of credit |
| Date | The date the report was generated |
| Billing Cycle Start Date | The start date of the current billing cycle |
| Next Autopay Date | The next date that an autopay is scheduled to occur |
| Next Autopay Amount | The amount scheduled for the next autopay |
| Billing Cycle End Date | The end of the current billing cycle |
| Total Outstanding Balance | Total balance on the account |
| Total Interest Bearing Amount | The amount of the balance that bears interest |
| Total Interest Amount | The total amount of interest charges on the account |
| Total Fees | The total amount of fees charged on the account |
| Total Swipes | Total amount accrued via swipes |
| Total Payments & Credits | Total amount of payments and credits |
| Available Credit | Total available credit |
| Credit Limit | The account’s total credit limit |
| Days Past Due | The amount of days the account is past due |
| Amount Past Due | The amount the account is past due |
| Next Statement Date | The date the next statement will generate |
| Billing Cycle Due Date | The due date for the current billing cycle |
| Next Due Date | The next date the account is due |
| Line of Credit Status ID | The ID of the status on the account |
| Line of Credit Status | The text of the status on the account |
| Line of Credit Sub-Status ID | The ID of the substatus on the account |
| Line of Credit Sub-Status | The text of the substatus on the account |
| Line of Credit Age | The age of the account |
| Line of Credit Recency | The amount of days between the date on which the late payment occurred and now |
| Last Human Activity | The last date of human activity on the account |
| First Delinquency Date | First date the account was delinquent |
| Unique Delinquencies | Number of delinquencies that have occurred on the account |
| Delinquency Percent | Percent of the account that is delinquent |
| Days Delinquent | This is how many days the account is delinquent |
| ECOA | The ECOA code for the primary borrower |
| Last Updated | The last date the account was updated |
| Minimum Payment | The minimum required payment on the account |
| Remaining Minimum | Remaining minimum payment amount if applicable |
| Linked Accounts Total Balance | Sum of the total balances for all linked accounts |
| Linked Accounts Days Past Due | The highest number of days past due across all linked accounts |
| Linked Accounts Amount Past Due | The sum of the amount past due for all linked accounts |
| Highest Credit Amount | Amount of the highest credit limit |
| Payment History | String of number and letters that represent the payment history |
| Date of First Delinquency | Date of the first delinquency on the account |
| Remaining Statement Balance | The balance remaining on the statement |
| Current Period Transaction Balance | Account balance for the current period |
| Credit Utilization | Percent of utilized credit on the account |
| Current Period Interest Charges | Amount of interest charges for the current period |
| Current Period Fees | Amount of fees charges for the current period |
| Current Period Payments | Sum of payments and credits for the current period |
SQL query example
SELECT
pe.title AS "Program Name",
loc.id AS "LoC ID",
DATE_FORMAT(CURRENT_DATE(), '%m/%d/%Y') AS "Date",
DATE_FORMAT(archive.billing_cycle_start_date, '%m/%d/%Y') AS "Billing Cycle Start Date",
DATE_FORMAT(archive.next_autopay_date, '%m/%d/%Y') AS "Next Autopay Date",
CONCAT('$', FORMAT(archive.next_autopay_amount, 2)) AS "Next Autopay Amount",
DATE_FORMAT(archive.billing_cycle_end_date, '%m/%d/%Y') AS "Billing Cycle End Date",
CONCAT('$', FORMAT(archive.total_balance, 2)) AS "Total Outstanding Balance",
CONCAT('$', FORMAT(archive.total_interest_bearing_amount, 2)) AS "Total Interest Bearing Amount",
CONCAT('$', FORMAT(archive.total_interest_charges, 2)) AS "Total Interest Amount",
CONCAT('$', FORMAT(archive.total_fees, 2)) AS "Total Fees",
CONCAT('$', FORMAT(archive.total_swipes, 2)) AS "Total Swipes",
CONCAT('$', FORMAT(archive.total_payments_and_credits, 2)) AS "Total Payments & Credits",
CONCAT('$', FORMAT(archive.total_available_credit, 2)) AS "Available Credit",
CONCAT('$', FORMAT(archive.total_credit_limit, 2)) AS "Credit Limit",
archive.days_past_due AS "Days Past Due",
CONCAT('$', FORMAT(archive.amount_past_due, 2)) AS "Amount Past Due",
DATE_FORMAT(archive.next_statement_date, '%m/%d/%Y') AS "Next Statement Date",
DATE_FORMAT(archive.billing_cycle_due_date, '%m/%d/%Y') AS "Billing Cycle Due Date",
DATE_FORMAT(archive.next_due_date, '%m/%d/%Y') AS "Next Due Date",
settings.loan_status_id AS "Line of Credit Status ID",
status.title AS "Line of Credit Status",
settings.loan_sub_status_id AS "Line of Credit Sub-Status ID",
substatus.title AS "Line of Credit Sub-Status",
archive.line_of_credit_age AS "Line of Credit Age",
archive.line_of_credit_recency AS "Line of Credit Recency",
DATE_FORMAT(archive.last_human_activity, '%m/%d/%Y') AS "Last Human Activity",
DATE_FORMAT(archive.first_delinquency_date, '%m/%d/%Y') AS "First Delinquency Date",
archive.unique_delinquencies AS "Unique Delinquencies",
CONCAT(FORMAT(archive.delinquency_percent * 100, 0), '%') AS "Delinquency Percent",
archive.delinquent_days AS "Days Delinquent",
settings.ecoa_code AS "ECOA",
DATE_FORMAT(archive.lastUpdated, '%m/%d/%Y %r') AS "Last Updated",
CONCAT('$', FORMAT(archive.minimum_payment, 2)) AS "Minimum Payment",
CONCAT('$', FORMAT(archive.remaining_minimum_payment, 2)) AS "Remaining Minimum",
CONCAT('$', FORMAT(archive.linked_accounts_total_balance, 2)) AS "Linked Accounts Total Balance",
archive.linked_accounts_days_past_due AS "Linked Accounts Days Past Due",
CONCAT('$', FORMAT(archive.linked_accounts_amount_past_due, 2)) AS "Linked Accounts Amounts Past Due",
CONCAT('$', FORMAT(archive.highest_credit, 2)) AS "Highest Credit Amount",
archive.payment_history_profile AS "Payment History",
DATE_FORMAT(archive.date_of_first_delinquency, '%m/%d/%Y') AS "Date of First Delinquency",
CONCAT('$', FORMAT(archive.remaining_statement_balance, 2)) AS "Remaining Statement Balance",
CONCAT('$', FORMAT(archive.period_swipes, 2)) AS "Current Period Transaction Balance",
CONCAT(FORMAT(archive.percent_credit_utilization * 100, 0), '%') AS "Credit Utilization",
CONCAT('$', FORMAT(archive.period_interest_charges, 2)) AS "Current Period Interest Charges",
CONCAT('$', FORMAT(archive.period_fees, 2)) AS "Current Period Fees",
CONCAT('$', FORMAT(archive.period_payments_and_credits, 2)) AS "Current Period Payments"
FROM
line_of_credit_entity loc
LEFT JOIN line_of_credit_settings_entity settings ON settings.line_of_credit_id = loc.id
LEFT JOIN loan_status_entity status ON status.id = settings.loan_status_id
LEFT JOIN loan_sub_status_entity substatus ON substatus.id = settings.loan_sub_status_id
LEFT JOIN line_of_credit_daily_archive archive ON archive.line_of_credit_id = loc.id AND archive.date = CURRENT_DATE()
LEFT JOIN product_entity pe ON pe.id = loc.product_id
WHERE
archive.days_past_due BETWEEN 61 AND 90;Delinquency 91+
The Delinquency 91+ report provides a comprehensive overview of all accounts that are over 91 days past due. It includes details such as days past due, amount past due, and account statuses.
Report values
| Column Name | Description |
|---|---|
| Program Name | The name of the line of credit program |
| LoC ID | The system ID of the line of credit |
| Date | The date the report was generated |
| Billing Cycle Start Date | The start date of the current billing cycle |
| Next Autopay Date | The next date that an autopay is scheduled to occur |
| Next Autopay Amount | The amount scheduled for the next autopay |
| Billing Cycle End Date | The end of the current billing cycle |
| Total Outstanding Balance | Total balance on the account |
| Total Interest Bearing Amount | The amount of the balance that bears interest |
| Total Interest Amount | The total amount of interest charges on the account |
| Total Fees | The total amount of fees charged on the account |
| Total Swipes | Total amount accrued via swipes |
| Total Payments & Credits | Total amount of payments and credits |
| Available Credit | Total available credit |
| Credit Limit | The account’s total credit limit |
| Days Past Due | The amount of days the account is past due |
| Amount Past Due | The amount the account is past due |
| Next Statement Date | The date the next statement will generate |
| Billing Cycle Due Date | The due date for the current billing cycle |
| Next Due Date | The next date the account is due |
| Line of Credit Status ID | The ID of the status on the account |
| Line of Credit Status | The text of the status on the account |
| Line of Credit Sub-Status ID | The ID of the substatus on the account |
| Line of Credit Sub-Status | The text of the substatus on the account |
| Line of Credit Age | The age of the account |
| Line of Credit Recency | The amount of days between the date on which the late payment occurred and now |
| Last Human Activity | The last date of human activity on the account |
| First Delinquency Date | First date the account was delinquent |
| Unique Delinquencies | Number of delinquencies that have occurred on the account |
| Delinquency Percent | Percent of the account that is delinquent |
| Days Delinquent | This is how many days the account is delinquent |
| ECOA | The ECOA code for the primary borrower |
| Last Updated | The last date the account was updated |
| Minimum Payment | The minimum required payment on the account |
| Remaining Minimum | Remaining minimum payment amount if applicable |
| Linked Accounts Total Balance | Sum of the total balances for all linked accounts |
| Linked Accounts Days Past Due | The highest number of days past due across all linked accounts |
| Linked Accounts Amount Past Due | The sum of the amount past due for all linked accounts |
| Highest Credit Amount | Amount of the highest credit limit |
| Payment History | String of number and letters that represent the payment history |
| Date of First Delinquency | Date of the first delinquency on the account |
| Remaining Statement Balance | The balance remaining on the statement |
| Current Period Transaction Balance | Account balance for the current period |
| Credit Utilization | Percent of utilized credit on the account |
| Current Period Interest Charges | Amount of interest charges for the current period |
| Current Period Fees | Amount of fees charges for the current period |
| Current Period Payments | Sum of payments and credits for the current period |
SQL query example
SELECT
pe.title AS "Program Name",
loc.id AS "LoC ID",
DATE_FORMAT(CURRENT_DATE(), '%m/%d/%Y') AS "Date",
DATE_FORMAT(archive.billing_cycle_start_date, '%m/%d/%Y') AS "Billing Cycle Start Date",
DATE_FORMAT(archive.next_autopay_date, '%m/%d/%Y') AS "Next Autopay Date",
CONCAT('$', FORMAT(archive.next_autopay_amount, 2)) AS "Next Autopay Amount",
DATE_FORMAT(archive.billing_cycle_end_date, '%m/%d/%Y') AS "Billing Cycle End Date",
CONCAT('$', FORMAT(archive.total_balance, 2)) AS "Total Outstanding Balance",
CONCAT('$', FORMAT(archive.total_interest_bearing_amount, 2)) AS "Total Interest Bearing Amount",
CONCAT('$', FORMAT(archive.total_interest_charges, 2)) AS "Total Interest Amount",
CONCAT('$', FORMAT(archive.total_fees, 2)) AS "Total Fees",
CONCAT('$', FORMAT(archive.total_swipes, 2)) AS "Total Swipes",
CONCAT('$', FORMAT(archive.total_payments_and_credits, 2)) AS "Total Payments & Credits",
CONCAT('$', FORMAT(archive.total_available_credit, 2)) AS "Available Credit",
CONCAT('$', FORMAT(archive.total_credit_limit, 2)) AS "Credit Limit",
archive.days_past_due AS "Days Past Due",
CONCAT('$', FORMAT(archive.amount_past_due, 2)) AS "Amount Past Due",
DATE_FORMAT(archive.next_statement_date, '%m/%d/%Y') AS "Next Statement Date",
DATE_FORMAT(archive.billing_cycle_due_date, '%m/%d/%Y') AS "Billing Cycle Due Date",
DATE_FORMAT(archive.next_due_date, '%m/%d/%Y') AS "Next Due Date",
settings.loan_status_id AS "Line of Credit Status ID",
status.title AS "Line of Credit Status",
settings.loan_sub_status_id AS "Line of Credit Sub-Status ID",
substatus.title AS "Line of Credit Sub-Status",
archive.line_of_credit_age AS "Line of Credit Age",
archive.line_of_credit_recency AS "Line of Credit Recency",
DATE_FORMAT(archive.last_human_activity, '%m/%d/%Y') AS "Last Human Activity",
DATE_FORMAT(archive.first_delinquency_date, '%m/%d/%Y') AS "First Delinquency Date",
archive.unique_delinquencies AS "Unique Delinquencies",
CONCAT(FORMAT(archive.delinquency_percent * 100, 0), '%') AS "Delinquency Percent",
archive.delinquent_days AS "Days Delinquent",
settings.ecoa_code AS "ECOA",
DATE_FORMAT(archive.lastUpdated, '%m/%d/%Y %r') AS "Last Updated",
CONCAT('$', FORMAT(archive.minimum_payment, 2)) AS "Minimum Payment",
CONCAT('$', FORMAT(archive.remaining_minimum_payment, 2)) AS "Remaining Minimum",
CONCAT('$', FORMAT(archive.linked_accounts_total_balance, 2)) AS "Linked Accounts Total Balance",
archive.linked_accounts_days_past_due AS "Linked Accounts Days Past Due",
CONCAT('$', FORMAT(archive.linked_accounts_amount_past_due, 2)) AS "Linked Accounts Amounts Past Due",
CONCAT('$', FORMAT(archive.highest_credit, 2)) AS "Highest Credit Amount",
archive.payment_history_profile AS "Payment History",
DATE_FORMAT(archive.date_of_first_delinquency, '%m/%d/%Y') AS "Date of First Delinquency",
CONCAT('$', FORMAT(archive.remaining_statement_balance, 2)) AS "Remaining Statement Balance",
CONCAT('$', FORMAT(archive.period_swipes, 2)) AS "Current Period Transaction Balance",
CONCAT(FORMAT(archive.percent_credit_utilization * 100, 0), '%') AS "Credit Utilization",
CONCAT('$', FORMAT(archive.period_interest_charges, 2)) AS "Current Period Interest Charges",
CONCAT('$', FORMAT(archive.period_fees, 2)) AS "Current Period Fees",
CONCAT('$', FORMAT(archive.period_payments_and_credits, 2)) AS "Current Period Payments"
FROM
line_of_credit_entity loc
LEFT JOIN line_of_credit_settings_entity settings ON settings.line_of_credit_id = loc.id
LEFT JOIN loan_status_entity status ON status.id = settings.loan_status_id
LEFT JOIN loan_sub_status_entity substatus ON substatus.id = settings.loan_sub_status_id
LEFT JOIN line_of_credit_daily_archive archive ON archive.line_of_credit_id = loc.id AND archive.date = CURRENT_DATE()
LEFT JOIN product_entity pe ON pe.id = loc.product_id
WHERE
archive.days_past_due > 90;Delinquency summary
The Delinquency Summary report provides comprehensive overview of all past due accounts for the past month. It includes details such as days past due, amount past due, and account statuses.
Sample Delinquency (Summary).csv
Report values
| Column Name | Description |
|---|---|
| As of Date | The date the report was generated |
| Delinquency Bands | The range of days past due. Expected values: 1-30 DPD, 31-60 DPD, 61-90 DPD, or 91+ DPD |
| Total Number of Accounts | Total number of accounts in this delinquency band |
| Total Outstanding Balance | Sum total of outstanding balances |
| Total Interest Bearing Amount | Sum total of interest bearing amounts |
| Total Interest Amount | Sum total of interest charged across all accounts |
| Total Fees | Sum total of fees charged across all accounts |
| Total Swipes | Sum total of swipes |
| Total Payments & Credits | Sum total of payments and credits |
| Available Credit | Sum total of available credit |
| Credit Limit | Sum total of credit limits |
| Amount Past Due | Sum total of amounts past due |
| Avg Unique Delinquencies | Avg amount of times delinquency has occurred across all accounts |
| Highest Credit Amount | Sum total of all the highest credit limit amounts |
| Current Period Transaction Balance | Sum total of swipes across all accounts in the current period |
| Current Period Interest Charges | Sum total of interest charged across all accounts in the current period |
| Current Period Fees | Sum total of fees charged across all accounts in the current period |
| Current Period Payments | Sum total of payments and credits across all accounts in the current period |
SQL query example
SELECT
DATE_FORMAT(CURRENT_DATE(), '%m/%d/%Y') AS "As of Date",
"1-30 DPD" AS "Delinquency Bands",
COUNT(loc.id) AS "Total Number of Accounts",
CONCAT('$', FORMAT(SUM(archive.total_balance), 2)) AS "Total Outstanding Balance",
CONCAT('$', FORMAT(SUM(archive.total_interest_bearing_amount), 2)) AS "Total Interest Bearing Amount",
CONCAT('$', FORMAT(SUM(archive.total_interest_charges), 2)) AS "Total Interest Amount",
CONCAT('$', FORMAT(SUM(archive.total_fees), 2)) AS "Total Fees",
CONCAT('$', FORMAT(SUM(archive.total_swipes), 2)) AS "Total Swipes",
CONCAT('$', FORMAT(SUM(archive.total_payments_and_credits), 2)) AS "Total Payments & Credits",
CONCAT('$', FORMAT(SUM(archive.total_available_credit), 2)) AS "Available Credit",
CONCAT('$', FORMAT(SUM(archive.total_credit_limit), 2)) AS "Credit Limit",
CONCAT('$', FORMAT(SUM(archive.amount_past_due), 2)) AS "Amount Past Due",
FORMAT(AVG(archive.unique_delinquencies), 0) AS "Avg Unique Delinquencies",
CONCAT('$', FORMAT(SUM(archive.highest_credit), 2)) AS "Highest Credit Amount",
CONCAT('$', FORMAT(SUM(archive.period_swipes), 2)) AS "Current Period Transaction Balance",
CONCAT('$', FORMAT(SUM(archive.period_interest_charges), 2)) AS "Current Period Interest Charges",
CONCAT('$', FORMAT(SUM(archive.period_fees), 2)) AS "Current Period Fees",
CONCAT('$', FORMAT(SUM(archive.period_payments_and_credits), 2)) AS "Current Period Payments"
FROM
line_of_credit_entity loc
LEFT JOIN
line_of_credit_daily_archive archive ON archive.line_of_credit_id = loc.id
AND archive.date = CURRENT_DATE()
WHERE
archive.days_past_due BETWEEN 1 AND 30
UNION ALL
SELECT
DATE_FORMAT(CURRENT_DATE(), '%m/%d/%Y') AS "As of Date",
"31-60 DPD" AS "Delinquency Bands",
COUNT(loc.id) AS "Total Number of Accounts",
CONCAT('$', FORMAT(SUM(archive.total_balance), 2)) AS "Total Outstanding Balance",
CONCAT('$', FORMAT(SUM(archive.total_interest_bearing_amount), 2)) AS "Total Interest Bearing Amount",
CONCAT('$', FORMAT(SUM(archive.total_interest_charges), 2)) AS "Total Interest Amount",
CONCAT('$', FORMAT(SUM(archive.total_fees), 2)) AS "Total Fees",
CONCAT('$', FORMAT(SUM(archive.total_swipes), 2)) AS "Total Swipes",
CONCAT('$', FORMAT(SUM(archive.total_payments_and_credits), 2)) AS "Total Payments & Credits",
CONCAT('$', FORMAT(SUM(archive.total_available_credit), 2)) AS "Available Credit",
CONCAT('$', FORMAT(SUM(archive.total_credit_limit), 2)) AS "Credit Limit",
CONCAT('$', FORMAT(SUM(archive.amount_past_due), 2)) AS "Amount Past Due",
FORMAT(AVG(archive.unique_delinquencies), 0) AS "Avg Unique Delinquencies",
CONCAT('$', FORMAT(SUM(archive.highest_credit), 2)) AS "Highest Credit Amount",
CONCAT('$', FORMAT(SUM(archive.period_swipes), 2)) AS "Current Period Transaction Balance",
CONCAT('$', FORMAT(SUM(archive.period_interest_charges), 2)) AS "Current Period Interest Charges",
CONCAT('$', FORMAT(SUM(archive.period_fees), 2)) AS "Current Period Fees",
CONCAT('$', FORMAT(SUM(archive.period_payments_and_credits), 2)) AS "Current Period Payments"
FROM
line_of_credit_entity loc
LEFT JOIN
line_of_credit_daily_archive archive ON archive.line_of_credit_id = loc.id
AND archive.date = CURRENT_DATE()
WHERE
archive.days_past_due BETWEEN 31 AND 60
UNION ALL
SELECT
DATE_FORMAT(CURRENT_DATE(), '%m/%d/%Y') AS "As of Date",
"61-90 DPD" AS "Delinquency Bands",
COUNT(loc.id) AS "Total Number of Accounts",
CONCAT('$', FORMAT(SUM(archive.total_balance), 2)) AS "Total Outstanding Balance",
CONCAT('$', FORMAT(SUM(archive.total_interest_bearing_amount), 2)) AS "Total Interest Bearing Amount",
CONCAT('$', FORMAT(SUM(archive.total_interest_charges), 2)) AS "Total Interest Amount",
CONCAT('$', FORMAT(SUM(archive.total_fees), 2)) AS "Total Fees",
CONCAT('$', FORMAT(SUM(archive.total_swipes), 2)) AS "Total Swipes",
CONCAT('$', FORMAT(SUM(archive.total_payments_and_credits), 2)) AS "Total Payments & Credits",
CONCAT('$', FORMAT(SUM(archive.total_available_credit), 2)) AS "Available Credit",
CONCAT('$', FORMAT(SUM(archive.total_credit_limit), 2)) AS "Credit Limit",
CONCAT('$', FORMAT(SUM(archive.amount_past_due), 2)) AS "Amount Past Due",
FORMAT(AVG(archive.unique_delinquencies), 0) AS "Avg Unique Delinquencies",
CONCAT('$', FORMAT(SUM(archive.highest_credit), 2)) AS "Highest Credit Amount",
CONCAT('$', FORMAT(SUM(archive.period_swipes), 2)) AS "Current Period Transaction Balance",
CONCAT('$', FORMAT(SUM(archive.period_interest_charges), 2)) AS "Current Period Interest Charges",
CONCAT('$', FORMAT(SUM(archive.period_fees), 2)) AS "Current Period Fees",
CONCAT('$', FORMAT(SUM(archive.period_payments_and_credits), 2)) AS "Current Period Payments"
FROM
line_of_credit_entity loc
LEFT JOIN
line_of_credit_daily_archive archive ON archive.line_of_credit_id = loc.id
AND archive.date = CURRENT_DATE()
WHERE
archive.days_past_due BETWEEN 61 AND 90
UNION ALL
SELECT
DATE_FORMAT(CURRENT_DATE(), '%m/%d/%Y') AS "As of Date",
"91+ DPD" AS "Delinquency Bands",
COUNT(loc.id) AS "Total Number of Accounts",
CONCAT('$', FORMAT(SUM(archive.total_balance), 2)) AS "Total Outstanding Balance",
CONCAT('$', FORMAT(SUM(archive.total_interest_bearing_amount), 2)) AS "Total Interest Bearing Amount",
CONCAT('$', FORMAT(SUM(archive.total_interest_charges), 2)) AS "Total Interest Amount",
CONCAT('$', FORMAT(SUM(archive.total_fees), 2)) AS "Total Fees",
CONCAT('$', FORMAT(SUM(archive.total_swipes), 2)) AS "Total Swipes",
CONCAT('$', FORMAT(SUM(archive.total_payments_and_credits), 2)) AS "Total Payments & Credits",
CONCAT('$', FORMAT(SUM(archive.total_available_credit), 2)) AS "Available Credit",
CONCAT('$', FORMAT(SUM(archive.total_credit_limit), 2)) AS "Credit Limit",
CONCAT('$', FORMAT(SUM(archive.amount_past_due), 2)) AS "Amount Past Due",
FORMAT(AVG(archive.unique_delinquencies), 0) AS "Avg Unique Delinquencies",
CONCAT('$', FORMAT(SUM(archive.highest_credit), 2)) AS "Highest Credit Amount",
CONCAT('$', FORMAT(SUM(archive.period_swipes), 2)) AS "Current Period Transaction Balance",
CONCAT('$', FORMAT(SUM(archive.period_interest_charges), 2)) AS "Current Period Interest Charges",
CONCAT('$', FORMAT(SUM(archive.period_fees), 2)) AS "Current Period Fees",
CONCAT('$', FORMAT(SUM(archive.period_payments_and_credits), 2)) AS "Current Period Payments"
FROM
line_of_credit_entity loc
LEFT JOIN
line_of_credit_daily_archive archive ON archive.line_of_credit_id = loc.id
AND archive.date = CURRENT_DATE()
WHERE
archive.days_past_due > 90;Revenue at risk
The Revenue At Risk report provides a comprehensive overview of all outstanding accounts that are in the Open status.
Sample Revenue at Risk Report.csv
Report values
| Column Name | Description |
|---|---|
| Program Name | The name of the line of credit program |
| LoC ID | The system ID of the line of credit |
| Date | The date the report was generated |
| Billing Cycle Start Date | The start date of the current billing cycle |
| Next Autopay Date | The next date that an autopay is scheduled to occur |
| Next Autopay Amount | The amount scheduled for the next autopay |
| Billing Cycle End Date | The end of the current billing cycle |
| Total Outstanding Balance | Total balance on the account |
| Total Interest Bearing Amount | The amount of the balance that bears interest |
| Total Interest Amount | The total amount of interest charges on the account |
| Total Fees | The total amount of fees charged on the account |
| Total Swipes | Total amount accrued via swipes |
| Total Payments & Credits | Total amount of payments and credits |
| Available Credit | Total available credit |
| Credit Limit | The account’s total credit limit |
| Days Past Due | The amount of days the account is past due |
| Amount Past Due | The amount the account is past due |
| Next Statement Date | The date the next statement will generate |
| Billing Cycle Due Date | The due date for the current billing cycle |
| Next Due Date | The next date the account is due |
| Line of Credit Status ID | The ID of the status on the account |
| Line of Credit Status | The text of the status on the account |
| Line of Credit Sub-Status ID | The ID of the substatus on the account |
| Line of Credit Sub-Status | The text of the substatus on the account |
| Line of Credit Age | The age of the account |
| Line of Credit Recency | The amount of days between the date on which the late payment occurred and now |
| Last Human Activity | The last date of human activity on the account |
| First Delinquency Date | First date the account was delinquent |
| Unique Delinquencies | Number of delinquencies that have occurred on the account |
| Delinquency Percent | Percent of the account that is delinquent |
| Days Delinquent | This is how many days the account is delinquent |
| ECOA | The ECOA code for the primary borrower |
| Last Updated | The last date the account was updated |
| Minimum Payment | The minimum required payment on the account |
| Remaining Minimum | Remaining minimum payment amount if applicable |
| Linked Accounts Total Balance | Sum of the total balances for all linked accounts |
| Linked Accounts Days Past Due | The highest number of days past due across all linked accounts |
| Linked Accounts Amount Past Due | The sum of the amount past due for all linked accounts |
| Highest Credit Amount | Amount of the highest credit limit |
| Payment History | String of number and letters that represent the payment history |
| Date of First Delinquency | Date of the first delinquency on the account |
| Remaining Statement Balance | The balance remaining on the statement |
| Current Period Transaction Balance | Account balance for the current period |
| Credit Utilization | Percent of utilized credit on the account |
| Current Period Interest Charges | Amount of interest charges for the current period |
| Current Period Fees | Amount of fees charges for the current period |
| Current Period Payments | Sum of payments and credits for the current period |
SQL query example
SELECT
pe.title AS "Program Name",
loc.id AS "LoC ID",
DATE_FORMAT(CURRENT_DATE(), '%m/%d/%Y') AS "Date",
DATE_FORMAT(archive.billing_cycle_start_date, '%m/%d/%Y') AS "Billing Cycle Start Date",
DATE_FORMAT(archive.next_autopay_date, '%m/%d/%Y') AS "Next Autopay Date",
CONCAT('$', FORMAT(archive.next_autopay_amount, 2)) AS "Next Autopay Amount",
DATE_FORMAT(archive.billing_cycle_end_date, '%m/%d/%Y') AS "Billing Cycle End Date",
CONCAT('$', FORMAT(archive.total_balance, 2)) AS "Total Outstanding Balance",
CONCAT('$', FORMAT(archive.total_interest_bearing_amount, 2)) AS "Total Interest Bearing Amount",
CONCAT('$', FORMAT(archive.total_interest_charges, 2)) AS "Total Interest Amount",
CONCAT('$', FORMAT(archive.total_fees, 2)) AS "Total Fees",
CONCAT('$', FORMAT(archive.total_swipes, 2)) AS "Total Swipes",
CONCAT('$', FORMAT(archive.total_payments_and_credits, 2)) AS "Total Payments & Credits",
CONCAT('$', FORMAT(archive.total_available_credit, 2)) AS "Available Credit",
CONCAT('$', FORMAT(archive.total_credit_limit, 2)) AS "Credit Limit",
archive.days_past_due AS "Days Past Due",
CONCAT('$', FORMAT(archive.amount_past_due, 2)) AS "Amount Past Due",
DATE_FORMAT(archive.next_statement_date, '%m/%d/%Y') AS "Next Statement Date",
DATE_FORMAT(archive.billing_cycle_due_date, '%m/%d/%Y') AS "Billing Cycle Due Date",
DATE_FORMAT(archive.next_due_date, '%m/%d/%Y') AS "Next Due Date",
settings.loan_status_id AS "Line of Credit Status ID",
status.title AS "Line of Credit Status",
settings.loan_sub_status_id AS "Line of Credit Sub-Status ID",
substatus.title AS "Line of Credit Sub-Status",
archive.line_of_credit_age AS "Line of Credit Age",
archive.line_of_credit_recency AS "Line of Credit Recency",
DATE_FORMAT(archive.last_human_activity, '%m/%d/%Y') AS "Last Human Activity",
DATE_FORMAT(archive.first_delinquency_date, '%m/%d/%Y') AS "First Delinquency Date",
archive.unique_delinquencies AS "Unique Delinquencies",
CONCAT(FORMAT(archive.delinquency_percent * 100, 0), '%') AS "Delinquency Percent",
archive.delinquent_days AS "Days Delinquent",
settings.ecoa_code AS "ECOA",
DATE_FORMAT(archive.lastUpdated, '%m/%d/%Y %r') AS "Last Updated",
CONCAT('$', FORMAT(archive.minimum_payment, 2)) AS "Minimum Payment",
CONCAT('$', FORMAT(archive.remaining_minimum_payment, 2)) AS "Remaining Minimum",
CONCAT('$', FORMAT(archive.linked_accounts_total_balance, 2)) AS "Linked Accounts Total Balance",
archive.linked_accounts_days_past_due AS "Linked Accounts Days Past Due",
CONCAT('$', FORMAT(archive.linked_accounts_amount_past_due, 2)) AS "Linked Accounts Amounts Past Due",
CONCAT('$', FORMAT(archive.highest_credit, 2)) AS "Highest Credit Amount",
archive.payment_history_profile AS "Payment History",
DATE_FORMAT(archive.date_of_first_delinquency, '%m/%d/%Y') AS "Date of First Delinquency",
CONCAT('$', FORMAT(archive.remaining_statement_balance, 2)) AS "Remaining Statement Balance",
CONCAT('$', FORMAT(archive.period_swipes, 2)) AS "Current Period Transaction Balance",
CONCAT(FORMAT(archive.percent_credit_utilization * 100, 0), '%') AS "Credit Utilization",
CONCAT('$', FORMAT(archive.period_interest_charges, 2)) AS "Current Period Interest Charges",
CONCAT('$', FORMAT(archive.period_fees, 2)) AS "Current Period Fees",
CONCAT('$', FORMAT(archive.period_payments_and_credits, 2)) AS "Current Period Payments"
FROM
line_of_credit_entity loc
LEFT JOIN line_of_credit_settings_entity settings ON settings.line_of_credit_id = loc.id
LEFT JOIN loan_status_entity status ON status.id = settings.loan_status_id
LEFT JOIN loan_sub_status_entity substatus ON substatus.id = settings.loan_sub_status_id
LEFT JOIN line_of_credit_daily_archive archive ON archive.line_of_credit_id = loc.id AND archive.date = CURRENT_DATE()
LEFT JOIN product_entity pe ON pe.id = loc.product_id
WHERE
settings.loan_status_id = 2;Transactions
Credits
The Credits report provides a comprehensive overview of all credit transactions that were created the previous day. It includes details such as transaction dates, amounts, and credit types.
Sample Credits Report.csv
Report values
| Column Name | Description |
|---|---|
| Program Name | The name of the program to which the line of credit is assigned |
| Transaction ID | The ID of the credit transaction |
| Account ID | Line of credit system ID |
| Transaction Date | The date the credit was applied |
| Authorization Date/Time | The date the credit was created |
| Settlement Date/Time | The settled date of the credit |
| Transaction Type | The name of the credit category Ex. Promo Credit, Charge Off Credit, etc. |
| Transaction Amount | The amount of the credit |
| Payee | The primary customer’s first and last name |
| Transaction Info | The credit’s memo |
| Status | The transaction status
|
SQL query example
SET @timezone = 'America/Denver';
SELECT
pe.title AS "Program Name",
loccte.id AS "Transaction ID",
loccte.entity_id AS "Account ID",
DATE_FORMAT(loccte.`date`, '%m/%d/%Y') AS "Transaction Date",
CONCAT(' ', DATE_FORMAT(CONVERT_TZ(loccte.created, 'UTC', @timezone), '%m/%d/%Y %r')) AS "Authorization Date/Time",
DATE_FORMAT(loccte.settled_date, '%m/%d/%Y') AS "Settlement Date/Time",
cce.title AS "Transaction Type",
CONCAT('$', FORMAT(loccte.amount, 2)) AS "Transaction Amount",
CONCAT(ce.first_name, " ", ce.last_name) AS "Payee",
loccte.title AS "Transaction Title",
SUBSTR(loccte.status, 32) AS "Status"
FROM
line_of_credit_credit_transaction_entity loccte
LEFT JOIN line_of_credit__customer locc ON locc.line_of_credit_id = loccte.entity_id AND locc.customer_role LIKE '%primary%'
LEFT JOIN customer_entity ce ON ce.id = locc.customer_id
LEFT JOIN credit_category_entity cce ON cce.id = loccte.category_id
LEFT JOIN line_of_credit_entity loce ON loce.id = loccte.entity_id
LEFT JOIN product_entity pe ON pe.id = loce.product_id
WHERE
loccte.deleted = 0
AND DATE(CONVERT_TZ(loccte.created, 'UTC', @timezone)) = DATE_SUB(DATE(CONVERT_TZ(NOW(), 'UTC', @timezone)), INTERVAL 1 DAY)Fees
The Fees report provides a comprehensive overview of all fee-related transactions that were created the previous day. It includes details such as transaction dates, amounts, and fee types.
Sample Fees Report.csv
Report values
| Column Name | Description |
|---|---|
| Program Name | The name of the program to which the line of credit is assigned |
| Account ID | Line of credit system ID |
| Transaction ID | The ID of the fee transaction |
| Transaction Date | The date the fee was applied |
| Authorization Date/Time | The date the fee was created |
| Settlement Date/Time | Not available |
| Transaction Type | The title of the charge type. Ex. Late Fee, Subscription Fee, etc. |
| Transaction Amount | The amount of the fee. |
| Transaction Info | The fee’s information.
|
| Status | The transaction status.
|
SQL Query Example
SET @timezone = 'America/Denver';
SELECT
pe.title AS "Program Name",
locce.entity_id AS "Account ID",
locce.id AS "Transaction ID",
DATE_FORMAT(locce.application_date, '%m/%d/%Y') AS "Transaction Date",
CONCAT(' ', DATE_FORMAT(CONVERT_TZ(locce.created, 'UTC', @timezone), '%m/%d/%Y %r')) AS "Authorization Date/Time",
"" AS "Settlement Date/Time",
ccte.title AS "Transaction Type",
CONCAT('$', FORMAT(locce.amount, 2)) AS "Transaction Amount",
locce.info AS "Transaction Title",
SUBSTR(locce.status, 32) AS "Status"
FROM
line_of_credit_charge_entity locce
LEFT JOIN custom_charge_type_entity ccte ON ccte.id = locce.custom_charge_type_id
LEFT JOIN line_of_credit_entity loce ON loce.id = locce.entity_id
LEFT JOIN product_entity pe ON pe.id = loce.product_id
WHERE
locce.deleted = 0
AND DATE(CONVERT_TZ(locce.created, 'UTC', @timezone)) = DATE_SUB(DATE(CONVERT_TZ(NOW(), 'UTC', @timezone)), INTERVAL 1 DAY)Finance charges
The Finance Charges report provides a comprehensive overview of all finance charge-related transactions that were created the previous day. It includes details such as transaction dates and amounts.
Sample Finance Charges Report.csv
Report values
| Column Name | Description |
|---|---|
| Program Name | The name of the program to which the line of credit is assigned |
| Account ID | Line of credit system ID |
| Transaction ID | The ID of the finance charge transaction |
| Transaction Date | The date the finance charge was applied |
| Authorization Date/Time | The date the finance charge was created |
| Settlement Date/Time | Not available |
| Transaction Type | Will always be ‘Finance Charge’ |
| Bucket | The name of the bucket to which the finance charge is applied |
| Transaction Amount | The amount of the finance charge |
| Transaction Info | The description of the finance charge
|
| Status | The transaction status
|
SQL query example
SET @timezone = 'America/Denver';
SELECT
pe.title AS "Program Name",
locfce.line_of_credit_id AS "Account ID",
locfce.id AS "Transaction ID",
DATE_FORMAT(locfce.apply_date, '%m/%d/%Y') AS "Transaction Date",
CONCAT(' ', DATE_FORMAT(CONVERT_TZ(locfce.created, 'UTC', @timezone), '%m/%d/%Y %r')) AS "Authorization Date/Time",
"" AS "Settlement Date/Time",
"Finance Charge" AS "Transaction Type",
be.title AS "Bucket",
CONCAT('$', locfce.amount) AS "Transaction Amount",
locfce.description AS "Transaction Info",
SUBSTR(locfce.status, 32) AS "Status"
FROM
line_of_credit_finance_charge_entity locfce
-- Bucket Name
LEFT JOIN bucket_entity be ON be.id = locfce.bucket_id
-- Program Name
LEFT JOIN line_of_credit_entity loce ON loce.id = locfce.line_of_credit_id
LEFT JOIN product_entity pe ON pe.id = loce.product_id
WHERE locfce.deleted = 0
AND DATE(CONVERT_TZ(locfce.created, 'UTC', @timezone)) = DATE_SUB(DATE(CONVERT_TZ(NOW(), 'UTC', @timezone)), INTERVAL 1 DAY)Payments
The Payments report provides a comprehensive overview of payment-related transactions that were created the previous day. The following payments will not be included in this report:
- Payments that belonged to accounts that have been deleted.
- Payments that have been deleted from an account.
Sample Payments Report.csv
Report values
| Column Name | Description |
|---|---|
| Program Name | The name of the program to which the line of credit is assigned |
| Account ID | Line of credit system ID |
| Transaction ID | The ID of the payment transaction |
| Transaction Date | The date the payment was applied |
| Authorization Date/Time | The date the payment was created |
| Payment Profile Name | The name of the payment profile used to make the payment |
| Transaction Type | This will always start with ‘Payment - ’ and then include the payment application type |
| Transaction Amount | The total amount of the payment |
| Principal | The portion of payment that was applied to swipes, abated swipes, and the interest bearing amount which is a portion of the ‘Other Balances’ section in the LMS |
| Interest | The portion of payment that was applied to the interest charges amount and the starting interest amount, which are both included under ‘Finance Charges’ in the LMS |
| Fees | The portion of payment that was applied to the interest bearing fee amount, interest bearing past fee amount, and the interest free fee amount, which are all included under the ‘Fees’ in the LMS |
| Extra Towards | The portion of the payment that was applied as extra towards, which is included under ‘Other Balances’ in the LMS |
| After Principal Balance | The principal balance after the payment |
| Extra Amount Description | Description of the extra application |
| Transaction Info | Text information regarding the payment |
| Edited - Old Pmt ID | This column will only be populated if the record is an updated version of a payment. This column holds the ID of the outdated version of the payment. |
| Edited - New Pmt ID | This column will only be populated if the record is an outdated version of a payment. This column holds the ID of the updated version of the payment |
| (Before) Account Status | The account’s status before the payment was made |
| (Before) Account Substatus | The account’s substatus before the payment was made |
| (After) Account Status | The account’s status after the payment was made |
| (After) Account Substatus | The account’s substatus after the payment was made |
| Status | The transaction status
|
SQL query example
SET @timezone = 'America/Denver';
SELECT
pe.title AS "Program Name",
loce.id AS "Account ID",
locpe.id AS "Transaction ID",
DATE_FORMAT(locpe.apply_date, '%m/%d/%Y') AS "Transaction Date",
CONCAT(' ', DATE_FORMAT(CONVERT_TZ(locpe.created, 'UTC', @timezone), '%m/%d/%Y %r')) AS "Authorization Date",
pae.title AS "Payment Profile Name",
CONCAT("Payment - ", locpte.title) AS "Transaction Type",
CONCAT('$', FORMAT(locpe.amount, 2)) AS "Transaction Amount",
IF(allocations.swipes + allocations.abatedswipes + allocations.other1 IS NOT NULL,
CONCAT('$', FORMAT(allocations.swipes + allocations.abatedswipes + allocations.other1, 2)),
CONCAT('$', FORMAT(allocations2.swipes + allocations2.abatedswipes + allocations2.other1, 2))) AS "Principal",
IF(allocations.interest1 + allocations.interest2 IS NOT NULL,
CONCAT('$', FORMAT(allocations.interest1 + allocations.interest2, 2)),
CONCAT('$', FORMAT(allocations2.interest1 + allocations2.interest2, 2))) AS "Interest",
IF(allocations.fee1 + allocations.fee2 + allocations.fee3 + allocations.fee4 IS NOT NULL,
CONCAT('$', FORMAT(allocations.fee1 + allocations.fee2 + allocations.fee3 + allocations.fee4, 2)),
CONCAT('$', FORMAT(allocations2.fee1 + allocations2.fee2 + allocations2.fee3 + allocations2.fee4, 2))) AS "Fees",
IF(allocations.other2 IS NOT NULL,
CONCAT('$', FORMAT(allocations.other2, 2)),
CONCAT('$', FORMAT(allocations2.other2, 2))) AS "Extra Towards",
CONCAT('$', FORMAT(locpe.after_principal_balance, 2)) AS "After Principal Balance",
locpe.extra AS "Extra Amount Description",
locpe.info AS "Transaction Info",
locpe.parent AS "Edited - Old Pmt ID",
locpe.child AS "Edited - New Pmt ID",
lse.title AS "(Before) Account Status",
lsse.title AS "(Before) Account Substatus",
lse2.title AS "(After) Account Status",
lsse2.title AS "(After) Account Sub-Status",
SUBSTR(locpe.status, 32) AS "Status"
FROM
line_of_credit_entity loce
JOIN line_of_credit_payment_entity locpe ON locpe.entity_id = loce.id
AND locpe.deleted = 0
AND locpe.reverse_date IS NULL
AND DATE(CONVERT_TZ(locpe.created, 'UTC', @timezone)) = DATE_SUB(DATE(CONVERT_TZ(NOW(), 'UTC', @timezone)), INTERVAL 1 DAY)
LEFT JOIN line_of_credit_payment_type_entity locpte ON locpte.id = locpe.payment_type_id
LEFT JOIN payment_account_entity pae ON pae.id = locpe.payment_account_id
LEFT JOIN loan_status_entity lse ON lse.id = locpe.before_account_status
LEFT JOIN loan_sub_status_entity lsse ON lsse.id = locpe.before_account_substatus
LEFT JOIN loan_status_entity lse2 ON lse2.id = locpe.after_account_status
LEFT JOIN loan_sub_status_entity lsse2 ON lsse2.id = locpe.after_account_substatus
LEFT JOIN (
SELECT
entity_id AS pmtID,
created AS alloCreated,
SUM(locpabe.swipe_amount) AS swipes,
SUM(locpabe.abated_swipe_amount) AS abatedswipes,
SUM(locpabe.other_interest_bearing_amount) AS other1,
SUM(locpabe.interest_charges_amount) AS interest1,
SUM(locpabe.starting_interest_charges_amount) AS interest2,
SUM(locpabe.interest_bearing_fee_amount) AS fee1,
SUM(locpabe.interest_bearing_past_fee_amount) AS fee2,
SUM(locpabe.interest_free_fee_amount) AS fee3,
SUM(locpabe.interest_free_past_fee_amount) AS fee4,
SUM(locpabe.extra_towards_amount) AS other2
FROM
line_of_credit_payments_allocation_breakdown_entity locpabe
WHERE
entity_type = "Entity.LineOfCreditPayment"
GROUP BY
pmtID
) allocations ON allocations.pmtID = locpe.id
LEFT JOIN (
SELECT
entity_id AS pmtID,
created AS alloCreated,
SUM(locpabe.swipe_amount) AS swipes,
SUM(locpabe.abated_swipe_amount) AS abatedswipes,
SUM(locpabe.other_interest_bearing_amount) AS other1,
SUM(locpabe.interest_charges_amount) AS interest1,
SUM(locpabe.starting_interest_charges_amount) AS interest2,
SUM(locpabe.interest_bearing_fee_amount) AS fee1,
SUM(locpabe.interest_bearing_past_fee_amount) AS fee2,
SUM(locpabe.interest_free_fee_amount) AS fee3,
SUM(locpabe.interest_free_past_fee_amount) AS fee4,
SUM(locpabe.extra_towards_amount) AS other2
FROM
line_of_credit_payments_allocation_breakdown_entity locpabe
WHERE
entity_type = "Entity.LineOfCreditPayment"
GROUP BY
pmtID
) allocations2 ON allocations2.pmtID = locpe.parent
LEFT JOIN product_entity pe ON pe.id = loce.product_id
WHERE
loce.deleted = 0Reversed Payments
The Reversed Payments report provides a comprehensive overview of payment transactions that were reversed the previous day. The following payments will not be included in this report:
- Payments that belonged to accounts that have been deleted.
- Payments that have been deleted from an account.
Refer to the Transaction Summary Report to view the payment totals for each day of the current month.
Sample Reversed Payments Report.csv
Report values
| Column Name | Description |
|---|---|
| Program Name | The name of the program to which the line of credit is assigned |
| Account ID | Line of credit system ID |
| Transaction ID | The ID of the payment transaction |
| Transaction Date | The date the payment was applied |
| Authorization Date/Time | The date the payment was created |
| Payment Profile Name | The name of the payment profile used to make the payment |
| Transaction Type | This will always start with ‘Payment - ’ and then include the payment application type |
| Transaction Amount | The total amount of the payment |
| Principal | The portion of payment that was applied to swipes, abated swipes, and the interest bearing amount which is a portion of the ‘Other Balances’ section in the LMS |
| Interest | The portion of payment that was applied to the interest charges amount and the starting interest amount, which are both included under ‘Finance Charges’ in the LMS |
| Fees | The portion of payment that was applied to the interest bearing fee amount, interest bearing past fee amount, and the interest free fee amount, which are all included under the ‘Fees’ in the LMS |
| Extra Towards | The portion of the payment that was applied as extra towards, which is included under ‘Other Balances’ in the LMS |
| After Principal Balance | The principal balance after the payment |
| Extra Amount Description | Description of the extra application |
| Transaction Info | Text information regarding the payment |
| Reversal Date | The date the payment was reversed |
| Reversal Reason | The reason for the payment reversal.
|
| Reversal Agent Comment | Text information the agent leaves as a comment when reversing a payment |
| Edited - Old Pmt ID | This column will only be populated if the record is an updated version of a payment. This column holds the ID of the outdated version of the payment. |
| Edited - New Pmt ID | This column will only be populated if the record is an outdated version of a payment. This column holds the ID of the updated version of the payment |
| (Before) Account Status | The account’s status before the payment was made |
| (Before) Account Substatus | The account’s substatus before the payment was made |
| (After) Account Status | The account’s status after the payment was made |
| (After) Account Substatus | The account’s substatus after the payment was made |
| Status | The transaction status ‘reversed’ |
SQL query example
Folder highlights
Documentation primarily consists of SQL queries and report guides detailing various aspects of line of credit data analysis.
SET @timezone = 'America/Denver';
SELECT
pe.title AS "Program Name",
locpe.entity_id AS "Account ID",
locpe.id AS "Payment ID",
CONCAT(' ', DATE_FORMAT(CONVERT_TZ(locpe.created, 'UTC', @timezone), '%m/%d/%Y %r')) AS "Created Date",
DATE_FORMAT(locpe.apply_date, '%m/%d/%Y') AS "Apply Date",
cpme.title AS "Payment Method",
CONCAT("Payment - ", locpte.title) AS "Type",
CONCAT('$', FORMAT(locpe.amount, 2)) AS "Total Payment Amount",
CONCAT('$', FORMAT(allocations.swipes + allocations.abatedswipes + allocations.other1, 2)) AS "Applied as Principal",
CONCAT('$', FORMAT(allocations.interest1 + allocations.interest2, 2)) AS "Applied as Interest",
CONCAT('$', FORMAT(allocations.fee1 + allocations.fee2 + allocations.fee3 + allocations.fee4, 2)) AS "Applied as Fees",
CONCAT('$', FORMAT(allocations.other2, 2)) AS "Applied as Extra Towards",
CONCAT('$', FORMAT(locpe.after_principal_balance, 2)) AS "After Principal Balance",
locpe.extra AS "Extra Amount Description",
locpe.info AS "Details",
IF(locpe.reverse_date IS NULL, "Yes", "No") AS "Applied",
IF(locpe.reverse_date IS NULL, "No", "Yes") AS "Reversed",
SUBSTR(locpe.reverse_reason, 17) AS "Reversal Reason",
DATE_FORMAT(locpe.reverse_date, '%m/%d/%Y') AS "Reversal Date",
locpe.comments AS "Agent Notes",
lse.title AS "(Before) Account Status",
lsse.title AS "(Before) Account Sub-Status",
lse2.title AS "(After) Account Status",
lsse2.title AS "(After) Account Sub-Status",
SUBSTR(locpe.status, 32) AS "Status"
FROM
line_of_credit_payment_entity locpe
LEFT JOIN line_of_credit_payment_type_entity locpte ON locpte.id = locpe.payment_type_id
LEFT JOIN custom_payment_method_entity cpme ON cpme.id = locpe.payment_method_id
LEFT JOIN loan_status_entity lse ON lse.id = locpe.before_account_status
LEFT JOIN loan_sub_status_entity lsse ON lsse.id = locpe.before_account_substatus
LEFT JOIN loan_status_entity lse2 ON lse2.id = locpe.after_account_status
LEFT JOIN loan_sub_status_entity lsse2 ON lsse2.id = locpe.after_account_substatus
LEFT JOIN (
SELECT
entity_id AS pmtID,
created AS alloCreated,
SUM(locpabe.swipe_amount) AS swipes,
SUM(locpabe.abated_swipe_amount) AS abatedswipes,
SUM(locpabe.other_interest_bearing_amount) AS other1,
SUM(locpabe.interest_charges_amount) AS interest1,
SUM(locpabe.starting_interest_charges_amount) AS interest2,
SUM(locpabe.interest_bearing_fee_amount) AS fee1,
SUM(locpabe.interest_bearing_past_fee_amount) AS fee2,
SUM(locpabe.interest_free_fee_amount) AS fee3,
SUM(locpabe.interest_free_past_fee_amount) AS fee4,
SUM(locpabe.extra_towards_amount) AS other2
FROM
line_of_credit_payments_allocation_breakdown_entity locpabe
WHERE
entity_type = "Entity.LineOfCreditPayment"
GROUP BY
pmtID
) allocations ON allocations.pmtID = locpe.id
LEFT JOIN line_of_credit_entity loce ON loce.id = locpe.entity_id
LEFT JOIN product_entity pe ON pe.id = loce.product_id
WHERE
locpe.deleted = 0
AND locpe.child IS NULL
AND DATE(locpe.reverse_date) = DATE_SUB(DATE(CONVERT_TZ(NOW(), 'UTC', @timezone)), INTERVAL 1 DAY)Swipes
The Swipes report provides a comprehensive overview of purchase-related transactions that were created the previous day. It includes details such as transaction dates, amounts, and bucket names.
Sample Swipes Report.csv
Report values
| Column Name | Description |
|---|---|
| Program Name | The name of the program to which the line of credit is assigned |
| Account ID | Line of credit system ID |
| Transaction ID | The ID of the transaction |
| Transaction Date | The date the purchase was applied |
| Authorization Date/Time | The date the purchase was created |
| Settled Date | The date the purchase was settled |
| Transaction Type | This will always start with ‘Purchase- ’ and then include the swipe category |
| Bucket Name | The bucket name where the transaction was routed |
| Transaction Amount | The total amount of the purchase. |
| Payee | If the purchase was made with a card linked to the account, this field will display the cardholder’s name. Otherwise, it will show the primary borrower’s name on the account |
| Status | The transaction status
|
| Card UUID | This is the merchant_card_id from the card_entity table and will match the loanpro_card_uuid in the Settlement Report |
SQL query example
SET @timezone = 'America/Denver';
SELECT
pe.title AS "Program Name",
locse.line_of_credit_id AS "Account ID",
locse.id AS "Transaction ID",
DATE_FORMAT(locse.apply_date, '%m/%d/%Y') AS "Transaction Date",
CONCAT(' ', DATE_FORMAT(CONVERT_TZ(locse.created, 'UTC', @timezone), '%m/%d/%Y %r')) AS "Authorization Date/Time",
DATE_FORMAT(locse.settled_date, '%m/%d/%Y') AS "Settled Date",
IF(locse.swipe_category_id IS NOT NULL, CONCAT("Purchase - ", ce3.title), "Purchase") AS "Transaction Type - Swipe Category",
be.title AS "Bucket Name",
CONCAT('$', FORMAT(locse.amount, 2)) AS "Transaction Amount",
IF(locse.cardholder IS NOT NULL, CONCAT(ce2.first_name, " ", ce2.last_name), CONCAT(ce.first_name, " ", ce.last_name)) AS "Payee",
locse.memo AS "Transaction Title",
SUBSTR(locse.status, 32) AS "Status",
card.merchant_card_id AS "Card UUID"
FROM
line_of_credit_swipe_entity locse
LEFT JOIN line_of_credit__customer locc ON locc.line_of_credit_id = locse.line_of_credit_id AND locc.customer_role LIKE '%primary%'
LEFT JOIN customer_entity ce ON ce.id = locc.customer_id
LEFT JOIN customer_entity ce2 ON ce2.id = locse.cardholder
LEFT JOIN bucket_entity be ON be.id = locse.bucket_id
LEFT JOIN category_entity ce3 ON ce3.id = locse.swipe_category_id
LEFT JOIN line_of_credit_entity loce ON loce.id = locse.line_of_credit_id
LEFT JOIN product_entity pe ON pe.id = loce.product_id
LEFT JOIN card_entity card ON card.id = locse.card_id
WHERE
locse.deleted = 0
AND DATE(CONVERT_TZ(locse.created, 'UTC', @timezone)) = DATE_SUB(DATE(CONVERT_TZ(NOW(), 'UTC', @timezone)), INTERVAL 1 DAY)Transactions detailed
The Transactions Detailed report report provides a comprehensive overview of all purchase, fee, payment, and finance charge transactions that were created the previous day. This report will not include the older versions of edited payments or any reversed payments.
Sample Transaction Detailed Report.csv
Report values
| Column Name | Description |
|---|---|
| Program Name | The name of the program to which the line of credit is assigned |
| Transaction ID | The ID of the transaction |
| Account ID | Line of credit system ID |
| Transaction Date | The date the transaction was created |
| Transaction Type | This will be populated with one of the following: Purchase, Fee, Credit, Payment, Finance Charge |
| Amount | The total amount of the transaction |
| Payee | For swipes and credits, the customer’s name will populate in this field. For fees, payments, and finance charges, ‘Lender’ will populate in this field. |
| Transaction Info | This field will be the purchase’s memo, the info text for a fee, the title of the credit, the info text for a payment, or the description of the finance charge |
SQL query example
SET @timezone = 'America/Denver';
SELECT
program.title AS "Program Name",
swipe.id AS "Transaction ID",
swipe.line_of_credit_id AS "Account ID",
CONCAT(' ', DATE_FORMAT(CONVERT_TZ(swipe.created, 'UTC', @timezone), '%m/%d/%Y %r')) AS "Transaction Date",
"Purchase" AS "Transaction Type",
CONCAT('$', FORMAT(swipe.amount, 2)) AS "Amount",
IF(swipe.cardholder IS NOT NULL, CONCAT(ce2.first_name, " ", ce2.last_name), CONCAT(customer.first_name, " ", customer.last_name)) AS "Payee",
swipe.memo AS "Transaction Info"
FROM
line_of_credit_swipe_entity swipe
LEFT JOIN line_of_credit_entity loc ON loc.id = swipe.line_of_credit_id
LEFT JOIN line_of_credit__customer locc ON locc.line_of_credit_id = loc.id
LEFT JOIN customer_entity customer ON customer.id = locc.customer_id
LEFT JOIN customer_entity ce2 ON ce2.id = swipe.cardholder
LEFT JOIN product_entity program ON program.id = loc.product_id
WHERE
DATE(CONVERT_TZ(swipe.created, 'UTC', @timezone)) = DATE_SUB(DATE(CONVERT_TZ(NOW(), 'UTC', @timezone)), INTERVAL 1 DAY)
UNION ALL
SELECT
program.title AS "Program Name",
charge.id AS "Transaction ID",
charge.entity_id AS "Account ID",
CONCAT(' ', DATE_FORMAT(CONVERT_TZ(charge.created, 'UTC', @timezone), '%m/%d/%Y %r')) AS "Transaction Date",
"Fee" AS "Transaction Type",
CONCAT('$', FORMAT(charge.amount, 2)) AS "Amount",
"Lender" AS "Payee",
charge.info AS "Transaction Info"
FROM
line_of_credit_charge_entity charge
LEFT JOIN line_of_credit_entity loc ON loc.id = charge.entity_id
LEFT JOIN product_entity program ON program.id = loc.product_id
WHERE
charge.entity_type = 'Entity.LineOfCredit'
AND DATE(CONVERT_TZ(charge.created, 'UTC', @timezone)) = DATE_SUB(DATE(CONVERT_TZ(NOW(), 'UTC', @timezone)), INTERVAL 1 DAY)
AND charge.deleted = 0
UNION ALL
SELECT
program.title AS "Program Name",
credit.id AS "Transaction ID",
credit.entity_id AS "Account ID",
CONCAT(' ', DATE_FORMAT(CONVERT_TZ(credit.created, 'UTC', @timezone), '%m/%d/%Y %r')) AS "Transaction Date",
"Credit" AS "Transaction Type",
CONCAT('$', FORMAT(credit.amount * -1, 2)) AS "Amount",
CONCAT(customer.first_name, " ", customer.last_name) AS "Payee",
credit.title AS "Transaction Info"
FROM
line_of_credit_credit_transaction_entity credit
LEFT JOIN line_of_credit_entity loc ON loc.id = credit.entity_id
LEFT JOIN line_of_credit__customer locc ON locc.line_of_credit_id = loc.id
LEFT JOIN customer_entity customer ON customer.id = locc.customer_id
LEFT JOIN product_entity program ON program.id = loc.product_id
WHERE
credit.entity_type = 'Entity.LineOfCredit'
AND DATE(CONVERT_TZ(credit.created, 'UTC', @timezone)) = DATE_SUB(DATE(CONVERT_TZ(NOW(), 'UTC', @timezone)), INTERVAL 1 DAY)
UNION ALL
SELECT
program.title AS "Program Name",
payment.id AS "Transaction ID",
payment.entity_id AS "Account ID",
CONCAT(' ', DATE_FORMAT(CONVERT_TZ(payment.created, 'UTC', @timezone), '%m/%d/%Y %r')) AS "Transaction Date",
"Payment" AS "Transaction Type",
CONCAT('$', FORMAT(payment.amount * -1, 2)) AS "Amount",
"Lender" AS "Payee",
payment.info AS "Transaction Info"
FROM
line_of_credit_payment_entity payment
LEFT JOIN line_of_credit_entity loc ON loc.id = payment.entity_id
LEFT JOIN product_entity program ON program.id = loc.product_id
WHERE
payment.entity_type = 'Entity.LineOfCredit'
AND payment.child IS NULL
AND payment.reverse_date IS NULL
AND DATE(CONVERT_TZ(payment.created, 'UTC', @timezone)) = DATE_SUB(DATE(CONVERT_TZ(NOW(), 'UTC', @timezone)), INTERVAL 1 DAY)
UNION ALL
SELECT
program.title AS "Program Name",
interest.id AS "Transaction ID",
interest.line_of_credit_id AS "Account ID",
CONCAT(' ', DATE_FORMAT(CONVERT_TZ(interest.created, 'UTC', @timezone), '%m/%d/%Y %r')) AS "Transaction Date",
"Finance Charge" AS "Transaction Type",
CONCAT('$', FORMAT(interest.amount, 2)) AS "Amount",
"Lender" AS "Payee",
interest.description AS "Transaction Info"
FROM
line_of_credit_finance_charge_entity interest
LEFT JOIN line_of_credit_entity loc ON loc.id = interest.line_of_credit_id
LEFT JOIN product_entity program ON program.id = loc.product_id
WHERE
interest.deleted = 0
AND DATE(CONVERT_TZ(interest.created, 'UTC', @timezone)) = DATE_SUB(DATE(CONVERT_TZ(NOW(), 'UTC', @timezone)), INTERVAL 1 DAY)Transaction summary
The Transaction Summary report provides a comprehensive summary of all purchase, fee, payment, and finance charge transaction totals for each day of the current month. This report will not include the older versions of edited payments or any reversed payments.
Sample Transaction Summary Report.csv
Report values
| Column Name | Description |
|---|---|
| Date | The first record will display the first day of the month, followed by subsequent entries up to yesterday's date. If today is the first of the month, the report will pull the previous month’s records |
| Payments Count | The total count of payments for that day |
| Payments Total | The sum total of payments made on that day |
| Purchase Count | The total count of purchases for that day |
| Purchase Total | The sum total of purchases made on that day |
| Fee Count | The total count of fees for that day |
| Fee Total | The sum total of fees made on that day |
| Credit Count | The total count of credits for that day |
| Credit Total | The sum total of credits made on that day |
| Finance Charge Count | The total count of finance charges for that day |
| Finance Charge Total | The sum total of finance charges made on that day |
SQL query example
SET @timezone = 'America/Denver';
WITH date_generator AS (
SELECT
CASE
WHEN DAY(DATE(CONVERT_TZ(NOW(), 'UTC', @timezone))) = 1 THEN DATE_FORMAT(DATE(CONVERT_TZ(NOW(), 'UTC', @timezone)) - INTERVAL 1 MONTH, '%Y-%m-01') + INTERVAL daynum DAY
ELSE DATE_FORMAT(DATE(CONVERT_TZ(NOW(), 'UTC', @timezone)), '%Y-%m-01') + INTERVAL daynum DAY
END AS day
FROM
(SELECT t*10 + u AS daynum
FROM (SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) A,
(SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) B
ORDER BY daynum) days
WHERE
(DAY(DATE(CONVERT_TZ(NOW(), 'UTC', @timezone))) = 1 AND DATE_FORMAT(DATE(CONVERT_TZ(NOW(), 'UTC', @timezone)) - INTERVAL 1 MONTH, '%Y-%m-01') + INTERVAL daynum DAY <= LAST_DAY(DATE(CONVERT_TZ(NOW(), 'UTC', @timezone)) - INTERVAL 1 MONTH))
OR (DAY(DATE(CONVERT_TZ(NOW(), 'UTC', @timezone))) > 1 AND DATE_FORMAT(DATE(CONVERT_TZ(NOW(), 'UTC', @timezone)), '%Y-%m-01') + INTERVAL daynum DAY < DATE(CONVERT_TZ(NOW(), 'UTC', @timezone)))
),
payment_summary AS (
SELECT DATE(created) AS day,
COUNT(id) AS payment_count,
SUM(amount) AS payment_total
FROM line_of_credit_payment_entity
WHERE child IS NULL AND reverse_date IS NULL AND deleted = 0
GROUP BY day
),
swipe_summary AS (
SELECT DATE(created) AS day,
COUNT(id) AS swipe_count,
SUM(amount) AS swipe_total
FROM line_of_credit_swipe_entity
WHERE revert_date IS NULL AND deleted = 0
GROUP BY day
),
credit_summary AS (
SELECT DATE(created) AS day,
COUNT(id) AS credit_count,
SUM(amount) AS credit_total
FROM line_of_credit_credit_transaction_entity
WHERE revert_date IS NULL AND deleted = 0
GROUP BY day
),
fee_summary AS (
SELECT DATE(created) AS day,
COUNT(id) AS fee_count,
SUM(amount) AS fee_total
FROM line_of_credit_charge_entity
WHERE revert_date IS NULL AND deleted = 0
GROUP BY day
),
daily_interest AS (
SELECT DATE(created) AS day,
COUNT(id) AS interest_count,
SUM(amount) AS interest_total
FROM line_of_credit_finance_charge_entity
WHERE deleted = 0
GROUP BY day
)
SELECT
DATE_FORMAT(dg.day, '%m/%d/%Y') AS "Date",
COALESCE(payment.payment_count, 0) AS "Payments Count",
CONCAT('$', FORMAT(COALESCE(payment.payment_total * -1, 0), 2)) AS "Payments Total",
COALESCE(swipe.swipe_count, 0) AS "Purchase Count",
CONCAT('$', FORMAT(COALESCE(swipe.swipe_total, 0), 2)) AS "Purchase Total",
COALESCE(fee.fee_count, 0) AS "Fee Count",
CONCAT('$', FORMAT(COALESCE(fee.fee_total, 0), 2)) AS "Fee Total",
COALESCE(credit.credit_count, 0) AS "Credit Count",
CONCAT('$', FORMAT(COALESCE(credit.credit_total * -1, 0), 2)) AS "Credit Total",
COALESCE(daily_interest.interest_count, 0) AS "Finance Charges Count",
CONCAT('$', FORMAT(COALESCE(daily_interest.interest_total, 0), 2)) AS "Finance Charges Total",
CONCAT('$', FORMAT(
-COALESCE(payment.payment_total, 0)
+ COALESCE(swipe.swipe_total, 0)
+ COALESCE(fee.fee_total, 0)
- COALESCE(credit.credit_total, 0)
+ COALESCE(daily_interest.interest_total, 0), 2
)) AS "EOD Total"
FROM
date_generator dg
LEFT JOIN payment_summary payment ON dg.day = payment.day
LEFT JOIN swipe_summary swipe ON dg.day = swipe.day
LEFT JOIN fee_summary fee ON dg.day = fee.day
LEFT JOIN credit_summary credit ON dg.day = credit.day
LEFT JOIN daily_interest daily_interest ON dg.day = daily_interest.day
ORDER BY dg.day;Additional reports
Complaints
The Complaints reports will only pull information if they are used in tandem with the Complaints Process for Line of Credit. There are three reports that can be pulled for complaints that all have the same column names:
- Complaints Received - pulls all Agent Action & Results with the ‘Complaint Received’ entry created the prior day.
- Complaints Investigated - pulls all Agent Action & Results with the ‘Complaint Investigated’ entry created the prior day.
- All Complaints - pulls all complaint Agent Action & Results logged for all accounts with no specified time frame.
Sample Complaints (ALL).csv
Sample Complaints (Received).csv
Report values
| Column Name | Description |
|---|---|
| Line of Credit ID | Line of credit system ID |
| Complaint Entry ID | The ID of the Agent Action & Result entry |
| Action | The text selection of the action Expected Values: Complaint Received, Complaint Type, Complaint Resolution Timeline, Complaint Investigation, Notified Complainant, Complaint Remediation Type, & Complaint Remediation. |
| Result | The text selection of the result Expected Values: Complaint Received: Email, SMS, In Writing, etc. Complaint Type: Product/Service, Regulatory/Legal, Security Complaint Resolution Timeline: 5 Business Days or 72 Hours Complaint Investigation: Complete Notified Complainant: Email, SMS, In Writing, etc. Complaint Remediation Type: Employee Retraining, Policy Adjustments, System Update(s) Complaint Remediation: Complete |
| Note | The note entered by the agent |
| Created Date | The date and time the Action & Result was created |
| Created By | The name of the agent who created the Action & Result |
| Last Updated Date | The date and time the Action & Result was last updated |
SQL query example
SELECT
arlocne.line_of_credit_id AS "Line of Credit ID",
arlocne.id AS "Complaint Entry ID",
arlocse.action_text AS "Action",
arlocse.result_text AS "Result",
REGEXP_REPLACE(arlocne.note, '<[^>]*>', '') AS "Note",
arlocne.created AS "Created Date",
arlocne.create_user_name AS "Created By",
arlocse.lastUpdated AS "Last Updated Date"
FROM
action_result_line_of_credit_selection_entity arlocse
LEFT JOIN action_result_line_of_credit_note_entity arlocne ON arlocne.id = arlocse.note_id
WHERE arlocse.action_text LIKE "%Complain%"
ORDER BY arlocne.line_of_credit_id, arlocne.id, arlocne.createdCredit limit monitoring
The Credit Limit Monitoring report tracks credit usage relative to assigned limits. It highlights accounts approaching or exceeding their limits, helping to identify potential risks, prevent over-limit fees, and support responsible credit management. By default, this report will pull a snapshot of the values from the Line of Credit Daily Archive for the current date that generally reflect the prior day’s account activity.
Sample Credit Limit Monitoring Report.csv
Report values
| Column Name | Description |
|---|---|
| Account ID | The system ID of the line of credit account |
| Account Status | The account status text of the account Expected Values: Underwriting, Open, Closed, Paid Off, etc. |
| Name on Account | The name attached to the line of credit account |
| Billing Period Start Date | The start date of the current period |
| Billing Period End Date | The end date of the current period |
| Balance | The current balance on the account |
| Available Credit | The current available credit on the account |
| Total Credit Limit | The total credit limit for the line of credit. |
| Credit Utilization Percent | The percentage of the total credit used within the current period |
SQL query example
SELECT
archive.line_of_credit_id AS "Account ID",
archive.line_of_credit_status_text AS "Account Status",
CONCAT(ce.first_name, ' ', ce.last_name) AS "Name on Account",
DATE_FORMAT(archive.billing_cycle_start_date, '%m/%d/%Y') AS "Billing Period Start Date",
DATE_FORMAT(archive.billing_cycle_end_date, '%m/%d/%Y') AS "Billing Period End Date",
archive.total_balance AS "Balance",
archive.total_available_credit AS "Available Credit",
archive.total_credit_limit AS "Total Credit Limit",
archive.percent_credit_utilization AS "Credit Utilization %"
FROM
line_of_credit_daily_archive archive
JOIN line_of_credit__customer locc ON locc.line_of_credit_id = archive.line_of_credit_id
JOIN customer_entity ce ON ce.id = locc.customer_id
WHERE archive.`date` = CURRENT_DATE()
ORDER BY archive.line_of_credit_idCustomer information
The Customer Information report provides a comprehensive overview of all customer-related information and activity. It includes details such as contact information, customer portal login activity, and payment activity.
Sample Customer Information & Activity Report.csv
Report values
| Column Name | Description |
|---|---|
| Account ID | Line of credit system ID |
| Customer ID | The ID of the customer |
| Role | Either ‘primary’ or ‘secondary’ will be populated |
| First Name | The borrower’s first name |
| Middle Name | The borrower’s middle name |
| Last Name | The borrower’s last name |
| Phone Number | The borrower’s phone number |
| The borrower’s email address | |
| Street Address 1 | The borrower’s street address |
| Street Address 2 | The borrower’s secondary unit designator Ex. ‘Apt B12’, ‘Unit 6’, or ‘#5C’, etc. |
| City | The name of the city that the borrower resides in |
| State | The name of the state that the borrower resides in |
| Zip Code | The zip code of the borrower’s address |
| Last Login | The most recent date the customer attempted to log in. |
| Logins in Last 30 Days | The number of times a customer attempted to sign on in the last 30 days |
| Payment Recency | The number of days ago that the borrower made a payment. |
| Total Payment Count | Total count of payments the borrower has made |
| Pmt Count in Last 30 Days | The total count of payments the borrower has made in the last 30 days |
| Pmt Count in Last 60 Days | The total count of payments the borrower has made in the last 60 days |
| Pmt Count in Last 90 Days | The total count of payments the borrower has made in the last 90 days. |
| Current Credit Status | The credit status description of the account associated with the customer. If the credit status on the account in the LMS is ‘0 - AUTO’, this field will automatically populate with the status that best matches the behavior of the account. If the credit status on the account is ‘DA’ or ‘DF in the LMS’, the credit status will pull the previous credit status. |
| AutoPay Setup | If the next autopay date is populated, this column will say ‘Yes’. Otherwise, ‘No’ will be shown. |
SQL query example
SELECT
loce.id AS "Account ID",
locc.customer_id AS "Customer ID",
SUBSTRING(locc.customer_role, 19) AS "Role",
ce.first_name AS "First Name",
ce.middle_name AS "Middle Name",
ce.last_name AS "Last Name",
CONCAT(SUBSTR(cpe.phone, 1, 3), '-', SUBSTR(cpe.phone, 4, 3), '-', SUBSTR(cpe.phone, 7, 4)) AS "Phone Number",
ce.email AS "Email",
ae.address1 AS "Street Address 1",
ae.address2 AS "Street Address 2",
ae.city AS "City",
SUBSTR(ae.state, 11) AS "State",
ae.zipcode AS "Zip Code",
DATE_FORMAT(cla.lastLogin, '%m/%d/%Y %r') AS "Last Login",
logins30.loginCount AS "Logins in Last 30 Days",
locda.line_of_credit_recency AS "Payment Recency",
payments.pmtCount AS "Total Payment Count",
payments30.pmtCount30 AS "Pmt Count in Last 30 Days",
payments60.pmtCount60 AS "Pmt Count in Last 60 Days",
payments90.pmtCount90 AS "Pmt Count in Last 90 Days",
CASE
WHEN SUBSTRING(locda.credit_status, 19) = '11' THEN "11- Current account"
WHEN SUBSTRING(locda.credit_status, 19) = '13' THEN "13 - Paid or closed account/zero balance"
WHEN SUBSTRING(locda.credit_status, 19) = '5' THEN "5 - Account transferred to another office"
WHEN SUBSTRING(locda.credit_status, 19) = '61' THEN "61 - Account paid in full, was a voluntary surrender"
WHEN SUBSTRING(locda.credit_status, 19) = '62' THEN "62 - Account paid in full, was a collection account"
WHEN SUBSTRING(locda.credit_status, 19) = '63' THEN "63 - Account paid in full, was repossession"
WHEN SUBSTRING(locda.credit_status, 19) = '64' THEN "64 - Account paid in full, was a charge-off"
WHEN SUBSTRING(locda.credit_status, 19) = '71' THEN "71 - Account 30-59 days past the due date"
WHEN SUBSTRING(locda.credit_status, 19) = '78' THEN "78 - Account 60-89 days past the due date"
WHEN SUBSTRING(locda.credit_status, 19) = '80' THEN "80 - Account 90-119 days past the due date"
WHEN SUBSTRING(locda.credit_status, 19) = '82' THEN "82 - Account 120-149 days past the due date"
WHEN SUBSTRING(locda.credit_status, 19) = '83' THEN "83 - Account 150-179 days past the due date"
WHEN SUBSTRING(locda.credit_status, 19) = '84' THEN "84 - Account 180 days or more past the due date"
WHEN SUBSTRING(locda.credit_status, 19) = '93' THEN "93 - Account assigned to internal or external collections"
WHEN SUBSTRING(locda.credit_status, 19) = '95' THEN "95 - Voluntary surrender"
WHEN SUBSTRING(locda.credit_status, 19) = '96' THEN "96 - Merchandise was repossessed: there may be a balance due"
WHEN SUBSTRING(locda.credit_status, 19) = '97' THEN "97 - Unpaid balance reported as a loss (charge-off)"
WHEN SUBSTRING(locda.credit_status, 19) = '99' THEN "99 - Do not send"
WHEN SUBSTRING(locda.credit_status, 19) = 'DA' THEN "DA - Deleted entire account (for reasons other than fraud)"
WHEN SUBSTRING(locda.credit_status, 19) = 'DF' THEN "DF - Delete entire account due to confirmed fraud (fraud investigation complete)"
ELSE ""
END AS "Current Credit Status",
IF (locda.next_autopay_date = '0000-00-00' OR locda.next_autopay_date IS NULL, "No", "Yes") AS "AutoPay Setup"
FROM
line_of_credit_entity loce
LEFT JOIN line_of_credit__customer locc ON locc.line_of_credit_id = loce.id
JOIN customer_entity ce ON ce.id = locc.customer_id
LEFT JOIN customer_phone_entity cpe ON cpe.entity_id = ce.id AND cpe.deleted = 0
LEFT JOIN address_entity ae ON ae.id = ce.mail_address_id
LEFT JOIN (
SELECT customer_id cID, MAX(attempt_time) lastLogin
FROM customer_login_attempts
GROUP BY customer_id
) cla ON cla.cID = ce.id
LEFT JOIN (
SELECT customer_id cID, COUNT(*) loginCount
FROM customer_login_attempts
WHERE attempt_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY cID
) logins30 ON logins30.cID = ce.id
LEFT JOIN line_of_credit_daily_archive locda ON locda.line_of_credit_id = loce.id AND locda.`date` = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
LEFT JOIN (
SELECT entity_id locID, COUNT(*) pmtCount
FROM line_of_credit_payment_entity
WHERE reverse_date IS NULL AND child IS NULL
GROUP BY locID
) payments ON payments.locID = loce.id
LEFT JOIN (
SELECT entity_id locID, COUNT(*) pmtCount30
FROM line_of_credit_payment_entity
WHERE reverse_date IS NULL AND child IS NULL AND apply_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY locID
) payments30 ON payments30.locID = loce.id
LEFT JOIN (
SELECT entity_id locID, COUNT(*) pmtCount60
FROM line_of_credit_payment_entity
WHERE reverse_date IS NULL AND child IS NULL AND apply_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 60 DAY)
GROUP BY locID
) payments60 ON payments60.locID = loce.id
LEFT JOIN (
SELECT entity_id locID, COUNT(*) pmtCount90
FROM line_of_credit_payment_entity
WHERE reverse_date IS NULL AND child IS NULL AND apply_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
GROUP BY locID
) payments90 ON payments90.locID = loce.id
WHERE
loce.deleted = 0
AND loce.archived = 0;Disputes & chargebacks
The Disputes & Chargebacks report provides a comprehensive overview of all dispute and chargeback activity. It includes details for the dispute, the disputed transaction, the provisional credit, and the chargeback credit associated with the account. These details may include IDs, statuses, amounts, dates, and merchant information.
Sample Disputes & Chargebacks Report.csv
Report values
| Column Name | Description |
|---|---|
| Program Name | The name of the program to which the line of credit is assigned |
| Account ID | Line of credit system ID |
| Card ID | The ID of the card that initiated the disputed purchase |
| Dispute ID | The ID of the dispute |
| Dispute Category ID | The ID of the dispute category |
| Dispute Logged | The date time that the dispute was logge |
| Dispute Last Updated | The datetime that the dispute was last updated |
| Dispute Status | The status of the dispute Ex. Approved, Pending, Denied |
| Dispute Initiated Date | The start date of the dispute |
| Dispute End Date | The end date of the dispute. |
| Disputed Amount | The amount of the dispute |
| Transaction Original Authorized Amount | Original authorized amount of the disputed transaction |
| Transaction Status | The status of the disputed transaction Ex. active, settled, etc. |
| Transaction Date | The date the transaction applied to the account |
| Transaction Settled Date | The date the transaction settled on the account |
| Transaction Type | The type of the disputed transaction This will always be ‘Swipe’. |
| Transaction ID | The ID of the disputed transaction |
| Transaction Billing Cycle | The billing cycle the disputed transaction occurred in |
| Transaction Settled Billing Cycle | The billing cycle the disputed transaction settled in |
| Merchant Name | The name of the merchant for the disputed transaction |
| Raw Transaction Memo | Information memo on the disputed transaction Ex. The Home Depot #24378 Glendale, AZ 85302 |
| MCC | The merchant category code for the disputed transaction Ex. 918, 816, 921, etc. |
| Merchant Category | The merchant category for the disputed transaction Ex. Groceries, Entertainment, etc. |
| Raw Metadata | The raw transaction feed of the disputed transaction Ex. The Home Depot #24378 Glendale, AZ 85302 |
| Provisional Credit ID | The ID of the provisional credit attached to the dispute |
| Provisional Credit Date | The date of the provisional credit attached to the dispute |
| Provisional Credit Amount | The amount of the provisional credit attached to the dispute |
| Provisional Credit Status | The status of the provisional credit attached to the dispute Ex. active, reversed, etc. |
| Provisional Reversal Date | The revert date of the provisional credit |
| Provisional Credit Category | The credit category associated with the provisional credit |
| Chargeback Credit ID | The ID of the chargeback credit In the cases where there are more than one chargeback credit for a single dispute (there may be an extra credit for an interest charge adjustment), the IDs of the chargebacks will be comma separated in this field. The chargeback credit and the provisional credit could have the same ID if the dispute was approved in the same period that the dispute was created. |
| Chargeback Credit Date | The date the chargeback credit was applied |
| Chargeback Credit Total | This is a sum total of all chargeback credits for the disputed transaction |
| Chargeback Credit Statuses | This displays the status for each chargeback credit Ex. active, reversed, etc. |
| Chargeback Reversal Date | The datetime of the chargeback credit reversal |
| Chargeback Credit Category | The credit category assigned to the chargeback credit. This may be a comma separated list if there are multiple chargeback credits for one dispute Ex. Chargeback Credit |
SQL query example
SET @timezone = 'America/Denver';
SELECT
pe.title AS "Program Name",
de.entity_id AS "Account ID",
locse.card_id AS "Card ID",
de.id AS "Dispute ID",
de.category_id AS "Dispute Category ID",
CONCAT(' ', DATE_FORMAT(CONVERT_TZ(de.created, 'UTC', @timezone), '%m/%d/%Y %r')) AS "Dispute Logged",
CONCAT(' ', DATE_FORMAT(CONVERT_TZ(de.lastUpdated, 'UTC', @timezone), '%m/%d/%Y %r')) AS "Dispute Last Updated",
dse.status_name AS "Dispute Status",
DATE_FORMAT(de.dispute_start_date, '%m/%d/%Y') AS "Dispute Intiated Date",
DATE_FORMAT(de.dispute_end_date, '%m/%d/%Y') AS "Dispute End Date",
CONCAT('$', de.original_dispute_amount) AS "Disputed Amount",
CONCAT('$', locse.amount) AS "Transaction Amount",
CONCAT('$', locse.original_auth_amount) AS "Transaction Original Authorized Amount",
SUBSTR(locse.status, 32) AS "Transaction Status",
DATE_FORMAT(locse.apply_date, '%m/%d/%Y') AS "Transaction Date",
locse.settled_date AS "Transaction Settled Date",
SUBSTR(de.disputed_transaction_type, 20) AS "Transaction Type",
de.disputed_transaction_id AS "Transaction ID",
locse.billing_cycle AS "Transaction Billing Cycle",
locse.settled_period_number AS "Transaction Settled Billing Cycle",
locse.merchant_name AS "Merchant Name",
locse.memo AS "Raw Transaction Memo",
locse.merchant_category_code AS "MCC",
locse.merchant_categories AS "Merchant Category",
locse.raw_transaction_feed AS "Raw Metadata",
de.provisional_credit_id AS "Provisional Credit ID",
DATE_FORMAT(provCred.`date`, '%m/%d/%Y') AS "Provisional Credit Date",
CONCAT('$', provCred.amount) AS "Provisional Credit Amount",
SUBSTR(provCred.status, 32) AS "Provisional Credit Status",
DATE_FORMAT(provCred.revert_date, '%m/%d/%Y') AS "Provisional Reversal Date",
cce.title AS "Provisional Credit Category",
CASE
WHEN SUBSTR(provCred.status, 32) = 'reversed' AND dse.status_name = 'Approved' THEN chargeback.chargebackIDs
WHEN SUBSTR(provCred.status, 32) = 'active' AND dse.status_name = 'Approved' THEN de.provisional_credit_id
ELSE ''
END AS "Chargeback Credit IDs",
CASE
WHEN SUBSTR(provCred.status, 32) = 'reversed' AND dse.status_name = 'Approved' THEN DATE_FORMAT(chargeback.chargebackDate, '%m/%d/%Y')
WHEN SUBSTR(provCred.status, 32) = 'active' AND dse.status_name = 'Approved' THEN DATE_FORMAT(provCred.`date`, '%m/%d/%Y')
ELSE ''
END AS "Chargeback Credit Date",
CASE
WHEN SUBSTR(provCred.status, 32) = 'reversed' AND dse.status_name = 'Approved' THEN CONCAT('$', chargeback.chargebackTotal)
WHEN SUBSTR(provCred.status, 32) = 'active' AND dse.status_name = 'Approved' THEN CONCAT('$', provCred.amount)
ELSE ''
END AS "Chargeback Credit Total",
CASE
WHEN SUBSTR(provCred.status, 32) = 'reversed' AND dse.status_name = 'Approved' THEN chargeback.chargebackStatus
WHEN SUBSTR(provCred.status, 32) = 'active' AND dse.status_name = 'Approved' THEN SUBSTR(provCred.status, 32)
ELSE ''
END AS "Chargeback Credit Statuses",
CASE
WHEN SUBSTR(provCred.status, 32) = 'reversed' AND dse.status_name = 'Approved' THEN DATE_FORMAT(chargeback.chargebackRevert, '%m/%d/%Y %r')
WHEN SUBSTR(provCred.status, 32) = 'active' AND dse.status_name = 'Approved' THEN DATE_FORMAT(provCred.revert_date, '%m/%d/%Y')
ELSE ''
END AS "Chargeback Reversal Date",
CASE
WHEN SUBSTR(provCred.status, 32) = 'reversed' AND dse.status_name = 'Approved' THEN chargeback.chargebackCategories
WHEN SUBSTR(provCred.status, 32) = 'active' AND dse.status_name = 'Approved' THEN cce.title
ELSE ''
END AS "Chargeback Credit Category"
FROM
line_of_credit_entity loce
-- Only disputes created the day before
JOIN dispute_entity de ON de.entity_id = loce.id AND de.disputed_transaction_type = "Entity.LineOfCreditSwipe"
AND DATE(CONVERT_TZ(de.created, 'UTC', @timezone)) = DATE_SUB(DATE(CONVERT_TZ(NOW(), 'UTC', @timezone)), INTERVAL 1 DAY)
-- Dispute Status
LEFT JOIN dispute_status_entity dse ON dse.id = de.status_id
-- Transaction Information
LEFT JOIN line_of_credit_swipe_entity locse ON locse.id = de.disputed_transaction_id
-- Program Name
LEFT JOIN product_entity pe ON pe.id = loce.product_id
-- Provisional Credit
LEFT JOIN line_of_credit_credit_transaction_entity provCred ON provCred.id = de.provisional_credit_id
-- Provisional Credit Category Name
LEFT JOIN credit_category_entity cce ON cce.id = provCred.category_id
-- Chargeback Credits
LEFT JOIN (SELECT
GROUP_CONCAT(loccte.id ORDER BY loccte.id SEPARATOR ', ') AS chargebackIDs,
loccte.`date` chargebackDate,
SUM(loccte.amount) chargebackTotal,
COUNT(*) AS Count,
GROUP_CONCAT(SUBSTR(loccte.status, 32) ORDER BY loccte.status SEPARATOR ', ') chargebackStatus,
loccte.revert_date chargebackRevert,
GROUP_CONCAT(cce.title ORDER BY cce.title SEPARATOR ', ') AS chargebackCategories,
loccte.entity_id AS chargebackLocId
FROM line_of_credit_credit_transaction_entity loccte
LEFT JOIN credit_category_entity cce ON cce.id = loccte.category_id
WHERE loccte.category_id = 6) chargeback ON chargeback.chargebackLocId = de.entity_id
WHERE loce.deleted = 0
GROUP BY de.idFraud
The Fraud report provides a comprehensive overview of the accounts that started the prior day in the Open - Fraud Process or Closed - Fraudulent Account status. It includes portfolio names assigned to the account, fraud types, fraud descriptions, dispute IDs and other fields that are tied to the Line of Credit Fraud Process.
Sample Fraud Report.csv
Report values
| Column Name | Description |
|---|---|
| Program Name | The name of the program to which the line of credit is assigned. |
| Account ID | Line of credit system ID. |
| Date | The date associated with the report archive. |
| Date Fraud Reported to FinWise | The date an agent records that fraud was reported to FinWise (or other bank). |
| Fraud Type | The general type of fraud. Expected Values: Account, Transaction, or Other. ** If this field is edited to add more selections, the query will need to be edited. |
| Fraud Subtype | The specific type of fraud. Expected Values: ID Theft, Fake Documentation, Application Fraud, Stolen Mail, Elder Fraud, Insider/Employee Fraud, Account Takeover, Debit/Credit Card Fraud, Debt Collection Fraud, Other. |
| Fraud Description | The text description of fraud entered by an agent. |
| Account Status | The account status at the beginning of the previous day. Expected Values: Open or Closed |
| Account Substatus | The account substatus at the beginning of the previous day. Expected Values: Open - Fraud Process and Closed - Fraudulent Account |
| Portfolios | The list of portfolio names assigned to the account at the beginning of the previous day. |
| Account Opening Date | The open date of the line of credit account. |
| Credit Limit | The credit limit of the account. |
| Fraud Decision | Whether the fraud was confirmed or denied. Expected Values: Transaction Fraud Confirmed, Transaction Fraud Denied, Transaction Fraud Partial Confirmation, Account Fraud Confirmed, Account Fraud Denied. |
| Fraud Decision Date | The date the agent confirmed the fraud |
| Account Closure Reason(s) | These are checklist values if selected under the ‘Account Closure’ section. Expected Values: Customer Request, Customer Request to Consolidate Accounts, Customer Request Due To Dissatisfaction With Terms, Customer Request Due To Financial Hardship, Creditor Request Due to Inactivity, Creditor Request Due To Risk Factors, Creditor Request Due To Fraudulent Activity, Creditor Request Due To A Failure To Meet Terms, Creditor Request Due To Company Policy. |
| Primary Customer Name | The full name of the primary customer on the account. |
| Primary Customer Identity Verification | An agent can selected ‘Verified’ or ‘Declined’ as part of customer identity verification. |
| Primary Customer Identity Verification Date | The date the agent entered that the verification was completed. |
| Primary Customer Phone Number | The phone number of the primary customer. Format: XXX-XXX-XXXX |
| Primary Customer Email Address | Email address of the primary customer. |
| Primary Customer Address | Address of the primary customer including street name, city, state, zip. |
| Dispute IDs (Reference Dispute Report for more details) | The IDs of disputes associated to each account. Find these IDs in the Dispute Report for more details. |
SQL query example
SELECT
pe.title AS "Program Name",
loce.id AS "Account ID",
DATE_FORMAT(archive.`date`, '%m/%d/%Y') AS "Date",
DATE_FORMAT(cie35.lastUpdated, '%m/%d/%Y') AS "Fraud Notification Received Date",
DATE_FORMAT(cfe50.custom_field_value, '%m/%d/%Y') AS "Date Fraud Reported to FinWise",
CASE
WHEN cfe3.custom_field_value = 1 THEN "Account"
WHEN cfe3.custom_field_value = 2 THEN "Transaction"
WHEN cfe3.custom_field_value = 3 THEN "Other"
END AS "Fraud Type",
CASE
WHEN cfe49.custom_field_value = 1 THEN "ID Theft"
WHEN cfe49.custom_field_value = 2 THEN "Fake Documentation"
WHEN cfe49.custom_field_value = 3 THEN "Application Fraud"
WHEN cfe49.custom_field_value = 4 THEN "Stolen Mail"
WHEN cfe49.custom_field_value = 5 THEN "Elder Fraud"
WHEN cfe49.custom_field_value = 6 THEN "Insider/Employee Fraud"
WHEN cfe49.custom_field_value = 7 THEN "Account Takeover"
WHEN cfe49.custom_field_value = 8 THEN "Debit/Credit Card Fraud"
WHEN cfe49.custom_field_value = 9 THEN "Debt Collection Fraud"
WHEN cfe49.custom_field_value = 10 THEN "Other"
END AS "Fraud Subtype",
cfe31.custom_field_value AS "Fraud Description",
archive.status AS "Account Status",
archive.substatus AS "Account Substatus",
archive.portfolio_titles_list AS "Portfolios",
DATE_FORMAT(locse2.open_date, '%m/%d/%Y') AS "Account Opening Date",
CONCAT('$', archive.credit_limit) AS "Credit Limit",
CASE
WHEN cfe25.custom_field_value = 1 THEN "Transaction Fraud Confirmed"
WHEN cfe25.custom_field_value = 2 THEN "Transaction Fraud Denied"
WHEN cfe25.custom_field_value = 3 THEN "Transaction Fraud Partial Confirmation"
WHEN cfe25.custom_field_value = 4 THEN "Account Fraud Confirmed"
WHEN cfe25.custom_field_value = 5 THEN "Account Fraud Denied"
END AS "Fraud Decision",
DATE_FORMAT(cfe1.custom_field_value, '%m/%d/%Y') AS "Fraud Decision Date",
acct_closed.closed_reasons AS "Account Closure Reason(s)",
CONCAT(ce.first_name,' ',ce.middle_name,' ',ce.last_name) AS "Primary Customer Name",
CASE
WHEN cfe53.custom_field_value = 1 THEN "Verified"
WHEN cfe53.custom_field_value = 2 THEN "Declined"
END AS "Primary Customer Identity Verification",
DATE_FORMAT(cfe52.custom_field_value, '%m/%d/%Y') AS "Primary Customer Identity Verification Date",
CONCAT(SUBSTR(cpe.phone, 1, 3),'-',SUBSTR(cpe.phone, 4, 3),'-',SUBSTR(cpe.phone, 7, 4)) AS "Primary Customer Phone Number",
ce.email AS "Primary Customer Email Address",
CONCAT(ae.address1, IF(ae.address2 <> '', CONCAT(ae.address2, ', '), ''), ', ', ae.city, ', ', SUBSTR(ae.state, 11), ', ', ae.zipcode) AS "Primary Customer Physical Address",
disputes.dispute_id_list AS "Dispute IDs (Reference Dispute Report for details)"
FROM
line_of_credit_entity loce
-- Program name
JOIN product_entity pe ON pe.id = loce.product_id
-- Fraud Notification Received
LEFT JOIN checklist_item__entity cie35 ON cie35.entity_id = loce.id AND cie35.entity_type = 'Entity.LineOfCredit' AND cie35.checklist_item_id = 35
-- Open - Fraud Process Status/Closed - Fraud Fraudulent Account Status/Portfolio Names/Credit Limit
JOIN (SELECT
locda.`date` AS date,
locda.line_of_credit_id AS loc_id,
locda.line_of_credit_status_text AS status,
locda.line_of_credit_sub_status_text AS substatus,
locda.total_credit_limit AS credit_limit,
REPLACE(REPLACE(REPLACE(locda.portfolio_breakdown, '[', ''), ']', ''), '"', '') AS portfolio_ids_list,
GROUP_CONCAT(DISTINCT pe.title ORDER BY pe.id SEPARATOR ', ') AS portfolio_titles_list
FROM
line_of_credit_daily_archive locda
LEFT JOIN line_of_credit__portfolio locp ON locp.line_of_credit_id = locda.line_of_credit_id
LEFT JOIN portfolio_entity pe ON pe.id = locp.portfolio_id
WHERE
FIND_IN_SET(pe.id, REPLACE(REPLACE(REPLACE(locda.portfolio_breakdown, '[', ''), ']', ''), '"', ''))
AND (locda.line_of_credit_sub_status_id = 10 OR locda.line_of_credit_sub_status_id = 35)
AND locda.`date` = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
GROUP BY
locda.line_of_credit_id) archive ON archive.loc_id = loce.id
-- Open Date
LEFT JOIN line_of_credit_setup_entity locse2 ON locse2.id = loce.setup_id
-- Fraud Type
LEFT JOIN custom_field__entity cfe3 ON cfe3.entity_id = loce.settings_id AND cfe3.custom_field_id = 3
-- Fraud Subtype
LEFT JOIN custom_field__entity cfe49 ON cfe49.entity_id = loce.settings_id AND cfe49.custom_field_id = 49
-- Fraud Decision
LEFT JOIN custom_field__entity cfe25 ON cfe25.entity_id = loce.settings_id AND cfe25.custom_field_id = 25
-- Fraud Decision Date
LEFT JOIN custom_field__entity cfe1 ON cfe1.entity_id = loce.settings_id AND cfe1.custom_field_id = 1
-- Date Reported to FinWise
LEFT JOIN custom_field__entity cfe50 ON cfe50.entity_id = loce.settings_id AND cfe50.custom_field_id = 50
-- Fraud Description
LEFT JOIN custom_field__entity cfe31 ON cfe31.entity_id = loce.settings_id AND cfe31.custom_field_id = 31
-- Account Closure Reasons
LEFT JOIN (SELECT
cie2.entity_id AS loc_id,
cie2.checklist_item_id AS checklist_id,
GROUP_CONCAT(cie.title SEPARATOR ', ') AS closed_reasons,
cie2.checklist_item_value AS checklist_value
FROM
checklist_item_entity cie
JOIN checklist_item__entity cie2 ON cie2.checklist_item_id = cie.id AND cie2.checklist_item_value = 1 AND cie2.entity_type = 'Entity.LineOfCredit'
WHERE
cie.id BETWEEN 37 AND 45
GROUP BY cie2.entity_id) acct_closed ON acct_closed.loc_id = loce.id
-- Primary Customer ID
JOIN line_of_credit__customer locc ON locc.line_of_credit_id = loce.id AND customer_role = 'loan.customerRole.primary'
-- Primary Customer Entity
JOIN customer_entity ce ON ce.id = locc.customer_id
-- Primary Customer Phone Number
LEFT JOIN customer_phone_entity cpe ON cpe.entity_id = ce.id AND cpe.deleted = 0
-- Primary Customer Address
LEFT JOIN address_entity ae ON ae.id = ce.mail_address_id
-- Primary Customer Identity Verification
LEFT JOIN custom_field__entity cfe53 ON cfe53.entity_id = ce.id AND cfe53.custom_field_id = 53
-- Primary Customer Identity Verification Date
LEFT JOIN custom_field__entity cfe52 ON cfe52.entity_id = ce.id AND cfe52.custom_field_id = 52
-- Disputed Swipes
LEFT JOIN (SELECT
de.entity_id AS loc_id,
GROUP_CONCAT(de.id ORDER BY de.id SEPARATOR ", ") AS dispute_id_list
FROM
dispute_entity de
GROUP BY de.entity_id) disputes ON disputes.loc_id = loce.id
WHERE loce.deleted = 0Historical archive
The Historical Archive report provides a snapshot of the previous day for all accounts from the line of credit daily archive. Details regarding autopays, outstanding balances, days past due, and transaction totals will be broken down in this report.
Sample Historical Archive Report.csv
Report values
| Column Name | Description |
|---|---|
| Account ID | Line of credit system ID. |
| Date | This is the date of the archive. |
| Billing Cycle Start Date | This is the date the billing cycle starts. |
| Next AutoPay Date | This is the next date that an AutoPay occur. |
| Next AutoPay Amount | This is the amount of next AutoPay. |
| Billing Cycle End Date | This is the date the billing cycle ends. |
| Outstanding Balance | This is the total balance on the line of credit account. |
| Interest Bearing Amount | This is the amount of the balance that bears interest. |
| Interest Charges | This is the total amount of interest charges on the account. |
| Fees | This is the total amount of fees charged on the account. |
| Purchases | This is the total amount accrued via swipes. |
| Payments & Credits | This is the total amount of payments and credits. |
| Balance Breakdown | This is the breakdown of the account balance. |
| Totals Breakdown | This is the breakdown of the account totals. |
| Available Credit | This is the amount of total available credit. |
| Available Credit Breakdown | This is the breakdown of available credit. |
| Credit Limit | This is the account's total credit limit. |
| Interest Charges Breakdown | This is the breakdown of the interest charges. |
| Fees Breakdown | This is the breakdown of the fees. |
| Days Past Due | This is the amount of days loan is past due. |
| Amount Past Due | This is the amount of money past due. |
| Next Statement Date | This is the date the next statement will generate. |
| Billing Cycle Due Date | This is the due date of the billing cycle. |
| Next Due Date | This is the next date the loan will be due. |
| Status ID | This is the status of the line of credit account. |
| Status | This is the status of the line of credit account in text. |
| Substatus ID | This is the sub-status of the line of credit account. |
| Substatus | This is the sub-status of the line of credit account in text. |
| Credit Status | This is the status of credit. |
| Age of Account | This is the age of the line of credit account. |
| Account Recency | This is the amount of days between the date on which the late payment occurred and now. |
| Last Human Activity | This is the last date of human activity on the account. |
| Net Charge Off | This is the amount of net charge-off. |
| First Delinquency Date | This is the first date the loan was delinquent. |
| Unique Delinquencies | This is the number of delinquencies that have occurred on the account. |
| Delinquency Percent | This is the percent of the account that is delinquent. |
| Days Delinquent | This is how many days the loan is delinquent. |
| Primary ECOA Code | This is the determined primary ECOA code. |
| Secondary ECOA Code | This is the determined secondary ECOA code. |
| Deleted Account | This determines whether the account was deleted. |
| Custom Fields Breakdown | This is the breakdown of custom fields on the account. |
| Portfolio Breakdown | This is the breakdown of the account's portfolios. |
| Subportfolio Breakdown | This is the breakdown of the account's sub-portfolios. |
| Last Updated | This is the date the account was last updated. |
| Source Company ID | This is the system ID for the associated source company. |
| Source Company | This is the description of associated Source Company. |
| Minimum Payment | This is the amount of minimum payment required on the account. |
| Delinquency Table | This holds a table showing delinquency on the line of credit account. |
| Abated Purchases | This holds info about swipes that occur in the abatement period. |
| Past Fees | This holds info about past fees. |
| Remaining Minimum Payment | This is the remaining minimum payment amount, if applicable. |
| Linked Accounts Total Balance | This is the sum of the total balances for all linked accounts. |
| Linked Accounts Days Past Due | This is the highest number of days past due across all linked accounts. |
| Linked Accounts Amount Past Due | This is the sum of the amount past due for all linked accounts. |
| Highest Credit Limit | This is the amount of the highest credit limit. |
| Payment Rating | This is an integer that represents the amount of days past due. DPD >= 150: “5” DPD >= 120: “4” DPD >= 90: “3” DPD >= 60: “2” DPD >= 30: “1” Else= “0” |
| Payment History Profile | This is a string of numbers and letters that represents the payment history. |
| Date of First Delinquency | This is the date of the first delinquency on the account. |
| Linked Accounts Effective Balance | This is the sum of the total effective balance for all linked accounts. |
| Remaining Statement Balance | The unpaid portion of the statement balance after any payments or credits have been applied. |
| Period Purchases | Refers to the total amount of purchases made on an account during a specific billing cycle. |
| Credit Utilization | Percentage of the available credit that is currently being used. |
| Period Interest Charges | Total amount of interest accrued on the account balance during a specific billing cycle. |
| Period Fees | Total charges applied to the account during a specific billing cycle. |
| Period Payments & Credits | Total amount of money applied to the account during a specific billing cycle to reduce the balance. |
| Amount Past Due 30 | This is 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. |
SQL query example
SELECT
locda.line_of_credit_id AS "Account ID",
DATE_FORMAT(locda.`date`, '%m/%d/%Y') AS "Date",
DATE_FORMAT(locda.billing_cycle_start_date, '%m/%d/%Y') AS "Billing Cycle Start Date",
DATE_FORMAT(locda.next_autopay_date, '%m/%d/%Y') AS "Next AutoPay Date",
CONCAT('$', locda.next_autopay_amount) AS "Next AutoPay Amount",
DATE_FORMAT(locda.billing_cycle_end_date, '%m/%d/%Y') AS "Billing Cycle End Date",
CONCAT('$', locda.total_balance) AS "Outstanding Balance",
CONCAT('$', locda.total_interest_bearing_amount) "Interest Bearing Amount",
CONCAT('$', locda.total_interest_charges) AS "Interest Charges",
CONCAT('$', locda.total_fees) AS "Fees",
CONCAT('$', locda.total_swipes) AS "Purchases",
CONCAT('$', locda.total_payments_and_credits) AS "Payments & Credits",
locda.balance_breakdown AS "Balance Breakdown",
locda.totals_breakdown AS "Totals Breakdown",
CONCAT('$', locda.total_available_credit) AS "Available Credit",
locda.available_credit_breakdown AS "Available Credit Breakdown",
CONCAT('$', locda.total_credit_limit) AS "Credit Limit",
locda.interest_charges_breakdown AS "Interest Charges Breakdown",
locda.fees_breakdown AS "Fees Breakdown",
locda.days_past_due AS "Days Past Due",
CONCAT('$', locda.amount_past_due) AS "Amount Past Due",
DATE_FORMAT(locda.next_statement_date, '%m/%d/%Y') AS "Next Statement Date",
DATE_FORMAT(locda.billing_cycle_due_date, '%m/%d/%Y') AS "Billing Cycle Due Date",
DATE_FORMAT(locda.next_due_date, '%m/%d/%Y') AS "Next Due Date",
locda.line_of_credit_status_id AS "Status ID",
locda.line_of_credit_status_text AS "Status",
locda.line_of_credit_sub_status_id AS "Substatus ID",
locda.line_of_credit_sub_status_text AS "Substatus",
SUBSTRING(locda.credit_status, 19) AS "Credit Status",
locda.line_of_credit_recency AS "Account Recency",
DATE_FORMAT(locda.last_human_activity, '%m/%d/%Y') AS "Last Human Activity",
CONCAT('$', locda.net_charge_off) AS "Net Charge Off",
DATE_FORMAT(locda.first_delinquency_date, '%m/%d/%Y') AS "First Delinquency Date",
locda.unique_delinquencies AS "Unique Delinquencies",
CONCAT(locda.delinquency_percent, '%') AS "Delinquency Percent",
locda.delinquent_days AS "Days Delinquent",
SUBSTRING(locda.calced_ecoa, 16) AS "Primary ECOA Code",
SUBSTRING(locda.calced_ecoa_cobuyer, 16) AS "Secondary ECOA Code",
IF (locda.deleted = 1, "deleted", "active") AS "Deleted Account",
locda.custom_fields_breakdown AS "Custom Fields Breakdown",
locda.portfolio_breakdown AS "Portfolio Breakdown",
locda.sub_portfolio_breakdown AS "Subportfolio Breakdown",
DATE_FORMAT(locda.lastUpdated, '%m/%d/%Y %r') AS "Last Updated",
locda.source_company_id AS "Source Company ID",
locda.source_company_text AS "Source Company",
CONCAT('$', locda.minimum_payment) AS "Minimum Payment",
locda.delinquency_table AS "Delinquency Table",
CONCAT('$', locda.abated_swipes) AS "Abated Purchases",
CONCAT('$', locda.past_fees) AS "Past Fees",
CONCAT('$', locda.remaining_minimum_payment) AS "Remaining Minimum Payment",
CONCAT('$', locda.linked_accounts_total_balance) AS "Linked Accounts Total Balance",
locda.linked_accounts_days_past_due AS "Linked Accounts Days Past Due",
CONCAT('$', locda.linked_accounts_amount_past_due) AS "Linked Accounts Amount Past Due",
CONCAT('$', locda.highest_credit) AS "Highest Credit Limit",
locda.payment_rating AS "Payment Rating",
locda.payment_history_profile AS "Payment History Profile",
DATE_FORMAT(locda.date_of_first_delinquency, '%m/%d/%Y') AS "Date of First Delinquency",
CONCAT('$', locda.linked_accounts_effective_balance) "Linked Accounts Effective Balance",
CONCAT('$', locda.remaining_statement_balance) AS "Remaining Statement Balance",
CONCAT('$', locda.period_swipes) AS "Period Purchases",
CONCAT(locda.percent_credit_utilization, '%') AS "Credit Utilization",
CONCAT('$', locda.period_interest_charges) AS "Period Interest Charges",
CONCAT('$', locda.period_fees) AS "Period Fees",
CONCAT('$', locda.period_payments_and_credits) AS "Period Payments & Credits",
CONCAT('$', locda.amount_past_due_30) AS "Amount Past Due 30"
FROM
line_of_credit_daily_archive locda
WHERE locda.`date` = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)Rewards accrual or redemption enabled
The Rewards Accrual or Redemption Enabled report provides configuration at the account level that will indicate the ID of the account along with whether or not accrual or redemption is enabled.
Sample Rewards Accrual or Redemption Enabled Report.csv
Report values
| Column Name | Description |
|---|---|
| Account ID | The system ID of the line of credit. |
| Accrual Enabled | Whether or not reward accrual is enabled. No |
| Redemption Enabled | Whether or not reward redemption is enabled. No |
SQL query example
SELECT
reward.line_of_credit_id AS "Account ID",
IF (reward.reward_accrual_enabled = 1, "Yes", "No") AS "Accrual Enabled",
IF (reward.reward_redemption_enabled = 1, "Yes", "No") AS "Redemption Enabled"
FROM
account_reward_base rewardRewards Redemption
The Rewards Redemption report provides an overview of all credits that are in the specified credit category designated for rewards and were created the previous day.
Sample Rewards Redemption Report.csv
Report values
| Column Name | Description |
|---|---|
| Account ID | The system ID of the line of credit account. |
| Credit TX ID | The ID of the credit transaction. |
| Credit Title | The title of the credit transaction. |
| Points Spent | By default, this is the amount of the credit multiplied by 100. This will be the case if each cent is worth 1 rewards point for your program. |
| Credit Amount | Amount of the credit. |
| Apply Date | The apply date of the credit. |
| Created Date | The date the credit was created. |
| Credit Category Title | The title of the credit category. |
| Credit Category ID | The ID of the credit category. By default, this report will look for ID 10. |
SQL query example
SET @timezone = 'America/Denver';
SELECT
credit.entity_id as "Account ID",
credit.id AS "Credit TX ID",
credit.title AS "Credit Title",
credit.amount * 100 AS "Points Spent",
credit.amount AS "Credit Amount",
DATE_FORMAT(credit.`date`, '%m/%d/%Y') AS "Apply Date",
CONCAT(' ', DATE_FORMAT(CONVERT_TZ(credit.created, 'UTC', @timezone), '%m/%d/%Y %r')) AS "Created Date",
category.title AS "Credit Cateogry Title",
credit.category_id AS "Credit Category ID"
FROM
line_of_credit_credit_transaction_entity credit
LEFT JOIN credit_category_entity category ON category.id = credit.category_id
WHERE
credit.category_id = 10
AND DATE(CONVERT_TZ(credit.created, 'UTC', @timezone)) = DATE_SUB(DATE(CONVERT_TZ(NOW(), 'UTC', @timezone)), INTERVAL 1 DAY)Rewards earning
The Rewards Earning report provides an overview of all transactions that resulted in a reward applied to the line of credit account on the previous day.
Sample Rewards Earning Data Report.csv
Report values
| Column Name | Description |
|---|---|
| Account ID | The system ID of the line of credit. |
| Transaction Type | The type of transaction that caused the rewards balance to be created. |
| Transaction ID | The ID of the transaction that caused the rewards transaction to be created. |
| Apply Date | The apply date that points were added/subtracted for the account. |
| Created Date | The created date of the transactions. |
| Base Rate | The base rate used to calculate the number of points for this transaction. This does not include modifiers applied, if any. |
| Modifier Value | The value of the modifier applied to the rewards transaction. E.g. if the modifier adds 0.25% to the base rate, this will hold "0.25" |
| Points Accrued | The total number of points accrued on this transaction. |
| Modifier Name | The name of the modifier applied to the rewards transaction |
SQL query example
SET @timezone = 'America/Denver';
SELECT
reward_tx.entity_id AS "Account ID",
reward_tx.entity_type AS "Transaction Type",
reward_tx.id AS "Transaction ID",
DATE_FORMAT(reward_tx.apply_date, '%m/%d/%Y') AS "Apply Date",
CONCAT(' ', DATE_FORMAT(CONVERT_TZ(reward_tx.created, 'UTC', @timezone), '%m/%d/%Y %r')) AS "Created Date",
reward_tx.base_rate AS "Base Rate",
modifier.modifier_value AS "Modifier Value",
reward_tx.total_points_accrued AS "Points Accrued",
modifier.modifier_name AS "Modifier Name"
FROM
account_reward_transaction reward_tx
LEFT JOIN account_reward_transaction_modifier_xref modifier ON modifier.account_reward_transaction_id = reward_tx.id
WHERE DATE(CONVERT_TZ(reward_tx.created, 'UTC', @timezone)) = DATE_SUB(DATE(CONVERT_TZ(NOW(), 'UTC', @timezone)), INTERVAL 1 DAY)Roll-forward
The Roll-forward report provides a financial or operational document used to track changes in account balances or other metrics over a specific period. It provides a clear view of how the beginning balance transitions to the ending balance through various activities or adjustments. This report provides ‘Previous Day’ (day before yesterday) and ‘Current’ (yesterday) values, as well as the difference between the two days.
Sample Roll Forward Report.csv
Report values
| Column Name | Description |
|---|---|
| LoC Display ID | The display ID of the line of credit account in the LMS. |
| LoC System ID | The system ID of the line of credit account found in the URL. |
| As of Date | The date the values are pulled from. This date should always be yesterday’s date. |
| Previous Day Total Balance | The total balance recorded on the account as of two days ago. |
| Swipes | The total amount of settled swipes that occurred on the account yesterday. |
| Payments | The total amount of payments created on the account yesterday, not including reversed payments. |
| Credits | The total amount of credits posted on the account yesterday, not including reversed credits. |
| Fees | The total amount of fees posted on the account yesterday, not including reversed fees. |
| Interest Charges | The total amount of interest charges recorded on the account yesterday for accounts that use the Daily Balance method. |
| Finance Charges | The total amount of finance charges applied to the account yesterday. |
| Chargebacks | The original disputed amount that settled on the account yesterday. |
| Net Charge Off | The net charge off amount recorded on the account yesterday. |
| Current Total Balance | The total balance recorded on the account as of end of day yesterday. |
| Total Balance Difference | The difference between the Previous Day Total Balance and the Current Total Balance columns. |
SQL query example
SET @timezone = 'America/Denver';
SELECT
loc.display_id AS "LoC Display ID",
loc.id AS "LoC System ID",
DATE_FORMAT(DATE_SUB(DATE(CONVERT_TZ(NOW(), 'UTC', @timezone)), INTERVAL 1 DAY), '%m/%d/%Y') AS "As of Date (Text)",
IFNULL(start_date.total_balance, 0) AS "Previous Day Total Balance",
IFNULL(SUM(end_swipes.amount), 0) AS "Swipes",
-(IFNULL(SUM(end_payments.amount), 0) - IFNULL(SUM(end_reverse_payments.amount), 0)) AS "Payments",
-(IFNULL(SUM(end_credits.amount), 0) - IFNULL(SUM(end_reverse_credits.amount), 0)) AS "Credits",
IFNULL(SUM(end_fees.amount), 0) - IFNULL(SUM(end_reverse_fees.amount), 0) AS "Fees",
FORMAT(SUM(JSON_UNQUOTE(JSON_EXTRACT(end_date.balance_breakdown, '$."1"."interest-charges"'))), 2) AS "Interest Charges",
IFNULL(SUM(end_charges.amount), 0) AS "Finance Charges",
IFNULL(end_chargebacks.original_dispute_amount, 0) AS "Chargebacks",
IFNULL(end_date.net_charge_off, 0) AS "Net Charge Off",
IFNULL(end_date.total_balance, 0) AS "Current Total Balance",
IFNULL(end_date.total_balance, 0) - IFNULL(start_date.total_balance, 0) AS "Total Balance Difference"
FROM
line_of_credit_entity loc
LEFT JOIN
line_of_credit_daily_archive archive
ON archive.line_of_credit_id = loc.id
AND archive.date = DATE(CONVERT_TZ(NOW(), 'UTC', @timezone))
LEFT JOIN
line_of_credit_daily_archive start_date
ON start_date.line_of_credit_id = loc.id
AND start_date.date = DATE_SUB(DATE(CONVERT_TZ(NOW(), 'UTC', @timezone)), INTERVAL 2 DAY)
LEFT JOIN
line_of_credit_daily_archive end_date
ON end_date.line_of_credit_id = loc.id
AND end_date.date = DATE_SUB(DATE(CONVERT_TZ(NOW(), 'UTC', @timezone)), INTERVAL 1 DAY)
-- SWIPES --
LEFT JOIN
line_of_credit_swipe_entity end_swipes
ON end_swipes.line_of_credit_id = loc.id
AND end_swipes.settled_date = DATE_SUB(DATE(CONVERT_TZ(NOW(), 'UTC', @timezone)), INTERVAL 1 DAY)
-- PAYMENTS --
LEFT JOIN
line_of_credit_payment_entity end_payments
ON end_payments.entity_id = loc.id
AND end_payments.entity_type = 'Entity.LineOfCredit'
AND end_payments.created = DATE_SUB(DATE(CONVERT_TZ(NOW(), 'UTC', @timezone)), INTERVAL 1 DAY)
AND end_payments.reverse_date IS NULL
LEFT JOIN
line_of_credit_payment_entity end_reverse_payments
ON end_reverse_payments.entity_id = loc.id
AND end_reverse_payments.entity_type = 'Entity.LineOfCredit'
AND end_reverse_payments.reverse_date = DATE_SUB(DATE(CONVERT_TZ(NOW(), 'UTC', @timezone)), INTERVAL 1 DAY)
-- CREDITS --
LEFT JOIN
line_of_credit_credit_transaction_entity end_credits
ON end_credits.entity_id = loc.id
AND end_credits.entity_type = 'Entity.LineOfCredit'
AND end_credits.date = DATE_SUB(DATE(CONVERT_TZ(NOW(), 'UTC', @timezone)), INTERVAL 1 DAY)
AND end_credits.revert_date IS NULL
LEFT JOIN
line_of_credit_credit_transaction_entity end_reverse_credits
ON end_reverse_credits.entity_id = loc.id
AND end_reverse_credits.entity_type = 'Entity.LineOfCredit'
AND end_reverse_credits.revert_date = DATE_SUB(DATE(CONVERT_TZ(NOW(), 'UTC', @timezone)), INTERVAL 1 DAY)
-- FEES --
LEFT JOIN
line_of_credit_charge_entity end_fees
ON end_fees.entity_id = loc.id
AND end_fees.entity_type = 'Entity.LineOfCredit'
AND end_fees.application_date = DATE_SUB(DATE(CONVERT_TZ(NOW(), 'UTC', @timezone)), INTERVAL 1 DAY)
AND end_fees.revert_date IS NULL
LEFT JOIN
line_of_credit_charge_entity end_reverse_fees
ON end_reverse_fees.entity_id = loc.id
AND end_reverse_fees.entity_type = 'Entity.LineOfCredit'
AND end_reverse_fees.revert_date = DATE_SUB(DATE(CONVERT_TZ(NOW(), 'UTC', @timezone)), INTERVAL 1 DAY)
-- FINANCE CHARGES --
LEFT JOIN
line_of_credit_finance_charge_entity end_charges
ON end_charges.line_of_credit_id = loc.id
AND end_charges.apply_date = DATE_SUB(DATE(CONVERT_TZ(NOW(), 'UTC', @timezone)), INTERVAL 1 DAY)
AND end_charges.deleted = 0
-- CHARGEBACKS --
LEFT JOIN
dispute_entity end_chargebacks
ON end_chargebacks.entity_id = loc.id
AND end_chargebacks.entity_type = 'Entity.LineOfCredit'
AND end_chargebacks.status_id = 2
AND end_chargebacks.dispute_end_date = DATE_SUB(DATE(CONVERT_TZ(NOW(), 'UTC', @timezone)), INTERVAL 1 DAY)
WHERE
loc.active = 1
AND loc.deleted = 0
GROUP BY
loc.idSettlement summary
The Settlement Summary report provides an overview of all card swipes that experienced a cleared event for each day of the month. This report can also be used for financial reconciliation.
Report values
| Column Name | Description |
|---|---|
| Event Category | This field will always display as ‘card-swipe-event’. |
| Created Timestamp | The timestamp of the event was created in UTC. |
| Event Type | The type of the event. Forced Clearing Clearing Reversal Credit Clearing Forced Credit Clearing Credit Clearing Reversal Dispute Chargeback Dispute Chargeback Reversal |
| BIN | The BIN of the card used. |
| Network | The card network. |
| Cleared Amount | The amount cleared for the transaction. |
| Visa DPS Card ID | The external ID of the card. |
| LoanPro Card UUID | This is the UUID of the card in Secure Payments or the Merchant Card ID in the LMS. |
| LoanPro Swipe ID | The ID of the swipe in the LMS. |
| LoanPro Swipe Event ID | The ID of the swipe event in Secure Payments. |
| Issuer Event ID | Event ID received from the card issuer, if any. |
Settlement detailed
The Settlement Detailed report provides a comprehensive overview of all card swipes that experienced a cleared event the previous day. It includes details such as the ID of the card that made the transaction, the bin, and network.
Report values
| Column Name | Description |
|---|---|
| Event Category | This field will always display as ‘card-swipe-event’. |
| Created Timestamp | The timestamp of the event was created in UTC. |
| Event Type | The type of the event. Forced Clearing Clearing Reversal Credit Clearing Forced Credit Clearing Credit Clearing Reversal Dispute Chargeback Dispute Chargeback Reversal |
| BIN | The BIN of the card used. |
| Network | The card network. |
| Cleared Amount | The amount cleared for the transaction. |
| Visa DPS Card ID | The external ID of the card. |
| LoanPro Card UUID | This is the UUID of the card in Secure Payments or the Merchant Card ID in the LMS. |
| LoanPro Swipe ID | The ID of the swipe in the LMS. |
| LoanPro Swipe Event ID | The ID of the swipe event in Secure Payments. |
| Issuer Event ID | Event ID received from the card issuer, if any. |
Credit Utilization
- Credit Utilization: provides a historical record of the account and shows the amount of money borrowed on the line of credit at any date over the life of the loan. This report is accesses through each individual account. Within a line of credit account, navigate to Reports > Credit Utilization.
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.
Report File Hub
Using the Report File Hub is our preferred method of delivery. It is a secure, easy-to-use location housed directly within the LoanPro dashboard. To open the Report File Hub, navigate to Reports > Administration > Report File Hub.
Within the Report File Hub, you can 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 also 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.