Line of credit reports
Detailed descriptions of available line of credit reports and instructions for accessing them.
Line of credit reports are available by request via the Report File Hub or a destination of your choosing. Your data delivery options are outlined in our Data on Demand article. These reports are designed to help simplify the process of internal reporting, but can also make it easy to share reports with sponsor banks. The report options include both summary reports and detailed reports that provide granular insights. This article will define each of the available reports and show you how to access them within your tenant.
Available reports
Line of credit reports are scheduled to query the database daily and deliver outputs to your Report File Hub or chosen location. In most cases, the report will display data from the previous day (T-1) although some reports pull data from the previous month.
Each available report is listed below. For an in-depth guide, click the report title.
Charge-off
Charge-off detailed
The Charge-off detailed 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. Reference the Charge Off Summary Report to view totals for each day of the current month.
Report values
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. Reference the Charge Off Detailed Report for more in depth information.
Report values
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.
Report values
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.
Report values
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
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
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
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 mprehensive overview of all past due accounts for the past month. It includes details such as days past due, amount past due, and account statuses.
Report values
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 loans that are in the Open status.
Report values
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-related transactions that were created the previous day. It includes details such as transaction dates, amounts, and credit types.
Report values
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.
Report values
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.
Report values
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 and payments that have been deleted from an account.
Report values
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 = 0Swipes
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.
Report values
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)Reversed payments
The reversed payments report provides a comprehensive overview of reversed 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 and payments that have been deleted from an account.
Report values
SQL query example
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)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.
Report values
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.
Report values
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.
Report values
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.
Report values
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.
Report values
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.
Report values
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.
Report values
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.
Report values
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.
Report values
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 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.
Report values
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)Rollforward
The rollforward 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.
Report values
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
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
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 here.
Report File Hub
Using the Report File Hub is our preferred method of delivery. It is a secure, easy-to-use location housed within the software. To open the Report File Hub, navigate to Reports > Administration > Report File Hub.
In the Report File Hub, you’ll be able to access any of the line of credit reports you requested as well as access or upload other documents you use in your tenant. Within the hub you can use search filters to find reports that have previously uploaded.
Other delivery options
If you have selected a different delivery option than the report file hub, your reports will be uploaded to the destination of your choosing. Open your selected location each day, and the reports will be available to you.
Was this article helpful?
Unclassified Public Data